Depts

Did some minor reorganization and file renaming, as part of the long-term goal of having a standard set of naming conventions; images are where most of the problems are. Also removed a bunch of old obsolete HTML and plist files that were in the repo for no good reason. Finally, added detailed documentation on how to add images to the repo and use them on pages. Docs are coming along nicely.

Met with PAB to discuss progress; projected edits/additions are still not complete, so we'll hold off on moving data into svn for the moment. Rescheduled to October. Meanwhile, I updated my backup of the data from eXist, and we troubleshot a set of obsolete and duplicate data files. All sorted now.

Updated the stats and found a bug in the author count report (now people count), so I fixed that and updated again.

Rebuilt the schema because new places have been added and linked to; rebuilt the site and uploaded the results; worked with GL to clear a machine and disk space to house our new RA, and scheduled her on the machine for the coming semester.

Per AM, pulled units 28-40 from Cadfael and rebuilt the web pages, then uploaded to the new_wheelock folder. Everything is now complete; it looks like it should be swapped in to replace the existing wheelock folder when approved by MN.

AC asked for a new diagnostic, so I've added it, and in the process updated the existing code to handle the change from authors to people, as well as enriching the info given about each poem in a diagnostic test result.

I now have text search actually working, after a lot of workarounds to handle eXist's fierce validation of the forms of URIs. However, the implementation of the search facets is not functional yet. Getting there.

Added four new images to the gallery per KB, and at the same time, documented the process for adding images to the gallery. Also re-encoded one of the poems (Lock of Milton) following KB's model, and added two images to an article page.

First we create a brand-new table for persons.

PHASE ONE: CHANGE AUTHORS TO PERSONS.

