UpdateDatabase/MysqlLinks.php

[Pman.Core] / UpdateDatabase / MysqlLinks.php

diff --git a/UpdateDatabase/MysqlLinks.php b/UpdateDatabase/MysqlLinks.php
index 18e6e64..def0dd9 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,14 +163,16 @@ 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 $tbl\n";
-                return;
+                echo "Skip $target_table  = table does not exist in schema\n";
+                continue;
             }
         
             
@@ -182,8 +188,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 +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,6 +215,7 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
             }
             
             $trigger .= "
+                END IF;
             END 
            
             ";
@@ -216,7 +223,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";
         }
         
         
@@ -245,7 +252,7 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
             FOR EACH ROW
             BEGIN
                DECLARE mid INT(11);
-               
+                if (@DISABLE_TRIGGER IS NULL AND @DISABLE_TRIGGER_{$target_table} IS NULL ) THEN 
                
             ";
             foreach($map as $source_col=>$target) {
@@ -257,11 +264,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,6 +294,7 @@ class Pman_Core_UpdateDatabase_MysqlLinks {
             }
             
             $trigger .= "
+                END IF;
             END 
            
             ";
@@ -287,7 +302,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";
             
             
             
@@ -331,11 +346,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;
@@ -358,7 +379,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";
             
             
             
@@ -402,9 +423,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();
@@ -418,6 +437,44 @@ 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
+            ");
+                     
+            $ce->fetch();
+            
+            if($ce->csname == 'utf8' && $ce->collatename == 'utf8_unicode_ci'){
+                echo "$tbl is Already utf8 \n";
+                continue;
+            }
+            
+            $ce = DB_DataObject::factory('core_enum');
+            $ce->query("ALTER TABLE {$tbl} CONVERT TO CHARACTER SET  utf8 COLLATE utf8_unicode_ci");
+            echo "FIXED utf8 on {$tbl}\n";
+            
+        }
+    }
     
 }