1 --*************************************************************************
2 -- (c) Copyright 2002, De Winter Information Solutions
3 -- This is free software; you can redistribute it and/or modify it under the
4 -- terms of the GNU General Public License, see the file COPYING.
5 --*************************************************************************/
7 -- Gnucomo database creation script.
10 -- This SQL script creates the initial tables for the Gnucomo database.
11 -- Before running this script with 'psql -f', you should have your
12 -- DBA create the database and give access permissions.
14 -- $Log: create.sql,v $
15 -- Revision 1.8 2003-02-08 07:36:41 arjen
16 -- Added new table to the database : log_adv_daemon
18 -- Revision 1.7 2003/02/05 09:29:08 arjen
19 -- Bug fix: action.statuscode was in upper case.
21 -- Revision 1.6 2003/01/20 07:29:48 arjen
22 -- Added new tables to the database: parameter_class and parameter_notification
24 -- Revision 1.5 2003/01/18 08:46:48 arjen
25 -- Added new records to the 'type_of_issue' table.
26 -- Changed semantics of actionid 9 in the 'action' table.
30 CREATE SEQUENCE "action_actionid_seq";
34 "actionid" bigint DEFAULT nextval('"action_actionid_seq"'::text) NOT NULL,
36 "statuscode" character varying(3),
41 COPY "action" FROM stdin;
42 1 Entry in the system new This indicates that a notification has been entered into the system.
43 2 Display to user opn The notification has been displayed to the user. It doesn't mean that the user actually read the notification, but he/she should be aware. For that reason we consider the notification to be OPEN
44 3 Remarks added pen Remarks have been added to the notification.
45 4 Priority changed manually pen The priority of the notification has been changed by the user.
46 5 Priority changed automatically pen The priority of the notification has been changed by the system. This can be the result of an action by the user or an automatical escalation.
47 6 Action taken pen An action has been taken.
48 7 Assignment to user pen The notification has been assigned to an user.
49 8 More information or research needed. inv The notification is relevant and will be handled, however more information or research will be needed. For that reason the status has been altered to UNDER INVESTIGATION.
50 9 Investigation completed pen Investigation has been done. Information is available to fix the problem.
51 10 Job output no longer valid cls By making a newer job output a valid reference, this data has been obsoleted. Since it was a reference once the output isn't interesting anymore and thus the notification can be closed.
52 11 Action taken - please verify cls An action has been taken and things should have been resolved. Before the notification can be closed a verification has to be done. The new status is now VERIFY.
53 12 Action not verified pen A check has been done and the results were not good. New verification is needed. The status has been changed back to PEN.
54 13 Action verified cls A check has been done and the results were approved. The notification has been closed.
55 15 SMS sent opn An SMS has been sent.
56 14 E-mail sent opn An e-mail has been sent.
57 16 Fax sent opn An fax has been sent.
58 17 Log entries shown \N The log entries relevant to the notification have been shown.
59 18 Notification closed cls The notification has been closed.
60 19 Notification reopend opn The notification has been reopend.
63 CREATE UNIQUE INDEX action_actionid_key ON "action" USING btree (actionid);
65 CREATE UNIQUE INDEX act_pk ON "action" USING btree (actionid);
67 CREATE UNIQUE INDEX act_actionname ON "action" USING btree (actionname);
69 CREATE INDEX act_statuscode ON "action" USING btree (statuscode);
71 SELECT setval ('"action_actionid_seq"', 19, true);
76 CREATE SEQUENCE "action_user_actionstepid_seq";
78 CREATE TABLE "action_user"
80 "actionstepid" bigint DEFAULT
81 nextval('"action_user_actionstepid_seq"'::text) NOT NULL,
84 "notificationid" bigint,
85 "timestamp" timestamp with time zone,
86 "statuscode" character varying(3),
90 CREATE UNIQUE INDEX action_user_actionstepid_key ON action_user USING btree (actionstepid);
92 CREATE INDEX anu_actionid ON action_user USING btree (actionid);
94 CREATE INDEX anu_username ON action_user USING btree (username);
96 CREATE INDEX anu_notificationid ON action_user USING btree (notificationid);
98 CREATE INDEX anu_timestamp ON action_user USING btree ("timestamp");
100 CREATE INDEX anu_statuscode ON action_user USING btree (statuscode);
102 SELECT setval ('"action_user_actionstepid_seq"', 1, false);
107 CREATE TABLE "db_value"
114 COPY "db_value" FROM stdin;
123 CREATE TABLE "history"
127 change_nature text, -- CREATED, MODIFIED or REMOVED
128 changed_property text,
137 CREATE SEQUENCE "log_logid_seq";
141 "logid" bigint DEFAULT nextval('"log_logid_seq"'::text) NOT NULL,
143 "original_filename" text,
145 "object_timestamp" timestamp with time zone,
146 "timestamp" timestamp with time zone,
148 "processed" boolean DEFAULT false,
149 "recognized" boolean DEFAULT false
152 CREATE UNIQUE INDEX log_logid_key ON log USING btree (logid);
154 CREATE INDEX log_objectid ON log USING btree (objectid);
156 CREATE INDEX log_original_filename ON log USING btree (original_filename);
158 CREATE INDEX log_servicecode ON log USING btree (servicecode);
160 CREATE INDEX log_object_timestmap ON log USING btree ("timestamp");
162 CREATE INDEX log_timestmap ON log USING btree ("timestamp");
164 CREATE INDEX log_timestamp ON log USING btree (object_timestamp);
166 CREATE INDEX log_processed ON log USING btree (processed);
168 SELECT setval ('"log_logid_seq"', 1, false);
174 CREATE SEQUENCE "log_advid_seq";
177 CREATE TABLE "log_adv"
179 "log_advid" bigint DEFAULT
180 nextval('"log_advid_seq"'::text) NOT NULL,
181 "logid" bigint NOT NULL,
182 "detailed_table" text
186 CREATE INDEX log_adv_logid ON log_adv USING btree (logid);
188 CREATE UNIQUE INDEX log_adv_log_advid ON log_adv USING btree (log_advid);
190 SELECT setval ('"log_advid_seq"', 1, false);
192 CREATE TABLE log_adv_kernel_network
199 packet_length bigint,
205 destination_port int,
211 syn boolean DEFAULT false,
218 ) INHERITS (log_adv);
220 CREATE TABLE log_adv_daemon
224 ) INHERITS (log_adv);
226 CREATE INDEX log_adv_daemon_service ON log_adv_daemon (service);
229 CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER '';
230 CREATE FUNCTION "funct_processlog" () RETURNS opaque AS '
231 -- Initial date: September 18th 2002
232 -- Update: November 13th 2002
233 -- Author: Brenno J.S.A.A.F. de Winter
234 -- Abstract: This routine sets a flag
241 UPDATE db_value SET setting_value = ''TRUE'' WHERE setting = ''log_flag'';
244 ' LANGUAGE 'plpgsql';
247 CREATE TRIGGER "log_insert" AFTER INSERT ON "log" FOR EACH ROW EXECUTE PROCEDURE "funct_processlog" ();
252 CREATE TABLE "log_notification"
254 "notificationid" bigint,
258 CREATE UNIQUE INDEX lon_pk ON log_notification USING btree (notificationid, logid);
260 CREATE UNIQUE INDEX lon_notificationid ON log_notification USING btree (notificationid);
262 CREATE UNIQUE INDEX lon_logid ON log_notification USING btree (logid);
267 CREATE SEQUENCE "notification_notificationid_seq";
269 CREATE TABLE "notification"
271 "notificationid" bigint DEFAULT
272 nextval('"notification_notificationid_seq"'::text) NOT NULL,
274 "type_of_issueid" bigint,
275 "timestamp" timestamp with time zone,
276 "statuscode" character varying(3),
278 "escalation_count_timestamp" timestamp with time zone,
279 "repeat_notification_timestamp" timestamp with time zone,
280 "securitylevel_view" integer,
281 "securitylevel_add" integer,
282 "securitylevel_close" integer
285 CREATE UNIQUE INDEX notification_notificationid_key ON notification USING btree (notificationid);
287 CREATE INDEX not_objectid ON notification USING btree (objectid);
289 CREATE INDEX not_type_of_issueid ON notification USING btree (type_of_issueid);
291 CREATE INDEX not_timestamp ON notification USING btree ("timestamp");
293 CREATE INDEX not_statuscode ON notification USING btree (statuscode);
295 CREATE INDEX not_priority ON notification USING btree (priority);
297 CREATE INDEX not_escalation_count_timestamp ON notification USING btree (escalation_count_timestamp);
299 CREATE INDEX not_repeat_notification_timesta ON notification USING btree (repeat_notification_timestamp);
301 SELECT setval ('"notification_notificationid_seq"', 1, false);
306 CREATE SEQUENCE "object_objectid_seq";
308 CREATE TABLE "object"
310 "objectid" bigint DEFAULT nextval('"object_objectid_seq"'::text) NOT NULL,
313 "scp_enabled" boolean,
315 "mail_enabled" boolean,
317 "sms_enabled" boolean,
319 "fax_enabled" boolean,
321 "object_description" text,
323 "physical_location" text,
330 CREATE UNIQUE INDEX object_objectid_key ON object USING btree (objectid);
332 CREATE UNIQUE INDEX obj_objectname ON object USING btree (objectname);
334 CREATE UNIQUE INDEX obj_objectcode ON object USING btree (objectcode);
336 CREATE INDEX obj_mail_from ON object USING btree (mail_from);
338 CREATE INDEX os ON object (os);
340 CREATE INDEX os_version ON object (os, os_version);
342 SELECT setval ('"object_objectid_seq"', 1, false);
347 CREATE TABLE "object_issue"
350 "type_of_issueid" bigint,
351 "default_priority" integer,
352 "escalation" boolean,
353 "escalation_time" time without time zone,
354 "max_priority" integer,
355 "adjust_setting" text
358 CREATE UNIQUE INDEX obj_pk ON object_issue USING btree (objectid, type_of_issueid);
360 CREATE INDEX obj_objectid ON object_issue USING btree (objectid);
362 CREATE UNIQUE INDEX obj_type_of_notificationid ON object_issue USING btree (type_of_issueid);
367 CREATE TABLE "object_priority"
370 "priorityid" integer,
374 "repeat_notification" boolean,
375 "interval_for_repeat" time without time zone
378 CREATE UNIQUE INDEX obi_pk ON object_priority USING btree (objectid, priorityid);
380 CREATE INDEX obi_objectid ON object_priority USING btree (objectid);
382 CREATE INDEX obi_priorityid ON object_priority USING btree (priorityid);
387 CREATE TABLE "object_service"
391 "expected_interval" bigint,
392 "last_entry" timestamp with time zone,
393 "default_priority" integer,
394 "maximum_priority" integer,
398 CREATE UNIQUE INDEX obs_pk ON object_service USING btree (objectid, servicecode);
400 CREATE INDEX obs_objectid ON object_service USING btree (objectid);
402 CREATE INDEX obs_servicecode ON object_service USING btree (servicecode);
404 CREATE INDEX obs_accepted ON object_service USING btree (accepted);
409 CREATE TABLE "object_user"
413 "security_level" integer
416 CREATE UNIQUE INDEX ous_pk ON object_user USING btree (objectid, username);
418 CREATE INDEX ous_objectid ON object_user USING btree (objectid);
420 CREATE INDEX ous_username ON object_user USING btree (username);
422 CREATE INDEX ous_security_level ON object_user USING btree (security_level);
427 CREATE SEQUENCE "paramid_seq";
429 CREATE TABLE "parameter"
431 "paramid" bigint DEFAULT nextval('"paramid_seq"'::text) NOT NULL,
437 primary key (paramid)
440 CREATE UNIQUE INDEX param_obj_name ON parameter USING btree (objectid, name, class);
442 SELECT setval ('"paramid_seq"', 1, true);
447 CREATE TABLE "parameter_class"
449 "name" text, -- Name of the class: see parameter.class
450 "property_name" text,
452 "property_type" text, -- STATIC or DYNAMIC
453 "min" float, -- Default minimum value
454 "max" float, -- Default maximum value
455 "notify" boolean, -- Notify if something changes ?
457 primary key (name, property_name)
460 INSERT INTO parameter_class (name, property_name, description, property_type, notify)
461 VALUES ('package', 'version', 'The installed version of the package', 'STATIC', 't');
466 CREATE TABLE "parameter_notification"
468 "notificationid" bigint,
471 primary key (notificationid, paramid)
477 CREATE TABLE "priority"
483 "repeat_notification" boolean,
484 "interval_for_repeat" time without time zone
488 CREATE UNIQUE INDEX pri_pk ON priority USING btree (priority);
493 CREATE TABLE "property"
498 type text, -- STATIC or DYNAMIC
502 primary key (paramid, name)
508 CREATE TABLE "service"
512 "default_priority" integer,
513 "max_priority" integer
516 COPY "service" FROM stdin;
526 CREATE UNIQUE INDEX ser_pk ON service USING btree (servicecode);
528 CREATE UNIQUE INDEX ser_servicename ON service USING btree (servicename);
532 CREATE TABLE "status"
534 "statuscode" character varying(3),
536 "open_notification" boolean,
541 COPY "status" FROM stdin;
542 new new entry t Just detected, but nothing has been done yet
543 opn open notification t The notification has been displayed to a user or a user has been notified. However nothing has been done yet.
544 pen pending t The notification is currently being worked on.
545 ver waiting for verification t The notification has been worked on and is currently awaiting the approval/verification.
546 rej rejected f The notification has been identified as a false postive and was reject. The notification is now closed
547 cls closed f The notification has been closed
548 inv needs investigation t The notification is currently under investigation and is awaiting additional details before one can work on this again.
551 CREATE UNIQUE INDEX sta_pk ON status USING btree (statuscode);
553 CREATE UNIQUE INDEX sta_statusname ON status USING btree (statusname);
555 CREATE INDEX sta_open_notification ON status USING btree (open_notification);
560 CREATE TABLE supported_os
566 CREATE UNIQUE INDEX spp_os ON supported_os (os_name);
571 CREATE SEQUENCE "type_of_issue_type_of_issue_seq";
573 CREATE TABLE "type_of_issue"
575 "type_of_issueid" bigint DEFAULT
576 nextval('"type_of_issue_type_of_issue_seq"'::text) NOT NULL,
578 "suggested_priority" text,
581 automated_check boolean,
584 recheck_interval timestamp
588 COPY "type_of_issue" FROM stdin;
589 1 manual entry 4 A manual entry of a notification t
590 2 parameter created 3 A new parameter was created t
591 3 property modified 3 The STATIC property of a parameter was modified t
592 4 parameter removed 3 A parameter was removed t
595 CREATE UNIQUE INDEX type_of_issue_type_of_issue_key ON type_of_issue USING btree (type_of_issueid);
597 CREATE UNIQUE INDEX toi_name ON type_of_issue USING btree (name);
599 CREATE INDEX toi_active ON type_of_issue USING btree (active);
601 SELECT setval ('"type_of_issue_type_of_issue_seq"', 4, true);
608 "username" text NOT NULL,
609 "active_sessionid" bigint,
610 "account_active" boolean,
611 "security_level" integer
614 CREATE UNIQUE INDEX usr_username ON usr USING btree (username);
616 CREATE UNIQUE INDEX usr_active_sessionid ON usr USING btree (active_sessionid);
618 CREATE INDEX usr_account_active ON usr USING btree (account_active);
620 CREATE INDEX usr_security_level ON usr USING btree (security_level);