dml.php 60.3 KB
Newer Older
Martyn Smith's avatar
Martyn Smith committed
1
<?php
Penny Leach's avatar
Penny Leach committed
2
/**
Francois Marier's avatar
Francois Marier committed
3 4
 * Mahara: Electronic portfolio, weblog, resume builder and social networking
 * Copyright (C) 2006-2007 Catalyst IT Ltd (http://www.catalyst.net.nz)
Penny Leach's avatar
Penny Leach committed
5
 *
Francois Marier's avatar
Francois Marier committed
6 7 8 9
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
Penny Leach's avatar
Penny Leach committed
10
 *
Francois Marier's avatar
Francois Marier committed
11 12 13 14
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
Penny Leach's avatar
Penny Leach committed
15
 *
Francois Marier's avatar
Francois Marier committed
16 17
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
Penny Leach's avatar
Penny Leach committed
18 19
 *
 * @package    mahara
Penny Leach's avatar
Penny Leach committed
20 21
 * @subpackage core
 * @author     Martin Dougiamas <martin@moodle.com>
Penny Leach's avatar
Penny Leach committed
22 23 24 25 26
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL
 * @copyright  (C) 2001-3001 Martin Dougiamas http://dougiamas.com
 * @copyright  additional modifications (c) Catalyst IT Ltd http://catalyst.net.nz
 *
 */
Penny Leach's avatar
Penny Leach committed
27

Penny Leach's avatar
Penny Leach committed
28
defined('INTERNAL') || die();
Penny Leach's avatar
Penny Leach committed
29

30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
/**
 * Return a table name, properly prefixed and escaped
 *
 */
function db_table_name($name) {
    return db_quote_identifier(get_config('dbprefix') . $name);
}

/**
 * Searches through a query for strings looking like {name}, to replace with 
 * correctly quoted and prefixed table names
 *
 * @param string $sql The SQL to replace the placeholders in
 * @return string
 */
function db_quote_table_placeholders($sql) {
46
    return preg_replace_callback('/\{([a-z][a-z0-9_]+)\}/', create_function('$matches', 'return db_table_name($matches[1]);'), $sql);
47 48 49 50 51 52 53 54 55 56 57 58
}

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

65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85
/**
 * 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;
}

86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106
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;
}

107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123
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
124 125 126 127 128 129 130
/**
 * 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.
131
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
132
 * @return string
133
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
134
 */
135
function execute_sql($command, $values=null) {
136 137 138
    global $db;
    
    if (!is_a($db, 'ADOConnection')) {
139
        throw new SQLException('Database connection is not available ');
140
    }
Penny Leach's avatar
Penny Leach committed
141

142 143
    $command = db_quote_table_placeholders($command);

144 145
    // @todo need to do more research into this flag - what is it for, we
    // probably want to just turn it off because we can catch the exceptions
Penny Leach's avatar
Penny Leach committed
146
    $olddebug = $db->debug;
147
    $db->debug = false;
Penny Leach's avatar
Penny Leach committed
148

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

    $db->debug = $olddebug;
165
    return true;
Penny Leach's avatar
Penny Leach committed
166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183
}

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

Penny Leach's avatar
Penny Leach committed
192 193 194 195 196 197 198
/**
 * Test whether any records exists in a table which match a particular WHERE clause.
 *
 * This function returns true if at least one record is returned
 * 
 * @param string $table The database table to be checked against.
 * @param string $select A fragment of SQL to be used in a WHERE clause in the SQL call.
199
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
200 201 202 203 204 205 206 207 208 209 210
 * @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;
    }

211
    return record_exists_sql('SELECT * FROM '. db_table_name($table) . ' ' . $select, $values);
Penny Leach's avatar
Penny Leach committed
212 213
}

Penny Leach's avatar
Penny Leach committed
214 215 216
/**
 * Test whether a SQL SELECT statement returns any records.
 *
217
 * This function returns true if at least one record is returned.
Penny Leach's avatar
Penny Leach committed
218 219
 *
 * @param string $sql The SQL statement to be executed. If using $values, placeholder ?s are expected. If not, the string should be escaped correctly.
220
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
221
 * @return bool true if the SQL executes without errors and returns at least one record.
222
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
223
 */
224 225 226
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
227 228 229 230 231 232 233 234 235 236 237 238 239
}

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

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

