migrations/009-up-pcu-types.sql

   1 --
   2 -- Tony Mack - PlanetLab
   3 --
   4 -- migration 009
   5 --
   6 -- purpose: provide a means for storing details about pcu models
   7 --
   8 --
   9
  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
  14 ) WITH OIDS;
  15 CREATE INDEX pcu_types_model_idx ON pcu_types (model);
  16
  17 CREATE TABLE pcu_type_port (
  18     pcu_type_id integer REFERENCES pcu_types NOT NULL,  -- PCU type identifier
  19     port integer NOT NULL,                              -- PCU port
  20     protocol text NOT NULL,                             -- Protocol
  21     supported boolean NOT NULL                          -- Does PLC support
  22 ) WITH OIDS;
  23 CREATE INDEX pcu_type_port_pcu_type_id ON pcu_type_port (pcu_type_id);
  24
  25
  26 CREATE OR REPLACE VIEW pcu_type_ports AS
  27 SELECT pcu_type_id,
  28 array_accum(port) as ports,
  29 array_accum(protocol) as protocols,
  30 array_accum(supported) as supported
  31 FROM pcu_type_port
  32 GROUP BY pcu_type_id;
  33
  34 CREATE OR REPLACE VIEW view_pcu_types AS
  35 SELECT
  36 pcu_types.pcu_type_id,
  37 pcu_types.model,
  38 pcu_types.name,
  39 COALESCE((SELECT ports FROM pcu_type_ports WHERE pcu_type_ports.pcu_type_id = pcu_types.pcu_type_id), '{}') AS ports,
  40 COALESCE((SELECT protocols FROM pcu_type_ports WHERE pcu_type_ports.pcu_type_id = pcu_types.pcu_type_id), '{}') AS protocols,
  41 COALESCE((SELECT supported FROM pcu_type_ports WHERE pcu_type_ports.pcu_type_id = pcu_types.pcu_type_id), '{}') AS supported
  42 FROM pcu_types;
  43
  44 UPDATE plc_db_version SET subversion = 9;