Adaptive Database

In textarea elements produced by my db_fields.php code, I used to insert a space inside the tag, with this explanation: "Add a space to keep the tag from self-closing. We'll trim this on submission."

This space was obviously showing up in the text boxes for the user, which is a little annoying, so I've experimented with turning it off (for MdhTextField and MdhLinkSetField). I can't detect any problems -- the tag is written out by PHP as a regular tag, not a self-closing one, so I'm not sure whether something has changed in the PHP version, or whether perhaps I wrote this code as part of my original home project, running on my mholmes.com account, where a different version of PHP was converting all empty tags to self-closing when serializing the HTML. In any case, I've removed the extra space, and as far as I can tell there are no instances of self-closing tags as a result. Keeping a watching brief, though, just in case.

We've had at least two occasions on which the data in the doc_to_docPeriod_id field was set to null unexpectedly. Today I finally figured out when it happens, which in turn enabled me to figure out how (with help from JN). It was triggered when I ran my script for copying the live data into the backup database. There was nothing in the script which did anything to explicitly delete data in the live db, though, so I was mystified, until JN suggested a trigger in one of the tables operating on the other. When I looked at the live db, I found there was indeed a foreign key which was pointing at the other database, by mistake. When the docPeriods table was truncated in the dev db, all the corresponding the live db was set to null. Doh!

Changed the existing doc_to_period_id relationship into a one-to-many field. These were the steps:

