From bfd2b5d324d7348eff22c4a991fa0f21e09360f5 Mon Sep 17 00:00:00 2001
From: Gibheer <gibheer+git@zero-knowledge.org>
Date: Fri, 16 Nov 2018 10:39:21 +0100
Subject: initial commit
---
schema/20181116.sql | 78 +++++++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 78 insertions(+)
create mode 100644 schema/20181116.sql
(limited to 'schema')
diff --git a/schema/20181116.sql b/schema/20181116.sql
new file mode 100644
index 0000000..cb97acf
--- /dev/null
+++ b/schema/20181116.sql
@@ -0,0 +1,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 unique 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 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;
--
cgit v1.2.3-70-g09d2