Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
tree: 6e58026b29
Fetching contributors…

Cannot retrieve contributors at this time

file 1552 lines (1351 sloc) 58.53 kb
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552
<?php // $Id$

///////////////////////////////////////////////////////////////////////////
// //
// NOTICE OF COPYRIGHT //
// //
// Moodle - Modular Object-Oriented Dynamic Learning Environment //
// http://moodle.com //
// //
// Copyright (C) 2001-3001 Martin Dougiamas http://dougiamas.com //
// //
// This program is free software; you can redistribute it and/or modify //
// it under the terms of the GNU General Public License as published by //
// the Free Software Foundation; either version 2 of the License, or //
// (at your option) any later version. //
// //
// This program is distributed in the hope that it will be useful, //
// but WITHOUT ANY WARRANTY; without even the implied warranty of //
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the //
// GNU General Public License for more details: //
// //
// http://www.gnu.org/copyleft/gpl.html //
// //
///////////////////////////////////////////////////////////////////////////

/// This library contains all the Data Manipulation Language (DML) functions
/// used to interact with the DB. All the dunctions in this library must be
/// generic and work against the major number of RDBMS possible. This is the
/// list of currently supported and tested DBs: mysql, postresql, mssql, oracle

/// This library is automatically included by Moodle core so you never need to
/// include it yourself.

/// For more info about the functions available in this library, please visit:
/// http://docs.moodle.org/en/DML_functions
/// (feel free to modify, improve and document such page, thanks!)

/// GLOBAL CONSTANTS /////////////////////////////////////////////////////////

$empty_rs_cache = array(); // Keeps copies of the recordsets used in one invocation

/// FUNCTIONS FOR DATABASE HANDLING ////////////////////////////////

/**
* Execute a given sql command string
*
* Completely general function - it just runs some SQL and reports success.
*
* @uses $db
* @param string $command The sql string you wish to be executed.
* @param bool $feedback Set this argument to true if the results generated should be printed. Default is true.
* @return string
*/
function execute_sql($command, $feedback=true) {
/// Completely general function - it just runs some SQL and reports success.

    global $db, $CFG;

    $olddebug = $db->debug;

    if (!$feedback) {
        $db->debug = false;
    }

    $empty_rs_cache = array(); // Clear out the cache, just in case changes were made to table structures

    if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };

    $result = $db->Execute($command);

    $db->debug = $olddebug;

    if ($result) {
        if ($feedback) {
            notify(get_string('success'), 'notifysuccess');
        }
        return true;
    } else {
        if ($feedback) {
            notify('<strong>' . get_string('error') . '</strong>');
        }
        if (!empty($CFG->dblogerror)) {
            $debug=array_shift(debug_backtrace());
            error_log("SQL ".$db->ErrorMsg()." in {$debug['file']} on line {$debug['line']}. STATEMENT: $command");
        }
        return false;
    }
}

/**
* on DBs that support it, switch to transaction mode and begin a transaction
* you'll need to ensure you call commit_sql() or your changes *will* be lost.
*
* Now using ADOdb standard transactions. Some day, we should switch to
* Smart Transactions (http://phplens.com/adodb/tutorial.smart.transactions.html)
* as they autodetect errors and are nestable and easier to write
*
* this is _very_ useful for massive updates
*/
function begin_sql() {

    global $db;

    $db->BeginTrans();

    return true;
}

/**
* on DBs that support it, commit the transaction
*
* Now using ADOdb standard transactions. Some day, we should switch to
* Smart Transactions (http://phplens.com/adodb/tutorial.smart.transactions.html)
* as they autodetect errors and are nestable and easier to write
*/
function commit_sql() {

    global $db;

    $db->CommitTrans();

    return true;
}

/**
* on DBs that support it, rollback the transaction
*
* Now using ADOdb standard transactions. Some day, we should switch to
* Smart Transactions (http://phplens.com/adodb/tutorial.smart.transactions.html)
* as they autodetect errors and are nestable and easier to write
*/
function rollback_sql() {

    global $db;

    $db->RollbackTrans();

    return true;
}

/**
* returns db specific uppercase function
* @deprecated Moodle 1.7 because all the RDBMS use upper()
*/
function db_uppercase() {
    return "upper";
}

/**
* returns db specific lowercase function
* @deprecated Moodle 1.7 because all the RDBMS use lower()
*/
function db_lowercase() {
    return "lower";
}


/**
* Run an arbitrary sequence of semicolon-delimited SQL commands
*
* Assumes that the input text (file or string) consists of
* a number of SQL statements ENDING WITH SEMICOLONS. The
* semicolons MUST be the last character in a line.
* Lines that are blank or that start with "#" or "--" (postgres) are ignored.
* Only tested with mysql dump files (mysqldump -p -d moodle)
*
* @uses $CFG
* @param string $sqlfile The path where a file with sql commands can be found on the server.
* @param string $sqlstring If no path is supplied then a string with semicolon delimited sql
* commands can be supplied in this argument.
* @return bool Returns true if databse was modified successfully.
*/
function modify_database($sqlfile='', $sqlstring='') {

    global $CFG;

    $success = true; // Let's be optimistic

    if (!empty($sqlfile)) {
        if (!is_readable($sqlfile)) {
            $success = false;
            echo '<p>Tried to modify database, but "'. $sqlfile .'" doesn\'t exist!</p>';
            return $success;
        } else {
            $lines = file($sqlfile);
        }
    } else {
        $sqlstring = trim($sqlstring);
        if ($sqlstring{strlen($sqlstring)-1} != ";") {
            $sqlstring .= ";"; // add it in if it's not there.
        }
        $lines[] = $sqlstring;
    }

    $command = '';

    foreach ($lines as $line) {
        $line = rtrim($line);
        $length = strlen($line);

        if ($length and $line[0] <> '#' and $line[0].$line[1] <> '--') {
            if (substr($line, $length-1, 1) == ';') {
                $line = substr($line, 0, $length-1); // strip ;
                $command .= $line;
                $command = str_replace('prefix_', $CFG->prefix, $command); // Table prefixes
                if (! execute_sql($command)) {
                    $success = false;
                }
                $command = '';
            } else {
                $command .= $line;
            }
        }
    }

    return $success;

}

/// GENERIC FUNCTIONS TO CHECK AND COUNT RECORDS ////////////////////////////////////////

