21/04/16

Permalink 09:55:10 am, by mholmes, 556 words, 123 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 //

10/11/15

Permalink 05:17:39 pm, by mholmes, 19 words, 123 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.

06/08/15

Permalink 01:15:01 pm, by mholmes, 61 words, 141 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.

05/08/15

Permalink 03:24:48 pm, by mholmes, 46 words, 147 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.

04/08/15

Permalink 04:49:56 pm, by mholmes, 165 words, 159 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.

11/05/15

Permalink 04:37:52 pm, by mholmes, 110 words, 263 views   English (CA)
Categories: Activity log; Mins. worked: 120

BUG TO FIX!

DEBUGGED AND DIAGNOSED 2015-08-04. A bug showed up in the Landscapes DB which I think is generic: when a record contains two different one-to-many fields which link to the same table at one remove, editing a record can result in the creation of more links than should be created. It looks as though for each table which is linked through the same set of foreign keys, a new entry is created, causing there to be links that did not previously exist, generated through the save operation. This must be fixed. I've worked around it by removing one of the links in the owners table in that db (to sellers).

18/08/14

Permalink 04:10:43 pm, by mholmes, 17 words, 355 views   English (CA)
Categories: Activity log; Mins. worked: 60

Working towards faster retrieval...

...but I have a bug I can't seem to figure out in the handling of results paging.

15/08/14

Permalink 03:40:44 pm, by mholmes, 105 words, 353 views   English (CA)
Categories: Activity log; Mins. worked: 300

Working on the spreadsheet functionality

Spreadsheet generation is painfully slow and often runs out of memory or time. This is understandable given the number of queries taking place for complex table relationships, but I've managed to improve it substantially. I'm also working on speeding up retrieval for regular display, and I have a function written but not tested yet (db_recordList.php populateFromDb2). Ultimately, though, a massive set of joins is probably going to have to be done in the initial single query, making it absolutely huge but having it run only once, and then the non-standard db fields (lookups, one-to-manys etc.) will have to figure out parsing those results.

14/08/14

Permalink 05:06:55 pm, by mholmes, 77 words, 351 views   English (CA)
Categories: Activity log; Mins. worked: 90

Added "search for nothing" functionality

This has been needed for a while, and now it's essential in VPN so I've added it: you can put this into a text field:

^$

and it will search for records which have nothing or just space in those fields.

That means the VPN folks can do that with the Images field in the Poems table and find all the records which don't have page-images; then an explanation can be added in their new Page-image notes field.

08/07/14

Permalink 09:13:00 pm, by mholmes, 43 words, 378 views   English (CA)
Categories: Activity log; Mins. worked: 60

Finished basic implementation of thumbnail feature

Needs a bit more documentation, and there may be a need to make the width configurable (it's set to 200px right now), but it's working for MdhLinkField and MdhLinkSetField.

More fun working on this sort of thing at home with no interruptions. :-)

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

Reports

XML Feeds