X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab4.sql;h=39d30d89d4d45ddbe836b1e53a33ecc5cb4b7884;hb=dfd614ed44d424d6677599d55dc8ccf76f274a2b;hp=1ae1461cd1b0a2e83d01c59b4e268284d1ddc44b;hpb=7e87a2ec0638e9116253ff73b207b6f4f6cd8c60;p=plcapi.git diff --git a/planetlab4.sql b/planetlab4.sql index 1ae1461..39d30d8 100644 --- a/planetlab4.sql +++ b/planetlab4.sql @@ -9,7 +9,7 @@ -- -- Copyright (C) 2006 The Trustees of Princeton University -- --- $Id: planetlab4.sql,v 1.35 2006/11/15 10:59:54 thierry Exp $ +-- $Id: planetlab4.sql,v 1.52 2006/11/29 17:57:27 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 + auth_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,23 +285,13 @@ 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 - site_id integer REFERENCES sites, -- At which site (clause NOT NULL removed for foreign_nodes) + 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 @@ -288,13 +299,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; @@ -306,22 +318,6 @@ array_accum(node_id) AS node_ids FROM nodes GROUP BY site_id; --- Nodes - peers relationship -CREATE TABLE peer_node ( - peer_id integer REFERENCES peers NOT NULL, -- Peer identifier - node_id integer REFERENCES nodes NOT NULL, -- (Local) node identifier - PRIMARY KEY (peer_id, node_id), - UNIQUE (node_id) -- Nodes can only be at one peer -) WITH OIDS; -CREATE INDEX peer_node_peer_id_idx ON peer_node (peer_id); - --- Nodes at each peer -CREATE VIEW peer_nodes AS -SELECT peer_id, -array_accum(node_id) AS node_ids -FROM peer_node -GROUP BY peer_id; - -------------------------------------------------------------------------------- -- Node groups -------------------------------------------------------------------------------- @@ -540,8 +536,9 @@ 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 instantiation text REFERENCES slice_instantiations NOT NULL DEFAULT 'plc-instantiated', -- Slice state, e.g. plc-instantiated url text, -- Project URL @@ -549,15 +546,14 @@ 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 - deleted boolean NOT NULL DEFAULT false + is_deleted boolean NOT NULL DEFAULT false ) WITH OIDS; -CREATE INDEX slices_site_id_idx ON slices (site_id) WHERE deleted IS false; -CREATE INDEX slices_name_idx ON slices (name) WHERE deleted IS false; +CREATE INDEX slices_site_id_idx ON slices (site_id) WHERE is_deleted IS false; +CREATE INDEX slices_name_idx ON slices (name) WHERE is_deleted IS false; -- Slivers CREATE TABLE slice_node ( @@ -591,23 +587,9 @@ CREATE VIEW site_slices AS SELECT site_id, array_accum(slice_id) AS slice_ids FROM slices +WHERE is_deleted is false GROUP BY site_id; --- Slices - peer relationship -CREATE TABLE peer_slice ( - peer_id integer REFERENCES peers NOT NULL, -- peer primary key - slice_id integer REFERENCES slices NOT NULL, -- node primary key - PRIMARY KEY (peer_id, slice_id) -) WITH OIDS; -CREATE INDEX peer_slice_peer_id_idx ON peer_slice (peer_id); -CREATE INDEX peer_slice_slice_id_idx ON peer_slice (slice_id); - -CREATE VIEW peer_slices AS -SELECT peer_id, -array_accum(slice_id) AS slice_ids -FROM peer_slice -GROUP BY peer_id; - -- Slice membership CREATE TABLE slice_person ( slice_id integer REFERENCES slices NOT NULL, -- Slice identifier @@ -640,7 +622,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 @@ -649,7 +633,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); @@ -693,6 +679,7 @@ CREATE TABLE node_session ( CREATE TABLE messages ( message_id text PRIMARY KEY, -- Message name + subject text, -- Message summary template text, -- Message template enabled bool NOT NULL DEFAULT true -- Whether message is enabled ) WITH OIDS; @@ -701,53 +688,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 ('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 ('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; @@ -775,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, @@ -800,6 +748,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, @@ -813,11 +762,50 @@ 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_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.*, +peer_sites.site_ids, +peer_persons.person_ids, +peer_nodes.node_ids, +peer_slices.slice_ids +FROM peers +LEFT JOIN peer_sites USING (peer_id) +LEFT JOIN peer_persons USING (peer_id) +LEFT JOIN peer_nodes USING (peer_id) +LEFT JOIN peer_slices USING (peer_id); + CREATE VIEW view_nodes AS SELECT nodes.node_id, nodes.hostname, nodes.site_id, +nodes.peer_id, nodes.boot_state, nodes.deleted, nodes.model, @@ -835,40 +823,12 @@ COALESCE(node_pcus.ports, '{}') AS ports, COALESCE(node_conf_files.conf_file_ids, '{}') AS conf_file_ids, node_session.session_id AS session FROM nodes -LEFT JOIN peer_node USING (node_id) LEFT JOIN node_nodenetworks USING (node_id) LEFT JOIN node_nodegroups USING (node_id) LEFT JOIN node_slices USING (node_id) LEFT JOIN node_pcus USING (node_id) LEFT JOIN node_conf_files USING (node_id) -LEFT JOIN node_session USING (node_id) -WHERE peer_node.peer_id IS NULL; - -CREATE VIEW view_peers AS -SELECT -peers.*, -peer_nodes.node_ids, -peer_slices.slice_ids -FROM peers -LEFT JOIN peer_nodes USING (peer_id) -LEFT JOIN peer_slices USING (peer_id); - -CREATE VIEW view_foreign_nodes AS -SELECT -nodes.node_id, -nodes.hostname, -peer_node.peer_id, -nodes.boot_state, -nodes.model, -nodes.version, -CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created, -CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated, -COALESCE(node_slices.slice_ids, '{}') AS slice_ids, -nodes.deleted -FROM nodes -LEFT JOIN peer_node USING (node_id) -LEFT JOIN node_slices USING (node_id) -WHERE peer_node.peer_id IS NOT NULL; +LEFT JOIN node_session USING (node_id); CREATE VIEW view_nodegroups AS SELECT @@ -930,6 +890,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, @@ -963,44 +924,23 @@ CREATE VIEW view_slices AS SELECT slices.slice_id, slices.site_id, +slices.peer_id, slices.name, slices.instantiation, slices.url, slices.description, slices.max_nodes, slices.creator_person_id, -slices.deleted, +slices.is_deleted, CAST(date_part('epoch', slices.created) AS bigint) AS created, CAST(date_part('epoch', slices.expires) AS bigint) AS expires, COALESCE(slice_nodes.node_ids, '{}') AS node_ids, COALESCE(slice_persons.person_ids, '{}') AS person_ids, COALESCE(slice_attributes.slice_attribute_ids, '{}') AS slice_attribute_ids FROM slices -LEFT JOIN peer_slice USING (slice_id) LEFT JOIN slice_nodes USING (slice_id) LEFT JOIN slice_persons USING (slice_id) -LEFT JOIN slice_attributes USING (slice_id) -WHERE peer_slice.peer_id IS NULL -AND slices.site_id IS NOT NULL -AND slices.creator_person_id IS NOT NULL; - -CREATE VIEW view_foreign_slices AS -SELECT -slices.slice_id, -slices.name, -peer_slice.peer_id, -slices.instantiation, -slices.url, -slices.description, -slices.max_nodes, -slices.deleted, -CAST(date_part('epoch', slices.created) AS bigint) AS created, -CAST(date_part('epoch', slices.expires) AS bigint) AS expires, -COALESCE(slice_nodes.node_ids, '{}') AS node_ids -FROM slices -LEFT JOIN peer_slice USING (slice_id) -LEFT JOIN slice_nodes USING (slice_id) -WHERE peer_slice.peer_id IS NOT NULL; +LEFT JOIN slice_attributes USING (slice_id); -- CREATE VIEW view_slice_attributes AS @@ -1012,7 +952,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);