Author: arkurth
Date: Wed Mar 21 18:41:12 2012
New Revision: 1303495

URL: http://svn.apache.org/viewvc?rev=1303495&view=rev
Log:
Updated vcl.sql and update-vcl.sql:

VCL-450
Removed vmtypeid from vmprofile.

VCL-499
Added vmprofile.resourcepath column.

VCL-545
Added entries to module and provisioning tables for libvirt provisioning module.


Modified:
    incubator/vcl/trunk/mysql/update-vcl.sql
    incubator/vcl/trunk/mysql/vcl.sql

Modified: incubator/vcl/trunk/mysql/update-vcl.sql
URL: 
http://svn.apache.org/viewvc/incubator/vcl/trunk/mysql/update-vcl.sql?rev=1303495&r1=1303494&r2=1303495&view=diff
==============================================================================
--- incubator/vcl/trunk/mysql/update-vcl.sql (original)
+++ incubator/vcl/trunk/mysql/update-vcl.sql Wed Mar 21 18:41:12 2012
@@ -42,7 +42,7 @@ BEGIN
     AND TABLE_NAME=tableName
   )
   THEN
-    SET @statement_array = CONCAT('ALTER TABLE ', Database(), '.', tableName, 
' ADD COLUMN ', columnName, ' ', columnDefinition);
+    SET @statement_array = CONCAT('ALTER TABLE `', Database(), '`.', 
tableName, ' ADD COLUMN ', columnName, ' ', columnDefinition);
     PREPARE statement_string FROM @statement_array;
     EXECUTE statement_string;
   END IF;
@@ -69,7 +69,10 @@ BEGIN
     AND TABLE_NAME=tableName
   )
   THEN
-    SET @statement_array = CONCAT('ALTER TABLE ', Database(), '.', tableName, 
' DROP COLUMN ', columnName);
+    CALL DropExistingConstraints(tableName, columnName);
+    CALL DropExistingIndices(tableName, columnName);
+  
+    SET @statement_array = CONCAT('ALTER TABLE `', Database(), '`.', 
tableName, ' DROP COLUMN ', columnName);
     PREPARE statement_string FROM @statement_array;
     EXECUTE statement_string;
   END IF;
