Adaptive Database

By trial and error, I've learned that you need to install the following packages to get Doxygen to produce Latex PDF output:

• TeX Live: Basic LaTeX packages (texlive-latex-base)
• TeX Live: TeX and Outline font utilities (texlive-font-utils)
• TeX Live: LaTeX recommended packages (texlive-latex-recommended)
• TexLive: Recommended fonts (texlive-fonts-recommended)
• graphviz

Now, after running Doxygen, you can run make in the latex directory and it will build a PDF.

I'm in the process of adding serious documentation to the whole codebase, for use with Doxygen, and I've been working on the db_fields.php file for a while. Did another hour today. It's a grim slog, but necessary. There are 13 classes and 81 functions to document in this file alone...

A user on the MoM project reported an odd problem with Chrome, which is also the case with Safari: when adding a one-to-many <select> element to add a new one-to-many link in a field, the newly-added <select> element does not appear. It seems to be hidden by default. This seems to be an outright bug in webkit, but pretty obscure, so I figured out a workaround: if I add the <br/> element that follows the <select> and its <button> OUTSIDE their containing span rather than INSIDE it, the appropriate refresh/update seems to take place and the <select> and its delete button appear. Can't see any bad side-effects from this, so I've migrated the change to the live and dev sites, as well as the two Properties and Kings sites, and the Siberian site.

I've added back-end handling for an orderBy parameter submitted to the search and spreadsheet routines, that sort the results according to the specified field. However, this works only partially with the normal table view, because many of the fields contain ids rather than sortable strings, so it will behave a bit unexpectedly from the user's point of view, because the sort order based on the ids will not match that of the visible strings by which they're represented in the results table.

I've come to the conclusion that the current state of this database (and the probable state of any database in which the content expert has complete control over custom fields, and an interest in exploring various approaches to the data) is such that sophisticated searching with flexible sorting and spreadsheet output can only be achieved by building a VIEW of the core table, incorporating all the related and custom fields in the form of either text, integers or dates. The only integer fields would be actual number fields (such as ids, counts, totals etc.), while all lookup and one-to-many fields would be converted into text fields containing their looked-up and amalgamated content. Searching such a table would mean that all fields could be treated as equal, and searching/sorting would be much faster.

It seems to me that the MdhRecord object should be able to spit out two blocks of code automatically, in order to help set up this system:

• A block of SQL which can be used to create the VIEW in the first place.
• A new MdhRecord object which models the data as it appears in the VIEW.

The former will be quite complicated, but I think the latter should be relatively straightforward. There are two outstanding issues:

• Should the VIEW-generation code be written in such a way that once it's been created, it's able to stay current (meaning that if, for instance, the user deletes or adds a custom field, the VIEW would automatically accommodate this); or should it attempt only to model one stable condition of the data?
• Should the MdhRecord object be generated on-the-fly when required (which is slow, but would accommodate on-the-fly changes), or should it also be re-generated when required?

It's worth considering that this view and its associated search ought really to be the public face of the db (not exposing any editing capabilities), so it might be acceptable for it to be static and to require updating when the db structure changes.

I have the output for the spreadsheet working, and it works fine for regular tables, but when there are custom fields and the result set is large, the process takes so long it just ends up timing out.

I'm already disposing of each object once I've finished with it; this solves the out-of-memory errors I was initially seeing, and if I run top on Lettuce, I don't see any inordinate memory usage from my script. However, with a large record set result (my test has 307 hits, and is showing a set of four regular and six custom fields), the process inevitably times out; sometimes I get an error, and sometimes an incomplete file.

As always, the cost of supporting custom fields proves to be insanely large. I'm thinking that it might be possible to reduce the overall timing and memory requirements of the operation in these ways:

• Instead of storing all the results in a single variable and then sending them out in one go, I can echo them steadily out, which would reduce some of the memory load and keep the connection alive. This gives me an incomplete file when the request times out, but what I have up to that point is good.
• Instead of doing the full query to fill the initial dataset, I could do a simpler query to get back just the id numbers of the hits, and then do a separate MdhRecord operation to fill and output each record separately. Combined with the above, we'd then have a long sequence of separate operations rather than one big one.
• I could investigate the possibility that PHP has support for streaming, and use a stream class to output my data.

I've now created a file called get_search_as_spreadsheet.php, which currently just contains a mini-csv file. Used this to test that the mime-type is working as expected; the data is indeed recognized as a spreadsheet and opened by OpenOffice. Now we have to write the actual output for the spreadsheet, as a method of MdhRecordList.

My first difficult major objective is now achieved: custom fields, like other fields in the table, now have "Show" checkboxes next to them, and you can select them and have them show up in the table of search results just like regular fields.

This is quite a costly operation, because it means you have to retrieve all the custom field data from the database for each regular record that you're retrieving, but I've done my best to mitigate this by making sure it only happens if it's needed; you only retrieve the custom field data if you've elected to display at least one custom field, and if you aren't displaying any, even if you're using one or more as a search parameter, they still aren't retrieved. Nevertheless, I wish it would go a bit faster. It's difficult to know how that could be done, though, without hand-optimizing SQL union queries, which undermines the class-based structure of my PHP objects. Since custom fields are only used in JW's project, though, it's not too crucial.

Now I can move on to the requirement for spreadsheet output.

These are the steps, and my progress through them:

• DONE: Start with MdhRecordList, and ensure that nothing can set the value of $parentSuffix to anything except an empty string. Test thoroughly.
• DONE: Next, move to MdhRecord, and ensure that nothing can set its $parentSuffix to anything but an empty string. Test again.
• DONE: 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.
• DONE: 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.
• DONE: Remove all remaining code from MdhRecordList which references $parentSuffix, and test again.
• DONE: Remove all code referencing $parentSuffix from MdhRecord, and test again.
• DONE: 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.
• DONE: Look at get_rec.php and save_rec.php to see if mentions of suffixes and ids there can now be simplified.
• DONE: Test test test test...

I've finished the second of the test pages, which tests the functionality of the search page. In the process, I discovered that my code for hiding unused search fields during a search, to keep the page size under control, was not working properly, so I fixed that.

One issue with the testing is that it really works much more straightforwardly if items are sorted by id -- that makes it easier to (for instance) use JavaScript to delete the last added record. The AdaptiveDB items are not sorted by id, so for testing I've changed that setting. I have to remember to avoid migrating that change to the main db. However, this makes for a larger issue, I think: if the Search page becomes the de facto interface for most work, once the project is under way and there's significant data in the db, then we need to think more seriously about what happens when you have a search result set, but you then add a new record which doesn't match the search. I think it should probably be added to the end of the table anyway. I need to look at that.

But first, I need to follow my own instructions in the earlier post to eliminate the rest of the embedded record code from the codebase.

The first test page is now finished. It tests the following functions:

• Retrieve an initial set of five records.
• Retrieve a view of the first, then hide it.
• Retrieve an edit form for the second, modify all the select elements, and save it.
• Get a new blank record, fill in all the text areas, and then submit it.
• Delete the newly-added record.
• Offer to move on to the search test page.

I'm now working on the search test page.