X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab4.sql;h=a879d243e651ef2636716f792bd1eb37b3748f31;hb=refs%2Fheads%2Fplanetlab-4_0-branch;hp=d129086368da37c085bba73eb705b04c6c362913;hpb=7bf31c7fc9333e615b1497829f7ddc83f61fabcf;p=plcapi.git

diff --git a/planetlab4.sql b/planetlab4.sql
index d129086..a879d24 100644
--- a/planetlab4.sql
+++ b/planetlab4.sql
@@ -9,7 +9,7 @@
 --
 -- Copyright (C) 2006 The Trustees of Princeton University
 --
--- $Id: planetlab4.sql,v 1.81 2007/08/08 19:50:40 tmack Exp $
+-- $Id: planetlab4.sql 6903 2007-11-12 22:45:01Z tmack $
 --
 
 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, 2);
+INSERT INTO plc_db_version (version, subversion) VALUES (4, 9);
 
 --------------------------------------------------------------------------------
 -- Accounts
@@ -450,6 +450,67 @@ array_accum(nodenetwork_id) AS nodenetwork_ids
 FROM nodenetworks_ordered
 GROUP BY node_id;
 
+--------------------------------------------------------------------------------
+-- Nodenetwork setting types and nodenetworks settings
+--------------------------------------------------------------------------------
+
+CREATE TABLE nodenetwork_setting_types (
+    nodenetwork_setting_type_id serial PRIMARY KEY,	
+						-- Setting Type Identifier
+    name text UNIQUE NOT NULL,			-- Setting Name    
+    description text,				-- Optional Description
+    category text NOT NULL,			-- Category, e.g. Wifi, or whatever
+    min_role_id integer references roles	-- If set, minimal role required
+) WITH OIDS;
+
+CREATE TABLE nodenetwork_setting (
+    nodenetwork_setting_id serial PRIMARY KEY,	-- Nodenetwork Setting Identifier
+    nodenetwork_id integer REFERENCES nodenetworks NOT NULL,
+						-- the nodenetwork this applies to
+    nodenetwork_setting_type_id integer REFERENCES nodenetwork_setting_types NOT NULL,
+						-- the setting type
+    value text
+) WITH OIDS;
+
+CREATE OR REPLACE VIEW nodenetwork_settings AS 
+SELECT nodenetwork_id,
+array_accum(nodenetwork_setting_id) AS nodenetwork_setting_ids
+FROM nodenetwork_setting
+GROUP BY nodenetwork_id;
+
+CREATE OR REPLACE VIEW view_nodenetwork_settings AS
+SELECT
+nodenetwork_setting.nodenetwork_setting_id,
+nodenetwork_setting.nodenetwork_id,
+nodenetwork_setting_types.nodenetwork_setting_type_id,
+nodenetwork_setting_types.name,
+nodenetwork_setting_types.description,
+nodenetwork_setting_types.category,
+nodenetwork_setting_types.min_role_id,
+nodenetwork_setting.value
+FROM nodenetwork_setting
+INNER JOIN nodenetwork_setting_types USING (nodenetwork_setting_type_id);
+
+CREATE OR REPLACE VIEW view_nodenetworks AS
+SELECT
+nodenetworks.nodenetwork_id,
+nodenetworks.node_id,
+nodenetworks.is_primary,
+nodenetworks.type,
+nodenetworks.method,
+nodenetworks.ip,
+nodenetworks.mac,
+nodenetworks.gateway,
+nodenetworks.network,
+nodenetworks.broadcast,
+nodenetworks.netmask,
+nodenetworks.dns1,
+nodenetworks.dns2,
+nodenetworks.bwlimit,
+nodenetworks.hostname,
+COALESCE((SELECT nodenetwork_setting_ids FROM nodenetwork_settings WHERE nodenetwork_settings.nodenetwork_id = nodenetworks.nodenetwork_id), '{}') AS nodenetwork_setting_ids
+FROM nodenetworks;
+
 --------------------------------------------------------------------------------
 -- Power control units (PCUs)
 --------------------------------------------------------------------------------
@@ -774,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
 --------------------------------------------------------------------------------
@@ -813,7 +902,7 @@ CREATE INDEX event_object_event_id_idx ON event_object (event_id);
 CREATE INDEX event_object_object_id_idx ON event_object (object_id);
 CREATE INDEX event_object_object_type_idx ON event_object (object_type);
 
-CREATE VIEW event_objects AS
+CREATE OR REPLACE VIEW event_objects AS
 SELECT event_id,
 array_accum(object_id) AS object_ids,
 array_accum(object_type) AS object_types
@@ -823,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
@@ -840,6 +936,21 @@ COALESCE((SELECT object_ids FROM event_objects WHERE event_objects.event_id = ev
 COALESCE((SELECT object_types FROM event_objects WHERE event_objects.event_id = events.event_id), '{}') AS object_types
 FROM events;
 
+CREATE OR REPLACE VIEW view_event_objects AS 
+SELECT
+events.event_id,
+events.person_id,
+events.node_id,
+events.fault_code,
+events.call_name,
+events.call,
+events.message,
+events.runtime,
+CAST(date_part('epoch', events.time) AS bigint) AS time,
+event_object.object_id,
+event_object.object_type
+FROM events LEFT JOIN event_object USING (event_id);
+
 CREATE OR REPLACE VIEW view_persons AS
 SELECT
 persons.person_id,