UpdateDatabase/MysqlLinks.php

[Pman.Core] / UpdateDatabase / MysqlLinks.php

diff --git a/UpdateDatabase/MysqlLinks.php b/UpdateDatabase/MysqlLinks.php
index 5488003..c25851e 100644 (file)
--- a/UpdateDatabase/MysqlLinks.php
+++ b/UpdateDatabase/MysqlLinks.php
@@ -38,6 +38,9 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
           
         $this->loadIniFiles();
         $this->updateTableComments();
+        
+        $this->updateCharacterSet();
+        
         $ff = HTML_FlexyFramework::get();
         if (!empty($ff->Pman['enable_trigger_tests'])) {
             
@@ -86,7 +89,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 +145,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 +163,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,11 +198,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,7 +222,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";
         }
         
         
@@ -250,11 +263,18 @@ 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;
@@ -280,7 +300,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";
             
             
             
@@ -324,11 +344,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;
@@ -351,7 +377,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";
             
             
             
@@ -395,9 +421,7 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
             while ($q->fetch()) {
                 $cache[$table][] = $q->SPECIFIC_NAME;
             }
-            if ($table == 'core_notify_recur') {
-                print_r($cache[$table]);
-            }
+            
         }
         // now see which of the procedures match the specification..
         $ret = array();
@@ -411,6 +435,38 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
         return $ret;
     }
         
+    function updateCharacterSet()
+    {
+        foreach (array_keys($this->schema) as $tbl){
+            
+            if(strpos($tbl, '__keys') !== false ){
+                continue;
+            }
+            
+            $ce = DB_DataObject::factory('core_enum');
+            
+            $ce->query("
+                SELECT
+                        CCSA.character_set_name csname,
+                        CCSA.collation_name collatename
+                FROM
+                        information_schema.`TABLES` T,
+                        information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
+                WHERE
+                        CCSA.collation_name = T.table_collation
+                    AND
+                        T.table_schema = '{$ce->database()}' COLLATE utf8_unicode_ci
+                    AND
+                        T.table_name = '{$tbl}' COLLATE utf8_unicode_ci
+            ");
+                     
+            
+            
+            
+            
+            
+        }
+    }
     
 }