vcl-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From arku...@apache.org
Subject svn commit: r1655394 - in /vcl/trunk/mysql: update-vcl.sql vcl.sql
Date Wed, 28 Jan 2015 17:24:53 GMT
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`



Mime
View raw message