New database tables: notification_check, notification_check_buffer,

notification_check_line and object_statistics.

diff --git a/src/database/create.sql b/src/database/create.sql
index b43289f..362fd86 100644 (file)
--- a/src/database/create.sql
+++ b/src/database/create.sql
@@ -12,7 +12,11 @@
 -- DBA create the database and give access permissions.
 --
 --  $Log: create.sql,v $
---  Revision 1.13  2003-03-29 08:27:05  arjen
+--  Revision 1.14  2003-07-09 07:14:59  arjen
+--  New database tables: notification_check, notification_check_buffer,
+--  notification_check_line and object_statistics.
+--
+--  Revision 1.13  2003/03/29 08:27:05  arjen
 --  New columns in the table 'log_adv_daemon_email': size, pri, relay,
 --  status_details and dsn.
 --  Added several indices for the table 'log_adv_daemon_email'.
@@ -136,8 +140,8 @@ CREATE TABLE "db_value"
 
 
 COPY "db_value" FROM stdin;
-db_version     36
-gcm_daemon_version     1
+db_version     41
+gcm_daemon_version     5
 log_processing 0
 last_notification      0
 \.
@@ -355,6 +359,73 @@ SELECT setval ('"notification_notificationid_seq"', 1, false);
 --
 --
 
+CREATE SEQUENCE checkid_seq;
+    
+CREATE TABLE notification_check
+(
+     checkid                 bigint DEFAULT nextval('checkid_seq'::text) NOT NULL,
+     checkname               TEXT,
+     description             TEXT,
+     time_between_executions INTERVAL, 
+     last_execution          DATETIME,
+     execution_counter       BIGINT,
+     notificationcounter     BIGINT,
+     decreasinglist          BOOLEAN default false,
+     type_of_issueid         BIGINT
+);
+
+CREATE UNIQUE INDEX not_check_checkid ON notification_check (checkid);
+
+CREATE UNIQUE INDEX not_check_checkname ON notification_check (checkname);
+
+CREATE INDEX not_check_check_lastexec ON notification_check (last_execution);
+
+--
+--
+
+CREATE TABLE notification_check_buffer
+(
+    checkid     BIGINT,
+    sortorder   INTEGER,
+    pid         INTEGER,
+    logid       bigint
+);
+
+CREATE INDEX notcheckbuffer_checkid ON notification_check_buffer(checkid);
+     
+CREATE INDEX notcheckbuffer_sort ON notification_check_buffer(sortorder);
+
+CREATE INDEX notcheckbuffer_pid ON notification_check_buffer(pid);
+     
+CREATE INDEX notcheckbuffer_logid ON notification_check_buffer(logid);
+
+--
+--
+
+CREATE SEQUENCE checklineid_seq;
+     
+CREATE TABLE notification_check_line
+(
+    checklineid             BIGINT DEFAULT nextval('checklineid_seq'::text) NOT NULL,
+    checkid                 BIGINT,
+    sortorder               INTEGER,
+    last_logid              BIGINT default 0,
+    historicboundary        INTERVAL default 0,
+    use_logid               BOOLEAN default false,
+    sql_query               TEXT
+);
+     
+CREATE UNIQUE INDEX notcheckline_checklineid ON notification_check_line (checklineid);
+
+CREATE INDEX notcheckline_checkid            ON notification_check_line (checkid);
+
+CREATE INDEX notcheckline_sort               ON notification_check_line (sortorder);
+
+CREATE INDEX notcheckline_check_sort         ON notification_check_line (checkid, sortorder);
+
+--
+--
+
 CREATE SEQUENCE "object_objectid_seq";
 
 CREATE TABLE "object"
@@ -461,6 +532,20 @@ CREATE INDEX obs_accepted ON object_service USING btree (accepted);
 --
 --
 
