git.roojs.org Git - Pman.Core/blob - UpdateDatabase/MysqlLinks.php

   1 <?php
   2 /**
   3  * our standard code relies on links.ini files for the relationships in mysql.
   4  *
   5  * as we use 'loose' relationships - eg. we allow '0' as a missing link mysql FORIEGN KEYS do not really work.
   6  *
   7  * There are a couple of ideas behind this code.
   8  *
   9  * a) put the relationships in the table comments FK(col=table:col,col=table:col)
  10  *  -- we can not put it in the column comments as there is no clean way to update column comments.
  11  *  -- This can be used by external programs to extract the Relationships.
  12  *
  13  * b) generate triggers? to protect against updates to the database..
  14  *
  15  *  -- stored procedures are named
  16  *     {tablename}_before_{insert|delete|update}
  17  *
  18  *
  19  *   initial code will auto generate triggers
  20  *   -- how to add User defined modifications to triggers?
  21  *   -- we can CALL a stored procedure..?
  22  *   -- {tablename}_trigger_{optional_string}_before_delete_{column_name}(NEW.column)
  23  *   -- {tablename}_trigger_{optional_string}_before_update_{column_name}(OLD.column, NEW.column}
  24  *   -- {tablename}_trigger_{optional_string}_before_insert_{column_name}(OLD.column}
  25  *
  26  *
  27  * ------- Importing with triggers disabled.
  28  *
  29  *  SET @DISABLE_TRIGGER=1; (or anything you like except NULL)
  30  *  do imports
  31  * SET @DISABLE_TRIGGER=NULL;
  32  *
  33  * ------ Call a method disabling a particular set of triggers
  34  *  SET @DISABLE_TRIGGER_the_table_name=1; (or anything you like except NULL)
  35  *  do action
  36  *  SET @DISABLE_TRIGGER_the_table_name=NULL;*
  37  */
  38
  39 class Pman_Core_UpdateDatabase_MysqlLinks {
  40
  41     var $dburl;
  42     var $schema;
  43     var $links;
  44
  45     function __construct()
  46     {
  47
  48         $this->loadIniFiles();
  49         $this->updateTableComments();
  50
  51         $ff = HTML_FlexyFramework::get();
  52         if (!empty($ff->Pman['enable_trigger_tests'])) {
  53
  54             // note we may want to override some of these... - to do special triggers..
  55             // as you can only have one trigger per table for each action.
  56
  57             $this->createDeleteTriggers();
  58             $this->createInsertTriggers();
  59             $this->createUpdateTriggers();
  60         }
  61
  62
  63     }
  64
  65     function loadIniFiles()
  66     {
  67         // will create the combined ini cache file for the running user.
  68
  69         $ff = HTML_FlexyFramework::get();
  70         $ff->generateDataobjectsCache(true);
  71         $this->dburl = parse_url($ff->database);
  72
  73         $dbini = 'ini_'. basename($this->dburl['path']);
  74
  75
  76         $iniCache = $ff->DB_DataObject[$dbini];
  77
  78         $this->schema = parse_ini_file($iniCache, true);
  79         $this->links = parse_ini_file(preg_replace('/\.ini$/', '.links.ini', $iniCache), true);
  80
  81
  82
  83     }
  84     function updateTableComments()
  85     {
  86         foreach($this->links as $tbl =>$map) {
  87             $this->updateTableComment($tbl, $map);
  88
  89         }
  90
  91
  92     }
  93
  94     function updateTableComment($tbl, $map)
  95     {
  96
  97
  98         if (!isset($this->schema[$tbl])) {
  99             echo "Skip $tbl = table does not exist in schema\n";
 100             return;
 101         }
 102
 103
 104         $q = DB_DAtaObject::factory('core_enum');
 105         $q->query("SELECT
 106                      TABLE_COMMENT
 107                     FROM
 108                         information_schema.TABLES
 109                     WHERE
 110                         TABLE_SCHEMA = '{$q->escape($q->database())}'
 111                         AND
 112                         TABLE_NAME = '{$q->escape($tbl)}'
 113         ");
 114         $q->fetch();
 115         $tc = $q->TABLE_COMMENT;
 116         //echo "$tbl: $tc\n\n";
 117         if (!empty($q->TABLE_COMMENT)) {
 118             //var_dump($tc);
 119             $tc = trim(preg_replace('/FK\([^)]+\)/', '' , $q->TABLE_COMMENT));
 120             //var_dump($tc);exit;
 121             // strip out the old FC(....)
 122
 123         }
 124         $fks = array();
 125         foreach($map as $k=>$v) {
 126             $fks[] = "$k=$v";
 127         }
 128         $fkstr = $tc . ' FK(' . implode("\n", $fks) .')';
 129         if ($q->TABLE_COMMENT == $fkstr) {
 130             return;
 131         }
 132
 133         $q = DB_DAtaObject::factory('core_enum');
 134         $q->query("ALTER TABLE $tbl COMMENT = '{$q->escape($fkstr)}'");
 135
 136
 137
 138     }
 139
 140     function createDeleteTriggers()
 141     {
 142
 143         // this should only be enabled if the project settings are configured..
 144
 145
 146
 147         // delete triggers on targets -
 148         // if you delete a company, and a person points to it, then it should fire an error...
 149
 150
 151
 152
 153         // create a list of source/targets from $this->links
 154
 155
 156         $revmap = array();
 157         foreach($this->links as $tbl => $map) {
 158             if (!isset($this->schema[$tbl])) {
 159                 continue;
 160             }
 161             foreach($map as $k =>$v) {
 162                 list ($tname, $tcol) = explode(':', $v);
 163
 164
 165                 if (!isset($revmap[$tname])) {
 166                     $revmap[$tname] = array();
 167                 }
 168                 $revmap[$tname]["$tbl:$k"] = "$tname:$tcol";
 169             }
 170         }
 171
 172
 173
 174
 175         foreach($revmap as $target_table => $sources) {
 176
 177
 178             // throw example.. UPDATE `Error: invalid_id_test` SET x=1;
 179
 180             if (!isset($this->schema[$target_table])) {
 181                 echo "Skip $target_table  = table does not exist in schema\n";
 182                 continue;
 183             }
 184
 185
 186
 187             $q = DB_DataObject::factory('core_enum');
 188             $q->query("
 189                 DROP TRIGGER IF EXISTS `{$target_table}_before_delete` ;
 190             ");
 191
 192             $trigger = "
 193
 194             CREATE TRIGGER `{$target_table}_before_delete`
 195                 BEFORE DELETE ON `{$target_table}`
 196             FOR EACH ROW
 197             BEGIN
 198                 DECLARE mid INT(11);
 199                 IF (@DISABLE_TRIGGER IS NULL AND @DISABLE_TRIGGER_{$target_table} IS NULL ) THEN
 200
 201             ";
 202             foreach($sources as $source=>$target) {
 203                 list($source_table , $source_col) = explode(':', $source);
 204                 list($target_table , $target_col) = explode(':', $target);
 205                 $err = substr("Failed Delete {$target_table} refs {$source_table}:{$source_col}", 0, 64);
 206                 $trigger .="
 207                     SET mid = 0;
 208                     IF OLD.{$target_col} > 0 THEN
 209                         SELECT count(*) into mid FROM {$source_table} WHERE {$source_col} = OLD.{$target_col} LIMIT 1;
 210                         IF mid > 0 THEN
 211                            UPDATE `$err` SET x = 1;
 212                         END IF;
 213                     END IF;
 214                 ";
 215             }
 216
 217             $ar = $this->listTriggerFunctions($tbl, 'delete');
 218             foreach($ar as $fn=>$col) {
 219                 $trigger .= "
 220                     CALL $fn( OLD.{$col});
 221                 ";
 222             }
 223
 224             $trigger .= "
 225                 END IF;
 226             END
 227
 228             ";
 229
 230             //DB_DAtaObject::debugLevel(1);
 231             $q = DB_DataObject::factory('core_enum');
 232             $q->query($trigger);
 233              echo "CREATED TRIGGER {$target_table}_before_delete\n";
 234         }
 235
 236
 237         // inserting - row should not point to a reference that does not exist...
 238
 239
 240
 241
 242     }
 243     function createInsertTriggers()
 244     {
 245         foreach($this->links as $tbl => $map) {
 246             if (!isset($this->schema[$tbl])) {
 247                 continue;
 248             }
 249
 250             $q = DB_DataObject::factory('core_enum');
 251             $q->query("
 252                 DROP TRIGGER IF EXISTS `{$tbl}_before_insert` ;
 253             ");
 254
 255             $trigger = "
 256
 257             CREATE TRIGGER `{$tbl}_before_insert`
 258                 BEFORE INSERT ON `{$tbl}`
 259             FOR EACH ROW
 260             BEGIN
 261                DECLARE mid INT(11);
 262                 IF (@DISABLE_TRIGGER IS NULL AND @DISABLE_TRIGGER_{$tbl} IS NULL ) THEN
 263
 264             ";
 265             $has_checks=  false;
 266             $errs = array();
 267             foreach($map as $source_col=>$target) {
 268                 // check that source_col exists in schema.
 269                 if (!isset($this->schema[$tbl][$source_col])) {
 270                     $errs[] = "SOURCE MISSING: $source_col => $target";
 271                     continue;
 272                 }
 273
 274
 275                 $source_tbl = $tbl;
 276                 list($target_table , $target_col) = explode(':', $target);
 277
 278                 if (!isset($this->schema[$target_table])) {
 279                     // skip... target table does not exist
 280                     $errs[] = "TARGET MISSING: $source_col => $target";
 281                     continue;
 282                 }
 283
 284
 285                 $err = substr("Fail: INSERT referenced {$tbl}:{$source_col}", 0, 64);
 286                 $trigger .="
 287                     SET mid = 0;
 288                     if NEW.{$source_col} > 0 THEN
 289                         SELECT {$target_col} into mid FROM {$target_table} WHERE {$target_col} = NEW.{$source_col} LIMIT 1;
 290                         IF mid < 1 THEN
 291                             UPDATE `$err` SET x = 1;
 292                         END IF;
 293
 294                     END IF;
 295                 ";
 296                 $has_checks=  true;
 297
 298
 299             }
 300
 301             $ar = $this->listTriggerFunctions($tbl, 'insert');
 302             foreach($ar as $fn=>$col) {
 303                 $trigger .= "
 304                     CALL $fn( NEW.{$col});
 305                 ";
 306                 $has_checks=  true;
 307             }
 308
 309             $trigger .= "
 310                 END IF;
 311             END
 312
 313             ";
 314
 315             if (!$has_checks) {
 316                 echo "SKIP TRIGGER {$tbl}_before_insert (missing " . implode(", ", $errs) . ")\n";
 317                 continue;
 318             }
 319             //echo $trigger; exit;
 320             //DB_DAtaObject::debugLevel(1);
 321             $q = DB_DataObject::factory('core_enum');
 322             $q->query($trigger);
 323             echo "CREATED TRIGGER {$tbl}_before_insert\n";
 324
 325
 326
 327
 328
 329
 330
 331         }
 332
 333
 334
 335     }
 336      function createUpdateTriggers()
 337     {
 338         foreach($this->links as $tbl => $map) {
 339             if (!isset($this->schema[$tbl])) {
 340                 continue;
 341             }
 342
 343             $q = DB_DataObject::factory('core_enum');
 344             $q->query("
 345                 DROP TRIGGER IF EXISTS `{$tbl}_before_update` ;
 346             ");
 347
 348             $trigger = "
 349
 350             CREATE TRIGGER `{$tbl}_before_update`
 351                 BEFORE UPDATE ON `{$tbl}`
 352             FOR EACH ROW
 353             BEGIN
 354                DECLARE mid INT(11);
 355                IF (@DISABLE_TRIGGER IS NULL AND @DISABLE_TRIGGER_{$tbl} IS NULL ) THEN
 356
 357             ";
 358             $has_checks=  false;
 359             $err = array();
 360             foreach($map as $source_col=>$target) {
 361                 // check that source_col exists in schema.
 362                 if (!isset($this->schema[$tbl][$source_col])) {
 363                     $err[] = "SOURCE MISSING: $source_col => $target";
 364                     continue;
 365                 }
 366
 367
 368                 $source_tbl = $tbl;
 369                 list($target_table , $target_col) = explode(':', $target);
 370
 371                 if (!isset($this->schema[$target_table])) {
 372                     // skip... target table does not exist
 373                     $err[] = "TARGET MISSING: $source_col => $target";
 374                     continue;
 375                 }
 376
 377                 $err = substr("Fail: UPDATE referenced {$tbl}:$source_col", 0, 64);
 378                 $trigger .="
 379                     SET mid = 0;
 380                     if NEW.{$source_col} > 0 THEN
 381                         SELECT {$target_col} into mid FROM {$target_table} WHERE {$target_col} = NEW.{$source_col} LIMIT 1;
 382                         IF mid < 1 THEN
 383                             UPDATE `$err` SET x = 1;
 384                         END IF;
 385
 386                     END IF;
 387                 ";
 388                 $has_checks=  true;
 389             }
 390             $ar = $this->listTriggerFunctions($tbl, 'update');
 391             foreach($ar as $fn=>$col) {
 392                 $trigger .= "
 393                     CALL $fn(OLD.{$col}, NEW.{$col});
 394                 ";
 395                 $has_checks=  true;
 396             }
 397
 398             $trigger .= "
 399                 END IF;
 400             END
 401
 402             ";
 403             if (!$has_checks) {
 404                 echo "SKIP TRIGGER {$tbl}_before_update (missing " . implode(", ", $err) . ")\n";
 405                 return;
 406             }
 407
 408             //echo $trigger; exit;
 409             //DB_DAtaObject::debugLevel(1);
 410             $q = DB_DataObject::factory('core_enum');
 411             $q->query($trigger);
 412             echo "CREATED TRIGGER {$tbl}_before_update\n";
 413
 414
 415
 416
 417
 418
 419
 420         }
 421
 422
 423
 424     }
 425     /**
 426      * check the information schema for any methods that match the trigger criteria.
 427      *   -- {tablename}_trigger_{optional_string}_before_delete_{column_name}(NEW.column)
 428      *   -- {tablename}_trigger_{optional_string}_before_update_{column_name}(OLD.column, NEW.column}
 429      *   -- {tablename}_trigger_{optional_string}_before_insert_{column_name}(OLD.column}
 430      *
 431      *
 432      */
 433     // type = update/insert/delete
 434
 435     function listTriggerFunctions($table, $type)
 436     {
 437         static $cache = array();
 438         if (!isset($cache[$table])) {
 439             $cache[$table] = array();
 440             $q = DB_DAtaObject::factory('core_enum');
 441             $q->query("SELECT
 442                             SPECIFIC_NAME
 443                         FROM
 444                             information_schema.ROUTINES
 445                         WHERE
 446                             ROUTINE_SCHEMA = '{$q->escape($q->database())}'
 447                             AND
 448                             ROUTINE_NAME LIKE '" . $q->escape("{$table}_trigger_")  . "%'
 449                             AND
 450                             ROUTINE_TYPE = 'PROCEDURE'
 451
 452             ");
 453             while ($q->fetch()) {
 454                 $cache[$table][] = $q->SPECIFIC_NAME;
 455             }
 456
 457         }
 458         // now see which of the procedures match the specification..
 459         $ret = array();
 460         foreach($cache[$table] as $cname) {
 461             $bits = explode("_before_{$type}_", $cname);
 462             if (count($bits) < 2) {
 463                 continue;
 464             }
 465             $ret[$cname] = $bits[1];
 466         }
 467         return $ret;
 468     }
 469
 470
 471
 472 }
 473