Capital Trials at the Old Bailey

I've completed my bare-bones version of the web-based record search tool. All of the logic for the search - both displaying the available options and parsing out a form submission - is contained within two classes, Search and Search_Form. The classes build the search query in an organized manner based on passed parameters. This format makes the search easy to change or extend, which I can foresee happening based on the needs of the data and the researcher SD. All of the code is documented using comments and standard docblock syntax.

The database-side of things is handled with Zend_Db, the Zend Framework's RDBMS adapter. Since the search form can result in some very complex queries, Zend_Db makes the process of building the select statement relatively easy. Plus, it's a more robust database adapter in terms of security and integrity than I could build in any decent amount of time.

The search form doesn't yet have any styling and is bare, but now that the functionality is done everything else will fall into place eventually. SD is coming in for a meeting Thursday Jan 27th so I hope to hammer out some more details then.

Started the rebuild of the web-based 'query builder' search tool. Stew's SQL structure was for the most part complete, except for a missing jury_id_fk field in the trials table to point to the juries table. Other than that, the SQL structure is as Stew left it, though that may change at some point during the rebuild. For the search itself, I'm starting from scratch and building some PHP classes to handle building the complex search queries.

As my first project at HCMC, I've been tasked with finishing Stewart's procedure for converting the raw data (as supplied by SD) to well-formed XML. Stewart did an admirable job changing a tab-delimited document into an almost-finished XML file using GREP and an exhaustive series of regular expressions. Since, however, the final steps involve populating elements based on the value of other elements (checked against a list of allowable values), I've decided - after talking with Martin and Greg - to use XSLT to finish off the procedure. I don't know XSLT, but since it will be an important part of my work here for the next eight months, this is a good opportunity to learn the language.

So the first step is to take the value of crime_text, figure out to which value it corresponds in the list of allowable crimes in the proofing RNG, and then use that value to populate crime_normalized. Then, crime_group will be populated based on the value of crime_normalized. respite_text and outcome_text will be massaged in a similar way, though respite doesn't have a respite_group (only _normalized).

Spent a good chunk of time learning my way around what's been done for the Bailey project, the XSLT language, and the specific data and code needs of this project. The consensus: writing a catch-all function in the XSL stylesheet to convert the *_text values to *_normalized values will be tough, so there will likely be the odd value that will need to be edited manually.

SimonD submitted his thoughts on how to restructure presentation - trial_file is primary presentation unit with a primary trial and then 0 or more secondary trials. His proposals have a fair bit of common ground with my rethinking distinguishing the trial_file (1 or more trials on charges involving the same criminal in the same report) and the trial (1 charge). Will have to consider what changes need to be made to the data structure and the data, and how to process existing data into that new structure.
Here's the table modifications I'm going to need:
[ ] = obsolete field, remove from table
* * = new field or table, add to table
trials
trial_id
[case_id_fk]
trial_reference
[trial_notes]
jury_id_fk
[criminal_id_fk]
[recorder_report_id_fk]
judge_id_fk
*trial_file_id_fk* integer
[trial_printed_source]
[trial_other_document]
[trial_notes]

*trial_files*
*trial_file_id (integer, primary key, generated by xslt)
*case_id_fk (was in trial table)
*criminal_id_fk (was in trial table)
*recorder_report_id_fk (was in trial table)
*trial_file_printed_source (renamed, was in trial table)
*trial_file_other_document (renamed, was in trial table)
*trial_file_notes (renamed, was in trial table)

respites
*trial_file_id_fk*
[trial_id_fk]

outcomes
*trial_file_id_fk*
[trial_id_fk]

I had thought there was one trial with one or more crimes, but in fact each crime is tried separately and then the overall file covering all the trial results is treated for respite and outcome.
We're trying to represent and distinguish two things:
1) the overall legal process involving the criminal, one or more charges, respite(s), outcome(s)
2) the legal process specific to one charge
and then figure which of each of our fields of information belongs with each of those two processes.

Met with SimonD and had email exchange. As of right now, it appears that:

Here's the XML structure I have now, which isn't sophisticated enough:
<case>
<trial>
<judge>Buller-F</judge>
<jury>
<jury_type>M</jury_type>
<jury_subtype>1</jury_subtype>
</jury>
<criminal>
<surname>Hetherington</surname>
<given_names>John</given_names>
<aliases>
<alias>John Lewis Brown</alias>
</aliases>
<age></age>
<gender>Male</gender>
</criminal>
<crimes>
<crime>
<crime_text>Housebreak</crime_text>
<crime_normalized>Housebreak</crime_normalized>
<crime_group>StealInDwelling</crime_group>
</crime>
<crime>
<crime_text>Returning</crime_text>
<crime_normalized>ReturnFromTransportation</crime_normalized>
<crime_group>Miscellaneous</crime_group>
</crime>
</crimes>
<respites>
<respite>
<respite_text>To Die (RR)</respite_text>
<respite_normalized>SpecifiedPunishment</respite_normalized>
<respite_delay></respite_delay>
<respite_punishment>ToDie</respite_punishment>
</respite>
</respites>
<mercy_appeals>
</mercy_appeals>
<outcomes>
<outcome>
<outcome_text>Executed</outcome_text>
<outcome_normalized>Executed</outcome_normalized>
<outcome_group>Executed</outcome_group>
<outcome_duration>Death</outcome_duration>
<outcome_date>1791Feb23</outcome_date>
<execution_mode>SimpleHanging</execution_mode>
<outcome_location></outcome_location>
<outcome_exceptional></outcome_exceptional>
<outcome_ref>HO 13/8, pp.173-4</outcome_ref>
</outcome>
</outcomes>
<trial_ref>OBSP 1790-1, pp.9-10 (1.7), 10-12 (1.9)</trial_ref>
<trial_printed_sources></trial_printed_sources>
<trial_other_documents></trial_other_documents>
<trial_notes></trial_notes>
</trial>
</case>

