/lib/dmllib.php
PHP | 2905 lines | 1378 code | 339 blank | 1188 comment | 415 complexity | 03aadfda35e4e1c88134f3388d93e371 MD5 | raw file
Possible License(s): BSD-3-Clause, LGPL-2.0, LGPL-2.1
Large files files are truncated, but you can click here to view the full file
- <?php // $Id: dmllib.php,v 1.116.2.38 2011/12/01 12:25:16 moodlerobot Exp $
-
- ///////////////////////////////////////////////////////////////////////////
- // //
- // NOTICE OF COPYRIGHT //
- // //
- // Moodle - Modular Object-Oriented Dynamic Learning Environment //
- // http://moodle.com //
- // //
- // Copyright (C) 1999 onwards 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/19/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
- $metadata_cache = array(); // Kereeps copies of the MetaColumns() for each table used in one invocations
-
- $rcache = new StdClass; // Cache simple get_record results
- $rcache->data = array();
- $rcache->hits = 0;
- $rcache->misses = 0;
-
- /// 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 bool success
- */
- 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;
- }
-
- if ($CFG->version >= 2006101007) { //Look for trailing ; from Moodle 1.7.0
- $command = trim($command);
- /// If the trailing ; is there, fix and warn!
- if (substr($command, strlen($command)-1, 1) == ';') {
- /// One noticeable exception, Oracle PL/SQL blocks require ending in ";"
- if ($CFG->dbfamily == 'oracle' && substr($command, -4) == 'END;') {
- /// Nothing to fix/warn. The command is one PL/SQL block, so it's ok.
- } else {
- $command = trim($command, ';');
- debugging('Warning. Avoid to end your SQL commands with a trailing ";".', DEBUG_DEVELOPER);
- }
- }
- }
-
- $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++; };
-
- $rs = $db->Execute($command);
-
- $db->debug = $olddebug;
-
- if ($rs) {
- if ($feedback) {
- notify(get_string('success'), 'notifysuccess');
- }
- return true;
- } else {
- if ($feedback) {
- notify('<strong>' . get_string('error') . '</strong>');
- }
- // these two may go to difference places
- debugging($db->ErrorMsg() .'<br /><br />'. s($command));
- 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
- *
- * @deprecated Moodle 1.7 use the new XMLDB stuff in lib/ddllib.php
- *
- * @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;
-
- if ($CFG->version > 2006101007) {
- debugging('Function modify_database() is deprecated. Replace it with the new XMLDB stuff.', DEBUG_DEVELOPER);
- }
-
- $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
-
- if (!$rs = get_recordset_sql($sql, $limitfrom, $limitnum)) {
- return false;
- }
-
- if (rs_EOF($rs)) {
- $result = false;
- } else {
- $result = true;
- }
-
- rs_close($rs);
- return $result;
- }
-
- /**
- * 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 (is_object($rs) and is_array($rs->fields)) {
- 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;
-
- // Check to see whether this record is eligible for caching (fields=*, only condition is id)
- $docache = false;
- if (!empty($CFG->rcache) && $CFG->rcache === true && $field1=='id' && !$field2 && !$field3 && $fields=='*') {
- $docache = true;
- // If it's in the cache, return it
- $cached = rcache_getforfill($table, $value1);
- if (!empty($cached)) {
- return $cached;
- }
- }
-
- $select = where_clause($field1, $value1, $field2, $value2, $field3, $value3);
-
- $record = get_record_sql('SELECT '.$fields.' FROM '. $CFG->prefix . $table .' '. $select);
-
- // If we're caching records, store this one
- // (supposing we got something - we don't cache failures)
- if ($docache) {
- if ($record !== false) {
- rcache_set($table, $value1, $record);
- } else {
- rcache_releaseforfill($table, $value1);
- }
- }
- return $record;
- }
-
- /**
- * 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 conveniently 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('', DEBUG_DEVELOPER)) {
- // 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 definitively delete them
- if ($CFG->dbfamily == 'oracle') {
- array_walk($rs->fields, 'onespace2empty');
- }
- /// End of 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.
- *
- * Since this method is a little less readable, use of it should be restricted to
- * code where it's possible there might be large datasets being returned. For known
- * small datasets use get_records - it leads to simpler code.
- *
- * 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) {
- $value = sql_magic_quotes_hack($value);
- $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 ($sort) {
- $sort = ' ORDER BY '. $sort;
- }
-
- return get_recordset_sql('SELECT '. $fields .' FROM '. $CFG->prefix . $table . $select . $sort, $limitfrom, $limitnum);
- }
-
- /**
- * 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.
- * Since this method is a little less readable, use of it should be restricted to
- * code where it's possible there might be large datasets being returned. For known
- * small datasets use get_records_sql - it leads to simpler code.
- *
- * 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;
- }
-
- /// Temporary hack as part of phasing out all access to obsolete user tables XXX
- if (!empty($CFG->rolesactive)) {
- if (strpos($sql, ' '.$CFG->prefix.'user_students ') ||
- strpos($sql, ' '.$CFG->prefix.'user_teachers ') ||
- strpos($sql, ' '.$CFG->prefix.'user_coursecreators ') ||
- strpos($sql, ' '.$CFG->prefix.'user_admins ')) {
- if (debugging()) { var_dump(debug_backtrace()); }
- error('This SQL relies on obsolete tables! Your code must be fixed by a developer.');
- }
- }
-
-
- 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 />'. s($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. Note that for this to work, the first column
- * in the recordset must contain unique values, as it is used as the key to the associative array.
- *
- * @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;
-
- $debugging = debugging('', DEBUG_DEVELOPER);
-
- if ($rs && !rs_EOF($rs)) {
- $objects = array();
- /// 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/19/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->dbfamily == 'oracle') {
- array_walk($record, 'onespace2empty');
- }
- /// End of DIRTY HACK
- $record[$firstcolumn->name] = $key;/// Re-add the assoc field
- if ($debugging && array_key_exists($key, $objects)) {
- 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);
- }
- $objects[$key] = (object) $record; /// To object
- }
- return $objects;
- /// Fallback in case we only have 1 field in the recordset. MDL-5877
- } else if ($rs->_numOfFields == 1 && $records = $rs->GetRows()) {
- 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->dbfamily == 'oracle') {
- array_walk($record, 'onespace2empty');
- }
- /// End of DIRTY HACK
- if ($debugging && array_key_exists($record[$firstcolumn->name], $objects)) {
- 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);
- }
- $objects[$record[$firstcolumn->name]] = (object) $record; /// The key is the first column value (like Assoc)
- }
- return $objects;
- } else {
- return false;
- }
- } else {
- return false;
- }
- }
-
- /**
- * This function is used to get the current record from the recordset. It
- * doesn't advance the recordset position. You'll need to do that by
- * using the rs_next_record($recordset) function.
- * @param ADORecordSet the recordset to fetch current record from
- * @return ADOFetchObj the object containing the fetched information
- */
- function rs_fetch_record(&$rs) {
- global $CFG;
-
- if (!$rs) {
- debugging('Incorrect $rs used!', DEBUG_DEVELOPER);
- return false;
- }
-
- $rec = $rs->FetchObj(); //Retrieve record as object without advance the pointer
-
- if ($rs->EOF) { //FetchObj requires manual checking of EOF to detect if it's the last record
- $rec = false;
- } else {
- /// 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->dbfamily == 'oracle') {
- $recarr = (array)$rec; /// Cast to array
- array_walk($recarr, 'onespace2empty');
- $rec = (object)$recarr;/// Cast back to object
- }
- /// End DIRTY HACK
- }
-
- return $rec;
- }
-
- /**
- * This function is used to advance the pointer of the recordset
- * to its next position/record.
- * @param ADORecordSet the recordset to be moved to the next record
- * @return boolean true if the movement was successful and false if not (end of recordset)
- */
- function rs_next_record(&$rs) {
- if (!$rs) {
- debugging('Incorrect $rs used!', DEBUG_DEVELOPER);
- return false;
- }
-
- return $rs->MoveNext(); //Move the pointer to the next record
- }
-
- /**
- * This function is used to get the current record from the recordset. It
- * does advance the recordset position.
- * This is the prefered way to iterate over recordsets with code blocks like this:
- *
- * $rs = get_recordset('SELECT .....');
- * while ($rec = rs_fetch_next_record($rs)) {
- * /// Perform actions with the $rec record here
- * }
- * rs_close($rs); /// Close the recordset if not used anymore. Saves memory (optional but recommended).
- *
- * @param ADORecordSet the recordset to fetch current record from
- * @return mixed ADOFetchObj the object containing the fetched information or boolean false if no record (end of recordset)
- */
- function rs_fetch_next_record(&$rs) {
-
- global $CFG;
-
- if (!$rs) {
- debugging('Incorrect $rs used!', DEBUG_DEVELOPER);
- return false;
- }
-
- $rec = false;
- $recarr = $rs->FetchRow(); //Retrieve record as object without advance the pointer. It's quicker that FetchNextObj()
-
- if ($recarr) {
- /// 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->dbfamily == 'oracle') {
- array_walk($recarr, 'onespace2empty');
- }
- /// End DIRTY HACK
- /// Cast array to object
- $rec = (object)$recarr;
- }
-
- return $rec;
- }
-
- /**
- * Returns true if no more records found
- * @param ADORecordSet the recordset
- * @return bool
- */
- function rs_EOF($rs) {
- if (!$rs) {
- debugging('Incorrect $rs used!', DEBUG_DEVELOPER);
- return true;
- }
- return $rs->EOF;
- }
-
- /**
- * This function closes the recordset, freeing all the memory and associated resources.
- * Note that, once closed, the recordset must not be used anymore along the request.
- * Saves memory (optional but recommended).
- * @param ADORecordSet the recordset to be closed
- * @return void
- */
- function rs_close(&$rs) {
- if (!$rs) {
- debugging('Incorrect $rs used!', DEBUG_DEVELOPER);
- return;
- }
-
- $rs->Close();
- }
-
- /**
- * 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)
- * @param mixed the key of the array in case we are using this function from array_walk,
- * defaults to null for other (direct) uses
- * @return boolean always true (the converted variable is returned by reference)
- */
- function onespace2empty(&$item, $key=null) {
- $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). The first field will be used as key for the
- * array so must be a unique field such as 'id'.
- * @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). The first field will be used as key for the
- * array so must be a unique field such as 'id'.
- * @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. If specified,
- * the first field should be a unique one such as 'id' since it will be used as a key in the associative
- * array.
- * @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. The first column of this SELECT statement
- * must be a unique value (usually the 'id' field), as it will be used as the key of the
- * returned array.
- * @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) {
- global $CFG;
- $menu = array();
- if ($rs && !rs_EOF($rs)) {
- $keys = array_keys($rs->fields);
- $key0=$keys[0];
- $key1=$keys[1];
- while (!$rs->EOF) {
- $menu[$rs->fields[$key0]] = $rs->fields[$key1];
- $rs->MoveNext();
- }
- /// 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->dbfamily == 'oracle') {
- array_walk($menu, 'onespace2empty');
- }
- /// End of DIRTY HACK
- return $menu;
- } else {
- return false;
- }
- }
-
- /**
- * Utility function
- * Similar to recordset_to_menu
- *
- * field1, field2 is needed because the order from get_records_sql is not reliable
- * @param records - records from get_records_sql() or get_records()
- * @param field1 - field to be used as menu index
- * @param field2 - feild to be used as coresponding menu value
- * @return mixed an associative array, or false if an error occured or the RecordSet was empty.
- */
- function records_to_menu($records, $field1, $field2) {
-
- $menu = array();
- foreach ($records as $record) {
- $menu[$record->$field1] = $record->$field2;
- }
-
- if (!empty($menu)) {
- 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).
- * @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 associative array, or false if no records were found or an error occured.
- */
- function get_records_menu($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
- $rs = get_recordset($table, $field, $value, $sort, $fields, $limitfrom, $limitnum);
- 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.
- * @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 associative array, or false if no records were found or an error occured.
- */
- function get_records_select_menu($table, $select='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
- $rs = get_recordset_select($table, $select, $sort, $fields, $limitfrom, $limitnum);
- 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.
- * @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 associative array, or false if no records were found or an error occured.
- */
- function get_records_sql_menu($sql, $limitfrom='', $limitnum='') {
- $rs = get_recordset_sql($sql, $limitfrom, $limitnum);
- 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) {
- global $CFG;
-
- /// Strip potential LIMIT uses arriving here, debugging them (MDL-7173)
- $newsql = preg_replace('/ LIMIT [0-9, ]+$/is', '', $sql);
- if ($newsql != $sql) {
- debugging('Incorrect use of LIMIT clause (not cross-db) in call to get_field_sql(): ' . s($sql), DEBUG_DEVELOPER);
- $sql = $newsql;
- }
-
- $rs = get_recordset_sql($sql, 0, 1);
-
- if ($rs && $rs->RecordCount() == 1) {
- /// 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->dbfamily == 'oracle') {
- $value = reset($rs->fields);
- onespace2empty($value);
- return $value;
- }
- /// End of DIRTY HACK
- return reset($rs->fields);
- } else {
- return false;
- }
- }
-
- /**
- * 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|false Returns the value return from the SQL statment or false if an error occured.
- */
- function get_fieldset_select($table, $return, $select) {
- global $CFG;
- if ($select) {
- $select = ' WHERE '. $select;
- }
- return get_fieldset_sql('SELECT ' . $return . ' FROM ' . $CFG->prefix . $table . $select);
- }
-
- /**
- * 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 />'. s($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_EOF($rs) ) {
- $keys = array_keys($rs->fields);
- $key0 = $keys[0];
- $results = array();
- while (!$rs->EOF) {
- array_push($results, $rs->fields[$key0]);
- $rs->MoveNext();
- }
- /// 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->dbfamily == 'oracle') {
- array_walk($results, 'onespace2empty');
- }
- /// End of DIRTY HACK
- rs_close($rs);
- return $results;
- } else {
- rs_close($rs);
- 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 $CFG;
-
- // Clear record_cache based on the parameters passed
- // (individual record or whole table)
- …
Large files files are truncated, but you can click here to view the full file