phpBB/includes/db/db2.php

   1 <?php
   2 /**
   3 *
   4 * @package dbal
   5 * @version $Id$
   6 * @copyright (c) 2009 phpBB Group
   7 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
   8 *
   9 */
  10
  11 /**
  12 * @ignore
  13 */
  14 if (!defined('IN_PHPBB'))
  15 {
  16         exit;
  17 }
  18
  19 /**
  20 * MSSQL Database Abstraction Layer
  21 * Minimum Requirement: DB2 8.2.2+
  22 * Minimum extension version: PECL ibm_db2 1.6.0+
  23 * @package dbal
  24 */
  25 class phpbb_dbal_db2 extends phpbb_dbal
  26 {
  27         /**
  28         * @var string Database type. No distinction between versions or used extensions.
  29         */
  30         public $dbms_type = 'db2';
  31
  32         /**
  33         * @var array Database type map, column layout information
  34         */
  35         public $dbms_type_map = array(
  36                 'INT:'          => 'integer',
  37                 'BINT'          => 'float',
  38                 'UINT'          => 'integer',
  39                 'UINT:'         => 'integer',
  40                 'TINT:'         => 'smallint',
  41                 'USINT'         => 'smallint',
  42                 'BOOL'          => 'smallint',
  43                 'VCHAR'         => 'varchar(255)',
  44                 'VCHAR:'        => 'varchar(%d)',
  45                 'CHAR:'         => 'char(%d)',
  46                 'XSTEXT'        => 'clob(65K)',
  47                 'STEXT'         => 'varchar(3000)',
  48                 'TEXT'          => 'clob(65K)',
  49                 'MTEXT'         => 'clob(16M)',
  50                 'XSTEXT_UNI'=> 'varchar(100)',
  51                 'STEXT_UNI'     => 'varchar(255)',
  52                 'TEXT_UNI'      => 'clob(65K)',
  53                 'MTEXT_UNI'     => 'clob(16M)',
  54                 'TIMESTAMP'     => 'integer',
  55                 'DECIMAL'       => 'float',
  56                 'VCHAR_UNI'     => 'varchar(255)',
  57                 'VCHAR_UNI:'=> 'varchar(%d)',
  58                 'VARBINARY'     => 'varchar(255)',
  59         );
  60
  61         /**
  62         * @var array Database features
  63         */
  64         public $features = array(
  65                 'multi_insert'                  => true,
  66                 'count_distinct'                => true,
  67                 'multi_table_deletion'  => true,
  68                 'truncate'                              => false,
  69         );
  70
  71         /**
  72         * Connect to server. See {@link phpbb_dbal::sql_connect() sql_connect()} for details.
  73         */
  74         public function sql_connect($server, $user, $password, $database, $port = false, $persistency = false , $new_link = false)
  75         {
  76                 $this->persistency = $persistency;
  77                 $this->user = $user;
  78                 $this->server = $server . (($port) ? ':' . $port : '');
  79                 $this->dbname = $database;
  80                 $this->port = $port;
  81
  82                 $this->db_connect_id = ($this->persistency) ? @db2_pconnect($this->dbname, $this->user, $password, array('autocommit' => DB2_AUTOCOMMIT_ON, 'DB2_ATTR_CASE' => DB2_CASE_LOWER)) : @db2_connect($this->dbname, $this->user, $password, array('autocommit' => DB2_AUTOCOMMIT_ON, 'DB2_ATTR_CASE' => DB2_CASE_LOWER));
  83
  84                 return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error('');
  85         }
  86
  87         /**
  88         * Version information about used database. See {@link phpbb_dbal::sql_server_info() sql_server_info()} for details.
  89         */
  90         public function sql_server_info($raw = false)
  91         {
  92                 if (!phpbb::registered('acm') || ($this->sql_server_version = phpbb::$acm->get('#db2_version')) === false)
  93                 {
  94                         $info = @db2_server_info($this->db_connect_id);
  95
  96                         $this->sql_server_info = is_object($info) ? $info->DBMS_VER : 0;
  97
  98                         if (phpbb::registered('acm'))
  99                         {
 100                                 phpbb::$acm->put('#db2_version', $this->sql_server_version);
 101                         }
 102                 }
 103
 104                 return ($raw) ? $this->sql_server_version : 'IBM DB2 ' . $this->sql_server_version;
 105         }
 106
 107         /**
 108         * DB-specific base query method. See {@link phpbb_dbal::_sql_query() _sql_query()} for details.
 109         */
 110         protected function _sql_query($query)
 111         {
 112                 $array = array();
 113
 114                 // Cope with queries larger than 32K
 115                 if (strlen($query) > 32740)
 116                 {
 117                         if (preg_match('/^(INSERT INTO[^(]++)\\(([^()]+)\\) VALUES[^(]++\\((.*?)\\)$/s', $query, $regs))
 118                         {
 119                                 if (strlen($regs[3]) > 32740)
 120                                 {
 121                                         preg_match_all('/\'(?:[^\']++|\'\')*+\'|[\d-.]+/', $regs[3], $vals, PREG_PATTERN_ORDER);
 122
 123                                         $inserts = $vals[0];
 124                                         unset($vals);
 125
 126                                         foreach ($inserts as $key => $value)
 127                                         {
 128                                                 // check to see if this thing is greater than the max + 'x2
 129                                                 if (!empty($value) && $value[0] === "'" && strlen($value) > 32742)
 130                                                 {
 131                                                         $inserts[$key] = '?';
 132                                                         $array[] = str_replace("''", "'", substr($value, 1, -1));
 133                                                 }
 134                                         }
 135
 136                                         $query = $regs[1] . '(' . $regs[2] . ') VALUES (' . implode(', ', $inserts) . ')';
 137                                 }
 138                         }
 139                         else if (preg_match_all('/^(UPDATE ([\\w_]++)\\s+SET )([\\w_]++\\s*=\\s*(?:\'(?:[^\']++|\'\')*+\'|[\d-.]+)(?:,\\s*[\\w_]++\\s*=\\s*(?:\'(?:[^\']++|\'\')*+\'|[\d-.]+))*+)\\s+(WHERE.*)$/s', $query, $data, PREG_SET_ORDER))
 140                         {
 141                                 if (strlen($data[0][3]) > 32740)
 142                                 {
 143                                         $update = $data[0][1];
 144                                         $where = $data[0][4];
 145                                         preg_match_all('/(\\w++) = (\'(?:[^\']++|\'\')*+\'|\\d++)/', $data[0][3], $temp, PREG_SET_ORDER);
 146                                         unset($data);
 147
 148                                         $cols = array();
 149                                         foreach ($temp as $value)
 150                                         {
 151                                                 // check to see if this thing is greater than the max + 'x2
 152                                                 if (!empty($value[2]) && $value[2][0] === "'" && strlen($value[2]) > 32742)
 153                                                 {
 154                                                         $array[] = str_replace("''", "'", substr($value[2], 1, -1));
 155                                                         $cols[] = $value[1] . '=?';
 156                                                 }
 157                                                 else
 158                                                 {
 159                                                         $cols[] = $value[1] . '=' . $value[2];
 160                                                 }
 161                                         }
 162
 163                                         $query = $update . implode(', ', $cols) . ' ' . $where;
 164                                         unset($cols);
 165                                 }
 166                         }
 167                 }
 168
 169                 if (sizeof($array))
 170                 {
 171                         $result = @db2_prepare($this->db_connect_id, $query);
 172
 173                         if (!$result)
 174                         {
 175                                 return false;
 176                         }
 177
 178                         if (!@db2_execute($result, $array))
 179                         {
 180                                 return false;
 181                         }
 182                 }
 183                 else
 184                 {
 185                         $result = @db2_exec($this->db_connect_id, $query);
 186                 }
 187
 188                 return $result;
 189         }
 190
 191         /**
 192         * Build LIMIT query and run it. See {@link phpbb_dbal::_sql_query_limit() _sql_query_limit()} for details.
 193         */
 194         protected function _sql_query_limit($query, $total, $offset, $cache_ttl)
 195         {
 196                 if ($total && $offset == 0)
 197                 {
 198                         return $this->sql_query($query . ' fetch first ' . $total . ' rows only', $cache_ttl);
 199                 }
 200
 201                 // Seek by $offset rows
 202                 if ($offset)
 203                 {
 204                         $limit_sql = 'SELECT a2.*
 205                                 FROM (
 206                                         SELECT ROW_NUMBER() OVER() AS rownum, a1.*
 207                                                 FROM (
 208                                                         ' . $query . '
 209                                                 ) a1
 210                                 ) a2
 211                         WHERE a2.rownum BETWEEN ' . ($offset + 1) . ' AND ' . ($offset + $total);
 212
 213                         return $this->sql_query($limit_sql, $cache_ttl);
 214                 }
 215
 216                 return $this->sql_query($query, $cache_ttl);
 217         }
 218
 219         /**
 220         * Close sql connection. See {@link phpbb_dbal::_sql_close() _sql_close()} for details.
 221         */
 222         protected function _sql_close()
 223         {
 224                 return @db2_close($this->db_connect_id);
 225         }
 226
 227         /**
 228         * SQL Transaction. See {@link phpbb_dbal::_sql_transaction() _sql_transaction()} for details.
 229         */
 230         protected function _sql_transaction($status)
 231         {
 232                 switch ($status)
 233                 {
 234                         case 'begin':
 235                                 return @db2_autocommit($this->db_connect_id, DB2_AUTOCOMMIT_OFF);
 236                         break;
 237
 238                         case 'commit':
 239                                 $result = @db2_commit($this->db_connect_id);
 240                                 @db2_autocommit($this->db_connect_id, DB2_AUTOCOMMIT_ON);
 241                                 return $result;
 242                         break;
 243
 244                         case 'rollback':
 245                                 $result = @db2_rollback($this->db_connect_id);
 246                                 @db2_autocommit($this->db_connect_id, DB2_AUTOCOMMIT_ON);
 247                                 return $result;
 248                         break;
 249                 }
 250
 251                 return true;
 252         }
 253
 254         /**
 255         * Return number of affected rows. See {@link phpbb_dbal::sql_affectedrows() sql_affectedrows()} for details.
 256         */
 257         public function sql_affectedrows()
 258         {
 259                 return ($this->db_connect_id) ? @db2_num_rows($this->db_connect_id) : false;
 260         }
 261
 262         /**
 263         * Get last inserted id after insert statement. See {@link phpbb_dbal::sql_nextid() sql_nextid()} for details.
 264         */
 265         public function sql_nextid()
 266         {
 267                 if (function_exists('db2_last_insert_id'))
 268                 {
 269                         return @db2_last_insert_id($this->db_connect_id);
 270                 }
 271
 272                 $result_id = @db2_exec($this->db_connect_id, 'VALUES IDENTITY_VAL_LOCAL()');
 273
 274                 if ($result_id)
 275                 {
 276                         if ($row = @db2_fetch_assoc($result_id))
 277                         {
 278                                 @db2_free_result($result_id);
 279                                 return (int) $row[1];
 280                         }
 281                         @db2_free_result($result_id);
 282                 }
 283
 284                 return false;
 285         }
 286
 287         /**
 288         * Fetch current row. See {@link phpbb_dbal::_sql_fetchrow() _sql_fetchrow()} for details.
 289         */
 290         protected function _sql_fetchrow($query_id)
 291         {
 292                 return @db2_fetch_assoc($query_id);
 293         }
 294
 295         /**
 296         * Free query result. See {@link phpbb_dbal::_sql_freeresult() _sql_freeresult()} for details.
 297         */
 298         protected function _sql_freeresult($query_id)
 299         {
 300                 return @db2_free_result($query_id);
 301         }
 302
 303         /**
 304         * Correctly adjust LIKE expression for special characters. See {@link phpbb_dbal::_sql_like_expression() _sql_like_expression()} for details.
 305         */
 306         protected function _sql_like_expression($expression)
 307         {
 308                 return $expression . " ESCAPE '\\'";
 309         }
 310
 311         /**
 312         * Escape string used in sql query. See {@link phpbb_dbal::sql_escape() sql_escape()} for details.
 313         */
 314         public function sql_escape($msg)
 315         {
 316                 return @db2_escape_string($msg);
 317         }
 318
 319         /**
 320         * Expose a DBMS specific function. See {@link phpbb_dbal::sql_function() sql_function()} for details.
 321         */
 322         public function sql_function($type, $col)
 323         {
 324                 switch ($type)
 325                 {
 326                         case 'length_varchar':
 327                         case 'length_text':
 328                                 return 'LENGTH(' . $col . ')';
 329                         break;
 330                 }
 331         }
 332
 333         /**
 334         * Handle data by using prepared statements. See {@link phpbb_dbal::sql_handle_data() sql_handle_data()} for details.
 335         public function sql_handle_data($type, $table, $data, $where = '')
 336         {
 337                 if ($type == 'INSERT')
 338                 {
 339                         $stmt = db2_prepare($this->db_connect_id, "INSERT INTO $table (". implode(', ', array_keys($data)) . ") VALUES (" . substr(str_repeat('?, ', sizeof($data)) ,0, -1) . ')');
 340                 }
 341                 else
 342                 {
 343                         $query = "UPDATE $table SET ";
 344
 345                         $set = array();
 346                         foreach (array_keys($data) as $key)
 347                         {
 348                                 $set[] = "$key = ?";
 349                         }
 350                         $query .= implode(', ', $set);
 351
 352                         if ($where !== '')
 353                         {
 354                                 $query .= $where;
 355                         }
 356
 357                         $stmt = db2_prepare($this->db_connect_id, $query);
 358                 }
 359
 360                 // get the stmt onto the top of the function arguments
 361                 array_unshift($data, $stmt);
 362
 363                 call_user_func_array('db2_execute', $data);
 364         }
 365         */
 366
 367         /**
 368         * Build DB-specific query bits. See {@link phpbb_dbal::_sql_custom_build() _sql_custom_build()} for details.
 369         */
 370         protected function _sql_custom_build($stage, $data)
 371         {
 372                 return $data;
 373         }
 374
 375         /**
 376         * return sql error array. See {@link phpbb_dbal::_sql_error() _sql_error()} for details.
 377         */
 378         protected function _sql_error()
 379         {
 380                 $message = @db2_stmt_errormsg();
 381                 $code = @db2_stmt_error();
 382
 383                 if (!$message && !$code)
 384                 {
 385                         $message = @db2_conn_errormsg();
 386                         $code = @db2_conn_error();
 387                 }
 388
 389                 $error = array(
 390                         'message'       => $message,
 391                         'code'          => $code,
 392                 );
 393
 394                 return $error;
 395         }
 396
 397         /**
 398         * Run DB-specific code to build SQL Report to explain queries, show statistics and runtime information. See {@link phpbb_dbal::_sql_report() _sql_report()} for details.
 399         */
 400         protected function _sql_report($mode, $query = '')
 401         {
 402                 switch ($mode)
 403                 {
 404                         case 'start':
 405
 406                                 $html_table = false;
 407                                 @db2_exec($this->db_connect_id, 'DELETE FROM EXPLAIN_INSTANCE');
 408                                 @db2_exec($this->db_connect_id, 'EXPLAIN PLAN FOR ' . $query);
 409
 410                                 // Get the data from the plan
 411                                 $sql = "SELECT O.Operator_ID, S2.Target_ID, O.Operator_Type, S.Object_Name, CAST(O.Total_Cost AS INTEGER) Cost
 412                                         FROM EXPLAIN_OPERATOR O
 413                                         LEFT OUTER JOIN EXPLAIN_STREAM S2 ON O.Operator_ID = S2.Source_ID
 414                                         LEFT OUTER JOIN EXPLAIN_STREAM S ON O.Operator_ID = S.Target_ID AND O.Explain_Time = S.Explain_Time AND S.Object_Name IS NOT NULL
 415                                         ORDER BY O.Explain_Time ASC, Operator_ID ASC";
 416                                 $query_id = @db2_exec($this->db_connect_id, $sql);
 417
 418                                 if ($query_id)
 419                                 {
 420                                         while ($row = @db2_fetch_assoc($query_id))
 421                                         {
 422                                                 $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 423                                         }
 424
 425                                         @db2_free_result($query_id);
 426                                 }
 427
 428                                 if ($html_table)
 429                                 {
 430                                         $this->html_hold .= '</table>';
 431                                 }
 432                         break;
 433
 434                         case 'fromcache':
 435                                 $endtime = explode(' ', microtime());
 436                                 $endtime = $endtime[0] + $endtime[1];
 437
 438                                 $result = @db2_exec($this->db_connect_id, $query);
 439                                 while ($void = @db2_fetch_assoc($result, IBASE_TEXT))
 440                                 {
 441                                         // Take the time spent on parsing rows into account
 442                                 }
 443                                 @db2_free_result($result);
 444
 445                                 $splittime = explode(' ', microtime());
 446                                 $splittime = $splittime[0] + $splittime[1];
 447
 448                                 $this->sql_report('record_fromcache', $query, $endtime, $splittime);
 449
 450                         break;
 451                 }
 452         }
 453 }
 454
 455 ?>