From 0bd0445cfd697ee373f9d923a3002be648a584c7 Mon Sep 17 00:00:00 2001
From: Kristian Lyngstol <kly@kly.no>
Date: Wed, 13 Feb 2019 21:44:32 +0100
Subject: Add basic collector-implementation

References #195

This is the first dumb-ass implementation of the collector-api.

It actually works fine for write AS IS, but has 0 safties. But I'm thinking the
API itself is reasonably stable and ready for testing.

I will need to extend it for dhcp-specific stuff, and the plan is to
create a secondary table with metadata (hidden from the user) and a few
other nice things.

Also, indexes. We need indexes. Obviously.
---
 build/schema.sql | 113 +++++++++++++++++++++++++++++++++++++++++++++++++++++--
 1 file changed, 109 insertions(+), 4 deletions(-)

(limited to 'build/schema.sql')

diff --git a/build/schema.sql b/build/schema.sql
index 9288098..d40bad1 100644
--- a/build/schema.sql
+++ b/build/schema.sql
@@ -149,6 +149,20 @@ ALTER TABLE public.linknets_linknet_seq OWNER TO nms;
 ALTER SEQUENCE public.linknets_linknet_seq OWNED BY public.linknets.linknet;
 
 
+--
+-- Name: metrics; Type: TABLE; Schema: public; Owner: postgres
+--
+
+CREATE TABLE public.metrics (
+    ts timestamp with time zone DEFAULT now(),
+    src text,
+    metadata jsonb,
+    data jsonb
+);
+
+
+ALTER TABLE public.metrics OWNER TO postgres;
+
 --
 -- Name: networks; Type: TABLE; Schema: public; Owner: nms
 --
@@ -447,6 +461,13 @@ CREATE INDEX dhcp_network ON public.dhcp USING btree (network);
 CREATE INDEX dhcp_time ON public.dhcp USING btree ("time");
 
 
+--
+-- Name: ping_brin_time; Type: INDEX; Schema: public; Owner: nms
+--
+
+CREATE INDEX ping_brin_time ON public.ping USING brin ("time");
+
+
 --
 -- Name: ping_index; Type: INDEX; Schema: public; Owner: nms
 --
@@ -461,6 +482,20 @@ CREATE INDEX ping_index ON public.ping USING btree ("time");
 CREATE INDEX ping_secondary_index ON public.ping_secondary_ip USING btree ("time");
 
 
+--
+-- Name: ping_switch_time_btree; Type: INDEX; Schema: public; Owner: nms
+--
+
+CREATE INDEX ping_switch_time_btree ON public.ping USING btree (switch, "time");
+
+
+--
+-- Name: ping_switch_time_unique_btree; Type: INDEX; Schema: public; Owner: nms
+--
+
+CREATE UNIQUE INDEX ping_switch_time_unique_btree ON public.ping USING btree (switch, "time");
+
+
 --
 -- Name: seen_mac_addr_family; Type: INDEX; Schema: public; Owner: nms
 --
@@ -476,17 +511,66 @@ CREATE INDEX seen_mac_seen ON public.seen_mac USING btree (seen);
 
 
 --
--- Name: snmp_time; Type: INDEX; Schema: public; Owner: nms
+-- Name: snmp_brin_switch_time; Type: INDEX; Schema: public; Owner: nms
 --
 
-CREATE INDEX snmp_time ON public.snmp USING btree ("time");
+CREATE INDEX snmp_brin_switch_time ON public.snmp USING brin (switch, "time");
+
+
+--
+-- Name: snmp_id_desc_btree; Type: INDEX; Schema: public; Owner: nms
+--
+
+CREATE INDEX snmp_id_desc_btree ON public.snmp USING btree (id DESC);
 
 
 --
--- Name: snmp_time15; Type: INDEX; Schema: public; Owner: nms
+-- Name: snmp_id_desc_switch_btree; Type: INDEX; Schema: public; Owner: nms
 --
 
-CREATE INDEX snmp_time15 ON public.snmp USING btree (id, switch);
+CREATE INDEX snmp_id_desc_switch_btree ON public.snmp USING btree (id DESC, switch);
+
+
+--
+-- Name: snmp_id_switch_btree; Type: INDEX; Schema: public; Owner: nms
+--
+
+CREATE INDEX snmp_id_switch_btree ON public.snmp USING btree (id, switch);
+
+
+--
+-- Name: snmp_switch_btree; Type: INDEX; Schema: public; Owner: nms
+--
+
+CREATE INDEX snmp_switch_btree ON public.snmp USING btree (switch);
+
+
+--
+-- Name: snmp_switch_id_btree; Type: INDEX; Schema: public; Owner: nms
+--
+
+CREATE INDEX snmp_switch_id_btree ON public.snmp USING btree (switch, id);
+
+
+--
+-- Name: snmp_switch_id_desc_btree; Type: INDEX; Schema: public; Owner: nms
+--
+
+CREATE INDEX snmp_switch_id_desc_btree ON public.snmp USING btree (switch, id DESC);
+
+
+--
+-- Name: snmp_switch_time_btree; Type: INDEX; Schema: public; Owner: nms
+--
+
+CREATE INDEX snmp_switch_time_btree ON public.snmp USING btree (switch, "time");
+
+
+--
+-- Name: snmp_time; Type: INDEX; Schema: public; Owner: nms
+--
+
+CREATE INDEX snmp_time ON public.snmp USING btree ("time");
 
 
 --
@@ -496,6 +580,20 @@ CREATE INDEX snmp_time15 ON public.snmp USING btree (id, switch);
 CREATE INDEX snmp_time6 ON public.snmp USING btree ("time" DESC, switch);
 
 
+--
+-- Name: snmp_time_brin; Type: INDEX; Schema: public; Owner: nms
+--
+
+CREATE INDEX snmp_time_brin ON public.snmp USING brin ("time");
+
+
+--
+-- Name: snmp_unique_switch_time_btree; Type: INDEX; Schema: public; Owner: nms
+--
+
+CREATE UNIQUE INDEX snmp_unique_switch_time_btree ON public.snmp USING btree (switch, "time");
+
+
 --
 -- Name: switches_switch; Type: INDEX; Schema: public; Owner: nms
 --
@@ -569,6 +667,13 @@ GRANT ALL ON TABLE public.linknet_ping TO dhcptail;
 GRANT ALL ON TABLE public.linknets TO dhcptail;
 
 
+--
+-- Name: TABLE metrics; Type: ACL; Schema: public; Owner: postgres
+--
+
+GRANT ALL ON TABLE public.metrics TO nms;
+
+
 --
 -- Name: TABLE networks; Type: ACL; Schema: public; Owner: nms
 --
-- 
cgit v1.2.3