source: trunk/forum/includes/db/firebird.php

Last change on this file was 702, checked in by george, 16 years ago
  • Upraveno: Aktualizace fóra.
File size: 11.7 KB
Line 
1<?php
2/**
3*
4* @package dbal
5* @version $Id$
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*/
14if (!defined('IN_PHPBB'))
15{
16 exit;
17}
18
19include_once($phpbb_root_path . 'includes/db/dbal.' . $phpEx);
20
21/**
22* Firebird/Interbase Database Abstraction Layer
23* Minimum Requirement is Firebird 2.1
24* @package dbal
25*/
26class dbal_firebird extends dbal
27{
28 var $last_query_text = '';
29 var $service_handle = false;
30 var $affected_rows = 0;
31
32 /**
33 * Connect to server
34 */
35 function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false, $new_link = false)
36 {
37 $this->persistency = $persistency;
38 $this->user = $sqluser;
39 $this->server = $sqlserver . (($port) ? ':' . $port : '');
40 $this->dbname = str_replace('\\', '/', $database);
41
42 // There are three possibilities to connect to an interbase db
43 if (!$this->server)
44 {
45 $use_database = $this->dbname;
46 }
47 else if (strpos($this->server, '//') === 0)
48 {
49 $use_database = $this->server . $this->dbname;
50 }
51 else
52 {
53 $use_database = $this->server . ':' . $this->dbname;
54 }
55
56 $this->db_connect_id = ($this->persistency) ? @ibase_pconnect($use_database, $this->user, $sqlpassword, false, false, 3) : @ibase_connect($use_database, $this->user, $sqlpassword, false, false, 3);
57
58 $this->service_handle = (function_exists('ibase_service_attach') && $this->server) ? @ibase_service_attach($this->server, $this->user, $sqlpassword) : false;
59
60 return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error('');
61 }
62
63 /**
64 * Version information about used database
65 * @param bool $raw if true, only return the fetched sql_server_version
66 * @return string sql server version
67 */
68 function sql_server_info($raw = false)
69 {
70 if ($this->service_handle !== false && function_exists('ibase_server_info'))
71 {
72 return @ibase_server_info($this->service_handle, IBASE_SVC_SERVER_VERSION);
73 }
74
75 return ($raw) ? '2.1' : 'Firebird/Interbase';
76 }
77
78 /**
79 * SQL Transaction
80 * @access private
81 */
82 function _sql_transaction($status = 'begin')
83 {
84 switch ($status)
85 {
86 case 'begin':
87 return true;
88 break;
89
90 case 'commit':
91 return @ibase_commit();
92 break;
93
94 case 'rollback':
95 return @ibase_rollback();
96 break;
97 }
98
99 return true;
100 }
101
102 /**
103 * Base query method
104 *
105 * @param string $query Contains the SQL query which shall be executed
106 * @param int $cache_ttl Either 0 to avoid caching or the time in seconds which the result shall be kept in cache
107 * @return mixed When casted to bool the returned value returns true on success and false on failure
108 *
109 * @access public
110 */
111 function sql_query($query = '', $cache_ttl = 0)
112 {
113 if ($query != '')
114 {
115 global $cache;
116
117 // EXPLAIN only in extra debug mode
118 if (defined('DEBUG_EXTRA'))
119 {
120 $this->sql_report('start', $query);
121 }
122
123 $this->last_query_text = $query;
124 $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
125 $this->sql_add_num_queries($this->query_result);
126
127 if ($this->query_result === false)
128 {
129 $array = array();
130 // We overcome Firebird's 32767 char limit by binding vars
131 if (strlen($query) > 32767)
132 {
133 if (preg_match('/^(INSERT INTO[^(]++)\\(([^()]+)\\) VALUES[^(]++\\((.*?)\\)$/s', $query, $regs))
134 {
135 if (strlen($regs[3]) > 32767)
136 {
137 preg_match_all('/\'(?:[^\']++|\'\')*+\'|[\d-.]+/', $regs[3], $vals, PREG_PATTERN_ORDER);
138
139 $inserts = $vals[0];
140 unset($vals);
141
142 foreach ($inserts as $key => $value)
143 {
144 if (!empty($value) && $value[0] === "'" && strlen($value) > 32769) // check to see if this thing is greater than the max + 'x2
145 {
146 $inserts[$key] = '?';
147 $array[] = str_replace("''", "'", substr($value, 1, -1));
148 }
149 }
150
151 $query = $regs[1] . '(' . $regs[2] . ') VALUES (' . implode(', ', $inserts) . ')';
152 }
153 }
154 else if (preg_match('/^(UPDATE ([\\w_]++)\\s+SET )([\\w_]++\\s*=\\s*(?:\'(?:[^\']++|\'\')*+\'|\\d+)(?:,\\s*[\\w_]++\\s*=\\s*(?:\'(?:[^\']++|\'\')*+\'|[\d-.]+))*+)\\s+(WHERE.*)$/s', $query, $data))
155 {
156 if (strlen($data[3]) > 32767)
157 {
158 $update = $data[1];
159 $where = $data[4];
160 preg_match_all('/(\\w++)\\s*=\\s*(\'(?:[^\']++|\'\')*+\'|[\d-.]++)/', $data[3], $temp, PREG_SET_ORDER);
161 unset($data);
162
163 $cols = array();
164 foreach ($temp as $value)
165 {
166 if (!empty($value[2]) && $value[2][0] === "'" && strlen($value[2]) > 32769) // check to see if this thing is greater than the max + 'x2
167 {
168 $array[] = str_replace("''", "'", substr($value[2], 1, -1));
169 $cols[] = $value[1] . '=?';
170 }
171 else
172 {
173 $cols[] = $value[1] . '=' . $value[2];
174 }
175 }
176
177 $query = $update . implode(', ', $cols) . ' ' . $where;
178 unset($cols);
179 }
180 }
181 }
182
183 if (!function_exists('ibase_affected_rows') && (preg_match('/^UPDATE ([\w_]++)\s+SET [\w_]++\s*=\s*(?:\'(?:[^\']++|\'\')*+\'|[\d-.]+)(?:,\s*[\w_]++\s*=\s*(?:\'(?:[^\']++|\'\')*+\'|[\d-.]+))*+\s+(WHERE.*)?$/s', $query, $regs) || preg_match('/^DELETE FROM ([\w_]++)\s*(WHERE\s*.*)?$/s', $query, $regs)))
184 {
185 $affected_sql = 'SELECT COUNT(*) as num_rows_affected FROM ' . $regs[1];
186 if (!empty($regs[2]))
187 {
188 $affected_sql .= ' ' . $regs[2];
189 }
190
191 if (!($temp_q_id = @ibase_query($this->db_connect_id, $affected_sql)))
192 {
193 return false;
194 }
195
196 $temp_result = @ibase_fetch_assoc($temp_q_id);
197 @ibase_free_result($temp_q_id);
198
199 $this->affected_rows = ($temp_result) ? $temp_result['NUM_ROWS_AFFECTED'] : false;
200 }
201
202 if (sizeof($array))
203 {
204 $p_query = @ibase_prepare($this->db_connect_id, $query);
205 array_unshift($array, $p_query);
206 $this->query_result = call_user_func_array('ibase_execute', $array);
207 unset($array);
208
209 if ($this->query_result === false)
210 {
211 $this->sql_error($query);
212 }
213 }
214 else if (($this->query_result = @ibase_query($this->db_connect_id, $query)) === false)
215 {
216 $this->sql_error($query);
217 }
218
219 if (defined('DEBUG_EXTRA'))
220 {
221 $this->sql_report('stop', $query);
222 }
223
224 if (!$this->transaction)
225 {
226 if (function_exists('ibase_commit_ret'))
227 {
228 @ibase_commit_ret();
229 }
230 else
231 {
232 // way cooler than ibase_commit_ret :D
233 @ibase_query('COMMIT RETAIN;');
234 }
235 }
236
237 if ($cache_ttl && method_exists($cache, 'sql_save'))
238 {
239 $this->open_queries[(int) $this->query_result] = $this->query_result;
240 $cache->sql_save($query, $this->query_result, $cache_ttl);
241 }
242 else if (strpos($query, 'SELECT') === 0 && $this->query_result)
243 {
244 $this->open_queries[(int) $this->query_result] = $this->query_result;
245 }
246 }
247 else if (defined('DEBUG_EXTRA'))
248 {
249 $this->sql_report('fromcache', $query);
250 }
251 }
252 else
253 {
254 return false;
255 }
256
257 return $this->query_result;
258 }
259
260 /**
261 * Build LIMIT query
262 */
263 function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
264 {
265 $this->query_result = false;
266
267 $query = 'SELECT FIRST ' . $total . ((!empty($offset)) ? ' SKIP ' . $offset : '') . substr($query, 6);
268
269 return $this->sql_query($query, $cache_ttl);
270 }
271
272 /**
273 * Return number of affected rows
274 */
275 function sql_affectedrows()
276 {
277 // PHP 5+ function
278 if (function_exists('ibase_affected_rows'))
279 {
280 return ($this->db_connect_id) ? @ibase_affected_rows($this->db_connect_id) : false;
281 }
282 else
283 {
284 return $this->affected_rows;
285 }
286 }
287
288 /**
289 * Fetch current row
290 */
291 function sql_fetchrow($query_id = false)
292 {
293 global $cache;
294
295 if ($query_id === false)
296 {
297 $query_id = $this->query_result;
298 }
299
300 if (isset($cache->sql_rowset[$query_id]))
301 {
302 return $cache->sql_fetchrow($query_id);
303 }
304
305 if ($query_id === false)
306 {
307 return false;
308 }
309
310 $row = array();
311 $cur_row = @ibase_fetch_object($query_id, IBASE_TEXT);
312
313 if (!$cur_row)
314 {
315 return false;
316 }
317
318 foreach (get_object_vars($cur_row) as $key => $value)
319 {
320 $row[strtolower($key)] = (is_string($value)) ? trim(str_replace(array("\\0", "\\n"), array("\0", "\n"), $value)) : $value;
321 }
322
323 return (sizeof($row)) ? $row : false;
324 }
325
326 /**
327 * Seek to given row number
328 * rownum is zero-based
329 */
330 function sql_rowseek($rownum, &$query_id)
331 {
332 global $cache;
333
334 if ($query_id === false)
335 {
336 $query_id = $this->query_result;
337 }
338
339 if (isset($cache->sql_rowset[$query_id]))
340 {
341 return $cache->sql_rowseek($rownum, $query_id);
342 }
343
344 if ($query_id === false)
345 {
346 return;
347 }
348
349 $this->sql_freeresult($query_id);
350 $query_id = $this->sql_query($this->last_query_text);
351
352 if ($query_id === false)
353 {
354 return false;
355 }
356
357 // We do not fetch the row for rownum == 0 because then the next resultset would be the second row
358 for ($i = 0; $i < $rownum; $i++)
359 {
360 if (!$this->sql_fetchrow($query_id))
361 {
362 return false;
363 }
364 }
365
366 return true;
367 }
368
369 /**
370 * Get last inserted id after insert statement
371 */
372 function sql_nextid()
373 {
374 $query_id = $this->query_result;
375
376 if ($query_id !== false && $this->last_query_text != '')
377 {
378 if ($this->query_result && preg_match('#^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)#i', $this->last_query_text, $tablename))
379 {
380 $sql = 'SELECT GEN_ID(' . $tablename[1] . '_gen, 0) AS new_id FROM RDB$DATABASE';
381
382 if (!($temp_q_id = @ibase_query($this->db_connect_id, $sql)))
383 {
384 return false;
385 }
386
387 $temp_result = @ibase_fetch_assoc($temp_q_id);
388 @ibase_free_result($temp_q_id);
389
390 return ($temp_result) ? $temp_result['NEW_ID'] : false;
391 }
392 }
393
394 return false;
395 }
396
397 /**
398 * Free sql result
399 */
400 function sql_freeresult($query_id = false)
401 {
402 global $cache;
403
404 if ($query_id === false)
405 {
406 $query_id = $this->query_result;
407 }
408
409 if (isset($cache->sql_rowset[$query_id]))
410 {
411 return $cache->sql_freeresult($query_id);
412 }
413
414 if (isset($this->open_queries[(int) $query_id]))
415 {
416 unset($this->open_queries[(int) $query_id]);
417 return @ibase_free_result($query_id);
418 }
419
420 return false;
421 }
422
423 /**
424 * Escape string used in sql query
425 */
426 function sql_escape($msg)
427 {
428 return str_replace(array("'", "\0"), array("''", ''), $msg);
429 }
430
431 /**
432 * Build LIKE expression
433 * @access private
434 */
435 function _sql_like_expression($expression)
436 {
437 return $expression . " ESCAPE '\\'";
438 }
439
440 /**
441 * Build db-specific query data
442 * @access private
443 */
444 function _sql_custom_build($stage, $data)
445 {
446 return $data;
447 }
448
449 function _sql_bit_and($column_name, $bit, $compare = '')
450 {
451 return 'BIN_AND(' . $column_name . ', ' . (1 << $bit) . ')' . (($compare) ? ' ' . $compare : '');
452 }
453
454 function _sql_bit_or($column_name, $bit, $compare = '')
455 {
456 return 'BIN_OR(' . $column_name . ', ' . (1 << $bit) . ')' . (($compare) ? ' ' . $compare : '');
457 }
458
459 /**
460 * return sql error array
461 * @access private
462 */
463 function _sql_error()
464 {
465 return array(
466 'message' => @ibase_errmsg(),
467 'code' => (@function_exists('ibase_errcode') ? @ibase_errcode() : '')
468 );
469 }
470
471 /**
472 * Close sql connection
473 * @access private
474 */
475 function _sql_close()
476 {
477 if ($this->service_handle !== false)
478 {
479 @ibase_service_detach($this->service_handle);
480 }
481
482 return @ibase_close($this->db_connect_id);
483 }
484
485 /**
486 * Build db-specific report
487 * @access private
488 */
489 function _sql_report($mode, $query = '')
490 {
491 switch ($mode)
492 {
493 case 'start':
494 break;
495
496 case 'fromcache':
497 $endtime = explode(' ', microtime());
498 $endtime = $endtime[0] + $endtime[1];
499
500 $result = @ibase_query($this->db_connect_id, $query);
501 while ($void = @ibase_fetch_object($result, IBASE_TEXT))
502 {
503 // Take the time spent on parsing rows into account
504 }
505 @ibase_free_result($result);
506
507 $splittime = explode(' ', microtime());
508 $splittime = $splittime[0] + $splittime[1];
509
510 $this->sql_report('record_fromcache', $query, $endtime, $splittime);
511
512 break;
513 }
514 }
515}
516
517?>
Note: See TracBrowser for help on using the repository browser.