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

   1 <?php
   2 /**
   3 *
   4 * @package dbal_firebird
   5 * @version $Id: firebird.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', 'firebird');
  18
  19 /**
  20 * @package dbal_firebird
  21 * Firebird/Interbase Database Abstraction Layer
  22 * Minimum Requirement is Firebird 1.5+/Interbase 7.1+
  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         var $last_query_text = '';
  35
  36         function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false)
  37         {
  38                 $this->persistency = $persistency;
  39                 $this->user = $sqluser;
  40                 $this->server = $sqlserver . (($port) ? ':' . $port : '');
  41                 $this->dbname = $database;
  42
  43                 $this->db_connect_id = ($this->persistency) ? @ibase_pconnect($this->server . ':' . $this->dbname, $this->user, $sqlpassword, false, false, 3) : @ibase_connect($this->server . ':' . $this->dbname, $this->user, $sqlpassword, false, false, 3);
  44
  45                 return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error('');
  46         }
  47
  48         //
  49         // Other base methods
  50         //
  51         function sql_close()
  52         {
  53                 if (!$this->db_connect_id)
  54                 {
  55                         return false;
  56                 }
  57
  58                 if ($this->transaction)
  59                 {
  60                         @ibase_commit($this->db_connect_id);
  61                 }
  62
  63                 if (sizeof($this->open_queries))
  64                 {
  65                         foreach ($this->open_queries as $i_query_id => $query_id)
  66                         {
  67                                 @ibase_free_query($query_id);
  68                         }
  69                 }
  70
  71                 return @ibase_close($this->db_connect_id);
  72         }
  73
  74         function sql_return_on_error($fail = false)
  75         {
  76                 $this->return_on_error = $fail;
  77         }
  78
  79         function sql_num_queries()
  80         {
  81                 return $this->num_queries;
  82         }
  83
  84         function sql_transaction($status = 'begin')
  85         {
  86                 switch ($status)
  87                 {
  88                         case 'begin':
  89                                 $this->transaction = true;
  90                                 break;
  91
  92                         case 'commit':
  93                                 $result = @ibase_commit();
  94                                 $this->transaction = false;
  95
  96                                 if (!$result)
  97                                 {
  98                                         @ibase_rollback();
  99                                 }
 100                                 break;
 101
 102                         case 'rollback':
 103                                 $result = @ibase_rollback();
 104                                 $this->transaction = false;
 105                                 break;
 106
 107                         default:
 108                                 $result = true;
 109                 }
 110
 111                 return $result;
 112         }
 113
 114         // Base query method
 115         function sql_query($query = '', $cache_ttl = 0)
 116         {
 117                 if ($query != '')
 118                 {
 119                         global $cache;
 120
 121                         $this->last_query_text = $query;
 122                         $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
 123
 124                         if (!$this->query_result)
 125                         {
 126                                 $this->num_queries++;
 127
 128                                 if (($this->query_result = @ibase_query($this->db_connect_id, $query)) === false)
 129                                 {
 130                                         $this->sql_error($query);
 131                                 }
 132
 133                                 // TODO: have to debug the commit states in firebird
 134                                 if (!$this->transaction)
 135                                 {
 136                                         @ibase_commit_ret();
 137                                 }
 138
 139                                 if ($cache_ttl && method_exists($cache, 'sql_save'))
 140                                 {
 141                                         $cache->sql_save($query, $this->query_result, $cache_ttl);
 142                                 }
 143                         }
 144                 }
 145                 else
 146                 {
 147                         return false;
 148                 }
 149
 150                 return ($this->query_result) ? $this->query_result : false;
 151         }
 152
 153         function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
 154         {
 155                 if ($query != '')
 156                 {
 157                         $this->query_result = false;
 158
 159                         $query = 'SELECT FIRST ' . $total . ((!empty($offset)) ? ' SKIP ' . $offset : '') . substr($query, 6);
 160
 161                         return $this->sql_query($query, $cache_ttl);
 162                 }
 163                 else
 164                 {
 165                         return false;
 166                 }
 167         }
 168
 169         // Idea for this from Ikonboard
 170         function sql_build_array($query, $assoc_ary = false)
 171         {
 172                 if (!is_array($assoc_ary))
 173                 {
 174                         return false;
 175                 }
 176
 177                 $fields = array();
 178                 $values = array();
 179                 if ($query == 'INSERT')
 180                 {
 181                         foreach ($assoc_ary as $key => $var)
 182                         {
 183                                 $fields[] = $key;
 184
 185                                 if (is_null($var))
 186                                 {
 187                                         $values[] = 'NULL';
 188                                 }
 189                                 elseif (is_string($var))
 190                                 {
 191                                         $values[] = "'" . $this->sql_escape($var) . "'";
 192                                 }
 193                                 else
 194                                 {
 195                                         $values[] = (is_bool($var)) ? intval($var) : $var;
 196                                 }
 197                         }
 198
 199                         $query = ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')';
 200                 }
 201                 else if ($query == 'UPDATE' || $query == 'SELECT')
 202                 {
 203                         $values = array();
 204                         foreach ($assoc_ary as $key => $var)
 205                         {
 206                                 if (is_null($var))
 207                                 {
 208                                         $values[] = "$key = NULL";
 209                                 }
 210                                 elseif (is_string($var))
 211                                 {
 212                                         $values[] = "$key = '" . $this->sql_escape($var) . "'";
 213                                 }
 214                                 else
 215                                 {
 216                                         $values[] = (is_bool($var)) ? "$key = " . intval($var) : "$key = $var";
 217                                 }
 218                         }
 219                         $query = implode(($query == 'UPDATE') ? ', ' : ' AND ', $values);
 220                 }
 221
 222                 return $query;
 223         }
 224
 225         // Other query methods
 226         //
 227         // NOTE :: Want to remove _ALL_ reliance on sql_numrows from core code ...
 228         //         don't want this here by a middle Milestone
 229         function sql_numrows($query_id = false)
 230         {
 231                 return FALSE;
 232         }
 233
 234         function sql_affectedrows()
 235         {
 236                 // TODO: hmm, maybe doing something similar as in mssql-odbc.php?
 237                 return ($this->query_result) ? true : false;
 238         }
 239
 240         function sql_fetchrow($query_id = false)
 241         {
 242                 global $cache;
 243
 244                 if (!$query_id)
 245                 {
 246                         $query_id = $this->query_result;
 247                 }
 248
 249                 if (isset($cache->sql_rowset[$query_id]))
 250                 {
 251                         return $cache->sql_fetchrow($query_id);
 252                 }
 253
 254                 $row = array();
 255                 $cur_row = @ibase_fetch_object($query_id, IBASE_TEXT);
 256
 257                 if (!$cur_row)
 258                 {
 259                         return false;
 260                 }
 261
 262                 foreach (get_object_vars($cur_row) as $key => $value)
 263                 {
 264                         $row[strtolower($key)] = trim(str_replace("\\0", "\0", str_replace("\\n", "\n", $value)));
 265                 }
 266                 return ($query_id) ? $row : false;
 267         }
 268
 269         function sql_fetchrowset($query_id = false)
 270         {
 271                 if (!$query_id)
 272                 {
 273                         $query_id = $this->query_result;
 274                 }
 275
 276                 if ($query_id)
 277                 {
 278                         unset($this->rowset[$query_id]);
 279                         unset($this->row[$query_id]);
 280
 281                         $result = array();
 282                         while ($this->rowset[$query_id] = get_object_vars(@ibase_fetch_object($query_id, IBASE_TEXT)))
 283                         {
 284                                 $result[] = $this->rowset[$query_id];
 285                         }
 286
 287                         return $result;
 288                 }
 289                 else
 290                 {
 291                         return false;
 292                 }
 293         }
 294
 295         function sql_fetchfield($field, $rownum = -1, $query_id = 0)
 296         {
 297                 if (!$query_id)
 298                 {
 299                         $query_id = $this->query_result;
 300                 }
 301
 302                 if ($query_id)
 303                 {
 304                         if ($rownum > -1)
 305                         {
 306                                 // erm... ok, my bad, we always use zero. :/
 307                                 for ($i = 0; $i <= $rownum; $i++)
 308                                 {
 309                                         $row = $this->sql_fetchrow($query_id);
 310                                 }
 311
 312                                 return $row[$field];
 313                         }
 314                         else
 315                         {
 316                                 if (empty($this->row[$query_id]) && empty($this->rowset[$query_id]))
 317                                 {
 318                                         if ($this->sql_fetchrow($query_id))
 319                                         {
 320                                                 $result = $this->row[$query_id][$field];
 321                                         }
 322                                 }
 323                                 else
 324                                 {
 325                                         if ($this->rowset[$query_id])
 326                                         {
 327                                                 $result = $this->rowset[$query_id][$field];
 328                                         }
 329                                         else if ($this->row[$query_id])
 330                                         {
 331                                                 $result = $this->row[$query_id][$field];
 332                                         }
 333                                 }
 334                         }
 335                         return $result;
 336                 }
 337                 else
 338                 {
 339                         return false;
 340                 }
 341         }
 342
 343         function sql_rowseek($rownum, $query_id = 0)
 344         {
 345                 if (!$query_id)
 346                 {
 347                         $query_id = $this->query_result;
 348                 }
 349
 350                 for($i = 1; $i < $rownum; $i++)
 351                 {
 352                         if (!$this->sql_fetchrow($query_id))
 353                         {
 354                                 return false;
 355                         }
 356                 }
 357
 358                 return true;
 359         }
 360
 361         function sql_nextid()
 362         {
 363                 if ($this->query_result && preg_match('#^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)#is', $this->last_query_text, $tablename))
 364                 {
 365                         $query = "SELECT GEN_ID('" . $tablename[1] . "_gen', 0) AS new_id
 366                                 FROM RDB\$DATABASE";
 367                         if (!($temp_q_id =  @ibase_query($this->db_connect_id, $query)))
 368                         {
 369                                 return false;
 370                         }
 371
 372                         $temp_result = @ibase_fetch_object($temp_q_id);
 373                         $this->sql_freeresult($temp_q_id);
 374
 375                         return ($temp_result) ? $temp_result->last_value : false;
 376                 }
 377         }
 378
 379         function sql_freeresult($query_id = false)
 380         {
 381                 if (!$query_id)
 382                 {
 383                         $query_id = $this->query_result;
 384                 }
 385
 386                 if (!$this->transaction && $query_id)
 387                 {
 388                         @ibase_commit();
 389                 }
 390
 391                 return ($query_id) ? @ibase_free_result($query_id) : false;
 392         }
 393
 394         function sql_escape($msg)
 395         {
 396                 return (@ini_get('magic_quotes_sybase') || strtolower(@ini_get('magic_quotes_sybase')) == 'on') ? str_replace('\\\'', '\'', addslashes($msg)) : str_replace('\'', '\'\'', stripslashes($msg));
 397         }
 398
 399         function sql_error($sql = '')
 400         {
 401                 if (!$this->return_on_error)
 402                 {
 403                         $this_page =(!empty($_SERVER['PHP_SELF'])) ? $_SERVER['PHP_SELF'] : $_ENV['PHP_SELF'];
 404                         $this_page .= '&' .((!empty($_SERVER['QUERY_STRING'])) ? $_SERVER['QUERY_STRING'] : $_ENV['QUERY_STRING']);
 405
 406                         $message = '<u>SQL ERROR</u> [ ' . SQL_LAYER . ' ]<br /><br />' . @ibase_errmsg() . '<br /><br /><u>CALLING PAGE</u><br /><br />'  . $this_page .(($sql != '') ? '<br /><br /><u>SQL</u><br /><br />' . $sql : '') . '<br />';
 407
 408                         if ($this->transaction)
 409                         {
 410                                 $this->sql_transaction('rollback');
 411                         }
 412
 413                         trigger_error($message, E_USER_ERROR);
 414                 }
 415
 416                 $result['message'] = @ibase_errmsg();
 417                 $result['code'] = '';
 418
 419                 return $result;
 420         }
 421
 422         function sql_report($mode, $query = '')
 423         {
 424                 if (empty($_GET['explain']))
 425                 {
 426                         return;
 427                 }
 428
 429                 global $cache, $starttime, $phpbb_root_path;
 430                 static $curtime, $query_hold, $html_hold;
 431                 static $sql_report = '';
 432                 static $cache_num_queries = 0;
 433
 434                 if (!$query && !empty($query_hold))
 435                 {
 436                         $query = $query_hold;
 437                 }
 438
 439                 switch ($mode)
 440                 {
 441                         case 'display':
 442                                 if (!empty($cache))
 443                                 {
 444                                         $cache->unload();
 445                                 }
 446                                 $this->sql_close();
 447
 448                                 $mtime = explode(' ', microtime());
 449                                 $totaltime = $mtime[0] + $mtime[1] - $starttime;
 450
 451                                 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";
 452                                 echo 'th { background-image: url(\'' . $phpbb_root_path . 'adm/images/cellpic3.gif\') }' . "\n";
 453                                 echo 'td.cat    { background-image: url(\'' . $phpbb_root_path . 'adm/images/cellpic1.gif\') }' . "\n";
 454                                 echo '</style><title>' . $msg_title . '</title></head><body>';
 455                                 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>';
 456                                 echo $sql_report;
 457                                 echo '</td></tr></table><br /></body></html>';
 458                                 exit;
 459                                 break;
 460
 461                         case 'start':
 462                                 $query_hold = $query;
 463                                 $html_hold = '';
 464
 465                                 $curtime = explode(' ', microtime());
 466                                 $curtime = $curtime[0] + $curtime[1];
 467                                 break;
 468
 469                         case 'fromcache':
 470                                 $endtime = explode(' ', microtime());
 471                                 $endtime = $endtime[0] + $endtime[1];
 472
 473                                 $result = @ibase_query($this->db_connect_id, $query);
 474                                 while ($void = @ibase_fetch_object($result, IBASE_TEXT))
 475                                 {
 476                                         // Take the time spent on parsing rows into account
 477                                 }
 478                                 $splittime = explode(' ', microtime());
 479                                 $splittime = $splittime[0] + $splittime[1];
 480
 481                                 $time_cache = $endtime - $curtime;
 482                                 $time_db = $splittime - $endtime;
 483                                 $color = ($time_db > $time_cache) ? 'green' : 'red';
 484
 485                                 $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">';
 486
 487                                 $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>';
 488
 489                                 // Pad the start time to not interfere with page timing
 490                                 $starttime += $time_db;
 491
 492                                 @ibase_freeresult($result);
 493                                 $cache_num_queries++;
 494                                 break;
 495
 496                         case 'stop':
 497                                 $endtime = explode(' ', microtime());
 498                                 $endtime = $endtime[0] + $endtime[1];
 499
 500                                 $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">';
 501
 502                                 if ($this->query_result)
 503                                 {
 504                                         if (preg_match('/^(UPDATE|DELETE|REPLACE)/', $query))
 505                                         {
 506                                                 $sql_report .= "Affected rows: <b>" . $this->sql_affectedrows($this->query_result) . '</b> | ';
 507                                         }
 508                                         $sql_report .= 'Before: ' . sprintf('%.5f', $curtime - $starttime) . 's | After: ' . sprintf('%.5f', $endtime - $starttime) . 's | Elapsed: <b>' . sprintf('%.5f', $endtime - $curtime) . 's</b>';
 509                                 }
 510                                 else
 511                                 {
 512                                         $error = $this->sql_error();
 513                                         $sql_report .= '<b style="color: red">FAILED</b> - ' . SQL_LAYER . ' Error ' . $error['code'] . ': ' . htmlspecialchars($error['message']);
 514                                 }
 515
 516                                 $sql_report .= '</p>';
 517
 518                                 $this->sql_time += $endtime - $curtime;
 519                                 break;
 520                 }
 521         }
 522
 523 } // class sql_db
 524
 525 } // if ... define
 526
 527 ?>