- added events_objects table
[plcapi.git] / planetlab4.sql
index 335a688..1dae758 100644 (file)
@@ -1,4 +1,4 @@
-
+--
 -- PlanetLab Central database schema
 -- Version 4, PostgreSQL
 --
@@ -9,7 +9,7 @@
 --
 -- Copyright (C) 2006 The Trustees of Princeton University
 --
--- $Id: planetlab4.sql,v 1.6 2006/10/06 18:19:07 mlhuang Exp $
+-- $Id: planetlab4.sql,v 1.12 2006/10/16 21:57:17 mlhuang Exp $
 --
 
 --------------------------------------------------------------------------------
@@ -54,7 +54,7 @@ CREATE TABLE persons (
     date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
     last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
 ) WITH OIDS;
-CREATE INDEX persons_email_key ON persons (email) WHERE deleted IS false;
+CREATE INDEX persons_email_idx ON persons (email) WHERE deleted IS false;
 
 --------------------------------------------------------------------------------
 -- Sites
@@ -81,7 +81,7 @@ CREATE TABLE sites (
     date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
     last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
 ) WITH OIDS;
-CREATE INDEX sites_login_base_key ON sites (login_base) WHERE deleted IS false;
+CREATE INDEX sites_login_base_idx ON sites (login_base) WHERE deleted IS false;
 
 -- Account site membership
 CREATE TABLE person_site (
@@ -90,8 +90,8 @@ CREATE TABLE person_site (
     is_primary boolean NOT NULL DEFAULT false, -- Is the primary site for this account
     PRIMARY KEY (person_id, site_id)
 );
-CREATE INDEX person_site_person_id_key ON person_site (person_id);
-CREATE INDEX person_site_site_id_key ON person_site (site_id);
+CREATE INDEX person_site_person_id_idx ON person_site (person_id);
+CREATE INDEX person_site_site_id_idx ON person_site (site_id);
 
 -- Ordered by primary site first
 CREATE VIEW person_site_ordered AS
@@ -182,11 +182,10 @@ CREATE TABLE keys (
 CREATE TABLE person_key (
     person_id integer REFERENCES persons NOT NULL, -- Account identifier
     key_id integer REFERENCES keys NOT NULL, -- Key identifier
-    is_primary boolean NOT NULL DEFAULT false, -- Is the primary key for this account
     PRIMARY KEY (person_id, key_id)
 ) WITH OIDS;
-CREATE INDEX person_key_person_id_key ON person_key (person_id);
-CREATE INDEX person_key_key_id_key ON person_key (key_id);
+CREATE INDEX person_key_person_id_idx ON person_key (person_id);
+CREATE INDEX person_key_key_id_idx ON person_key (key_id);
 
 CREATE VIEW person_keys AS
 SELECT person_id,
@@ -215,7 +214,7 @@ CREATE TABLE person_role (
     role_id integer REFERENCES roles NOT NULL, -- Role identifier
     PRIMARY KEY (person_id, role_id)
 ) WITH OIDS;
-CREATE INDEX person_role_person_id_key ON person_role (person_id);
+CREATE INDEX person_role_person_id_idx ON person_role (person_id);
 
 -- Account roles
 CREATE VIEW person_roles AS
@@ -247,7 +246,7 @@ CREATE TABLE nodes (
     node_id serial PRIMARY KEY, -- Node identifier
     hostname text NOT NULL, -- Node hostname
     site_id integer REFERENCES sites NOT NULL, -- At which site
-    boot_state text REFERENCES boot_states NOT NULL, -- Node boot state
+    boot_state text REFERENCES boot_states NOT NULL DEFAULT 'inst', -- Node boot state
     deleted boolean NOT NULL DEFAULT false, -- Is deleted
 
     -- Optional
@@ -263,8 +262,8 @@ CREATE TABLE nodes (
     date_created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
     last_updated timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
 ) WITH OIDS;
-CREATE INDEX nodes_hostname_key ON nodes (hostname) WHERE deleted IS false;
-CREATE INDEX nodes_site_id_key ON nodes (site_id) WHERE deleted IS false;
+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;
 
 -- Nodes at each site
 CREATE VIEW site_nodes AS
@@ -290,8 +289,8 @@ CREATE TABLE nodegroup_node (
     node_id integer REFERENCES nodes NOT NULL, -- Node identifier
     PRIMARY KEY (nodegroup_id, node_id)
 ) WITH OIDS;
-CREATE INDEX nodegroup_node_nodegroup_id_key ON nodegroup_node (nodegroup_id);
-CREATE INDEX nodegroup_node_node_id_key ON nodegroup_node (node_id);
+CREATE INDEX nodegroup_node_nodegroup_id_idx ON nodegroup_node (nodegroup_id);
+CREATE INDEX nodegroup_node_node_id_idx ON nodegroup_node (node_id);
 
 -- Nodes in each node gruop
 CREATE VIEW nodegroup_nodes AS
@@ -350,7 +349,7 @@ CREATE TABLE nodenetworks (
     bwlimit integer, -- Bandwidth limit in bps
     hostname text -- Hostname of this interface
 ) WITH OIDS;
-CREATE INDEX nodenetworks_node_id_key ON nodenetworks (node_id);
+CREATE INDEX nodenetworks_node_id_idx ON nodenetworks (node_id);
 
 -- Ordered by primary interface first
 CREATE VIEW nodenetworks_ordered AS
@@ -373,7 +372,7 @@ CREATE TABLE pcus (
     -- Mandatory
     pcu_id serial PRIMARY KEY, -- PCU identifier
     site_id integer REFERENCES sites NOT NULL, -- Site identifier
-    hostname text NOT NULL, -- Hostname, not necessarily unique (multiple logical sites could use the same PCU)
+    hostname text, -- Hostname, not necessarily unique (multiple logical sites could use the same PCU)
     ip text NOT NULL, -- IP, not necessarily unique
 
     -- Optional
@@ -383,15 +382,30 @@ CREATE TABLE pcus (
     model text, -- Model, e.g. BayTech or iPal
     notes text -- Random notes
 ) WITH OIDS;
+CREATE INDEX pcus_site_id_idx ON pcus (site_id);
+
+CREATE VIEW site_pcus AS
+SELECT site_id,
+array_to_string(array_accum(pcu_id), ',') AS pcu_ids
+FROM pcus
+GROUP BY site_id;
 
 CREATE TABLE pcu_node (
     pcu_id integer REFERENCES pcus NOT NULL, -- PCU identifier
     node_id integer REFERENCES nodes NOT NULL, -- Node identifier
     port integer NOT NULL, -- Port number
-    PRIMARY KEY (pcu_id, node_id)
+    PRIMARY KEY (pcu_id, node_id), -- The same node cannot be controlled by different ports
+    UNIQUE (pcu_id, port) -- The same port cannot control multiple nodes
 );
-CREATE INDEX pcu_node_pcu_id_key ON pcu_node (pcu_id);
-CREATE INDEX pcu_node_node_id_key ON pcu_node (node_id);
+CREATE INDEX pcu_node_pcu_id_idx ON pcu_node (pcu_id);
+CREATE INDEX pcu_node_node_id_idx ON pcu_node (node_id);
+
+CREATE VIEW node_pcus AS
+SELECT node_id,
+array_to_string(array_accum(pcu_id), ',') AS pcu_ids,
+array_to_string(array_accum(port), ',') AS ports
+FROM pcu_node
+GROUP BY node_id;
 
 CREATE VIEW pcu_nodes AS
 SELECT pcu_id,
@@ -428,16 +442,16 @@ CREATE TABLE slices (
 
     is_deleted boolean NOT NULL DEFAULT false
 ) WITH OIDS;
-CREATE INDEX slices_site_id_key ON slices (site_id) WHERE is_deleted IS false;
-CREATE INDEX slices_name_key ON slices (name) WHERE is_deleted IS false;
+CREATE INDEX slices_site_id_idx ON slices (site_id) WHERE is_deleted IS false;
+CREATE INDEX slices_name_idx ON slices (name) WHERE is_deleted IS false;
 
 -- Slivers
 CREATE TABLE slice_node (
     slice_id integer REFERENCES slices NOT NULL, -- Slice identifier
     node_id integer REFERENCES nodes NOT NULL -- Node identifier
 ) WITH OIDS;
-CREATE INDEX slice_node_slice_id_key ON slice_node (slice_id);
-CREATE INDEX slice_node_node_id_key ON slice_node (node_id);
+CREATE INDEX slice_node_slice_id_idx ON slice_node (slice_id);
+CREATE INDEX slice_node_node_id_idx ON slice_node (node_id);
 
 -- Synonym for slice_node
 CREATE VIEW slivers AS
@@ -470,8 +484,8 @@ CREATE TABLE slice_person (
     person_id integer REFERENCES persons NOT NULL, -- Account identifier
     PRIMARY KEY (slice_id, person_id)
 ) WITH OIDS;
-CREATE INDEX slice_person_slice_id_key ON slice_person (slice_id);
-CREATE INDEX slice_person_person_id_key ON slice_person (person_id);
+CREATE INDEX slice_person_slice_id_idx ON slice_person (slice_id);
+CREATE INDEX slice_person_person_id_idx ON slice_person (person_id);
 
 -- Members of the slice
 CREATE VIEW slice_persons AS
@@ -491,9 +505,9 @@ GROUP BY person_id;
 -- Attributes
 --------------------------------------------------------------------------------
 
--- Generic attribute types
-CREATE TABLE attributes (
-    attribute_id serial PRIMARY KEY, -- Attribute type identifier
+-- Slice attribute types
+CREATE TABLE slice_attribute_types (
+    attribute_type_id serial PRIMARY KEY, -- Attribute type identifier
     name text UNIQUE NOT NULL, -- Attribute name
     description text, -- Attribute description
     min_role_id integer REFERENCES roles DEFAULT 10 -- If set, minimum (least powerful) role that can set or change this attribute
@@ -504,11 +518,11 @@ CREATE TABLE slice_attribute (
     slice_attribute_id serial PRIMARY KEY, -- Slice attribute identifier
     slice_id integer REFERENCES slices NOT NULL, -- Slice identifier
     node_id integer REFERENCES nodes, -- Sliver attribute if set
-    attribute_id integer REFERENCES attributes NOT NULL, -- Attribute identifier
+    attribute_type_id integer REFERENCES slice_attribute_types NOT NULL, -- Attribute type identifier
     value text
 ) WITH OIDS;
-CREATE INDEX slice_attribute_slice_id_key ON slice_attribute (slice_id);
-CREATE INDEX slice_attribute_node_id_key ON slice_attribute (node_id);
+CREATE INDEX slice_attribute_slice_id_idx ON slice_attribute (slice_id);
+CREATE INDEX slice_attribute_node_id_idx ON slice_attribute (node_id);
 
 CREATE VIEW slice_attributes AS
 SELECT slice_id,
@@ -516,20 +530,79 @@ array_to_string(array_accum(slice_attribute_id), ',') AS slice_attribute_ids
 FROM slice_attribute
 GROUP BY slice_id;
 
--- Node attributes
-CREATE TABLE node_attribute (
-    node_attribute_id serial PRIMARY KEY, -- Node attribute identifier
-    node_id integer REFERENCES nodes NOT NULL, -- Node identifier
-    attribute_id integer REFERENCES attributes NOT NULL, -- Attribute identifier
-    value text
+--------------------------------------------------------------------------------
+-- Events
+--------------------------------------------------------------------------------
+
+-- event types
+CREATE TABLE event_types (
+       event_type text PRIMARY KEY -- Event type
+
 ) WITH OIDS;
-CREATE INDEX node_attribute_node_id_key ON node_attribute (node_id);
 
-CREATE VIEW node_attributes AS
-SELECT node_id,
-array_to_string(array_accum(node_attribute_id), ',') AS node_attribute_ids
-FROM node_attribute
-GROUP BY node_id;
+INSERT INTO event_types (event_type) VALUES ('Add');
+INSERT INTO event_types (event_type) VALUES ('Get');
+INSERT INTO event_types (event_type) VALUES ('Update');
+INSERT INTO event_types (event_type) VALUES ('Delete');
+INSERT INTO event_types (event_type) VALUES ('Unknown');
+
+-- object types
+CREATE TABLE object_types (
+       object_type text PRIMARY KEY -- Object type 
+
+) WITH OIDS;
+
+INSERT INTO object_types (object_type) VALUES ('Person');
+INSERT INTO object_types (object_type) VALUES ('Site');
+INSERT INTO object_types (object_type) VALUES ('Node');
+INSERT INTO object_types (object_type) VALUES ('Slice');
+INSERT INTO object_types (object_type) VALUES ('Address');
+INSERT INTO object_types (object_type) VALUES ('Attribute');
+INSERT INTO object_types (object_type) VALUES ('Key');
+INSERT INTO object_types (object_type) VALUES ('Nodegroup');
+INSERT INTO object_types (object_type) VALUES ('Unknown');
+
+-- fault types
+CREATE TABLE fault_types (
+       fault_code integer PRIMARY KEY, -- Fault identifier
+       fault_type text UNIQUE NOT NULL -- Fault type
+
+) WITH OIDS;
+
+INSERT INTO fault_types (fault_code, fault_type) VALUES (0, 'Success');
+INSERT INTO fault_types (fault_code, fault_type) VALUES (100, 'PLCInvalidAPIMethod');
+INSERT INTO fault_types (fault_code, fault_type) VALUES (101, 'PLCInvalidArgumentCount');
+INSERT INTO fault_types (fault_code, fault_type) VALUES (102, 'PLCInvalidArgument');
+INSERT INTO fault_types (fault_code, fault_type) VALUES (103, 'PLCAuthenticationFailure');
+INSERT INTO fault_types (fault_code, fault_type) VALUES (109, 'PLCNotImplemented');
+INSERT INTO fault_types (fault_code, fault_type) VALUES (106, 'PLCDBError');
+INSERT INTO fault_types (fault_code, fault_type) VALUES (108, 'PLCPermissionDenied');
+INSERT INTO fault_types (fault_code, fault_type) VALUES (111, 'PLCAPIError');
+
+
+-- events
+CREATE TABLE events (
+       event_id serial PRIMARY KEY,  -- Event identifier
+       person_id integer REFERENCES persons NOT NULL, -- 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 REFERENCES fault_types NOT NULL DEFAULT 0, -- did this event result in error
+        call text NOT NULL, -- Call name
+       
+       -- Optional
+       runtime float,  -- Event run time
+
+       -- Timestamps
+       time timestamp without time zone  NOT NULL DEFAULT CURRENT_TIMESTAMP
+   
+) WITH OIDS;
+
+-- event objects
+CREATE TABLE event_objects (
+        event_id integer REFERENCES events NOT NULL, -- Event identifier
+        object_id integer NOT NULL -- Object identifier
+
+) WITH OIDS;
 
 --------------------------------------------------------------------------------
 -- Useful views
@@ -579,23 +652,14 @@ 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,
 node_nodegroups.nodegroup_ids,
-node_slices.slice_ids
+node_slices.slice_ids,
+node_pcus.pcu_ids,
+node_pcus.ports
 FROM nodes
 LEFT JOIN node_nodenetworks USING (node_id)
 LEFT JOIN node_nodegroups USING (node_id)
-LEFT JOIN node_slices USING (node_id);
-
-CREATE VIEW view_node_attributes AS
-SELECT
-node_attribute.node_attribute_id,
-node_attribute.node_id,
-attributes.attribute_id,
-attributes.name,
-attributes.description,
-attributes.min_role_id,
-node_attribute.value
-FROM node_attribute
-INNER JOIN attributes USING (attribute_id);
+LEFT JOIN node_slices USING (node_id)
+LEFT JOIN node_pcus USING (node_id);
 
 CREATE VIEW view_nodegroups AS
 SELECT
@@ -606,6 +670,22 @@ nodegroup_nodes.node_ids
 FROM nodegroups
 LEFT JOIN nodegroup_nodes USING (nodegroup_id);
 
+CREATE VIEW view_pcus AS
+SELECT
+pcus.pcu_id,
+pcus.site_id,
+pcus.hostname,
+pcus.ip,
+pcus.protocol,
+pcus.username,
+pcus.password,
+pcus.model,
+pcus.notes,
+pcu_nodes.node_ids,
+pcu_nodes.ports
+FROM pcus
+LEFT JOIN pcu_nodes USING (pcu_id);
+
 CREATE VIEW view_sites AS
 SELECT
 sites.site_id,
@@ -624,12 +704,14 @@ CAST(date_part('epoch', sites.last_updated) AS bigint) AS last_updated,
 site_persons.person_ids,
 site_nodes.node_ids,
 site_addresses.address_ids,
-site_slices.slice_ids
+site_slices.slice_ids,
+site_pcus.pcu_ids
 FROM sites
 LEFT JOIN site_persons USING (site_id)
 LEFT JOIN site_nodes USING (site_id)
 LEFT JOIN site_addresses USING (site_id)
-LEFT JOIN site_slices USING (site_id);
+LEFT JOIN site_slices USING (site_id)
+LEFT JOIN site_pcus USING (site_id);
 
 CREATE VIEW view_addresses AS
 SELECT
@@ -673,13 +755,13 @@ SELECT
 slice_attribute.slice_attribute_id,
 slice_attribute.slice_id,
 slice_attribute.node_id,
-attributes.attribute_id,
-attributes.name,
-attributes.description,
-attributes.min_role_id,
+slice_attribute_types.attribute_type_id,
+slice_attribute_types.name,
+slice_attribute_types.description,
+slice_attribute_types.min_role_id,
 slice_attribute.value
 FROM slice_attribute
-INNER JOIN attributes USING (attribute_id);
+INNER JOIN slice_attribute_types USING (attribute_type_id);
 
 --------------------------------------------------------------------------------
 -- Built-in maintenance account and default site