projects.mako.cc - scuttle/blob - includes/db/postgres.php

   1 <?php
   2 /**
   3 *
   4 * @package dbal_postgres
   5 * @version $Id: postgres.php,v 1.2 2005/06/10 08:52:03 devalley Exp $
   6 * @copyright (c) 2005 phpBB Group
   7 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
   8 *
   9 */
  10
  11 /**
  12 * @ignore
  13 */
  14 if (!defined('SQL_LAYER'))
  15 {
  16
  17 define('SQL_LAYER', 'postgresql');
  18
  19 /**
  20 * @package dbal_postgres
  21 * PostgreSQL Database Abstraction Layer
  22 * Minimum Requirement is Version 7.3+
  23 */
  24 class sql_db
  25 {
  26         var $db_connect_id;
  27         var $query_result;
  28         var $return_on_error = false;
  29         var $transaction = false;
  30         var $sql_time = 0;
  31         var $num_queries = 0;
  32         var $open_queries = array();
  33
  34         function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false)
  35         {
  36                 $this->connect_string = '';
  37
  38                 if ($sqluser)
  39                 {
  40                         $this->connect_string .= "user=$sqluser ";
  41                 }
  42
  43                 if ($sqlpassword)
  44                 {
  45                         $this->connect_string .= "password=$sqlpassword ";
  46                 }
  47
  48                 if ($sqlserver)
  49                 {
  50                         if (ereg(":", $sqlserver))
  51                         {
  52                                 list($sqlserver, $sqlport) = split(":", $sqlserver);
  53                                 $this->connect_string .= "host=$sqlserver port=$sqlport ";
  54                         }
  55                         else
  56                         {
  57                                 if ($sqlserver != "localhost")
  58                                 {
  59                                         $this->connect_string .= "host=$sqlserver ";
  60                                 }
  61
  62                                 if ($port)
  63                                 {
  64                                         $this->connect_string .= "port=$port ";
  65                                 }
  66                         }
  67                 }
  68
  69                 if ($database)
  70                 {
  71                         $this->dbname = $database;
  72                         $this->connect_string .= "dbname=$database";
  73                 }
  74
  75                 $this->persistency = $persistency;
  76
  77                 $this->db_connect_id = ($this->persistency) ? @pg_pconnect($this->connect_string) : @pg_connect($this->connect_string);
  78
  79                 return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error('');
  80         }
  81
  82         //
  83         // Other base methods
  84         //
  85         function sql_close()
  86         {
  87                 if (!$this->db_connect_id)
  88                 {
  89                         return false;
  90                 }
  91
  92                 if ($this->transaction)
  93                 {
  94                         @pg_exec($this->db_connect_id, 'COMMIT');
  95                 }
  96
  97                 return @pg_close($this->db_connect_id);
  98         }
  99
 100         function sql_return_on_error($fail = false)
 101         {
 102                 $this->return_on_error = $fail;
 103         }
 104
 105         function sql_num_queries()
 106         {
 107                 return $this->num_queries;
 108         }
 109
 110         function sql_transaction($status = 'begin')
 111         {
 112                 switch ($status)
 113                 {
 114                         case 'begin':
 115                                 $result = @pg_exec($this->db_connect_id, 'BEGIN');
 116                                 $this->transaction = true;
 117                                 break;
 118
 119                         case 'commit':
 120                                 $result = @pg_exec($this->db_connect_id, 'COMMIT');
 121                                 $this->transaction = false;
 122
 123                                 if (!$result)
 124                                 {
 125                                         @pg_exec($this->db_connect_id, 'ROLLBACK');
 126                                 }
 127                                 break;
 128
 129                         case 'rollback':
 130                                 $result = @pg_exec($this->db_connect_id, 'ROLLBACK');
 131                                 $this->transaction = false;
 132                                 break;
 133
 134                         default:
 135                                 $result = true;
 136                 }
 137
 138                 return $result;
 139         }
 140
 141         // Base query method
 142         function sql_query($query = '', $cache_ttl = 0)
 143         {
 144                 if ($query != '')
 145                 {
 146                         global $cache;
 147
 148                         // EXPLAIN only in extra debug mode
 149                         if (defined('DEBUG_EXTRA'))
 150                         {
 151                                 $this->sql_report('start', $query);
 152                         }
 153
 154                         $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
 155
 156                         if (!$this->query_result)
 157                         {
 158                                 $this->num_queries++;
 159                                 $this->last_query_text = $query;
 160
 161                                 if (($this->query_result = @pg_exec($this->db_connect_id, $query)) === false)
 162                                 {
 163                                         $this->sql_error($query);
 164                                 }
 165
 166                                 if (defined('DEBUG_EXTRA'))
 167                                 {
 168                                         $this->sql_report('stop', $query);
 169                                 }
 170
 171                                 if ($cache_ttl && method_exists($cache, 'sql_save'))
 172                                 {
 173                                         $cache->sql_save($query, $this->query_result, $cache_ttl);
 174                                 }
 175                         }
 176                         else if (defined('DEBUG_EXTRA'))
 177                         {
 178                                 $this->sql_report('fromcache', $query);
 179                         }
 180                 }
 181                 else
 182                 {
 183                         return false;
 184                 }
 185
 186                 return ($this->query_result) ? $this->query_result : false;
 187         }
 188
 189         function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
 190         {
 191                 if ($query != '')
 192                 {
 193                         $this->query_result = false;
 194
 195                         // if $total is set to 0 we do not want to limit the number of rows
 196                         if ($total == 0)
 197                         {
 198                                 $total = -1;
 199                         }
 200
 201                         $query .= "\n LIMIT $total OFFSET $offset";
 202
 203                         return $this->sql_query($query, $cache_ttl);
 204                 }
 205                 else
 206                 {
 207                         return false;
 208                 }
 209         }
 210
 211         // Idea for this from Ikonboard
 212         function sql_build_array($query, $assoc_ary = false)
 213         {
 214                 if (!is_array($assoc_ary))
 215                 {
 216                         return false;
 217                 }
 218
 219                 $fields = array();
 220                 $values = array();
 221                 if ($query == 'INSERT')
 222                 {
 223                         foreach ($assoc_ary as $key => $var)
 224                         {
 225                                 $fields[] = $key;
 226
 227                                 if (is_null($var))
 228                                 {
 229                                         $values[] = 'NULL';
 230                                 }
 231                                 elseif (is_string($var))
 232                                 {
 233                                         $values[] = "'" . $this->sql_escape($var) . "'";
 234                                 }
 235                                 else
 236                                 {
 237                                         $values[] = (is_bool($var)) ? intval($var) : $var;
 238                                 }
 239                         }
 240
 241                         $query = ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')';
 242                 }
 243                 else if ($query == 'UPDATE' || $query == 'SELECT')
 244                 {
 245                         $values = array();
 246                         foreach ($assoc_ary as $key => $var)
 247                         {
 248                                 if (is_null($var))
 249                                 {
 250                                         $values[] = "$key = NULL";
 251                                 }
 252                                 elseif (is_string($var))
 253                                 {
 254                                         $values[] = "$key = '" . $this->sql_escape($var) . "'";
 255                                 }
 256                                 else
 257                                 {
 258                                         $values[] = (is_bool($var)) ? "$key = " . intval($var) : "$key = $var";
 259                                 }
 260                         }
 261                         $query = implode(($query == 'UPDATE') ? ', ' : ' AND ', $values);
 262                 }
 263
 264                 return $query;
 265         }
 266
 267         // Other query methods
 268         //
 269         // NOTE :: Want to remove _ALL_ reliance on sql_numrows from core code ...
 270         //         don't want this here by a middle Milestone
 271         function sql_numrows($query_id = false)
 272         {
 273                 if (!$query_id)
 274                 {
 275                         $query_id = $this->query_result;
 276                 }
 277
 278                 return ($query_id) ? @pg_numrows($query_id) : false;
 279         }
 280
 281         function sql_affectedrows($query_id = false)
 282         {
 283                 if (!$query_id)
 284                 {
 285                         $query_id = $this->query_result;
 286                 }
 287
 288                 return ($query_id) ? @pg_cmdtuples($query_id) : false;
 289         }
 290
 291         function sql_fetchrow($query_id = false)
 292         {
 293                 global $cache;
 294
 295                 if (!$query_id)
 296                 {
 297                         $query_id = $this->query_result;
 298                 }
 299
 300                 if (!isset($this->rownum[$query_id]))
 301                 {
 302                         $this->rownum[$query_id] = 0;
 303                 }
 304
 305                 if (isset($cache->sql_rowset[$query_id]))
 306                 {
 307                         return $cache->sql_fetchrow($query_id);
 308                 }
 309
 310                 $result = @pg_fetch_array($query_id, NULL, PGSQL_ASSOC);
 311
 312                 if ($result)
 313                 {
 314                         $this->rownum[$query_id]++;
 315                 }
 316
 317                 return $result;
 318         }
 319
 320         function sql_fetchrowset($query_id = false)
 321         {
 322                 if (!$query_id)
 323                 {
 324                         $query_id = $this->query_result;
 325                 }
 326
 327                 $result = array();
 328
 329                 if ($query_id)
 330                 {
 331                         unset($this->rowset[$query_id]);
 332                         unset($this->row[$query_id]);
 333
 334                         $result = array();
 335                         while ($this->rowset[$query_id] = $this->sql_fetchrow($query_id))
 336                         {
 337                                 $result[] = $this->rowset[$query_id];
 338                         }
 339                         return $result;
 340                 }
 341
 342                 return false;
 343         }
 344
 345         function sql_fetchfield($field, $rownum = -1, $query_id = false)
 346         {
 347                 if (!$query_id)
 348                 {
 349                         $query_id = $this->query_result;
 350                 }
 351
 352                 if ($query_id)
 353                 {
 354                         if ($rownum > -1)
 355                         {
 356                                 if (@function_exists('pg_result_seek'))
 357                                 {
 358                                         @pg_result_seek($query_id, $rownum);
 359                                         $row = @pg_fetch_assoc($query_id);
 360                                         $result = isset($row[$field]) ? $row[$field] : false;
 361                                 }
 362                                 else
 363                                 {
 364                                         $this->sql_rowseek($offset, $query_id);
 365                                         $row = $this->sql_fetchrow($query_id);
 366                                         $result = isset($row[$field]) ? $row[$field] : false;
 367                                 }
 368                         }
 369                         else
 370                         {
 371                                 if (empty($this->row[$query_id]) && empty($this->rowset[$query_id]))
 372                                 {
 373                                         if ($this->sql_fetchrow($query_id))
 374                                         {
 375                                                 $result = $this->row[$query_id][$field];
 376                                         }
 377                                 }
 378                                 else
 379                                 {
 380                                         if ($this->rowset[$query_id])
 381                                         {
 382                                                 $result = $this->rowset[$query_id][$field];
 383                                         }
 384                                         elseif ($this->row[$query_id])
 385                                         {
 386                                                 $result = $this->row[$query_id][$field];
 387                                         }
 388                                 }
 389                         }
 390                         return $result;
 391                 }
 392                 return false;
 393         }
 394
 395         function sql_rowseek($offset, $query_id = false)
 396         {
 397                 if (!$query_id)
 398                 {
 399                         $query_id = $this->query_result;
 400                 }
 401
 402                 if ($query_id)
 403                 {
 404                         if ($offset > -1)
 405                         {
 406                                 if (@function_exists('pg_result_seek'))
 407                                 {
 408                                         @pg_result_seek($query_id, $rownum);
 409                                 }
 410                                 else
 411                                 {
 412                                         for ($i = $this->rownum[$query_id]; $i < $offset; $i++)
 413                                         {
 414                                                 $this->sql_fetchrow($query_id);
 415                                         }
 416                                 }
 417                                 return true;
 418                         }
 419                         else
 420                         {
 421                                 return false;
 422                         }
 423                 }
 424
 425                 return false;
 426         }
 427
 428         function sql_nextid()
 429         {
 430                 $query_id = $this->query_result;
 431
 432                 if ($query_id && $this->last_query_text != '')
 433                 {
 434                         if (preg_match("/^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)/is", $this->last_query_text, $tablename))
 435                         {
 436                                 $query = "SELECT currval('" . $tablename[1] . "_id_seq') AS last_value";
 437                                 $temp_q_id =  @pg_exec($this->db_connect_id, $query);
 438                                 if (!$temp_q_id)
 439                                 {
 440                                         return false;
 441                                 }
 442
 443                                 $temp_result = @pg_fetch_array($temp_q_id, NULL, PGSQL_ASSOC);
 444
 445                                 return ($temp_result) ? $temp_result['last_value'] : false;
 446                         }
 447                 }
 448
 449                 return false;
 450         }
 451
 452         function sql_freeresult($query_id = false)
 453         {
 454                 if (!$query_id)
 455                 {
 456                         $query_id = $this->query_result;
 457                 }
 458
 459                 return (is_resource($query_id)) ? @pg_freeresult($query_id) : false;
 460         }
 461
 462         function sql_escape($msg)
 463         {
 464                 return str_replace("'", "''", str_replace('\\', '\\\\', $msg));
 465         }
 466
 467         function sql_error($sql = '')
 468         {
 469                 if (!$this->return_on_error)
 470                 {
 471                         $this_page = (isset($_SERVER['PHP_SELF']) && !empty($_SERVER['PHP_SELF'])) ? $_SERVER['PHP_SELF'] : $_ENV['PHP_SELF'];
 472                         $this_page .= '&' . ((isset($_SERVER['QUERY_STRING']) && !empty($_SERVER['QUERY_STRING'])) ? $_SERVER['QUERY_STRING'] : (isset($_ENV['QUERY_STRING']) ? $_ENV['QUERY_STRING'] : ''));
 473
 474                         $message = '<u>SQL ERROR</u> [ ' . SQL_LAYER . ' ]<br /><br />' . @pg_errormessage() . '<br /><br /><u>CALLING PAGE</u><br /><br />'  . htmlspecialchars($this_page) . (($sql != '') ? '<br /><br /><u>SQL</u><br /><br />' . $sql : '') . '<br />';
 475
 476                         if ($this->transaction)
 477                         {
 478                                 $this->sql_transaction('rollback');
 479                         }
 480
 481                         trigger_error($message, E_USER_ERROR);
 482                 }
 483
 484                 $result = array(
 485                         'message'       => @pg_errormessage(),
 486                         'code'          => ''
 487                 );
 488
 489                 return $result;
 490         }
 491
 492         function sql_report($mode, $query = '')
 493         {
 494                 if (empty($_GET['explain']))
 495                 {
 496                         return;
 497                 }
 498
 499                 global $cache, $starttime, $phpbb_root_path;
 500                 static $curtime, $query_hold, $html_hold;
 501                 static $sql_report = '';
 502                 static $cache_num_queries = 0;
 503
 504                 if (!$query && !empty($query_hold))
 505                 {
 506                         $query = $query_hold;
 507                 }
 508
 509                 switch ($mode)
 510                 {
 511                         case 'display':
 512                                 if (!empty($cache))
 513                                 {
 514                                         $cache->unload();
 515                                 }
 516                                 $this->sql_close();
 517
 518                                 $mtime = explode(' ', microtime());
 519                                 $totaltime = $mtime[0] + $mtime[1] - $starttime;
 520
 521                                 echo '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><meta http-equiv="Content-Type" content="text/html; charset=iso-8869-1"><meta http-equiv="Content-Style-Type" content="text/css"><link rel="stylesheet" href="' . $phpbb_root_path . 'adm/subSilver.css" type="text/css"><style type="text/css">' . "\n";
 522                                 echo 'th { background-image: url(\'' . $phpbb_root_path . 'adm/images/cellpic3.gif\') }' . "\n";
 523                                 echo 'td.cat    { background-image: url(\'' . $phpbb_root_path . 'adm/images/cellpic1.gif\') }' . "\n";
 524                                 echo '</style><title>' . $msg_title . '</title></head><body>';
 525                                 echo '<table width="100%" cellspacing="0" cellpadding="0" border="0"><tr><td><a href="' . htmlspecialchars(preg_replace('/&explain=([^&]*)/', '', $_SERVER['REQUEST_URI'])) . '"><img src="' . $phpbb_root_path . 'adm/images/header_left.jpg" width="200" height="60" alt="phpBB Logo" title="phpBB Logo" border="0"/></a></td><td width="100%" background="' . $phpbb_root_path . 'adm/images/header_bg.jpg" height="60" align="right" nowrap="nowrap"><span class="maintitle">SQL Report</span> &nbsp; &nbsp; &nbsp;</td></tr></table><br clear="all"/><table width="95%" cellspacing="1" cellpadding="4" border="0" align="center"><tr><td height="40" align="center" valign="middle"><b>Page generated in ' . round($totaltime, 4) . " seconds with {$this->num_queries} queries" . (($cache_num_queries) ? " + $cache_num_queries " . (($cache_num_queries == 1) ? 'query' : 'queries') . ' returning data from cache' : '') . '</b></td></tr><tr><td align="center" nowrap="nowrap">Time spent on MySQL queries: <b>' . round($this->sql_time, 5) . 's</b> | Time spent on PHP: <b>' . round($totaltime - $this->sql_time, 5) . 's</b></td></tr></table><table width="95%" cellspacing="1" cellpadding="4" border="0" align="center"><tr><td>';
 526                                 echo $sql_report;
 527                                 echo '</td></tr></table><br /></body></html>';
 528                                 exit;
 529                                 break;
 530
 531                         case 'start':
 532                                 $query_hold = $query;
 533                                 $html_hold = '';
 534
 535                                 $curtime = explode(' ', microtime());
 536                                 $curtime = $curtime[0] + $curtime[1];
 537                                 break;
 538
 539                         case 'fromcache':
 540                                 $endtime = explode(' ', microtime());
 541                                 $endtime = $endtime[0] + $endtime[1];
 542
 543                                 $result = @pg_exec($this->db_connect_id, $query);
 544                                 while ($void = @pg_fetch_array($result, NULL, PGSQL_ASSOC))
 545                                 {
 546                                         // Take the time spent on parsing rows into account
 547                                 }
 548                                 $splittime = explode(' ', microtime());
 549                                 $splittime = $splittime[0] + $splittime[1];
 550
 551                                 $time_cache = $endtime - $curtime;
 552                                 $time_db = $splittime - $endtime;
 553                                 $color = ($time_db > $time_cache) ? 'green' : 'red';
 554
 555                                 $sql_report .= '<hr width="100%"/><br /><table class="bg" width="100%" cellspacing="1" cellpadding="4" border="0"><tr><th>Query results obtained from the cache</th></tr><tr><td class="row1"><textarea style="font-family:\'Courier New\',monospace;width:100%" rows="5">' . preg_replace('/\t(AND|OR)(\W)/', "\$1\$2", htmlspecialchars(preg_replace('/[\s]*[\n\r\t]+[\n\r\s\t]*/', "\n", $query))) . '</textarea></td></tr></table><p align="center">';
 556
 557                                 $sql_report .= 'Before: ' . sprintf('%.5f', $curtime - $starttime) . 's | After: ' . sprintf('%.5f', $endtime - $starttime) . 's | Elapsed [cache]: <b style="color: ' . $color . '">' . sprintf('%.5f', ($time_cache)) . 's</b> | Elapsed [db]: <b>' . sprintf('%.5f', $time_db) . 's</b></p>';
 558
 559                                 // Pad the start time to not interfere with page timing
 560                                 $starttime += $time_db;
 561
 562                                 @pg_freeresult($result);
 563                                 $cache_num_queries++;
 564                                 break;
 565
 566                         case 'stop':
 567                                 $endtime = explode(' ', microtime());
 568                                 $endtime = $endtime[0] + $endtime[1];
 569
 570                                 $sql_report .= '<hr width="100%"/><br /><table class="bg" width="100%" cellspacing="1" cellpadding="4" border="0"><tr><th>Query #' . $this->num_queries . '</th></tr><tr><td class="row1"><textarea style="font-family:\'Courier New\',monospace;width:100%" rows="5">' . preg_replace('/\t(AND|OR)(\W)/', "\$1\$2", htmlspecialchars(preg_replace('/[\s]*[\n\r\t]+[\n\r\s\t]*/', "\n", $query))) . '</textarea></td></tr></table> ' . $html_hold . '<p align="center">';
 571
 572                                 if ($this->query_result)
 573                                 {
 574                                         if (preg_match('/^(UPDATE|DELETE|REPLACE)/', $query))
 575                                         {
 576                                                 $sql_report .= "Affected rows: <b>" . $this->sql_affectedrows($this->query_result) . '</b> | ';
 577                                         }
 578                                         $sql_report .= 'Before: ' . sprintf('%.5f', $curtime - $starttime) . 's | After: ' . sprintf('%.5f', $endtime - $starttime) . 's | Elapsed: <b>' . sprintf('%.5f', $endtime - $curtime) . 's</b>';
 579                                 }
 580                                 else
 581                                 {
 582                                         $error = $this->sql_error();
 583                                         $sql_report .= '<b style="color: red">FAILED</b> - ' . SQL_LAYER . ' Error ' . $error['code'] . ': ' . htmlspecialchars($error['message']);
 584                                 }
 585
 586                                 $sql_report .= '</p>';
 587
 588                                 $this->sql_time += $endtime - $curtime;
 589                                 break;
 590                 }
 591         }
 592
 593 } // class ... db_sql
 594
 595 } // if ... defined
 596
 597 ?>