lib/dmllib.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 //                                                                       //
  12 // This program is free software; you can redistribute it and/or modify  //
  13 // it under the terms of the GNU General Public License as published by  //
  14 // the Free Software Foundation; either version 2 of the License, or     //
  15 // (at your option) any later version.                                   //
  16 //                                                                       //
  17 // This program is distributed in the hope that it will be useful,       //
  18 // but WITHOUT ANY WARRANTY; without even the implied warranty of        //
  19 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         //
  20 // GNU General Public License for more details:                          //
  21 //                                                                       //
  22 //          http://www.gnu.org/copyleft/gpl.html                         //
  23 //                                                                       //
  24 ///////////////////////////////////////////////////////////////////////////
  25
  26 /// This library contains all the Data Manipulation Language (DML) functions
  27 /// used to interact with the DB. All the dunctions in this library must be
  28 /// generic and work against the major number of RDBMS possible. This is the
  29 /// list of currently supported and tested DBs: mysql, postresql, mssql, oracle
  30
  31 /// This library is automatically included by Moodle core so you never need to
  32 /// include it yourself.
  33
  34 /// For more info about the functions available in this library, please visit:
  35 ///     http://docs.moodle.org/en/DML_functions
  36 /// (feel free to modify, improve and document such page, thanks!)
  37
  38 /// GLOBAL CONSTANTS /////////////////////////////////////////////////////////
  39
  40 $empty_rs_cache = array();   // Keeps copies of the recordsets used in one invocation
  41 $metadata_cache = array();   // Keeps copies of the MetaColumns() for each table used in one invocations
  42 $record_cache = array();     // Keeps copies of all simple get_record results from one invocation
  43
  44 /// FUNCTIONS FOR DATABASE HANDLING  ////////////////////////////////
  45
  46 /**
  47  * Execute a given sql command string
  48  *
  49  * Completely general function - it just runs some SQL and reports success.
  50  *
  51  * @uses $db
  52  * @param string $command The sql string you wish to be executed.
  53  * @param bool $feedback Set this argument to true if the results generated should be printed. Default is true.
  54  * @return string
  55  */
  56 function execute_sql($command, $feedback=true) {
  57 /// Completely general function - it just runs some SQL and reports success.
  58
  59     global $db, $CFG;
  60
  61     $olddebug = $db->debug;
  62
  63     if (!$feedback) {
  64         $db->debug = false;
  65     }
  66
  67     $empty_rs_cache = array();  // Clear out the cache, just in case changes were made to table structures
  68
  69     if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
  70
  71     $result = $db->Execute($command);
  72
  73     $db->debug = $olddebug;
  74
  75     if ($result) {
  76         if ($feedback) {
  77             notify(get_string('success'), 'notifysuccess');
  78         }
  79         return true;
  80     } else {
  81         if ($feedback) {
  82             notify('<strong>' . get_string('error') . '</strong>');
  83         }
  84         if (!empty($CFG->dblogerror)) {
  85             $debug=array_shift(debug_backtrace());
  86             error_log("SQL ".$db->ErrorMsg()." in {$debug['file']} on line {$debug['line']}. STATEMENT:  $command");
  87         }
  88         return false;
  89     }
  90 }
  91
  92 /**
  93 * on DBs that support it, switch to transaction mode and begin a transaction
  94 * you'll need to ensure you call commit_sql() or your changes *will* be lost.
  95 *
  96 * Now using ADOdb standard transactions. Some day, we should switch to
  97 * Smart Transactions (http://phplens.com/adodb/tutorial.smart.transactions.html)
  98 * as they autodetect errors and are nestable and easier to write
  99 *
 100 * this is _very_ useful for massive updates
 101 */
 102 function begin_sql() {
 103
 104     global $db;
 105
 106     $db->BeginTrans();
 107
 108     return true;
 109 }
 110
 111 /**
 112 * on DBs that support it, commit the transaction
 113 *
 114 * Now using ADOdb standard transactions. Some day, we should switch to
 115 * Smart Transactions (http://phplens.com/adodb/tutorial.smart.transactions.html)
 116 * as they autodetect errors and are nestable and easier to write
 117 */
 118 function commit_sql() {
 119
 120     global $db;
 121
 122     $db->CommitTrans();
 123
 124     return true;
 125 }
 126
 127 /**
 128 * on DBs that support it, rollback the transaction
 129 *
 130 * Now using ADOdb standard transactions. Some day, we should switch to
 131 * Smart Transactions (http://phplens.com/adodb/tutorial.smart.transactions.html)
 132 * as they autodetect errors and are nestable and easier to write
 133 */
 134 function rollback_sql() {
 135
 136     global $db;
 137
 138     $db->RollbackTrans();
 139
 140     return true;
 141 }
 142
 143 /**
 144  * returns db specific uppercase function
 145  * @deprecated Moodle 1.7 because all the RDBMS use upper()
 146  */
 147 function db_uppercase() {
 148     return "upper";
 149 }
 150
 151 /**
 152  * returns db specific lowercase function
 153  * @deprecated Moodle 1.7 because all the RDBMS use lower()
 154  */
 155 function db_lowercase() {
 156     return "lower";
 157 }
 158
 159
 160 /**
 161  * Run an arbitrary sequence of semicolon-delimited SQL commands
 162  *
 163  * Assumes that the input text (file or string) consists of
 164  * a number of SQL statements ENDING WITH SEMICOLONS.  The
 165  * semicolons MUST be the last character in a line.
 166  * Lines that are blank or that start with "#" or "--" (postgres) are ignored.
 167  * Only tested with mysql dump files (mysqldump -p -d moodle)
 168  *
 169  * @uses $CFG
 170  *
 171  * @deprecated Moodle 1.7 use the new XMLDB stuff in lib/ddllib.php
 172  *
 173  * @param string $sqlfile The path where a file with sql commands can be found on the server.
 174  * @param string $sqlstring If no path is supplied then a string with semicolon delimited sql
 175  * commands can be supplied in this argument.
 176  * @return bool Returns true if databse was modified successfully.
 177  */
 178 function modify_database($sqlfile='', $sqlstring='') {
 179
 180     global $CFG;
 181
 182     if ($CFG->version > 2006101007) {
 183         debugging('Function modify_database() is deprecated. Replace it with the new XMLDB stuff.', DEBUG_DEVELOPER);
 184     }
 185
 186     $success = true;  // Let's be optimistic
 187
 188     if (!empty($sqlfile)) {
 189         if (!is_readable($sqlfile)) {
 190             $success = false;
 191             echo '<p>Tried to modify database, but "'. $sqlfile .'" doesn\'t exist!</p>';
 192             return $success;
 193         } else {
 194             $lines = file($sqlfile);
 195         }
 196     } else {
 197         $sqlstring = trim($sqlstring);
 198         if ($sqlstring{strlen($sqlstring)-1} != ";") {
 199             $sqlstring .= ";"; // add it in if it's not there.
 200         }
 201         $lines[] = $sqlstring;
 202     }
 203
 204     $command = '';
 205
 206     foreach ($lines as $line) {
 207         $line = rtrim($line);
 208         $length = strlen($line);
 209
 210         if ($length and $line[0] <> '#' and $line[0].$line[1] <> '--') {
 211             if (substr($line, $length-1, 1) == ';') {
 212                 $line = substr($line, 0, $length-1);   // strip ;
 213                 $command .= $line;
 214                 $command = str_replace('prefix_', $CFG->prefix, $command); // Table prefixes
 215                 if (! execute_sql($command)) {
 216                     $success = false;
 217                 }
 218                 $command = '';
 219             } else {
 220                 $command .= $line;
 221             }
 222         }
 223     }
 224
 225     return $success;
 226
 227 }
 228
 229 /// GENERIC FUNCTIONS TO CHECK AND COUNT RECORDS ////////////////////////////////////////
 230
 231 /**
 232  * Test whether a record exists in a table where all the given fields match the given values.
 233  *
 234  * The record to test is specified by giving up to three fields that must
 235  * equal the corresponding values.
 236  *
 237  * @uses $CFG
 238  * @param string $table The table to check.
 239  * @param string $field1 the first field to check (optional).
 240  * @param string $value1 the value field1 must have (requred if field1 is given, else optional).
 241  * @param string $field2 the second field to check (optional).
 242  * @param string $value2 the value field2 must have (requred if field2 is given, else optional).
 243  * @param string $field3 the third field to check (optional).
 244  * @param string $value3 the value field3 must have (requred if field3 is given, else optional).
 245  * @return bool true if a matching record exists, else false.
 246  */
 247 function record_exists($table, $field1='', $value1='', $field2='', $value2='', $field3='', $value3='') {
 248
 249     global $CFG;
 250
 251     $select = where_clause($field1, $value1, $field2, $value2, $field3, $value3);
 252
 253     return record_exists_sql('SELECT * FROM '. $CFG->prefix . $table .' '. $select);
 254 }
 255
 256 /**
 257  * Test whether any records exists in a table which match a particular WHERE clause.
 258  *
 259  * @uses $CFG
 260  * @param string $table The database table to be checked against.
 261  * @param string $select A fragment of SQL to be used in a WHERE clause in the SQL call.
 262  * @return bool true if a matching record exists, else false.
 263  */
 264 function record_exists_select($table, $select='') {
 265
 266     global $CFG;
 267
 268     if ($select) {
 269         $select = 'WHERE '.$select;
 270     }
 271
 272     return record_exists_sql('SELECT * FROM '. $CFG->prefix . $table . ' ' . $select);
 273 }
 274
 275 /**
 276  * Test whether a SQL SELECT statement returns any records.
 277  *
 278  * This function returns true if the SQL statement executes
 279  * without any errors and returns at least one record.
 280  *
 281  * @param string $sql The SQL statement to execute.
 282  * @return bool true if the SQL executes without errors and returns at least one record.
 283  */
 284 function record_exists_sql($sql) {
 285
 286     $limitfrom = 0; /// Number of records to skip
 287     $limitnum  = 1; /// Number of records to retrieve
 288
 289     $rs = get_recordset_sql($sql, $limitfrom, $limitnum);
 290
 291     if ($rs && $rs->RecordCount() > 0) {
 292         return true;
 293     } else {
 294         return false;
 295     }
 296 }
 297
 298 /**
 299  * Count the records in a table where all the given fields match the given values.
 300  *
 301  * @uses $CFG
 302  * @param string $table The table to query.
 303  * @param string $field1 the first field to check (optional).
 304  * @param string $value1 the value field1 must have (requred if field1 is given, else optional).
 305  * @param string $field2 the second field to check (optional).
 306  * @param string $value2 the value field2 must have (requred if field2 is given, else optional).
 307  * @param string $field3 the third field to check (optional).
 308  * @param string $value3 the value field3 must have (requred if field3 is given, else optional).
 309  * @return int The count of records returned from the specified criteria.
 310  */
 311 function count_records($table, $field1='', $value1='', $field2='', $value2='', $field3='', $value3='') {
 312
 313     global $CFG;
 314
 315     $select = where_clause($field1, $value1, $field2, $value2, $field3, $value3);
 316
 317     return count_records_sql('SELECT COUNT(*) FROM '. $CFG->prefix . $table .' '. $select);
 318 }
 319
 320 /**
 321  * Count the records in a table which match a particular WHERE clause.
 322  *
 323  * @uses $CFG
 324  * @param string $table The database table to be checked against.
 325  * @param string $select A fragment of SQL to be used in a WHERE clause in the SQL call.
 326  * @param string $countitem The count string to be used in the SQL call. Default is COUNT(*).
 327  * @return int The count of records returned from the specified criteria.
 328  */
 329 function count_records_select($table, $select='', $countitem='COUNT(*)') {
 330
 331     global $CFG;
 332
 333     if ($select) {
 334         $select = 'WHERE '.$select;
 335     }
 336
 337     return count_records_sql('SELECT '. $countitem .' FROM '. $CFG->prefix . $table .' '. $select);
 338 }
 339
 340 /**
 341  * Get the result of a SQL SELECT COUNT(...) query.
 342  *
 343  * Given a query that counts rows, return that count. (In fact,
 344  * given any query, return the first field of the first record
 345  * returned. However, this method should only be used for the
 346  * intended purpose.) If an error occurrs, 0 is returned.
 347  *
 348  * @uses $CFG
 349  * @uses $db
 350  * @param string $sql The SQL string you wish to be executed.
 351  * @return int the count. If an error occurrs, 0 is returned.
 352  */
 353 function count_records_sql($sql) {
 354     $rs = get_recordset_sql($sql);
 355
 356     if ($rs) {
 357         return reset($rs->fields);
 358     } else {
 359         return 0;
 360     }
 361 }
 362
 363 /// GENERIC FUNCTIONS TO GET, INSERT, OR UPDATE DATA  ///////////////////////////////////
 364
 365
 366 /**
 367  * Get a single record as an object
 368  *
 369  * @uses $CFG
 370  * @param string $table The table to select from.
 371  * @param string $field1 the first field to check (optional).
 372  * @param string $value1 the value field1 must have (requred if field1 is given, else optional).
 373  * @param string $field2 the second field to check (optional).
 374  * @param string $value2 the value field2 must have (requred if field2 is given, else optional).
 375  * @param string $field3 the third field to check (optional).
 376  * @param string $value3 the value field3 must have (requred if field3 is given, else optional).
 377  * @return mixed a fieldset object containing the first mathcing record, or false if none found.
 378  */
 379 function get_record($table, $field1, $value1, $field2='', $value2='', $field3='', $value3='', $fields='*') {
 380
 381     global $CFG, $record_cache;
 382
 383     // Check to see whether this record is eligible for caching (fields=*, only condition is id)
 384     $docache = false;
 385     if (!empty($CFG->enablerecordcache) && $field1=='id' && !$field2 && !$field3 && $fields=='*') {
 386         $docache = true;
 387         // If it's in the cache, return it
 388         if (!empty($record_cache[$table][$value1])) {
 389             return $record_cache[$table][$value1];
 390         }
 391     }
 392
 393     $select = where_clause($field1, $value1, $field2, $value2, $field3, $value3);
 394
 395     $record = get_record_sql('SELECT '.$fields.' FROM '. $CFG->prefix . $table .' '. $select);
 396
 397     // If we're caching records, store this one (supposing we got something - we don't cache failures)
 398     if (!empty($CFG->enablerecordcache) && $record && $docache) {
 399         $record_cache[$table][$value1] = $record;
 400     }
 401
 402     return $record;
 403 }
 404
 405 /**
 406  * Get a single record as an object using an SQL statement
 407  *
 408  * The SQL statement should normally only return one record. In debug mode
 409  * you will get a warning if more record is returned (unless you
 410  * set $expectmultiple to true). In non-debug mode, it just returns
 411  * the first record.
 412  *
 413  * @uses $CFG
 414  * @uses $db
 415  * @param string $sql The SQL string you wish to be executed, should normally only return one record.
 416  * @param bool $expectmultiple If the SQL cannot be written to conviniently return just one record,
 417  *      set this to true to hide the debug message.
 418  * @param bool $nolimit sometimes appending ' LIMIT 1' to the SQL causes an error. Set this to true
 419  *      to stop your SQL being modified. This argument should probably be deprecated.
 420  * @return Found record as object. False if not found or error
 421  */
 422 function get_record_sql($sql, $expectmultiple=false, $nolimit=false) {
 423
 424     global $CFG;
 425
 426 /// Default situation
 427     $limitfrom = 0; /// Number of records to skip
 428     $limitnum  = 1; /// Number of records to retrieve
 429
 430 /// Only a few uses of the 2nd and 3rd parameter have been found
 431 /// I think that we should avoid to use them completely, one
 432 /// record is one record, and everything else should return error.
 433 /// So the proposal is to change all the uses, (4-5 inside Moodle
 434 /// Core), drop them from the definition and delete the next two
 435 /// "if" sentences. (eloy, 2006-08-19)
 436
 437     if ($nolimit) {
 438         $limitfrom = 0;
 439         $limitnum  = 0;
 440     } else if ($expectmultiple) {
 441         $limitfrom = 0;
 442         $limitnum  = 1;
 443     } else if (debugging()) {
 444         // Debugging mode - don't use a limit of 1, but do change the SQL, because sometimes that
 445         // causes errors, and in non-debug mode you don't see the error message and it is
 446         // impossible to know what's wrong.
 447         $limitfrom = 0;
 448         $limitnum  = 100;
 449     }
 450
 451     if (!$rs = get_recordset_sql($sql, $limitfrom, $limitnum)) {
 452         return false;
 453     }
 454
 455     $recordcount = $rs->RecordCount();
 456
 457     if ($recordcount == 0) {          // Found no records
 458         return false;
 459
 460     } else if ($recordcount == 1) {    // Found one record
 461     /// DIRTY HACK to retrieve all the ' ' (1 space) fields converted back
 462     /// to '' (empty string) for Oracle. It's the only way to work with
 463     /// all those NOT NULL DEFAULT '' fields until we definetively delete them
 464         if ($CFG->dbtype == 'oci8po') {
 465             array_walk($rs->fields, 'onespace2empty');
 466         }
 467     /// End od DIRTY HACK
 468         return (object)$rs->fields;
 469
 470     } else {                          // Error: found more than one record
 471         notify('Error:  Turn off debugging to hide this error.');
 472         notify($sql . '(with limits ' . $limitfrom . ', ' . $limitnum . ')');
 473         if ($records = $rs->GetAssoc(true)) {
 474             notify('Found more than one record in get_record_sql !');
 475             print_object($records);
 476         } else {
 477             notify('Very strange error in get_record_sql !');
 478             print_object($rs);
 479         }
 480         print_continue("$CFG->wwwroot/$CFG->admin/config.php");
 481     }
 482 }
 483
 484 /**
 485  * Gets one record from a table, as an object
 486  *
 487  * @uses $CFG
 488  * @param string $table The database table to be checked against.
 489  * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
 490  * @param string $fields A comma separated list of fields to be returned from the chosen table.
 491  * @return object|false Returns an array of found records (as objects) or false if no records or error occured.
 492  */
 493 function get_record_select($table, $select='', $fields='*') {
 494
 495     global $CFG;
 496
 497     if ($select) {
 498         $select = 'WHERE '. $select;
 499     }
 500
 501     return get_record_sql('SELECT '. $fields .' FROM '. $CFG->prefix . $table .' '. $select);
 502 }
 503
 504 /**
 505  * Get a number of records as an ADODB RecordSet.
 506  *
 507  * Selects records from the table $table.
 508  *
 509  * If specified, only records where the field $field has value $value are retured.
 510  *
 511  * If specified, the results will be sorted as specified by $sort. This
 512  * is added to the SQL as "ORDER BY $sort". Example values of $sort
 513  * mightbe "time ASC" or "time DESC".
 514  *
 515  * If $fields is specified, only those fields are returned.
 516  *
 517  * This function is internal to datalib, and should NEVER should be called directly
 518  * from general Moodle scripts.  Use get_record, get_records etc.
 519  *
 520  * If you only want some of the records, specify $limitfrom and $limitnum.
 521  * The query will skip the first $limitfrom records (according to the sort
 522  * order) and then return the next $limitnum records. If either of $limitfrom
 523  * or $limitnum is specified, both must be present.
 524  *
 525  * The return value is an ADODB RecordSet object
 526  * @link http://phplens.com/adodb/reference.functions.adorecordset.html
 527  * if the query succeeds. If an error occurrs, false is returned.
 528  *
 529  * @param string $table the table to query.
 530  * @param string $field a field to check (optional).
 531  * @param string $value the value the field must have (requred if field1 is given, else optional).
 532  * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
 533  * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
 534  * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 535  * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 536  * @return mixed an ADODB RecordSet object, or false if an error occured.
 537  */
 538 function get_recordset($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
 539
 540     if ($field) {
 541         $select = "$field = '$value'";
 542     } else {
 543         $select = '';
 544     }
 545
 546     return get_recordset_select($table, $select, $sort, $fields, $limitfrom, $limitnum);
 547 }
 548
 549 /**
 550  * Get a number of records as an ADODB RecordSet.
 551  *
 552  * If given, $select is used as the SELECT parameter in the SQL query,
 553  * otherwise all records from the table are returned.
 554  *
 555  * Other arguments and the return type as for @see function get_recordset.
 556  *
 557  * @uses $CFG
 558  * @param string $table the table to query.
 559  * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
 560  * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
 561  * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
 562  * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 563  * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 564  * @return mixed an ADODB RecordSet object, or false if an error occured.
 565  */
 566 function get_recordset_select($table, $select='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
 567
 568     global $CFG;
 569
 570     if ($select) {
 571         $select = ' WHERE '. $select;
 572     }
 573
 574     if ($sort) {
 575         $sort = ' ORDER BY '. $sort;
 576     }
 577
 578     return get_recordset_sql('SELECT '. $fields .' FROM '. $CFG->prefix . $table . $select . $sort, $limitfrom, $limitnum);
 579 }
 580
 581 /**
 582  * Get a number of records as an ADODB RecordSet.
 583  *
 584  * Only records where $field takes one of the values $values are returned.
 585  * $values should be a comma-separated list of values, for example "4,5,6,10"
 586  * or "'foo','bar','baz'".
 587  *
 588  * Other arguments and the return type as for @see function get_recordset.
 589  *
 590  * @param string $table the table to query.
 591  * @param string $field a field to check (optional).
 592  * @param string $values comma separated list of values the field must have (requred if field is given, else optional).
 593  * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
 594  * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
 595  * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 596  * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 597  * @return mixed an ADODB RecordSet object, or false if an error occured.
 598  */
 599 function get_recordset_list($table, $field='', $values='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
 600
 601     if ($field) {
 602         $select = "$field IN ($values)";
 603     } else {
 604         $select = '';
 605     }
 606
 607     return get_recordset_select($table, $select, $sort, $fields, $limitfrom, $limitnum);
 608 }
 609
 610 /**
 611  * Get a number of records as an ADODB RecordSet.  $sql must be a complete SQL query.
 612  * This function is internal to datalib, and should NEVER should be called directly
 613  * from general Moodle scripts.  Use get_record, get_records etc.
 614  *
 615  * The return type is as for @see function get_recordset.
 616  *
 617  * @uses $CFG
 618  * @uses $db
 619  * @param string $sql the SQL select query to execute.
 620  * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 621  * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 622  * @return mixed an ADODB RecordSet object, or false if an error occured.
 623  */
 624 function get_recordset_sql($sql, $limitfrom=null, $limitnum=null) {
 625     global $CFG, $db;
 626
 627     if (empty($db)) {
 628         return false;
 629     }
 630
 631 /// Temporary hack as part of phasing out all access to obsolete user tables  XXX
 632     if (!empty($CFG->rolesactive)) {
 633         if (strpos($sql, $CFG->prefix.'user_students') ||
 634             strpos($sql, $CFG->prefix.'user_teachers') ||
 635             strpos($sql, $CFG->prefix.'user_coursecreators') ||
 636             strpos($sql, $CFG->prefix.'user_admins')) {
 637             if (debugging()) { var_dump(debug_backtrace()); }
 638             error('This SQL relies on obsolete tables!  Your code must be fixed by a developer.');
 639         }
 640     }
 641
 642
 643     if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
 644
 645     if ($limitfrom || $limitnum) {
 646         ///Special case, 0 must be -1 for ADOdb
 647         $limitfrom = empty($limitfrom) ? -1 : $limitfrom;
 648         $limitnum  = empty($limitnum) ? -1 : $limitnum;
 649         $rs = $db->SelectLimit($sql, $limitnum, $limitfrom);
 650     } else {
 651         $rs = $db->Execute($sql);
 652     }
 653     if (!$rs) {
 654         debugging($db->ErrorMsg() .'<br /><br />'. $sql);
 655         if (!empty($CFG->dblogerror)) {
 656             $debug=array_shift(debug_backtrace());
 657             error_log("SQL ".$db->ErrorMsg()." in {$debug['file']} on line {$debug['line']}. STATEMENT:  $sql with limits ($limitfrom, $limitnum)");
 658         }
 659         return false;
 660     }
 661
 662     return $rs;
 663 }
 664
 665 /**
 666  * Utility function used by the following 4 methods.
 667  *
 668  * @param object an ADODB RecordSet object.
 669  * @return mixed mixed an array of objects, or false if an error occured or the RecordSet was empty.
 670  */
 671 function recordset_to_array($rs) {
 672
 673     global $CFG;
 674
 675     if ($rs && $rs->RecordCount() > 0) {
 676     /// First of all, we are going to get the name of the first column
 677     /// to introduce it back after transforming the recordset to assoc array
 678     /// See http://docs.moodle.org/en/XMLDB_Problems, fetch mode problem.
 679         $firstcolumn = $rs->FetchField(0);
 680     /// Get the whole associative array
 681         if ($records = $rs->GetAssoc(true)) {
 682             foreach ($records as $key => $record) {
 683             /// Really DIRTY HACK for Oracle, but it's the only way to make it work
 684             /// until we got all those NOT NULL DEFAULT '' out from Moodle
 685                 if ($CFG->dbtype == 'oci8po') {
 686                     array_walk($record, 'onespace2empty');
 687                 }
 688             /// End of DIRTY HACK
 689                 $record[$firstcolumn->name] = $key;/// Re-add the assoc field
 690                 $objects[$key] = (object) $record; /// To object
 691             }
 692             return $objects;
 693     /// Fallback in case we only have 1 field in the recordset. MDL-5877
 694         } else if ($rs->_numOfFields == 1 && $records = $rs->GetRows()) {
 695             foreach ($records as $key => $record) {
 696             /// Really DIRTY HACK for Oracle, but it's the only way to make it work
 697             /// until we got all those NOT NULL DEFAULT '' out from Moodle
 698                 if ($CFG->dbtype == 'oci8po') {
 699                     array_walk($record, 'onespace2empty');
 700                 }
 701             /// End of DIRTY HACK
 702                 $objects[$record[$firstcolumn->name]] = (object) $record; /// The key is the first column value (like Assoc)
 703             }
 704             return $objects;
 705         } else {
 706             return false;
 707         }
 708     } else {
 709         return false;
 710     }
 711 }
 712
 713 /**
 714  * This function is used to convert all the Oracle 1-space defaults to the empty string
 715  * like a really DIRTY HACK to allow it to work better until all those NOT NULL DEFAULT ''
 716  * fields will be out from Moodle.
 717  * @param string the string to be converted to '' (empty string) if it's ' ' (one space)
 718  * @param mixed the key of the array in case we are using this function from array_walk,
 719  *              defaults to null for other (direct) uses
 720  * @return boolean always true (the converted variable is returned by reference)
 721  */
 722 function onespace2empty(&$item, $key=null) {
 723     $item = $item == ' ' ? '' : $item;
 724     return true;
 725 }
 726 ///End DIRTY HACK
 727
 728
 729 /**
 730  * Get a number of records as an array of objects.
 731  *
 732  * If the query succeeds and returns at least one record, the
 733  * return value is an array of objects, one object for each
 734  * record found. The array key is the value from the first
 735  * column of the result set. The object associated with that key
 736  * has a member variable for each column of the results.
 737  *
 738  * @param string $table the table to query.
 739  * @param string $field a field to check (optional).
 740  * @param string $value the value the field must have (requred if field1 is given, else optional).
 741  * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
 742  * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
 743  * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 744  * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 745  * @return mixed an array of objects, or false if no records were found or an error occured.
 746  */
 747 function get_records($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
 748     $rs = get_recordset($table, $field, $value, $sort, $fields, $limitfrom, $limitnum);
 749     return recordset_to_array($rs);
 750 }
 751
 752 /**
 753  * Get a number of records as an array of objects.
 754  *
 755  * Return value as for @see function get_records.
 756  *
 757  * @param string $table the table to query.
 758  * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
 759  * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
 760  * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
 761  * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 762  * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 763  * @return mixed an array of objects, or false if no records were found or an error occured.
 764  */
 765 function get_records_select($table, $select='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
 766     $rs = get_recordset_select($table, $select, $sort, $fields, $limitfrom, $limitnum);
 767     return recordset_to_array($rs);
 768 }
 769
 770 /**
 771  * Get a number of records as an array of objects.
 772  *
 773  * Return value as for @see function get_records.
 774  *
 775  * @param string $table The database table to be checked against.
 776  * @param string $field The field to search
 777  * @param string $values Comma separated list of possible value
 778  * @param string $sort Sort order (as valid SQL sort parameter)
 779  * @param string $fields A comma separated list of fields to be returned from the chosen table.
 780  * @return mixed an array of objects, or false if no records were found or an error occured.
 781  */
 782 function get_records_list($table, $field='', $values='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
 783     $rs = get_recordset_list($table, $field, $values, $sort, $fields, $limitfrom, $limitnum);
 784     return recordset_to_array($rs);
 785 }
 786
 787 /**
 788  * Get a number of records as an array of objects.
 789  *
 790  * Return value as for @see function get_records.
 791  *
 792  * @param string $sql the SQL select query to execute.
 793  * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 794  * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 795  * @return mixed an array of objects, or false if no records were found or an error occured.
 796  */
 797 function get_records_sql($sql, $limitfrom='', $limitnum='') {
 798     $rs = get_recordset_sql($sql, $limitfrom, $limitnum);
 799     return recordset_to_array($rs);
 800 }
 801
 802 /**
 803  * Utility function used by the following 3 methods.
 804  *
 805  * @param object an ADODB RecordSet object with two columns.
 806  * @return mixed an associative array, or false if an error occured or the RecordSet was empty.
 807  */
 808 function recordset_to_menu($rs) {
 809     global $CFG;
 810
 811     if ($rs && $rs->RecordCount() > 0) {
 812         $keys = array_keys($rs->fields);
 813         $key0=$keys[0];
 814         $key1=$keys[1];
 815         while (!$rs->EOF) {
 816             $menu[$rs->fields[$key0]] = $rs->fields[$key1];
 817             $rs->MoveNext();
 818         }
 819         /// Really DIRTY HACK for Oracle, but it's the only way to make it work
 820         /// until we got all those NOT NULL DEFAULT '' out from Moodle
 821         if ($CFG->dbtype == 'oci8po') {
 822             array_walk($menu, 'onespace2empty');
 823         }
 824         /// End of DIRTY HACK
 825         return $menu;
 826     } else {
 827         return false;
 828     }
 829 }
 830
 831 /**
 832  * Get the first two columns from a number of records as an associative array.
 833  *
 834  * Arguments as for @see function get_recordset.
 835  *
 836  * If no errors occur, and at least one records is found, the return value
 837  * is an associative whose keys come from the first field of each record,
 838  * and whose values are the corresponding second fields. If no records are found,
 839  * or an error occurs, false is returned.
 840  *
 841  * @param string $table the table to query.
 842  * @param string $field a field to check (optional).
 843  * @param string $value the value the field must have (requred if field1 is given, else optional).
 844  * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
 845  * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
 846  * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 847  * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 848  * @return mixed an associative array, or false if no records were found or an error occured.
 849  */
 850 function get_records_menu($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
 851     $rs = get_recordset($table, $field, $value, $sort, $fields, $limitfrom, $limitnum);
 852     return recordset_to_menu($rs);
 853 }
 854
 855 /**
 856  * Get the first two columns from a number of records as an associative array.
 857  *
 858  * Arguments as for @see function get_recordset_select.
 859  * Return value as for @see function get_records_menu.
 860  *
 861  * @param string $table The database table to be checked against.
 862  * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
 863  * @param string $sort Sort order (optional) - a valid SQL order parameter
 864  * @param string $fields A comma separated list of fields to be returned from the chosen table.
 865  * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 866  * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 867  * @return mixed an associative array, or false if no records were found or an error occured.
 868  */
 869 function get_records_select_menu($table, $select='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
 870     $rs = get_recordset_select($table, $select, $sort, $fields, $limitfrom, $limitnum);
 871     return recordset_to_menu($rs);
 872 }
 873
 874 /**
 875  * Get the first two columns from a number of records as an associative array.
 876  *
 877  * Arguments as for @see function get_recordset_sql.
 878  * Return value as for @see function get_records_menu.
 879  *
 880  * @param string $sql The SQL string you wish to be executed.
 881  * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 882  * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 883  * @return mixed an associative array, or false if no records were found or an error occured.
 884  */
 885 function get_records_sql_menu($sql, $limitfrom='', $limitnum='') {
 886     $rs = get_recordset_sql($sql, $limitfrom, $limitnum);
 887     return recordset_to_menu($rs);
 888 }
 889
 890 /**
 891  * Get a single value from a table row where all the given fields match the given values.
 892  *
 893  * @param string $table the table to query.
 894  * @param string $return the field to return the value of.
 895  * @param string $field1 the first field to check (optional).
 896  * @param string $value1 the value field1 must have (requred if field1 is given, else optional).
 897  * @param string $field2 the second field to check (optional).
 898  * @param string $value2 the value field2 must have (requred if field2 is given, else optional).
 899  * @param string $field3 the third field to check (optional).
 900  * @param string $value3 the value field3 must have (requred if field3 is given, else optional).
 901  * @return mixed the specified value, or false if an error occured.
 902  */
 903 function get_field($table, $return, $field1, $value1, $field2='', $value2='', $field3='', $value3='') {
 904     global $CFG;
 905     $select = where_clause($field1, $value1, $field2, $value2, $field3, $value3);
 906     return get_field_sql('SELECT ' . $return . ' FROM ' . $CFG->prefix . $table . ' ' . $select);
 907 }
 908
 909 /**
 910  * Get a single value from a table row where a particular select clause is true.
 911  *
 912  * @uses $CFG
 913  * @param string $table the table to query.
 914  * @param string $return the field to return the value of.
 915  * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
 916  * @return mixed the specified value, or false if an error occured.
 917  */
 918 function get_field_select($table, $return, $select) {
 919     global $CFG;
 920     if ($select) {
 921         $select = 'WHERE '. $select;
 922     }
 923     return get_field_sql('SELECT ' . $return . ' FROM ' . $CFG->prefix . $table . ' ' . $select);
 924 }
 925
 926 /**
 927  * Get a single value from a table.
 928  *
 929  * @param string $sql an SQL statement expected to return a single value.
 930  * @return mixed the specified value, or false if an error occured.
 931  */
 932 function get_field_sql($sql) {
 933     global $CFG;
 934
 935 /// Strip potential LIMIT uses arriving here, debugging them (MDL-7173)
 936     $newsql = preg_replace('/ LIMIT [0-9, ]+$/is', '', $sql);
 937     if ($newsql != $sql) {
 938         debugging('Incorrect use of LIMIT clause (not cross-db) in call to get_field_sql(): ' . $sql, DEBUG_DEVELOPER);
 939         $sql = $newsql;
 940     }
 941
 942     $rs = get_recordset_sql($sql, 0, 1);
 943
 944     if ($rs && $rs->RecordCount() == 1) {
 945         /// DIRTY HACK to retrieve all the ' ' (1 space) fields converted back
 946         /// to '' (empty string) for Oracle. It's the only way to work with
 947         /// all those NOT NULL DEFAULT '' fields until we definetively delete them
 948         if ($CFG->dbtype == 'oci8po') {
 949             $value = reset($rs->fields);
 950             onespace2empty($value);
 951             return $value;
 952         }
 953         /// End of DIRTY HACK
 954         return reset($rs->fields);
 955     } else {
 956         return false;
 957     }
 958 }
 959
 960 /**
 961  * Get an array of data from one or more fields from a database
 962  * use to get a column, or a series of distinct values
 963  *
 964  * @uses $CFG
 965  * @uses $db
 966  * @param string $sql The SQL string you wish to be executed.
 967  * @return mixed|false Returns the value return from the SQL statment or false if an error occured.
 968  * @todo Finish documenting this function
 969  */
 970 function get_fieldset_sql($sql) {
 971
 972     global $db, $CFG;
 973
 974     if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
 975
 976     $rs = $db->Execute($sql);
 977     if (!$rs) {
 978         debugging($db->ErrorMsg() .'<br /><br />'. $sql);
 979         if (!empty($CFG->dblogerror)) {
 980             $debug=array_shift(debug_backtrace());
 981             error_log("SQL ".$db->ErrorMsg()." in {$debug['file']} on line {$debug['line']}. STATEMENT:  $sql");
 982         }
 983         return false;
 984     }
 985
 986     if ( $rs->RecordCount() > 0 ) {
 987         $keys = array_keys($rs->fields);
 988         $key0 = $keys[0];
 989         $results = array();
 990         while (!$rs->EOF) {
 991             array_push($results, $rs->fields[$key0]);
 992             $rs->MoveNext();
 993         }
 994         /// DIRTY HACK to retrieve all the ' ' (1 space) fields converted back
 995         /// to '' (empty string) for Oracle. It's the only way to work with
 996         /// all those NOT NULL DEFAULT '' fields until we definetively delete them
 997         if ($CFG->dbtype == 'oci8po') {
 998             array_walk($results, 'onespace2empty');
 999         }
1000         /// End of DIRTY HACK
1001         return $results;
1002     } else {
1003         return false;
1004     }
1005 }
1006
1007 /**
1008  * Set a single field in every table row where all the given fields match the given values.
1009  *
1010  * @uses $CFG
1011  * @uses $db
1012  * @param string $table The database table to be checked against.
1013  * @param string $newfield the field to set.
1014  * @param string $newvalue the value to set the field to.
1015  * @param string $field1 the first field to check (optional).
1016  * @param string $value1 the value field1 must have (requred if field1 is given, else optional).
1017  * @param string $field2 the second field to check (optional).
1018  * @param string $value2 the value field2 must have (requred if field2 is given, else optional).
1019  * @param string $field3 the third field to check (optional).
1020  * @param string $value3 the value field3 must have (requred if field3 is given, else optional).
1021  * @return mixed An ADODB RecordSet object with the results from the SQL call or false.
1022  */
1023 function set_field($table, $newfield, $newvalue, $field1, $value1, $field2='', $value2='', $field3='', $value3='') {
1024
1025     global $db, $CFG, $record_cache;
1026
1027     if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
1028
1029     // Clear record_cache based on the parameters passed (individual record or whole table)
1030     if (!empty($CFG->enablerecordcache)) {
1031         if ($field1 == 'id') {
1032             if (isset($record_cache[$table][$value1])) {
1033                 unset($record_cache[$table][$value1]);
1034             }
1035         } else if ($field2 == 'id') {
1036             if (isset($record_cache[$table][$value2])) {
1037                 unset($record_cache[$table][$value2]);
1038             }
1039         } else if ($field3 == 'id') {
1040             if (isset($record_cache[$table][$value3])) {
1041                 unset($record_cache[$table][$value3]);
1042             }
1043         } else {
1044             if (isset($record_cache[$table])) {
1045                 unset($record_cache[$table]);
1046             }
1047         }
1048     }
1049
1050     $select = where_clause($field1, $value1, $field2, $value2, $field3, $value3);
1051
1052     $dataobject = new StdClass;
1053     $dataobject->{$newfield} = $newvalue;
1054     // Oracle DIRTY HACK -
1055     if ($CFG->dbtype == 'oci8po') {
1056         oracle_dirty_hack($table, $dataobject); // Convert object to the correct "empty" values for Oracle DB
1057         $newvalue = $dataobject->{$newfield};
1058     }
1059     // End DIRTY HACK
1060
1061 /// Under Oracle and MSSQL we have our own set field process
1062 /// If the field being updated is clob/blob, we use our alternate update here
1063 /// They will be updated later
1064     if (($CFG->dbtype == 'oci8po' || $CFG->dbtype == 'mssql' || $CFG->dbtype == 'odbc_mssql' || $CFG->dbtype == 'mssql_n')
1065       && !empty($select)) {
1066     /// Detect lobs
1067         $foundclobs = array();
1068         $foundblobs = array();
1069         db_detect_lobs($table, $dataobject, $foundclobs, $foundblobs);
1070     }
1071
1072 /// Under Oracle and MSSQL, finally, Update all the Clobs and Blobs present in the record
1073 /// if we know we have some of them in the query
1074     if (($CFG->dbtype == 'oci8po' || $CFG->dbtype == 'mssql' || $CFG->dbtype == 'odbc_mssql' || $CFG->dbtype == 'mssql_n')
1075       && !empty($select) &&
1076       (!empty($foundclobs) || !empty($foundblobs))) {
1077         if (!db_update_lobs($table, $select, $foundclobs, $foundblobs)) {
1078             return false; //Some error happened while updating LOBs
1079         } else {
1080             return true; //Everrything was ok
1081         }
1082     }
1083
1084 /// Arriving here, standard update
1085     return $db->Execute('UPDATE '. $CFG->prefix . $table .' SET '. $newfield  .' = \''. $newvalue .'\' '. $select);
1086 }
1087
1088 /**
1089  * Delete the records from a table where all the given fields match the given values.
1090  *
1091  * @uses $CFG
1092  * @uses $db
1093  * @param string $table the table to delete from.
1094  * @param string $field1 the first field to check (optional).
1095  * @param string $value1 the value field1 must have (requred if field1 is given, else optional).
1096  * @param string $field2 the second field to check (optional).
1097  * @param string $value2 the value field2 must have (requred if field2 is given, else optional).
1098  * @param string $field3 the third field to check (optional).
1099  * @param string $value3 the value field3 must have (requred if field3 is given, else optional).
1100  * @return mixed An ADODB RecordSet object with the results from the SQL call or false.
1101  */
1102 function delete_records($table, $field1='', $value1='', $field2='', $value2='', $field3='', $value3='') {
1103
1104     global $db, $CFG, $record_cache;
1105
1106     // Clear record_cache based on the parameters passed (individual record or whole table)
1107     if (!empty($CFG->enablerecordcache)) {
1108         if ($field1 == 'id') {
1109             if (isset($record_cache[$table][$value1])) {
1110                 unset($record_cache[$table][$value1]);
1111             }
1112         } else if ($field2 == 'id') {
1113             if (isset($record_cache[$table][$value2])) {
1114                 unset($record_cache[$table][$value2]);
1115             }
1116         } else if ($field3 == 'id') {
1117             if (isset($record_cache[$table][$value3])) {
1118                 unset($record_cache[$table][$value3]);
1119             }
1120         } else {
1121             if (isset($record_cache[$table])) {
1122                 unset($record_cache[$table]);
1123             }
1124         }
1125     }
1126
1127     if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
1128
1129     $select = where_clause($field1, $value1, $field2, $value2, $field3, $value3);
1130
1131     return $db->Execute('DELETE FROM '. $CFG->prefix . $table .' '. $select);
1132 }
1133
1134 /**
1135  * Delete one or more records from a table
1136  *
1137  * @uses $CFG
1138  * @uses $db
1139  * @param string $table The database table to be checked against.
1140  * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
1141  * @return object A PHP standard object with the results from the SQL call.
1142  * @todo Verify return type.
1143  */
1144 function delete_records_select($table, $select='') {
1145
1146     global $CFG, $db, $record_cache;
1147
1148     // Clear record_cache (whole table)
1149     if (!empty($CFG->enablerecordcache) && isset($record_cache[$table])) {
1150         unset($record_cache[$table]);
1151     }
1152
1153     if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
1154
1155     if ($select) {
1156         $select = 'WHERE '.$select;
1157     }
1158
1159     return $db->Execute('DELETE FROM '. $CFG->prefix . $table .' '. $select);
1160 }
1161
1162 /**
1163  * Insert a record into a table and return the "id" field if required
1164  *
1165  * If the return ID isn't required, then this just reports success as true/false.
1166  * $dataobject is an object containing needed data
1167  *
1168  * @uses $db
1169  * @uses $CFG
1170  * @param string $table The database table to be checked against.
1171  * @param array $dataobject A data object with values for one or more fields in the record
1172  * @param bool $returnid Should the id of the newly created record entry be returned? If this option is not requested then true/false is returned.
1173  * @param string $primarykey The primary key of the table we are inserting into (almost always "id")
1174  */
1175 function insert_record($table, $dataobject, $returnid=true, $primarykey='id') {
1176
1177     global $db, $CFG, $empty_rs_cache;
1178
1179     if (empty($db)) {
1180         return false;
1181     }
1182
1183 /// Temporary hack as part of phasing out all access to obsolete user tables  XXX
1184     if (!empty($CFG->rolesactive)) {
1185         if (in_array($table, array('user_students', 'user_teachers', 'user_coursecreators', 'user_admins'))) {
1186             if (debugging()) { var_dump(debug_backtrace()); }
1187             error('This SQL relies on obsolete tables ('.$table.')!  Your code must be fixed by a developer.');
1188         }
1189     }
1190
1191     if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
1192
1193 /// In Moodle we always use auto-numbering fields for the primary key
1194 /// so let's unset it now before it causes any trouble later
1195     unset($dataobject->{$primarykey});
1196
1197 /// Get an empty recordset. Cache for multiple inserts.
1198     if (empty($empty_rs_cache[$table])) {
1199         /// Execute a dummy query to get an empty recordset
1200         if (!$empty_rs_cache[$table] = $db->Execute('SELECT * FROM '. $CFG->prefix . $table .' WHERE '. $primarykey  .' = \'-1\'')) {
1201             return false;
1202         }
1203     }
1204
1205     $rs = $empty_rs_cache[$table];
1206
1207 /// Postgres doesn't have the concept of primary key built in
1208 /// and will return the OID which isn't what we want.
1209 /// The efficient and transaction-safe strategy is to
1210 /// move the sequence forward first, and make the insert
1211 /// with an explicit id.
1212     if ( $CFG->dbtype === 'postgres7' && $returnid == true ) {
1213         if ($nextval = (int)get_field_sql("SELECT NEXTVAL('{$CFG->prefix}{$table}_{$primarykey}_seq')")) {
1214             $dataobject->{$primarykey} = $nextval;
1215         }
1216     }
1217
1218 /// Begin DIRTY HACK
1219     if ($CFG->dbtype == 'oci8po') {
1220         oracle_dirty_hack($table, $dataobject); // Convert object to the correct "empty" values for Oracle DB
1221     }
1222 /// End DIRTY HACK
1223
1224 /// Under Oracle and MSSQL we have our own insert record process
1225 /// detect all the clob/blob fields and change their contents to @#CLOB#@ and @#BLOB#@
1226 /// saving them into $foundclobs and $foundblobs [$fieldname]->contents
1227 /// Same for mssql (only processing blobs - image fields)
1228     if (($CFG->dbtype == 'oci8po' || $CFG->dbtype == 'mssql' || $CFG->dbtype == 'odbc_mssql' || $CFG->dbtype == 'mssql_n')) {
1229         $foundclobs = array();
1230         $foundblobs = array();
1231         db_detect_lobs($table, $dataobject, $foundclobs, $foundblobs);
1232     }
1233
1234 /// Under Oracle, if the primary key inserted has been requested OR
1235 /// if there are LOBs to insert, we calculate the next value via
1236 /// explicit query to the sequence.
1237 /// Else, the pre-insert trigger will do the job, because the primary
1238 /// key isn't needed at all by the rest of PHP code
1239     if ( $CFG->dbtype === 'oci8po' && ($returnid == true || !empty($foundclobs) || !empty($foundblobs))) {
1240     /// We need this here (move this function to dmlib?)
1241         include_once($CFG->libdir . '/ddllib.php');
1242         $xmldb_table = new XMLDBTable($table);
1243         $seqname = find_sequence_name($xmldb_table);
1244         if (!$seqname) {
1245         /// Fallback, seqname not found, something is wrong. Inform and use the alternative getNameForObject() method
1246             debugging('Sequence name for table ' . $table->getName() . ' not found', DEBUG_DEVELOPER);
1247             $generator = new XMLDBoci8po();
1248             $generator->setPrefix($CFG->prefix);
1249             $seqname = $generator->getNameForObject($table, $primarykey, 'seq');
1250         }
1251         if ($nextval = (int)$db->GenID($seqname)) {
1252             $dataobject->{$primarykey} = $nextval;
1253         }
1254     }
1255
1256 /// Get the correct SQL from adoDB
1257     if (!$insertSQL = $db->GetInsertSQL($rs, (array)$dataobject, true)) {
1258         return false;
1259     }
1260
1261 /// Under Oracle and MSSQL, replace all the '@#CLOB#@' and '@#BLOB#@' ocurrences to proper default values
1262 /// if we know we have some of them in the query
1263     if (($CFG->dbtype == 'oci8po' || $CFG->dbtype == 'mssql' || $CFG->dbtype == 'odbc_mssql' || $CFG->dbtype == 'mssql_n') &&
1264       (!empty($foundclobs) || !empty($foundblobs))) {
1265     /// Initial configuration, based on DB
1266         switch ($CFG->dbtype) {
1267             case 'oci8po':
1268                 $clobdefault = 'empty_clob()'; //Value of empty default clobs for this DB
1269                 $blobdefault = 'empty_blob()'; //Value of empty default blobs for this DB
1270                 break;
1271             case 'mssql':
1272             case 'odbc_mssql':
1273             case 'mssql_n':
1274                 $clobdefault = 'null'; //Value of empty default clobs for this DB (under mssql this won't be executed
1275                 $blobdefault = 'null'; //Value of empty default blobs for this DB
1276                 break;
1277         }
1278         $insertSQL = str_replace("'@#CLOB#@'", $clobdefault, $insertSQL);
1279         $insertSQL = str_replace("'@#BLOB#@'", $blobdefault, $insertSQL);
1280     }
1281
1282 /// Run the SQL statement
1283     if (!$rs = $db->Execute($insertSQL)) {
1284         debugging($db->ErrorMsg() .'<br /><br />'.$insertSQL);
1285         if (!empty($CFG->dblogerror)) {
1286             $debug=array_shift(debug_backtrace());
1287             error_log("SQL ".$db->ErrorMsg()." in {$debug['file']} on line {$debug['line']}. STATEMENT:  $insertSQL");
1288         }
1289         return false;
1290     }
1291
1292 /// Under Oracle, finally, Update all the Clobs and Blobs present in the record
1293 /// if we know we have some of them in the query
1294     if ($CFG->dbtype == 'oci8po' &&
1295       !empty($dataobject->{$primarykey}) &&
1296       (!empty($foundclobs) || !empty($foundblobs))) {
1297         if (!db_update_lobs($table, $dataobject->{$primarykey}, $foundclobs, $foundblobs)) {
1298             return false; //Some error happened while updating LOBs
1299         }
1300     }
1301
1302 /// If a return ID is not needed then just return true now (but not in MSSQL DBs, where we may have some pending tasks)
1303     if (!$returnid && !($CFG->dbtype == 'mssql' || $CFG->dbtype == 'odbc_mssql' || $CFG->dbtype == 'mssql_n')) {
1304         return true;
1305     }
1306
1307 /// We already know the record PK if it's been passed explicitly,
1308 /// or if we've retrieved it from a sequence (Postgres and Oracle).
1309     if (!empty($dataobject->{$primarykey})) {
1310         return $dataobject->{$primarykey};
1311     }
1312
1313 /// This only gets triggered with MySQL and MSQL databases
1314 /// however we have some postgres fallback in case we failed
1315 /// to find the sequence.
1316     $id = $db->Insert_ID();
1317
1318 /// Under MSSQL all the Blobs (IMAGE) present in the record
1319 /// if we know we have some of them in the query
1320     if (($CFG->dbtype == 'mssql' || $CFG->dbtype == 'odbc_mssql' || $CFG->dbtype == 'mssql_n') &&
1321       !empty($id) &&
1322       (!empty($foundclobs) || !empty($foundblobs))) {
1323         if (!db_update_lobs($table, $id, $foundclobs, $foundblobs)) {
1324             return false; //Some error happened while updating LOBs
1325         }
1326     }
1327
1328     if ($CFG->dbtype === 'postgres7') {
1329         // try to get the primary key based on id
1330         if ( ($rs = $db->Execute('SELECT '. $primarykey .' FROM '. $CFG->prefix . $table .' WHERE oid = '. $id))
1331              && ($rs->RecordCount() == 1) ) {
1332             trigger_error("Retrieved $primarykey from oid on table $table because we could not find the sequence.");
1333             return (integer)reset($rs->fields);
1334         }
1335         trigger_error('Failed to retrieve primary key after insert: SELECT '. $primarykey .
1336                       ' FROM '. $CFG->prefix . $table .' WHERE oid = '. $id);
1337         return false;
1338     }
1339
1340     return (integer)$id;
1341 }
1342
1343 /**
1344  * Update a record in a table
1345  *
1346  * $dataobject is an object containing needed data
1347  * Relies on $dataobject having a variable "id" to
1348  * specify the record to update
1349  *
1350  * @uses $CFG
1351  * @uses $db
1352  * @param string $table The database table to be checked against.
1353  * @param array $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
1354  * @return bool
1355  */
1356 function update_record($table, $dataobject) {
1357
1358     global $db, $CFG, $record_cache;
1359
1360     if (! isset($dataobject->id) ) {
1361         return false;
1362     }
1363
1364     // Remove this record from record cache since it will change
1365     if (!empty($CFG->enablerecordcache) && isset($record_cache[$table][$dataobject->id])) {
1366         unset($record_cache[$table][$dataobject->id]);
1367     }
1368
1369 /// Temporary hack as part of phasing out all access to obsolete user tables  XXX
1370     if (!empty($CFG->rolesactive)) {
1371         if (in_array($table, array('user_students', 'user_teachers', 'user_coursecreators', 'user_admins'))) {
1372             if (debugging()) { var_dump(debug_backtrace()); }
1373             error('This SQL relies on obsolete tables ('.$table.')!  Your code must be fixed by a developer.');
1374         }
1375     }
1376
1377 /// Begin DIRTY HACK
1378     if ($CFG->dbtype == 'oci8po') {
1379         oracle_dirty_hack($table, $dataobject); // Convert object to the correct "empty" values for Oracle DB
1380     }
1381 /// End DIRTY HACK
1382
1383 /// Under Oracle and MSSQL we have our own update record process
1384 /// detect all the clob/blob fields and delete them from the record being updated
1385 /// saving them into $foundclobs and $foundblobs [$fieldname]->contents
1386 /// They will be updated later
1387     if (($CFG->dbtype == 'oci8po' || $CFG->dbtype == 'mssql' || $CFG->dbtype == 'odbc_mssql' || $CFG->dbtype == 'mssql_n')
1388       && !empty($dataobject->id)) {
1389     /// Detect lobs
1390         $foundclobs = array();
1391         $foundblobs = array();
1392         db_detect_lobs($table, $dataobject, $foundclobs, $foundblobs, true);
1393     }
1394
1395     // Determine all the fields in the table
1396     if (!$columns = $db->MetaColumns($CFG->prefix . $table)) {
1397         return false;
1398     }
1399     $data = (array)$dataobject;
1400
1401     if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
1402
1403     // Pull out data matching these fields
1404     $ddd = array();
1405     foreach ($columns as $column) {
1406         if ($column->name <> 'id' and isset($data[$column->name]) ) {
1407             $ddd[$column->name] = $data[$column->name];
1408             // PostgreSQL bytea support
1409             if ($CFG->dbtype == 'postgres7' && $column->type == 'bytea') {
1410                 $ddd[$column->name] = $db->BlobEncode($ddd[$column->name]);
1411             }
1412         }
1413     }
1414
1415     // Construct SQL queries
1416     $numddd = count($ddd);
1417     $count = 0;
1418     $update = '';
1419
1420 /// Only if we have fields to be updated (this will prevent both wrong updates +
1421 /// updates of only LOBs in Oracle
1422     if ($numddd) {
1423         foreach ($ddd as $key => $value) {
1424             $count++;
1425             $update .= $key .' = \''. $value .'\'';   // All incoming data is already quoted
1426             if ($count < $numddd) {
1427                 $update .= ', ';
1428             }
1429         }
1430
1431         if (!$rs = $db->Execute('UPDATE '. $CFG->prefix . $table .' SET '. $update .' WHERE id = \''. $dataobject->id .'\'')) {
1432             debugging($db->ErrorMsg() .'<br /><br />UPDATE '. $CFG->prefix . $table .' SET '. $update .' WHERE id = \''. $dataobject->id .'\'');
1433             if (!empty($CFG->dblogerror)) {
1434                 $debug=array_shift(debug_backtrace());
1435                 error_log("SQL ".$db->ErrorMsg()." in {$debug['file']} on line {$debug['line']}. STATEMENT:  UPDATE $CFG->prefix$table SET $update WHERE id = '$dataobject->id'");
1436             }
1437             return false;
1438         }
1439     }
1440
1441 /// Under Oracle AND MSSQL, finally, Update all the Clobs and Blobs present in the record
1442 /// if we know we have some of them in the query
1443     if (($CFG->dbtype == 'oci8po' || $CFG->dbtype == 'mssql' || $CFG->dbtype == 'odbc_mssql' || $CFG->dbtype == 'mssql_n')
1444       && !empty($dataobject->id) &&
1445       (!empty($foundclobs) || !empty($foundblobs))) {
1446         if (!db_update_lobs($table, $dataobject->id, $foundclobs, $foundblobs)) {
1447             return false; //Some error happened while updating LOBs
1448         }
1449     }
1450
1451     return true;
1452 }
1453
1454
1455
1456 /**
1457  * Returns the proper SQL to do paging
1458  *
1459  * @uses $CFG
1460  * @param string $page Offset page number
1461  * @param string $recordsperpage Number of records per page
1462  * @deprecated Moodle 1.7 use the new $limitfrom, $limitnum available in all
1463  *             the get_recordXXX() funcions.
1464  * @return string
1465  */
1466 function sql_paging_limit($page, $recordsperpage) {
1467     global $CFG;
1468
1469     debugging('Function sql_paging_limit() is deprecated. Replace it with the correct use of limitfrom, limitnum parameters', DEBUG_DEVELOPER);
1470
1471     switch ($CFG->dbtype) {
1472         case 'postgres7':
1473              return 'LIMIT '. $recordsperpage .' OFFSET '. $page;
1474         default:
1475              return 'LIMIT '. $page .','. $recordsperpage;
1476     }
1477 }
1478
1479 /**
1480  * Returns the proper SQL to do LIKE in a case-insensitive way
1481  *
1482  * Note the LIKE are case sensitive for Oracle. Oracle 10g is required to use
1483  * the caseinsensitive search using regexp_like() or NLS_COMP=LINGUISTIC :-(
1484  * See http://docs.moodle.org/en/XMLDB_Problems#Case-insensitive_searches
1485  *
1486  * @uses $CFG
1487  * @return string
1488  */
1489 function sql_ilike() {
1490     global $CFG;
1491
1492     switch ($CFG->dbtype) {
1493         case 'postgres7':
1494              return 'ILIKE';
1495         default:
1496              return 'LIKE';
1497     }
1498 }
1499
1500
1501 /**
1502  * Returns the proper SQL (for the dbms in use) to concatenate $firstname and $lastname
1503  *
1504  * @uses $CFG
1505  * @param string $firstname User's first name
1506  * @param string $lastname User's last name
1507  * @return string
1508  */
1509 function sql_fullname($firstname='firstname', $lastname='lastname') {
1510     return sql_concat($firstname, "' '", $lastname);
1511 }
1512
1513 /**
1514  * Returns the proper SQL to do CONCAT between the elements passed
1515  * Can take many parameters - just a passthrough to $db->Concat()
1516  *
1517  * @uses $db
1518  * @param string $element
1519  * @return string
1520  */
1521 function sql_concat() {
1522     global $db;
1523
1524     $args = func_get_args();
1525     return call_user_func_array(array($db, 'Concat'), $args);
1526 }
1527
1528 /**
1529  * Returns the proper SQL to do CONCAT between the elements passed
1530  * with a given separator
1531  *
1532  * @uses $db
1533  * @param string $separator
1534  * @param array  $elements
1535  * @return string
1536  */
1537 function sql_concat_join($separator="' '", $elements=array()) {
1538     global $db;
1539
1540     // copy to ensure pass by value
1541     $elem = $elements;
1542
1543     // Intersperse $elements in the array.
1544     // Add items to the array on the fly, walking it
1545     // _backwards_ splicing the elements in. The loop definition
1546     // should skip first and last positions.
1547     for ($n=count($elem)-1; $n > 0 ; $n--) {
1548         array_splice($elem, $n, 0, $separator);
1549     }
1550     return call_user_func_array(array($db, 'Concat'), $elem);
1551 }
1552
1553 /**
1554  * Returns the proper SQL to do IS NULL
1555  * @uses $CFG
1556  * @param string $fieldname The field to add IS NULL to
1557  * @return string
1558  */
1559 function sql_isnull($fieldname) {
1560     global $CFG;
1561
1562     switch ($CFG->dbtype) {
1563         case 'mysql':
1564              return $fieldname.' IS NULL';
1565         default:
1566              return $fieldname.' IS NULL';
1567     }
1568 }
1569
1570 /**
1571  * Returns the proper AS keyword to be used to aliase columns
1572  * SQL defines the keyword as optional and nobody but PG
1573  * seems to require it. This function should be used inside all
1574  * the statements using column aliases.
1575  * Note than the use of table aliases doesn't require the
1576  * AS keyword at all, only columns for postgres.
1577  * @uses $CFG
1578  * @ return string the keyword
1579  * @deprecated Moodle 1.7 because coding guidelines now enforce to use AS in column aliases
1580  */
1581 function sql_as() {
1582     global $CFG, $db;
1583
1584     switch ($CFG->dbtype) {
1585         case 'postgres7':
1586             return 'AS';
1587         default:
1588             return '';
1589     }
1590 }
1591
1592 /**
1593  * Returns the proper substr() function for each DB
1594  * Relies on ADOdb $db->substr property
1595  */
1596 function sql_substr() {
1597
1598     global $db;
1599
1600     return $db->substr;
1601 }
1602
1603 /**
1604  * Returns the SQL text to be used to order by one TEXT (clob) column, because
1605  * some RDBMS doesn't support direct ordering of such fields.
1606  * Note that the use or queries being ordered by TEXT columns must be minimised,
1607  * because it's really slooooooow.
1608  * @param string fieldname the name of the TEXT field we need to order by
1609  * @param string number of chars to use for the ordering (defaults to 32)
1610  * @return string the piece of SQL code to be used in your statement.
1611  */
1612 function sql_order_by_text($fieldname, $numchars=32) {
1613
1614     global $CFG;
1615
1616     switch ($CFG->dbtype) {
1617         case 'mssql':
1618         case 'mssql_n':
1619         case 'odbc_mssql':
1620             return 'CONVERT(varchar, ' . $fieldname . ', ' . $numchars . ')';
1621             break;
1622         case 'oci8po':
1623             return 'dbms_lob.substr(' . $fieldname . ', ' . $numchars . ',1)';
1624             break;
1625         default:
1626             return $fieldname;
1627     }
1628 }
1629
1630
1631 /**
1632  * Returns SQL to be used as a subselect to find the primary role of users.
1633  * Geoff Cant <geoff@catalyst.net.nz> (the author) is very keen for this to
1634  * be implemented as a view in future versions.
1635  *
1636  * eg if this function returns a string called $primaryroles, then you could:
1637  * $sql = 'SELECT COUNT(DISTINCT prs.userid) FROM ('.$primary_roles.') prs
1638  *          WHERE prs.primary_roleid='.$role->id.' AND prs.courseid='.$course->id.
1639  *          ' AND prs.contextlevel = '.CONTEXT_COURSE;
1640  *
1641  * @return string the piece of SQL code to be used in your FROM( ) statement.
1642  */
1643 function sql_primary_role_subselect() {
1644     global $CFG;
1645     return 'SELECT ra.userid,
1646                 ra.roleid AS primary_roleid,
1647                 ra.contextid,
1648                 r.sortorder,
1649                 r.name,
1650                 r.description,
1651                 r.shortname,
1652                 c.instanceid AS courseid,
1653                 c.contextlevel
1654             FROM '.$CFG->prefix.'role_assignments ra
1655             INNER JOIN '.$CFG->prefix.'role r ON ra.roleid = r.id
1656             INNER JOIN '.$CFG->prefix.'context c ON ra.contextid = c.id
1657             WHERE NOT EXISTS (
1658                               SELECT 1
1659                               FROM '.$CFG->prefix.'role_assignments i_ra
1660                               INNER JOIN '.$CFG->prefix.'role i_r ON i_ra.roleid = i_r.id
1661                               WHERE ra.userid = i_ra.userid AND
1662                                      ra.contextid = i_ra.contextid AND
1663                                      i_r.sortorder < r.sortorder
1664                               ) ';
1665 }
1666
1667 /**
1668  * Prepare a SQL WHERE clause to select records where the given fields match the given values.
1669  *
1670  * Prepares a where clause of the form
1671  *     WHERE field1 = value1 AND field2 = value2 AND field3 = value3
1672  * except that you need only specify as many arguments (zero to three) as you need.
1673  *
1674  * @param string $field1 the first field to check (optional).
1675  * @param string $value1 the value field1 must have (requred if field1 is given, else optional).
1676  * @param string $field2 the second field to check (optional).
1677  * @param string $value2 the value field2 must have (requred if field2 is given, else optional).
1678  * @param string $field3 the third field to check (optional).
1679  * @param string $value3 the value field3 must have (requred if field3 is given, else optional).
1680  */
1681 function where_clause($field1='', $value1='', $field2='', $value2='', $field3='', $value3='') {
1682     if ($field1) {
1683         $select = "WHERE $field1 = '$value1'";
1684         if ($field2) {
1685             $select .= " AND $field2 = '$value2'";
1686             if ($field3) {
1687                 $select .= " AND $field3 = '$value3'";
1688             }
1689         }
1690     } else {
1691         $select = '';
1692     }
1693     return $select;
1694 }
1695
1696 /**
1697  * Get the data type of a table column, using an ADOdb MetaType() call.
1698  *
1699  * @uses $CFG
1700  * @uses $db
1701  * @param string $table The name of the database table
1702  * @param string $column The name of the field in the table
1703  * @return string Field type or false if error
1704  */
1705
1706 function column_type($table, $column) {
1707     global $CFG, $db;
1708
1709     if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
1710
1711     if(!$rs = $db->Execute('SELECT '.$column.' FROM '.$CFG->prefix.$table.' WHERE 1=2')) {
1712         return false;
1713     }
1714
1715     $field = $rs->FetchField(0);
1716     return $rs->MetaType($field->type);
1717 }
1718
1719 /**
1720  * This function will execute an array of SQL commands, returning
1721  * true/false if any error is found and stopping/continue as desired.
1722  * It's widely used by all the ddllib.php functions
1723  *
1724  * @param array sqlarr array of sql statements to execute
1725  * @param boolean continue to specify if must continue on error (true) or stop (false)
1726  * @param boolean feedback to specify to show status info (true) or not (false)
1727  * @param boolean true if everything was ok, false if some error was found
1728  */
1729 function execute_sql_arr($sqlarr, $continue=true, $feedback=true) {
1730
1731     if (!is_array($sqlarr)) {
1732         return false;
1733     }
1734
1735     $status = true;
1736     foreach($sqlarr as $sql) {
1737         if (!execute_sql($sql, $feedback)) {
1738             $status = false;
1739             if (!$continue) {
1740                 break;
1741             }
1742         }
1743     }
1744     return $status;
1745 }
1746
1747 /**
1748  * This function, called from setup.php includes all the configuration
1749  * needed to properly work agains any DB. It setups connection encoding
1750  * and some other variables.
1751  */
1752 function configure_dbconnection() {
1753
1754     global $CFG, $db;
1755
1756     switch ($CFG->dbtype) {
1757         case 'mysql':
1758         /// Set names if needed
1759             if ($CFG->unicodedb) {
1760                 $db->Execute("SET NAMES 'utf8'");
1761             }
1762             break;
1763         case 'postgres7':
1764         /// Set names if needed
1765             if ($CFG->unicodedb) {
1766                 $db->Execute("SET NAMES 'utf8'");
1767             }
1768             break;
1769         case 'mssql':
1770         case 'mssql_n':
1771         case 'odbc_mssql':
1772         /// No need to set charset. It must be specified in the driver conf
1773         /// Allow quoted identifiers
1774             $db->Execute('SET QUOTED_IDENTIFIER ON');
1775         /// Force ANSI nulls so the NULL check was done by IS NULL and NOT IS NULL
1776         /// instead of equal(=) and distinct(<>) simbols
1777             $db->Execute('SET ANSI_NULLS ON');
1778         /// Enable sybase quotes, so addslashes and stripslashes will use "'"
1779             ini_set('magic_quotes_sybase', '1');
1780         /// NOTE: Not 100% useful because GPC has been addslashed with the setting off
1781         ///       so IT'S MANDATORY TO CHANGE THIS UNDER php.ini or .htaccess for this DB
1782         ///       or to turn off magic_quotes to allow Moodle to do it properly
1783             break;
1784         case 'oci8po':
1785         /// No need to set charset. It must be specified by the NLS_LANG env. variable
1786         /// Enable sybase quotes, so addslashes and stripslashes will use "'"
1787             ini_set('magic_quotes_sybase', '1');
1788         /// NOTE: Not 100% useful because GPC has been addslashed with the setting off
1789         ///       so IT'S MANDATORY TO ENABLE THIS UNDER php.ini or .htaccess for this DB
1790         ///       or to turn off magic_quotes to allow Moodle to do it properly
1791             break;
1792     }
1793 }
1794
1795 /**
1796  * This function will handle all the records before being inserted/updated to DB for Oracle
1797  * installations. This is because the "special feature" of Oracle where the empty string is
1798  * equal to NULL and this presents a problem with all our currently NOT NULL default '' fields.
1799  *
1800  * Once Moodle DB will be free of this sort of false NOT NULLS, this hack could be removed safely
1801  *
1802  * Note that this function is 100% private and should be used, exclusively by DML functions
1803  * in this file. Also, this is considered a DIRTY HACK to be removed when possible. (stronk7)
1804  *
1805  * This function is private and must not be used outside dmllib at all
1806  *
1807  * @param $table string the table where the record is going to be inserted/updated (without prefix)
1808  * @param $dataobject object the object to be inserted/updated
1809  * @param $usecache boolean flag to determinate if we must use the per request cache of metadata
1810  *        true to use it, false to ignore and delete it
1811  */
1812 function oracle_dirty_hack ($table, &$dataobject, $usecache = true) {
1813
1814     global $CFG, $db, $metadata_cache;
1815
1816 /// Init and delete metadata cache
1817     if (!isset($metadata_cache) || !$usecache) {
1818         $metadata_cache = array();
1819     }
1820
1821 /// For Oracle DB, empty strings are converted to NULLs in DB
1822 /// and this breaks a lot of NOT NULL columns currenty Moodle. In the future it's
1823 /// planned to move some of them to NULL, if they must accept empty values and this
1824 /// piece of code will become less and less used. But, for now, we need it.
1825 /// What we are going to do is to examine all the data being inserted and if it's
1826 /// an empty string (NULL for Oracle) and the field is defined as NOT NULL, we'll modify
1827 /// such data in the best form possible ("0" for booleans and numbers and " " for the
1828 /// rest of strings. It isn't optimal, but the only way to do so.
1829 /// In the oppsite, when retrieving records from Oracle, we'll decode " " back to
1830 /// empty strings to allow everything to work properly. DIRTY HACK.
1831
1832 /// If the db isn't Oracle, return without modif
1833     if ( $CFG->dbtype != 'oci8po') {
1834         return;
1835     }
1836
1837 /// Get Meta info to know what to change, using the cached meta if exists
1838     if (!isset($metadata_cache[$table])) {
1839         $metadata_cache[$table] = array_change_key_case($db->MetaColumns($CFG->prefix . $table), CASE_LOWER);
1840     }
1841     $columns = $metadata_cache[$table];
1842 /// Iterate over all the fields in the insert, transforming values
1843 /// in the best possible form
1844     foreach ($dataobject as $fieldname => $fieldvalue) {
1845     /// If the field doesn't exist in metadata, skip
1846         if (!isset($columns[strtolower($fieldname)])) {
1847             continue;
1848         }
1849     /// If the field ins't VARCHAR or CLOB, skip
1850         if ($columns[strtolower($fieldname)]->type != 'VARCHAR2' && $columns[strtolower($fieldname)]->type != 'CLOB') {
1851             continue;
1852         }
1853     /// If the field isn't NOT NULL, skip (it's nullable, so accept empty values)
1854         if (!$columns[strtolower($fieldname)]->not_null) {
1855             continue;
1856         }
1857     /// If the value isn't empty, skip
1858         if (!empty($fieldvalue)) {
1859             continue;
1860         }
1861     /// Now, we have one empty value, going to be inserted to one NOT NULL, VARCHAR2 or CLOB field
1862     /// Try to get the best value to be inserted
1863         if (gettype($fieldvalue) == 'boolean') {
1864             $dataobject->$fieldname = '0'; /// Transform false to '0' that evaluates the same for PHP
1865         } else if (gettype($fieldvalue) == 'integer') {
1866             $dataobject->$fieldname = '0'; /// Transform 0 to '0' that evaluates the same for PHP
1867         } else if (gettype($fieldvalue) == 'NULL') {
1868             $dataobject->$fieldname = '0'; /// Transform NULL to '0' that evaluates the same for PHP
1869         } else {
1870             $dataobject->$fieldname = ' '; /// Transform '' to ' ' that DONT'T EVALUATE THE SAME
1871                                            /// (we'll transform back again on get_records_XXX functions and others)!!
1872         }
1873     }
1874 }
1875 /// End of DIRTY HACK
1876
1877 /**
1878  * This function will search for all the CLOBs and BLOBs fields passed in the dataobject, replacing
1879  * their contents by the fixed strings '@#CLOB#@' and '@#BLOB#@' and returning one array for all the
1880  * found CLOBS and another for all the found BLOBS
1881  * Used by Oracle drivers to perform the two-step insertion/update of LOBs and
1882  * by MSSQL to perform the same exclusively for BLOBs (IMAGE fields)
1883  *
1884  * This function is private and must not be used outside dmllib at all
1885  *
1886  * @param $table string the table where the record is going to be inserted/updated (without prefix)
1887  * @param $dataobject object the object to be inserted/updated
1888  * @param $clobs array of clobs detected
1889  * @param $dataobject array of blobs detected
1890  * @param $unset boolean to specify if we must unset found LOBs from the original object (true) or
1891  *        just return them modified to @#CLOB#@ and @#BLOB#@ (false)
1892  * @param $usecache boolean flag to determinate if we must use the per request cache of metadata
1893  *        true to use it, false to ignore and delete it
1894  */
1895 function db_detect_lobs ($table, &$dataobject, &$clobs, &$blobs, $unset = false, $usecache = true) {
1896
1897     global $CFG, $db, $metadata_cache;
1898
1899     $dataarray = (array)$dataobject; //Convert to array. It's supposed that PHP 4.3 doesn't iterate over objects
1900
1901 /// Initial configuration, based on DB
1902     switch ($CFG->dbtype) {
1903         case 'oci8po':
1904             $clobdbtype = 'CLOB'; //Name of clobs for this DB
1905             $blobdbtype = 'BLOB'; //Name of blobs for this DB
1906             break;
1907         case 'mssql':
1908         case 'odbc_mssql':
1909         case 'mssql_n':
1910             $clobdbtype = 'NOTPROCESSES'; //Name of clobs for this DB (under mssql flavours we don't process CLOBS)
1911             $blobdbtype = 'IMAGE'; //Name of blobs for this DB
1912             break;
1913         default:
1914             return; //Other DB doesn't need this two step to happen, prevent continue
1915     }
1916
1917 /// Init and delete metadata cache
1918     if (!isset($metadata_cache) || !$usecache) {
1919         $metadata_cache = array();
1920     }
1921
1922 /// Get Meta info to know what to change, using the cached meta if exists
1923     if (!isset($metadata_cache[$table])) {
1924         $metadata_cache[$table] = array_change_key_case($db->MetaColumns($CFG->prefix . $table), CASE_LOWER);
1925     }
1926     $columns = $metadata_cache[$table];
1927
1928     foreach ($dataarray as $fieldname => $fieldvalue) {
1929     /// If the field doesn't exist in metadata, skip
1930         if (!isset($columns[strtolower($fieldname)])) {
1931             continue;
1932         }
1933     /// If the field is CLOB, update its value to '@#CLOB#@' and store it in the $clobs array
1934         if (strtoupper($columns[strtolower($fieldname)]->type) == $clobdbtype) {
1935         /// Oracle optimization. CLOBs under 4000cc can be directly inserted (no need to apply 2-phases to them)
1936             if ($db->dbtype = 'oci8po' && strlen($dataobject->$fieldname) < 4000) {
1937                 continue;
1938             }
1939             $clobs[$fieldname] = $dataobject->$fieldname;
1940             if ($unset) {
1941                 unset($dataobject->$fieldname);
1942             } else {
1943                 $dataobject->$fieldname = '@#CLOB#@';
1944             }
1945             continue;
1946         }
1947
1948     /// If the field is BLOB OR IMAGE, update its value to '@#BLOB#@' and store it in the $blobs array
1949         if (strtoupper($columns[strtolower($fieldname)]->type) == $blobdbtype) {
1950             $blobs[$fieldname] = $dataobject->$fieldname;
1951             if ($unset) {
1952                 unset($dataobject->$fieldname);
1953             } else {
1954                 $dataobject->$fieldname = '@#BLOB#@';
1955             }
1956             continue;
1957         }
1958     }
1959 }
1960
1961 /**
1962  * This function will iterate over $clobs and $blobs array, executing the needed
1963  * UpdateClob() and UpdateBlob() ADOdb function calls to store LOBs contents properly
1964  * Records to be updated are always searched by PK (id always!)
1965  *
1966  * Used by Orace CLOBS and BLOBS and MSSQL IMAGES
1967  *
1968  * This function is private and must not be used outside dmllib at all
1969  *
1970  * @param $table string the table where the record is going to be inserted/updated (without prefix)
1971  * @param $sqlcondition mixed value defining the records to be LOB-updated. It it's a number, must point
1972  *        to the PK og the table (id field), else it's processed as one harcoded SQL condition (WHERE clause)
1973  * @param $clobs array of clobs to be updated
1974  * @param $blobs array of blobs to be updated
1975  */
1976 function db_update_lobs ($table, $sqlcondition, &$clobs, &$blobs) {
1977
1978     global $CFG, $db;
1979
1980     $status = true;
1981
1982 /// Initial configuration, based on DB
1983     switch ($CFG->dbtype) {
1984         case 'oci8po':
1985             $clobdbtype = 'CLOB'; //Name of clobs for this DB
1986             $blobdbtype = 'BLOB'; //Name of blobs for this DB
1987             break;
1988         case 'mssql':
1989         case 'odbc_mssql':
1990         case 'mssql_n':
1991             $clobdbtype = 'NOTPROCESSES'; //Name of clobs for this DB (under mssql flavours we don't process CLOBS)
1992             $blobdbtype = 'IMAGE'; //Name of blobs for this DB
1993             break;
1994         default:
1995             return; //Other DB doesn't need this two step to happen, prevent continue
1996     }
1997
1998 /// Calculate the update sql condition
1999     if (is_numeric($sqlcondition)) { /// If passing a number, it's the PK of the table (id)
2000         $sqlcondition = 'id=' . $sqlcondition;
2001     } else { /// Else, it's a formal standard SQL condition, we try to delete the WHERE in case it exists
2002         $sqlcondition = trim(preg_replace('/^WHERE/is', '', trim($sqlcondition)));
2003     }
2004
2005 /// Update all the clobs
2006     if ($clobs) {
2007         foreach ($clobs as $key => $value) {
2008
2009             if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; }; /// Count the extra updates in PERF
2010
2011             if (!$db->UpdateClob($CFG->prefix.$table, $key, $value, $sqlcondition)) {
2012                 $status = false;
2013                 $statement = "UpdateClob('$CFG->prefix$table', '$key', '" . substr($value, 0, 100) . "...', '$sqlcondition')";
2014                 debugging($db->ErrorMsg() ."<br /><br />$statement");
2015                 if (!empty($CFG->dblogerror)) {
2016                     $debug=array_shift(debug_backtrace());
2017                     error_log("SQL ".$db->ErrorMsg()." in {$debug['file']} on line {$debug['line']}. STATEMENT:  $statement");
2018                 }
2019             }
2020         }
2021     }
2022 /// Update all the blobs
2023     if ($blobs) {
2024         foreach ($blobs as $key => $value) {
2025
2026             if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; }; /// Count the extra updates in PERF
2027
2028             if(!$db->UpdateBlob($CFG->prefix.$table, $key, $value, $sqlcondition)) {
2029                 $status = false;
2030                 $statement = "UpdateBlob('$CFG->prefix$table', '$key', '" . substr($value, 0, 100) . "...', '$sqlcondition')";
2031                 debugging($db->ErrorMsg() ."<br /><br />$statement");
2032                 if (!empty($CFG->dblogerror)) {
2033                     $debug=array_shift(debug_backtrace());
2034                     error_log("SQL ".$db->ErrorMsg()." in {$debug['file']} on line {$debug['line']}. STATEMENT:  $statement");
2035                 }
2036             }
2037         }
2038     }
2039     return $status;
2040 }
2041
2042 ?>