DROP TABLE IF EXISTS `persons`;
CREATE TABLE `persons` (
  `prs_id` int(11) NOT NULL auto_increment,
  `prs_displayName` varchar(45) collate utf8_unicode_ci default NULL,
  `prs_surname` varchar(45) collate utf8_unicode_ci default NULL,
  `prs_firstnames` varchar(45) collate utf8_unicode_ci default NULL,
  `prs_honorific` varchar(45) collate utf8_unicode_ci default NULL,
  `prs_quals` varchar(45) collate utf8_unicode_ci default NULL,
  `prs_gender` int(11) default NULL,
  `prs_birth` date default NULL,
  `prs_death` date default NULL,
  `prs_nationality` varchar(45) collate utf8_unicode_ci default NULL,
  `prs_links` varchar(2048) collate utf8_unicode_ci default NULL,
  `prs_notes` varchar(2048) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`prs_id`),
  KEY `idx_authors` (`prs_surname`,`prs_firstnames`,`prs_notes`(255),`prs_id`,`prs_quals`,`prs_honorific`,`prs_displayName`,`prs_nationality`),
  KEY `fk_prs_to_gender` (`prs_gender`),
  CONSTRAINT `fk_prs_to_gender` FOREIGN KEY (`prs_gender`) REFERENCES `genders` (`gn_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2546 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Now we migrate the existing data into it.

INSERT INTO `persons` (`prs_id`, `prs_displayName`, `prs_surname`, `prs_firstnames`, `prs_honorific`, `prs_quals`, `prs_gender`, `prs_birth`, `prs_death`, `prs_nationality`, `prs_links`, `prs_notes`) SELECT `au_id`, `au_displayName`, `au_surname`, `au_firstnames`, `au_honorific`, `au_quals`, `au_gender`, `au_birth`, `au_death`, `au_nationality`, `au_links`, `au_notes` FROM `authors` WHERE 1;

Now we have to update foreign key constraints.

ALTER TABLE `poems_to_authors` DROP FOREIGN KEY  `fk_pta_author`;

ALTER TABLE `poems_to_authors` ADD CONSTRAINT `fk_pta_author` FOREIGN KEY (`pta_au_id`) REFERENCES `persons` (`prs_id`) ON DELETE CASCADE ON UPDATE CASCADE;

NOW UPDATE local_classes.php and test.

PHASE TWO: ADD ILLUSTRATORS.

Next, we can create the poems_to_illustrators table.

DROP TABLE IF EXISTS `poems_to_illustrators`;
CREATE TABLE `poems_to_illustrators` (
  `pti_id` int(11) NOT NULL auto_increment,
  `pti_po_id` int(11) default NULL,
  `pti_il_id` int(11) default NULL,
  PRIMARY KEY  (`pti_id`),
  KEY `fk_pti_illustrator` (`pti_il_id`),
  KEY `fk_pti_poem` (`pti_po_id`),
  CONSTRAINT `fk_pti_illustrator` FOREIGN KEY (`pti_il_id`) REFERENCES `persons` (`prs_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_pti_poem` FOREIGN KEY (`pti_po_id`) REFERENCES `poems` (`po_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

NOW UPDATE local_classes.php and test.

Next, we insert all of the distinct values for illustrators into the persons table.

This is the XQuery to generate the SQL:

declare namespace output = "http://www.w3.org/2010/xslt-xquery-serialization";

declare option output:method "text"; 

let $prefix := 'INSERT INTO `persons` (`prs_displayName`, `prs_surname`, `prs_firstnames`, `prs_notes`) ',
$inserts := 
for $i in distinct-values(//field[@name='po_illustrator'][string-length(normalize-space(.)) gt 0]/normalize-space(.))
return
let $displayName := $i, 
$surname := if (contains($i, ',')) then substring-before($i, ',') else $i,
$firstnames := if (contains($i, ',')) then normalize-space(substring-after($i, ',')) else ''
return
concat('("', replace($displayName, '"', '""'), '", "', replace($surname, '"', '""'), '", "', replace($firstnames, '"', '""'), '", "Illustrator")')
return concat($prefix, ' VALUES ', string-join($inserts, ',
'), ';')

Now the hard bit: create links in the poems_to_illustrators * table between poems and the new persons entries.

First, dump a new version of the database including the new rows in the persons table.

Next, run this XQuery against the dump.

declare namespace output = "http://www.w3.org/2010/xslt-xquery-serialization";

declare option output:method "text"; 

let $prefix := 'INSERT INTO `poems_to_illustrators` (`pti_po_id`, `pti_il_id`) VALUES ',
$inserts := for $i in 
 //field[@name='po_illustrator'][string-length(.) gt 0]
 let
$po := $i/preceding-sibling::field[@name='po_id'],
$prs := //field[@name='prs_displayName'][. = $i]/preceding-sibling::field[@name='prs_id']
return concat(' ("', $po, '", "', $prs[last()], '")')

return concat($prefix, string-join($inserts, ', 
'), ';')

Next, run the resulting SQL against the db to insert * the new records.

Finally, manually tweak all the stored procedures which generate the search table. I'm not sure these are even running properly any more, and they're not even being used, but best to avoid an error for now.

I've made the changes to the database as planned, and as far as I can tell, everything is working well. Results:

• The "Authors" table is now a "Persons" table, but it works just the same way.
• The "Persons" table now includes entries for all the people who were previously identified in the "Illustrator" field of the "Poems" table.
• The original "Illustrator" field is now renamed to "OLD Illustrator field"; eventually it will go away entirely.
• Below it there is now a new "Illustrators" field, which is a one-to-many link to the "People" table, working just like the "Authors" field.
• People in the "People" table who were imported from the old "Illustrator" field have the word "Illustrator" in their Notes field. That's just for information and convenience, and can be removed if necessary.
• Information on the new people in the "People" table (i.e. illustrators) is sparse, because we had very little info about them in the "Poems" table before. Someone will have to edit each of those entries. For instance, they all have unknown gender at the moment.
• It's possible that if the same person is both an author and an illustrator, they now appear in the "People" table twice; CE will be checking through all the illustrators and merging records where necessary.

Detailed explanation of the code steps follows in the next post.