--
-- 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
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 (
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 (
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
- tagvalue text NOT NULL -- with this value attached
+ -- 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 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 (
-- 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);