--------------------------------------------------------------------------------
CREATE TABLE person_tag (
person_tag_id serial PRIMARY KEY, -- ID
- person_id integer REFERENCES persons NOT NULL, -- person id
+ person_id integer REFERENCES persons NOT NULL, -- person id
tag_type_id integer REFERENCES tag_types, -- tag type id
value text -- value attached
) WITH OIDS;
version text, -- Boot CD version string updated by Boot Manager
ssh_rsa_key text, -- SSH host key updated by Boot Manager
key text, -- Node key generated when boot file is downloaded
- verified boolean NOT NULL DEFAULT false, -- whether or not the node & pcu are verified
+ verified boolean NOT NULL DEFAULT false, -- whether or not the node & pcu are verified
-- Timestamps
date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ last_download timestamp without time zone,
+ last_pcu_reboot timestamp without time zone,
+ last_pcu_confirmation timestamp without time zone,
last_contact timestamp without time zone
) WITH OIDS;
CREATE INDEX nodes_hostname_idx ON nodes (hostname);
dns1 text, -- Primary DNS server
dns2 text, -- Secondary DNS server
bwlimit integer, -- Bandwidth limit in bps
- hostname text -- Hostname of this interface
+ hostname text, -- Hostname of this interface
+ last_updated timestamp without time zone -- When the interface was last updated
) WITH OIDS;
CREATE INDEX interfaces_node_id_idx ON interfaces (node_id);
interfaces.dns2,
interfaces.bwlimit,
interfaces.hostname,
+CAST(date_part('epoch', interfaces.last_updated) AS bigint) AS last_updated,
COALESCE((SELECT interface_tag_ids FROM interface_tags WHERE interface_tags.interface_id = interfaces.interface_id), '{}') AS interface_tag_ids
FROM interfaces;
username text, -- Username, if applicable
"password" text, -- Password, if applicable
model text, -- Model, e.g. BayTech or iPal
+ last_updated timestamp without time zone,
notes text -- Random notes
) WITH OIDS;
CREATE INDEX pcus_site_id_idx ON pcus (site_id);
-- Slice/sliver attributes
CREATE TABLE slice_tag (
- slice_tag_id serial PRIMARY KEY, -- Slice attribute identifier
+ slice_tag_id serial PRIMARY KEY, -- Slice attribute identifier
slice_id integer REFERENCES slices NOT NULL, -- Slice identifier
node_id integer REFERENCES nodes, -- Sliver attribute if set
nodegroup_id integer REFERENCES nodegroups, -- Node group attribute if set
cacert text, -- (SSL) Public certificate of peer API server
key text, -- (GPG) Public key used for authentication
shortname text, -- abbreviated name for displaying foreign objects
- hrn_root text, -- root for this peer domain
+ hrn_root text, -- root for this peer domain
deleted boolean NOT NULL DEFAULT false
) WITH OIDS;
CREATE INDEX peers_peername_idx ON peers (peername) WHERE deleted IS false;
CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact,
+CAST(date_part('epoch', nodes.last_boot) AS bigint) AS last_boot,
+CAST(date_part('epoch', nodes.last_download) AS bigint) AS last_download,
+CAST(date_part('epoch', nodes.last_pcu_reboot) AS bigint) AS last_pcu_reboot,
+CAST(date_part('epoch', nodes.last_pcu_confirmation) AS bigint) AS last_pcu_confirmation,
peer_node.peer_id,
peer_node.peer_node_id,
COALESCE((SELECT interface_ids FROM node_interfaces
FROM conf_files;
--------------------------------------------------------------------------------
+DROP VIEW view_pcus;
CREATE OR REPLACE VIEW view_pcus AS
SELECT
-pcus.*,
+pcus.pcu_id,
+pcus.site_id,
+pcus.hostname,
+pcus.ip,
+pcus.protocol,
+pcus.username,
+pcus.password,
+pcus.model,
+pcus.notes,
+CAST(date_part('epoch', pcus.last_updated) AS bigint) AS last_updated,
COALESCE((SELECT node_ids FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS node_ids,
COALESCE((SELECT ports FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS ports
FROM pcus;
+
--------------------------------------------------------------------------------
CREATE OR REPLACE VIEW view_sites AS
SELECT