From 7b4d4be1d1cb063dae5f46adf93f51b55db8da89 Mon Sep 17 00:00:00 2001 From: Mark Huang Date: Fri, 27 Oct 2006 15:31:28 +0000 Subject: [PATCH] - add session support - whitespace nits --- planetlab4.sql | 91 +++++++++++++++++++++++++++++++++----------------- 1 file changed, 61 insertions(+), 30 deletions(-) diff --git a/planetlab4.sql b/planetlab4.sql index d2bba6c..0e28ec1 100644 --- 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 -------------------------------------------------------------------------------- -- 2.43.0