/**
* Test whether a record exists in a table where all the given fields match the given values.
*
* The record to test is specified by giving up to three fields that must
* equal the corresponding values.
*
* @uses $CFG
* @param string $table The table to check.
* @param string $field1 the first field to check (optional).
* @param string $value1 the value field1 must have (requred if field1 is given, else optional).
* @param string $field2 the second field to check (optional).
* @param string $value2 the value field2 must have (requred if field2 is given, else optional).
* @param string $field3 the third field to check (optional).
* @param string $value3 the value field3 must have (requred if field3 is given, else optional).
* @return bool true if a matching record exists, else false.
*/
function record_exists($table, $field1='', $value1='', $field2='', $value2='', $field3='', $value3='') {

    global $CFG;

    $select = where_clause($field1, $value1, $field2, $value2, $field3, $value3);

    return record_exists_sql('SELECT * FROM '. $CFG->prefix . $table .' '. $select);
}

/**
* Test whether any records exists in a table which match a particular WHERE clause.
*
* @uses $CFG
* @param string $table The database table to be checked against.
* @param string $select A fragment of SQL to be used in a WHERE clause in the SQL call.
* @return bool true if a matching record exists, else false.
*/
function record_exists_select($table, $select='') {

    global $CFG;

    if ($select) {
        $select = 'WHERE '.$select;
    }

    return record_exists_sql('SELECT * FROM '. $CFG->prefix . $table . ' ' . $select);
}

/**
* Test whether a SQL SELECT statement returns any records.
*
* This function returns true if the SQL statement executes
* without any errors and returns at least one record.
*
* @param string $sql The SQL statement to execute.
* @return bool true if the SQL executes without errors and returns at least one record.
*/
function record_exists_sql($sql) {

    $limitfrom = 0; /// Number of records to skip
    $limitnum = 1; /// Number of records to retrieve

    $rs = get_recordset_sql($sql, $limitfrom, $limitnum);

    if ($rs && $rs->RecordCount() > 0) {
        return true;
    } else {
        return false;
    }
}

/**
* Count the records in a table where all the given fields match the given values.
*
* @uses $CFG
* @param string $table The table to query.
* @param string $field1 the first field to check (optional).
* @param string $value1 the value field1 must have (requred if field1 is given, else optional).
* @param string $field2 the second field to check (optional).
* @param string $value2 the value field2 must have (requred if field2 is given, else optional).
* @param string $field3 the third field to check (optional).
* @param string $value3 the value field3 must have (requred if field3 is given, else optional).
* @return int The count of records returned from the specified criteria.
*/
function count_records($table, $field1='', $value1='', $field2='', $value2='', $field3='', $value3='') {

    global $CFG;

    $select = where_clause($field1, $value1, $field2, $value2, $field3, $value3);

    return count_records_sql('SELECT COUNT(*) FROM '. $CFG->prefix . $table .' '. $select);
}

/**
* Count the records in a table which match a particular WHERE clause.
*
* @uses $CFG
* @param string $table The database table to be checked against.
* @param string $select A fragment of SQL to be used in a WHERE clause in the SQL call.
* @param string $countitem The count string to be used in the SQL call. Default is COUNT(*).
* @return int The count of records returned from the specified criteria.
*/
function count_records_select($table, $select='', $countitem='COUNT(*)') {

    global $CFG;

    if ($select) {
        $select = 'WHERE '.$select;
    }

    return count_records_sql('SELECT '. $countitem .' FROM '. $CFG->prefix . $table .' '. $select);
}

/**
* Get the result of a SQL SELECT COUNT(...) query.
*
* Given a query that counts rows, return that count. (In fact,
* given any query, return the first field of the first record
* returned. However, this method should only be used for the
* intended purpose.) If an error occurrs, 0 is returned.
*
* @uses $CFG
* @uses $db
* @param string $sql The SQL string you wish to be executed.
* @return int the count. If an error occurrs, 0 is returned.
*/
function count_records_sql($sql) {
    $rs = get_recordset_sql($sql);

    if ($rs) {
        return reset($rs->fields);
    } else {
        return 0;
    }
}

/// GENERIC FUNCTIONS TO GET, INSERT, OR UPDATE DATA ///////////////////////////////////

/**
* Get a single record as an object
*
* @uses $CFG
* @param string $table The table to select from.
* @param string $field1 the first field to check (optional).
* @param string $value1 the value field1 must have (requred if field1 is given, else optional).
* @param string $field2 the second field to check (optional).
* @param string $value2 the value field2 must have (requred if field2 is given, else optional).
* @param string $field3 the third field to check (optional).
* @param string $value3 the value field3 must have (requred if field3 is given, else optional).
* @return mixed a fieldset object containing the first mathcing record, or false if none found.
*/
function get_record($table, $field1, $value1, $field2='', $value2='', $field3='', $value3='', $fields='*') {

    global $CFG;

    $select = where_clause($field1, $value1, $field2, $value2, $field3, $value3);

    return get_record_sql('SELECT '.$fields.' FROM '. $CFG->prefix . $table .' '. $select);
}

