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`
--
