Adaptive Database

More updates, new features

Martin — Thu, 27 Sep 2018 23:03:29 +0000

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).

Updates for new webservers

Martin — Wed, 19 Sep 2018 23:50:38 +0000

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.

Original post blogged on b2evolution.

Fixes for record deletion bug, and optimization

Martin — Mon, 09 Jul 2018 21:48:16 +0000

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.

Original post blogged on b2evolution.

Weird behaviour for SA's site

Martin — Wed, 22 Nov 2017 00:51:45 +0000

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.

Original post blogged on b2evolution.

Moving to mysqli

Martin — Wed, 23 Aug 2017 17:08:31 +0000

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.

Original post blogged on b2evolution.

How to get BEFORE INSERT triggers to use the AUTO_INCREMENT value

Martin — Thu, 21 Apr 2016 16:55:10 +0000

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`;

[Set the delimiter to // in the phpMyAdmin interface before running this:] CREATE TRIGGER `landscapes_mapridgedev`.`own_desc_insert` BEFORE INSERT ON `landscapes_mapridgedev`.`owners` FOR EACH ROW BEGIN DECLARE next_id INT; SET next_id = (SELECT `AUTO_INCREMENT` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`=DATABASE() AND `TABLE_NAME`='owners'); SET NEW.`own_desc` = CASE WHEN (LENGTH(NEW.`own_surname`) > 0 AND LENGTH(NEW.`own_forenames`) > 0 AND LENGTH(NEW.`own_street_num`) > 0 AND LENGTH(NEW.`own_street`) > 0) THEN concat(NEW.`own_surname`,_utf8', ',NEW.`own_forenames`,_utf8' : ',NEW.`own_street_num`,_utf8', ',NEW.`own_street`,_utf8' (',next_id,_utf8')') WHEN (LENGTH(NEW.`own_surname`) > 0 AND LENGTH(NEW.`own_forenames`) > 0 AND LENGTH(NEW.`own_street`) > 0) THEN concat(NEW.`own_surname`,_utf8', ',NEW.`own_forenames`,_utf8' : ',NEW.`own_street`,_utf8' (',@next_id,_utf8')') WHEN (LENGTH(NEW.`own_surname`) > 0 AND LENGTH(NEW.`own_forenames`) > 0) THEN concat(NEW.`own_surname`,_utf8', ',NEW.`own_forenames`,_utf8' : (no address)',_utf8' (',@next_id,_utf8')') WHEN (LENGTH(NEW.`own_surname`) > 0) THEN concat(NEW.`own_surname`,_utf8' (no forenames or address)',_utf8' (',@next_id,_utf8')') WHEN (LENGTH(NEW.`own_institution_name`) > 0 AND LENGTH(NEW.`own_street_num`) > 0 AND LENGTH(NEW.`own_street`) > 0) THEN concat(NEW.`own_institution_name`,_utf8' : ',NEW.`own_street_num`,_utf8', ',NEW.`own_street`,_utf8' (',@next_id,_utf8')') WHEN (LENGTH(NEW.`own_institution_name`) > 0 AND LENGTH(NEW.`own_street`) > 0) THEN concat(NEW.`own_institution_name`,_utf8' : ',NEW.`own_street`,_utf8' (',@next_id,_utf8')') WHEN (LENGTH(NEW.`own_institution_name`) > 0) THEN concat(NEW.`own_institution_name`,_utf8' : (no address)',_utf8' (',@next_id,_utf8')') WHEN (LENGTH(NEW.`own_street_num`) > 0 AND LENGTH(NEW.`own_street`) > 0) THEN concat(_utf8' : (no name): ',NEW.`own_street_num`,_utf8', ',NEW.`own_street`,_utf8' (',@next_id,_utf8')') WHEN (LENGTH(NEW.`own_street_num`) > 0 AND LENGTH(NEW.`own_street`) > 0) THEN concat(_utf8' : (no name or street number): ',NEW.`own_street`,_utf8' (',@nextid,_utf8')') ELSE concat(_utf8'(no name or address)',_utf8' (',@nextid,_utf8')') END; END //

Original post blogged on b2evolution.

Updated documentation

Martin — Wed, 11 Nov 2015 01:17:39 +0000

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

Original post blogged on b2evolution.

Read-only interface tested and working

Martin — Thu, 06 Aug 2015 20:15:01 +0000

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.

Original post blogged on b2evolution.