Setting tag plcapi-5.4-2

[plcapi.git] / planetlab5.sql

diff --git a/planetlab5.sql b/planetlab5.sql
index 522b8fd..95eb02b 100644 (file)
--- a/planetlab5.sql
+++ b/planetlab5.sql
@@ -12,9 +12,6 @@
 --
 -- NOTE: this file was first created for version 4.3, the filename might be confusing
 --
--- $Id$
--- $URL$
---
 
 SET client_encoding = 'UNICODE';
 
@@ -72,6 +69,8 @@ CREATE TABLE tag_types (
     tag_type_id serial PRIMARY KEY,                    -- ID
     tagname text UNIQUE NOT NULL,                      -- Tag Name
     description text,                                  -- Optional Description
+-- this is deprecated -- see migrations/104*
+-- starting with subversion 104, a tag type has a SET OF roles attached to it
     min_role_id integer REFERENCES roles DEFAULT 10,   -- set minimal role required
     category text NOT NULL DEFAULT 'general'           -- Free text for grouping tags together
 ) WITH OIDS;
@@ -392,6 +391,9 @@ CREATE TABLE nodes (
     -- 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 +459,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 +526,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 +666,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);
@@ -835,7 +840,7 @@ CREATE TABLE initscripts (
     initscript_id serial PRIMARY KEY,                  -- Initscript identifier
     name text NOT NULL,                                        -- Initscript name
     enabled bool NOT NULL DEFAULT true,                        -- Initscript is active
-    script text NOT NULL,                              -- Initscript body
+    script text NOT NULL,                              -- Initscript code
     UNIQUE (name)
 ) WITH OIDS;
 CREATE INDEX initscripts_name_idx ON initscripts (name);
@@ -1165,6 +1170,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 +1231,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