1 ALTER TABLE nodes ADD COLUMN last_time_spent_online integer;
2 ALTER TABLE nodes ADD COLUMN last_time_spent_offline integer;
5 CREATE OR REPLACE VIEW view_nodes AS
20 CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
21 CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
22 CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact,
23 CAST(date_part('epoch', nodes.last_boot) AS bigint) AS last_boot,
24 CAST(date_part('epoch', nodes.last_download) AS bigint) AS last_download,
25 CAST(date_part('epoch', nodes.last_pcu_reboot) AS bigint) AS last_pcu_reboot,
26 CAST(date_part('epoch', nodes.last_pcu_confirmation) AS bigint) AS last_pcu_confirmation,
27 nodes.last_time_spent_online,
28 nodes.last_time_spent_offline,
30 peer_node.peer_node_id,
31 COALESCE((SELECT interface_ids FROM node_interfaces
32 WHERE node_interfaces.node_id = nodes.node_id), '{}')
34 COALESCE((SELECT nodegroup_ids FROM node_nodegroups
35 WHERE node_nodegroups.node_id = nodes.node_id), '{}')
37 COALESCE((SELECT slice_ids FROM node_slices
38 WHERE node_slices.node_id = nodes.node_id), '{}')
40 COALESCE((SELECT slice_ids_whitelist FROM node_slices_whitelist
41 WHERE node_slices_whitelist.node_id = nodes.node_id), '{}')
42 AS slice_ids_whitelist,
43 COALESCE((SELECT pcu_ids FROM node_pcus
44 WHERE node_pcus.node_id = nodes.node_id), '{}')
46 COALESCE((SELECT ports FROM node_pcus
47 WHERE node_pcus.node_id = nodes.node_id), '{}')
49 COALESCE((SELECT conf_file_ids FROM node_conf_files
50 WHERE node_conf_files.node_id = nodes.node_id), '{}')
52 COALESCE((SELECT node_tag_ids FROM node_tags
53 WHERE node_tags.node_id = nodes.node_id), '{}')
55 node_session.session_id AS session
57 LEFT JOIN peer_node USING (node_id)
58 LEFT JOIN node_session USING (node_id);
60 --------------------------------------------------------------------------------
62 UPDATE plc_db_version SET subversion = 105;