X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab4.sql;h=12167994743c33696430c91d5922b4c73c70a7ae;hb=df89953c9a34cd167b36eaae8a03f2ae1efb03b4;hp=6278b0dc6dfdf9e50a0b50339067a61a65354dd3;hpb=d2b94ea21edc3e1781e5a3e03820d5d28cbb14d0;p=plcapi.git diff --git a/planetlab4.sql b/planetlab4.sql index 6278b0d..1216799 100644 --- a/planetlab4.sql +++ b/planetlab4.sql @@ -9,7 +9,7 @@ -- -- Copyright (C) 2006 The Trustees of Princeton University -- --- $Id: planetlab4.sql,v 1.47 2006/11/27 16:43:31 thierry Exp $ +-- $Id: planetlab4.sql,v 1.56 2006/12/20 14:06:40 tmack 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; @@ -299,7 +298,6 @@ 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 @@ -319,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 -------------------------------------------------------------------------------- @@ -598,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 @@ -702,55 +687,16 @@ 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 + message text, -- High level description of this event runtime float, -- Event run time time timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP -- Event timestamp ) WITH OIDS; @@ -778,9 +724,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, @@ -817,13 +762,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