Capital Trials at the Old Bailey

Doing a bunch more tests with the chart vs table queries. The only difference between the two seems to be the value for GROUP BY clause.
In the chart output it was GROUP BY cg.crime_group_text, YEAR(tf.trial_file_start_date)
In the table output is was GROUP BY `c`.`criminal_id`
So, in the chart output if two records had the same crime_group_text and YEAR, then they'd return only 1 result, whereas there is no chance of a criminal_id being duplicated. For example search for 8 to 14 year olds from 1730 to 1759 returns 14 hits on the chart output and 16 hits on the table output.

I modified includes/classes/Search.php.387 so that both queries use the same GROUP BY value (and ORDER BY as well just if user goes to chart and then to table they get results in same order as if they go straight to table).

At the moment the graphing is failing on the chart, but that's a separate problem I'll have to sort out. It looks like I've got a tentative solution to the problem of inconsistent numbers of results returned by the two queries.

Completed a couple more elements in the search form, including Post-Execution Disposition. Discovered in that one that the values are text strings in a field in the outcomes table, rather than integer value lookups to a "corpse-treatment" table. The _orWhereClause generator assumes the latter, so couldn't be used to generate the where clause for this search form element. I essentially duplicated the code from the function, but added a case statement to transform the integer values to the appropriate string values.

I was unable to figure out how a pardon is represented in the database, so couldn't begin to figure out how to count them, which I need to do for the final search form element (Min Pardons). I emailed SD to ask. Meanwhile, I'll return to the more complex problem of rejigging the chart search query so that it returns results consistent with the table search query.

After much wrestling, I figured out how to get the value(s) for the respite duration field in the search form to be correctly incorporated into the big mysql query (at line 945 of classes/Search.php)

if ($this->inData('respite-delay')) {
$respiteDelay = $this->_orWhereClause('respite-delay', 'rd.respite_delay_id');
$this->_select->where($respiteDelay);
}

In working on this, I discovered an oddity in the data. For example, a trial for Robert Abel includes a respite delay of one week. If I search just on his firstname and surname, I get one hit and the correct respite_rr_date. If I search for his firstname, lastname and respite_delay of one week, I get the one hit, but this time with a respite_rr_date of 0000-00-00.
His trial_file_id is 3297. In the respites table, three records with respite id 3405, 3406, 3407 all point to trial_file_id_fk 3297, but only the first record in the respites table has the correct value for the respite_rr_date field (1784-11-12), the other two have the value 0000-00-00. It looks like all respites after the first for any given trial_file have a respite_rr_date of 0000-00-00.

I'm not sure why that is or what the implications would be if I changed the approximately 422 instances to have proper values. It looks like the 0000-00-00 value is used in Search.php to distinguish respite-at-report from respite-post-report.

The form field for the recorder's report date did not remember the selected values when the user returned from the search results. Turns out the problems is a combination of
1) that field was populated by a fetchCol (rather then fetchPairs) invocation, because I needed only the one value and not the id and string
2) the array returned within recordersReportData invokes the select function, and that function requires array items to consist of two elements, and not one

So, as a workaround, in recordersReportDate() I now invoke fetchPairs and have it return the respite_rr_date field twice. Seems to work.

When I get a chance I'll see if there's some way to modify the select function so that it will work properly with an array of single-element items.

repopulated the dev instance of the Bailey db with data provided by Simon in August. Imported all data files so I could find the various anomalous records and ensure that they had been corrected.
Also flagged a number of crime_normalizeds and outcome_normalizeds as DEPRECATED - still have to do the same for the Production instance.

If I do the query for 12-14 years olds in 1729 to 1759
with no grouping I get 18 hits (1 trial with 2 charges, 1 trial with 3)
with criminal_id grouping (i.e. table query) I get 15 hits (correct result, counting trials rather than charges)
with crime_group and year grouping (i.e. chart query) I get 13 hits. Two of the trials found by the table query are not found by this query.
I haven't been able to figure out why not. Will have to look in more detail at the GROUP BY directive and whether the "missing" records have gappy data causing the GROUP BY directive to skip them.

