Display SAT personal timetables on AT foyer display screens

Aims

We are obliged to present an individual timetable for students from 2013/2014 onwards. Our plan is to present info from our RBS onto displays the foyers in AT. This project was intended to provide this information

Work carried out

Update RBS

The RBS schema was updated adding a table to store incoming data from (mainly course data) and a table used to provide a source for the data feed to theon. The schema was modified to add a unique identifier to recurring bookings through a trigger function to act as a reference in theon.

The php web interface was patched to read the course entry data from this table, previously this had been staticaly defined in the php code. The interface was also modified to make the unique identifier field read only.

The updates were deployed and a one off query was used to generate booking ref entried for all pre-exisiting repeated bookings.

After the updates were deployed and whilst testing the theon feed it was noticed that the ito rbs was generating non unique booking references so the trigger function generating the bookingref was altered to check that the reference was unique.

RBS to theon feed

A theon sync feed was developed to take booking information about repeated bookings from rbs and sync it with theon via the generic sunc framework detailed in https://svn.theon.inf.ed.ac.uk/trac/wiki/TheonCoupler.

Theon to RBS feed

A python script was developed to pull course information from the ITO "sorted list" report and inject it into rbs.

Theon report to generate Calendars

The final part of the project was to develop a number of theon reports to generate timetables for each course and also for each year in whatever formats were requried, initially html and some format suitable for the displays in the applton tower. Following further consultation with the ITO this requirement was reduced to html.

The project was canceled with with the theon sync feed and the final reports unfinished.

Problems/issues

The only real issues with the RBS system part was the generation of non-unique unique IDs. The docs for the UUID_SHORT function state:

 The value of UUID_SHORT() is guaranteed to be unique if the following
conditions hold:

    The server_id of the current host is unique among your set of master and
slave servers

    server_id is between 0 and 255

    You do not set back your system time for your server between mysqld
restarts

    You do not invoke UUID_SHORT() on average more than 16 million times per
second between mysqld restarts 

Given this the uids generated ought to have been unique, also given that the uids are time based we would have expected that duplicate bookings should have been created around the same time, this seemed not to be the case. After some investigation it was never really clear whether the duplicate booking references were caused by UUID_SHORT generating duplicate UUIDs or from the php code splitting bookings and not generating a new bookingref. We initially though of using the full UUID() function (generating a 128 bit id) but it was thoughtbetter to check for dupes in the trigger function and log details of any dupes found.

There was a significant learning curve involved with the theon development part of the project. The sync framework in particular could benefit from development of debugging tools and better logging. The report generating part was OK although throughout the project I had to maintain two development environments as some environmental variables seemed to clash. Despite significant investigation from gdutton and timc much of the development environment problems were never really explained.

There was also an SQL (re)learing overhead, iainr's residual SQL race memory was sufficient for the RBS work and the sync feed but was struggling severely with generating a weekly timetable from the various tables. Despite hoggin TIm for an entire morning we never really got a correctly formatted weekly timetable generated.

Time spent

Total time spent was 8 weeks FTE with probably about 2 days consultancy from Tim and significantly more from Graham. This was significanlty more than the initial estimation but a large part of the excess (say 3 weeks) should be put down to a new user getting up to speed with the theon sync framework. Several days should also be attributed to relearing SQL.

-- IainRae - 27 Jan 2014