From 56a5987b760a822784bb53a3c27f30afec9f6a82 Mon Sep 17 00:00:00 2001
From: Kristian Lyngstol <kly@kly.no>
Date: Sun, 20 Mar 2016 14:09:25 +0000
Subject: NMS: Update schema with recent dhcp-changes

---
 nms/nms-dump.sql | 41 +++++++++++++++++++++--------------------
 1 file changed, 21 insertions(+), 20 deletions(-)

(limited to 'nms/nms-dump.sql')

diff --git a/nms/nms-dump.sql b/nms/nms-dump.sql
index bbe141d..335a3b0 100644
--- a/nms/nms-dump.sql
+++ b/nms/nms-dump.sql
@@ -44,17 +44,18 @@ SET default_tablespace = '';
 SET default_with_oids = false;
 
 --
--- Name: dhcp; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
+-- Name: dhcp; Type: TABLE; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE TABLE dhcp (
     switch integer,
     "time" timestamp without time zone,
-    mac macaddr
+    mac macaddr,
+    ip inet
 );
 
 
-ALTER TABLE dhcp OWNER TO postgres;
+ALTER TABLE dhcp OWNER TO nms;
 
 --
 -- Name: linknet_ping; Type: TABLE; Schema: public; Owner: nms; Tablespace: 
