1 ALTER TABLE nodes DROP COLUMN last_download;
2 ALTER TABLE nodes DROP COLUMN last_pcu_reboot;
3 ALTER TABLE nodes DROP COLUMN last_pcu_confirmation;
5 ALTER TABLE pcus DROP COLUMN last_updated timestamp without time zone;
7 ALTER TABLE interfaces DROP COLUMN last_updated timestamp without time zone;
10 CREATE OR REPLACE VIEW view_nodes AS
25 CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
26 CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
27 CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact,
29 peer_node.peer_node_id,
30 COALESCE((SELECT interface_ids FROM node_interfaces
31 WHERE node_interfaces.node_id = nodes.node_id), '{}')
33 COALESCE((SELECT nodegroup_ids FROM node_nodegroups
34 WHERE node_nodegroups.node_id = nodes.node_id), '{}')
36 COALESCE((SELECT slice_ids FROM node_slices
37 WHERE node_slices.node_id = nodes.node_id), '{}')
39 COALESCE((SELECT slice_ids_whitelist FROM node_slices_whitelist
40 WHERE node_slices_whitelist.node_id = nodes.node_id), '{}')
41 AS slice_ids_whitelist,
42 COALESCE((SELECT pcu_ids FROM node_pcus
43 WHERE node_pcus.node_id = nodes.node_id), '{}')
45 COALESCE((SELECT ports FROM node_pcus
46 WHERE node_pcus.node_id = nodes.node_id), '{}')
48 COALESCE((SELECT conf_file_ids FROM node_conf_files
49 WHERE node_conf_files.node_id = nodes.node_id), '{}')
51 COALESCE((SELECT node_tag_ids FROM node_tags
52 WHERE node_tags.node_id = nodes.node_id), '{}')
54 node_session.session_id AS session
56 LEFT JOIN peer_node USING (node_id)
57 LEFT JOIN node_session USING (node_id);
60 CREATE OR REPLACE VIEW view_pcus AS
63 COALESCE((SELECT node_ids FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS node_ids,
64 COALESCE((SELECT ports FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS ports
68 DROP VIEW view_interfaces;
69 CREATE OR REPLACE VIEW view_interfaces AS
71 interfaces.interface_id,
73 interfaces.is_primary,
86 COALESCE((SELECT interface_tag_ids FROM interface_tags WHERE interface_tags.interface_id = interfaces.interface_id), '{}') AS interface_tag_ids