X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab4.sql;h=bf3f156942cebd7c917b101be9c676331829d48d;hb=22aa65daea960e86e0e791684db1cfc6dfd0fabd;hp=908459db0b0d05ab4c8d155e8ba8a0e6cbdb9291;hpb=2696eec3582eb1b1092d0a98c863a040f6aa2af4;p=plcapi.git diff --git a/planetlab4.sql b/planetlab4.sql index 908459d..bf3f156 100644 --- a/planetlab4.sql +++ b/planetlab4.sql @@ -9,7 +9,7 @@ -- -- Copyright (C) 2006 The Trustees of Princeton University -- --- $Id: planetlab4.sql,v 1.41 2006/11/17 19:31:08 tmack Exp $ +-- $Id: planetlab4.sql,v 1.51 2006/11/28 22:00:14 tmack Exp $ -- -------------------------------------------------------------------------------- @@ -36,6 +36,22 @@ CREATE TABLE plc_db_version ( INSERT INTO plc_db_version (version) VALUES (4); +-------------------------------------------------------------------------------- +-- Peers +-------------------------------------------------------------------------------- + +-- Peers +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 + + deleted boolean NOT NULL DEFAULT false +) WITH OIDS; + -------------------------------------------------------------------------------- -- Accounts -------------------------------------------------------------------------------- @@ -63,7 +79,9 @@ CREATE TABLE persons ( -- 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 persons_email_idx ON persons (email) WHERE deleted IS false; @@ -90,7 +108,9 @@ CREATE TABLE sites ( -- 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 sites_login_base_idx ON sites (login_base) WHERE deleted IS false; @@ -197,7 +217,8 @@ CREATE TABLE keys ( key_id serial PRIMARY KEY, -- Key identifier key_type text REFERENCES key_types NOT NULL, -- Key type key text NOT NULL, -- Key material - is_blacklisted boolean NOT NULL DEFAULT false -- Has been blacklisted + is_blacklisted boolean NOT NULL DEFAULT false, -- Has been blacklisted + peer_id integer REFERENCES peers -- From which peer ) WITH OIDS; -- Account authentication key(s) @@ -264,26 +285,12 @@ INSERT INTO boot_states (boot_state) VALUES ('rins'); INSERT INTO boot_states (boot_state) VALUES ('rcnf'); INSERT INTO boot_states (boot_state) VALUES ('new'); --- Peers -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 - person_id integer REFERENCES persons NOT NULL, -- the account we use for logging in - - deleted boolean NOT NULL DEFAULT false -) WITH OIDS; - - -- Nodes 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 @@ -298,7 +305,9 @@ 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, + + 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; @@ -310,13 +319,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 -------------------------------------------------------------------------------- @@ -535,8 +537,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 @@ -546,8 +547,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 @@ -591,12 +591,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 @@ -629,7 +623,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 @@ -638,7 +634,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); @@ -691,55 +689,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; @@ -767,9 +725,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, @@ -792,6 +749,7 @@ persons.title, persons.phone, persons.url, persons.bio, +persons.peer_id, CAST(date_part('epoch', persons.date_created) AS bigint) AS date_created, CAST(date_part('epoch', persons.last_updated) AS bigint) AS last_updated, COALESCE(person_roles.role_ids, '{}') AS role_ids, @@ -805,6 +763,19 @@ LEFT JOIN person_sites USING (person_id) LEFT JOIN person_keys USING (person_id) LEFT JOIN person_slices USING (person_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; + +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.*, @@ -904,6 +875,7 @@ sites.max_slivers, sites.latitude, sites.longitude, sites.url, +sites.peer_id, CAST(date_part('epoch', sites.date_created) AS bigint) AS date_created, CAST(date_part('epoch', sites.last_updated) AS bigint) AS last_updated, COALESCE(site_persons.person_ids, '{}') AS person_ids, @@ -965,7 +937,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);