--
-- PlanetLab Central database schema
--- Version 4, PostgreSQL
+-- Version 5, PostgreSQL
--
-- Aaron Klingaman <alk@cs.princeton.edu>
-- Reid Moran <rmoran@cs.princeton.edu>
-- Mark Huang <mlhuang@cs.princeton.edu>
-- Tony Mack <tmack@cs.princeton.edu>
+-- Thierry Parmentelat <thierry.parmentelat@sophia.inria.fr>
--
-- Copyright (C) 2006 The Trustees of Princeton University
--
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 (
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
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 (
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
--------------------------------------------------------------------------------
-- 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;
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
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 (
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;
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
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)
--------------------------------------------------------------------------------
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 (
-- 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);
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
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
-- 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);
git://git.onelab.eu / plcapi.git / blobdiff