Structure based on Simon's email of 100924 2:07pm
<case>
<trial_group>
<criminal>
<surname>Hetherington</surname>
<given_names>John</given_names>
<aliases>
<alias>John Lewis Brown</alias>
</aliases>
<age></age>
<gender>Male</gender>
</criminal>
<trial>
<judge>Buller-F</judge>
<jury>
<jury_type>M</jury_type>
<jury_subtype>1</jury_subtype>
</jury>
<crime>
<crime_text>Housebreak</crime_text>
<crime_normalized>Housebreak</crime_normalized>
<crime_group>StealInDwelling</crime_group>
</crime>
<mercy_appeals>
</mercy_appeals>
<!-- <trial_ref>OBSP 1790-1, pp.9-10 (1.7), 10-12 (1.9)</trial_ref> -->
<trial_ref>OBSP 1790-1, pp.9-10</trial_ref>
</trial>
<trial>
<judge>Buller-F</judge>
<jury>
<jury_type>M</jury_type>
<jury_subtype>1</jury_subtype>
</jury>
<crime>
<crime_text>Returning</crime_text>
<crime_normalized>ReturnFromTransportation</crime_normalized>
<crime_group>Miscellaneous</crime_group>
</crime>
<mercy_appeals>
</mercy_appeals>
<!-- <trial_ref>OBSP 1790-1, pp.9-10 (1.7), 10-12 (1.9)</trial_ref> -->
<trial_ref>OBSP 1790-1, pp.10-12</trial_ref>
</trial>
<respites>
<respite>
<respite_text>To Die (RR)</respite_text>
<respite_normalized>SpecifiedPunishment</respite_normalized>
<respite_delay></respite_delay>
<respite_punishment>ToDie</respite_punishment>
</respite>
</respites>
<outcomes>
<outcome>
<outcome_text>Executed</outcome_text>
<outcome_normalized>Executed</outcome_normalized>
<outcome_group>Executed</outcome_group>
<outcome_duration>Death</outcome_duration>
<outcome_date>1791Feb23</outcome_date>
<execution_mode>SimpleHanging</execution_mode>
<outcome_location></outcome_location>
<outcome_exceptional></outcome_exceptional>
<outcome_ref>HO 13/8, pp.173-4</outcome_ref>
</outcome>
</outcomes>
<trial_group_printed_sources></trial_printed_sources>
<trial_group_other_documents></trial_other_documents>
<trial_group_notes></trial_notes>
</trial_group>
</case>

Here's the table modifications I'm going to need:
[ ] = obsolete field, remove from table
* * = new field or table, add to table
trials
trial_id
[case_id_fk]
trial_reference
[trial_notes]
jury_id_fk
[criminal_id_fk]
[recorder_report_id_fk]
judge_id_fk
*trial_file_id_fk* integer

*trial_files*
*trial_file_id (integer, primary key, generated by xslt)
*case_id_fk (was in trial table)
*criminal_id_fk (was in trial table)
*recorder_report_id_fk (was in trial table)
*trial_file_printed_source (renamed, was in trial table)
*trial_file_other_document (renamed, was in trial table)
*trial_file_notes (renamed, was in trial table)

respites
*trial_file_id_fk*
[trial_id_fk]

outcomes
*trial_file_id_fk*
[trial_id_fk]

Trial-oriented presentation:
- each trial appears as a record in the display with fields
criminal_name |
crime_text |
judge_name |
jury_code |
>1 trials in file |
>0 mercy appeals |
respiteFINAL_text |
outcomeFINAL_text
- details shows
criminal_name | aliases | criminal_gender | criminal_age
[1...n trials in trial_file]
crime_text | crime_normalized
[0...n] mercy_appeal_who | mercy_appeal_why
trial_reference
[1...n] respite_text | respite_normalized
[1...n] outome_normalized | outcome_date | outcome_reference, outcome_location | outcome_exceptional | execution_mode
trial_file_printed_sources | trial_file_other_documents
trial_file_notes
- alternate row colours based on trial_file value (i.e. all trials in a file share same bg colour)

