dml.php 64 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 22 23
/**
 * 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
24
 * Searches through a query for strings looking like {name}, to replace with
25 26 27 28 29 30
 * correctly quoted and prefixed table names
 *
 * @param string $sql The SQL to replace the placeholders in
 * @return string
 */
function db_quote_table_placeholders($sql) {
31
    return preg_replace_callback('/\{([a-z][a-z0-9_]+)\}/', '_db_quote_table_placeholders_callback', $sql);
32 33 34
}

/**
35 36 37 38 39 40 41 42 43
 * A callback function used only in db_quote_table_placeholders
 * @param array $matches
 */
function _db_quote_table_placeholders_callback($matches) {
    return db_table_name($matches[1]);
}

/**
 * Given a table name or other identifier, return it quoted for the appropriate
44 45 46 47 48 49
 * 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
50
    // Currently, postgres and mysql (in postgres compat. mode) both support
51
    // the sql standard "
52 53 54
    if (strpos($identifier, '"') !== false) {
        return $identifier;
    }
55 56 57
    return '"' . $identifier . '"';
}

58 59 60 61 62 63 64 65 66 67 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()) {
        $result = $db->Execute("SHOW VARIABLES LIKE 'character_set_database'");
        return $result->fields['Value'] == 'utf8';
    }
    if (is_postgres()) {
        $result = $db->Execute("SHOW SERVER_ENCODING");
        return $result->fields['server_encoding'] == 'UTF8';
    }
    return false;
}

79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
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;
}

100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116
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;
}

Penny Leach's avatar
Penny Leach committed
117 118 119 120 121 122 123
/**
 * 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.
124
 * @param array $values When using prepared statements, this is the value array (optional).
125
 * @return boolean
126
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
127
 */
128
function execute_sql($command, $values=null) {
129
    global $db;
Aaron Wells's avatar
Aaron Wells committed
130

131
    if (!is_a($db, 'ADOConnection')) {
132
        throw new SQLException('Database connection is not available ');
133
    }
Penny Leach's avatar
Penny Leach committed
134

135 136
    $command = db_quote_table_placeholders($command);

137
    try {
138 139 140 141 142 143 144
        if (!empty($values) && is_array($values) && count($values) > 0) {
            $stmt = $db->Prepare($command);
            $result = $db->Execute($stmt, $values);
        }
        else {
            $result = $db->Execute($command);
        }
145 146
    }
    catch (ADODB_Exception $e) {
147
        log_debug($e->getMessage() . "Command was: $command");
148
        throw new SQLException('Could not execute command: ' . $command);
Penny Leach's avatar
Penny Leach committed
149 150
    }

151
    return true;
Penny Leach's avatar
Penny Leach committed
152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169
}

/// 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.
170
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
171 172 173 174
 */
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);
175
    return record_exists_sql('SELECT * FROM ' . db_table_name($table) .' '. $select, $values);
Penny Leach's avatar
Penny Leach committed
176 177
}

Penny Leach's avatar
Penny Leach committed
178 179 180 181
/**
 * 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
182
 *
Penny Leach's avatar
Penny Leach committed
183 184
 * @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.
185
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
186 187 188 189 190 191 192 193 194 195 196
 * @return bool true if a matching record exists, else false.
 * @throws SQLException
 */
function record_exists_select($table, $select='', $values=null) {

    global $CFG;

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

197
    return record_exists_sql('SELECT * FROM '. db_table_name($table) . ' ' . $select, $values);
Penny Leach's avatar
Penny Leach committed
198 199
}

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

/**
 * 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.
226
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
227 228 229 230
 */
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);
231
    return count_records_sql('SELECT COUNT(*) FROM '. db_table_name($table) . ' ' . $select, $values);
Penny Leach's avatar
Penny Leach committed
232 233 234 235 236 237 238 239 240 241
}

/**
 * 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.
242
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
243 244 245
 */
function count_records_select($table, $select='', $values=null, $countitem='COUNT(*)') {
    if ($select) {
246
        $select = 'WHERE ' . $select;
Penny Leach's avatar
Penny Leach committed
247
    }
248
    return count_records_sql('SELECT '. $countitem .' FROM '. db_table_name($table) . ' ' . $select, $values);
Penny Leach's avatar
Penny Leach committed
249 250 251 252 253 254 255 256 257 258 259 260
}

/**
 * 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.
261
 * @param array $values When using prepared statements, this is the value array (optional).
262
 * @return int        The count.
263
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
264
 */
