Adaptive Database

Got the redisplay of changed data working, so that when an existing record is edited, its row in the table is updated; while when a new record is added or a record is deleted, the whole display table is updated. Added a New button and a row for new item editing at the top of the table, and also hooked up the Delete button to some code to make it work. Everything now seems to be working fine, so the next stage is to do serious testing of large numbers of records to make sure the paging is correct. After that, it's time to work on the user-fields.

Did quite a lot of rewriting to enable the level of flexibility I need, given that you can simultaneously be editing multiple records from the same table, and from different tables, in our GUI. When data is saved back to the db, it's important to make sure that form element ids are unique, and that enough data goes back to the db so that the correct (stateless) display configuration can be updated or replaced. Edited records are now being saved to the db correctly, although the required data isn't yet being returned to the AJAX caller for insertion into the page. That's next, then we need a mechanism for entering a new item, and for deletion.

Implemented the AJAX functionality and wrote some more code to enable the interface to display tables of records, and retrieve individual records for view or edit, displaying them injected into the table at the appropriate point. Then took a second pass through my code and simplified it by abstracting a lot of it, so that both JS and PHP functions don't care what kind of record they're dealing with.

Next steps:

• Save edited records back to the db, and decide how to reload that table cell. Perhaps the MdhRecordList object could be passed a parameter enabling it to grab just a single record and return that record as a row, which could replace the existing display row in the table.
• Create and add new records (perhaps a popup editing cell which is modal -- masks the rest of the page).
• Delete records and redisplay the table.
• Extension of MdhRecord to handle the related field items (the interesting stuff...)

Writing the MdhRecordList class, which can be passed the classname of an MdhRecord object as a string, along with the db table name, and then instantiate and populate a list of the classes it's passed. It can then write them out as a table, with controls for navigation and for View / Edit / Delete. This is now basically working, and we're at the stage where I need to graft on the AJAX stuff so that it can retrieve individual records for editing.

Working out the implications of a multi-form page using a tabbed panel interface to edit items in different tables, it became clear that every field name in every table has to be unique. This caused me to rename most of the database fields to tag them with a table identifier at the beginning, and make it clear in the field names when a field is a lookup to a field in another table. The I wrote the code for inserting or updating a record in the db, and tested it; that's working OK, although it's currently just in testing mode through PHP; next I need to write the AJAX code.

Also spent a while trying to get form controls to lay out in a user-friendly manner; checkboxes in particular are very annoying in that they don't take up a full line, so you have to twiddle all sorts of little CSS things to make them line up next to their labels.

Before I go any further, I'll need to plan out the GUI in detail, so that I know what the relationship is between browsing records and editing them. In this db, it's not absolutely clear whether there needs to be separation between a browsing interface and an editing interface, but it will be essential to view tabular readouts from each of the key tables, and pick a record to edit, or add a new one. Perhaps each tab should by default have a tabular readout of a fixed number of records from the db, so that you could "browse", and each row should have the usual Edit and Delete buttons.

Worked on the date field items, adding validation/highlighting to date fields; I'll also have to handle year-only and year-mm dates on the receiving end because mySQL wants yyyy-00-00 for those types of situations, but we don't want the user to need to edit dates. Did more data checking in the form of enforcing maxlength parameters; this still needs to be done for textarea elements, because that requires javascript. Created a select-based element for true lookup fields, and tested it. Started building the descendant classes required for this specific project, beginning with a JwDocument object which can already load itself from the DB and display itself for editing.

Today's progress:

• Wrote a lookup string field class, which can get a set of previous distinct values from the same or another table, for combo box edit functionality.
• Wrote the JS and CSS to make the combo box work, and tested it.
• Created a user and wrote connection code to enable simple connectivity for queries.
• Extended the record class so it can populate itself from the db.

Next it needs to be able to populate itself from a POST request, and then save itself to the db.

Figured out what was screwing up the classes I wrote on Friday (use of the "var" keyword, which used to be normal as part of a class definition in PHP 4, but when I use it with 5 it kills the script). Did some basic testing and tweaking, and added a class for handling records.

Worked most of the day on a set of base classes for handling the various field types for the db, and writing them out as editable form objects (with a combo box option) etc. This is reprising work done for the Graves db years ago, but in a more OO form, with an abstract base class. Wrote a lot of code without testing any of it, and just got as far as finding out that nothing shows up when I run a quick test on the server; I'll have to do some error-trapping on Monday.

I've started work on a mock-up of the GUI, which is going quite well, and I've been mapping out the next few stages into discrete tasks which I know can be addressed in this order:

• Set up an account on the SAN for the storage of binaries. Make JW, EM and myself members of the group, all with full privileges.
• Add a "binaries" table to the db, and a linked table for storing links between binaries and documents.
• Elaborate the "Basic document data" panel to include Project and Binaries controls, such that projects are listed as checkboxes, and binaries can be added in any number. Include a "View/check binary location" option, which pops up a new window with the binary in it.
• Create an HTML version of a combo box. Many of the core document fields (such as Côte or Archive) will have a relatively stable set of values, but need to be insertable as plain text; it's not worth the machinery of managing a fixed value list, but it is worth having a control to assist in data-entry and regulation.
• Write a PHP function that can create the above combo box based on a single table and field name.
• Make a decision about the handling of dates (needs some discussion with JW). Dates are frequently vague, unspecified, or constitute ranges; do we need a precise way of dealing with this, or should we settle for open text? What are the implications and limitations of open text, and do they matter for the project?
• Write a PHP class to handle the creation of a GUI editor for a simple table consisting only of ints, booleans, short strings and mediumtext elements. This will be generally useful -- I wrote something like it back when I wrote the Graves db. Given the name of a table, it should be able to ask mySQL for info about all the field types, then construct a fieldset with editing controls, and populate it. It should also be able to decide whether to create a combo box for a specific short string field, based on the number of distinct values in the field. When passed a valid id, it should fill in the data for editing, and when not, it should create an empty form with the id set to -1.
• Add functionality to the class so that it can write an item out in non-editable forms too, as a panel of data, and as a table row.
• Make the class able to spit out the whole db table in tabular format.
• Revisit the exact structure of the basic document data, and see if we can finalize the fields before we start inserting data.
• Port the AJAX code from the CityStats project over and make sure it'll work well for this project.
• Pull together the work so far by hooking up the basic document data insertion/editing. Start by generating SQL from the spreadsheet to pre-populate the database with a block of documents, then edit document records to add things that can't be pulled from the database easily (document types, for instance).
• Write the front-end to manage Projects and Document Types; these should be fairly simple.
• Create some useful Views in the db, which pre-compile convenience tables we can query to find out whether any of a particular group of fields is used with a particular doctype, and to grab the values of all of a particular set of fields that pertain to a specific document id. We'll probably need tables of all the distinct fieldIds for group1, for instance, and
• Plan a set of PHP classes which can handle the Additional Fields functionality, based on the Views above.
• Built a kind of unit test infrastructure to help with the construction of these classes. This bit of the project is tricky, so we want to move in small steps and have good ways of making sure that each of these classes is actually working before moving on to the next.
• Make a detailed plan for the remaining stages of the project...