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');
-- --------------------------------------------------------
