Author: arkurth
Date: Wed Jan 28 17:24:52 2015
New Revision: 1655394
URL: http://svn.apache.org/r1655394
Log:
VCL-764
In vcl.sql and update-vcl.sql:
Changed default value of computer.predictivemoduleid from 1 to 9. Module 1 is
not by default a predictive loading module. Module 9 is "level 2".
Changed default value of image.basedoffrevisionid from 0 to 1 (noimage).
Cleaned up connectmethod names and descriptions. These are shown to end users
and should be formatted consistently.
Removed constraints:
image.basedoffrevisionid
serverrequest.serverprofileid
sublog.blockRequestid
In update-vcl.sql:
Added PrintMessage procedure for debugging.
Improved DropExistingConstraints procedure to make sure
information_schema.KEY_COLUMN.REFERENCED_TABLE_NAME is not null. It's possible
to have multiple entries which map a table and row. Only the entries with a
REFERENCED_TABLE_NAME value represent actual constraints.
Added an exception handler in AddConstraintIfNotExists. If a constraint can't
be added, the update would exit immediately. It now continues.
Added command to change blockRequest.admingroupid to a smallint(5) to match
usergroup.id column. This is already correct in vcl.sql.
Added commands to change computerloadflow.computerloadstateid and nextstateid
to unsigned to match computerloadstate.id.
Updated natlog table definition to match vcl.sql.
Changed capitalization of user.sshpublickeys to match vcl.conf and the backend
code.
Fixed problem where duplicate entries were inserted into connectmethodport if
the original connectmethod.protocol value was an empty string.
Added several constraints to match vcl.sql.
Modified:
vcl/trunk/mysql/update-vcl.sql
vcl/trunk/mysql/vcl.sql
Modified: vcl/trunk/mysql/update-vcl.sql
URL:
http://svn.apache.org/viewvc/vcl/trunk/mysql/update-vcl.sql?rev=1655394&r1=1655393&r2=1655394&view=diff
==============================================================================
--- vcl/trunk/mysql/update-vcl.sql (original)
+++ vcl/trunk/mysql/update-vcl.sql Wed Jan 28 17:24:52 2015
@@ -144,13 +144,17 @@ BEGIN
SELECT CONSTRAINT_NAME, TABLE_SCHEMA FROM
information_schema.KEY_COLUMN_USAGE WHERE
TABLE_SCHEMA = Database()
AND TABLE_NAME = tableName
- AND COLUMN_NAME = columnName;
+ AND COLUMN_NAME = columnName
+ AND REFERENCED_TABLE_NAME IS NOT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN select_existing_constraint_names;
+
+ -- CALL PrintMessage((SELECT CONCAT('DropExistingConstraints: ', tableName,
'.', columnName)));
REPEAT
FETCH select_existing_constraint_names INTO existing_constraint_name,
database_name;
+ -- CALL PrintMessage((SELECT CONCAT('existing constraint: ',
existing_constraint_name)));
-- SELECT existing_constraint_name, database_name;
IF NOT done THEN
SET @drop_existing_constraint = CONCAT('ALTER TABLE `', Database(),
'`.', tableName, ' DROP FOREIGN KEY ', existing_constraint_name);
@@ -304,6 +308,14 @@ CREATE PROCEDURE `AddConstraintIfNotExis
IN constraintAction tinytext
)
BEGIN
+ DECLARE EXIT HANDLER FOR SQLEXCEPTION
+ BEGIN
+ SELECT CONCAT('WARNING: AddConstraintIfNotExists: ', tableName, '.',
columnName, ' --> ', referencedTableName, '.', referencedColumnName) AS '';
+ -- GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno =
MYSQL_ERRNO, @text = MESSAGE_TEXT;
+ -- SELECT CONCAT('ERROR ', @errno, ': ', @text) AS '';
+ END;
+
+ -- CALL PrintMessage((SELECT CONCAT('AddConstraintIfNotExists: ', tableName,
'.', columnName, ' --> ', referencedTableName, '.', referencedColumnName)));
IF NOT EXISTS (
SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE
TABLE_SCHEMA=Database()
@@ -648,7 +660,7 @@ END$$
-- --------------------------------------------------------
/*
-Procedure : populateConnectMethodPort
+Procedure : moveConnectMethodPortProtocol
Description : Populates connectmethodport table from connectmethod table if it
is empty
*/
@@ -678,7 +690,7 @@ BEGIN
AND TABLE_NAME='connectmethod'
)
THEN
- INSERT INTO connectmethodport (connectmethodid, port, protocol) SELECT
id, port, protocol FROM connectmethod;
+ INSERT INTO connectmethodport (connectmethodid, port, protocol) SELECT
id, port, IFNULL(NULLIF(protocol,''),'TCP') FROM connectmethod;
CALL DropColumnIfExists('connectmethod', 'port');
CALL DropColumnIfExists('connectmethod', 'protocol');
END IF;
@@ -714,6 +726,22 @@ BEGIN
END$$
+-- --------------------------------------------------------
+
+/*
+Procedure : PrintMessage
+Parameters : message
+Description :
+*/
+
+DROP PROCEDURE IF EXISTS `PrintMessage`$$
+CREATE PROCEDURE PrintMessage(
+ IN message VARCHAR(255)
+)
+BEGIN
+ SELECT CONCAT("** ", message) AS '';
+END $$
+
/* ============= End of Stored Procedures ===============*/
-- --------------------------------------------------------
@@ -742,6 +770,7 @@ CALL AddColumnIfNotExists('blockComputer
CALL AddColumnIfNotExists('blockRequest', 'status',
"enum('requested','accepted','completed','rejected','deleted') NOT NULL DEFAULT
'accepted'");
CALL AddColumnIfNotExists('blockRequest', 'comments', "text");
+ALTER TABLE `blockRequest` CHANGE `admingroupid` `admingroupid` smallint(5)
unsigned NOT NULL;
-- --------------------------------------------------------
--
@@ -790,7 +819,7 @@ EXECUTE nextimageid_noimage;
-- change RAM to mediumint
ALTER TABLE `computer` CHANGE `RAM` `RAM` MEDIUMINT UNSIGNED NOT NULL DEFAULT
'0';
ALTER TABLE `computer` CHANGE `location` `location` VARCHAR(255) NULL DEFAULT
NULL;
-CALL AddColumnIfNotExists('computer', 'predictivemoduleid', "SMALLINT(5)
UNSIGNED NOT NULL DEFAULT '8'");
+CALL AddColumnIfNotExists('computer', 'predictivemoduleid', "SMALLINT(5)
UNSIGNED NOT NULL DEFAULT '9'");
-- set datedeleted for deleted computers
UPDATE computer SET datedeleted = NOW() WHERE deleted = 1 AND datedeleted =
'0000-00-00 00:00:00';
@@ -798,6 +827,15 @@ UPDATE computer SET datedeleted = NOW()
-- --------------------------------------------------------
--
+-- Table structure for table `computerloadflow`
+--
+
+ALTER TABLE `computerloadflow` CHANGE `computerloadstateid`
`computerloadstateid` smallint(8) unsigned NOT NULL;
+ALTER TABLE `computerloadflow` CHANGE `nextstateid` `nextstateid` smallint(8)
unsigned default NULL;
+
+-- --------------------------------------------------------
+
+--
-- Table structure for table `computerloadlog`
--
@@ -861,8 +899,6 @@ CREATE TABLE IF NOT EXISTS `connectmetho
KEY `connectmethodid` (`connectmethodid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-CALL moveConnectMethodPortProtocol;
-
-- --------------------------------------------------------
--
@@ -890,11 +926,16 @@ CREATE TABLE IF NOT EXISTS connectlog (
-- Table structure for table `image`
--
--- change minram to mediumint
ALTER TABLE `image` CHANGE `minram` `minram` MEDIUMINT UNSIGNED NOT NULL
DEFAULT '0';
+ALTER TABLE `image` CHANGE `platformid` `platformid` tinyint(3) unsigned NOT
NULL default '1';
+ALTER TABLE `image` CHANGE `size` `size` smallint(5) unsigned NOT NULL default
'0';
+
CALL AddColumnIfNotExists('image', 'imagetypeid', "smallint(5) unsigned NOT
NULL default '1' AFTER ownerid");
CALL AddIndexIfNotExists('image', 'imagetypeid');
+ALTER TABLE `image` CHANGE `basedoffrevisionid` `basedoffrevisionid`
mediumint(8) unsigned NOT NULL default '1';
+CALL AddIndexIfNotExists('image', 'basedoffrevisionid');
+
-- --------------------------------------------------------
--
@@ -993,16 +1034,13 @@ CREATE TABLE IF NOT EXISTS `nathost` (
CREATE TABLE IF NOT EXISTS `natlog` (
`logid` int(10) unsigned NOT NULL,
- `connectmethodportid` tinyint(3) unsigned default NULL,
- `nathostid` smallint(5) unsigned default NULL,
- `publicIPaddress` varchar(15) NOT NULL,
`computerid` smallint(5) unsigned NOT NULL,
+ `publicIPaddress` varchar(15) NOT NULL,
+ `internalIPaddress` varchar(15) NOT NULL,
`publicport` smallint(5) unsigned NOT NULL,
- `privateport` smallint(5) unsigned NOT NULL,
+ `internalport` smallint(5) unsigned NOT NULL,
`protocol` enum('TCP','UDP') NOT NULL,
KEY `logid` (`logid`),
- KEY `connectmethodportid` (`connectmethodportid`),
- KEY `nathostid` (`nathostid`),
KEY `computerid` (`computerid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
@@ -1238,7 +1276,7 @@ CALL AddColumnIfNotExists('request', 'ch
CALL AddColumnIfNotExists('user', 'validated', "tinyint(1) unsigned NOT NULL
default '1'");
CALL AddColumnIfNotExists('user', 'usepublickeys', "tinyint(1) unsigned NOT
NULL default '0'");
-CALL AddColumnIfNotExists('user', 'sshPublicKeys', "text");
+CALL AddColumnIfNotExists('user', 'sshpublickeys', "text");
CALL AddColumnIfNotExists('user', 'rdpport', "SMALLINT UNSIGNED NULL AFTER
`mapserial`");
--
@@ -1476,6 +1514,8 @@ UPDATE image, OS, module SET image.image
UPDATE image, OS, module SET image.imagetypeid = (SELECT `id` FROM `imagetype`
WHERE `name` = 'vmdk') WHERE image.imagetypeid = 0 AND image.OSid = OS.id AND
OS.moduleid = module.id AND module.perlpackage REGEXP 'vmware|esx';
UPDATE image SET image.imagetypeid = (SELECT `id` FROM `imagetype` WHERE
`name` = 'none') WHERE image.imagetypeid = 0;
+UPDATE image SET imagemetaid = NULL WHERE NOT EXISTS (SELECT * FROM imagemeta
WHERE image.imagemetaid = imagemeta.id);
+
-- --------------------------------------------------------
--
@@ -1595,10 +1635,15 @@ DELETE FROM provisioningOSinstalltype WH
-- Inserts for table `connectmethod`
--
+UPDATE `connectmethod` SET name = 'SSH', description = 'SSH for Linux & Unix'
WHERE name = 'ssh';
+UPDATE `connectmethod` SET name = 'RDP', description = 'Remote Desktop for
Windows' WHERE name = 'rdp';
+
INSERT IGNORE INTO `connectmethod` (`name`, `description`, `connecttext`,
`servicename`, `startupscript`) VALUES
-('ssh', 'ssh on port 22', 'You will need to have an X server running on your
local computer and use an ssh client to connect to the system. If you did not
click on the <b>Connect!</b> button from the computer you will be using to
access the VCL system, you will need to return to the <strong>Current
Reservations</strong> page and click the <strong>Connect!</strong> button from
a web browser running on the same computer from which you will be connecting to
the VCL system. Otherwise, you may be denied access to the remote
computer.<br><br>\r\nUse the following information when you are ready to
connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>:
#connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>:
#password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this
reservation only</i>. You will be given a different password for any other
reservations.<br>\r\n<strong><big>NOTE:</big> You cannot use the Windows Remote
Desktop Connection to connect to th
is computer. You must use an ssh client.</strong>', 'ext_sshd',
'/etc/init.d/ext_sshd');
+('SSH', 'SSH for Linux & Unix', 'You will need to have an X server running on
your local computer and use an SSH client to connect to the system. If you did
not click on the <b>Connect!</b> button from the computer you will be using to
access the VCL system, you will need to return to the <strong>Current
Reservations</strong> page and click the <strong>Connect!</strong> button from
a web browser running on the same computer from which you will be connecting to
the VCL system. Otherwise, you may be denied access to the remote
computer.<br><br>\r\nUse the following information when you are ready to
connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>:
#connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>:
#password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this
reservation only</i>. You will be given a different password for any other
reservations.<br>\r\n<strong><big>NOTE:</big> You cannot use the Windows Remote
Desktop Connection to connect
to this computer. You must use an ssh client.</strong>', 'ext_sshd',
'/etc/init.d/ext_sshd');
+
INSERT IGNORE INTO `connectmethod` (`name`, `description`, `connecttext`,
`servicename`, `startupscript`) VALUES
-('RDP', 'Remote Desktop', 'You will need to use a Remote Desktop program to
connect to the system. If you did not click on the <b>Connect!</b> button from
the computer you will be using to access the VCL system, you will need to
return to the <strong>Current Reservations</strong> page and click the
<strong>Connect!</strong> button from a web browser running on the same
computer from which you will be connecting to the VCL system. Otherwise, you
may be denied access to the remote computer.<br><br>\r\n\r\nUse the following
information when you are ready to connect:<br>\r\n<UL>\r\n<LI><b>Remote
Computer</b>: #connectIP#</LI>\r\n<LI><b>User ID</b>:
#userid#</LI>\r\n<LI><b>Password</b>:
#password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this
reservation only</i>. You will be given a different password for any other
reservations.<br>\r\n<br>\r\nFor automatic connection, you can download an RDP
file that can be opened by the Remote Desktop Connection program.<br><br>
\r\n', 'TermService', NULL);
+('RDP', 'Remote Desktop for Windows', 'You will need to use a Remote Desktop
program to connect to the system. If you did not click on the <b>Connect!</b>
button from the computer you will be using to access the VCL system, you will
need to return to the <strong>Current Reservations</strong> page and click the
<strong>Connect!</strong> button from a web browser running on the same
computer from which you will be connecting to the VCL system. Otherwise, you
may be denied access to the remote computer.<br><br>\r\n\r\nUse the following
information when you are ready to connect:<br>\r\n<UL>\r\n<LI><b>Remote
Computer</b>: #connectIP#</LI>\r\n<LI><b>User ID</b>:
#userid#</LI>\r\n<LI><b>Password</b>:
#password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this
reservation only</i>. You will be given a different password for any other
reservations.<br>\r\n<br>\r\nFor automatic connection, you can download an RDP
file that can be opened by the Remote Desktop Connection prog
ram.<br><br>\r\n', 'TermService', NULL);
+
INSERT IGNORE INTO `connectmethod` (`name`, `description`, `connecttext`,
`servicename`, `startupscript`) VALUES
('iRAPP RDP', 'Remote Desktop for OS X', 'You will need to use a Remote
Desktop program to connect to the system. If you did not click on the
<b>Connect!</b> button from the computer you will be using to access the VCL
system, you will need to return to the <strong>Current Reservations</strong>
page and click the <strong>Connect!</strong> button from a web browser running
on the same computer from which you will be connecting to the VCL system.
Otherwise, you may be denied access to the remote computer.<br><br>\r\n\r\nUse
the following information when you are ready to
connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>:
#connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>:
#password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this
reservation only</i>. You will be given a different password for any other
reservations.<br>\r\n<br>\r\nFor automatic connection, you can download an RDP
file that can be opened by the Remote Desktop Connection p
rogram.<br><br>\r\n', NULL, NULL);
@@ -1608,8 +1653,12 @@ INSERT IGNORE INTO `connectmethod` (`nam
-- Inserts for table `connectmethodport`
--
+CALL moveConnectMethodPortProtocol;
+
+UPDATE connectmethodport SET protocol = 'TCP' WHERE protocol = '';
+
INSERT IGNORE INTO `connectmethodport` (`connectmethodid`, `port`, `protocol`)
VALUES
-((SELECT id FROM connectmethod WHERE name = 'ssh'), 22, 'TCP'),
+((SELECT id FROM connectmethod WHERE name LIKE 'ssh'), 22, 'TCP'),
((SELECT id FROM connectmethod WHERE name = 'RDP'), 3389, 'TCP'),
((SELECT id FROM connectmethod WHERE name = 'iRAPP RDP'), 3389, 'TCP');
@@ -1780,6 +1829,81 @@ UPDATE vmprofile SET vmprofile.datastore
-- --------------------------------------------------------
--
+-- Constraints for table `blockComputers`
+--
+
+CALL DropExistingConstraints('blockComputers', 'computerid');
+
+CALL AddConstraintIfNotExists('blockComputers', 'blockTimeid', 'blockTimes',
'id', 'both', 'CASCADE');
+CALL AddConstraintIfNotExists('blockComputers', 'computerid', 'computer',
'id', 'UPDATE', 'CASCADE');
+CALL AddConstraintIfNotExists('blockComputers', 'imageid', 'image', 'id',
'UPDATE', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
+-- Constraints for table `blockRequest`
+--
+
+CALL DropExistingConstraints('blockRequest', 'imageid');
+CALL DropExistingConstraints('blockRequest', 'groupid');
+CALL DropExistingConstraints('blockRequest', 'ownerid');
+
+CALL AddConstraintIfNotExists('blockRequest', 'imageid', 'image', 'id',
'update', 'CASCADE');
+CALL AddConstraintIfNotExists('blockRequest', 'groupid', 'usergroup', 'id',
'update', 'CASCADE');
+CALL AddConstraintIfNotExists('blockRequest', 'ownerid', 'user', 'id',
'update', 'CASCADE');
+CALL AddConstraintIfNotExists('blockRequest', 'admingroupid', 'usergroup',
'id', 'update', 'CASCADE');
+CALL AddConstraintIfNotExists('blockRequest', 'managementnodeid',
'managementnode', 'id', 'update', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
+-- Constraints for table `blockTimes`
+--
+
+CALL DropExistingConstraints('blockTimes', 'blockRequestid');
+CALL AddConstraintIfNotExists('blockTimes', 'blockRequestid', 'blockRequest',
'id', 'both', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
+-- Constraints for table `blockWebDate`
+--
+
+CALL DropExistingConstraints('blockWebDate', 'blockRequestid');
+CALL AddConstraintIfNotExists('blockWebDate', 'blockRequestid',
'blockRequest', 'id', 'both', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
+-- Constraints for table `blockWebTime`
+--
+
+CALL DropExistingConstraints('blockWebTime', 'blockRequestid');
+CALL AddConstraintIfNotExists('blockWebTime', 'blockRequestid',
'blockRequest', 'id', 'both', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
+-- Constraints for table `changelog`
+--
+
+CALL AddConstraintIfNotExists('changelog', 'computerid', 'computer', 'id',
'update', 'CASCADE');
+CALL AddConstraintIfNotExists('changelog', 'logid', 'log', 'id', 'update',
'CASCADE');
+CALL AddConstraintIfNotExists('changelog', 'userid', 'user', 'id', 'update',
'CASCADE');
+
+-- --------------------------------------------------------
+
+--
+-- Constraints for table `clickThroughs`
+--
+
+CALL AddConstraintIfNotExists('clickThroughs', 'userid', 'user', 'id',
'update', 'CASCADE');
+CALL AddConstraintIfNotExists('clickThroughs', 'imageid', 'image', 'id',
'update', 'CASCADE');
+CALL AddConstraintIfNotExists('clickThroughs', 'imagerevisionid',
'imagerevision', 'id', 'update', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
-- Constraints for table `computer`
--
@@ -1787,6 +1911,16 @@ CALL AddConstraintIfNotExists('computer'
CALL AddConstraintIfNotExists('computer', 'vmhostid', 'vmhost', 'id',
'update', 'CASCADE');
CALL AddConstraintIfNotExists('computer', 'imagerevisionid', 'imagerevision',
'id', 'update', 'CASCADE');
CALL AddConstraintIfNotExists('computer', 'nextimageid', 'image', 'id',
'update', 'CASCADE');
+CALL AddConstraintIfNotExists('computer', 'predictivemoduleid', 'module',
'id', 'update', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
+-- Constraints for table `computerloadflow`
+--
+
+CALL AddConstraintIfNotExists('computerloadflow', 'computerloadstateid',
'computerloadstate', 'id', 'update', 'CASCADE');
+CALL AddConstraintIfNotExists('computerloadflow', 'nextstateid',
'computerloadstate', 'id', 'update', 'CASCADE');
-- --------------------------------------------------------
@@ -1811,6 +1945,14 @@ CALL AddConstraintIfNotExists('connectme
-- --------------------------------------------------------
--
+-- Constraints for table `connectmethodport`
+--
+
+CALL AddConstraintIfNotExists('connectmethodport', 'connectmethodid',
'connectmethod', 'id', 'both', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
-- Constraints for table `connectlog`
--
@@ -1824,6 +1966,7 @@ CALL AddConstraintIfNotExists('connectlo
--
CALL AddConstraintIfNotExists('image', 'imagetypeid', 'imagetype', 'id',
'update', 'CASCADE');
+CALL AddConstraintIfNotExists('image', 'imagemetaid', 'imagemeta', 'id',
'update', 'CASCADE');
-- --------------------------------------------------------
@@ -1836,6 +1979,22 @@ CALL AddConstraintIfNotExists('image', '
-- --------------------------------------------------------
--
+-- Constraints for table `log`
+--
+
+CALL AddConstraintIfNotExists('log', 'computerid', 'computer', 'id', 'update',
'CASCADE');
+
+-- --------------------------------------------------------
+
+--
+-- Constraints for table `loginlog`
+--
+
+CALL AddConstraintIfNotExists('loginlog', 'affiliationid', 'affiliation',
'id', 'update', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
-- Constraints for table `nathost`
--
@@ -1849,8 +2008,6 @@ CALL AddConstraintIfNotExists('nathost',
CALL AddConstraintIfNotExists('natlog', 'computerid', 'computer', 'id',
'both', 'CASCADE');
CALL AddConstraintIfNotExists('natlog', 'logid', 'log', 'id', 'both',
'CASCADE');
-CALL AddConstraintIfNotExists('natlog', 'connectmethodportid',
'connectmethodport', 'id', 'UPDATE', 'CASCADE');
-CALL AddConstraintIfNotExists('natlog', 'nathostid', 'nathost', 'id',
'UPDATE', 'CASCADE');
-- --------------------------------------------------------
@@ -1902,8 +2059,8 @@ CALL AddConstraintIfNotExists('provision
-- Constraints for table `reservation`
--
-CALL AddConstraintIfNotExists('reservation', 'imageid', 'image', 'id',
'restrict', 'CASCADE');
-CALL AddConstraintIfNotExists('reservation', 'imagerevisionid',
'imagerevision', 'id', 'restrict', 'CASCADE');
+CALL AddConstraintIfNotExists('reservation', 'imageid', 'image', 'id',
'update', 'CASCADE');
+CALL AddConstraintIfNotExists('reservation', 'imagerevisionid',
'imagerevision', 'id', 'update', 'CASCADE');
-- --------------------------------------------------------
@@ -1917,6 +2074,39 @@ CALL AddConstraintIfNotExists('reservati
-- --------------------------------------------------------
--
+-- Constraints for table `resourcemap`
+--
+
+CALL DropExistingConstraints('resourcemap', 'resourcegroupid1');
+CALL DropExistingConstraints('resourcemap', 'resourcegroupid2');
+
+CALL AddConstraintIfNotExists('resourcemap', 'resourcegroupid1',
'resourcegroup', 'id', 'both', 'CASCADE');
+CALL AddConstraintIfNotExists('resourcemap', 'resourcegroupid2',
'resourcegroup', 'id', 'both', 'CASCADE');
+CALL AddConstraintIfNotExists('resourcemap', 'resourcetypeid1',
'resourcetype', 'id', 'update', 'CASCADE');
+CALL AddConstraintIfNotExists('resourcemap', 'resourcetypeid2',
'resourcetype', 'id', 'update', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
+-- Constraints for table `scheduletimes`
+--
+
+CALL AddConstraintIfNotExists('scheduletimes', 'scheduleid', 'schedule', 'id',
'update', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
+-- Constraints for table `semaphore`
+--
+
+CALL AddConstraintIfNotExists('semaphore', 'computerid', 'computer', 'id',
'update', 'CASCADE');
+CALL AddConstraintIfNotExists('semaphore', 'imageid', 'image', 'id', 'update',
'CASCADE');
+CALL AddConstraintIfNotExists('semaphore', 'imagerevisionid', 'imagerevision',
'id', 'update', 'CASCADE');
+CALL AddConstraintIfNotExists('semaphore', 'managementnodeid',
'managementnode', 'id', 'update', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
-- Constraints for table `serverprofile`
--
@@ -1937,6 +2127,54 @@ CALL AddConstraintIfNotExists('serverreq
-- --------------------------------------------------------
+--
+-- Constraints for table `shibauth`
+--
+
+CALL AddConstraintIfNotExists('shibauth', 'userid', 'user', 'id', 'update',
'CASCADE');
+
+-- --------------------------------------------------------
+
+--
+-- Constraints for table `statgraphcache`
+--
+
+CALL AddConstraintIfNotExists('statgraphcache', 'affiliationid',
'affiliation', 'id', 'update', 'CASCADE');
+CALL AddConstraintIfNotExists('statgraphcache', 'provisioningid',
'provisioning', 'id', 'update', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
+-- Constraints for table `subimages`
+--
+
+CALL AddConstraintIfNotExists('subimages', 'imageid', 'image', 'id', 'update',
'CASCADE');
+CALL AddConstraintIfNotExists('subimages', 'imagemetaid', 'imagemeta', 'id',
'update', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
+-- Constraints for table `sublog`
+--
+
+CALL AddConstraintIfNotExists('sublog', 'logid', 'log', 'id', 'UPDATE',
'CASCADE');
+CALL AddConstraintIfNotExists('sublog', 'imageid', 'image', 'id', 'UPDATE',
'CASCADE');
+CALL AddConstraintIfNotExists('sublog', 'imagerevisionid', 'imagerevision',
'id', 'UPDATE', 'CASCADE');
+CALL AddConstraintIfNotExists('sublog', 'computerid', 'computer', 'id',
'UPDATE', 'CASCADE');
+CALL AddConstraintIfNotExists('sublog', 'managementnodeid', 'managementnode',
'id', 'UPDATE', 'CASCADE');
+CALL AddConstraintIfNotExists('sublog', 'predictivemoduleid', 'module', 'id',
'UPDATE', 'CASCADE');
+CALL AddConstraintIfNotExists('sublog', 'hostcomputerid', 'computer', 'id',
'UPDATE', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
+-- Constraints for table `usergroup`
+--
+
+CALL AddConstraintIfNotExists('usergroup', 'affiliationid', 'affiliation',
'id', 'update', 'CASCADE');
+
+-- --------------------------------------------------------
+
--
-- Constraints for table `usergrouppriv`
--
Modified: vcl/trunk/mysql/vcl.sql
URL:
http://svn.apache.org/viewvc/vcl/trunk/mysql/vcl.sql?rev=1655394&r1=1655393&r2=1655394&view=diff
==============================================================================
--- vcl/trunk/mysql/vcl.sql (original)
+++ vcl/trunk/mysql/vcl.sql Wed Jan 28 17:24:52 2015
@@ -234,7 +234,7 @@ CREATE TABLE IF NOT EXISTS `computer` (
`host` blob,
`hostpub` mediumtext,
`vmtypeid` tinyint(3) unsigned default NULL,
- `predictivemoduleid` smallint(5) unsigned NOT NULL default '1',
+ `predictivemoduleid` smallint(5) unsigned NOT NULL default '9',
PRIMARY KEY (`id`),
UNIQUE KEY `hostname` (`hostname`, `datedeleted`),
UNIQUE KEY `eth1macaddress` (`eth1macaddress`, `datedeleted`),
@@ -443,7 +443,7 @@ CREATE TABLE IF NOT EXISTS `image` (
`architecture` enum('x86','x86_64') NOT NULL default 'x86',
`description` text,
`usage` text,
- `basedoffrevisionid` mediumint(8) unsigned NOT NULL default '0',
+ `basedoffrevisionid` mediumint(8) unsigned NOT NULL default '1',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
UNIQUE KEY `prettyname` (`prettyname`),
@@ -1588,8 +1588,8 @@ INSERT IGNORE INTO `computerloadflow` (`
--
INSERT IGNORE INTO `connectmethod` (`id`, `name`, `description`,
`connecttext`, `servicename`, `startupscript`) VALUES
-(1, 'ssh', 'ssh on port 22', 'You will need to have an X server running on
your local computer and use an ssh client to connect to the system. If you did
not click on the <b>Connect!</b> button from the computer you will be using to
access the VCL system, you will need to return to the <strong>Current
Reservations</strong> page and click the <strong>Connect!</strong> button from
a web browser running on the same computer from which you will be connecting to
the VCL system. Otherwise, you may be denied access to the remote
computer.<br><br>\r\nUse the following information when you are ready to
connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>:
#connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>:
#password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this
reservation only</i>. You will be given a different password for any other
reservations.<br>\r\n<strong><big>NOTE:</big> You cannot use the Windows Remote
Desktop Connection to connect to
this computer. You must use an ssh client.</strong>', 'ext_sshd',
'/etc/init.d/ext_sshd'),
-(2, 'RDP', 'Remote Desktop', 'You will need to use a Remote Desktop program to
connect to the system. If you did not click on the <b>Connect!</b> button from
the computer you will be using to access the VCL system, you will need to
return to the <strong>Current Reservations</strong> page and click the
<strong>Connect!</strong> button from a web browser running on the same
computer from which you will be connecting to the VCL system. Otherwise, you
may be denied access to the remote computer.<br><br>\r\n\r\nUse the following
information when you are ready to connect:<br>\r\n<UL>\r\n<LI><b>Remote
Computer</b>: #connectIP#</LI>\r\n<LI><b>User ID</b>:
#userid#</LI>\r\n<LI><b>Password</b>:
#password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this
reservation only</i>. You will be given a different password for any other
reservations.<br>\r\n<br>\r\nFor automatic connection, you can download an RDP
file that can be opened by the Remote Desktop Connection program.<br><
br>\r\n', 'TermService', NULL),
+(1, 'SSH', 'SSH for Linux & Unix', 'You will need to have an X server running
on your local computer and use an SSH client to connect to the system. If you
did not click on the <b>Connect!</b> button from the computer you will be using
to access the VCL system, you will need to return to the <strong>Current
Reservations</strong> page and click the <strong>Connect!</strong> button from
a web browser running on the same computer from which you will be connecting to
the VCL system. Otherwise, you may be denied access to the remote
computer.<br><br>\r\nUse the following information when you are ready to
connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>:
#connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>:
#password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this
reservation only</i>. You will be given a different password for any other
reservations.<br>\r\n<strong><big>NOTE:</big> You cannot use the Windows Remote
Desktop Connection to conn
ect to this computer. You must use an ssh client.</strong>', 'ext_sshd',
'/etc/init.d/ext_sshd'),
+(2, 'RDP', 'Remote Desktop for Windows', 'You will need to use a Remote
Desktop program to connect to the system. If you did not click on the
<b>Connect!</b> button from the computer you will be using to access the VCL
system, you will need to return to the <strong>Current Reservations</strong>
page and click the <strong>Connect!</strong> button from a web browser running
on the same computer from which you will be connecting to the VCL system.
Otherwise, you may be denied access to the remote computer.<br><br>\r\n\r\nUse
the following information when you are ready to
connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>:
#connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>:
#password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this
reservation only</i>. You will be given a different password for any other
reservations.<br>\r\n<br>\r\nFor automatic connection, you can download an RDP
file that can be opened by the Remote Desktop Connection p
rogram.<br><br>\r\n', 'TermService', NULL),
(3, 'iRAPP RDP', 'Remote Desktop for OS X', 'You will need to use a Remote
Desktop program to connect to the system. If you did not click on the
<b>Connect!</b> button from the computer you will be using to access the VCL
system, you will need to return to the <strong>Current Reservations</strong>
page and click the <strong>Connect!</strong> button from a web browser running
on the same computer from which you will be connecting to the VCL system.
Otherwise, you may be denied access to the remote computer.<br><br>\r\n\r\nUse
the following information when you are ready to
connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>:
#connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>:
#password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this
reservation only</i>. You will be given a different password for any other
reservations.<br>\r\n<br>\r\nFor automatic connection, you can download an RDP
file that can be opened by the Remote Desktop Connectio
n program.<br><br>\r\n', NULL, NULL);
--
@@ -2243,7 +2243,6 @@ ALTER TABLE `image` ADD CONSTRAINT FOREI
ALTER TABLE `image` ADD CONSTRAINT FOREIGN KEY (`OSid`) REFERENCES `OS` (`id`)
ON UPDATE CASCADE;
ALTER TABLE `image` ADD CONSTRAINT FOREIGN KEY (`imagetypeid`) REFERENCES
`imagetype` (`id`) ON UPDATE CASCADE;
ALTER TABLE `image` ADD CONSTRAINT FOREIGN KEY (`imagemetaid`) REFERENCES
`imagemeta` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
-ALTER TABLE `image` ADD CONSTRAINT FOREIGN KEY (`basedoffrevisionid`)
REFERENCES `imagerevision` (`id`) ON UPDATE CASCADE;
--
-- Constraints for table `imagerevision`
@@ -2350,8 +2349,8 @@ ALTER TABLE `request` ADD CONSTRAINT FOR
ALTER TABLE `reservation` ADD CONSTRAINT FOREIGN KEY (`requestid`) REFERENCES
`request` (`id`) ON DELETE CASCADE;
ALTER TABLE `reservation` ADD CONSTRAINT FOREIGN KEY (`managementnodeid`)
REFERENCES `managementnode` (`id`);
ALTER TABLE `reservation` ADD CONSTRAINT FOREIGN KEY (`computerid`) REFERENCES
`computer` (`id`) ON UPDATE CASCADE;
-ALTER TABLE `reservation` ADD CONSTRAINT reservation_ibfk_13 FOREIGN KEY
(imagerevisionid) REFERENCES imagerevision (id) ON UPDATE CASCADE;
-ALTER TABLE `reservation` ADD CONSTRAINT reservation_ibfk_12 FOREIGN KEY
(imageid) REFERENCES image (id) ON UPDATE CASCADE;
+ALTER TABLE `reservation` ADD CONSTRAINT FOREIGN KEY (`imagerevisionid`)
REFERENCES `imagerevision` (`id`) ON UPDATE CASCADE;
+ALTER TABLE `reservation` ADD CONSTRAINT FOREIGN KEY (`imageid`) REFERENCES
`image` (`id`) ON UPDATE CASCADE;
--
-- Constraints for table `reservationaccounts`
@@ -2422,7 +2421,6 @@ ALTER TABLE `serverprofile` ADD CONSTRAI
ALTER TABLE `serverrequest` ADD CONSTRAINT FOREIGN KEY (`requestid`)
REFERENCES `request` (`id`) ON DELETE CASCADE;
ALTER TABLE `serverrequest` ADD CONSTRAINT FOREIGN KEY (`admingroupid`)
REFERENCES `usergroup` (`id`) ON UPDATE CASCADE;
ALTER TABLE `serverrequest` ADD CONSTRAINT FOREIGN KEY (`logingroupid`)
REFERENCES `usergroup` (`id`) ON UPDATE CASCADE;
-ALTER TABLE `serverrequest` ADD CONSTRAINT FOREIGN KEY (`serverprofileid`)
REFERENCES `serverprofile` (`id`) ON UPDATE CASCADE;
--
-- Constraints for table `shibauth`
@@ -2451,7 +2449,6 @@ ALTER TABLE `sublog` ADD CONSTRAINT FORE
ALTER TABLE `sublog` ADD CONSTRAINT FOREIGN KEY (`managementnodeid`)
REFERENCES `managementnode` (`id`);
ALTER TABLE `sublog` ADD CONSTRAINT FOREIGN KEY (`predictivemoduleid`)
REFERENCES `module` (`id`);
ALTER TABLE `sublog` ADD CONSTRAINT FOREIGN KEY (`hostcomputerid`) REFERENCES
`computer` (`id`);
-ALTER TABLE `sublog` ADD CONSTRAINT FOREIGN KEY (`blockRequestid`) REFERENCES
`blockRequest` (`id`);
--
-- Constraints for table `user`
