Moving Logic Into the Database

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