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