svn commit: r1656038 - /vcl/trunk/mysql/update-vcl.sql

[jfthomps]

Author: jfthomps
Date: Fri Jan 30 15:43:26 2015
New Revision: 1656038

URL: http://svn.apache.org/r1656038
Log:
VCL-764 - Database changes for VCL 2.4

modified queries that update computer.provisioningid and 
statgraphcache.provisioningid: queries were missing constraints that tied 
computer/statgraphcache to provisioning table which was causing all records in 
computer/statgraphcache to get updated

added DELETE queries in the same section that delete entries from 
provisioningOSinstalltype for the same provisioning modules being updated in 
that section

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

Modified: vcl/trunk/mysql/update-vcl.sql
URL: 
http://svn.apache.org/viewvc/vcl/trunk/mysql/update-vcl.sql?rev=1656038&r1=1656037&r2=1656038&view=diff
==============================================================================
--- vcl/trunk/mysql/update-vcl.sql (original)
+++ vcl/trunk/mysql/update-vcl.sql Fri Jan 30 15:43:26 2015
@@ -2259,7 +2259,8 @@ computer.provisioningid = (
   WHERE
   provisioning.moduleid = (SELECT MIN(module.id) FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::VMware::VMware')
 )
-WHERE provisioning.moduleid IN (SELECT module.id FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::vmware');
+WHERE provisioning.moduleid IN (SELECT module.id FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::vmware')
+AND computer.provisioningid = provisioning.id;
 
 UPDATE IGNORE statgraphcache, provisioning SET
 statgraphcache.provisioningid = (
@@ -2271,7 +2272,10 @@ statgraphcache.provisioningid = (
   WHERE
   provisioning.moduleid = (SELECT MIN(module.id) FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::VMware::VMware')
 )
-WHERE provisioning.moduleid IN (SELECT module.id FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::vmware');
+WHERE provisioning.moduleid IN (SELECT module.id FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::vmware')
+AND statgraphcache.provisioningid = provisioning.id;
+
+DELETE FROM provisioningOSinstalltype WHERE 
provisioningOSinstalltype.provisioningid IN (SELECT module.id FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::vmware');
 
 DELETE FROM provisioning WHERE provisioning.moduleid IN (SELECT module.id FROM 
module WHERE module.perlpackage = 'VCL::Module::Provisioning::vmware');
 
@@ -2291,7 +2295,9 @@ computer.provisioningid = (
   WHERE
   provisioning.moduleid = (SELECT MIN(module.id) FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::xCAT')
 )
-WHERE provisioning.moduleid IN (SELECT module.id FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::xCAT2');
+WHERE provisioning.moduleid IN (SELECT module.id FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::xCAT2')
+AND computer.provisioningid = provisioning.id;
+
 UPDATE IGNORE statgraphcache, provisioning SET
 statgraphcache.provisioningid = (
   SELECT DISTINCT
@@ -2302,7 +2308,10 @@ statgraphcache.provisioningid = (
   WHERE
   provisioning.moduleid = (SELECT MIN(module.id) FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::xCAT')
 )
-WHERE provisioning.moduleid IN (SELECT module.id FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::xCAT2');
+WHERE provisioning.moduleid IN (SELECT module.id FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::xCAT2')
+AND statgraphcache.provisioningid = provisioning.id;
+
+DELETE FROM provisioningOSinstalltype WHERE 
provisioningOSinstalltype.provisioningid IN (SELECT module.id FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::xCAT2');
 
 DELETE FROM provisioning WHERE provisioning.moduleid IN (SELECT module.id FROM 
module WHERE module.perlpackage = 'VCL::Module::Provisioning::xCAT2');
 
@@ -2322,7 +2331,9 @@ computer.provisioningid = (
   WHERE
   provisioning.moduleid = (SELECT MIN(module.id) FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::xCAT')
 )
-WHERE provisioning.moduleid IN (SELECT module.id FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::xCAT21');
+WHERE provisioning.moduleid IN (SELECT module.id FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::xCAT21')
+AND computer.provisioningid = provisioning.id;
+
 UPDATE IGNORE statgraphcache, provisioning SET
 statgraphcache.provisioningid = (
   SELECT DISTINCT
@@ -2333,7 +2344,10 @@ statgraphcache.provisioningid = (
   WHERE
   provisioning.moduleid = (SELECT MIN(module.id) FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::xCAT')
 )
-WHERE provisioning.moduleid IN (SELECT module.id FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::xCAT21');
+WHERE provisioning.moduleid IN (SELECT module.id FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::xCAT21')
+AND statgraphcache.provisioningid = provisioning.id;
+
+DELETE FROM provisioningOSinstalltype WHERE 
provisioningOSinstalltype.provisioningid IN (SELECT module.id FROM module WHERE 
module.perlpackage = 'VCL::Module::Provisioning::xCAT21');
 
 DELETE FROM provisioning WHERE provisioning.moduleid IN (SELECT module.id FROM 
module WHERE module.perlpackage = 'VCL::Module::Provisioning::xCAT21');