/**
* Get a single record as an object using an SQL statement
*
* The SQL statement should normally only return one record. In debug mode
* you will get a warning if more record is returned (unless you
* set $expectmultiple to true). In non-debug mode, it just returns
* the first record.
*
* @uses $CFG
* @uses $db
* @param string $sql The SQL string you wish to be executed, should normally only return one record.
* @param bool $expectmultiple If the SQL cannot be written to conviniently return just one record,
* set this to true to hide the debug message.
* @param bool $nolimit sometimes appending ' LIMIT 1' to the SQL causes an error. Set this to true
* to stop your SQL being modified. This argument should probably be deprecated.
* @return Found record as object. False if not found or error
*/
function get_record_sql($sql, $expectmultiple=false, $nolimit=false) {

    global $CFG;

/// Default situation
    $limitfrom = 0; /// Number of records to skip
    $limitnum = 1; /// Number of records to retrieve

/// Only a few uses of the 2nd and 3rd parameter have been found
/// I think that we should avoid to use them completely, one
/// record is one record, and everything else should return error.
/// So the proposal is to change all the uses, (4-5 inside Moodle
/// Core), drop them from the definition and delete the next two
/// "if" sentences. (eloy, 2006-08-19)

    if ($nolimit) {
        $limitfrom = 0;
        $limitnum = 0;
    } else if ($expectmultiple) {
        $limitfrom = 0;
        $limitnum = 1;
    } else if (debugging()) {
        // Debugging mode - don't use a limit of 1, but do change the SQL, because sometimes that
        // causes errors, and in non-debug mode you don't see the error message and it is
        // impossible to know what's wrong.
        $limitfrom = 0;
        $limitnum = 100;
    }

    if (!$rs = get_recordset_sql($sql, $limitfrom, $limitnum)) {
        return false;
    }

    $recordcount = $rs->RecordCount();

    if ($recordcount == 0) { // Found no records
        return false;

    } else if ($recordcount == 1) { // Found one record
    /// DIRTY HACK to retrieve all the ' ' (1 space) fields converted back
    /// to '' (empty string) for Oracle. It's the only way to work with
    /// all those NOT NULL DEFAULT '' fields until we definetively delete them
        if ($CFG->dbtype == 'oci8po') {
            array_walk($rs->fields, 'onespace2empty');
        }
    /// End od DIRTY HACK
        return (object)$rs->fields;

    } else { // Error: found more than one record
        notify('Error: Turn off debugging to hide this error.');
        notify($sql . '(with limits ' . $limitfrom . ', ' . $limitnum . ')');
        if ($records = $rs->GetAssoc(true)) {
            notify('Found more than one record in get_record_sql !');
            print_object($records);
        } else {
            notify('Very strange error in get_record_sql !');
            print_object($rs);
        }
        print_continue("$CFG->wwwroot/$CFG->admin/config.php");
    }
}

/**
* Gets one record from a table, as an object
*
* @uses $CFG
* @param string $table The database table to be checked against.
* @param string $select A fragment of SQL to be used in a where clause in the SQL call.
* @param string $fields A comma separated list of fields to be returned from the chosen table.
* @return object|false Returns an array of found records (as objects) or false if no records or error occured.
*/
function get_record_select($table, $select='', $fields='*') {

    global $CFG;

    if ($select) {
        $select = 'WHERE '. $select;
    }

    return get_record_sql('SELECT '. $fields .' FROM '. $CFG->prefix . $table .' '. $select);
}

/**
* Get a number of records as an ADODB RecordSet.
*
* Selects records from the table $table.
*
* If specified, only records where the field $field has value $value are retured.
*
* If specified, the results will be sorted as specified by $sort. This
* is added to the SQL as "ORDER BY $sort". Example values of $sort
* mightbe "time ASC" or "time DESC".
*
* If $fields is specified, only those fields are returned.
*
* This function is internal to datalib, and should NEVER should be called directly
* from general Moodle scripts. Use get_record, get_records etc.
*
* If you only want some of the records, specify $limitfrom and $limitnum.
* The query will skip the first $limitfrom records (according to the sort
* order) and then return the next $limitnum records. If either of $limitfrom
* or $limitnum is specified, both must be present.
*
* The return value is an ADODB RecordSet object
* @link http://phplens.com/adodb/reference.functions.adorecordset.html
* if the query succeeds. If an error occurrs, false is returned.
*
* @param string $table the table to query.
* @param string $field a field to check (optional).
* @param string $value the value the field must have (requred if field1 is given, else optional).
* @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
* @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
* @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
* @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
* @return mixed an ADODB RecordSet object, or false if an error occured.
*/
function get_recordset($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {

    if ($field) {
        $select = "$field = '$value'";
    } else {
        $select = '';
    }

    return get_recordset_select($table, $select, $sort, $fields, $limitfrom, $limitnum);
}

/**
* Get a number of records as an ADODB RecordSet.
*
* If given, $select is used as the SELECT parameter in the SQL query,
* otherwise all records from the table are returned.
*
* Other arguments and the return type as for @see function get_recordset.
*
* @uses $CFG
* @param string $table the table to query.
* @param string $select A fragment of SQL to be used in a where clause in the SQL call.
* @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
* @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
* @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
* @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
* @return mixed an ADODB RecordSet object, or false if an error occured.
*/
function get_recordset_select($table, $select='', $sort='', $fields='*', $limitfrom='', $limitnum='') {

    global $CFG;

    if ($select) {
        $select = ' WHERE '. $select;
    }

    if ($limitfrom !== '') {
        $limit = sql_paging_limit($limitfrom, $limitnum);
    } else {
        $limit = '';
    }

    if ($sort) {
        $sort = ' ORDER BY '. $sort;
    }

    return get_recordset_sql('SELECT '. $fields .' FROM '. $CFG->prefix . $table . $select . $sort .' '. $limit);
}

/**
* Get a number of records as an ADODB RecordSet.
*
* Only records where $field takes one of the values $values are returned.
* $values should be a comma-separated list of values, for example "4,5,6,10"
* or "'foo','bar','baz'".
*
* Other arguments and the return type as for @see function get_recordset.
*
* @param string $table the table to query.
* @param string $field a field to check (optional).
* @param string $values comma separated list of values the field must have (requred if field is given, else optional).
* @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
* @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
* @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
* @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
* @return mixed an ADODB RecordSet object, or false if an error occured.
*/
function get_recordset_list($table, $field='', $values='', $sort='', $fields='*', $limitfrom='', $limitnum='') {

    if ($field) {
        $select = "$field IN ($values)";
    } else {
        $select = '';
    }

    return get_recordset_select($table, $select, $sort, $fields, $limitfrom, $limitnum);
}

/**
* Get a number of records as an ADODB RecordSet. $sql must be a complete SQL query.
* This function is internal to datalib, and should NEVER should be called directly
* from general Moodle scripts. Use get_record, get_records etc.
*
* The return type is as for @see function get_recordset.
*
* @uses $CFG
* @uses $db
* @param string $sql the SQL select query to execute.
* @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
* @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
* @return mixed an ADODB RecordSet object, or false if an error occured.
*/
function get_recordset_sql($sql, $limitfrom=null, $limitnum=null) {
    global $CFG, $db;

    if (empty($db)) {
        return false;
    }

    if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };

    if ($limitfrom || $limitnum) {
        ///Special case, 0 must be -1 for ADOdb
        $limitfrom = empty($limitfrom) ? -1 : $limitfrom;
        $limitnum = empty($limitnum) ? -1 : $limitnum;
        $rs = $db->SelectLimit($sql, $limitnum, $limitfrom);
    } else {
        $rs = $db->Execute($sql);
    }
    if (!$rs) {
        debugging($db->ErrorMsg() .'<br /><br />'. $sql);
        if (!empty($CFG->dblogerror)) {
            $debug=array_shift(debug_backtrace());
            error_log("SQL ".$db->ErrorMsg()." in {$debug['file']} on line {$debug['line']}. STATEMENT: $sql with limits ($limitfrom, $limitnum)");
        }
        return false;
    }

    return $rs;
}

