X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab5.sql;h=a4ec8917df8b335cf9dc243205ccd38289d4d1de;hb=950812875ad1720806a559fd34d3dabb6b7b4621;hp=a0e072de45c49e44be3adfa73f7b97662b065717;hpb=4492a19a0528f29d64f0c2bc1bb42723fd2df032;p=plcapi.git diff --git a/planetlab5.sql b/planetlab5.sql index a0e072d..a4ec891 100644 --- a/planetlab5.sql +++ b/planetlab5.sql @@ -10,7 +10,10 @@ -- -- Copyright (C) 2006 The Trustees of Princeton University -- +-- NOTE: this file was first created for version 4.3, the filename might be confusing +-- -- $Id$ +-- $URL$ -- SET client_encoding = 'UNICODE'; @@ -253,26 +256,42 @@ GROUP BY person_id; -- Nodes -------------------------------------------------------------------------------- --- Valid node boot states +-- Valid node boot states (Nodes.py expect max length to be 20) CREATE TABLE boot_states ( boot_state text PRIMARY KEY ) WITH OIDS; INSERT INTO boot_states (boot_state) VALUES ('boot'); INSERT INTO boot_states (boot_state) VALUES ('safeboot'); -INSERT INTO boot_states (boot_state) VALUES ('failboot'); -INSERT INTO boot_states (boot_state) VALUES ('disabled'); -INSERT INTO boot_states (boot_state) VALUES ('install'); INSERT INTO boot_states (boot_state) VALUES ('reinstall'); +INSERT INTO boot_states (boot_state) VALUES ('disabled'); + +CREATE TABLE run_levels ( + run_level text PRIMARY KEY +) WITH OIDS; +INSERT INTO run_levels (run_level) VALUES ('boot'); +INSERT INTO run_levels (run_level) VALUES ('safeboot'); +INSERT INTO run_levels (run_level) VALUES ('failboot'); +INSERT INTO run_levels (run_level) VALUES ('reinstall'); + +-- Known node types (Nodes.py expect max length to be 20) +CREATE TABLE node_types ( + node_type text PRIMARY KEY +) WITH OIDS; +INSERT INTO node_types (node_type) VALUES ('regular'); +INSERT INTO node_types (node_type) VALUES ('dummynet'); -- Nodes CREATE TABLE nodes ( -- Mandatory node_id serial PRIMARY KEY, -- Node identifier + node_type text REFERENCES node_types -- node type + DEFAULT 'regular', + hostname text NOT NULL, -- Node hostname site_id integer REFERENCES sites NOT NULL, -- At which site - boot_state text REFERENCES boot_states NOT NULL -- Node boot state - DEFAULT 'install', + DEFAULT 'reinstall', + run_level text REFERENCES run_levels DEFAULT NULL, -- Node Run Level deleted boolean NOT NULL DEFAULT false, -- Is deleted -- Optional @@ -281,6 +300,7 @@ CREATE TABLE nodes ( version text, -- Boot CD version string updated by Boot Manager ssh_rsa_key text, -- SSH host key updated by Boot Manager key text, -- Node key generated when boot file is downloaded + verified boolean NOT NULL DEFAULT false, -- whether or not the node & pcu are verified -- Timestamps date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, @@ -314,30 +334,9 @@ CREATE TABLE node_tag ( node_tag_id serial PRIMARY KEY, -- ID node_id integer REFERENCES nodes NOT NULL, -- node id tag_type_id integer REFERENCES tag_types, -- tag type id - tagvalue text -- value attached + value text -- value attached ) WITH OIDS; -CREATE OR REPLACE VIEW node_tags AS -SELECT node_id, -array_accum(node_tag_id) AS tag_ids -FROM node_tag -GROUP BY node_id; - -CREATE OR REPLACE VIEW view_node_tags AS -SELECT -node_tag.node_tag_id, -node_tag.node_id, -nodes.hostname, -tag_types.tag_type_id, -tag_types.tagname, -tag_types.description, -tag_types.category, -tag_types.min_role_id, -node_tag.tagvalue -FROM node_tag -INNER JOIN tag_types USING (tag_type_id) -INNER JOIN nodes USING (node_id); - -------------------------------------------------------------------------------- -- (network) interfaces -------------------------------------------------------------------------------- @@ -360,7 +359,7 @@ INSERT INTO network_methods (method) VALUES ('tap'); INSERT INTO network_methods (method) VALUES ('ipmi'); INSERT INTO network_methods (method) VALUES ('unknown'); --- Node network interfaces +-- Network interfaces CREATE TABLE interfaces ( -- Mandatory interface_id serial PRIMARY KEY, -- Network interface identifier @@ -397,7 +396,7 @@ FROM interfaces_ordered GROUP BY node_id; -------------------------------------------------------------------------------- --- Interface settings +-- Interface tags (formerly known as interface settings) -------------------------------------------------------------------------------- CREATE TABLE interface_tag ( @@ -417,6 +416,7 @@ CREATE OR REPLACE VIEW view_interface_tags AS SELECT interface_tag.interface_tag_id, interface_tag.interface_id, +interfaces.ip, tag_types.tag_type_id, tag_types.tagname, tag_types.description, @@ -424,7 +424,8 @@ tag_types.category, tag_types.min_role_id, interface_tag.value FROM interface_tag -INNER JOIN tag_types USING (tag_type_id); +INNER JOIN tag_types USING (tag_type_id) +INNER JOIN interfaces USING (interface_id); CREATE OR REPLACE VIEW view_interfaces AS SELECT @@ -461,23 +462,10 @@ CREATE OR REPLACE VIEW view_ilinks AS SELECT * FROM tag_types INNER JOIN ilink USING (tag_type_id); --- expose node_ids ??? --- -- cannot mention the same table twice in a join ? --- -- CREATE OR REPLACE VIEW ilink_src_node AS --- SELECT --- ilink.tag_type_id, --- ilink.src_interface_id, --- interfaces.node_id AS src_node_id, --- ilink.dst_interface_id --- FROM ilink --- INNER JOIN interfaces ON ilink.src_interface_id = interfaces.interface_id; --- --- CREATE OR REPLACE VIEW ilink_nodes AS --- SELECT --- ilink_src_node.*, --- interfaces.node_id as dst_node_id --- FROM ilink_src_node --- INNER JOIN interfaces ON ilink_src_node.dst_interface_id = interfaces.interface_id; +-- xxx TODO : expose to view_interfaces the set of ilinks a given interface is part of +-- this is needed for properly deleting these ilinks when an interface gets deleted +-- as this is not done yet, it prevents DeleteInterface, thus DeleteNode, thus DeleteSite +-- from working correctly when an iLink is set -------------------------------------------------------------------------------- -- Node groups @@ -489,7 +477,7 @@ CREATE TABLE nodegroups ( groupname text UNIQUE NOT NULL, -- Group name tag_type_id integer REFERENCES tag_types, -- node is in nodegroup if it has this tag defined -- can be null, make management faster & easier - tagvalue text -- with this value attached + value text -- with this value attached ) WITH OIDS; -- xxx - first rough implem. similar to former semantics but might be slow @@ -499,7 +487,7 @@ FROM tag_types JOIN node_tag USING (tag_type_id) JOIN nodegroups -USING (tag_type_id,tagvalue); +USING (tag_type_id,value); CREATE OR REPLACE VIEW nodegroup_nodes AS SELECT nodegroup_id, @@ -743,7 +731,7 @@ FROM node_slice_whitelist GROUP BY node_id; -------------------------------------------------------------------------------- --- Slice attributes +-- Slice tags (formerly known as slice attributes) -------------------------------------------------------------------------------- -- Slice/sliver attributes @@ -759,12 +747,6 @@ CREATE INDEX slice_tag_slice_id_idx ON slice_tag (slice_id); CREATE INDEX slice_tag_node_id_idx ON slice_tag (node_id); CREATE INDEX slice_tag_nodegroup_id_idx ON slice_tag (nodegroup_id); -CREATE OR REPLACE VIEW slice_tags AS -SELECT slice_id, -array_accum(slice_tag_id) AS slice_tag_ids -FROM slice_tag -GROUP BY slice_id; - -------------------------------------------------------------------------------- -- Initscripts -------------------------------------------------------------------------------- @@ -791,9 +773,12 @@ CREATE TABLE peers ( peer_url text NOT NULL, -- (HTTPS) URL of the peer PLCAPI interface cacert text, -- (SSL) Public certificate of peer API server key text, -- (GPG) Public key used for authentication + shortname text, -- abbreviated name for displaying foreign objects + hrn_root text, -- root for this peer domain deleted boolean NOT NULL DEFAULT false ) WITH OIDS; CREATE INDEX peers_peername_idx ON peers (peername) WHERE deleted IS false; +CREATE INDEX peers_shortname_idx ON peers (shortname) WHERE deleted IS false; -- Objects at each peer CREATE TABLE peer_site ( @@ -984,6 +969,7 @@ COALESCE((SELECT pcu_protocol_type_ids FROM pcu_protocol_types AS pcu_protocol_type_ids FROM pcu_types; +-------------------------------------------------------------------------------- CREATE OR REPLACE VIEW view_events AS SELECT events.event_id, @@ -1015,6 +1001,7 @@ event_object.object_id, event_object.object_type FROM events LEFT JOIN event_object USING (event_id); +-------------------------------------------------------------------------------- CREATE OR REPLACE VIEW view_persons AS SELECT persons.person_id, @@ -1042,6 +1029,7 @@ COALESCE((SELECT slice_ids FROM person_slices WHERE person_slices.person_id = pe FROM persons LEFT JOIN peer_person USING (person_id); +-------------------------------------------------------------------------------- CREATE OR REPLACE VIEW view_peers AS SELECT peers.*, @@ -1057,16 +1045,41 @@ COALESCE((SELECT slice_ids FROM peer_slices WHERE peer_slices.peer_id = peers.pe COALESCE((SELECT peer_slice_ids FROM peer_slices WHERE peer_slices.peer_id = peers.peer_id), '{}') AS peer_slice_ids FROM peers; +-------------------------------------------------------------------------------- +CREATE OR REPLACE VIEW node_tags AS +SELECT node_id, +array_accum(node_tag_id) AS node_tag_ids +FROM node_tag +GROUP BY node_id; + +CREATE OR REPLACE VIEW view_node_tags AS +SELECT +node_tag.node_tag_id, +node_tag.node_id, +nodes.hostname, +tag_types.tag_type_id, +tag_types.tagname, +tag_types.description, +tag_types.category, +tag_types.min_role_id, +node_tag.value +FROM node_tag +INNER JOIN tag_types USING (tag_type_id) +INNER JOIN nodes USING (node_id); + CREATE OR REPLACE VIEW view_nodes AS SELECT nodes.node_id, +nodes.node_type, nodes.hostname, nodes.site_id, nodes.boot_state, +nodes.run_level, nodes.deleted, nodes.model, nodes.boot_nonce, nodes.version, +nodes.verified, nodes.ssh_rsa_key, nodes.key, CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created, @@ -1095,14 +1108,15 @@ AS ports, COALESCE((SELECT conf_file_ids FROM node_conf_files WHERE node_conf_files.node_id = nodes.node_id), '{}') AS conf_file_ids, -COALESCE((SELECT tag_ids FROM node_tags +COALESCE((SELECT node_tag_ids FROM node_tags WHERE node_tags.node_id = nodes.node_id), '{}') -AS tag_ids, +AS node_tag_ids, node_session.session_id AS session FROM nodes LEFT JOIN peer_node USING (node_id) LEFT JOIN node_session USING (node_id); +-------------------------------------------------------------------------------- CREATE OR REPLACE VIEW view_nodegroups AS SELECT nodegroups.*, @@ -1115,6 +1129,7 @@ COALESCE((SELECT node_ids FROM nodegroup_nodes AS node_ids FROM nodegroups INNER JOIN tag_types USING (tag_type_id); +-------------------------------------------------------------------------------- CREATE OR REPLACE VIEW view_conf_files AS SELECT conf_files.*, @@ -1126,6 +1141,7 @@ COALESCE((SELECT nodegroup_ids FROM conf_file_nodegroups AS nodegroup_ids FROM conf_files; +-------------------------------------------------------------------------------- CREATE OR REPLACE VIEW view_pcus AS SELECT pcus.*, @@ -1133,6 +1149,7 @@ COALESCE((SELECT node_ids FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), COALESCE((SELECT ports FROM pcu_nodes WHERE pcu_nodes.pcu_id = pcus.pcu_id), '{}') AS ports FROM pcus; +-------------------------------------------------------------------------------- CREATE OR REPLACE VIEW view_sites AS SELECT sites.site_id, @@ -1160,6 +1177,7 @@ COALESCE((SELECT pcu_ids FROM site_pcus WHERE site_pcus.site_id = sites.site_id) FROM sites LEFT JOIN peer_site USING (site_id); +-------------------------------------------------------------------------------- CREATE OR REPLACE VIEW view_addresses AS SELECT addresses.*, @@ -1167,6 +1185,7 @@ COALESCE((SELECT address_type_ids FROM address_address_types WHERE address_addre COALESCE((SELECT address_types FROM address_address_types WHERE address_address_types.address_id = addresses.address_id), '{}') AS address_types FROM addresses; +-------------------------------------------------------------------------------- CREATE OR REPLACE VIEW view_keys AS SELECT keys.*, @@ -1177,6 +1196,13 @@ FROM keys LEFT JOIN person_key USING (key_id) LEFT JOIN peer_key USING (key_id); +-------------------------------------------------------------------------------- +CREATE OR REPLACE VIEW slice_tags AS +SELECT slice_id, +array_accum(slice_tag_id) AS slice_tag_ids +FROM slice_tag +GROUP BY slice_id; + CREATE OR REPLACE VIEW view_slices AS SELECT slices.slice_id, @@ -1209,10 +1235,13 @@ tag_types.tagname, tag_types.description, tag_types.category, tag_types.min_role_id, -slice_tag.value +slice_tag.value, +slices.name FROM slice_tag -INNER JOIN tag_types USING (tag_type_id); +INNER JOIN tag_types USING (tag_type_id) +INNER JOIN slices USING (slice_id); +-------------------------------------------------------------------------------- CREATE OR REPLACE VIEW view_sessions AS SELECT sessions.session_id,