Home arrow PHP arrow Page 2 - Moving Logic Into the Database
PHP

Moving Logic Into the Database


In this article Tim shows us how moving logic from our code into our DB can make life much easier and improve data integrity, he demonstrates this on a Gantt chart (task list).

Author Info:
By: Tim Perdue
Rating: 5 stars5 stars5 stars5 stars5 stars / 2
January 16, 2003
TABLE OF CONTENTS:
  1. · Moving Logic Into the Database
  2. · Triggers and PostgreSQL
  3. · Triggers and PostgreSQL (cont'd)
  4. · Conclusion

print this article
SEARCH DEVARTICLES

Moving Logic Into the Database - Triggers and PostgreSQL
(Page 2 of 4 )

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.

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();

blog comments powered by Disqus
PHP ARTICLES

- Removing Singletons in PHP
- Singletons in PHP
- Implement Facebook Javascript SDK with PHP
- Making Usage Statistics in PHP
- Installing PHP under Windows: Further Config...
- File Version Management in PHP
- Statistical View of Data in a Clustered Bar ...
- Creating a Multi-File Upload Script in PHP
- Executing Microsoft SQL Server Stored Proced...
- Code 10x More Efficiently Using Data Access ...
- A Few Tips for Speeding Up PHP Code
- The Modular Web Page
- Quick E-Commerce with PHP and PayPal
- Regression Testing With JMeter
- Building an Iterator with PHP

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials