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