/**
* Utility function used by the following 4 methods.
*
* @param object an ADODB RecordSet object.
* @return mixed mixed an array of objects, or false if an error occured or the RecordSet was empty.
*/
function recordset_to_array($rs) {

    global $CFG;

    if ($rs && $rs->RecordCount() > 0) {
    /// First of all, we are going to get the name of the first column
    /// to introduce it back after transforming the recordset to assoc array
    /// See http://docs.moodle.org/en/XMLDB_Problems, fetch mode problem.
        $firstcolumn = $rs->FetchField(0);
    /// Get the whole associative array
        if ($records = $rs->GetAssoc(true)) {
            foreach ($records as $key => $record) {
            /// Really DIRTY HACK for Oracle, but it's the only way to make it work
            /// until we got all those NOT NULL DEFAULT '' out from Moodle
                if ($CFG->dbtype == 'oci8po') {
                    array_walk($record, 'onespace2empty');
                }
            /// End of DIRTY HACK
                $record[$firstcolumn->name] = $key;/// Re-add the assoc field
                $objects[$key] = (object) $record; /// To object
            }
            return $objects;
        } else {
            return false;
        }
    } else {
        return false;
    }
}

/**
* This function is used to convert all the Oracle 1-space defaults to the empty string
* like a really DIRTY HACK to allow it to work better until all those NOT NULL DEFAULT ''
* fields will be out from Moodle.
* @param string the string to be converted to '' (empty string) if it's ' ' (one space)
*/
function onespace2empty(&$item, $key) {
    $item = $item == ' ' ? '' : $item;
    return true;
}
///End DIRTY HACK


/**
* Get a number of records as an array of objects.
*
* If the query succeeds and returns at least one record, the
* return value is an array of objects, one object for each
* record found. The array key is the value from the first
* column of the result set. The object associated with that key
* has a member variable for each column of the results.
*
* @param string $table the table to query.
* @param string $field a field to check (optional).
* @param string $value the value the field must have (requred if field1 is given, else optional).
* @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
* @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
* @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
* @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
* @return mixed an array of objects, or false if no records were found or an error occured.
*/
function get_records($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
    $rs = get_recordset($table, $field, $value, $sort, $fields, $limitfrom, $limitnum);
    return recordset_to_array($rs);
}

/**
* Get a number of records as an array of objects.
*
* Return value as for @see function get_records.
*
* @param string $table the table to query.
* @param string $select A fragment of SQL to be used in a where clause in the SQL call.
* @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
* @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
* @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
* @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
* @return mixed an array of objects, or false if no records were found or an error occured.
*/
function get_records_select($table, $select='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
    $rs = get_recordset_select($table, $select, $sort, $fields, $limitfrom, $limitnum);
    return recordset_to_array($rs);
}

/**
* Get a number of records as an array of objects.
*
* Return value as for @see function get_records.
*
* @param string $table The database table to be checked against.
* @param string $field The field to search
* @param string $values Comma separated list of possible value
* @param string $sort Sort order (as valid SQL sort parameter)
* @param string $fields A comma separated list of fields to be returned from the chosen table.
* @return mixed an array of objects, or false if no records were found or an error occured.
*/
function get_records_list($table, $field='', $values='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
    $rs = get_recordset_list($table, $field, $values, $sort, $fields, $limitfrom, $limitnum);
    return recordset_to_array($rs);
}

/**
* Get a number of records as an array of objects.
*
* Return value as for @see function get_records.
*
* @param string $sql the SQL select query to execute.
* @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
* @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
* @return mixed an array of objects, or false if no records were found or an error occured.
*/
function get_records_sql($sql, $limitfrom='', $limitnum='') {
    $rs = get_recordset_sql($sql, $limitfrom, $limitnum);
    return recordset_to_array($rs);
}

/**
* Utility function used by the following 3 methods.
*
* @param object an ADODB RecordSet object with two columns.
* @return mixed an associative array, or false if an error occured or the RecordSet was empty.
*/
function recordset_to_menu($rs) {
    if ($rs && $rs->RecordCount() > 0) {
        $keys = array_keys($rs->fields);
        $key0=$keys[0];
        $key1=$keys[1];
        while (!$rs->EOF) {
            $menu[$rs->fields[$key0]] = $rs->fields[$key1];
            $rs->MoveNext();
        }
        return $menu;
    } else {
        return false;
    }
}

/**
* Get the first two columns from a number of records as an associative array.
*
* Arguments as for @see function get_recordset.
*
* If no errors occur, and at least one records is found, the return value
* is an associative whose keys come from the first field of each record,
* and whose values are the corresponding second fields. If no records are found,
* or an error occurs, false is returned.
*
* @param string $table the table to query.
* @param string $field a field to check (optional).
* @param string $value the value the field must have (requred if field1 is given, else optional).
* @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
* @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
* @return mixed an associative array, or false if no records were found or an error occured.
*/
function get_records_menu($table, $field='', $value='', $sort='', $fields='*') {
    $rs = get_recordset($table, $field, $value, $sort, $fields);
    return recordset_to_menu($rs);
}

/**
* Get the first two columns from a number of records as an associative array.
*
* Arguments as for @see function get_recordset_select.
* Return value as for @see function get_records_menu.
*
* @param string $table The database table to be checked against.
* @param string $select A fragment of SQL to be used in a where clause in the SQL call.
* @param string $sort Sort order (optional) - a valid SQL order parameter
* @param string $fields A comma separated list of fields to be returned from the chosen table.
* @return mixed an associative array, or false if no records were found or an error occured.
*/
function get_records_select_menu($table, $select='', $sort='', $fields='*') {
    $rs = get_recordset_select($table, $select, $sort, $fields);
    return recordset_to_menu($rs);
}

