dml.php 59.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
318
    $limitfrom = 0;
    $limitnum  = 2;
Penny Leach's avatar
Penny Leach committed
319
320
321
322
323
324
325
326
327

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

    $recordcount = $rs->RecordCount();

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

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

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

    if ($limitfrom !== '') {
426
        $limit = 'LIMIT ' . intval($limitnum)  . ' OFFSET ' . intval($limitfrom);
Penny Leach's avatar
Penny Leach committed
427
428
429
430
431
432
433
434
    } else {
        $limit = '';
    }

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

435
    return get_recordset_sql('SELECT '. $fields .' FROM '. db_table_name($table) . $select . $sort .' '. $limit, $values);
Penny Leach's avatar
Penny Leach committed
436
437
438
439
440
}

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

456
    if (!is_a($db, 'ADOConnection')) {
457
        throw new SQLException('Database connection is not available ');
Penny Leach's avatar
Penny Leach committed
458
459
    }

460
461
    $sql = db_quote_table_placeholders($sql);

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

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

505
506
507
508
509

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

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

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

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

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

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

666
667
668
669
670

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

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

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

Penny Leach's avatar
Penny Leach committed
700
701
702
/**
 * Get the first two columns from a number of records as an associative array.
 *
703
 * Arguments as for {@link get_recordset}.
Penny Leach's avatar
Penny Leach committed
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
 *
 * 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.
730
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
 * @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.
747
 * @param array $values When using prepared statements, this is the value array (optional).
Penny Leach's avatar
Penny Leach committed
748
749
750
751
752
753
 * @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);
}
754
755
756
757
758


//
// Field related data access - get_field*
//
Penny Leach's avatar
Penny Leach committed
759
760
761
762
763

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

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

798
799
800
801
802

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

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

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

835
836
    $sql = db_quote_table_placeholders($sql);

837
838
839
840
841
842
    try {
        return $db->GetCol($sql, $values);
    }
    catch (ADODB_Exception $e) {
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
    }
843
844
}

845
846
847
848
849

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

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

    $select = where_clause_prepared($field1, $field2, $field3);
Penny Leach's avatar
Penny Leach committed
870
871
872
873
    $values = where_values_prepared($value1, $value2, $value3);

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

Penny Leach's avatar
Penny Leach committed
875
876
877
function set_field_select($table, $newfield, $newvalue, $select, $values) {
    global $db;

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

883
884
    $select = db_quote_table_placeholders($select);

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

896
897
898
899
900

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

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

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

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

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

948
949
950
951
952
/**
 * @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
953
954
function delete_records_sql($sql, $values=null) {
    global $db;
Penny Leach's avatar
Penny Leach committed
955

956
957
    $sql = db_quote_table_placeholders($sql);

958
959
960
    try {
        $result = false;
        if (!empty($values) && is_array($values) && count($values) > 0) {
Penny Leach's avatar
Penny Leach committed
961
962
            $stmt = $db->Prepare($sql);
            $result = $db->Execute($stmt, $values);
963
        } else {
Penny Leach's avatar
Penny Leach committed
964
            $result = $db->Execute($sql);
965
966
967
        }
    }
    catch (ADODB_Exception $e) {
968
        throw new SQLException(create_sql_exception_message($e, $sql, $values));
Penny Leach's avatar
Penny Leach committed
969
970
971
972
973
974
975
976
977
978
979
980
981
982
    }
    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")
983
 * @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.
984
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
985
986
 */
function insert_record($table, $dataobject, $primarykey=false, $returnpk=false) {
987
988
989
990
991
992
993
994
    // $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
995
996
997
    static $table_columns;
    
    // Determine all the fields in the table
998
    if (empty($INSERTRECORD_NOCACHE) && is_array($table_columns) && array_key_exists($table, $table_columns)) {
Penny Leach's avatar
Penny Leach committed
999
        $columns = $table_columns[$table];
1000
1001
    }
    else {
1002
        if (!$columns = $db->MetaColumns(get_config('dbprefix') . $table)) {
1003
            throw new SQLException('Table "' . get_config('dbprefix') . $table . '" does not appear to exist');
Penny Leach's avatar
Penny Leach committed
1004
1005
1006
1007
1008
1009
        }
        $table_columns[$table] = $columns;
    }
    
    if (!empty($primarykey)) {
        unset($dataobject->{$primarykey});
1010
        if (!empty($returnpk) && is_postgres()) {
1011
            $pksql = "SELECT NEXTVAL('" . get_config('dbprefix') . "{$table}_{$primarykey}_seq')";
Penny Leach's avatar
Penny Leach committed
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
            if ($nextval = (int)get_field_sql($pksql)) {
                $setfromseq = true;
                $dataobject->{$primarykey} = $nextval;
            }
        }
    }

    $data = (array)$dataobject;

  // Pull out data matching these fields
1022
    $ddd = array();
Penny Leach's avatar
Penny Leach committed
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
    foreach ($columns as $column) {
        if (isset($data[$column->name])) {
            if ($column->name == $primarykey && empty($setfromseq)) {
                continue;
            }
            $ddd[$column->name] = $data[$column->name];
        }
    }

    // Construct SQL queries
    $numddd = count($ddd);
    $count = 0;
1035
    $insertSQL = 'INSERT INTO '. db_table_name($table) .' (';
Penny Leach's avatar
Penny Leach committed
1036
1037
1038
1039
    $fields = '';
    $values = '';
    foreach ($ddd as $key => $value) {
        $count++;
1040
        $fields .= '"' . $key . '"';
Penny Leach's avatar
Penny Leach committed
1041
1042
1043
1044
1045
1046
1047
1048
        $values .= '?';
        if ($count < $numddd) {
            $fields .= ', ';
            $values .= ', ';
        }
    }
    $insertSQL .= $fields.') VALUES ('.$values.')';

1049
1050
1051
1052
1053
1054
    // Run the SQL statement
    try {
        $stmt = $db->Prepare($insertSQL);
        $rs = $db->Execute($stmt,$ddd);
    }
    catch (ADODB_Exception $e) {
1055
        throw new SQLException(create_sql_exception_message($e, $insertSQL, $ddd));
Penny Leach's avatar
Penny Leach committed
1056
1057
    }

1058
    // If a return ID is not needed then just return true now
Penny Leach's avatar
Penny Leach committed
1059
1060
1061
1062
    if (empty($returnpk)) {
        return true;
    }

1063
1064
    // We already know the record PK if it's been passed explicitly,
    // or if we've retrieved it from a sequence (Postgres).
Penny Leach's avatar
Penny Leach committed
1065
1066
1067
1068
    if (!empty($dataobject->{$primarykey})) {
        return $dataobject->{$primarykey};
    }

1069
1070
1071
    // This only gets triggered with non-Postgres databases
    // however we have some postgres fallback in case we failed 
    // to find the sequence.
Penny Leach's avatar
Penny Leach committed
1072
1073
1074
1075
    $id = $db->Insert_ID();  

    if (is_postgres()) {
        // try to get the primary key based on id
1076
        try {
1077
            $oidsql = 'SELECT ' . $primarykey . ' FROM '. db_table_name($table) . ' WHERE oid = ' . $id;
1078
1079
1080
1081
            $rs = $db->Execute($oidsql);
            if ($rs->RecordCount() == 1) {
                return (integer)$rs->fields[0];
            }
1082
            throw new SQLException('WTF: somehow got more than one record when searching for a primary key');
1083
1084
        }
        catch (ADODB_Exception $e) {
1085
1086
            throw new SQLException("Trying to get pk from oid failed: " 
                                   . $e->getMessage() . " sql was $oidsql");
1087
        }
Penny Leach's avatar
Penny Leach committed
1088
1089
1090
1091
1092
    }

    return (integer)$id;
}

