From caa387a297086afd613b5bf1983d82b5fa511697 Mon Sep 17 00:00:00 2001 From: Mark Huang Date: Wed, 11 Oct 2006 20:49:39 +0000 Subject: [PATCH] - use _idx as the suffix on all custom indices to avoid conflicts with automatic indices - add node_pcus view --- planetlab4.sql | 65 +++++++++++++++++++++++++++++--------------------- 1 file changed, 38 insertions(+), 27 deletions(-) diff --git a/planetlab4.sql b/planetlab4.sql index dcbc2f1..1eda80d 100644 --- a/planetlab4.sql +++ b/planetlab4.sql @@ -9,7 +9,7 @@ -- -- Copyright (C) 2006 The Trustees of Princeton University -- --- $Id: planetlab4.sql,v 1.8 2006/10/11 15:37:12 mlhuang Exp $ +-- $Id: planetlab4.sql,v 1.9 2006/10/11 19:53:42 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 @@ -184,8 +184,8 @@ CREATE TABLE person_key ( key_id integer REFERENCES keys NOT NULL, -- Key identifier 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, @@ -214,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 @@ -262,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 @@ -289,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 @@ -349,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 @@ -382,7 +382,7 @@ CREATE TABLE pcus ( model text, -- Model, e.g. BayTech or iPal notes text -- Random notes ) WITH OIDS; -CREATE INDEX pcus_site_id_key ON pcus (site_id); +CREATE INDEX pcus_site_id_idx ON pcus (site_id); CREATE VIEW site_pcus AS SELECT site_id, @@ -394,10 +394,18 @@ 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, @@ -434,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 @@ -476,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 @@ -513,8 +521,8 @@ CREATE TABLE slice_attribute ( attribute_id integer REFERENCES attributes 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, @@ -570,11 +578,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); +LEFT JOIN node_slices USING (node_id) +LEFT JOIN node_pcus USING (node_id); CREATE VIEW view_nodegroups AS SELECT -- 2.43.0