[icinga-checkins] icinga.org: icinga-core/test/ido: idoutils: enhance dbversion table with modified and created columns #2562

git at icinga.org git at icinga.org
Sun Apr 29 09:49:43 CEST 2012


Module: icinga-core
Branch: test/ido
Commit: 90b51c75e708bad5d7a5739f844d7ef3aed544fe
URL:    https://git.icinga.org/?p=icinga-core.git;a=commit;h=90b51c75e708bad5d7a5739f844d7ef3aed544fe

Author: Michael Friedrich <michael.friedrich at univie.ac.at>
Date:   Fri Apr 27 18:17:25 2012 +0200

idoutils: enhance dbversion table with modified and created columns #2562

will mark the same as icinga-web does.

allows us (verification script) plus users to gather insights
when they created/modified their db schema.

fixes #2562

---

 Changelog                                          |    4 +++
 module/idoutils/db/mysql/mysql.sql                 |    4 ++-
 .../db/mysql/upgrade/mysql-upgrade-1.7.0.sql       |    9 ++++++-
 .../db/oracle/create_icinga_objects_oracle.sql     |    4 ++-
 module/idoutils/db/oracle/oracle.sql               |    2 +-
 .../db/oracle/upgrade/oracle-upgrade-1.7.0.sql     |   12 +++++++--
 module/idoutils/db/pgsql/pgsql.sql                 |    7 ++++-
 .../db/pgsql/upgrade/pgsql-upgrade-1.7.0.sql       |   25 ++++++++++++++++++++
 8 files changed, 58 insertions(+), 9 deletions(-)

diff --git a/Changelog b/Changelog
index 96b6b19..9e3ad66 100644
--- a/Changelog
+++ b/Changelog
@@ -40,6 +40,7 @@ ENHANCEMENTS
 * idoutils: add new index for state in table statehistory #2274 - TD
 * idoutils: add is_in_effect and trigger_time to scheduleddowntime and downtimehistory tables #2539 - MF
 * idoutils: change varchar(255) to TEXT in mysql (not cs and address rfc columns) #2181 - MF
+* idoutils: enhance dbversion table with modified and created columns #2562 - MF
 
 * install: add configure option --with-temp-file=<filepath> to set temp_file for icinga.cfg #2121 - MF
 * install: add --with-plugin-dir to configure for setting the plugins path accordingly #2344 - MF
@@ -235,6 +236,9 @@ CHANGES
 
 * idoutils change varchar(255) to TEXT in mysql # 2181
 
+* idoutils: add create_time and modify_time to dbversion table #2562
+	** allows to determine the initial import plus modified date
+
 
 1.6.1 - 02/12/2011
 
diff --git a/module/idoutils/db/mysql/mysql.sql b/module/idoutils/db/mysql/mysql.sql
index 617973b..1bd7fc3 100644
--- a/module/idoutils/db/mysql/mysql.sql
+++ b/module/idoutils/db/mysql/mysql.sql
@@ -373,6 +373,8 @@ CREATE TABLE IF NOT EXISTS icinga_dbversion (
   dbversion_id serial,
   name varchar(10) character set latin1  default '',
   version varchar(10) character set latin1  default '',
+  create_time timestamp default '0000-00-00 00:00:00',
+  modify_time timestamp default '0000-00-00 00:00:00',
   PRIMARY KEY (dbversion_id),
   UNIQUE KEY dbversion (name)
 ) ENGINE=InnoDB;
