Adaptive Database

The following work completed today:

• All output is now valid HTML5.
• A new feature has been added to integer and string fields enabling them to be auto-linked to a target using a regular expression. This is needed for the Landscapes dbs and will be needed for DVPP.
• Support for user.css and user.js files has been added, so that if they exist, they will be included in the page. This enables easier configuration/customization without editing core files.
• All select statements now include SQL_NO_CACHE in an attempt to get around server caching (which doesn't actually seem to have worked very well in our case, but is still worth doing).

Continuing the work started earlier this year, which is becoming more urgent, I've tested the Potluck test db under PHP7 and confirmed it's all working as expected. So I've now started to port the changes forward to the existing dev and live databases, which were originally forked at different points in the development of the codebase. So far I've updated Maple Ridge Dev, Maple Ridge Live, VPN Dev and VPN Live, and all seems to be well. The last hurdle is the Vancouver properties db, which is a bit older and will need some care. But the problem seems to be solvable. With luck, the first transition to PHP 5.6 will just work, and then we can setEnv UVPHP_VERSION 70 in the .htaccess file and get some speed benefits from PHP 7. In the process of merging changes, I also brought a couple of good ideas into the main codebase from the VPN version.

Record deletion was broken on the VPN db, and it turned out it was because it was timing out because the project_settings.php file was not being included in del_rec.php, so the time required to do the deletion was running over the default time for PHP requests. However, having fixed that, I decided that the way del_rec.php was behaving made no sense anyway; after deleting a record, it made a whole fresh request to the db for its first set of records and replaced the existing table with that. Instead, it now returns a simple OK, and the JS code instead calls a function which deletes the two table rows associated with the deleted record, leaving the rest of the stuff intact. That's much quicker and more intuitive.

A similar process was required for multi-record editing. At first I thought this was going to be really complicated, but it turned out that since all the search filter data is being sent along with the editing data as part of the query, it's sufficient just to include the regular get_search_results.php file at the required point, causing the current search query to be re-run and the modified results returned. Looks like it works perfectly; we'll see if there are any reports of errors.

Spent an hour trying to debug a situation in which SA's new site with adaptive db code from the repo would not work. No further forward, really, but I must now integrate my mqsqli changes into the repo, pulling them from the potluck test db code.

The upcoming deployment of PHP7, with expected performance gains, provides an impetus to move this codebase forward, and the first problem is a straight incompatibility in connecting to MySQL; my code uses the old mysql_* functions, while PHP7 has removed these and requires the use of the somewhat equivalent mysqli_* functions. Today I worked through the Potluck application to make all the necessary changes for testing purposes. It's not a straight search-and-replace; some equivalently-named functions seem to have their parameters reversed. It's working (although not solidly tested); I haven't migrated the changes to svn yet because I need to do more testing to make sure the results work both on PHP5 and PHP7.

This is something I've struggled with for ages, and I've finally got a working solution, so I'm documenting it here with a real example, laid out the way you'd need to implement it through phpMyAdmin, since that's the most common interface we use. The problem arises when you want to auto-populate a field which will serve as a single-line descriptor for the record, suitable for use in drop-down lists when editing linked tables. One obvious requirement is to provide the primary key for the record. In a BEFORE UPDATE trigger this is easy, because you can just access NEW.`fieldName`, but in a BEFORE INSERT trigger you can't, because it hasn't been set yet. You can't use an AFTER UPDATE trigger because then you don't have access to the NEW record data. The solution is to read the AUTO_INCREMENT value for the table from the information_schema database. Here's an example:

[Run this first]

DROP TRIGGER IF EXISTS `landscapes_mapridgedev`.`own_desc_insert`;

GN pointed out that the documentation for a key param in the MdhOneToManyField was wrong. Fixed it, regenerated docs.

Did a little work last night and some more this morning to get this running and tested. I had to do some file re-organization to handle issues around relative paths with nested includes, but everything seems to be fine on the test database. My next task is to roll this out to the dev database, assuming I have permission for that.

Started work on a read-only interface (with an infrastructure for other permission levels if required) for JSR's upcoming class. This has involved some reorganization of file locations as well as the actual implementation. I think I'm about half-way through, and it looks like it'll work fine.

I started work on the bug reported in the previous post, by reproducing the structure required in the Potluck test db and trying to confirm what was happening. I couldn't reproduce it at all. So I went back to the original Landscapes local_classes.php file and searched for anything that might be anomalous. What I found was that the two fields in the owner table that linked to the titles as owner and seller had the same field name. That was obviously wrong, and was a good candidate for causing all the issues we'd seen. I fixed the issue in the dev db and tested as thoroughly as I could, without being able to trigger any errors, so I've now asked the field team to test the dev db, and if they can't break anything, we'll port the fix to the live db (and obviously test again there).

If this is fixed, it will free me to implement read-only access as requested by JSR.