-- DBA create the database and give access permissions.
--
-- $Log: create.sql,v $
--- Revision 1.11 2003-02-16 08:24:38 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
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
\.
);
+CREATE INDEX history_pid_mod ON history(paramid, modified);
+
--
--
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 '
--
--
+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"
--
--
+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,