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