dml.php 71.9 KB
Newer Older
Martyn Smith's avatar
Martyn Smith committed
1
<?php
Penny Leach's avatar
Penny Leach committed
2 3 4
/**
 *
 * @package    mahara
Penny Leach's avatar
Penny Leach committed
5 6
 * @subpackage core
 * @author     Martin Dougiamas <martin@moodle.com>
7 8
 * @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.
Penny Leach's avatar
Penny Leach committed
9 10 11
 * @copyright  (C) 2001-3001 Martin Dougiamas http://dougiamas.com
 *
 */
Penny Leach's avatar
Penny Leach committed
12

Penny Leach's avatar
Penny Leach committed
13
defined('INTERNAL') || die();
Penny Leach's avatar
Penny Leach committed
14

15 16 17 18 19 20 21
/** 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);

22 23 24 25 26 27 28 29 30
/**
 * Return a table name, properly prefixed and escaped
 *
 */
function db_table_name($name) {
    return db_quote_identifier(get_config('dbprefix') . $name);
}

/**
Aaron Wells's avatar
Aaron Wells committed
31
 * Searches through a query for strings looking like {name}, to replace with
32 33 34 35 36 37
 * correctly quoted and prefixed table names
 *
 * @param string $sql The SQL to replace the placeholders in
 * @return string
 */
function db_quote_table_placeholders($sql) {
38
    return preg_replace_callback('/\{([a-z][a-z0-9_]+)\}/', '_db_quote_table_placeholders_callback', $sql);
39 40 41
}

/**
42 43 44
 * A callback function used only in db_quote_table_placeholders
 * @param array $matches
 */
45
function _db_quote_table_placeholders_callback(array $matches) {
46 47 48 49 50
    return db_table_name($matches[1]);
}

/**
 * Given a table name or other identifier, return it quoted for the appropriate
51 52 53 54 55 56
 * database engine currently being used
 *
 * @param string $identifier The identifier to quote
 * @return string
 */
function db_quote_identifier($identifier) {
Aaron Wells's avatar
Aaron Wells committed
57
    // Currently, postgres and mysql (in postgres compat. mode) both support
58
    // the sql standard "
59 60
    $identifier = trim($identifier);
    if (strpos($identifier, '"') !== false
61
        || $identifier === '*'
62
        || preg_match('/\(/i', $identifier)) {
63 64
        return $identifier;
    }
65 66 67
    return '"' . $identifier . '"';
}

68 69 70 71 72 73 74 75 76 77 78
/**
 * 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()) {
79
        $result = $db->_Execute("SHOW VARIABLES LIKE 'character_set_database'");
80
        return preg_match('/^utf8/', $result->fields['Value']);
81 82
    }
    if (is_postgres()) {
83
        $result = $db->_Execute("SHOW SERVER_ENCODING");
84 85 86 87 88
        return $result->fields['server_encoding'] == 'UTF8';
    }
    return false;
}

89 90 91 92 93 94 95
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()) {
96
        $result = $db->_Execute("
97 98 99 100 101 102 103
            SELECT SUM( data_length + index_length ) AS dbsize
            FROM information_schema.tables
            WHERE table_schema = $dbname
        ");
        return $result->fields['dbsize'];
    }
    if (is_postgres()) {
104
        $result = $db->_Execute("SELECT * FROM pg_database_size($dbname)");
105 106 107 108 109
        return $result->fields['pg_database_size'];
    }
    return false;
}

110 111 112 113 114 115
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()) {
116
        $result = $db->_Execute("SHOW VARIABLES LIKE 'collation_database'");
117 118 119 120 121 122 123 124 125 126
        $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;
}

Penny Leach's avatar
Penny Leach committed
127 128 129 130 131 132 133
/**
 * 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.
134
 * @param array $values When using prepared statements, this is the value array (optional).
135
 * @return boolean
136
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
137
 */
