From 71259fb1f44900dff518cd81f9051300d10fbcae Mon Sep 17 00:00:00 2001
From: Alan Knowles <alan@roojs.com>
Date: Thu, 16 Apr 2015 12:52:37 +0800
Subject: [PATCH] UpdateDatabase/MysqlLinks.php

---
 UpdateDatabase/MysqlLinks.php | 345 ++++++++++++++++++++++++++++++++++
 1 file changed, 345 insertions(+)
 create mode 100644 UpdateDatabase/MysqlLinks.php

diff --git a/UpdateDatabase/MysqlLinks.php b/UpdateDatabase/MysqlLinks.php
new file mode 100644
index 00000000..e086bf72
--- /dev/null
+++ b/UpdateDatabase/MysqlLinks.php
@@ -0,0 +1,345 @@
+<?php
+/**
+ * our standard code relies on links.ini files for the relationships in mysql.
+ *
+ * as we use 'loose' relationships - eg. we allow '0' as a missing link mysql FORIEGN KEYS do not really work.
+ *
+ * There are a couple of ideas behind this code.
+ *
+ * a) put the relationships in the table comments FK(col=table:col,col=table:col)
+ *  -- we can not put it in the column comments as there is no clean way to update column comments.
+ *  -- This can be used by external programs to extract the Relationships.
+ *
+ * b) generate triggers? to protect against updates to the database..
+ *
+ *  -- stored procedures are named
+ *     {tablename}_before_{insert|delete|update}
+ *     
+ *  
+ *   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_delete_{column_name}(OLD.column, NEW.column}
+ *   -- {tablename}_trigger_before_delete_{column_name}(OLD.column}
+ *
+ *  
+ *
+ */
+
+class Pman_Core_UpdateDatabase_MysqlLinks {
+    
+    var $dburl;
+    var $schema;
+    var $links;
+    
+    function __construct()
+    {
+          
+        $this->loadIniFiles();
+        $this->updateTableComments();
+        $ff = HTML_FlexyFramework::get();
+        if (!empty($ff->Pman['enable_trigger_tests'])) {
+            
+            // 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();
+        }
+        
+        
+    }
+    
+    function loadIniFiles()
+    {
+        // will create the combined ini cache file for the running user.
+        
+        $ff = HTML_FlexyFramework::get();
+        $ff->generateDataobjectsCache(true);
+        $this->dburl = parse_url($ff->database);
+        
+        $dbini = 'ini_'. basename($this->dburl['path']);
+        
+        
+        $iniCache = $ff->DB_DataObject[$dbini];
+        
+        $this->schema = parse_ini_file($iniCache, true);
+        $this->links = parse_ini_file(preg_replace('/\.ini$/', '.links.ini', $iniCache), true);
+        
+
+        
+    }
+    function updateTableComments()
+    {
+        foreach($this->links as $tbl =>$map) {
+            $this->updateTableComment($tbl, $map);
+            
+        }
+        
+        
+    }
+    
+    function updateTableComment($tbl, $map)
+    {
+         
+        
+        if (!isset($this->schema[$tbl])) {
+            echo "Skip $tbl\n";
+            return;
+        }
+        
+        
+        $q = DB_DAtaObject::factory('core_enum');
+        $q->query("SELECT
+                     TABLE_COMMENT
+                    FROM
+                        information_schema.TABLES
+                    WHERE
+                        TABLE_SCHEMA = '{$q->escape($q->database())}'
+                        AND
+                        TABLE_NAME = '{$q->escape($tbl)}'
+        ");
+        $q->fetch();
+        $tc = $q->TABLE_COMMENT;
+        //echo "$tbl: $tc\n\n";
+        if (!empty($q->TABLE_COMMENT)) {
+            //var_dump($tc);
+            $tc = trim(preg_replace('/FK\([^)]+\)/', '' , $q->TABLE_COMMENT));
+            //var_dump($tc);exit;
+            // strip out the old FC(....) 
+                        
+        }
+        $fks = array();
+        foreach($map as $k=>$v) {
+            $fks[] = "$k=$v";
+        }
+        $fkstr = $tc . ' FK(' . implode("\n", $fks) .')';
+        if ($q->TABLE_COMMENT == $fkstr) {
+            return;
+        }
+        
+        $q = DB_DAtaObject::factory('core_enum');
+        $q->query("ALTER TABLE $tbl COMMENT = '{$q->escape($fkstr)}'");
+        
+        
+        
+    }
+    
+    function createDeleteTriggers()
+    {
+        
+        // this should only be enabled if the project settings are configured..
+        
+       
+        
+        // delete triggers on targets -
+        // if you delete a company, and a person points to it, then it should fire an error...
+        
+        
+        
+        
+        // create a list of source/targets from $this->links
+        
+        $revmap = array();
+        foreach($this->links as $tbl => $map) {
+            if (!isset($this->schema[$tbl])) {
+                continue;
+            }
+            foreach($map as $k =>$v) {
+                list ($tname, $tcol) = explode(':', $v);
+                
+                
+                if (!isset($revmap[$tname])) {
+                    $revmap[$tname] = array();
+                }
+                $revmap[$tname]["$tbl:$k"] = "$tname:$tcol";
+            }
+        }
+        
+        
+        foreach($revmap as $target_table => $sources) {
+            
+            
+            // throw example.. UPDATE `Error: invalid_id_test` SET x=1;
+            
+            $q = DB_DataObject::factory('core_enum');
+            $q->query("
+                DROP TRIGGER IF EXISTS `{$target_table}_before_delete` ;
+            ");
+            
+            $trigger = "
+             
+            CREATE TRIGGER `{$target_table}_before_delete`
+                BEFORE DELETE ON `{$target_table}`
+            FOR EACH ROW
+            BEGIN
+               DECLARE mid INT(11);
+             
+               
+            ";
+            foreach($sources as $source=>$target) {
+                list($source_table , $source_col) = explode(':', $source);
+                list($target_table , $target_col) = explode(':', $target);
+                $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;
+                       
+                    END IF;
+                ";
+            }
+            $trigger .= "
+            END 
+           
+            ";
+            
+            //DB_DAtaObject::debugLevel(1);
+            $q = DB_DataObject::factory('core_enum');
+            $q->query($trigger);
+             
+        }
+        
+        
+        // inserting - row should not point to a reference that does not exist...
+        
+        
+        
+        
+    }
+    function createInsertTriggers()
+    {
+        foreach($this->links as $tbl => $map) {
+            if (!isset($this->schema[$tbl])) {
+                continue;
+            }
+            
+            $q = DB_DataObject::factory('core_enum');
+            $q->query("
+                DROP TRIGGER IF EXISTS `{$tbl}_before_insert` ;
+            ");
+            
+            $trigger = "
+             
+            CREATE TRIGGER `{$tbl}_before_insert`
+                BEFORE INSERT ON `{$tbl}`
+            FOR EACH ROW
+            BEGIN
+               DECLARE mid INT(11);
+               
+               
+            ";
+            foreach($map as $source_col=>$target) {
+                // check that source_col exists in schema.
+                if (!isset($this->schema[$tbl][$source_col])) {
+                    continue;
+                }
+                
+                
+                $source_tbl = $tbl;
+                list($target_table , $target_col) = explode(':', $target);
+                $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};
+                        IF mid < 1 THEN
+                            UPDATE `$err` SET x = 1;
+                        END IF;
+                       
+                    END IF;
+                ";
+            }
+            $trigger .= "
+            END 
+           
+            ";
+            //echo $trigger; exit;
+            //DB_DAtaObject::debugLevel(1);
+            $q = DB_DataObject::factory('core_enum');
+            $q->query($trigger);
+             
+            
+            
+            
+            
+            
+            
+            
+        }
+        
+        
+        
+    }
+     function createUpdateTriggers()
+    {
+        foreach($this->links as $tbl => $map) {
+            if (!isset($this->schema[$tbl])) {
+                continue;
+            }
+            
+            $q = DB_DataObject::factory('core_enum');
+            $q->query("
+                DROP TRIGGER IF EXISTS `{$tbl}_before_update` ;
+            ");
+            
+            $trigger = "
+             
+            CREATE TRIGGER `{$tbl}_before_update`
+                BEFORE UPDATE ON `{$tbl}`
+            FOR EACH ROW
+            BEGIN
+               DECLARE mid INT(11);
+               
+               
+            ";
+            foreach($map as $source_col=>$target) {
+                // check that source_col exists in schema.
+                if (!isset($this->schema[$tbl][$source_col])) {
+                    continue;
+                }
+                
+                
+                $source_tbl = $tbl;
+                list($target_table , $target_col) = explode(':', $target);
+                $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};
+                        IF mid < 1 THEN
+                            UPDATE `$err` SET x = 1;
+                        END IF;
+                       
+                    END IF;
+                ";
+            }
+            $trigger .= "
+            END 
+           
+            ";
+            //echo $trigger; exit;
+            //DB_DAtaObject::debugLevel(1);
+            $q = DB_DataObject::factory('core_enum');
+            $q->query($trigger);
+             
+            
+            
+            
+            
+            
+            
+            
+        }
+        
+        
+        
+    }
+    
+    
+}
+
-- 
2.39.2