@@ -78,6 +81,86 @@ END$$
 -- --------------------------------------------------------
 
 /*
+Procedure   : DropExistingConstraints
+Parameters  : tableName, columnName
+Description : Drops all constraints set for an existing column.
+*/
+
+DROP PROCEDURE IF EXISTS `DropExistingConstraints`$$
+CREATE PROCEDURE `DropExistingConstraints`(
+  IN tableName tinytext,
+  IN columnName tinytext
+)
+BEGIN
+  DECLARE done INT DEFAULT 0;
+  DECLARE existing_constraint_name CHAR(16);
+  DECLARE database_name CHAR(16);
+
+  DECLARE select_existing_constraint_names CURSOR FOR
+    SELECT CONSTRAINT_NAME, TABLE_SCHEMA FROM 
information_schema.KEY_COLUMN_USAGE WHERE
+    TABLE_SCHEMA = Database()
+    AND TABLE_NAME = tableName
+    AND COLUMN_NAME = columnName;
+  
+  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
+  OPEN select_existing_constraint_names;
+
+  REPEAT
+    FETCH select_existing_constraint_names INTO existing_constraint_name, 
database_name;
+    -- SELECT existing_constraint_name, database_name;
+    IF NOT done THEN
+      SET @drop_existing_constraint = CONCAT('ALTER TABLE `', Database(), 
'`.', tableName, ' DROP FOREIGN KEY ', existing_constraint_name);
+      PREPARE drop_existing_constraint FROM @drop_existing_constraint;
+      EXECUTE drop_existing_constraint;
+    END IF;
+  UNTIL done END REPEAT;
+
+  CLOSE select_existing_constraint_names;
+END$$
+
+-- --------------------------------------------------------
+
+/*
+Procedure   : DropExistingIndices
+Parameters  : tableName, columnName
+Description : Drops all indices set for an existing column.
+*/
+
+DROP PROCEDURE IF EXISTS `DropExistingIndices`$$
+CREATE PROCEDURE `DropExistingIndices`(
+  IN tableName tinytext,
+  IN columnName tinytext
+)
+BEGIN
+  DECLARE done INT DEFAULT 0;
+  DECLARE existing_index_name CHAR(16);
+  DECLARE database_name CHAR(16);
+  
+  DECLARE select_existing_index_names CURSOR FOR
+    SELECT INDEX_NAME, TABLE_SCHEMA FROM information_schema.STATISTICS WHERE
+    TABLE_SCHEMA = Database()
+    AND TABLE_NAME = tableName
+    AND COLUMN_NAME = columnName;
+
+  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
+  OPEN select_existing_index_names;
+
+  REPEAT
+    FETCH select_existing_index_names INTO existing_index_name, database_name;
+    -- SELECT existing_index_name, database_name;
+    IF NOT done THEN
+      SET @drop_existing_index = CONCAT('ALTER TABLE `', Database(), '`.', 
tableName, ' DROP INDEX ', existing_index_name);
+      PREPARE drop_existing_index FROM @drop_existing_index;
+      EXECUTE drop_existing_index;
+    END IF;
+  UNTIL done END REPEAT;
+
+  CLOSE select_existing_index_names;
+END$$
+
+-- --------------------------------------------------------
+
+/*
 Procedure   : AddIndexIfNotExists
 Parameters  : tableName, columnName
 Description : Adds an index to an existing table if an index for the column 
does
@@ -97,7 +180,7 @@ BEGIN
     AND COLUMN_NAME=columnName
   )
   THEN
-    SET @statement_array = CONCAT('ALTER TABLE ', Database(), '.', tableName, 
' ADD INDEX (', columnName, ')');
+    SET @statement_array = CONCAT('ALTER TABLE `', Database(), '`.', 
tableName, ' ADD INDEX (', columnName, ')');
     PREPARE statement_string FROM @statement_array;
     EXECUTE statement_string;
   END IF;
@@ -136,7 +219,7 @@ BEGIN
   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);
+      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;
@@ -152,7 +235,7 @@ BEGIN
     AND NON_UNIQUE = 0
   )
   THEN
-    SET @add_unique_index = CONCAT('ALTER TABLE ', Database(), '.', tableName, 
' ADD UNIQUE (', columnName, ')');
+    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;
@@ -188,15 +271,15 @@ BEGIN
   THEN
     IF constraintType = 'update'
     THEN
-      SET @statement_array = CONCAT('ALTER TABLE ', Database(), '.', 
tableName, ' ADD FOREIGN KEY (', columnName, ') REFERENCES ', Database(), '.', 
referencedTableName, ' (', referencedColumnName, ') ON UPDATE ', 
constraintAction);
+      SET @statement_array = CONCAT('ALTER TABLE `', Database(), '`.', 
tableName, ' ADD FOREIGN KEY (', columnName, ') REFERENCES `', Database(), 
'`.', referencedTableName, ' (', referencedColumnName, ') ON UPDATE ', 
constraintAction);
     ELSEIF constraintType = 'delete'
     THEN
-      SET @statement_array = CONCAT('ALTER TABLE ', Database(), '.', 
tableName, ' ADD FOREIGN KEY (', columnName, ') REFERENCES ', Database(), '.', 
referencedTableName, ' (', referencedColumnName, ') ON DELETE ', 
constraintAction);
+      SET @statement_array = CONCAT('ALTER TABLE `', Database(), '`.', 
tableName, ' ADD FOREIGN KEY (', columnName, ') REFERENCES `', Database(), 
'`.', referencedTableName, ' (', referencedColumnName, ') ON DELETE ', 
constraintAction);
     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);
+      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, ')');
+      SET @statement_array = CONCAT('ALTER TABLE `', Database(), '`.', 
tableName, ' ADD FOREIGN KEY (', columnName, ') REFERENCES `', Database(), 
'`.', referencedTableName, ' (', referencedColumnName, ')');
     END IF;
     PREPARE statement_string FROM @statement_array;
     EXECUTE statement_string;
@@ -576,15 +659,15 @@ CREATE TABLE IF NOT EXISTS `sitemaintena
 --
 
 CREATE TABLE IF NOT EXISTS `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;
 
 CALL AddColumnIfNotExists('statgraphcache', 'provisioningid', "smallint(5) 
unsigned default NULL");
@@ -646,13 +729,18 @@ CALL AddIndexIfNotExists('vmhost', 'vmpr
 -- 
 
 CALL DropColumnIfExists('vmprofile', 'nasshare');
-CALL AddColumnIfNotExists('vmprofile', 'repositorypath', "varchar(128) default 
NULL AFTER imageid");
-CALL AddColumnIfNotExists('vmprofile', 'virtualdiskpath', "varchar(128) 
default NULL AFTER imageid");
+CALL DropColumnIfExists('vmprofile', 'vmtypeid');
+CALL DropColumnIfExists('vmprofile', 'virtualdiskpath');
+
+CALL AddColumnIfNotExists('vmprofile', 'resourcepath', "varchar(256) default 
NULL AFTER imageid");
+CALL AddColumnIfNotExists('vmprofile', 'repositorypath', "varchar(128) default 
NULL AFTER resourcepath");
 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 AddUniqueIndex('vmprofile', 'profilename');
+
 -- --------------------------------------------------------
 
 --
@@ -711,6 +799,7 @@ INSERT IGNORE INTO `module` (`name`, `pr
 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');
 INSERT IGNORE INTO `module` (`name`, `prettyname`, `description`, 
`perlpackage`) VALUES ('os_osx', 'OSX OS Module', '', 'VCL::Module::OS::OSX');
+INSERT IGNORE INTO `module` (`name`, `prettyname`, `description`, 
`perlpackage`) VALUES ('provisioning_libvirt', 'Libvirt Provisioning Module', 
'', 'VCL::Module::Provisioning::libvirt');
 
 -- --------------------------------------------------------
 
@@ -750,6 +839,7 @@ INSERT IGNORE INTO `OSinstalltype` (`nam
 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'));
+INSERT IGNORE INTO `provisioning` (`name`, `prettyname`, `moduleid`) VALUES 
('libvirt','Libvirt Virtualization API', (SELECT `id` FROM `module` WHERE 
`name` LIKE 'provisioning_libvirt'));
 
 -- --------------------------------------------------------
 

Modified: incubator/vcl/trunk/mysql/vcl.sql
URL: 
http://svn.apache.org/viewvc/incubator/vcl/trunk/mysql/vcl.sql?rev=1303495&r1=1303494&r2=1303495&view=diff
==============================================================================
--- incubator/vcl/trunk/mysql/vcl.sql (original)
+++ incubator/vcl/trunk/mysql/vcl.sql Wed Mar 21 18:41:12 2012
@@ -1200,11 +1200,10 @@ CREATE TABLE IF NOT EXISTS `vmhost` (
 CREATE TABLE IF NOT EXISTS `vmprofile` (
   `id` smallint(5) unsigned NOT NULL auto_increment,
   `profilename` varchar(56) NOT NULL,
-  `vmtypeid` tinyint(3) unsigned NOT NULL,
   `imageid` smallint(5) unsigned NOT NULL,
+  `resourcepath` varchar(256) default NULL,
   `repositorypath` varchar(128) default NULL,
   `datastorepath` varchar(128) NOT NULL,
-  `virtualdiskpath` varchar(128) default NULL,
   `vmpath` varchar(128) default NULL,
   `virtualswitch0` varchar(80) NOT NULL default 'VMnet0',
   `virtualswitch1` varchar(80) NOT NULL default 'VMnet2',
@@ -1216,7 +1215,7 @@ CREATE TABLE IF NOT EXISTS `vmprofile` (
   `vmware_mac_eth0_generated` tinyint(1) NOT NULL default '0',
   `vmware_mac_eth1_generated` tinyint(1) NOT NULL default '0',
   PRIMARY KEY  (`id`),
-  KEY `vmtypeid` (`vmtypeid`,`imageid`),
+  UNIQUE KEY `profilename` (`profilename`),
   KEY `imageid` (`imageid`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
 
@@ -1463,7 +1462,8 @@ INSERT INTO `module` (`id`, `name`, `pre
 (23, 'base_module', 'VCL Base Module', '', 'VCL::Module'),
 (24, 'provisioning_vbox', 'Virtual Box Provisioning Module', '', 
'VCL::Module::Provisioning::vbox'),
 (25, 'os_esxi', 'VMware ESXi OS Module', '', 'VCL::Module::OS::Linux::ESXi'),
-(26, 'os_osx', 'OSX OS Module', '', 'VCL::Module::OS::OSX');
+(26, 'os_osx', 'OSX OS Module', '', 'VCL::Module::OS::OSX'),
+(27, 'provisioning_libvirt', 'Libvirt Provisioning Module', '', 
'VCL::Module::Provisioning::libvirt');
 
 -- 
 -- Dumping data for table `OS`
@@ -1558,7 +1558,8 @@ INSERT INTO `provisioning` (`id`, `name`
 (5, 'xcat_21', 'xCAT 2.1', 11),
 (6, 'xcat_2x', 'xCAT 2.x', 20),
 (7, 'vmware', 'VMware', 21),
-(8, 'vbox', 'Virtual Box', 24);
+(8, 'vbox', 'Virtual Box', 24),
+(9, 'libvirt', 'Libvirt Virtualization API', 27);
 
 --
 -- Dumping data for table `provisioningOSinstalltype`
@@ -1817,13 +1818,13 @@ INSERT INTO `variable` (`id`, `name`, `s
 -- Dumping data for table `vmprofile`
 -- 
 
-INSERT INTO `vmprofile` (`id`, `profilename`, `vmtypeid`, `imageid`, 
`repositorypath`, `datastorepath`, `vmpath`, `virtualswitch0`, 
`virtualswitch1`, `vmdisk`) VALUES
-(1, 'VMware Server 1.x - local storage', 1, 4, NULL, '/var/lib/vmware/Virtual 
Machines', NULL, 'VMnet0', 'VMnet2', 'localdisk'),
-(2, 'VMware Server 2.x - local storage', 1, 4, NULL, '/var/lib/vmware/Virtual 
Machines', NULL, 'Bridged', 'Bridged (2)', 'localdisk'),
-(3, 'VMware Server 2.x - network storage', 1, 4, NULL, 
'/vmfs/volumes/nfs-datastore', '/var/lib/vmware/Virtual Machines', 'Bridged', 
'Bridged (2)', 'networkdisk'),
-(4, 'VMware ESX - local storage', 5, 4, NULL, '/vmfs/volumes/local-datastore', 
NULL, 'Private', 'Public', 'localdisk'),
-(5, 'VMware ESX - network storage', 5, 4, NULL, '/vmfs/volumes/nfs-datastore', 
NULL, 'Private', 'Public', 'networkdisk'),
-(6, 'VMware ESX - local & network storage', 5, 4, NULL, 
'/vmfs/volumes/nfs-datastore1', '/vmfs/volumes/local-datastore', 'Private', 
'Public', 'networkdisk');
+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');
 
 -- 
 -- Dumping data for table `vmtype`
@@ -2099,7 +2100,6 @@ ALTER TABLE `vmhost`
 -- Constraints for table `vmprofile`
 --
 ALTER TABLE `vmprofile`
-  ADD CONSTRAINT `vmprofile_ibfk_2` FOREIGN KEY (`vmtypeid`) REFERENCES 
`vmtype` (`id`) ON UPDATE CASCADE,
   ADD CONSTRAINT `vmprofile_ibfk_1` FOREIGN KEY (`imageid`) REFERENCES `image` 
(`id`) ON UPDATE CASCADE;
 
 --


Reply via email to