138
function execute_sql($command, array $values=null) {
139
    global $db;
Aaron Wells's avatar
Aaron Wells committed
140

141
    if (!is_a($db, 'ADOConnection')) {
142
        throw new SQLException('Database connection is not available ');
143
    }
Penny Leach's avatar
Penny Leach committed
144

145 146
    $command = db_quote_table_placeholders($command);

147
    try {
148 149 150 151 152
        if (!empty($values) && is_array($values) && count($values) > 0) {
            $stmt = $db->Prepare($command);
            $result = $db->Execute($stmt, $values);
        }
        else {
153
            $result = $db->_Execute($command);
154
        }
155 156
    }
    catch (ADODB_Exception $e) {
157
        log_debug($e->getMessage() . "Command was: $command");
158
        throw new SQLException('Could not execute command: ' . $command);
Penny Leach's avatar
Penny Leach committed
159 160
    }

161
    return true;
Penny Leach's avatar
Penny Leach committed
162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179
}

/// 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.
180
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
181 182 183 184
 */
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);
185
    return record_exists_sql('SELECT * FROM ' . db_table_name($table) .' '. $select, $values);
Penny Leach's avatar
Penny Leach committed
186 187
}

Penny Leach's avatar
Penny Leach committed
188 189 190 191
/**
 * 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
Aaron Wells's avatar
Aaron Wells committed
192
 *
Penny Leach's avatar
Penny Leach committed
193 194
 * @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.
195
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
196 197 198
 * @return bool true if a matching record exists, else false.
 * @throws SQLException
 */
199
function record_exists_select($table, $select='', array $values=null) {
Penny Leach's avatar
Penny Leach committed
200 201 202 203 204 205 206

    global $CFG;

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

207
    return record_exists_sql('SELECT * FROM '. db_table_name($table) . ' ' . $select, $values);
Penny Leach's avatar
Penny Leach committed
208 209
}

Penny Leach's avatar
Penny Leach committed
210 211 212
/**
 * Test whether a SQL SELECT statement returns any records.
 *
213
 * This function returns true if at least one record is returned.
Penny Leach's avatar
Penny Leach committed
214 215
 *
 * @param string $sql The SQL statement to be executed. If using $values, placeholder ?s are expected. If not, the string should be escaped correctly.
216
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
217
 * @return bool true if the SQL executes without errors and returns at least one record.
218
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
219
 */
220
function record_exists_sql($sql, array $values=null) {
221 222
    $rs = get_recordset_sql($sql, $values, 0, 1);
    return $rs->RecordCount() > 0;
Penny Leach's avatar
Penny Leach committed
223 224 225 226 227 228 229 230 231 232 233 234 235
}

/**
 * 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.
236
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
237 238 239 240
 */
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);
241
    return count_records_sql('SELECT COUNT(*) FROM '. db_table_name($table) . ' ' . $select, $values);
Penny Leach's avatar
Penny Leach committed
242 243 244 245 246 247 248 249 250 251
}

/**
 * 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.
252
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
253
 */
254
function count_records_select($table, $select='', array $values=null, $countitem='COUNT(*)') {
Penny Leach's avatar
Penny Leach committed
255
    if ($select) {
256
        $select = 'WHERE ' . $select;
Penny Leach's avatar
Penny Leach committed
257
    }
258
    return count_records_sql('SELECT '. $countitem .' FROM '. db_table_name($table) . ' ' . $select, $values);
Penny Leach's avatar
Penny Leach committed
259 260 261 262 263 264 265 266 267 268 269 270
}

/**
 * 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.
271
 * @param array $values When using prepared statements, this is the value array (optional).
272
 * @return int        The count.
273
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
274
 */
275
function count_records_sql($sql, array $values=null) {
276
    $rs = get_recordset_sql($sql, $values);
277 278 279
    if (!$rs->fields) {
        throw new SQLException('count_records_sql() should not return false. Is your query misisng "COUNT(*)"?');
    }
280
    return reset($rs->fields);
Penny Leach's avatar
Penny Leach committed
281 282 283 284 285 286 287 288 289
}

/// 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).
290
 * @param string $value1 the value field1 must have (required if field1 is given, else optional).
Penny Leach's avatar
Penny Leach committed
291
 * @param string $field2 the second field to check (optional).
292
 * @param string $value2 the value field2 must have (required if field2 is given, else optional).
Penny Leach's avatar
Penny Leach committed
293
 * @param string $field3 the third field to check (optional).