265 266 267
function count_records_sql($sql, $values=null) {
    $rs = get_recordset_sql($sql, $values);
    return reset($rs->fields);
Penny Leach's avatar
Penny Leach committed
268 269 270 271 272 273 274 275 276
}

/// 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).
277
 * @param string $value1 the value field1 must have (required if field1 is given, else optional).
Penny Leach's avatar
Penny Leach committed
278
 * @param string $field2 the second field to check (optional).
279
 * @param string $value2 the value field2 must have (required if field2 is given, else optional).
Penny Leach's avatar
Penny Leach committed
280
 * @param string $field3 the third field to check (optional).
281
 * @param string $value3 the value field3 must have (required if field3 is given, else optional).
Penny Leach's avatar
Penny Leach committed
282
 * @return mixed a fieldset object containing the first mathcing record, or false if none found.
283
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
284 285 286 287
 */
function get_record($table, $field1, $value1, $field2=null, $value2=null, $field3=null, $value3=null, $fields='*') {
    $select = where_clause_prepared($field1, $field2, $field3);
    $values = where_values_prepared($value1, $value2, $value3);
288
    return get_record_sql('SELECT ' . $fields . ' FROM ' . db_table_name($table) . ' ' . $select, $values);
Penny Leach's avatar
Penny Leach committed
289 290 291 292 293
}

/**
 * Get a single record as an object using an SQL statement
 *
294
 * This function is designed to retrieve ONE record. If your query returns more than one record,
295
 * 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
296 297
 *
 * @param string $sql The SQL string you wish to be executed, should normally only return one record.
298
 * @param array $values When using prepared statements, this is the value array (optional).
299
 * @return Found record as object. False if not found
300
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
301 302
 */
function get_record_sql($sql, $values=null) {
303
    $limitfrom = 0;
304 305 306 307 308 309
    $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;
    }
Penny Leach's avatar
Penny Leach committed
310 311 312 313 314 315 316 317 318

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

    $recordcount = $rs->RecordCount();

    if ($recordcount == 0) {          // Found no records
        return false;
319
    }
Aaron Wells's avatar
Aaron Wells committed
320
    else if ($recordcount == 1) {    // Found one record
Penny Leach's avatar
Penny Leach committed
321
       return (object)$rs->fields;
322 323
    }
    else {                          // Error: found more than one record
324
        throw new SQLException('get_record_sql found more than one row. If you meant to retrieve more '
325
            . 'than one record, use get_records_*, otherwise check your code or database for inconsistencies');
Penny Leach's avatar
Penny Leach committed
326 327 328 329 330 331 332 333
    }
}

/**
 * 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.
334
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
335
 * @param string $fields A comma separated list of fields to be returned from the chosen table.
336
 * @return object Returns an array of found records (as objects)
337
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
338 339 340 341 342
 */
function get_record_select($table, $select='', $values=null, $fields='*') {
    if ($select) {
        $select = 'WHERE '. $select;
    }
343
    return get_record_sql('SELECT '. $fields .' FROM ' . db_table_name($table) .' '. $select, $values);
Penny Leach's avatar
Penny Leach committed
344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359
}

/**
 * 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
360
 * from general Moodle scripts.  Use get_record, get_records_* etc.
Penny Leach's avatar
Penny Leach committed
361 362 363 364 365 366 367 368
 *
 * 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
369
 * if the query succeeds. If an error occurrs, an exception is thrown.
Penny Leach's avatar
Penny Leach committed
370 371 372
 *
 * @param string $table the table to query.
 * @param string $field a field to check (optional).
373
 * @param string $value the value the field must have (required if field1 is given, else optional).
Penny Leach's avatar
Penny Leach committed
374 375 376 377
 * @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).
378
 * @return mixed an ADODB RecordSet object.
379
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
380 381 382 383
 */
