2 -- Tony Mack - PlanetLab
6 -- purpose: provide a means for storing details about pcu models
10 CREATE TABLE pcu_types (
11 pcu_type_id serial PRIMARY KEY,
12 model text NOT NULL, -- PCU model name
13 name text -- Full PCU model name
15 CREATE INDEX pcu_types_model_idx ON pcu_types (model);
17 CREATE TABLE pcu_protocol_type (
18 pcu_protocol_type_id serial PRIMARY KEY,
19 pcu_type_id integer REFERENCES pcu_types NOT NULL, -- PCU type identifier
20 port integer NOT NULL, -- PCU port
21 protocol text NOT NULL, -- Protocol
22 supported boolean NOT NULL DEFAULT True -- Does PLC support
24 CREATE INDEX pcu_protocol_type_pcu_type_id ON pcu_protocol_type (pcu_type_id);
27 CREATE OR REPLACE VIEW pcu_protocol_types AS
29 array_accum(pcu_protocol_type_id) as pcu_protocol_type_ids
30 FROM pcu_protocol_type
33 CREATE OR REPLACE VIEW view_pcu_types AS
35 pcu_types.pcu_type_id,
38 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
41 UPDATE plc_db_version SET subversion = 9;