294
 * @param string $value3 the value field3 must have (required if field3 is given, else optional).
295
 * @param string $fields Which fields to return (default '*')
296 297 298
 * @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.
Penny Leach's avatar
Penny Leach committed
299
 * @return mixed a fieldset object containing the first mathcing record, or false if none found.
300
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
301
 */
302
function get_record($table, $field1, $value1, $field2=null, $value2=null, $field3=null, $value3=null, $fields='*', $strictness=WARN_MULTIPLE) {
Penny Leach's avatar
Penny Leach committed
303 304
    $select = where_clause_prepared($field1, $field2, $field3);
    $values = where_values_prepared($value1, $value2, $value3);
305
    return get_record_sql('SELECT ' . $fields . ' FROM ' . db_table_name($table) . ' ' . $select, $values, $strictness);
Penny Leach's avatar
Penny Leach committed
306 307 308 309 310
}

/**
 * Get a single record as an object using an SQL statement
 *
311
 * This function is designed to retrieve ONE record. If your query returns more than one record,
312
 * an exception is thrown. If you want more than one record, use get_records_sql_array or get_records_sql_assoc
Penny Leach's avatar
Penny Leach committed
313 314
 *
 * @param string $sql The SQL string you wish to be executed, should normally only return one record.
315
 * @param array $values When using prepared statements, this is the value array (optional).
316 317 318
 * @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.
319
 * @return Found record as object. False if not found
320
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
321
 */
322
function get_record_sql($sql, array $values=null, $strictness=WARN_MULTIPLE) {
323
    $limitfrom = 0;
324 325 326 327 328
    $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;
329 330 331 332 333

        // Don't even bother checking for multiples if they don't care
        if ($strictness == IGNORE_MULTIPLE) {
            $limitnum = 1;
        }
334
    }
Penny Leach's avatar
Penny Leach committed
335 336 337 338 339 340 341

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

    $recordcount = $rs->RecordCount();

342 343
    // Found no records
    if ($recordcount == 0) {
Penny Leach's avatar
Penny Leach committed
344
        return false;
345
    }
346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361

    // 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;
        }
Penny Leach's avatar
Penny Leach committed
362
    }
363
    return (object)$rs->fields;
Penny Leach's avatar
Penny Leach committed
364 365 366 367 368 369 370
}

/**
 * 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.
371
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
372
 * @param string $fields A comma separated list of fields to be returned from the chosen table.
373 374 375
 * @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.
376
 * @return object Returns an array of found records (as objects)
377
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
378
 */
379
function get_record_select($table, $select='', array $values=null, $fields='*', $strictness=WARN_MULTIPLE) {
Penny Leach's avatar
Penny Leach committed
380 381 382
    if ($select) {
        $select = 'WHERE '. $select;
    }
383
    return get_record_sql('SELECT '. $fields .' FROM ' . db_table_name($table) .' '. $select, $values, $strictness);
Penny Leach's avatar
Penny Leach committed
384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399
}

/**
 * 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
400
 * from general Moodle scripts.  Use get_record, get_records_* etc.
Penny Leach's avatar
Penny Leach committed
401 402 403 404 405 406 407 408
 *
 * 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
409
 * if the query succeeds. If an error occurrs, an exception is thrown.
Penny Leach's avatar
Penny Leach committed
410 411 412
 *
 * @param string $table the table to query.
 * @param string $field a field to check (optional).
413
 * @param string $value the value the field must have (required if field1 is given, else optional).
Penny Leach's avatar
Penny Leach committed
414 415 416 417
 * @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).
418
 * @return ADORecordSet an ADODB RecordSet object.
419
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
420 421 422 423
 */
