--
-- Copyright (C) 2006 The Trustees of Princeton University
--
--- $Id: planetlab4.sql,v 1.79 2007/07/12 17:48:19 tmack Exp $
+-- $Id: planetlab4.sql 6903 2007-11-12 22:45:01Z tmack $
--
SET client_encoding = 'UNICODE';
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
SELECT site_id,
array_accum(node_id) AS node_ids
FROM nodes
+WHERE deleted IS false
GROUP BY site_id;
--- slice whitelist on nodes
-CREATE TABLE node_slice_whitelist (
- node_id integer REFERENCES nodes NOT NULL, -- Node id of whitelist
- slice_id integer REFERENCES slices NOT NULL, -- Slice id thats allowd on this node
- PRIMARY KEY (node_id, slice_id)
-) WITH OIDS;
-CREATE INDEX node_slice_whitelist_node_id_idx ON node_slice_whitelist (node_id);
-CREATE INDEX node_slice_whitelist_slice_id_idx ON node_slice_whitelist (slice_id);
-
--- Slices on each node
-CREATE VIEW node_slices_whitelist AS
-SELECT node_id,
-array_accum(slice_id) AS slice_ids_whitelist
-FROM node_slice_whitelist
-GROUP BY node_id;
-
--------------------------------------------------------------------------------
-- Node groups
--------------------------------------------------------------------------------
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)
--------------------------------------------------------------------------------
FROM slice_person
GROUP BY person_id;
+--------------------------------------------------------------------------------
+-- Slice whitelist
+--------------------------------------------------------------------------------
+-- slice whitelist on nodes
+CREATE TABLE node_slice_whitelist (
+ node_id integer REFERENCES nodes NOT NULL, -- Node id of whitelist
+ slice_id integer REFERENCES slices NOT NULL, -- Slice id thats allowd on this node
+ PRIMARY KEY (node_id, slice_id)
+) WITH OIDS;
+CREATE INDEX node_slice_whitelist_node_id_idx ON node_slice_whitelist (node_id);
+CREATE INDEX node_slice_whitelist_slice_id_idx ON node_slice_whitelist (slice_id);
+
+-- Slices on each node
+CREATE VIEW node_slices_whitelist AS
+SELECT node_id,
+array_accum(slice_id) AS slice_ids_whitelist
+FROM node_slice_whitelist
+GROUP BY node_id;
+
--------------------------------------------------------------------------------
-- Slice attributes
--------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------
-- 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
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,
git://git.onelab.eu / plcapi.git / blobdiff