Send user an email when thier account has been disable or enabled. This should help...

[plcapi.git] / planetlab5.sql

diff --git a/planetlab5.sql b/planetlab5.sql
index 7219d9b..a9bca21 100644 (file)
--- a/planetlab5.sql
+++ b/planetlab5.sql
@@ -1,11 +1,12 @@
 --
 -- 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
 --
@@ -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
@@ -295,8 +306,8 @@ CREATE TABLE tag_types (
     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 (
@@ -341,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 (
@@ -472,7 +488,8 @@ 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
-    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
@@ -619,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 (
@@ -1211,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);