- create a separate site_address join table

[plcapi.git] / planetlab4.sql

diff --git a/planetlab4.sql b/planetlab4.sql
index da28e02..3a7e9f3 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.13 2006/10/17 15:27:38 tmack Exp $
+-- $Id: planetlab4.sql,v 1.21 2006/10/23 20:44:16 tmack Exp $
 --
 
 --------------------------------------------------------------------------------
@@ -121,7 +121,8 @@ CREATE TABLE address_types (
     address_type_id serial PRIMARY KEY, -- Address type identifier
     name text UNIQUE NOT NULL, -- Address type
     description text -- Address type description
-);
+) WITH OIDS;
+
 INSERT INTO address_types (name) VALUES ('Personal');
 INSERT INTO address_types (name) VALUES ('Shipping');
 -- XXX Used to be Site
@@ -130,7 +131,6 @@ INSERT INTO address_types (name) VALUES ('Billing');
 -- Mailing addresses
 CREATE TABLE addresses (
     address_id serial PRIMARY KEY, -- Address identifier
-    site_id integer REFERENCES sites NOT NULL, -- Site identifier
     line1 text NOT NULL, -- Address line 1
     line2 text, -- Address line 2
     line3 text, -- Address line 3
@@ -143,8 +143,11 @@ CREATE TABLE addresses (
 -- Each mailing address can be one of several types
 CREATE TABLE address_address_type (
     address_id integer REFERENCES addresses NOT NULL, -- Address identifier
-    address_type_id integer REFERENCES address_types NOT NULL -- Address type
+    address_type_id integer REFERENCES address_types NOT NULL, -- Address type
+    PRIMARY KEY (address_id, address_type_id)
 ) WITH OIDS;
+CREATE INDEX address_address_type_address_id_idx ON address_address_type (address_id);
+CREATE INDEX address_address_type_address_type_id_idx ON address_address_type (address_type_id);
 
 CREATE VIEW address_address_types AS
 SELECT address_id,
@@ -154,10 +157,18 @@ FROM address_address_type
 LEFT JOIN address_types USING (address_type_id)
 GROUP BY address_id;
 
+CREATE TABLE site_address (
+    site_id integer REFERENCES sites NOT NULL, -- Site identifier
+    address_id integer REFERENCES addresses NOT NULL, -- Address identifier
+    PRIMARY KEY (site_id, address_id)
+) WITH OIDS;
+CREATE INDEX site_address_site_id_idx ON site_address (site_id);
+CREATE INDEX site_address_address_id_idx ON site_address (address_id);
+
 CREATE VIEW site_addresses AS
 SELECT site_id,
 array_to_string(array_accum(address_id), ',') AS address_ids
-FROM addresses
+FROM site_address
 GROUP BY site_id;
 
 --------------------------------------------------------------------------------
@@ -306,6 +317,69 @@ array_to_string(array_accum(nodegroup_id), ',') AS nodegroup_ids
 FROM nodegroup_node
 GROUP BY node_id;
 
+--------------------------------------------------------------------------------
+-- Node configuration files
+--------------------------------------------------------------------------------
+
+CREATE TABLE conf_files (
+    conf_file_id serial PRIMARY KEY, -- Configuration file identifier
+    enabled bool NOT NULL DEFAULT true, -- Configuration file is active
+    source text NOT NULL, -- Relative path on the boot server where file can be downloaded
+    dest text NOT NULL, -- Absolute path where file should be installed
+    file_permissions text NOT NULL DEFAULT '0644', -- chmod(1) permissions
+    file_owner text NOT NULL DEFAULT 'root', -- chown(1) owner
+    file_group text NOT NULL DEFAULT 'root', -- chgrp(1) owner
+    preinstall_cmd text, -- Shell command to execute prior to installing
+    postinstall_cmd text, -- Shell command to execute after installing
+    error_cmd text, -- Shell command to execute if any error occurs
+    ignore_cmd_errors bool NOT NULL DEFAULT false, -- Install file anyway even if an error occurs
+    always_update bool NOT NULL DEFAULT false -- Always attempt to install file even if unchanged
+);
+
+CREATE TABLE conf_file_node (
+    conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier
+    node_id integer REFERENCES nodes NOT NULL, -- Node identifier
+    PRIMARY KEY (conf_file_id, node_id)
+);
+CREATE INDEX conf_file_node_conf_file_id_idx ON conf_file_node (conf_file_id);
+CREATE INDEX conf_file_node_node_id_idx ON conf_file_node (node_id);
+
+-- Nodes linked to each configuration file
+CREATE VIEW conf_file_nodes AS
+SELECT conf_file_id,
+array_to_string(array_accum(node_id), ',') AS node_ids
+FROM conf_file_node
+GROUP BY conf_file_id;
+
+-- Configuration files linked to each node
+CREATE VIEW node_conf_files AS
+SELECT node_id,
+array_to_string(array_accum(conf_file_id), ',') AS conf_file_ids
+FROM conf_file_node
+GROUP BY node_id;
+
+CREATE TABLE conf_file_nodegroup (
+    conf_file_id integer REFERENCES conf_files NOT NULL, -- Configuration file identifier
+    nodegroup_id integer REFERENCES nodegroups NOT NULL, -- Node group identifier
+    PRIMARY KEY (conf_file_id, nodegroup_id)
+);
+CREATE INDEX conf_file_nodegroup_conf_file_id_idx ON conf_file_nodegroup (conf_file_id);
+CREATE INDEX conf_file_nodegroup_nodegroup_id_idx ON conf_file_nodegroup (nodegroup_id);
+
+-- Node groups linked to each configuration file
+CREATE VIEW conf_file_nodegroups AS
+SELECT conf_file_id,
+array_to_string(array_accum(nodegroup_id), ',') AS nodegroup_ids
+FROM conf_file_nodegroup
+GROUP BY conf_file_id;
+
+-- Configuration files linked to each node group
+CREATE VIEW nodegroup_conf_files AS
+SELECT nodegroup_id,
+array_to_string(array_accum(conf_file_id), ',') AS conf_file_ids
+FROM conf_file_nodegroup
+GROUP BY nodegroup_id;
+
 --------------------------------------------------------------------------------
 -- Node network interfaces
 --------------------------------------------------------------------------------
@@ -448,7 +522,8 @@ 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
+    node_id integer REFERENCES nodes NOT NULL, -- Node identifier
+    PRIMARY KEY (slice_id, node_id)
 ) WITH OIDS;
 CREATE INDEX slice_node_slice_id_idx ON slice_node (slice_id);
 CREATE INDEX slice_node_node_id_idx ON slice_node (node_id);
@@ -502,7 +577,7 @@ FROM slice_person
 GROUP BY person_id;
 
 --------------------------------------------------------------------------------
--- Attributes
+-- Slice attributes
 --------------------------------------------------------------------------------
 
 -- Slice attribute types
@@ -541,9 +616,11 @@ CREATE TABLE event_types (
 ) 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');
 INSERT INTO event_types (event_type) VALUES ('Update');
 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
@@ -552,21 +629,31 @@ CREATE TABLE object_types (
 
 ) 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');
+INSERT INTO object_types (object_type) VALUES ('ConfFile');
+INSERT INTO object_types (object_type) VALUES ('KeyType');
+INSERT INTO object_types (object_type) VALUES ('Key');
+INSERT INTO object_types (object_type) VALUES ('NetworkMethod');
+INSERT INTO object_types (object_type) VALUES ('NetworkType');
+INSERT INTO object_types (object_type) VALUES ('Network');
+INSERT INTO object_types (object_type) VALUES ('NodeGroup');
+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 ('Person');
+INSERT INTO object_types (object_type) VALUES ('Role');
 INSERT INTO object_types (object_type) VALUES ('Site');
-INSERT INTO object_types (object_type) VALUES ('Node');
+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 ('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');
 
-
 -- events
 CREATE TABLE events (
        event_id serial PRIMARY KEY,  -- Event identifier
-       person_id integer REFERENCES persons NOT NULL, -- person responsible for event
+       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
@@ -607,6 +694,7 @@ events.event_type,
 events.object_type,
 events.fault_code,
 events.call,
+events.runtime,
 events.time
 From events
 LEFT JOIN event_objects USING (event_id);
@@ -658,21 +746,45 @@ node_nodenetworks.nodenetwork_ids,
 node_nodegroups.nodegroup_ids,
 node_slices.slice_ids,
 node_pcus.pcu_ids,
-node_pcus.ports
+node_pcus.ports,
+node_conf_files.conf_file_ids
 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_pcus USING (node_id)
+LEFT JOIN node_conf_files USING (node_id);
 
 CREATE VIEW view_nodegroups AS
 SELECT
 nodegroups.nodegroup_id,
 nodegroups.name,
 nodegroups.description,
-nodegroup_nodes.node_ids
+nodegroup_nodes.node_ids,
+nodegroup_conf_files.conf_file_ids
 FROM nodegroups
-LEFT JOIN nodegroup_nodes USING (nodegroup_id);
+LEFT JOIN nodegroup_nodes USING (nodegroup_id)
+LEFT JOIN nodegroup_conf_files USING (nodegroup_id);
+
+CREATE VIEW view_conf_files AS
+SELECT
+conf_files.conf_file_id,
+conf_files.enabled,
+conf_files.source,
+conf_files.dest,
+conf_files.file_permissions,
+conf_files.file_owner,
+conf_files.file_group,
+conf_files.preinstall_cmd,
+conf_files.postinstall_cmd,
+conf_files.error_cmd,
+conf_files.ignore_cmd_errors,
+conf_files.always_update,
+conf_file_nodes.node_ids,
+conf_file_nodegroups.nodegroup_ids
+FROM conf_files
+LEFT JOIN conf_file_nodes USING (conf_file_id)
+LEFT JOIN conf_file_nodegroups USING (conf_file_id);
 
 CREATE VIEW view_pcus AS
 SELECT
@@ -720,7 +832,6 @@ LEFT JOIN site_pcus USING (site_id);
 CREATE VIEW view_addresses AS
 SELECT
 addresses.address_id,
-addresses.site_id,
 addresses.line1,
 addresses.line2,
 addresses.line3,