From 7ba8f8e7ca4b3f4ef8e02bd608c6d41cc88fdc53 Mon Sep 17 00:00:00 2001
From: Tony Mack <tmack@cs.princeton.edu>
Date: Fri, 9 Nov 2007 21:41:38 +0000
Subject: [PATCH] Support storing pcu types

---
 migrations/009-down-pcu-types.sql | 13 +++++++++
 migrations/009-up-pcu-types.sql   | 44 +++++++++++++++++++++++++++++++
 2 files changed, 57 insertions(+)
 create mode 100644 migrations/009-down-pcu-types.sql
 create mode 100644 migrations/009-up-pcu-types.sql

diff --git a/migrations/009-down-pcu-types.sql b/migrations/009-down-pcu-types.sql
new file mode 100644
index 00000000..1dbe9d2e
--- /dev/null
+++ b/migrations/009-down-pcu-types.sql
@@ -0,0 +1,13 @@
+--
+-- 009 revert
+--
+
+DROP VIEW view_pcu_types;
+
+DROP VIEW pcu_type_ports;
+
+DROP TABLE pcu_type_port;
+
+DROP TABLE pcu_types; 
+
+UPDATE plc_db_version SET subversion = 7;
diff --git a/migrations/009-up-pcu-types.sql b/migrations/009-up-pcu-types.sql
new file mode 100644
index 00000000..8baea300
--- /dev/null
+++ b/migrations/009-up-pcu-types.sql
@@ -0,0 +1,44 @@
+--
+-- Tony Mack - PlanetLab
+--
+-- migration 009
+--
+-- purpose: provide a means for storing details about pcu models
+--
+--
+
+CREATE TABLE pcu_types (
+    pcu_type_id serial PRIMARY KEY, 	
+    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 (
+    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
+) WITH OIDS;
+CREATE INDEX pcu_type_port_pcu_type_id ON pcu_type_port (pcu_type_id);
+  
+
+CREATE OR REPLACE VIEW pcu_type_ports AS
+SELECT pcu_type_id,
+array_accum(port) as ports,
+array_accum(protocol) as protocols,
+array_accum(supported) as supported
+FROM pcu_type_port
+GROUP BY pcu_type_id;
+    
+CREATE OR REPLACE VIEW view_pcu_types AS
+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
+FROM pcu_types; 
+
+UPDATE plc_db_version SET subversion = 9;
-- 
2.47.0