/**
 * 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.
275
 * @param array $values When using prepared statements, this is the value array (optional).
276
 * @return int        The count.
277
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
278
 */
279 280 281
function count_records_sql($sql, $values=null) {
    $rs = get_recordset_sql($sql, $values);
    return reset($rs->fields);
Penny Leach's avatar
Penny Leach committed
282 283 284 285 286 287 288 289 290
}

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

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

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

    $recordcount = $rs->RecordCount();

    if ($recordcount == 0) {          // Found no records
        return false;
333 334
    }
    else if ($recordcount == 1) {    // Found one record 
Penny Leach's avatar
Penny Leach committed
335
       return (object)$rs->fields;
336 337
    }
    else {                          // Error: found more than one record
338
        throw new SQLException('get_record_sql found more than one row. If you meant to retrieve more '
339
            . 'than one record, use get_records_*, otherwise check your code or database for inconsistencies');
Penny Leach's avatar
Penny Leach committed
340 341 342 343 344 345 346 347
    }
}

/**
 * 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.
348
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
349
 * @param string $fields A comma separated list of fields to be returned from the chosen table.
350
 * @return object Returns an array of found records (as objects)
351
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
352 353 354 355 356
 */
function get_record_select($table, $select='', $values=null, $fields='*') {
    if ($select) {
        $select = 'WHERE '. $select;
    }
357
    return get_record_sql('SELECT '. $fields .' FROM ' . db_table_name($table) .' '. $select, $values);
Penny Leach's avatar
Penny Leach committed
358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373
}

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

    if ($limitfrom !== '') {
431
        $limit = 'LIMIT ' . intval($limitnum)  . ' OFFSET ' . intval($limitfrom);
Penny Leach's avatar
Penny Leach committed
432 433 434 435 436 437 438 439
    } else {
        $limit = '';
    }

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

440
    return get_recordset_sql('SELECT '. $fields .' FROM '. db_table_name($table) . $select . $sort .' '. $limit, $values);
Penny Leach's avatar
Penny Leach committed
441 442 443 444 445
}

/**
 * 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
446
 * from general Moodle scripts.  Use get_record, get_records_* etc.
Penny Leach's avatar
Penny Leach committed
447 448 449 450 451
 *
 * The return type is as for @see function get_recordset.
 *
 * @uses $db
 * @param string $sql the SQL select query to execute.
452
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
453 454
 * @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).
455
 * @return mixed an ADODB RecordSet object
456
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
457 458
 */
function get_recordset_sql($sql, $values=null, $limitfrom=null, $limitnum=null) {
459
    global $db;
Penny Leach's avatar
Penny Leach committed
460

461
    if (!is_a($db, 'ADOConnection')) {
462
        throw new SQLException('Database connection is not available ');
Penny Leach's avatar
Penny Leach committed
463 464
    }

465 466
    $sql = db_quote_table_placeholders($sql);

467 468 469 470 471
    try {
        if ($limitfrom || $limitnum) {
            ///Special case, 0 must be -1 for ADOdb
            $limitfrom = empty($limitfrom) ? -1 : $limitfrom;
            $limitnum  = empty($limitnum) ? -1 : $limitnum;
472
            $rs = $db->SelectLimit($sql, $limitnum, $limitfrom, $values);
Penny Leach's avatar
Penny Leach committed
473
        } else {
474 475 476 477 478 479 480
            $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
481 482
        }
    }
483
    catch (ADODB_Exception $e) {
484
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
Penny Leach's avatar
Penny Leach committed
485 486 487 488 489 490
    }
 
   return $rs;
}

/**
491
 * Utility function to turn a result set into an array of records
Penny Leach's avatar
Penny Leach committed
492 493
 *
 * @param object an ADODB RecordSet object.
494
 * @return mixed an array of objects, or false if the RecordSet was empty.
495
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
496 497
 */
function recordset_to_array($rs) {
498
    if ($rs && $rs->RecordCount() > 0) {
499 500 501 502
        $array = $rs->GetArray();
        foreach ($array as &$a) {
            $a = (object)$a;
        }
503
        return $array;
504 505 506 507 508 509
    }
    else {
        return false;
    }
}

510 511 512 513 514

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

515 516 517 518 519
/**
 * 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)
 *
520
 * @param  object $rs An ADODB RecordSet object.
521
 * @return mixed  An array of objects, or false if the RecordSet was empty.
522
 * @throws SQLException
523
 * @access private
524 525
 */