@@ -162,7 +163,7 @@ CREATE TABLE seen_mac (
 ALTER TABLE seen_mac OWNER TO nms;
 
 --
--- Name: snmp; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
+-- Name: snmp; Type: TABLE; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE TABLE snmp (
@@ -176,7 +177,7 @@ CREATE TABLE snmp (
 ALTER TABLE snmp OWNER TO nms;
 
 --
--- Name: snmp_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
+-- Name: snmp_id_seq; Type: SEQUENCE; Schema: public; Owner: nms
 --
 
 CREATE SEQUENCE snmp_id_seq
@@ -190,7 +191,7 @@ CREATE SEQUENCE snmp_id_seq
 ALTER TABLE snmp_id_seq OWNER TO nms;
 
 --
--- Name: snmp_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
+-- Name: snmp_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: nms
 --
 
 ALTER SEQUENCE snmp_id_seq OWNED BY snmp.id;
@@ -292,7 +293,7 @@ ALTER TABLE ONLY linknets ALTER COLUMN linknet SET DEFAULT nextval('linknets_lin
 
 
 --
--- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
+-- Name: id; Type: DEFAULT; Schema: public; Owner: nms
 --
 
 ALTER TABLE ONLY snmp ALTER COLUMN id SET DEFAULT nextval('snmp_id_seq'::regclass);
@@ -354,14 +355,14 @@ ALTER TABLE ONLY switches
 
 
 --
--- Name: dhcp_switch; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
+-- Name: dhcp_switch; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE INDEX dhcp_switch ON dhcp USING btree (switch);
 
 
 --
--- Name: dhcp_time; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
+-- Name: dhcp_time; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE INDEX dhcp_time ON dhcp USING btree ("time");
@@ -417,21 +418,21 @@ CREATE INDEX seen_mac_seen ON seen_mac USING btree (seen);
 
 
 --
--- Name: snmp_time; Type: INDEX; Schema: public; Owner: postgres; 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: postgres; 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: postgres; Tablespace: 
+-- Name: snmp_time6; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
 
 CREATE INDEX snmp_time6 ON snmp USING btree ("time" DESC, switch);
@@ -466,7 +467,7 @@ CREATE INDEX updated_index3 ON ping_secondary_ip USING btree ("time");
 
 
 --
--- Name: dhcp_switch_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+-- Name: dhcp_switch_fkey; Type: FK CONSTRAINT; Schema: public; Owner: nms
 --
 
 ALTER TABLE ONLY dhcp
@@ -474,7 +475,7 @@ ALTER TABLE ONLY dhcp
 
 
 --
--- Name: snmp_switch_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+-- Name: snmp_switch_fkey; Type: FK CONSTRAINT; Schema: public; Owner: nms
 --
 
 ALTER TABLE ONLY snmp
@@ -525,23 +526,23 @@ GRANT ALL ON TABLE seen_mac TO nms;
 
 
 --
--- Name: snmp; Type: ACL; Schema: public; Owner: postgres
+-- Name: snmp; Type: ACL; Schema: public; Owner: nms
 --
 
 REVOKE ALL ON TABLE snmp FROM PUBLIC;
-REVOKE ALL ON TABLE snmp FROM postgres;
-GRANT ALL ON TABLE snmp TO postgres;
+REVOKE ALL ON TABLE snmp FROM nms;
 GRANT ALL ON TABLE snmp TO nms;
+GRANT ALL ON TABLE snmp TO postgres;
 
 
 --
--- Name: snmp_id_seq; Type: ACL; Schema: public; Owner: postgres
+-- Name: snmp_id_seq; Type: ACL; Schema: public; Owner: nms
 --
 
 REVOKE ALL ON SEQUENCE snmp_id_seq FROM PUBLIC;
-REVOKE ALL ON SEQUENCE snmp_id_seq FROM postgres;
-GRANT ALL ON SEQUENCE snmp_id_seq TO postgres;
+REVOKE ALL ON SEQUENCE snmp_id_seq FROM nms;
 GRANT ALL ON SEQUENCE snmp_id_seq TO nms;
+GRANT ALL ON SEQUENCE snmp_id_seq TO postgres;
 
 
 --
-- 
cgit v1.2.3


From 10493f224f11bcf1dac3b509c3e440cf63fe39fc Mon Sep 17 00:00:00 2001
From: Kristian Lyngstol <kly@kly.no>
Date: Sat, 26 Mar 2016 03:59:37 +0100
Subject: NMS: Dump updated schema

It needs cleaning, obviously, but at least it's there.
---
 nms/nms-dump.sql | 149 ++++++++++++++++++++++++++++++++++++++++++++++++++++---
 1 file changed, 142 insertions(+), 7 deletions(-)

(limited to 'nms/nms-dump.sql')

diff --git a/nms/nms-dump.sql b/nms/nms-dump.sql
index 335a3b0..25ba3ac 100644
--- a/nms/nms-dump.sql
+++ b/nms/nms-dump.sql
@@ -49,7 +49,7 @@ SET default_with_oids = false;
 
 CREATE TABLE dhcp (
     switch integer,
-    "time" timestamp without time zone,
+    "time" timestamp with time zone,
     mac macaddr,
     ip inet
 );
@@ -78,9 +78,11 @@ ALTER TABLE linknet_ping OWNER TO nms;
 CREATE TABLE linknets (
     linknet integer NOT NULL,
     switch1 integer NOT NULL,
-    addr1 inet NOT NULL,
+    addr1 inet,
     switch2 integer NOT NULL,
-    addr2 inet NOT NULL
+    addr2 inet,
+    port1 character varying(10),
+    port2 character varying(10)
 );
 
 
@@ -253,19 +255,26 @@ ALTER TABLE switch_temp OWNER TO nms;
 
 CREATE TABLE switches (
     switch integer DEFAULT nextval(('"switches_switch_seq"'::text)::regclass) NOT NULL,
-    ip inet,
+    mgmt_v4_addr inet,
     sysname character varying NOT NULL,
     switchtype character varying DEFAULT 'ex2200'::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 'public'::character varying NOT NULL,
+    community character varying DEFAULT 'FullPuppTilNMS'::character varying NOT NULL,
     lldp_chassis_id character varying,
-    secondary_ip inet,
+    mgmt_v6_addr inet,
     placement box,
     subnet4 cidr,
     subnet6 cidr,
-    distro character varying
+    distro_name character varying,
+    distro_phy_port character varying(100),
+    mgmt_v6_gw inet,
+    mgmt_v4_gw inet,
+    mgmt_vlan integer DEFAULT 666,
+    traffic_vlan integer,
+    last_config_fetch timestamp with time zone,
+    current_mac macaddr
 );
 
 
@@ -285,6 +294,17 @@ CREATE SEQUENCE switches_switch_seq
 
 ALTER TABLE switches_switch_seq OWNER TO nms;
 
+--
+-- Name: test_table; Type: TABLE; Schema: public; Owner: nms; Tablespace: 
+--
+
+CREATE TABLE test_table (
+    test timestamp with time zone
+);
+
+
+ALTER TABLE test_table OWNER TO nms;
+
 --
 -- Name: linknet; Type: DEFAULT; Schema: public; Owner: nms
 --
@@ -354,6 +374,20 @@ ALTER TABLE ONLY switches
     ADD CONSTRAINT switches_sysname_key1 UNIQUE (sysname);
 
 
+--
+-- 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: 
+--
+
+CREATE INDEX dhcp_mac ON dhcp USING btree (mac);
+
+
 --
 -- Name: dhcp_switch; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
@@ -375,6 +409,13 @@ CREATE INDEX dhcp_time ON dhcp USING btree ("time");
 CREATE INDEX ping_index ON ping USING btree ("time");
 
 
+--
+-- Name: ping_secondary_index; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
+--
+
+CREATE INDEX ping_secondary_index ON ping_secondary_ip USING btree ("time");
+
+
 --
 -- Name: polls_ifname; Type: INDEX; Schema: public; Owner: nms; Tablespace: 
 --
@@ -514,6 +555,67 @@ REVOKE ALL ON SCHEMA public FROM PUBLIC;
 REVOKE ALL ON SCHEMA public FROM postgres;
 GRANT ALL ON SCHEMA public TO postgres;
 GRANT ALL ON SCHEMA public TO PUBLIC;
+GRANT ALL ON SCHEMA public TO fap;
+
+
+--
+-- Name: dhcp; Type: ACL; Schema: public; Owner: nms
+--
+
+REVOKE ALL ON TABLE dhcp FROM PUBLIC;
+REVOKE ALL ON TABLE dhcp FROM nms;
+GRANT ALL ON TABLE dhcp TO nms;
+GRANT ALL ON TABLE dhcp TO fap;
+
+
+--
+-- Name: linknet_ping; Type: ACL; Schema: public; Owner: nms
+--
+
+REVOKE ALL ON TABLE linknet_ping FROM PUBLIC;
+REVOKE ALL ON TABLE linknet_ping FROM nms;
+GRANT ALL ON TABLE linknet_ping TO nms;
+GRANT ALL ON TABLE linknet_ping TO fap;
+
+
+--
+-- Name: linknets; Type: ACL; Schema: public; Owner: nms
+--
+
+REVOKE ALL ON TABLE linknets FROM PUBLIC;
+REVOKE ALL ON TABLE linknets FROM nms;
+GRANT ALL ON TABLE linknets TO nms;
+GRANT ALL ON TABLE linknets TO fap;
+
+
+--
+-- Name: ping; Type: ACL; Schema: public; Owner: nms
+--
+
+REVOKE ALL ON TABLE ping FROM PUBLIC;
+REVOKE ALL ON TABLE ping FROM nms;
+GRANT ALL ON TABLE ping TO nms;
+GRANT ALL ON TABLE ping TO fap;
+
+
+--
+-- Name: ping_secondary_ip; Type: ACL; Schema: public; Owner: nms
+--
+
+REVOKE ALL ON TABLE ping_secondary_ip FROM PUBLIC;
+REVOKE ALL ON TABLE ping_secondary_ip FROM nms;
+GRANT ALL ON TABLE ping_secondary_ip TO nms;
+GRANT ALL ON TABLE ping_secondary_ip TO fap;
+
+
+--
+-- Name: polls; Type: ACL; Schema: public; Owner: nms
+--
+
+REVOKE ALL ON TABLE polls FROM PUBLIC;
+REVOKE ALL ON TABLE polls FROM nms;
+GRANT ALL ON TABLE polls TO nms;
+GRANT ALL ON TABLE polls TO fap;
 
 
 --
@@ -523,6 +625,7 @@ GRANT ALL ON SCHEMA public TO PUBLIC;
 REVOKE ALL ON TABLE seen_mac FROM PUBLIC;
 REVOKE ALL ON TABLE seen_mac FROM nms;
 GRANT ALL ON TABLE seen_mac TO nms;
+GRANT ALL ON TABLE seen_mac TO fap;
 
 
 --
@@ -533,6 +636,7 @@ REVOKE ALL ON TABLE snmp FROM PUBLIC;
 REVOKE ALL ON TABLE snmp FROM nms;
 GRANT ALL ON TABLE snmp TO nms;
 GRANT ALL ON TABLE snmp TO postgres;
+GRANT ALL ON TABLE snmp TO fap;
 
 
 --
@@ -545,6 +649,26 @@ GRANT ALL ON SEQUENCE snmp_id_seq TO nms;
 GRANT ALL ON SEQUENCE snmp_id_seq TO postgres;
 
 
+--
+-- Name: switch_comments; Type: ACL; Schema: public; Owner: nms
+--
+
+REVOKE ALL ON TABLE switch_comments FROM PUBLIC;
+REVOKE ALL ON TABLE switch_comments FROM nms;
+GRANT ALL ON TABLE switch_comments TO nms;
+GRANT ALL ON TABLE switch_comments TO fap;
+
+
+--
+-- Name: switch_temp; Type: ACL; Schema: public; Owner: nms
+--
+
+REVOKE ALL ON TABLE switch_temp FROM PUBLIC;
+REVOKE ALL ON TABLE switch_temp FROM nms;
+GRANT ALL ON TABLE switch_temp TO nms;
+GRANT ALL ON TABLE switch_temp TO fap;
+
+
 --
 -- Name: switches; Type: ACL; Schema: public; Owner: nms
 --
@@ -552,6 +676,17 @@ GRANT ALL ON SEQUENCE snmp_id_seq TO postgres;
 REVOKE ALL ON TABLE switches FROM PUBLIC;
 REVOKE ALL ON TABLE switches FROM nms;
 GRANT ALL ON TABLE switches TO nms;
+GRANT ALL ON TABLE switches TO fap;
+
+
+--
+-- Name: test_table; Type: ACL; Schema: public; Owner: nms
+--
+
+REVOKE ALL ON TABLE test_table FROM PUBLIC;
+REVOKE ALL ON TABLE test_table FROM nms;
+GRANT ALL ON TABLE test_table TO nms;
+GRANT ALL ON TABLE test_table TO fap;
 
 
 --
-- 
cgit v1.2.3

