Changes between Version 32 and Version 33 of statusdatabase

Timestamp:

Oct 16, 2014, 11:21:35 AM (10 years ago)

Author:

tec

Comment:

--

statusdatabase

@@ -20 +20 @@
 
 === SQL Explanation ===
-This explanation might not always be up-to-date but should be rather close.
+This explanation might not always be up-to-date but should be rather close. I'm only going to discuss the complex bits of SQL, the table creation stuff is not worth discussing.
 
 This needs to be run as the superuser on the database, normally `postgres`
@@ -27 +27 @@
 CREATE EXTENSION tablefunc;
 }}}
+
+OK onto the fun stuff, pivot tables. The most evil nasty piece of poo you can think to do with databases.
+{{{
+#!html
+<pre>
+CREATE OR REPLACE VIEW not_started_sensors_pivot AS
+SELECT *
+FROM crosstab('SELECT fs.flight_id, fs.priority, fs.sensor_name || ''|'' || ss.progress || ''|'' || ss.username
+FROM flight_sensors fs
+JOIN not_finished_sensors ss
+ON (fs.id = ss.flight_sensor_id)
+JOIN priorities p
+ON (fs.priority = p.priority_name)
+WHERE ss.progress IN (''not_started'', ''blocked'')
+AND fs.priority NOT IN (''not_required'', ''unfunded'')
+ORDER BY p.value DESC, ss.submit_time, fs.sensor_name ASC')
+AS ct(id int, c1 text, c2 text, c3 text, c4 text, c5 text, c6 text, c7 text, c8 text, c9 text);
+</pre>
+}}}