Capital Trials at the Old Bailey

The production instance of bailey is now in the www/project/oldbailey folder in the hcmc account on nfs.hcmc.uvic.ca. The instance in the oldbailey account on web.uvic.ca will be discontinued (see below).

The dev instance of bailey continues to be in sarneil/public/oldbailey.

I updated both instances to work in php 5.6 and 7, including using https for all the protocol invocations.

Remaining: ideally, we'll put oldbailey into a projects folder in hcmc/www. I need to sort out why hcmc.uvic.ca/projects/projects/oldbailey does not redirect, but webserver2.hcmc.uvic.ca/projects/oldbailey does. I would have expected them to behave consistently, specifically they would both redirect.

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
$this->_select->group('tf.trial_file_id');
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
saved_searches

3b) drop fields in this order

FIELD HAS _id_fk POINTER TO FIELD(S)
execution_specials execution_mode, outcome
outcome_durations 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

trial_files criminal
aliases criminal

outcome_normalizeds outcome_group
crime_normalizeds crime_group
criminals gender

age_groups 0
crime_groups 0
execution_modes 0
genders 0
judges 0
judge_respite_normalizeds 0
juries 0
mercy_appeal_whos 0
mercy_appeal_whys 0
outcome_exceptionals 0
outcome_groups 0
respite_delays 0
respite_normalizeds 0
respite_punishments 0

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.

Informed SD that all outstanding issues with Old Bailey have been fixed as far as I can tell. He still has to provide some static text content. I'll also have to check that the production database backend has current data and that the production front-end is connecting to it.

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
http://www.2general.com/blog/2012/05/30/flot_stacked_bar_chart.html
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.

In trying to solve the inconsistencies between table and chart queries, I decided to create two separate queries rather than one which is manipulated depending on whether you're doing a table or chart output. Created parallel variables and methods in the Search.php class file and modified the execute function. Had to make similar adjustments in half a dozen other files. This had the desired effect, namely that doing a query in table view and then in chart view and back and forth gave me a consistent number of results and consistent outputs. It did not address the problem within the flot charting library of superimposing rather than stacking occasional colour blocks. Once I solve the problem with the flot library, I'll try to undo the changes made here.

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.