Verzeichnisstruktur phpBB-3.0.0
- Veröffentlicht
- 12.12.2007
So funktioniert es
|
|
Auf das letzte Element klicken. Dies geht jeweils ein Schritt zurück |
Auf das Icon klicken, dies öffnet das Verzeichnis. Nochmal klicken schließt das Verzeichnis. |
|
|
(Beispiel Datei-Icons)
|
Auf das Icon klicken um den Quellcode anzuzeigen |
oracle.php
001 <?php
002 /**
003 *
004 * @package dbal
005 * @version $Id$
006 * @copyright (c) 2005 phpBB Group
007 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
008 *
009 */
010
011 /**
012 * @ignore
013 */
014 if (!defined('IN_PHPBB'))
015 {
016 exit;
017 }
018
019 include_once($phpbb_root_path . 'includes/db/dbal.' . $phpEx);
020
021 /**
022 * Oracle Database Abstraction Layer
023 * @package dbal
024 */
025 class dbal_oracle extends dbal
026 {
027 var $last_query_text = '';
028
029 /**
030 * Connect to server
031 */
032 function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false, $new_link = false)
033 {
034 $this->persistency = $persistency;
035 $this->user = $sqluser;
036 $this->server = $sqlserver . (($port) ? ':' . $port : '');
037 $this->dbname = $database;
038
039 $connect = $database;
040
041 // support for "easy connect naming"
042 if ($sqlserver !== '' && $sqlserver !== '/')
043 {
044 if (substr($sqlserver, -1, 1) == '/')
045 {
046 $sqlserver == substr($sqlserver, 0, -1);
047 }
048 $connect = $sqlserver . (($port) ? ':' . $port : '') . '/' . $database;
049 }
050
051 $this->db_connect_id = ($new_link) ? @ocinlogon($this->user, $sqlpassword, $connect, 'UTF8') : (($this->persistency) ? @ociplogon($this->user, $sqlpassword, $connect, 'UTF8') : @ocilogon($this->user, $sqlpassword, $connect, 'UTF8'));
052
053 return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error('');
054 }
055
056 /**
057 * Version information about used database
058 */
059 function sql_server_info()
060 {
061 return @ociserverversion($this->db_connect_id);
062 }
063
064 /**
065 * SQL Transaction
066 * @access private
067 */
068 function _sql_transaction($status = 'begin')
069 {
070 switch ($status)
071 {
072 case 'begin':
073 return true;
074 break;
075
076 case 'commit':
077 return @ocicommit($this->db_connect_id);
078 break;
079
080 case 'rollback':
081 return @ocirollback($this->db_connect_id);
082 break;
083 }
084
085 return true;
086 }
087
088 /**
089 * Oracle specific code to handle the fact that it does not compare columns properly
090 * @access private
091 */
092 function _rewrite_col_compare($args)
093 {
094 if (sizeof($args) == 4)
095 {
096 if ($args[2] == '=')
097 {
098 return '(' . $args[0] . ' OR (' . $args[1] . ' is NULL AND ' . $args[3] . ' is NULL))';
099 }
100 else if ($args[2] == '<>')
101 {
102 // really just a fancy way of saying foo <> bar or (foo is NULL XOR bar is NULL) but SQL has no XOR :P
103 return '(' . $args[0] . ' OR ((' . $args[1] . ' is NULL AND ' . $args[3] . ' is NOT NULL) OR (' . $args[1] . ' is NOT NULL AND ' . $args[3] . ' is NULL)))';
104 }
105 }
106 else
107 {
108 return $this->_rewrite_where($args[0]);
109 }
110 }
111
112 /**
113 * Oracle specific code to handle it's lack of sanity
114 * @access private
115 */
116 function _rewrite_where($where_clause)
117 {
118 preg_match_all('/\s*(AND|OR)?\s*([\w_.]++)\s*(?:(=|<[=>]?|>=?)\s*((?>\'(?>[^\']++|\'\')*+\'|[\d-.]+))|((NOT )?IN\s*\((?>\'(?>[^\']++|\'\')*+\',? ?|[\d-.]+,? ?)*+\)))/', $where_clause, $result, PREG_SET_ORDER);
119 $out = '';
120 foreach ($result as $val)
121 {
122 if (!isset($val[5]))
123 {
124 if ($val[4] !== "''")
125 {
126 $out .= $val[0];
127 }
128 else
129 {
130 $out .= ' ' . $val[1] . ' ' . $val[2];
131 if ($val[3] == '=')
132 {
133 $out .= ' is NULL';
134 }
135 else if ($val[3] == '<>')
136 {
137 $out .= ' is NOT NULL';
138 }
139 }
140 }
141 else
142 {
143 $in_clause = array();
144 $sub_exp = substr($val[5], strpos($val[5], '(') + 1, -1);
145 $extra = false;
146 preg_match_all('/\'(?>[^\']++|\'\')*+\'|[\d-.]++/', $sub_exp, $sub_vals, PREG_PATTERN_ORDER);
147 $i = 0;
148 foreach ($sub_vals[0] as $sub_val)
149 {
150 // two things:
151 // 1) This determines if an empty string was in the IN clausing, making us turn it into a NULL comparison
152 // 2) This fixes the 1000 list limit that Oracle has (ORA-01795)
153 if ($sub_val !== "''")
154 {
155 $in_clause[(int) $i++/1000][] = $sub_val;
156 }
157 else
158 {
159 $extra = true;
160 }
161 }
162 if (!$extra && $i < 1000)
163 {
164 $out .= $val[0];
165 }
166 else
167 {
168 $out .= ' ' . $val[1] . '(';
169 $in_array = array();
170
171 // constuct each IN() clause
172 foreach ($in_clause as $in_values)
173 {
174 $in_array[] = $val[2] . ' ' . (isset($val[6]) ? $val[6] : '') . 'IN(' . implode(', ', $in_values) . ')';
175 }
176
177 // Join the IN() clauses against a few ORs (IN is just a nicer OR anyway)
178 $out .= implode(' OR ', $in_array);
179
180 // handle the empty string case
181 if ($extra)
182 {
183 $out .= ' OR ' . $val[2] . ' is ' . (isset($val[6]) ? $val[6] : '') . 'NULL';
184 }
185 $out .= ')';
186
187 unset($in_array, $in_clause);
188 }
189 }
190 }
191
192 return $out;
193 }
194
195 /**
196 * Base query method
197 *
198 * @param string $query Contains the SQL query which shall be executed
199 * @param int $cache_ttl Either 0 to avoid caching or the time in seconds which the result shall be kept in cache
200 * @return mixed When casted to bool the returned value returns true on success and false on failure
201 *
202 * @access public
203 */
204 function sql_query($query = '', $cache_ttl = 0)
205 {
206 if ($query != '')
207 {
208 global $cache;
209
210 // EXPLAIN only in extra debug mode
211 if (defined('DEBUG_EXTRA'))
212 {
213 $this->sql_report('start', $query);
214 }
215
216 $this->last_query_text = $query;
217 $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
218 $this->sql_add_num_queries($this->query_result);
219
220 if ($this->query_result === false)
221 {
222 $in_transaction = false;
223 if (!$this->transaction)
224 {
225 $this->sql_transaction('begin');
226 }
227 else
228 {
229 $in_transaction = true;
230 }
231
232 $array = array();
233
234 // We overcome Oracle's 4000 char limit by binding vars
235 if (strlen($query) > 4000)
236 {
237 if (preg_match('/^(INSERT INTO[^(]++)\\(([^()]+)\\) VALUES[^(]++\\((.*?)\\)$/s', $query, $regs))
238 {
239 if (strlen($regs[3]) > 4000)
240 {
241 $cols = explode(', ', $regs[2]);
242 preg_match_all('/\'(?:[^\']++|\'\')*+\'|[\d-.]+/', $regs[3], $vals, PREG_PATTERN_ORDER);
243
244 $inserts = $vals[0];
245 unset($vals);
246
247 foreach ($inserts as $key => $value)
248 {
249 if (!empty($value) && $value[0] === "'" && strlen($value) > 4002) // check to see if this thing is greater than the max + 'x2
250 {
251 $inserts[$key] = ':' . strtoupper($cols[$key]);
252 $array[$inserts[$key]] = str_replace("''", "'", substr($value, 1, -1));
253 }
254 }
255
256 $query = $regs[1] . '(' . $regs[2] . ') VALUES (' . implode(', ', $inserts) . ')';
257 }
258 }
259 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))
260 {
261 if (strlen($data[0][2]) > 4000)
262 {
263 $update = $data[0][1];
264 $where = $data[0][3];
265 preg_match_all('/([\\w_]++)\\s*=\\s*(\'(?:[^\']++|\'\')*+\'|[\d-.]++)/', $data[0][2], $temp, PREG_SET_ORDER);
266 unset($data);
267
268 $cols = array();
269 foreach ($temp as $value)
270 {
271 if (!empty($value[2]) && $value[2][0] === "'" && strlen($value[2]) > 4002) // check to see if this thing is greater than the max + 'x2
272 {
273 $cols[] = $value[1] . '=:' . strtoupper($value[1]);
274 $array[$value[1]] = str_replace("''", "'", substr($value[2], 1, -1));
275 }
276 else
277 {
278 $cols[] = $value[1] . '=' . $value[2];
279 }
280 }
281
282 $query = $update . implode(', ', $cols) . ' ' . $where;
283 unset($cols);
284 }
285 }
286 }
287
288 switch (substr($query, 0, 6))
289 {
290 case 'DELETE':
291 if (preg_match('/^(DELETE FROM [\w_]++ WHERE)((?:\s*(?:AND|OR)?\s*[\w_]+\s*(?:(?:=|<>)\s*(?>\'(?>[^\']++|\'\')*+\'|[\d-.]+)|(?:NOT )?IN\s*\((?>\'(?>[^\']++|\'\')*+\',? ?|[\d-.]+,? ?)*+\)))*+)$/', $query, $regs))
292 {
293 $query = $regs[1] . $this->_rewrite_where($regs[2]);
294 unset($regs);
295 }
296 break;
297
298 case 'UPDATE':
299 if (preg_match('/^(UPDATE [\\w_]++\\s+SET [\\w_]+\s*=\s*(?:\'(?:[^\']++|\'\')*+\'|[\d-.]++|:\w++)(?:, [\\w_]+\s*=\s*(?:\'(?:[^\']++|\'\')*+\'|[\d-.]++|:\w++))*+\\s+WHERE)(.*)$/s', $query, $regs))
300 {
301 $query = $regs[1] . $this->_rewrite_where($regs[2]);
302 unset($regs);
303 }
304 break;
305
306 case 'SELECT':
307 $query = preg_replace_callback('/([\w_.]++)\s*(?:(=|<>)\s*(?>\'(?>[^\']++|\'\')*+\'|[\d-.]++|([\w_.]++))|(?:NOT )?IN\s*\((?>\'(?>[^\']++|\'\')*+\',? ?|[\d-.]++,? ?)*+\))/', array($this, '_rewrite_col_compare'), $query);
308 break;
309 }
310
311 $this->query_result = @ociparse($this->db_connect_id, $query);
312
313 foreach ($array as $key => $value)
314 {
315 @ocibindbyname($this->query_result, $key, $array[$key], -1);
316 }
317
318 $success = @ociexecute($this->query_result, OCI_DEFAULT);
319
320 if (!$success)
321 {
322 $this->sql_error($query);
323 $this->query_result = false;
324 }
325 else
326 {
327 if (!$in_transaction)
328 {
329 $this->sql_transaction('commit');
330 }
331 }
332
333 if (defined('DEBUG_EXTRA'))
334 {
335 $this->sql_report('stop', $query);
336 }
337
338 if ($cache_ttl && method_exists($cache, 'sql_save'))
339 {
340 $this->open_queries[(int) $this->query_result] = $this->query_result;
341 $cache->sql_save($query, $this->query_result, $cache_ttl);
342 }
343 else if (strpos($query, 'SELECT') === 0 && $this->query_result)
344 {
345 $this->open_queries[(int) $this->query_result] = $this->query_result;
346 }
347 }
348 else if (defined('DEBUG_EXTRA'))
349 {
350 $this->sql_report('fromcache', $query);
351 }
352 }
353 else
354 {
355 return false;
356 }
357
358 return ($this->query_result) ? $this->query_result : false;
359 }
360
361 /**
362 * Build LIMIT query
363 */
364 function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
365 {
366 $this->query_result = false;
367
368 $query = 'SELECT * FROM (SELECT /*+ FIRST_ROWS */ rownum AS xrownum, a.* FROM (' . $query . ') a WHERE rownum <= ' . ($offset + $total) . ') WHERE xrownum >= ' . $offset;
369
370 return $this->sql_query($query, $cache_ttl);
371 }
372
373 /**
374 * Return number of affected rows
375 */
376 function sql_affectedrows()
377 {
378 return ($this->query_result) ? @ocirowcount($this->query_result) : false;
379 }
380
381 /**
382 * Fetch current row
383 */
384 function sql_fetchrow($query_id = false)
385 {
386 global $cache;
387
388 if ($query_id === false)
389 {
390 $query_id = $this->query_result;
391 }
392
393 if (isset($cache->sql_rowset[$query_id]))
394 {
395 return $cache->sql_fetchrow($query_id);
396 }
397
398 if ($query_id !== false)
399 {
400 $row = array();
401 $result = @ocifetchinto($query_id, $row, OCI_ASSOC + OCI_RETURN_NULLS);
402
403 if (!$result || !$row)
404 {
405 return false;
406 }
407
408 $result_row = array();
409 foreach ($row as $key => $value)
410 {
411 // Oracle treats empty strings as null
412 if (is_null($value))
413 {
414 $value = '';
415 }
416
417 // OCI->CLOB?
418 if (is_object($value))
419 {
420 $value = $value->load();
421 }
422
423 $result_row[strtolower($key)] = $value;
424 }
425
426 return $result_row;
427 }
428
429 return false;
430 }
431
432 /**
433 * Seek to given row number
434 * rownum is zero-based
435 */
436 function sql_rowseek($rownum, &$query_id)
437 {
438 global $cache;
439
440 if ($query_id === false)
441 {
442 $query_id = $this->query_result;
443 }
444
445 if (isset($cache->sql_rowset[$query_id]))
446 {
447 return $cache->sql_rowseek($rownum, $query_id);
448 }
449
450 if ($query_id === false)
451 {
452 return false;
453 }
454
455 // Reset internal pointer
456 @ociexecute($query_id, OCI_DEFAULT);
457
458 // We do not fetch the row for rownum == 0 because then the next resultset would be the second row
459 for ($i = 0; $i < $rownum; $i++)
460 {
461 if (!$this->sql_fetchrow($query_id))
462 {
463 return false;
464 }
465 }
466
467 return true;
468 }
469
470 /**
471 * Get last inserted id after insert statement
472 */
473 function sql_nextid()
474 {
475 $query_id = $this->query_result;
476
477 if ($query_id !== false && $this->last_query_text != '')
478 {
479 if (preg_match('#^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)#is', $this->last_query_text, $tablename))
480 {
481 $query = 'SELECT ' . $tablename[1] . '_seq.currval FROM DUAL';
482 $stmt = @ociparse($this->db_connect_id, $query);
483 @ociexecute($stmt, OCI_DEFAULT);
484
485 $temp_result = @ocifetchinto($stmt, $temp_array, OCI_ASSOC + OCI_RETURN_NULLS);
486 @ocifreestatement($stmt);
487
488 if ($temp_result)
489 {
490 return $temp_array['CURRVAL'];
491 }
492 else
493 {
494 return false;
495 }
496 }
497 }
498
499 return false;
500 }
501
502 /**
503 * Free sql result
504 */
505 function sql_freeresult($query_id = false)
506 {
507 global $cache;
508
509 if ($query_id === false)
510 {
511 $query_id = $this->query_result;
512 }
513
514 if (isset($cache->sql_rowset[$query_id]))
515 {
516 return $cache->sql_freeresult($query_id);
517 }
518
519 if (isset($this->open_queries[(int) $query_id]))
520 {
521 unset($this->open_queries[(int) $query_id]);
522 return @ocifreestatement($query_id);
523 }
524
525 return false;
526 }
527
528 /**
529 * Escape string used in sql query
530 */
531 function sql_escape($msg)
532 {
533 return str_replace("'", "''", $msg);
534 }
535
536 /**
537 * Build LIKE expression
538 * @access private
539 */
540 function _sql_like_expression($expression)
541 {
542 return $expression . " ESCAPE '\\'";
543 }
544
545 function _sql_custom_build($stage, $data)
546 {
547 return $data;
548 }
549
550 /**
551 * return sql error array
552 * @access private
553 */
554 function _sql_error()
555 {
556 $error = @ocierror();
557 $error = (!$error) ? @ocierror($this->query_result) : $error;
558 $error = (!$error) ? @ocierror($this->db_connect_id) : $error;
559
560 if ($error)
561 {
562 $this->last_error_result = $error;
563 }
564 else
565 {
566 $error = (isset($this->last_error_result) && $this->last_error_result) ? $this->last_error_result : array();
567 }
568
569 return $error;
570 }
571
572 /**
573 * Close sql connection
574 * @access private
575 */
576 function _sql_close()
577 {
578 return @ocilogoff($this->db_connect_id);
579 }
580
581 /**
582 * Build db-specific report
583 * @access private
584 */
585 function _sql_report($mode, $query = '')
586 {
587 switch ($mode)
588 {
589 case 'start':
590
591 $html_table = false;
592
593 // Grab a plan table, any will do
594 $sql = "SELECT table_name
595 FROM USER_TABLES
596 WHERE table_name LIKE '%PLAN_TABLE%'";
597 $stmt = ociparse($this->db_connect_id, $sql);
598 ociexecute($stmt);
599 $result = array();
600
601 if (ocifetchinto($stmt, $result, OCI_ASSOC + OCI_RETURN_NULLS))
602 {
603 $table = $result['TABLE_NAME'];
604
605 // This is the statement_id that will allow us to track the plan
606 $statement_id = substr(md5($query), 0, 30);
607
608 // Remove any stale plans
609 $stmt2 = ociparse($this->db_connect_id, "DELETE FROM $table WHERE statement_id='$statement_id'");
610 ociexecute($stmt2);
611 ocifreestatement($stmt2);
612
613 // Explain the plan
614 $sql = "EXPLAIN PLAN
615 SET STATEMENT_ID = '$statement_id'
616 FOR $query";
617 $stmt2 = ociparse($this->db_connect_id, $sql);
618 ociexecute($stmt2);
619 ocifreestatement($stmt2);
620
621 // Get the data from the plan
622 $sql = "SELECT operation, options, object_name, object_type, cardinality, cost
623 FROM plan_table
624 START WITH id = 0 AND statement_id = '$statement_id'
625 CONNECT BY PRIOR id = parent_id
626 AND statement_id = '$statement_id'";
627 $stmt2 = ociparse($this->db_connect_id, $sql);
628 ociexecute($stmt2);
629
630 $row = array();
631 while (ocifetchinto($stmt2, $row, OCI_ASSOC + OCI_RETURN_NULLS))
632 {
633 $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
634 }
635
636 ocifreestatement($stmt2);
637
638 // Remove the plan we just made, we delete them on request anyway
639 $stmt2 = ociparse($this->db_connect_id, "DELETE FROM $table WHERE statement_id='$statement_id'");
640 ociexecute($stmt2);
641 ocifreestatement($stmt2);
642 }
643
644 ocifreestatement($stmt);
645
646 if ($html_table)
647 {
648 $this->html_hold .= '</table>';
649 }
650
651 break;
652
653 case 'fromcache':
654 $endtime = explode(' ', microtime());
655 $endtime = $endtime[0] + $endtime[1];
656
657 $result = @ociparse($this->db_connect_id, $query);
658 $success = @ociexecute($result, OCI_DEFAULT);
659 $row = array();
660
661 while (@ocifetchinto($result, $row, OCI_ASSOC + OCI_RETURN_NULLS))
662 {
663 // Take the time spent on parsing rows into account
664 }
665 @ocifreestatement($result);
666
667 $splittime = explode(' ', microtime());
668 $splittime = $splittime[0] + $splittime[1];
669
670 $this->sql_report('record_fromcache', $query, $endtime, $splittime);
671
672 break;
673 }
674 }
675 }
676
677 ?>