/**
* Get the first two columns from a number of records as an associative array.
*
* Arguments as for @see function get_recordset_sql.
* Return value as for @see function get_records_menu.
*
* @param string $sql The SQL string you wish to be executed.
* @return mixed an associative array, or false if no records were found or an error occured.
*/
function get_records_sql_menu($sql) {
    $rs = get_recordset_sql($sql);
    return recordset_to_menu($rs);
}

/**
* Get a single value from a table row where all the given fields match the given values.
*
* @param string $table the table to query.
* @param string $return the field to return the value of.
* @param string $field1 the first field to check (optional).
* @param string $value1 the value field1 must have (requred if field1 is given, else optional).
* @param string $field2 the second field to check (optional).
* @param string $value2 the value field2 must have (requred if field2 is given, else optional).
* @param string $field3 the third field to check (optional).
* @param string $value3 the value field3 must have (requred if field3 is given, else optional).
* @return mixed the specified value, or false if an error occured.
*/
function get_field($table, $return, $field1, $value1, $field2='', $value2='', $field3='', $value3='') {
    global $CFG;
    $select = where_clause($field1, $value1, $field2, $value2, $field3, $value3);
    return get_field_sql('SELECT ' . $return . ' FROM ' . $CFG->prefix . $table . ' ' . $select);
}

/**
* Get a single value from a table row where a particular select clause is true.
*
* @uses $CFG
* @param string $table the table to query.
* @param string $return the field to return the value of.
* @param string $select A fragment of SQL to be used in a where clause in the SQL call.
* @return mixed the specified value, or false if an error occured.
*/
function get_field_select($table, $return, $select) {
    global $CFG;
    if ($select) {
        $select = 'WHERE '. $select;
    }
    return get_field_sql('SELECT ' . $return . ' FROM ' . $CFG->prefix . $table . ' ' . $select);
}

/**
* Get a single value from a table.
*
* @param string $sql an SQL statement expected to return a single value.
* @return mixed the specified value, or false if an error occured.
*/
function get_field_sql($sql) {

    $rs = get_recordset_sql($sql);

    if ($rs && $rs->RecordCount() == 1) {
        return reset($rs->fields);
    } else {
        return false;
    }
}

/**
* Get an array of data from one or more fields from a database
* use to get a column, or a series of distinct values
*
* @uses $CFG
* @uses $db
* @param string $sql The SQL string you wish to be executed.
* @return mixed|false Returns the value return from the SQL statment or false if an error occured.
* @todo Finish documenting this function
*/
function get_fieldset_sql($sql) {

    global $db, $CFG;

    if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };

    $rs = $db->Execute($sql);
    if (!$rs) {
        debugging($db->ErrorMsg() .'<br /><br />'. $sql);
        if (!empty($CFG->dblogerror)) {
            $debug=array_shift(debug_backtrace());
            error_log("SQL ".$db->ErrorMsg()." in {$debug['file']} on line {$debug['line']}. STATEMENT: $sql");
        }
        return false;
    }

    if ( $rs->RecordCount() > 0 ) {
        $keys = array_keys($rs->fields);
        $key0 = $keys[0];
        $results = array();
        while (!$rs->EOF) {
            array_push($results, $rs->fields[$key0]);
            $rs->MoveNext();
        }
        return $results;
    } else {
        return false;
    }
}

/**
* Set a single field in every table row where all the given fields match the given values.
*
* @uses $CFG
* @uses $db
* @param string $table The database table to be checked against.
* @param string $newfield the field to set.
* @param string $newvalue the value to set the field to.
* @param string $field1 the first field to check (optional).
* @param string $value1 the value field1 must have (requred if field1 is given, else optional).
* @param string $field2 the second field to check (optional).
* @param string $value2 the value field2 must have (requred if field2 is given, else optional).
* @param string $field3 the third field to check (optional).
* @param string $value3 the value field3 must have (requred if field3 is given, else optional).
* @return mixed An ADODB RecordSet object with the results from the SQL call or false.
*/
function set_field($table, $newfield, $newvalue, $field1, $value1, $field2='', $value2='', $field3='', $value3='') {

    global $db, $CFG;

    if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };

    $select = where_clause($field1, $value1, $field2, $value2, $field3, $value3);

    return $db->Execute('UPDATE '. $CFG->prefix . $table .' SET '. $newfield .' = \''. $newvalue .'\' '. $select);
}

/**
* Delete the records from a table where all the given fields match the given values.
*
* @uses $CFG
* @uses $db
* @param string $table the table to delete from.
* @param string $field1 the first field to check (optional).
* @param string $value1 the value field1 must have (requred if field1 is given, else optional).
* @param string $field2 the second field to check (optional).
* @param string $value2 the value field2 must have (requred if field2 is given, else optional).
* @param string $field3 the third field to check (optional).
* @param string $value3 the value field3 must have (requred if field3 is given, else optional).
* @return mixed An ADODB RecordSet object with the results from the SQL call or false.
*/
function delete_records($table, $field1='', $value1='', $field2='', $value2='', $field3='', $value3='') {

    global $db, $CFG;

    if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };

    $select = where_clause($field1, $value1, $field2, $value2, $field3, $value3);

    return $db->Execute('DELETE FROM '. $CFG->prefix . $table .' '. $select);
}

/**
* Delete one or more records from a table
*
* @uses $CFG
* @uses $db
* @param string $table The database table to be checked against.
* @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
* @return object A PHP standard object with the results from the SQL call.
* @todo Verify return type.
*/
function delete_records_select($table, $select='') {

    global $CFG, $db;

    if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };

    if ($select) {
        $select = 'WHERE '.$select;
    }

    return $db->Execute('DELETE FROM '. $CFG->prefix . $table .' '. $select);
}

/**
* Insert a record into a table and return the "id" field if required
*
* If the return ID isn't required, then this just reports success as true/false.
* $dataobject is an object containing needed data
*
* @uses $db
* @uses $CFG
* @param string $table The database table to be checked against.
* @param array $dataobject A data object with values for one or more fields in the record
* @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.
* @param string $primarykey The primary key of the table we are inserting into (almost always "id")
*/
function insert_record($table, $dataobject, $returnid=true, $primarykey='id') {

    global $db, $CFG, $empty_rs_cache;

    if (empty($db)) {
        return false;
    }

    if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };

