[icinga-checkins] icinga.org: icinga-core/next: idoutils: add index for BP Addon #2274

git at icinga.org git at icinga.org
Wed Feb 22 10:46:50 CET 2012


Module: icinga-core
Branch: next
Commit: 69e8bd7c1a5000b70965690813ed89a431ee8a25
URL:    https://git.icinga.org/?p=icinga-core.git;a=commit;h=69e8bd7c1a5000b70965690813ed89a431ee8a25

Author: Thomas Dressler <tdressler at tdressler.net>
Date:   Sun Jan 29 17:15:42 2012 +0100

idoutils: add index for BP Addon #2274
refs #2274

---

 module/idoutils/db/mysql/mysql.sql                 |    3 +
 .../db/mysql/upgrade/mysql-upgrade-1.7.0.sql       |   20 +++++++
 .../db/oracle/create_icinga_objects_oracle.sql     |    8 ++-
 .../db/oracle/upgrade/oracle-upgrade-1.7.0.sql     |   56 ++++++++++++++++++++
 module/idoutils/db/pgsql/pgsql.sql                 |    2 +
 .../db/pgsql/upgrade/pgsql-upgrade-1.7.0.sql       |   20 +++++++
 6 files changed, 107 insertions(+), 2 deletions(-)

diff --git a/module/idoutils/db/mysql/mysql.sql b/module/idoutils/db/mysql/mysql.sql
index d33b371..bc93d60 100644
--- a/module/idoutils/db/mysql/mysql.sql
+++ b/module/idoutils/db/mysql/mysql.sql
@@ -1563,6 +1563,9 @@ CREATE INDEX loge_inst_id_time_idx on icinga_logentries (instance_id ASC, logent
 
 -- statehistory
 CREATE INDEX statehist_i_id_o_id_s_ty_s_ti on icinga_statehistory(instance_id, object_id, state_type, state_time);
+--#2274
+create index statehist_state_idx on icinga_statehistory(objectid,state);
+
 
 -- SLA statehistory
 CREATE INDEX slahist_i_id_o_id_s_ti_s_s_ti_e on icinga_slahistory(instance_id,object_id,start_time,end_time);
diff --git a/module/idoutils/db/mysql/upgrade/mysql-upgrade-1.7.0.sql b/module/idoutils/db/mysql/upgrade/mysql-upgrade-1.7.0.sql
new file mode 100644
index 0000000..996a86e
--- /dev/null
+++ b/module/idoutils/db/mysql/upgrade/mysql-upgrade-1.7.0.sql
@@ -0,0 +1,20 @@
+-- -----------------------------------------
+-- upgrade path for Icinga IDOUtils 1.7.0
+--
+-- -----------------------------------------
+-- Copyright (c) 2011 Icinga Development Team (http://www.icinga.org)
+--
+-- Please check http://docs.icinga.org for upgrading information!
+-- -----------------------------------------
+
+-- -----------------------------------------
+--#2274
+-- -----------------------------------------
+create index statehist_state_idx on icinga_statehistory(objectid,state);
+
+-- -----------------------------------------
+-- update dbversion
+-- -----------------------------------------
+
+INSERT INTO icinga_dbversion (name, version) VALUES ('idoutils', '1.6.0') ON DUPLICATE KEY UPDATE version='1.6.0';
+
diff --git a/module/idoutils/db/oracle/create_icinga_objects_oracle.sql b/module/idoutils/db/oracle/create_icinga_objects_oracle.sql
index a1b2465..a433a06 100644
--- a/module/idoutils/db/oracle/create_icinga_objects_oracle.sql
+++ b/module/idoutils/db/oracle/create_icinga_objects_oracle.sql
@@ -8,7 +8,7 @@
 --
 -- initial version: 2008-02-20 David Schmidt
 --                  2011-01-17 Michael Friedrich <michael.friedrich(at)univie.ac.at>
--- current version: 2011-10-27 Thomas Dressler
+-- current version: 2012-01-29 Thomas Dressler
 -- -- --------------------------------------------------------
 */
 -- -----------------------------------------
@@ -1967,7 +1967,11 @@ CREATE INDEX objects_inst_id_idx ON objects(instance_id) tablespace &&IDXTBS;
 CREATE INDEX loge_time_idx on logentries(logentry_time) tablespace &&IDXTBS;
 
 -- statehistory
-CREATE INDEX statehist_i_id_o_id_s_ty_s_ti on statehistory(instance_id, object_id, state_type, state_time) tablespace &&IDXTBS;
+CREATE INDEX statehist_time_idx on statehistory(instance_id, object_id, state_type, state_time) tablespace &&IDXTBS;
+-- #2274
+create index statehistory_state_idx on statehistory(object_id,state)
+tablespace &&IDXTBS;
+
 
 -- slahistory
 CREATE INDEX slahist_idx on slahistory(instance_id,object_id,start_time,end_time) tablespace &&IDXTBS;
diff --git a/module/idoutils/db/oracle/upgrade/oracle-upgrade-1.7.0.sql b/module/idoutils/db/oracle/upgrade/oracle-upgrade-1.7.0.sql
new file mode 100644
index 0000000..7879615
--- /dev/null
+++ b/module/idoutils/db/oracle/upgrade/oracle-upgrade-1.7.0.sql
@@ -0,0 +1,56 @@
+-- -----------------------------------------
+-- upgrade path for Icinga IDOUtils 1.7.0
+--
+-- run it as icinga database user whithin  current directory
+-- sqlplus icinga@<instance> @ oracle-upgrade.1-6.0.sql
+-- -----------------------------------------
+-- Copyright (c) 2010-2011 Icinga Development Team (http://www.icinga.org)
+--
+-- Please check http://docs.icinga.org for upgrading information!
+-- -----------------------------------------
+set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER SQL>"
+set pagesize 200;
+set linesize 200;
+set heading on;
+set echo on;
+set feedback on;
+
+define ICINGA_VERSION=1.6.0
+
+-- --------------------------------------------------------
+-- warning:edit this script to define existing tablespaces
+-- this particular step can be skipped safetly if no new table or index included
+-- --------------------------------------------------------
+/* set real TBS names on which you have quota, no checks are implemented!*/
+define DATATBS='ICINGA_DATA1';
+define LOBTBS='ICINGA_LOB1';
+define IDXTBS='ICINGA_IDX1';
+
+/* script will be terminated on the first error */
+whenever sqlerror exit failure
+spool oracle-upgrade-&&ICINGA_VERSION..log
+
+-- -----------------------------------------
+-- index for BP Addon #2274
+-- -----------------------------------------
+create index statehistory_state_idx on statehistory(objectid,state)
+tablespace &IDXTBS;
+
+-- -----------------------------------------
+-- finally update dbversion
+-- -----------------------------------------
+
+MERGE INTO dbversion
+	USING DUAL ON (name='idoutils')
+	WHEN MATCHED THEN
+		UPDATE SET version='&&ICINGA_VERSION'
+	WHEN NOT MATCHED THEN
+		INSERT (id, name, version) VALUES ('1', 'idoutils', '&&ICINGA_VERSION');
+
+/* last check */
+select object_name,object_type,status  from user_objects where status !='VALID';
+
+/* goodbye */
+spool off
+exit;
+
diff --git a/module/idoutils/db/pgsql/pgsql.sql b/module/idoutils/db/pgsql/pgsql.sql
index 4ccaa76..900c27c 100644
--- a/module/idoutils/db/pgsql/pgsql.sql
+++ b/module/idoutils/db/pgsql/pgsql.sql
@@ -1602,6 +1602,8 @@ CREATE INDEX services_combined_object_idx ON icinga_services(service_object_id,
 
 -- statehistory
 CREATE INDEX statehist_i_id_o_id_s_ty_s_ti on icinga_statehistory(instance_id, object_id, state_type, state_time);
+--#2274
+create index statehist_state_idx on icinga_statehistory(objectid,state);
 
 -- SLA statehistory
 CREATE INDEX slahist_i_id_o_id_s_ti_s_s_ti_e on icinga_slahistory(instance_id,object_id,start_time,end_time);
diff --git a/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-1.7.0.sql b/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-1.7.0.sql
new file mode 100644
index 0000000..996a86e
--- /dev/null
+++ b/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-1.7.0.sql
@@ -0,0 +1,20 @@
+-- -----------------------------------------
+-- upgrade path for Icinga IDOUtils 1.7.0
+--
+-- -----------------------------------------
+-- Copyright (c) 2011 Icinga Development Team (http://www.icinga.org)
+--
+-- Please check http://docs.icinga.org for upgrading information!
+-- -----------------------------------------
+
+-- -----------------------------------------
+--#2274
+-- -----------------------------------------
+create index statehist_state_idx on icinga_statehistory(objectid,state);
+
+-- -----------------------------------------
+-- update dbversion
+-- -----------------------------------------
+
+INSERT INTO icinga_dbversion (name, version) VALUES ('idoutils', '1.6.0') ON DUPLICATE KEY UPDATE version='1.6.0';
+





More information about the icinga-checkins mailing list