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