Author: jfthomps
Date: Thu Nov 29 22:04:02 2012
New Revision: 1415403
URL: http://svn.apache.org/viewvc?rev=1415403&view=rev
Log:
VCL-651
libvirt to vmware entry missing from provisioningOSinstalltype table
VCL-451
Cannot add deleted computers
vcl.sql:
-added datedeleted to computer table
-changed hostname, eth0macaddress, and eth1macaddress keys to be unique keys on
those fields and datedeleted (i.e. hostname key: unique(hostname, datedeleted))
-added entry to provisioningOSinstalltype for libvirt<->vmware
update-vcl.sql:
-added Add2ColUniqueIndexIfNotExist stored procedure
-added datedeleted to computer table
-removed existing calls for changing eth0macaddress and eth1macaddress keys in
computer table
-added calls to Add2ColUniqueIndexIfNotExist to change hostname,
eth0macaddress, and eth1macaddress keys to be unique keys on those fields and
datedeleted (i.e. hostname key: unique(hostname, datedeleted))
-added entry to set datedeleted to NOW() for any deleted computers where
datedeleted is the default value
-added entry to provisioningOSinstalltype for libvirt<->vmware
Modified:
vcl/trunk/mysql/update-vcl.sql
vcl/trunk/mysql/vcl.sql
Modified: vcl/trunk/mysql/update-vcl.sql
URL:
http://svn.apache.org/viewvc/vcl/trunk/mysql/update-vcl.sql?rev=1415403&r1=1415402&r2=1415403&view=diff
==============================================================================
--- vcl/trunk/mysql/update-vcl.sql (original)
+++ vcl/trunk/mysql/update-vcl.sql Thu Nov 29 22:04:02 2012
@@ -473,6 +473,76 @@ BEGIN
END IF;
END$$
+-- --------------------------------------------------------
+
+/*
+Procedure : Add2ColUniqueIndexIfNotExist
+Parameters : tableName, columnName1, columnName2
+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 `Add2ColUniqueIndexIfNotExist`$$
+CREATE PROCEDURE `Add2ColUniqueIndexIfNotExist`(
+ IN tableName tinytext,
+ IN columnName1 tinytext,
+ IN columnName2 tinytext
+)
+BEGIN
+ DECLARE done INT DEFAULT 0;
+ DECLARE nonunique_index_name CHAR(16);
+
+ DECLARE select_index_names CURSOR FOR
+ SELECT i1.INDEX_NAME FROM information_schema.STATISTICS i1
+ LEFT JOIN
+ (
+ SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME
+ FROM information_schema.STATISTICS
+ WHERE TABLE_SCHEMA = Database()
+ AND TABLE_NAME = tableName
+ AND SEQ_IN_INDEX = 2
+ )
+ i2 ON (i1.INDEX_NAME = i2.INDEX_NAME AND i1.SEQ_IN_INDEX = 1 AND
i2.SEQ_IN_INDEX = 2)
+ WHERE i1.TABLE_SCHEMA = Database()
+ AND i1.TABLE_NAME = tableName
+ AND i1.SEQ_IN_INDEX = 1
+ AND i1.COLUMN_NAME = columnName1
+ AND i2.COLUMN_NAME IS NULL;
+
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
+
+ OPEN select_index_names;
+
+ REPEAT
+ FETCH select_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_index_names;
+
+ IF NOT EXISTS (
+ SELECT i1.INDEX_NAME
+ FROM information_schema.STATISTICS i1, information_schema.STATISTICS i2
+ WHERE i1.TABLE_SCHEMA = Database()
+ AND i1.TABLE_NAME = tableName
+ AND i2.TABLE_SCHEMA = Database()
+ AND i2.TABLE_NAME = tableName
+ AND i1.INDEX_NAME = i2.INDEX_NAME
+ AND i1.COLUMN_NAME != i2.COLUMN_NAME
+ AND i1.COLUMN_NAME = columnName1
+ )
+ THEN
+ SET @add_unique_index = CONCAT('ALTER TABLE `', Database(), '`.',
tableName, ' ADD UNIQUE (', columnName1, ',', columnName2, ')');
+ PREPARE add_unique_index FROM @add_unique_index;
+ EXECUTE add_unique_index;
+ END IF;
+END$$
+
/* ============= End of Stored Procedures ===============*/
-- --------------------------------------------------------
@@ -523,12 +593,13 @@ CALL AddColumnIfNotExists('changelog', '
-- Table structure for table `computer`
--
+CALL AddColumnIfNotExists('computer', 'datedeleted', "DATETIME NOT NULL
DEFAULT '0000-00-00 00:00:00' AFTER `deleted`");
+
CALL DropColumnIfExists('computer', 'preferredimageid');
CALL AddIndexIfNotExists('computer', 'imagerevisionid');
-CALL DropExistingIndices('computer', 'eth0macaddress');
-CALL AddIndexIfNotExists('computer', 'eth0macaddress');
-CALL DropExistingIndices('computer', 'eth1macaddress');
-CALL AddIndexIfNotExists('computer', 'eth1macaddress');
+CALL Add2ColUniqueIndexIfNotExist('computer', 'hostname', 'datedeleted');
+CALL Add2ColUniqueIndexIfNotExist('computer', 'eth0macaddress', 'datedeleted');
+CALL Add2ColUniqueIndexIfNotExist('computer', 'eth1macaddress', 'datedeleted');
-- 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'));
@@ -543,6 +614,9 @@ EXECUTE nextimageid_noimage;
ALTER TABLE `computer` CHANGE `RAM` `RAM` MEDIUMINT UNSIGNED NOT NULL DEFAULT
'0';
ALTER TABLE `computer` CHANGE `location` `location` VARCHAR(255) NULL DEFAULT
NULL;
+-- set datedeleted for deleted computers
+UPDATE computer SET datedeleted = NOW() WHERE deleted = 1 AND datedeleted =
'0000-00-00 00:00:00';
+
-- --------------------------------------------------------
--
@@ -1062,6 +1136,7 @@ INSERT IGNORE provisioningOSinstalltype
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';
+INSERT IGNORE provisioningOSinstalltype (provisioningid, OSinstalltypeid)
SELECT provisioning.id, OSinstalltype.id FROM provisioning, OSinstalltype WHERE
provisioning.name LIKE '%libvirt%' AND OSinstalltype.name = 'vmware';
-- --------------------------------------------------------
@@ -1391,3 +1466,4 @@ DROP PROCEDURE IF EXISTS `AddOrRenameCol
DROP PROCEDURE IF EXISTS `DropExistingConstraints`;
DROP PROCEDURE IF EXISTS `DropExistingIndices`;
DROP PROCEDURE IF EXISTS `AddManageMapping`;
+DROP PROCEDURE IF EXISTS `Add2ColUniqueIndexIfNotExist`;
Modified: vcl/trunk/mysql/vcl.sql
URL:
http://svn.apache.org/viewvc/vcl/trunk/mysql/vcl.sql?rev=1415403&r1=1415402&r2=1415403&view=diff
==============================================================================
--- vcl/trunk/mysql/vcl.sql (original)
+++ vcl/trunk/mysql/vcl.sql Thu Nov 29 22:04:02 2012
@@ -212,6 +212,7 @@ CREATE TABLE IF NOT EXISTS `computer` (
`provisioningid` smallint(5) unsigned NOT NULL,
`drivetype` varchar(4) NOT NULL default 'hda',
`deleted` tinyint(1) unsigned NOT NULL default '0',
+ `datedeleted` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
`notes` text,
`lastcheck` datetime default NULL,
`location` varchar(255) default NULL,
@@ -224,9 +225,9 @@ CREATE TABLE IF NOT EXISTS `computer` (
`vmhostid` smallint(5) unsigned default NULL,
`vmtypeid` tinyint(3) unsigned default NULL,
PRIMARY KEY (`id`),
- UNIQUE KEY `hostname` (`hostname`),
- KEY `eth1macaddress` (`eth1macaddress`),
- KEY `eth0macaddress` (`eth0macaddress`),
+ UNIQUE KEY `hostname` (`hostname`, `datedeleted`),
+ UNIQUE KEY `eth1macaddress` (`eth1macaddress`, `datedeleted`),
+ UNIQUE KEY `eth0macaddress` (`eth0macaddress`, `datedeleted`),
KEY `ownerid` (`ownerid`),
KEY `stateid` (`stateid`),
KEY `platformid` (`platformid`),
@@ -1619,6 +1620,7 @@ INSERT IGNORE provisioningOSinstalltype
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';
+INSERT IGNORE provisioningOSinstalltype (provisioningid, OSinstalltypeid)
SELECT provisioning.id, OSinstalltype.id FROM provisioning, OSinstalltype WHERE
provisioning.name LIKE '%libvirt%' AND OSinstalltype.name = 'vmware';
--
-- Dumping data for table `resource`
