federation in progress - associate a local slice to a foreign node

[plcapi.git] / planetlab4.sql

diff --git a/planetlab4.sql b/planetlab4.sql
index 5d3e264..9e1d1d5 100644 (file)
--- a/planetlab4.sql
+++ b/planetlab4.sql
@@ -9,7 +9,7 @@
 --
 -- Copyright (C) 2006 The Trustees of Princeton University
 --
--- $Id: planetlab4.sql,v 1.26 2006/11/03 16:05:20 mlhuang Exp $
+-- $Id: planetlab4.sql,v 1.27 2006/11/03 20:36:05 thierry Exp $
 --
 
 --------------------------------------------------------------------------------
@@ -219,6 +219,8 @@ INSERT INTO roles (role_id, name) VALUES (30, 'user');
 INSERT INTO roles (role_id, name) VALUES (40, 'tech');
 INSERT INTO roles (role_id, name) VALUES (1000, 'node');
 INSERT INTO roles (role_id, name) VALUES (2000, 'anonymous');
+-- xxx not sure this us useful yet
+--INSERT INTO roles (role_id, name) VALUES (3000, 'peer');
 
 CREATE TABLE person_role (
     person_id integer REFERENCES persons NOT NULL, -- Account identifier
@@ -251,13 +253,26 @@ INSERT INTO boot_states (boot_state) VALUES ('rins');
 INSERT INTO boot_states (boot_state) VALUES ('rcnf');
 INSERT INTO boot_states (boot_state) VALUES ('new');
 
+-- Peers
+CREATE TABLE peers (
+     peer_id  serial PRIMARY KEY, -- identifier
+     peername text NOT NULL,      -- free text
+     peer_url text NOT NULL,      -- the url of that peer's API
+     person_id integer REFERENCES persons NOT NULL, -- the account we use for logging in
+       
+     deleted boolean NOT NULL DEFAULT false
+) WITH OIDS;
+
+
 -- Nodes
 CREATE TABLE nodes (
     -- Mandatory
     node_id serial PRIMARY KEY, -- Node identifier
     hostname text NOT NULL, -- Node hostname
-    site_id integer REFERENCES sites NOT NULL, -- At which site
+    site_id integer REFERENCES sites, -- At which site (clause NOT NULL removed for foreign_nodes)
     boot_state text REFERENCES boot_states NOT NULL DEFAULT 'inst', -- Node boot state
+    cached boolean NOT NULL DEFAULT false,  -- is this entry cached from a peer ?
+    peer_id integer REFERENCES peers,      -- if cached, then from what peer
     deleted boolean NOT NULL DEFAULT false, -- Is deleted
 
     -- Optional
@@ -282,6 +297,20 @@ array_to_string(array_accum(node_id), ',') AS node_ids
 FROM nodes
 GROUP BY site_id;
 
+-- Nodes at each peer
+CREATE VIEW peer_nodes AS
+SELECT peer_id,
+array_to_string(array_accum(node_id), ',') AS node_ids
+FROM nodes 
+GROUP BY peer_id;
+
+CREATE VIEW view_peers AS
+SELECT 
+peers.*, 
+peer_nodes.node_ids 
+FROM peers
+LEFT JOIN peer_nodes USING (peer_id);
+
 --------------------------------------------------------------------------------
 -- Node groups
 --------------------------------------------------------------------------------
@@ -761,7 +790,6 @@ nodes.node_id,
 nodes.hostname,
 nodes.site_id,
 nodes.boot_state,
-nodes.deleted,
 nodes.model,
 nodes.boot_nonce,
 nodes.version,
@@ -775,14 +803,32 @@ node_slices.slice_ids,
 node_pcus.pcu_ids,
 node_pcus.ports,
 node_conf_files.conf_file_ids,
-node_session.session_id AS session
+node_session.session_id AS session,
+nodes.deleted
 FROM nodes
 LEFT JOIN node_nodenetworks USING (node_id)
 LEFT JOIN node_nodegroups USING (node_id)
 LEFT JOIN node_slices USING (node_id)
 LEFT JOIN node_pcus USING (node_id)
 LEFT JOIN node_conf_files USING (node_id)
-LEFT JOIN node_session USING (node_id);
+LEFT JOIN node_session USING (node_id)
+WHERE nodes.cached=False;
+
+CREATE VIEW view_foreign_nodes AS
+SELECT
+nodes.node_id,
+nodes.hostname,
+nodes.peer_id,
+nodes.boot_state,
+nodes.model,
+nodes.version,
+CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
+CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
+node_slices.slice_ids,
+nodes.deleted
+FROM nodes
+LEFT JOIN node_slices USING (node_id)
+WHERE nodes.cached=True AND nodes.deleted=False;
 
 CREATE VIEW view_nodegroups AS
 SELECT
@@ -937,35 +983,20 @@ VALUES
 ('pl', 'PlanetLab Central', 'PLC', 100);
 
 -- federation stuff starting here
-INSERT INTO roles (role_id, name) VALUES (3000, 'peer');
 
-CREATE TABLE peers (
-     peer_id  serial PRIMARY KEY, -- identifier
-     peername text NOT NULL,      -- free text
-     peer_url text NOT NULL,      -- the url of that peer's API
-     person_id integer REFERENCES persons NOT NULL, -- the account we use for logging in
-       
-     deleted boolean NOT NULL DEFAULT false
-) WITH OIDS;
+--CREATE TABLE foreign_nodes (
+--     foreign_node_id serial PRIMARY KEY, -- identifier
+--     hostname text NOT NULL, 
+--     boot_state text NOT NULL,
+--     peer_id integer REFERENCES peers NOT NULL,
+--       
+--     deleted boolean NOT NULL DEFAULT false
+--) WITH OIDS;
 
-CREATE TABLE foreign_nodes (
-     foreign_node_id serial PRIMARY KEY, -- identifier
-     hostname text NOT NULL, 
-     boot_state text NOT NULL,
-     peer_id integer REFERENCES peers NOT NULL,
-       
-     deleted boolean NOT NULL DEFAULT false
-) WITH OIDS;
+--CREATE VIEW peer_foreign_nodes AS
+--SELECT peer_id,
+--array_to_string(array_accum(foreign_node_id), ',') AS foreign_node_ids
+--FROM foreign_nodes
+--GROUP BY peer_id;
 
-CREATE VIEW peer_foreign_nodes AS
-SELECT peer_id,
-array_to_string(array_accum(foreign_node_id), ',') AS foreign_node_ids
-FROM foreign_nodes
-GROUP BY peer_id;
 
-CREATE VIEW view_peers AS
-SELECT 
-peers.*, 
-peer_foreign_nodes.foreign_node_ids 
-FROM peers
-LEFT JOIN peer_foreign_nodes USING (peer_id);