Author: arkurth Date: Fri Feb 25 18:57:32 2011 New Revision: 1074667 URL: http://svn.apache.org/viewvc?rev=1074667&view=rev Log: VCL-434 Renamed update-2.2.1.sql to update-vcl.sql and added all of the changes from the update-2.2.sql file. This allows all updates to be applied by merging update-vcl.sql whether or not update-2.2.sql had been previously applied.
Added key and constraints for the provisioningOSinstalltype table. VCL-435 Added module and OS table entries to vcl.sql and update-vcl.sql for the ESXi.pm module. Added: incubator/vcl/trunk/mysql/update-vcl.sql - copied, changed from r1074535, incubator/vcl/trunk/mysql/update-2.2.1.sql Removed: incubator/vcl/trunk/mysql/update-2.2.1.sql Modified: incubator/vcl/trunk/mysql/vcl.sql Copied: incubator/vcl/trunk/mysql/update-vcl.sql (from r1074535, incubator/vcl/trunk/mysql/update-2.2.1.sql) URL: http://svn.apache.org/viewvc/incubator/vcl/trunk/mysql/update-vcl.sql?p2=incubator/vcl/trunk/mysql/update-vcl.sql&p1=incubator/vcl/trunk/mysql/update-2.2.1.sql&r1=1074535&r2=1074667&rev=1074667&view=diff ============================================================================== --- incubator/vcl/trunk/mysql/update-2.2.1.sql (original) +++ incubator/vcl/trunk/mysql/update-vcl.sql Fri Feb 25 18:57:32 2011 @@ -15,7 +15,7 @@ limitations under the License. */ --- Apache VCL version 2.2 to 2.2.1 database schema changes +-- Apache VCL version 2.1 to 2.2.1 database schema changes -- -------------------------------------------------------- @@ -190,13 +190,116 @@ BEGIN END IF; END$$ -DELIMITER ; +-- -------------------------------------------------------- + +-- +-- Table structure for table `affiliation` +-- + +CALL AddUniqueIndex('affiliation', 'name'); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `blockComputers` +-- + +CALL AddColumnIfNotExists('blockComputers', 'reloadrequestid', "mediumint(8) unsigned NOT NULL default '0'"); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `blockRequest` +-- + +CALL AddColumnIfNotExists('blockRequest', 'status', "enum('requested','accepted','completed','rejected','deleted') NOT NULL DEFAULT 'accepted'"); +CALL AddColumnIfNotExists('blockRequest', 'comments', "text"); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `blockTimes` +-- + +CALL AddColumnIfNotExists('blockTimes', 'skip', "tinyint(1) unsigned NOT NULL default '0'"); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `computer` +-- + +CALL DropColumnIfExists('computer', 'preferredimageid'); +CALL AddIndexIfNotExists('computer', 'imagerevisionid'); + +-- Set the default values for the currentimage and next image columns to 'noimage' +SET @currentimageid_noimage = CONCAT('ALTER TABLE computer CHANGE currentimageid currentimageid SMALLINT(5) UNSIGNED NOT NULL DEFAULT ', (SELECT id FROM image WHERE name LIKE 'noimage')); +PREPARE currentimageid_noimage FROM @currentimageid_noimage; +EXECUTE currentimageid_noimage; + +SET @nextimageid_noimage = CONCAT('ALTER TABLE computer CHANGE nextimageid nextimageid SMALLINT(5) UNSIGNED NOT NULL DEFAULT ', (SELECT id FROM image WHERE name LIKE 'noimage')); +PREPARE nextimageid_noimage FROM @nextimageid_noimage; +EXECUTE nextimageid_noimage; + +-- -------------------------------------------------------- + +-- +-- Table structure for table `loginlog` +-- + +CREATE TABLE IF NOT EXISTS `loginlog` ( + `user` varchar(50) NOT NULL, + `authmech` varchar(30) NOT NULL, + `affiliationid` mediumint(8) unsigned NOT NULL, + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, + `passfail` tinyint(1) unsigned NOT NULL default '0', + `remoteIP` varchar(15) NOT NULL, + KEY `user` (`user`), + KEY `affiliationid` (`affiliationid`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- -------------------------------------------------------- + +-- +-- Table structure change for table `managementnode` +-- + +CALL AddColumnIfNotExists('managementnode', 'publicIPconfiguration', "enum('dynamicDHCP','manualDHCP','static') NOT NULL default 'dynamicDHCP'"); +CALL AddColumnIfNotExists('managementnode', 'publicSubnetMask', "varchar(56) default NULL"); +CALL AddColumnIfNotExists('managementnode', 'publicDefaultGateway', "varchar(56) default NULL"); +CALL AddColumnIfNotExists('managementnode', 'publicDNSserver', "varchar(56) default NULL"); +CALL AddColumnIfNotExists('managementnode', 'sysadminEmailAddress', "varchar(128) default NULL"); +CALL AddColumnIfNotExists('managementnode', 'sharedMailBox', "varchar(128) default NULL"); +CALL AddColumnIfNotExists('managementnode', 'NOT_STANDALONE', "varchar(128) default NULL"); + +-- -------------------------------------------------------- + +-- +-- Table structure change for table `module` +-- + +CALL AddUniqueIndex('module', 'name'); + +-- -------------------------------------------------------- + +-- +-- Table structure change for table `OS` +-- + +ALTER TABLE `OS` CHANGE `prettyname` `prettyname` varchar(64) NOT NULL default ''; + +-- -------------------------------------------------------- + +-- +-- Table structure change for table `provisioning` +-- +CALL AddUniqueIndex('provisioning', 'name'); -- -------------------------------------------------------- -- --- Create for table `provisioningOSinstalltype` +-- Create table `provisioningOSinstalltype` -- CREATE TABLE IF NOT EXISTS `provisioningOSinstalltype` ( @@ -207,23 +310,124 @@ CREATE TABLE IF NOT EXISTS `provisioning -- -------------------------------------------------------- +-- +-- Table structure for table `sitemaintenance` +-- + +CREATE TABLE IF NOT EXISTS `sitemaintenance` ( + `id` smallint(5) unsigned NOT NULL auto_increment, + `start` datetime NOT NULL, + `end` datetime NOT NULL, + `ownerid` mediumint(8) unsigned NOT NULL, + `created` datetime NOT NULL, + `reason` text, + `usermessage` text NOT NULL, + `informhoursahead` smallint(5) unsigned NOT NULL, + `allowreservations` tinyint(1) unsigned NOT NULL, + PRIMARY KEY (`id`), + KEY `start` (`start`), + KEY `end` (`end`), + KEY `ownerid` (`ownerid`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- -------------------------------------------------------- + -- --- Inserts for table `module` +-- Table structure change for table `request` -- -INSERT IGNORE INTO `module` (`id`, `name`, `prettyname`, `description`, `perlpackage`) VALUES -(22, 'state_image', 'VCL Image State Module', '', 'VCL::image'), -(23, 'base', 'VCL Base Module', '', 'VCL::Module'), -(24, 'provisioning_vbox', 'Virtual Box Provisioning Module', '', 'VCL::Module::Provisioning::vbox'); +CALL AddColumnIfNotExists('request', 'checkuser', "tinyint(1) unsigned NOT NULL default '1'"); + +-- -------------------------------------------------------- + +-- +-- Table structure change for table `vmhost` +-- + +CALL AddIndexIfNotExists('vmhost', 'vmprofileid'); + +-- -------------------------------------------------------- + +-- +-- Table structure change for table `vmprofile` +-- + +CALL DropColumnIfExists('vmprofile', 'nasshare'); +CALL AddColumnIfNotExists('vmprofile', 'repositorypath', "varchar(128) default NULL AFTER imageid"); +CALL AddColumnIfNotExists('vmprofile', 'virtualswitch2', "varchar(80) NULL default NULL AFTER `virtualswitch1`"); +CALL AddColumnIfNotExists('vmprofile', 'virtualswitch3', "varchar(80) NULL default NULL AFTER `virtualswitch2`"); +CALL AddColumnIfNotExists('vmprofile', 'vmware_mac_eth0_generated', "tinyint(1) NOT NULL default '0'"); +CALL AddColumnIfNotExists('vmprofile', 'vmware_mac_eth1_generated', "tinyint(1) NOT NULL default '0'"); -- -------------------------------------------------------- -- --- Inserts for table `provisioning` +-- Table structure for table `winKMS` +-- +CREATE TABLE IF NOT EXISTS `winKMS` ( + `affiliationid` mediumint(8) unsigned NOT NULL, + `address` varchar(50) NOT NULL, + `port` smallint(5) unsigned NOT NULL default '1688', + UNIQUE KEY `affiliationid_address` (`affiliationid`,`address`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- -------------------------------------------------------- + +-- +-- Table structure for table `winProductKey` -- -INSERT IGNORE INTO `provisioning` (`id`, `name`, `prettyname`, `moduleid`) VALUES -('8', 'vbox', 'Virtual Box', (SELECT `id` FROM `module` WHERE `name` LIKE 'provisioning_vbox')); +CREATE TABLE IF NOT EXISTS `winProductKey` ( + `affiliationid` mediumint(8) unsigned NOT NULL, + `productname` varchar(100) NOT NULL, + `productkey` varchar(100) NOT NULL, + UNIQUE KEY `affiliationid_productname` (`affiliationid`,`productname`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- -------------------------------------------------------- + +-- +-- Inserts for table `affiliation` +-- + +INSERT IGNORE INTO `affiliation` (`name`, `dataUpdateText`) VALUES ('Global', ''); + +-- -------------------------------------------------------- + +-- +-- Inserts for table `computer` +-- + +UPDATE `computer` SET `currentimageid` = (SELECT `id` FROM `image` WHERE `name` = 'noimage') WHERE NOT EXISTS (SELECT * FROM `image` WHERE `image`.`id` = `computer`.`currentimageid`); +UPDATE `computer` SET `nextimageid` = (SELECT `id` FROM `image` WHERE `name` = 'noimage') WHERE NOT EXISTS (SELECT * FROM `image` WHERE `image`.`id` = `computer`.`nextimageid`); +UPDATE `computer` SET `imagerevisionid` = (SELECT `id` FROM `imagerevision` WHERE `imagename` = 'noimage') WHERE NOT EXISTS (SELECT * FROM `imagerevision` WHERE `imagerevision`.`id` = `computer`.`imagerevisionid`); + +-- -------------------------------------------------------- + +-- +-- Inserts for table `module` +-- + +UPDATE IGNORE `module` SET `name` = 'provisioning_vmware_1x', `prettyname` = 'VMware Server 1.x Provisioning Module' WHERE `name` = 'provisioning_vmware_gsx'; +INSERT IGNORE INTO `module` (`name`, `prettyname`, `description`, `perlpackage`) VALUES ('os_win7', 'Windows 7 OS Module', '', 'VCL::Module::OS::Windows::Version_6::7'); +INSERT IGNORE INTO `module` (`name`, `prettyname`, `description`, `perlpackage`) VALUES ('provisioning_xcat_2x', 'xCAT 2.x Provisioning Module', '', 'VCL::Module::Provisioning::xCAT2'); +INSERT IGNORE INTO `module` (`name`, `prettyname`, `description`, `perlpackage`) VALUES ('provisioning_vmware', 'VMware Provisioning Module', '', 'VCL::Module::Provisioning::VMware::VMware'); +INSERT IGNORE INTO `module` (`name`, `prettyname`, `description`, `perlpackage`) VALUES ('state_image', 'VCL Image State Module', '', 'VCL::image'); +INSERT IGNORE INTO `module` (`name`, `prettyname`, `description`, `perlpackage`) VALUES ('base_module', 'VCL Base Module', '', 'VCL::Module'); +INSERT IGNORE INTO `module` (`name`, `prettyname`, `description`, `perlpackage`) VALUES ('provisioning_vbox', 'Virtual Box Provisioning Module', '', 'VCL::Module::Provisioning::vbox'); +INSERT IGNORE INTO `module` (`name`, `prettyname`, `description`, `perlpackage`) VALUES ('os_esxi', 'VMware ESXi OS Module', '', 'VCL::Module::OS::Linux::ESXi'); + +-- -------------------------------------------------------- + +-- +-- Inserts for table `OS` +-- + +INSERT IGNORE INTO `OS` (`name`, `prettyname`, `type`, `installtype`, `sourcepath`, `moduleid`) VALUES ('win7', 'Windows 7', 'windows', 'partimage', 'image', (SELECT `id` FROM `module` WHERE `name` LIKE 'os_win7')); +INSERT IGNORE INTO `OS` (`name`, `prettyname`, `type`, `installtype`, `sourcepath`, `moduleid`) VALUES ('vmwarewin7', 'VMware Windows 7', 'windows', 'vmware', 'vmware_images', (SELECT `id` FROM `module` WHERE `name` LIKE 'os_win7')); +INSERT IGNORE INTO `OS` (`name`, `prettyname`, `type`, `installtype`, `sourcepath`, `moduleid`) VALUES ('vmwarelinux', 'VMware Generic Linux', 'linux', 'vmware', 'vmware_images', (SELECT `id` FROM `module` WHERE `name` LIKE 'os_linux')); +INSERT IGNORE INTO `OS` (`name`, `prettyname`, `type`, `installtype`, `sourcepath`, `moduleid`) VALUES ('vmwarewin2003', 'VMware Windows 2003 Server', 'windows', 'vmware', 'vmware_images', (SELECT `id` FROM `module` WHERE `name` LIKE 'os_win2003')); +INSERT IGNORE INTO `OS` (`name`, `prettyname`, `type`, `installtype`, `sourcepath`, `moduleid`) VALUES ('esxi4.1', 'VMware ESXi 4.1', 'linux', 'kickstart', 'esxi4.1', (SELECT `id` FROM `module` WHERE `name` LIKE 'os_esxi')); -- -------------------------------------------------------- @@ -231,26 +435,82 @@ INSERT IGNORE INTO `provisioning` (`id`, -- Inserts for table `OSinstalltype` -- -INSERT IGNORE INTO `OSinstalltype` (`id`, `name`) VALUES ('5', 'vbox'); +INSERT IGNORE INTO `OSinstalltype` (`name`) VALUES ('vbox'); -- -------------------------------------------------------- + +-- +-- Inserts for table `provisioning` +-- + +INSERT IGNORE INTO `provisioning` (`name`, `prettyname`, `moduleid`) VALUES ('xcat_2x', 'xCAT 2.x', (SELECT `id` FROM `module` WHERE `name` LIKE 'provisioning_xcat_2x')); +INSERT IGNORE INTO `provisioning` (`name`, `prettyname`, `moduleid`) VALUES ('vmware', 'VMware', (SELECT `id` FROM `module` WHERE `name` LIKE 'provisioning_vmware')); +INSERT IGNORE INTO `provisioning` (`name`, `prettyname`, `moduleid`) VALUES ('vbox', 'Virtual Box', (SELECT `id` FROM `module` WHERE `name` LIKE 'provisioning_vbox')); + -- -------------------------------------------------------- -- -- Inserts for table `provisioningOSinstalltype` --- -INSERT IGNORE INTO `provisioningOSinstalltype` (`provisioningid`, `OSinstalltypeid`) VALUES -(1, 1), -(5, 1), -(6, 1), -(1, 2), -(5, 2), -(6, 2), -(3, 3), -(7, 4), -(8, 5); +-- + +INSERT IGNORE provisioningOSinstalltype (provisioningid, OSinstalltypeid) SELECT provisioning.id, OSinstalltype.id FROM provisioning, OSinstalltype WHERE provisioning.name LIKE '%xcat%' AND OSinstalltype.name = 'partimage'; +INSERT IGNORE provisioningOSinstalltype (provisioningid, OSinstalltypeid) SELECT provisioning.id, OSinstalltype.id FROM provisioning, OSinstalltype WHERE provisioning.name LIKE '%xcat%' AND OSinstalltype.name = 'kickstart'; +INSERT IGNORE provisioningOSinstalltype (provisioningid, OSinstalltypeid) SELECT provisioning.id, OSinstalltype.id FROM provisioning, OSinstalltype WHERE provisioning.name LIKE '%vmware%' AND OSinstalltype.name = 'vmware'; +INSERT IGNORE provisioningOSinstalltype (provisioningid, OSinstalltypeid) SELECT provisioning.id, OSinstalltype.id FROM provisioning, OSinstalltype WHERE provisioning.name LIKE '%esx%' AND OSinstalltype.name = 'vmware'; +INSERT IGNORE provisioningOSinstalltype (provisioningid, OSinstalltypeid) SELECT provisioning.id, OSinstalltype.id FROM provisioning, OSinstalltype WHERE provisioning.name LIKE '%vbox%' AND OSinstalltype.name = 'vbox'; +INSERT IGNORE provisioningOSinstalltype (provisioningid, OSinstalltypeid) SELECT provisioning.id, OSinstalltype.id FROM provisioning, OSinstalltype WHERE provisioning.name LIKE '%lab%' AND OSinstalltype.name = 'none'; + +-- -------------------------------------------------------- + +-- +-- Constraints for table `computer` +-- + +CALL AddConstraintIfNotExists('computer', 'currentimageid', 'image', 'id'); -- -------------------------------------------------------- + +-- +-- Constraints for table `provisioningOSinstalltype` +-- + +CALL AddConstraintIfNotExists('provisioningOSinstalltype', 'provisioningid', 'provisioning', 'id'); +CALL AddConstraintIfNotExists('provisioningOSinstalltype', 'OSinstalltypeid', 'OSinstalltype', 'id'); + +-- -------------------------------------------------------- + +-- +-- Constraints for table `vmhost` +-- + +CALL AddConstraintIfNotExists('vmhost', 'vmprofileid', 'vmprofile', 'id'); + +-- -------------------------------------------------------- + +-- +-- Constraints for table `winKMS` +-- + +CALL AddConstraintIfNotExists('winKMS', 'affiliationid', 'affiliation', 'id'); + +-- -------------------------------------------------------- + +-- +-- Constraints for table `winProductKey` +-- + +CALL AddConstraintIfNotExists('winProductKey', 'affiliationid', 'affiliation', 'id'); + +-- -------------------------------------------------------- + +-- +-- remove table xmlrpcKey +-- + +DROP TABLE IF EXISTS `xmlrpcKey`; + +-- -------------------------------------------------------- + -- -- Remove Procedures -- Modified: incubator/vcl/trunk/mysql/vcl.sql URL: http://svn.apache.org/viewvc/incubator/vcl/trunk/mysql/vcl.sql?rev=1074667&r1=1074666&r2=1074667&view=diff ============================================================================== --- incubator/vcl/trunk/mysql/vcl.sql (original) +++ incubator/vcl/trunk/mysql/vcl.sql Fri Feb 25 18:57:32 2011 @@ -633,7 +633,8 @@ CREATE TABLE IF NOT EXISTS `provisioning CREATE TABLE IF NOT EXISTS `provisioningOSinstalltype` ( `provisioningid` smallint(5) unsigned NOT NULL, `OSinstalltypeid` tinyint(3) unsigned NOT NULL, - PRIMARY KEY (`provisioningid`,`OSinstalltypeid`) + PRIMARY KEY (`provisioningid`,`OSinstalltypeid`), + KEY `OSinstalltypeid` (`OSinstalltypeid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- @@ -1268,7 +1269,7 @@ INSERT INTO `localauth` (`userid`, `pass INSERT INTO `module` (`id`, `name`, `prettyname`, `description`, `perlpackage`) VALUES (1, 'provisioning_xcat_13', 'xCAT 1.3 Provisioning Module', '', 'VCL::Module::Provisioning::xCAT'), -(2, 'provisioning_vmware_gsx', 'VMware Server 1.x Provisioning Module', '', 'VCL::Module::Provisioning::vmware'), +(2, 'provisioning_vmware_1x', 'VMware Server 1.x Provisioning Module', '', 'VCL::Module::Provisioning::vmware'), (3, 'provisioning_lab', 'Computing Lab Provisioning Module', '', 'VCL::Module::Provisioning::Lab'), (4, 'os_windows', 'Windows OS Module', '', 'VCL::Module::OS::Windows'), (5, 'os_linux', 'Linux OS Module', '', 'VCL::Module::OS::Linux'), @@ -1287,7 +1288,8 @@ INSERT INTO `module` (`id`, `name`, `pre (21, 'provisioning_vmware', 'VMware Provisioning Module', '', 'VCL::Module::Provisioning::VMware::VMware'), (22, 'state_image', 'VCL Image State Module', '', 'VCL::image'), (23, 'base_module', 'VCL Base Module', '', 'VCL::Module'), -(24, 'vbox_module', 'Virtual Box Module', '', 'VCL::Module::Provisioning::vbox'); +(24, 'provisioning_vbox', 'Virtual Box Provisioning Module', '', 'VCL::Module::Provisioning::vbox'), +(25, 'os_esxi', 'VMware ESXi OS Module', '', 'VCL::Module::OS::Linux::ESXi'); -- -- Dumping data for table `OS` @@ -1327,7 +1329,8 @@ INSERT INTO `OS` (`id`, `name`, `prettyn (34, 'win7', 'Windows 7 (Bare Metal)', 'windows', 'partimage', 'image', 17), (35, 'vmwarewin7', 'Windows 7 (VMware)', 'windows', 'vmware', 'vmware_images', 17), (36, 'vmwarelinux', 'Generic Linux (VMware)', 'linux', 'vmware', 'vmware_images', 5), -(37, 'vmwarewin2003', 'Windows 2003 Server (VMware)', 'windows', 'vmware', 'vmware_images', 13); +(37, 'vmwarewin2003', 'Windows 2003 Server (VMware)', 'windows', 'vmware', 'vmware_images', 13), +(38, 'esxi4.1', 'VMware ESXi 4.1', 'linux', 'kickstart', 'esxi4.1', 25); -- -- Dumping data for table `OSinstalltype` @@ -1385,17 +1388,12 @@ INSERT INTO `provisioning` (`id`, `name` -- Dumping data for table `provisioningOSinstalltype` -- -INSERT INTO `provisioningOSinstalltype` (`provisioningid`, `OSinstalltypeid`) VALUES -(1, 1), -(5, 1), -(6, 1), -(1, 2), -(5, 2), -(6, 2), -(3, 3), -(7, 4); -INSERT IGNORE INTO `provisioningOSinstalltype` (`provisioningid`, `OSinstalltypeid`) VALUES -((SELECT `id` FROM `provisioning` WHERE `name` LIKE 'vbox' ), (SELECT `id` FROM `OSinstalltype` WHERE `name` LIKE 'vbox'`)); +INSERT IGNORE provisioningOSinstalltype (provisioningid, OSinstalltypeid) SELECT provisioning.id, OSinstalltype.id FROM provisioning, OSinstalltype WHERE provisioning.name LIKE '%xcat%' AND OSinstalltype.name = 'partimage'; +INSERT IGNORE provisioningOSinstalltype (provisioningid, OSinstalltypeid) SELECT provisioning.id, OSinstalltype.id FROM provisioning, OSinstalltype WHERE provisioning.name LIKE '%xcat%' AND OSinstalltype.name = 'kickstart'; +INSERT IGNORE provisioningOSinstalltype (provisioningid, OSinstalltypeid) SELECT provisioning.id, OSinstalltype.id FROM provisioning, OSinstalltype WHERE provisioning.name LIKE '%vmware%' AND OSinstalltype.name = 'vmware'; +INSERT IGNORE provisioningOSinstalltype (provisioningid, OSinstalltypeid) SELECT provisioning.id, OSinstalltype.id FROM provisioning, OSinstalltype WHERE provisioning.name LIKE '%esx%' AND OSinstalltype.name = 'vmware'; +INSERT IGNORE provisioningOSinstalltype (provisioningid, OSinstalltypeid) SELECT provisioning.id, OSinstalltype.id FROM provisioning, OSinstalltype WHERE provisioning.name LIKE '%vbox%' AND OSinstalltype.name = 'vbox'; +INSERT IGNORE provisioningOSinstalltype (provisioningid, OSinstalltypeid) SELECT provisioning.id, OSinstalltype.id FROM provisioning, OSinstalltype WHERE provisioning.name LIKE '%lab%' AND OSinstalltype.name = 'none'; -- -- Dumping data for table `resource` @@ -1653,12 +1651,12 @@ ALTER TABLE `blockWebTime` -- Constraints for table `computer` -- ALTER TABLE `computer` - ADD CONSTRAINT `computer_ibfk_37` FOREIGN KEY (`provisioningid`) REFERENCES `provisioning` (`id`) ON UPDATE CASCADE, ADD CONSTRAINT `computer_ibfk_12` FOREIGN KEY (`ownerid`) REFERENCES `user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `computer_ibfk_30` FOREIGN KEY (`scheduleid`) REFERENCES `schedule` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `computer_ibfk_33` FOREIGN KEY (`stateid`) REFERENCES `state` (`id`) ON UPDATE CASCADE, ADD CONSTRAINT `computer_ibfk_35` FOREIGN KEY (`platformid`) REFERENCES `platform` (`id`) ON UPDATE CASCADE, - ADD CONSTRAINT `computer_ibfk_36` FOREIGN KEY (`currentimageid`) REFERENCES `image` (`id`) ON UPDATE CASCADE; + ADD CONSTRAINT `computer_ibfk_36` FOREIGN KEY (`currentimageid`) REFERENCES `image` (`id`) ON UPDATE CASCADE, + ADD CONSTRAINT `computer_ibfk_37` FOREIGN KEY (`provisioningid`) REFERENCES `provisioning` (`id`) ON UPDATE CASCADE; -- -- Constraints for table `computerloadlog` @@ -1713,9 +1711,9 @@ ALTER TABLE `managementnode` -- Constraints for table `OS` -- ALTER TABLE `OS` - ADD CONSTRAINT `OS_ibfk_4` FOREIGN KEY (`moduleid`) REFERENCES `module` (`id`) ON UPDATE CASCADE, ADD CONSTRAINT `OS_ibfk_2` FOREIGN KEY (`type`) REFERENCES `OStype` (`name`) ON UPDATE CASCADE, - ADD CONSTRAINT `OS_ibfk_3` FOREIGN KEY (`installtype`) REFERENCES `OSinstalltype` (`name`) ON UPDATE CASCADE; + ADD CONSTRAINT `OS_ibfk_3` FOREIGN KEY (`installtype`) REFERENCES `OSinstalltype` (`name`) ON UPDATE CASCADE, + ADD CONSTRAINT `OS_ibfk_4` FOREIGN KEY (`moduleid`) REFERENCES `module` (`id`) ON UPDATE CASCADE; -- -- Constraints for table `privnode` @@ -1729,6 +1727,13 @@ ALTER TABLE `privnode` ALTER TABLE `provisioning` ADD CONSTRAINT `provisioning_ibfk_1` FOREIGN KEY (`moduleid`) REFERENCES `module` (`id`) ON UPDATE CASCADE; +-- +-- Constraints for table `provisioningOSinstalltype` +-- +ALTER TABLE `provisioningOSinstalltype` + ADD CONSTRAINT `provisioningOSinstalltype_ibfk_2` FOREIGN KEY (`OSinstalltypeid`) REFERENCES `OSinstalltype` (`id`) ON UPDATE CASCADE, + ADD CONSTRAINT `provisioningOSinstalltype_ibfk_1` FOREIGN KEY (`provisioningid`) REFERENCES `provisioning` (`id`) ON UPDATE CASCADE; + -- -- Constraints for table `querylog` --