svn commit: r1070635 - /incubator/vcl/trunk/mysql/update-2.2.1.sql

[fa...@apache.org]

Author: fapeeler
Date: Mon Feb 14 20:11:08 2011
New Revision: 1070635

URL: http://svn.apache.org/viewvc?rev=1070635&view=rev
Log:
VCL-434

Initial commit of sql file to be used to update an existing vcl database.


Added:
    incubator/vcl/trunk/mysql/update-2.2.1.sql

Added: incubator/vcl/trunk/mysql/update-2.2.1.sql
URL: http://svn.apache.org/viewvc/incubator/vcl/trunk/mysql/update-2.2.1.sql?rev=1070635&view=auto
==============================================================================
--- incubator/vcl/trunk/mysql/update-2.2.1.sql (added)
+++ incubator/vcl/trunk/mysql/update-2.2.1.sql Mon Feb 14 20:11:08 2011
@@ -0,0 +1,250 @@
+/*
+  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.2 to 2.2.1 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.STATISTICS WHERE
+    TABLE_SCHEMA=Database()
+    AND TABLE_NAME=tableName
+    AND COLUMN_NAME=columnName
+  )
+  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$$
+
+DELIMITER ;
+
+
+-- --------------------------------------------------------
+
+-- 
+-- Create for table `provisioningOSinstalltype`
+-- 
+
+CREATE TABLE IF NOT EXISTS `provisioningOSinstalltype` (
+  `provisioningid` smallint(5) unsigned NOT NULL,
+  `OSinstalltypeid` tinyint(3) unsigned NOT NULL,
+  PRIMARY KEY  (`provisioningid`,`OSinstalltypeid`)
+) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+-- 
+-- Inserts for table `module`
+-- 
+
+INSERT IGNORE INTO `module` (`name`, `prettyname`, `description`, `perlpackage`) VALUES ('provisioning_vbox', 'Virtual Box Provisioning Module', '', 'VCL::Module::Provisioning::vbox');
+
+-- --------------------------------------------------------
+
+--
+-- Inserts for table `provisioning`
+--
+
+INSERT IGNORE INTO `provisioning` (`name`, `prettyname`, `moduleid`) VALUES ('vbox', 'Virtual Box', (SELECT `id` FROM `module` WHERE `name` LIKE 'provisioning_vbox'));
+
+-- --------------------------------------------------------
+
+--
+-- Inserts for table `OSinstalltype`
+--
+
+INSERT IGNORE INTO `OSinstalltype` (`name`) VALUES ('vbox');
+
+-- --------------------------------------------------------
+-- --------------------------------------------------------
+
+-- 
+-- Inserts for table `provisioningOSinstalltype`
+-- 
+
+INSERT IGNORE INTO `provisioningOSinstalltype` (`provisioningid`, `OSinstalltypeid`) VALUES 
+((SELECT `id` FROM `provisioning` WHERE `name` LIKE 'vbox' ), (SELECT `id` FROM `OSinstalltype` WHERE `name` LIKE 'vbox'`));
+
+-- --------------------------------------------------------
+--
+-- Remove Procedures
+--
+
+DROP PROCEDURE IF EXISTS `AddColumnIfNotExists`;
+DROP PROCEDURE IF EXISTS `DropColumnIfExists`;
+DROP PROCEDURE IF EXISTS `AddIndexIfNotExists`;
+DROP PROCEDURE IF EXISTS `AddConstraintIfNotExists`;