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