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