1 INSERT INTO network_types (type) VALUES ('ipv6');
3 CREATE TABLE ip_addresses (
5 ip_address_id serial PRIMARY KEY, -- Network interface identifier
6 interface_id integer REFERENCES interfaces NOT NULL, -- Which interface
7 type text REFERENCES network_types NOT NULL, -- ipv4 or ipv6
8 ip_addr text NOT NULL, -- IP address
9 netmask text NOT NULL, -- Netmask
10 last_updated timestamp without time zone -- When the interface was last updated
12 CREATE INDEX ip_addresses_interface_id_idx ON ip_addresses (interface_id);
14 CREATE OR REPLACE VIEW interface_ip_addresses AS
16 array_accum(ip_address_id) AS ip_address_ids
18 GROUP BY interface_id;
20 CREATE OR REPLACE VIEW view_ip_addresses AS
22 ip_addresses.ip_address_id,
23 ip_addresses.interface_id,
27 CAST(date_part('epoch', ip_addresses.last_updated) AS bigint) AS last_updated
30 DROP VIEW view_interfaces;
31 -- FIXME: Why was this needed?
32 -- DROP VIEW tagvalue_view_interface_id_ifname;
33 DROP VIEW view_interface_tags;
35 INSERT INTO ip_addresses (interface_id, type, ip_addr, netmask)
36 SELECT interface_id, type, ip as ip_addr, netmask FROM interfaces;
38 ALTER TABLE interfaces DROP COLUMN type;
39 ALTER TABLE interfaces DROP COLUMN ip;
40 ALTER TABLE interfaces DROP COLUMN network;
41 ALTER TABLE interfaces DROP COLUMN broadcast;
42 ALTER TABLE interfaces DROP COLUMN netmask;
43 ALTER TABLE interfaces ADD COLUMN if_name text;
45 CREATE OR REPLACE VIEW view_interfaces AS
47 interfaces.interface_id,
49 interfaces.is_primary,
55 CAST(date_part('epoch', interfaces.last_updated) AS bigint) AS last_updated,
56 COALESCE((SELECT interface_tag_ids FROM interface_tags WHERE interface_tags.interface_id = interfaces.interface_id), '{}') AS interface_tag_ids,
57 COALESCE((SELECT ip_address_ids FROM interface_ip_addresses WHERE interface_ip_addresses.interface_id = interfaces.interface_id), '{}') AS ip_address_ids
60 CREATE OR REPLACE VIEW view_interface_tags AS
62 interface_tag.interface_tag_id,
63 interface_tag.interface_id,
64 tag_types.tag_type_id,
66 tag_types.description,
70 INNER JOIN tag_types USING (tag_type_id)
71 INNER JOIN interfaces USING (interface_id);
75 route_id serial PRIMARY KEY, -- Network interface identifier
76 node_id integer REFERENCES nodes NOT NULL, -- Which node
77 subnet text NOT NULL, -- Subnet route applies to
78 next_hop text NOT NULL, -- Next hop IP address
79 interface_id integer REFERENCES interfaces NOT NULL, -- Outgoing interface
80 last_updated timestamp without time zone -- When the interface was last updated
82 CREATE INDEX routes_node_id_idx ON routes (node_id);
84 CREATE OR REPLACE VIEW view_routes AS
91 CAST(date_part('epoch', routes.last_updated) AS bigint) AS last_updated
94 CREATE OR REPLACE VIEW node_routes AS
96 array_accum(route_id) AS route_ids
100 ALTER TABLE nodes ADD COLUMN dns text;
101 ALTER TABLE nodes ADD COLUMN ntp text;
103 -- Insert the default gateways from the primary interfaces on each node as
104 -- a route with subnet=0.0.0.0/0 and next_hop=gateway
105 INSERT INTO routes (subnet, node_id, interface_id, next_hop)
106 SELECT '0.0.0.0/0' as subnet, node_id, interface_id, gateway as next_hop FROM interfaces WHERE gateway is not NULL and is_primary is true;
108 -- Set the DNS fields in the nodes table from the DNS fields in the interfaces table
109 -- for primary interfaces with one dns server
110 UPDATE nodes SET dns = dns1 FROM interfaces
111 WHERE is_primary is True and nodes.node_id = interfaces.node_id and dns1 is not NULL and dns2 is NULL;
113 -- Set the DNS fields in the nodes table from the DNS fields in the interfaces table
114 -- for primary interfaces with two dns servers
115 UPDATE nodes SET dns = dns1 || ', ' || dns2 FROM interfaces
116 WHERE is_primary is True and nodes.node_id = interfaces.node_id and dns1 is not NULL and dns2 is not NULL;
118 -- now we can drop the gateway and dns columns from interface
119 ALTER TABLE interfaces DROP COLUMN gateway;
120 ALTER TABLE interfaces DROP COLUMN dns1;
121 ALTER TABLE interfaces DROP COLUMN dns2;
123 DROP VIEW view_nodes;
124 CREATE OR REPLACE VIEW view_nodes AS
141 CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
142 CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
143 CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact,
144 CAST(date_part('epoch', nodes.last_boot) AS bigint) AS last_boot,
145 CAST(date_part('epoch', nodes.last_download) AS bigint) AS last_download,
146 CAST(date_part('epoch', nodes.last_pcu_reboot) AS bigint) AS last_pcu_reboot,
147 CAST(date_part('epoch', nodes.last_pcu_confirmation) AS bigint) AS last_pcu_confirmation,
148 nodes.last_time_spent_online,
149 nodes.last_time_spent_offline,
151 peer_node.peer_node_id,
152 COALESCE((SELECT interface_ids FROM node_interfaces
153 WHERE node_interfaces.node_id = nodes.node_id), '{}')
155 COALESCE((SELECT nodegroup_ids FROM node_nodegroups
156 WHERE node_nodegroups.node_id = nodes.node_id), '{}')
158 COALESCE((SELECT slice_ids FROM node_slices
159 WHERE node_slices.node_id = nodes.node_id), '{}')
161 COALESCE((SELECT slice_ids_whitelist FROM node_slices_whitelist
162 WHERE node_slices_whitelist.node_id = nodes.node_id), '{}')
163 AS slice_ids_whitelist,
164 COALESCE((SELECT pcu_ids FROM node_pcus
165 WHERE node_pcus.node_id = nodes.node_id), '{}')
167 COALESCE((SELECT ports FROM node_pcus
168 WHERE node_pcus.node_id = nodes.node_id), '{}')
170 COALESCE((SELECT conf_file_ids FROM node_conf_files
171 WHERE node_conf_files.node_id = nodes.node_id), '{}')
173 COALESCE((SELECT node_tag_ids FROM node_tags
174 WHERE node_tags.node_id = nodes.node_id), '{}')
176 COALESCE((SELECT route_ids FROM node_routes
177 WHERE node_routes.node_id = nodes.node_id), '{}')
179 node_session.session_id AS session
181 LEFT JOIN peer_node USING (node_id)
182 LEFT JOIN node_session USING (node_id);
184 UPDATE plc_db_version SET subversion = 106;