X-Git-Url: http://git.roojs.org/?a=blobdiff_plain;f=UpdateDatabase%2FMysqlLinks.php;h=02db5481d7a7dca3fb27cba0a97106717adace5a;hb=3dfa28ee12e72e27f69a7734bef807c2628e5d53;hp=91c2738518e4578d642f7c5d33a7f1f657eeddc8;hpb=e183bd94f32284294667fed86693f25bde5caf46;p=Pman.Core

diff --git a/UpdateDatabase/MysqlLinks.php b/UpdateDatabase/MysqlLinks.php
index 91c27385..02db5481 100644
--- a/UpdateDatabase/MysqlLinks.php
+++ b/UpdateDatabase/MysqlLinks.php
@@ -23,31 +23,46 @@
  *   -- {tablename}_trigger_{optional_string}_before_update_{column_name}(OLD.column, NEW.column}
  *   -- {tablename}_trigger_{optional_string}_before_insert_{column_name}(OLD.column}
  *
- *  
  *
+ * ------- Importing with triggers disabled.
+ *
+ *  SET @DISABLE_TRIGGER=1; (or anything you like except NULL) 
+ *  do imports
+ * SET @DISABLE_TRIGGER=NULL;
+ *
+ * ------ Call a method disabling a particular set of triggers
+ *  SET @DISABLE_TRIGGER_the_table_name=1; (or anything you like except NULL) 
+ *  do action
+ *  SET @DISABLE_TRIGGER_the_table_name=NULL;*
  */
 
 class Pman_Core_UpdateDatabase_MysqlLinks {
     
     var $dburl;
     var $schema;
-    var $links;
+    var $links = array();
     
     function __construct()
     {
           
         $this->loadIniFiles();
         $this->updateTableComments();
+       
         $ff = HTML_FlexyFramework::get();
-        if (!empty($ff->Pman['enable_trigger_tests'])) {
+        if (empty($ff->Pman['enable_trigger_tests'])) {
+            return;
+        }
+        if (!empty($ff->page->opts['disable-create-triggers'])) {
+            return;
+        }
             
             // note we may want to override some of these... - to do special triggers..
             // as you can only have one trigger per table for each action.
             
-            $this->createDeleteTriggers();
-            $this->createInsertTriggers();
-            $this->createUpdateTriggers();
-        }
+        $this->createDeleteTriggers();
+        $this->createInsertTriggers();
+        $this->createUpdateTriggers();
+        
         
         
     }
@@ -63,12 +78,43 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
         $dbini = 'ini_'. basename($this->dburl['path']);
         
         
-        $iniCache = $ff->DB_DataObject[$dbini];
+        $iniCache = isset( $ff->PDO_DataObject) ?  $ff->PDO_DataObject['schema_location'] : $ff->DB_DataObject[$dbini];
         
+        if (strpos($iniCache, DIRECTORY_SEPARATOR) !== false) {
+            echo "SKIP links code - cached ini file has not been created\n";
+            return;
+        }
         $this->schema = parse_ini_file($iniCache, true);
         $this->links = parse_ini_file(preg_replace('/\.ini$/', '.links.ini', $iniCache), true);
         
-
+        $lcfg = &$this->links;
+        $cfg = empty($ff->DB_DataObject) ? array() : $ff->DB_DataObject;
+        
+        if (!empty($cfg['table_alias'])) {
+            $ta = $cfg['table_alias'];
+            foreach($lcfg  as $k=>$v) {
+                $kk = $k;
+                if (isset($ta[$k])) {
+                    $kk = $ta[$k];
+                    if (!isset($lcfg[$kk])) {
+                        $lcfg[$kk] = array();
+                    }
+                }
+                foreach($v as $l => $t_c) {
+                    $bits = explode(':',$t_c);
+                    $tt = isset($ta[$bits[0]]) ? $ta[$bits[0]] : $bits[0];
+                    if ($tt == $bits[0] && $kk == $k) {
+                        continue;
+                    }
+                    
+                    $lcfg[$kk][$l] = $tt .':'. $bits[1];
+                    
+                    
+                }
+                
+            }
+        }
+         
         
     }
     function updateTableComments()
@@ -97,7 +143,7 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
                     FROM
                         information_schema.TABLES
                     WHERE
-                        TABLE_SCHEMA = '{$q->escape($q->database())}'
+                        TABLE_SCHEMA = DATABASE()
                         AND
                         TABLE_NAME = '{$q->escape($tbl)}'
         ");
