Moving Logic Into the Database

Monday Jan 13th 2003 by DatabaseJournal.com Staff
Share:

Do you find yourself demanding more precise control over your data? Join Tim Perdue as he describes how to strip programming logic from the PHP code and move it into the database itself.

by Tim Perdue

As my programming skills have improved over the years, I find myself demanding more precise and guaranteed control over my data. Simply inserting into the database and hoping for the best is not good enough anymore. Using transactions and foreign keys are good first steps in ensuring data integrity, but with some of the more advanced databases, you can go further still and move programming logic into the database itself, stripping it from your PHP code.

I recently picked up development of GForge, an open source software development/project management tool. Part of what I wanted to do was create a first-class "Project Manager", similar to MS Project or similar tools which do Gantt charting, and I wanted to do it with PHP using a web interface.

In Gantt charting, you have a series of tasks, which can be constrained by prior tasks, as in the example below. Task B is dependent on the completion of Task A, and Task C is dependent on Task B.


click for larger image

That sounds easy enough, but what if a user attempts to enter a start date on Task B that is earlier than the end date of Task A? At the time you insert Task B, or update its entry in the database, you could have PHP logic that queries the database and checks the end date of task A. That's a couple lines of PHP. But what if you need to delay Task A by a week? Now you need a recursive function in PHP which will go all the way down the line and delay Task B and then Task C by the proper amount and commit all the changes to the db. Suddenly, you are looking at a lot of queries and recursion in PHP and you've got a performance problem, especially if you have a high-traffic web application.

by Tim Perdue

Triggers and PL/pgsql

The alternative approach is to move these constraints into triggers in the database. I have messed with triggers that update counters and create rows in related tables, but this is the most I have done with PL/pgsql, the PostgreSQL programming language of choice.

There are two kinds of triggers in pl/pgsql programming, the "before" trigger and the after trigger. You use the "before" triggers if you want to modify a row of data before it is inserted into the database. That sounds like how we want to handle our start/end dates for tasks. If a user attempts to insert Task B with a start date earlier than the end date of Task A, we need to override him and push his dates back.

The other kind of trigger is the "after" trigger, which you use if you want to perform actions after your row is inserted into the database. That's how we will push back Tasks B and C if Task A is delayed.

What's really neat is that we are essentially getting recursion for "free". If Task A is updated, the trigger fires and updates Task B. The trigger for Task B is set off as well, which causes Task C to update, and so on down the line until everything is validated.

We can start by looking at the "before" trigger, which validates the start/end dates.



CREATE OR REPLACE FUNCTION projtask_insert_depend () RETURNS OPAQUE AS '
DECLARE
        dependon RECORD;
        delta INTEGER;
BEGIN
        --
        --  ENFORCE START/END DATE logic
        --
        IF NEW.start_date >= NEW.end_date THEN
                RAISE EXCEPTION ''START DATE CANNOT BE AFTER END DATE'';
        END IF;
        --
        --        First make sure we start on or after end_date of tasks
        --        that we depend on
        --
        FOR dependon IN SELECT * FROM project_dependon_vw
                                WHERE project_task_id=NEW.project_task_id LOOP
                --
                --        See if the task we are dependon on
                --        ends after we are supposed to start
                --
                IF dependon.end_date > NEW.start_date THEN
                        delta := dependon.end_date-NEW.start_date;
                        RAISE NOTICE ''Bumping Back: % Delta: % '',
                            NEW.project_task_id,delta;
                        NEW.start_date := NEW.start_date+delta;
                        NEW.end_date := NEW.end_date+delta;
                END IF;

        END LOOP;
        RETURN NEW;
END;
' LANGUAGE 'plpgsql';

As you can see, we have logic to raise an exception if a (dimwitted) user tries to make his start date after his end date.

We also have a FOR ... IN loop which queries the tasks we are dependent on, and adjusts our start/end dates if we attempt to start before our predecessor is completed.

NEW is a reference to the row that this trigger is acting on. If this were on an UDPATE event, we would also have access to the OLD row as well. As you can see, we can reference all the fields in NEW, such as NEW.start_date, and reference all of the rows we are querying as "dependon".

Those few lines of very simple code will now guarantee that our data is always correct. If we had put this logic into PHP, it would be possible for someone to issue commands on the psql command line that could mess up our system.

by Tim Perdue



