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