@@ -142,6 +188,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,6 +206,8 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
         }
         
         
+        
+        
         foreach($revmap as $target_table => $sources) {
             
             
@@ -166,7 +215,7 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
             
             if (!isset($this->schema[$target_table])) {
                 echo "Skip $target_table  = table does not exist in schema\n";
-                return;
+                continue;
             }
         
             
@@ -182,8 +231,8 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
                 BEFORE DELETE ON `{$target_table}`
             FOR EACH ROW
             BEGIN
-               DECLARE mid INT(11);
-             
+                DECLARE mid INT(11);
+                IF (@DISABLE_TRIGGER IS NULL AND @DISABLE_TRIGGER_{$target_table} IS NULL ) THEN  
                
             ";
             foreach($sources as $source=>$target) {
@@ -192,11 +241,11 @@ 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;
                 ";
             }
@@ -209,6 +258,7 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
             }
             
             $trigger .= "
+                END IF;
             END 
            
             ";
@@ -245,44 +295,63 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
             FOR EACH ROW
             BEGIN
                DECLARE mid INT(11);
-               
+                IF (@DISABLE_TRIGGER IS NULL AND @DISABLE_TRIGGER_{$tbl} IS NULL ) THEN 
                
             ";
+            $has_checks=  false;
+            $errs = array();
             foreach($map as $source_col=>$target) {
                 // check that source_col exists in schema.
                 if (!isset($this->schema[$tbl][$source_col])) {
+                    $errs[] = "SOURCE MISSING: $source_col => $target";
                     continue;
                 }
                 
                 
                 $source_tbl = $tbl;
                 list($target_table , $target_col) = explode(':', $target);
+                
+                if (!isset($this->schema[$target_table])) {
+                    // skip... target table does not exist
+                    $errs[] = "TARGET MISSING: $source_col => $target";
+                    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;
                 ";
-                
+                $has_checks=  true;
                 
                 
             }
+            
             $ar = $this->listTriggerFunctions($tbl, 'insert');
             foreach($ar as $fn=>$col) {
                 $trigger .= "
                     CALL $fn( NEW.{$col});
                 ";
+                $has_checks=  true;
             }
             
             $trigger .= "
+                END IF;
             END 
            
             ";
+            
+            if (!$has_checks) {
+                echo "SKIP TRIGGER {$tbl}_before_insert (missing " . implode(", ", $errs) . ")\n";
+                continue;
+            }
             //echo $trigger; exit;
             //DB_DAtaObject::debugLevel(1);
             $q = DB_DataObject::factory('core_enum');
@@ -319,41 +388,59 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
             FOR EACH ROW
             BEGIN
                DECLARE mid INT(11);
-               
+               IF (@DISABLE_TRIGGER IS NULL AND @DISABLE_TRIGGER_{$tbl} IS NULL ) THEN  
                
             ";
+            $has_checks=  false;
+            $errs = array();
             foreach($map as $source_col=>$target) {
                 // check that source_col exists in schema.
                 if (!isset($this->schema[$tbl][$source_col])) {
+                    $errs[] = "SOURCE MISSING: $source_col => $target";
                     continue;
                 }
                 
                 
                 $source_tbl = $tbl;
                 list($target_table , $target_col) = explode(':', $target);
+                
+                if (!isset($this->schema[$target_table])) {
+                    // skip... target table does not exist
+                    $errs[] = "TARGET MISSING: $source_col => $target";
+                    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;
                        
                     END IF;
                 ";
+                $has_checks=  true;
             }
             $ar = $this->listTriggerFunctions($tbl, 'update');
             foreach($ar as $fn=>$col) {
                 $trigger .= "
                     CALL $fn(OLD.{$col}, NEW.{$col});
                 ";
+                $has_checks=  true;
             }
             
             $trigger .= "
+                END IF;
             END 
            
             ";
+            if (!$has_checks) {
+                echo "SKIP TRIGGER {$tbl}_before_update (missing " . implode(", ", $errs) . ")\n";
+                continue;
+            }
+            
             //echo $trigger; exit;
             //DB_DAtaObject::debugLevel(1);
             $q = DB_DataObject::factory('core_enum');
@@ -361,11 +448,6 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
             echo "CREATED TRIGGER {$tbl}_before_update\n";
             
             
-            
-            
-            
-            
-            
         }
         
         
@@ -417,5 +499,6 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
     }
         
     
+    
 }