From 9470528dfe75f26321ca8d9cdfc74cdaa51b39e2 Mon Sep 17 00:00:00 2001 From: arjen Date: Wed, 20 Nov 2002 18:10:05 +0000 Subject: [PATCH] New database tabels: db_value, history, parameter, property. Removed tables: object_system_user, unprocessed_log. ...and a few fields changed in existing tables (log, log_adv, notification, object_issue, user). The table gnucomo_user is now called user. --- src/database/create.sql | 183 ++++++++++++++++++++++++++++++----------------- src/database/destroy.sql | 13 ++++ 2 files changed, 129 insertions(+), 67 deletions(-) diff --git a/src/database/create.sql b/src/database/create.sql index 95ea41e..8cd0a4e 100644 --- a/src/database/create.sql +++ b/src/database/create.sql @@ -11,6 +11,7 @@ -- Before running this script with 'psql -f', you should have your -- DBA create the database and give access permissions. -- +-- $log$ -- CREATE SEQUENCE "action_actionid_seq"; @@ -90,6 +91,34 @@ SELECT setval ('"action_user_actionstepid_seq"', 1, false); -- -- +CREATE TABLE "db_value" +( + "setting" text, + "setting_value" text +); + + +COPY "db_value" FROM stdin; +db_version 1 +\. + +-- +-- + +CREATE TABLE "history" +( + paramid bigint, + modified timestamp, + change_nature text, -- CREATED, MODIFIED or REMOVED + changed_property text, + new_value text, + remark text + +); + +-- +-- + CREATE SEQUENCE "log_logid_seq"; CREATE TABLE "log" @@ -100,7 +129,8 @@ CREATE TABLE "log" "servicecode" text, "object_timestamp" timestamp with time zone, "timestamp" timestamp with time zone, - "rawdata" text + "rawdata" text, + "processed" boolean ); CREATE UNIQUE INDEX log_logid_key ON log USING btree (logid); @@ -117,48 +147,55 @@ CREATE INDEX log_timestmap ON log USING btree ("timestamp"); CREATE INDEX log_timestamp ON log USING btree (object_timestamp); +CREATE INDEX log_processed ON log USING btree (processed); + SELECT setval ('"log_logid_seq"', 1, false); + +CREATE RULE log_update AS ON UPDATE TO log DO INSTEAD NOTHING; + -- -- +CREATE SEQUENCE "log_advid_seq"; + + CREATE TABLE "log_adv" ( - "logid" bigint, - "source_ip" inet, - "destination_ip" inet, - "mac_address" macaddr, - "packetlength" integer, - "protocol" text, - "source_port" integer, - "destination_port" integer, - "messageid" text, - "system_username" text, - "networkdevice" text + "log_advid" bigint DEFAULT + nextval('"log_advid_seq"'::text) NOT NULL, + "logid" bigint NOT NULL ); -CREATE INDEX loa_logid ON log_adv USING btree (logid); -CREATE INDEX loa_source_ip ON log_adv USING btree (source_ip); +CREATE INDEX log_adv_logid ON log_adv USING btree (logid); -CREATE INDEX loa_destination_ip ON log_adv USING btree (destination_ip); +CREATE UNIQUE INDEX log_adv_log_advid ON log_adv USING btree (log_advid); -CREATE INDEX loa_mac_address ON log_adv USING btree (mac_address); +SELECT setval ('"log_advid_seq"', 1, false); -CREATE INDEX loa_packetlength ON log_adv USING btree (packetlength); -CREATE INDEX loa_protocol ON log_adv USING btree (protocol); +/* +CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER ''; +CREATE FUNCTION "funct_processlog" () RETURNS opaque AS ' +-- Initial date: September 18th 2002 +-- Update: November 13th 2002 +-- Author: Brenno J.S.A.A.F. de Winter +-- Abstract: This routine sets a flag +DECLARE + var_value VARCHAR; + var_setting VARCHAR; -CREATE INDEX loa_source_port ON log_adv USING btree (source_port); +BEGIN -CREATE INDEX loa_destination_port ON log_adv USING btree (destination_port); + UPDATE db_value SET setting_value = ''TRUE'' WHERE setting = ''log_flag''; + RETURN NULL; +END; + ' LANGUAGE 'plpgsql'; -CREATE INDEX loa_messageid ON log_adv USING btree (messageid); - -CREATE INDEX loa_system_username ON log_adv USING btree (system_username); - -CREATE INDEX loa_networkdevice ON log_adv USING btree (networkdevice); +CREATE TRIGGER "log_insert" AFTER INSERT ON "log" FOR EACH ROW EXECUTE PROCEDURE "funct_processlog" (); +*/ -- -- @@ -184,7 +221,7 @@ CREATE TABLE "notification" "notificationid" bigint DEFAULT nextval('"notification_notificationid_seq"'::text) NOT NULL, "objectid" bigint, - "type_of_notificationid" bigint, + "type_of_issueid" bigint, "timestamp" timestamp with time zone, "statuscode" character varying(3), "priority" integer, @@ -254,7 +291,7 @@ SELECT setval ('"object_objectid_seq"', 1, false); CREATE TABLE "object_issue" ( "objectid" bigint, - "type_of_notificationid" bigint, + "type_of_issueid" bigint, "default_priority" integer, "escalation" boolean, "escalation_time" time without time zone, @@ -313,23 +350,6 @@ CREATE INDEX obs_accepted ON object_service USING btree (accepted); -- -- -CREATE TABLE "object_system_user" -( - "objectid" bigint, - "system_username" text, - "can_login" boolean, - "can_be_root" boolean -); - -CREATE UNIQUE INDEX osu_pk ON object_system_user USING btree (objectid, system_username); - -CREATE INDEX osu_objectid ON object_system_user USING btree (objectid); - -CREATE INDEX osu_system_username ON object_system_user USING btree (system_username); - --- --- - CREATE TABLE "object_user" ( "objectid" bigint, @@ -348,6 +368,26 @@ CREATE INDEX ous_security_level ON object_user USING btree (security_level); -- -- +CREATE SEQUENCE "paramid_seq"; + +CREATE TABLE "parameter" +( + "paramid" bigint DEFAULT nextval('"paramid_seq"'::text) NOT NULL, + "objectid" bigint, + "name" text, + "class" text, + "description" text, + + primary key (paramid) +); + +CREATE UNIQUE INDEX param_obj_name ON parameter USING btree (objectid, name, class); + +SELECT setval ('"paramid_seq"', 1, true); + +-- +-- + CREATE TABLE "priority" ( "priority" integer, @@ -364,6 +404,21 @@ CREATE UNIQUE INDEX pri_pk ON priority USING btree (priority); -- -- +CREATE TABLE "property" +( + paramid bigint, + name text, + value text, + type text, -- STATIC or DYNAMIC + min float, + max float, + + primary key (paramid, name) +); + +-- +-- + CREATE TABLE "service" ( "servicecode" text, @@ -372,12 +427,21 @@ CREATE TABLE "service" "max_priority" integer ); +COPY "service" FROM stdin; +httpd httpd 1 5 +imap imap 1 5 +imapd imapd 1 5 +kernel kernel 1 5 +sshd sshd 1 5 +su su 1 5 +syslogd syslogd 1 5 +\. + CREATE UNIQUE INDEX ser_pk ON service USING btree (servicecode); CREATE UNIQUE INDEX ser_servicename ON service USING btree (servicename); -- --- CREATE TABLE "status" ( @@ -435,34 +499,19 @@ SELECT setval ('"type_of_issue_type_of_issue_seq"', 1, true); -- -- -CREATE SEQUENCE "unprocessed_l_unprocessedid_seq"; - -CREATE TABLE "unprocessed_log" -( - "unprocessedid" bigint DEFAULT - nextval('"unprocessed_l_unprocessedid_seq"'::text) NOT NULL, - "objectid" bigint, - "servicecode" text -); - -CREATE UNIQUE INDEX unprocessed_l_unprocessedid_key ON unprocessed_log USING btree (unprocessedid); - -SELECT setval ('"unprocessed_l_unprocessedid_seq"', 1, false); - --- --- - -CREATE TABLE "user_gnucomo" +CREATE TABLE "user" ( - "username" text, - "password" text, + "username" text NOT NULL, "active_sessionid" bigint, "account_active" boolean, "security_level" integer ); -CREATE UNIQUE INDEX usr_pk ON user_gnucomo USING btree (username); +CREATE UNIQUE INDEX user_username ON user USING btree (username); + +CREATE UNIQUE INDEX user_active_sessionid ON user USING btree (active_sessionid); -CREATE INDEX usr_active_sessionid ON user_gnucomo USING btree (active_sessionid); +CREATE INDEX user_account_active ON user USING btree (account_active); +CREATE INDEX user_security_level ON user USING btree (security_level); diff --git a/src/database/destroy.sql b/src/database/destroy.sql index 51db156..4cccde4 100644 --- a/src/database/destroy.sql +++ b/src/database/destroy.sql @@ -21,10 +21,16 @@ DROP SEQUENCE "action_user_actionstepid_seq"; DROP TABLE "action_user"; +DROP TABLE "db_value"; + +DROP TABLE "history"; + DROP SEQUENCE "log_logid_seq"; DROP TABLE "log"; +DROP SEQUENCE "log_advid_seq"; + DROP TABLE "log_adv"; DROP TABLE "log_notification"; @@ -47,8 +53,14 @@ DROP TABLE "object_system_user"; DROP TABLE "object_user"; +DROP SEQUENCE "paramid_seq"; + +DROP TABLE "parameter"; + DROP TABLE "priority"; +DROP TABLE "property"; + DROP TABLE "service"; DROP TABLE "status"; @@ -62,4 +74,5 @@ DROP SEQUENCE "unprocessed_l_unprocessedid_seq"; DROP TABLE "unprocessed_log"; DROP TABLE "user_gnucomo"; +DROP TABLE "user"; -- 2.11.0