X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab5.sql;h=25b3edd358ab000d901aff16a70b106dedbb10cc;hb=refs%2Fheads%2Fextensible;hp=aa79b56506646e34186271eb7dd9b5e1f55c23c3;hpb=5b3409148b4100ff8cde3636d6c68c8f07ad8a87;p=plcapi.git

diff --git a/planetlab5.sql b/planetlab5.sql
index aa79b56..25b3edd 100644
--- a/planetlab5.sql
+++ b/planetlab5.sql
@@ -111,7 +111,7 @@ CREATE INDEX persons_email_idx ON persons (email);
 --------------------------------------------------------------------------------
 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;
@@ -387,11 +387,14 @@ CREATE TABLE nodes (
     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);
@@ -457,7 +460,8 @@ CREATE TABLE interfaces (
     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);
 
@@ -523,6 +527,7 @@ interfaces.dns1,
 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;
 
@@ -662,6 +667,7 @@ CREATE TABLE pcus (
     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);
@@ -815,7 +821,7 @@ GROUP BY node_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
@@ -853,7 +859,7 @@ CREATE TABLE peers (
     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;
@@ -1165,6 +1171,10 @@ nodes.key,
 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 
@@ -1222,13 +1232,24 @@ AS nodegroup_ids
 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