HCMC Blogs

I've finished writing and testing a PHP script to import an XML file (validated against http://web.uvic.ca/~lang02/bailey/schema/bailey_trialfile_proofing.rng ) into the MySQL database. The script using SimpleXMLElement to load and loop through the file. The database handling is done by Zend_Db, since that's already being used in the main Bailey website.

I wrote the importer in such a way that new foreign keys (e.g. crime_normalizeds, judges, etc.) don't need to be entered into the MySQL database beforehand. Since the XML is validated against the schema, I can be reasonably certain that all of the values in the data are intentional. So, if the importer comes across a value that's not already in the MySQL database (say, a new judge), it simply inserts that value and continues with the import.

The importer isn't yet online, but once it is I'll publish its URL to this blog.

Updated courses page with requested Timetable Change.
Sent confirmation email to SA (Rels).

While working on the import process to bring the XML data into the website database, it occurred to me that, after all of the changes to the schema, the <case> element doesn't serve much of a function anymore. While it used to group together multiple trial files, all of the useful grouping information (namely the trial dates) has been moved to the <trial_file> elements themselves. So, we don't need the extra level in the hierarchy.

For that matter, we also don't need the <regular_cases> element that wraps up the <case> elements, since it's also an extra level of hierarchy. The schema really just needs to be: data -> one or more trial files

Thus, I've removed <regular_cases> <case> from the schema and modified the XSLT transformation scripts accordingly. I also modified the data that's already been processed.

These changes will make the PHP script that converts the XML to MySQL simpler.

Due to a primary key sequence inconsistency on the occupation table between my local machine, where I first imported and tested all of the data, and the development server, many of the new occupation codes inserted for the 1911 data had a different occupation_id on each machine. As a result, the occupations in the 1911 view for census records made reference to incorrect occupation IDs and, thus, incorrect occupation codes.

Since occupation_id isn't actually needed - occupation_code is also a unique identifier and doesn't suffer from the same sequence restraints - I decided to add an occupation_code column to all of the census tables and update the views to use this column instead of occupation_id.

The steps I took:

#1: Added occupation_code character varying (5) column to 1881, 1891, 1901, 1911 on both local machine and dev server

#2: For all except 1911 executed this SQL on test server:

UPDATE census_1881 SET occupation_code = (SELECT occupation_code FROM occupation WHERE occupation.occupation_id = census_1881.occupation_id)

UPDATE census_1891 SET occupation_code = (SELECT occupation_code FROM occupation WHERE occupation.occupation_id = census_1891.occupation_id)

UPDATE census_1901 SET occupation_code = (SELECT occupation_code FROM occupation WHERE occupation.occupation_id = census_1901.occupation_id)

On my local host, I also executed this on 1911:

UPDATE census_1911 SET occupation_code = (SELECT occupation_code FROM occupation WHERE occupation.occupation_id = census_1911.occupation_id)

#3: Altered the 1881, 1891, 1901, 1911 view definitions to change this (1881 used as example):

LEFT JOIN occupation ON occupation.occupation_id = census_1881.occupation_id

To this:

LEFT JOIN occupation ON occupation.occupation_code = census_1881.occupation_code

Also changed the occupation_id column definition in the views from

census_<year>.occupation_id

to

occupation.occupation_id

. This effectively rendered the occupation_id column in the census tables obsolete, since occupation_id is no longer used as the foreign key lookup in the views.

#4: Modified vOccupations<year> views to join the census table on occupation_code rather than occupation_id

#5: Dropped now unneeded occupation_id from census_1911, but not the earlier data. It's not strictly needed in the other tables but I don't want to rock the boat too much.

#6: re-imported all of the 1911 data from my localhost to the development server.

All is good - incorrect occupations have been corrected.

Additions/changes:

-TALKS page: eliminated the Upcoming Talks section in center column
-All pages: included under Announcements the recent announcement - Call for papers....... etc.; with link to pdf.

In progress:
HOME page: logo discussion
Technicalities: Stopping Right Column from breaking on window refresh

Sent update email to SB.

Met with PD yesterday (July 26) to go over some loose ends with the new dataset. Tasks for me that arose from the meeting:

• PD pointed out some inconsistencies with occupation codes; namely, that some 1911 census records have the wrong code. I need to do a full scan to find out how many there are, and then fix them.
• PD also pointed out some incorrect data in 6 of the records. The mistakes were in the original CSV files. I need to go into the database and correct them.
• The 1881 data should have Racial/Ethnic origin as a searchable field - it's in the data but not searchable on the website.
• The 1901 data should not have a searchable 'dot' for birthplace of father.
• The meta tags should be updated with new information to match the latest data imported.