function get_recordset($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
    $values = null;
    if ($field) {
424
        $select = db_quote_identifier($field) . " = ?";
Penny Leach's avatar
Penny Leach committed
425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442
        $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.
443
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
444 445 446 447
 * @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).
448
 * @return ADORecordSet an ADODB RecordSet object
449
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
450
 */
451
function get_recordset_select($table, $select='', array $values=null, $sort='', $fields='*', $limitfrom='', $limitnum='') {
Penny Leach's avatar
Penny Leach committed
452 453 454 455 456
    if ($select) {
        $select = ' WHERE '. $select;
    }

    if ($limitfrom !== '') {
457
        $limit = 'LIMIT ' . intval($limitnum)  . ' OFFSET ' . intval($limitfrom);
Penny Leach's avatar
Penny Leach committed
458 459 460 461 462 463 464 465
    } else {
        $limit = '';
    }

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

466
    return get_recordset_sql('SELECT '. $fields .' FROM '. db_table_name($table) . $select . $sort .' '. $limit, $values);
Penny Leach's avatar
Penny Leach committed
467 468 469 470 471
}

/**
 * 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
472
 * from general Moodle scripts.  Use get_record, get_records_* etc.
Penny Leach's avatar
Penny Leach committed
473 474 475 476 477
 *
 * The return type is as for @see function get_recordset.
 *
 * @uses $db
 * @param string $sql the SQL select query to execute.
478
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
479 480
 * @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).
481
 * @return ADORecordSet an ADODB RecordSet object
482
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
483
 */
484
function get_recordset_sql($sql, array $values=null, $limitfrom=null, $limitnum=null) {
485
    global $db;
Penny Leach's avatar
Penny Leach committed
486

487
    if (!is_a($db, 'ADOConnection')) {
488
        throw new SQLException('Database connection is not available ');
Penny Leach's avatar
Penny Leach committed
489 490
    }

491 492
    $sql = db_quote_table_placeholders($sql);

493 494 495 496 497 498 499
    if ($values === null || $values === array()) {
        $values = false;
    }
    else if (!is_array($values)) {
        throw new SQLException('Invalid values parameter sent to get_recordset_sql.');
    }

500 501 502 503 504
    try {
        if ($limitfrom || $limitnum) {
            ///Special case, 0 must be -1 for ADOdb
            $limitfrom = empty($limitfrom) ? -1 : $limitfrom;
            $limitnum  = empty($limitnum) ? -1 : $limitnum;
505
            $rs = $db->SelectLimit($sql, $limitnum, $limitfrom, $values);
Penny Leach's avatar
Penny Leach committed
506
        } else {
507
            $rs = false;
508
            if ($values) {
509 510 511
                $stmt = $db->Prepare($sql);
                $rs = $db->Execute($stmt, $values);
            } else {
512
                $rs = $db->_Execute($sql);
513
            }
Penny Leach's avatar
Penny Leach committed
514 515
        }
    }
516
    catch (ADODB_Exception $e) {
517
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
Penny Leach's avatar
Penny Leach committed
518
    }
Aaron Wells's avatar
Aaron Wells committed
519

Penny Leach's avatar
Penny Leach committed
520 521 522 523
   return $rs;
}

/**
524
 * Utility function to turn a result set into an array of records
Penny Leach's avatar
Penny Leach committed
525
 *
526
 * @param ADORecordSet an ADODB RecordSet object.
527
 * @return mixed an array of objects, or false if the RecordSet was empty.
528
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
529
 */
530
function recordset_to_array(ADORecordSet $rs) {
531
    if ($rs && $rs->RecordCount() > 0) {
532 533 534 535
        $array = $rs->GetArray();
        foreach ($array as &$a) {
            $a = (object)$a;
        }
536
        return $array;
537 538 539 540 541 542
    }
    else {
        return false;
    }
}

543 544 545 546 547

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

548 549 550 551 552
/**
 * 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)
 *
553 554
 * @param  ADORecordSet $rs An ADODB RecordSet object.
 * @return mixed An array of objects, or false if the RecordSet was empty.
555
 * @throws SQLException
556
 * @access private
557
 */
558
function recordset_to_assoc(ADORecordSet $rs) {
559 560 561 562 563 564 565 566 567 568 569 570 571 572

    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;
    }

Penny Leach's avatar
Penny Leach committed
573
    if ($rs && $rs->RecordCount() > 0) {
574 575 576
        // 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.
Penny Leach's avatar
Penny Leach committed
577
        $firstcolumn = $rs->FetchField(0);
578
        // Get the whole associative array
Penny Leach's avatar
Penny Leach committed
579 580
        if ($records = $rs->GetAssoc(true)) {
            foreach ($records as $key => $record) {
581
                $record[$firstcolumn->name] = $key;
Aaron Wells's avatar
Aaron Wells committed
582
                $objects[$key] = (object) $record;
Penny Leach's avatar
Penny Leach committed
583 584 585 586 587 588 589 590 591 592 593
            }
            return $objects;
        } else {
            return false;
        }
    } else {
        return false;
    }
}