Now for the trigger which will activate this function:




CREATE TRIGGER projtask_insert_depend_trig 
        BEFORE INSERT OR UPDATE ON project_task
        FOR EACH ROW EXECUTE PROCEDURE projtask_insert_depend();

Now for the more confusing process. When a task is updated, we need to look at the tasks that are dependent on us. If Task B has a start date that is the same as our end date, that means Task B was constrained by us, and we need to push him back if we are delayed, or bring him forward if we move our end date up.

But what if Task B is also dependent on another task, say Task D? If we try to bring Task B forward too much, we might be in violation of that constraint -- we might be trying to start Task B before Task D is completed.

No problem! Our "before" trigger protects us, no matter what. If we try to move Task B forward too much, Task B's triggers are going to fire and push him back again! We're getting all of this for free because we have put a few lines of code inside of the database itself. Every row becomes its own boss. Trying to perform this maneuver inside of PHP would be mind-bending to say the least.



CREATE OR REPLACE FUNCTION projtask_update_depend () RETURNS OPAQUE AS '
DECLARE
    dependent RECORD;
    dependon RECORD;
    delta   INTEGER;
BEGIN
    --
    --  See if tasks that are dependent on us are OK
    --  See if the end date has changed
    --
    IF NEW.end_date > OLD.end_date THEN
        --
        --  If the end date pushed back, push back dependent tasks
        --
        FOR dependent IN SELECT * FROM project_depend_vw
            WHERE is_dependent_on_task_id=NEW.project_task_id LOOP
            --
            --  Some dependent tasks may not start immediately
            --
            IF dependent.start_date > OLD.end_date THEN
                IF dependent.start_date < NEW.end_date THEN
                    delta := NEW.end_date-dependent.start_date;
                    UPDATE project_task
                        SET start_date=start_date+delta,
                        end_date=end_date+delta
                        WHERE project_task_id=dependent.project_task_id;
                END IF;
            ELSE
                IF dependent.start_date = OLD.end_date THEN
                    delta := NEW.end_date-OLD.end_date;
                    UPDATE project_task
                        SET start_date=start_date+delta,
                        end_date=end_date+delta
                        WHERE project_task_id=dependent.project_task_id;
                END IF;
            END IF;
        END LOOP;
    ELSIF NEW.end_date < OLD.end_date THEN
            --
            --  If the end date moved up, move up dependent tasks
            --
            FOR dependent IN SELECT * FROM project_depend_vw 
                WHERE is_dependent_on_task_id=NEW.project_task_id LOOP
                IF dependent.start_date = OLD.end_date THEN
                    --
                    --  dependent task was constrained by us - bring it forward
                    --
                    delta := OLD.end_date-NEW.end_date;
                    UPDATE project_task
                        SET start_date=start_date-delta,
                        end_date=end_date-delta
                        WHERE project_task_id=dependent.project_task_id;
                END IF;
            END LOOP;
    END IF;
--
--  MAY WISH TO INSERT AUDIT TRAIL HERE FOR CHANGED begin/end DATES
--
    RETURN NEW;
END;
' LANGUAGE 'plpgsql';

Now for the trigger that will activate the function above. This trigger only needs to fire on "updates" to the database, since no other tasks could be dependent on a brand new task when it is first inserted.

CREATE TRIGGER projtask_update_depend_trig 
    AFTER UPDATE ON project_task 
    FOR EACH ROW EXECUTE PROCEDURE projtask_update_depend();
by Tim Perdue

Now there is one serious pitfall you can encounter here. What if a mischievous end-user tries to make Task A dependent on Task C, creating a circular dependency? With all this logic in the database, I believe you could create an infinite loop of triggers firing and fighting back and forth inside of your database. I didn't try to prevent this with PL/pgsql logic (if anyone can figure out how to do that, let me know), but instead created a few lines of recursive PHP code to eliminate any chance of circular dependencies.


click here for code sample

There, now we have guaranteed data integrity, and we got a ton of functionality for free while we were at it. Our database rows now manage themselves - pushing themselves back and forth as necessary to maintain the constraints we have designated. Now, I'll grant you that most PL/pgsql projects will be far easier than this, especially if you are just getting started, but once you get your toes wet in true database programming, you are only going to want more.

Happy coding,

Tim Perdue

References:
Project Manager Demo
PostgreSQL PL/pgsql Docs

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved