02/03/17

Permalink 02:37:47 pm, by sarneil, 186 words, 6 views   English (CA)
Categories: Activity Log; Mins. worked: 180

solve problem with count() and charting

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.

10/02/17

Permalink 09:45:17 am, by sarneil, 123 words, 17 views   English (CA)
Categories: Activity Log; Mins. worked: 720

Add Academic Notes, References in lightbox

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

14/10/16

Permalink 04:02:39 pm, by sarneil, 315 words, 31 views   English (CA)
Categories: Activity Log; Mins. worked: 180

copy all tables in dev instance to production instance

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.

Permalink 11:41:43 am, by sarneil, 49 words, 38 views   English (CA)
Categories: Activity Log; Mins. worked: 5

Bailey in oldbail account ready

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.
Permalink 11:38:56 am, by sarneil, 193 words, 35 views   English (CA)
Categories: Activity Log; Mins. worked: 240

Undo two results strategy due to out of memory error

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.

Permalink 11:11:52 am, by sarneil, 99 words, 30 views   English (CA)
Categories: Activity Log; Mins. worked: 3600

Fixed bug in flot library causing superimposing rather than stacking

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.

Permalink 11:06:01 am, by sarneil, 129 words, 38 views   English (CA)
Categories: Activity Log; Mins. worked: 2400

Separate Table and Chart queries

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.

25/04/16

Permalink 09:37:47 am, by sarneil, 186 words, 59 views   English (CA)
Categories: Activity Log; Mins. worked: 360

stacking in chart plot still not working

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).

Permalink 08:58:09 am, by sarneil, 196 words, 99 views   English (CA)
Categories: Activity Log; Mins. worked: 1200

v3_dev instance set up and working (almost)

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.

04/03/16

Permalink 04:02:09 pm, by sarneil, 193 words, 64 views   English (CA)
Categories: Activity Log; Mins. worked: 300

more on inconsistent counts between chart and table

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 >>

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.

Reports

Categories

March 2017
Sun Mon Tue Wed Thu Fri Sat
 << <   > >>
      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31

XML Feeds