[icinga-checkins] icinga.org: icinga-core/master: IDOUTILS: fix oracle scripts

git at icinga.org git at icinga.org
Wed Apr 13 11:25:04 CEST 2011


Module: icinga-core
Branch: master
Commit: 5174b14ff0fb1d2e35c81c6df22f35702977f126
URL:    https://git.icinga.org/?p=icinga-core.git;a=commit;h=5174b14ff0fb1d2e35c81c6df22f35702977f126

Author: Thomas Dressler <tdressler at tdressler.net>
Date:   Sun Apr  3 14:40:35 2011 +0200

IDOUTILS: fix oracle scripts

---

 docbook                                            |    2 +-
 module/icinga-api                                  |    2 +-
 .../db/oracle/create_icinga_objects_oracle.sql     |    4 +-
 module/idoutils/db/oracle/create_oracle_sys.sql    |    2 +-
 module/idoutils/db/oracle/oracle.sql               |   12 ++--
 .../db/oracle/upgrade/alter_icinga13_numbers.sql   |    5 +-
 .../db/oracle/upgrade/alter_icinga13_objects.sql   |   10 ++--
 .../db/oracle/upgrade/oracle-upgrade-1.4.0.sql     |   68 +++-----------------
 8 files changed, 29 insertions(+), 76 deletions(-)

diff --git a/docbook b/docbook
index 31c171b..ae636a7 160000
--- a/docbook
+++ b/docbook
@@ -1 +1 @@
-Subproject commit 31c171b31a9618f735f835c6888a2a53185d2360
+Subproject commit ae636a767ca3c38b6b99451f1a64a133c05753b6
diff --git a/module/icinga-api b/module/icinga-api
index f1fa702..286c426 160000
--- a/module/icinga-api
+++ b/module/icinga-api
@@ -1 +1 @@
-Subproject commit f1fa70229f6e63437c2c11a0b88bf0d6c208f136
+Subproject commit 286c4263674ec1e1035816f253f163c1ea85bf89
diff --git a/module/idoutils/db/oracle/create_icinga_objects_oracle.sql b/module/idoutils/db/oracle/create_icinga_objects_oracle.sql
old mode 100755
new mode 100644
index 6e34bbe..e440368
--- 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-03-27 Thomas Dreßler
+-- current version: 2011-04-03 Thomas Dressler
 -- -- --------------------------------------------------------
 */
 -- -----------------------------------------
@@ -121,6 +121,7 @@ BEGIN
 	|| ' WHERE instance_id='
 	|| p_id;
 	EXECUTE IMMEDIATE v_stmt_str;
+	exception when NO_DATA_FOUND then null;
 END;
 /
 
@@ -141,6 +142,7 @@ BEGIN
 	|| p_time
 	|| ')';
         EXECUTE IMMEDIATE v_stmt_str;
+	exception when NO_DATA_FOUND then null;
 END;
 /
 
diff --git a/module/idoutils/db/oracle/create_oracle_sys.sql b/module/idoutils/db/oracle/create_oracle_sys.sql
old mode 100755
new mode 100644
index cbc549d..d15541d
--- a/module/idoutils/db/oracle/create_oracle_sys.sql
+++ b/module/idoutils/db/oracle/create_oracle_sys.sql
@@ -9,7 +9,7 @@
 -- works with Oracle10+ and sqlplus
 -- for because of grants on v$ views this must run as sys 
 -- if not needed, normal dba user is valid to run
--- initial version: 2011-03-07 Thomas Dreßler
+-- initial version: 2011-03-07 Thomas Dressler
 -- current version: 2011-03-27 
 -- --------------------------------------------------------
 */
diff --git a/module/idoutils/db/oracle/oracle.sql b/module/idoutils/db/oracle/oracle.sql
old mode 100755
new mode 100644
index 8a45563..becfd93
--- a/module/idoutils/db/oracle/oracle.sql
+++ b/module/idoutils/db/oracle/oracle.sql
@@ -16,7 +16,7 @@
 --
 -- initial version: 2008-02-20 David Schmidt
 -- 		    2011-01-17 Michael Friedrich <michael.friedrich(at)univie.ac.at>
--- current version: 2011-03-27 Thomas Dreßler
+-- current version: 2011-04-03 Thomas Dressler
 --
 -- -- --------------------------------------------------------
 */
@@ -39,11 +39,11 @@ DEFINE LOBFS=./
 icinga tablespaces and user must fit definitions in create_icinga_objects_oracle.sql
 */
 
-DEFINE DATATBS=ICINGA_DATA1
-DEFINE IDXTBS=ICINGA_IDX1
-DEFINE LOBTBS=ICINGA_LOB1
-DEFINE ICINGA_USER=icinga14
-DEFINE ICINGA_PASSWORD=icinga14
+DEFINE DATATBS=ICINGA_DATA
+DEFINE IDXTBS=ICINGA_IDX
+DEFINE LOBTBS=ICINGA_LOB
+DEFINE ICINGA_USER=icinga
+DEFINE ICINGA_PASSWORD=icinga
 -- -----------------------------------------
 -- set sqlplus parameter
 -- -----------------------------------------
diff --git a/module/idoutils/db/oracle/upgrade/alter_icinga13_numbers.sql b/module/idoutils/db/oracle/upgrade/alter_icinga13_numbers.sql
old mode 100755
new mode 100644
index b827d58..aae7166
--- a/module/idoutils/db/oracle/upgrade/alter_icinga13_numbers.sql
+++ b/module/idoutils/db/oracle/upgrade/alter_icinga13_numbers.sql
@@ -10,8 +10,8 @@
 -- --------------------------------------------------------

 -- works with Oracle10+ and sqlplus

 -- 

--- initial version: 2011-03-01 Thomas Dreßler

--- current version: 2011-03-27 Thomas Dreßler

+-- initial version: 2011-03-01 Thomas Dressler

+-- current version: 2011-03-27 Thomas Dressler

 -- --------------------------------------------------------

 */

 -- -----------------------------------------

@@ -39,7 +39,6 @@ select 'Drop index '||index_name||';' from user_indexes where INDEX_TYPE='FUNCTI
 -- Prepare Alter Table script for each number column and run it

 -- -----------------------------------------

 select 'ALTER TABLE '||table_name ||' modify ('|| column_name||' '|| decode(data_scale,0,'INTEGER','NUMBER')||');' from user_tab_columns where data_type='NUMBER';

-prompt spool off;;

 spool off;

 set heading on;

 set echo on;

diff --git a/module/idoutils/db/oracle/upgrade/alter_icinga13_objects.sql b/module/idoutils/db/oracle/upgrade/alter_icinga13_objects.sql
old mode 100755
new mode 100644
index 8844f63..090c6cd
--- a/module/idoutils/db/oracle/upgrade/alter_icinga13_objects.sql
+++ b/module/idoutils/db/oracle/upgrade/alter_icinga13_objects.sql
@@ -7,8 +7,8 @@
 -- seperates constraints, lobs and indexes
 -- works with Oracle10+ and sqlplus
 --
--- initial version: 2011-03-01 Thomas Dreßler
--- current version: 2011-03-27 Thomas Dreßler
+-- initial version: 2011-03-01 Thomas Dressler
+-- current version: 2011-04-03 Thomas Dressler
 --
 -- this will ask you for the tablespace names unless you run it from oracle-upgrade-1.4.0.sql 
 -- or defined it previous in defines (eg. define IDXTBS=<yourDATATBS> ....)
@@ -37,7 +37,9 @@ spool move_icinga13_objects.log
 -- --------------------------------------------------------
 */
 declare
-cursor c is select constraint_name,table_name from user_constraints where constraint_type in ('C','P','U');
+cursor c is 
+	select constraint_name,table_name from user_constraints i
+	where table_name not like 'BIN$%' and constraint_type in ('C','P','U');
 r c%rowtype;
 s varchar2(2000);
 begin
@@ -213,6 +215,4 @@ close c;
 end;
 /
 
-/* goodbye */
 spool off;
