dml.php

<?php
/**
 *
 * @package    mahara
 * @subpackage core
 * @author     Martin Dougiamas <martin@moodle.com>
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL version 3 or later
 * @copyright  For copyright information on Mahara, please see the README file distributed with this software.
 * @copyright  (C) 2001-3001 Martin Dougiamas http://dougiamas.com
 *
 */

defined('INTERNAL') || die();

/** Does not show debug warning if multiple records found (Use with care)*/
define('IGNORE_MULTIPLE', 0);
/** Show debug warning if multiple records found */
define('WARN_MULTIPLE', 1);
/** Throws an error if multiple records found */
define('ERROR_MULTIPLE', 2);

/**
 * Return a table name, properly prefixed and escaped
 *
 */
function db_table_name($name) {
    return db_quote_identifier(get_config('dbprefix') . $name);
}

/**
 * Searches through a query for strings looking like {name}, to replace with
 * correctly quoted and prefixed table names
 *
 * @param string $sql The SQL to replace the placeholders in
 * @return string
 */
function db_quote_table_placeholders($sql) {
    return preg_replace_callback('/\{([a-z][a-z0-9_]+)\}/', '_db_quote_table_placeholders_callback', $sql);
}

/**
 * A callback function used only in db_quote_table_placeholders
 * @param array $matches
 */
function _db_quote_table_placeholders_callback(array $matches) {
    return db_table_name($matches[1]);
}

/**
 * Given a table name or other identifier, return it quoted for the appropriate
 * database engine currently being used
 *
 * @param string $identifier The identifier to quote
 * @return string
 */
function db_quote_identifier($identifier) {
    // Currently, postgres and mysql (in postgres compat. mode) both support
    // the sql standard "
    $identifier = trim($identifier);
    if (strpos($identifier, '"') !== false
        || $identifier === '*'
        || preg_match('/\(/i', $identifier)) {
        return $identifier;
    }
    return '"' . $identifier . '"';
}

/**
 * Check whether the db's default character encoding is utf8
 *
 * @return bool
 */
function db_is_utf8() {
    global $db;
    if (!is_a($db, 'ADOConnection')) {
        throw new SQLException('Database connection is not available ');
    }
    if (is_mysql()) {
        $result = $db->_Execute("SHOW VARIABLES LIKE 'character_set_database'");
        return preg_match('/^utf8/', $result->fields['Value']);
    }
    if (is_postgres()) {
        $result = $db->_Execute("SHOW SERVER_ENCODING");
        return $result->fields['server_encoding'] == 'UTF8';
    }
    return false;
}

