From c6fc1a75efd52dbe206f6415c1e691814f9e1b44 Mon Sep 17 00:00:00 2001 From: Tony Mack Date: Mon, 12 Nov 2007 22:45:01 +0000 Subject: [PATCH] -modified 'pcu_protocol_type', 'pcu_protocol_types', 'view_pcu_types' schema --- migrations/009-up-pcu-types.sql | 21 +++++++++------------ planetlab4.sql | 22 +++++++++------------- 2 files changed, 18 insertions(+), 25 deletions(-) diff --git a/migrations/009-up-pcu-types.sql b/migrations/009-up-pcu-types.sql index 8baea30..c7dd7b6 100644 --- a/migrations/009-up-pcu-types.sql +++ b/migrations/009-up-pcu-types.sql @@ -9,26 +9,25 @@ CREATE TABLE pcu_types ( pcu_type_id serial PRIMARY KEY, - model text NOT NULL , -- PCU model name + model text NOT NULL, -- PCU model name name text -- Full PCU model name ) WITH OIDS; CREATE INDEX pcu_types_model_idx ON pcu_types (model); -CREATE TABLE pcu_type_port ( +CREATE TABLE pcu_protocol_type ( + pcu_protocol_type_id serial PRIMARY KEY, pcu_type_id integer REFERENCES pcu_types NOT NULL, -- PCU type identifier port integer NOT NULL, -- PCU port protocol text NOT NULL, -- Protocol - supported boolean NOT NULL -- Does PLC support + supported boolean NOT NULL DEFAULT True -- Does PLC support ) WITH OIDS; -CREATE INDEX pcu_type_port_pcu_type_id ON pcu_type_port (pcu_type_id); +CREATE INDEX pcu_protocol_type_pcu_type_id ON pcu_protocol_type (pcu_type_id); -CREATE OR REPLACE VIEW pcu_type_ports AS +CREATE OR REPLACE VIEW pcu_protocol_types AS SELECT pcu_type_id, -array_accum(port) as ports, -array_accum(protocol) as protocols, -array_accum(supported) as supported -FROM pcu_type_port +array_accum(pcu_protocol_type_id) as pcu_protocol_type_ids +FROM pcu_protocol_type GROUP BY pcu_type_id; CREATE OR REPLACE VIEW view_pcu_types AS @@ -36,9 +35,7 @@ SELECT pcu_types.pcu_type_id, pcu_types.model, pcu_types.name, -COALESCE((SELECT ports FROM pcu_type_ports WHERE pcu_type_ports.pcu_type_id = pcu_types.pcu_type_id), '{}') AS ports, -COALESCE((SELECT protocols FROM pcu_type_ports WHERE pcu_type_ports.pcu_type_id = pcu_types.pcu_type_id), '{}') AS protocols, -COALESCE((SELECT supported FROM pcu_type_ports WHERE pcu_type_ports.pcu_type_id = pcu_types.pcu_type_id), '{}') AS supported +COALESCE((SELECT pcu_protocol_type_ids FROM pcu_protocol_types WHERE pcu_protocol_types.pcu_type_id = pcu_types.pcu_type_id), '{}') AS pcu_protocol_type_ids FROM pcu_types; UPDATE plc_db_version SET subversion = 9; diff --git a/planetlab4.sql b/planetlab4.sql index 65d0bba..8bfb42a 100644 --- a/planetlab4.sql +++ b/planetlab4.sql @@ -847,21 +847,20 @@ CREATE TABLE pcu_types ( ) WITH OIDS; CREATE INDEX pcu_types_model_idx ON pcu_types (model); -CREATE TABLE pcu_type_port ( +CREATE TABLE pcu_protocol_type ( + pcu_protocol_type_id serial PRIMARY KEY, pcu_type_id integer REFERENCES pcu_types NOT NULL, -- PCU type identifier port integer NOT NULL, -- PCU port - protocol text NOT NULL, -- Protocol - supported boolean NOT NULL -- Does PLC support + protocol text NOT NULL, -- Protocol + supported boolean NOT NULL DEFAULT True -- Does PLC support ) WITH OIDS; -CREATE INDEX pcu_type_port_pcu_type_id ON pcu_type_port (pcu_type_id); +CREATE INDEX pcu_protocol_type_pcu_type_id ON pcu_protocol_type (pcu_type_id); -CREATE OR REPLACE VIEW pcu_type_ports AS +CREATE OR REPLACE VIEW pcu_protocol_types AS SELECT pcu_type_id, -array_accum(port) as ports, -array_accum(protocol) as protocols, -array_accum(supported) as supported -FROM pcu_type_port +array_accum(pcu_protocol_type_id) as pcu_protocol_type_ids +FROM pcu_protocol_type GROUP BY pcu_type_id; -------------------------------------------------------------------------------- @@ -918,12 +917,9 @@ SELECT pcu_types.pcu_type_id, pcu_types.model, pcu_types.name, -COALESCE((SELECT ports FROM pcu_type_ports WHERE pcu_type_ports.pcu_type_id = pcu_types.pcu_type_id), '{}') AS ports, -COALESCE((SELECT protocols FROM pcu_type_ports WHERE pcu_type_ports.pcu_type_id = pcu_types.pcu_type_id), '{}') AS protocols, -COALESCE((SELECT supported FROM pcu_type_ports WHERE pcu_type_ports.pcu_type_id = pcu_types.pcu_type_id), '{}') AS supported +COALESCE((SELECT pcu_protocol_type_ids FROM pcu_protocol_types WHERE pcu_protocol_types.pcu_type_id = pcu_types.pcu_type_id), '{}') AS pcu_protocol_type_ids FROM pcu_types; - CREATE OR REPLACE VIEW view_events AS SELECT events.event_id, -- 2.43.0