Implementing multi-statement trigger creation in Laravel migrations

Question

So I'm working with a large-scale Laravel project (5.7.26) on PHP (7.2.10) with a MariaDB (10.1.36-MariaDB ) database, and right now and I'm running into some issues with setting up my migrations. Currently I have laravel pointed at a second test db and have it successfully creating 4 tables necessary for my test--but, unfortunately, I also need to test some triggers used in filling one of the test tables--triggers that I've normally created through phpmyadmin (4.8.3) with no issues. Since the migration rollback removes the triggers when dropping tables (which is something I need for broader reasons), I'm looking to programmatically re-insert them using laravel's DB->uprepared() expressions in a separate migration file (one of the problematic triggers is reproduced below):

CREATE TRIGGER `Update TitleDescFormatCoverage` AFTER UPDATE ON `omeka_element_texts`
 FOR EACH ROW IF (NEW.element_id = 50) THEN
    UPDATE mi_items as m
    SET m.title = IF(m.title=OLD.text,IF(NEW.text='',(
        SELECT text FROM omeka_element_texts WHERE text != '' ORDER BY id DESC LIMIT 1
    ),NEW.text),m.title)
    WHERE m.id = NEW.record_id;
ELSEIF (NEW.element_id = 42) THEN
    UPDATE mi_items as m
    SET m.format = IF(m.format=OLD.text,IF(NEW.text='',(
        SELECT text FROM omeka_element_texts WHERE text != '' ORDER BY id DESC LIMIT 1
    ),NEW.text),m.format)
    WHERE m.id = NEW.record_id;
ELSEIF (NEW.element_id = 41) THEN
    UPDATE mi_items as m
    SET m.description = IF(m.description=OLD.text,IF(NEW.text='',(
        SELECT text FROM omeka_element_texts WHERE text != '' ORDER BY id DESC LIMIT 1
    ),NEW.text),m.description)
    WHERE m.id = NEW.record_id;
ELSEIF (NEW.element_id = 38) THEN
    UPDATE mi_items as m
    SET m.coverage = IF(m.coverage=OLD.text,IF(NEW.text='',(
        SELECT text FROM omeka_element_texts WHERE text != '' ORDER BY id DESC LIMIT 1
    ),NEW.text),m.coverage)
    WHERE m.id = NEW.record_id;
END IF

The above trigger causes a predictable error during migration (due to hitting the semicolon statement delimiter, I presume). I can adjust the SQL and run the query successfully through phpmyadmin by adding a DELIMITER && to the top and && to the end of the SQL, but this is invalid outside of a mySQL client.

To address this, I rebuilt the above SQL as follows:

        DB::connection('test')->unprepared("
            CREATE TRIGGER `Update Title` AFTER UPDATE ON `omeka_element_texts` FOR EACH ROW
                UPDATE mi_items as m
                SET m.title = IF(NEW.element_id = 50 && m.title=OLD.text,IF(NEW.text='',(
                    SELECT text FROM omeka_element_texts WHERE text != '' ORDER BY id DESC LIMIT 1
                ),NEW.text),m.title)
                WHERE m.id = NEW.record_id;
        ");
        DB::connection('test')->unprepared("
        CREATE TRIGGER `Update Format` AFTER UPDATE ON `omeka_element_texts` FOR EACH ROW
            UPDATE mi_items as m
            SET m.format = IF(NEW.element_id = 42 && m.format=OLD.text,IF(NEW.text='',(
                SELECT text FROM omeka_element_texts WHERE text != '' ORDER BY id DESC LIMIT 1
            ),NEW.text),m.format)
            WHERE m.id = NEW.record_id;
        ");
        DB::connection('test')->unprepared("
        CREATE TRIGGER `Update Description` AFTER UPDATE ON `omeka_element_texts` FOR EACH ROW
            UPDATE mi_items as m
            SET m.description = IF(NEW.element_id = 41 && m.description=OLD.text,IF(NEW.text='',(
                SELECT text FROM omeka_element_texts WHERE text != '' ORDER BY id DESC LIMIT 1
            ),NEW.text),m.description)
            WHERE m.id = NEW.record_id;
        ");
        DB::connection('test')->unprepared("
        CREATE TRIGGER `Update Coverage` AFTER UPDATE ON `omeka_element_texts` FOR EACH ROW
            UPDATE mi_items as m
            SET m.coverage = IF(NEW.element_id = 50 && m.coverage=OLD.text,IF(NEW.text='',(
                SELECT text FROM omeka_element_texts WHERE text != '' ORDER BY id DESC LIMIT 1
            ),NEW.text),m.coverage)
            WHERE m.id = NEW.record_id;
        ");

HOWEVER, this also generates an error, as apparently MariaDB does not support multiple triggers on the same action (BEFORE/AFTER, CREATE/UPDATE/DELETE). Unfortunately, due to the application's needs I'll need to effectively run 4 statements (represented above) for some of the triggers. So, this brings us to my question:

QUESTION: is there a way to use Laravel/Eloquent/some additional compatible library (with my existing DB) to programmatically build a multi-statement trigger on a single action (AFTER UPDATE, for example)? My next idea was to go up to some sort of scripting and try and run a laravel console command for the migration with some sort of scripted direct call into mySQL, but exploring that is my last resort atm. Does anybody know of a better way to do this?

EDIT: I don't think eloquent model events will work, as whatever mechanism I use here needs to work on a production site/db where the tables in question will be written to / modified by a separate application (Omeka) that doesn't use Eloquent.

Sound like you are asking for [model events](https://laravel.com/docs/5.0/eloquent#model-events) ? -> *"Eloquent models fire several events, allowing you to hook into various points in the model's lifecycle using the following methods: creating, created, updating, updated, saving, saved, deleting, deleted, restoring, restored."* — Raymond Nijland, Oct 02 '19 at 18:14
.. or [Model Observers](https://laravel.com/docs/5.0/eloquent#model-observers) -> *"To consolidate the handling of model events, you may register a model observer. An observer class may have methods that correspond to the various model events. For example, creating, updating, saving methods may be on an observer,"* — Raymond Nijland, Oct 02 '19 at 18:17
... but i think you could rather use a VIEW and query the view from laravel instead to generate the correct data for some reason this feels like a iffy approach.. — Raymond Nijland, Oct 02 '19 at 18:19
I don't think either of those will work, as whatever mechanism I use here needs to also be used on the production site/db, where a separate application (omeka) is interacting with the tables in question without using Eloquent. Can Eloquent events be used to monitor db operations performed by some outside application? — Kirk Lundblade, Oct 02 '19 at 18:45
You mean you have a API which omeka uses without Eloquent, if that is the case it would use a VIEW for sure when fetching the data when needed..I would not use triggers to update the main reason the more id's omeka will send to update the longer the update and more risk happen on deadlocks because of the way those triggers work.. ... — Raymond Nijland, Oct 02 '19 at 18:51
This may be a newb question, but what do you mean by VIEW, and how is that going to mirror the functionality of the triggers? — Kirk Lundblade, Oct 02 '19 at 19:07

Implementing multi-statement trigger creation in Laravel migrations

0 Answers0