function db_total_size() {
    global $db;
    if (!is_a($db, 'ADOConnection')) {
        throw new SQLException('Database connection is not available ');
    }
    $dbname = db_quote(get_config('dbname'));
    if (is_mysql()) {
        $result = $db->_Execute("
            SELECT SUM( data_length + index_length ) AS dbsize
            FROM information_schema.tables
            WHERE table_schema = $dbname
        ");
        return $result->fields['dbsize'];
    }
    if (is_postgres()) {
        $result = $db->_Execute("SELECT * FROM pg_database_size($dbname)");
        return $result->fields['pg_database_size'];
    }
    return false;
}

function column_collation_is_default($table, $column) {
    global $db;
    if (!is_a($db, 'ADOConnection')) {
        throw new SQLException('Database connection is not available ');
    }
    if (is_mysql()) {
        $result = $db->_Execute("SHOW VARIABLES LIKE 'collation_database'");
        $defaultcollation = $result->fields['Value'];

        $command = 'SHOW FULL COLUMNS FROM ' . db_table_name($table) . ' WHERE field = ?';
        $stmt = $db->Prepare($command);
        $result = $db->Execute($stmt, array($column));
        return $result->fields['Collation'] == $defaultcollation;
    }
    return true;
}

/**
 * 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 array $values When using prepared statements, this is the value array (optional).
 * @return boolean
 * @throws SQLException
 */
function execute_sql($command, array $values=null) {
    global $db;

    if (!is_a($db, 'ADOConnection')) {
        throw new SQLException('Database connection is not available ');
    }

    $command = db_quote_table_placeholders($command);

    try {
        if (!empty($values) && is_array($values) && count($values) > 0) {
            $stmt = $db->Prepare($command);
            $result = $db->Execute($stmt, $values);
        }
        else {
            $result = $db->_Execute($command);
        }
    }
    catch (ADODB_Exception $e) {
        log_debug($e->getMessage() . "Command was: $command");
        throw new SQLException('Could not execute command: ' . $command);
    }

    return true;
}

/// 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.
 *
 * @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.
 * @throws SQLException
 */
function record_exists($table, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
    $select = where_clause_prepared($field1, $field2, $field3);
    $values = where_values_prepared($value1, $value2, $value3);
    return record_exists_sql('SELECT * FROM ' . db_table_name($table) .' '. $select, $values);
}

/**
 * Test whether any records exists in a table which match a particular WHERE clause.
 *
 * This function returns true if at least one record is returned
 *
 * @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 array $values When using prepared statements, this is the value array (optional).
 * @return bool true if a matching record exists, else false.
 * @throws SQLException
 */
function record_exists_select($table, $select='', array $values=null) {

    global $CFG;

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

    return record_exists_sql('SELECT * FROM '. db_table_name($table) . ' ' . $select, $values);
}

/**
 * Test whether a SQL SELECT statement returns any records.
 *
 * This function returns true if at least one record is returned.
 *
 * @param string $sql The SQL statement to be executed. If using $values, placeholder ?s are expected. If not, the string should be escaped correctly.
 * @param array $values When using prepared statements, this is the value array (optional).
 * @return bool true if the SQL executes without errors and returns at least one record.
 * @throws SQLException
 */
function record_exists_sql($sql, array $values=null) {
    $rs = get_recordset_sql($sql, $values, 0, 1);
    return $rs->RecordCount() > 0;
}

/**
 * Count the records in a table where all the given fields match the given values.
 *
 * @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.
 * @throws SQLException
 */
function count_records($table, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
    $select = where_clause_prepared($field1, $field2, $field3);
    $values = where_values_prepared($value1, $value2, $value3);
    return count_records_sql('SELECT COUNT(*) FROM '. db_table_name($table) . ' ' . $select, $values);
}

/**
 * Count the records in a table which match a particular WHERE clause.
 *
 * @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 array $values if using a prepared statement with placeholders in $select, pass values here. optional
 * @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.
 * @throws SQLException
 */
function count_records_select($table, $select='', array $values=null, $countitem='COUNT(*)') {
    if ($select) {
        $select = 'WHERE ' . $select;
    }
    return count_records_sql('SELECT '. $countitem .' FROM '. db_table_name($table) . ' ' . $select, $values);
}

/**
 * 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 $db
 * @param string $sql The SQL string you wish to be executed.
 * @param array $values When using prepared statements, this is the value array (optional).
 * @return int        The count.
 * @throws SQLException
 */
function count_records_sql($sql, array $values=null) {
    $rs = get_recordset_sql($sql, $values);
    if (!$rs->fields) {
        throw new SQLException('count_records_sql() should not return false. Is your query misisng "COUNT(*)"?');
    }
    return reset($rs->fields);
}

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

/**
 * Get a single record as an object
 *
 * @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 (required if field1 is given, else optional).
 * @param string $field2 the second field to check (optional).
 * @param string $value2 the value field2 must have (required if field2 is given, else optional).
 * @param string $field3 the third field to check (optional).
 * @param string $value3 the value field3 must have (required if field3 is given, else optional).
 * @param string $fields Which fields to return (default '*')
 * @param int $strictness IGNORE_MULITPLE means no special action if multiple records found
 *                        WARN_MULTIPLE means log a warning message if multiple records found
 *                        ERROR_MULTIPLE means we will throw an exception if multiple records found.
 * @return mixed a fieldset object containing the first mathcing record, or false if none found.
 * @throws SQLException
 */
function get_record($table, $field1, $value1, $field2=null, $value2=null, $field3=null, $value3=null, $fields='*', $strictness=WARN_MULTIPLE) {
    $select = where_clause_prepared($field1, $field2, $field3);
    $values = where_values_prepared($value1, $value2, $value3);
    return get_record_sql('SELECT ' . $fields . ' FROM ' . db_table_name($table) . ' ' . $select, $values, $strictness);
}

/**
 * Get a single record as an object using an SQL statement
 *
 * This function is designed to retrieve ONE record. If your query returns more than one record,
 * an exception is thrown. If you want more than one record, use get_records_sql_array or get_records_sql_assoc
 *
 * @param string $sql The SQL string you wish to be executed, should normally only return one record.
 * @param array $values When using prepared statements, this is the value array (optional).
 * @param int $strictness IGNORE_MULITPLE means no special action if multiple records found
 *                        WARN_MULTIPLE means log a warning message if multiple records found
 *                        ERROR_MULTIPLE means we will throw an exception if multiple records found.
 * @return Found record as object. False if not found
 * @throws SQLException
 */
function get_record_sql($sql, array $values=null, $strictness=WARN_MULTIPLE) {
    $limitfrom = 0;
    $limitnum  = 0;
    # regex borrowed from htdocs/lib/adodb/adodb-lib.inc.php
    if (!preg_match('/\sLIMIT\s+[0-9]+/i', $sql)) {
        $limitfrom = 0;
        $limitnum  = 2;

        // Don't even bother checking for multiples if they don't care
        if ($strictness == IGNORE_MULTIPLE) {
            $limitnum = 1;
        }
    }

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

    $recordcount = $rs->RecordCount();

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

    // Error: found more than one record
    if ($recordcount > 1) {
        $msg = 'get_record_sql found more than one row. If you meant to retrieve more '
            . 'than one record, use get_records_*, otherwise check your code or database for inconsistencies';
        switch ($strictness) {
            case ERROR_MULTIPLE:
                throw new SQLException($msg);
                break;
            case WARN_MULTIPLE:
                log_debug($msg);
                break;
            case IGNORE_MULITPLE:
                // Do nothing!
                break;
        }
    }
    return (object)$rs->fields;
}

/**
 * Gets one record from a table, as an object
 *
 * @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 array $values When using prepared statements, this is the value array (optional).
 * @param string $fields A comma separated list of fields to be returned from the chosen table.
 * @param int $strictness IGNORE_MULITPLE means no special action if multiple records found
 *                        WARN_MULTIPLE means log a warning message if multiple records found
 *                        ERROR_MULTIPLE means we will throw an exception if multiple records found.
 * @return object Returns an array of found records (as objects)
 * @throws SQLException
 */
function get_record_select($table, $select='', array $values=null, $fields='*', $strictness=WARN_MULTIPLE) {
    if ($select) {
        $select = 'WHERE '. $select;
    }
    return get_record_sql('SELECT '. $fields .' FROM ' . db_table_name($table) .' '. $select, $values, $strictness);
}

/**
 * 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, an exception is thrown.
 *
 * @param string $table the table to query.
 * @param string $field a field to check (optional).
 * @param string $value the value the field must have (required 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 ADORecordSet an ADODB RecordSet object.
 * @throws SQLException
 */
function get_recordset($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
    $values = null;
    if ($field) {
        $select = db_quote_identifier($field) . " = ?";
        $values = array($value);
    } else {
        $select = '';
    }

    return get_recordset_select($table, $select, $values, $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.
 *
 * @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 array $values When using prepared statements, this is the value array (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 ADORecordSet an ADODB RecordSet object
 * @throws SQLException
 */
function get_recordset_select($table, $select='', array $values=null, $sort='', $fields='*', $limitfrom='', $limitnum='') {
    if ($select) {
        $select = ' WHERE '. $select;
    }

    if ($limitfrom !== '') {
        $limit = 'LIMIT ' . intval($limitnum)  . ' OFFSET ' . intval($limitfrom);
    } else {
        $limit = '';
    }

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

    return get_recordset_sql('SELECT '. $fields .' FROM '. db_table_name($table) . $select . $sort .' '. $limit, $values);
}

/**
 * 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 $db
 * @param string $sql the SQL select query to execute.
 * @param array $values When using prepared statements, this is the value array (optional).
 * @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 ADORecordSet an ADODB RecordSet object
 * @throws SQLException
 */
function get_recordset_sql($sql, array $values=null, $limitfrom=null, $limitnum=null) {
    global $db;

    if (!is_a($db, 'ADOConnection')) {
        throw new SQLException('Database connection is not available ');
    }

    $sql = db_quote_table_placeholders($sql);

    if ($values === null || $values === array()) {
        $values = false;
    }
    else if (!is_array($values)) {
        throw new SQLException('Invalid values parameter sent to get_recordset_sql.');
    }

    try {
        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, $values);
        } else {
            $rs = false;
            if ($values) {
                $stmt = $db->Prepare($sql);
                $rs = $db->Execute($stmt, $values);
            } else {
                $rs = $db->_Execute($sql);
            }
        }
    }
    catch (ADODB_Exception $e) {
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
    }

   return $rs;
}

/**
 * Utility function to turn a result set into an array of records
 *
 * @param ADORecordSet an ADODB RecordSet object.
 * @return mixed an array of objects, or false if the RecordSet was empty.
 * @throws SQLException
 */
function recordset_to_array(ADORecordSet $rs) {
    if ($rs && $rs->RecordCount() > 0) {
        $array = $rs->GetArray();
        foreach ($array as &$a) {
            $a = (object)$a;
        }
        return $array;
    }
    else {
        return false;
    }
}


//
// Generic data retrieval functions - get_records*
//

/**
 * Utility function to turn a result set into an associative array of records
 * This method turns a result set into a hash of records (keyed by the first
 * field in the result set)
 *
 * @param  ADORecordSet $rs An ADODB RecordSet object.
 * @return mixed An array of objects, or false if the RecordSet was empty.
 * @throws SQLException
 * @access private
 */
function recordset_to_assoc(ADORecordSet $rs) {

    if ($rs->NumCols() < 2) {
        $colname = $rs->FetchField(0)->name;
        $records = $rs->GetAll();
        if (empty($records)) {
            return false;
        }
        $objects = [];
        foreach ($records as $val) {
            $objects[(string) $val[$colname]] = (object) $val;
        }
        return $objects;
    }

    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) {
                $record[$firstcolumn->name] = $key;
                $objects[$key] = (object) $record;
            }
            return $objects;
        } else {
            return false;
        }
    } else {
        return false;
    }
}