-
diff --git a/module/idoutils/db/oracle/upgrade/oracle-upgrade-1.4.0.sql b/module/idoutils/db/oracle/upgrade/oracle-upgrade-1.4.0.sql
old mode 100755
new mode 100644
index 1173ff8..29729d4
--- a/module/idoutils/db/oracle/upgrade/oracle-upgrade-1.4.0.sql
+++ b/module/idoutils/db/oracle/upgrade/oracle-upgrade-1.4.0.sql
@@ -18,9 +18,6 @@ set feedback on;
 define ICINGA_VERSION=1.4.0
 
 -- --------------------------------------------------------
--- move icinga index/lobs to dedicated tablespace
--- implements Feature #1355
--- https://dev.icinga.org/issues/1355
 -- warning:edit this script to define existing tablespaces
 -- this particular step can be skipped safetly
 -- --------------------------------------------------------
@@ -28,7 +25,15 @@ define ICINGA_VERSION=1.4.0
 define DATATBS='ICINGA_DATA';
 define LOBTBS='ICINGA_DATA';
 define IXTBS='ICINGA_IDX';
- at move_icinga13_objects.sql
+
+-- --------------------------------------------------------
+-- rewrite objects
+-- --------------------------------------------------------
+ at alter_icinga13_objects.sql
+-- --------------------------------------------------------
+-- recreate functions
+-- --------------------------------------------------------
+ at recreate_icinga13_functions.sql
 
 -- --------------------------------------------------------
 -- remove number limitations
@@ -41,59 +46,6 @@ define IXTBS='ICINGA_IDX';
 whenever sqlerror exit failure
 spool oracle-upgrade-&&ICINGA_VERSION..log
 
--- --------------------------------------------------------
--- redefine unix time functions
--- --------------------------------------------------------
--- unix timestamp to oracle date function
--- --------------------------------------------------------
-
-CREATE OR REPLACE FUNCTION unixts2date( n_seconds   IN    integer) RETURN    DATE
-IS
-        unix_start  DATE    := TO_DATE('01.01.1970','DD.MM.YYYY');
-        unix_max    INTEGER  := 2145916799;
-        unix_min    INTEGER     := -2114380800;
-
-BEGIN
-				 if n_seconds is null then
-          return unix_start;
-        end if;
-        IF n_seconds > unix_max THEN
-                RAISE_APPLICATION_ERROR( -20901, 'UNIX timestamp too large for 32 bit limit' );
-        ELSIF n_seconds < unix_min THEN
-                RAISE_APPLICATION_ERROR( -20902, 'UNIX timestamp too small for 32 bit limit' );
-       END IF;
-       RETURN unix_start + NUMTODSINTERVAL( n_seconds, 'SECOND' );
-/* no exception handling, all errors goes to application */        
-END;
-/
-
--- --------------------------------------------------------
--- oracle date to unix timestamp function
--- --------------------------------------------------------
-
-CREATE OR REPLACE FUNCTION date2unixts( d in date) RETURN    INTEGER
-IS
-        unix_start  DATE    := TO_DATE('01.01.1970','DD.MM.YYYY');
-        n_seconds   integer;
-        unix_max    INTEGER  := 2145916799;
-        unix_min    INTEGER     := -2114380800;
-
-BEGIN
-				if d is null then
-          return 0;
-        end if;
-        
-				n_seconds:=(d-unix_start)*60*60*24;
-        IF n_seconds > unix_max THEN
-                RAISE_APPLICATION_ERROR( -20901, 'UNIX timestamp too large for 32 bit limit' );
-        ELSIF n_seconds < unix_min THEN
-                RAISE_APPLICATION_ERROR( -20902, 'UNIX timestamp too small for 32 bit limit' );
-        END IF;
-        return n_seconds;
-/* no exception handling, all errors goes to application */
-END;
-/
-
 
 -- -----------------------------------------
 -- finally update dbversion
@@ -102,7 +54,7 @@ END;
 MERGE INTO dbversion
 	USING DUAL ON (name='idoutils')
 	WHEN MATCHED THEN
-		UPDATE SET version='1.4.0'
+		UPDATE SET version='&&ICINGA_VERSION'
 	WHEN NOT MATCHED THEN
 		INSERT (id, name, version) VALUES ('1', 'idoutils', '&&ICINGA_VERSION');
 





More information about the icinga-checkins mailing list