Changes between Version 32 and Version 33 of statusdatabase


Ignore:
Timestamp:
Oct 16, 2014 11:21:35 AM (4 years ago)
Author:
tec
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • statusdatabase

    v32 v33  
    2020
    2121=== SQL Explanation ===
    22 This explanation might not always be up-to-date but should be rather close.
     22This 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.
    2323
    2424This needs to be run as the superuser on the database, normally `postgres`
     
    2727CREATE EXTENSION tablefunc;
    2828}}}
     29
     30OK onto the fun stuff, pivot tables. The most evil nasty piece of poo you can think to do with databases.
     31{{{
     32#!html
     33<pre>
     34CREATE OR REPLACE VIEW not_started_sensors_pivot AS
     35SELECT *
     36FROM crosstab('SELECT fs.flight_id, fs.priority, fs.sensor_name || ''|'' || ss.progress || ''|'' || ss.username
     37FROM flight_sensors fs
     38JOIN not_finished_sensors ss
     39ON (fs.id = ss.flight_sensor_id)
     40JOIN priorities p
     41ON (fs.priority = p.priority_name)
     42WHERE ss.progress IN (''not_started'', ''blocked'')
     43AND fs.priority NOT IN (''not_required'', ''unfunded'')
     44ORDER BY p.value DESC, ss.submit_time, fs.sensor_name ASC')
     45AS ct(id int, c1 text, c2 text, c3 text, c4 text, c5 text, c6 text, c7 text, c8 text, c9 text);
     46</pre>
     47}}}