X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab4.sql;h=05c5baef931c5fe94baf0ab08878cea7138e32ae;hb=075d40b7d75d9b0f604c1fb5758bf11757c9850a;hp=2b3b4c3ec94096a970d05f8042d6126703ace729;hpb=35c5c511b0a60018b0c567c08dd884f07239728e;p=plcapi.git diff --git a/planetlab4.sql b/planetlab4.sql index 2b3b4c3..05c5bae 100644 --- a/planetlab4.sql +++ b/planetlab4.sql @@ -9,7 +9,7 @@ -- -- Copyright (C) 2006 The Trustees of Princeton University -- --- $Id: planetlab4.sql,v 1.44 2006/11/24 12:05:59 thierry Exp $ +-- $Id: planetlab4.sql,v 1.54 2006/12/15 18:34:46 mlhuang Exp $ -- -------------------------------------------------------------------------------- @@ -45,10 +45,9 @@ CREATE TABLE peers ( peer_id serial PRIMARY KEY, -- identifier peername text NOT NULL, -- free text peer_url text NOT NULL, -- the url of that peer's API - -- oops, looks like we have a dependency loop here - --person_id integer REFERENCES persons NOT NULL, -- the account we use for logging in - person_id integer NOT NULL, -- the account we use for logging in - + cacert text, -- (SSL) Public certificate of peer API server + key text, -- (GPG) Public key used for authentication + deleted boolean NOT NULL DEFAULT false ) WITH OIDS; @@ -290,10 +289,7 @@ CREATE TABLE nodes ( -- Mandatory node_id serial PRIMARY KEY, -- Node identifier hostname text NOT NULL, -- Node hostname - -- temporarily removed NOT NULL clause for foreign_nodes - site_id integer REFERENCES sites, -- At which site - -- may be NULL for local_nodes - peer_id integer REFERENCES peers, -- From which peer + site_id integer REFERENCES sites NOT NULL, -- At which site boot_state text REFERENCES boot_states NOT NULL DEFAULT 'inst', -- Node boot state deleted boolean NOT NULL DEFAULT false, -- Is deleted @@ -302,13 +298,14 @@ CREATE TABLE nodes ( model text, -- Hardware make and model boot_nonce text, -- Random nonce updated by Boot Manager version text, -- Boot CD version string updated by Boot Manager - -- XXX Should be key_id integer REFERENCES keys ssh_rsa_key text, -- SSH host key updated by Boot Manager key text, -- Node key generated by API when configuration file is downloaded -- 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, + + peer_id integer REFERENCES peers -- From which peer ) 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; @@ -320,13 +317,6 @@ array_accum(node_id) AS node_ids FROM nodes GROUP BY site_id; --- Nodes at each peer -CREATE VIEW peer_nodes AS -SELECT peer_id, -array_accum(node_id) AS node_ids -FROM nodes -GROUP BY peer_id; - -------------------------------------------------------------------------------- -- Node groups -------------------------------------------------------------------------------- @@ -545,8 +535,7 @@ INSERT INTO slice_instantiations (instantiation) VALUES ('delegated'); -- Manual -- Slices CREATE TABLE slices ( slice_id serial PRIMARY KEY, -- Slice identifier --- xxx temporarily remove the NOT NULL constraint - site_id integer REFERENCES sites, -- Site identifier + site_id integer REFERENCES sites NOT NULL, -- Site identifier peer_id integer REFERENCES peers, -- on which peer name text NOT NULL, -- Slice name @@ -556,8 +545,7 @@ CREATE TABLE slices ( max_nodes integer NOT NULL DEFAULT 100, -- Maximum number of nodes that can be assigned to this slice --- xxx temporarily remove the NOT NULL constraint - creator_person_id integer REFERENCES persons, -- Creator + creator_person_id integer REFERENCES persons NOT NULL, -- Creator created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Creation date expires timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP + '2 weeks', -- Expiration date @@ -601,12 +589,6 @@ FROM slices WHERE is_deleted is false GROUP BY site_id; -CREATE VIEW peer_slices AS -SELECT peer_id, -array_accum(slice_id) AS slice_ids -FROM slices -GROUP BY peer_id; - -- Slice membership CREATE TABLE slice_person ( slice_id integer REFERENCES slices NOT NULL, -- Slice identifier @@ -639,7 +621,9 @@ CREATE TABLE slice_attribute_types ( attribute_type_id serial PRIMARY KEY, -- Attribute type identifier name text UNIQUE NOT NULL, -- Attribute name description text, -- Attribute description - min_role_id integer REFERENCES roles DEFAULT 10 -- If set, minimum (least powerful) role that can set or change this attribute + min_role_id integer REFERENCES roles DEFAULT 10, -- If set, minimum (least powerful) role that can set or change this attribute + + peer_id integer REFERENCES peers -- From which peer ) WITH OIDS; -- Slice/sliver attributes @@ -648,7 +632,9 @@ CREATE TABLE slice_attribute ( slice_id integer REFERENCES slices NOT NULL, -- Slice identifier node_id integer REFERENCES nodes, -- Sliver attribute if set attribute_type_id integer REFERENCES slice_attribute_types NOT NULL, -- Attribute type identifier - value text + value text, + + peer_id integer REFERENCES peers -- From which peer ) 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); @@ -701,55 +687,15 @@ CREATE TABLE messages ( -- Events -------------------------------------------------------------------------------- --- Event types -CREATE TABLE event_types ( - event_type text PRIMARY KEY -- Event type -) WITH OIDS; -INSERT INTO event_types (event_type) VALUES ('Add'); -INSERT INTO event_types (event_type) VALUES ('AddTo'); -INSERT INTO event_types (event_type) VALUES ('Get'); -INSERT INTO event_types (event_type) VALUES ('Update'); -INSERT INTO event_types (event_type) VALUES ('Delete'); -INSERT INTO event_types (event_type) VALUES ('DeleteFrom'); -INSERT INTO event_types (event_type) VALUES ('Unknown'); - --- Object types -CREATE TABLE object_types ( - object_type text PRIMARY KEY -- Object type -) WITH OIDS; -INSERT INTO object_types (object_type) VALUES ('AddressType'); -INSERT INTO object_types (object_type) VALUES ('Address'); -INSERT INTO object_types (object_type) VALUES ('BootState'); -INSERT INTO object_types (object_type) VALUES ('ConfFile'); -INSERT INTO object_types (object_type) VALUES ('KeyType'); -INSERT INTO object_types (object_type) VALUES ('Key'); -INSERT INTO object_types (object_type) VALUES ('Message'); -INSERT INTO object_types (object_type) VALUES ('NetworkMethod'); -INSERT INTO object_types (object_type) VALUES ('NetworkType'); -INSERT INTO object_types (object_type) VALUES ('Network'); -INSERT INTO object_types (object_type) VALUES ('NodeGroup'); -INSERT INTO object_types (object_type) VALUES ('NodeNetwork'); -INSERT INTO object_types (object_type) VALUES ('Node'); -INSERT INTO object_types (object_type) VALUES ('PCU'); -INSERT INTO object_types (object_type) VALUES ('Person'); -INSERT INTO object_types (object_type) VALUES ('Role'); -INSERT INTO object_types (object_type) VALUES ('Session'); -INSERT INTO object_types (object_type) VALUES ('Site'); -INSERT INTO object_types (object_type) VALUES ('SliceAttributeType'); -INSERT INTO object_types (object_type) VALUES ('SliceAttribute'); -INSERT INTO object_types (object_type) VALUES ('Slice'); -INSERT INTO object_types (object_type) VALUES ('SliceInstantiation'); -INSERT INTO object_types (object_type) VALUES ('Unknown'); -- Events 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 - event_type text REFERENCES event_types NOT NULL DEFAULT 'Unknown', -- Event type - object_type text REFERENCES object_types NOT NULL DEFAULT 'Unknown', -- Object type associated with event fault_code integer NOT NULL DEFAULT 0, -- Did this event result in error - call text NOT NULL, -- Call responsible for this event + call_name text Not NULL, -- Call responsible for this event + call text NOT NULL, -- Call responsible for this event, including paramters runtime float, -- Event run time time timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP -- Event timestamp ) WITH OIDS; @@ -777,9 +723,8 @@ SELECT events.event_id, events.person_id, events.node_id, -events.event_type, -events.object_type, events.fault_code, +events.call_name, events.call, events.runtime, CAST(date_part('epoch', events.time) AS bigint) AS time, @@ -816,13 +761,66 @@ LEFT JOIN person_sites USING (person_id) LEFT JOIN person_keys USING (person_id) LEFT JOIN person_slices USING (person_id); +-- Objects at each peer +CREATE VIEW peer_sites AS +SELECT peer_id, +array_accum(site_id) AS site_ids +FROM sites +GROUP BY peer_id; + +CREATE VIEW peer_persons AS +SELECT peer_id, +array_accum(person_id) AS person_ids +FROM persons +GROUP BY peer_id; + +CREATE VIEW peer_keys AS +SELECT peer_id, +array_accum(key_id) AS key_ids +FROM keys +GROUP BY peer_id; + +CREATE VIEW peer_nodes AS +SELECT peer_id, +array_accum(node_id) AS node_ids +FROM nodes +GROUP BY peer_id; + +CREATE VIEW peer_slice_attribute_types AS +SELECT peer_id, +array_accum(attribute_type_id) AS attribute_type_ids +FROM slice_attribute_types +GROUP BY peer_id; + +CREATE VIEW peer_slice_attributes AS +SELECT peer_id, +array_accum(slice_attribute_id) AS slice_attribute_ids +FROM slice_attribute +GROUP BY peer_id; + +CREATE VIEW peer_slices AS +SELECT peer_id, +array_accum(slice_id) AS slice_ids +FROM slices +GROUP BY peer_id; + CREATE VIEW view_peers AS SELECT peers.*, -peer_nodes.node_ids, -peer_slices.slice_ids +COALESCE(peer_sites.site_ids, '{}') AS site_ids, +COALESCE(peer_persons.person_ids, '{}') AS person_ids, +COALESCE(peer_keys.key_ids, '{}') AS key_ids, +COALESCE(peer_nodes.node_ids, '{}') AS node_ids, +COALESCE(peer_slice_attribute_types.attribute_type_ids, '{}') AS attribute_type_ids, +COALESCE(peer_slice_attributes.slice_attribute_ids, '{}') AS slice_attribute_ids, +COALESCE(peer_slices.slice_ids, '{}') AS slice_ids FROM peers +LEFT JOIN peer_sites USING (peer_id) +LEFT JOIN peer_persons USING (peer_id) +LEFT JOIN peer_keys USING (peer_id) LEFT JOIN peer_nodes USING (peer_id) +LEFT JOIN peer_slice_attribute_types USING (peer_id) +LEFT JOIN peer_slice_attributes USING (peer_id) LEFT JOIN peer_slices USING (peer_id); CREATE VIEW view_nodes AS @@ -977,7 +975,8 @@ slice_attribute_types.attribute_type_id, slice_attribute_types.name, slice_attribute_types.description, slice_attribute_types.min_role_id, -slice_attribute.value +slice_attribute.value, +slice_attribute.peer_id FROM slice_attribute INNER JOIN slice_attribute_types USING (attribute_type_id);