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.