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