--
-- Copyright (C) 2006 The Trustees of Princeton University
--
--- $Id: planetlab4.sql,v 1.46 2006/11/27 12:18:12 thierry Exp $
+-- $Id: planetlab4.sql,v 1.49 2006/11/28 14:54:59 thierry Exp $
--
--------------------------------------------------------------------------------
-- Mandatory
node_id serial PRIMARY KEY, -- Node identifier
hostname text NOT NULL, -- Node hostname
- -- temporarily removed NOT NULL clause for foreign_nodes
- site_id integer REFERENCES sites, -- At which site
- -- may be NULL for local_nodes
- peer_id integer REFERENCES peers, -- From which peer
+ site_id integer REFERENCES sites NOT NULL, -- At which site
boot_state text REFERENCES boot_states NOT NULL DEFAULT 'inst', -- Node boot state
deleted boolean NOT NULL DEFAULT false, -- Is deleted
-- Timestamps
date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
- last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
+ last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+
+ peer_id integer REFERENCES peers -- From which peer
) WITH OIDS;
CREATE INDEX nodes_hostname_idx ON nodes (hostname) WHERE deleted IS false;
CREATE INDEX nodes_site_id_idx ON nodes (site_id) WHERE deleted IS false;
FROM nodes
GROUP BY site_id;
--- Nodes at each peer
-CREATE VIEW peer_nodes AS
-SELECT peer_id,
-array_accum(node_id) AS node_ids
-FROM nodes
-GROUP BY peer_id;
-
--------------------------------------------------------------------------------
-- Node groups
--------------------------------------------------------------------------------
-- Slices
CREATE TABLE slices (
slice_id serial PRIMARY KEY, -- Slice identifier
--- xxx temporarily remove the NOT NULL constraint
- site_id integer REFERENCES sites, -- Site identifier
+ site_id integer REFERENCES sites NOT NULL, -- Site identifier
peer_id integer REFERENCES peers, -- on which peer
name text NOT NULL, -- Slice name
max_nodes integer NOT NULL DEFAULT 100, -- Maximum number of nodes that can be assigned to this slice
--- xxx temporarily remove the NOT NULL constraint
- creator_person_id integer REFERENCES persons, -- Creator
+ creator_person_id integer REFERENCES persons NOT NULL, -- Creator
created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Creation date
expires timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP + '2 weeks', -- Expiration date
WHERE is_deleted is false
GROUP BY site_id;
-CREATE VIEW peer_slices AS
-SELECT peer_id,
-array_accum(slice_id) AS slice_ids
-FROM slices
-GROUP BY peer_id;
-
-- Slice membership
CREATE TABLE slice_person (
slice_id integer REFERENCES slices NOT NULL, -- Slice identifier
INSERT INTO object_types (object_type) VALUES ('NodeNetwork');
INSERT INTO object_types (object_type) VALUES ('Node');
INSERT INTO object_types (object_type) VALUES ('PCU');
+INSERT INTO object_types (object_type) VALUES ('Peer');
INSERT INTO object_types (object_type) VALUES ('Person');
INSERT INTO object_types (object_type) VALUES ('Role');
INSERT INTO object_types (object_type) VALUES ('Session');
LEFT JOIN person_keys USING (person_id)
LEFT JOIN person_slices USING (person_id);
+-- Nodes at each peer
+CREATE VIEW peer_nodes AS
+SELECT peer_id,
+array_accum(node_id) AS node_ids
+FROM nodes
+GROUP BY peer_id;
+
+CREATE VIEW peer_slices AS
+SELECT peer_id,
+array_accum(slice_id) AS slice_ids
+FROM slices
+GROUP BY peer_id;
+
CREATE VIEW view_peers AS
SELECT
peers.*,