lib/datalib.php

   1 <?php
   2
   3 /**
   4  * Library of functions for database manipulation.
   5  * This library is most of lib/datalib.php from moodle
   6  * http://moodle.org || http://sourceforge.net/projects/moodle
   7  * Copyright (C) 2001-2003  Martin Dougiamas  http://dougiamas.com
   8  * @author Martin Dougiamas and many others
   9  * @license http://www.gnu.org/copyleft/gpl.html GNU Public License
  10  */
  11
  12
  13 /// FUNCTIONS FOR DATABASE HANDLING  ////////////////////////////////
  14
  15 /**
  16  * Execute a given sql command string
  17  *
  18  * Completely general function - it just runs some SQL and reports success.
  19  *
  20  * @uses $db
  21  * @param string $command The sql string you wish to be executed.
  22  * @param bool $feedback Set this argument to true if the results generated should be printed. Default is true.
  23  * @return string
  24  */
  25 function execute_sql($command, $feedback=true) {
  26 /// Completely general function - it just runs some SQL and reports success.
  27
  28     global $db, $CFG;
  29
  30     $olddebug = $db->debug;
  31
  32     if (!$feedback) {
  33         $db->debug = false;
  34     }
  35
  36     if (defined('ELGG_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
  37
  38     $result = $db->Execute($command);
  39
  40     $db->debug = $olddebug;
  41
  42     if ($result) {
  43         if ($feedback) {
  44             notify(gettext('Success'), 'notifysuccess');
  45         }
  46         return true;
  47     } else {
  48         if ($feedback) {
  49             echo '<p><span class="error">'. gettext('Error') .'</span></p>';
  50         }
  51         if (!empty($CFG->dblogerror)) {
  52             $debug = debug_backtrace();
  53             foreach ($debug as $d) {
  54                 if (strpos($d['file'],'datalib') === false) {
  55                     error_log("SQL ".$db->ErrorMsg()." in {$d['file']} on line {$d['line']}. STATEMENT:  $command");
  56                     break;
  57                 }
  58             }
  59         }
  60         return false;
  61     }
  62 }
  63 /**
  64 * on DBs that support it, switch to transaction mode and begin a transaction
  65 * you'll need to ensure you call commit_sql() or your changes *will* be lost
  66 * this is _very_ useful for massive updates
  67 */
  68 function begin_sql() {
  69 /// Completely general function - it just runs some SQL and reports success.
  70
  71     global $CFG;
  72     if ($CFG->dbtype === 'postgres7') {
  73         return execute_sql('BEGIN', false);
  74     }
  75     return true;
  76 }
  77 /**
  78 * on DBs that support it, commit the transaction
  79 */
  80 function rollback_sql() {
  81 /// Completely general function - it just runs some SQL and reports success.
  82
  83     global $CFG;
  84     if ($CFG->dbtype === 'postgres7') {
  85         return execute_sql('ROLLBACK', false);
  86     }
  87     return true;
  88 }
  89
  90
  91
  92 /**
  93  * returns db specific uppercase function
  94  */
  95 function db_uppercase() {
  96     global $CFG;
  97     switch (strtolower($CFG->dbtype)) {
  98
  99     case "postgres7":
 100         return "upper";
 101
 102     case "mysql":
 103     default:
 104         return "ucase";
 105
 106     }
 107 }
 108
 109 /**
 110  * returns db specific lowercase function
 111  */
 112 function db_lowercase() {
 113     global $CFG;
 114     switch (strtolower($CFG->dbtype)) {
 115
 116     case "postgres7":
 117         return "lower";
 118
 119     case "mysql":
 120     default:
 121         return "lcase";
 122
 123     }
 124 }
 125
 126 /**
 127 * on DBs that support it, commit the transaction
 128 */
 129 function commit_sql() {
 130 /// Completely general function - it just runs some SQL and reports success.
 131
 132     global $CFG;
 133     if ($CFG->dbtype === 'postgres7') {
 134         return execute_sql('COMMIT', false);
 135     }
 136     return true;
 137 }
 138
 139 /**
 140  * Run an arbitrary sequence of semicolon-delimited SQL commands
 141  *
 142  * Assumes that the input text (file or string) consists of
 143  * a number of SQL statements ENDING WITH SEMICOLONS.  The
 144  * semicolons MUST be the last character in a line.
 145  * Lines that are blank or that start with "#" or "--" (postgres) are ignored.
 146  * Only tested with mysql dump files (mysqldump -p -d moodle)
 147  *
 148  * @uses $CFG
 149  * @param string $sqlfile The path where a file with sql commands can be found on the server.
 150  * @param string $sqlstring If no path is supplied then a string with semicolon delimited sql
 151  * commands can be supplied in this argument.
 152  * @return bool Returns true if databse was modified successfully.
 153  */
 154 function modify_database($sqlfile='', $sqlstring='') {
 155
 156     global $CFG;
 157
 158     $success = true;  // Let's be optimistic
 159
 160     if (!empty($sqlfile)) {
 161         if (!is_readable($sqlfile)) {
 162             $success = false;
 163             echo '<p>Tried to modify database, but "'. $sqlfile .'" doesn\'t exist!</p>';
 164             return $success;
 165         } else {
 166             $lines = file($sqlfile);
 167         }
 168     } else {
 169         $sqlstring = trim($sqlstring);
 170         if ($sqlstring{strlen($sqlstring)-1} != ";") {
 171             $sqlstring .= ";"; // add it in if it's not there.
 172         }
 173         $lines[] = $sqlstring;
 174     }
 175
 176     $command = '';
 177
 178     foreach ($lines as $line) {
 179         $line = rtrim($line);
 180         $length = strlen($line);
 181
 182         if ($length and $line[0] <> '#' and $line[0].$line[1] <> '--') {
 183             if (substr($line, $length-1, 1) == ';') {
 184                 $line = substr($line, 0, $length-1);   // strip ;
 185                 $command .= $line;
 186                 $command = str_replace('prefix_', $CFG->prefix, $command); // Table prefixes
 187                 if (! execute_sql($command)) {
 188                     $success = false;
 189                 }
 190                 $command = '';
 191             } else {
 192                 $command .= $line;
 193             }
 194         }
 195     }
 196
 197     return $success;
 198
 199 }
 200
 201 /// FUNCTIONS TO MODIFY TABLES ////////////////////////////////////////////
 202
 203 /**
 204  * Add a new field to a table, or modify an existing one (if oldfield is defined).
 205  *
 206  * @uses $CFG
 207  * @uses $db
 208  * @param string $table ?
 209  * @param string $oldfield ?
 210  * @param string $field ?
 211  * @param string $type ?
 212  * @param string $size ?
 213  * @param string $signed ?
 214  * @param string $default ?
 215  * @param string $null ?
 216  * @todo Finish documenting this function
 217  */
 218
 219 function table_column($table, $oldfield, $field, $type='integer', $size='10',
 220                       $signed='unsigned', $default='0', $null='not null', $after='') {
 221     global $CFG, $db;
 222
 223     if (empty($oldfield) && !empty($field)) { //adding
 224         // check it doesn't exist first.
 225         if ($columns = $db->MetaColumns($CFG->prefix . $table)) {
 226             foreach ($columns as $c) {
 227                 if ($c->name == $field) {
 228                     $oldfield = $field;
 229                 }
 230             }
 231         }
 232     }
 233
 234     switch (strtolower($CFG->dbtype)) {
 235
 236         case 'mysql':
 237         case 'mysqlt':
 238
 239             switch (strtolower($type)) {
 240                 case 'text':
 241                     $type = 'TEXT';
 242                     $signed = '';
 243                     break;
 244                 case 'integer':
 245                     $type = 'INTEGER('. $size .')';
 246                     break;
 247                 case 'varchar':
 248                     $type = 'VARCHAR('. $size .')';
 249                     $signed = '';
 250                     break;
 251                 case 'char':
 252                     $type = 'CHAR('. $size .')';
 253                     $signed = '';
 254                     break;
 255             }
 256
 257             if (!empty($oldfield)) {
 258                 $operation = 'CHANGE '. $oldfield .' '. $field;
 259             } else {
 260                 $operation = 'ADD '. $field;
 261             }
 262
 263             $default = 'DEFAULT \''. $default .'\'';
 264
 265             if (!empty($after)) {
 266                 $after = 'AFTER `'. $after .'`';
 267             }
 268
 269             return execute_sql('ALTER TABLE '. $CFG->prefix . $table .' '. $operation .' '. $type .' '. $signed .' '. $default .' '. $null .' '. $after);
 270
 271         case 'postgres7':        // From Petri Asikainen
 272             //Check db-version
 273             $dbinfo = $db->ServerInfo();
 274             $dbver = substr($dbinfo['version'],0,3);
 275
 276             //to prevent conflicts with reserved words
 277             $realfield = '"'. $field .'"';
 278             $field = '"'. $field .'_alter_column_tmp"';
 279             $oldfield = '"'. $oldfield .'"';
 280
 281             switch (strtolower($type)) {
 282                 case 'tinyint':
 283                 case 'integer':
 284                     if ($size <= 4) {
 285                         $type = 'INT2';
 286                     }
 287                     if ($size <= 10) {
 288                         $type = 'INT';
 289                     }
 290                     if  ($size > 10) {
 291                         $type = 'INT8';
 292                     }
 293                     break;
 294                 case 'varchar':
 295                     $type = 'VARCHAR('. $size .')';
 296                     break;
 297                 case 'char':
 298                     $type = 'CHAR('. $size .')';
 299                     $signed = '';
 300                     break;
 301             }
 302
 303             $default = '\''. $default .'\'';
 304
 305             //After is not implemented in postgesql
 306             //if (!empty($after)) {
 307             //    $after = "AFTER '$after'";
 308             //}
 309
 310             //Use transactions
 311             execute_sql('BEGIN');
 312
 313             //Always use temporary column
 314             execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ADD COLUMN '. $field .' '. $type);
 315             //Add default values
 316             execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .'='. $default);
 317
 318
 319             if ($dbver >= '7.3') {
 320                 // modifying 'not null' is posible before 7.3
 321                 //update default values to table
 322                 if (strtoupper($null) == 'NOT NULL') {
 323                     execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .'='. $default .' WHERE '. $field .' IS NULL');
 324                     execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $null);
 325                 } else {
 326                     execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' DROP NOT NULL');
 327                 }
 328             }
 329
 330             execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET DEFAULT '. $default);
 331
 332             if ( $oldfield != '""' ) {
 333
 334                 // We are changing the type of a column. This may require doing some casts...
 335                 $casting = '';
 336                 $oldtype = column_type($table, $oldfield);
 337                 $newtype = column_type($table, $field);
 338
 339                 // Do we need a cast?
 340                 if($newtype == 'N' && $oldtype == 'C') {
 341                     $casting = 'CAST(CAST('.$oldfield.' AS TEXT) AS REAL)';
 342                 }
 343                 else if($newtype == 'I' && $oldtype == 'C') {
 344                     $casting = 'CAST(CAST('.$oldfield.' AS TEXT) AS INTEGER)';
 345                 }
 346                 else {
 347                     $casting = $oldfield;
 348                 }
 349
 350                 // Run the update query, casting as necessary
 351                 execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .' = '. $casting);
 352                 execute_sql('ALTER TABLE  '. $CFG->prefix . $table .' DROP COLUMN '. $oldfield);
 353             }
 354
 355             execute_sql('ALTER TABLE '. $CFG->prefix . $table .' RENAME COLUMN '. $field .' TO '. $realfield);
 356
 357             return execute_sql('COMMIT');
 358
 359         default:
 360             switch (strtolower($type)) {
 361                 case 'integer':
 362                     $type = 'INTEGER';
 363                     break;
 364                 case 'varchar':
 365                     $type = 'VARCHAR';
 366                     break;
 367             }
 368
 369             $default = 'DEFAULT \''. $default .'\'';
 370
 371             if (!empty($after)) {
 372                 $after = 'AFTER '. $after;
 373             }
 374
 375             if (!empty($oldfield)) {
 376                 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' RENAME COLUMN '. $oldfield .' '. $field);
 377             } else {
 378                 execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ADD COLUMN '. $field .' '. $type);
 379             }
 380
 381             execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $null);
 382             return execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $default);
 383     }
 384 }
 385
 386 /**
 387  * Get the data type of a table column, using an ADOdb MetaType() call.
 388  *
 389  * @uses $CFG
 390  * @uses $db
 391  * @param string $table The name of the database table
 392  * @param string $column The name of the field in the table
 393  * @return string Field type or false if error
 394  */
 395
 396 function column_type($table, $column) {
 397     global $CFG, $db;
 398
 399     if (defined('ELGG_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
 400
 401     if(!$rs = $db->Execute('SELECT '.$column.' FROM '.$CFG->prefix.$table.' LIMIT 0')) {
 402         return false;
 403     }
 404
 405     $field = $rs->FetchField(0);
 406     return $rs->MetaType($field->type);
 407 }
 408
 409
 410 /// GENERIC FUNCTIONS TO CHECK AND COUNT RECORDS ////////////////////////////////////////
 411
 412 /**
 413  * Test whether a record exists in a table where all the given fields match the given values.
 414  *
 415  * The record to test is specified by giving up to three fields that must
 416  * equal the corresponding values.
 417  *
 418  * @uses $CFG
 419  * @param string $table The table to check.
 420  * @param string $field1 the first field to check (optional).
 421  * @param string $value1 the value field1 must have (requred if field1 is given, else optional).
 422  * @param string $field2 the second field to check (optional).
 423  * @param string $value2 the value field2 must have (requred if field2 is given, else optional).
 424  * @param string $field3 the third field to check (optional).
 425  * @param string $value3 the value field3 must have (requred if field3 is given, else optional).
 426  * @return bool true if a matching record exists, else false.
 427  */
 428 function record_exists($table, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
 429
 430     global $CFG;
 431
 432     $select = where_clause_prepared($field1, $field2, $field3);
 433
 434     $values = where_values_prepared($value1, $value2, $value3);
 435
 436     return record_exists_sql('SELECT * FROM '. $CFG->prefix . $table .' '. $select .' LIMIT 1',$values);
 437
 438 }
 439
 440
 441 /**
 442 * Determine whether a specified record exists.
 443 *
 444 * This function returns true if the SQL executed returns records.
 445 *
 446 * @uses $CFG
 447 * @uses $db
 448 * @param string $sql The SQL statement to be executed. If using $values, placeholder ?s are expected. If not, the string should be escaped correctly.
 449 * @param array $values When using prepared statements, this is the value array. Optional.
 450 * @return bool
 451 */
 452 function record_exists_sql($sql,$values=null) {
 453
 454     global $CFG, $db;
 455
 456     if (defined('ELGG_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
 457
 458     $rs = false;
 459     if (!empty($values) && is_array($values) && count($values) > 0) {
 460         $stmt = $db->Prepare($sql);
 461         $rs = $db->Execute($stmt,$values);
 462     } else {
 463         $rs = $db->Execute($sql);
 464     }
 465
 466     if (empty($rs)) {
 467         if (isset($CFG->debug) and $CFG->debug > 7) {
 468             notify($db->ErrorMsg().'<br /><br />'.$sql);
 469         }
 470         if (!empty($CFG->dblogerror)) {
 471             $debug = debug_backtrace();
 472             foreach ($debug as $d) {
 473                 if (strpos($d['file'],'datalib') === false) {
 474                     error_log("SQL ".$db->ErrorMsg()." in {$d['file']} on line {$d['line']}. STATEMENT:  $sql");
 475                     break;
 476                 }
 477             }
 478         }
 479         return false;
 480     }
 481
 482     if ( $rs->RecordCount() ) {
 483         return true;
 484     } else {
 485         return false;
 486     }
 487     }
 488
 489
 490 /**
 491  * Count the records in a table where all the given fields match the given values.
 492  *
 493  * @uses $CFG
 494  * @param string $table The table to query.
 495  * @param string $field1 the first field to check (optional).
 496  * @param string $value1 the value field1 must have (requred if field1 is given, else optional).
 497  * @param string $field2 the second field to check (optional).
 498  * @param string $value2 the value field2 must have (requred if field2 is given, else optional).
 499  * @param string $field3 the third field to check (optional).
 500  * @param string $value3 the value field3 must have (requred if field3 is given, else optional).
 501  * @return int The count of records returned from the specified criteria.
 502  */
 503 function count_records($table, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
 504
 505     global $CFG;
 506
 507     $select = where_clause_prepared($field1, $field2, $field3);
 508
 509     $values = where_values_prepared($value1, $value2, $value3);
 510
 511     return count_records_sql('SELECT COUNT(*) FROM '. $CFG->prefix . $table .' '. $select, $values);
 512 }
 513
 514 /**
 515  * Get all the records and count them
 516  *
 517  * @uses $CFG
 518  * @param string $table The database table to be checked against.
 519  * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
 520  * @param array $values if using a prepared statement with placeholders in $select, pass values here. optional
 521  * @param string $countitem The count string to be used in the SQL call. Default is COUNT(*).
 522  * @return int The count of records returned from the specified criteria.
 523  */
 524 function count_records_select($table, $select='', $values=null, $countitem='COUNT(*)') {
 525
 526     global $CFG;
 527
 528     if ($select) {
 529         $select = 'WHERE '.$select;
 530     }
 531
 532     return count_records_sql('SELECT '. $countitem .' FROM '. $CFG->prefix . $table .' '. $select, $values);
 533 }
 534
 535
 536 /**
 537  * Get all the records returned from the specified SQL call and return the count of them
 538  *
 539  * @uses $CFG
 540  * @uses $db
 541  * @param string $sql The SQL string you wish to be executed.
 542  * @return int The count of records returned from the specified SQL string.
 543  */
 544 function count_records_sql($sql, $values=null) {
 545
 546     global $CFG, $db;
 547
 548     if (defined('ELGG_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
 549
 550     $rs = false;
 551     if (!empty($values) && is_array($values) && count($values) > 0) {
 552         $stmt = $db->Prepare($sql);
 553         $rs = $db->Execute($stmt,$values);
 554     } else {
 555         $rs = $db->Execute($sql);
 556     }
 557     if (!$rs) {
 558         if (isset($CFG->debug) and $CFG->debug > 7) {
 559             notify($db->ErrorMsg() .'<br /><br />'. $sql);
 560         }
 561         if (!empty($CFG->dblogerror)) {
 562             $debug = debug_backtrace();
 563             foreach ($debug as $d) {
 564                 if (strpos($d['file'],'datalib') === false) {
 565                     error_log("SQL ".$db->ErrorMsg()." in {$d['file']} on line {$d['line']}. STATEMENT:  $sql");
 566                     break;
 567                 }
 568             }
 569         }
 570         return 0;
 571     }
 572
 573     return $rs->fields[0];
 574 }
 575
 576
 577
 578
 579 /// GENERIC FUNCTIONS TO GET, INSERT, OR UPDATE DATA  ///////////////////////////////////
 580
 581 /**
 582  * Get a single record as an object
 583  *
 584  * @uses $CFG
 585  * @param string $table The table to select from.
 586  * @param string $field1 the first field to check (optional).
 587  * @param string $value1 the value field1 must have (requred if field1 is given, else optional).
 588  * @param string $field2 the second field to check (optional).
 589  * @param string $value2 the value field2 must have (requred if field2 is given, else optional).
 590  * @param string $field3 the third field to check (optional).
 591  * @param string $value3 the value field3 must have (requred if field3 is given, else optional).
 592  * @return mixed a fieldset object containing the first mathcing record, or false if none found.
 593  */
 594 function get_record($table, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null, $fields='*') {
 595
 596     global $CFG;
 597
 598     $select = where_clause_prepared($field1, $field2, $field3);
 599
 600     $values = where_values_prepared($value1, $value2, $value3);
 601
 602     return get_record_sql('SELECT '.$fields.' FROM '. $CFG->prefix . $table .' '. $select, $values);
 603 }
 604
 605 /**
 606  * Get a single record as an object using the specified SQL statement
 607  *
 608  * A LIMIT is normally added to only look for 1 record
 609  * If debugging is OFF only the first record is returned even if there is
 610  * more than one matching record!
 611  *
 612  * @uses $CFG
 613  * @uses $db
 614  * @param string $sql The SQL string you wish to be executed.
 615  * @param array $values If using placeholder ?s in the $sql, pass values here.
 616  * @return Found record as object. False if not found or error
 617  */
 618 function get_record_sql($sql, $values=null, $expectmultiple=false, $nolimit=false) {
 619
 620     global $db, $CFG;
 621
 622     if (isset($CFG->debug) && $CFG->debug > 7 && !$expectmultiple) {    // Debugging mode - don't use limit
 623        $limit = '';
 624     } else if ($nolimit) {
 625        $limit = '';
 626     } else {
 627        $limit = ' LIMIT 1';    // Workaround - limit to one record
 628     }
 629
 630     if (defined('ELGG_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
 631
 632     $rs = false;
 633     if (!empty($values) && is_array($values) && count($values) > 0) {
 634         $stmt = $db->Prepare($sql. $limit);
 635         $rs = $db->Execute($stmt, $values);
 636     } else {
 637         $rs = $db->Execute($sql . $limit);
 638     }
 639     if (!$rs) {
 640         if (isset($CFG->debug) and $CFG->debug > 7) {    // Debugging mode - print checks
 641             notify( $db->ErrorMsg() . '<br /><br />'. $sql . $limit );
 642         }
 643         if (!empty($CFG->dblogerror)) {
 644             $debug = debug_backtrace();
 645             foreach ($debug as $d) {
 646                 if (strpos($d['file'],'datalib') === false) {
 647                     error_log("SQL ".$db->ErrorMsg()." in {$d['file']} on line {$d['line']}. STATEMENT:  $sql$limit");
 648                     break;
 649                 }
 650             }
 651         }
 652         return false;
 653     }
 654
 655     if (!$recordcount = $rs->RecordCount()) {
 656         return false;                 // Found no records
 657     }
 658
 659     if ($recordcount == 1) {          // Found one record
 660         return (object)$rs->fields;
 661
 662     } else {                          // Error: found more than one record
 663         notify('Error:  Turn off debugging to hide this error.');
 664         notify($sql . $limit);
 665         if ($records = $rs->GetAssoc(true)) {
 666             notify('Found more than one record in get_record_sql !');
 667             print_object($records);
 668         } else {
 669             notify('Very strange error in get_record_sql !');
 670             print_object($rs);
 671         }
 672         print_continue("$CFG->wwwroot/$CFG->admin/config.php");
 673     }
 674 }
 675
 676 /**
 677  * Gets one record from a table, as an object
 678  *
 679  * @uses $CFG
 680  * @param string $table The database table to be checked against.
 681  * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
 682  * @param array $values If using placeholder ? in $select, pass values here.
 683  * @param string $fields A comma separated list of fields to be returned from the chosen table.
 684  * @return object|false Returns an array of found records (as objects) or false if no records or error occured.
 685  */
 686 function get_record_select($table, $select='', $values=null, $fields='*') {
 687
 688     global $CFG;
 689
 690     if ($select) {
 691         $select = 'WHERE '. $select;
 692     }
 693
 694     return get_record_sql('SELECT '. $fields .' FROM '. $CFG->prefix . $table .' '. $select, $values);
 695 }
 696
 697 /**
 698  * Get a number of records as an ADODB RecordSet.
 699  *
 700  * Selects records from the table $table.
 701  *
 702  * If specified, only records where the field $field has value $value are retured.
 703  *
 704  * If specified, the results will be sorted as specified by $sort. This
 705  * is added to the SQL as "ORDER BY $sort". Example values of $sort
 706  * mightbe "time ASC" or "time DESC".
 707  *
 708  * If $fields is specified, only those fields are returned.
 709  * Use this wherever possible to reduce memory requirements.
 710  *
 711  * If you only want some of the records, specify $limitfrom and $limitnum.
 712  * The query will skip the first $limitfrom records (according to the sort
 713  * order) and then return the next $limitnum records. If either of $limitfrom
 714  * or $limitnum is specified, both must be present.
 715  *
 716  * The return value is an ADODB RecordSet object
 717  * @link http://phplens.com/adodb/reference.functions.adorecordset.html
 718  * if the query succeeds. If an error occurrs, false is returned.
 719  *
 720  * @param string $table the table to query.
 721  * @param string $field a field to check (optional).
 722  * @param string $value the value the field must have (requred if field1 is given, else optional).
 723  * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
 724  * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
 725  * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 726  * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 727  * @return mixed an ADODB RecordSet object, or false if an error occured.
 728  */
 729 function get_recordset($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
 730
 731     $values = null;
 732     if ($field) {
 733         $select = "$field = ?";
 734         $values = array($value);
 735     } else {
 736         $select = '';
 737     }
 738
 739     return get_recordset_select($table, $select, $values, $sort, $fields, $limitfrom, $limitnum);
 740 }
 741
 742 /**
 743  * Get a number of records as an ADODB RecordSet.
 744  *
 745  * If given, $select is used as the SELECT parameter in the SQL query,
 746  * otherwise all records from the table are returned.
 747  *
 748  * Other arguments and the return type as for @see function get_recordset.
 749  *
 750  * @uses $CFG
 751  * @param string $table the table to query.
 752  * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
 753  * @param array $values If using placeholder ?s in $select, pass values here.
 754  * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
 755  * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
 756  * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 757  * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 758  * @return mixed an ADODB RecordSet object, or false if an error occured.
 759  */
 760 function get_recordset_select($table, $select='', $values=null, $sort='', $fields='*', $limitfrom='', $limitnum='') {
 761
 762     global $CFG;
 763
 764     if ($select) {
 765         $select = ' WHERE '. $select;
 766     }
 767
 768     if ($limitfrom !== '') {
 769         $limit = sql_paging_limit($limitfrom, $limitnum);
 770     } else {
 771         $limit = '';
 772     }
 773
 774     if ($sort) {
 775         $sort = ' ORDER BY '. $sort;
 776     }
 777
 778     return get_recordset_sql('SELECT '. $fields .' FROM '. $CFG->prefix . $table . $select . $sort .' '. $limit, $values);
 779 }
 780
 781 /**
 782  * Get a number of records as an ADODB RecordSet.
 783  *
 784  * Only records where $field takes one of the values $values are returned.
 785  * $values should be a comma-separated list of values, for example "4,5,6,10"
 786  * or "'foo','bar','baz'".
 787  *
 788  * Other arguments and the return type as for @see function get_recordset.
 789  *
 790  * @param string $table the table to query.
 791  * @param string $field a field to check (optional).
 792  * @param array $values the value the field must have (requred if field1 is given, else optional).
 793  * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
 794  * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
 795  * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 796  * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 797  * @return mixed an ADODB RecordSet object, or false if an error occured.
 798  */
 799 function get_recordset_list($table, $field='', $values=null, $sort='', $fields='*', $limitfrom='', $limitnum='') {
 800
 801     global $CFG;
 802
 803     if (!empty($field) && is_array($values) && count($values) > 0) {
 804         $placeholder = array();
 805         for ($i = 0; $i < count($values); $i++) {
 806             $placeholder[] = '?';
 807         }
 808         $select = "$field IN (".implode(',',$placeholder).")";
 809     } else {
 810         $select = '';
 811     }
 812
 813     get_recordset_select($table, $select, $values, $sort, $fields, $limitfrom, $limitnum);
 814 }
 815
 816 /**
 817  * Get a number of records as an ADODB RecordSet.
 818  *
 819  * $sql must be a complete SQL query.
 820  *
 821  * The return type is as for @see function get_recordset.
 822  *
 823  * @uses $CFG
 824  * @uses $db
 825  * @param string $sql the SQL select query to execute.
 826  * @return mixed an ADODB RecordSet object, or false if an error occured.
 827  */
 828 function get_recordset_sql($sql,$values=null) {
 829
 830     global $CFG, $db;
 831
 832     if (defined('ELGG_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
 833
 834     $rs = false;
 835     if (!empty($values) && is_array($values) && count($values) > 0) {
 836         $stmt = $db->Prepare($sql);
 837         $rs = $db->Execute($stmt,$values);
 838     } else {
 839         $rs = $db->Execute($sql);
 840     }
 841     if (!$rs) {
 842         if (isset($CFG->debug) and $CFG->debug > 7) {
 843             notify($db->ErrorMsg() .'<br /><br />'. $sql);
 844         }
 845         if (!empty($CFG->dblogerror)) {
 846             $debug = debug_backtrace();
 847             foreach ($debug as $d) {
 848                 if (strpos($d['file'],'datalib') === false) {
 849                     error_log("SQL ".$db->ErrorMsg()." in {$d['file']} on line {$d['line']}. STATEMENT:  $sql");
 850                     break;
 851                 }
 852             }
 853         }
 854         return false;
 855     }
 856
 857     return $rs;
 858 }
 859
 860 /**
 861  * Utility function used by the following 4 methods.
 862  *
 863  * @param object an ADODB RecordSet object.
 864  * @return mixed mixed an array of objects, or false if an error occured or the RecordSet was empty.
 865  */
 866 function recordset_to_array($rs) {
 867     if ($rs && $rs->RecordCount() > 0) {
 868         if ($records = $rs->GetAssoc(true)) {
 869             foreach ($records as $key => $record) {
 870                 $objects[$key] = (object) $record;
 871             }
 872             return $objects;
 873         } else {
 874             return false;
 875         }
 876     } else {
 877         return false;
 878     }
 879 }
 880
 881 /**
 882  * Get a number of records as an array of objects.
 883  *
 884  * @deprecated try to use @see function get_recordset instead.
 885  *
 886  * Arguments as for @see function get_recordset.
 887  *
 888  * If the query succeeds and returns at least one record, the
 889  * return value is an array of objects, one object for each
 890  * record found. The array key is the value from the first
 891  * column of the result set. The object associated with that key
 892  * has a member variable for each column of the results.
 893  *
 894  * @param string $table the table to query.
 895  * @param string $field a field to check (optional).
 896  * @param string $value the value the field must have (requred if field1 is given, else optional).
 897  * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
 898  * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
 899  * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 900  * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 901  * @return mixed an array of objects, or false if no records were found or an error occured.
 902  */
 903 function get_records($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') {
 904     $rs = get_recordset($table, $field, $value, $sort, $fields, $limitfrom, $limitnum);
 905     return recordset_to_array($rs);
 906 }
 907
 908 /**
 909  * Get a number of records as an array of objects.
 910  *
 911  * @deprecated try to use @see function get_recordset_select instead.
 912  *
 913  * Arguments as for @see function get_recordset_select.
 914  * Return value as for @see function get_records.
 915  *
 916  * @param string $table the table to query.
 917  * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
 918  * @param array $values if using placeholder ? in $select, pass values here.
 919  * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
 920  * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
 921  * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 922  * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 923  * @return mixed an array of objects, or false if no records were found or an error occured.
 924  */
 925 function get_records_select($table, $select='', $values=null, $sort='', $fields='*', $limitfrom='', $limitnum='') {
 926     $rs = get_recordset_select($table, $select, $values, $sort, $fields, $limitfrom, $limitnum);
 927     return recordset_to_array($rs);
 928 }
 929
 930 /**
 931  * Get a number of records as an array of objects.
 932  *
 933  * @deprecated try to use @see function get_recordset_list instead.
 934  *
 935  * Arguments as for @see function get_recordset_list.
 936  * Return value as for @see function get_records.
 937  *
 938  * @param string $table The database table to be checked against.
 939  * @param string $field The field to search
 940  * @param array $values Array of possible values
 941  * @param string $sort Sort order (as valid SQL sort parameter)
 942  * @param string $fields A comma separated list of fields to be returned from the chosen table.
 943  * @return mixed an array of objects, or false if no records were found or an error occured.
 944  */
 945 function get_records_list($table, $field='', $values=null, $sort='', $fields='*', $limitfrom='', $limitnum='') {
 946     $rs = get_recordset_list($table, $field, $values, $sort, $fields, $limitfrom, $limitnum);
 947     return recordset_to_array($rs);
 948 }
 949
 950 /**
 951  * Get a number of records as an array of objects.
 952  *
 953  * @deprecated try to use @see function get_recordset_list instead.
 954  *
 955  * Arguments as for @see function get_recordset_sql.
 956  * Return value as for @see function get_records.
 957  *
 958  * @param string $sql the SQL select query to execute.
 959  * @return mixed an array of objects, or false if no records were found or an error occured.
 960  */
 961 function get_records_sql($sql,$values=null) {
 962     $rs = get_recordset_sql($sql,$values);
 963     return recordset_to_array($rs);
 964 }
 965
 966 /**
 967  * Utility function used by the following 3 methods.
 968  *
 969  * @param object an ADODB RecordSet object with two columns.
 970  * @return mixed an associative array, or false if an error occured or the RecordSet was empty.
 971  */
 972 function recordset_to_menu($rs) {
 973     if ($rs && $rs->RecordCount() > 0) {
 974         while (!$rs->EOF) {
 975             $menu[$rs->fields[0]] = $rs->fields[1];
 976             $rs->MoveNext();
 977         }
 978         return $menu;
 979     } else {
 980         return false;
 981     }
 982 }
 983
 984 /**
 985  * Get the first two columns from a number of records as an associative array.
 986  *
 987  * Arguments as for @see function get_recordset.
 988  *
 989  * If no errors occur, and at least one records is found, the return value
 990  * is an associative whose keys come from the first field of each record,
 991  * and whose values are the corresponding second fields. If no records are found,
 992  * or an error occurs, false is returned.
 993  *
 994  * @param string $table the table to query.
 995  * @param string $field a field to check (optional).
 996  * @param string $value the value the field must have (requred if field1 is given, else optional).
 997  * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
 998  * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
 999  * @return mixed an associative array, or false if no records were found or an error occured.
1000  */
1001 function get_records_menu($table, $field='', $value='', $sort='', $fields='*') {
1002     $rs = get_recordset($table, $field, $value, $sort, $fields);
1003     return recordset_to_menu($rs);
1004 }
1005
1006 /**
1007  * Get the first two columns from a number of records as an associative array.
1008  *
1009  * Arguments as for @see function get_recordset_select.
1010  * Return value as for @see function get_records_menu.
1011  *
1012  * @param string $table The database table to be checked against.
1013  * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1014  * @param string $sort Sort order (optional) - a valid SQL order parameter
1015  * @param string $fields A comma separated list of fields to be returned from the chosen table.
1016  * @return mixed an associative array, or false if no records were found or an error occured.
1017  */
1018 function get_records_select_menu($table, $select='', $values=null, $sort='', $fields='*') {
1019     $rs = get_recordset_select($table, $select, $values, $sort, $fields);
1020     return recordset_to_menu($rs);
1021 }
1022
1023 /**
1024  * Get the first two columns from a number of records as an associative array.
1025  *
1026  * Arguments as for @see function get_recordset_sql.
1027  * Return value as for @see function get_records_menu.
1028  *
1029  * @param string $sql The SQL string you wish to be executed.
1030  * @return mixed an associative array, or false if no records were found or an error occured.
1031  */
1032 function get_records_sql_menu($sql,$values=null) {
1033     $rs = get_recordset_sql($sql,$values);
1034     return recordset_to_menu($rs);
1035 }
1036
1037 /**
1038  * Get a single value from a table row where all the given fields match the given values.
1039  *
1040  * @uses $CFG
1041  * @uses $db
1042  * @param string $table the table to query.
1043  * @param string $return the field to return the value of.
1044  * @param string $field1 the first field to check (optional).
1045  * @param string $value1 the value field1 must have (requred if field1 is given, else optional).
1046  * @param string $field2 the second field to check (optional).
1047  * @param string $value2 the value field2 must have (requred if field2 is given, else optional).
1048  * @param string $field3 the third field to check (optional).
1049  * @param string $value3 the value field3 must have (requred if field3 is given, else optional).
1050  * @return mixed the specified value, or false if an error occured.
1051  */
1052 function get_field($table, $return, $field1, $value1, $field2=null, $value2=null, $field3=null, $value3=null) {
1053
1054     global $db, $CFG;
1055
1056     $select = where_clause_prepared($field1, $field2, $field3);
1057
1058     $values = where_values_prepared($value1, $value2, $value3);
1059
1060     if (defined('ELGG_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
1061
1062     // this always generates a where query, so there must always be values to look up
1063     if (count($values)) {
1064         $stmt = $db->Prepare('SELECT '. $return .' FROM '. $CFG->prefix . $table .' '. $select);
1065         $rs = $db->Execute($stmt,$values);
1066         if (!$rs) {
1067             if (isset($CFG->debug) and $CFG->debug > 7) {
1068                 notify($db->ErrorMsg() .'<br /><br />SELECT '. $return .' FROM '. $CFG->prefix . $table .' '. $select);
1069             }
1070             if (!empty($CFG->dblogerror)) {
1071                 $debug = debug_backtrace();
1072                 foreach ($debug as $d) {
1073                     if (strpos($d['file'],'datalib') === false) {
1074                         error_log("SQL ".$db->ErrorMsg()." in {$d['file']} on line {$d['line']}. STATEMENT:  SELECT $return FROM $CFG->prefix$table $select");
1075                         break;
1076                     }
1077                 }
1078             }
1079             return false;
1080         }
1081
1082         if ( $rs->RecordCount() == 1 ) {
1083             return $rs->fields[$return];
1084         } else {
1085             return false;
1086         }
1087     } else {
1088         return false;
1089     }
1090 }
1091
1092
1093 /**
1094  * Get a single field from a database record
1095  *
1096  * @uses $CFG
1097  * @uses $db
1098  * @param string $sql The SQL string you wish to be executed.
1099  * @return mixed|false Returns the value return from the SQL statment or false if an error occured.
1100  * @todo Finish documenting this function
1101  */
1102 function get_field_sql($sql, $values=null) {
1103
1104     global $db, $CFG;
1105
1106     if (defined('ELGG_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
1107
1108     $rs = false;
1109     if (!empty($values) && is_array($values) && count($values) > 0) {
1110         $stmt = $db->Prepare($sql);
1111         $rs = $db->Execute($stmt,$values);
1112     } else {
1113         $rs = $db->Execute($sql);
1114     }
1115     if (!$rs) {
1116         if (isset($CFG->debug) and $CFG->debug > 7) {
1117             notify($db->ErrorMsg() .'<br /><br />'. $sql);
1118         }
1119         if (!empty($CFG->dblogerror)) {
1120             $debug = debug_backtrace();
1121             foreach ($debug as $d) {
1122                 if (strpos($d['file'],'datalib') === false) {
1123                     error_log("SQL ".$db->ErrorMsg()." in {$d['file']} on line {$d['line']}. STATEMENT:  $sql");
1124                     break;
1125                 }
1126             }
1127         }
1128         return false;
1129     }
1130
1131     if ( $rs->RecordCount() == 1 ) {
1132         return $rs->fields[0];
1133     } else {
1134         return false;
1135     }
1136 }
1137
1138 /**
1139  * Set a single field in the table row where all the given fields match the given values.
1140  *
1141  * @uses $CFG
1142  * @uses $db
1143  * @param string $table The database table to be checked against.
1144  * @param string $newfield the field to set.
1145  * @param string $newvalue the value to set the field to.
1146  * @param string $field1 the first field to check (optional).
1147  * @param string $value1 the value field1 must have (requred if field1 is given, else optional).
1148  * @param string $field2 the second field to check (optional).
1149  * @param string $value2 the value field2 must have (requred if field2 is given, else optional).
1150  * @param string $field3 the third field to check (optional).
1151  * @param string $value3 the value field3 must have (requred if field3 is given, else optional).
1152  * @return mixed An ADODB RecordSet object with the results from the SQL call or false.
1153  */
1154 function set_field($table, $newfield, $newvalue, $field1, $value1, $field2=null, $value2=null, $field3=null, $value3=null) {
1155
1156     global $db, $CFG;
1157
1158     if (defined('ELGG_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
1159
1160     $select = where_clause_prepared($field1, $field2, $field3);
1161     $values = where_values_prepared($newvalue, $value1, $value2, $value3);
1162
1163     $stmt = $db->Prepare('UPDATE '. $CFG->prefix . $table .' SET '. $newfield  .' = ? '. $select);
1164     return $db->Execute($stmt,$values);
1165 }
1166
1167 /**
1168  * Delete the records from a table where all the given fields match the given values.
1169  *
1170  * @uses $CFG
1171  * @uses $db
1172  * @param string $table the table to delete from.
1173  * @param string $field1 the first field to check (optional).
1174  * @param string $value1 the value field1 must have (requred if field1 is given, else optional).
1175  * @param string $field2 the second field to check (optional).
1176  * @param string $value2 the value field2 must have (requred if field2 is given, else optional).
1177  * @param string $field3 the third field to check (optional).
1178  * @param string $value3 the value field3 must have (requred if field3 is given, else optional).
1179  * @return mixed An ADODB RecordSet object with the results from the SQL call or false.
1180  */
1181 function delete_records($table, $field1=null, $value1=null, $field2=null, $value2=null, $field3=null, $value3=null) {
1182
1183     global $db, $CFG;
1184
1185     if (defined('ELGG_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
1186
1187     $select = where_clause_prepared($field1, $field2, $field3);
1188     $values = where_values_prepared($value1, $value2, $value3);
1189
1190     $stmt = $db->Prepare('DELETE FROM '. $CFG->prefix . $table .' '. $select);
1191     return $db->Execute($stmt,$values);
1192 }
1193
1194 /**
1195  * Delete one or more records from a table
1196  *
1197  * @uses $CFG
1198  * @uses $db
1199  * @param string $table The database table to be checked against.
1200  * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
1201  * @return object A PHP standard object with the results from the SQL call.
1202  * @todo Verify return type.
1203  */
1204 function delete_records_select($table, $select='',$values=null) {
1205
1206     global $CFG, $db;
1207
1208     if (defined('ELGG_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
1209
1210     if ($select) {
1211         $select = 'WHERE '.$select;
1212     }
1213
1214     $result = false;
1215     if (!empty($values) && is_array($values) && count($values) > 0) {
1216         $stmt = $db->Prepare('DELETE FROM '. $CFG->prefix . $table .' '. $select);
1217         $result = $db->Execute($stmt,$values);
1218     } else {
1219         $result = $db->Execute('DELETE FROM '. $CFG->prefix . $table .' '. $select);
1220     }
1221     return $result;
1222 }
1223
1224 /**
1225  * Insert a record into a table and return the "ident" field if required
1226  *
1227  * If the return ID isn't required, then this just reports success as true/false.
1228  * $dataobject is an object containing needed data
1229  *
1230  * @uses $db
1231  * @uses $CFG
1232  * @param string $table The database table to be checked against.
1233  * @param array $dataobject A data object with values for one or more fields in the record
1234  * @param bool $returnid Should the id of the newly created record entry be returned? If this option is not requested then true/false is returned.
1235  * @param string $primarykey The primary key of the table we are inserting into (almost always "ident")
1236  */
1237 function insert_record($table, $dataobject, $returnid=true, $primarykey='ident') {
1238
1239     global $db, $CFG;
1240     static $table_columns;
1241
1242     // Determine all the fields in the table
1243     if (is_array($table_columns) && array_key_exists($table,$table_columns)) {
1244         $columns = $table_columns[$table];
1245     } else {
1246         if (!$columns = $db->MetaColumns($CFG->prefix . $table)) {
1247             return false;
1248         }
1249         $table_columns[$table] = $columns;
1250     }
1251
1252
1253     if (defined('ELGG_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
1254
1255     /// Postgres doesn't have the concept of primary key built in
1256     /// and will return the OID which isn't what we want.
1257     /// The efficient and transaction-safe strategy is to
1258     /// move the sequence forward first, and make the insert
1259     /// with an explicit id.
1260     if ( empty($dataobject->{$primarykey})
1261          && $CFG->dbtype === 'postgres7'
1262          && $returnid == true ) {
1263         if ($nextval = (int)get_field_sql("SELECT NEXTVAL('{$CFG->prefix}{$table}_{$primarykey}_seq')")) {
1264             $setfromseq = true;
1265             $dataobject->{$primarykey} = $nextval;
1266         }
1267     }
1268
1269     $data = (array)$dataobject;
1270
1271   // Pull out data matching these fields
1272     foreach ($columns as $column) {
1273         if ($column->name <> 'ident' and isset($data[$column->name]) ) {
1274             $ddd[$column->name] = $data[$column->name];
1275         }
1276     }
1277
1278     if (!empty($setfromseq)) {
1279         $ddd['ident'] = $dataobject->ident;
1280     }
1281
1282     // Construct SQL queries
1283     $numddd = count($ddd);
1284     $count = 0;
1285     $insertSQL = 'INSERT INTO '.$CFG->prefix . $table .' (';
1286     $fields = '';
1287     $values = '';
1288     foreach ($ddd as $key => $value) {
1289         $count++;
1290         $fields .= $key;
1291         $values .= '?';
1292         if ($count < $numddd) {
1293             $fields .= ', ';
1294             $values .= ', ';
1295         }
1296     }
1297     $insertSQL .= $fields.') VALUES ('.$values.')';
1298
1299     /// Run the SQL statement
1300     $stmt = $db->Prepare($insertSQL);
1301     if (!$rs = $db->Execute($stmt,$ddd)) {
1302         if (isset($CFG->debug) and $CFG->debug > 7) {
1303             notify($db->ErrorMsg() .'<br /><br />'.$insertSQL);
1304         }
1305         if (!empty($CFG->dblogerror)) {
1306             $debug = debug_backtrace();
1307             foreach ($debug as $d) {
1308                 if (strpos($d['file'],'datalib') === false) {
1309                     error_log("SQL ".$db->ErrorMsg()." in {$d['file']} on line {$d['line']}. STATEMENT:  $insertSQL");
1310                     break;
1311                 }
1312             }
1313         }
1314         return false;
1315     }
1316
1317 /// If a return ID is not needed then just return true now
1318     if (!$returnid) {
1319         return true;
1320     }
1321
1322 /// We already know the record PK if it's been passed explicitly,
1323 /// or if we've retrieved it from a sequence (Postgres).
1324     if (!empty($dataobject->{$primarykey})) {
1325         return $dataobject->{$primarykey};
1326     }
1327
1328 /// This only gets triggered with non-Postgres databases
1329 /// however we have some postgres fallback in case we failed
1330 /// to find the sequence.
1331     $id = $db->Insert_ID();
1332
1333     if ($CFG->dbtype === 'postgres7') {
1334         // try to get the primary key based on id
1335         if ( ($rs = $db->Execute('SELECT '. $primarykey .' FROM '. $CFG->prefix . $table .' WHERE oid = '. $id))
1336              && ($rs->RecordCount() == 1) ) {
1337             trigger_error("Retrieved $primarykey from oid on table $table because we could not find the sequence.");
1338             return (integer)$rs->fields[0];
1339         }
1340         trigger_error('Failed to retrieve primary key after insert: SELECT '. $primarykey .
1341                       ' FROM '. $CFG->prefix . $table .' WHERE oid = '. $id);
1342         return false;
1343     }
1344
1345     return (integer)$id;
1346 }
1347
1348 /**
1349  * Escape all dangerous characters in a data record
1350  *
1351  * $dataobject is an object containing needed data
1352  * Run over each field exectuting addslashes() function
1353  * to escape SQL unfriendly characters (e.g. quotes)
1354  * Handy when writing back data read from the database
1355  *
1356  * @param $dataobject Object containing the database record
1357  * @return object Same object with neccessary characters escaped
1358  */
1359 function addslashes_object( $dataobject ) {
1360     $a = get_object_vars( $dataobject);
1361     foreach ($a as $key=>$value) {
1362       $a[$key] = addslashes( $value );
1363     }
1364     return (object)$a;
1365 }
1366
1367 /**
1368  * Update a record in a table
1369  *
1370  * $dataobject is an object containing needed data
1371  * Relies on $dataobject having a variable "ident" to
1372  * specify the record to update
1373  *
1374  * @uses $CFG
1375  * @uses $db
1376  * @param string $table The database table to be checked against.
1377  * @param array $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'ident' to map to the table specified.
1378  * @return bool
1379  * @todo Finish documenting this function. Dataobject is actually an associateive array, correct?
1380  */
1381 function update_record($table, $dataobject) {
1382
1383     global $db, $CFG;
1384
1385     if (! isset($dataobject->ident) ) {
1386         return false;
1387     }
1388
1389     static $table_columns;
1390
1391     // Determine all the fields in the table
1392     if (is_array($table_columns) && array_key_exists($table,$table_columns)) {
1393         $columns = $table_columns[$table];
1394     } else {
1395         if (!$columns = $db->MetaColumns($CFG->prefix . $table)) {
1396             return false;
1397         }
1398         $table_columns[$table] = $columns;
1399     }
1400
1401     $data = (array)$dataobject;
1402
1403     if (defined('ELGG_PERFDB')) { global $PERF ; $PERF->dbqueries++; };
1404
1405     // Pull out data matching these fields
1406     foreach ($columns as $column) {
1407         if ($column->name <> 'ident' and isset($data[$column->name]) ) {
1408             $ddd[$column->name] = $data[$column->name];
1409         }
1410     }
1411
1412     // Construct SQL queries
1413     $numddd = count($ddd);
1414     $count = 0;
1415     $update = '';
1416
1417     foreach ($ddd as $key => $value) {
1418         $count++;
1419         $update .= $key .' = ?';
1420         if ($count < $numddd) {
1421             $update .= ', ';
1422         }
1423     }
1424
1425     $stmt = $db->Prepare('UPDATE '. $CFG->prefix . $table .' SET '. $update .' WHERE ident = \''. $dataobject->ident .'\'');
1426     if ($rs = $db->Execute($stmt,$ddd)) {
1427         return true;
1428     } else {
1429         if (isset($CFG->debug) and $CFG->debug > 7) {
1430             notify($db->ErrorMsg() .'<br /><br />UPDATE '. $CFG->prefix . $table .' SET '. $update .' WHERE ident = \''. $dataobject->ident .'\'');
1431         }
1432         if (!empty($CFG->dblogerror)) {
1433             $debug = debug_backtrace();
1434             foreach ($debug as $d) {
1435                 if (strpos($d['file'],'datalib') === false) {
1436                     error_log("SQL ".$db->ErrorMsg()." in {$d['file']} on line {$d['line']}. STATEMENT:  UPDATE $CFG->prefix$table SET $update WHERE ident = '$dataobject->ident'");
1437                     break;
1438                 }
1439             }
1440         }
1441         return false;
1442     }
1443 }
1444
1445 /// GENERAL HELPFUL THINGS  ///////////////////////////////////
1446
1447 /**
1448  * Dump a given object's information in a PRE block.
1449  *
1450  * Mostly just used for debugging.
1451  *
1452  * @param mixed $object The data to be printed
1453  * @todo add example usage and example output
1454  */
1455 function print_object($object) {
1456
1457     echo '<pre>';
1458     print_r($object);
1459     echo '</pre>';
1460 }
1461
1462 /**
1463  * Returns the proper SQL to do paging
1464  *
1465  * @uses $CFG
1466  * @param string $page Offset page number
1467  * @param string $recordsperpage Number of records per page
1468  * @return string
1469  */
1470 function sql_paging_limit($page, $recordsperpage) {
1471     global $CFG;
1472
1473     switch ($CFG->dbtype) {
1474         case 'postgres7':
1475              return 'LIMIT '. $recordsperpage .' OFFSET '. $page;
1476         default:
1477              return 'LIMIT '. $page .','. $recordsperpage;
1478     }
1479 }
1480
1481 /**
1482  * Returns the proper SQL to do LIKE in a case-insensitive way
1483  *
1484  * @uses $CFG
1485  * @return string
1486  */
1487 function sql_ilike() {
1488     global $CFG;
1489
1490     switch ($CFG->dbtype) {
1491         case 'mysql':
1492              return 'LIKE';
1493         default:
1494              return 'ILIKE';
1495     }
1496 }
1497
1498
1499 /**
1500  * Returns the proper SQL to do LIKE in a case-insensitive way
1501  *
1502  * @uses $CFG
1503  * @param string $firstname User's first name
1504  * @param string $lastname User's last name
1505  * @return string
1506  */
1507 function sql_fullname($firstname='firstname', $lastname='lastname') {
1508     global $CFG;
1509
1510     switch ($CFG->dbtype) {
1511         case 'mysql':
1512              return ' CONCAT('. $firstname .'," ",'. $lastname .') ';
1513         case 'postgres7':
1514              return " ". $firstname ."||' '||". $lastname ." ";
1515         default:
1516              return ' '. $firstname .'||" "||'. $lastname .' ';
1517     }
1518 }
1519
1520 /**
1521  * Returns the proper SQL to do IS NULL
1522  * @uses $CFG
1523  * @param string $fieldname The field to add IS NULL to
1524  * @return string
1525  */
1526 function sql_isnull($fieldname) {
1527     global $CFG;
1528
1529     switch ($CFG->dbtype) {
1530         case 'mysql':
1531              return $fieldname.' IS NULL';
1532         default:
1533              return $fieldname.' IS NULL';
1534     }
1535 }
1536
1537 /**
1538  * Prepare a SQL WHERE clause to select records where the given fields match the given values.
1539  *
1540  * Prepares a where clause of the form
1541  *     WHERE field1 = value1 AND field2 = value2 AND field3 = value3
1542  * except that you need only specify as many arguments (zero to three) as you need.
1543  *
1544  * @param string $field1 the first field to check (optional).
1545  * @param string $value1 the value field1 must have (requred if field1 is given, else optional).
1546  * @param string $field2 the second field to check (optional).
1547  * @param string $value2 the value field2 must have (requred if field2 is given, else optional).
1548  * @param string $field3 the third field to check (optional).
1549  * @param string $value3 the value field3 must have (requred if field3 is given, else optional).
1550  */
1551 function where_clause($field1='', $value1='', $field2='', $value2='', $field3='', $value3='') {
1552     if ($field1) {
1553         $select = "WHERE $field1 = '$value1'";
1554         if ($field2) {
1555             $select .= " AND $field2 = '$value2'";
1556             if ($field3) {
1557                 $select .= " AND $field3 = '$value3'";
1558             }
1559         }
1560     } else {
1561         $select = '';
1562     }
1563     return $select;
1564 }
1565
1566 /**
1567  * Prepares a SQL WHERE clause to select records where the given fields match some values.
1568  * Uses ? as placeholders for prepared statments
1569  *
1570  * @param string $field1 the first field to check (optional).
1571  * @param string $field2 the second field to check (optional).
1572  * @param string $field3 the third field to check (optional).
1573  */
1574 function where_clause_prepared($field1='', $field2='', $field3='') {
1575     $select = '';
1576     if (!empty($field1)) {
1577         $select = " WHERE $field1 = ? ";
1578         if (!empty($field2)) {
1579             $select .= " AND $field2 = ? ";
1580             if (!empty($field3)) {
1581                 $select .= " AND $field3 = ? ";
1582             }
1583         }
1584     }
1585     return $select;
1586 }
1587
1588 /*
1589  * useful helper function to only push optional values into the array
1590  * for prepared statements to avoid empty slots.
1591  * all parameters are optional.
1592  */
1593 function where_values_prepared($value1=null, $value2=null, $value3=null) {
1594     $values = array();
1595     if (isset($value1)) {
1596         $values[] = $value1;
1597         if (isset($value2)) {
1598             $values[] = $value2;
1599             if (isset($value3)) {
1600                 $values[] = $value3;
1601             }
1602         }
1603     }
1604     return $values;
1605 }
1606
1607
1608 /**
1609  * Checks for pg or mysql > 4
1610  * (lots of stuff we might want to use
1611  * requires more complicated JOIN syntax
1612  * that mysql < 4 will get upset by)
1613  */
1614
1615 function check_db_compat() {
1616     global $CFG,$db;
1617
1618     if ($CFG->dbtype == 'postgres7') {
1619         return true;
1620     }
1621
1622     if (!$rs = $db->Execute("SELECT version();")) {
1623         return false;
1624     }
1625
1626     if (intval($rs->fields[0]) <= 3) {
1627         return false;
1628     }
1629
1630     return true;
1631 }
1632
1633 ?>