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