Author: jfthomps
Date: Tue Mar 31 13:27:32 2015
New Revision: 1670335
URL: http://svn.apache.org/r1670335
Log:
VCL-763 - Add missing constraints to database tables
vcl.sql:
-modified blockRequest.groupid to allow NULL
-modified statgraphcache.affiliationid and statgraphcache.provisioningid to
allow NULL
update-vcl.sql:
-changes for blockRequest table: change groupid and managementnodeid to allow
NULL; added updates to change any entries that had those fields set to 0 to be
NULL
-changes for statgraphcache table: changed fields affiliationid and
provisioningid in create table for statgraphcache to allow NULL; added alter
tables to change those fields to allow NULL for when the table already exists;
added updates to change any entries that had those fields set to 0 to be NULL;
added deletes to remove any entries from statgraphcache where affiliationid or
provisioningid have values that are not in the corresponding tables
-changes for blockRequest constraints: changed foreign keys for groupid and
managementnodeid to set null on delete and cascade on update
-changes for statgraphcache constraints: remove existing constraints, recreate
them with cascade for both detele and update
Modified:
vcl/trunk/mysql/update-vcl.sql
vcl/trunk/mysql/vcl.sql
Modified: vcl/trunk/mysql/update-vcl.sql
URL:
http://svn.apache.org/viewvc/vcl/trunk/mysql/update-vcl.sql?rev=1670335&r1=1670334&r2=1670335&view=diff
==============================================================================
--- vcl/trunk/mysql/update-vcl.sql (original)
+++ vcl/trunk/mysql/update-vcl.sql Tue Mar 31 13:27:32 2015
@@ -784,6 +784,10 @@ CALL AddColumnIfNotExists('blockRequest'
CALL AddColumnIfNotExists('blockRequest', 'comments', "text");
CALL DropColumnIfExists('blockRequest', 'admingroupid');
+ALTER TABLE `blockRequest` CHANGE `groupid` `groupid` smallint(5) unsigned
DEFAULT NULL;
+UPDATE blockRequest SET groupid = NULL WHERE groupid = 0;
+ALTER TABLE `blockRequest` CHANGE `managementnodeid` `managementnodeid`
smallint(5) unsigned DEFAULT NULL;
+UPDATE blockRequest SET managementnodeid = NULL WHERE managementnodeid = 0;
-- --------------------------------------------------------
--
@@ -1267,9 +1271,9 @@ CREATE TABLE IF NOT EXISTS `sitemaintena
CREATE TABLE IF NOT EXISTS `statgraphcache` (
`graphtype` enum('totalres','concurres','concurblade','concurvm') NOT NULL,
`statdate` date NOT NULL,
- `affiliationid` mediumint(8) unsigned NOT NULL,
+ `affiliationid` mediumint(8) unsigned default NULL,
`value` mediumint(8) unsigned NOT NULL,
- `provisioningid` smallint(5) unsigned NOT NULL,
+ `provisioningid` smallint(5) unsigned default NULL,
KEY `graphtype` (`graphtype`),
KEY `statdate` (`statdate`),
KEY `affiliationid` (`affiliationid`),
@@ -1279,6 +1283,13 @@ CREATE TABLE IF NOT EXISTS `statgraphcac
CALL AddColumnIfNotExists('statgraphcache', 'provisioningid', "smallint(5)
unsigned default NULL");
CALL AddIndexIfNotExists('statgraphcache', 'provisioningid');
+ALTER TABLE `statgraphcache` CHANGE `affiliationid` `affiliationid`
mediumint(8) unsigned default NULL;
+ALTER TABLE `statgraphcache` CHANGE `provisioningid` `provisioningid`
smallint(5) unsigned default NULL;
+UPDATE statgraphcache SET affiliationid = NULL WHERE affiliationid = 0;
+UPDATE statgraphcache SET provisioningid = NULL WHERE provisioningid = 0;
+DELETE FROM statgraphcache WHERE affiliationid IS NOT NULL AND affiliationid
NOT IN (SELECT id FROM affiliation);
+DELETE FROM statgraphcache WHERE provisioningid IS NOT NULL AND provisioningid
NOT IN (SELECT id FROM provisioning);
+
-- --------------------------------------------------------
--
@@ -1892,11 +1903,12 @@ CALL AddConstraintIfNotExists('blockComp
CALL DropExistingConstraints('blockRequest', 'imageid');
CALL DropExistingConstraints('blockRequest', 'groupid');
CALL DropExistingConstraints('blockRequest', 'ownerid');
+CALL DropExistingConstraints('blockRequest', 'managementnodeid');
CALL AddConstraintIfNotExists('blockRequest', 'imageid', 'image', 'id',
'update', 'CASCADE');
-CALL AddConstraintIfNotExists('blockRequest', 'groupid', 'usergroup', 'id',
'update', 'CASCADE');
+CALL AddConstraintIfNotExists('blockRequest', 'groupid', 'usergroup', 'id',
'both', 'nullCASCADE');
CALL AddConstraintIfNotExists('blockRequest', 'ownerid', 'user', 'id',
'update', 'CASCADE');
-CALL AddConstraintIfNotExists('blockRequest', 'managementnodeid',
'managementnode', 'id', 'update', 'CASCADE');
+CALL AddConstraintIfNotExists('blockRequest', 'managementnodeid',
'managementnode', 'id', 'both', 'nullCASCADE');
-- --------------------------------------------------------
@@ -2183,8 +2195,10 @@ CALL AddConstraintIfNotExists('shibauth'
-- Constraints for table `statgraphcache`
--
-CALL AddConstraintIfNotExists('statgraphcache', 'affiliationid',
'affiliation', 'id', 'update', 'CASCADE');
-CALL AddConstraintIfNotExists('statgraphcache', 'provisioningid',
'provisioning', 'id', 'update', 'CASCADE');
+CALL DropExistingConstraints('statgraphcache', 'affiliationid');
+CALL DropExistingConstraints('statgraphcache', 'provisioningid');
+CALL AddConstraintIfNotExists('statgraphcache', 'affiliationid',
'affiliation', 'id', 'both', 'CASCADE');
+CALL AddConstraintIfNotExists('statgraphcache', 'provisioningid',
'provisioning', 'id', 'both', 'CASCADE');
-- --------------------------------------------------------
Modified: vcl/trunk/mysql/vcl.sql
URL:
http://svn.apache.org/viewvc/vcl/trunk/mysql/vcl.sql?rev=1670335&r1=1670334&r2=1670335&view=diff
==============================================================================
--- vcl/trunk/mysql/vcl.sql (original)
+++ vcl/trunk/mysql/vcl.sql Tue Mar 31 13:27:32 2015
@@ -84,7 +84,7 @@ CREATE TABLE IF NOT EXISTS `blockRequest
`name` varchar(80) NOT NULL,
`imageid` smallint(5) unsigned NOT NULL,
`numMachines` tinyint(3) unsigned NOT NULL,
- `groupid` smallint(5) unsigned NOT NULL,
+ `groupid` smallint(5) unsigned default NULL,
`repeating` enum('weekly','monthly','list') NOT NULL default 'weekly',
`ownerid` mediumint(8) unsigned NOT NULL,
`managementnodeid` smallint(5) unsigned default NULL,
@@ -1179,9 +1179,9 @@ CREATE TABLE IF NOT EXISTS `state` (
CREATE TABLE IF NOT EXISTS `statgraphcache` (
`graphtype` enum('totalres','concurres','concurblade','concurvm') NOT NULL,
`statdate` date NOT NULL,
- `affiliationid` mediumint(8) unsigned NOT NULL,
+ `affiliationid` mediumint(8) unsigned default NULL,
`value` mediumint(8) unsigned NOT NULL,
- `provisioningid` smallint(5) unsigned NOT NULL,
+ `provisioningid` smallint(5) unsigned default NULL,
KEY `graphtype` (`graphtype`),
KEY `statdate` (`statdate`),
KEY `affiliationid` (`affiliationid`),
