Added index to the history table.

[gnucomo.git] / src / database / create.sql

diff --git a/src/database/create.sql b/src/database/create.sql
index 9cd0309..1c3ba8d 100644 (file)
--- a/src/database/create.sql
+++ b/src/database/create.sql
@@ -12,7 +12,25 @@
 -- DBA create the database and give access permissions.
 --
 --  $Log: create.sql,v $
---  Revision 1.10  2003-02-14 06:32:27  arjen
+--  Revision 1.15  2003-08-05 07:43:24  arjen
+--  Added index to the history table.
+--
+--  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'.
+--
+--  Revision 1.12  2003/02/21 08:38:38  arjen
+--  Added new table to the database: log_adv_daemon_email.
+--
+--  Revision 1.11  2003/02/16 08:24:38  arjen
+--  Added a new entry to the action table: Notification was displayed in the listing
+--
+--  Revision 1.10  2003/02/14 06:32:27  arjen
 --  Setup the groups and database permissions. The three groups
 --  are: view, ops and admin.
 --  Added a new entry to the action table.
@@ -70,6 +88,7 @@ COPY "action" FROM stdin;
 18     Notification closed     cls     The notification has been closed.
 19     Notification reopend    opn     The notification has been reopend.
 20     Redisplayed to user     \N      The notification has been presented to a user. This is not the first time
+21     Displayed in list       \N      The notification has been presented in a list with other notifications.
 \.
 
 CREATE UNIQUE INDEX action_actionid_key ON "action" USING btree (actionid);
@@ -124,8 +143,8 @@ CREATE TABLE "db_value"
 
 
 COPY "db_value" FROM stdin;
-db_version     28
-gcm_daemon_version     1
+db_version     41
+gcm_daemon_version     5
 log_processing 0
 last_notification      0
 \.
@@ -144,6 +163,8 @@ CREATE TABLE "history"
 
 );
 
+CREATE INDEX history_pid_mod  ON history(paramid, modified);
+
 --
 --
 
@@ -233,11 +254,38 @@ CREATE TABLE log_adv_kernel_network
 CREATE TABLE log_adv_daemon
 (
        service TEXT,
-       event TEXT
+       event   TEXT
 ) INHERITS (log_adv);
 
 CREATE INDEX log_adv_daemon_service ON log_adv_daemon (service);
 
+CREATE TABLE log_adv_daemon_email
+(
+     source_ip            INET,
+     destination_ip       INET,
+     internal_messageid   TEXT, 
+     external_messageid   TEXT,
+     to_email             TEXT,
+     from_email           TEXT,
+     delay                TIME,
+     xdelay               TIME,
+     mailer               TEXT,
+     status               TEXT,
+     pid                  INT,
+     size                 INT,
+     pri                  INT,
+     relay                TEXT,
+     status_details       TEXT,
+     dsn                  TEXT
+) INHERITS (log_adv_daemon);
+
+CREATE INDEX log_adv_daemon_email_s_ip ON log_adv_daemon_email (source_ip);
+CREATE INDEX log_adv_daemon_email_d_ip ON log_adv_daemon_email (destination_ip);
+CREATE INDEX log_adv_daemon_email_to   ON log_adv_daemon_email (to_email);
+CREATE INDEX log_adv_daemon_email_from ON log_adv_daemon_email (from_email);
+CREATE INDEX log_adv_email_status      ON log_adv_daemon_email (status);
+
+
 /*
 CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER '';
 CREATE FUNCTION "funct_processlog" () RETURNS opaque AS '
@@ -316,6 +364,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"
@@ -422,6 +537,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,