From 4d57a7e6a9acd5f8ad538e3a4725ee4534d78c74 Mon Sep 17 00:00:00 2001 From: arjen Date: Wed, 9 Jul 2003 07:14:59 +0000 Subject: [PATCH] New database tables: notification_check, notification_check_buffer, notification_check_line and object_statistics. --- src/database/create.sql | 91 +++++++++++++++++++++- src/database/destroy.sql | 12 +++ src/gcm_daemon/classes/gnucomo_db_version.php | 106 ++++++++++++++++++++++++-- 3 files changed, 200 insertions(+), 9 deletions(-) diff --git a/src/database/create.sql b/src/database/create.sql index b43289f..362fd86 100644 --- 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 --- 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 --- 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 /* -- 2.11.0