Adaptive Database

Got the OK from JW to migrate the changes in the previous post to the live db (after backing up everything first). All seems to be well...

At JW's request, created (in the dev version initially) a new table:

CREATE TABLE IF NOT EXISTS `docPeriods` (
  `dp_id` int(11) NOT NULL auto_increment,
  `dp_name` varchar(64) collate utf8_unicode_ci NOT NULL,
  `dp_description` text collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`dp_id`),
  KEY `dt_name` (`dp_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

then added a linked field in the documents table:

ALTER TABLE `documents` ADD `doc_to_docPeriods_id` INT( 11 ) NULL DEFAULT '1' AFTER `doc_to_docTypes_id` ,
ADD INDEX ( doc_to_docPeriods_id )

and then set up a relationship:

UPDATE `documents` SET doc_to_docPeriods_id = "1" WHERE 1=1

(necessary precursor), then

INSERT INTO `docPeriods` (
`dp_id` ,
`dp_name` ,
`dp_description`
)
VALUES (
'1', '[n/a]', 'Not assigned yet'
);

then

ALTER TABLE `documents` ADD FOREIGN KEY ( `doc_to_docPeriods_id` ) REFERENCES `adaptiveDBdev`.`docPeriods` (
`dp_id`
) ON DELETE SET NULL ON UPDATE SET NULL ;

Finally I ran this to set the original values back to null:

UPDATE `documents` SET doc_to_docPeriods_id = NULL WHERE 1=1

Then, for the front-end, I had to do two things:

1. Add a Period tab and table display/definition. In local_classes.php:

     class JwPeriod extends MdhRecord{
       public function __construct(){
         parent::__construct('docPeriods', 'Periods');    
         $this->addField(new MdhIntField('dp_id', 'id', 0));
         $this->addField(new MdhStrField('dp_name', 'Period name', '', array(), 64));
         $this->addField(new MdhTextField('dp_description', 'Description', '', MAX_LONG_TEXT));
       }
     }

   and in get_reclist.php (and a similar clause to get_search_results.php, for completeness and possible future needs):

   case 'docPeriods':
             $recList = new MdhRecordList('docPeriods', 'JwPeriod');
             $recList->recsToDisplay = $recsToDisplay;
             $recList->colsToDisplay = $colsToDisplay;
             $recList->orderByField = 'dp_id';
             break;

   and in get_rec.php, del_rec.php and save_rec.php:

   case 'docPeriods':
           $rec = new JwPeriod();
           break;

   Note to self: these duplicated blocks should be centralized somewhere.
2. In enter.php, added these items:

   <span id="tabPeriods" onclick="showTab('Periods')">Periods</span>

         <div id="pnlPeriods" class="tabPanel">
           
           <?php 
             getRecordList('docPeriods', 4, 20);
           ?>
           
         </div>
   

JW suggests that individual fields should be editable in-place, in the context of the table cell display, through a double-click. This is a great idea; we'd have to write a setup so that JS would retrieve a one-field form, with a hidden id field, and the MdhRecord object would have some streamlined write-for-edit and populate-from-form-fields routines that take only a single field.

I've made a couple of small changes this morning:

1. For tables of records that are NOT sorted by their id, the Pages dropdown list no longer includes the id range in its captions.
2. The id column is now much narrower than it was, leaving more room for other columns in the table.
3. There is now a print stylesheet, so that if you print one of the tables, it will appear differently from the screen view: the Actions column is hidden, the font size is smaller, the filter controls are hidden, and (on the Search page) the Search box itself is hidden. If you go to Print Preview in Firefox you'll see what I mean. Selecting Landscape mode in the print settings will give you the best result.

I've made some changes based on our discussion this morning:

• Linked files now show up in the table view and in the record view as paperclip images (which act as links to the file).
• The "Linked files" field is now second in the sequence, and I've set the default number of columns to display as 9, so that you still see all the columns you saw before by default in the table.
• There's another Search button at the top of the search page, so that it's easier to get to it when you are using the fields near the top.
• The default sort order is now Archive followed by Côte.
• You should now be able to delete user-defined fields from a record, simply by setting them to empty. In the case of boolean fields, they always show up as a drop-down list in the editing view now, so you can also set them to "empty" in order to remove them from the record.
• The default number of records returned in a search is now 500 rather than 50.

The remaining features we'll be adding later are better sorting options in the search itself, and also the ability to sort the rows of any table of records by clicking on a column header. We should be able to add those features next time I can get some time on the project (possibly November).

Meeting with JW and JMT, to get JMT started with the db. Out of this some short-term fixes and long-term plans emerged:

Short-term (bugfixes etc.)

• Add JMT to the showofha group (message already sent to sysadmin).
• Default sorting of Documents table should be by Archive, then by Côte.
• Bugfix for the current system: a custom field being set to an empty string should result in the field being deleted from the db.
• Default display of search results should include all records (meaning 1000, by default), not 50 as it is now.
• Bugfix for the current system: Boolean custom fields should always be displayed as a select box in the editing view, never as a checkbox, because when editing you need to be able to set it to an empty string in order to cause the field to be deleted (as part of the fix above).

Long-term (new features):

• Table view should be sortable by clicking on the headers. This would NOT involve re-doing the query; it would just sort the table as currently displayed.
• Search screen should have options for sorting by at least two fields (three for future-proofing). These could be achieved using three drop-downs of the fieldnames.
• Have the linked files field show up as a little paperclip somewhere when there is a file. This needs some thought; it might be achieved simply by moving that field earlier in the sort order and changing its view code (which would be fairly quick, I think).

Finally got the last feature working (the lookup field type in custom fields). Once I had that showing up appropriately in the Search form, I realized that it would be better to have regular lookup fields show up as lookups rather than selects, on the basis that this allows for easier searching with LIKE and percents. Also tweaked a lot of the CSS settings, then migrated the changes over to the main site. Then I migrated the main site data back into my development copy of the db, so they're both in sync.

Looking back through our correspondence, I realized that JW had mentioned in passing that one of the custom fields she wanted was a lookup field (a string field which has a drop-down of all previous distinct values applied to that field as a prompt). This wasn't part of my original spec, so it's an unexpected piece of extra work; ended up pushing into the evening in an effort to get it done, but it's actually quite complicated because the SQL that gathers the previous values is more complex than that for a normal lookup field. I've managed to get it half-done. When editing an existing record, the previous values show up, and work, but when editing a new field, or using the search, they don't get populated, and I can't figure out why so far. I can put one more day into this, but after that it'll have to be shelved, I think. Lots of interruptions from other projects and work coming in have made it very difficult to concentrate today, so I didn't get far until everyone had gone home.

Migrated the changes from the last few weeks over to the live db:

• Created the VIEW:

  CREATE VIEW docTypes_to_fieldTypes AS SELECT DISTINCT documents.doc_to_docTypes_id, customFieldData.cfd_to_cf_id
  FROM documents, customFieldData
  WHERE documents.doc_id = customFieldData.cfd_to_doc_id
  

• Exported the data from the customFields table in the dev db, eliminating my one integer test field which isn't needed right now, and imported it into the main db.
• Migrated all the PHP, CSS, JS and other code changes, backing up the existing setup first.

Everything appears to be working. I'm going to do some additional cosmetic changes and finishing work today, and probably migrate all of that at the end of the day.

Search is now functionally complete, as originally planned. I now have to think about migrating data over from the development db to the production db.