@@ -1586,6 +1588,6 @@ CREATE INDEX services_combined_object_idx ON icinga_services(service_object_id,
 -- -----------------------------------------
 -- set dbversion
 -- -----------------------------------------
-INSERT INTO icinga_dbversion (name, version) VALUES ('idoutils', '1.7.0') ON DUPLICATE KEY UPDATE version='1.7.0';
+INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.7.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.7.0', modify_time=NOW();
 
 
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
index bed08e8..f5290bc 100644
--- a/module/idoutils/db/mysql/upgrade/mysql-upgrade-1.7.0.sql
+++ b/module/idoutils/db/mysql/upgrade/mysql-upgrade-1.7.0.sql
@@ -146,8 +146,15 @@ alter table icinga_systemcommands modify output TEXT;
 alter table icinga_timeperiods modify alias TEXT;
 
 -- -----------------------------------------
+-- #2562
+-- -----------------------------------------
+
+alter table icinga_dbversion add create_time timestamp NOT NULL DEFAULT '0000-00-00 00:00:00';
+alter table icinga_dbversion add modify_time timestamp NOT NULL DEFAULT '0000-00-00 00:00:00';
+
+-- -----------------------------------------
 -- update dbversion
 -- -----------------------------------------
 
-INSERT INTO icinga_dbversion (name, version) VALUES ('idoutils', '1.7.0') ON DUPLICATE KEY UPDATE version='1.7.0';
+INSERT INTO icinga_dbversion (name, version, create_time, modify_time) VALUES ('idoutils', '1.7.0', NOW(), NOW()) ON DUPLICATE KEY UPDATE version='1.7.0', modify_time=NOW();
 
diff --git a/module/idoutils/db/oracle/create_icinga_objects_oracle.sql b/module/idoutils/db/oracle/create_icinga_objects_oracle.sql
index d6344b3..7205717 100644
--- a/module/idoutils/db/oracle/create_icinga_objects_oracle.sql
+++ b/module/idoutils/db/oracle/create_icinga_objects_oracle.sql
@@ -638,7 +638,9 @@ CREATE INDEX customvariablest_idx ON customvariablestatus(varname)
 CREATE TABLE dbversion (
   id integer ,
   name varchar2(10),
-  version varchar2(10)
+  version varchar2(10),
+  create_time TIMESTAMP(0) WITH LOCAL TIME ZONE default TO_TIMESTAMP_TZ('01.01.1970 UTC','DD.MM.YYYY TZR') ,
+  modify_time TIMESTAMP(0) WITH LOCAL TIME ZONE default TO_TIMESTAMP_TZ('01.01.1970 UTC','DD.MM.YYYY TZR')
 )tablespace &&DATATBS;
 alter table dbversion add constraint dbversion_pk PRIMARY KEY  (id)
 	using index tablespace &&IDXTBS;
diff --git a/module/idoutils/db/oracle/oracle.sql b/module/idoutils/db/oracle/oracle.sql
index 7c6efad..2301940 100644
--- a/module/idoutils/db/oracle/oracle.sql
+++ b/module/idoutils/db/oracle/oracle.sql
@@ -63,7 +63,7 @@ SET ESCAPE \
 -- -----------------------------------------
 -- set dbversion
 -- -----------------------------------------
-INSERT INTO dbversion (id, name, version) VALUES ('1', 'idoutils', '&&ICINGA_VERSION');
+INSERT INTO dbversion (id, name, version, create_time, modify_time) VALUES ('1', 'idoutils', '&&ICINGA_VERSION', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
 commit;
 /*goodbye */
 exit;
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
index 7cc8053..9f2c6d4 100644
--- a/module/idoutils/db/oracle/upgrade/oracle-upgrade-1.7.0.sql
+++ b/module/idoutils/db/oracle/upgrade/oracle-upgrade-1.7.0.sql
@@ -4,7 +4,7 @@
 -- run it as icinga database user from directory of this file
 -- sqlplus icinga@<instance> @ oracle-upgrade.1-7.0.sql
 -- -----------------------------------------
--- Copyright (c) 2010-2011 Icinga Development Team (http://www.icinga.org)
+-- Copyright (c) 2010-2012 Icinga Development Team (http://www.icinga.org)
 --
 -- Please check http://docs.icinga.org for upgrading information!
 -- -----------------------------------------
@@ -118,15 +118,21 @@ alter table scheduleddowntime add is_in_effect integer default 0;
 alter table scheduleddowntime add trigger_time TIMESTAMP(0) WITH LOCAL TIME ZONE default TO_TIMESTAMP_TZ('01.01.1970 UTC','DD.MM.YYYY TZR');
 
 -- -----------------------------------------
+-- #2562
+-- -----------------------------------------
+alter table dbversion add create_time TIMESTAMP(0) WITH LOCAL TIME ZONE default TO_TIMESTAMP_TZ('01.01.1970 UTC','DD.MM.YYYY TZR');
+alter table dbversion add modify_time TIMESTAMP(0) WITH LOCAL TIME ZONE default TO_TIMESTAMP_TZ('01.01.1970 UTC','DD.MM.YYYY TZR');
+
+-- -----------------------------------------
 -- finally update dbversion
 -- -----------------------------------------
 
 MERGE INTO dbversion
 	USING DUAL ON (name='idoutils')
 	WHEN MATCHED THEN
-		UPDATE SET version='&&ICINGA_VERSION'
+		UPDATE SET version='&&ICINGA_VERSION', modify_time=CURRENT_TIMESTAMP
 	WHEN NOT MATCHED THEN
-		INSERT (id, name, version) VALUES ('1', 'idoutils', '&&ICINGA_VERSION');
+		INSERT (id, name, version, create_time, modify_time) VALUES ('1', 'idoutils', '&&ICINGA_VERSION', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
 
 /* last check */
 select object_name,object_type,status  from user_objects where status !='VALID';
diff --git a/module/idoutils/db/pgsql/pgsql.sql b/module/idoutils/db/pgsql/pgsql.sql
index 9d276b1..2aa37a6 100644
--- a/module/idoutils/db/pgsql/pgsql.sql
+++ b/module/idoutils/db/pgsql/pgsql.sql
@@ -31,9 +31,10 @@ BEGIN
         IF EXISTS( SELECT * FROM icinga_dbversion WHERE name='idoutils')
         THEN
                 UPDATE icinga_dbversion
-                SET version=version_i WHERE name='idoutils';
+                SET version=version_i, modify_time=NOW()
+		WHERE name='idoutils';
         ELSE
-                INSERT INTO icinga_dbversion (dbversion_id, name, version) VALUES ('1', 'idoutils', version_i);
+                INSERT INTO icinga_dbversion (dbversion_id, name, version, create_time, modify_time) VALUES ('1', 'idoutils', version_i, NOW(), NOW());
         END IF;
 
         RETURN;
@@ -402,6 +403,8 @@ CREATE TABLE  icinga_dbversion (
   dbversion_id bigserial,
   name TEXT  default '',
   version TEXT  default '',
+  create_time timestamp with time zone default '1970-01-01 00:00:00',
+  modify_time timestamp with time zone default '1970-01-01 00:00:00',
   CONSTRAINT PK_dbversion_id PRIMARY KEY (dbversion_id) ,
   CONSTRAINT UQ_dbversion UNIQUE (name)
 ) ;
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
index b4eac2d..3c78981 100644
--- a/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-1.7.0.sql
+++ b/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-1.7.0.sql
@@ -34,6 +34,31 @@ alter table icinga_downtimehistory add trigger_time timestamp with time zone def
 alter table icinga_scheduleddowntime add is_in_effect INTEGER  default 0;
 alter table icinga_scheduleddowntime add trigger_time timestamp with time zone default '1970-01-01 00:00:00';
 
+
+-- -----------------------------------------
+-- #2562
+-- -----------------------------------------
+
+alter table icinga_dbversion add create_time timestamp with time zone default '1970-01-01 00:00:00';
+alter table icinga_dbversion add modify_time timestamp with time zone default '1970-01-01 00:00:00';
+
+CREATE OR REPLACE FUNCTION updatedbversion(version_i TEXT) RETURNS void AS $$
+BEGIN
+        IF EXISTS( SELECT * FROM icinga_dbversion WHERE name='idoutils')
+        THEN
+                UPDATE icinga_dbversion
+                SET version=version_i, modify_time=NOW()
+                WHERE name='idoutils';
+        ELSE
+                INSERT INTO icinga_dbversion (dbversion_id, name, version, create_time, modify_time) VALUES ('1', 'idoutils', version_i, NOW(), NOW());
+        END IF;
+
+        RETURN;
+END;
+$$ LANGUAGE plpgsql;
+-- HINT: su - postgres; createlang plpgsql icinga;
+
+
 -- -----------------------------------------
 -- update dbversion
 -- -----------------------------------------





More information about the icinga-checkins mailing list