[icinga-checkins] icinga.org: icinga-web/next: Oracle: add new index on nsm_user_preferences and delete duplicates #3870

git at icinga.org git at icinga.org
Mon Mar 18 14:26:26 CET 2013


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

Author: Thomas Dressler <tdressler at tdressler.net>
Date:   Sun Mar 17 20:51:09 2013 +0100

Oracle: add new index on nsm_user_preferences and delete duplicates #3870
Refs #3870 #3868

---

 .../updates/oracle/oracle_v1-8-0_to_v1-8-3.sql     |   41 ++++++++++++++++++++
 1 files changed, 41 insertions(+), 0 deletions(-)

diff --git a/etc/schema/updates/oracle/oracle_v1-8-0_to_v1-8-3.sql b/etc/schema/updates/oracle/oracle_v1-8-0_to_v1-8-3.sql
new file mode 100644
index 0000000..c5f829c
--- /dev/null
+++ b/etc/schema/updates/oracle/oracle_v1-8-0_to_v1-8-3.sql
@@ -0,0 +1,41 @@
+/* update script for icinga-web on oracle version v1.8.3 */
+set echo on
+set feedback on
+/* logging */
+spool oracle_v1-8-0_to_v1-8-3.log
+
+/* define index tablespace. change this for your needs */
+define IXTBS='ICINGAWEB_IDX1';
+
+/* 
+drop existing constraint if any 
+ignore ORA-02443 nonexistent constraint warning!
+*/
+alter table nsm_user_preference drop constraint nsm_user_pref_userid_key_uq drop index;
+
+/* from now leave on error */
+whenever sqlerror exit failure
+
+
+/* delete duplicates ifirst if any and than add unique key for preference user_id and key  #3870 */
+delete from nsm_user_preference ud
+where 
+  upref_modified <
+  (select max(upref_modified) from nsm_user_preference ur
+    where ud.upref_user_id=ur.upref_user_id
+      and ud.upref_key=ur.upref_key);
+alter table nsm_user_preference add constraint nsm_user_pref_userid_key_uq UNIQUE (upref_user_id, upref_key)
+  using index tablespace &IXTBS;
+
+
+/* update version info */
+DELETE FROM nsm_db_version;
+INSERT INTO nsm_db_version VALUES ('1','icinga-web/v1.8.3', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
+
+/* commit all changes as single transaction, this will not be reached on error */
+commit;
+
+/*  done */
+spool off;
+exit;
+





More information about the icinga-checkins mailing list