Myths on Maps

Today was the first test of the new AdaptiveDB code rewrites and new features on a serious scale, on the MoM db. Greg and I ported it over by writing a new local_classes.php file for the system. These are some notable issues, all solved:

• I had forgotten that I'd created a special lat-long field for storing coordinates, for the MoM project. We had to port that field definition back into the main codebase.
• That field wasn't quite functioning right, because when an empty string was returned to the db for storage, a zero was written. Obviously zero is a real coordinate, which is not the same as no coordinate at all. So I've rewritten the saveToDb() code in MdhRecord to take account of that.
• We discovered that when an integer field is used as a filter, a zero is written to the filter input automatically because zero was being set as the default value for the field, causing a filter value to be created on the page without the user's intervention. Because integer fields CAN be legitimately zero, we can't of course treat this as a special case, so I had to depend on the typing looseness of PHP to set the initial value of the field to an empty string, which solves the problem.
• Various other minor tweaks.

The idea of presenting data from a link table as if it were "part of" two different parent tables works really well; you just have to remember to get the field name parameters right (which is pretty hard -- I'm going to have to do some proper documentation here, once it all shakes down).

I've been working on a PHP KML generator for some time now. It's now producing functional KML that can be used in the MoM application as a base layer. The KML contains info about the location and links to the Perseus texts at UofChicago.

Next up is to generate KML files that include implicit searches (using $_GET) for People and Events which took place there (I'm using place as the "anchor" for all searches). What seems to be missing from the data is a People-to-Place table...

CM has been tasked with vetting the data in the DB and giving me the go-ahead to leave the DB alone and put all of my time in to the front-end.

Got a smidgen of actual coding done today.
I'm puzzling over the best way of querying the DB. This one (as opposed to the slide db) is more complex and cannot be contained by a single view. For example, if I do an all-encompassing query for everything, like this:

SELECT * FROM view_characters_characters,view_events_characters,view_groups_characters,view_events_places WHERE 
view_groups_characters.citation LIKE "%Melisseus%" OR
view_events_characters.citation LIKE "%Melisseus%" OR
view_characters_characters.citation LIKE "%Melisseus%" OR
view_events_places.citation LIKE "%Melisseus%" OR
view_groups_characters.character_name LIKE "%Melisseus%" OR
view_events_characters.character_name LIKE "%Melisseus%" OR
view_characters_characters.primaryChar LIKE "%Melisseus%" OR
view_characters_characters.relatedChar LIKE "%Melisseus%" OR
view_characters_characters.relationship LIKE "%Melisseus%" OR
view_events_characters.event_description LIKE "%Melisseus%" OR
view_events_places.event_description LIKE "%Melisseus%" OR
view_events_places.place_name LIKE "%Melisseus%" OR
view_groups_characters.group_name LIKE "%Melisseus%"

the query takes nearly 13 seconds to complete (indexing has not been done yet, however), and it's far from elegant.

According to this, there are very few views that are updatable in MySQL 5.x.
I'm still going to try making at least some of my views updatable, but it appears certain that I won't be able to create an updatable Character to Character relationship view. The article says in no uncertain terms that I can't update a view that has "Multiple references to any column of a base table." Fooey.

To create a view (in order to make my queries more straightforward) I had to build some SQL that queried the same table twice. My successful query looks like this:
SELECT lookup_primary_character_id, PrimChar.character_name, lookup_related_character_id, RelChar.character_name, relationship
FROM CharToCharRels
JOIN Characters AS PrimChar ON lookup_primary_character_id = PrimChar.character_id
JOIN Characters AS RelChar ON lookup_related_character_id = RelChar.character_id

The trick was to do one SELECT and two JOINs to the same table, using different aliases (PrimChar and RelChar) in the JOINs.

I extended this method to produce a query that creates a result which includes the name info and the cite info, thusly:
SELECT lookup_rel_id, lookup_cite_id, CharToCharRels.id, lookup_primary_character_id, PrimChar.character_name AS primaryChar, lookup_related_character_id, RelChar.character_name AS relatedChar, relationship
FROM CharToCharRels
JOIN Characters AS PrimChar ON lookup_primary_character_id = PrimChar.character_id
JOIN Characters AS RelChar ON lookup_related_character_id = RelChar.character_id
JOIN CharToCharToCiteRels AS cites ON lookup_rel_id = CharToCharRels.id

NOTE: Martin pointed out that Stewart had solved this, and blogged about it A YEAR AGO!! Grrr.

Just finished the EventToCharRels population.

As of now I've populated the following tables from L's spreadsheets:
Characters
Events
EventToCharRels
EventToPlaceRels
Groups
GroupToCharRels
Places
PlaceToCiteRels

Still to do:
CharToCharRels
CharToCharToCiteRels
CharToCiteRels
EventToCharToCiteRels
EventToCiteRels
EventToEventRels
EventToPlaceToCiteRels
GroupToCharToCiteRels

Need to finish some data transformations.
I have an Excel sheet that I'm slowly swapping strings for ints, for which I'm using Transformer in a Windows 7 VM.