From 417d931bd1c961a7aa8f1569e949bbc2a7abfade Mon Sep 17 00:00:00 2001 From: "Ole Mathias Aa. Heggem" Date: Sun, 28 Jan 2018 15:43:54 +0100 Subject: Making network first class citizen Still work in progress --- ansible/roles/postgres/files/schema.sql | 101 ++++++++++++++++++++------------ 1 file changed, 62 insertions(+), 39 deletions(-) (limited to 'ansible/roles/postgres') diff --git a/ansible/roles/postgres/files/schema.sql b/ansible/roles/postgres/files/schema.sql index 41d513d..ed94e80 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,7 +79,7 @@ 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 ( @@ -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,7 @@ ALTER SEQUENCE linknets_linknet_seq OWNED BY linknets.linknet; -- --- Name: oplog; Type: TABLE; Schema: public; Owner: nms; Tablespace: +-- Name: oplog; Type: TABLE; Schema: public; Owner: nms; Tablespace: -- CREATE TABLE oplog ( @@ -182,7 +182,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 +195,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 +208,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 +221,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,27 +256,23 @@ 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, - subnet4 cidr, - subnet6 cidr, 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, + traffic_vlan character varying, tags jsonb DEFAULT '[]'::jsonb ); @@ -297,6 +293,35 @@ 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 -- @@ -326,7 +351,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 @@ -334,7 +359,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 @@ -342,7 +367,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 @@ -350,7 +375,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 @@ -358,98 +383,98 @@ 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_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: 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"); @@ -583,5 +608,3 @@ GRANT ALL ON TABLE switches TO nms; -- -- PostgreSQL database dump complete --- - -- cgit v1.2.3