You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@vcl.apache.org by jf...@apache.org on 2011/11/11 17:14:22 UTC
svn commit: r1200928 - /incubator/vcl/trunk/mysql/update-vcl.sql
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');
-- --------------------------------------------------------