Author: arkurth
Date: Wed Mar 12 20:52:38 2014
New Revision: 1576901
URL: http://svn.apache.org/r1576901
Log:
VCL-753
Added unique key across changelog userid, reservationid, and remoteIP.
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=1576901&r1=1576900&r2=1576901&view=diff
==============================================================================
--- vcl/trunk/mysql/update-vcl.sql (original)
+++ vcl/trunk/mysql/update-vcl.sql Wed Mar 12 20:52:38 2014
@@ -543,6 +543,108 @@ BEGIN
END IF;
END$$
+-- --------------------------------------------------------
+
+/*
+Procedure : Add3ColUniqueIndexIfNotExist
+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.
+*/
+
+DROP PROCEDURE IF EXISTS `Add3ColUniqueIndexIfNotExist`$$
+CREATE PROCEDURE `Add3ColUniqueIndexIfNotExist`(
+ IN tableName tinytext,
+ IN columnName1 tinytext,
+ IN columnName2 tinytext,
+ IN columnName3 tinytext
+)
+BEGIN
+ DECLARE done INT DEFAULT 0;
+ DECLARE nonunique_index_name CHAR(16);
+
+ DECLARE select_index_names CURSOR FOR
+ SELECT
+ i1.INDEX_NAME
+ FROM
+ information_schema.STATISTICS i1,
+ information_schema.STATISTICS i2
+ LEFT JOIN information_schema.STATISTICS i3 ON (
+ i3.TABLE_SCHEMA = i2.TABLE_SCHEMA
+ AND i3.TABLE_NAME = i2.TABLE_NAME
+ AND i3.INDEX_NAME = i2.INDEX_NAME
+ AND i3.SEQ_IN_INDEX = 3
+ )
+ WHERE
+ i1.TABLE_SCHEMA = Database()
+ AND i1.TABLE_NAME = tableName
+ AND i1.SEQ_IN_INDEX = 1
+ AND i1.COLUMN_NAME IN (columnName1, columnName2, columnName3)
+ AND i2.TABLE_SCHEMA = i1.TABLE_SCHEMA
+ AND i2.TABLE_NAME = i1.TABLE_NAME
+ AND i2.INDEX_NAME = i1.INDEX_NAME
+ AND i2.SEQ_IN_INDEX = 2
+ AND i2.COLUMN_NAME IN (columnName1, columnName2, columnName3)
+ AND (i3.COLUMN_NAME IS NULL OR i3.COLUMN_NAME IN (columnName1,
columnName2, columnName3));
+
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
+
+ OPEN select_index_names;
+
+ REPEAT
+ FETCH select_index_names INTO nonunique_index_name;
+ IF NOT done THEN
+ SET @drop_nonunique_index = CONCAT('ALTER TABLE `', Database(), '`.',
tableName, ' DROP INDEX ', nonunique_index_name);
+ PREPARE drop_nonunique_index FROM @drop_nonunique_index;
+ EXECUTE drop_nonunique_index;
+ END IF;
+ UNTIL done END REPEAT;
+
+ CLOSE select_index_names;
+
+ IF NOT EXISTS (
+ SELECT
+ i1.INDEX_NAME,
+ i1.COLUMN_NAME AS c1,
+ i2.COLUMN_NAME AS c2,
+ i3.COLUMN_NAME AS c3
+
+ FROM
+ information_schema.STATISTICS i1,
+ information_schema.STATISTICS i2,
+ information_schema.STATISTICS i3
+
+ WHERE
+ i1.TABLE_SCHEMA = Database()
+ AND i1.TABLE_SCHEMA = i2.TABLE_SCHEMA
+ AND i1.TABLE_SCHEMA = i3.TABLE_SCHEMA
+
+ AND i1.TABLE_NAME = tableName
+ AND i1.TABLE_NAME = i2.TABLE_NAME
+ AND i1.TABLE_NAME = i3.TABLE_NAME
+
+ AND i1.INDEX_NAME = i2.INDEX_NAME
+ AND i1.INDEX_NAME = i3.INDEX_NAME
+
+ AND i1.COLUMN_NAME != i2.COLUMN_NAME
+ AND i1.COLUMN_NAME != i3.COLUMN_NAME
+
+ AND i1.SEQ_IN_INDEX = 1
+ AND i2.SEQ_IN_INDEX = 2
+ AND i3.SEQ_IN_INDEX = 3
+
+ AND i1.COLUMN_NAME IN (columnName1, columnName2, columnName3)
+ AND i2.COLUMN_NAME IN (columnName1, columnName2, columnName3)
+ AND i3.COLUMN_NAME IN (columnName1, columnName2, columnName3)
+ )
+ THEN
+ SET @add_unique_index = CONCAT('ALTER TABLE `', Database(), '`.',
tableName, ' ADD UNIQUE (', columnName1, ',', columnName2, ',', columnName3,
')');
+ PREPARE add_unique_index FROM @add_unique_index;
+ EXECUTE add_unique_index;
+ END IF;
+END$$
+
/* ============= End of Stored Procedures ===============*/
-- --------------------------------------------------------
@@ -591,6 +693,8 @@ CALL AddColumnIfNotExists('changelog', '
CALL AddIndexIfNotExists('changelog', 'userid');
CALL AddIndexIfNotExists('changelog', 'reservationid');
+CALL Add3ColUniqueIndexIfNotExist('changelog', 'reservationid', 'userid',
'remoteIP');
+
-- --------------------------------------------------------
--
Modified: vcl/trunk/mysql/vcl.sql
URL:
http://svn.apache.org/viewvc/vcl/trunk/mysql/vcl.sql?rev=1576901&r1=1576900&r2=1576901&view=diff
==============================================================================
--- vcl/trunk/mysql/vcl.sql (original)
+++ vcl/trunk/mysql/vcl.sql Wed Mar 12 20:52:38 2014
@@ -168,7 +168,8 @@ CREATE TABLE IF NOT EXISTS `changelog` (
PRIMARY KEY (`id`),
KEY `logid` (`logid`),
KEY `userid` (`userid`),
- KEY `reservationid` (`reservationid`)
+ KEY `reservationid` (`reservationid`),
+ UNIQUE KEY reservation_user_remoteIP (userid,reservationid,remoteIP)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