/// In Moodle we always use auto-numbering fields for the primary key
/// so let's unset it now before it causes any trouble later
    unset($dataobject->{$primarykey});

/// Get an empty recordset. Cache for multiple inserts.
    if (empty($empty_rs_cache[$table])) {
        /// Execute a dummy query to get an empty recordset
        if (!$empty_rs_cache[$table] = $db->Execute('SELECT * FROM '. $CFG->prefix . $table .' WHERE '. $primarykey .' = \'-1\'')) {
            return false;
        }
    }

    $rs = $empty_rs_cache[$table];

/// Postgres doesn't have the concept of primary key built in
/// and will return the OID which isn't what we want.
/// The efficient and transaction-safe strategy is to
/// move the sequence forward first, and make the insert
/// with an explicit id.
    if ( $CFG->dbtype === 'postgres7' && $returnid == true ) {
        if ($nextval = (int)get_field_sql("SELECT NEXTVAL('{$CFG->prefix}{$table}_{$primarykey}_seq')")) {
            $dataobject->{$primarykey} = $nextval;
        }
    }

/// First basic support of insert for Oracle. As it doesn't
/// support autogenerated fields, we rely on the corresponding
/// sequence. It will work automatically, unless we need to
/// return the primary from the function, in this case we
/// get the next sequence value here and insert it manually.
    if ( $CFG->dbtype === 'oci8po' && $returnid == true) {
    /// We need this here (move this function to dmlib?)
        include_once($CFG->libdir . '/xmldb/classes/generators/XMLDBGenerator.class.php');
        include_once($CFG->libdir . '/xmldb/classes/generators/oci8po/oci8po.class.php');
        $generator = new XMLDBoci8po();
        $generator->setPrefix($CFG->prefix);
        $seqname = $generator->getNameForObject($table, $primarykey, 'seq');
        if ($nextval = (int)get_field_sql("SELECT $seqname.NEXTVAL from dual")) {
            $dataobject->{$primarykey} = $nextval;
        }
    }

/// Begin DIRTY HACK
    if ($CFG->dbtype == 'oci8po') {
        oracle_dirty_hack($table, $dataobject); // Convert object to the correct "empty" values for Oracle DB
    }
/// End DIRTY HACK
    
/// Get the correct SQL from adoDB
    if (!$insertSQL = $db->GetInsertSQL($rs, (array)$dataobject, true)) {
        return false;
    }

/// Run the SQL statement
    if (!$rs = $db->Execute($insertSQL)) {
        debugging($db->ErrorMsg() .'<br /><br />'.$insertSQL);
        if (!empty($CFG->dblogerror)) {
            $debug=array_shift(debug_backtrace());
            error_log("SQL ".$db->ErrorMsg()." in {$debug['file']} on line {$debug['line']}. STATEMENT: $insertSQL");
        }
        return false;
    }

/// If a return ID is not needed then just return true now
    if (!$returnid) {
        return true;
    }

/// We already know the record PK if it's been passed explicitly,
/// or if we've retrieved it from a sequence (Postgres and Oracle).
    if (!empty($dataobject->{$primarykey})) {
        return $dataobject->{$primarykey};
    }

/// This only gets triggered with non-Postgres databases
/// however we have some postgres fallback in case we failed
/// to find the sequence.
    $id = $db->Insert_ID();

    if ($CFG->dbtype === 'postgres7') {
        // try to get the primary key based on id
        if ( ($rs = $db->Execute('SELECT '. $primarykey .' FROM '. $CFG->prefix . $table .' WHERE oid = '. $id))
             && ($rs->RecordCount() == 1) ) {
            trigger_error("Retrieved $primarykey from oid on table $table because we could not find the sequence.");
            return (integer)reset($rs->fields);
        }
        trigger_error('Failed to retrieve primary key after insert: SELECT '. $primarykey .
                      ' FROM '. $CFG->prefix . $table .' WHERE oid = '. $id);
        return false;
    }

    return (integer)$id;
}

/**
* Update a record in a table
*
* $dataobject is an object containing needed data
* Relies on $dataobject having a variable "id" to
* specify the record to update
*
* @uses $CFG
* @uses $db
* @param string $table The database table to be checked against.
* @param array $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
* @return bool
*/
function update_record($table, $dataobject) {

    global $db, $CFG;

    if (! isset($dataobject->id) ) {
        return false;
    }

/// Begin DIRTY HACK
    if ($CFG->dbtype == 'oci8po') {
        oracle_dirty_hack($table, $dataobject); // Convert object to the correct "empty" values for Oracle DB
    }
/// End DIRTY HACK

    // Determine all the fields in the table
    if (!$columns = $db->MetaColumns($CFG->prefix . $table)) {
        return false;
    }
    $data = (array)$dataobject;

    if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };

    // Pull out data matching these fields
    foreach ($columns as $column) {
        if ($column->name <> 'id' and isset($data[$column->name]) ) {
            $ddd[$column->name] = $data[$column->name];
            // PostgreSQL bytea support
            if ($CFG->dbtype == 'postgres7' && $column->type == 'bytea') {
                $ddd[$column->name] = $db->BlobEncode($ddd[$column->name]);
            }
        }
    }

    // Construct SQL queries
    $numddd = count($ddd);
    $count = 0;
    $update = '';

    foreach ($ddd as $key => $value) {
        $count++;
        $update .= $key .' = \''. $value .'\''; // All incoming data is already quoted
        if ($count < $numddd) {
            $update .= ', ';
        }
    }

    if ($rs = $db->Execute('UPDATE '. $CFG->prefix . $table .' SET '. $update .' WHERE id = \''. $dataobject->id .'\'')) {
        return true;
    } else {
        debugging($db->ErrorMsg() .'<br /><br />UPDATE '. $CFG->prefix . $table .' SET '. $update .' WHERE id = \''. $dataobject->id .'\'');
        if (!empty($CFG->dblogerror)) {
            $debug=array_shift(debug_backtrace());
            error_log("SQL ".$db->ErrorMsg()." in {$debug['file']} on line {$debug['line']}. STATEMENT: UPDATE $CFG->prefix$table SET $update WHERE id = '$dataobject->id'");
        }
        return false;
    }
}



