ARSF Documentation

ARSF Status Page Documentation

Documentation for web files can be generated using PHPDoc and JSDoc as they contain comments which can be converted into HTML documentation.

The gist of the status web stuff is that the edit/{index,order}.php and progress/index.php files send AJAX queries to the API I created and that returns data back to the page. The API essentially consists of a regular expression which matches the URL that the AJAX calls, and then passes that to a class which returns the data back to the browser. Look at the code, its relatively simple. There is a database class which does all the heavy lifting, basically it just runs queries on tables and views. Even then the actual database does the majority of work, due to my awesome, yet evil-looking SQL code. PHPDoc command to generate documentation. JSDoc command is similar, there are a few ways to get JSDoc so I wont document the command here.

phpdoc run --title "ARSF Status Documentation" --visibility "public,protected,private" --sourcecode -p  -t OUTPUT_DIRECTORY -d WEBFILES_DIRECTORY

ARSF Status Database

Ok I will have hopefully attached an up-to-date entity relationship diagram to show the layout of the database, don't believe it wholeheartedly, check the actual database (pgsql.npm.ac.uk AKA hodgson.npm.ac.uk) for actual column names etc...

The majority of the database is self explanatory. PIs are associated with projects, a flight can be related to many projects (as defined in project_flights). A flight can have flight_sensors (basically what sensor was used on that flight) and each flight sensor can have many sensor_statuses (this keeps track of the progress, I like to think of it as an audit trail, keeps track of what everyone has done). That is basically it. Now do not touch my database unless you are sure you know what you are doing'''

The following info is only useful to those already fluent in SQL

SQL Explanation

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

CREATE EXTENSION tablefunc;

OK onto the fun stuff, pivot tables. The most evil nasty piece of poo you can think to do with databases.

This is the view (not_started_sensors_pivot) that provides the main content for the not_started_sensors view All it does is finds all the sensors that have not been started or are blocked, it then uses the "crosstab" function to essentially group things by flight_id and priority. Google crosstab, its hard to explain and it only gets worse. The cross tab takes all the row with the same flight line and puts them onto the same row. The quotes inside of the crosstab have been escaped, to escape a ' you write ''.

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);

The update_view function runs whenever the sensor table is updated. This is the nasty hack, basically crosstab cannot create pivot tables with a dynamic number of columns, this function gets around that. It builds up an sql statement that creates a view of a crosstab, but at the end where you define crosstab columns it runs a query on the sensor table and adds the sensors as the columns then runs the query. Quotes in this are escaped 2 times so ' = ''''