/**
 * Get a number of records as an associative array of objects. (WARNING: this
 * does not return an array, it returns an associative array keyed by the first
 * column in the result set. As a result, you may lose some rows! Please use
 * {@link get_records_*_array} instead where possible)
 *
 * 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.
 * @throws SQLException
 */
function get_records_assoc($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
    $rs = get_recordset($table, $field, $value, $sort, $fields, $limitfrom, $limitnum);
    return recordset_to_assoc($rs);
}

/**
 * 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.
 * @throws SQLException
 */
function get_records_array($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 associative array of objects.
 *
 * Return value as for @see function get_records_assoc
 *
 * @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 array $values When using prepared statements, this is the value array (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.
 * @throws SQLException
 */
function get_records_select_assoc($table, $select='', $values=null, $sort='', $fields='*', $limitfrom='', $limitnum='') {
    $rs = get_recordset_select($table, $select, $values, $sort, $fields, $limitfrom, $limitnum);
    return recordset_to_assoc($rs);
}

/**
 * Get a number of records as an array of objects.
 *
 * Return value as for {@link get_records_array}.
 *
 * @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 array $values When using prepared statements, this is the value array (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.
 * @throws SQLException
 */
function get_records_select_array($table, $select='', array $values=null, $sort='', $fields='*', $limitfrom='', $limitnum='') {
    $rs = get_recordset_select($table, $select, $values, $sort, $fields, $limitfrom, $limitnum);
    return recordset_to_array($rs);
}

/**
 * Get a number of records as an associative array of objects.
 *
 * Return value as for @see function get_records_assoc
 *
 * @param string $sql the SQL select query to execute.
 * @param array $values When using prepared statements, this is the value 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.
 * @throws SQLException
 */
function get_records_sql_assoc($sql, array $values = null, $limitfrom = '', $limitnum = '') {
    $rs = get_recordset_sql($sql, $values, $limitfrom, $limitnum);
    return recordset_to_assoc($rs);
}

/**
 * Get a number of records as an array of objects.
 *
 * Return value as for {@link get_records_array}
 *
 * @param string $sql the SQL select query to execute.
 * @param array $values When using prepared statements, this is the value 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.
 * @throws SQLException
 */
function get_records_sql_array($sql, array $values = null, $limitfrom = '', $limitnum = '') {
    $rs = get_recordset_sql($sql, $values, $limitfrom, $limitnum);
    return recordset_to_array($rs);
}


//
// Menu related functions - get_records_*_menu
//

/**
 * Utility function used by the following 3 methods.
 *
 * @param ADORecordSet $rs an ADODB RecordSet object with two columns.
 * @return mixed an associative array, or false if an error occurred or the RecordSet was empty.
 * @access private
 */
function recordset_to_menu(ADORecordSet $rs) {
    global $CFG;

    if ($rs && $rs->RecordCount() > 0) {
        $keys = array_keys($rs->fields);
        $key0 = $keys[0];
        if (isset($keys[1])) {
            $key1 = $keys[1];
        }
        else {
            $key1 = $keys[0];
        }
        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 {@link 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 occurred.
 */
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 array $values When using prepared statements, this is the value array (optional).
 * @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 occurred.
 */
function get_records_select_menu($table, $select='', array $values=null, $sort='', $fields='*') {
    $rs = get_recordset_select($table, $select, $values, $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.
 * @param array $values When using prepared statements, this is the value array (optional).
 * @return mixed an associative array, or false if no records were found or an error occured.
 */
function get_records_sql_menu($sql, array $values=null) {
    $rs = get_recordset_sql($sql,$values);
    return recordset_to_menu($rs);
}


//
// Field related data access - get_field*
//

/**
 * 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 $field 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
 * @throws SQLException
 */
function get_field($table, $field, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
    $select = where_clause_prepared($field1, $field2, $field3);
    $values = where_values_prepared($value1, $value2, $value3);

    return get_field_sql('SELECT ' . db_quote_identifier($field) . ' FROM ' . db_table_name($table) . ' ' . $select, $values);
}

/**
 * Get a single value from a table.
 *
 * @param string $sql an SQL statement expected to return a single value.
 * @param array $values When using prepared statements, this is the value array (optional).
 * @return mixed the specified value.
 * @throws SQLException
 */
function get_field_sql($sql, array $values=null) {
    $rs = get_recordset_sql($sql, $values);
    if ($rs && $rs->RecordCount() == 1) {
        return reset($rs->fields);
    } else {
        return false;
    }
}


//
// Column related data access - get_column*
//

/**
 * Get a single column from a table where all the given fields match the given values.
 *
 * @param string $table the table to query.
 * @param string $field 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
 * @throws SQLException
 */
function get_column($table, $field, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
    $select = where_clause_prepared($field1, $field2, $field3);
    $values = where_values_prepared($value1, $value2, $value3);

    return get_column_sql('SELECT ' . db_quote_identifier($field) . ' FROM ' . db_table_name($table) . ' ' . $select, $values);
}

/**
 * Get a single column from a table.
 *
 * @param string $sql an SQL statement expected to return a single value.
 * @param array $values When using prepared statements, this is the value array (optional).
 * @return mixed the specified value.
 * @throws SQLException
 */
function get_column_sql($sql, array $values=null) {
    global $db;

    $sql = db_quote_table_placeholders($sql);

    try {
        if (!empty($values) && is_array($values) && count($values) > 0) {
            return $db->GetCol($sql, $values);
        }
        else {
            return $db->GetCol($sql);
        }
    }
    catch (ADODB_Exception $e) {
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
    }
}


//
// Field related data modification - set_field*
//

/**
 * Set a single field in every table row where all the given fields match the given values.
 *
 * @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 ADORecordSet An ADODB RecordSet object with the results from the SQL call or false.
 * @throws SQLException
 */
function set_field($table, $newfield, $newvalue, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
    global $db;

    $select = where_clause_prepared($field1, $field2, $field3);
    $values = where_values_prepared($value1, $value2, $value3);

    return set_field_select($table, $newfield, $newvalue, $select, $values);
}

function set_field_select($table, $newfield, $newvalue, $select, array $values) {
    global $db;

    // @todo Catalyst IT Ltd
    if (!empty($select) && !preg_match('/^\s*where/i', $select)) {
        $select = ' WHERE ' . $select;
    }

    $select = db_quote_table_placeholders($select);

    $values = array_merge(array($newvalue), $values);
    $sql = 'UPDATE '. db_table_name($table) .' SET '. db_quote_identifier($newfield)  .' = ? ' . $select;
    try {
        $stmt = $db->Prepare($sql);
        return $db->Execute($stmt, $values);
    }
    catch (ADODB_Exception $e) {
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
    }
}


//
// Delete based functions - delete_records*
//

/**
 * Delete the records from a table where all the given fields match the given values.
 *
 * @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 ADORecordSet An ADODB RecordSet object with the results from the SQL call or false.
 * @throws SQLException
 */
function delete_records($table, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
    global $db;

    $select = where_clause_prepared($field1, $field2, $field3);
    $values = where_values_prepared($value1, $value2, $value3);

    $sql = 'DELETE FROM '. db_table_name($table) . ' ' . $select;
    try {
        $stmt = $db->Prepare($sql);
        return $db->Execute($stmt,$values);
    }
    catch (ADODB_Exception $e) {
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
    }
}

/**
 * Delete one or more records from a table
 *
 * @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).
 * @param array $values When using prepared statements, this is the value array (optional).
 * @return ADORecordSet An ADODB RecordSet object with the results from the SQL call or false.
 * @throws SQLException
 */
function delete_records_select($table, $select='', array $values=null) {
    if ($select) {
        $select = 'WHERE '.$select;
    }
    return delete_records_sql('DELETE FROM '. db_table_name($table) .' '. $select, $values);
}

/**
 * @todo <nigel> This function does nothing delete specific. The functionality
 * it has with the $values parameter should be merged with the execute_sql
 * function
 */
function delete_records_sql($sql, array $values=null) {
    global $db;

    $sql = db_quote_table_placeholders($sql);

    try {
        $result = false;
        if (!empty($values) && is_array($values) && count($values) > 0) {
            $stmt = $db->Prepare($sql);
            $result = $db->Execute($stmt, $values);
        } else {
            $result = $db->_Execute($sql);
        }
    }
    catch (ADODB_Exception $e) {
        throw new SQLException(create_sql_exception_message(