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) 2001-3001 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 XMLDDB');
  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  * Warning: Please be careful on primary keys, as this function will eat auto_increments
  69  *
  70  * @uses $CFG
  71  * @uses $db
  72  * @param string $table the name of the table to modify. (Without the prefix.)
  73  * @param string $oldfield If changing an existing column, the name of that column.
  74  * @param string $field The name of the column at the end of the operation.
  75  * @param string $type The type of the column at the end of the operation. TEXT, VARCHAR, CHAR, INTEGER, REAL, or TINYINT
  76  * @param string $size The size of that column type. As in VARCHAR($size), or INTEGER($size).
  77  * @param string $signed For numeric column types, whether that column is 'signed' or 'unsigned'.
  78  * @param string $default The new default value for the column.
  79  * @param string $null 'not null', or '' to allow nulls.
  80  * @param string $after Which column to insert this one after. Not supported on Postgres.
  81  *
  82  * @return boolean Wheter the operation succeeded.
  83  */
  84 function table_column($table, $oldfield, $field, $type='integer', $size='10',
  85                       $signed='unsigned', $default='0', $null='not null', $after='') {
  86     global $CFG, $db, $empty_rs_cache;
  87
  88     if (!empty($empty_rs_cache[$table])) {  // Clear the recordset cache because it's out of date
  89         unset($empty_rs_cache[$table]);
  90     }
  91
  92     switch (strtolower($CFG->dbtype)) {
  93
  94         case 'mysql':
  95         case 'mysqlt':
  96
  97             switch (strtolower($type)) {
  98                 case 'text':
  99                     $type = 'TEXT';
 100                     $signed = '';
 101                     break;
 102                 case 'integer':
 103                     $type = 'INTEGER('. $size .')';
 104                     break;
 105                 case 'varchar':
 106                     $type = 'VARCHAR('. $size .')';
 107                     $signed = '';
 108                     break;
 109                 case 'char':
 110                     $type = 'CHAR('. $size .')';
 111                     $signed = '';
 112                     break;
 113             }
 114
 115             if (!empty($oldfield)) {
 116                 $operation = 'CHANGE '. $oldfield .' '. $field;
 117             } else {
 118                 $operation = 'ADD '. $field;
 119             }
 120
 121             $default = 'DEFAULT \''. $default .'\'';
 122
 123             if (!empty($after)) {
 124                 $after = 'AFTER `'. $after .'`';
 125             }
 126
 127             return execute_sql('ALTER TABLE '. $CFG->prefix . $table .' '. $operation .' '. $type .' '. $signed .' '. $default .' '. $null .' '. $after);
 128
 129         case 'postgres7':        // From Petri Asikainen
 130             //Check db-version
 131             $dbinfo = $db->ServerInfo();
 132             $dbver = substr($dbinfo['version'],0,3);
 133
 134             //to prevent conflicts with reserved words
 135             $realfield = '"'. $field .'"';
 136             $field = '"'. $field .'_alter_column_tmp"';
 137             $oldfield = '"'. $oldfield .'"';
 138
 139             switch (strtolower($type)) {
 140                 case 'tinyint':
 141                 case 'integer':
 142                     if ($size <= 4) {
 143                         $type = 'INT2';
 144                     }
 145                     if ($size <= 10) {
 146                         $type = 'INT';
 147                     }
 148                     if  ($size > 10) {
 149                         $type = 'INT8';
 150                     }
 151                     break;
 152                 case 'varchar':
 153                     $type = 'VARCHAR('. $size .')';
 154                     break;
 155                 case 'char':
 156                     $type = 'CHAR('. $size .')';
 157                     $signed = '';
 158                     break;
 159             }
 160
 161             $default = '\''. $default .'\'';
 162
 163             //After is not implemented in postgesql
 164             //if (!empty($after)) {
 165             //    $after = "AFTER '$after'";
 166             //}
 167
 168             //Use transactions
 169             execute_sql('BEGIN');
 170
 171             //Always use temporary column
 172             execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ADD COLUMN '. $field .' '. $type);
 173             //Add default values
 174             execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .'='. $default);
 175
 176
 177             if ($dbver >= '7.3') {
 178                 // modifying 'not null' is posible before 7.3
 179                 //update default values to table
 180                 if (strtoupper($null) == 'NOT NULL') {
 181                     execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .'='. $default .' WHERE '. $field .' IS NULL');
 182                     execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $null);
 183                 } else {
 184                     execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' DROP NOT NULL');
 185                 }
 186             }
 187
 188             execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET DEFAULT '. $default);
 189
 190             if ( $oldfield != '""' ) {
 191
 192                 // We are changing the type of a column. This may require doing some casts...
 193                 $casting = '';
 194                 $oldtype = column_type($table, $oldfield);
 195                 $newtype = column_type($table, $field);
 196
 197                 // Do we need a cast?
 198                 if($newtype == 'N' && $oldtype == 'C') {
 199                     $casting = 'CAST(CAST('.$oldfield.' AS TEXT) AS REAL)';
 200                 }
 201                 else if($newtype == 'I' && $oldtype == 'C') {
 202                     $casting = 'CAST(CAST('.$oldfield.' AS TEXT) AS INTEGER)';
 203                 }
 204                 else {
 205                     $casting = $oldfield;
 206                 }
 207
 208                 // Run the update query, casting as necessary
 209                 execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .' = '. $casting);
 210                 execute_sql('ALTER TABLE  '. $CFG->prefix . $table .' DROP COLUMN '. $oldfield);
 211             }
 212
 213             execute_sql('ALTER TABLE '. $CFG->prefix . $table .' RENAME COLUMN '. $field .' TO '. $realfield);
 214
 215             return execute_sql('COMMIT');
 216
 217         default:
 218             switch (strtolower($type)) {
 219                 case 'integer':
 220                     $type = 'INTEGER';
 221                     break;
 222                 case 'varchar':
 223                     $type = 'VARCHAR';
 224                     break;
 225             }
 226
 227             $default = 'DEFAULT \''. $default .'\'';
 228
 229             if (!empty($after)) {
 230                 $after = 'AFTER '. $after;
 231             }
 232
 233             if (!empty($oldfield)) {
 234                 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' RENAME COLUMN '. $oldfield .' '. $field);
 235             } else {
 236                 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ADD COLUMN '. $field .' '. $type);
 237             }
 238
 239             execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $null);
 240             return execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $default);
 241     }
 242 }
 243
 244 /**
 245  * Given one XMLDBTable, check if it exists in DB (true/false)
 246  *
 247  * @param XMLDBTable table to be searched for
 248  * @return boolean true/false
 249  */
 250 function table_exists($table) {
 251
 252     global $CFG, $db;
 253
 254     $exists = true;
 255
 256 /// Do this function silenty (to avoid output in install/upgrade process)
 257     $olddbdebug = $db->debug;
 258     $db->debug = false;
 259
 260 /// Load the needed generator
 261     $classname = 'XMLDB' . $CFG->dbtype;
 262     $generator = new $classname();
 263     $generator->setPrefix($CFG->prefix);
 264 /// Calculate the name of the table
 265     $tablename = $generator->getTableName($table, false);
 266
 267 /// Search such tablename in DB
 268     $metatables = $db->MetaTables();
 269     $metatables = array_flip($metatables);
 270     $metatables = array_change_key_case($metatables, CASE_LOWER);
 271     if (!array_key_exists($tablename,  $metatables)) {
 272         $exists = false;
 273     }
 274
 275 /// Re-set original debug
 276     $db->debug = $olddbdebug;
 277
 278     return $exists;
 279 }
 280
 281 /**
 282  * Given one XMLDBField, check if it exists in DB (true/false)
 283  *
 284  * @uses, $db
 285  * @param XMLDBTable the table
 286  * @param XMLDBField the field to be searched for
 287  * @return boolean true/false
 288  */
 289 function field_exists($table, $field) {
 290
 291     global $CFG, $db;
 292
 293     $exists = true;
 294
 295 /// Do this function silenty (to avoid output in install/upgrade process)
 296     $olddbdebug = $db->debug;
 297     $db->debug = false;
 298
 299 /// Check the table exists
 300     if (!table_exists($table)) {
 301         $db->debug = $olddbdebug; //Re-set original $db->debug
 302         return false;
 303     }
 304
 305 /// Load the needed generator
 306     $classname = 'XMLDB' . $CFG->dbtype;
 307     $generator = new $classname();
 308     $generator->setPrefix($CFG->prefix);
 309 /// Calculate the name of the table
 310     $tablename = $generator->getTableName($table, false);
 311
 312 /// Get list of fields in table
 313     $fields = null;
 314     if ($fields = $db->MetaColumns($tablename)) {
 315         $fields = array_change_key_case($fields, CASE_LOWER);
 316     }
 317
 318     if (!array_key_exists($field->getName(),  $fields)) {
 319         $exists = false;
 320     }
 321
 322 /// Re-set original debug
 323     $db->debug = $olddbdebug;
 324
 325     return $exists;
 326 }
 327
 328 /**
 329  * Given one XMLDBIndex, check if it exists in DB (true/false)
 330  *
 331  * @uses, $db
 332  * @param XMLDBTable the table
 333  * @param XMLDBIndex the index to be searched for
 334  * @return boolean true/false
 335  */
 336 function index_exists($table, $index) {
 337
 338     global $CFG, $db;
 339
 340     $exists = true;
 341
 342 /// Do this function silenty (to avoid output in install/upgrade process)
 343     $olddbdebug = $db->debug;
 344     $db->debug = false;
 345
 346 /// Wrap over find_index_name to see if the index exists
 347     if (!find_index_name($table, $index)) {
 348         $exists = false;
 349     }
 350
 351 /// Re-set original debug
 352     $db->debug = $olddbdebug;
 353
 354     return $exists;
 355 }
 356
 357 /**
 358  * This function IS NOT IMPLEMENTED. ONCE WE'LL BE USING RELATIONAL
 359  * INTEGRITY IT WILL BECOME MORE USEFUL. FOR NOW, JUST CALCULATE "OFFICIAL"
 360  * KEY NAMES WITHOUT ACCESSING TO DB AT ALL.
 361  * Given one XMLDBKey, the function returns the name of the key in DB (if exists)
 362  * of false if it doesn't exist
 363  *
 364  * @uses, $db
 365  * @param XMLDBTable the table to be searched
 366  * @param XMLDBKey the key to be searched
 367  * @return string key name of false
 368  */
 369 function find_key_name($table, $xmldb_key) {
 370
 371     global $CFG, $db;
 372
 373 /// Extract key columns
 374     $keycolumns = $xmldb_key->getFields();
 375
 376 /// Get list of keys in table
 377 /// first primaries (we aren't going to use this now, because the MetaPrimaryKeys is awful)
 378     ///TODO: To implement when we advance in relational integrity
 379 /// then uniques (note that Moodle, for now, shouldn't have any UNIQUE KEY for now, but unique indexes)
 380     ///TODO: To implement when we advance in relational integrity (note that AdoDB hasn't any MetaXXX for this.
 381 /// then foreign (note that Moodle, for now, shouldn't have any FOREIGN KEY for now, but indexes)
 382     ///TODO: To implement when we advance in relational integrity (note that AdoDB has one MetaForeignKeys()
 383     ///but it's far from perfect.
 384 /// TODO: To create the proper functions inside each generator to retrieve all the needed KEY info (name
 385 ///       columns, reftable and refcolumns
 386
 387 /// So all we do is to return the official name of the requested key without any confirmation!)
 388     $classname = 'XMLDB' . $CFG->dbtype;
 389     $generator = new $classname();
 390     $generator->setPrefix($CFG->prefix);
 391 /// One exception, harcoded primary constraint names
 392     if ($generator->primary_key_name && $xmldb_key->getType() == XMLDB_KEY_PRIMARY) {
 393         return $generator->primary_key_name;
 394     } else {
 395     /// Calculate the name suffix
 396         switch ($xmldb_key->getType()) {
 397             case XMLDB_KEY_PRIMARY:
 398                 $suffix = 'pk';
 399                 break;
 400             case XMLDB_KEY_UNIQUE:
 401                 $suffix = 'uk';
 402                 break;
 403             case XMLDB_KEY_FOREIGN_UNIQUE:
 404             case XMLDB_KEY_FOREIGN:
 405                 $suffix = 'fk';
 406                 break;
 407         }
 408     /// And simply, return the oficial name
 409         return $generator->getNameForObject($table->getName(), implode(', ', $xmldb_key->getFields()), $suffix);
 410     }
 411 }
 412
 413 /**
 414  * Given one XMLDBIndex, the function returns the name of the index in DB (if exists)
 415  * of false if it doesn't exist
 416  *
 417  * @uses, $db
 418  * @param XMLDBTable the table to be searched
 419  * @param XMLDBIndex the index to be searched
 420  * @return string index name of false
 421  */
 422 function find_index_name($table, $index) {
 423
 424     global $CFG, $db;
 425
 426 /// Do this function silenty (to avoid output in install/upgrade process)
 427     $olddbdebug = $db->debug;
 428     $db->debug = false;
 429
 430 /// Extract index columns
 431     $indcolumns = $index->getFields();
 432
 433 /// Check the table exists
 434     if (!table_exists($table)) {
 435         $db->debug = $olddbdebug; //Re-set original $db->debug
 436         return false;
 437     }
 438
 439 /// Load the needed generator
 440     $classname = 'XMLDB' . $CFG->dbtype;
 441     $generator = new $classname();
 442     $generator->setPrefix($CFG->prefix);
 443 /// Calculate the name of the table
 444     $tablename = $generator->getTableName($table, false);
 445
 446 /// Get list of indexes in table
 447     $indexes = null;
 448     if ($indexes = $db->MetaIndexes($tablename)) {
 449         $indexes = array_change_key_case($indexes, CASE_LOWER);
 450     }
 451
 452 /// Iterate over them looking for columns coincidence
 453     if ($indexes) {
 454         foreach ($indexes as $indexname => $index) {
 455             $columns = $index['columns'];
 456         /// Lower case column names
 457             $columns = array_flip($columns);
 458             $columns = array_change_key_case($columns, CASE_LOWER);
 459             $columns = array_flip($columns);
 460         /// Check if index matchs queried index
 461             $diferences = array_merge(array_diff($columns, $indcolumns), array_diff($indcolumns, $columns));
 462         /// If no diferences, we have find the index
 463             if (empty($diferences)) {
 464                 $db->debug = $olddbdebug; //Re-set original $db->debug
 465                 return $indexname;
 466             }
 467         }
 468     }
 469 /// Arriving here, index not found
 470     $db->debug = $olddbdebug; //Re-set original $db->debug
 471     return false;
 472 }
 473
 474 /**
 475  * Given one XMLDBTable, the function returns the name of its sequence in DB (if exists)
 476  * of false if it doesn't exist
 477  *
 478  * @param XMLDBTable the table to be searched
 479  * @return string sequence name of false
 480  */
 481 function find_sequence_name($table) {
 482
 483     global $CFG, $db;
 484
 485     $sequencename = false;
 486
 487 /// Do this function silenty (to avoid output in install/upgrade process)
 488     $olddbdebug = $db->debug;
 489     $db->debug = false;
 490
 491     if (strtolower(get_class($table)) != 'xmldbtable') {
 492         $db->debug = $olddbdebug; //Re-set original $db->debug
 493         return false;
 494     }
 495
 496 /// Check table exists
 497     if (!table_exists($table)) {
 498         debugging('Table ' . $table->getName() . ' do not exist. Sequence not found', DEBUG_DEVELOPER);
 499         $db->debug = $olddbdebug; //Re-set original $db->debug
 500         return false; //Table doesn't exist, nothing to do
 501     }
 502
 503     $sequencename = $table->getSequenceFromDB($CFG->dbtype, $CFG->prefix);
 504
 505     $db->debug = $olddbdebug; //Re-set original $db->debug
 506     return $sequencename;
 507 }
 508
 509 /**
 510  * This function will load one entire XMLDB file, generating all the needed
 511  * SQL statements, specific for each RDBMS ($CFG->dbtype) and, finally, it
 512  * will execute all those statements against the DB.
 513  *
 514  * @uses $CFG, $db
 515  * @param $file full path to the XML file to be used
 516  * @return boolean (true on success, false on error)
 517  */
 518 function install_from_xmldb_file($file) {
 519
 520     global $CFG, $db;
 521
 522     $status = true;
 523
 524
 525     $xmldb_file = new XMLDBFile($file);
 526
 527     if (!$xmldb_file->fileExists()) {
 528         return false;
 529     }
 530
 531     $loaded = $xmldb_file->loadXMLStructure();
 532     if (!$loaded || !$xmldb_file->isLoaded()) {
 533     /// Show info about the error if we can find it
 534         if ($structure =& $xmldb_file->getStructure()) {
 535             if ($errors = $structure->getAllErrors()) {
 536                 notify('Errors found in XMLDB file: '. implode (', ', $errors));
 537             }
 538         }
 539         return false;
 540     }
 541
 542     $structure = $xmldb_file->getStructure();
 543
 544     if (!$sqlarr = $structure->getCreateStructureSQL($CFG->dbtype, $CFG->prefix, false)) {
 545         return true; //Empty array = nothing to do = no error
 546     }
 547
 548     return execute_sql_arr($sqlarr);
 549 }
 550
 551 /**
 552  * This function will create the table passed as argument with all its
 553  * fields/keys/indexes/sequences, everything based in the XMLDB object
 554  *
 555  * @uses $CFG, $db
 556  * @param XMLDBTable table object (full specs are required)
 557  * @param boolean continue to specify if must continue on error (true) or stop (false)
 558  * @param boolean feedback to specify to show status info (true) or not (false)
 559  * @return boolean true on success, false on error
 560  */
 561 function create_table($table, $continue=true, $feedback=true) {
 562
 563     global $CFG, $db;
 564
 565     $status = true;
 566
 567     if (strtolower(get_class($table)) != 'xmldbtable') {
 568         return false;
 569     }
 570
 571 /// Check table doesn't exist
 572     if (table_exists($table)) {
 573         debugging('Table ' . $table->getName() . ' exists. Create skipped', DEBUG_DEVELOPER);
 574         return true; //Table exists, nothing to do
 575     }
 576
 577     if(!$sqlarr = $table->getCreateTableSQL($CFG->dbtype, $CFG->prefix, false)) {
 578         return true; //Empty array = nothing to do = no error
 579     }
 580
 581     return execute_sql_arr($sqlarr, $continue, $feedback);
 582 }
 583
 584 /**
 585  * This function will drop the table passed as argument
 586  * and all the associated objects (keys, indexes, constaints, sequences, triggers)
 587  * will be dropped too.
 588  *
 589  * @uses $CFG, $db
 590  * @param XMLDBTable table object (just the name is mandatory)
 591  * @param boolean continue to specify if must continue on error (true) or stop (false)
 592  * @param boolean feedback to specify to show status info (true) or not (false)
 593  * @return boolean true on success, false on error
 594  */
 595 function drop_table($table, $continue=true, $feedback=true) {
 596
 597     global $CFG, $db;
 598
 599     $status = true;
 600
 601     if (strtolower(get_class($table)) != 'xmldbtable') {
 602         return false;
 603     }
 604
 605 /// Check table exists
 606     if (!table_exists($table)) {
 607         debugging('Table ' . $table->getName() . ' do not exist. Delete skipped', DEBUG_DEVELOPER);
 608         return true; //Table don't exist, nothing to do
 609     }
 610
 611     if(!$sqlarr = $table->getDropTableSQL($CFG->dbtype, $CFG->prefix, false)) {
 612         return true; //Empty array = nothing to do = no error
 613     }
 614
 615     return execute_sql_arr($sqlarr, $continue, $feedback);
 616 }
 617
 618 /**
 619  * This function will create the temporary table passed as argument with all its
 620  * fields/keys/indexes/sequences, everything based in the XMLDB object
 621  *
 622  * TRUNCATE the table immediately after creation. A previous process using
 623  * the same persistent connection may have created the temp table and failed to
 624  * drop it. In that case, the table will exist, and create_temp_table() will
 625  * will succeed.
 626  *
 627  * NOTE: The return value is the tablename - some DBs (MSSQL at least) use special
 628  * names for temp tables.
 629  *
 630  * @uses $CFG, $db
 631  * @param XMLDBTable table object (full specs are required)
 632  * @param boolean continue to specify if must continue on error (true) or stop (false)
 633  * @param boolean feedback to specify to show status info (true) or not (false)
 634  * @return string tablename on success, false on error
 635  */
 636 function create_temp_table($table, $continue=true, $feedback=true) {
 637
 638     global $CFG, $db;
 639
 640     $status = true;
 641
 642     if (strtolower(get_class($table)) != 'xmldbtable') {
 643         return false;
 644     }
 645
 646
 647     $temporary = 'TEMPORARY';
 648     switch (strtolower($CFG->dbfamily)) {
 649         case 'mssql':
 650             // TODO: somehow change the name to have a #
 651             $temporary = '';
 652             break;
 653         case 'oracle':
 654             $temporary = 'GLOBAL TEMPORARY';
 655             break;
 656     }
 657
 658 /// Check table doesn't exist
 659     if (table_exists($table)) {
 660         debugging('Table ' . $table->getName() . ' exists. Create skipped', DEBUG_DEVELOPER);
 661         return $table->getName(); //Table exists, nothing to do
 662     }
 663
 664     if(!$sqlarr = $table->getCreateTableSQL($CFG->dbtype, $CFG->prefix, false)) {
 665         return $table->getName(); //Empty array = nothing to do = no error
 666     }
 667
 668     if (!empty($temporary)) {
 669         $sqlarr = preg_replace('/^CREATE/', "CREATE $temporary", $sqlarr);
 670     }
 671
 672     if (execute_sql_arr($sqlarr, $continue, $feedback)) {
 673         return $table->getName();
 674     } else {
 675         return false;
 676     }
 677 }
 678
 679 /**
 680  * This function will rename the table passed as argument
 681  * Before renaming the index, the function will check it exists
 682  *
 683  * @uses $CFG, $db
 684  * @param XMLDBTable table object (just the name is mandatory)
 685  * @param string new name of the index
 686  * @param boolean continue to specify if must continue on error (true) or stop (false)
 687  * @param boolean feedback to specify to show status info (true) or not (false)
 688  * @return boolean true on success, false on error
 689  */
 690 function rename_table($table, $newname, $continue=true, $feedback=true) {
 691
 692     global $CFG, $db;
 693
 694     $status = true;
 695
 696     if (strtolower(get_class($table)) != 'xmldbtable') {
 697         return false;
 698     }
 699
 700 /// Check table exists
 701     if (!table_exists($table)) {
 702         debugging('Table ' . $table->getName() . ' do not exist. Rename skipped', DEBUG_DEVELOPER);
 703         return true; //Table doesn't exist, nothing to do
 704     }
 705
 706 /// Check new table doesn't exist
 707     $check = new XMLDBTable($newname);
 708     if (table_exists($check)) {
 709         debugging('Table ' . $check->getName() . ' already exists. Rename skipped', DEBUG_DEVELOPER);
 710         return true; //Table exists, nothing to do
 711     }
 712
 713 /// Check newname isn't empty
 714     if (!$newname) {
 715         debugging('New name for table ' . $index->getName() . ' is empty! Rename skipped', DEBUG_DEVELOPER);
 716         return true; //Table doesn't exist, nothing to do
 717     }
 718
 719     if(!$sqlarr = $table->getRenameTableSQL($CFG->dbtype, $CFG->prefix, $newname, false)) {
 720         return true; //Empty array = nothing to do = no error
 721     }
 722
 723     return execute_sql_arr($sqlarr, $continue, $feedback);
 724 }
 725
 726 /**
 727  * This function will add the field to the table passed as arguments
 728  *
 729  * @uses $CFG, $db
 730  * @param XMLDBTable table object (just the name is mandatory)
 731  * @param XMLDBField field object (full specs are required)
 732  * @param boolean continue to specify if must continue on error (true) or stop (false)
 733  * @param boolean feedback to specify to show status info (true) or not (false)
 734  * @return boolean true on success, false on error
 735  */
 736 function add_field($table, $field, $continue=true, $feedback=true) {
 737
 738     global $CFG, $db;
 739
 740     $status = true;
 741
 742     if (strtolower(get_class($table)) != 'xmldbtable') {
 743         return false;
 744     }
 745     if (strtolower(get_class($field)) != 'xmldbfield') {
 746         return false;
 747     }
 748
 749 /// Check the field doesn't exist
 750     if (field_exists($table, $field)) {
 751         debugging('Field ' . $field->getName() . ' exists. Create skipped', DEBUG_DEVELOPER);
 752         return true;
 753     }
 754
 755 /// If NOT NULL and no default given, check the table is empty
 756     if ($field->getNotNull() && $field->getDefault() === NULL && count_records($table->getName())) {
 757         debugging('Field ' . $field->getName() . ' cannot be added. Not null fields added to non empty tables require default value. Create skipped', DEBUG_DEVELOPER);
 758          return true;
 759     }
 760
 761     if(!$sqlarr = $table->getAddFieldSQL($CFG->dbtype, $CFG->prefix, $field, false)) {
 762         return true; //Empty array = nothing to do = no error
 763     }
 764
 765     return execute_sql_arr($sqlarr, $continue, $feedback);
 766 }
 767
 768 /**
 769  * This function will drop the field from the table passed as arguments
 770  *
 771  * @uses $CFG, $db
 772  * @param XMLDBTable table object (just the name is mandatory)
 773  * @param XMLDBField field object (just the name is mandatory)
 774  * @param boolean continue to specify if must continue on error (true) or stop (false)
 775  * @param boolean feedback to specify to show status info (true) or not (false)
 776  * @return boolean true on success, false on error
 777  */
 778 function drop_field($table, $field, $continue=true, $feedback=true) {
 779
 780     global $CFG, $db;
 781
 782     $status = true;
 783
 784     if (strtolower(get_class($table)) != 'xmldbtable') {
 785         return false;
 786     }
 787     if (strtolower(get_class($field)) != 'xmldbfield') {
 788         return false;
 789     }
 790
 791 /// Check the field exists
 792     if (!field_exists($table, $field)) {
 793         debugging('Field ' . $field->getName() . ' do not exist. Delete skipped', DEBUG_DEVELOPER);
 794         return true;
 795     }
 796
 797     if(!$sqlarr = $table->getDropFieldSQL($CFG->dbtype, $CFG->prefix, $field, false)) {
 798         return true; //Empty array = nothing to do = no error
 799     }
 800
 801     return execute_sql_arr($sqlarr, $continue, $feedback);
 802 }
 803
 804 /**
 805  * This function will change the type of the field in the table passed as arguments
 806  *
 807  * @uses $CFG, $db
 808  * @param XMLDBTable table object (just the name is mandatory)
 809  * @param XMLDBField field object (full specs are required)
 810  * @param boolean continue to specify if must continue on error (true) or stop (false)
 811  * @param boolean feedback to specify to show status info (true) or not (false)
 812  * @return boolean true on success, false on error
 813  */
 814 function change_field_type($table, $field, $continue=true, $feedback=true) {
 815
 816     global $CFG, $db;
 817
 818     $status = true;
 819
 820     if (strtolower(get_class($table)) != 'xmldbtable') {
 821         return false;
 822     }
 823     if (strtolower(get_class($field)) != 'xmldbfield') {
 824         return false;
 825     }
 826
 827     if(!$sqlarr = $table->getAlterFieldSQL($CFG->dbtype, $CFG->prefix, $field, false)) {
 828         return true; //Empty array = nothing to do = no error
 829     }
 830
 831     return execute_sql_arr($sqlarr, $continue, $feedback);
 832 }
 833
 834 /**
 835  * This function will change the precision of the field in the table passed as arguments
 836  *
 837  * @uses $CFG, $db
 838  * @param XMLDBTable table object (just the name is mandatory)
 839  * @param XMLDBField field object (full specs are required)
 840  * @param boolean continue to specify if must continue on error (true) or stop (false)
 841  * @param boolean feedback to specify to show status info (true) or not (false)
 842  * @return boolean true on success, false on error
 843  */
 844 function change_field_precision($table, $field, $continue=true, $feedback=true) {
 845
 846 /// Just a wrapper over change_field_type. Does exactly the same processing
 847     return change_field_type($table, $field, $continue, $feedback);
 848 }
 849
 850 /**
 851  * This function will change the unsigned/signed of the field in the table passed as arguments
 852  *
 853  * @uses $CFG, $db
 854  * @param XMLDBTable table object (just the name is mandatory)
 855  * @param XMLDBField field object (full specs are required)
 856  * @param boolean continue to specify if must continue on error (true) or stop (false)
 857  * @param boolean feedback to specify to show status info (true) or not (false)
 858  * @return boolean true on success, false on error
 859  */
 860 function change_field_unsigned($table, $field, $continue=true, $feedback=true) {
 861
 862 /// Just a wrapper over change_field_type. Does exactly the same processing
 863     return change_field_type($table, $field, $continue, $feedback);
 864 }
 865
 866 /**
 867  * This function will change the nullability of the field in the table passed as arguments
 868  *
 869  * @uses $CFG, $db
 870  * @param XMLDBTable table object (just the name is mandatory)
 871  * @param XMLDBField field object (full specs are required)
 872  * @param boolean continue to specify if must continue on error (true) or stop (false)
 873  * @param boolean feedback to specify to show status info (true) or not (false)
 874  * @return boolean true on success, false on error
 875  */
 876 function change_field_notnull($table, $field, $continue=true, $feedback=true) {
 877
 878 /// Just a wrapper over change_field_type. Does exactly the same processing
 879     return change_field_type($table, $field, $continue, $feedback);
 880 }
 881
 882 /**
 883  * This function will change the enum status of the field in the table passed as arguments
 884  *
 885  * @uses $CFG, $db
 886  * @param XMLDBTable table object (just the name is mandatory)
 887  * @param XMLDBField field object (full specs are required)
 888  * @param boolean continue to specify if must continue on error (true) or stop (false)
 889  * @param boolean feedback to specify to show status info (true) or not (false)
 890  * @return boolean true on success, false on error
 891  */
 892 function change_field_enum($table, $field, $continue=true, $feedback=true) {
 893
 894     global $CFG, $db;
 895
 896     $status = true;
 897
 898     if (strtolower(get_class($table)) != 'xmldbtable') {
 899         return false;
 900     }
 901     if (strtolower(get_class($field)) != 'xmldbfield') {
 902         return false;
 903     }
 904
 905     if(!$sqlarr = $table->getModifyEnumSQL($CFG->dbtype, $CFG->prefix, $field, false)) {
 906         return true; //Empty array = nothing to do = no error
 907     }
 908
 909     return execute_sql_arr($sqlarr, $continue, $feedback);
 910 }
 911 /**
 912  * This function will change the default of the field in the table passed as arguments
 913  * One null value in the default field means delete the default
 914  *
 915  * @uses $CFG, $db
 916  * @param XMLDBTable table object (just the name is mandatory)
 917  * @param XMLDBField field object (full specs are required)
 918  * @param boolean continue to specify if must continue on error (true) or stop (false)
 919  * @param boolean feedback to specify to show status info (true) or not (false)
 920  * @return boolean true on success, false on error
 921  */
 922 function change_field_default($table, $field, $continue=true, $feedback=true) {
 923
 924     global $CFG, $db;
 925
 926     $status = true;
 927
 928     if (strtolower(get_class($table)) != 'xmldbtable') {
 929         return false;
 930     }
 931     if (strtolower(get_class($field)) != 'xmldbfield') {
 932         return false;
 933     }
 934
 935     if(!$sqlarr = $table->getModifyDefaultSQL($CFG->dbtype, $CFG->prefix, $field, false)) {
 936         return true; //Empty array = nothing to do = no error
 937     }
 938
 939     return execute_sql_arr($sqlarr, $continue, $feedback);
 940 }
 941
 942 /**
 943  * This function will rename the field in the table passed as arguments
 944  * Before renaming the field, the function will check it exists
 945  *
 946  * @uses $CFG, $db
 947  * @param XMLDBTable table object (just the name is mandatory)
 948  * @param XMLDBField index object (full specs are required)
 949  * @param string new name of the field
 950  * @param boolean continue to specify if must continue on error (true) or stop (false)
 951  * @param boolean feedback to specify to show status info (true) or not (false)
 952  * @return boolean true on success, false on error
 953  */
 954 function rename_field($table, $field, $newname, $continue=true, $feedback=true) {
 955
 956     global $CFG, $db;
 957
 958     $status = true;
 959
 960     if (strtolower(get_class($table)) != 'xmldbtable') {
 961         return false;
 962     }
 963     if (strtolower(get_class($field)) != 'xmldbfield') {
 964         return false;
 965     }
 966
 967 /// Check we have included full field specs
 968     if (!$field->getType()) {
 969         debugging('Field ' . $field->getName() . ' must contain full specs. Rename skipped', DEBUG_DEVELOPER);
 970         return false;
 971     }
 972
 973 /// Check field isn't id. Renaming over that field is not allowed
 974     if ($field->getName() == 'id') {
 975         debugging('Field ' . $field->getName() . ' cannot be renamed. Rename skipped', DEBUG_DEVELOPER);
 976         return true; //Field is "id", nothing to do
 977     }
 978
 979 /// Check field exists
 980     if (!field_exists($table, $field)) {
 981         debugging('Field ' . $field->getName() . ' do not exist. Rename skipped', DEBUG_DEVELOPER);
 982         return true; //Field doesn't exist, nothing to do
 983     }
 984
 985 /// Check newname isn't empty
 986     if (!$newname) {
 987         debugging('New name for field ' . $field->getName() . ' is empty! Rename skipped', DEBUG_DEVELOPER);
 988         return true; //Field doesn't exist, nothing to do
 989     }
 990
 991     if(!$sqlarr = $table->getRenameFieldSQL($CFG->dbtype, $CFG->prefix, $field, $newname, false)) {
 992         return true; //Empty array = nothing to do = no error
 993     }
 994
 995     return execute_sql_arr($sqlarr, $continue, $feedback);
 996 }
 997
 998 /**
 999  * This function will create the key in the table passed as arguments
1000  *
1001  * @uses $CFG, $db
1002  * @param XMLDBTable table object (just the name is mandatory)
1003  * @param XMLDBKey index object (full specs are required)
1004  * @param boolean continue to specify if must continue on error (true) or stop (false)
1005  * @param boolean feedback to specify to show status info (true) or not (false)
1006  * @return boolean true on success, false on error
1007  */
1008 function add_key($table, $key, $continue=true, $feedback=true) {
1009
1010     global $CFG, $db;
1011
1012     $status = true;
1013
1014     if (strtolower(get_class($table)) != 'xmldbtable') {
1015         return false;
1016     }
1017     if (strtolower(get_class($key)) != 'xmldbkey') {
1018         return false;
1019     }
1020     if ($key->getType() == XMLDB_KEY_PRIMARY) { // Prevent PRIMARY to be added (only in create table, being serious  :-P)
1021         debugging('Primary Keys can be added at table create time only', DEBUG_DEVELOPER);
1022         return true;
1023     }
1024
1025     if(!$sqlarr = $table->getAddKeySQL($CFG->dbtype, $CFG->prefix, $key, false)) {
1026         return true; //Empty array = nothing to do = no error
1027     }
1028
1029     return execute_sql_arr($sqlarr, $continue, $feedback);
1030 }
1031
1032 /**
1033  * This function will drop the key in the table passed as arguments
1034  *
1035  * @uses $CFG, $db
1036  * @param XMLDBTable table object (just the name is mandatory)
1037  * @param XMLDBKey key object (full specs are required)
1038  * @param boolean continue to specify if must continue on error (true) or stop (false)
1039  * @param boolean feedback to specify to show status info (true) or not (false)
1040  * @return boolean true on success, false on error
1041  */
1042 function drop_key($table, $key, $continue=true, $feedback=true) {
1043
1044     global $CFG, $db;
1045
1046     $status = true;
1047
1048     if (strtolower(get_class($table)) != 'xmldbtable') {
1049         return false;
1050     }
1051     if (strtolower(get_class($key)) != 'xmldbkey') {
1052         return false;
1053     }
1054     if ($key->getType() == XMLDB_KEY_PRIMARY) { // Prevent PRIMARY to be dropped (only in drop table, being serious  :-P)
1055         debugging('Primary Keys can be deleted at table drop time only', DEBUG_DEVELOPER);
1056         return true;
1057     }
1058
1059     if(!$sqlarr = $table->getDropKeySQL($CFG->dbtype, $CFG->prefix, $key, false)) {
1060         return true; //Empty array = nothing to do = no error
1061     }
1062
1063     return execute_sql_arr($sqlarr, $continue, $feedback);
1064 }
1065
1066 /**
1067  * This function will rename the key in the table passed as arguments
1068  * Experimental. Shouldn't be used at all in normal installation/upgrade!
1069  *
1070  * @uses $CFG, $db
1071  * @param XMLDBTable table object (just the name is mandatory)
1072  * @param XMLDBKey key object (full specs are required)
1073  * @param string new name of the key
1074  * @param boolean continue to specify if must continue on error (true) or stop (false)
1075  * @param boolean feedback to specify to show status info (true) or not (false)
1076  * @return boolean true on success, false on error
1077  */
1078 function rename_key($table, $key, $newname, $continue=true, $feedback=true) {
1079
1080     global $CFG, $db;
1081
1082     debugging('rename_key() is one experimental feature. You must not use it in production!', DEBUG_DEVELOPER);
1083
1084     $status = true;
1085
1086     if (strtolower(get_class($table)) != 'xmldbtable') {
1087         return false;
1088     }
1089     if (strtolower(get_class($key)) != 'xmldbkey') {
1090         return false;
1091     }
1092
1093 /// Check newname isn't empty
1094     if (!$newname) {
1095         debugging('New name for key ' . $key->getName() . ' is empty! Rename skipped', DEBUG_DEVELOPER);
1096         return true; //Key doesn't exist, nothing to do
1097     }
1098
1099     if(!$sqlarr = $table->getRenameKeySQL($CFG->dbtype, $CFG->prefix, $key, $newname, false)) {
1100         debugging('Some DBs do not support key renaming (MySQL, PostgreSQL, MsSQL). Rename skipped', DEBUG_DEVELOPER);
1101         return true; //Empty array = nothing to do = no error
1102     }
1103
1104     return execute_sql_arr($sqlarr, $continue, $feedback);
1105 }
1106
1107 /**
1108  * This function will create the index in the table passed as arguments
1109  * Before creating the index, the function will check it doesn't exists
1110  *
1111  * @uses $CFG, $db
1112  * @param XMLDBTable table object (just the name is mandatory)
1113  * @param XMLDBIndex index object (full specs are required)
1114  * @param boolean continue to specify if must continue on error (true) or stop (false)
1115  * @param boolean feedback to specify to show status info (true) or not (false)
1116  * @return boolean true on success, false on error
1117  */
1118 function add_index($table, $index, $continue=true, $feedback=true) {
1119
1120     global $CFG, $db;
1121
1122     $status = true;
1123
1124     if (strtolower(get_class($table)) != 'xmldbtable') {
1125         return false;
1126     }
1127     if (strtolower(get_class($index)) != 'xmldbindex') {
1128         return false;
1129     }
1130
1131 /// Check index doesn't exist
1132     if (index_exists($table, $index)) {
1133         debugging('Index ' . $index->getName() . ' exists. Create skipped', DEBUG_DEVELOPER);
1134         return true; //Index exists, nothing to do
1135     }
1136
1137     if(!$sqlarr = $table->getAddIndexSQL($CFG->dbtype, $CFG->prefix, $index, false)) {
1138         return true; //Empty array = nothing to do = no error
1139     }
1140
1141     return execute_sql_arr($sqlarr, $continue, $feedback);
1142 }
1143
1144 /**
1145  * This function will drop the index in the table passed as arguments
1146  * Before dropping the index, the function will check it exists
1147  *
1148  * @uses $CFG, $db
1149  * @param XMLDBTable table object (just the name is mandatory)
1150  * @param XMLDBIndex index object (full specs are required)
1151  * @param boolean continue to specify if must continue on error (true) or stop (false)
1152  * @param boolean feedback to specify to show status info (true) or not (false)
1153  * @return boolean true on success, false on error
1154  */
1155 function drop_index($table, $index, $continue=true, $feedback=true) {
1156
1157     global $CFG, $db;
1158
1159     $status = true;
1160
1161     if (strtolower(get_class($table)) != 'xmldbtable') {
1162         return false;
1163     }
1164     if (strtolower(get_class($index)) != 'xmldbindex') {
1165         return false;
1166     }
1167
1168 /// Check index exists
1169     if (!index_exists($table, $index)) {
1170         debugging('Index ' . $index->getName() . ' do not exist. Delete skipped', DEBUG_DEVELOPER);
1171         return true; //Index doesn't exist, nothing to do
1172     }
1173
1174     if(!$sqlarr = $table->getDropIndexSQL($CFG->dbtype, $CFG->prefix, $index, false)) {
1175         return true; //Empty array = nothing to do = no error
1176     }
1177
1178     return execute_sql_arr($sqlarr, $continue, $feedback);
1179 }
1180
1181 /**
1182  * This function will rename the index in the table passed as arguments
1183  * Before renaming the index, the function will check it exists
1184  * Experimental. Shouldn't be used at all!
1185  *
1186  * @uses $CFG, $db
1187  * @param XMLDBTable table object (just the name is mandatory)
1188  * @param XMLDBIndex index object (full specs are required)
1189  * @param string new name of the index
1190  * @param boolean continue to specify if must continue on error (true) or stop (false)
1191  * @param boolean feedback to specify to show status info (true) or not (false)
1192  * @return boolean true on success, false on error
1193  */
1194 function rename_index($table, $index, $newname, $continue=true, $feedback=true) {
1195
1196     global $CFG, $db;
1197
1198     debugging('rename_index() is one experimental feature. You must not use it in production!', DEBUG_DEVELOPER);
1199
1200     $status = true;
1201
1202     if (strtolower(get_class($table)) != 'xmldbtable') {
1203         return false;
1204     }
1205     if (strtolower(get_class($index)) != 'xmldbindex') {
1206         return false;
1207     }
1208
1209 /// Check index exists
1210     if (!index_exists($table, $index)) {
1211         debugging('Index ' . $index->getName() . ' do not exist. Rename skipped', DEBUG_DEVELOPER);
1212         return true; //Index doesn't exist, nothing to do
1213     }
1214
1215 /// Check newname isn't empty
1216     if (!$newname) {
1217         debugging('New name for index ' . $index->getName() . ' is empty! Rename skipped', DEBUG_DEVELOPER);
1218         return true; //Index doesn't exist, nothing to do
1219     }
1220
1221     if(!$sqlarr = $table->getRenameIndexSQL($CFG->dbtype, $CFG->prefix, $index, $newname, false)) {
1222         debugging('Some DBs do not support index renaming (MySQL). Rename skipped', DEBUG_DEVELOPER);
1223         return true; //Empty array = nothing to do = no error
1224     }
1225
1226     return execute_sql_arr($sqlarr, $continue, $feedback);
1227 }
1228
1229 /* trys to change default db encoding to utf8, if empty db
1230  */
1231 function change_db_encoding() {
1232     global $CFG, $db;
1233     // try forcing utf8 collation, if mysql db and no tables present
1234     if (($CFG->dbfamily=='mysql') && !$db->Metatables()) {
1235         $SQL = 'ALTER DATABASE '.$CFG->dbname.' CHARACTER SET utf8';
1236         execute_sql($SQL, false); // silent, if it fails it fails
1237         if (setup_is_unicodedb()) {
1238             configure_dbconnection();
1239         }
1240     }
1241 }
1242
1243 ?>