/**
* Returns the proper SQL to do paging
*
* @uses $CFG
* @param string $page Offset page number
* @param string $recordsperpage Number of records per page
* @deprecated Moodle 1.7 use the new $limitfrom, $limitnum available in all
* the get_recordXXX() funcions.
* @return string
*/
function sql_paging_limit($page, $recordsperpage) {
    global $CFG;

    switch ($CFG->dbtype) {
        case 'postgres7':
             return 'LIMIT '. $recordsperpage .' OFFSET '. $page;
        default:
             return 'LIMIT '. $page .','. $recordsperpage;
    }
}

/**
* Returns the proper SQL to do LIKE in a case-insensitive way
*
* @uses $CFG
* @return string
*/
function sql_ilike() {
    global $CFG;

    switch ($CFG->dbtype) {
        case 'mysql':
             return 'LIKE';
        default:
             return 'ILIKE';
    }
}


/**
* Returns the proper SQL (for the dbms in use) to concatenate $firstname and $lastname
*
* @uses $CFG
* @param string $firstname User's first name
* @param string $lastname User's last name
* @return string
*/
function sql_fullname($firstname='firstname', $lastname='lastname') {
    global $CFG;

    switch ($CFG->dbtype) {
        case 'mysql':
             return ' CONCAT('. $firstname .'," ",'. $lastname .') ';
        case 'postgres7':
             return " ". $firstname ."||' '||". $lastname ." ";
        case 'mssql':
             return " ". $firstname ."+' '+". $lastname ." ";
        default:
             return ' '. $firstname .'||" "||'. $lastname .' ';
    }
}

/**
* Returns the proper SQL to do IS NULL
* @uses $CFG
* @param string $fieldname The field to add IS NULL to
* @return string
*/
function sql_isnull($fieldname) {
    global $CFG;

    switch ($CFG->dbtype) {
        case 'mysql':
             return $fieldname.' IS NULL';
        default:
             return $fieldname.' IS NULL';
    }
}

/**
* Returns the proper AS keyword to be used to aliase columns
* SQL defines the keyword as optional and nobody but PG
* seems to require it. This function should be used inside all
* the statements using column aliases.
* Note than the use of table aliases doesn't require the
* AS keyword at all, only columns for postgres.
* @uses $CFG
* @ return string the keyword
*/
function sql_as() {
    global $CFG, $db;

    switch ($CFG->dbtype) {
        case 'postgres7':
            return 'AS';
        default:
            return '';
    }
}

/**
* Returns the SQL text to be used to order by one TEXT (clob) column, because
* some RDBMS doesn't support direct ordering of such fields.
* Note that the use or queries being ordered by TEXT columns must be minimised,
* because it's really slooooooow.
* @param string fieldname the name of the TEXT field we need to order by
* @param string number of chars to use for the ordering (defaults to 32)
* @return string the piece of SQL code to be used in your statement.
*/
function sql_order_by_text($fieldname, $numchars=32) {

    global $CFG;

    switch ($CFG->dbtype) {
        case 'mssql':
            return 'CONVERT(varchar, ' . $fieldname . ', ' . $numchars . ')';
            break;
        case 'oci8po':
            return 'dbms_lob.substr(' . $fieldname . ', ' . $numchars . ',1)';
            break;
        default:
            return $fieldname;
    }
}


/**
* Returns SQL to be used as a subselect to find the primary role of users.
* Geoff Cant <geoff@catalyst.net.nz> (the author) is very keen for this to
* be implemented as a view in future versions.
*
* eg if this function returns a string called $primaryroles, then you could:
* $sql = 'SELECT COUNT(DISTINCT prs.userid) FROM ('.$primary_roles.') prs
* WHERE prs.primary_roleid='.$role->id.' AND prs.courseid='.$course->id.
* ' AND prs.contextlevel = '.CONTEXT_COURSE;
*
* @return string the piece of SQL code to be used in your FROM( ) statement.
*/
function sql_primary_role_subselect() {
    global $CFG;
    return 'SELECT ra.userid,
ra.roleid AS primary_roleid,
ra.contextid,
r.sortorder,
r.name,
r.description,
r.shortname,
c.instanceid AS courseid,
c.contextlevel
FROM '.$CFG->prefix.'role_assignments ra
INNER JOIN '.$CFG->prefix.'role r ON ra.roleid = r.id
INNER JOIN '.$CFG->prefix.'context c ON ra.contextid = c.id
WHERE NOT EXISTS (
SELECT 1
FROM '.$CFG->prefix.'role_assignments i_ra
INNER JOIN '.$CFG->prefix.'role i_r ON i_ra.roleid = i_r.id
WHERE ra.userid = i_ra.userid AND
ra.contextid = i_ra.contextid AND
i_r.sortorder < r.sortorder
) ';
}

/**
* Prepare a SQL WHERE clause to select records where the given fields match the given values.
*
* Prepares a where clause of the form
* WHERE field1 = value1 AND field2 = value2 AND field3 = value3
* except that you need only specify as many arguments (zero to three) as you need.
*
* @param string $field1 the first field to check (optional).
* @param string $value1 the value field1 must have (requred if field1 is given, else optional).
* @param string $field2 the second field to check (optional).
* @param string $value2 the value field2 must have (requred if field2 is given, else optional).
* @param string $field3 the third field to check (optional).
* @param string $value3 the value field3 must have (requred if field3 is given, else optional).
*/
function where_clause($field1='', $value1='', $field2='', $value2='', $field3='', $value3='') {
    if ($field1) {
        $select = "WHERE $field1 = '$value1'";
        if ($field2) {
            $select .= " AND $field2 = '$value2'";
            if ($field3) {
                $select .= " AND $field3 = '$value3'";
            }
        }
    } else {
        $select = '';
    }
    return $select;
}

/**
* Get the data type of a table column, using an ADOdb MetaType() call.
*
* @uses $CFG
* @uses $db
* @param string $table The name of the database table
* @param string $column The name of the field in the table
* @return string Field type or false if error
*/

function column_type($table, $column) {
    global $CFG, $db;

    if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; };

    if(!$rs = $db->Execute('SELECT '.$column.' FROM '.$CFG->prefix.$table.' WHERE 1=2')) {
        return false;
    }

    $field = $rs->FetchField(0);
    return $rs->MetaType($field->type);
}