1093
/**
1094
1095
 * Inserts a record, only if the record does not already exist. 
 * If the record DOES exist, it is updated.
1096
1097
1098
 *
 * @uses $db
 * @param string $table The database table to be checked against.
1099
1100
 * @param array $whereobject A data object with values for one or more fields in the record (to determine whether the record exists or not)
 * @param array $dataobject A data object with values for one or more fields in the record (to be inserted or updated)
1101
1102
1103
1104
 * @param string $primarykey The primary key of the table we are inserting into (almost always "id")
 * @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.
 * @throws SQLException
 */
1105
1106
1107
1108
1109
1110
1111
1112
1113
function ensure_record_exists($table, $whereobject, $dataobject, $primarykey=false, $returnpk=false) {
    $columns = (array)$whereobject;
    $field = '*';
    $where = array();
    $toreturn = false;

    foreach ($columns as $key => $value) {
        if ($field == '*') {
            $field = $key;
1114
1115
        }

1116
1117
1118
1119
        $where[] = db_quote_identifier($key) . ' = ' . db_quote($value);
    }

    $where = implode(' AND ', $where);
1120

1121
1122
    if (is_postgres()) {
        $where .= ' FOR UPDATE ';
1123
1124
    }
    else {
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
        // @TODO maybe some mysql specific stuff here
    }
        
    db_begin();
    if ($exists = get_record_select($table, $where)) {
        if ($returnpk) {
            $toreturn = $exists->{$primarykey};
        }
        else {
            $toreturn = true;
1135
        }
1136
        if ($dataobject && $dataobject != $whereobject) { // we want to update it)
1137
1138
1139
1140
1141
            update_record($table, $dataobject, $whereobject);
        }
    }
    else {
        $toreturn = insert_record($table, $dataobject, $primarykey, $returnpk);
1142
    }
1143
1144
    db_commit();
    return $toreturn;
1145
1146
}

Penny Leach's avatar
Penny Leach committed
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
/**
 * Update a record in a table
 *
 * $dataobject is an object containing needed data
 * Relies on $dataobject having a variable "id" to
 * specify the record to update
 *
 * @uses $db
 * @param string $table The database table to be checked against.
 * @param array $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
1157
1158
 * @param mixed $where defines the WHERE part of the upgrade. Can be string (key) or array (keys) or hash (keys/values).
 * If the first two, values are expected to be in $dataobject. 
Penny Leach's avatar
Penny Leach committed
1159
 * @return bool
1160
 * @throws SQLException
Penny Leach's avatar
Penny Leach committed
1161
 */
1162
function update_record($table, $dataobject, $where=null) {
Penny Leach's avatar
Penny Leach committed
1163

1164
    global $db;
Penny Leach's avatar
Penny Leach committed
1165

1166
1167
1168
    if (is_object($dataobject)) {
        $dataobject = clone $dataobject;
    }
1169

1170
1171
1172
1173
1174
1175
    if (empty($where)) {
        $where = 'id';
        if (!isset($dataobject->id) ) { 
            // nothing to put in the where clause and we don't want to update everything
            throw new SQLException('update_record called with no where clause and no ID');
        }
Penny Leach's avatar
Penny Leach committed
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
    }

    $wherefields = array();
    $wherevalues = array();
    $values = array();

    if (is_string($where)) { 
        // treat it like a stack (ie, field in dataobject)
        $where = array($where);
    }

    if (is_object($where) || is_hash($where)) {
        // the values are contained in the where ...
        foreach ((array)$where as $field => $value) {
            $wherefields[] = $field;
            $wherevalues[] = $value;
            unset(