Depts

Changes to course information on SA's instructions.

At JW's request, converted the Court field in the main Trial table from a single item to a one-to-many. Here's the SQL:

/*Create the new linking table.*/

CREATE TABLE IF NOT EXISTS `courts_to_trials` (
  `ctt_ctt_id` int(11) NOT NULL auto_increment,
  `ctt_tr_id_fk` int(11) default NULL,
  `ctt_ct_id_fk` int(11) default NULL,
  PRIMARY KEY  (`ctt_ctt_id`),
  KEY `ctt_ibfk_1` (`ctt_tr_id_fk`),
  KEY `ctt_ibfk_2` (`ctt_ct_id_fk`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

/*Add constraints to the new linking table.*/

ALTER TABLE `courts_to_trials`
  ADD CONSTRAINT `ctt_ibfk_1` FOREIGN KEY (`ctt_tr_id_fk`) REFERENCES `trial` (`tr_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ctt_ibfk_2` FOREIGN KEY (`ctt_ct_id_fk`) REFERENCES `court` (`ct_id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
/*Copy existing data.*/

INSERT INTO `courts_to_trials` (`ctt_tr_id_fk`, `ctt_ct_id_fk`) (SELECT `tr_id`, `tr_court` FROM `trial`);

/*Delete original field.*/

ALTER TABLE `trial` DROP FOREIGN KEY `tr_ibfk_1`;
ALTER TABLE `trial` DROP COLUMN `tr_court`;

Updated the local_classes.php file appropriately. Tested on dev, then ran on live. In the process of testing, found a little bug: when deleting a record, the table that comes back still has all the old column-header filter fields in it. I'll fix that now.

New account on home1t has been set up for storage of page-images, so I've set up the db to point to it, and tested it. I've also tweaked the dev_to_live_update.sh script so it doesn't copy itself into the live db tree.

Changed a title and added a poster on JS-R's instructions; also did some research on easy ways to do free event registration, and found three plugins that might be usable. This will be required for one of the talks, which will be a big draw, and must be limited to 80 attendees.

On instructions from JW, added a new table and two new fields to the db. Tested first in dev, then carried out the same changes in live. SQL:

CREATE TABLE IF NOT EXISTS `period` (
  `pd_id` INT NOT NULL AUTO_INCREMENT ,
  `pd_name`  varchar(128) collate utf8_unicode_ci default NULL ,
  PRIMARY KEY (`pd_id`) 
)
ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `period` (`pd_id`, `pd_name`) VALUES (1, '[Unassigned]');
INSERT INTO `period` (`pd_id`, `pd_name`) VALUES (2, 'AR');
INSERT INTO `period` (`pd_id`, `pd_name`) VALUES (3, 'FR');

ALTER TABLE `trial` ADD COLUMN `tr_period` INT(11) NULL AFTER `tr_cote`;
ALTER TABLE `trial` ADD COLUMN `tr_photos` INT(11) NULL AFTER `tr_description`;

ALTER TABLE `trial` 
ADD CONSTRAINT `trial_ibfk_3` FOREIGN KEY (`tr_period`) REFERENCES `period` (`pd_id`) ON DELETE SET NULL ON UPDATE CASCADE;

Corresponding changes made to local_classes.php. Also wrote a script to copy data from live to dev db, and fixed a minor bug in the dev_to_live_update.sh script.

Made a number of changes, on BB's instructions.

...completion of work started last Friday, on DR's instructions.

The old site is still the live site, and will be for a while, so we're still updating it. Did some updates on DR's instructions.

I'm just mapping out what I have so far from AC, based on our recent discussions. This will probably change a lot before implementation.

1. Convert "authors" to "people", so it can also include engravers and illustrators. They will therefore inherit all the fields already available for authors automatically.
2. Add an "illustrations" table consisting of:
   • id
   • illustration title (if there is one)
   • illustration description
   • medium (MdhStrLookupField)
   • organ
   • vol
   • num
   • page(s)
   • illustrators (one-to-many link to "people" table)
   • engravers (ditto)
   • associated poem(s) (one-to-many link to "poems" table)
   (Possibly also "anonymous" and "unsigned", as we have for the poems?)
3. Add a one-to-many "illustrations" field to the "poems" table.
4. Remove the following fields from the "poems" table:
   • illustrator
   • illustrations

As documented in the AdaptiveDB blog, I've done the basic enhancements to the AdaptiveDB codebase that were specified for phase one of VPN. I'll roll out the results to the live DB tomorrow. The only remaining tasks for me arising out of the meeting the other day are: the requirement to generate a bash script to check links automatically (still haven't figured out the best way to approach that), and the alteration of the db structure to hive off the illustrations (still waiting on feedback from AC on that one).

I've gone through SR's spec document for the form he wants to create to gather metadata from the authors of the articles in his encyclopedia. Quick and dirty would be SurveyMonkey, but it's not really set up to allow the user to add additional elements to the survey form. (e.g. name major works associated with artistic movement X and rate that work's importance outside the movement). I'm not sure he'll be willing to compromise the look and feel of the form to the degree I think will be required by SurveyMonkey.
Also created a very small ajax site that does allow the user to add elements to the form, and then processes the values of those elements to generate strings that go into an SQL db. Major concern is what to use as delimiters within those text strings. E.G. if the field in the DB is called Question1 and there are an arbitrary number of fields in the form whose values I must concatenate to produce the string that goes into Question1, what do I use to delimit each of those values from the form field? We're only using the SQL db as a temporary repository, so I don't really want to go to the bother of creating a full-on set of relational tables and all the code to modify then properly.

Met with AC and worked out details of the phase-1 enhancements agreed by the HCMC Committee:

Adaptive DB enhancements and data reorganization:

• Allow searching on an integer field (such as id) using a comma-separated list of values.
• Add a popup-help button to text fields.
• Create a feature that can generate an automated script that uses curl or wget to check the accuracy of links inside links fields.
• Hive off illustrations into a separate table, move the data over there, add some extra fields. I'm still discussing this with AC -- it's not clear how much information needs to be stored with an illustration, or how to handle illustrators -- but for the moment, RAs will stop entering illustration info (they've been inserting titles into the links field so far, which makes for broken links, so we'd like to minimize that).

Search interface enhancements:

• Retrieve page-image links and show them using a generic page icon that's clickable to show the image.
• (Eventually, after reorganization of illustration data): add linked illustration search, search by illustrator, etc. Perhaps this will require a separate search page, since people may want to search the illustrations independently of the poem data.

Added the list of new lectures scheduled for this year, and tweaked the committee list, per JS-R.