Adaptive Database

Cleaned out any project-specific stuff (except for the sample project), removed all doxygen-generated files from SVN, and regenerated all docs, after installing all the required libraries locally. I can now generate docs OK by running doxywizard, then loading the Doxygen file and running it; to get a PDF, I switch to docs/latex and run make refman.pdf.

Moved the memory and timeout settings into a file called <proj/project_settings{_SAMPLE}.php> so that it can be user-controlled and set on a per-project basis. Took the opportunity to rework the JSRDocs project setup to make it more robust, for testing this rollout (it previously had only one incarnation, and no testing version):

• Created parallel live and dev versions of the db.
• Created live and dev code trees.
• Wrote scripts for copying data from live to dev, and rolling out changes to dev, from dev to live local, and to dev on server.
• Tested and backed up.

Also noticed some floating users in mySQL that had been created for testing purposes and should have been deleted. Removed them. Now watching out for any unexpected behaviour in dbs, but not expecting any.

I have the link checker requested by AC coded up and working, and it's been tested in the VPN, Manifest and Trials dbs (tested in dev, then rolled out to live). I will also roll it out to JSRDocs when I get a chance (there's no dev there, so I always do that one last), and possible Properties (although there are no links in that DB AFAIK).

I have a working link-checker for FLD_LINK and FLD_LINKSET fields throughout the database, which uses CURL to check links and reports when they fail. There is still some work to do on this, because I'm not yet correctly escaping characters such as apostrophes which should never turn up in filenames or folder names, but which do regularly turn up because users love to ignore instructions. I'll have to decide whether I should report these as errors even though the links work (on the basis that the names are likely to cause problems down the road), in which case I should also add regex checking to the input fields on the page, or whether I should just escape the characters so that CURL can handle them, and give up on trying to badger people into using sensible filenames.

This feature was requested for VPN, but will also be useful for JW's projects.

The table returned from record deletion was embellished with the old column-header filter fields. I've now got rid of those, since they're obsolete. PUshed out the fix to all current live projects.

Updated the VPN, Properties, Trials, Manifest, and JSRDocs database projects with the new codebase changes.

Right now the project_variables_SAMPLE.php file, which is copied to create a working file, contains a hard-coded set of instructions for the db. These should be linked via an include somehow, because as the db code develops, the instructions change, and the ones in project files get out of date and have to be updated manually.

As part of the VPN project, phase one (agreed by the HCMC Committee), I've enhanced the Adaptive DB codebase with a couple of new features:

• You can now search for a comma-separated list of integers in an integer field (so you can retrieve a specific known set of records by their ids, for instance).
• Input from the search form is much better sanitized than before.
• The search wildcard character has been switched from the SQL % to the more familiar asterisk (*), and backslash-escaping of the wildcard now allows you to search for it as a literal character.
• Help pages and popup help buttons have been enhanced.

This development was done on the VPN dev db, and will be rolled out to the live version for full testing, before being rolled out to other dbs.

Added a simple filter feature to the MdhStrField/MdhStrLookupField editing control, so that typing filters the drop-down list to items which match what's been typed so far. This could be way more sophisticated (auto-complete etc.), but simple may be best in this case.

Edit: the following bug is now fixed, and I've rolled the fix out to the various dbs.

Steps to reproduce:

• Specify a reduced set of columns in the search table, and do a search.
• Edit one of the records.
• Save the changes.

The edited row that comes back and is inserted into the table does not honour the selection of display columns, so it typically runs out to the right of the table.

It looks as though search_instructions.inc is no longer needed. Check on this, and delete it from the repo if that's the case.

Currently, memory and time settings are in db/connect.php, which is a generic file shared by all projects. This requires modification of the file if a particular project has different requirements, which puts its working copy out of sync with the repo. Instead, these lines should be moved somewhere else -- possibly into local_classes.php:

ini_set("memory_limit","100M");
set_time_limit(120);

I've updated the instructions in the sample file to include all the new features.

I've been rolling out the latest version to some of the existing projects. So far, Manifestoes has the current code, as does JSRDocs (as of today); VPN dev has it, but I haven't moved it to VPN yet. Properties will be the last one I do, I think.

This was not major, just an annoying GUI behaviour:

• Do a search which specifies some selected fields to display in the results.
• The results table shows the expected fields.
• Do a multi-record edit.
• When the table is replaced, it no longer has the expected fields visible; instead, it has defaulted to the normal display fields.

It turned out that I wasn't sending the search filter parameters along with the record update data. That's now being done.

Started detailed bug-testing:

• Master edit-all checkbox wasn't changing the onclick event of the edit-all button. That's now fixed.
• In IE, the Search filters box is not collapsed on load. Fixed: obscure problem involving hiding and then showing again.
• In IE, checking the checkbox to include a one-to-many field in a multi-edit submission does not enable its "new" button. Fixed: remember the IE returns all nodeName attributes as upper-case, even if the document is XHTML.