/**
* This function will execute an array of SQL commands, returning
* true/false if any error is found and stopping/continue as desired.
* It's widely used by all the ddllib.php functions
*
* @param array sqlarr array of sql statements to execute
* @param boolean continue to specify if must continue on error (true) or stop (false)
* @param boolean feedback to specify to show status info (true) or not (false)
* @param boolean true if everything was ok, false if some error was found
*/
function execute_sql_arr($sqlarr, $continue=true, $feedback=true) {

    if (!is_array($sqlarr)) {
        return false;
    }

    $status = true;
    foreach($sqlarr as $sql) {
        if (!execute_sql($sql, $feedback)) {
            $status = false;
            if (!$continue) {
                break;
            }
        }
    }
    return $status;
}

/**
* This function, called from setup.php includes all the configuration
* needed to properly work agains any DB. It setups connection encoding
* and some other variables.
*/
function configure_dbconnection() {

    global $CFG, $db;

    switch ($CFG->dbtype) {
        case 'mysql':
        /// Set names if needed
            if ($CFG->unicodedb) {
                $db->Execute("SET NAMES 'utf8'");
            }
            break;
        case 'postgres7':
        /// Set names if needed
            if ($CFG->unicodedb) {
                $db->Execute("SET NAMES 'utf8'");
            }
            break;
        case 'mssql':
        /// No need to set charset. It must be specified in the driver conf
        /// Allow quoted identifiers
            $db->Execute('SET QUOTED_IDENTIFIER ON');
        /// Force ANSI nulls so the NULL check was done by IS NULL and NOT IS NULL
        /// instead of equal(=) and distinct(<>) simbols
            $db->Execute('SET ANSI_NULLS ON');
        /// Enable sybase quotes, so addslashes and stripslashes will use "'"
            ini_set('magic_quotes_sybase', '1');
        /// NOTE: Not 100% useful because GPC has been addslashed with the setting off
        /// so IT'S MANDATORY TO CHANGE THIS UNDER php.ini or .htaccess for this DB
            break;
        case 'oci8po':
        /// No need to set charset. It must be specified by the NLS_LANG env. variable
        /// Enable sybase quotes, so addslashes and stripslashes will use "'"
            ini_set('magic_quotes_sybase', '1');
        /// NOTE: Not 100% useful because GPC has been addslashed with the setting off
        /// so IT'S MANDATORY TO ENABLE THIS UNDER php.ini or .htaccess for this DB
            break;
    }
}

/**
* This function will handle all the records before being inserted/updated to DB for Oracle
* installations. This is because the "special feature" of Oracle where the empty string is
* equal to NULL and this presents a problem with all our currently NOT NULL default '' fields.
*
* Once Moodle DB will be free of this sort of false NOT NULLS, this hack could be removed safely
*
* Note that this function is 100% private and should be used, exclusively by DML functions
* in this file. Also, this is considered a DIRTY HACK to be removed when possible. (stronk7)
*
* @param $table string the table where the record is going to be inserted/updated (without prefix)
* @param $dataobject object the object to be inserted/updated
* @param $usecache boolean flag to determinate if we must use the per request cache of metadata
* true to use it, false to ignore and delete it
*/
function oracle_dirty_hack ($table, &$dataobject, $usecache = true) {

    global $CFG, $db;

/// Init and delete metadata cache
    if (!isset($metadatacache) || !$usecache) {
        static $metadatacache = array();
    }

/// For Oracle DB, empty strings are converted to NULLs in DB
/// and this breaks a lot of NOT NULL columns currenty Moodle. In the future it's
/// planned to move some of them to NULL, if they must accept empty values and this
/// piece of code will become less and less used. But, for now, we need it.
/// What we are going to do is to examine all the data being inserted and if it's
/// an empty string (NULL for Oracle) and the field is defined as NOT NULL, we'll modify
/// such data in the best form possible ("0" for booleans and numbers and " " for the
/// rest of strings. It isn't optimal, but the only way to do so.
/// In the oppsite, when retrieving records from Oracle, we'll decode " " back to
/// empty strings to allow everything to work properly. DIRTY HACK.

/// If the db isn't Oracle, return without modif
    if ( $CFG->dbtype != 'oci8po') {
        return;
    }

/// Get Meta info to know what to change, using the cached meta if exists
    if (!isset($metadatacache[$table])) {
        $metadatacache[$table] = array_change_key_case($db->MetaColumns($CFG->prefix . $table), CASE_LOWER);
    }
    $columns = $metadatacache[$table];
/// Iterate over all the fields in the insert, transforming values
/// in the best possible form
    foreach ($dataobject as $fieldname => $fieldvalue) {
    /// If the field doesn't exist in metadata, skip
        if (!isset($columns[strtolower($fieldname)])) {
            continue;
        }
    /// If the field ins't VARCHAR or CLOB, skip
        if ($columns[strtolower($fieldname)]->type != 'VARCHAR2' && $columns[strtolower($fieldname)]->type != 'CLOB') {
            continue;
        }
    /// If the field isn't NOT NULL, skip (it's nullable, so accept empty values)
        if (!$columns[strtolower($fieldname)]->not_null) {
            continue;
        }
    /// If the value isn't empty, skip
        if (!empty($fieldvalue)) {
            continue;
        }
    /// Now, we have one empty value, going to be inserted to one NOT NULL, VARCHAR2 or CLOB field
    /// Try to get the best value to be inserted
        if (gettype($fieldvalue) == 'boolean') {
            $dataobject->$fieldname = '0'; /// Transform false to '0' that evaluates the same for PHP
        } else if (gettype($fieldvalue) == 'integer') {
            $dataobject->$fieldname = '0'; /// Transform 0 to '0' that evaluates the same for PHP
        } else if (gettype($fieldvalue) == 'NULL') {
            $dataobject->$fieldname = '0'; /// Transform NULL to '0' that evaluates the same for PHP
        } else {
            $dataobject->$fieldname = ' '; /// Transform '' to ' ' that DONT'T EVALUATE THE SAME
                                           /// (we'll transform back again on get_records_XXX functions and others)!!
        }
    }
}
/// End of DIRTY HACK

?>
Something went wrong with that request. Please try again.