Fixed a few typos.

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

diff --git a/src/database/create.sql b/src/database/create.sql
index 219abe0..3ce7098 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.11  2003-02-16 08:24:38  arjen
+--  Revision 1.16  2003-08-16 14:29:02  arjen
+--  Fixed a few typos.
+--
+--  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
@@ -128,8 +146,8 @@ CREATE TABLE "db_value"
 
 
 COPY "db_value" FROM stdin;
-db_version     31
-gcm_daemon_version     1
+db_version     41
+gcm_daemon_version     5
 log_processing 0
 last_notification      0
 \.
@@ -148,6 +166,8 @@ CREATE TABLE "history"
 
 );
 
+CREATE INDEX history_pid_mod  ON history(paramid, modified);
+
 --
 --
 
@@ -237,11 +257,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 '
@@ -320,6 +367,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"
@@ -426,6 +540,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,
@@ -674,9 +802,9 @@ GRANT SELECT ON usr TO GROUP view, GROUP ops, GROUP admin;
 
 GRANT INSERT ON action_user TO GROUP ops, GROUP admin;
 GRANT UPDATE ON notification TO GROUP ops, GROUP admin;
-GRANT INSERT ON object GROUP admin;
+GRANT INSERT ON object TO GROUP admin;
 GRANT UPDATE ON object TO GROUP ops, GROUP admin;
-GRANT DELETE ON object GROUP admin;
+GRANT DELETE ON object TO GROUP admin;
 GRANT UPDATE ON usr TO GROUP view, GROUP ops, GROUP admin;
 GRANT INSERT ON usr TO GROUP admin;
 GRANT DELETE ON usr TO GROUP admin;