1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
|
BEGIN;
CREATE TABLE notifier (
id serial NOT NULL primary key,
name text NOT NULL
);
CREATE TABLE groups (
id serial NOT NULL primary key,
name text NOT NULL
);
CREATE TABLE nodes (
id bigserial NOT NULL primary key,
name text NOT NULL,
updated timestamp with time zone DEFAULT now() NOT NULL,
created timestamp with time zone DEFAULT now() NOT NULL,
message text NOT NULL
);
CREATE TABLE nodes_groups (
node_id bigint not null references nodes(id) on delete cascade,
group_id int not null references groups(id) on delete cascade,
unique(node_id, group_id)
);
CREATE TABLE commands (
id serial NOT NULL primary key,
name text NOT NULL,
command text NOT NULL,
updated timestamp with time zone DEFAULT now() NOT NULL,
created timestamp with time zone DEFAULT now() NOT NULL,
message text NOT NULL
);
CREATE TABLE checks (
id bigserial NOT NULL primary key,
node_id integer references nodes(id) on delete cascade,
command_id integer references commands(id) on delete cascade,
intval interval DEFAULT '00:05:00'::interval NOT NULL,
options jsonb DEFAULT '{}'::jsonb NOT NULL,
updated timestamp with time zone DEFAULT now() NOT NULL,
last_refresh timestamp with time zone,
enabled boolean not null DEFAULT true,
notifier_id integer NOT NULL,
notify boolean DEFAULT true NOT NULL,
message text NOT NULL,
unique(node_id, command_id)
);
CREATE TABLE notifications (
id bigserial NOT NULL primary key,
check_id bigint not null references checks(id) on delete cascade,
states integer[] not null,
output text,
inserted timestamp with time zone DEFAULT now() NOT NULL,
sent timestamp with time zone
);
CREATE TABLE active_checks (
check_id bigint NOT NULL unique references checks(id) on delete cascade,
cmdline text[] NOT NULL,
next_time timestamp with time zone DEFAULT now() NOT NULL,
states integer[] DEFAULT ARRAY[0] NOT NULL,
intval interval NOT NULL,
enabled boolean NOT NULL,
notify boolean NOT NULL,
notice text
);
CREATE INDEX ON active_checks(next_time) WHERE enabled;
CREATE INDEX ON checks(updated, last_refresh nulls first);
CREATE INDEX ON checks(node_id);
CREATE INDEX ON checks(command_id);
CREATE INDEX ON notifications(inserted) WHERE sent is null;
CREATE INDEX ON notifications (check_id, inserted desc);
CREATE INDEX ON commands(updated);
CREATE INDEX ON nodes(updated);
COMMIT;
|
