1<?php 2// This file is part of Moodle - http://moodle.org/ 3// 4// Moodle is free software: you can redistribute it and/or modify 5// it under the terms of the GNU General Public License as published by 6// the Free Software Foundation, either version 3 of the License, or 7// (at your option) any later version. 8// 9// Moodle is distributed in the hope that it will be useful, 10// but WITHOUT ANY WARRANTY; without even the implied warranty of 11// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12// GNU General Public License for more details. 13// 14// You should have received a copy of the GNU General Public License 15// along with Moodle. If not, see <http://www.gnu.org/licenses/>. 16 17/** 18 * Database manager instance is responsible for all database structure modifications. 19 * 20 * @package core_ddl 21 * @copyright 1999 onwards Martin Dougiamas http://dougiamas.com 22 * 2001-3001 Eloy Lafuente (stronk7) http://contiento.com 23 * 2008 Petr Skoda http://skodak.org 24 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 25 */ 26 27defined('MOODLE_INTERNAL') || die(); 28 29/** 30 * Database manager instance is responsible for all database structure modifications. 31 * 32 * It is using db specific generators to find out the correct SQL syntax to do that. 33 * 34 * @package core_ddl 35 * @copyright 1999 onwards Martin Dougiamas http://dougiamas.com 36 * 2001-3001 Eloy Lafuente (stronk7) http://contiento.com 37 * 2008 Petr Skoda http://skodak.org 38 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 39 */ 40class database_manager { 41 42 /** @var moodle_database A moodle_database driver specific instance.*/ 43 protected $mdb; 44 45 /** @var sql_generator A driver specific SQL generator instance. Public because XMLDB editor needs to access it.*/ 46 public $generator; 47 48 /** 49 * Creates a new database manager instance. 50 * @param moodle_database $mdb A moodle_database driver specific instance. 51 * @param sql_generator $generator A driver specific SQL generator instance. 52 */ 53 public function __construct($mdb, $generator) { 54 $this->mdb = $mdb; 55 $this->generator = $generator; 56 } 57 58 /** 59 * Releases all resources 60 */ 61 public function dispose() { 62 if ($this->generator) { 63 $this->generator->dispose(); 64 $this->generator = null; 65 } 66 $this->mdb = null; 67 } 68 69 /** 70 * This function will execute an array of SQL commands. 71 * 72 * @param string[] $sqlarr Array of sql statements to execute. 73 * @param array|null $tablenames an array of xmldb table names affected by this request. 74 * @throws ddl_change_structure_exception This exception is thrown if any error is found. 75 */ 76 protected function execute_sql_arr(array $sqlarr, $tablenames = null) { 77 $this->mdb->change_database_structure($sqlarr, $tablenames); 78 } 79 80 /** 81 * Execute a given sql command string. 82 * 83 * @param string $sql The sql string you wish to be executed. 84 * @throws ddl_change_structure_exception This exception is thrown if any error is found. 85 */ 86 protected function execute_sql($sql) { 87 $this->mdb->change_database_structure($sql); 88 } 89 90 /** 91 * Given one xmldb_table, check if it exists in DB (true/false). 92 * 93 * @param string|xmldb_table $table The table to be searched (string name or xmldb_table instance). 94 * @return bool True is a table exists, false otherwise. 95 */ 96 public function table_exists($table) { 97 if (!is_string($table) and !($table instanceof xmldb_table)) { 98 throw new ddl_exception('ddlunknownerror', NULL, 'incorrect table parameter!'); 99 } 100 return $this->generator->table_exists($table); 101 } 102 103 /** 104 * Reset a sequence to the id field of a table. 105 * @param string|xmldb_table $table Name of table. 106 * @throws ddl_exception thrown upon reset errors. 107 */ 108 public function reset_sequence($table) { 109 if (!is_string($table) and !($table instanceof xmldb_table)) { 110 throw new ddl_exception('ddlunknownerror', NULL, 'incorrect table parameter!'); 111 } else { 112 if ($table instanceof xmldb_table) { 113 $tablename = $table->getName(); 114 } else { 115 $tablename = $table; 116 } 117 } 118 119 // Do not test if table exists because it is slow 120 121 if (!$sqlarr = $this->generator->getResetSequenceSQL($table)) { 122 throw new ddl_exception('ddlunknownerror', null, 'table reset sequence sql not generated'); 123 } 124 125 $this->execute_sql_arr($sqlarr, array($tablename)); 126 } 127 128 /** 129 * Given one xmldb_field, check if it exists in DB (true/false). 130 * 131 * @param string|xmldb_table $table The table to be searched (string name or xmldb_table instance). 132 * @param string|xmldb_field $field The field to be searched for (string name or xmldb_field instance). 133 * @return boolean true is exists false otherwise. 134 * @throws ddl_table_missing_exception 135 */ 136 public function field_exists($table, $field) { 137 // Calculate the name of the table 138 if (is_string($table)) { 139 $tablename = $table; 140 } else { 141 $tablename = $table->getName(); 142 } 143 144 // Check the table exists 145 if (!$this->table_exists($table)) { 146 throw new ddl_table_missing_exception($tablename); 147 } 148 149 if (is_string($field)) { 150 $fieldname = $field; 151 } else { 152 // Calculate the name of the table 153 $fieldname = $field->getName(); 154 } 155 156 // Get list of fields in table 157 $columns = $this->mdb->get_columns($tablename); 158 159 $exists = array_key_exists($fieldname, $columns); 160 161 return $exists; 162 } 163 164 /** 165 * Given one xmldb_index, the function returns the name of the index in DB 166 * of false if it doesn't exist 167 * 168 * @param xmldb_table $xmldb_table table to be searched 169 * @param xmldb_index $xmldb_index the index to be searched 170 * @param bool $returnall true means return array of all indexes, false means first index only as string 171 * @return array|string|bool Index name, array of index names or false if no indexes are found. 172 * @throws ddl_table_missing_exception Thrown when table is not found. 173 */ 174 public function find_index_name(xmldb_table $xmldb_table, xmldb_index $xmldb_index, $returnall = false) { 175 // Calculate the name of the table 176 $tablename = $xmldb_table->getName(); 177 178 // Check the table exists 179 if (!$this->table_exists($xmldb_table)) { 180 throw new ddl_table_missing_exception($tablename); 181 } 182 183 // Extract index columns 184 $indcolumns = $xmldb_index->getFields(); 185 186 // Get list of indexes in table 187 $indexes = $this->mdb->get_indexes($tablename); 188 189 $return = array(); 190 191 // Iterate over them looking for columns coincidence 192 foreach ($indexes as $indexname => $index) { 193 $columns = $index['columns']; 194 // Check if index matches queried index 195 $diferences = array_merge(array_diff($columns, $indcolumns), array_diff($indcolumns, $columns)); 196 // If no differences, we have find the index 197 if (empty($diferences)) { 198 if ($returnall) { 199 $return[] = $indexname; 200 } else { 201 return $indexname; 202 } 203 } 204 } 205 206 if ($return and $returnall) { 207 return $return; 208 } 209 210 // Arriving here, index not found 211 return false; 212 } 213 214 /** 215 * Given one xmldb_index, check if it exists in DB (true/false). 216 * 217 * @param xmldb_table $xmldb_table The table to be searched. 218 * @param xmldb_index $xmldb_index The index to be searched for. 219 * @return boolean true id index exists, false otherwise. 220 */ 221 public function index_exists(xmldb_table $xmldb_table, xmldb_index $xmldb_index) { 222 if (!$this->table_exists($xmldb_table)) { 223 return false; 224 } 225 return ($this->find_index_name($xmldb_table, $xmldb_index) !== false); 226 } 227 228 /** 229 * This function IS NOT IMPLEMENTED. ONCE WE'LL BE USING RELATIONAL 230 * INTEGRITY IT WILL BECOME MORE USEFUL. FOR NOW, JUST CALCULATE "OFFICIAL" 231 * KEY NAMES WITHOUT ACCESSING TO DB AT ALL. 232 * Given one xmldb_key, the function returns the name of the key in DB (if exists) 233 * of false if it doesn't exist 234 * 235 * @param xmldb_table $xmldb_table The table to be searched. 236 * @param xmldb_key $xmldb_key The key to be searched. 237 * @return string key name if found 238 */ 239 public function find_key_name(xmldb_table $xmldb_table, xmldb_key $xmldb_key) { 240 241 $keycolumns = $xmldb_key->getFields(); 242 243 // Get list of keys in table 244 // first primaries (we aren't going to use this now, because the MetaPrimaryKeys is awful) 245 //TODO: To implement when we advance in relational integrity 246 // then uniques (note that Moodle, for now, shouldn't have any UNIQUE KEY for now, but unique indexes) 247 //TODO: To implement when we advance in relational integrity (note that AdoDB hasn't any MetaXXX for this. 248 // then foreign (note that Moodle, for now, shouldn't have any FOREIGN KEY for now, but indexes) 249 //TODO: To implement when we advance in relational integrity (note that AdoDB has one MetaForeignKeys() 250 //but it's far from perfect. 251 // TODO: To create the proper functions inside each generator to retrieve all the needed KEY info (name 252 // columns, reftable and refcolumns 253 254 // So all we do is to return the official name of the requested key without any confirmation!) 255 // One exception, hardcoded primary constraint names 256 if ($this->generator->primary_key_name && $xmldb_key->getType() == XMLDB_KEY_PRIMARY) { 257 return $this->generator->primary_key_name; 258 } else { 259 // Calculate the name suffix 260 switch ($xmldb_key->getType()) { 261 case XMLDB_KEY_PRIMARY: 262 $suffix = 'pk'; 263 break; 264 case XMLDB_KEY_UNIQUE: 265 $suffix = 'uk'; 266 break; 267 case XMLDB_KEY_FOREIGN_UNIQUE: 268 case XMLDB_KEY_FOREIGN: 269 $suffix = 'fk'; 270 break; 271 } 272 // And simply, return the official name 273 return $this->generator->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), $suffix); 274 } 275 } 276 277 /** 278 * This function will delete all tables found in XMLDB file from db 279 * 280 * @param string $file Full path to the XML file to be used. 281 * @return void 282 */ 283 public function delete_tables_from_xmldb_file($file) { 284 285 $xmldb_file = new xmldb_file($file); 286 287 if (!$xmldb_file->fileExists()) { 288 throw new ddl_exception('ddlxmlfileerror', null, 'File does not exist'); 289 } 290 291 $loaded = $xmldb_file->loadXMLStructure(); 292 $structure = $xmldb_file->getStructure(); 293 294 if (!$loaded || !$xmldb_file->isLoaded()) { 295 // Show info about the error if we can find it 296 if ($structure) { 297 if ($errors = $structure->getAllErrors()) { 298 throw new ddl_exception('ddlxmlfileerror', null, 'Errors found in XMLDB file: '. implode (', ', $errors)); 299 } 300 } 301 throw new ddl_exception('ddlxmlfileerror', null, 'not loaded??'); 302 } 303 304 if ($xmldb_tables = $structure->getTables()) { 305 // Delete in opposite order, this should help with foreign keys in the future. 306 $xmldb_tables = array_reverse($xmldb_tables); 307 foreach($xmldb_tables as $table) { 308 if ($this->table_exists($table)) { 309 $this->drop_table($table); 310 } 311 } 312 } 313 } 314 315 /** 316 * This function will drop the table passed as argument 317 * and all the associated objects (keys, indexes, constraints, sequences, triggers) 318 * will be dropped too. 319 * 320 * @param xmldb_table $xmldb_table Table object (just the name is mandatory). 321 * @return void 322 */ 323 public function drop_table(xmldb_table $xmldb_table) { 324 // Check table exists 325 if (!$this->table_exists($xmldb_table)) { 326 throw new ddl_table_missing_exception($xmldb_table->getName()); 327 } 328 329 if (!$sqlarr = $this->generator->getDropTableSQL($xmldb_table)) { 330 throw new ddl_exception('ddlunknownerror', null, 'table drop sql not generated'); 331 } 332 $this->execute_sql_arr($sqlarr, array($xmldb_table->getName())); 333 334 $this->generator->cleanup_after_drop($xmldb_table); 335 } 336 337 /** 338 * Load an install.xml file, checking that it exists, and that the structure is OK. 339 * @param string $file the full path to the XMLDB file. 340 * @return xmldb_file the loaded file. 341 */ 342 private function load_xmldb_file($file) { 343 $xmldb_file = new xmldb_file($file); 344 345 if (!$xmldb_file->fileExists()) { 346 throw new ddl_exception('ddlxmlfileerror', null, 'File does not exist'); 347 } 348 349 $loaded = $xmldb_file->loadXMLStructure(); 350 if (!$loaded || !$xmldb_file->isLoaded()) { 351 // Show info about the error if we can find it 352 if ($structure = $xmldb_file->getStructure()) { 353 if ($errors = $structure->getAllErrors()) { 354 throw new ddl_exception('ddlxmlfileerror', null, 'Errors found in XMLDB file: '. implode (', ', $errors)); 355 } 356 } 357 throw new ddl_exception('ddlxmlfileerror', null, 'not loaded??'); 358 } 359 360 return $xmldb_file; 361 } 362 363 /** 364 * This function will load one entire XMLDB file and call install_from_xmldb_structure. 365 * 366 * @param string $file full path to the XML file to be used 367 * @return void 368 */ 369 public function install_from_xmldb_file($file) { 370 $xmldb_file = $this->load_xmldb_file($file); 371 $xmldb_structure = $xmldb_file->getStructure(); 372 $this->install_from_xmldb_structure($xmldb_structure); 373 } 374 375 /** 376 * This function will load one entire XMLDB file and call install_from_xmldb_structure. 377 * 378 * @param string $file full path to the XML file to be used 379 * @param string $tablename the name of the table. 380 * @param bool $cachestructures boolean to decide if loaded xmldb structures can be safely cached 381 * useful for testunits loading the enormous main xml file hundred of times (100x) 382 */ 383 public function install_one_table_from_xmldb_file($file, $tablename, $cachestructures = false) { 384 385 static $xmldbstructurecache = array(); // To store cached structures 386 if (!empty($xmldbstructurecache) && array_key_exists($file, $xmldbstructurecache)) { 387 $xmldb_structure = $xmldbstructurecache[$file]; 388 } else { 389 $xmldb_file = $this->load_xmldb_file($file); 390 $xmldb_structure = $xmldb_file->getStructure(); 391 if ($cachestructures) { 392 $xmldbstructurecache[$file] = $xmldb_structure; 393 } 394 } 395 396 $targettable = $xmldb_structure->getTable($tablename); 397 if (is_null($targettable)) { 398 throw new ddl_exception('ddlunknowntable', null, 'The table ' . $tablename . ' is not defined in file ' . $file); 399 } 400 $targettable->setNext(NULL); 401 $targettable->setPrevious(NULL); 402 403 $tempstructure = new xmldb_structure('temp'); 404 $tempstructure->addTable($targettable); 405 $this->install_from_xmldb_structure($tempstructure); 406 } 407 408 /** 409 * This function will generate all the needed SQL statements, specific for each 410 * RDBMS type and, finally, it will execute all those statements against the DB. 411 * 412 * @param stdClass $xmldb_structure xmldb_structure object. 413 * @return void 414 */ 415 public function install_from_xmldb_structure($xmldb_structure) { 416 417 if (!$sqlarr = $this->generator->getCreateStructureSQL($xmldb_structure)) { 418 return; // nothing to do 419 } 420 421 $tablenames = array(); 422 foreach ($xmldb_structure as $xmldb_table) { 423 if ($xmldb_table instanceof xmldb_table) { 424 $tablenames[] = $xmldb_table->getName(); 425 } 426 } 427 $this->execute_sql_arr($sqlarr, $tablenames); 428 } 429 430 /** 431 * This function will create the table passed as argument with all its 432 * fields/keys/indexes/sequences, everything based in the XMLDB object 433 * 434 * @param xmldb_table $xmldb_table Table object (full specs are required). 435 * @return void 436 */ 437 public function create_table(xmldb_table $xmldb_table) { 438 // Check table doesn't exist 439 if ($this->table_exists($xmldb_table)) { 440 throw new ddl_exception('ddltablealreadyexists', $xmldb_table->getName()); 441 } 442 443 if (!$sqlarr = $this->generator->getCreateTableSQL($xmldb_table)) { 444 throw new ddl_exception('ddlunknownerror', null, 'table create sql not generated'); 445 } 446 $this->execute_sql_arr($sqlarr, array($xmldb_table->getName())); 447 } 448 449 /** 450 * This function will create the temporary table passed as argument with all its 451 * fields/keys/indexes/sequences, everything based in the XMLDB object 452 * 453 * If table already exists ddl_exception will be thrown, please make sure 454 * the table name does not collide with existing normal table! 455 * 456 * @param xmldb_table $xmldb_table Table object (full specs are required). 457 * @return void 458 */ 459 public function create_temp_table(xmldb_table $xmldb_table) { 460 461 // Check table doesn't exist 462 if ($this->table_exists($xmldb_table)) { 463 throw new ddl_exception('ddltablealreadyexists', $xmldb_table->getName()); 464 } 465 466 if (!$sqlarr = $this->generator->getCreateTempTableSQL($xmldb_table)) { 467 throw new ddl_exception('ddlunknownerror', null, 'temp table create sql not generated'); 468 } 469 $this->execute_sql_arr($sqlarr, array($xmldb_table->getName())); 470 } 471 472 /** 473 * This function will drop the temporary table passed as argument with all its 474 * fields/keys/indexes/sequences, everything based in the XMLDB object 475 * 476 * It is recommended to drop temp table when not used anymore. 477 * 478 * @deprecated since 2.3, use drop_table() for all table types 479 * @param xmldb_table $xmldb_table Table object. 480 * @return void 481 */ 482 public function drop_temp_table(xmldb_table $xmldb_table) { 483 debugging('database_manager::drop_temp_table() is deprecated, use database_manager::drop_table() instead'); 484 $this->drop_table($xmldb_table); 485 } 486 487 /** 488 * This function will rename the table passed as argument 489 * Before renaming the index, the function will check it exists 490 * 491 * @param xmldb_table $xmldb_table Table object (just the name is mandatory). 492 * @param string $newname New name of the index. 493 * @return void 494 */ 495 public function rename_table(xmldb_table $xmldb_table, $newname) { 496 // Check newname isn't empty 497 if (!$newname) { 498 throw new ddl_exception('ddlunknownerror', null, 'newname can not be empty'); 499 } 500 501 $check = new xmldb_table($newname); 502 503 // Check table already renamed 504 if (!$this->table_exists($xmldb_table)) { 505 if ($this->table_exists($check)) { 506 throw new ddl_exception('ddlunknownerror', null, 'table probably already renamed'); 507 } else { 508 throw new ddl_table_missing_exception($xmldb_table->getName()); 509 } 510 } 511 512 // Check new table doesn't exist 513 if ($this->table_exists($check)) { 514 throw new ddl_exception('ddltablealreadyexists', $check->getName(), 'can not rename table'); 515 } 516 517 if (!$sqlarr = $this->generator->getRenameTableSQL($xmldb_table, $newname)) { 518 throw new ddl_exception('ddlunknownerror', null, 'table rename sql not generated'); 519 } 520 521 $this->execute_sql_arr($sqlarr); 522 } 523 524 /** 525 * This function will add the field to the table passed as arguments 526 * 527 * @param xmldb_table $xmldb_table Table object (just the name is mandatory). 528 * @param xmldb_field $xmldb_field Index object (full specs are required). 529 * @return void 530 */ 531 public function add_field(xmldb_table $xmldb_table, xmldb_field $xmldb_field) { 532 // Check the field doesn't exist 533 if ($this->field_exists($xmldb_table, $xmldb_field)) { 534 throw new ddl_exception('ddlfieldalreadyexists', $xmldb_field->getName()); 535 } 536 537 // If NOT NULL and no default given (we ask the generator about the 538 // *real* default that will be used) check the table is empty 539 if ($xmldb_field->getNotNull() && $this->generator->getDefaultValue($xmldb_field) === NULL && $this->mdb->count_records($xmldb_table->getName())) { 540 throw new ddl_exception('ddlunknownerror', null, 'Field ' . $xmldb_table->getName() . '->' . $xmldb_field->getName() . 541 ' cannot be added. Not null fields added to non empty tables require default value. Create skipped'); 542 } 543 544 if (!$sqlarr = $this->generator->getAddFieldSQL($xmldb_table, $xmldb_field)) { 545 throw new ddl_exception('ddlunknownerror', null, 'addfield sql not generated'); 546 } 547 $this->execute_sql_arr($sqlarr, array($xmldb_table->getName())); 548 } 549 550 /** 551 * This function will drop the field from the table passed as arguments 552 * 553 * @param xmldb_table $xmldb_table Table object (just the name is mandatory). 554 * @param xmldb_field $xmldb_field Index object (full specs are required). 555 * @return void 556 */ 557 public function drop_field(xmldb_table $xmldb_table, xmldb_field $xmldb_field) { 558 if (!$this->table_exists($xmldb_table)) { 559 throw new ddl_table_missing_exception($xmldb_table->getName()); 560 } 561 // Check the field exists 562 if (!$this->field_exists($xmldb_table, $xmldb_field)) { 563 throw new ddl_field_missing_exception($xmldb_field->getName(), $xmldb_table->getName()); 564 } 565 // Check for dependencies in the DB before performing any action 566 $this->check_field_dependencies($xmldb_table, $xmldb_field); 567 568 if (!$sqlarr = $this->generator->getDropFieldSQL($xmldb_table, $xmldb_field)) { 569 throw new ddl_exception('ddlunknownerror', null, 'drop_field sql not generated'); 570 } 571 572 $this->execute_sql_arr($sqlarr, array($xmldb_table->getName())); 573 } 574 575 /** 576 * This function will change the type of the field in the table passed as arguments 577 * 578 * @param xmldb_table $xmldb_table Table object (just the name is mandatory). 579 * @param xmldb_field $xmldb_field Index object (full specs are required). 580 * @return void 581 */ 582 public function change_field_type(xmldb_table $xmldb_table, xmldb_field $xmldb_field) { 583 if (!$this->table_exists($xmldb_table)) { 584 throw new ddl_table_missing_exception($xmldb_table->getName()); 585 } 586 // Check the field exists 587 if (!$this->field_exists($xmldb_table, $xmldb_field)) { 588 throw new ddl_field_missing_exception($xmldb_field->getName(), $xmldb_table->getName()); 589 } 590 // Check for dependencies in the DB before performing any action 591 $this->check_field_dependencies($xmldb_table, $xmldb_field); 592 593 if (!$sqlarr = $this->generator->getAlterFieldSQL($xmldb_table, $xmldb_field)) { 594 return; // probably nothing to do 595 } 596 597 $this->execute_sql_arr($sqlarr, array($xmldb_table->getName())); 598 } 599 600 /** 601 * This function will change the precision of the field in the table passed as arguments 602 * 603 * @param xmldb_table $xmldb_table Table object (just the name is mandatory). 604 * @param xmldb_field $xmldb_field Index object (full specs are required). 605 * @return void 606 */ 607 public function change_field_precision(xmldb_table $xmldb_table, xmldb_field $xmldb_field) { 608 // Just a wrapper over change_field_type. Does exactly the same processing 609 $this->change_field_type($xmldb_table, $xmldb_field); 610 } 611 612 /** 613 * This function will change the unsigned/signed of the field in the table passed as arguments 614 * 615 * @deprecated since 2.3, only singed numbers are allowed now, migration is automatic 616 * @param xmldb_table $xmldb_table Table object (just the name is mandatory). 617 * @param xmldb_field $xmldb_field Field object (full specs are required). 618 * @return void 619 */ 620 public function change_field_unsigned(xmldb_table $xmldb_table, xmldb_field $xmldb_field) { 621 debugging('All unsigned numbers are converted to signed automatically during Moodle upgrade.'); 622 $this->change_field_type($xmldb_table, $xmldb_field); 623 } 624 625 /** 626 * This function will change the nullability of the field in the table passed as arguments 627 * 628 * @param xmldb_table $xmldb_table Table object (just the name is mandatory). 629 * @param xmldb_field $xmldb_field Index object (full specs are required). 630 * @return void 631 */ 632 public function change_field_notnull(xmldb_table $xmldb_table, xmldb_field $xmldb_field) { 633 // Just a wrapper over change_field_type. Does exactly the same processing 634 $this->change_field_type($xmldb_table, $xmldb_field); 635 } 636 637 /** 638 * This function will change the default of the field in the table passed as arguments 639 * One null value in the default field means delete the default 640 * 641 * @param xmldb_table $xmldb_table Table object (just the name is mandatory). 642 * @param xmldb_field $xmldb_field Index object (full specs are required). 643 * @return void 644 */ 645 public function change_field_default(xmldb_table $xmldb_table, xmldb_field $xmldb_field) { 646 if (!$this->table_exists($xmldb_table)) { 647 throw new ddl_table_missing_exception($xmldb_table->getName()); 648 } 649 // Check the field exists 650 if (!$this->field_exists($xmldb_table, $xmldb_field)) { 651 throw new ddl_field_missing_exception($xmldb_field->getName(), $xmldb_table->getName()); 652 } 653 // Check for dependencies in the DB before performing any action 654 $this->check_field_dependencies($xmldb_table, $xmldb_field); 655 656 if (!$sqlarr = $this->generator->getModifyDefaultSQL($xmldb_table, $xmldb_field)) { 657 return; //Empty array = nothing to do = no error 658 } 659 660 $this->execute_sql_arr($sqlarr, array($xmldb_table->getName())); 661 } 662 663 /** 664 * This function will rename the field in the table passed as arguments 665 * Before renaming the field, the function will check it exists 666 * 667 * @param xmldb_table $xmldb_table Table object (just the name is mandatory). 668 * @param xmldb_field $xmldb_field Index object (full specs are required). 669 * @param string $newname New name of the field. 670 * @return void 671 */ 672 public function rename_field(xmldb_table $xmldb_table, xmldb_field $xmldb_field, $newname) { 673 if (empty($newname)) { 674 throw new ddl_exception('ddlunknownerror', null, 'newname can not be empty'); 675 } 676 677 if (!$this->table_exists($xmldb_table)) { 678 throw new ddl_table_missing_exception($xmldb_table->getName()); 679 } 680 681 // Check the field exists 682 if (!$this->field_exists($xmldb_table, $xmldb_field)) { 683 throw new ddl_field_missing_exception($xmldb_field->getName(), $xmldb_table->getName()); 684 } 685 686 // Check we have included full field specs 687 if (!$xmldb_field->getType()) { 688 throw new ddl_exception('ddlunknownerror', null, 689 'Field ' . $xmldb_table->getName() . '->' . $xmldb_field->getName() . 690 ' must contain full specs. Rename skipped'); 691 } 692 693 // Check field isn't id. Renaming over that field is not allowed 694 if ($xmldb_field->getName() == 'id') { 695 throw new ddl_exception('ddlunknownerror', null, 696 'Field ' . $xmldb_table->getName() . '->' . $xmldb_field->getName() . 697 ' cannot be renamed. Rename skipped'); 698 } 699 700 if (!$sqlarr = $this->generator->getRenameFieldSQL($xmldb_table, $xmldb_field, $newname)) { 701 return; //Empty array = nothing to do = no error 702 } 703 704 $this->execute_sql_arr($sqlarr, array($xmldb_table->getName())); 705 } 706 707 /** 708 * This function will check, for the given table and field, if there there is any dependency 709 * preventing the field to be modified. It's used by all the public methods that perform any 710 * DDL change on fields, throwing one ddl_dependency_exception if dependencies are found. 711 * 712 * @param xmldb_table $xmldb_table Table object (just the name is mandatory). 713 * @param xmldb_field $xmldb_field Index object (full specs are required). 714 * @return void 715 * @throws ddl_dependency_exception|ddl_field_missing_exception|ddl_table_missing_exception if dependency not met. 716 */ 717 private function check_field_dependencies(xmldb_table $xmldb_table, xmldb_field $xmldb_field) { 718 719 // Check the table exists 720 if (!$this->table_exists($xmldb_table)) { 721 throw new ddl_table_missing_exception($xmldb_table->getName()); 722 } 723 724 // Check the field exists 725 if (!$this->field_exists($xmldb_table, $xmldb_field)) { 726 throw new ddl_field_missing_exception($xmldb_field->getName(), $xmldb_table->getName()); 727 } 728 729 // Check the field isn't in use by any index in the table 730 if ($indexes = $this->mdb->get_indexes($xmldb_table->getName(), false)) { 731 foreach ($indexes as $indexname => $index) { 732 $columns = $index['columns']; 733 if (in_array($xmldb_field->getName(), $columns)) { 734 throw new ddl_dependency_exception('column', $xmldb_table->getName() . '->' . $xmldb_field->getName(), 735 'index', $indexname . ' (' . implode(', ', $columns) . ')'); 736 } 737 } 738 } 739 } 740 741 /** 742 * This function will create the key in the table passed as arguments 743 * 744 * @param xmldb_table $xmldb_table Table object (just the name is mandatory). 745 * @param xmldb_key $xmldb_key Index object (full specs are required). 746 * @return void 747 */ 748 public function add_key(xmldb_table $xmldb_table, xmldb_key $xmldb_key) { 749 750 if ($xmldb_key->getType() == XMLDB_KEY_PRIMARY) { // Prevent PRIMARY to be added (only in create table, being serious :-P) 751 throw new ddl_exception('ddlunknownerror', null, 'Primary Keys can be added at table create time only'); 752 } 753 754 if (!$sqlarr = $this->generator->getAddKeySQL($xmldb_table, $xmldb_key)) { 755 return; //Empty array = nothing to do = no error 756 } 757 758 $this->execute_sql_arr($sqlarr, array($xmldb_table->getName())); 759 } 760 761 /** 762 * This function will drop the key in the table passed as arguments 763 * 764 * @param xmldb_table $xmldb_table Table object (just the name is mandatory). 765 * @param xmldb_key $xmldb_key Key object (full specs are required). 766 * @return void 767 */ 768 public function drop_key(xmldb_table $xmldb_table, xmldb_key $xmldb_key) { 769 if ($xmldb_key->getType() == XMLDB_KEY_PRIMARY) { // Prevent PRIMARY to be dropped (only in drop table, being serious :-P) 770 throw new ddl_exception('ddlunknownerror', null, 'Primary Keys can be deleted at table drop time only'); 771 } 772 773 if (!$sqlarr = $this->generator->getDropKeySQL($xmldb_table, $xmldb_key)) { 774 return; //Empty array = nothing to do = no error 775 } 776 777 $this->execute_sql_arr($sqlarr, array($xmldb_table->getName())); 778 } 779 780 /** 781 * This function will rename the key in the table passed as arguments 782 * Experimental. Shouldn't be used at all in normal installation/upgrade! 783 * 784 * @param xmldb_table $xmldb_table Table object (just the name is mandatory). 785 * @param xmldb_key $xmldb_key key object (full specs are required). 786 * @param string $newname New name of the key. 787 * @return void 788 */ 789 public function rename_key(xmldb_table $xmldb_table, xmldb_key $xmldb_key, $newname) { 790 debugging('rename_key() is one experimental feature. You must not use it in production!', DEBUG_DEVELOPER); 791 792 // Check newname isn't empty 793 if (!$newname) { 794 throw new ddl_exception('ddlunknownerror', null, 'newname can not be empty'); 795 } 796 797 if (!$sqlarr = $this->generator->getRenameKeySQL($xmldb_table, $xmldb_key, $newname)) { 798 throw new ddl_exception('ddlunknownerror', null, 'Some DBs do not support key renaming (MySQL, PostgreSQL, MsSQL). Rename skipped'); 799 } 800 801 $this->execute_sql_arr($sqlarr, array($xmldb_table->getName())); 802 } 803 804 /** 805 * This function will create the index in the table passed as arguments 806 * Before creating the index, the function will check it doesn't exists 807 * 808 * @param xmldb_table $xmldb_table Table object (just the name is mandatory). 809 * @param xmldb_index $xmldb_intex Index object (full specs are required). 810 * @return void 811 */ 812 public function add_index($xmldb_table, $xmldb_intex) { 813 if (!$this->table_exists($xmldb_table)) { 814 throw new ddl_table_missing_exception($xmldb_table->getName()); 815 } 816 817 // Check index doesn't exist 818 if ($this->index_exists($xmldb_table, $xmldb_intex)) { 819 throw new ddl_exception('ddlunknownerror', null, 820 'Index ' . $xmldb_table->getName() . '->' . $xmldb_intex->getName() . 821 ' already exists. Create skipped'); 822 } 823 824 if (!$sqlarr = $this->generator->getAddIndexSQL($xmldb_table, $xmldb_intex)) { 825 throw new ddl_exception('ddlunknownerror', null, 'add_index sql not generated'); 826 } 827 828 try { 829 $this->execute_sql_arr($sqlarr, array($xmldb_table->getName())); 830 } catch (ddl_change_structure_exception $e) { 831 // There could be a problem with the index length related to the row format of the table. 832 // If we are using utf8mb4 and the row format is 'compact' or 'redundant' then we need to change it over to 833 // 'compressed' or 'dynamic'. 834 if (method_exists($this->mdb, 'convert_table_row_format')) { 835 $this->mdb->convert_table_row_format($xmldb_table->getName()); 836 $this->execute_sql_arr($sqlarr, array($xmldb_table->getName())); 837 } else { 838 // It's some other problem that we are currently not handling. 839 throw $e; 840 } 841 } 842 } 843 844 /** 845 * This function will drop the index in the table passed as arguments 846 * Before dropping the index, the function will check it exists 847 * 848 * @param xmldb_table $xmldb_table Table object (just the name is mandatory). 849 * @param xmldb_index $xmldb_intex Index object (full specs are required). 850 * @return void 851 */ 852 public function drop_index($xmldb_table, $xmldb_intex) { 853 if (!$this->table_exists($xmldb_table)) { 854 throw new ddl_table_missing_exception($xmldb_table->getName()); 855 } 856 857 // Check index exists 858 if (!$this->index_exists($xmldb_table, $xmldb_intex)) { 859 throw new ddl_exception('ddlunknownerror', null, 860 'Index ' . $xmldb_table->getName() . '->' . $xmldb_intex->getName() . 861 ' does not exist. Drop skipped'); 862 } 863 864 if (!$sqlarr = $this->generator->getDropIndexSQL($xmldb_table, $xmldb_intex)) { 865 throw new ddl_exception('ddlunknownerror', null, 'drop_index sql not generated'); 866 } 867 868 $this->execute_sql_arr($sqlarr, array($xmldb_table->getName())); 869 } 870 871 /** 872 * This function will rename the index in the table passed as arguments 873 * Before renaming the index, the function will check it exists 874 * Experimental. Shouldn't be used at all! 875 * 876 * @param xmldb_table $xmldb_table Table object (just the name is mandatory). 877 * @param xmldb_index $xmldb_intex Index object (full specs are required). 878 * @param string $newname New name of the index. 879 * @return void 880 */ 881 public function rename_index($xmldb_table, $xmldb_intex, $newname) { 882 debugging('rename_index() is one experimental feature. You must not use it in production!', DEBUG_DEVELOPER); 883 884 // Check newname isn't empty 885 if (!$newname) { 886 throw new ddl_exception('ddlunknownerror', null, 'newname can not be empty'); 887 } 888 889 // Check index exists 890 if (!$this->index_exists($xmldb_table, $xmldb_intex)) { 891 throw new ddl_exception('ddlunknownerror', null, 892 'Index ' . $xmldb_table->getName() . '->' . $xmldb_intex->getName() . 893 ' does not exist. Rename skipped'); 894 } 895 896 if (!$sqlarr = $this->generator->getRenameIndexSQL($xmldb_table, $xmldb_intex, $newname)) { 897 throw new ddl_exception('ddlunknownerror', null, 'Some DBs do not support index renaming (MySQL). Rename skipped'); 898 } 899 900 $this->execute_sql_arr($sqlarr, array($xmldb_table->getName())); 901 } 902 903 /** 904 * Get the list of install.xml files. 905 * 906 * @return array 907 */ 908 public function get_install_xml_files(): array { 909 global $CFG; 910 require_once($CFG->libdir.'/adminlib.php'); 911 912 $files = []; 913 $dbdirs = get_db_directories(); 914 foreach ($dbdirs as $dbdir) { 915 $filename = "{$dbdir}/install.xml"; 916 if (file_exists($filename)) { 917 $files[] = $filename; 918 } 919 } 920 921 return $files; 922 } 923 924 /** 925 * Reads the install.xml files for Moodle core and modules and returns an array of 926 * xmldb_structure object with xmldb_table from these files. 927 * @return xmldb_structure schema from install.xml files 928 */ 929 public function get_install_xml_schema() { 930 global $CFG; 931 require_once($CFG->libdir.'/adminlib.php'); 932 933 $schema = new xmldb_structure('export'); 934 $schema->setVersion($CFG->version); 935 936 foreach ($this->get_install_xml_files() as $filename) { 937 $xmldb_file = new xmldb_file($filename); 938 if (!$xmldb_file->loadXMLStructure()) { 939 continue; 940 } 941 $structure = $xmldb_file->getStructure(); 942 $tables = $structure->getTables(); 943 foreach ($tables as $table) { 944 $table->setPrevious(null); 945 $table->setNext(null); 946 $schema->addTable($table); 947 } 948 } 949 return $schema; 950 } 951 952 /** 953 * Checks the database schema against a schema specified by an xmldb_structure object 954 * @param xmldb_structure $schema export schema describing all known tables 955 * @param array $options 956 * @return array keyed by table name with array of difference messages as values 957 */ 958 public function check_database_schema(xmldb_structure $schema, array $options = null) { 959 $alloptions = array( 960 'extratables' => true, 961 'missingtables' => true, 962 'extracolumns' => true, 963 'missingcolumns' => true, 964 'changedcolumns' => true, 965 'missingindexes' => true, 966 'extraindexes' => true 967 ); 968 969 $typesmap = array( 970 'I' => XMLDB_TYPE_INTEGER, 971 'R' => XMLDB_TYPE_INTEGER, 972 'N' => XMLDB_TYPE_NUMBER, 973 'F' => XMLDB_TYPE_NUMBER, // Nobody should be using floats! 974 'C' => XMLDB_TYPE_CHAR, 975 'X' => XMLDB_TYPE_TEXT, 976 'B' => XMLDB_TYPE_BINARY, 977 'T' => XMLDB_TYPE_TIMESTAMP, 978 'D' => XMLDB_TYPE_DATETIME, 979 ); 980 981 $options = (array)$options; 982 $options = array_merge($alloptions, $options); 983 984 // Note: the error descriptions are not supposed to be localised, 985 // it is intended for developers and skilled admins only. 986 $errors = array(); 987 988 /** @var string[] $dbtables */ 989 $dbtables = $this->mdb->get_tables(false); 990 /** @var xmldb_table[] $tables */ 991 $tables = $schema->getTables(); 992 993 foreach ($tables as $table) { 994 $tablename = $table->getName(); 995 996 if ($options['missingtables']) { 997 // Missing tables are a fatal problem. 998 if (empty($dbtables[$tablename])) { 999 $errors[$tablename][] = "table is missing"; 1000 continue; 1001 } 1002 } 1003 1004 /** @var database_column_info[] $dbfields */ 1005 $dbfields = $this->mdb->get_columns($tablename, false); 1006 $dbindexes = $this->mdb->get_indexes($tablename); 1007 /** @var xmldb_field[] $fields */ 1008 $fields = $table->getFields(); 1009 1010 foreach ($fields as $field) { 1011 $fieldname = $field->getName(); 1012 if (empty($dbfields[$fieldname])) { 1013 if ($options['missingcolumns']) { 1014 // Missing columns are a fatal problem. 1015 $errors[$tablename][] = "column '$fieldname' is missing"; 1016 } 1017 } else if ($options['changedcolumns']) { 1018 $dbfield = $dbfields[$fieldname]; 1019 1020 if (!isset($typesmap[$dbfield->meta_type])) { 1021 $errors[$tablename][] = "column '$fieldname' has unsupported type '$dbfield->meta_type'"; 1022 } else { 1023 $dbtype = $typesmap[$dbfield->meta_type]; 1024 $type = $field->getType(); 1025 if ($type == XMLDB_TYPE_FLOAT) { 1026 $type = XMLDB_TYPE_NUMBER; 1027 } 1028 if ($type != $dbtype) { 1029 if ($expected = array_search($type, $typesmap)) { 1030 $errors[$tablename][] = "column '$fieldname' has incorrect type '$dbfield->meta_type', expected '$expected'"; 1031 } else { 1032 $errors[$tablename][] = "column '$fieldname' has incorrect type '$dbfield->meta_type'"; 1033 } 1034 } else { 1035 if ($field->getNotNull() != $dbfield->not_null) { 1036 if ($field->getNotNull()) { 1037 $errors[$tablename][] = "column '$fieldname' should be NOT NULL ($dbfield->meta_type)"; 1038 } else { 1039 $errors[$tablename][] = "column '$fieldname' should allow NULL ($dbfield->meta_type)"; 1040 } 1041 } 1042 if ($dbtype == XMLDB_TYPE_TEXT) { 1043 // No length check necessary - there is one size only now. 1044 1045 } else if ($dbtype == XMLDB_TYPE_NUMBER) { 1046 if ($field->getType() == XMLDB_TYPE_FLOAT) { 1047 // Do not use floats in any new code, they are deprecated in XMLDB editor! 1048 1049 } else if ($field->getLength() != $dbfield->max_length or $field->getDecimals() != $dbfield->scale) { 1050 $size = "({$field->getLength()},{$field->getDecimals()})"; 1051 $dbsize = "($dbfield->max_length,$dbfield->scale)"; 1052 $errors[$tablename][] = "column '$fieldname' size is $dbsize, expected $size ($dbfield->meta_type)"; 1053 } 1054 1055 } else if ($dbtype == XMLDB_TYPE_CHAR) { 1056 // This is not critical, but they should ideally match. 1057 if ($field->getLength() != $dbfield->max_length) { 1058 $errors[$tablename][] = "column '$fieldname' length is $dbfield->max_length, expected {$field->getLength()} ($dbfield->meta_type)"; 1059 } 1060 1061 } else if ($dbtype == XMLDB_TYPE_INTEGER) { 1062 // Integers may be bigger in some DBs. 1063 $length = $field->getLength(); 1064 if ($length > 18) { 1065 // Integers are not supposed to be bigger than 18. 1066 $length = 18; 1067 } 1068 if ($length > $dbfield->max_length) { 1069 $errors[$tablename][] = "column '$fieldname' length is $dbfield->max_length, expected at least {$field->getLength()} ($dbfield->meta_type)"; 1070 } 1071 1072 } else if ($dbtype == XMLDB_TYPE_BINARY) { 1073 // Ignore binary types. 1074 continue; 1075 1076 } else if ($dbtype == XMLDB_TYPE_TIMESTAMP) { 1077 $errors[$tablename][] = "column '$fieldname' is a timestamp, this type is not supported ($dbfield->meta_type)"; 1078 continue; 1079 1080 } else if ($dbtype == XMLDB_TYPE_DATETIME) { 1081 $errors[$tablename][] = "column '$fieldname' is a datetime, this type is not supported ($dbfield->meta_type)"; 1082 continue; 1083 1084 } else { 1085 // Report all other unsupported types as problems. 1086 $errors[$tablename][] = "column '$fieldname' has unknown type ($dbfield->meta_type)"; 1087 continue; 1088 } 1089 1090 // Note: The empty string defaults are a bit messy... 1091 if ($field->getDefault() != $dbfield->default_value) { 1092 $default = is_null($field->getDefault()) ? 'NULL' : $field->getDefault(); 1093 $dbdefault = is_null($dbfield->default_value) ? 'NULL' : $dbfield->default_value; 1094 $errors[$tablename][] = "column '$fieldname' has default '$dbdefault', expected '$default' ($dbfield->meta_type)"; 1095 } 1096 } 1097 } 1098 } 1099 unset($dbfields[$fieldname]); 1100 } 1101 1102 // Check for missing indexes/keys. 1103 if ($options['missingindexes']) { 1104 // Check the foreign keys. 1105 if ($keys = $table->getKeys()) { 1106 foreach ($keys as $key) { 1107 // Primary keys are skipped. 1108 if ($key->getType() == XMLDB_KEY_PRIMARY) { 1109 continue; 1110 } 1111 1112 $keyname = $key->getName(); 1113 1114 // Create the interim index. 1115 $index = new xmldb_index('anyname'); 1116 $index->setFields($key->getFields()); 1117 switch ($key->getType()) { 1118 case XMLDB_KEY_UNIQUE: 1119 case XMLDB_KEY_FOREIGN_UNIQUE: 1120 $index->setUnique(true); 1121 break; 1122 case XMLDB_KEY_FOREIGN: 1123 $index->setUnique(false); 1124 break; 1125 } 1126 if (!$this->index_exists($table, $index)) { 1127 $errors[$tablename][] = $this->get_missing_index_error($table, $index, $keyname); 1128 } else { 1129 $this->remove_index_from_dbindex($dbindexes, $index); 1130 } 1131 } 1132 } 1133 1134 // Check the indexes. 1135 if ($indexes = $table->getIndexes()) { 1136 foreach ($indexes as $index) { 1137 if (!$this->index_exists($table, $index)) { 1138 $errors[$tablename][] = $this->get_missing_index_error($table, $index, $index->getName()); 1139 } else { 1140 $this->remove_index_from_dbindex($dbindexes, $index); 1141 } 1142 } 1143 } 1144 } 1145 1146 // Check if we should show the extra indexes. 1147 if ($options['extraindexes']) { 1148 // Hack - skip for table 'search_simpledb_index' as this plugin adds indexes dynamically on install 1149 // which are not included in install.xml. See search/engine/simpledb/db/install.php. 1150 if ($tablename != 'search_simpledb_index') { 1151 foreach ($dbindexes as $indexname => $index) { 1152 $errors[$tablename][] = "Unexpected index '$indexname'."; 1153 } 1154 } 1155 } 1156 1157 // Check for extra columns (indicates unsupported hacks) - modify install.xml if you want to pass validation. 1158 foreach ($dbfields as $fieldname => $dbfield) { 1159 if ($options['extracolumns']) { 1160 $errors[$tablename][] = "column '$fieldname' is not expected ($dbfield->meta_type)"; 1161 } 1162 } 1163 unset($dbtables[$tablename]); 1164 } 1165 1166 if ($options['extratables']) { 1167 // Look for unsupported tables - local custom tables should be in /local/xxxx/db/install.xml file. 1168 // If there is no prefix, we can not say if table is ours, sorry. 1169 if ($this->generator->prefix !== '') { 1170 foreach ($dbtables as $tablename => $unused) { 1171 if (strpos($tablename, 'pma_') === 0) { 1172 // Ignore phpmyadmin tables. 1173 continue; 1174 } 1175 if (strpos($tablename, 'test') === 0) { 1176 // Legacy simple test db tables need to be eventually removed, 1177 // report them as problems! 1178 $errors[$tablename][] = "table is not expected (it may be a leftover after Simpletest unit tests)"; 1179 } else { 1180 $errors[$tablename][] = "table is not expected"; 1181 } 1182 } 1183 } 1184 } 1185 1186 return $errors; 1187 } 1188 1189 /** 1190 * Returns a string describing the missing index error. 1191 * 1192 * @param xmldb_table $table 1193 * @param xmldb_index $index 1194 * @param string $indexname 1195 * @return string 1196 */ 1197 private function get_missing_index_error(xmldb_table $table, xmldb_index $index, string $indexname): string { 1198 $sqlarr = $this->generator->getAddIndexSQL($table, $index); 1199 $sqlarr = $this->generator->getEndedStatements($sqlarr); 1200 $sqltoadd = reset($sqlarr); 1201 1202 return "Missing index '" . $indexname . "' " . "(" . $index->readableInfo() . "). \n" . $sqltoadd; 1203 } 1204 1205 /** 1206 * Removes an index from the array $dbindexes if it is found. 1207 * 1208 * @param array $dbindexes 1209 * @param xmldb_index $index 1210 */ 1211 private function remove_index_from_dbindex(array &$dbindexes, xmldb_index $index) { 1212 foreach ($dbindexes as $key => $dbindex) { 1213 if ($dbindex['columns'] == $index->getFields()) { 1214 unset($dbindexes[$key]); 1215 } 1216 } 1217 } 1218} 1219