Progress Update

Views

Yesterday a gave a short presentation on the progress of this project. The slides for this presentation are available for OpenOffice as OPD or as PDF.
The presentation included a summary of progress on the gadget outlined in the previous blog post. Using the bullet points from the previous post, the current to-do list now looks something like this (points in italics are new ideas):

• GridLoad style stacked charts.


• A "League Table" (totaled over the time period).


• Pie charts (of the "League Table").


• Filters and/or sub filters (just successful jobs, just jobs by one VO, just jobs for this CE etc).


• A tabbed interface.


• Variable X-axis time-step granularity.


• Variable Y-axis parameter (jobs submitted, jobs started, jobs finished etc).


• Data export option.


• Automate back end data-source (currently using a small sample data set).


• Regular Expression based filtering (Full regexp support added after the presentation.)


• Make the interface more intuitive, including tool-tips etc.


• Optimise database queries.


• Make the interface more friendly.


• Move to a more dynamic chart legend style.


• Ensure w3 standards compliance/cross-browser compatibility & testing.


• Possible inclusion of more "real time" data.

Today I was perticularly pleased to fix a small bug that had been a persistent problem for quite a while. This problem related to an SQL database query containing both a GROUP BY and a WHERE clause. Specifically I was grouping by a DATE(columnid) and the produced result contained a COUNT of the number of jobs submitted per day WHERE the jobs had to meet a set of criteria. However the result set was far from optimal, specifically the WHERE clause caused the days for which there were no jobs meeting the criteria to be omitted from the results. Whilst this is perfectly reasonable, I required those days to be shown (but with a COUNT of '0' next to them). My initial thought was to JOIN (right outer) this results set onto a list of all possible days, unfortunately this had no effect as the WHERE clause still prevented COUNT '0' rows from being show (in reality these rows had a 'NULL' COUNT instead of a '0' COUNT, which would have displayed). After much Google-ing, I eventually found the solution was rather trivial although not at all documented and very non-intuitive. I simply had to switch the WHERE for an AND (normally AND is used to chain WHERE clauses and as such can never normally appear without a preceding WHERE). A particularly unusual solution.

Original:
SELECT DATE(jobsubmitedtimestamp) AS date, COUNT(jobs) FROM maintable WHERE ui REGEXP 'ch' AND vo='alice' GROUP BY date;

Joined, but still not right:
SELECT days.day, COUNT(jobs) FROM maintable RIGHT JOIN days ON maintable.date=days.day WHERE ui REGEXP 'ch' AND vo='alice' GROUP BY days.day;

Solution:
SELECT days.day, COUNT(jobs) FROM maintable RIGHT JOIN days ON maintable.date=days.day AND ui REGEXP 'ch' AND vo='alice' GROUP BY days.day;

(The above are simplified versions of the my queries.)