Adaptive Database

The plan outlined in the previous post requires detailed testing at each stage of the process, and right now that would have to be done by hand, which is very tedious and error-prone, so I've started working on a test page which is designed to run all the key operations. This took a long time to get started, because initially I was trying to call pages which are designed as AJAX response routines inline in the page; that doesn't work because allow_url_include is false (and can't be changed in php.ini with our current setup). I then tried using curl to get the content, but that failed too, possibly because I'm using https. Rather than try to figure that out, I decided to use JavaScript to run all the functions, meaning that I'd actually be testing the AJAX components at the same time.

I now have a page which is doing some of the work -- retrieving a table of data, retrieving a view of the first item, and retrieving an edit form for the second item. Next I need to make it pull down a form for a new item, copy all the data from the existing record to that item, then change the existing data, submit the form, and retrieve a view of the changed item. Then perhaps it should create the new item, check it's there, copy its data back to the old one, etc.

I also need to incorporate search, and probably filtering too, to make sure that these core functions work. With luck, I'll end up with a library that runs its tests in less than two minutes, with clear results. That will make the process of pruning out the cruft much easier and quicker, and also help with developing future functionality.

I've made some significant progress with this, but now I'm getting down to the parts where I have obsolete properties entangled with similar live properties in method signatures and code blocks. I've managed to eliminate three of the four obsolete properties from MdhRecordList: $isEmbedded, $parentId, and $parentIdField. These are hardly used at all by other classes, so eliminating them was simply a local issue, with a few exceptions.

The remaining property is $parentSuffix, and this is problematic because it's passed to any child MdhRecord objects, and referenced throughout the code of both classes, although I don't believe it's actually used at all any more. This is what I believe to be the case, after my first reading of the code and comments:

• I don't believe any of the current code makes use of the MdhRecordList::parentSuffix property at all; it's set to an empty string and never changed.
• However, it is invoked in many locations in the code. Where it occurs in direct functions writing out XHTML, I believe it could simply be eliminated.
• It is also passed to any child MdhRecord objects, where it is assigned to MdhRecord::parentSuffix. Inside the MdhRecord class this is frequently invoked. Generally it is chained together with MdhRecord::localSuffix, a property which is used, and is essential, to ensure that individual forms and form elements have unique id attributes.
• The Adaptive Fields library adaptive_fields.php uses a property with the same name, but I don't believe there's any direct connection between the two.
• When MdhRecord uses a suffix, it's usually done by calling its getSuffix() method, which chains together parentSuffix and localSuffix. In current usage, since the parentSuffix is always set to an empty string, I believe only the localSuffix is ever written out.

Given this, I think this might be a suitably cautious approach to eliminating this complication:

• Start with MdhRecordList, and ensure that nothing can set the value of $parentSuffix to anything except an empty string. Test thoroughly.
• Next, move to MdhRecord, and ensure that nothing can set its $parentSuffix to anything but an empty string. Test again.
• If all is well, eliminate all uses of $parentSuffix from MdhRecordList and test again. This will leave the class making calls to MdhRecord methods where it supplies an empty string in place of its $parentSuffix.
• Identify those methods in MdhRecord, and refactor them so that they don't require that parameter; then remove it from all calls to the methods. Test very thoroughly.
• Remove all remaining code from MdhRecordList which references $parentSuffix, and test again.
• Remove all code referencing $parentSuffix from MdhRecord, and test again.
• Examine gui.js. This has functions which look for the parent suffix and use it (although I believe it's always an empty string) to build ids for form elements and querystring parameters. Remove all references to it, and test again.
• Look at get_rec.php and save_rec.php to see if mentions of suffixes and ids therecan now be simplified.
• Test test test test...

The AdaptiveDB code is full of cruft that originated in a previous attempt to do something similar to what the one-to-many field does now. That code has its fingers everywhere, so I've started the process of trying to clean it out. It's a bit messy because some of the principles (and variable names) used in that code were also used in the adaptive/custom fields, so I have to be really careful what I do. I've got rid of the originating MdhRelTableField class from db_fields.php, and some associated code blocks in other files, and I'm now working on some properties of MdhRecordList that were designed to make it embeddable in another record. The result of all this will be a cleaner, faster codebase which is easier to understand, but it's slow going. I want to get this done before I start on the larger task of making the adaptive fields displayable in the db table.

I also revised a bit of code in the MdhDateField class so that the null year "0000" is never displayed -- now, if there's no date, you see nothing.

Note to self on something I noticed while testing:

I need to revisit the logic that is used when handling edits to records retrieved from a search operation. In particular, if you add a record, but that record doesn't have fields matching the search results you previously retrieved, it doesn't display, of course. It's difficult to see how it could, but it's a bit confusing. Similarly, if you delete a single record which is the result of a search operation, the back-end retrieves a whole set of records as if you'd run an empty search; it should just remove the record from the results table, I think. This needs careful planning, though.

Continuing the process of learning how to turn my regular comments into proper Doxygen comments. I'm still working on the db_fields.php file, which is substantial and has multiple classes in it. I've also been trying to work out how to get PDF documentation out of Doxygen; it generates a Latex project with a Makefile, but when I run Make on the directory, it fails to find the command pdflatex. I can't find a package by that name, or any package which is supposed to contain it, so I'm installing as many core Latex-related packages as I can find. We shall see. Maybe nobody uses the PDF output any more, but it would be handy for e-readers and printed output.

Figuring it was time to start proper documentation of this codebase, I've started with the db_fields.php file, and worked out how to use @file and @defgroup to start documenting the constants at the head of the file. This will be a long job, but it's a good time to do it because it'll help me to prune out old code.

In the current system, when you do a search and retrieve a table of results, you can choose which fields you want to display in the results table. This is very useful for targetted searches, and largely precludes the need for retrieving individual records to examine their fields. However, it doesn't function for custom fields. You cannot specify that you want custom fields to appear in the results table; you can only examine them by retrieving each complete individual record.

This has been a failing for quite a while, and it's been ignorable up to this point, but we're now thinking about creating spreadsheet output from searches as well as the web-page tables. Custom fields must be includable in spreadsheet output, since they can't be retrieved later and merged into the spreadsheet.

So before I start the spreadsheet work, I need to add the capability to retrieve custom field data and display it as part of the search results table. This will not be simple, and there will be a considerable penalty in terms of time, so I need to figure out if there's an efficient way to do it based on SQL joins. The problem is that at the moment, I do the search and get back one row for each record; but there are arbitrary numbers of custom fields associated with any given record, so using a join will get me potentially multiple rows for each custom field data record associated with the document record. I've been re-reading all the custom field code, which is quite complicated, to try and get a handle on how best to do this.

It's going to take some hard thinking.

This involved rewriting a bunch of scripts, to avoid copying over credentials files and .svn directories, but it should all be automated in future. The live db is now using the latest revision.

Both JW and JS-R want output that can be loaded or pasted into a spreadsheet, so that's my next priority. This is how I envisage doing it:

• The current Search button will be accompanied by a second button, for retrieving a CSV file. Perhaps the caption of Search should be changed to something else too, so it's clear that they both search, but they retrieve different things.
• The second button should submit the same search, to the get_search_results.php file, but with a different reqType parameter ('csv' as opposed to 'ajax').
• get_search_results.php reads that parameter and sends the appropriate mime type header; then proceeds as normal, except that it ignores the firstRec and recsToDisplay parameters (CSV searches retrieve the entire set that matches the search), and instead of calling MdhRecordList::writeAsTable, it calls a writeAsCSV method (yet to be coded).

Before I start, I need to figure out all the rules and gotchas for CSV files which can have large text fields, as well as embedded quotes, and I need to determine how to write text in UTF-8 and ensure that spreadsheet programs can read UTF-8 CSV files.

I have a basic system working for hiding/showing rows of the search table that have actually been used. Its only current limitation is that adaptive field groups are still displayed, even if none of their rows are showing, but to hide/show them as well would be complicated and probably not worth the trouble since only one project uses them.

The code is now being managed in SVN (revision.tapor.uvic.ca).

EDIT: svn on the server is working now -- partly my fault, partly an Ubuntu bug with Gnome keyring. I've populated it, but I now need to move over my working copy from the local svn to the server one.

Because there are so many projects using AdaptiveDB code, in various versions, I've decided to manage the project with SVN. Initially, I asked for a repository on our SVN server (revision.tapor.uvic.ca, which is actually Lettuce), but for various reasons that isn't working properly yet; it's there, but I don't seem to have permission to check anything out or make directories. In the meantime, I set up subversion on the local machine and started working with that.

The primary requirement was to have the mysql credentials stored in a location outside all the code files. I've done that by moving them to a directory structure like this:

• Credentials
  • editor
    • creds.php
  • reader
    • creds.php

One or other of these files is require_onced at the head of any file that needs to make connections, ahead of the connect.php file, which I've now moved to /db/. The credentials files can be excluded from the repo so that they're not overwritten by a checkout.

I've tested the new structure with the adaptiveDBdev db, and it all seems to be working fine. I'll have to migrate the other projects over to the new structure carefully, as well as updating their own dev-to-live rsync scripts appropriately.