Linkset fields now shorten their link text around an ellipsis by default (default max length is 60). Preliminary testing confirms that everything works on FF, Chrome, Opera and IE9. I'm not testing with earlier IEs.

Found the bug, eventually -- it was a lesson I've learned before, and forgotten: you can't use a period in the name of a request parameter. It has to be underscores.

I've done a couple of other tweaks, and now I'm going to roll it out to the dev version of the Manifestoes db and do serious testing on various browsers.

Multiple-record editing is now basically working, but I have one more problem to solve:

When you submit an edit form for an existing single record, for each one-to-many field, all existing records in the linking table are deleted, then new ones are created if there are any new ones in the submission; otherwise, none are left. In the case of a multi-record edit, though, we have a complex situation because if the one-to-many field is enabled, but no items are in it, then the intention is to delete all link records relating to all the records being edited. However, when the MdhRecord object reads data from the submission, it finds no parameters relating to the one-to-many field, because no selections were made, so it (currently) concludes that the field was not "set", and does nothing, leaving all the existing link records in place.

I need to find a way to submit the fact that the one-to-many field is selected even though there are no data items for it. This might be achieved by reading the disabled attribute of the hidden generic select control which is used to clone new selects when you add new items to the field. The disabled state of this control should reflect the disabled state of the field itself, so I should be able to send a flag based on this which the one-to-many control can read.

Once that's done, there will be extensive cross-browser testing to do, and then I want to complete a couple more tweaks: saving the state of the table display controls as cookies, and elliding URLs in MdhLinkField and MdhLinkSetField.

I've puzzled for a while with the issue of how to serve up a file type for download, with a suggested extension that's different from .php, from a .php module on the server. Turns out it's perfectly simple:

  header("Content-type: application/csv");
  header("Content-Disposition: attachment; filename=file.csv");
  header("Pragma: no-cache");

I've added that to the get_search_as_spreadsheet.php module, and it works fine. Another annoyance fixed.

All field types are now working in the multi-edit GUI. I've also fixed up some display issues in the one-to-many field, which were caused by working around a bug in webkit; after deleting a one-to-many item, a linebreak would be left in place, which was a big ugly.

Next I have to get the back-end code working...

MdhLatLongField had no validation routine attached to it, so I've added that. I've also made searching on the field more intuitive (it uses LIKE to enable variable precision). This field type should probably be changed into a coordinate-pair field, ultimately.

This morning I've completed the following field types:

• MdhTextField
• MdhStrSelectField
• MdhLinkSetField

Some other fields are covered by their ancestor fields, so only MdhLatLongField and MdhOneToManyField remain to be done.

I think I'll also add a method to shorten the visible URLs in MdhLinkField and MdhLinkSetField using ellipses, where there's no associated link image; that will make display layout more flexible.

The MdhRecord class now has a new paramater on its WriteAsFormElements() method which determines whether the form is being written for a single record edit (in which case all the fields are filled with existing info, in the case of an existing record, and they're all enabled), or for multi-edit (in which case fields are empty and disabled by default, and provided with a checkbox to enable them if the user wants to set them). The following classes can now write themselves out in the form of enable-able and disable-able editing controls, so that the user can select which fields they want to change in the multiple records:

• MdhIntField
• MdhBoolField
• MdhStrField
• MdhStrLookupField

Remaining to be done:

• MdhDateField
• MdhLatLongField
• MdhLinkField
• MdhLinkSetField
• MdhLongTextField
• MdhOneToManyField
• MdhStrSelectField
• MdhTextField

I've also been giving some thought to how to refresh the table of search results from which the editable records were selected, and concluded that the simplest way to do this is simply to re-run the query that was last used. We're already storing the last search query parameters in the spreadsheetSearch array, which is indexed by table name, so we can simply re-run it; although since we'll now be using it for two different purposes, it should probably be renamed to something like "lastSearch". Running a single search query will mean that only one query hits the db (as opposed to trying to update every row that's changed in the current table), and it will also solve the problem of rows which should disappear from the table because they no longer match the current query. We can cause this query to run by using the funcToCall parameter to the AJAX sendRequest() method.

Added checkboxes to allow selection of records that will be group-edited, as well as a master checkbox that controls them all, which functions in a tri-state way, and an edit button.

The next stage is to figure out how to perform the edit. The logical thing seems to be:

• Pass back a special query with a list of ids that will be edited.
• Generate an empty edit form, as for a new record, but with (again) checkboxes to allow the user to select fields which will be changed.
• Show the form in the first row, as with a new record.

One question: do we attempt to pre-fill any of the data, based on whether all the edited records share the same value or not? This looks like it might be very expensive in terms of queries to check this, unless there's a fast SQL approach to this. I'll do some research.

They seem to include early dates as well (such as 100 AD). I have to do more research on how we might support BC dates.

Changes have been rolled out the the Manifestoes project as a first test on real data.

The validity checking and help popup is now done. Now we have to write the backend.

Range searching for integer fields in now working (so you can enter "<50>40" in a search field in order to find a number between 41 and 49). This provides a model for range searching on dates, although that will be slightly more complicated.