Category: Academic

14/08/18

Permalink 04:25:39 pm, by mholmes, 44 words, 7 views   English (CA)
Categories: Academic; Mins. worked: 60

TCCD: rebuild of schema & site; scheduling and planning

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.

04/07/18

Permalink 11:48:11 am, by mholmes, 714 words, 11 views   English (CA)
Categories: Academic; Mins. worked: 10

DVPP: details of db changes

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.

Permalink 11:42:18 am, by mholmes, 224 words, 10 views   English (CA)
Categories: Academic; Mins. worked: 240

DVPP: reconfiguring the db

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.

03/07/18

Permalink 04:28:32 pm, by mholmes, 23 words, 11 views   English (CA)
Categories: Activity log, Academic; Mins. worked: 120

Keats: working on presentation for Tokyo

I have four to prep, so I'm starting early. :-) The Keats one, which is part TEI part Endings, is perhaps the easiest.

08/06/18

Permalink 04:53:41 pm, by mholmes, 27 words, 20 views   English (CA)
Categories: Academic; Mins. worked: 60

Keats: resolved svn conflict, did a couple of releases, discussed future plans

The problem conflict file in svn is now fixed (svn resolve [file] --accept theirs-full) and we have a good idea of where we're going after a discussion.

24/05/18

Permalink 04:50:04 pm, by mholmes, 48 words, 18 views   English (CA)
Categories: Academic; Mins. worked: 90

Keats: new images for gallery, fix for poem

Fixed the remnants of a conflict in a file committed by KB. Combined the two files with the two parts of Lamia into one, because KB has been linking to a single file that doesn't exist, and it's not horribly big. Added four new images to the gallery.

Permalink 10:48:32 am, by mholmes, 61 words, 19 views   English (CA)
Categories: Academic; Mins. worked: 60

TCCD: Completed ticket

Credits for proofers are now included in the output where they're available in the XML as respStmts. There is XSLT which can harvest most proofer info automatically from the HOCR, so with the exception of about 500 pages, where the proofer is only known personally to DH, that info can be added as collections are completed, and will appear on the site.

16/04/18

Permalink 11:02:52 am, by sarneil, 200 words, 16 views   English (CA)
Categories: Academic; Mins. worked: 360

Serv: spreadsheet output from DB

File in the /build folder called db_to_spreadsheet.php that creates an approximation of the original spreadsheet with info obtained from various tables in the db. Outputs a text file called db_to_spreadsheet.txt into the same folder as soon as the page is loaded. I'm using \t for the field delimiter and \n for the line delimiter. The character encodings are wobbly. I'm getting consistent and apparently correct results with either of these methods: 1) - right-click on View Text File link and save the file as db_to_spreadsheet.txt - Open file in text editor (tell it the file is Latin_windows_1 encoding), then set the character encoding on the file to UTF-8, save file. - in Excel, Import as text file, set character encoding to UTF-8, choose "delimited" not "fixed width", choose tab as delimiter character. 2) - click on the View Text file link, see the text in the browser window. - select text in browser window, copy - open text editor, paste text in, ensure character encoding is UTF-8 and save file - in excel, Import as text file, set character encoding to UTF-8, choose "delimited" not "fixed width", choose tab as delimiter character.

16/03/18

Permalink 04:01:12 pm, by mholmes, 14 words, 31 views   English (CA)
Categories: Academic; Mins. worked: 5

Keats: developing local search engine

I'll document this on the Endings blog, because it's really part of that project.

14/03/18

Permalink 05:19:55 pm, by mholmes, 9 words, 22 views   English (CA)
Categories: Academic; Mins. worked: 75

Keats: working on documentation

Documentation in the ODD file is coming along nicely.

:: Next Page >>

Depts

This blog is for work done for academic departments which does not fall under other categories.

Reports

XML Feeds