X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab5.sql;h=a4ec8917df8b335cf9dc243205ccd38289d4d1de;hb=753b291058dcc75ce017b216b8ec93bf5978136d;hp=e686ecce385d5dc49163635b3e9b5765a5c41bc8;hpb=91a1fbdf638839bb5b2ef2dfe1d3ab18e800ad49;p=plcapi.git diff --git a/planetlab5.sql b/planetlab5.sql index e686ecc..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'; @@ -259,10 +262,16 @@ CREATE TABLE boot_states ( ) 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 ( @@ -281,7 +290,8 @@ CREATE TABLE nodes ( 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 @@ -290,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, @@ -348,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 @@ -405,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, @@ -412,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 @@ -449,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 @@ -773,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 ( @@ -1045,7 +1048,7 @@ FROM peers; -------------------------------------------------------------------------------- CREATE OR REPLACE VIEW node_tags AS SELECT node_id, -array_accum(node_tag_id) AS tag_ids +array_accum(node_tag_id) AS node_tag_ids FROM node_tag GROUP BY node_id; @@ -1071,10 +1074,12 @@ 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, @@ -1103,9 +1108,9 @@ 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) @@ -1230,9 +1235,11 @@ 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