X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab4.sql;h=8bfb42a37bbaccbc3f20120d7f2bb6fd9d43b7fd;hb=609e48bac0b714c49641b771cf9c29aaf7c7771a;hp=555ee288b7840688f1e872920bbc355592c391fa;hpb=1600712a2312d824ade8c8a688e1dee9a426a68e;p=plcapi.git

diff --git a/planetlab4.sql b/planetlab4.sql
index 555ee28..8bfb42a 100644
--- a/planetlab4.sql
+++ b/planetlab4.sql
@@ -9,7 +9,7 @@
 --
 -- Copyright (C) 2006 The Trustees of Princeton University
 --
--- $Id: planetlab4.sql 793 2007-08-28 15:21:17Z thierry $
+-- $Id$
 --
 
 SET client_encoding = 'UNICODE';
@@ -37,7 +37,7 @@ CREATE TABLE plc_db_version (
     subversion integer NOT NULL DEFAULT 0
 ) WITH OIDS;
 
-INSERT INTO plc_db_version (version, subversion) VALUES (4, 8);
+INSERT INTO plc_db_version (version, subversion) VALUES (4, 9);
 
 --------------------------------------------------------------------------------
 -- Accounts
@@ -835,6 +835,34 @@ CREATE TABLE node_session (
     UNIQUE (session_id) -- Sessions are unique
 ) WITH OIDS;
 
+
+
+-------------------------------------------------------------------------------
+-- PCU Types
+------------------------------------------------------------------------------
+CREATE TABLE pcu_types (
+    pcu_type_id serial PRIMARY KEY,
+    model text NOT NULL ,               -- PCU model name
+    name text                           -- Full PCU model name
+) WITH OIDS;
+CREATE INDEX pcu_types_model_idx ON pcu_types (model);
+
+CREATE TABLE pcu_protocol_type (
+    pcu_protocol_type_id serial PRIMARY KEY,
+    pcu_type_id integer REFERENCES pcu_types NOT NULL,  -- PCU type identifier
+    port integer NOT NULL,                              -- PCU port
+    protocol text NOT NULL,                             -- Protocol
+    supported boolean NOT NULL DEFAULT True             -- Does PLC support
+) WITH OIDS;
+CREATE INDEX pcu_protocol_type_pcu_type_id ON pcu_protocol_type (pcu_type_id);
+
+
+CREATE OR REPLACE VIEW pcu_protocol_types AS
+SELECT pcu_type_id,
+array_accum(pcu_protocol_type_id) as pcu_protocol_type_ids
+FROM pcu_protocol_type
+GROUP BY pcu_type_id;
+
 --------------------------------------------------------------------------------
 -- Message templates
 --------------------------------------------------------------------------------
@@ -884,6 +912,13 @@ GROUP BY event_id;
 --------------------------------------------------------------------------------
 -- Useful views
 --------------------------------------------------------------------------------
+CREATE OR REPLACE VIEW view_pcu_types AS
+SELECT
+pcu_types.pcu_type_id,
+pcu_types.model,
+pcu_types.name,
+COALESCE((SELECT pcu_protocol_type_ids FROM pcu_protocol_types WHERE pcu_protocol_types.pcu_type_id = pcu_types.pcu_type_id), '{}') AS pcu_protocol_type_ids
+FROM pcu_types;
 
 CREATE OR REPLACE VIEW view_events AS
 SELECT