This commit was manufactured by cvs2svn to create branch

[plcapi.git] / planetlab4.sql

diff --git a/planetlab4.sql b/planetlab4.sql
index 0b5c819..d129086 100644 (file)
--- a/planetlab4.sql
+++ b/planetlab4.sql
@@ -9,7 +9,7 @@
 --
 -- Copyright (C) 2006 The Trustees of Princeton University
 --
--- $Id$
+-- $Id: planetlab4.sql,v 1.81 2007/08/08 19:50:40 tmack Exp $
 --
 
 SET client_encoding = 'UNICODE';
@@ -293,6 +293,7 @@ CREATE VIEW site_nodes AS
 SELECT site_id,
 array_accum(node_id) AS node_ids
 FROM nodes
+WHERE deleted IS false
 GROUP BY site_id;
 
 --------------------------------------------------------------------------------
@@ -509,6 +510,7 @@ CREATE TABLE slice_instantiations (
 INSERT INTO slice_instantiations (instantiation) VALUES ('not-instantiated'); -- Placeholder slice
 INSERT INTO slice_instantiations (instantiation) VALUES ('plc-instantiated'); -- Instantiated by Node Manager
 INSERT INTO slice_instantiations (instantiation) VALUES ('delegated'); -- Manually instantiated
+INSERT INTO slice_instantiations (instantiation) VALUES ('nm-controller'); -- NM Controller
 
 -- Slices
 CREATE TABLE slices (
@@ -589,6 +591,25 @@ array_accum(slice_id) AS slice_ids
 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
 --------------------------------------------------------------------------------
@@ -606,11 +627,13 @@ CREATE TABLE slice_attribute (
     slice_attribute_id serial PRIMARY KEY, -- Slice attribute identifier
     slice_id integer REFERENCES slices NOT NULL, -- Slice identifier
     node_id integer REFERENCES nodes, -- Sliver attribute if set
+    nodegroup_id integer REFERENCES nodegroups, -- Node group attribute if set
     attribute_type_id integer REFERENCES slice_attribute_types NOT NULL, -- Attribute type identifier
     value text
 ) WITH OIDS;
 CREATE INDEX slice_attribute_slice_id_idx ON slice_attribute (slice_id);
 CREATE INDEX slice_attribute_node_id_idx ON slice_attribute (node_id);
+CREATE INDEX slice_attribute_nodegroup_id_idx ON slice_attribute (nodegroup_id);
 
 CREATE VIEW slice_attributes AS
 SELECT slice_id,
@@ -879,6 +902,7 @@ peer_node.peer_node_id,
 COALESCE((SELECT nodenetwork_ids FROM node_nodenetworks WHERE node_nodenetworks.node_id = nodes.node_id), '{}') AS nodenetwork_ids,
 COALESCE((SELECT nodegroup_ids FROM node_nodegroups WHERE node_nodegroups.node_id = nodes.node_id), '{}') AS nodegroup_ids,
 COALESCE((SELECT slice_ids FROM node_slices WHERE node_slices.node_id = nodes.node_id), '{}') AS slice_ids,
+COALESCE((SELECT slice_ids_whitelist FROM node_slices_whitelist WHERE node_slices_whitelist.node_id = nodes.node_id), '{}') AS slice_ids_whitelist,
 COALESCE((SELECT pcu_ids FROM node_pcus WHERE node_pcus.node_id = nodes.node_id), '{}') AS pcu_ids,
 COALESCE((SELECT ports FROM node_pcus WHERE node_pcus.node_id = nodes.node_id), '{}') AS ports,
 COALESCE((SELECT conf_file_ids FROM node_conf_files WHERE node_conf_files.node_id = nodes.node_id), '{}') AS conf_file_ids,
@@ -978,6 +1002,7 @@ SELECT
 slice_attribute.slice_attribute_id,
 slice_attribute.slice_id,
 slice_attribute.node_id,
+slice_attribute.nodegroup_id,
 slice_attribute_types.attribute_type_id,
 slice_attribute_types.name,
 slice_attribute_types.description,