1 DELETE FROM network_types WHERE type='ipv6';
3 ALTER TABLE interfaces ADD COLUMN gateway text;
4 ALTER TABLE interfaces ADD COLUMN dns1 text;
5 ALTER TABLE interfaces ADD COLUMN dns2 text;
6 ALTER TABLE interfaces ADD COLUMN type text;
7 ALTER TABLE interfaces ADD COLUMN ip text;
8 ALTER TABLE interfaces ADD COLUMN network text;
9 ALTER TABLE interfaces ADD COLUMN broadcast text;
10 ALTER TABLE interfaces ADD COLUMN netmask text;
12 DROP VIEW view_interfaces;
13 CREATE OR REPLACE VIEW view_interfaces AS
15 interfaces.interface_id,
17 interfaces.is_primary,
30 CAST(date_part('epoch', interfaces.last_updated) AS bigint) AS last_updated,
31 COALESCE((SELECT interface_tag_ids FROM interface_tags WHERE interface_tags.interface_id = interfaces.interface_id), '{}') AS interface_tag_ids
34 DROP VIEW view_interface_tags;
35 CREATE OR REPLACE VIEW view_interface_tags AS
37 interface_tag.interface_tag_id,
38 interface_tag.interface_id,
40 tag_types.tag_type_id,
42 tag_types.description,
46 INNER JOIN tag_types USING (tag_type_id)
47 INNER JOIN interfaces USING (interface_id);
51 CREATE OR REPLACE VIEW view_nodes AS
66 CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
67 CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
68 CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact,
69 CAST(date_part('epoch', nodes.last_boot) AS bigint) AS last_boot,
70 CAST(date_part('epoch', nodes.last_download) AS bigint) AS last_download,
71 CAST(date_part('epoch', nodes.last_pcu_reboot) AS bigint) AS last_pcu_reboot,
72 CAST(date_part('epoch', nodes.last_pcu_confirmation) AS bigint) AS last_pcu_confirmation,
73 nodes.last_time_spent_online,
74 nodes.last_time_spent_offline,
76 peer_node.peer_node_id,
77 COALESCE((SELECT interface_ids FROM node_interfaces
78 WHERE node_interfaces.node_id = nodes.node_id), '{}')
80 COALESCE((SELECT nodegroup_ids FROM node_nodegroups
81 WHERE node_nodegroups.node_id = nodes.node_id), '{}')
83 COALESCE((SELECT slice_ids FROM node_slices
84 WHERE node_slices.node_id = nodes.node_id), '{}')
86 COALESCE((SELECT slice_ids_whitelist FROM node_slices_whitelist
87 WHERE node_slices_whitelist.node_id = nodes.node_id), '{}')
88 AS slice_ids_whitelist,
89 COALESCE((SELECT pcu_ids FROM node_pcus
90 WHERE node_pcus.node_id = nodes.node_id), '{}')
92 COALESCE((SELECT ports FROM node_pcus
93 WHERE node_pcus.node_id = nodes.node_id), '{}')
95 COALESCE((SELECT conf_file_ids FROM node_conf_files
96 WHERE node_conf_files.node_id = nodes.node_id), '{}')
98 COALESCE((SELECT node_tag_ids FROM node_tags
99 WHERE node_tags.node_id = nodes.node_id), '{}')
101 node_session.session_id AS session
103 LEFT JOIN peer_node USING (node_id)
104 LEFT JOIN node_session USING (node_id);
106 ALTER TABLE interfaces DROP COLUMN if_name;
107 ALTER TABLE nodes DROP COLUMN dns;
108 ALTER TABLE nodes DROP COLUMN ntp;
110 DROP VIEW node_routes;
111 DROP VIEW view_routes;
114 DROP VIEW interface_ip_addresses;
115 DROP VIEW view_ip_addresses;
116 DROP INDEX ip_addresses_interface_id_idx;
117 DROP TABLE ip_addresses;
119 UPDATE plc_db_version SET subversion = 105;