function recordset_to_assoc($rs) {
Penny Leach's avatar
Penny Leach committed
526
    if ($rs && $rs->RecordCount() > 0) {
527 528 529
        // 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
530
        $firstcolumn = $rs->FetchField(0);
531
        // Get the whole associative array
Penny Leach's avatar
Penny Leach committed
532 533
        if ($records = $rs->GetAssoc(true)) {
            foreach ($records as $key => $record) {
534 535
                $record[$firstcolumn->name] = $key;
                $objects[$key] = (object) $record; 
Penny Leach's avatar
Penny Leach committed
536 537 538 539 540 541 542 543 544 545 546
            }
            return $objects;
        } else {
            return false;
        }
    } else {
        return false;
    }
}

/**
547 548
 * 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
549
 * column in the result set. As a result, you may lose some rows! Please use
550
 * {@link get_records_*_array} instead where possible)
Penny Leach's avatar
Penny Leach committed
551 552 553 554 555 556 557 558 559 560 561 562 563 564
 *
 * 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).
565
 * @return mixed an array of objects, or false if no records were found.
566
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
567
 */
568
function get_records_assoc($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
Penny Leach's avatar
Penny Leach committed
569
    $rs = get_recordset($table, $field, $value, $sort, $fields, $limitfrom, $limitnum);
570
    return recordset_to_assoc($rs);
Penny Leach's avatar
Penny Leach committed
571 572 573 574 575
}

/**
 * Get a number of records as an array of objects.
 *
576 577 578 579 580 581 582 583 584 585 586 587 588 589
 * 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.
590
 * @throws SQLException
591
 */
592
function get_records_array($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
593 594 595 596 597 598 599
    $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.
 *
600
 * Return value as for @see function get_records_assoc
Penny Leach's avatar
Penny Leach committed
601 602 603
 *
 * @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.
604
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
605 606 607 608
 * @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).
609
 * @return mixed an array of objects, or false if no records were found.
610
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
611
 */
612
function get_records_select_assoc($table, $select='', $values=null, $sort='', $fields='*', $limitfrom='', $limitnum='') {
Penny Leach's avatar
Penny Leach committed
613
    $rs = get_recordset_select($table, $select, $values, $sort, $fields, $limitfrom, $limitnum);
614
    return recordset_to_assoc($rs);
Penny Leach's avatar
Penny Leach committed
615 616 617 618 619
}

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

/**
 * Get a number of records as an array of objects.
 *
657
 * Return value as for {@link get_records_array}
658 659
 *
 * @param string $sql the SQL select query to execute.
660
 * @param array $values When using prepared statements, this is the value array.
661 662 663
 * @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.
664
 * @throws SQLException
665
 */
666
function get_records_sql_array($sql,$values, $limitfrom='', $limitnum='') {
Penny Leach's avatar
Penny Leach committed
667 668 669 670
    $rs = get_recordset_sql($sql, $values, $limitfrom, $limitnum);
    return recordset_to_array($rs);
}

671 672 673 674 675

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

Penny Leach's avatar
Penny Leach committed
676 677 678
/**
 * Utility function used by the following 3 methods.
 *
679
 * @param object $rs an ADODB RecordSet object with two columns.
Penny Leach's avatar
Penny Leach committed
680
 * @return mixed an associative array, or false if an error occured or the RecordSet was empty.
681
 * @access private
Penny Leach's avatar
Penny Leach committed
682 683 684 685 686 687
 */
function recordset_to_menu($rs) {
    global $CFG;

    if ($rs && $rs->RecordCount() > 0) {
        $keys = array_keys($rs->fields);
688 689 690 691 692 693 694
        $key0 = $keys[0];
        if (isset($keys[1])) {
            $key1 = $keys[1];
        }
        else {
            $key1 = $keys[0];
        }
Penny Leach's avatar
Penny Leach committed
695 696 697 698 699 700 701 702 703
        while (!$rs->EOF) {
            $menu[$rs->fields[$key0]] = $rs->fields[$key1];
            $rs->MoveNext();
        }
        return $menu;
    } else {
        return false;
    }
}
704

