X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab5.sql;h=95eb02b37d666342040a7504ff0a1fa70e8b19a7;hb=refs%2Fheads%2Fremove-xmlrpc;hp=522b8fd898289e5e3781050b8878fe6a79779009;hpb=c652f3ad9decac58ea08c6b8e6345f70a34399f4;p=plcapi.git

diff --git a/planetlab5.sql b/planetlab5.sql
index 522b8fd..95eb02b 100644
--- 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