/**
594 595
 * 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
596
 * column in the result set. As a result, you may lose some rows! Please use
597
 * {@link get_records_*_array} instead where possible)
Penny Leach's avatar
Penny Leach committed
598 599 600 601 602 603 604 605 606 607 608 609 610 611
 *
 * 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).
612
 * @return mixed an array of objects, or false if no records were found.
613
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
614
 */
615
function get_records_assoc($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
Penny Leach's avatar
Penny Leach committed
616
    $rs = get_recordset($table, $field, $value, $sort, $fields, $limitfrom, $limitnum);
617
    return recordset_to_assoc($rs);
Penny Leach's avatar
Penny Leach committed
618 619 620 621 622
}

/**
 * Get a number of records as an array of objects.
 *
623 624 625 626 627 628 629 630 631 632 633 634 635 636
 * 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.
637
 * @throws SQLException
638
 */
639
function get_records_array($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
640 641 642 643 644 645 646
    $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.
 *
647
 * Return value as for @see function get_records_assoc
Penny Leach's avatar
Penny Leach committed
648 649 650
 *
 * @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.
651
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
652 653 654 655
 * @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).
656
 * @return mixed an array of objects, or false if no records were found.
657
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
658
 */
659
function get_records_select_assoc($table, $select='', $values=null, $sort='', $fields='*', $limitfrom='', $limitnum='') {
Penny Leach's avatar
Penny Leach committed
660
    $rs = get_recordset_select($table, $select, $values, $sort, $fields, $limitfrom, $limitnum);
661
    return recordset_to_assoc($rs);
Penny Leach's avatar
Penny Leach committed
662 663 664 665 666
}

/**
 * Get a number of records as an array of objects.
 *
667
 * Return value as for {@link get_records_array}.
668 669 670
 *
 * @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.
671
 * @param array $values When using prepared statements, this is the value array (optional).
672 673 674 675 676
 * @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.
677
 * @throws SQLException
678
 */
679
function get_records_select_array($table, $select='', array $values=null, $sort='', $fields='*', $limitfrom='', $limitnum='') {
680 681 682 683 684 685 686
    $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.
 *
687
 * Return value as for @see function get_records_assoc
Penny Leach's avatar
Penny Leach committed
688 689
 *
 * @param string $sql the SQL select query to execute.
690
 * @param array $values When using prepared statements, this is the value array.
Penny Leach's avatar
Penny Leach committed
691 692
 * @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).
693
 * @return mixed an array of objects, or false if no records were found.
694
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
695
 */
696
function get_records_sql_assoc($sql, array $values = null, $limitfrom = '', $limitnum = '') {
697 698 699 700 701 702 703
    $rs = get_recordset_sql($sql, $values, $limitfrom, $limitnum);
    return recordset_to_assoc($rs);
}

/**
 * Get a number of records as an array of objects.
 *
704
 * Return value as for {@link get_records_array}
705 706
 *
 * @param string $sql the SQL select query to execute.
707
 * @param array $values When using prepared statements, this is the value array.
708 709 710
 * @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.
711
 * @throws SQLException
712
 */
713
function get_records_sql_array($sql, array $values = null, $limitfrom = '', $limitnum = '') {
Penny Leach's avatar
Penny Leach committed
714 715 716 717
    $rs = get_recordset_sql($sql, $values, $limitfrom, $limitnum);
    return recordset_to_array($rs);
}

718 719 720 721 722

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

Penny Leach's avatar
Penny Leach committed
723 724 725
/**
 * Utility function used by the following 3 methods.
 *
726
 * @param ADORecordSet $rs an ADODB RecordSet object with two columns.
727
 * @return mixed an associative array, or false if an error occurred or the RecordSet was empty.
728
 * @access private
Penny Leach's avatar
Penny Leach committed
729
 */