Penny Leach's avatar
Penny Leach committed
705 706 707
/**
 * Get the first two columns from a number of records as an associative array.
 *
708
 * Arguments as for {@link get_recordset}.
Penny Leach's avatar
Penny Leach committed
709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734
 *
 * If no errors occur, and at least one records is found, the return value
 * is an associative whose keys come from the first field of each record,
 * and whose values are the corresponding second fields. If no records are found,
 * or an error occurs, false is returned.
 *
 * @param string $table the table to query.
 * @param string $field a field to check (optional).
 * @param string $value the value the field must have (requred if field1 is given, else optional).
 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
 * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
 * @return mixed an associative array, or false if no records were found or an error occured.
 */
function get_records_menu($table, $field='', $value='', $sort='', $fields='*') {
    $rs = get_recordset($table, $field, $value, $sort, $fields);
    return recordset_to_menu($rs);
}

/**
 * Get the first two columns from a number of records as an associative array.
 *
 * Arguments as for @see function get_recordset_select.
 * Return value as for @see function get_records_menu.
 *
 * @param string $table The database table to be checked against.
 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
735
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751
 * @param string $sort Sort order (optional) - a valid SQL order parameter
 * @param string $fields A comma separated list of fields to be returned from the chosen table.
 * @return mixed an associative array, or false if no records were found or an error occured.
 */
function get_records_select_menu($table, $select='', $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.
752
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
753 754 755 756 757 758
 * @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);
}
759 760 761 762 763


//
// Field related data access - get_field*
//
Penny Leach's avatar
Penny Leach committed
764 765 766 767 768

/**
 * Get a single value from a table row where all the given fields match the given values.
 *
 * @param string $table the table to query.
769
 * @param string $field the field to return the value of.
Penny Leach's avatar
Penny Leach committed
770 771 772 773 774 775
 * @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).
776
 * @return mixed the specified value
777
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
778
 */
Martyn Smith's avatar
Martyn Smith committed
779
function get_field($table, $field, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
Penny Leach's avatar
Penny Leach committed
780 781
    $select = where_clause_prepared($field1, $field2, $field3);
    $values = where_values_prepared($value1, $value2, $value3);
782
    
783
    return get_field_sql('SELECT ' . $field . ' FROM ' . db_table_name($table) . ' ' . $select, $values);
Penny Leach's avatar
Penny Leach committed
784 785
}

786 787 788 789
/**
 * Get a single value from a table.
 *
 * @param string $sql an SQL statement expected to return a single value.
790
 * @param array $values When using prepared statements, this is the value array (optional).
791 792 793 794 795 796 797 798 799 800 801 802
 * @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;
    }
}

803 804 805 806 807

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

808 809 810 811
/**
 * Get a single column from a table where all the given fields match the given values.
 *
 * @param string $table the table to query.
812
 * @param string $field the field to return the value of.
813 814 815 816 817 818 819 820 821
 * @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
 */
822
function get_column($table, $field, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
823 824 825
    $select = where_clause_prepared($field1, $field2, $field3);
    $values = where_values_prepared($value1, $value2, $value3);
    
826
    return get_column_sql('SELECT ' . $field . ' FROM ' . db_table_name($table) . ' ' . $select, $values);
827 828 829 830 831 832
}

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

840 841
    $sql = db_quote_table_placeholders($sql);

842 843 844 845 846 847
    try {
        return $db->GetCol($sql, $values);
    }
    catch (ADODB_Exception $e) {
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
    }
848 849
}

850 851 852 853 854

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

Penny Leach's avatar
Penny Leach committed
855 856 857 858 859 860 861 862 863 864 865 866 867 868
/**
 * 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.
869
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
870
 */
