- add session support

- whitespace nits

diff --git a/planetlab4.sql b/planetlab4.sql
index d2bba6c..0e28ec1 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.22 2006/10/24 19:54:38 mlhuang Exp $
+-- $Id: planetlab4.sql,v 1.23 2006/10/25 14:13:14 mlhuang Exp $
 --
 
 --------------------------------------------------------------------------------
@@ -267,7 +267,6 @@ CREATE TABLE nodes (
     -- XXX Should be key_id integer REFERENCES keys
     ssh_rsa_key text, -- SSH host key updated by Boot Manager
     key text, -- Node key generated by API when configuration file is downloaded
-    session text, -- Session key generated by PLC when Boot Manager authenticates
 
     -- Timestamps
     date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
@@ -605,15 +604,40 @@ FROM slice_attribute
 GROUP BY slice_id;
 
 --------------------------------------------------------------------------------
--- Events
+-- Authenticated sessions
 --------------------------------------------------------------------------------
 
--- event types
-CREATE TABLE event_types (
-       event_type text PRIMARY KEY -- Event type
+-- Authenticated sessions
+CREATE TABLE sessions (
+    session_id text PRIMARY KEY, -- Session identifier
+    expires timestamp without time zone
+) WITH OIDS;
 
+-- People can have multiple sessions
+CREATE TABLE person_session (
+    person_id integer REFERENCES persons NOT NULL, -- Account identifier
+    session_id text REFERENCES sessions NOT NULL, -- Session identifier
+    PRIMARY KEY (person_id, session_id),
+    UNIQUE (session_id) -- Sessions are unique
 ) WITH OIDS;
+CREATE INDEX person_session_person_id_idx ON person_session (person_id);
+
+-- Nodes can have only one session
+CREATE TABLE node_session (
+    node_id integer REFERENCES nodes NOT NULL, -- Node identifier
+    session_id text REFERENCES sessions NOT NULL, -- Session identifier
+    UNIQUE (node_id), -- Nodes can have only one session
+    UNIQUE (session_id) -- Sessions are unique
+) WITH OIDS;
+
+--------------------------------------------------------------------------------
+-- Events
+--------------------------------------------------------------------------------
 
+-- Event types
+CREATE TABLE event_types (
+    event_type text PRIMARY KEY -- Event type
+) WITH OIDS;
 INSERT INTO event_types (event_type) VALUES ('Add');
 INSERT INTO event_types (event_type) VALUES ('AddTo');
 INSERT INTO event_types (event_type) VALUES ('Get');
@@ -622,12 +646,10 @@ INSERT INTO event_types (event_type) VALUES ('Delete');
 INSERT INTO event_types (event_type) VALUES ('DeleteFrom');
 INSERT INTO event_types (event_type) VALUES ('Unknown');
 
--- object types
+-- Object types
 CREATE TABLE object_types (
-       object_type text PRIMARY KEY -- Object type 
-
+    object_type text PRIMARY KEY -- Object type 
 ) WITH OIDS;
-
 INSERT INTO object_types (object_type) VALUES ('AddressType');
 INSERT INTO object_types (object_type) VALUES ('Address');
 INSERT INTO object_types (object_type) VALUES ('BootState');
@@ -643,34 +665,31 @@ INSERT INTO object_types (object_type) VALUES ('Node');
 INSERT INTO object_types (object_type) VALUES ('PCU');
 INSERT INTO object_types (object_type) VALUES ('Person');
 INSERT INTO object_types (object_type) VALUES ('Role');
+INSERT INTO object_types (object_type) VALUES ('Session');
 INSERT INTO object_types (object_type) VALUES ('Site');
 INSERT INTO object_types (object_type) VALUES ('SliceAttributeType');
 INSERT INTO object_types (object_type) VALUES ('SliceAttribute');
 INSERT INTO object_types (object_type) VALUES ('Slice');
 INSERT INTO object_types (object_type) VALUES ('Unknown');
 
--- events
+-- Events
 CREATE TABLE events (
-       event_id serial PRIMARY KEY,  -- Event identifier
-       person_id  integer REFERENCES persons, -- person responsible for event
-       event_type text REFERENCES  event_types NOT NULL DEFAULT 'Unknown', -- Event type 
-       object_type text REFERENCES object_types NOT NULL DEFAULT 'Unknown', -- Object type associated with event
-       fault_code integer NOT NULL DEFAULT 0, -- did this event result in error
-        call text NOT NULL, -- call responsible for this event
-       runtime float, -- Event run time
-
-       -- Timestamps
-       time timestamp without time zone  NOT NULL DEFAULT CURRENT_TIMESTAMP
-   
+    event_id serial PRIMARY KEY,  -- Event identifier
+    person_id integer REFERENCES persons, -- Person responsible for event, if any
+    node_id integer REFERENCES nodes, -- Node responsible for event, if any
+    event_type text REFERENCES event_types NOT NULL DEFAULT 'Unknown', -- Event type 
+    object_type text REFERENCES object_types NOT NULL DEFAULT 'Unknown', -- Object type associated with event
+    fault_code integer NOT NULL DEFAULT 0, -- Did this event result in error
+    call text NOT NULL, -- Call responsible for this event
+    runtime float, -- Event run time
+    time timestamp without time zone  NOT NULL DEFAULT CURRENT_TIMESTAMP -- Event timestamp
 ) WITH OIDS;
 
--- event objects
+-- Event objects
 CREATE TABLE event_object (
-        event_id integer REFERENCES events NOT NULL, -- Event identifier
-        object_id integer NOT NULL -- Object identifier
-
+    event_id integer REFERENCES events NOT NULL, -- Event identifier
+    object_id integer NOT NULL -- Object identifier
 ) WITH OIDS;
-
 CREATE INDEX event_object_event_id_idx ON event_object (event_id);
 CREATE INDEX event_object_object_id_idx ON event_object (object_id);
 
@@ -679,6 +698,7 @@ SELECT event_id,
 array_to_string(array_accum(object_id), ',') AS object_ids
 FROM event_object
 GROUP BY event_id;
+
 --------------------------------------------------------------------------------
 -- Useful views
 --------------------------------------------------------------------------------
@@ -738,7 +758,6 @@ nodes.boot_nonce,
 nodes.version,
 nodes.ssh_rsa_key,
 nodes.key,
-nodes.session,
 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_nodenetworks.nodenetwork_ids,
@@ -746,13 +765,15 @@ node_nodegroups.nodegroup_ids,
 node_slices.slice_ids,
 node_pcus.pcu_ids,
 node_pcus.ports,
-node_conf_files.conf_file_ids
+node_conf_files.conf_file_ids,
+node_session.session_id AS session
 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_conf_files USING (node_id)
+LEFT JOIN node_session USING (node_id);
 
 CREATE VIEW view_nodegroups AS
 SELECT
@@ -877,6 +898,16 @@ slice_attribute.value
 FROM slice_attribute
 INNER JOIN slice_attribute_types USING (attribute_type_id);
 
+CREATE VIEW view_sessions AS
+SELECT
+sessions.session_id,
+CAST(date_part('epoch', sessions.expires) AS bigint) AS expires,
+person_session.person_id,
+node_session.node_id
+FROM sessions
+LEFT JOIN person_session USING (session_id)
+LEFT JOIN node_session USING (session_id);
+
 --------------------------------------------------------------------------------
 -- Built-in maintenance account and default site
 --------------------------------------------------------------------------------