Capital Trials at the Old Bailey

Since there have been so many structural changes and changes to allowed values recently, I've sent the 1790s and 1800s data to SD for what will likely be the final proofing before the data gets imported to the website.

SD and I met to discuss two issues: some data oddities that I wasn't sure how to handle, and the re-shuffling of the recorder report dates.

#1: Data Oddities

We've made the following changes to the value lists (respites, crimes, outcomes):

• Added "Stealing" as a crime in the "Miscellaneous" group
• Removed "PledBelly" from the list of allowable respites (it's actually a judge's respite)
• Decided that the list of judge's respites (the judge_respite family of elements) would be "Respited-Judge", "Belly-Q", and "Belly-NQ"
• Added "NoRR" as a respite_normalized
• Added "SelfTL" and "SelfTR" as outcomes in the "Transport" group
• Added "PrisonRemission" and "Whipped" as outcomes in the "EarlyRelease" group

#2: Recorder's Report Dates

We also discussed a better way to handle rec_rep_start_date, rec_rep_end_date, and rec_rep_pub_date. These currently cover multiple trial_files within the same case, but, given that the dates may change for trials within one case, we've decided to move the dates to within the trial_file element. This will result in more redundant data but will also give SD the flexibility to enter date oddities. Specifically, these changes will be made:

• Removing the rec_rep_head element
• Renaming rec_rep_start_date and rec_rep_end_date to trial_file_start_date and trial_file_end_date, and moving them to within trial_file
• Renaming rec_rep_pub_date to respite_rr_date and moving it to within the first respite element. This is being done because, as SD explained, sometimes a respite resulting from a trial may not be published in the 'original' recorder's report, but be delayed and be published in a later report, even though the trial dates may match those of the original recorder's report. So, blanketing each trial_file within the recorder's report with the same publication date doesn't allow for that flexibility.
• Removal of rec_rep_notes, which, according to SD, is no longer needed.

After creating an outcome_executions DB table to link outcomes with multiple execution modes, I discovered that SA had already set up a slyly-named table called "execution_specials" which accomplishes the same task and already has 1790s data. Oops.

SD wants to double check the 1790s XML data. So, I made a new version of the 1790s XML file, 1790-1799_new_structure.xml, to take into account the changes SD and I made to the XML structure. I used the 8_to_9_SD_changes.xsl stylesheet for the transformation (the same one I used for the 1800s data). The data may have to be re-imported into the MySQL database; not too sure yet.

SD just sent me his proofed version of the 1800s data XML file. We're making good progress.

SD's latest request for the search interface is that, when a trial file has more than one associated trial, respite, and/or outcome, only the first record in each of those subtables should be searchable. For example, if a trial file has two outcomes - 'Transported' and 'Military Service' - only the 'Transported' outcome should be searchable. I had planned to accomplish this in the search query by imposing a LIMIT, for example:

SELECT .... FROM trial_files WHERE outcomes.outcome_id IN (SELECT outcome_id FROM outcomes WHERE outcomes.trial_file_id_fk = trial_files.trial_file_id LIMIT 1)

However, it turns out that, as of version 5.5, MySQL doesn't yet support LIMIT in subqueries:

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

So that approach won't work. It's for the best, though, since that relies on the order of the records in the database. What I will do now is add a "rank" column to the respites, outcomes, and trials tables. Only records with a rank of 1 will be searchable. In the detail view for a record, respites, outcomes and crimes will be displayed in order of rank. The SQL:

ALTER TABLE  `trials` ADD  `rank` INT NULL DEFAULT NULL;
ALTER TABLE  `outcomes` ADD  `rank` INT NULL DEFAULT NULL;
ALTER TABLE  `respites` ADD  `rank` INT NULL DEFAULT NULL;

I've manually added ranks to the 1790s data in the database; future rank will be taken from the order of the elements in the XML files.

Wrote a simple function to save the current search parameters so that the same results can be seen at a later date. This is the flow:

1. User clicks on "Save Search" link at bottom of results
2. Javascript function intercepts the link and sends an AJAX request to includes/helpers/save_search.php
3. The save_search script loads the Search class
4. The Search class in turn grabs the search params from the session, and saves a new record to the saved_searches table, and then returns the ID of the saved search record
5. The save_search script returns a URL that can be used to access the saved search, which is just the search script (search.php) with a 'saved_search' GET parameter equal to the ID of the newly saved search
6. The Javascript function informs the user of the successful save and provides them with the URL
7. Success!

I've linked up the search form with the chart plotting tool, so now it's possible to:

• Execute a search and immediately see the results in either a paginated list or the Flot-based chart tool
• Pass the search results between both views (paginated list/chart)
• Refine the results within either view

Contrary to my last blog post, the search data is not passed between the views via AJAX. Although that would be 'sexier', it doesn't lend itself well to storing results for the future. Speaking of which, my next task is to devise some sort of clever method for bookmarking search results, which'll be tricky since search data is stored in the session after the initial search (to avoid POST problems). I think some sort of DB-stored session ID may be the answer.

Note that development thus far has been done on my local machine but will be pushed to the Bailey website soon.

After having good success experimenting with Flot, an open Javascript chart-plotting script based on JQuery, I've decided that it's officially "the one" for the Bailey project. The Flot homepage is here: http://code.google.com/p/flot/

I chose Flot for three main reasons over Simile's Timeplot and Google's Visualization API:

• It does what this project needs more or less out of the box
• JSON data formatting is a snap
• It's by far the easiest to use of the three tools, especially for anyone with JQuery experience (but that's by no means necessary)

I've already been working to integrate Flot for the past week or so, and am progressing at a good rate. I've written the bare-bones search form and the bare-bones visualization page; the next step is to allow the results for one to be exported to the other. I don't foresee any problems with the integration and am looking forward to seeing the tools working together soon. I'd like to design a single page that incorporates both views (search form + visualization) and uses AJAX to pass the data around, perhaps using tabs to separate the two. That way I can avoid having to constantly POST data.

I've sent the 1800s data XML file to SD for proofing again, after writing an XSLT stylesheet to incorporate the structural changes that came out of our last meeting on Feb. 17/11 (documented in an earlier blog post).