lib/ddllib.php

   1 <?php // $Id$
   2
   3 ///////////////////////////////////////////////////////////////////////////
   4 //                                                                       //
   5 // NOTICE OF COPYRIGHT                                                   //
   6 //                                                                       //
   7 // Moodle - Modular Object-Oriented Dynamic Learning Environment         //
   8 //          http://moodle.com                                            //
   9 //                                                                       //
  10 // Copyright (C) 1999 onwards Martin Dougiamas        http://dougiamas.com  //
  11 //           (C) 2001-3001 Eloy Lafuente (stronk7) http://contiento.com  //
  12 //                                                                       //
  13 // This program is free software; you can redistribute it and/or modify  //
  14 // it under the terms of the GNU General Public License as published by  //
  15 // the Free Software Foundation; either version 2 of the License, or     //
  16 // (at your option) any later version.                                   //
  17 //                                                                       //
  18 // This program is distributed in the hope that it will be useful,       //
  19 // but WITHOUT ANY WARRANTY; without even the implied warranty of        //
  20 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         //
  21 // GNU General Public License for more details:                          //
  22 //                                                                       //
  23 //          http://www.gnu.org/copyleft/gpl.html                         //
  24 //                                                                       //
  25 ///////////////////////////////////////////////////////////////////////////
  26
  27 // This library includes all the required functions used to handle the DB
  28 // structure (DDL) independently of the underlying RDBMS in use. All the functions
  29 // rely on the XMLDBDriver classes to be able to generate the correct SQL
  30 // syntax needed by each DB.
  31 //
  32 // To define any structure to be created we'll use the schema defined
  33 // by the XMLDB classes, for tables, fields, indexes, keys and other
  34 // statements instead of direct handling of SQL sentences.
  35 //
  36 // This library should be used, exclusively, by the installation and
  37 // upgrade process of Moodle.
  38 //
  39 // For further documentation, visit http://docs.moodle.org/en/DDL_functions
  40
  41 /// Add required XMLDB constants
  42     require_once($CFG->libdir . '/xmldb/classes/XMLDBConstants.php');
  43
  44 /// Add main XMLDB Generator
  45     require_once($CFG->libdir . '/xmldb/classes/generators/XMLDBGenerator.class.php');
  46
  47 /// Add required XMLDB DB classes
  48     require_once($CFG->libdir . '/xmldb/classes/XMLDBObject.class.php');
  49     require_once($CFG->libdir . '/xmldb/classes/XMLDBFile.class.php');
  50     require_once($CFG->libdir . '/xmldb/classes/XMLDBStructure.class.php');
  51     require_once($CFG->libdir . '/xmldb/classes/XMLDBTable.class.php');
  52     require_once($CFG->libdir . '/xmldb/classes/XMLDBField.class.php');
  53     require_once($CFG->libdir . '/xmldb/classes/XMLDBKey.class.php');
  54     require_once($CFG->libdir . '/xmldb/classes/XMLDBIndex.class.php');
  55     require_once($CFG->libdir . '/xmldb/classes/XMLDBStatement.class.php');
  56
  57 /// Based on $CFG->dbtype, add the proper generator class
  58     if (!file_exists($CFG->libdir . '/xmldb/classes/generators/' . $CFG->dbtype . '/' . $CFG->dbtype . '.class.php')) {
  59         error ('DB Type: ' . $CFG->dbtype . ' not supported by XMLDB');
  60     }
  61     require_once($CFG->libdir . '/xmldb/classes/generators/' . $CFG->dbtype . '/' . $CFG->dbtype . '.class.php');
  62
  63
  64 /// Add other libraries
  65     require_once($CFG->libdir . '/xmlize.php');
  66 /**
  67  * Add a new field to a table, or modify an existing one (if oldfield is defined).
  68  *
  69  * WARNING: This function is deprecated and will be removed in future versions.
  70  * Please use XMLDB (see http://docs.moodle.org/en/Development:DDL_functions ).
  71  *
  72  * Warning: Please be careful on primary keys, as this function will eat auto_increments
  73  *
  74  * @uses $CFG
  75  * @uses $db
  76  * @param string $table the name of the table to modify. (Without the prefix.)
  77  * @param string $oldfield If changing an existing column, the name of that column.
  78  * @param string $field The name of the column at the end of the operation.
  79  * @param string $type The type of the column at the end of the operation. TEXT, VARCHAR, CHAR, INTEGER, REAL, or TINYINT
  80  * @param string $size The size of that column type. As in VARCHAR($size), or INTEGER($size).
  81  * @param string $signed For numeric column types, whether that column is 'signed' or 'unsigned'.
  82  * @param string $default The new default value for the column.
  83  * @param string $null 'not null', or '' to allow nulls.
  84  * @param string $after Which column to insert this one after. Not supported on Postgres.
  85  *
  86  * @return boolean Wheter the operation succeeded.
  87  */
  88 function table_column($table, $oldfield, $field, $type='integer', $size='10',
  89                       $signed='unsigned', $default='0', $null='not null', $after='') {
  90     global $CFG, $db, $empty_rs_cache;
  91
  92     if (!empty($empty_rs_cache[$table])) {  // Clear the recordset cache because it's out of date
  93         unset($empty_rs_cache[$table]);
  94     }
  95
  96     switch (strtolower($CFG->dbtype)) {
  97
  98         case 'mysql':
  99         case 'mysqlt':
 100
 101             switch (strtolower($type)) {
 102                 case 'text':
 103                     $type = 'TEXT';
 104                     $signed = '';
 105                     break;
 106                 case 'integer':
 107                     $type = 'INTEGER('. $size .')';
 108                     break;
 109                 case 'varchar':
 110                     $type = 'VARCHAR('. $size .')';
 111                     $signed = '';
 112                     break;
 113                 case 'char':
 114                     $type = 'CHAR('. $size .')';
 115                     $signed = '';
 116                     break;
 117             }
 118
 119             if (!empty($oldfield)) {
 120                 $operation = 'CHANGE '. $oldfield .' '. $field;
 121             } else {
 122                 $operation = 'ADD '. $field;
 123             }
 124
 125             $default = 'DEFAULT \''. $default .'\'';
 126
 127             if (!empty($after)) {
 128                 $after = 'AFTER `'. $after .'`';
 129             }
 130
 131             return execute_sql('ALTER TABLE '. $CFG->prefix . $table .' '. $operation .' '. $type .' '. $signed .' '. $default .' '. $null .' '. $after);
 132
 133         case 'postgres7':        // From Petri Asikainen
 134             //Check db-version
 135             $dbinfo = $db->ServerInfo();
 136             $dbver = substr($dbinfo['version'],0,3);
 137
 138             //to prevent conflicts with reserved words
 139             $realfield = '"'. $field .'"';
 140             $field = '"'. $field .'_alter_column_tmp"';
 141             $oldfield = '"'. $oldfield .'"';
 142
 143             switch (strtolower($type)) {
 144                 case 'tinyint':
 145                 case 'integer':
 146                     if ($size <= 4) {
 147                         $type = 'INT2';
 148                     }
 149                     if ($size <= 10) {
 150                         $type = 'INT';
 151                     }
 152                     if  ($size > 10) {
 153                         $type = 'INT8';
 154                     }
 155                     break;
 156                 case 'varchar':
 157                     $type = 'VARCHAR('. $size .')';
 158                     break;
 159                 case 'char':
 160                     $type = 'CHAR('. $size .')';
 161                     $signed = '';
 162                     break;
 163             }
 164
 165             $default = '\''. $default .'\'';
 166
 167             //After is not implemented in postgesql
 168             //if (!empty($after)) {
 169             //    $after = "AFTER '$after'";
 170             //}
 171
 172             //Use transactions
 173             execute_sql('BEGIN');
 174
 175             //Always use temporary column
 176             execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ADD COLUMN '. $field .' '. $type);
 177             //Add default values
 178             execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .'='. $default);
 179
 180
 181             if ($dbver >= '7.3') {
 182                 // modifying 'not null' is posible before 7.3
 183                 //update default values to table
 184                 if (strtoupper($null) == 'NOT NULL') {
 185                     execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .'='. $default .' WHERE '. $field .' IS NULL');
 186                     execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $null);
 187                 } else {
 188                     execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' DROP NOT NULL');
 189                 }
 190             }
 191
 192             execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET DEFAULT '. $default);
 193
 194             if ( $oldfield != '""' ) {
 195
 196                 // We are changing the type of a column. This may require doing some casts...
 197                 $casting = '';
 198                 $oldtype = column_type($table, $oldfield);
 199                 $newtype = column_type($table, $field);
 200
 201                 // Do we need a cast?
 202                 if($newtype == 'N' && $oldtype == 'C') {
 203                     $casting = 'CAST(CAST('.$oldfield.' AS TEXT) AS REAL)';
 204                 }
 205                 else if($newtype == 'I' && $oldtype == 'C') {
 206                     $casting = 'CAST(CAST('.$oldfield.' AS TEXT) AS INTEGER)';
 207                 }
 208                 else {
 209                     $casting = $oldfield;
 210                 }
 211
 212                 // Run the update query, casting as necessary
 213                 execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .' = '. $casting);
 214                 execute_sql('ALTER TABLE  '. $CFG->prefix . $table .' DROP COLUMN '. $oldfield);
 215             }
 216
 217             execute_sql('ALTER TABLE '. $CFG->prefix . $table .' RENAME COLUMN '. $field .' TO '. $realfield);
 218
 219             return execute_sql('COMMIT');
 220
 221         default:
 222             switch (strtolower($type)) {
 223                 case 'integer':
 224                     $type = 'INTEGER';
 225                     break;
 226                 case 'varchar':
 227                     $type = 'VARCHAR';
 228                     break;
 229             }
 230
 231             $default = 'DEFAULT \''. $default .'\'';
 232
 233             if (!empty($after)) {
 234                 $after = 'AFTER '. $after;
 235             }
 236
 237             if (!empty($oldfield)) {
 238                 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' RENAME COLUMN '. $oldfield .' '. $field);
 239             } else {
 240                 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ADD COLUMN '. $field .' '. $type);
 241             }
 242
 243             execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $null);
 244             return execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $default);
 245     }
 246 }
 247
 248 /**
 249  * Given one XMLDBTable, check if it exists in DB (true/false)
 250  *
 251  * @param XMLDBTable table to be searched for
 252  * @return boolean true/false
 253  */
 254 function table_exists($table) {
 255
 256     global $CFG, $db;
 257
 258     $exists = true;
 259
 260 /// Do this function silenty (to avoid output in install/upgrade process)
 261     $olddbdebug = $db->debug;
 262     $db->debug = false;
 263
 264 /// Load the needed generator
 265     $classname = 'XMLDB' . $CFG->dbtype;
 266     $generator = new $classname();
 267     $generator->setPrefix($CFG->prefix);
 268 /// Calculate the name of the table
 269     $tablename = $generator->getTableName($table, false);
 270
 271 /// Search such tablename in DB
 272     $metatables = $db->MetaTables();
 273     $metatables = array_flip($metatables);
 274     $metatables = array_change_key_case($metatables, CASE_LOWER);
 275     if (!array_key_exists($tablename,  $metatables)) {
 276         $exists = false;
 277     }
 278
 279 /// Re-set original debug
 280     $db->debug = $olddbdebug;
 281
 282     return $exists;
 283 }
 284
 285 /**
 286  * Given one XMLDBField, check if it exists in DB (true/false)
 287  *
 288  * @uses, $db
 289  * @param XMLDBTable the table
 290  * @param XMLDBField the field to be searched for
 291  * @return boolean true/false
 292  */
 293 function field_exists($table, $field) {
 294
 295     global $CFG, $db;
 296
 297     $exists = true;
 298
 299 /// Do this function silenty (to avoid output in install/upgrade process)
 300     $olddbdebug = $db->debug;
 301     $db->debug = false;
 302
 303 /// Check the table exists
 304     if (!table_exists($table)) {
 305         $db->debug = $olddbdebug; //Re-set original $db->debug
 306         return false;
 307     }
 308
 309 /// Load the needed generator
 310     $classname = 'XMLDB' . $CFG->dbtype;
 311     $generator = new $classname();
 312     $generator->setPrefix($CFG->prefix);
 313 /// Calculate the name of the table
 314     $tablename = $generator->getTableName($table, false);
 315
 316 /// Get list of fields in table
 317     $fields = null;
 318     if ($fields = $db->MetaColumns($tablename)) {
 319         $fields = array_change_key_case($fields, CASE_LOWER);
 320     }
 321
 322     if (!array_key_exists($field->getName(),  $fields)) {
 323         $exists = false;
 324     }
 325
 326 /// Re-set original debug
 327     $db->debug = $olddbdebug;
 328
 329     return $exists;
 330 }
 331
 332 /**
 333  * Given one XMLDBIndex, check if it exists in DB (true/false)
 334  *
 335  * @uses, $db
 336  * @param XMLDBTable the table
 337  * @param XMLDBIndex the index to be searched for
 338  * @return boolean true/false
 339  */
 340 function index_exists($table, $index) {
 341
 342     global $CFG, $db;
 343
 344     $exists = true;
 345
 346 /// Do this function silenty (to avoid output in install/upgrade process)
 347     $olddbdebug = $db->debug;
 348     $db->debug = false;
 349
 350 /// Wrap over find_index_name to see if the index exists
 351     if (!find_index_name($table, $index)) {
 352         $exists = false;
 353     }
 354
 355 /// Re-set original debug
 356     $db->debug = $olddbdebug;
 357
 358     return $exists;
 359 }
 360
 361 /**
 362  * Given one XMLDBField, check if it has a check constraint in DB
 363  *
 364  * @uses, $db
 365  * @param XMLDBTable the table
 366  * @param XMLDBField the field to be searched for any existing constraint
 367  * @return boolean true/false
 368  */
 369 function check_constraint_exists($table, $field) {
 370
 371     global $CFG, $db;
 372
 373     $exists = true;
 374
 375 /// Do this function silenty (to avoid output in install/upgrade process)
 376     $olddbdebug = $db->debug;
 377     $db->debug = false;
 378
 379 /// Wrap over find_check_constraint_name to see if the index exists
 380     if (!find_check_constraint_name($table, $field)) {
 381         $exists = false;
 382     }
 383
 384 /// Re-set original debug
 385     $db->debug = $olddbdebug;
 386
 387     return $exists;
 388 }
 389
 390 /**
 391  * This function IS NOT IMPLEMENTED. ONCE WE'LL BE USING RELATIONAL
 392  * INTEGRITY IT WILL BECOME MORE USEFUL. FOR NOW, JUST CALCULATE "OFFICIAL"
 393  * KEY NAMES WITHOUT ACCESSING TO DB AT ALL.
 394  * Given one XMLDBKey, the function returns the name of the key in DB (if exists)
 395  * of false if it doesn't exist
 396  *
 397  * @uses, $db
 398  * @param XMLDBTable the table to be searched
 399  * @param XMLDBKey the key to be searched
 400  * @return string key name of false
 401  */
 402 function find_key_name($table, $xmldb_key) {
 403
 404     global $CFG, $db;
 405
 406 /// Extract key columns
 407     $keycolumns = $xmldb_key->getFields();
 408
 409 /// Get list of keys in table
 410 /// first primaries (we aren't going to use this now, because the MetaPrimaryKeys is awful)
 411     ///TODO: To implement when we advance in relational integrity
 412 /// then uniques (note that Moodle, for now, shouldn't have any UNIQUE KEY for now, but unique indexes)
 413     ///TODO: To implement when we advance in relational integrity (note that AdoDB hasn't any MetaXXX for this.
 414 /// then foreign (note that Moodle, for now, shouldn't have any FOREIGN KEY for now, but indexes)
 415     ///TODO: To implement when we advance in relational integrity (note that AdoDB has one MetaForeignKeys()
 416     ///but it's far from perfect.
 417 /// TODO: To create the proper functions inside each generator to retrieve all the needed KEY info (name
 418 ///       columns, reftable and refcolumns
 419
 420 /// So all we do is to return the official name of the requested key without any confirmation!)
 421     $classname = 'XMLDB' . $CFG->dbtype;
 422     $generator = new $classname();
 423     $generator->setPrefix($CFG->prefix);
 424 /// One exception, harcoded primary constraint names
 425     if ($generator->primary_key_name && $xmldb_key->getType() == XMLDB_KEY_PRIMARY) {
 426         return $generator->primary_key_name;
 427     } else {
 428     /// Calculate the name suffix
 429         switch ($xmldb_key->getType()) {
 430             case XMLDB_KEY_PRIMARY:
 431                 $suffix = 'pk';
 432                 break;
 433             case XMLDB_KEY_UNIQUE:
 434                 $suffix = 'uk';
 435                 break;
 436             case XMLDB_KEY_FOREIGN_UNIQUE:
 437             case XMLDB_KEY_FOREIGN:
 438                 $suffix = 'fk';
 439                 break;
 440         }
 441     /// And simply, return the oficial name
 442         return $generator->getNameForObject($table->getName(), implode(', ', $xmldb_key->getFields()), $suffix);
 443     }
 444 }
 445
 446 /**
 447  * Given one XMLDBIndex, the function returns the name of the index in DB (if exists)
 448  * of false if it doesn't exist
 449  *
 450  * @uses, $db
 451  * @param XMLDBTable the table to be searched
 452  * @param XMLDBIndex the index to be searched
 453  * @return string index name of false
 454  */
 455 function find_index_name($table, $index) {
 456
 457     global $CFG, $db;
 458
 459 /// Do this function silenty (to avoid output in install/upgrade process)
 460     $olddbdebug = $db->debug;
 461     $db->debug = false;
 462
 463 /// Extract index columns
 464     $indcolumns = $index->getFields();
 465
 466 /// Check the table exists
 467     if (!table_exists($table)) {
 468         $db->debug = $olddbdebug; //Re-set original $db->debug
 469         return false;
 470     }
 471
 472 /// Load the needed generator
 473     $classname = 'XMLDB' . $CFG->dbtype;
 474     $generator = new $classname();
 475     $generator->setPrefix($CFG->prefix);
 476 /// Calculate the name of the table
 477     $tablename = $generator->getTableName($table, false);
 478
 479 /// Get list of indexes in table
 480     $indexes = null;
 481     if ($indexes = $db->MetaIndexes($tablename)) {
 482         $indexes = array_change_key_case($indexes, CASE_LOWER);
 483     }
 484
 485 /// Iterate over them looking for columns coincidence
 486     if ($indexes) {
 487         foreach ($indexes as $indexname => $index) {
 488             $columns = $index['columns'];
 489         /// Lower case column names
 490             $columns = array_flip($columns);
 491             $columns = array_change_key_case($columns, CASE_LOWER);
 492             $columns = array_flip($columns);
 493         /// Check if index matchs queried index
 494             $diferences = array_merge(array_diff($columns, $indcolumns), array_diff($indcolumns, $columns));
 495         /// If no diferences, we have find the index
 496             if (empty($diferences)) {
 497                 $db->debug = $olddbdebug; //Re-set original $db->debug
 498                 return $indexname;
 499             }
 500         }
 501     }
 502 /// Arriving here, index not found
 503     $db->debug = $olddbdebug; //Re-set original $db->debug
 504     return false;
 505 }
 506
 507 /**
 508  * Given one XMLDBField, the function returns the name of the check constraint in DB (if exists)
 509  * of false if it doesn't exist. Note that XMLDB limits the number of check constrainst per field
 510  * to 1 "enum-like" constraint. So, if more than one is returned, only the first one will be
 511  * retrieved by this funcion.
 512  *
 513  * @uses, $db
 514  * @param XMLDBTable the table to be searched
 515  * @param XMLDBField the field to be searched
 516  * @return string check consrtaint name or false
 517  */
 518 function find_check_constraint_name($table, $field) {
 519
 520     global $CFG, $db;
 521
 522 /// Do this function silenty (to avoid output in install/upgrade process)
 523     $olddbdebug = $db->debug;
 524     $db->debug = false;
 525
 526 /// Check the table exists
 527     if (!table_exists($table)) {
 528         $db->debug = $olddbdebug; //Re-set original $db->debug
 529         return false;
 530     }
 531
 532 /// Check the field exists
 533     if (!field_exists($table, $field)) {
 534         $db->debug = $olddbdebug; //Re-set original $db->debug
 535         return false;
 536     }
 537
 538 /// Load the needed generator
 539     $classname = 'XMLDB' . $CFG->dbtype;
 540     $generator = new $classname();
 541     $generator->setPrefix($CFG->prefix);
 542 /// Calculate the name of the table
 543     $tablename = $generator->getTableName($table, false);
 544
 545 /// Get list of check_constraints in table/field
 546     $checks = null;
 547     if ($objchecks = $generator->getCheckConstraintsFromDB($table, $field)) {
 548     /// Get only the 1st element. Shouldn't be more than 1 under XMLDB
 549         $objcheck = array_shift($objchecks);
 550         if ($objcheck) {
 551             $checks = strtolower($objcheck->name);
 552         }
 553     }
 554
 555 /// Arriving here, check not found
 556     $db->debug = $olddbdebug; //Re-set original $db->debug
 557     return $checks;
 558 }
 559
 560 /**
 561  * Given one XMLDBTable, the function returns the name of its sequence in DB (if exists)
 562  * of false if it doesn't exist
 563  *
 564  * @param XMLDBTable the table to be searched
 565  * @return string sequence name of false
 566  */
 567 function find_sequence_name($table) {
 568
 569     global $CFG, $db;
 570
 571     $sequencename = false;
 572
 573 /// Do this function silenty (to avoid output in install/upgrade process)
 574     $olddbdebug = $db->debug;
 575     $db->debug = false;
 576
 577     if (strtolower(get_class($table)) != 'xmldbtable') {
 578         $db->debug = $olddbdebug; //Re-set original $db->debug
 579         return false;
 580     }
 581
 582 /// Check table exists
 583     if (!table_exists($table)) {
 584         debugging('Table ' . $table->getName() .
 585                   ' does not exist. Sequence not found', DEBUG_DEVELOPER);
 586         $db->debug = $olddbdebug; //Re-set original $db->debug
 587         return false; //Table doesn't exist, nothing to do
 588     }
 589
 590     $sequencename = $table->getSequenceFromDB($CFG->dbtype, $CFG->prefix);
 591
 592     $db->debug = $olddbdebug; //Re-set original $db->debug
 593     return $sequencename;
 594 }
 595
 596 /**
 597  * This function will load one entire XMLDB file, generating all the needed
 598  * SQL statements, specific for each RDBMS ($CFG->dbtype) and, finally, it
 599  * will execute all those statements against the DB.
 600  *
 601  * @uses $CFG, $db
 602  * @param $file full path to the XML file to be used
 603  * @return boolean (true on success, false on error)
 604  */
 605 function install_from_xmldb_file($file) {
 606
 607     global $CFG, $db;
 608
 609     $status = true;
 610
 611
 612     $xmldb_file = new XMLDBFile($file);
 613
 614     if (!$xmldb_file->fileExists()) {
 615         return false;
 616     }
 617
 618     $loaded = $xmldb_file->loadXMLStructure();
 619     if (!$loaded || !$xmldb_file->isLoaded()) {
 620     /// Show info about the error if we can find it
 621         if ($structure =& $xmldb_file->getStructure()) {
 622             if ($errors = $structure->getAllErrors()) {
 623                 notify('Errors found in XMLDB file: '. implode (', ', $errors));
 624             }
 625         }
 626         return false;
 627     }
 628
 629     $structure = $xmldb_file->getStructure();
 630
 631     if (!$sqlarr = $structure->getCreateStructureSQL($CFG->dbtype, $CFG->prefix, false)) {
 632         return true; //Empty array = nothing to do = no error
 633     }
 634
 635     return execute_sql_arr($sqlarr);
 636 }
 637
 638 /**
 639  * This function will all tables found in XMLDB file from db
 640  *
 641  * @uses $CFG, $db
 642  * @param $file full path to the XML file to be used
 643  * @param $feedback
 644  * @return boolean (true on success, false on error)
 645  */
 646 function delete_tables_from_xmldb_file($file, $feedback=true ) {
 647
 648     global $CFG, $db;
 649
 650     $status = true;
 651
 652
 653     $xmldb_file = new XMLDBFile($file);
 654
 655     if (!$xmldb_file->fileExists()) {
 656         return false;
 657     }
 658
 659     $loaded    = $xmldb_file->loadXMLStructure();
 660     $structure =& $xmldb_file->getStructure();
 661
 662     if (!$loaded || !$xmldb_file->isLoaded()) {
 663     /// Show info about the error if we can find it
 664         if ($feedback and $structure) {
 665             if ($errors = $structure->getAllErrors()) {
 666                 notify('Errors found in XMLDB file: '. implode (', ', $errors));
 667             }
 668         }
 669         return false;
 670     }
 671
 672     if ($tables = $structure->getTables()) {
 673         foreach($tables as $table) {
 674             if (table_exists($table)) {
 675                 drop_table($table, true, $feedback);
 676             }
 677         }
 678     }
 679
 680     return true;
 681 }
 682
 683 /**
 684  * Delete all plugin tables
 685  * @name string name of plugin, used as table prefix
 686  * @file string path to install.xml file
 687  * @feedback boolean
 688  */
 689 function drop_plugin_tables($name, $file, $feedback=true) {
 690     global $CFG, $db;
 691
 692     // first try normal delete
 693     if (delete_tables_from_xmldb_file($file, $feedback)) {
 694         return true;
 695     }
 696
 697     // then try to find all tables that start with name and are not in any xml file
 698     $used_tables = get_used_table_names();
 699
 700     $tables = $db->MetaTables();
 701     /// Iterate over, fixing id fields as necessary
 702     foreach ($tables as $table) {
 703         if (strlen($CFG->prefix)) {
 704             if (strpos($table, $CFG->prefix) !== 0) {
 705                 continue;
 706             }
 707             $table = substr($table, strlen($CFG->prefix));
 708         }
 709         $table = strtolower($table);
 710         if (strpos($table, $name) !== 0) {
 711             continue;
 712         }
 713         if (in_array($table, $used_tables)) {
 714             continue;
 715         }
 716
 717         // found orphan table --> delete it
 718         $table = new XMLDBTable($table);
 719         if (table_exists($table)) {
 720             drop_table($table, true, $feedback);
 721         }
 722     }
 723
 724     return true;
 725 }
 726
 727 /**
 728  * Returns names of all known tables == tables that moodle knowns about.
 729  * @return array of lowercase table names
 730  */
 731 function get_used_table_names() {
 732     $table_names = array();
 733     $dbdirs = get_db_directories();
 734
 735     foreach ($dbdirs as $dbdir) {
 736         $file = $dbdir.'/install.xml';
 737
 738         $xmldb_file = new XMLDBFile($file);
 739
 740         if (!$xmldb_file->fileExists()) {
 741             continue;
 742         }
 743
 744         $loaded    = $xmldb_file->loadXMLStructure();
 745         $structure =& $xmldb_file->getStructure();
 746
 747         if ($loaded and $tables = $structure->getTables()) {
 748             foreach($tables as $table) {
 749                 $table_names[] = strtolower($table->name);
 750             }
 751         }
 752     }
 753
 754     return $table_names;
 755 }
 756
 757 /**
 758  * Returns list of all directories where we expect install.xml files
 759  * @return array of paths
 760  */
 761 function get_db_directories() {
 762     global $CFG;
 763
 764     $dbdirs = array();
 765
 766 /// First, the main one (lib/db)
 767     $dbdirs[] = $CFG->libdir.'/db';
 768
 769 /// Now, activity modules (mod/xxx/db)
 770     if ($plugins = get_list_of_plugins('mod')) {
 771         foreach ($plugins as $plugin) {
 772             $dbdirs[] = $CFG->dirroot.'/mod/'.$plugin.'/db';
 773         }
 774     }
 775
 776 /// Now, assignment submodules (mod/assignment/type/xxx/db)
 777     if ($plugins = get_list_of_plugins('mod/assignment/type')) {
 778         foreach ($plugins as $plugin) {
 779             $dbdirs[] = $CFG->dirroot.'/mod/assignment/type/'.$plugin.'/db';
 780         }
 781     }
 782
 783 /// Now, question types (question/type/xxx/db)
 784     if ($plugins = get_list_of_plugins('question/type')) {
 785         foreach ($plugins as $plugin) {
 786             $dbdirs[] = $CFG->dirroot.'/question/type/'.$plugin.'/db';
 787         }
 788     }
 789
 790 /// Now, backup/restore stuff (backup/db)
 791     $dbdirs[] = $CFG->dirroot.'/backup/db';
 792
 793 /// Now, block system stuff (blocks/db)
 794     $dbdirs[] = $CFG->dirroot.'/blocks/db';
 795
 796 /// Now, blocks (blocks/xxx/db)
 797     if ($plugins = get_list_of_plugins('blocks', 'db')) {
 798         foreach ($plugins as $plugin) {
 799             $dbdirs[] = $CFG->dirroot.'/blocks/'.$plugin.'/db';
 800         }
 801     }
 802
 803 /// Now, course formats (course/format/xxx/db)
 804     if ($plugins = get_list_of_plugins('course/format', 'db')) {
 805         foreach ($plugins as $plugin) {
 806             $dbdirs[] = $CFG->dirroot.'/course/format/'.$plugin.'/db';
 807         }
 808     }
 809
 810 /// Now, enrolment plugins (enrol/xxx/db)
 811     if ($plugins = get_list_of_plugins('enrol', 'db')) {
 812         foreach ($plugins as $plugin) {
 813             $dbdirs[] = $CFG->dirroot.'/enrol/'.$plugin.'/db';
 814         }
 815     }
 816
 817 /// Now admin report plugins (admin/report/xxx/db)
 818     if ($plugins = get_list_of_plugins($CFG->admin.'/report', 'db')) {
 819         foreach ($plugins as $plugin) {
 820             $dbdirs[] = $CFG->dirroot.'/'.$CFG->admin.'/report/'.$plugin.'/db';
 821         }
 822     }
 823
 824 /// Local database changes, if the local folder exists.
 825     if (file_exists($CFG->dirroot . '/local')) {
 826         $dbdirs[] = $CFG->dirroot.'/local/db';
 827     }
 828
 829     return $dbdirs;
 830 }
 831
 832 /**
 833  * This function will create the table passed as argument with all its
 834  * fields/keys/indexes/sequences, everything based in the XMLDB object
 835  *
 836  * @uses $CFG, $db
 837  * @param XMLDBTable table object (full specs are required)
 838  * @param boolean continue to specify if must continue on error (true) or stop (false)
 839  * @param boolean feedback to specify to show status info (true) or not (false)
 840  * @return boolean true on success, false on error
 841  */
 842 function create_table($table, $continue=true, $feedback=true) {
 843
 844     global $CFG, $db;
 845
 846     $status = true;
 847
 848     if (strtolower(get_class($table)) != 'xmldbtable') {
 849         return false;
 850     }
 851
 852 /// Check table doesn't exist
 853     if (table_exists($table)) {
 854         debugging('Table ' . $table->getName() .
 855                   ' already exists. Create skipped', DEBUG_DEVELOPER);
 856         return true; //Table exists, nothing to do
 857     }
 858
 859     if(!$sqlarr = $table->getCreateTableSQL($CFG->dbtype, $CFG->prefix, false)) {
 860         return true; //Empty array = nothing to do = no error
 861     }
 862
 863     return execute_sql_arr($sqlarr, $continue, $feedback);
 864 }
 865
 866 /**
 867  * This function will drop the table passed as argument
 868  * and all the associated objects (keys, indexes, constaints, sequences, triggers)
 869  * will be dropped too.
 870  *
 871  * @uses $CFG, $db
 872  * @param XMLDBTable table object (just the name is mandatory)
 873  * @param boolean continue to specify if must continue on error (true) or stop (false)
 874  * @param boolean feedback to specify to show status info (true) or not (false)
 875  * @return boolean true on success, false on error
 876  */
 877 function drop_table($table, $continue=true, $feedback=true) {
 878
 879     global $CFG, $db;
 880
 881     $status = true;
 882
 883     if (strtolower(get_class($table)) != 'xmldbtable') {
 884         return false;
 885     }
 886
 887 /// Check table exists
 888     if (!table_exists($table)) {
 889         debugging('Table ' . $table->getName() .
 890                   ' does not exist. Delete skipped', DEBUG_DEVELOPER);
 891         return true; //Table don't exist, nothing to do
 892     }
 893
 894     if(!$sqlarr = $table->getDropTableSQL($CFG->dbtype, $CFG->prefix, false)) {
 895         return true; //Empty array = nothing to do = no error
 896     }
 897
 898     return execute_sql_arr($sqlarr, $continue, $feedback);
 899 }
 900
 901 /**
 902  * This function will create the temporary table passed as argument with all its
 903  * fields/keys/indexes/sequences, everything based in the XMLDB object
 904  *
 905  * TRUNCATE the table immediately after creation. A previous process using
 906  * the same persistent connection may have created the temp table and failed to
 907  * drop it. In that case, the table will exist, and create_temp_table() will
 908  * will succeed.
 909  *
 910  * NOTE: The return value is the tablename - some DBs (MSSQL at least) use special
 911  * names for temp tables.
 912  *
 913  * @uses $CFG, $db
 914  * @param XMLDBTable table object (full specs are required)
 915  * @param boolean continue to specify if must continue on error (true) or stop (false)
 916  * @param boolean feedback to specify to show status info (true) or not (false)
 917  * @return string tablename on success, false on error
 918  */
 919 function create_temp_table($table, $continue=true, $feedback=true) {
 920
 921     global $CFG, $db;
 922
 923     $status = true;
 924
 925     if (strtolower(get_class($table)) != 'xmldbtable') {
 926         return false;
 927     }
 928
 929
 930     $temporary = 'TEMPORARY';
 931     switch (strtolower($CFG->dbfamily)) {
 932         case 'mssql':
 933             // TODO: somehow change the name to have a #
 934             $temporary = '';
 935             break;
 936         case 'oracle':
 937             $temporary = 'GLOBAL TEMPORARY';
 938             break;
 939     }
 940
 941 /// Check table doesn't exist
 942     if (table_exists($table)) {
 943         debugging('Table ' . $table->getName() .
 944                   ' already exists. Create skipped', DEBUG_DEVELOPER);
 945         return $table->getName(); //Table exists, nothing to do
 946     }
 947
 948     if(!$sqlarr = $table->getCreateTableSQL($CFG->dbtype, $CFG->prefix, false)) {
 949         return $table->getName(); //Empty array = nothing to do = no error
 950     }
 951
 952     if (!empty($temporary)) {
 953         $sqlarr = preg_replace('/^CREATE/', "CREATE $temporary", $sqlarr);
 954     }
 955
 956     if (execute_sql_arr($sqlarr, $continue, $feedback)) {
 957         return $table->getName();
 958     } else {
 959         return false;
 960     }
 961 }
 962
 963 /**
 964  * This function will rename the table passed as argument
 965  * Before renaming the index, the function will check it exists
 966  *
 967  * @uses $CFG, $db
 968  * @param XMLDBTable table object (just the name is mandatory)
 969  * @param string new name of the index
 970  * @param boolean continue to specify if must continue on error (true) or stop (false)
 971  * @param boolean feedback to specify to show status info (true) or not (false)
 972  * @return boolean true on success, false on error
 973  */
 974 function rename_table($table, $newname, $continue=true, $feedback=true) {
 975
 976     global $CFG, $db;
 977
 978     $status = true;
 979
 980     if (strtolower(get_class($table)) != 'xmldbtable') {
 981         return false;
 982     }
 983
 984 /// Check table exists
 985     if (!table_exists($table)) {
 986         debugging('Table ' . $table->getName() .
 987                   ' does not exist. Rename skipped', DEBUG_DEVELOPER);
 988         return true; //Table doesn't exist, nothing to do
 989     }
 990
 991 /// Check new table doesn't exist
 992     $check = new XMLDBTable($newname);
 993     if (table_exists($check)) {
 994         debugging('Table ' . $check->getName() .
 995                   ' already exists. Rename skipped', DEBUG_DEVELOPER);
 996         return true; //Table exists, nothing to do
 997     }
 998
 999 /// Check newname isn't empty
1000     if (!$newname) {
1001         debugging('New name for table ' . $table->getName() .
1002                   ' is empty! Rename skipped', DEBUG_DEVELOPER);
1003         return true; //Table doesn't exist, nothing to do
1004     }
1005
1006     if(!$sqlarr = $table->getRenameTableSQL($CFG->dbtype, $CFG->prefix, $newname, false)) {
1007         return true; //Empty array = nothing to do = no error
1008     }
1009
1010     return execute_sql_arr($sqlarr, $continue, $feedback);
1011 }
1012
1013 /**
1014  * This function will add the field to the table passed as arguments
1015  *
1016  * @uses $CFG, $db
1017  * @param XMLDBTable table object (just the name is mandatory)
1018  * @param XMLDBField field object (full specs are required)
1019  * @param boolean continue to specify if must continue on error (true) or stop (false)
1020  * @param boolean feedback to specify to show status info (true) or not (false)
1021  * @return boolean true on success, false on error
1022  */
1023 function add_field($table, $field, $continue=true, $feedback=true) {
1024
1025     global $CFG, $db;
1026
1027     $status = true;
1028
1029     if (strtolower(get_class($table)) != 'xmldbtable') {
1030         return false;
1031     }
1032     if (strtolower(get_class($field)) != 'xmldbfield') {
1033         return false;
1034     }
1035
1036 /// Load the needed generator
1037     $classname = 'XMLDB' . $CFG->dbtype;
1038     $generator = new $classname();
1039     $generator->setPrefix($CFG->prefix);
1040
1041 /// Check the field doesn't exist
1042     if (field_exists($table, $field)) {
1043         debugging('Field ' . $table->getName() . '->' . $field->getName() .
1044                   ' already exists. Create skipped', DEBUG_DEVELOPER);
1045         return true;
1046     }
1047
1048 /// If NOT NULL and no default given (we ask the generator about the
1049 /// *real* default that will be used) check the table is empty
1050     if ($field->getNotNull() && $generator->getDefaultValue($field) === NULL && count_records($table->getName())) {
1051         debugging('Field ' . $table->getName() . '->' . $field->getName() .
1052                   ' cannot be added. Not null fields added to non empty tables require default value. Create skipped', DEBUG_DEVELOPER);
1053          return true;
1054     }
1055
1056     if(!$sqlarr = $table->getAddFieldSQL($CFG->dbtype, $CFG->prefix, $field, false)) {
1057         return true; //Empty array = nothing to do = no error
1058     }
1059
1060     return execute_sql_arr($sqlarr, $continue, $feedback);
1061 }
1062
1063 /**
1064  * This function will drop the field from the table passed as arguments
1065  *
1066  * @uses $CFG, $db
1067  * @param XMLDBTable table object (just the name is mandatory)
1068  * @param XMLDBField field object (just the name is mandatory)
1069  * @param boolean continue to specify if must continue on error (true) or stop (false)
1070  * @param boolean feedback to specify to show status info (true) or not (false)
1071  * @return boolean true on success, false on error
1072  */
1073 function drop_field($table, $field, $continue=true, $feedback=true) {
1074
1075     global $CFG, $db;
1076
1077     $status = true;
1078
1079     if (strtolower(get_class($table)) != 'xmldbtable') {
1080         return false;
1081     }
1082     if (strtolower(get_class($field)) != 'xmldbfield') {
1083         return false;
1084     }
1085
1086 /// Check the field exists
1087     if (!field_exists($table, $field)) {
1088         debugging('Field ' . $table->getName() . '->' . $field->getName() .
1089                   ' does not exist. Delete skipped', DEBUG_DEVELOPER);
1090         return true;
1091     }
1092
1093     if(!$sqlarr = $table->getDropFieldSQL($CFG->dbtype, $CFG->prefix, $field, false)) {
1094         return true; //Empty array = nothing to do = no error
1095     }
1096
1097     return execute_sql_arr($sqlarr, $continue, $feedback);
1098 }
1099
1100 /**
1101  * This function will change the type of the field in the table passed as arguments
1102  *
1103  * @uses $CFG, $db
1104  * @param XMLDBTable table object (just the name is mandatory)
1105  * @param XMLDBField field object (full specs are required)
1106  * @param boolean continue to specify if must continue on error (true) or stop (false)
1107  * @param boolean feedback to specify to show status info (true) or not (false)
1108  * @return boolean true on success, false on error
1109  */
1110 function change_field_type($table, $field, $continue=true, $feedback=true) {
1111
1112     global $CFG, $db;
1113
1114     $status = true;
1115
1116     if (strtolower(get_class($table)) != 'xmldbtable') {
1117         return false;
1118     }
1119     if (strtolower(get_class($field)) != 'xmldbfield') {
1120         return false;
1121     }
1122
1123     if(!$sqlarr = $table->getAlterFieldSQL($CFG->dbtype, $CFG->prefix, $field, false)) {
1124         return true; //Empty array = nothing to do = no error
1125     }
1126
1127     return execute_sql_arr($sqlarr, $continue, $feedback);
1128 }
1129
1130 /**
1131  * This function will change the precision of the field in the table passed as arguments
1132  *
1133  * @uses $CFG, $db
1134  * @param XMLDBTable table object (just the name is mandatory)
1135  * @param XMLDBField field object (full specs are required)
1136  * @param boolean continue to specify if must continue on error (true) or stop (false)
1137  * @param boolean feedback to specify to show status info (true) or not (false)
1138  * @return boolean true on success, false on error
1139  */
1140 function change_field_precision($table, $field, $continue=true, $feedback=true) {
1141
1142 /// Just a wrapper over change_field_type. Does exactly the same processing
1143     return change_field_type($table, $field, $continue, $feedback);
1144 }
1145
1146 /**
1147  * This function will change the unsigned/signed of the field in the table passed as arguments
1148  *
1149  * @uses $CFG, $db
1150  * @param XMLDBTable table object (just the name is mandatory)
1151  * @param XMLDBField field object (full specs are required)
1152  * @param boolean continue to specify if must continue on error (true) or stop (false)
1153  * @param boolean feedback to specify to show status info (true) or not (false)
1154  * @return boolean true on success, false on error
1155  */
1156 function change_field_unsigned($table, $field, $continue=true, $feedback=true) {
1157
1158 /// Just a wrapper over change_field_type. Does exactly the same processing
1159     return change_field_type($table, $field, $continue, $feedback);
1160 }
1161
1162 /**
1163  * This function will change the nullability of the field in the table passed as arguments
1164  *
1165  * @uses $CFG, $db
1166  * @param XMLDBTable table object (just the name is mandatory)
1167  * @param XMLDBField field object (full specs are required)
1168  * @param boolean continue to specify if must continue on error (true) or stop (false)
1169  * @param boolean feedback to specify to show status info (true) or not (false)
1170  * @return boolean true on success, false on error
1171  */
1172 function change_field_notnull($table, $field, $continue=true, $feedback=true) {
1173
1174 /// Just a wrapper over change_field_type. Does exactly the same processing
1175     return change_field_type($table, $field, $continue, $feedback);
1176 }
1177
1178 /**
1179  * This function will change the enum status of the field in the table passed as arguments
1180  *
1181  * @uses $CFG, $db
1182  * @param XMLDBTable table object (just the name is mandatory)
1183  * @param XMLDBField field object (full specs are required)
1184  * @param boolean continue to specify if must continue on error (true) or stop (false)
1185  * @param boolean feedback to specify to show status info (true) or not (false)
1186  * @return boolean true on success, false on error
1187  */
1188 function change_field_enum($table, $field, $continue=true, $feedback=true) {
1189
1190     global $CFG, $db;
1191
1192     $status = true;
1193
1194     if (strtolower(get_class($table)) != 'xmldbtable') {
1195         return false;
1196     }
1197     if (strtolower(get_class($field)) != 'xmldbfield') {
1198         return false;
1199     }
1200
1201 /// If enum is defined, we're going to create it, check it doesn't exist.
1202     if ($field->getEnum()) {
1203         if (check_constraint_exists($table, $field)) {
1204             debugging('Enum for ' . $table->getName() . '->' . $field->getName() .
1205                       ' already exists. Create skipped', DEBUG_DEVELOPER);
1206             return true; //Enum exists, nothing to do
1207         }
1208     } else { /// Else, we're going to drop it, check it exists
1209         if (!check_constraint_exists($table, $field)) {
1210             debugging('Enum for ' . $table->getName() . '->' . $field->getName() .
1211                       ' does not exist. Delete skipped', DEBUG_DEVELOPER);
1212             return true; //Enum doesn't exist, nothing to do
1213         }
1214     }
1215
1216     if(!$sqlarr = $table->getModifyEnumSQL($CFG->dbtype, $CFG->prefix, $field, false)) {
1217         return true; //Empty array = nothing to do = no error
1218     }
1219
1220     return execute_sql_arr($sqlarr, $continue, $feedback);
1221 }
1222 /**
1223  * This function will change the default of the field in the table passed as arguments
1224  * One null value in the default field means delete the default
1225  *
1226  * @uses $CFG, $db
1227  * @param XMLDBTable table object (just the name is mandatory)
1228  * @param XMLDBField field object (full specs are required)
1229  * @param boolean continue to specify if must continue on error (true) or stop (false)
1230  * @param boolean feedback to specify to show status info (true) or not (false)
1231  * @return boolean true on success, false on error
1232  */
1233 function change_field_default($table, $field, $continue=true, $feedback=true) {
1234
1235     global $CFG, $db;
1236
1237     $status = true;
1238
1239     if (strtolower(get_class($table)) != 'xmldbtable') {
1240         return false;
1241     }
1242     if (strtolower(get_class($field)) != 'xmldbfield') {
1243         return false;
1244     }
1245
1246     if(!$sqlarr = $table->getModifyDefaultSQL($CFG->dbtype, $CFG->prefix, $field, false)) {
1247         return true; //Empty array = nothing to do = no error
1248     }
1249
1250     return execute_sql_arr($sqlarr, $continue, $feedback);
1251 }
1252
1253 /**
1254  * This function will rename the field in the table passed as arguments
1255  * Before renaming the field, the function will check it exists
1256  *
1257  * @uses $CFG, $db
1258  * @param XMLDBTable table object (just the name is mandatory)
1259  * @param XMLDBField index object (full specs are required)
1260  * @param string new name of the field
1261  * @param boolean continue to specify if must continue on error (true) or stop (false)
1262  * @param boolean feedback to specify to show status info (true) or not (false)
1263  * @return boolean true on success, false on error
1264  */
1265 function rename_field($table, $field, $newname, $continue=true, $feedback=true) {
1266
1267     global $CFG, $db;
1268
1269     $status = true;
1270
1271     if (strtolower(get_class($table)) != 'xmldbtable') {
1272         return false;
1273     }
1274     if (strtolower(get_class($field)) != 'xmldbfield') {
1275         return false;
1276     }
1277
1278 /// Check we have included full field specs
1279     if (!$field->getType()) {
1280         debugging('Field ' . $table->getName() . '->' . $field->getName() .
1281                   ' must contain full specs. Rename skipped', DEBUG_DEVELOPER);
1282         return false;
1283     }
1284
1285 /// Check field isn't id. Renaming over that field is not allowed
1286     if ($field->getName() == 'id') {
1287         debugging('Field ' . $table->getName() . '->' . $field->getName() .
1288                   ' cannot be renamed. Rename skipped', DEBUG_DEVELOPER);
1289         return true; //Field is "id", nothing to do
1290     }
1291
1292 /// Check field exists
1293     if (!field_exists($table, $field)) {
1294         debugging('Field ' . $table->getName() . '->' . $field->getName() .
1295                   ' does not exist. Rename skipped', DEBUG_DEVELOPER);
1296         return true; //Field doesn't exist, nothing to do
1297     }
1298
1299 /// Check newname isn't empty
1300     if (!$newname) {
1301         debugging('New name for field ' . $table->getName() . '->' . $field->getName() .
1302                   ' is empty! Rename skipped', DEBUG_DEVELOPER);
1303         return true; //Field doesn't exist, nothing to do
1304     }
1305
1306     if(!$sqlarr = $table->getRenameFieldSQL($CFG->dbtype, $CFG->prefix, $field, $newname, false)) {
1307         return true; //Empty array = nothing to do = no error
1308     }
1309
1310     return execute_sql_arr($sqlarr, $continue, $feedback);
1311 }
1312
1313 /**
1314  * This function will create the key in the table passed as arguments
1315  *
1316  * @uses $CFG, $db
1317  * @param XMLDBTable table object (just the name is mandatory)
1318  * @param XMLDBKey index object (full specs are required)
1319  * @param boolean continue to specify if must continue on error (true) or stop (false)
1320  * @param boolean feedback to specify to show status info (true) or not (false)
1321  * @return boolean true on success, false on error
1322  */
1323 function add_key($table, $key, $continue=true, $feedback=true) {
1324
1325     global $CFG, $db;
1326
1327     $status = true;
1328
1329     if (strtolower(get_class($table)) != 'xmldbtable') {
1330         return false;
1331     }
1332     if (strtolower(get_class($key)) != 'xmldbkey') {
1333         return false;
1334     }
1335     if ($key->getType() == XMLDB_KEY_PRIMARY) { // Prevent PRIMARY to be added (only in create table, being serious  :-P)
1336         debugging('Primary Keys can be added at table create time only', DEBUG_DEVELOPER);
1337         return true;
1338     }
1339
1340     if(!$sqlarr = $table->getAddKeySQL($CFG->dbtype, $CFG->prefix, $key, false)) {
1341         return true; //Empty array = nothing to do = no error
1342     }
1343
1344     return execute_sql_arr($sqlarr, $continue, $feedback);
1345 }
1346
1347 /**
1348  * This function will drop the key in the table passed as arguments
1349  *
1350  * @uses $CFG, $db
1351  * @param XMLDBTable table object (just the name is mandatory)
1352  * @param XMLDBKey key object (full specs are required)
1353  * @param boolean continue to specify if must continue on error (true) or stop (false)
1354  * @param boolean feedback to specify to show status info (true) or not (false)
1355  * @return boolean true on success, false on error
1356  */
1357 function drop_key($table, $key, $continue=true, $feedback=true) {
1358
1359     global $CFG, $db;
1360
1361     $status = true;
1362
1363     if (strtolower(get_class($table)) != 'xmldbtable') {
1364         return false;
1365     }
1366     if (strtolower(get_class($key)) != 'xmldbkey') {
1367         return false;
1368     }
1369     if ($key->getType() == XMLDB_KEY_PRIMARY) { // Prevent PRIMARY to be dropped (only in drop table, being serious  :-P)
1370         debugging('Primary Keys can be deleted at table drop time only', DEBUG_DEVELOPER);
1371         return true;
1372     }
1373
1374     if(!$sqlarr = $table->getDropKeySQL($CFG->dbtype, $CFG->prefix, $key, false)) {
1375         return true; //Empty array = nothing to do = no error
1376     }
1377
1378     return execute_sql_arr($sqlarr, $continue, $feedback);
1379 }
1380
1381 /**
1382  * This function will rename the key in the table passed as arguments
1383  * Experimental. Shouldn't be used at all in normal installation/upgrade!
1384  *
1385  * @uses $CFG, $db
1386  * @param XMLDBTable table object (just the name is mandatory)
1387  * @param XMLDBKey key object (full specs are required)
1388  * @param string new name of the key
1389  * @param boolean continue to specify if must continue on error (true) or stop (false)
1390  * @param boolean feedback to specify to show status info (true) or not (false)
1391  * @return boolean true on success, false on error
1392  */
1393 function rename_key($table, $key, $newname, $continue=true, $feedback=true) {
1394
1395     global $CFG, $db;
1396
1397     debugging('rename_key() is one experimental feature. You must not use it in production!', DEBUG_DEVELOPER);
1398
1399     $status = true;
1400
1401     if (strtolower(get_class($table)) != 'xmldbtable') {
1402         return false;
1403     }
1404     if (strtolower(get_class($key)) != 'xmldbkey') {
1405         return false;
1406     }
1407
1408 /// Check newname isn't empty
1409     if (!$newname) {
1410         debugging('New name for key ' . $table->getName() . '->' . $key->getName() .
1411                   ' is empty! Rename skipped', DEBUG_DEVELOPER);
1412         return true; //Key doesn't exist, nothing to do
1413     }
1414
1415     if(!$sqlarr = $table->getRenameKeySQL($CFG->dbtype, $CFG->prefix, $key, $newname, false)) {
1416         debugging('Some DBs do not support key renaming (MySQL, PostgreSQL, MsSQL). Rename skipped', DEBUG_DEVELOPER);
1417         return true; //Empty array = nothing to do = no error
1418     }
1419
1420     return execute_sql_arr($sqlarr, $continue, $feedback);
1421 }
1422
1423 /**
1424  * This function will create the index in the table passed as arguments
1425  * Before creating the index, the function will check it doesn't exists
1426  *
1427  * @uses $CFG, $db
1428  * @param XMLDBTable table object (just the name is mandatory)
1429  * @param XMLDBIndex index object (full specs are required)
1430  * @param boolean continue to specify if must continue on error (true) or stop (false)
1431  * @param boolean feedback to specify to show status info (true) or not (false)
1432  * @return boolean true on success, false on error
1433  */
1434 function add_index($table, $index, $continue=true, $feedback=true) {
1435
1436     global $CFG, $db;
1437
1438     $status = true;
1439
1440     if (strtolower(get_class($table)) != 'xmldbtable') {
1441         return false;
1442     }
1443     if (strtolower(get_class($index)) != 'xmldbindex') {
1444         return false;
1445     }
1446
1447 /// Check index doesn't exist
1448     if (index_exists($table, $index)) {
1449         debugging('Index ' . $table->getName() . '->' . $index->getName() .
1450                   ' already exists. Create skipped', DEBUG_DEVELOPER);
1451         return true; //Index exists, nothing to do
1452     }
1453
1454     if(!$sqlarr = $table->getAddIndexSQL($CFG->dbtype, $CFG->prefix, $index, false)) {
1455         return true; //Empty array = nothing to do = no error
1456     }
1457
1458     return execute_sql_arr($sqlarr, $continue, $feedback);
1459 }
1460
1461 /**
1462  * This function will drop the index in the table passed as arguments
1463  * Before dropping the index, the function will check it exists
1464  *
1465  * @uses $CFG, $db
1466  * @param XMLDBTable table object (just the name is mandatory)
1467  * @param XMLDBIndex index object (full specs are required)
1468  * @param boolean continue to specify if must continue on error (true) or stop (false)
1469  * @param boolean feedback to specify to show status info (true) or not (false)
1470  * @return boolean true on success, false on error
1471  */
1472 function drop_index($table, $index, $continue=true, $feedback=true) {
1473
1474     global $CFG, $db;
1475
1476     $status = true;
1477
1478     if (strtolower(get_class($table)) != 'xmldbtable') {
1479         return false;
1480     }
1481     if (strtolower(get_class($index)) != 'xmldbindex') {
1482         return false;
1483     }
1484
1485 /// Check index exists
1486     if (!index_exists($table, $index)) {
1487         debugging('Index ' . $table->getName() . '->' . $index->getName() .
1488                   ' does not exist. Delete skipped', DEBUG_DEVELOPER);
1489         return true; //Index doesn't exist, nothing to do
1490     }
1491
1492     if(!$sqlarr = $table->getDropIndexSQL($CFG->dbtype, $CFG->prefix, $index, false)) {
1493         return true; //Empty array = nothing to do = no error
1494     }
1495
1496     return execute_sql_arr($sqlarr, $continue, $feedback);
1497 }
1498
1499 /**
1500  * This function will rename the index in the table passed as arguments
1501  * Before renaming the index, the function will check it exists
1502  * Experimental. Shouldn't be used at all!
1503  *
1504  * @uses $CFG, $db
1505  * @param XMLDBTable table object (just the name is mandatory)
1506  * @param XMLDBIndex index object (full specs are required)
1507  * @param string new name of the index
1508  * @param boolean continue to specify if must continue on error (true) or stop (false)
1509  * @param boolean feedback to specify to show status info (true) or not (false)
1510  * @return boolean true on success, false on error
1511  */
1512 function rename_index($table, $index, $newname, $continue=true, $feedback=true) {
1513
1514     global $CFG, $db;
1515
1516     debugging('rename_index() is one experimental feature. You must not use it in production!', DEBUG_DEVELOPER);
1517
1518     $status = true;
1519
1520     if (strtolower(get_class($table)) != 'xmldbtable') {
1521         return false;
1522     }
1523     if (strtolower(get_class($index)) != 'xmldbindex') {
1524         return false;
1525     }
1526
1527 /// Check index exists
1528     if (!index_exists($table, $index)) {
1529         debugging('Index ' . $table->getName() . '->' . $index->getName() .
1530                   ' does not exist. Rename skipped', DEBUG_DEVELOPER);
1531         return true; //Index doesn't exist, nothing to do
1532     }
1533
1534 /// Check newname isn't empty
1535     if (!$newname) {
1536         debugging('New name for index ' . $table->getName() . '->' . $index->getName() .
1537                   ' is empty! Rename skipped', DEBUG_DEVELOPER);
1538         return true; //Index doesn't exist, nothing to do
1539     }
1540
1541     if(!$sqlarr = $table->getRenameIndexSQL($CFG->dbtype, $CFG->prefix, $index, $newname, false)) {
1542         debugging('Some DBs do not support index renaming (MySQL). Rename skipped', DEBUG_DEVELOPER);
1543         return true; //Empty array = nothing to do = no error
1544     }
1545
1546     return execute_sql_arr($sqlarr, $continue, $feedback);
1547 }
1548
1549 /* trys to change default db encoding to utf8, if empty db
1550  */
1551 function change_db_encoding() {
1552     global $CFG, $db;
1553     // try forcing utf8 collation, if mysql db and no tables present
1554     if (($CFG->dbfamily=='mysql') && !$db->Metatables()) {
1555         $SQL = 'ALTER DATABASE '.$CFG->dbname.' CHARACTER SET utf8';
1556         execute_sql($SQL, false); // silent, if it fails it fails
1557         if (setup_is_unicodedb()) {
1558             configure_dbconnection();
1559         }
1560     }
1561 }
1562
1563 ?>