1 ALTER TABLE nodes ADD COLUMN last_boot timestamp without time zone;
2 ALTER TABLE nodes ADD COLUMN last_download timestamp without time zone;
3 ALTER TABLE nodes ADD COLUMN last_pcu_reboot timestamp without time zone;
4 ALTER TABLE nodes ADD COLUMN last_pcu_confirmation timestamp without time zone;
6 ALTER TABLE pcus ADD COLUMN last_updated timestamp without time zone;
8 ALTER TABLE interfaces ADD COLUMN last_updated timestamp without time zone;
11 CREATE OR REPLACE VIEW view_nodes AS
26 CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
27 CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
28 CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact,
29 CAST(date_part('epoch', nodes.last_boot) AS bigint) AS last_boot,
30 CAST(date_part('epoch', nodes.last_download) AS bigint) AS last_download,
31 CAST(date_part('epoch', nodes.last_pcu_reboot) AS bigint) AS last_pcu_reboot,
32 CAST(date_part('epoch', nodes.last_pcu_confirmation) AS bigint) AS last_pcu_confirmation,
34 peer_node.peer_node_id,
35 COALESCE((SELECT interface_ids FROM node_interfaces
36 WHERE node_interfaces.node_id = nodes.node_id), '{}')
38 COALESCE((SELECT nodegroup_ids FROM node_nodegroups
39 WHERE node_nodegroups.node_id = nodes.node_id), '{}')
41 COALESCE((SELECT slice_ids FROM node_slices
42 WHERE node_slices.node_id = nodes.node_id), '{}')
44 COALESCE((SELECT slice_ids_whitelist FROM node_slices_whitelist
45 WHERE node_slices_whitelist.node_id = nodes.node_id), '{}')
46 AS slice_ids_whitelist,
47 COALESCE((SELECT pcu_ids FROM node_pcus
48 WHERE node_pcus.node_id = nodes.node_id), '{}')
50 COALESCE((SELECT ports FROM node_pcus
51 WHERE node_pcus.node_id = nodes.node_id), '{}')
53 COALESCE((SELECT conf_file_ids FROM node_conf_files
54 WHERE node_conf_files.node_id = nodes.node_id), '{}')
56 COALESCE((SELECT node_tag_ids FROM node_tags
57 WHERE node_tags.node_id = nodes.node_id), '{}')
59 node_session.session_id AS session
61 LEFT JOIN peer_node USING (node_id)
62 LEFT JOIN node_session USING (node_id);
64 --------------------------------------------------------------------------------
66 CREATE OR REPLACE VIEW view_pcus AS
77 CAST(date_part('epoch', pcus.last_updated) AS bigint) AS last_updated,
78 COALESCE((SELECT node_ids FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS node_ids,
79 COALESCE((SELECT ports FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS ports
83 DROP VIEW view_interfaces;
84 CREATE OR REPLACE VIEW view_interfaces AS
86 interfaces.interface_id,
88 interfaces.is_primary,
101 CAST(date_part('epoch', interfaces.last_updated) AS bigint) AS last_updated,
102 COALESCE((SELECT interface_tag_ids FROM interface_tags WHERE interface_tags.interface_id = interfaces.interface_id), '{}') AS interface_tag_ids
106 UPDATE plc_db_version SET subversion = 102;