From e21ba2695f0e6419c3d250a07dbf768d733b47b2 Mon Sep 17 00:00:00 2001 From: Tony Mack Date: Thu, 12 Jul 2007 17:48:19 +0000 Subject: [PATCH] - added node_slice_whitelist table - added node_slices_whitelist aggregate view - updated view_nodes to include node_slices_whitelist view as 'slice_ids_whitelist' column --- planetlab4.sql | 19 ++++++++++++++++++- 1 file changed, 18 insertions(+), 1 deletion(-) diff --git a/planetlab4.sql b/planetlab4.sql index 17ac42c..6853ecb 100644 --- a/planetlab4.sql +++ b/planetlab4.sql @@ -9,7 +9,7 @@ -- -- Copyright (C) 2006 The Trustees of Princeton University -- --- $Id: planetlab4.sql,v 1.77 2007/04/30 18:53:50 tmack Exp $ +-- $Id: planetlab4.sql,v 1.78 2007/05/17 16:06:46 tmack Exp $ -- SET client_encoding = 'UNICODE'; @@ -295,6 +295,22 @@ array_accum(node_id) AS node_ids FROM nodes 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 -------------------------------------------------------------------------------- @@ -882,6 +898,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, -- 2.43.0