vcl-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jftho...@apache.org
Subject svn commit: r1200928 - /incubator/vcl/trunk/mysql/update-vcl.sql
Date Fri, 11 Nov 2011 16:14:22 GMT
Author: jfthomps
Date: Fri Nov 11 16:14:22 2011
New Revision: 1200928

URL: http://svn.apache.org/viewvc?rev=1200928&view=rev
Log:
VCL-463
add ability to deploy images as servers

modified AddConstraintIfNotExists stored procedure to have 2 more arguments:
-constraintType - one of update, delete, both, or none - what type of constraint(s) to add
-constraintAction - what should be done for the constraint(s), this is directly used in the
ALTER statement and must be one of the values that can be used for mysql constraints (currently
RESTRICT, CASCADE, SET NULL, and NO ACTION)

updated all calls to AddConstraintIfNotExists to include these two new arguments

added constraint to vmhost table for computerid

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

Modified: incubator/vcl/trunk/mysql/update-vcl.sql
URL: http://svn.apache.org/viewvc/incubator/vcl/trunk/mysql/update-vcl.sql?rev=1200928&r1=1200927&r2=1200928&view=diff
==============================================================================
--- incubator/vcl/trunk/mysql/update-vcl.sql (original)
+++ incubator/vcl/trunk/mysql/update-vcl.sql Fri Nov 11 16:14:22 2011
@@ -172,7 +172,9 @@ CREATE PROCEDURE `AddConstraintIfNotExis
   IN tableName tinytext,
   IN columnName tinytext,
   IN referencedTableName tinytext,
-  IN referencedColumnName tinytext
+  IN referencedColumnName tinytext,
+  IN constraintType tinytext,
+  IN constraintAction tinytext
 )
 BEGIN
   IF NOT EXISTS (
@@ -184,7 +186,18 @@ BEGIN
     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');
+    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
+      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);
+    ELSE
+      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;
   END IF;
@@ -721,7 +734,7 @@ INSERT IGNORE userpriv (usergroupid, pri
 -- Constraints for table `computer`
 --
 
-CALL AddConstraintIfNotExists('computer', 'currentimageid', 'image', 'id');
+CALL AddConstraintIfNotExists('computer', 'currentimageid', 'image', 'id', 'update', 'CASCADE');
 
 -- --------------------------------------------------------
 
@@ -729,10 +742,10 @@ CALL AddConstraintIfNotExists('computer'
 -- Constraints for table `connectmethodmap`
 --
 
-CALL AddConstraintIfNotExists('connectmethodmap', 'connectmethodid', 'connectmethod', 'id');
-CALL AddConstraintIfNotExists('connectmethodmap', 'OStypeid', 'OStype', 'id');
-CALL AddConstraintIfNotExists('connectmethodmap', 'OSid', 'OS', 'id');
-CALL AddConstraintIfNotExists('connectmethodmap', 'imagerevisionid', 'imagerevision', 'id');
+CALL AddConstraintIfNotExists('connectmethodmap', 'connectmethodid', 'connectmethod', 'id',
'both', 'CASCADE');
+CALL AddConstraintIfNotExists('connectmethodmap', 'OStypeid', 'OStype', 'id', 'both', 'CASCADE');
+CALL AddConstraintIfNotExists('connectmethodmap', 'OSid', 'OS', 'id', 'both', 'CASCADE');
+CALL AddConstraintIfNotExists('connectmethodmap', 'imagerevisionid', 'imagerevision', 'id',
'both', 'CASCADE');
 
 -- --------------------------------------------------------
 
@@ -740,8 +753,8 @@ CALL AddConstraintIfNotExists('connectme
 -- Constraints for table `provisioningOSinstalltype`
 --
  
-CALL AddConstraintIfNotExists('provisioningOSinstalltype', 'provisioningid', 'provisioning',
'id');
-CALL AddConstraintIfNotExists('provisioningOSinstalltype', 'OSinstalltypeid', 'OSinstalltype',
'id');
+CALL AddConstraintIfNotExists('provisioningOSinstalltype', 'provisioningid', 'provisioning',
'id', 'both', 'CASCADE');
+CALL AddConstraintIfNotExists('provisioningOSinstalltype', 'OSinstalltypeid', 'OSinstalltype',
'id', 'both', 'CASCADE');
 
 -- --------------------------------------------------------
 
@@ -749,8 +762,8 @@ CALL AddConstraintIfNotExists('provision
 -- Constraints for table `reservationaccounts`
 --
 
-CALL AddConstraintIfNotExists('reservationaccounts', 'reservationid', 'reservation', 'id');
-CALL AddConstraintIfNotExists('reservationaccounts', 'userid', 'user', 'id');
+CALL AddConstraintIfNotExists('reservationaccounts', 'reservationid', 'reservation', 'id',
'both', 'CASCADE');
+CALL AddConstraintIfNotExists('reservationaccounts', 'userid', 'user', 'id', 'both', 'CASCADE');
 
 -- --------------------------------------------------------
 
@@ -758,10 +771,10 @@ CALL AddConstraintIfNotExists('reservati
 -- Constraints for table `serverprofile`
 --
 
-CALL AddConstraintIfNotExists('serverprofile', 'ownerid', 'user', 'id');
-CALL AddConstraintIfNotExists('serverprofile', 'admingroupid', 'usergroup', 'id');
-CALL AddConstraintIfNotExists('serverprofile', 'logingroupid', 'usergroup', 'id');
-CALL AddConstraintIfNotExists('serverprofile', 'imageid', 'image', 'id');
+CALL AddConstraintIfNotExists('serverprofile', 'ownerid', 'user', 'id', 'none', '');
+CALL AddConstraintIfNotExists('serverprofile', 'admingroupid', 'usergroup', 'id', 'none',
'');
+CALL AddConstraintIfNotExists('serverprofile', 'logingroupid', 'usergroup', 'id', 'none',
'');
+CALL AddConstraintIfNotExists('serverprofile', 'imageid', 'image', 'id', 'none', '');
 
 -- --------------------------------------------------------
 
@@ -769,9 +782,9 @@ CALL AddConstraintIfNotExists('serverpro
 -- Constraints for table `serverrequest`
 --
 
-CALL AddConstraintIfNotExists('serverrequest', 'requestid', 'request', 'id');
-CALL AddConstraintIfNotExists('serverrequest', 'admingroupid', 'usergroup', 'id');
-CALL AddConstraintIfNotExists('serverrequest', 'logingroupid', 'usergroup', 'id');
+CALL AddConstraintIfNotExists('serverrequest', 'requestid', 'request', 'id', 'delete', 'CASCADE');
+CALL AddConstraintIfNotExists('serverrequest', 'admingroupid', 'usergroup', 'id', 'update',
'CASCADE');
+CALL AddConstraintIfNotExists('serverrequest', 'logingroupid', 'usergroup', 'id', 'update',
'CASCADE');
 
 -- --------------------------------------------------------
 
@@ -779,7 +792,8 @@ CALL AddConstraintIfNotExists('serverreq
 -- Constraints for table `vmhost`
 --
  
-CALL AddConstraintIfNotExists('vmhost', 'vmprofileid', 'vmprofile', 'id');
+CALL AddConstraintIfNotExists('vmhost', 'vmprofileid', 'vmprofile', 'id', 'update', 'CASCADE');
+CALL AddConstraintIfNotExists('vmhost', 'computerid', 'computer', 'id', 'update', 'CASCADE');
 
 -- --------------------------------------------------------
 
@@ -787,7 +801,7 @@ CALL AddConstraintIfNotExists('vmhost', 
 -- Constraints for table `winKMS`
 --
 
-CALL AddConstraintIfNotExists('winKMS', 'affiliationid', 'affiliation', 'id');
+CALL AddConstraintIfNotExists('winKMS', 'affiliationid', 'affiliation', 'id', 'update', 'CASCADE');
  
 -- --------------------------------------------------------
 
@@ -795,7 +809,7 @@ CALL AddConstraintIfNotExists('winKMS', 
 -- Constraints for table `winProductKey`
 --
 
-CALL AddConstraintIfNotExists('winProductKey', 'affiliationid', 'affiliation', 'id');
+CALL AddConstraintIfNotExists('winProductKey', 'affiliationid', 'affiliation', 'id', 'update',
'CASCADE');
  
 -- --------------------------------------------------------
 



Mime
View raw message