-
+--
-- PlanetLab Central database schema
-- Version 4, PostgreSQL
--
--
-- Copyright (C) 2006 The Trustees of Princeton University
--
--- $Id: planetlab4.sql,v 1.5 2006/10/06 15:41:50 tmack Exp $
+-- $Id: planetlab4.sql,v 1.8 2006/10/11 15:37:12 mlhuang Exp $
--
--------------------------------------------------------------------------------
CREATE TABLE person_key (
person_id integer REFERENCES persons NOT NULL, -- Account identifier
key_id integer REFERENCES keys NOT NULL, -- Key identifier
- is_primary boolean NOT NULL DEFAULT false, -- Is the primary key for this account
PRIMARY KEY (person_id, key_id)
) WITH OIDS;
CREATE INDEX person_key_person_id_key ON person_key (person_id);
node_id serial PRIMARY KEY, -- Node identifier
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
+ boot_state text REFERENCES boot_states NOT NULL DEFAULT 'inst', -- Node boot state
deleted boolean NOT NULL DEFAULT false, -- Is deleted
-- Optional
--------------------------------------------------------------------------------
-- Valid network addressing schemes
-CREATE TABLE nodenetwork_types (
+CREATE TABLE network_types (
type text PRIMARY KEY -- Addressing scheme
) WITH OIDS;
-INSERT INTO nodenetwork_types (type) VALUES ('ipv4');
-INSERT INTO nodenetwork_types (type) VALUES ('ipv6');
+INSERT INTO network_types (type) VALUES ('ipv4');
+INSERT INTO network_types (type) VALUES ('ipv6');
-- Valid network configuration methods
-CREATE TABLE nodenetwork_methods (
+CREATE TABLE network_methods (
method text PRIMARY KEY -- Configuration method
) WITH OIDS;
-INSERT INTO nodenetwork_methods (method) VALUES ('static');
-INSERT INTO nodenetwork_methods (method) VALUES ('dhcp');
-INSERT INTO nodenetwork_methods (method) VALUES ('proxy');
-INSERT INTO nodenetwork_methods (method) VALUES ('tap');
-INSERT INTO nodenetwork_methods (method) VALUES ('ipmi');
-INSERT INTO nodenetwork_methods (method) VALUES ('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 nodenetworks (
nodenetwork_id serial PRIMARY KEY, -- Network interface identifier
node_id integer REFERENCES nodes NOT NULL, -- Which node
is_primary boolean NOT NULL DEFAULT false, -- Is the primary interface for this node
- type text REFERENCES nodenetwork_types NOT NULL, -- Addressing scheme
- method text REFERENCES nodenetwork_methods NOT NULL, -- Configuration method
+ type text REFERENCES network_types NOT NULL, -- Addressing scheme
+ method text REFERENCES network_methods NOT NULL, -- Configuration method
-- Optional, depending on type and method
ip text, -- IP address
FROM nodenetworks_ordered
GROUP BY node_id;
+--------------------------------------------------------------------------------
+-- Power control units (PCUs)
+--------------------------------------------------------------------------------
+
+CREATE TABLE pcus (
+ -- Mandatory
+ pcu_id serial PRIMARY KEY, -- PCU identifier
+ site_id integer REFERENCES sites NOT NULL, -- Site identifier
+ hostname text, -- Hostname, not necessarily unique (multiple logical sites could use the same PCU)
+ ip text NOT NULL, -- IP, not necessarily unique
+
+ -- Optional
+ protocol text, -- Protocol, e.g. ssh or https or telnet
+ username text, -- Username, if applicable
+ "password" text, -- Password, if applicable
+ model text, -- Model, e.g. BayTech or iPal
+ notes text -- Random notes
+) WITH OIDS;
+CREATE INDEX pcus_site_id_key ON pcus (site_id);
+
+CREATE VIEW site_pcus AS
+SELECT site_id,
+array_to_string(array_accum(pcu_id), ',') AS pcu_ids
+FROM pcus
+GROUP BY site_id;
+
+CREATE TABLE pcu_node (
+ pcu_id integer REFERENCES pcus NOT NULL, -- PCU identifier
+ node_id integer REFERENCES nodes NOT NULL, -- Node identifier
+ port integer NOT NULL, -- Port number
+ PRIMARY KEY (pcu_id, node_id)
+);
+CREATE INDEX pcu_node_pcu_id_key ON pcu_node (pcu_id);
+CREATE INDEX pcu_node_node_id_key ON pcu_node (node_id);
+
+CREATE VIEW pcu_nodes AS
+SELECT pcu_id,
+array_to_string(array_accum(node_id), ',') AS node_ids,
+array_to_string(array_accum(port), ',') AS ports
+FROM pcu_node
+GROUP BY pcu_id;
+
--------------------------------------------------------------------------------
-- Slices
--------------------------------------------------------------------------------
-- Attributes
--------------------------------------------------------------------------------
--- Generic attribute types
+-- Slice attribute types
CREATE TABLE attributes (
attribute_id serial PRIMARY KEY, -- Attribute type identifier
name text UNIQUE NOT NULL, -- Attribute name
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
- attribute_id integer REFERENCES attributes NOT NULL, -- Attribute identifier
+ attribute_id integer REFERENCES attributes NOT NULL, -- Attribute type identifier
value text
) WITH OIDS;
CREATE INDEX slice_attribute_slice_id_key ON slice_attribute (slice_id);
FROM slice_attribute
GROUP BY slice_id;
--- Node attributes
-CREATE TABLE node_attribute (
- node_attribute_id serial PRIMARY KEY, -- Node attribute identifier
- node_id integer REFERENCES nodes NOT NULL, -- Node identifier
- attribute_id integer REFERENCES attributes NOT NULL, -- Attribute identifier
- value text
-) WITH OIDS;
-CREATE INDEX node_attribute_node_id_key ON node_attribute (node_id);
-
-CREATE VIEW node_attributes AS
-SELECT node_id,
-array_to_string(array_accum(node_attribute_id), ',') AS node_attribute_ids
-FROM node_attribute
-GROUP BY node_id;
-
--------------------------------------------------------------------------------
-- Useful views
--------------------------------------------------------------------------------
LEFT JOIN node_nodegroups USING (node_id)
LEFT JOIN node_slices USING (node_id);
-CREATE VIEW view_node_attributes AS
-SELECT
-node_attribute.node_attribute_id,
-node_attribute.node_id,
-attributes.attribute_id,
-attributes.name,
-attributes.description,
-attributes.min_role_id,
-node_attribute.value
-FROM node_attribute
-INNER JOIN attributes USING (attribute_id);
-
CREATE VIEW view_nodegroups AS
SELECT
nodegroups.nodegroup_id,
FROM nodegroups
LEFT JOIN nodegroup_nodes USING (nodegroup_id);
+CREATE VIEW view_pcus AS
+SELECT
+pcus.pcu_id,
+pcus.site_id,
+pcus.hostname,
+pcus.ip,
+pcus.protocol,
+pcus.username,
+pcus.password,
+pcus.model,
+pcus.notes,
+pcu_nodes.node_ids,
+pcu_nodes.ports
+FROM pcus
+LEFT JOIN pcu_nodes USING (pcu_id);
+
CREATE VIEW view_sites AS
SELECT
sites.site_id,
site_persons.person_ids,
site_nodes.node_ids,
site_addresses.address_ids,
-site_slices.slice_ids
+site_slices.slice_ids,
+site_pcus.pcu_ids
FROM sites
LEFT JOIN site_persons USING (site_id)
LEFT JOIN site_nodes USING (site_id)
LEFT JOIN site_addresses USING (site_id)
-LEFT JOIN site_slices USING (site_id);
+LEFT JOIN site_slices USING (site_id)
+LEFT JOIN site_pcus USING (site_id);
CREATE VIEW view_addresses AS
SELECT
git://git.onelab.eu / plcapi.git / blobdiff