730
function recordset_to_menu(ADORecordSet $rs) {
Penny Leach's avatar
Penny Leach committed
731 732 733 734
    global $CFG;

    if ($rs && $rs->RecordCount() > 0) {
        $keys = array_keys($rs->fields);
735 736 737 738 739 740 741
        $key0 = $keys[0];
        if (isset($keys[1])) {
            $key1 = $keys[1];
        }
        else {
            $key1 = $keys[0];
        }
Penny Leach's avatar
Penny Leach committed
742 743 744 745 746 747 748 749 750
        while (!$rs->EOF) {
            $menu[$rs->fields[$key0]] = $rs->fields[$key1];
            $rs->MoveNext();
        }
        return $menu;
    } else {
        return false;
    }
}
751

Penny Leach's avatar
Penny Leach committed
752 753 754
/**
 * Get the first two columns from a number of records as an associative array.
 *
755
 * Arguments as for {@link get_recordset}.
Penny Leach's avatar
Penny Leach committed
756 757 758 759 760 761 762 763 764 765 766
 *
 * 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).
767
 * @return mixed an associative array, or false if no records were found or an error occurred.
Penny Leach's avatar
Penny Leach committed
768 769 770 771 772 773 774 775 776 777 778 779 780 781
 */
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.
782
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
783 784
 * @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.
785
 * @return mixed an associative array, or false if no records were found or an error occurred.
Penny Leach's avatar
Penny Leach committed
786
 */
787
function get_records_select_menu($table, $select='', array $values=null, $sort='', $fields='*') {
Penny Leach's avatar
Penny Leach committed
788 789 790 791 792 793 794 795 796 797 798
    $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.
799
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
800 801
 * @return mixed an associative array, or false if no records were found or an error occured.
 */
802
function get_records_sql_menu($sql, array $values=null) {
Penny Leach's avatar
Penny Leach committed
803 804 805
    $rs = get_recordset_sql($sql,$values);
    return recordset_to_menu($rs);
}
806 807 808 809 810


//
// Field related data access - get_field*
//
Penny Leach's avatar
Penny Leach committed
811 812 813 814 815

/**
 * Get a single value from a table row where all the given fields match the given values.
 *
 * @param string $table the table to query.
816
 * @param string $field the field to return the value of.
Penny Leach's avatar
Penny Leach committed
817 818 819 820 821 822
 * @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).
823
 * @return mixed the specified value
824
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
825
 */
Martyn Smith's avatar
Martyn Smith committed
826
function get_field($table, $field, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
Penny Leach's avatar
Penny Leach committed
827 828
    $select = where_clause_prepared($field1, $field2, $field3);
    $values = where_values_prepared($value1, $value2, $value3);
Aaron Wells's avatar
Aaron Wells committed
829

830
    return get_field_sql('SELECT ' . db_quote_identifier($field) . ' FROM ' . db_table_name($table) . ' ' . $select, $values);
Penny Leach's avatar
Penny Leach committed
831 832
}

833 834 835 836
/**
 * Get a single value from a table.
 *
 * @param string $sql an SQL statement expected to return a single value.
837
 * @param array $values When using prepared statements, this is the value array (optional).
838 839 840
 * @return mixed the specified value.
 * @throws SQLException
 */
841
function get_field_sql($sql, array $values=null) {
842 843 844 845 846 847 848 849
    $rs = get_recordset_sql($sql, $values);
    if ($rs && $rs->RecordCount() == 1) {
        return reset($rs->fields);
    } else {
        return false;
    }
}

850 851 852 853 854

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

855 856 857 858
/**
 * Get a single column from a table where all the given fields match the given values.
 *
 * @param string $table the table to query.
859
 * @param string $field the field to return the value of.
860 861 862 863 864 865 866 867 868
 * @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
 */
869
function get_column($table, $field, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
870 871
    $select = where_clause_prepared($field1, $field2, $field3);
    $values = where_values_prepared($value1, $value2, $value3);
Aaron Wells's avatar
Aaron Wells committed
872

873
    return get_column_sql('SELECT ' . db_quote_identifier($field) . ' FROM ' . db_table_name($table) . ' ' . $select, $values);
874 875 876 877 878 879
}

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

887 888
    $sql = db_quote_table_placeholders($sql);

889
    try {
890 891 892 893 894 895
        if (!empty($values) && is_array($values) && count($values) > 0) {
            return $db->GetCol($sql, $values);
        }
        else {
            return $db->GetCol($sql);
        }
896 897 898 899
    }
    catch (ADODB_Exception $e) {
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
    }
900 901
}

