From 38b48dd77b57b116f633b1cf5d68cc70a44b1ea8 Mon Sep 17 00:00:00 2001
From: root <root@colazero.tele.tg18.gathering.org>
Date: Sat, 31 Mar 2018 22:28:26 +0200
Subject: Throw TG18-stuff over the wall

---
 ansible/roles/postgres/files/schema.sql | 162 ++++++++++++++++++--------------
 1 file changed, 90 insertions(+), 72 deletions(-)

(limited to 'ansible/roles/postgres/files/schema.sql')

diff --git a/ansible/roles/postgres/files/schema.sql b/ansible/roles/postgres/files/schema.sql
index ed94e80..0be7d17 100644
--- a/ansible/roles/postgres/files/schema.sql
+++ b/ansible/roles/postgres/files/schema.sql
@@ -10,14 +10,14 @@ SET check_function_bodies = false;
 SET client_min_messages = warning;
 
 --
--- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
+-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
 --
 
 CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
 
 
 --
--- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
+-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
 --
 
 COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
@@ -44,7 +44,7 @@ SET default_tablespace = '';
 SET default_with_oids = false;
 
 --
--- Name: config; Type: TABLE; Schema: public; Owner: nms; Tablespace:
+-- Name: config; Type: TABLE; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE TABLE config (
@@ -79,11 +79,11 @@ ALTER SEQUENCE config_id_seq OWNED BY config.id;
 
 
 --
--- Name: dhcp; Type: TABLE; Schema: public; Owner: nms; Tablespace:
+-- Name: dhcp; Type: TABLE; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE TABLE dhcp (
-    switch integer,
+    network integer,
     "time" timestamp with time zone,
     mac macaddr,
     ip inet,
@@ -94,7 +94,7 @@ CREATE TABLE dhcp (
 ALTER TABLE dhcp OWNER TO nms;
 
 --
--- Name: linknet_ping; Type: TABLE; Schema: public; Owner: nms; Tablespace:
+-- Name: linknet_ping; Type: TABLE; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE TABLE linknet_ping (
@@ -108,7 +108,7 @@ CREATE TABLE linknet_ping (
 ALTER TABLE linknet_ping OWNER TO nms;
 
 --
--- Name: linknets; Type: TABLE; Schema: public; Owner: nms; Tablespace:
+-- Name: linknets; Type: TABLE; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE TABLE linknets (
@@ -146,7 +146,42 @@ ALTER SEQUENCE linknets_linknet_seq OWNED BY linknets.linknet;
 
 
 --
--- Name: oplog; Type: TABLE; Schema: public; Owner: nms; Tablespace:
+-- Name: networks; Type: TABLE; Schema: public; Owner: nms; Tablespace: 
+--
+
+CREATE TABLE networks (
+    network integer DEFAULT nextval(('"networks_network_seq"'::text)::regclass) NOT NULL,
+    name character varying NOT NULL,
+    last_updated timestamp with time zone,
+    placement box,
+    subnet4 cidr,
+    subnet6 cidr,
+    gw4 inet,
+    gw6 inet,
+    routing_point character varying,
+    vlan integer,
+    tags jsonb DEFAULT '[]'::jsonb
+);
+
+
+ALTER TABLE networks OWNER TO nms;
+
+--
+-- Name: networks_network_seq; Type: SEQUENCE; Schema: public; Owner: nms
+--
+
+CREATE SEQUENCE networks_network_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+ALTER TABLE networks_network_seq OWNER TO nms;
+
+--
+-- Name: oplog; Type: TABLE; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE TABLE oplog (
@@ -182,7 +217,7 @@ ALTER SEQUENCE oplog_id_seq OWNED BY oplog.id;
 
 
 --
--- Name: ping; Type: TABLE; Schema: public; Owner: nms; Tablespace:
+-- Name: ping; Type: TABLE; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE TABLE ping (
@@ -195,7 +230,7 @@ CREATE TABLE ping (
 ALTER TABLE ping OWNER TO nms;
 
 --
--- Name: ping_secondary_ip; Type: TABLE; Schema: public; Owner: nms; Tablespace:
+-- Name: ping_secondary_ip; Type: TABLE; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE TABLE ping_secondary_ip (
@@ -208,7 +243,7 @@ CREATE TABLE ping_secondary_ip (
 ALTER TABLE ping_secondary_ip OWNER TO nms;
 
 --
--- Name: seen_mac; Type: TABLE; Schema: public; Owner: nms; Tablespace:
+-- Name: seen_mac; Type: TABLE; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE TABLE seen_mac (
@@ -221,7 +256,7 @@ CREATE TABLE seen_mac (
 ALTER TABLE seen_mac OWNER TO nms;
 
 --
--- Name: snmp; Type: TABLE; Schema: public; Owner: nms; Tablespace:
+-- Name: snmp; Type: TABLE; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE TABLE snmp (
@@ -256,24 +291,26 @@ ALTER SEQUENCE snmp_id_seq OWNED BY snmp.id;
 
 
 --
--- Name: switches; Type: TABLE; Schema: public; Owner: nms; Tablespace:
+-- Name: switches; Type: TABLE; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE TABLE switches (
     switch integer DEFAULT nextval(('"switches_switch_seq"'::text)::regclass) NOT NULL,
     mgmt_v4_addr inet,
-    mgmt_v6_addr inet,
-    mgmt_vlan character varying,
     sysname character varying NOT NULL,
     last_updated timestamp with time zone,
     locked boolean DEFAULT false NOT NULL,
     poll_frequency interval DEFAULT '00:01:00'::interval NOT NULL,
     community character varying DEFAULT 'FullPuppTilNMS'::character varying NOT NULL,
+    mgmt_v6_addr inet,
     placement box,
     distro_name character varying,
     distro_phy_port character varying(100),
+    tags jsonb DEFAULT '[]'::jsonb,
+    deleted boolean DEFAULT false,
+    mgmt_vlan character varying,
     traffic_vlan character varying,
-    tags jsonb DEFAULT '[]'::jsonb
+    network integer
 );
 
 
@@ -293,35 +330,6 @@ CREATE SEQUENCE switches_switch_seq
 
 ALTER TABLE switches_switch_seq OWNER TO nms;
 
---
--- Name: networks; Type: TABLE; Schema: public; Owner: nms; Tablespace:
---
-
-CREATE TABLE networks (
-    network integer DEFAULT nextval(('"networks_network_seq"'::text)::regclass) NOT NULL,
-    name character varying NOT NULL,
-    last_updated timestamp with time zone,
-    placement box,
-    subnet4 cidr,
-    subnet6 cidr,
-    gw4 inet,
-    gw6 inet,
-    routing_point character varying,
-    vlan integer,
-    tags jsonb DEFAULT '[]'::jsonb
-);
-
-ALTER TABLE networks OWNER TO nms;
-
-CREATE SEQUENCE networks_network_seq
-    START WITH 1
-    INCREMENT BY 1
-    NO MINVALUE
-    NO MAXVALUE
-    CACHE 1;
-
-ALTER TABLE networks_network_seq OWNER TO nms;
-
 --
 -- Name: id; Type: DEFAULT; Schema: public; Owner: nms
 --
@@ -351,7 +359,7 @@ ALTER TABLE ONLY snmp ALTER COLUMN id SET DEFAULT nextval('snmp_id_seq'::regclas
 
 
 --
--- Name: seen_mac_pkey; Type: CONSTRAINT; Schema: public; Owner: nms; Tablespace:
+-- Name: seen_mac_pkey; Type: CONSTRAINT; Schema: public; Owner: nms; Tablespace: 
 --
 
 ALTER TABLE ONLY seen_mac
@@ -359,7 +367,7 @@ ALTER TABLE ONLY seen_mac
 
 
 --
--- Name: switches_pkey; Type: CONSTRAINT; Schema: public; Owner: nms; Tablespace:
+-- Name: switches_pkey; Type: CONSTRAINT; Schema: public; Owner: nms; Tablespace: 
 --
 
 ALTER TABLE ONLY switches
@@ -367,7 +375,7 @@ ALTER TABLE ONLY switches
 
 
 --
--- Name: switches_sysname_key; Type: CONSTRAINT; Schema: public; Owner: nms; Tablespace:
+-- Name: switches_sysname_key; Type: CONSTRAINT; Schema: public; Owner: nms; Tablespace: 
 --
 
 ALTER TABLE ONLY switches
@@ -375,7 +383,7 @@ ALTER TABLE ONLY switches
 
 
 --
--- Name: switches_sysname_key1; Type: CONSTRAINT; Schema: public; Owner: nms; Tablespace:
+-- Name: switches_sysname_key1; Type: CONSTRAINT; Schema: public; Owner: nms; Tablespace: 
 --
 
 ALTER TABLE ONLY switches
@@ -383,111 +391,103 @@ ALTER TABLE ONLY switches
 
 
 --
--- Name: dhcp_ip; Type: INDEX; Schema: public; Owner: nms; Tablespace:
+-- Name: dhcp_ip; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE INDEX dhcp_ip ON dhcp USING btree (ip);
 
 
 --
--- Name: dhcp_mac; Type: INDEX; Schema: public; Owner: nms; Tablespace:
+-- Name: dhcp_mac; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE INDEX dhcp_mac ON dhcp USING btree (mac);
 
 
 --
--- Name: dhcp_switch; Type: INDEX; Schema: public; Owner: nms; Tablespace:
+-- Name: dhcp_network; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
 
-CREATE INDEX dhcp_switch ON dhcp USING btree (switch);
+CREATE INDEX dhcp_network ON dhcp USING btree (network);
 
 
 --
--- Name: dhcp_time; Type: INDEX; Schema: public; Owner: nms; Tablespace:
+-- Name: dhcp_time; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE INDEX dhcp_time ON dhcp USING btree ("time");
 
 
 --
--- Name: ping_index; Type: INDEX; Schema: public; Owner: nms; Tablespace:
+-- Name: ping_index; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE INDEX ping_index ON ping USING btree ("time");
 
 
 --
--- Name: ping_secondary_index; Type: INDEX; Schema: public; Owner: nms; Tablespace:
+-- Name: ping_secondary_index; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE INDEX ping_secondary_index ON ping_secondary_ip USING btree ("time");
 
 
 --
--- Name: seen_mac_addr_family; Type: INDEX; Schema: public; Owner: nms; Tablespace:
+-- Name: seen_mac_addr_family; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE INDEX seen_mac_addr_family ON seen_mac USING btree (family(address));
 
 
 --
--- Name: seen_mac_seen; Type: INDEX; Schema: public; Owner: nms; Tablespace:
+-- Name: seen_mac_seen; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE INDEX seen_mac_seen ON seen_mac USING btree (seen);
 
 
 --
--- Name: snmp_time; Type: INDEX; Schema: public; Owner: nms; Tablespace:
+-- Name: snmp_time; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE INDEX snmp_time ON snmp USING btree ("time");
 
 
 --
--- Name: snmp_time15; Type: INDEX; Schema: public; Owner: nms; Tablespace:
+-- Name: snmp_time15; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE INDEX snmp_time15 ON snmp USING btree (id, switch);
 
 
 --
--- Name: snmp_time6; Type: INDEX; Schema: public; Owner: nms; Tablespace:
+-- Name: snmp_time6; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE INDEX snmp_time6 ON snmp USING btree ("time" DESC, switch);
 
 
 --
--- Name: switches_switch; Type: INDEX; Schema: public; Owner: nms; Tablespace:
+-- Name: switches_switch; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE INDEX switches_switch ON switches USING hash (switch);
 
 
 --
--- Name: updated_index2; Type: INDEX; Schema: public; Owner: nms; Tablespace:
+-- Name: updated_index2; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE INDEX updated_index2 ON linknet_ping USING btree ("time");
 
 
 --
--- Name: updated_index3; Type: INDEX; Schema: public; Owner: nms; Tablespace:
+-- Name: updated_index3; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE INDEX updated_index3 ON ping_secondary_ip USING btree ("time");
 
 
---
--- Name: dhcp_switch_fkey; Type: FK CONSTRAINT; Schema: public; Owner: nms
---
-
-ALTER TABLE ONLY dhcp
-    ADD CONSTRAINT dhcp_switch_fkey FOREIGN KEY (switch) REFERENCES switches(switch);
-
-
 --
 -- Name: snmp_switch_fkey; Type: FK CONSTRAINT; Schema: public; Owner: nms
 --
@@ -550,6 +550,24 @@ REVOKE ALL ON TABLE linknets FROM nms;
 GRANT ALL ON TABLE linknets TO nms;
 
 
+--
+-- Name: networks; Type: ACL; Schema: public; Owner: nms
+--
+
+REVOKE ALL ON TABLE networks FROM PUBLIC;
+REVOKE ALL ON TABLE networks FROM nms;
+GRANT ALL ON TABLE networks TO nms;
+
+
+--
+-- Name: oplog; Type: ACL; Schema: public; Owner: nms
+--
+
+REVOKE ALL ON TABLE oplog FROM PUBLIC;
+REVOKE ALL ON TABLE oplog FROM nms;
+GRANT ALL ON TABLE oplog TO nms;
+
+
 --
 -- Name: ping; Type: ACL; Schema: public; Owner: nms
 --
-- 
cgit v1.2.3

