Permalink 11:59:18 am, by sarneil, 148 words, 112 views   English (CA)
Categories: Activity Log; Mins. worked: 360

search form all done but minimum pardons

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.


Permalink 03:53:22 pm, by sarneil, 235 words, 149 views   English (CA)
Categories: Activity Log; Mins. worked: 240

got respite_delay query working, discovered oddity in respite_rr_date values for multiple respites for same trial_file

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');

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.

Permalink 09:59:55 am, by sarneil, 128 words, 106 views   English (CA)
Categories: Activity Log; Mins. worked: 180

recorder's report search now working

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.


Permalink 04:06:23 pm, by sarneil, 57 words, 136 views   English (CA)
Categories: Activity Log; Mins. worked: 90

repopulate DEV instance

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.

Permalink 04:04:13 pm, by sarneil, 35 words, 126 views   English (CA)
Categories: Activity Log; Mins. worked: 90

reconcile dev and prod instances

In preparation for making changes based on suggestions provided by SD in the summer, I ensured that the dev instance was identical to the prod instance and that both are backed up to my mac.


Permalink 04:02:57 pm, by sarneil, 105 words, 254 views   English (CA)
Categories: Activity Log; Mins. worked: 120

more on inconsistent query results

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.


Permalink 03:46:23 pm, by sarneil, 337 words, 331 views   English (CA)
Categories: Activity Log; Mins. worked: 180

inconsistent results for chart query vs table query

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]

Permalink 12:36:48 pm, by sarneil, 118 words, 270 views   English (CA)
Categories: Activity Log; Mins. worked: 60

Repopulate Bailey DB

1) Clear data out of database
these tables first to avoid foreign-key violation errors:
TRUNCATE trial_files
TRUNCATE criminals
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 outcomes; TRUNCATE respites; TRUNCATE trials;

These tables are occasionally modified:
TRUNCATE aliases
TRUNCATE judge_respites
TRUNCATE outcome_durations
TRUNCATE execution_specials
TRUNCATE mercy_appeals
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.


Permalink 11:21:56 am, by sarneil, 83 words, 357 views   English (CA)
Categories: Activity Log; Mins. worked: 30

oddity in stacking : fill colours transparent

set query to lastname Smith, year range 1786 to 1789. Look at plot. For 1786 there should be a yellow box of height 1 and an orange box of height 1. The orange box is 2 high and the yellow box is superimposed, but the fill on both is (partially) transparent, so the yellow box appears as an intermediary colour between yellow and orange. Same problem with red and purple in 1788. I've tried fiddling with various settings in jquery.flot.min.js, but I always get a transparent fill.
Permalink 09:13:22 am, by sarneil, 131 words, 384 views   English (CA)
Categories: Activity Log; Mins. worked: 60

reverse order of items in legend

The items in the legend appear in the opposite order that the associated colour blocks appear in the actual chart, so I wanted to reverse the order of the items in the legend. http://stackoverflow.com/questions/15812217/how-to-reverse-legend-labels-in-flot-stacked-bar-chart Turns out the version of flot we're using (0.7) doesn't support that, but version 0.8 does by adding 'sorted:"reverse"' to the end of the attributes for the legend: just after the specification of the colors: . Tried installing v0.8 of the library and calling it from the chart.php page, but no chart appears, nor do any error statements. I don't have the time right now to figure this out just to get the legend items to appear in reverse order, but I'll look into it when I have some time on my hands.

<< Previous Page :: Next Page >>

Capital Trials at the Old Bailey

Simon Devereaux has approximately 10,000 records of people convicted in potentially capital cases between 1710 and 1840 in London heard at the Old Bailey court. This project will create a web-based database which will allow interested researchers and members of the public to compose queries on that data (e.g. women charged with robbery 1710-1720). It must be able to support a range of queries and produce output allowing researchers to identify trends in judicial practice over that time.


XML Feeds