219abe04573f303bdd207f83f1c026235b5c38b6
[gnucomo.git] / src / database / create.sql
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 --*************************************************************************/
6 --
7 -- Gnucomo database creation script.
8 --
9 --
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.
13 --
14 --  $Log: create.sql,v $
15 --  Revision 1.11  2003-02-16 08:24:38  arjen
16 --  Added a new entry to the action table: Notification was displayed in the listing
17 --
18 --  Revision 1.10  2003/02/14 06:32:27  arjen
19 --  Setup the groups and database permissions. The three groups
20 --  are: view, ops and admin.
21 --  Added a new entry to the action table.
22 --
23 --  Revision 1.9  2003/02/13 08:46:54  arjen
24 --  Added log, notification and parameter counters to the 'object' table.
25 --  Counting these things at the time a user interface needs them is
26 --  too slow. Other programs, like gcm_daemon en gcm_input should prepare
27 --  these counters for quick retrieval.
28 --
29 --  Revision 1.8  2003/02/08 07:36:41  arjen
30 --  Added new table to the database : log_adv_daemon
31 --
32 --  Revision 1.7  2003/02/05 09:29:08  arjen
33 --  Bug fix: action.statuscode was in upper case.
34 --
35 --  Revision 1.6  2003/01/20 07:29:48  arjen
36 --  Added new tables to the database: parameter_class and parameter_notification
37 --
38 --  Revision 1.5  2003/01/18 08:46:48  arjen
39 --  Added new records to the 'type_of_issue' table.
40 --  Changed semantics of actionid 9 in the 'action' table.
41 --
42 --
43
44 CREATE SEQUENCE "action_actionid_seq";
45
46 CREATE TABLE "action"
47 (
48         "actionid" bigint DEFAULT nextval('"action_actionid_seq"'::text) NOT NULL,
49         "actionname" text,
50         "statuscode" character varying(3),
51         "description" text
52 );
53
54
55 COPY "action" FROM stdin;
56 1       Entry in the system     new     This indicates that a notification has been entered into the system.
57 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
58 3       Remarks added   pen     Remarks have been added to the notification.
59 4       Priority changed manually       pen     The priority of the notification has been changed by the user.
60 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.
61 6       Action taken    pen     An action has been taken.
62 7       Assignment to user      pen     The notification has been assigned to an user.
63 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.
64 9       Investigation completed pen     Investigation has been done. Information is available to fix the problem.
65 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.
66 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.
67 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.
68 13      Action verified cls     A check has been done and the results were approved. The notification has been closed.
69 15      SMS sent        opn     An SMS has been sent.
70 14      E-mail sent     opn     An e-mail has been sent.
71 16      Fax sent        opn     An fax has been sent.
72 17      Log entries shown       \N      The log entries relevant to the notification have been shown.
73 18      Notification closed     cls     The notification has been closed.
74 19      Notification reopend    opn     The notification has been reopend.
75 20      Redisplayed to user     \N      The notification has been presented to a user. This is not the first time
76 21      Displayed in list       \N      The notification has been presented in a list with other notifications.
77 \.
78
79 CREATE UNIQUE INDEX action_actionid_key ON "action" USING btree (actionid);
80
81 CREATE UNIQUE INDEX act_pk ON "action" USING btree (actionid);
82
83 CREATE UNIQUE INDEX act_actionname ON "action" USING btree (actionname);
84
85 CREATE INDEX act_statuscode ON "action" USING btree (statuscode);
86
87 SELECT setval ('"action_actionid_seq"', 19, true);
88
89 --
90 --
91
92 CREATE SEQUENCE "action_user_actionstepid_seq";
93
94 CREATE TABLE "action_user"
95 (
96         "actionstepid" bigint DEFAULT
97             nextval('"action_user_actionstepid_seq"'::text) NOT NULL,
98         "actionid" bigint,
99         "username" text,
100         "notificationid" bigint,
101         "timestamp" timestamp with time zone,
102         "statuscode" character varying(3),
103         "remarks" text
104 );
105
106 CREATE UNIQUE INDEX action_user_actionstepid_key ON action_user USING btree (actionstepid);
107
108 CREATE INDEX anu_actionid ON action_user USING btree (actionid);
109
110 CREATE INDEX anu_username ON action_user USING btree (username);
111
112 CREATE INDEX anu_notificationid ON action_user USING btree (notificationid);
113
114 CREATE INDEX anu_timestamp ON action_user USING btree ("timestamp");
115
116 CREATE INDEX anu_statuscode ON action_user USING btree (statuscode);
117
118 SELECT setval ('"action_user_actionstepid_seq"', 1, false);
119
120 --
121 --
122
123 CREATE TABLE "db_value"
124 (
125         "setting" text,
126         "setting_value" text
127 );
128
129
130 COPY "db_value" FROM stdin;
131 db_version      31
132 gcm_daemon_version      1
133 log_processing  0
134 last_notification       0
135 \.
136
137 --
138 --
139
140 CREATE TABLE "history"
141 (
142    paramid            bigint,
143    modified           timestamp,
144    change_nature      text,       --  CREATED, MODIFIED or REMOVED
145    changed_property   text,
146    new_value          text,
147    remark             text
148
149 );
150
151 --
152 --
153
154 CREATE SEQUENCE "log_logid_seq";
155
156 CREATE TABLE "log"
157 (
158         "logid" bigint DEFAULT nextval('"log_logid_seq"'::text) NOT NULL,
159         "objectid" bigint,
160         "original_filename" text,
161         "servicecode" text,
162         "object_timestamp" timestamp with time zone,
163         "timestamp" timestamp with time zone,
164         "rawdata" text,
165         "processed" boolean DEFAULT false,
166         "recognized" boolean DEFAULT false
167 );
168
169 CREATE UNIQUE INDEX log_logid_key ON log USING btree (logid);
170
171 CREATE INDEX log_objectid ON log USING btree (objectid);
172
173 CREATE INDEX log_original_filename ON log USING btree (original_filename);
174
175 CREATE INDEX log_servicecode ON log USING btree (servicecode);
176
177 CREATE INDEX log_object_timestmap ON log USING btree ("timestamp");
178
179 CREATE INDEX log_timestmap ON log USING btree ("timestamp");
180
181 CREATE INDEX log_timestamp ON log USING btree (object_timestamp);
182
183 CREATE INDEX log_processed ON log USING btree (processed);
184
185 SELECT setval ('"log_logid_seq"', 1, false);
186
187
188 --
189 --
190
191 CREATE SEQUENCE "log_advid_seq";
192
193
194 CREATE TABLE "log_adv"
195 (
196         "log_advid" bigint DEFAULT
197               nextval('"log_advid_seq"'::text) NOT NULL,
198         "logid" bigint NOT NULL,
199         "detailed_table"    text
200 );
201
202
203 CREATE INDEX log_adv_logid ON log_adv USING btree (logid);
204
205 CREATE UNIQUE INDEX log_adv_log_advid ON log_adv USING btree (log_advid);
206
207 SELECT setval ('"log_advid_seq"', 1, false);
208
209 CREATE TABLE log_adv_kernel_network
210 (
211        device_in         text,
212        device_out        text,
213        hw_address        text, 
214        source_ip         INET,
215        destination_ip    INET,
216        packet_length     bigint, 
217        tos_bit           text, 
218        prec_bit          text,
219        ttl               int,
220        header_id         bigint,
221        source_port       int, 
222        destination_port  int,
223        body_length       int,
224        protocol          text, 
225        body_len          int,
226        window            text,
227        urgp              int,
228        syn               boolean DEFAULT false,
229        type              int,
230        code              int,
231        sequence_number   int,
232        res               text,
233        rst               boolean,
234        df                boolean
235 ) INHERITS (log_adv);
236
237 CREATE TABLE log_adv_daemon
238 (
239        service TEXT,
240        event TEXT
241 ) INHERITS (log_adv);
242
243 CREATE INDEX log_adv_daemon_service ON log_adv_daemon (service);
244
245 /*
246 CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER '';
247 CREATE FUNCTION "funct_processlog" () RETURNS opaque AS '
248 -- Initial date: September 18th 2002
249 -- Update: November 13th 2002
250 -- Author: Brenno J.S.A.A.F. de Winter
251 -- Abstract: This routine sets a flag
252 DECLARE
253    var_value    VARCHAR;
254    var_setting  VARCHAR;
255
256 BEGIN
257
258     UPDATE db_value SET setting_value = ''TRUE'' WHERE setting = ''log_flag'';
259     RETURN NULL;
260 END;
261  ' LANGUAGE 'plpgsql';
262
263
264 CREATE TRIGGER "log_insert" AFTER INSERT ON "log"  FOR EACH ROW EXECUTE PROCEDURE "funct_processlog" ();
265 */
266 --
267 --
268
269 CREATE TABLE "log_notification"
270 (
271         "notificationid" bigint,
272         "logid" bigint
273 );
274
275 CREATE UNIQUE INDEX lon_pk ON log_notification USING btree (notificationid, logid);
276
277 CREATE UNIQUE INDEX lon_notificationid ON log_notification USING btree (notificationid);
278
279 CREATE UNIQUE INDEX lon_logid ON log_notification USING btree (logid);
280
281 --
282 --
283
284 CREATE SEQUENCE "notification_notificationid_seq";
285
286 CREATE TABLE "notification"
287 (
288         "notificationid" bigint DEFAULT
289             nextval('"notification_notificationid_seq"'::text) NOT NULL,
290         "objectid" bigint,
291         "type_of_issueid" bigint,
292         "timestamp" timestamp with time zone,
293         "statuscode" character varying(3),
294         "priority" integer,
295         "escalation_count_timestamp" timestamp with time zone,
296         "repeat_notification_timestamp" timestamp with time zone,
297         "securitylevel_view" integer,
298         "securitylevel_add" integer,
299         "securitylevel_close" integer
300 );
301
302 CREATE UNIQUE INDEX notification_notificationid_key ON notification USING btree (notificationid);
303
304 CREATE INDEX not_objectid ON notification USING btree (objectid);
305
306 CREATE INDEX not_type_of_issueid ON notification USING btree (type_of_issueid);
307
308 CREATE INDEX not_timestamp ON notification USING btree ("timestamp");
309
310 CREATE INDEX not_statuscode ON notification USING btree (statuscode);
311
312 CREATE INDEX not_priority ON notification USING btree (priority);
313
314 CREATE INDEX not_escalation_count_timestamp ON notification USING btree (escalation_count_timestamp);
315
316 CREATE INDEX not_repeat_notification_timesta ON notification USING btree (repeat_notification_timestamp);
317
318 SELECT setval ('"notification_notificationid_seq"', 1, false);
319
320 --
321 --
322
323 CREATE SEQUENCE "object_objectid_seq";
324
325 CREATE TABLE "object"
326 (
327         "objectid" bigint DEFAULT nextval('"object_objectid_seq"'::text) NOT NULL,
328         "objectname" text,
329         "objectcode" text,
330         "scp_enabled" boolean,
331         "scp_inet" inet,
332         "mail_enabled" boolean,
333         "mail_from" text,
334         "sms_enabled" boolean,
335         "sms_number" text,
336         "fax_enabled" boolean,
337         "fax_number" text,
338         "object_description" text,
339         "object_owner" text,
340         "physical_location" text,
341         "timezone" text,
342         "remark" text,
343         "os"     text,
344         "os_version"   text,
345         "log_count"    bigint,
346         "notification_count"   bigint,
347         "parameter_count"      bigint
348 );
349
350 CREATE UNIQUE INDEX object_objectid_key ON object USING btree (objectid);
351
352 CREATE UNIQUE INDEX obj_objectname ON object USING btree (objectname);
353
354 CREATE UNIQUE INDEX obj_objectcode ON object USING btree (objectcode);
355
356 CREATE INDEX obj_mail_from ON object USING btree (mail_from);
357
358 CREATE INDEX os ON object (os);
359
360 CREATE INDEX os_version ON object (os, os_version);
361
362 SELECT setval ('"object_objectid_seq"', 1, false);
363
364 --
365 --
366
367 CREATE TABLE "object_issue"
368 (
369         "objectid" bigint,
370         "type_of_issueid" bigint,
371         "default_priority" integer,
372         "escalation" boolean,
373         "escalation_time" time without time zone,
374         "max_priority" integer,
375         "adjust_setting" text
376 );
377
378 CREATE UNIQUE INDEX obj_pk ON object_issue USING btree (objectid, type_of_issueid);
379
380 CREATE INDEX obj_objectid ON object_issue USING btree (objectid);
381
382 CREATE UNIQUE INDEX obj_type_of_notificationid ON object_issue USING btree (type_of_issueid);
383
384 --
385 --
386
387 CREATE TABLE "object_priority"
388 (
389         "objectid" bigint,
390         "priorityid" integer,
391         "send_mail" boolean,
392         "send_sms" boolean,
393         "send_fax" boolean,
394         "repeat_notification" boolean,
395         "interval_for_repeat" time without time zone
396 );
397
398 CREATE UNIQUE INDEX obi_pk ON object_priority USING btree (objectid, priorityid);
399
400 CREATE INDEX obi_objectid ON object_priority USING btree (objectid);
401
402 CREATE INDEX obi_priorityid ON object_priority USING btree (priorityid);
403
404 --
405 --
406
407 CREATE TABLE "object_service"
408 (
409         "objectid" bigint,
410         "servicecode" text,
411         "expected_interval" bigint,
412         "last_entry" timestamp with time zone,
413         "default_priority" integer,
414         "maximum_priority" integer,
415         "accepted" boolean
416 );
417
418 CREATE UNIQUE INDEX obs_pk ON object_service USING btree (objectid, servicecode);
419
420 CREATE INDEX obs_objectid ON object_service USING btree (objectid);
421
422 CREATE INDEX obs_servicecode ON object_service USING btree (servicecode);
423
424 CREATE INDEX obs_accepted ON object_service USING btree (accepted);
425
426 --
427 --
428
429 CREATE TABLE "object_user"
430 (
431         "objectid" bigint,
432         "username" text,
433         "security_level" integer
434 );
435
436 CREATE UNIQUE INDEX ous_pk ON object_user USING btree (objectid, username);
437
438 CREATE INDEX ous_objectid ON object_user USING btree (objectid);
439
440 CREATE INDEX ous_username ON object_user USING btree (username);
441
442 CREATE INDEX ous_security_level ON object_user USING btree (security_level);
443
444 --
445 --
446
447 CREATE SEQUENCE "paramid_seq";
448
449 CREATE TABLE "parameter"
450 (
451         "paramid" bigint DEFAULT nextval('"paramid_seq"'::text) NOT NULL,
452         "objectid" bigint,
453         "name" text,
454         "class" text,
455         "description" text,
456
457         primary key (paramid)
458 );
459
460 CREATE UNIQUE INDEX param_obj_name ON parameter USING btree (objectid, name, class);
461
462 SELECT setval ('"paramid_seq"', 1, true);
463
464 --
465 --
466
467 CREATE TABLE "parameter_class"
468 (
469    "name"             text,     --  Name of the class: see parameter.class
470    "property_name"    text,
471    "description"      text,
472    "property_type"    text,     --  STATIC or DYNAMIC
473    "min"              float,    --  Default minimum value
474    "max"              float,    --  Default maximum value
475    "notify"           boolean,  --  Notify if something changes ?
476
477    primary key (name, property_name)
478 );
479
480 INSERT INTO parameter_class (name, property_name, description, property_type, notify)
481   VALUES ('package', 'version', 'The installed version of the package', 'STATIC', 't');
482
483 --
484 --
485
486 CREATE TABLE "parameter_notification"
487 (
488    "notificationid" bigint,
489    "paramid"        bigint,
490
491    primary key (notificationid, paramid)
492 );
493
494 --
495 --
496
497 CREATE TABLE "priority"
498 (
499         "priority" integer,
500         "send_mail" boolean,
501         "send_sms" boolean,
502         "send_fax" boolean,
503         "repeat_notification" boolean,
504         "interval_for_repeat" time without time zone
505 );
506
507
508 CREATE UNIQUE INDEX pri_pk ON priority USING btree (priority);
509
510 --
511 --
512
513 CREATE TABLE "property"
514 (
515    paramid bigint,
516    name    text,
517    value   text,
518    type    text,   --   STATIC or DYNAMIC
519    min     float,
520    max     float,
521
522    primary key (paramid, name)
523 );
524
525 --
526 --
527
528 CREATE TABLE "service"
529 (
530         "servicecode" text,
531         "servicename" text,
532         "default_priority" integer,
533         "max_priority" integer
534 );
535
536 COPY "service" FROM stdin;
537 httpd   httpd   1       5
538 imap    imap    1       5
539 imapd   imapd   1       5
540 kernel  kernel  1       5
541 sshd    sshd    1       5
542 su      su      1       5
543 syslogd syslogd 1       5
544 \.
545
546 CREATE UNIQUE INDEX ser_pk ON service USING btree (servicecode);
547
548 CREATE UNIQUE INDEX ser_servicename ON service USING btree (servicename);
549
550 --
551
552 CREATE TABLE "status"
553 (
554         "statuscode" character varying(3),
555         "statusname" text,
556         "open_notification" boolean,
557         "description" text
558 );
559
560
561 COPY "status" FROM stdin;
562 new     new entry       t       Just detected, but nothing has been done yet
563 opn     open notification       t       The notification has been displayed to a user or a user has been notified. However nothing has been done yet.
564 pen     pending t       The notification is currently being worked on.
565 ver     waiting for verification        t       The notification has been worked on and is currently awaiting the approval/verification.
566 rej     rejected        f       The notification has been identified as a false postive and was reject. The notification is now closed
567 cls     closed  f       The notification has been closed
568 inv     needs investigation     t       The notification is currently under investigation and is awaiting additional details before one can work on this again.
569 \.
570
571 CREATE UNIQUE INDEX sta_pk ON status USING btree (statuscode);
572
573 CREATE UNIQUE INDEX sta_statusname ON status USING btree (statusname);
574
575 CREATE INDEX sta_open_notification ON status USING btree (open_notification);
576
577 --
578 --
579
580 CREATE TABLE supported_os
581 (
582     os_name text,
583     remarks text
584 );
585
586 CREATE UNIQUE INDEX spp_os ON supported_os (os_name);
587
588 --
589 --
590
591 CREATE SEQUENCE "type_of_issue_type_of_issue_seq";
592
593 CREATE TABLE "type_of_issue"
594 (
595         "type_of_issueid" bigint DEFAULT
596            nextval('"type_of_issue_type_of_issue_seq"'::text) NOT NULL,
597         "name" text,
598         "suggested_priority" text,
599         "description" text,
600         "active" boolean,
601         automated_check       boolean,
602         alert_level           int,
603         last_run              timestamp,
604         recheck_interval      timestamp
605 );
606
607
608 COPY "type_of_issue" FROM stdin;
609 1       manual entry    4       A manual entry of a notification        t
610 2       parameter created       3       A new parameter was created     t
611 3       property modified       3       The STATIC property of a parameter was modified t
612 4       parameter removed       3       A parameter was removed t
613 \.
614
615 CREATE UNIQUE INDEX type_of_issue_type_of_issue_key ON type_of_issue USING btree (type_of_issueid);
616
617 CREATE UNIQUE INDEX toi_name ON type_of_issue USING btree (name);
618
619 CREATE INDEX toi_active ON type_of_issue USING btree (active);
620
621 SELECT setval ('"type_of_issue_type_of_issue_seq"', 4, true);
622
623 --
624 --
625
626 CREATE TABLE "usr"
627 (
628         "username" text NOT NULL,
629         "active_sessionid" bigint,
630         "account_active" boolean,
631         "security_level" integer
632 );
633
634 CREATE UNIQUE INDEX usr_username ON usr USING btree (username);
635
636 CREATE UNIQUE INDEX usr_active_sessionid ON usr USING btree (active_sessionid);
637
638 CREATE INDEX usr_account_active ON usr USING btree (account_active);
639
640 CREATE INDEX usr_security_level ON usr USING btree (security_level);
641
642 --
643 --  Set up user groups and grant permissions in the proper places.
644
645 CREATE GROUP view;
646 CREATE GROUP ops;
647 CREATE GROUP admin;
648
649 GRANT SELECT ON action TO GROUP view, GROUP ops, GROUP admin;
650 GRANT SELECT ON action_user TO GROUP view, GROUP ops, GROUP admin;
651 GRANT SELECT ON db_value TO GROUP view, GROUP ops, GROUP admin;
652 GRANT SELECT ON history TO GROUP view, GROUP ops, GROUP admin;
653 GRANT SELECT ON log TO GROUP view, GROUP ops, GROUP admin;
654 GRANT SELECT ON log_adv TO GROUP view, GROUP ops, GROUP admin;
655 GRANT SELECT ON log_adv_daemon TO GROUP view, GROUP ops, GROUP admin;
656 GRANT SELECT ON log_adv_kernel_network TO GROUP view, GROUP ops, GROUP admin;
657 GRANT SELECT ON log_notification TO GROUP view, GROUP ops, GROUP admin;
658 GRANT SELECT ON notification TO GROUP view, GROUP ops, GROUP admin;
659 GRANT SELECT ON object TO GROUP view, GROUP ops, GROUP admin;
660 GRANT SELECT ON object_issue TO GROUP view, GROUP ops, GROUP admin;
661 GRANT SELECT ON object_priority TO GROUP view, GROUP ops, GROUP admin;
662 GRANT SELECT ON object_service TO GROUP view, GROUP ops, GROUP admin;
663 GRANT SELECT ON object_user TO GROUP view, GROUP ops, GROUP admin;
664 GRANT SELECT ON parameter TO GROUP view, GROUP ops, GROUP admin;
665 GRANT SELECT ON parameter_class TO GROUP view, GROUP ops, GROUP admin;
666 GRANT SELECT ON parameter_notification TO GROUP view, GROUP ops, GROUP admin;
667 GRANT SELECT ON priority TO GROUP view, GROUP ops, GROUP admin;
668 GRANT SELECT ON property TO GROUP view, GROUP ops, GROUP admin;
669 GRANT SELECT ON service TO GROUP view, GROUP ops, GROUP admin;
670 GRANT SELECT ON status TO GROUP view, GROUP ops, GROUP admin;
671 GRANT SELECT ON supported_os TO GROUP view, GROUP ops, GROUP admin;
672 GRANT SELECT ON type_of_issue TO GROUP view, GROUP ops, GROUP admin;
673 GRANT SELECT ON usr TO GROUP view, GROUP ops, GROUP admin;
674
675 GRANT INSERT ON action_user TO GROUP ops, GROUP admin;
676 GRANT UPDATE ON notification TO GROUP ops, GROUP admin;
677 GRANT INSERT ON object GROUP admin;
678 GRANT UPDATE ON object TO GROUP ops, GROUP admin;
679 GRANT DELETE ON object GROUP admin;
680 GRANT UPDATE ON usr TO GROUP view, GROUP ops, GROUP admin;
681 GRANT INSERT ON usr TO GROUP admin;
682 GRANT DELETE ON usr TO GROUP admin;