function get_recordset($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
    $values = null;
    if ($field) {
384
        $select = db_quote_identifier($field) . " = ?";
Penny Leach's avatar
Penny Leach committed
385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402
        $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.
403
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
404 405 406 407
 * @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).
408
 * @return mixed an ADODB RecordSet object
409
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
410 411 412 413 414 415 416
 */
function get_recordset_select($table, $select='', $values=null, $sort='', $fields='*', $limitfrom='', $limitnum='') {
    if ($select) {
        $select = ' WHERE '. $select;
    }

    if ($limitfrom !== '') {
417
        $limit = 'LIMIT ' . intval($limitnum)  . ' OFFSET ' . intval($limitfrom);
Penny Leach's avatar
Penny Leach committed
418 419 420 421 422 423 424 425
    } else {
        $limit = '';
    }

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

426
    return get_recordset_sql('SELECT '. $fields .' FROM '. db_table_name($table) . $select . $sort .' '. $limit, $values);
Penny Leach's avatar
Penny Leach committed
427 428 429 430 431
}

/**
 * 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
432
 * from general Moodle scripts.  Use get_record, get_records_* etc.
Penny Leach's avatar
Penny Leach committed
433 434 435 436 437
 *
 * The return type is as for @see function get_recordset.
 *
 * @uses $db
 * @param string $sql the SQL select query to execute.
438
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
439 440
 * @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).
441
 * @return mixed an ADODB RecordSet object
442
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
443 444
 */
function get_recordset_sql($sql, $values=null, $limitfrom=null, $limitnum=null) {
445
    global $db;
Penny Leach's avatar
Penny Leach committed
446

447
    if (!is_a($db, 'ADOConnection')) {
448
        throw new SQLException('Database connection is not available ');
Penny Leach's avatar
Penny Leach committed
449 450
    }

451 452
    $sql = db_quote_table_placeholders($sql);

453 454 455 456 457
    try {
        if ($limitfrom || $limitnum) {
            ///Special case, 0 must be -1 for ADOdb
            $limitfrom = empty($limitfrom) ? -1 : $limitfrom;
            $limitnum  = empty($limitnum) ? -1 : $limitnum;
458
            $rs = $db->SelectLimit($sql, $limitnum, $limitfrom, $values);
Penny Leach's avatar
Penny Leach committed
459
        } else {
460 461 462 463 464 465 466
            $rs = false;
            if (!empty($values) && is_array($values) && count($values) > 0) {
                $stmt = $db->Prepare($sql);
                $rs = $db->Execute($stmt, $values);
            } else {
                $rs = $db->Execute($sql);
            }
Penny Leach's avatar
Penny Leach committed
467 468
        }
    }
469
    catch (ADODB_Exception $e) {
470
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
Penny Leach's avatar
Penny Leach committed
471
    }
Aaron Wells's avatar
Aaron Wells committed
472

Penny Leach's avatar
Penny Leach committed
473 474 475 476
   return $rs;
}

/**
477
 * Utility function to turn a result set into an array of records
Penny Leach's avatar
Penny Leach committed
478 479
 *
 * @param object an ADODB RecordSet object.
480
 * @return mixed an array of objects, or false if the RecordSet was empty.
481
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
482 483
 */
function recordset_to_array($rs) {
484
    if ($rs && $rs->RecordCount() > 0) {
485 486 487 488
        $array = $rs->GetArray();
        foreach ($array as &$a) {
            $a = (object)$a;
        }
489
        return $array;
490 491 492 493 494 495
    }
    else {
        return false;
    }
}

496 497 498 499 500

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

501 502 503 504 505
/**
 * 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)
 *
506
 * @param  object $rs An ADODB RecordSet object.
507
 * @return mixed  An array of objects, or false if the RecordSet was empty.
508
 * @throws SQLException
509
 * @access private
510 511
 */
function recordset_to_assoc($rs) {
Penny Leach's avatar
Penny Leach committed
512
    if ($rs && $rs->RecordCount() > 0) {
513 514 515
        // 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
516
        $firstcolumn = $rs->FetchField(0);
517
        // Get the whole associative array
Penny Leach's avatar
Penny Leach committed
518 519
        if ($records = $rs->GetAssoc(true)) {
            foreach ($records as $key => $record) {
520
                $record[$firstcolumn->name] = $key;
Aaron Wells's avatar
Aaron Wells committed
521
                $objects[$key] = (object) $record;
Penny Leach's avatar
Penny Leach committed
522 523 524 525 526 527 528 529 530 531 532
            }
            return $objects;
        } else {
            return false;
        }
    } else {
        return false;
    }
}

/**
533 534
 * 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
535
 * column in the result set. As a result, you may lose some rows! Please use
536
 * {@link get_records_*_array} instead where possible)
Penny Leach's avatar
Penny Leach committed
537 538 539 540 541 542 543 544 545 546 547 548 549 550
 *
 * 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).
551
 * @return mixed an array of objects, or false if no records were found.
552
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
553
 */
554
function get_records_assoc($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
Penny Leach's avatar
Penny Leach committed
555
    $rs = get_recordset($table, $field, $value, $sort, $fields, $limitfrom, $limitnum);
556
    return recordset_to_assoc($rs);
Penny Leach's avatar
Penny Leach committed
557 558 559 560 561
}

/**
 * Get a number of records as an array of objects.
 *
562 563 564 565 566 567 568 569 570 571 572 573 574 575
 * 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.
576
 * @throws SQLException
577
 */
578
function get_records_array($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
579 580 581 582 583 584 585
    $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.
 *
586
 * Return value as for @see function get_records_assoc
Penny Leach's avatar
Penny Leach committed
587 588 589
 *
 * @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.
590
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
591 592 593 594
 * @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).
595
 * @return mixed an array of objects, or false if no records were found.
596
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
597
 */
598
function get_records_select_assoc($table, $select='', $values=null, $sort='', $fields='*', $limitfrom='', $limitnum='') {
Penny Leach's avatar
Penny Leach committed
599
    $rs = get_recordset_select($table, $select, $values, $sort, $fields, $limitfrom, $limitnum);
600
    return recordset_to_assoc($rs);
Penny Leach's avatar
Penny Leach committed
601 602 603 604 605
}

/**
 * Get a number of records as an array of objects.
 *
606
 * Return value as for {@link get_records_array}.
607 608 609
 *
 * @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.
610
 * @param array $values When using prepared statements, this is the value array (optional).
611 612 613 614 615
 * @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.
616
 * @throws SQLException
617
 */
618
function get_records_select_array($table, $select='', $values=null, $sort='', $fields='*', $limitfrom='', $limitnum='') {
619 620 621 622 623 624 625
    $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.
 *
626
 * Return value as for @see function get_records_assoc
Penny Leach's avatar
Penny Leach committed
627 628
 *
 * @param string $sql the SQL select query to execute.
629
 * @param array $values When using prepared statements, this is the value array.
Penny Leach's avatar
Penny Leach committed
630 631
 * @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).
632
 * @return mixed an array of objects, or false if no records were found.
633
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
634
 */
635
function get_records_sql_assoc($sql,$values, $limitfrom='', $limitnum='') {
636 637 638 639 640 641 642
    $rs = get_recordset_sql($sql, $values, $limitfrom, $limitnum);
    return recordset_to_assoc($rs);
}

/**
 * Get a number of records as an array of objects.
 *
643
 * Return value as for {@link get_records_array}
644 645
 *
 * @param string $sql the SQL select query to execute.
646
 * @param array $values When using prepared statements, this is the value array.
647 648 649
 * @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.
650
 * @throws SQLException
651
 */
652
function get_records_sql_array($sql,$values, $limitfrom='', $limitnum='') {
Penny Leach's avatar
Penny Leach committed
653 654 655 656
    $rs = get_recordset_sql($sql, $values, $limitfrom, $limitnum);
    return recordset_to_array($rs);
}

657 658 659 660 661

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

Penny Leach's avatar
Penny Leach committed
662 663 664
/**
 * Utility function used by the following 3 methods.
 *
665
 * @param object $rs an ADODB RecordSet object with two columns.
666
 * @return mixed an associative array, or false if an error occurred or the RecordSet was empty.
667
 * @access private
Penny Leach's avatar
Penny Leach committed
668 669 670 671 672 673
 */
function recordset_to_menu($rs) {
    global $CFG;

    if ($rs && $rs->RecordCount() > 0) {
        $keys = array_keys($rs->fields);
674 675 676 677 678 679 680
        $key0 = $keys[0];
        if (isset($keys[1])) {
            $key1 = $keys[1];
        }
        else {
            $key1 = $keys[0];
        }
Penny Leach's avatar
Penny Leach committed
681 682 683 684 685 686 687 688 689
        while (!$rs->EOF) {
            $menu[$rs->fields[$key0]] = $rs->fields[$key1];
            $rs->MoveNext();
        }
        return $menu;
    } else {
        return false;
    }
}
690

Penny Leach's avatar
Penny Leach committed
691 692 693
/**
 * Get the first two columns from a number of records as an associative array.
 *
694
 * Arguments as for {@link get_recordset}.
Penny Leach's avatar
Penny Leach committed
695 696 697 698 699 700 701 702 703 704 705
 *
 * 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).
706
 * @return mixed an associative array, or false if no records were found or an error occurred.
Penny Leach's avatar
Penny Leach committed
707 708 709 710 711 712 713 714 715 716 717 718 719 720
 */
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.
721
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
722 723
 * @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.
724
 * @return mixed an associative array, or false if no records were found or an error occurred.
Penny Leach's avatar
Penny Leach committed
725 726 727 728 729 730 731 732 733 734 735 736 737
 */
function get_records_select_menu($table, $select='', $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.
738
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
739 740 741 742 743 744
 * @return mixed an associative array, or false if no records were found or an error occured.
 */
function get_records_sql_menu($sql,$values=null) {
    $rs = get_recordset_sql($sql,$values);
    return recordset_to_menu($rs);
}
745 746 747 748 749


//
// Field related data access - get_field*
//
Penny Leach's avatar
Penny Leach committed
750 751 752 753 754

/**
 * Get a single value from a table row where all the given fields match the given values.
 *
 * @param string $table the table to query.
755
 * @param string $field the field to return the value of.
Penny Leach's avatar
Penny Leach committed
756 757 758 759 760 761
 * @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).
762
 * @return mixed the specified value
763
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
764
 */
Martyn Smith's avatar
Martyn Smith committed
765
function get_field($table, $field, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
Penny Leach's avatar
Penny Leach committed
766 767
    $select = where_clause_prepared($field1, $field2, $field3);
    $values = where_values_prepared($value1, $value2, $value3);
Aaron Wells's avatar
Aaron Wells committed
768

769
    return get_field_sql('SELECT ' . $field . ' FROM ' . db_table_name($table) . ' ' . $select, $values);
Penny Leach's avatar
Penny Leach committed
770 771
}

772 773 774 775
/**
 * Get a single value from a table.
 *
 * @param string $sql an SQL statement expected to return a single value.
776
 * @param array $values When using prepared statements, this is the value array (optional).
777 778 779 780 781 782 783 784 785 786 787 788
 * @return mixed the specified value.
 * @throws SQLException
 */
function get_field_sql($sql, $values=null) {
    $rs = get_recordset_sql($sql, $values);
    if ($rs && $rs->RecordCount() == 1) {
        return reset($rs->fields);
    } else {
        return false;
    }
}

789 790 791 792 793

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

794 795 796 797
/**
 * Get a single column from a table where all the given fields match the given values.
 *
 * @param string $table the table to query.
798
 * @param string $field the field to return the value of.
799 800 801 802 803 804 805 806 807
 * @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
 */
808
function get_column($table, $field, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
809 810
    $select = where_clause_prepared($field1, $field2, $field3);
    $values = where_values_prepared($value1, $value2, $value3);
Aaron Wells's avatar
Aaron Wells committed
811

812
    return get_column_sql('SELECT ' . $field . ' FROM ' . db_table_name($table) . ' ' . $select, $values);
813 814 815 816 817 818
}

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

826 827
    $sql = db_quote_table_placeholders($sql);

828 829 830 831 832 833
    try {
        return $db->GetCol($sql, $values);
    }
    catch (ADODB_Exception $e) {
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
    }
834 835
}

836 837 838 839 840

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

Penny Leach's avatar
Penny Leach committed
841 842 843 844 845 846 847 848 849 850 851 852 853 854
/**
 * 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 mixed An ADODB RecordSet object with the results from the SQL call or false.
855
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
856
 */
857
function set_field($table, $newfield, $newvalue, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
858
    global $db;
Penny Leach's avatar
Penny Leach committed
859 860

    $select = where_clause_prepared($field1, $field2, $field3);
Penny Leach's avatar
Penny Leach committed
861 862 863 864
    $values = where_values_prepared($value1, $value2, $value3);

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

Penny Leach's avatar
Penny Leach committed
866 867 868
function set_field_select($table, $newfield, $newvalue, $select, $values) {
    global $db;

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

874 875
    $select = db_quote_table_placeholders($select);

Penny Leach's avatar
Penny Leach committed
876
    $values = array_merge(array($newvalue), $values);
877
    $sql = 'UPDATE '. db_table_name($table) .' SET '. db_quote_identifier($newfield)  .' = ? ' . $select;
878
    try {
879
        $stmt = $db->Prepare($sql);
880 881 882
        return $db->Execute($stmt, $values);
    }
    catch (ADODB_Exception $e) {
883
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
884
    }
Penny Leach's avatar
Penny Leach committed
885 886
}

887 888 889 890 891

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

Penny Leach's avatar
Penny Leach committed
892 893 894 895 896 897 898 899 900 901 902 903
/**
 * 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 mixed An ADODB RecordSet object with the results from the SQL call or false.
904
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
905 906
 */
function delete_records($table, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
907
    global $db;
Penny Leach's avatar
Penny Leach committed
908 909 910

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

912
    $sql = 'DELETE FROM '. db_table_name($table) . ' ' . $select;
913
    try {
914
        $stmt = $db->Prepare($sql);
915 916 917
        return $db->Execute($stmt,$values);
    }
    catch (ADODB_Exception $e) {
918
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
919
    }
Penny Leach's avatar
Penny Leach committed
920 921 922 923 924 925 926 927
}

/**
 * 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).
928
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
929
 * @return object A PHP standard object with the results from the SQL call.
930
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
931 932 933 934 935
 */
function delete_records_select($table, $select='',$values=null) {
    if ($select) {
        $select = 'WHERE '.$select;
    }
936
    return delete_records_sql('DELETE FROM '. db_table_name($table) .' '. $select, $values);
Penny Leach's avatar
Penny Leach committed
937 938
}

939
/**
Aaron Wells's avatar
Aaron Wells committed
940 941
 * @todo <nigel> This function does nothing delete specific. The functionality
 * it has with the $values parameter should be merged with the execute_sql
942 943
 * function
 */
Penny Leach's avatar
Penny Leach committed
944 945
function delete_records_sql($sql, $values=null) {
    global $db;
Penny Leach's avatar
Penny Leach committed
946

947 948
    $sql = db_quote_table_placeholders($sql);

949 950 951
    try {
        $result = false;
        if (!empty($values) && is_array($values) && count($values) > 0) {
Penny Leach's avatar
Penny Leach committed
952 953
            $stmt = $db->Prepare($sql);
            $result = $db->Execute($stmt, $values);
954
        } else {
Penny Leach's avatar
Penny Leach committed
955
            $result = $db->Execute($sql);
956 957 958
        }
    }
    catch (ADODB_Exception $e) {
959
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
Penny Leach's avatar
Penny Leach committed
960 961 962 963 964 965 966 967 968 969 970 971 972 973
    }
    return $result;
}

/**
 * Insert a record into a table and return the "id" field if required
 *
 * If the return ID isn't required, then this just reports success as true/false.
 * $dataobject is an object containing needed data
 *
 * @uses $db
 * @param string $table The database table to be checked against.
 * @param array $dataobject A data object with values for one or more fields in the record
 * @param string $primarykey The primary key of the table we are inserting into (almost always "id")
974
 * @param bool $returnpk Should the id of the newly created record entry be returned? If this option is not requested then true/false is returned.
975
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
976 977
 */
function insert_record($table, $dataobject, $primarykey=false, $returnpk=false) {
978
    // $INSERTRECORD_NOCACHE is yet another work around of dmllib/adodb's ineptitude.
Aaron Wells's avatar
Aaron Wells committed
979 980 981
    // It's all nice to cache the table columns lookup, but what if the table
    // columns change over the life of the page load? This happens when an
    // upgrade is running. All of a sudden, the table_column cache is out of
982
    // date and we can't insert new data properly.
Aaron Wells's avatar
Aaron Wells committed
983
    // Temporary solution: set INSERTRECORD_NOCACHE to true before your calls
984 985
    // that need a new lookup, and unset it afterwards
    global $db, $INSERTRECORD_NOCACHE;
Penny Leach's avatar
Penny Leach committed
986
    static $table_columns;
Aaron Wells's avatar
Aaron Wells committed
987

Penny Leach's avatar
Penny Leach committed
988
    // Determine all the fields in the table
989
    if (empty($INSERTRECORD_NOCACHE) && is_array($table_columns) && array_key_exists($table, $table_columns)) {
Penny Leach's avatar
Penny Leach committed
990
        $columns = $table_columns[$table];
991 992
    }
    else {
993
        if (!$columns = $db->MetaColumns(get_config('dbprefix') . $table)) {
994
            throw new SQLException('Table "' . get_config('dbprefix') . $table . '" does not appear to exist');
Penny Leach's avatar
Penny Leach committed
995 996 997
        }
        $table_columns[$table] = $columns;
    }
Aaron Wells's avatar
Aaron Wells committed
998

Penny Leach's avatar
Penny Leach committed