Author: arkurth
Date: Wed Aug 25 19:04:35 2010
New Revision: 989301
URL: http://svn.apache.org/viewvc?rev=989301&view=rev
Log:
VCL-357
Updated vcl.sql:
-Added unique keys: affiliation.name, provisioning.name
-Changed key to unique key: module.name
-Removed default image and imagerevision entries except for 'noimage'. Also
removed associated resource and -resourcegroupmembers entries.
Updated update-2.2.sql:
-Added ASF header
-Added procedures: AddColumnIfNotExists, DropColumnIfExist,
AddIndexIfNotExists, AddUniqueIndex, AddConstraintIfNotExists
-Made changes so that the update-2.2.sql can be run without failing if some of
the changes already exist
Modified:
incubator/vcl/trunk/mysql/update-2.2.sql
incubator/vcl/trunk/mysql/vcl.sql
Modified: incubator/vcl/trunk/mysql/update-2.2.sql
URL:
http://svn.apache.org/viewvc/incubator/vcl/trunk/mysql/update-2.2.sql?rev=989301&r1=989300&r2=989301&view=diff
==============================================================================
--- incubator/vcl/trunk/mysql/update-2.2.sql (original)
+++ incubator/vcl/trunk/mysql/update-2.2.sql Wed Aug 25 19:04:35 2010
@@ -1,135 +1,428 @@
- -- 2.1 to 2.2 schema changes
- -- Computer table
-ALTER TABLE `computer` DROP `preferredimageid` ;
+/*
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- Apache VCL version 2.1 to 2.2 database schema changes
+
+-- --------------------------------------------------------
+
+DELIMITER $$
+
+/*
+Procedure : AddColumnIfNotExists
+Parameters : tableName, columnName, columnDefinition
+Description : Adds a column to an existing table if a column with the same name
+ does not already exist.
+*/
+
+DROP PROCEDURE IF EXISTS `AddColumnIfNotExists`$$
+CREATE PROCEDURE `AddColumnIfNotExists`(
+ IN tableName tinytext,
+ IN columnName tinytext,
+ IN columnDefinition text
+)
+BEGIN
+ IF NOT EXISTS (
+ SELECT * FROM information_schema.COLUMNS WHERE
+ TABLE_SCHEMA=Database()
+ AND COLUMN_NAME=columnName
+ AND TABLE_NAME=tableName
+ )
+ THEN
+ SET @statement_array = CONCAT('ALTER TABLE ', Database(), '.', tableName,
' ADD COLUMN ', columnName, ' ', columnDefinition);
+ PREPARE statement_string FROM @statement_array;
+ EXECUTE statement_string;
+ END IF;
+END$$
+
+-- --------------------------------------------------------
+
+/*
+Procedure : DropColumnIfExists
+Parameters : tableName, columnName
+Description : Drops a column from an existing table.
+*/
+
+DROP PROCEDURE IF EXISTS `DropColumnIfExists`$$
+CREATE PROCEDURE `DropColumnIfExists`(
+ IN tableName tinytext,
+ IN columnName tinytext
+)
+BEGIN
+ IF EXISTS (
+ SELECT * FROM information_schema.COLUMNS WHERE
+ TABLE_SCHEMA=Database()
+ AND COLUMN_NAME=columnName
+ AND TABLE_NAME=tableName
+ )
+ THEN
+ SET @statement_array = CONCAT('ALTER TABLE ', Database(), '.', tableName,
' DROP COLUMN ', columnName);
+ PREPARE statement_string FROM @statement_array;
+ EXECUTE statement_string;
+ END IF;
+END$$
+
+-- --------------------------------------------------------
+
+/*
+Procedure : AddIndexIfNotExists
+Parameters : tableName, columnName
+Description : Adds an index to an existing table if an index for the column
does
+ not already exist.
+*/
+
+DROP PROCEDURE IF EXISTS `AddIndexIfNotExists`$$
+CREATE PROCEDURE `AddIndexIfNotExists`(
+ IN tableName tinytext,
+ IN columnName tinytext
+)
+BEGIN
+ IF NOT EXISTS (
+ SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE
+ TABLE_SCHEMA=Database()
+ AND COLUMN_NAME=columnName
+ AND TABLE_NAME=tableName
+ )
+ THEN
+ SET @statement_array = CONCAT('ALTER TABLE ', Database(), '.', tableName,
' ADD INDEX (', columnName, ')');
+ PREPARE statement_string FROM @statement_array;
+ EXECUTE statement_string;
+ END IF;
+END$$
+
+-- --------------------------------------------------------
+
+/*
+Procedure : AddUniqueIndex
+Parameters : tableName, columnName
+Description : Adds a unique index to an existing table if a primary or unique
+ index does not already exist for the column. Any non-unique
+ indices are dropped before the unique index is added.
+*/
+
+DROP PROCEDURE IF EXISTS `AddUniqueIndex`$$
+CREATE PROCEDURE `AddUniqueIndex`(
+ IN tableName tinytext,
+ IN columnName tinytext
+)
+BEGIN
+ DECLARE done INT DEFAULT 0;
+ DECLARE nonunique_index_name CHAR(16);
+
+ DECLARE select_nonunique_index_names CURSOR FOR
+ SELECT INDEX_NAME FROM information_schema.STATISTICS WHERE
+ TABLE_SCHEMA = Database()
+ AND TABLE_NAME = tableName
+ AND COLUMN_NAME = columnName
+ AND NON_UNIQUE = 1;
+
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
+
+ OPEN select_nonunique_index_names;
+
+ REPEAT
+ FETCH select_nonunique_index_names INTO nonunique_index_name;
+ IF NOT done THEN
+ SET @drop_nonunique_index = CONCAT('ALTER TABLE ', Database(), '.',
tableName, ' DROP INDEX ', nonunique_index_name);
+ PREPARE drop_nonunique_index FROM @drop_nonunique_index;
+ EXECUTE drop_nonunique_index;
+ END IF;
+ UNTIL done END REPEAT;
+
+ CLOSE select_nonunique_index_names;
+
+ IF NOT EXISTS (
+ SELECT INDEX_NAME FROM information_schema.STATISTICS WHERE
+ TABLE_SCHEMA = Database()
+ AND TABLE_NAME = tableName
+ AND COLUMN_NAME = columnName
+ AND NON_UNIQUE = 0
+ )
+ THEN
+ SET @add_unique_index = CONCAT('ALTER TABLE ', Database(), '.', tableName,
' ADD UNIQUE (', columnName, ')');
+ PREPARE add_unique_index FROM @add_unique_index;
+ EXECUTE add_unique_index;
+ END IF;
+END$$
+
+-- --------------------------------------------------------
+
+/*
+Procedure : AddConstraintIfNotExists
+Parameters : tableName, columnName, referencedTableName, referencedColumnName
+Description : Adds a foreign key constraint to an existing table if the
+ constraint does not already exist.
+*/
+
+DROP PROCEDURE IF EXISTS `AddConstraintIfNotExists`$$
+CREATE PROCEDURE `AddConstraintIfNotExists`(
+ IN tableName tinytext,
+ IN columnName tinytext,
+ IN referencedTableName tinytext,
+ IN referencedColumnName tinytext
+)
+BEGIN
+ IF NOT EXISTS (
+ SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE
+ TABLE_SCHEMA=Database()
+ AND TABLE_NAME=tableName
+ AND COLUMN_NAME=columnName
+ AND REFERENCED_TABLE_NAME=referencedTableName
+ AND REFERENCED_COLUMN_NAME=referencedColumnName
+ )
+ THEN
+ SET @statement_array = CONCAT('ALTER TABLE ', Database(), '.', tableName,
' ADD FOREIGN KEY (', columnName, ') REFERENCES ', Database(), '.',
referencedTableName, ' (', referencedColumnName, ') ON UPDATE CASCADE');
+ PREPARE statement_string FROM @statement_array;
+ EXECUTE statement_string;
+ END IF;
+END$$
- -- 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;
-
- -- --------------------------------------------------------
+DELIMITER ;
- --
- -- 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`
- --
-
- 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;
-
- -- --------------------------------------------------------
+-- --------------------------------------------------------
+
+--
+-- 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'");
+
+-- --------------------------------------------------------
+
+--
+-- 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`
--
-ALTER TABLE `managementnode` ADD `publicIPconfiguration`
enum('dynamicDHCP','manualDHCP','static') NOT NULL default 'dynamicDHCP';
-ALTER TABLE `managementnode` ADD `publicSubnetMask` varchar(56) default NULL;
-ALTER TABLE `managementnode` ADD `publicDefaultGateway` varchar(56) default
NULL;
-ALTER TABLE `managementnode` ADD `publicDNSserver` varchar(56) default NULL;
-ALTER TABLE `managementnode` ADD `sysadminEmailAddress` varchar(128) default
NULL;
-ALTER TABLE `managementnode` ADD `sharedMailBox` varchar(128) default NULL;
-ALTER TABLE `managementnode` ADD `NOT_STANDALONE` varchar(128) default NULL;
+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');
+
+-- --------------------------------------------------------
--
-- Table structure change for table `request`
--
-ALTER TABLE `request` ADD `checkuser` tinyint(1) unsigned NOT NULL default '1';
+CALL AddColumnIfNotExists('request', 'checkuser', "tinyint(1) unsigned NOT
NULL default '1'");
- -- --------------------------------------------------------
+-- --------------------------------------------------------
--
--- Table structure change for table `vmprofile`
+-- Table structure change for table `vmhost`
--
-ALTER TABLE `vmprofile` ADD `virtualswitch2` varchar(80) NULL default NULL;
-ALTER TABLE `vmprofile` ADD `virtualswitch3` varchar(80) NULL default NULL;
-ALTER TABLE `vmprofile` ADD `vmware_mac_eth0_generated` tinyint(1) NOT NULL
default '0';
-ALTER TABLE `vmprofile` ADD `vmware_mac_eth1_generated` tinyint(1) NOT NULL
default '0';
+ALTER TABLE `vmhost` CHANGE `vmprofileid` `vmprofileid` SMALLINT(5) UNSIGNED
NOT NULL DEFAULT '1';
+CALL AddIndexIfNotExists('vmhost', 'vmprofileid');
- -- --------------------------------------------------------
+-- --------------------------------------------------------
--
--- Inserts for table `module`
+-- Table structure change for table `vmprofile`
--
-INSERT INTO `module` (`id`, `name`, `prettyname`, `description`,
`perlpackage`) VALUES
-(17, 'os_win7', 'Windows 7 OS Module', '',
'VCL::Module::OS::Windows::Version_7::7'),
-(20, 'provisioning_xCAT_2x', 'xCAT 2x provisioning module', '',
'VCL::Module::Provisioning::xCAT2');
+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'");
+
+-- --------------------------------------------------------
+
+--
+-- 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`
+--
+
+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 INTO `affiliation` (`id`, `name`, `dataUpdateText`) VALUES (2,
'Global', '');
+INSERT IGNORE INTO `affiliation` (`name`, `dataUpdateText`) VALUES ('Global',
'');
- -- --------------------------------------------------------
+-- --------------------------------------------------------
--
--- Update change for table `image`
+-- Inserts for table `module`
--
-UPDATE `image` SET `name` = 'vmwarewinxp-base7-v0' WHERE `image`.`id` =7 LIMIT
1 ;
+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_vsphere', 'VMware vSphere
Provisioning Module', '', 'VCL::Module::Provisioning::VMware::VMware');
+INSERT IGNORE INTO `module` (`name`, `prettyname`, `description`,
`perlpackage`) VALUES ('state_image', 'VCL Image State Module', '',
'VCL::image');
- -- --------------------------------------------------------
+-- --------------------------------------------------------
+
+--
+-- 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'));
+
+-- --------------------------------------------------------
--
-- Inserts for table `provisioning`
--
-INSERT INTO `provisioning` (`id`, `name`, `prettyname`, `moduleid`) VALUES
-(7, 'xCAT_2x', 'xCAT 2.x', 20);
+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_vsphere', 'VMware vSphere', (SELECT `id` FROM `module` WHERE `name`
LIKE 'provisioning_vmware_vsphere'));
- -- --------------------------------------------------------
+-- --------------------------------------------------------
---
--- Inserts for table `OS`
---
-
-INSERT INTO `OS` (`id`, `name`, `prettyname`, `type`, `installtype`,
`sourcepath`, `moduleid`) VALUES
-(34, 'win7', 'Windows 7', 'windows', 'partimage', 'image', 17),
-(35, 'vmwarewin7', 'VMware Windows 7', 'windows', 'vmware', 'vmware_images',
17),
-(36, 'vmwarelinux', 'VMware Generic Linux', 'linux', 'vmware',
'vmware_images', 5);
+--
+-- Constraints for table `vmhost`
+--
+
+CALL AddConstraintIfNotExists('vmhost', 'vmprofileid', 'vmprofile', 'id');
- -- --------------------------------------------------------
+-- --------------------------------------------------------
--
-- Constraints for table `winKMS`
--
-ALTER TABLE `winKMS` ADD CONSTRAINT `winKMS_ibfk_1` FOREIGN KEY
(`affiliationid`) REFERENCES `affiliation` (`id`) ON UPDATE CASCADE;
+
+CALL AddConstraintIfNotExists('winKMS', 'affiliationid', 'affiliation', 'id');
- -- --------------------------------------------------------
+-- --------------------------------------------------------
+
--
-- Constraints for table `winProductKey`
--
-ALTER TABLE `winProductKey` ADD CONSTRAINT `winProductKey_ibfk_1` FOREIGN KEY
(`affiliationid`) REFERENCES `affiliation` (`id`) ON UPDATE CASCADE;
+
+CALL AddConstraintIfNotExists('winProductKey', 'affiliationid', 'affiliation',
'id');
+
+-- --------------------------------------------------------
+
+--
+-- Remove Procedures
+--
+
+DROP PROCEDURE IF EXISTS `AddColumnIfNotExists`;
+DROP PROCEDURE IF EXISTS `DropColumnIfExists`;
+DROP PROCEDURE IF EXISTS `AddIndexIfNotExists`;
+DROP PROCEDURE IF EXISTS `AddConstraintIfNotExists`;
Modified: incubator/vcl/trunk/mysql/vcl.sql
URL:
http://svn.apache.org/viewvc/incubator/vcl/trunk/mysql/vcl.sql?rev=989301&r1=989300&r2=989301&view=diff
==============================================================================
--- incubator/vcl/trunk/mysql/vcl.sql (original)
+++ incubator/vcl/trunk/mysql/vcl.sql Wed Aug 25 19:04:35 2010
@@ -48,7 +48,8 @@ CREATE TABLE IF NOT EXISTS `affiliation`
`sitewwwaddress` varchar(56) default NULL,
`helpaddress` varchar(32) default NULL,
`shibonly` tinyint(1) unsigned NOT NULL default '0',
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
@@ -528,7 +529,7 @@ CREATE TABLE IF NOT EXISTS `module` (
`description` varchar(255) NOT NULL,
`perlpackage` varchar(150) NOT NULL,
PRIMARY KEY (`id`),
- KEY `name` (`name`)
+ UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
@@ -618,7 +619,8 @@ CREATE TABLE IF NOT EXISTS `provisioning
`prettyname` varchar(70) NOT NULL,
`moduleid` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`),
- KEY `moduleid` (`moduleid`)
+ KEY `moduleid` (`moduleid`),
+ UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
@@ -1215,30 +1217,14 @@ INSERT INTO `documentation` (`name`, `ti
--
INSERT INTO `image` (`id`, `name`, `prettyname`, `ownerid`, `platformid`,
`OSid`, `imagemetaid`, `minram`, `minprocnumber`, `minprocspeed`, `minnetwork`,
`maxconcurrent`, `reloadtime`, `deleted`, `test`, `lastupdate`, `forcheckout`,
`maxinitialtime`, `project`, `size`) VALUES
-(1, 'winxp-base1-v0', 'No Apps (WinXP)', 1, 1, 7, NULL, 0, 1, 0, 10, NULL, 14,
0, 0, '2007-04-11 16:07:38', 1, 0, 'vcl', 1045),
-(2, 'rhel4-base2-v0', 'Red Hat Enterprise Linux 4.4 Base (KS)', 1, 1, 12,
NULL, 1024, 1, 1024, 100, NULL, 13, 0, 0, '2007-03-02 16:33:33', 1, 0, 'vcl',
0),
-(3, 'rh4image-base3-v0', 'RHEL4 base (image)', 1, 1, 13, NULL, 10, 1, 1024,
100, NULL, 10, 0, 0, '2007-01-24 15:02:07', 1, 0, 'vcl', 1450),
-(4, 'noimage', 'No Image', 1, 1, 2, NULL, 0, 1, 0, 10, NULL, 0, 0, 0, NULL, 1,
0, 'vcl', 1450),
-(5, 'rhfc5-fc5base5-v0', 'Red Hat Fedora Core 5 base (KS)', 1, 1, 15, NULL,
1024, 1, 1024, 100, NULL, 13, 0, 0, '2006-10-02 10:04:24', 1, 0, 'vcl', 1450),
-(6, 'fc5image-FC5baseRHFC56-v0', 'FC5base(RHFC5)', 1, 1, 14, NULL, 64, 1, 500,
10, NULL, 10, 0, 0, '2006-10-02 14:13:26', 1, 0, 'vcl', 0),
-(7, 'vmwarewinxp-base7-v0', 'No Apps (WinXP vmware)', 1, 1, 16, NULL, 512, 1,
1024, 100, NULL, 5, 0, 0, '2007-04-04 09:45:38', 1, 0, 'vcl', 3244),
-(8, 'rh4image-VMwareserverhostRHEL48-v3', 'VMware server host (RHEL4)', 1, 1,
13, NULL, 64, 1, 500, 10, NULL, 10, 0, 0, '2007-11-26 11:23:53', 1, 0, 'vcl',
1450),
-(9, 'esx35-base-v0', 'VMware ESX 3.5 standard server', 1, 1, 20, NULL, 2048,
2, 2000, 100, NULL, 9, 0, 0, '2008-03-24 14:23:54', 1, 0, 'vcl', 1450);
+(4, 'noimage', 'No Image', 1, 1, 2, NULL, 0, 1, 0, 10, NULL, 0, 0, 0, NULL, 1,
0, 'vcl', 1450);
--
-- Dumping data for table `imagerevision`
--
INSERT INTO `imagerevision` (`id`, `imageid`, `revision`, `userid`,
`datecreated`, `deleted`, `production`, `comments`, `imagename`) VALUES
-(1, 1, 0, 1, '2007-03-01 14:46:26', 0, 1, NULL, 'winxp-base1-v0'),
-(2, 2, 0, 1, '2006-09-22 16:33:24', 0, 1, NULL, 'rhel4-base2-v0'),
-(3, 3, 0, 1, '2007-01-24 15:04:11', 0, 1, NULL, 'rh4image-base3-v0'),
-(4, 4, 0, 1, '1980-01-01 00:00:00', 0, 1, NULL, 'noimage'),
-(5, 5, 0, 1, '2006-10-02 10:04:24', 0, 1, NULL, 'rhfc5-fc5base5-v0'),
-(6, 6, 0, 1, '2006-10-02 14:13:26', 0, 1, NULL, 'fc5image-FC5baseRHFC56-v0'),
-(7, 7, 0, 1, '2007-04-03 11:24:12', 0, 1, NULL, 'vmwarewinxp-base7-v0'),
-(8, 8, 0, 1, '2007-01-30 10:50:56', 0, 1, NULL,
'rh4image-VMwareserverhostRHEL48-v0'),
-(9, 9, 0, 1, '2008-03-24 14:23:54', 0, 1, NULL, 'esx35-base-v0');
+(4, 4, 0, 1, '1980-01-01 00:00:00', 0, 1, NULL, 'noimage');
--
-- Dumping data for table `IMtype`
@@ -1262,7 +1248,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 GSX Provisioning Module', '',
'VCL::Module::Provisioning::vmware'),
+(2, 'provisioning_vmware_gsx', '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'),
@@ -1275,7 +1261,7 @@ INSERT INTO `module` (`id`, `name`, `pre
(12, 'os_winxp', 'Windows XP OS Module', '',
'VCL::Module::OS::Windows::Version_5::XP'),
(13, 'os_win2003', 'Windows Server 2003 OS Module', '',
'VCL::Module::OS::Windows::Version_5::2003'),
(14, 'os_linux_ubuntu', 'Ubuntu Linux OS Module', '',
'VCL::Module::OS::Linux::Ubuntu'),
-(15, 'os_unix_lab', 'Unix Lab Module', 'Unix Lab OS support module',
'VCL::Module::OS::Linux::UnixLab'),
+(15, 'os_unix_lab', 'Unix Lab OS Module', 'Unix Lab OS support module',
'VCL::Module::OS::Linux::UnixLab'),
(16, 'os_win2008', 'Windows Server 2008 OS Module', '',
'VCL::Module::OS::Windows::Version_6::2008'),
(17, 'os_win7', 'Windows 7 OS Module', '',
'VCL::Module::OS::Windows::Version_6::7'),
(20, 'provisioning_xCAT_2x', 'xCAT 2.x Provisioning Module', '',
'VCL::Module::Provisioning::xCAT2'),
@@ -1367,7 +1353,7 @@ INSERT INTO `privnode` (`id`, `parent`,
INSERT INTO `provisioning` (`id`, `name`, `prettyname`, `moduleid`) VALUES
(1, 'xcat_13', 'xCAT 1.3', 1),
-(2, 'vmware_server_1', 'VMware Server 1.x', 2),
+(2, 'vmware_server_gsx', 'VMware Server 1.x', 2),
(3, 'lab', 'Computing Lab', 3),
(4, 'vmware_esx', 'VMware ESX', 10),
(5, 'xcat_21', 'xCAT 2.1', 11),
@@ -1379,16 +1365,8 @@ INSERT INTO `provisioning` (`id`, `name`
--
INSERT INTO `resource` (`id`, `resourcetypeid`, `subid`) VALUES
-(1, 13, 1),
-(2, 13, 2),
-(3, 13, 3),
(4, 13, 4),
-(5, 13, 5),
-(6, 13, 6),
-(7, 13, 7),
-(8, 15, 1),
-(9, 13, 8),
-(10, 13, 9);
+(8, 15, 1);
--
-- Dumping data for table `resourcegroup`
@@ -1408,15 +1386,7 @@ INSERT INTO `resourcegroup` (`id`, `name
--
INSERT INTO `resourcegroupmembers` (`resourceid`, `resourcegroupid`) VALUES
-(1, 2),
-(2, 2),
-(3, 2),
-(5, 2),
-(6, 2),
-(7, 10),
-(8, 4),
-(9, 2),
-(10, 2);
+(8, 4);
--
-- Dumping data for table `resourcemap`
