Adaptive Database

I've now completed several major steps in the GUI for filters:

• First, I rationalized and streamlined the record list display controls at the top, separating the hidden form which keeps recordlist settings from a new, anonymous form which contains the controls for navigation (paging, row and column display totals, etc.). This enabled me to place another copy of the navigation controls at the bottom of the page, which is another convenience.
• I've decided to place the filter controls inside the table header row, obviating the need for them to have labels (since the header row already has the field name in it). I have three drop-down filter controls displaying themselves already (the three fields which are MdhStrSelectField objects).
• I've added a Filter button to the final column of the header row of the table (the Actions column, whose header already contains the "New record" button). This Filter button actually just calls the generic navigateList function with the current settings. That's the same function called by the other navigation controls, but it will have the effect of applying any filter changes because the filter control settings will be read during the navigateList function and sent back to the server, to be handled by MdhRecordList. I don't want to use an onchange event on the filter controls themselves, because the user will probably want to make several filter setting changes before applying them (and some will ultimately be text boxes).
• I had to wrap the filter controls in a <form> element, but I couldn't wrap the whole table in a form, because other editing forms are often inserted into the middle of the table by the editing code; I needed to wrap only the header row. If I enclose the <tr> element in a <form> element, though, the display blows up, and the XHTML would be invalid anyway, so I had to include a separate little form inside each table header cell to wrap its control. Annoying, but it works.

Found that I'd forgotten to add display and editing clauses to the get_rec, save_rec and del_rec libraries to handle the new document status value table, so I did that; in the process I made a couple of cosmetic tweaks (getting rid of the gradient fill on tabs, for instance, which looks hokey and annoys me).

Added a new control in the form of two classes (MdhRecPage and MdhRecPageList), the latter of which is used by MdhRecordList to place a drop-down list of record pages at the top of the table view of records.

Working out how I can best implement filters in a structured way. This is the initial plan:

• The base class for DB fields, MdhBaseField, gets a new public property called $usedAsFilter, which is false by default. That's done in the DEV tree.
• All constructors have a final parameter, $usedAsFilter, which has a default value of false. Also done in DEV.
• The base class gets an abstract function:

  abstract public function writeAsFilter($tableName, $suffix, $inclCaption, $currFilterVal);

  Done in DEV.
• All the descendant classes are given implementations, all empty initially; we can fill them in later. Done in DEV.
• Implementation of the functionality can start with the MdhStrSelectField class, which is very simple in that it auto-populates itself with its possible values already. I've already filled out the writeAsFilter function in that class to output a select element.
• The basic idea is that a temporary instance of the relevant table's MdhRecord object would be created by the MdhRecordList object, and this would do two things: first, load its current settings in from the submitted form (ensuring persistence of choices across requests), and second, spitting itself out in the form of a set of form controls at the top of the main form.
• This means that the MdhRecord object needs functions for readAsFilter, which reads the form elements and (more important) where they have values, constructs an SQL filter string from them, which is then available to the parent MdhRecordList; and writeAsFilter, which writes all specified fields out as filters.

I'll continue with this tomorrow, and hope to have the first filter type (lookup strings) from a linked table) working by the end of the day.

Ported over all the changes from yesterday to the main db, and let EM and JW know.

I''m working on some requests from JW regarding the order of fields in the documents table, and also the need for one new column. This is a record of my changes in the DEV copy of the project, so that when we're happy I can port those changes over to the LIVE copy:

• Edited local_classes.php to change the order of fields in the JwDocument object.
• Created a new table called docStatusVals, with one primary key (dsv_id) and one VARCHAR 64 (dsv_description). Added four items per JW's instructions, and one initial value indicating "not yet assigned". This will be a lookup table for the new field in the documents table.
• Ran this on the main documents table, to assign an initial value of 1 to this new field in all of the records: UPDATE documents set doc_to_docStatusVals_id = "1" where 1=1. This step is necessary before you can add the actual foreign key constraint; otherwise setting the constraint will fail.
• Added a new line in the JwDocument constructor:
  $this->addField(new MdhStrSelectField('doc_to_docStatusVals_id', 'Status', 0, 'docStatusVals', 'dsv_id', 'dsv_description', true));
• Added a new panel to the editing interface:

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

• Added a new tab, to make the panel accessible:

  <span id="tabDocStatusVals" onclick="showTab('DocStatusVals')">Document status values</span>
  

• Added a new class to the local_classes.php file:

    class JwDocStatusVal extends MdhRecord{
      public function __construct(){
  //Call the parent constructor.
        parent::__construct('docStatusVals', 'Document status values');    
        $this->addField(new MdhIntField('dsv_id', 'id', 0));
        $this->addField(new MdhStrField('dsv_description', 'Status description', '', array(), 64));
      }
    }
  

• Added a new clause to the get_reclist.php file to handle the new table type:

  case 'docStatusVals':
  $recList = new MdhRecordList('docStatusVals', 'JwDocStatusVal');
  $recList->recsToDisplay = $recsToDisplay;
  $recList->colsToDisplay = $colsToDisplay;
  $recList->orderByField = 'dsv_id';
  break;
  

