Author: arkurth
Date: Tue Apr 10 15:11:24 2012
New Revision: 1311778
URL: http://svn.apache.org/viewvc?rev=1311778&view=rev
Log:
VCL-566
Added imagetype definition to SQL files. Added image.imagetypeid,
vmprofile.repositoryimagetypeid, and vmprofile.datastoreimagetypeid columns.
VCL-545
Added a sample vmprofile entry for KVM to vcl.sql.
VCL-418
Set theme name to 'default' for Local and Global affiliations.
Other
Changed image and imagerevision IDs for noimage to 1.
Modified:
incubator/vcl/trunk/mysql/phpmyadmin.sql
incubator/vcl/trunk/mysql/update-vcl.sql
incubator/vcl/trunk/mysql/vcl.sql
Modified: incubator/vcl/trunk/mysql/phpmyadmin.sql
URL:
http://svn.apache.org/viewvc/incubator/vcl/trunk/mysql/phpmyadmin.sql?rev=1311778&r1=1311777&r2=1311778&view=diff
==============================================================================
--- incubator/vcl/trunk/mysql/phpmyadmin.sql (original)
+++ incubator/vcl/trunk/mysql/phpmyadmin.sql Tue Apr 10 15:11:24 2012
@@ -36,13 +36,15 @@ display" value for a table:
-- Database: `phpmyadmin`
--
+use `phpmyadmin`;
+
-- --------------------------------------------------------
--
-- Dumping data for table `pma_table_info`
--
-INSERT INTO `pma_table_info` (`db_name`, `table_name`, `display_field`) VALUES
+INSERT IGNORE INTO `pma_table_info` (`db_name`, `table_name`, `display_field`)
VALUES
('vcl', 'IMtype', 'name'),
('vcl', 'OS', 'prettyname'),
('vcl', 'OSinstalltype', 'name'),
@@ -56,6 +58,7 @@ INSERT INTO `pma_table_info` (`db_name`,
('vcl', 'computerloadstate', 'loadstatename'),
('vcl', 'image', 'prettyname'),
('vcl', 'imagerevision', 'imagename'),
+('vcl', 'imagetype', 'name'),
('vcl', 'localauth', 'userid'),
('vcl', 'managementnode', 'hostname'),
('vcl', 'module', 'prettyname'),
Modified: incubator/vcl/trunk/mysql/update-vcl.sql
URL:
http://svn.apache.org/viewvc/incubator/vcl/trunk/mysql/update-vcl.sql?rev=1311778&r1=1311777&r2=1311778&view=diff
==============================================================================
--- incubator/vcl/trunk/mysql/update-vcl.sql (original)
+++ incubator/vcl/trunk/mysql/update-vcl.sql Tue Apr 10 15:11:24 2012
@@ -21,6 +21,50 @@
DELIMITER $$
+-- --------------------------------------------------------
+
+/*
+Procedure : AddOrRenameColumn
+Parameters : tableName, oldColumnName, newColumnName, columnDefinition
+Description : If oldColumnName already exists in the table, it is renamed to
+ newColumnName and its definition is updated. If it doesn't exist,
+ a new column is added.
+*/
+
+DROP PROCEDURE IF EXISTS `AddOrRenameColumn`$$
+CREATE PROCEDURE `AddOrRenameColumn`(
+ IN tableName tinytext,
+ IN oldColumnName tinytext,
+ IN newColumnName tinytext,
+ IN columnDefinition text
+)
+BEGIN
+ IF EXISTS (
+ SELECT * FROM information_schema.COLUMNS WHERE
+ TABLE_SCHEMA=Database()
+ AND TABLE_NAME=tableName
+ AND COLUMN_NAME=oldColumnName
+ )
+ THEN
+ SET @statement_array = CONCAT('ALTER TABLE `', Database(), '`.',
tableName, ' CHANGE ', oldColumnName , ' ', newColumnName, ' ',
columnDefinition);
+ ELSEIF EXISTS (
+ SELECT * FROM information_schema.COLUMNS WHERE
+ TABLE_SCHEMA=Database()
+ AND TABLE_NAME=tableName
+ AND COLUMN_NAME=newColumnName
+ )
+ THEN
+ SET @statement_array = CONCAT('ALTER TABLE `', Database(), '`.',
tableName, ' CHANGE ', newColumnName , ' ', newColumnName, ' ',
columnDefinition);
+ ELSE
+ SET @statement_array = CONCAT('ALTER TABLE `', Database(), '`.',
tableName, ' ADD COLUMN ', newColumnName, ' ', columnDefinition);
+ END IF;
+
+ PREPARE statement_string FROM @statement_array;
+ EXECUTE statement_string;
+END$$
+
+-- --------------------------------------------------------
+
/*
Procedure : AddColumnIfNotExists
Parameters : tableName, columnName, columnDefinition
@@ -269,14 +313,11 @@ BEGIN
AND REFERENCED_COLUMN_NAME=referencedColumnName
)
THEN
- IF constraintType = 'update'
- THEN
+ IF constraintType = 'update' THEN
SET @statement_array = CONCAT('ALTER TABLE `', Database(), '`.',
tableName, ' ADD FOREIGN KEY (', columnName, ') REFERENCES `', Database(),
'`.', referencedTableName, ' (', referencedColumnName, ') ON UPDATE ',
constraintAction);
- ELSEIF constraintType = 'delete'
- THEN
+ ELSEIF constraintType = 'delete' THEN
SET @statement_array = CONCAT('ALTER TABLE `', Database(), '`.',
tableName, ' ADD FOREIGN KEY (', columnName, ') REFERENCES `', Database(),
'`.', referencedTableName, ' (', referencedColumnName, ') ON DELETE ',
constraintAction);
- ELSEIF constraintType = 'both'
- THEN
+ ELSEIF constraintType = 'both' THEN
SET @statement_array = CONCAT('ALTER TABLE `', Database(), '`.',
tableName, ' ADD FOREIGN KEY (', columnName, ') REFERENCES `', Database(),
'`.', referencedTableName, ' (', referencedColumnName, ') ON DELETE ',
constraintAction, ' ON UPDATE ', constraintAction);
ELSE
SET @statement_array = CONCAT('ALTER TABLE `', Database(), '`.',
tableName, ' ADD FOREIGN KEY (', columnName, ') REFERENCES `', Database(),
'`.', referencedTableName, ' (', referencedColumnName, ')');
@@ -475,6 +516,8 @@ CREATE TABLE IF NOT EXISTS `connectmetho
-- change minram to mediumint
ALTER TABLE `image` CHANGE `minram` `minram` MEDIUMINT UNSIGNED NOT NULL
DEFAULT '0';
+CALL AddColumnIfNotExists('image', 'imagetypeid', "smallint(5) unsigned NOT
NULL default '1' AFTER ownerid");
+CALL AddIndexIfNotExists('image', 'imagetypeid');
-- --------------------------------------------------------
@@ -487,6 +530,19 @@ CALL AddColumnIfNotExists('imagerevision
-- --------------------------------------------------------
--
+-- Table structure for table `imagetype`
+--
+
+CREATE TABLE IF NOT EXISTS `imagetype` (
+ `id` smallint(5) unsigned NOT NULL auto_increment,
+ `name` varchar(16) NOT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `name` (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+--
-- Table structure for table `loginlog`
--
@@ -572,7 +628,9 @@ CALL AddColumnIfNotExists('reservation',
CREATE TABLE IF NOT EXISTS `reservationaccounts` (
`reservationid` mediumint(8) unsigned NOT NULL,
`userid` mediumint(8) unsigned NOT NULL,
- `password` varchar(50) default NULL
+ `password` varchar(50) default NULL,
+ UNIQUE KEY `reservationid` (`reservationid`,`userid`),
+ KEY `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
@@ -702,7 +760,8 @@ CALL AddColumnIfNotExists('request', 'ch
CREATE TABLE IF NOT EXISTS `usergrouppriv` (
`usergroupid` smallint(5) unsigned NOT NULL,
`userprivtypeid` tinyint(3) unsigned NOT NULL,
- UNIQUE KEY `usergroupid` (`usergroupid`,`userprivtypeid`)
+ UNIQUE KEY `usergroupid` (`usergroupid`,`userprivtypeid`),
+ KEY `userprivtypeid` (`userprivtypeid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
@@ -738,12 +797,17 @@ CALL DropColumnIfExists('vmprofile', 'vi
CALL AddColumnIfNotExists('vmprofile', 'resourcepath', "varchar(256) default
NULL AFTER imageid");
CALL AddColumnIfNotExists('vmprofile', 'repositorypath', "varchar(128) default
NULL AFTER resourcepath");
+CALL AddColumnIfNotExists('vmprofile', 'repositoryimagetypeid', "smallint(5)
unsigned NOT NULL default '1' AFTER repositorypath");
+CALL AddColumnIfNotExists('vmprofile', 'datastoreimagetypeid', "smallint(5)
unsigned NOT NULL default '1' AFTER datastorepath");
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'");
+
+CALL AddOrRenameColumn('vmprofile', 'vmware_mac_eth0_generated',
'eth0generated', "tinyint(1) unsigned NOT NULL default '0'");
+CALL AddOrRenameColumn('vmprofile', 'vmware_mac_eth1_generated',
'eth1generated', "tinyint(1) unsigned NOT NULL default '0'");
CALL AddUniqueIndex('vmprofile', 'profilename');
+CALL AddIndexIfNotExists('vmprofile', 'repositoryimagetypeid');
+CALL AddIndexIfNotExists('vmprofile', 'datastoreimagetypeid');
-- --------------------------------------------------------
@@ -791,6 +855,36 @@ UPDATE `computer` SET `imagerevisionid`
-- --------------------------------------------------------
--
+-- Inserts for table `imagetype`
+--
+
+INSERT IGNORE INTO `imagetype` (`name`) VALUES ('none');
+INSERT IGNORE INTO `imagetype` (`name`) VALUES ('partimage');
+INSERT IGNORE INTO `imagetype` (`name`) VALUES ('partimage-ng');
+INSERT IGNORE INTO `imagetype` (`name`) VALUES ('lab');
+INSERT IGNORE INTO `imagetype` (`name`) VALUES ('kickstart');
+INSERT IGNORE INTO `imagetype` (`name`) VALUES ('vmdk');
+INSERT IGNORE INTO `imagetype` (`name`) VALUES ('qcow2');
+INSERT IGNORE INTO `imagetype` (`name`) VALUES ('vdi');
+
+-- --------------------------------------------------------
+
+--
+-- Inserts for table `image`
+--
+
+UPDATE image SET image.imagetypeid = (SELECT `id` FROM `imagetype` WHERE
`name` = 'none') WHERE image.name = 'noimage';
+UPDATE image, OS SET image.imagetypeid = (SELECT `id` FROM `imagetype` WHERE
`name` = 'vmdk') WHERE image.imagetypeid = 0 AND image.OSid = OS.id AND
OS.installtype LIKE '%vmware%';
+UPDATE image, OS SET image.imagetypeid = (SELECT `id` FROM `imagetype` WHERE
`name` = 'partimage') WHERE image.imagetypeid = 0 AND image.OSid = OS.id AND
OS.installtype LIKE '%partimage%';
+UPDATE image, OS SET image.imagetypeid = (SELECT `id` FROM `imagetype` WHERE
`name` = 'kickstart') WHERE image.imagetypeid = 0 AND image.OSid = OS.id AND
OS.installtype LIKE '%kickstart%';
+UPDATE image, OS SET image.imagetypeid = (SELECT `id` FROM `imagetype` WHERE
`name` = 'vdi') WHERE image.imagetypeid = 0 AND image.OSid = OS.id AND
OS.installtype LIKE '%vbox%';
+UPDATE image, OS, module SET image.imagetypeid = (SELECT `id` FROM `imagetype`
WHERE `name` = 'lab') WHERE image.imagetypeid = 0 AND image.OSid = OS.id AND
OS.moduleid = module.id AND module.perlpackage LIKE '%lab%';
+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;
+
+-- --------------------------------------------------------
+
+--
-- Inserts for table `module`
--
@@ -819,16 +913,16 @@ INSERT IGNORE INTO `OStype` (`name`) VAL
-- 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'));
+INSERT IGNORE INTO `OS` (`name`, `prettyname`, `type`, `installtype`,
`sourcepath`, `moduleid`) VALUES ('win7', 'Windows 7 (Bare Metal)', 'windows',
'partimage', 'image', (SELECT `id` FROM `module` WHERE `name` LIKE 'os_win7'));
+INSERT IGNORE INTO `OS` (`name`, `prettyname`, `type`, `installtype`,
`sourcepath`, `moduleid`) VALUES ('vmwarewin7', 'Windows 7 (VMware)',
'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', 'Generic Linux (VMware)',
'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', 'Windows 2003 Server
(VMware)', '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 (Kickstart)',
'linux', 'kickstart', 'esxi4.1', (SELECT `id` FROM `module` WHERE `name` LIKE
'os_esxi'));
INSERT IGNORE INTO `OS` (`name`, `prettyname`, `type`, `installtype`,
`sourcepath`, `moduleid`) VALUES ('vmwareosx', 'OSX Snow Leopard (VMware)',
'osx', 'vmware', 'vmware_images', (SELECT `id` FROM `module` WHERE `name` LIKE
'os_osx'));
-INSERT IGNORE INTO `OS` (`name`, `prettyname`, `type`, `installtype`,
`sourcepath`, `moduleid`) VALUES ('rhel6', 'Red Hat Enterprise Level 6',
'linux', 'kickstart', 'rhel6', (SELECT `id` FROM `module` WHERE `name` LIKE
'os_linux'));
-INSERT IGNORE INTO `OS` (`name`, `prettyname`, `type`, `installtype`,
`sourcepath`, `moduleid`) VALUES ('rh6image', 'Red Hat 6 image', 'linux',
'partimage', 'image', (SELECT `id` FROM `module` WHERE `name` LIKE 'os_linux'));
-INSERT IGNORE INTO `OS` (`name`, `prettyname`, `type`, `installtype`,
`sourcepath`, `moduleid`) VALUES ('fedora16', 'Fedora 16', 'linux',
'kickstart', 'fedora16', (SELECT `id` FROM `module` WHERE `name` LIKE
'os_linux'));
-INSERT IGNORE INTO `OS` (`name`, `prettyname`, `type`, `installtype`,
`sourcepath`, `moduleid`) VALUES ('fedoraimage', 'Fedora 16 image', 'linux',
'partimage', 'image', (SELECT `id` FROM `module` WHERE `name` LIKE 'os_linux'));
+INSERT IGNORE INTO `OS` (`name`, `prettyname`, `type`, `installtype`,
`sourcepath`, `moduleid`) VALUES ('rhel6', 'Red Hat Enterprise 6 (Kickstart)',
'linux', 'kickstart', 'rhel6', (SELECT `id` FROM `module` WHERE `name` LIKE
'os_linux'));
+INSERT IGNORE INTO `OS` (`name`, `prettyname`, `type`, `installtype`,
`sourcepath`, `moduleid`) VALUES ('rh6image', 'Red Hat Enterprise 6 (Bare
Metal)', 'linux', 'partimage', 'image', (SELECT `id` FROM `module` WHERE `name`
LIKE 'os_linux'));
+INSERT IGNORE INTO `OS` (`name`, `prettyname`, `type`, `installtype`,
`sourcepath`, `moduleid`) VALUES ('fedora16', 'Fedora 16 (Kickstart)', 'linux',
'kickstart', 'fedora16', (SELECT `id` FROM `module` WHERE `name` LIKE
'os_linux'));
+INSERT IGNORE INTO `OS` (`name`, `prettyname`, `type`, `installtype`,
`sourcepath`, `moduleid`) VALUES ('fedoraimage', 'Fedora 16 (Bare Metal)',
'linux', 'partimage', 'image', (SELECT `id` FROM `module` WHERE `name` LIKE
'os_linux'));
-- --------------------------------------------------------
@@ -887,7 +981,7 @@ CALL AddConnectMethodMapIfNotExists('RDP
CALL AddConnectMethodMapIfNotExists('iRAPP RDP', 'osx', 0, 0, 0, 1);
CALL AddConnectMethodMapIfNotExists('ssh', 'linux', 0, 0, 0, 2);
CALL AddConnectMethodMapIfNotExists('ssh', 'unix', 0, 0, 0, 2);
-CALL AddConnectMethodMapIfNotExists('RDP', 'linux', 0, 0, 0, 2);
+CALL AddConnectMethodMapIfNotExists('RDP', 'windows', 0, 0, 0, 2);
CALL AddConnectMethodMapIfNotExists('iRAPP RDP', 'osx', 0, 0, 0, 2);
-- --------------------------------------------------------
@@ -909,6 +1003,25 @@ INSERT IGNORE INTO state (id, name) VALU
-- --------------------------------------------------------
--
+-- Inserts for table `usergroup`
+--
+
+UPDATE IGNORE `usergroup` SET `overlapResCount` = '50' WHERE
`usergroup`.`name` = 'adminUsers' AND `usergroup`.`overlapResCount` = 0;
+
+-- --------------------------------------------------------
+
+--
+-- Inserts for table `usergroupmembers`
+--
+
+INSERT IGNORE INTO `usergroupmembers` (`userid`, `usergroupid`) VALUES
+((SELECT `id` FROM `user` WHERE `unityid` = 'admin' AND `affiliationid` =
(SELECT `id` FROM `affiliation` WHERE `name` = 'Local')), (SELECT `id` FROM
`usergroup` WHERE `name` = 'adminUsers' AND `affiliationid` = (SELECT `id` FROM
`affiliation` WHERE `name` = 'Local'))),
+((SELECT `id` FROM `user` WHERE `unityid` = 'admin' AND `affiliationid` =
(SELECT `id` FROM `affiliation` WHERE `name` = 'Local')), (SELECT `id` FROM
`usergroup` WHERE `name` = 'manageNewImages' AND `affiliationid` = (SELECT `id`
FROM `affiliation` WHERE `name` = 'Local'))),
+((SELECT `id` FROM `user` WHERE `unityid` = 'admin' AND `affiliationid` =
(SELECT `id` FROM `affiliation` WHERE `name` = 'Local')), (SELECT `id` FROM
`usergroup` WHERE `name` = 'Specify End Time' AND `affiliationid` = (SELECT
`id` FROM `affiliation` WHERE `name` = 'Local')));
+
+-- --------------------------------------------------------
+
+--
-- Inserts for table `usergroupprivtype`
--
@@ -955,6 +1068,16 @@ INSERT IGNORE INTO userprivtype (id, nam
INSERT IGNORE userpriv (usergroupid, privnodeid, userprivtypeid) SELECT
usergroup.id, privnode.id, userprivtype.id FROM usergroup, privnode,
userprivtype WHERE usergroup.name = 'adminUsers' AND usergroup.affiliationid =
(SELECT id FROM affiliation WHERE name = 'Local') AND privnode.name = 'admin'
AND privnode.parent = 3 AND userprivtype.name = 'serverCheckOut';
INSERT IGNORE userpriv (usergroupid, privnodeid, userprivtypeid) SELECT
usergroup.id, privnode.id, userprivtype.id FROM usergroup, privnode,
userprivtype WHERE usergroup.name = 'adminUsers' AND usergroup.affiliationid =
(SELECT id FROM affiliation WHERE name = 'Local') AND privnode.name = 'admin'
AND privnode.parent = 3 AND userprivtype.name = 'serverProfileAdmin';
+
+-- --------------------------------------------------------
+
+--
+-- Inserts for table `vmprofile`
+--
+
+UPDATE vmprofile SET vmprofile.repositoryimagetypeid = (SELECT `id` FROM
`imagetype` WHERE `name` = 'none') WHERE vmprofile.repositoryimagetypeid = 0;
+UPDATE vmprofile SET vmprofile.datastoreimagetypeid = (SELECT `id` FROM
`imagetype` WHERE `name` = 'none') WHERE vmprofile.datastoreimagetypeid = 0;
+
-- --------------------------------------------------------
--
@@ -977,6 +1100,14 @@ CALL AddConstraintIfNotExists('connectme
-- --------------------------------------------------------
--
+-- Constraints for table `image`
+--
+
+CALL AddConstraintIfNotExists('image', 'imagetypeid', 'imagetype', 'id',
'update', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
-- Constraints for table `provisioningOSinstalltype`
--
@@ -1016,6 +1147,15 @@ CALL AddConstraintIfNotExists('serverreq
-- --------------------------------------------------------
--
+-- Constraints for table `usergrouppriv`
+--
+
+CALL AddConstraintIfNotExists('usergrouppriv', 'usergroupid', 'usergroup',
'id', 'both', 'CASCADE');
+CALL AddConstraintIfNotExists('usergrouppriv', 'userprivtypeid',
'usergroupprivtype', 'id', 'both', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
-- Constraints for table `vmhost`
--
@@ -1025,6 +1165,15 @@ CALL AddConstraintIfNotExists('vmhost',
-- --------------------------------------------------------
--
+-- Constraints for table `vmprofile`
+--
+
+CALL AddConstraintIfNotExists('vmprofile', 'repositoryimagetypeid',
'imagetype', 'id', 'update', 'CASCADE');
+CALL AddConstraintIfNotExists('vmprofile', 'datastoreimagetypeid',
'imagetype', 'id', 'update', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
-- Constraints for table `winKMS`
--
@@ -1058,3 +1207,6 @@ DROP PROCEDURE IF EXISTS `AddIndexIfNotE
DROP PROCEDURE IF EXISTS `AddUniqueIndex`;
DROP PROCEDURE IF EXISTS `AddConstraintIfNotExists`;
DROP PROCEDURE IF EXISTS `AddConnectMethodMapIfNotExists`;
+DROP PROCEDURE IF EXISTS `AddOrRenameColumn`;
+DROP PROCEDURE IF EXISTS `DropExistingConstraints`;
+DROP PROCEDURE IF EXISTS `DropExistingIndices`;
\ No newline at end of file
Modified: incubator/vcl/trunk/mysql/vcl.sql
URL:
http://svn.apache.org/viewvc/incubator/vcl/trunk/mysql/vcl.sql?rev=1311778&r1=1311777&r2=1311778&view=diff
==============================================================================
--- incubator/vcl/trunk/mysql/vcl.sql (original)
+++ incubator/vcl/trunk/mysql/vcl.sql Tue Apr 10 15:11:24 2012
@@ -196,9 +196,9 @@ CREATE TABLE IF NOT EXISTS `computer` (
`ownerid` mediumint(8) unsigned default '1',
`platformid` tinyint(3) unsigned NOT NULL default '0',
`scheduleid` tinyint(3) unsigned default NULL,
- `currentimageid` smallint(5) unsigned NOT NULL default '4',
- `nextimageid` smallint(5) unsigned NOT NULL default '4',
- `imagerevisionid` mediumint(8) unsigned NOT NULL default '4',
+ `currentimageid` smallint(5) unsigned NOT NULL default '1',
+ `nextimageid` smallint(5) unsigned NOT NULL default '1',
+ `imagerevisionid` mediumint(8) unsigned NOT NULL default '1',
`RAM` mediumint(8) unsigned NOT NULL default '0',
`procnumber` tinyint(5) unsigned NOT NULL default '1',
`procspeed` smallint(5) unsigned NOT NULL default '0',
@@ -374,6 +374,7 @@ CREATE TABLE IF NOT EXISTS `image` (
`name` varchar(70) NOT NULL default '',
`prettyname` varchar(60) NOT NULL default '',
`ownerid` mediumint(8) unsigned default '1',
+ `imagetypeid` smallint(5) unsigned NOT NULL default '1',
`platformid` tinyint(3) unsigned NOT NULL default '0',
`OSid` tinyint(3) unsigned NOT NULL default '0',
`imagemetaid` smallint(5) unsigned default NULL,
@@ -400,7 +401,8 @@ CREATE TABLE IF NOT EXISTS `image` (
KEY `ownerid` (`ownerid`),
KEY `platformid` (`platformid`),
KEY `OSid` (`OSid`),
- KEY `imagemetaid` (`imagemetaid`)
+ KEY `imagemetaid` (`imagemetaid`),
+ KEY `imagetypeid` (`imagetypeid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
@@ -448,6 +450,19 @@ CREATE TABLE IF NOT EXISTS `imagerevisio
-- --------------------------------------------------------
+--
+-- Table structure for table `imagetype`
+--
+
+CREATE TABLE IF NOT EXISTS `imagetype` (
+ `id` smallint(5) unsigned NOT NULL auto_increment,
+ `name` varchar(16) NOT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `name` (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
--
-- Table structure for table `IMtype`
--
@@ -757,7 +772,9 @@ CREATE TABLE IF NOT EXISTS `reservation`
CREATE TABLE IF NOT EXISTS `reservationaccounts` (
`reservationid` mediumint(8) unsigned NOT NULL,
`userid` mediumint(8) unsigned NOT NULL,
- `password` varchar(50) default NULL
+ `password` varchar(50) default NULL,
+ UNIQUE KEY `reservationid` (`reservationid`,`userid`),
+ KEY `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
@@ -983,15 +1000,15 @@ CREATE TABLE IF NOT EXISTS `state` (
--
CREATE TABLE `statgraphcache` (
- `graphtype` enum('totalres','concurres','concurblade','concurvm') NOT
NULL,
- `statdate` date NOT NULL,
- `affiliationid` mediumint(8) unsigned NOT NULL,
- `value` mediumint(8) unsigned NOT NULL,
- `provisioningid` smallint(5) unsigned NOT NULL,
- KEY `graphtype` (`graphtype`),
- KEY `statdate` (`statdate`),
- KEY `affiliationid` (`affiliationid`),
- KEY `provisioningid` (`provisioningid`)
+ `graphtype` enum('totalres','concurres','concurblade','concurvm') NOT NULL,
+ `statdate` date NOT NULL,
+ `affiliationid` mediumint(8) unsigned NOT NULL,
+ `value` mediumint(8) unsigned NOT NULL,
+ `provisioningid` smallint(5) unsigned NOT NULL,
+ KEY `graphtype` (`graphtype`),
+ KEY `statdate` (`statdate`),
+ KEY `affiliationid` (`affiliationid`),
+ KEY `provisioningid` (`provisioningid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
@@ -1112,7 +1129,8 @@ CREATE TABLE IF NOT EXISTS `usergroupmem
CREATE TABLE IF NOT EXISTS `usergrouppriv` (
`usergroupid` smallint(5) unsigned NOT NULL,
`userprivtypeid` tinyint(3) unsigned NOT NULL,
- UNIQUE KEY `usergroupid` (`usergroupid`,`userprivtypeid`)
+ UNIQUE KEY `usergroupid` (`usergroupid`,`userprivtypeid`),
+ KEY `userprivtypeid` (`userprivtypeid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
@@ -1207,7 +1225,9 @@ CREATE TABLE IF NOT EXISTS `vmprofile` (
`imageid` smallint(5) unsigned NOT NULL,
`resourcepath` varchar(256) default NULL,
`repositorypath` varchar(128) default NULL,
+ `repositoryimagetypeid` smallint(5) unsigned NOT NULL default '1',
`datastorepath` varchar(128) NOT NULL,
+ `datastoreimagetypeid` smallint(5) unsigned NOT NULL default '1',
`vmpath` varchar(128) default NULL,
`virtualswitch0` varchar(80) NOT NULL default 'VMnet0',
`virtualswitch1` varchar(80) NOT NULL default 'VMnet2',
@@ -1216,11 +1236,13 @@ CREATE TABLE IF NOT EXISTS `vmprofile` (
`vmdisk` enum('localdisk','networkdisk') NOT NULL default 'localdisk',
`username` varchar(80) NULL default NULL,
`password` varchar(256) NULL default NULL,
- `vmware_mac_eth0_generated` tinyint(1) NOT NULL default '0',
- `vmware_mac_eth1_generated` tinyint(1) NOT NULL default '0',
+ `eth0generated` tinyint(1) unsigned NOT NULL default '0',
+ `eth1generated` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `profilename` (`profilename`),
- KEY `imageid` (`imageid`)
+ KEY `imageid` (`imageid`),
+ KEY `repositoryimagetypeid` (`repositoryimagetypeid`),
+ KEY `datastoreimagetypeid` (`datastoreimagetypeid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
@@ -1294,8 +1316,8 @@ INSERT INTO `adminlevel` (`id`, `name`)
--
INSERT INTO `affiliation` (`id`, `name`, `dataUpdateText`, `theme`) VALUES
-(1, 'Local', ''),
-(2, 'Global', '');
+(1, 'Local', '', 'default'),
+(2, 'Global', '', 'default');
--
-- Dumping data for table `computerloadflow`
@@ -1413,15 +1435,29 @@ INSERT INTO `documentation` (`name`, `ti
-- Dumping data for table `image`
--
-INSERT INTO `image` (`id`, `name`, `prettyname`, `ownerid`, `platformid`,
`OSid`, `imagemetaid`, `minram`, `minprocnumber`, `minprocspeed`, `minnetwork`,
`maxconcurrent`, `reloadtime`, `deleted`, `test`, `lastupdate`, `forcheckout`,
`maxinitialtime`, `project`, `size`) VALUES
-(4, 'noimage', 'No Image', 1, 1, 2, NULL, 0, 1, 0, 10, NULL, 0, 0, 0, NULL, 0,
0, 'vcl', 1450);
+INSERT INTO `image` (`id`, `name`, `prettyname`, `ownerid`, `imagetypeid`,
`platformid`, `OSid`, `imagemetaid`, `minram`, `minprocnumber`, `minprocspeed`,
`minnetwork`, `maxconcurrent`, `reloadtime`, `deleted`, `test`, `lastupdate`,
`forcheckout`, `maxinitialtime`, `project`, `size`) VALUES
+(1, 'noimage', 'No Image', 1, 1, 1, 2, NULL, 0, 1, 0, 10, NULL, 0, 0, 0, NULL,
0, 0, 'vcl', 1450);
--
-- Dumping data for table `imagerevision`
--
INSERT INTO `imagerevision` (`id`, `imageid`, `revision`, `userid`,
`datecreated`, `deleted`, `production`, `comments`, `imagename`) VALUES
-(4, 4, 0, 1, '1980-01-01 00:00:00', 0, 1, NULL, 'noimage');
+(1, 1, 0, 1, '1980-01-01 00:00:00', 0, 1, NULL, 'noimage');
+
+--
+-- Dumping data for table `imagetype`
+--
+
+INSERT INTO `imagetype` (`id`, `name`) VALUES
+(1, 'none'),
+(2, 'partimage'),
+(3, 'partimage-ng'),
+(4, 'lab'),
+(5, 'kickstart'),
+(6, 'vmdk'),
+(7, 'qcow2'),
+(8, 'vdi');
--
-- Dumping data for table `IMtype`
@@ -1510,10 +1546,10 @@ INSERT INTO `OS` (`id`, `name`, `prettyn
(37, 'vmwarewin2003', 'Windows 2003 Server (VMware)', 'windows', 'vmware',
'vmware_images', 13),
(38, 'esxi4.1', 'VMware ESXi 4.1', 'linux', 'kickstart', 'esxi4.1', 25),
(39, 'vmwareosx', 'OSX Snow Leopard (VMware)', 'osx', 'vmware',
'vmware_images', 26),
-(40, 'rhel6', 'Red Hat Enterprise Lev 6', 'linux', 'kickstart', 'rhel6', 5),
-(41, 'rh6image', 'Red Hat Enterprise 6 image', 'linux', 'partimage', 'image',
5),
-(42, 'fedora16', 'Fedora 16 kickstart', 'linux', 'kickstart', 'fedora16', 5),
-(43, 'fedoraimage', 'Fedora 16 image', 'linux', 'partimage', 'image', 5);
+(40, 'rhel6', 'Red Hat Enterprise 6 (Kickstart)', 'linux', 'kickstart',
'rhel6', 5),
+(41, 'rh6image', 'Red Hat Enterprise 6 (Bare Metal)', 'linux', 'partimage',
'image', 5),
+(42, 'fedora16', 'Fedora 16 (Kickstart)', 'linux', 'kickstart', 'fedora16', 5),
+(43, 'fedoraimage', 'Fedora 16 (Bare Metal)', 'linux', 'partimage', 'image',
5);
--
-- Dumping data for table `OSinstalltype`
@@ -1826,13 +1862,12 @@ INSERT INTO `variable` (`id`, `name`, `s
-- Dumping data for table `vmprofile`
--
-INSERT INTO `vmprofile` (`id`, `profilename`, `imageid`, `resourcepath`,
`repositorypath`, `datastorepath`, `vmpath`, `virtualswitch0`,
`virtualswitch1`, `vmdisk`) VALUES
-(1, 'VMware Server 1.x - local storage', 4, NULL, NULL,
'/var/lib/vmware/Virtual Machines', NULL, 'VMnet0', 'VMnet2', 'localdisk'),
-(2, 'VMware Server 2.x - local storage', 4, NULL, NULL,
'/var/lib/vmware/Virtual Machines', NULL, 'Bridged', 'Bridged (2)',
'localdisk'),
-(3, 'VMware Server 2.x - network storage', 4, NULL, NULL,
'/vmfs/volumes/nfs-datastore', '/var/lib/vmware/Virtual Machines', 'Bridged',
'Bridged (2)', 'networkdisk'),
-(4, 'VMware ESX - local storage', 4, NULL, NULL,
'/vmfs/volumes/local-datastore', NULL, 'Private', 'Public', 'localdisk'),
-(5, 'VMware ESX - network storage', 4, NULL, NULL,
'/vmfs/volumes/nfs-datastore', NULL, 'Private', 'Public', 'networkdisk'),
-(6, 'VMware ESX - local & network storage', 4, NULL, NULL,
'/vmfs/volumes/nfs-datastore1', '/vmfs/volumes/local-datastore', 'Private',
'Public', 'networkdisk');
+INSERT INTO `vmprofile` (`profilename`, `imageid`, `resourcepath`,
`repositorypath`, `repositoryimagetypeid`, `datastorepath`,
`datastoreimagetypeid`, `vmpath`, `virtualswitch0`, `virtualswitch1`, `vmdisk`,
`username`, `password`) VALUES
+('VMware ESXi - local storage', (SELECT `id` FROM `image` WHERE `name` =
'noimage'), NULL, NULL, (SELECT `id` FROM `imagetype` WHERE `name` = 'none'),
'datastore1', (SELECT `id` FROM `imagetype` WHERE `name` = 'vmdk'),
'datastore1', 'Private', 'Public', 'localdisk', NULL, NULL),
+('VMware ESXi - network storage', (SELECT `id` FROM `image` WHERE `name` =
'noimage'), NULL, NULL, (SELECT `id` FROM `imagetype` WHERE `name` = 'none'),
'nfs-datastore', (SELECT `id` FROM `imagetype` WHERE `name` = 'vmdk'),
'nfs-datastore', 'Private', 'Public', 'networkdisk', NULL, NULL),
+('VMware ESXi - local & network storage', (SELECT `id` FROM `image` WHERE
`name` = 'noimage'), NULL, NULL, (SELECT `id` FROM `imagetype` WHERE `name` =
'none'), 'nfs-datastore', (SELECT `id` FROM `imagetype` WHERE `name` = 'vmdk'),
'datastore1', 'Private', 'Public', 'networkdisk', NULL, NULL),
+('VMware vCenter', (SELECT `id` FROM `image` WHERE `name` = 'noimage'),
'/DatacenterName/ClusterName/ResourcePoolName', 'repo-datastore', (SELECT `id`
FROM `imagetype` WHERE `name` = 'vmdk'), 'nfs-datastore', (SELECT `id` FROM
`imagetype` WHERE `name` = 'vmdk'), 'datastore1', 'Private', 'Public',
'networkdisk', 'vcenter-admin', 'vcenter-password'),
+('KVM - local storage', (SELECT `id` FROM `image` WHERE `name` = 'noimage'),
NULL, NULL, (SELECT `id` FROM `imagetype` WHERE `name` = 'qcow2'),
'/var/lib/libvirt/images', (SELECT `id` FROM `imagetype` WHERE `name` =
'qcow2'), '/var/lib/libvirt/images', 'br0', 'br1', 'localdisk', NULL, NULL);
--
-- Dumping data for table `vmtype`
@@ -1923,7 +1958,8 @@ ALTER TABLE `continuations`
ALTER TABLE `image`
ADD CONSTRAINT `image_ibfk_1` FOREIGN KEY (`ownerid`) REFERENCES `user`
(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
ADD CONSTRAINT `image_ibfk_6` FOREIGN KEY (`platformid`) REFERENCES
`platform` (`id`) ON UPDATE CASCADE,
- ADD CONSTRAINT `image_ibfk_7` FOREIGN KEY (`OSid`) REFERENCES `OS` (`id`) ON
UPDATE CASCADE;
+ ADD CONSTRAINT `image_ibfk_7` FOREIGN KEY (`OSid`) REFERENCES `OS` (`id`) ON
UPDATE CASCADE,
+ ADD CONSTRAINT `image_ibfk_8` FOREIGN KEY (`imagetypeid`) REFERENCES
`imagetype` (`id`) ON UPDATE CASCADE;
--
-- Constraints for table `imagerevision`
@@ -2088,6 +2124,13 @@ ALTER TABLE `usergroupmembers`
ADD CONSTRAINT `usergroupmembers_ibfk_1` FOREIGN KEY (`userid`) REFERENCES
`user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `usergroupmembers_ibfk_2` FOREIGN KEY (`usergroupid`)
REFERENCES `usergroup` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
+--
+-- Constraints for table `usergrouppriv`
+--
+ALTER TABLE `usergrouppriv`
+ ADD CONSTRAINT `usergrouppriv_ibfk_2` FOREIGN KEY (`userprivtypeid`)
REFERENCES `usergroupprivtype` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ ADD CONSTRAINT `usergrouppriv_ibfk_1` FOREIGN KEY (`usergroupid`) REFERENCES
`usergroup` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
+
--
-- Constraints for table `userpriv`
--
@@ -2108,7 +2151,9 @@ ALTER TABLE `vmhost`
-- Constraints for table `vmprofile`
--
ALTER TABLE `vmprofile`
- ADD CONSTRAINT `vmprofile_ibfk_1` FOREIGN KEY (`imageid`) REFERENCES `image`
(`id`) ON UPDATE CASCADE;
+ ADD CONSTRAINT `vmprofile_ibfk_1` FOREIGN KEY (`imageid`) REFERENCES `image`
(`id`) ON UPDATE CASCADE,
+ ADD CONSTRAINT `vmprofile_ibfk_3` FOREIGN KEY (`repositoryimagetypeid`)
REFERENCES `imagetype` (`id`) ON UPDATE CASCADE,
+ ADD CONSTRAINT `vmprofile_ibfk_4` FOREIGN KEY (`datastoreimagetypeid`)
REFERENCES `imagetype` (`id`) ON UPDATE CASCADE;
--
-- Constraints for table `winKMS`