902 903 904 905 906

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

Penny Leach's avatar
Penny Leach committed
907 908 909 910 911 912 913 914 915 916 917 918 919
/**
 * 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).
920
 * @return ADORecordSet An ADODB RecordSet object with the results from the SQL call or false.
921
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
922
 */
923
function set_field($table, $newfield, $newvalue, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
924
    global $db;
Penny Leach's avatar
Penny Leach committed
925 926

    $select = where_clause_prepared($field1, $field2, $field3);
Penny Leach's avatar
Penny Leach committed
927 928 929 930
    $values = where_values_prepared($value1, $value2, $value3);

    return set_field_select($table, $newfield, $newvalue, $select, $values);
}
Penny Leach's avatar
Penny Leach committed
931

932
function set_field_select($table, $newfield, $newvalue, $select, array $values) {
Penny Leach's avatar
Penny Leach committed
933 934
    global $db;

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

940 941
    $select = db_quote_table_placeholders($select);

Penny Leach's avatar
Penny Leach committed
942
    $values = array_merge(array($newvalue), $values);
943
    $sql = 'UPDATE '. db_table_name($table) .' SET '. db_quote_identifier($newfield)  .' = ? ' . $select;
944
    try {
945
        $stmt = $db->Prepare($sql);
946 947 948
        return $db->Execute($stmt, $values);
    }
    catch (ADODB_Exception $e) {
949
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
950
    }
Penny Leach's avatar
Penny Leach committed
951 952
}

953 954 955 956 957

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

Penny Leach's avatar
Penny Leach committed
958 959 960 961 962 963 964 965 966 967 968
/**
 * 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).
969
 * @return ADORecordSet An ADODB RecordSet object with the results from the SQL call or false.
970
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
971 972
 */
function delete_records($table, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
973
    global $db;
Penny Leach's avatar
Penny Leach committed
974 975 976

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

978
    $sql = 'DELETE FROM '. db_table_name($table) . ' ' . $select;
979
    try {
980
        $stmt = $db->Prepare($sql);
981 982 983
        return $db->Execute($stmt,$values);
    }
    catch (ADODB_Exception $e) {
984
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
985
    }
Penny Leach's avatar
Penny Leach committed
986 987 988 989 990 991 992 993
}

/**
 * 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).
994
 * @param array $values When using prepared statements, this is the value array (optional).
995
 * @return ADORecordSet An ADODB RecordSet object with the results from the SQL call or false.
996
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
997
 */
998
function delete_records_select($table, $select='', array $values=null) {
Penny Leach's avatar
Penny Leach committed
999 1000 1001
    if ($select) {
        $select = 'WHERE '.$select;
    }
1002
    return delete_records_sql('DELETE FROM '. db_table_name($table) .' '. $select, $values);
Penny Leach's avatar
Penny Leach committed
1003 1004
}

1005
/**
Aaron Wells's avatar
Aaron Wells committed
1006 1007
 * @todo <nigel> This function does nothing delete specific. The functionality
 * it has with the $values parameter should be merged with the execute_sql
1008 1009
 * function
 */
1010
function delete_records_sql($sql, array $values=null) {
Penny Leach's avatar
Penny Leach committed
1011
    global $db;
Penny Leach's avatar
Penny Leach committed
1012

1013 1014
    $sql = db_quote_table_placeholders($sql);

1015 1016 1017
    try {
        $result = false;
        if (!empty($values) && is_array($values) && count($values) > 0) {
Penny Leach's avatar
Penny Leach committed
1018 1019
            $stmt = $db->Prepare($sql);
            $result = $db->Execute($stmt, $values);
1020
        } else {
1021
            $result = $db->_Execute($sql);
1022 1023 1024
        }
    }
    catch (ADODB_Exception $e) {
1025
        throw new SQLException(create_sql_exception_message(