Certain trial-file records (e.g. Ely Smith 1750 age 20 Robbery) return a count() of 2 rather than 1, and I can't figure out why. The count returned from the sql query seems to be used only in the json.php file to determine how big to make the brick for the specified crime/gender/agegroup etc for the specified year. So, I modified one line of code in the json file to replace the value of count() with 1.
The _execute function in the Search.php file has been simplified so that it always uses
Previously table output used that argument and chart output used the argument
$this->_select->group($this->allowedGroups[$this->_data['group']] . ', YEAR(tf.trial_file_start_date)');
but that caused inconsistencies in the total number of records returned, which I haven't been able to resolve.
The issue now is that the chart appears as a stack of same-coloured bricks for each year-attribute pair rather than 1 brick of height count(). For the time being I'll trade off the slightly less elegant display for the accurate and consistent counts.
Added js, css and html to form.php to support lightbox feature. Also added buttons to the form to launch the lightbox and populate it with the correct block of information.
Information extracted from docx files from SD. Also took those docx files and aggregated them into one big libreoffice file and then exported out to PDF file.
Added button on search page (and links within the lightbox) to launch the Full Notes and References pdf file in a new tab (as otherwise, if user hits browser back button, typically the form is reset and they lose any settings they've made).
Files modified: includes/search/form.php
Fiddled around with adding a button in footer. Files modified: css/main.css, includes/footer.php
Many of the tables in the bailey db have foreign key constraints to other tables.
So, if I simply try to export an sql dump of one instance and import that into an existing instance, I get "foreign key constraint violation" errors.
SQL won't let me truncate a table which has a foreign key pointing to it from another table. Only solution I could come up with was to figure out the foreign keys for each table (by looking at the xsl export file), then in the destination DB drop the tables in reverse order of dependencies (so that for each table, I had already dropped any tables that had foreign keys pointing to that table). Then I could import the .sql file into the database containing no tables (and thus no constraints on tables).
Specifically, for Bailey
1) EXPORT dev database (e.g. dev_db.sql), ensure you include the "DROP TABLE" checkbox
2) EXPORT prod database (e.g. prod_db.sql), ensure you include the "DROP TABLE" checkbox
3) On production database:
3a) DON'T DROP this field
3b) drop fields in this order
FIELD HAS _id_fk POINTER TO FIELD(S)
execution_specials execution_mode, outcome
outcomes trial, outcome_normalized
respites trial, respite_delay, respite_punishment, respite_normalized
judge_respites trial, respite_delay, respite_punishment, respite_normalized
crimes trial, crime_normalized
mercy_appeals trial, mercy_appeal_who, mercy_appeal_why
trials trial_file, judge, jury
3c) import dev_db.sql (it may time out and tell you to upload again to continue) until you get "Import has been successfully finished" message
4) test to make sure everything is working.
If I create a query for all the records in the db (9481), and request table output and then on the table click on Plot on Chart, I go to the chart page but get a php out-of-error message when creating the array of result objects.
If I create the same query and request a chart output, all is well. If I then ask for table output, all is well. If I switch back to chart, all is well.
I don't get why I get the error in the one case and not the other.
I reverted back to one query and results in half a dozen files (i.e. undid the changes documented a couple of posts ago). I used the //2to1 comment tag at each change and commented out my recent modifications, but left them all in there. That reduced the amount of memory I needed, but not as much as I hoped. So, I added a statement in the config.php file to allocate more memory to the process, and that worked. Given the absolute numbers aren't huge (64MB to 128MB) and relatively modest user-demands, I think this will be OK.
After many hours of labourious testing of the chart query and the code processing that into json data structures and then processing those for use by the flot library, I could confirm that I had an instance of the bug described at https://github.com/flot/flot/issues/1076 and working through that found the solution at
and the code snippet I needed at https://code.google.com/archive/p/flot/issues/247
I have patched the jquery.flot.stack.js file as instructed there, and the stacking now works properly.
Query 1731-1733 ages 8-15 I get six hits.
group by crime I get:
1731 1 Robbery and 1 Stealing overlayed (incorrect)
1732 2 Robbery stacked (correct)
1733 1 Currency and 1 Robbery overlayed (incorrect)
group by gender I get
1731 2 male stacked (correct)
1732 2 male stacked (correct)
1733 1 male and 1 female stacked (correct)
group by outcome I get (THIS IS A PARTICULARLY WEIRD CASE)
1731 1 executed and 1 transport stacked (correct)
1732 2 executed stacked (correct)
1733 1 executed and 1 transport overlayed (incorrect)
group by respite I get (THIS IS A PARTICULARLY WEIRD CASE)
1731 1 toDie and 1 specified overlayed (incorrect)
1732 2 toDie stacked (correct)
1733 1 toDie and 1 specified stacked (correct)
group by respite I get (THIS IS A PARTICULARLY WEIRD CASE)
1731 1 1-14 and 1 15-19 overlayed (incorrect)
1732 2 15-19 stacked (correct)
1733 2 15-19 stacked (correct)
in the query, the for the initial query is crime_group_text, year, and the other queries appear to be grouping-by correctly. I don't get why sometimes there is overlay and sometimes stacking of 2 series (e.g. the two incorrect results for sort by crime), and I really don't get why there is sometimes overly and sometimes stacking for the same combination of series (e.g. sorting by outcome, executed and transport in 1731 and 1733).
After many hours trying to debug why the chart would not appear in the v2_dev instance, I took Greg's advice and created a copy of the v2_prod instance, aimed it at the v2_dev backend db, and then gradually added in the modifications I had added in to the v2_dev instance until something broke. It turns out that in the /search.php page I had included a php echo statement.
<?php echo '<p>/search.php.136 <br />' . $_SESSION['finalQuery'] . "</p>" ?>
That line was the problem when that file was invoked by json as an ajax call. Sending that output somehow caused the call to fail - I suspect something to do with headers not being sent first, but I haven't figured it out for sure.
Anyway, I now have v3_dev which has all the new interface elements Simon wanted (with the exception of minimum pardons), and connects to the current data (which is in the dev db).
The counts in the charting seem to be correct now too. The only thing not working properly in the charting is the stacking. See next post.
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.
:: Next Page >>
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.