dml.php 59.5 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
    static $table_columns;