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

[jf...@apache.org]

Author: jfthomps
Date: Mon Feb  2 18:40:42 2015
New Revision: 1656536

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

Versions of vcl.sql before the 2.3 release had a unique key on userpriv.(userid, usergroupid, privnodeid, userprivtypeid) which, due to some fields being allowed to be NULL, didn't prevent duplicates. The vcl.sql file released as part of 2.3 fixed this by removing that unique key and adding separate ones on userpriv.(userid, privnodeid, userprivtypeid) and userpriv.(usergroupid, privnodeid, userprivtypeid). However, this never made its way into the update-vcl.sql file. This commit brings userpriv to the proper table structure.

update-vcl.sql:
-modified Add3ColUniqueIndexIfNotExist: added deleteduplicates argument - passing 1 for it will cause any existing duplicates in the table to be dropped by calling 'ALTER IGNORE TABLE' instead of 'ALTER TABLE'
-modified one existing call to Add3ColUniqueIndexIfNotExist to include extra argument
-added section to modify keys on userpriv table
  -drop existing constraints on userpriv.userid
  -drop existing indices on userpriv.userid
  -add 3 column unique index on userpriv.(userid, privnodeid, userprivtypeid)
  -add 3 column unique index on userpriv.(usergroupid, privnodeid, userprivtypeid)
  -add constraint on userpriv.userid to user.id with cascade on both delete and update

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=1656536&r1=1656535&r2=1656536&view=diff
==============================================================================
--- vcl/trunk/mysql/update-vcl.sql (original)
+++ vcl/trunk/mysql/update-vcl.sql Mon Feb  2 18:40:42 2015
@@ -562,7 +562,10 @@ Procedure   : Add3ColUniqueIndexIfNotExi
 Parameters  : tableName, columnName1, columnName2, columnName2
 Description : Adds a unique index to an existing table if a primary or unique
               index does not already exist for the column. Any non-unique
-              indices are dropped before the unique index is added.
+              indices are dropped before the unique index is added. If 
+              deleteduplicates is passed as 1, any duplicates in the table
+              will be dropped. If any other value is passed and there are
+              duplicates in the table, it will throw an error.
 */
 
 DROP PROCEDURE IF EXISTS `Add3ColUniqueIndexIfNotExist`$$
@@ -570,7 +573,8 @@ CREATE PROCEDURE `Add3ColUniqueIndexIfNo
   IN tableName tinytext,
   IN columnName1 tinytext,
   IN columnName2 tinytext,
-  IN columnName3 tinytext
+  IN columnName3 tinytext,
+  IN deleteduplicates tinyint
 )
 BEGIN
   DECLARE done INT DEFAULT 0;
@@ -651,7 +655,11 @@ BEGIN
     AND i3.COLUMN_NAME IN (columnName1, columnName2, columnName3)
   )
   THEN
-    SET @add_unique_index = CONCAT('ALTER TABLE `', Database(), '`.', tableName, ' ADD UNIQUE (', columnName1, ',', columnName2, ',', columnName3, ')');
+    IF deleteduplicates = 1 THEN
+      SET @add_unique_index = CONCAT('ALTER IGNORE TABLE `', Database(), '`.', tableName, ' ADD UNIQUE (', columnName1, ',', columnName2, ',', columnName3, ')');
+    ELSE
+      SET @add_unique_index = CONCAT('ALTER TABLE `', Database(), '`.', tableName, ' ADD UNIQUE (', columnName1, ',', columnName2, ',', columnName3, ')');
+    END IF;
     PREPARE add_unique_index FROM @add_unique_index;
     EXECUTE add_unique_index;
   END IF;
@@ -791,7 +799,7 @@ CALL AddColumnIfNotExists('changelog', '
 CALL AddIndexIfNotExists('changelog', 'userid');
 CALL AddIndexIfNotExists('changelog', 'reservationid');
 
-CALL Add3ColUniqueIndexIfNotExist('changelog', 'reservationid', 'userid', 'remoteIP');
+CALL Add3ColUniqueIndexIfNotExist('changelog', 'reservationid', 'userid', 'remoteIP', 0);
 
 -- --------------------------------------------------------
 
@@ -1803,6 +1811,18 @@ INSERT IGNORE INTO userprivtype (id, nam
 
 -- --------------------------------------------------------
 
+-- 
+-- UNIQUE KEY changes for userpriv table
+--
+
+CALL DropExistingConstraints('userpriv', 'userid');
+CALL DropExistingIndices('userpriv', 'userid');
+CALL Add3ColUniqueIndexIfNotExist('userpriv', 'userid', 'privnodeid', 'userprivtypeid', 1);
+CALL Add3ColUniqueIndexIfNotExist('userpriv', 'usergroupid', 'privnodeid', 'userprivtypeid', 1);
+CALL AddConstraintIfNotExists('userpriv', 'userid', 'user', 'id', 'both', 'CASCADE');
+
+-- --------------------------------------------------------
+
 -- 
 -- Inserts for table `userpriv`
 --