UpdateDatabase/MysqlLinks.php

[Pman.Core] / UpdateDatabase / MysqlLinks.php

diff --git a/UpdateDatabase/MysqlLinks.php b/UpdateDatabase/MysqlLinks.php
index 5272e19..990117a 100644 (file)
--- a/UpdateDatabase/MysqlLinks.php
+++ b/UpdateDatabase/MysqlLinks.php
@@ -19,9 +19,9 @@
  *   initial code will auto generate triggers
  *   -- how to add User defined modifications to triggers?
  *   -- we can CALL a stored procedure..?
- *   -- {tablename}_trigger_before_delete_{column_name}(NEW.column)
- *   -- {tablename}_trigger_before_update_{column_name}(OLD.column, NEW.column}
- *   -- {tablename}_trigger_before_insert_{column_name}(OLD.column}
+ *   -- {tablename}_trigger_{optional_string}_before_delete_{column_name}(NEW.column)
+ *   -- {tablename}_trigger_{optional_string}_before_update_{column_name}(OLD.column, NEW.column}
+ *   -- {tablename}_trigger_{optional_string}_before_insert_{column_name}(OLD.column}
  *
  *  
  *
@@ -86,7 +86,7 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
          
         
         if (!isset($this->schema[$tbl])) {
-            echo "Skip $tbl\n";
+            echo "Skip $tbl = table does not exist in schema\n";
             return;
         }
         
@@ -142,6 +142,7 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
         
         // create a list of source/targets from $this->links
         
+                
         $revmap = array();
         foreach($this->links as $tbl => $map) {
             if (!isset($this->schema[$tbl])) {
@@ -159,11 +160,20 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
         }
         
         
+        
+        
         foreach($revmap as $target_table => $sources) {
             
             
             // throw example.. UPDATE `Error: invalid_id_test` SET x=1;
             
+            if (!isset($this->schema[$target_table])) {
+                echo "Skip $target_table  = table does not exist in schema\n";
+                continue;
+            }
+        
+            
+            
             $q = DB_DataObject::factory('core_enum');
             $q->query("
                 DROP TRIGGER IF EXISTS `{$target_table}_before_delete` ;
@@ -185,14 +195,22 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
                 $err = substr("Failed Delete {$target_table} refs {$source_table}:{$source_col}", 0, 64);
                 $trigger .="
                     SET mid = 0;
-                    SELECT count(*) into mid FROM {$source_table} WHERE {$source_col} = OLD.{$target_col};
-                    IF mid > 0 THEN
-                       
-                       UPDATE `$err` SET x = 1;
-                       
+                    IF OLD.{$target_col} > 0 THEN 
+                        SELECT count(*) into mid FROM {$source_table} WHERE {$source_col} = OLD.{$target_col} LIMIT 1;
+                        IF mid > 0 THEN   
+                           UPDATE `$err` SET x = 1;
+                        END IF;
                     END IF;
                 ";
             }
+            
+            $ar = $this->listTriggerFunctions($tbl, 'delete');
+            foreach($ar as $fn=>$col) {
+                $trigger .= "
+                    CALL $fn( OLD.{$col});
+                ";
+            }
+            
             $trigger .= "
             END 
            
@@ -201,7 +219,7 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
             //DB_DAtaObject::debugLevel(1);
             $q = DB_DataObject::factory('core_enum');
             $q->query($trigger);
-             
+             echo "CREATED TRIGGER {$target_table}_before_delete\n";
         }
         
         
@@ -242,18 +260,35 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
                 
                 $source_tbl = $tbl;
                 list($target_table , $target_col) = explode(':', $target);
+                
+                if (!isset($this->schema[$target_table])) {
+                    // skip... target table does not exist
+                    continue;
+                }
+                
+                
                 $err = substr("Fail: INSERT referenced {$tbl}:{$source_col}", 0, 64);
                 $trigger .="
                     SET mid = 0;
                     if NEW.{$source_col} > 0 THEN
-                        SELECT {$target_col} into mid FROM {$target_table} WHERE {$target_col} = NEW.{$source_col};
+                        SELECT {$target_col} into mid FROM {$target_table} WHERE {$target_col} = NEW.{$source_col} LIMIT 1;
                         IF mid < 1 THEN
                             UPDATE `$err` SET x = 1;
                         END IF;
                        
                     END IF;
                 ";
+                
+                
+                
+            }
+            $ar = $this->listTriggerFunctions($tbl, 'insert');
+            foreach($ar as $fn=>$col) {
+                $trigger .= "
+                    CALL $fn( NEW.{$col});
+                ";
             }
