Home arrow PHP arrow Page 3 - 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 (cont'd)
(Page 3 of 4 )

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


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.

<?php

  //
  //  recursively look at tasks you are dependent on
  //  and make sure no one is dependent on you anywhere
  //  up the chain
  //
  function checkCircular($depend_on_id, $original_id) {
    if ($depend_on_id == $original_id) {
      echo 'Circular Dependency Detected';
      return false;
    }

    $res=db_query("SELECT is_dependent_on_task_id AS id
      FROM project_dependencies
      WHERE project_task_id='$depend_on_id'");
    $rows=db_numrows($res);

    for ($i=0; $i<$rows; $i++) {
      // recurse
      if (!$this->checkCircular(db_result($res,$i,'id'), $original_id)) {
        return false;
      }
    }
    return true;
  }


  //
  //  Use this call when you go to insert your dependent tasks
  //
  if (!$this->checkCircular($add_arr[$i],$this->getID())) {
    return false;
  }

?>

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