X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab4.sql;h=e657946d9c2408d62f3f5045f0fd15bc58e8d1cb;hb=e848398878d06cdc99f374052388fce4629645c5;hp=9649b28eec6c801d6c28d1d28bfb2f3d39464438;hpb=c4a07c966885e2efe5706b9de49616a8e7a777f1;p=plcapi.git diff --git a/planetlab4.sql b/planetlab4.sql index 9649b28..e657946 100644 --- a/planetlab4.sql +++ b/planetlab4.sql @@ -9,7 +9,7 @@ -- -- Copyright (C) 2006 The Trustees of Princeton University -- --- $Id: planetlab4.sql,v 1.71 2007/02/01 23:05:59 mlhuang Exp $ +-- $Id: planetlab4.sql,v 1.80 2007/08/01 17:01:01 tmack Exp $ -- SET client_encoding = 'UNICODE'; @@ -282,7 +282,8 @@ CREATE TABLE nodes ( -- Timestamps date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, - last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP + last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, + last_contact timestamp without time zone ) WITH OIDS; CREATE INDEX nodes_hostname_idx ON nodes (hostname) WHERE deleted IS false; CREATE INDEX nodes_site_id_idx ON nodes (site_id) WHERE deleted IS false; @@ -292,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; -------------------------------------------------------------------------------- @@ -508,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 ( @@ -588,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 -------------------------------------------------------------------------------- @@ -605,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, @@ -617,6 +641,21 @@ array_accum(slice_attribute_id) AS slice_attribute_ids FROM slice_attribute GROUP BY slice_id; +-------------------------------------------------------------------------------- +-- Initscripts +-------------------------------------------------------------------------------- + +-- Initscripts +CREATE TABLE initscripts ( + initscript_id serial PRIMARY KEY, -- Initscript identifier + name text NOT NULL, -- Initscript name + enabled bool NOT NULL DEFAULT true, -- Initscript is active + script text NOT NULL, -- Initscript + UNIQUE (name) +) WITH OIDS; +CREATE INDEX initscripts_name_idx ON initscripts (name); + + -------------------------------------------------------------------------------- -- Peers -------------------------------------------------------------------------------- @@ -755,6 +794,7 @@ CREATE TABLE events ( event_id serial PRIMARY KEY, -- Event identifier person_id integer REFERENCES persons, -- Person responsible for event, if any node_id integer REFERENCES nodes, -- Node responsible for event, if any + auth_type text, -- Type of auth used. i.e. AuthMethod fault_code integer NOT NULL DEFAULT 0, -- Did this event result in error call_name text NOT NULL, -- Call responsible for this event call text NOT NULL, -- Call responsible for this event, including parameters @@ -767,7 +807,7 @@ CREATE TABLE events ( CREATE TABLE event_object ( event_id integer REFERENCES events NOT NULL, -- Event identifier object_id integer NOT NULL, -- Object identifier - object_type text -- What type of object is this event affecting + object_type text NOT NULL Default 'Unknown' -- What type of object is this event affecting ) WITH OIDS; CREATE INDEX event_object_event_id_idx ON event_object (event_id); CREATE INDEX event_object_object_id_idx ON event_object (object_id); @@ -789,6 +829,7 @@ SELECT events.event_id, events.person_id, events.node_id, +events.auth_type, events.fault_code, events.call_name, events.call, @@ -855,11 +896,13 @@ nodes.ssh_rsa_key, nodes.key, CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created, CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated, +CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact, peer_node.peer_id, 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, @@ -959,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,