871
function set_field($table, $newfield, $newvalue, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
872
    global $db;
Penny Leach's avatar
Penny Leach committed
873 874

    $select = where_clause_prepared($field1, $field2, $field3);
Penny Leach's avatar
Penny Leach committed
875 876 877 878
    $values = where_values_prepared($value1, $value2, $value3);

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

Penny Leach's avatar
Penny Leach committed
880 881 882
function set_field_select($table, $newfield, $newvalue, $select, $values) {
    global $db;

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

888 889
    $select = db_quote_table_placeholders($select);

Penny Leach's avatar
Penny Leach committed
890
    $values = array_merge(array($newvalue), $values);
891
    $sql = 'UPDATE '. db_table_name($table) .' SET '. db_quote_identifier($newfield)  .' = ? ' . $select;
892
    try {
893
        $stmt = $db->Prepare($sql);
894 895 896
        return $db->Execute($stmt, $values);
    }
    catch (ADODB_Exception $e) {
897
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
898
    }
Penny Leach's avatar
Penny Leach committed
899 900
}

901 902 903 904 905

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

Penny Leach's avatar
Penny Leach committed
906 907 908 909 910 911 912 913 914 915 916 917
/**
 * 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.
918
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
919 920
 */
function delete_records($table, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
921
    global $db;
Penny Leach's avatar
Penny Leach committed
922 923 924

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

926
    $sql = 'DELETE FROM '. db_table_name($table) . ' ' . $select;
927
    try {
928
        $stmt = $db->Prepare($sql);
929 930 931
        return $db->Execute($stmt,$values);
    }
    catch (ADODB_Exception $e) {
932
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
933
    }
Penny Leach's avatar
Penny Leach committed
934 935 936 937 938 939 940 941
}

/**
 * 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).
942
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
943
 * @return object A PHP standard object with the results from the SQL call.
944
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
945 946 947 948 949
 */
function delete_records_select($table, $select='',$values=null) {
    if ($select) {
        $select = 'WHERE '.$select;
    }
950
    return delete_records_sql('DELETE FROM '. db_table_name($table) .' '. $select, $values);
Penny Leach's avatar
Penny Leach committed
951 952
}

953 954 955 956 957
/**
 * @todo <nigel> This function does nothing delete specific. The functionality 
 * it has with the $values parameter should be merged with the execute_sql 
 * function
 */
Penny Leach's avatar
Penny Leach committed
958 959
function delete_records_sql($sql, $values=null) {
    global $db;
Penny Leach's avatar
Penny Leach committed
960

961 962
    $sql = db_quote_table_placeholders($sql);

963 964 965
    try {
        $result = false;
        if (!empty($values) && is_array($values) && count($values) > 0) {
Penny Leach's avatar
Penny Leach committed
966 967
            $stmt = $db->Prepare($sql);
            $result = $db->Execute($stmt, $values);
968
        } else {
Penny Leach's avatar
Penny Leach committed
969
            $result = $db->Execute($sql);
970 971 972
        }
    }
    catch (ADODB_Exception $e) {
973
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
Penny Leach's avatar
Penny Leach committed
974 975 976 977 978 979 980 981 982 983 984 985 986 987
    }
    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")
988
 * @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.
989
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
990 991
 */
function insert_record($table, $dataobject, $primarykey=false, $returnpk=false) {
992 993 994 995 996 997 998 999
    // $INSERTRECORD_NOCACHE is yet another work around of dmllib/adodb's ineptitude.
    // 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 
    // date and we can't insert new data properly.
    // Temporary solution: set INSERTRECORD_NOCACHE to true before your calls 
    // that need a new lookup, and unset it afterwards
    global $db, $INSERTRECORD_NOCACHE;
Penny Leach's avatar
Penny Leach committed
1000 1001 1002
    static $table_columns;
    
    // Determine all the fields in the table
1003
    if (empty($INSERTRECORD_NOCACHE) && is_array($table_columns) && array_key_exists($table, $table_columns)) {
Penny Leach's avatar
Penny Leach committed
1004
        $columns = $table_columns[$table];
1005 1006
    }
    else {
1007
        if (!$columns = $db->MetaColumns(get_config('dbprefix') . $table)) {
1008
            throw new SQLException('Table "' . get_config('dbprefix') . $table . '" does not appear to exist');
Penny Leach's avatar
Penny Leach committed
1009 1010 1011 1012 1013 1014
        }
        $table_columns[$table] = $columns;
    }
    
    if (!empty($primarykey)) {
        unset($dataobject->{$primarykey});
1015
        if (!empty($returnpk) && is_postgres()) {
1016
            $pksql = "SELECT NEXTVAL('" . get_config('dbprefix') . "{$table}_{$primarykey}_seq')";
Penny Leach's avatar
Penny Leach committed
1017 1018 1019 1020 1021 1022 1023 1024 1025 1026
            if ($nextval = (int)get_field_sql($pksql)) {
                $setfromseq = true;
                $dataobject->{$primarykey} = $nextval;
            }
        }
    }

    $data = (array)$dataobject;

  // Pull out data matching these fields
1027
    $ddd = array();
Penny Leach's avatar
Penny Leach committed
1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039
    foreach ($columns as $column)