• Create a docs_to_docPeriods table:

  --
  -- Table structure for table `docs_to_docPeriods`
  --
  
  CREATE TABLE IF NOT EXISTS `docs_to_docPeriods` (
    `dtp_dtp_id` int(11) NOT NULL auto_increment,
    `dtp_doc_id_fk` int(11) NOT NULL,
    `dtp_docPeriod_id_fk` int(11) NOT NULL,
    PRIMARY KEY  (`dtp_dtp_id`),
    KEY `dtp_docPeriod_id_fk_idx` (`dtp_docPeriod_id_fk`),
    KEY `dtp_doc_id_fk_idx` (`dtp_doc_id_fk`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
  

• Add constraints to it:

  --
  -- Constraints for table `docs_to_docPeriods`
  --
  ALTER TABLE `docs_to_docPeriods`
    ADD CONSTRAINT `docs_to_docPeriods_ibfk_1` FOREIGN KEY (`dtp_doc_id_fk`) REFERENCES `documents` (`doc_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `docs_to_docPeriods_ibfk_2` FOREIGN KEY (`dtp_docPeriod_id_fk`) REFERENCES `docPeriods` (`dp_id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
  

• Copy the existing data over to it:

  INSERT INTO `docs_to_docPeriods` (`dtp_doc_id_fk`, `dtp_docPeriod_id_fk`) SELECT `doc_id`, `doc_to_docPeriods_id` FROM `documents`
  

• Add the support for this in local_classes.php:

  $this->addField(new MdhOneToManyField('doc_periods', 'Period(s)',
                                  'doc_id', 
                                  'disPeriods',
                                  'dp_id',
                                  'dp_name',
                                  'docs_to_docPeriods',
                                  'dtp_dtp_id',
                                  'dtp_doc_id_fk',
                                  'dtp_docPeriod_id_fk',
                                  true, 'dp_name', true));
        

• Comment out the original field (we'll leave the data in there for a while, just in case).
• Test on dev db, then migrate to live.

The flagged-for-chapter field I added recently should actually have been a one-to-many, so I've now done that, which involves a quick SQL script to build the two required tables, and changes to the local_classes.php file to add the Chapters table as an editable one and the one-to-many field in the documents table (doc_chapters). There's some text data in the old field to be transferred; once JW has done that, I'll remove the old field. Tested first in dev, and migrated to live.

The dev version of the Manifest db apparently wasn't tested, although I assumed it had been, so when asked for the live one I went ahead and built it, but obvious problems emerged immediately. The heart of the problem is the attempt to include custom fields. The custom fields code is intimately tied to the original AdaptiveDB structure, and has never been written to be portable and generalized (no other project has ever needed it). In order to work for another db, a special view has to be created, and the code as it is also depends on one specific field in the original documents table (the docTypes field), which doesn't exist in the new db. It would be possible to port the code, but I don't have the time to do it right now, and I'm also not sure whether it really is needed in this new db, given that it's very quick to add new fields on request using the existing setup, so for the moment I'm removing the custom fields from the Manifest db. It now seems to be working (at least the dev version I've tested is working -- waiting for feedback on the live one because I don't want to enter test data into it). I've also written the standard live-to-dev copy script for future bugfixing.

In the long run, it would be possible to make the custom field code more portable, but no other projects have required it apart from JW's so far, so I'm reluctant. It's also a serious performance bottleneck, especially when it comes to building large spreadsheets, so it may be a bit of a developmental dead end.

The same thing that happened at the beginning of last year, which I blogged here, has happened again. All data in the doc_to_docTypes_id field was set to null between August 16 and August 17 last year. I've restored it using the INSERT statements in the preceding backup, processed using Transformer 3 in my Win7 VM, using a sequence that builds UPDATE statements. However, I still have no idea how this could possibly be happening. I need to take a close look at the db itself. Googling reveals nothing....

I've set up the dev version of the new db, and passed the URL to JW for a quick test before copying it to create a live version:

• Checked out copies of the AdaptiveDB code into the live and dev trees.
• Created creds.php files.
• Set up .htaccess files.
• Created a local_classes.php file.
• Modified the instructions.inc and the other includes for the new project name ("Manifestoes").
• Uploaded the dev version and briefly tested it.

JW has requested a new, smaller-scale db for another project, and sent details of the field structure. It will also use custom fields, so it's a useful testbed alongside the larger AdaptiveDB. Today I:

• built the schema in MySQL Workbench
• sent the schema diagram to JW for checking
• generated the db-creation sql
• added the custom-field-related sql (to create the fieldTypes, customFields, and customFieldData tables). I made a generic template for this, which I've added to the AdaptiveDB codebase.
• Created a db ("manifest") and an admin user for it ("manifest_admin") on mysql_dev.
• Tested and tweaked the table-creation sql until it worked.

If JW OKs the structure, I can build the local_classes.php file and we'll be ready to roll.

The long-term plan for rapid generation of spreadsheet output and niftier searches is to create a VIEW of the core table which contains all the data simply in the form of strings, which can be indexed, searched and rendered more rapidly. The first stage of this is figuring out how to turn the adaptive DB field types into string fields (or integers where they're simple integers). This is a bit tricky in the case of the one-to-many fields and the custom fields. This is a block of working code which covers a few of the field types:

CREATE VIEW `documents_view` AS SELECT
 
	`doc_id` AS `doc_id`, 

	(SELECT `dt_name` FROM `docTypes` WHERE `dt_id` = `documents`.`doc_to_docTypes_id`) AS `doc_to_docTypes_id`,
	
        `doc_archive` AS `doc_archive`,

	(SELECT GROUP_CONCAT(`disTypes`.`dt_name` SEPARATOR ', ') FROM `docs_to_disTypes` INNER JOIN `disTypes` ON `disTypes`.`dt_id` = `docs_to_disTypes`.`dtd_disType_id_fk` INNER JOIN `documents` AS `temp_docs` ON `temp_docs`.`doc_id` = `docs_to_disTypes`.`dtd_doc_id_fk` WHERE `docs_to_disTypes`.`dtd_doc_id_fk` = `documents`.`doc_id`) AS `doc_disTypes`

FROM `documents`

This is based on JW's documents table. The first is a straight MdhIntField; the second is an MdhStrSelectField; the third is an MdhStrLookupField; and the fourth is the trickiest one so far (thanks Jamie and Greg), an MdhOneToManyField.

Once I've worked through generation code for all of the field types, and tested it, I need to add an abstract function called getViewGenerationCode() to MdhBaseField, and implement it in the descendants, so that MdhRecord can call this function on all its fields and use it to construct an SQL command that will generate a view.

One issue that remains unresolved -- we're still working on it -- is what happens when your one-to-many field exceeds the default group_concat_max_len value for MySQL (which is 341). Right now, it looks as though any field in a view created with group_concat will end up as varchar(341), but we don't know what will happen if the output of the group_concat is longer than that; will it be truncated, or will the view field be expanded? The former seems more likely, unless we can increase the group_concat_max_len config setting, which can apparently be done for a single session (SET @@group_concat_max_len = 9999999;). Jury's out on this until we can do more research and testing.

Added some more detailed comments to db_fields.php, as part of the long process to document this stuff properly for Doxygen.