X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=planetlab5.sql;h=a9bca218e1ffedb25c0101dfe3118862369815c1;hb=28b6e3dcb08d702ebee9c56d316eb39c731a0ee8;hp=382589fb3fa41f631d39a4cc5c72f2932b55e565;hpb=a7f0a8c621447d357b9f2b42cfa2513211aeb751;p=plcapi.git diff --git a/planetlab5.sql b/planetlab5.sql index 382589f..a9bca21 100644 --- a/planetlab5.sql +++ b/planetlab5.sql @@ -1,11 +1,12 @@ -- -- PlanetLab Central database schema --- Version 4, PostgreSQL +-- Version 5, PostgreSQL -- -- Aaron Klingaman -- Reid Moran -- Mark Huang -- Tony Mack +-- Thierry Parmentelat -- -- Copyright (C) 2006 The Trustees of Princeton University -- @@ -138,8 +139,11 @@ CREATE TABLE address_types ( description text -- Address type description ) WITH OIDS; +-- Multi-rows insertion "insert .. values (row1), (row2)" is not supported by pgsql-8.1 -- 'Billing' Used to be 'Site' -INSERT INTO address_types (name) VALUES ('Personal'), ('Shipping'), ('Billing'); +INSERT INTO address_types (name) VALUES ('Personal'); +INSERT INTO address_types (name) VALUES ('Shipping'); +INSERT INTO address_types (name) VALUES ('Billing'); -- Mailing addresses CREATE TABLE addresses ( @@ -224,7 +228,10 @@ CREATE TABLE roles ( role_id integer PRIMARY KEY, -- Role identifier name text UNIQUE NOT NULL -- Role symbolic name ) WITH OIDS; -INSERT INTO roles (role_id, name) VALUES (10, 'admin'), (20, 'pi'), (30, 'user'), (40, 'tech'); +INSERT INTO roles (role_id, name) VALUES (10, 'admin'); +INSERT INTO roles (role_id, name) VALUES (20, 'pi'); +INSERT INTO roles (role_id, name) VALUES (30, 'user'); +INSERT INTO roles (role_id, name) VALUES (40, 'tech'); CREATE TABLE person_role ( person_id integer REFERENCES persons NOT NULL, -- Account identifier @@ -250,8 +257,12 @@ GROUP BY person_id; CREATE TABLE boot_states ( boot_state text PRIMARY KEY ) WITH OIDS; -INSERT INTO boot_states (boot_state) - VALUES ('boot'), ('dbg'), ('diag'), ('disable'), ('inst'), ('rins'), ('new'); +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'); -- Nodes CREATE TABLE nodes ( @@ -261,7 +272,7 @@ CREATE TABLE nodes ( site_id integer REFERENCES sites NOT NULL, -- At which site boot_state text REFERENCES boot_states NOT NULL -- Node boot state - DEFAULT 'inst', + DEFAULT 'install', deleted boolean NOT NULL DEFAULT false, -- Is deleted -- Optional @@ -290,19 +301,19 @@ GROUP BY site_id; -------------------------------------------------------------------------------- -- node tags -------------------------------------------------------------------------------- -CREATE TABLE node_tag_types ( +CREATE TABLE tag_types ( - node_tag_type_id serial PRIMARY KEY, -- ID + tag_type_id serial PRIMARY KEY, -- ID tagname text UNIQUE NOT NULL, -- Tag Name description text, -- Optional Description - category text NOT NULL DEFAULT 'general', -- Free text for grouping tags together - min_role_id integer REFERENCES roles DEFAULT 10 -- set minimal role required + min_role_id integer REFERENCES roles DEFAULT 10, -- set minimal role required + category text NOT NULL DEFAULT 'general' -- Free text for grouping tags together ) WITH OIDS; CREATE TABLE node_tag ( node_tag_id serial PRIMARY KEY, -- ID node_id integer REFERENCES nodes NOT NULL, -- node id - node_tag_type_id integer REFERENCES node_tag_types, -- tag type id + tag_type_id integer REFERENCES tag_types, -- tag type id tagvalue text -- value attached ) WITH OIDS; @@ -317,116 +328,18 @@ SELECT node_tag.node_tag_id, node_tag.node_id, nodes.hostname, -node_tag_types.node_tag_type_id, -node_tag_types.tagname, -node_tag_types.description, -node_tag_types.category, -node_tag_types.min_role_id, +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 node_tag_types USING (node_tag_type_id) +INNER JOIN tag_types USING (tag_type_id) INNER JOIN nodes USING (node_id); -------------------------------------------------------------------------------- --- Node groups --------------------------------------------------------------------------------- - --- Node groups -CREATE TABLE nodegroups ( - nodegroup_id serial PRIMARY KEY, -- Group identifier - groupname text UNIQUE NOT NULL, -- Group name - node_tag_type_id integer REFERENCES node_tag_types, -- node is in nodegroup if it has this tag defined - tagvalue text NOT NULL -- with this value attached -) WITH OIDS; - --- xxx - first rough implem -CREATE OR REPLACE VIEW nodegroup_node AS -SELECT nodegroup_id, node_id -FROM node_tag_types -JOIN node_tag -USING (node_tag_type_id) -JOIN nodegroups -USING (node_tag_type_id,tagvalue); - -CREATE OR REPLACE VIEW nodegroup_nodes AS -SELECT nodegroup_id, -array_accum(node_id) AS node_ids -FROM nodegroup_node -GROUP BY nodegroup_id; - --- Node groups that each node is a member of -CREATE OR REPLACE VIEW node_nodegroups AS -SELECT node_id, -array_accum(nodegroup_id) AS nodegroup_ids -FROM nodegroup_node -GROUP BY node_id; - --------------------------------------------------------------------------------- --- Node configuration files --------------------------------------------------------------------------------- - -CREATE TABLE conf_files ( - conf_file_id serial PRIMARY KEY, -- Configuration file identifier - enabled bool NOT NULL DEFAULT true, -- Configuration file is active - source text NOT NULL, -- Relative path on the boot server - -- where file can be downloaded - dest text NOT NULL, -- Absolute path where file should be installed - file_permissions text NOT NULL DEFAULT '0644', -- chmod(1) permissions - file_owner text NOT NULL DEFAULT 'root', -- chown(1) owner - file_group text NOT NULL DEFAULT 'root', -- chgrp(1) owner - preinstall_cmd text, -- Shell command to execute prior to installing - postinstall_cmd text, -- Shell command to execute after installing - error_cmd text, -- Shell command to execute if any error occurs - ignore_cmd_errors bool NOT NULL DEFAULT false, -- Install file anyway even if an error occurs - always_update bool NOT NULL DEFAULT false -- Always attempt to install file even if unchanged -) WITH OIDS; - -CREATE TABLE conf_file_node ( - conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier - node_id integer REFERENCES nodes NOT NULL, -- Node identifier - PRIMARY KEY (conf_file_id, node_id) -); -CREATE INDEX conf_file_node_conf_file_id_idx ON conf_file_node (conf_file_id); -CREATE INDEX conf_file_node_node_id_idx ON conf_file_node (node_id); - --- Nodes linked to each configuration file -CREATE OR REPLACE VIEW conf_file_nodes AS -SELECT conf_file_id, -array_accum(node_id) AS node_ids -FROM conf_file_node -GROUP BY conf_file_id; - --- Configuration files linked to each node -CREATE OR REPLACE VIEW node_conf_files AS -SELECT node_id, -array_accum(conf_file_id) AS conf_file_ids -FROM conf_file_node -GROUP BY node_id; - -CREATE TABLE conf_file_nodegroup ( - conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier - nodegroup_id integer REFERENCES nodegroups NOT NULL, -- Node group identifier - PRIMARY KEY (conf_file_id, nodegroup_id) -); -CREATE INDEX conf_file_nodegroup_conf_file_id_idx ON conf_file_nodegroup (conf_file_id); -CREATE INDEX conf_file_nodegroup_nodegroup_id_idx ON conf_file_nodegroup (nodegroup_id); - --- Node groups linked to each configuration file -CREATE OR REPLACE VIEW conf_file_nodegroups AS -SELECT conf_file_id, -array_accum(nodegroup_id) AS nodegroup_ids -FROM conf_file_nodegroup -GROUP BY conf_file_id; - --- Configuration files linked to each node group -CREATE OR REPLACE VIEW nodegroup_conf_files AS -SELECT nodegroup_id, -array_accum(conf_file_id) AS conf_file_ids -FROM conf_file_nodegroup -GROUP BY nodegroup_id; - --------------------------------------------------------------------------------- --- Node network interfaces +-- (network) interfaces -------------------------------------------------------------------------------- -- Valid network addressing schemes @@ -439,8 +352,13 @@ INSERT INTO network_types (type) VALUES ('ipv4'); CREATE TABLE network_methods ( method text PRIMARY KEY -- Configuration method ) WITH OIDS; -INSERT INTO network_methods (method) VALUES - ('static'), ('dhcp'), ('proxy'), ('tap'), ('ipmi'), ('unknown'); + +INSERT INTO network_methods (method) VALUES ('static'); +INSERT INTO network_methods (method) VALUES ('dhcp'); +INSERT INTO network_methods (method) VALUES ('proxy'); +INSERT INTO network_methods (method) VALUES ('tap'); +INSERT INTO network_methods (method) VALUES ('ipmi'); +INSERT INTO network_methods (method) VALUES ('unknown'); -- Node network interfaces CREATE TABLE interfaces ( @@ -479,28 +397,19 @@ FROM interfaces_ordered GROUP BY node_id; -------------------------------------------------------------------------------- --- Interface setting types and interfaces settings +-- Interface settings -------------------------------------------------------------------------------- -CREATE TABLE interface_setting_types ( - interface_setting_type_id serial PRIMARY KEY, -- Setting Type Identifier - name text UNIQUE NOT NULL, -- Setting Name - description text, -- Optional Description - category text NOT NULL DEFAULT 'general', -- Free text for grouping, e.g. Wifi, or whatever - min_role_id integer REFERENCES roles DEFAULT 10 -- If set, minimal role required -) WITH OIDS; - CREATE TABLE interface_setting ( interface_setting_id serial PRIMARY KEY, -- Interface Setting Identifier interface_id integer REFERENCES interfaces NOT NULL,-- the interface this applies to - interface_setting_type_id integer - REFERENCES interface_setting_types NOT NULL, -- the setting type + tag_type_id integer REFERENCES tag_types NOT NULL, -- the setting type value text -- value attached ) WITH OIDS; CREATE OR REPLACE VIEW interface_settings AS SELECT interface_id, -array_accum(interface_setting_id) AS setting_ids +array_accum(interface_setting_id) AS interface_setting_ids FROM interface_setting GROUP BY interface_id; @@ -508,14 +417,14 @@ CREATE OR REPLACE VIEW view_interface_settings AS SELECT interface_setting.interface_setting_id, interface_setting.interface_id, -interface_setting_types.interface_setting_type_id, -interface_setting_types.name, -interface_setting_types.description, -interface_setting_types.category, -interface_setting_types.min_role_id, +tag_types.tag_type_id, +tag_types.tagname, +tag_types.description, +tag_types.category, +tag_types.min_role_id, interface_setting.value FROM interface_setting -INNER JOIN interface_setting_types USING (interface_setting_type_id); +INNER JOIN tag_types USING (tag_type_id); CREATE OR REPLACE VIEW view_interfaces AS SELECT @@ -534,9 +443,141 @@ interfaces.dns1, interfaces.dns2, interfaces.bwlimit, interfaces.hostname, -COALESCE((SELECT setting_ids FROM interface_settings WHERE interface_settings.interface_id = interfaces.interface_id), '{}') AS setting_ids +COALESCE((SELECT interface_setting_ids FROM interface_settings WHERE interface_settings.interface_id = interfaces.interface_id), '{}') AS interface_setting_ids FROM interfaces; +-------------------------------------------------------------------------------- +-- ilinks : links between interfaces +-------------------------------------------------------------------------------- +CREATE TABLE ilink ( + ilink_id serial PRIMARY KEY, -- id + tag_type_id integer REFERENCES tag_types, -- id of the tag type + src_interface_id integer REFERENCES interfaces not NULL, -- id of src interface + dst_interface_id integer REFERENCES interfaces NOT NULL, -- id of dst interface + value text -- optional value on the link +) WITH OIDS; + +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; + +-------------------------------------------------------------------------------- +-- Node groups +-------------------------------------------------------------------------------- + +-- Node groups +CREATE TABLE nodegroups ( + nodegroup_id serial PRIMARY KEY, -- Group identifier + 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 +) WITH OIDS; + +-- xxx - first rough implem. similar to former semantics but might be slow +CREATE OR REPLACE VIEW nodegroup_node AS +SELECT nodegroup_id, node_id +FROM tag_types +JOIN node_tag +USING (tag_type_id) +JOIN nodegroups +USING (tag_type_id,tagvalue); + +CREATE OR REPLACE VIEW nodegroup_nodes AS +SELECT nodegroup_id, +array_accum(node_id) AS node_ids +FROM nodegroup_node +GROUP BY nodegroup_id; + +-- Node groups that each node is a member of +CREATE OR REPLACE VIEW node_nodegroups AS +SELECT node_id, +array_accum(nodegroup_id) AS nodegroup_ids +FROM nodegroup_node +GROUP BY node_id; + +-------------------------------------------------------------------------------- +-- Node configuration files +-------------------------------------------------------------------------------- + +CREATE TABLE conf_files ( + conf_file_id serial PRIMARY KEY, -- Configuration file identifier + enabled bool NOT NULL DEFAULT true, -- Configuration file is active + source text NOT NULL, -- Relative path on the boot server + -- where file can be downloaded + dest text NOT NULL, -- Absolute path where file should be installed + file_permissions text NOT NULL DEFAULT '0644', -- chmod(1) permissions + file_owner text NOT NULL DEFAULT 'root', -- chown(1) owner + file_group text NOT NULL DEFAULT 'root', -- chgrp(1) owner + preinstall_cmd text, -- Shell command to execute prior to installing + postinstall_cmd text, -- Shell command to execute after installing + error_cmd text, -- Shell command to execute if any error occurs + ignore_cmd_errors bool NOT NULL DEFAULT false, -- Install file anyway even if an error occurs + always_update bool NOT NULL DEFAULT false -- Always attempt to install file even if unchanged +) WITH OIDS; + +CREATE TABLE conf_file_node ( + conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier + node_id integer REFERENCES nodes NOT NULL, -- Node identifier + PRIMARY KEY (conf_file_id, node_id) +); +CREATE INDEX conf_file_node_conf_file_id_idx ON conf_file_node (conf_file_id); +CREATE INDEX conf_file_node_node_id_idx ON conf_file_node (node_id); + +-- Nodes linked to each configuration file +CREATE OR REPLACE VIEW conf_file_nodes AS +SELECT conf_file_id, +array_accum(node_id) AS node_ids +FROM conf_file_node +GROUP BY conf_file_id; + +-- Configuration files linked to each node +CREATE OR REPLACE VIEW node_conf_files AS +SELECT node_id, +array_accum(conf_file_id) AS conf_file_ids +FROM conf_file_node +GROUP BY node_id; + +CREATE TABLE conf_file_nodegroup ( + conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier + nodegroup_id integer REFERENCES nodegroups NOT NULL, -- Node group identifier + PRIMARY KEY (conf_file_id, nodegroup_id) +); +CREATE INDEX conf_file_nodegroup_conf_file_id_idx ON conf_file_nodegroup (conf_file_id); +CREATE INDEX conf_file_nodegroup_nodegroup_id_idx ON conf_file_nodegroup (nodegroup_id); + +-- Node groups linked to each configuration file +CREATE OR REPLACE VIEW conf_file_nodegroups AS +SELECT conf_file_id, +array_accum(nodegroup_id) AS nodegroup_ids +FROM conf_file_nodegroup +GROUP BY conf_file_id; + +-- Configuration files linked to each node group +CREATE OR REPLACE VIEW nodegroup_conf_files AS +SELECT nodegroup_id, +array_accum(conf_file_id) AS conf_file_ids +FROM conf_file_nodegroup +GROUP BY nodegroup_id; + -------------------------------------------------------------------------------- -- Power control units (PCUs) -------------------------------------------------------------------------------- @@ -595,11 +636,10 @@ GROUP BY pcu_id; CREATE TABLE slice_instantiations ( instantiation text PRIMARY KEY ) WITH OIDS; -INSERT INTO slice_instantiations (instantiation) VALUES - ('not-instantiated'), -- Placeholder slice - ('plc-instantiated'), -- Instantiated by Node Manager - ('delegated'), -- Manually instantiated - ('nm-controller'); -- NM Controller +INSERT INTO slice_instantiations (instantiation) VALUES ('not-instantiated'); -- Placeholder slice +INSERT INTO slice_instantiations (instantiation) VALUES ('plc-instantiated'); -- Instantiated by Node Manager +INSERT INTO slice_instantiations (instantiation) VALUES ('delegated'); -- Manually instantiated +INSERT INTO slice_instantiations (instantiation) VALUES ('nm-controller'); -- NM Controller -- Slices CREATE TABLE slices ( @@ -706,23 +746,13 @@ GROUP BY node_id; -- Slice attributes -------------------------------------------------------------------------------- --- Slice attribute types -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 -) WITH OIDS; - -- Slice/sliver attributes CREATE TABLE slice_attribute ( slice_attribute_id serial PRIMARY KEY, -- Slice attribute identifier slice_id integer REFERENCES slices NOT NULL, -- Slice identifier node_id integer REFERENCES nodes, -- Sliver attribute if set nodegroup_id integer REFERENCES nodegroups, -- Node group attribute if set - attribute_type_id integer -- Attribute type identifier - REFERENCES slice_attribute_types NOT NULL, + tag_type_id integer REFERENCES tag_types NOT NULL, -- Attribute type identifier value text ) WITH OIDS; CREATE INDEX slice_attribute_slice_id_idx ON slice_attribute (slice_id); @@ -1076,14 +1106,14 @@ LEFT JOIN node_session USING (node_id); CREATE OR REPLACE VIEW view_nodegroups AS SELECT nodegroups.*, -node_tag_types.tagname, +tag_types.tagname, COALESCE((SELECT conf_file_ids FROM nodegroup_conf_files WHERE nodegroup_conf_files.nodegroup_id = nodegroups.nodegroup_id), '{}') AS conf_file_ids, COALESCE((SELECT node_ids FROM nodegroup_nodes WHERE nodegroup_nodes.nodegroup_id = nodegroups.nodegroup_id), '{}') AS node_ids -FROM nodegroups INNER JOIN node_tag_types USING (node_tag_type_id); +FROM nodegroups INNER JOIN tag_types USING (tag_type_id); CREATE OR REPLACE VIEW view_conf_files AS SELECT @@ -1174,13 +1204,14 @@ slice_attribute.slice_attribute_id, slice_attribute.slice_id, slice_attribute.node_id, slice_attribute.nodegroup_id, -slice_attribute_types.attribute_type_id, -slice_attribute_types.name, -slice_attribute_types.description, -slice_attribute_types.min_role_id, +tag_types.tag_type_id, +tag_types.tagname, +tag_types.description, +tag_types.category, +tag_types.min_role_id, slice_attribute.value FROM slice_attribute -INNER JOIN slice_attribute_types USING (attribute_type_id); +INNER JOIN tag_types USING (tag_type_id); CREATE OR REPLACE VIEW view_sessions AS SELECT @@ -1196,15 +1227,13 @@ LEFT JOIN node_session USING (session_id); -- Built-in maintenance account and default site -------------------------------------------------------------------------------- -INSERT INTO persons -(first_name, last_name, email, password, enabled) -VALUES -('Maintenance', 'Account', 'maint@localhost.localdomain', 'nopass', true); +INSERT INTO persons (first_name, last_name, email, password, enabled) +VALUES ('Maintenance', 'Account', 'maint@localhost.localdomain', 'nopass', true); -INSERT INTO person_role (person_id, role_id) - VALUES (1, 10), (1, 20), (1, 30), (1, 40); +INSERT INTO person_role (person_id, role_id) VALUES (1, 10); +INSERT INTO person_role (person_id, role_id) VALUES (1, 20); +INSERT INTO person_role (person_id, role_id) VALUES (1, 30); +INSERT INTO person_role (person_id, role_id) VALUES (1, 40); -INSERT INTO sites -(login_base, name, abbreviated_name, max_slices) -VALUES -('pl', 'PlanetLab Central', 'PLC', 100); +INSERT INTO sites (login_base, name, abbreviated_name, max_slices) +VALUES ('pl', 'PlanetLab Central', 'PLC', 100);