[icinga-checkins] icinga.org: icinga-web/next: SQL Updates for duplicated user_preference data (refs #3868)

git at icinga.org git at icinga.org
Mon May 6 17:15:00 CEST 2013


Module: icinga-web
Branch: next
Commit: 6bd9c13733267a34f20b671d75e32fa82abecda0
URL:    https://git.icinga.org/?p=icinga-web.git;a=commit;h=6bd9c13733267a34f20b671d75e32fa82abecda0

Author: Markus Frosch <markus at lazyfrosch.de>
Date:   Tue Mar 12 10:06:42 2013 +0100

SQL Updates for duplicated user_preference data (refs #3868)

for MySQL and PgSQL

in addition the update scripts should cleanup duplicated data

---

 .../models/generated/BaseNsmUserPreference.php     |    9 ++++
 etc/schema/mysql.sql                               |    2 +-
 etc/schema/pgsql.sql                               |    7 +++
 .../updates/mysql/mysql_v1-8-1_to_v1-8-3.sql       |   44 +++++++++++++++++++
 .../updates/pgsql/pgsql_v1-8-1_to_v1-8-3.sql       |   46 ++++++++++++++++++++
 5 files changed, 107 insertions(+), 1 deletions(-)

diff --git a/app/modules/AppKit/lib/database/models/generated/BaseNsmUserPreference.php b/app/modules/AppKit/lib/database/models/generated/BaseNsmUserPreference.php
index 8a7df63..abb093c 100644
--- a/app/modules/AppKit/lib/database/models/generated/BaseNsmUserPreference.php
+++ b/app/modules/AppKit/lib/database/models/generated/BaseNsmUserPreference.php
@@ -107,6 +107,15 @@ abstract class BaseNsmUserPreference extends Doctrine_Record {
 
         $this->index('upref_search_key_idx', array('fields' => array('upref_key')));
         $this->index('principal_role_id_ix', array('fields' => array('upref_user_id')));
+
+        $this->index('upref_user_key_unique_idx', array(
+                         'fields' => array(
+                             'upref_user_id',
+                             'upref_key',
+                         ),
+                         'type' => 'unique'
+        ));
+
     }
 
     public function setUp() {
diff --git a/etc/schema/mysql.sql b/etc/schema/mysql.sql
index 6ca0fba..8076414 100644
--- a/etc/schema/mysql.sql
+++ b/etc/schema/mysql.sql
@@ -19,7 +19,7 @@ CREATE TABLE nsm_session (session_entry_id INT AUTO_INCREMENT, session_id VARCHA
 CREATE TABLE nsm_target (target_id INT AUTO_INCREMENT, target_name VARCHAR(45) NOT NULL, target_description VARCHAR(100), target_class VARCHAR(80), target_type VARCHAR(45) NOT NULL, PRIMARY KEY(target_id)) ENGINE = INNODB;
 CREATE TABLE nsm_target_value (tv_pt_id INT, tv_key VARCHAR(45), tv_val VARCHAR(45) NOT NULL, PRIMARY KEY(tv_pt_id, tv_key)) ENGINE = INNODB;
 CREATE TABLE nsm_user (user_id INT AUTO_INCREMENT, user_account INT DEFAULT 0 NOT NULL, user_name VARCHAR(127) NOT NULL, user_lastname VARCHAR(40) NOT NULL, user_firstname VARCHAR(40) NOT NULL, user_password VARCHAR(64) NOT NULL, user_salt VARCHAR(64) NOT NULL, user_authsrc VARCHAR(45) DEFAULT 'internal' NOT NULL, user_authid TEXT, user_authkey VARCHAR(64), user_email VARCHAR(254) NOT NULL, user_disabled TINYINT DEFAULT '1' NOT NULL, user_created DATETIME NOT NULL, user_modified DATETIME NOT NULL, UNIQUE INDEX user_name_unique_idx (user_name), INDEX user_search_idx (user_name, user_authsrc, user_disabled, user_authid(127)), PRIMARY KEY(user_id)) ENGINE = INNODB;
-CREATE TABLE nsm_user_preference (upref_id INT AUTO_INCREMENT, upref_user_id INT NOT NULL, upref_val VARCHAR(100), upref_longval LONGTEXT, upref_key VARCHAR(50) NOT NULL, upref_created DATETIME NOT NULL, upref_modified DATETIME NOT NULL, INDEX upref_search_key_idx_idx (upref_key), INDEX principal_role_id_ix_idx (upref_user_id), PRIMARY KEY(upref_id)) ENGINE = INNODB;
+CREATE TABLE nsm_user_preference (upref_id INT AUTO_INCREMENT, upref_user_id INT NOT NULL, upref_val VARCHAR(100), upref_longval LONGTEXT, upref_key VARCHAR(50) NOT NULL, upref_created DATETIME NOT NULL, upref_modified DATETIME NOT NULL, INDEX upref_search_key_idx_idx (upref_key), INDEX principal_role_id_ix_idx (upref_user_id), PRIMARY KEY(upref_id), UNIQUE KEY `upref_user_key_unique_idx` (`upref_user_id`,`upref_key`)) ENGINE = INNODB;
 CREATE TABLE nsm_user_role (usro_user_id INT, usro_role_id INT, INDEX nsm_user_role_ix_idx (usro_role_id), PRIMARY KEY(usro_user_id, usro_role_id)) ENGINE = INNODB;
 ALTER TABLE cronk ADD CONSTRAINT cronk_cronk_user_id_nsm_user_user_id FOREIGN KEY (cronk_user_id) REFERENCES nsm_user(user_id);
 ALTER TABLE cronk_category_cronk ADD CONSTRAINT cronk_category_cronk_ccc_cronk_id_cronk_cronk_id FOREIGN KEY (ccc_cronk_id) REFERENCES cronk(cronk_id);
diff --git a/etc/schema/pgsql.sql b/etc/schema/pgsql.sql
index 42f4b51..4f5cc4b 100644
--- a/etc/schema/pgsql.sql
+++ b/etc/schema/pgsql.sql
@@ -971,6 +971,13 @@ CREATE INDEX upref_search_key_idx ON nsm_user_preference USING btree (upref_key)
 
 
 --
+-- Name: upref_user_key_unique_idx; Type: INDEX; Schema: public; Owner: icinga_web; Tablespace: 
+--
+
+CREATE INDEX upref_user_key_unique_idx ON nsm_user_preference USING btree (upref_user_id, upref_key);
+
+
+--
 -- Name: user_name_unique; Type: INDEX; Schema: public; Owner: icinga_web; Tablespace: 
 --
 
diff --git a/etc/schema/updates/mysql/mysql_v1-8-1_to_v1-8-3.sql b/etc/schema/updates/mysql/mysql_v1-8-1_to_v1-8-3.sql
new file mode 100644
index 0000000..f0968e9
--- /dev/null
+++ b/etc/schema/updates/mysql/mysql_v1-8-1_to_v1-8-3.sql
@@ -0,0 +1,44 @@
+BEGIN;
+SET autocommit = 0;
+
+--
+-- renaming duplicate appstate in user preference data by using the newest row
+--
+DROP TABLE IF EXISTS `nsm_user_preference_trans`;
+
+-- copying current data to temp table
+CREATE TEMPORARY TABLE nsm_user_preference_trans
+SELECT * FROM nsm_user_preference;
+
+-- locking
+LOCK TABLES `nsm_user_preference` WRITE, `nsm_user_preference_trans` WRITE;
+
+-- cleaning the table
+DELETE FROM nsm_user_preference;
+
+-- inserting deduplicated data
+INSERT INTO nsm_user_preference
+SELECT
+    upref_id,
+    upref_user_id,
+    upref_val,
+    upref_longval,
+    upref_key,
+    upref_created,
+    upref_modified
+FROM nsm_user_preference_trans
+GROUP by upref_user_id, upref_key
+HAVING MAX(upref_modified);
+
+-- adding unique key for user_id and key of preference
+ALTER TABLE nsm_user_preference ADD UNIQUE KEY upref_user_key_unique_idx (upref_user_id, upref_key);
+
+-- clean up
+DROP TABLE nsm_user_preference_trans;
+UNLOCK TABLES;
+
+-- update version info
+DELETE FROM nsm_db_version;
+INSERT INTO nsm_db_version VALUES ('1','icinga-web/v1.8.3', NOW(), NOW());
+
+COMMIT;
diff --git a/etc/schema/updates/pgsql/pgsql_v1-8-1_to_v1-8-3.sql b/etc/schema/updates/pgsql/pgsql_v1-8-1_to_v1-8-3.sql
new file mode 100644
index 0000000..e2e7f4c
--- /dev/null
+++ b/etc/schema/updates/pgsql/pgsql_v1-8-1_to_v1-8-3.sql
@@ -0,0 +1,46 @@
+BEGIN;
+
+--
+-- renaming duplicate appstate in user preference data by using the newest row
+--
+DROP TABLE IF EXISTS nsm_user_preference_trans;
+
+-- copying current data to temp table
+CREATE TEMPORARY TABLE nsm_user_preference_trans AS
+SELECT * FROM nsm_user_preference;
+
+-- locking
+LOCK TABLE nsm_user_preference, nsm_user_preference_trans IN ACCESS EXCLUSIVE MODE;
+
+-- cleaning the table
+DELETE FROM nsm_user_preference;
+
+-- inserting deduplicated data
+INSERT INTO nsm_user_preference
+SELECT
+    upref_id,
+    upref_user_id,
+    upref_val,
+    upref_longval,
+    upref_key,
+    upref_created,
+    upref_modified
+FROM nsm_user_preference_trans t
+WHERE upref_modified = (
+    SELECT MAX(upref_modified)
+    FROM nsm_user_preference_trans t2
+    WHERE t.upref_key = t2.upref_key
+      and t.upref_user_id = t2.upref_user_id
+);
+
+-- adding unique key for user_id and key of preference
+CREATE UNIQUE INDEX upref_user_key_unique_idx ON nsm_user_preference USING btree (upref_user_id, upref_key);
+
+-- clean up
+DROP TABLE nsm_user_preference_trans;
+
+-- update version info
+DELETE FROM nsm_db_version;
+INSERT INTO nsm_db_version VALUES ('1','icinga-web/v1.8.3', NOW(), NOW());
+
+COMMIT;





More information about the icinga-checkins mailing list