Trial_file-oriented presentation
- each trial_file appears as record in display with fields
criminal_name |
[1...n] crime_normalized for each trial in this trial_file |
[1...n] respite_text |
[1...n] outcome_text
- details shows
criminal_name | aliases | criminal_gender | criminal_age
[1...n trials in this trial_file]
crime_text | crime_normalized | judge_name | jury_code
[0...n] mercy_appeal_who | mercy_appeal_why
trial_reference
[1...n] respite_text | respite_normalized
[1...n] outcome_normalized, outcome_date, outcome_reference, outcome_location, outcome_exceptional, execution_mode
trial_file_printed_sources
trial_file_other_documents
trial_file_notes

In writing the sql code to generate reports, I'm getting confused on relationship between trials, crimes, respites and outcomes, wrote SimonD for clarification, and to set up meeting.

There is a case containing one trial, in which John Hetherington is charged with 2 crimes. Am I right to present that as one trial, indicate that there is more than one crime in the summary line, and display all the crimes in the details? If not, then what do you suggest? For the purposes of the visualization, do we treat this as one trial categorized by the first crime?

There are about a dozen trials in which 2 crimes are being tried. (i.e. the crimes table has about 12 more records than then trials table). I've eventually got to figure out how to return results which include more than one crime where appropriate.

This simple snippet returns a row for each record in the crimes table which has the same trial_id_fk as at least one other record in the crimes table. A more sophisticated version of this kind of will have to find its way as a subquery into the real query. Similarly for any other data with a similar relationship to the trial table.

SELECT crime_text,trial_id_fk FROM crimes AS temp
WHERE 1 < (SELECT COUNT(*) FROM crimes WHERE crimes.trial_id_fk = temp.trial_id_fk);

Holly's query was not giving me the results I expected.
Her code:
SELECT trials.trial_id FROM trials
JOIN crimes ON (crimes.trial_id_fk = trials.trial_id)
JOIN crime_normalizeds ON (crimes.crime_normalized_id_fk = crime_normalizeds.crime_normalized_id)
JOIN criminals ON (criminals.criminal_id = trials.criminal_id_fk)
JOIN aliases ON (aliases.criminal_id_fk = criminals.criminal_id)
JOIN judges ON (judges.judge_id = trials.judge_id_fk)
JOIN juries ON (juries.jury_id = trials.jury_id_fk)
JOIN mercy_appeals ON (mercy_appeals.trial_id_fk = trials.trial_id)
JOIN mercy_appeal_whos ON (mercy_appeals.mercy_appeal_who_id_fk = mercy_appeal_whos.mercy_appeal_who_id)
JOIN respites ON (respites.trial_id_fk = trials.trial_id)
JOIN respite_normalizeds ON (respite_normalizeds. respite_normalized_id = respites.respite_normalized_id_fk)
JOIN outcomes ON (outcomes.trial_id_fk = trials.trial_id)
JOIN outcome_normalizeds ON (outcome_normalizeds. outcome_normalized_id = outcomes. outcome_normalized_id_fk)

The problem is that using "JOIN" means that if there is no value in the e.g. aliases table for a criminal, then no row is produced. This code solved that problem:

SELECT trials.trial_id FROM trials
LEFT JOIN crimes ON (crimes.trial_id_fk = trials.trial_id)
LEFT JOIN crime_normalizeds ON (crimes.crime_normalized_id_fk = crime_normalizeds.crime_normalized_id)
LEFT JOIN criminals ON (criminals.criminal_id = trials.criminal_id_fk)
LEFT JOIN aliases ON (aliases.criminal_id_fk = criminals.criminal_id)
LEFT JOIN judges ON (judges.judge_id = trials.judge_id_fk)
LEFT JOIN juries ON (juries.jury_id = trials.jury_id_fk)
LEFT JOIN mercy_appeals ON (mercy_appeals.trial_id_fk = trials.trial_id)
LEFT JOIN mercy_appeal_whos ON (mercy_appeals.mercy_appeal_who_id_fk = mercy_appeal_whos.mercy_appeal_who_id)
LEFT JOIN respites ON (respites.trial_id_fk = trials.trial_id)
LEFT JOIN respite_normalizeds ON (respite_normalizeds. respite_normalized_id = respites.respite_normalized_id_fk)
LEFT JOIN outcomes ON (outcomes.trial_id_fk = trials.trial_id)
LEFT JOIN outcome_normalizeds ON (outcome_normalizeds. outcome_normalized_id = outcomes. outcome_normalized_id_fk)

HR having problems establishing an maintaining a connection to the DB. It appears to be a problem with the ordering of code that initializes variables and includes of files that assign values to those variables. Also, she requested a listing of all the tables and field names, so I generated an SQL structure-only file and sent that to her.

Started work on revising the bailey_add_id_xsl file. It's now processing all but the aliases and mercy_appeals. The date conversion function is not working Did extensive changes to the database. Added necessary lookup tables, fields to existing lookup tables and values to all lookup tables. Cleaned out all records in the crimes, trials etc. tables (i.e. those populated from the data files) and then changed the fieldtype on their ID fields from varchar to int. Changed the table type on all tables to innoDB. Have not yet formally created the foreign-key connections.