+            
             $trigger .= "
             END 
            
@@ -262,7 +297,7 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
             //DB_DAtaObject::debugLevel(1);
             $q = DB_DataObject::factory('core_enum');
             $q->query($trigger);
-             
+            echo "CREATED TRIGGER {$tbl}_before_insert\n";
             
             
             
@@ -306,11 +341,17 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
                 
                 $source_tbl = $tbl;
                 list($target_table , $target_col) = explode(':', $target);
+                
+                if (!isset($this->schema[$target_table])) {
+                    // skip... target table does not exist
+                    continue;
+                }
+                
                 $err = substr("Fail: UPDATE referenced {$tbl}:$source_col", 0, 64);
                 $trigger .="
                     SET mid = 0;
                     if NEW.{$source_col} > 0 THEN
-                        SELECT {$target_col} into mid FROM {$target_table} WHERE {$target_col} = NEW.{$source_col};
+                        SELECT {$target_col} into mid FROM {$target_table} WHERE {$target_col} = NEW.{$source_col} LIMIT 1;
                         IF mid < 1 THEN
                             UPDATE `$err` SET x = 1;
                         END IF;
@@ -318,6 +359,13 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
                     END IF;
                 ";
             }
+            $ar = $this->listTriggerFunctions($tbl, 'update');
+            foreach($ar as $fn=>$col) {
+                $trigger .= "
+                    CALL $fn(OLD.{$col}, NEW.{$col});
+                ";
+            }
+            
             $trigger .= "
             END 
            
@@ -326,7 +374,7 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
             //DB_DAtaObject::debugLevel(1);
             $q = DB_DataObject::factory('core_enum');
             $q->query($trigger);
-             
+            echo "CREATED TRIGGER {$tbl}_before_update\n";
             
             
             
@@ -339,7 +387,51 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
         
         
     }
+    /**
+     * check the information schema for any methods that match the trigger criteria.
+     *   -- {tablename}_trigger_{optional_string}_before_delete_{column_name}(NEW.column)
+     *   -- {tablename}_trigger_{optional_string}_before_update_{column_name}(OLD.column, NEW.column}
+     *   -- {tablename}_trigger_{optional_string}_before_insert_{column_name}(OLD.column}
+     *
+     *
+     */
+    // type = update/insert/delete
     
+    function listTriggerFunctions($table, $type)
+    {
+        static $cache = array();
+        if (!isset($cache[$table])) {
+            $cache[$table] = array();
+            $q = DB_DAtaObject::factory('core_enum');
+            $q->query("SELECT
+                            SPECIFIC_NAME
+                        FROM
+                            information_schema.ROUTINES
+                        WHERE
+                            ROUTINE_SCHEMA = '{$q->escape($q->database())}'
+                            AND
+                            ROUTINE_NAME LIKE '" . $q->escape("{$table}_trigger_")  . "%'
+                            AND
+                            ROUTINE_TYPE = 'PROCEDURE'
+                            
+            ");
+            while ($q->fetch()) {
+                $cache[$table][] = $q->SPECIFIC_NAME;
+            }
+            
+        }
+        // now see which of the procedures match the specification..
+        $ret = array();
+        foreach($cache[$table] as $cname) {
+            $bits = explode("_before_{$type}_", $cname);
+            if (count($bits) < 2) {
+                continue;
+            }
+            $ret[$cname] = $bits[1];
+        }
+        return $ret;
+    }
+        
     
 }