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

Last change on this file was 702, checked in by george, 16 years ago
  • Upraveno: Aktualizace fóra.
File size: 10.6 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* MySQL4 Database Abstraction Layer
23* Compatible with:
24* MySQL 3.23+
25* MySQL 4.0+
26* MySQL 4.1+
27* MySQL 5.0+
28* @package dbal
29*/
30class dbal_mysql extends dbal
31{
32 var $multi_insert = true;
33
34 /**
35 * Connect to server
36 * @access public
37 */
38 function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false, $new_link = false)
39 {
40 $this->persistency = $persistency;
41 $this->user = $sqluser;
42 $this->server = $sqlserver . (($port) ? ':' . $port : '');
43 $this->dbname = $database;
44
45 $this->sql_layer = 'mysql4';
46
47 $this->db_connect_id = ($this->persistency) ? @mysql_pconnect($this->server, $this->user, $sqlpassword) : @mysql_connect($this->server, $this->user, $sqlpassword, $new_link);
48
49 if ($this->db_connect_id && $this->dbname != '')
50 {
51 if (@mysql_select_db($this->dbname, $this->db_connect_id))
52 {
53 // Determine what version we are using and if it natively supports UNICODE
54 if (version_compare($this->sql_server_info(true), '4.1.0', '>='))
55 {
56 @mysql_query("SET NAMES 'utf8'", $this->db_connect_id);
57
58 // enforce strict mode on databases that support it
59 if (version_compare($this->sql_server_info(true), '5.0.2', '>='))
60 {
61 $result = @mysql_query('SELECT @@session.sql_mode AS sql_mode', $this->db_connect_id);
62 $row = @mysql_fetch_assoc($result);
63 @mysql_free_result($result);
64 $modes = array_map('trim', explode(',', $row['sql_mode']));
65
66 // TRADITIONAL includes STRICT_ALL_TABLES and STRICT_TRANS_TABLES
67 if (!in_array('TRADITIONAL', $modes))
68 {
69 if (!in_array('STRICT_ALL_TABLES', $modes))
70 {
71 $modes[] = 'STRICT_ALL_TABLES';
72 }
73
74 if (!in_array('STRICT_TRANS_TABLES', $modes))
75 {
76 $modes[] = 'STRICT_TRANS_TABLES';
77 }
78 }
79
80 $mode = implode(',', $modes);
81 @mysql_query("SET SESSION sql_mode='{$mode}'", $this->db_connect_id);
82 }
83 }
84 else if (version_compare($this->sql_server_info(true), '4.0.0', '<'))
85 {
86 $this->sql_layer = 'mysql';
87 }
88
89 return $this->db_connect_id;
90 }
91 }
92
93 return $this->sql_error('');
94 }
95
96 /**
97 * Version information about used database
98 * @param bool $raw if true, only return the fetched sql_server_version
99 * @return string sql server version
100 */
101 function sql_server_info($raw = false)
102 {
103 global $cache;
104
105 if (empty($cache) || ($this->sql_server_version = $cache->get('mysql_version')) === false)
106 {
107 $result = @mysql_query('SELECT VERSION() AS version', $this->db_connect_id);
108 $row = @mysql_fetch_assoc($result);
109 @mysql_free_result($result);
110
111 $this->sql_server_version = $row['version'];
112
113 if (!empty($cache))
114 {
115 $cache->put('mysql_version', $this->sql_server_version);
116 }
117 }
118
119 return ($raw) ? $this->sql_server_version : 'MySQL ' . $this->sql_server_version;
120 }
121
122 /**
123 * SQL Transaction
124 * @access private
125 */
126 function _sql_transaction($status = 'begin')
127 {
128 switch ($status)
129 {
130 case 'begin':
131 return @mysql_query('BEGIN', $this->db_connect_id);
132 break;
133
134 case 'commit':
135 return @mysql_query('COMMIT', $this->db_connect_id);
136 break;
137
138 case 'rollback':
139 return @mysql_query('ROLLBACK', $this->db_connect_id);
140 break;
141 }
142
143 return true;
144 }
145
146 /**
147 * Base query method
148 *
149 * @param string $query Contains the SQL query which shall be executed
150 * @param int $cache_ttl Either 0 to avoid caching or the time in seconds which the result shall be kept in cache
151 * @return mixed When casted to bool the returned value returns true on success and false on failure
152 *
153 * @access public
154 */
155 function sql_query($query = '', $cache_ttl = 0)
156 {
157 if ($query != '')
158 {
159 global $cache;
160
161 // EXPLAIN only in extra debug mode
162 if (defined('DEBUG_EXTRA'))
163 {
164 $this->sql_report('start', $query);
165 }
166
167 $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
168 $this->sql_add_num_queries($this->query_result);
169
170 if ($this->query_result === false)
171 {
172 if (($this->query_result = @mysql_query($query, $this->db_connect_id)) === false)
173 {
174 $this->sql_error($query);
175 }
176
177 if (defined('DEBUG_EXTRA'))
178 {
179 $this->sql_report('stop', $query);
180 }
181
182 if ($cache_ttl && method_exists($cache, 'sql_save'))
183 {
184 $this->open_queries[(int) $this->query_result] = $this->query_result;
185 $cache->sql_save($query, $this->query_result, $cache_ttl);
186 }
187 else if (strpos($query, 'SELECT') === 0 && $this->query_result)
188 {
189 $this->open_queries[(int) $this->query_result] = $this->query_result;
190 }
191 }
192 else if (defined('DEBUG_EXTRA'))
193 {
194 $this->sql_report('fromcache', $query);
195 }
196 }
197 else
198 {
199 return false;
200 }
201
202 return $this->query_result;
203 }
204
205 /**
206 * Build LIMIT query
207 */
208 function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
209 {
210 $this->query_result = false;
211
212 // if $total is set to 0 we do not want to limit the number of rows
213 if ($total == 0)
214 {
215 // Having a value of -1 was always a bug
216 $total = '18446744073709551615';
217 }
218
219 $query .= "\n LIMIT " . ((!empty($offset)) ? $offset . ', ' . $total : $total);
220
221 return $this->sql_query($query, $cache_ttl);
222 }
223
224 /**
225 * Return number of affected rows
226 */
227 function sql_affectedrows()
228 {
229 return ($this->db_connect_id) ? @mysql_affected_rows($this->db_connect_id) : false;
230 }
231
232 /**
233 * Fetch current row
234 */
235 function sql_fetchrow($query_id = false)
236 {
237 global $cache;
238
239 if ($query_id === false)
240 {
241 $query_id = $this->query_result;
242 }
243
244 if (isset($cache->sql_rowset[$query_id]))
245 {
246 return $cache->sql_fetchrow($query_id);
247 }
248
249 return ($query_id !== false) ? @mysql_fetch_assoc($query_id) : false;
250 }
251
252 /**
253 * Seek to given row number
254 * rownum is zero-based
255 */
256 function sql_rowseek($rownum, &$query_id)
257 {
258 global $cache;
259
260 if ($query_id === false)
261 {
262 $query_id = $this->query_result;
263 }
264
265 if (isset($cache->sql_rowset[$query_id]))
266 {
267 return $cache->sql_rowseek($rownum, $query_id);
268 }
269
270 return ($query_id !== false) ? @mysql_data_seek($query_id, $rownum) : false;
271 }
272
273 /**
274 * Get last inserted id after insert statement
275 */
276 function sql_nextid()
277 {
278 return ($this->db_connect_id) ? @mysql_insert_id($this->db_connect_id) : false;
279 }
280
281 /**
282 * Free sql result
283 */
284 function sql_freeresult($query_id = false)
285 {
286 global $cache;
287
288 if ($query_id === false)
289 {
290 $query_id = $this->query_result;
291 }
292
293 if (isset($cache->sql_rowset[$query_id]))
294 {
295 return $cache->sql_freeresult($query_id);
296 }
297
298 if (isset($this->open_queries[(int) $query_id]))
299 {
300 unset($this->open_queries[(int) $query_id]);
301 return @mysql_free_result($query_id);
302 }
303
304 return false;
305 }
306
307 /**
308 * Escape string used in sql query
309 */
310 function sql_escape($msg)
311 {
312 if (!$this->db_connect_id)
313 {
314 return @mysql_real_escape_string($msg);
315 }
316
317 return @mysql_real_escape_string($msg, $this->db_connect_id);
318 }
319
320 /**
321 * Build LIKE expression
322 * @access private
323 */
324 function _sql_like_expression($expression)
325 {
326 return $expression;
327 }
328
329 /**
330 * Build db-specific query data
331 * @access private
332 */
333 function _sql_custom_build($stage, $data)
334 {
335 switch ($stage)
336 {
337 case 'FROM':
338 $data = '(' . $data . ')';
339 break;
340 }
341
342 return $data;
343 }
344
345 /**
346 * return sql error array
347 * @access private
348 */
349 function _sql_error()
350 {
351 if (!$this->db_connect_id)
352 {
353 return array(
354 'message' => @mysql_error(),
355 'code' => @mysql_errno()
356 );
357 }
358
359 return array(
360 'message' => @mysql_error($this->db_connect_id),
361 'code' => @mysql_errno($this->db_connect_id)
362 );
363 }
364
365 /**
366 * Close sql connection
367 * @access private
368 */
369 function _sql_close()
370 {
371 return @mysql_close($this->db_connect_id);
372 }
373
374 /**
375 * Build db-specific report
376 * @access private
377 */
378 function _sql_report($mode, $query = '')
379 {
380 static $test_prof;
381
382 // current detection method, might just switch to see the existance of INFORMATION_SCHEMA.PROFILING
383 if ($test_prof === null)
384 {
385 $test_prof = false;
386 if (version_compare($this->sql_server_info(true), '5.0.37', '>=') && version_compare($this->sql_server_info(true), '5.1', '<'))
387 {
388 $test_prof = true;
389 }
390 }
391
392 switch ($mode)
393 {
394 case 'start':
395
396 $explain_query = $query;
397 if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
398 {
399 $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
400 }
401 else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
402 {
403 $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
404 }
405
406 if (preg_match('/^SELECT/', $explain_query))
407 {
408 $html_table = false;
409
410 // begin profiling
411 if ($test_prof)
412 {
413 @mysql_query('SET profiling = 1;', $this->db_connect_id);
414 }
415
416 if ($result = @mysql_query("EXPLAIN $explain_query", $this->db_connect_id))
417 {
418 while ($row = @mysql_fetch_assoc($result))
419 {
420 $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
421 }
422 }
423 @mysql_free_result($result);
424
425 if ($html_table)
426 {
427 $this->html_hold .= '</table>';
428 }
429
430 if ($test_prof)
431 {
432 $html_table = false;
433
434 // get the last profile
435 if ($result = @mysql_query('SHOW PROFILE ALL;', $this->db_connect_id))
436 {
437 $this->html_hold .= '<br />';
438 while ($row = @mysql_fetch_assoc($result))
439 {
440 // make <unknown> HTML safe
441 if (!empty($row['Source_function']))
442 {
443 $row['Source_function'] = str_replace(array('<', '>'), array('&lt;', '&gt;'), $row['Source_function']);
444 }
445
446 // remove unsupported features
447 foreach ($row as $key => $val)
448 {
449 if ($val === null)
450 {
451 unset($row[$key]);
452 }
453 }
454 $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
455 }
456 }
457 @mysql_free_result($result);
458
459 if ($html_table)
460 {
461 $this->html_hold .= '</table>';
462 }
463
464 @mysql_query('SET profiling = 0;', $this->db_connect_id);
465 }
466 }
467
468 break;
469
470 case 'fromcache':
471 $endtime = explode(' ', microtime());
472 $endtime = $endtime[0] + $endtime[1];
473
474 $result = @mysql_query($query, $this->db_connect_id);
475 while ($void = @mysql_fetch_assoc($result))
476 {
477 // Take the time spent on parsing rows into account
478 }
479 @mysql_free_result($result);
480
481 $splittime = explode(' ', microtime());
482 $splittime = $splittime[0] + $splittime[1];
483
484 $this->sql_report('record_fromcache', $query, $endtime, $splittime);
485
486 break;
487 }
488 }
489}
490
491?>
Note: See TracBrowser for help on using the repository browser.