Permalink 04:03:29 pm, by mholmes, 112 words, 191 views   English (CA)
Categories: Activity log; Mins. worked: 240

More updates, new features

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


Permalink 04:50:38 pm, by mholmes, 152 words, 32 views   English (CA)
Categories: Activity log; Mins. worked: 180

Updates for new webservers

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.


Permalink 02:48:16 pm, by mholmes, 219 words, 23 views   English (CA)
Categories: Activity log; Mins. worked: 150

Fixes for record deletion bug, and optimization

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.


Permalink 04:51:45 pm, by mholmes, 46 words, 104 views   English (CA)
Categories: Activity log; Mins. worked: 60

Weird behaviour for SA's site

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.


Permalink 10:08:31 am, by mholmes, 113 words, 127 views   English (CA)
Categories: Activity log; Mins. worked: 90

Moving to mysqli

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.


Permalink 09:55:10 am, by mholmes, 556 words, 271 views   English (CA)
Categories: Activity log; Mins. worked: 60

How to get BEFORE INSERT triggers to use the AUTO_INCREMENT value

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 //


Permalink 05:17:39 pm, by mholmes, 19 words, 270 views   English (CA)
Categories: Activity log; Mins. worked: 30

Updated documentation

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


Permalink 01:15:01 pm, by mholmes, 61 words, 273 views   English (CA)
Categories: Activity log; Mins. worked: 180

Read-only interface tested and working

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.


Permalink 03:24:48 pm, by mholmes, 46 words, 288 views   English (CA)
Categories: Activity log; Mins. worked: 360

Working on read-only GUI

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.


Permalink 04:49:56 pm, by mholmes, 165 words, 310 views   English (CA)
Categories: Activity log; Mins. worked: 180

Fixed bug in Landscapes db (I think, pending testing)

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.

:: Next Page >>

Adaptive Database

This project aims to design a flexible online database which can be set up as very quickly for a researcher requiring a relational database, and is easy to modify and extend. The Web GUI provides tools for data-entry in as simple and convenient a manner as possible, while also allowing data-integrity checks. The application development sandbox is currently at https://hcmc.uvic.ca/people/martin/potluck/editor/index.php; and half a dozen UVic projects are already using the codebase. The code is managed through SVN here: https://revision.hcmc.uvic.ca/svn/adaptivedb/.


XML Feeds