Query 1 to 14 year olds, 1730 to 1759
Manual query on database returns 15 results
Table query returns 15 results (see below)
Chart query returns 18 results (see below)

Travis had 2 simultaneous trials for Burglary. The table query counts each of those as 1 hit (likely due to GROUP BY = criminal_id); the graph query counts each of those as 2 hits (likely due to GROUP BY = crime_group_text, Year). Similarly Gadd, who has 3 simultaneous trials for Robbery.

That's why the counts are what they are, and inconsistent between table searches and chart searches.

Table results:
Name Age G Crime Trial Date Judge Jury RR Date Mercy? Respite Outcome
*John Peaverly 14 M Stealing in a Dwelling 1731-05-03 M 1731-05-11 No T (RR) Transport
*John Travis 14 M Burglary 1734-01-18 M 1734-02-07 No T14 (RR) Transport
*Thomas Stafford 14 M Housebreaking 1736-12-13 1737-02-28 No To Die (RR Executed(Hanged)
*Charlotte Gregg 14 F Stealing in a Dwelling 1737-10-15 1738-01-12 No Free (RR) Free
*Jarvis Hare 14 M Horse (1) 1739-06-09 L 1739-06-28 No T (RR) Transport
*John Hetherington 14 M Stealing in a Dwelling 1740-04-19 1740-04-30 No T (RR) Transport
*Charles Shooter 12 M Stealing in a Dwelling 1741-01-20 1741-03-11 Yes T (RR) Transport
*Jane Wood 14 F Stealing in a Dwelling 1742-09-13 1742-11-11 Yes T14 (RR) Transport
*Joseph Loach 14 M Robbery 1743-09-12 1743-10-13 No T14 (RR) Transport
*John Bunn 13 M Robbery 1743-09-12 1743-10-13 No T14 (RR) Transport
Joseph Fitzwater 13 M Robbery 1744-09-15 1744-09-27 Yes TL (RR) Transport
Henry Gadd 14 M Robbery 1744-12-10 L 1744-12-19 No To Die (RR) Executed(Hanged)
*Anthony Dunn 14 M Robbery 1749-07-10 No Died
*Mary Rimer 13 F Pickpocket 1753-09-10 Willes-J L 1753-09-26 Yes T7 (RR) Transport
*Stephen Barnes 13 M Housebreaking 1753-12-10 Moreton-W M 1754-01-29 Yes TL (RR) Transport

Description of Chart results:

1731 StealInDwelling 1 [Peaverly]
1734 Burglary 2 [Travis] []
1736 Burglary 1 [Housebreaking]
1737 StealInDwelling 1 [Gregg]
1739 AnimalTheft 1 [Hare]
1740 StealInDwelling 1 [Hetherington]
1741 StealInDwelling 1 [Shooter]
1742 StealInDwelling 1 [Wood]
1743 Robbery 2 [Loach] [Bunn]
1744 Robbery 4 [Fitzwater] [Gadd] [] []
1749 Robbery 1 [Dunn]
1753 Burglary 1 [Rimer]
1753 Miscellaneous 1 [Barnes]

1) Clear data out of database
these tables first to avoid foreign-key violation errors:
TRUNCATE crimes
TRUNCATE trial_files
TRUNCATE criminals
[or
TRUNCATE crimes; TRUNCATE trial_files; TRUNCATE criminals;
]

These tables are virtually always modified when you add a new record(s), so do them next:
TRUNCATE outcomes
TRUNCATE respites
TRUNCATE trials
[or
TRUNCATE outcomes; TRUNCATE respites; TRUNCATE trials;
]

These tables are occasionally modified:
TRUNCATE aliases
TRUNCATE judge_respites
TRUNCATE outcome_durations
TRUNCATE execution_specials
TRUNCATE mercy_appeals
[or
TRUNCATE aliases; TRUNCATE judge_respites; TRUNCATE outcome_durations; TRUNCATE execution_specials; TRUNCATE mercy_appeals;
]

2) Run the Importer.php file on each data file to upload it into db. Can take up to a minute. Dev and production instances both working.