• In db_recordlist.php, added a new option to see 500 files in the dropdown list (line 293: $recsToDisplayOpts = array(10,20,50,100,200,500);).

The project is now live, with 356 documents entered, and I can't go hacking around with the code and entering phony data, so I need a development copy to work on. I cloned the site in site_dev, and cloned the db to adaptiveDBdev, hooking the cloned site up to the cloned DB. To clone the DB, I had to create an empty db, then take a dump from the source db, changing the database name; then I created a new empty db in phpMyAdmin, and imported the dump file. That was the only way to get the key constraints to transfer successfully; the "Copy database" functionality in phpMyAdmin attempts to add constraints before all the tables are created, which causes it to fail.

This is intended as documentation for Stew and Greg to help JW and EM as they start using the database. Unfortunately, they're going to use it for the first time AFTER I've gone on vacation.

• The database is on mysqldev. It's fairly simple in structure, with all InnoDB tables, using UTF-8, and with a few foreign keys linking tables.
• The interface is here. This uses https to control access, and the htaccess file allows all three of us access, as well as JW and EM. I have tested EM's login and it works; we haven't had a chance to test JW's yet, and if, as I suspect, the sysops haven't yet copied over her hash to the TAPoR boxes, it may not work. If so, Greg -- could you handle this?
• The db interface has a full set of instructions (click on the Instructions tab). I've only tested in Firefox, so I'm recommending that they only use Firefox. As far as I can see, everything's working OK -- I've tested with the full range of ampersands, quotes, accents and Japanese characters -- but I'd be amazed if there are no bugs at all, so they might need some help working around anything that emerges, till I get the chance to fix it.
• They'll be working from JW's document collection on the "showofha" account on the SAN. I'm not linking to it because I don't want Google-harvestable links to the docs to be available until I know whether this data should be "public" or not; it's not restricted right now, but it could be if necessary. I asked for both JW and EM to be made members of that group, but when JW tried to log in, she couldn't get in; I put a ticket in about this yesterday, then again today when I got no response, copying Greg this time. It's "Re: [RT #138854] Request for new project group on TAPoR".
• One of EM's first tasks will be building PDFs from each of the page-image collections in "showofha"; we've already gone through this process once, and it's straightforward. The full dataset is available on the Mac with two monitors, and linked from EM's desktop. Once the PDFs are created, they'll need to be uploaded to the SAN account.

That's it for now, I think... I'm only really away from July 4 to 11, and if any quick fixes are needed I can do them from home after that.

Met with JW today and set up some actual project and document type records, and cleared out some of the testing data. Discussed what she'll do with her document collection. this is the plan:

• Over the weekend she'll rename the folders and files to eliminate network-unfriendly characters (spaces, punctuation and accented characters), and also normalize case.
• On Monday we'll upload the whole collection to the showofha account.
• EM will be shown how to use Adobe Acrobat to create a single PDF from a collection of page images (I tested this on the dual-screen Mac, and there's an easy wizard for it). EM will create a PDF for each document, in the same folder as the images which constitute it, on the server. These PDFs will be the item which is primarily linked for each document, although it's also possible to link to each of the individual images.

Meanwhile, I added a new field to the db, linking documents to projects, and we will finalize the field names on Monday. I should then have the db in a state where JW and EM can enter documents and doc types into the db throughout July. When I come back, I should be able to finish the custom fields code, so they can continue with that.

Meanwhile, I got further with the custom fields code, and I'm now able to do a check to see whether any other document of the same doctype has a custom field of a given field type (to determine whether a field should be set for a given document). However, I'm now rethinking again; in the interests of simplicity, I think it might be much easier to create a single table of custom fields, with each one having four data columns, of which only one will be used. The item type would be determined by its type id, and the wasted space will be fairly negligible, compensated for by the great speed improvements due to the complexity of the SQL queries being substantially reduced. Given that a lot of these queries will be executed for any retrieval of a single document record for editing or display, I think the minor space sacrifice is well worth it for the speed improvement. I'll implement that on Monday, and rewrite the custom fields code I've got so far; it'll be much more straightforward.

I may not finish this stuff by Tuesday, but I'm moving steadily along. I've got the basic display and editing of custom fields working using a descendant of the MdhRecord and the regular MdhRecordList object. I've built some custom versions of MdhField descendants which have properties for "set" and "should be set", and I've started work on an object called MdhCustomFieldGroup, which is designed to retrieve all the custom fields that have a particular string for their main group, and then, given a linked document record, retrieve all the instances of fields that exist linked to that record. It will then be able to present the whole group of fields (probably as a fieldset), with set ones set, and with those that SHOULD be set highlighted. The "set" property will have to be rendered in a particular way, perhaps with an extra button that turns the field on or off; in the case of display rather than edit, we need only show the fields which are "set".