+CREATE TABLE object_statistics
+(
+    objectid       bigint,
+    statname       text,
+    statvalue      double precision,
+
+    primary key (objectid, statname)
+);
+
+CREATE INDEX obj_stat_objid ON object_statistics USING btree (objectid);
+
+--
+--
+
 CREATE TABLE "object_user"
 (
        "objectid" bigint,

diff --git a/src/database/destroy.sql b/src/database/destroy.sql
index f69b1ed..3b7f389 100644 (file)
--- a/src/database/destroy.sql
+++ b/src/database/destroy.sql
@@ -42,6 +42,16 @@ DROP SEQUENCE "notification_notificationid_seq";
 
 DROP TABLE "notification";
 
+DROP SEQUENCE checkid_seq;
+    
+DROP TABLE notification_check;
+
+DROP TABLE notification_check_buffer;
+
+DROP SEQUENCE checklineid_seq;
+     
+DROP TABLE notification_check_line;
+
 DROP SEQUENCE "object_objectid_seq";
 
 DROP TABLE "object";
@@ -52,6 +62,8 @@ DROP TABLE "object_priority";
 
 DROP TABLE "object_service";
 
+DROP TABLE object_statistics;
+
 DROP TABLE "object_system_user";
 
 DROP TABLE "object_user";

diff --git a/src/gcm_daemon/classes/gnucomo_db_version.php b/src/gcm_daemon/classes/gnucomo_db_version.php
index f28e62c..ff05e7e 100644 (file)
--- a/src/gcm_daemon/classes/gnucomo_db_version.php
+++ b/src/gcm_daemon/classes/gnucomo_db_version.php
@@ -185,7 +185,7 @@
       $local_sql .= " values ('parameter removed',3,'A parameter was removed') ";
       $dbms->query($local_sql);
 
-  case 20:
+ case 20:
       $local_sql = "CREATE TABLE parameter_notification (notificationid bigint, paramid bigint,  primary key (notificationid, paramid))";
       $dbms->query($local_sql);
   
@@ -198,11 +198,11 @@
       $dbms->query($local_sql);
 
 
-  case 21:
+case 21:
       $local_sql = "UPDATE action SET statuscode = LOWER(statuscode)";
       $dbms->query($local_sql);
 
- case 22:
+case 22:
        //Create a log_adv_kernel_network table that recognizes the log-records
        //that have come from the kernel-network interface (typically iptables).
        $local_sql  = "CREATE TABLE log_adv_daemon (";
@@ -210,11 +210,11 @@
        $local_sql .= ") INHERITS (log_adv)";
        $dbms->query($local_sql);
 
- case 23:
+case 23:
       $local_sql = "CREATE INDEX log_adv_daemon_service ON log_adv_daemon (service)";
       $dbms->query($local_sql);
  
- case 24: 
+case 24: 
        $local_sql = "ALTER TABLE object ADD COLUMN log_count BIGINT";
        $dbms->query($local_sql);
 
@@ -329,7 +329,101 @@ case 34:
 case 35:
      $local_sql = "ALTER TABLE log_adv_daemon_email ADD COLUMN dsn TEXT";
      $dbms->query($local_sql);
-         
+
+case 36:
+     //Create a notification check
+    
+     $local_sql  = "CREATE SEQUENCE checkid_seq";
+     $dbms->query($local_sql);
+    
+     $local_sql  = "CREATE TABLE notification_check (";
+     $local_sql .= "checkid bigint DEFAULT nextval('checkid_seq'::text) NOT NULL,";
+     $local_sql .= "checkname TEXT, description TEXT, time_between_executions INTERVAL, ";
+     $local_sql .= "last_execution DATETIME, execution_counter BIGINT,";
+     $local_sql .= "notificationcounter BIGINT)";
+     $dbms->query($local_sql);
+
+     $local_sql = "CREATE UNIQUE INDEX not_check_checkid ON notification_check (checkid)";
+     $dbms->query($local_sql);
+
+     $local_sql = "CREATE UNIQUE INDEX not_check_checkname ON notification_check (checkname)";
+     $dbms->query($local_sql);
+
+     $local_sql = "CREATE INDEX not_check_check_lastexec ON notification_check (last_execution)";
+     $dbms->query($local_sql);
+
+case 37:
+     $local_sql = "ALTER TABLE notification_check ADD COLUMN decreasinglist BOOLEAN";
+     $dbms->query($local_sql);
+
+
+     $local_sql = "ALTER TABLE notification_check ADD COLUMN type_of_issueid BIGINT";
+     $dbms->query($local_sql);
+
+     $local_sql = "ALTER TABLE notification_check ALTER COLUMN decreasinglist SET DEFAULT 'FALSE'";
+     $dbms->query($local_sql);
+
+     $local_sql  = "CREATE SEQUENCE checklineid_seq";
+     $dbms->query($local_sql);
+     
+     $local_sql  = "CREATE TABLE notification_check_line (";
+     $local_sql .= "checklineid BIGINT DEFAULT nextval('checklineid_seq'::text) NOT NULL,";
+     $local_sql .= "checkid BIGINT, sortorder INTEGER, last_logid BIGINT, ";
+     $local_sql .= "historicboundary INTERVAL, use_logid BOOLEAN, sql_query TEXT";
+     $local_sql .= ")";
+     $dbms->query($local_sql);
+     
+     $local_sql = "ALTER TABLE notification_check_line ALTER COLUMN historicboundary SET DEFAULT '0'";
+     $dbms->query($local_sql);
+     
+     $local_sql = "ALTER TABLE notification_check_line ALTER COLUMN use_logid        SET DEFAULT 'FALSE'";
+     $dbms->query($local_sql);
+     
+     $local_sql = "ALTER TABLE notification_check_line ALTER COLUMN last_logid       SET DEFAULT '0'";
+     $dbms->query($local_sql);
+
+case 38:
+     $local_sql = "CREATE UNIQUE INDEX notcheckline_checklineid ON notification_check_line (checklineid)";
+     $dbms->query($local_sql);
+
+     $local_sql = "CREATE INDEX notcheckline_checkid            ON notification_check_line (checkid)";
+     $dbms->query($local_sql);
+
+     $local_sql = "CREATE INDEX notcheckline_sort               ON notification_check_line (sortorder)";
+     $dbms->query($local_sql);
+
+     $local_sql = "CREATE INDEX notcheckline_check_sort         ON notification_check_line (checkid, sortorder)";
+     $dbms->query($local_sql);
+
+case 39: 
+     $local_sql  = "CREATE TABLE notification_check_buffer (";
+     $local_sql .= "checkid BIGINT, sortorder INTEGER, pid INTEGER, logid bigint";
+     $local_sql .= ")";
+     $dbms->query($local_sql);
+
+     $local_sql = "CREATE INDEX notcheckbuffer_checkid ON notification_check_buffer(checkid)";
+     $dbms->query($local_sql);
+     
+     $local_sql = "CREATE INDEX notcheckbuffer_sort ON notification_check_buffer(sortorder)";
+     $dbms->query($local_sql);
+
+     $local_sql = "CREATE INDEX notcheckbuffer_pid ON notification_check_buffer(pid)";
+     $dbms->query($local_sql);
+     
+     $local_sql = "CREATE INDEX notcheckbuffer_logid ON notification_check_buffer(logid)";
+     $dbms->query($local_sql);
+     
+case 40:
+     $local_sql  = "CREATE TABLE object_statistics (";
+     $local_sql .= "objectid       bigint,";
+     $local_sql .= "statname       text,";
+     $local_sql .= "statvalue      double precision,";
+     $local_sql .= "primary key (objectid, statname)";
+     $local_sql .= ")";
+     $dbms->query($local_sql);
+
+     $local_sql  = "CREATE INDEX obj_stat_objid ON object_statistics USING btree (objectid)";
+     $dbms->query($local_sql);
 
 //These columns have to be removed when a new version of PGSQL has become mainstream that supportsa DROP COLUMN
 /*