|
0
|
1 <?php
|
|
|
2
|
|
|
3 /**
|
|
|
4 +-----------------------------------------------------------------------+
|
|
|
5 | This file is part of the Roundcube Webmail client |
|
|
|
6 | Copyright (C) 2005-2012, The Roundcube Dev Team |
|
|
|
7 | |
|
|
|
8 | Licensed under the GNU General Public License version 3 or |
|
|
|
9 | any later version with exceptions for skins & plugins. |
|
|
|
10 | See the README file for a full license statement. |
|
|
|
11 | |
|
|
|
12 | PURPOSE: |
|
|
|
13 | Database wrapper class that implements PHP PDO functions |
|
|
|
14 +-----------------------------------------------------------------------+
|
|
|
15 | Author: Aleksander Machniak <alec@alec.pl> |
|
|
|
16 +-----------------------------------------------------------------------+
|
|
|
17 */
|
|
|
18
|
|
|
19 /**
|
|
|
20 * Database independent query interface.
|
|
|
21 * This is a wrapper for the PHP PDO.
|
|
|
22 *
|
|
|
23 * @package Framework
|
|
|
24 * @subpackage Database
|
|
|
25 */
|
|
|
26 class rcube_db
|
|
|
27 {
|
|
|
28 public $db_provider;
|
|
|
29
|
|
|
30 protected $db_dsnw; // DSN for write operations
|
|
|
31 protected $db_dsnr; // DSN for read operations
|
|
|
32 protected $db_connected = false; // Already connected ?
|
|
|
33 protected $db_mode; // Connection mode
|
|
|
34 protected $dbh; // Connection handle
|
|
|
35 protected $dbhs = array();
|
|
|
36 protected $table_connections = array();
|
|
|
37
|
|
|
38 protected $db_error = false;
|
|
|
39 protected $db_error_msg = '';
|
|
|
40 protected $conn_failure = false;
|
|
|
41 protected $db_index = 0;
|
|
|
42 protected $last_result;
|
|
|
43 protected $tables;
|
|
|
44 protected $variables;
|
|
|
45
|
|
|
46 protected $options = array(
|
|
|
47 // column/table quotes
|
|
|
48 'identifier_start' => '"',
|
|
|
49 'identifier_end' => '"',
|
|
|
50 // date/time input format
|
|
|
51 'datetime_format' => 'Y-m-d H:i:s',
|
|
|
52 );
|
|
|
53
|
|
|
54 const DEBUG_LINE_LENGTH = 4096;
|
|
|
55 const DEFAULT_QUOTE = '`';
|
|
|
56
|
|
|
57 /**
|
|
|
58 * Factory, returns driver-specific instance of the class
|
|
|
59 *
|
|
|
60 * @param string $db_dsnw DSN for read/write operations
|
|
|
61 * @param string $db_dsnr Optional DSN for read only operations
|
|
|
62 * @param bool $pconn Enables persistent connections
|
|
|
63 *
|
|
|
64 * @return rcube_db Object instance
|
|
|
65 */
|
|
|
66 public static function factory($db_dsnw, $db_dsnr = '', $pconn = false)
|
|
|
67 {
|
|
|
68 $driver = strtolower(substr($db_dsnw, 0, strpos($db_dsnw, ':')));
|
|
|
69 $driver_map = array(
|
|
|
70 'sqlite2' => 'sqlite',
|
|
|
71 'sybase' => 'mssql',
|
|
|
72 'dblib' => 'mssql',
|
|
|
73 'mysqli' => 'mysql',
|
|
|
74 'oci' => 'oracle',
|
|
|
75 'oci8' => 'oracle',
|
|
|
76 );
|
|
|
77
|
|
|
78 $driver = isset($driver_map[$driver]) ? $driver_map[$driver] : $driver;
|
|
|
79 $class = "rcube_db_$driver";
|
|
|
80
|
|
|
81 if (!$driver || !class_exists($class)) {
|
|
|
82 rcube::raise_error(array('code' => 600, 'type' => 'db',
|
|
|
83 'line' => __LINE__, 'file' => __FILE__,
|
|
|
84 'message' => "Configuration error. Unsupported database driver: $driver"),
|
|
|
85 true, true);
|
|
|
86 }
|
|
|
87
|
|
|
88 return new $class($db_dsnw, $db_dsnr, $pconn);
|
|
|
89 }
|
|
|
90
|
|
|
91 /**
|
|
|
92 * Object constructor
|
|
|
93 *
|
|
|
94 * @param string $db_dsnw DSN for read/write operations
|
|
|
95 * @param string $db_dsnr Optional DSN for read only operations
|
|
|
96 * @param bool $pconn Enables persistent connections
|
|
|
97 */
|
|
|
98 public function __construct($db_dsnw, $db_dsnr = '', $pconn = false)
|
|
|
99 {
|
|
|
100 if (empty($db_dsnr)) {
|
|
|
101 $db_dsnr = $db_dsnw;
|
|
|
102 }
|
|
|
103
|
|
|
104 $this->db_dsnw = $db_dsnw;
|
|
|
105 $this->db_dsnr = $db_dsnr;
|
|
|
106 $this->db_pconn = $pconn;
|
|
|
107
|
|
|
108 $this->db_dsnw_array = self::parse_dsn($db_dsnw);
|
|
|
109 $this->db_dsnr_array = self::parse_dsn($db_dsnr);
|
|
|
110
|
|
|
111 $config = rcube::get_instance()->config;
|
|
|
112
|
|
|
113 $this->options['table_prefix'] = $config->get('db_prefix');
|
|
|
114 $this->options['dsnw_noread'] = $config->get('db_dsnw_noread', false);
|
|
|
115 $this->options['table_dsn_map'] = array_map(array($this, 'table_name'), $config->get('db_table_dsn', array()));
|
|
|
116 }
|
|
|
117
|
|
|
118 /**
|
|
|
119 * Connect to specific database
|
|
|
120 *
|
|
|
121 * @param array $dsn DSN for DB connections
|
|
|
122 * @param string $mode Connection mode (r|w)
|
|
|
123 */
|
|
|
124 protected function dsn_connect($dsn, $mode)
|
|
|
125 {
|
|
|
126 $this->db_error = false;
|
|
|
127 $this->db_error_msg = null;
|
|
|
128
|
|
|
129 // return existing handle
|
|
|
130 if ($this->dbhs[$mode]) {
|
|
|
131 $this->dbh = $this->dbhs[$mode];
|
|
|
132 $this->db_mode = $mode;
|
|
|
133 return $this->dbh;
|
|
|
134 }
|
|
|
135
|
|
|
136 // connect to database
|
|
|
137 if ($dbh = $this->conn_create($dsn)) {
|
|
|
138 $this->dbh = $dbh;
|
|
|
139 $this->dbhs[$mode] = $dbh;
|
|
|
140 $this->db_mode = $mode;
|
|
|
141 $this->db_connected = true;
|
|
|
142 }
|
|
|
143 }
|
|
|
144
|
|
|
145 /**
|
|
|
146 * Create PDO connection
|
|
|
147 */
|
|
|
148 protected function conn_create($dsn)
|
|
|
149 {
|
|
|
150 // Get database specific connection options
|
|
|
151 $dsn_string = $this->dsn_string($dsn);
|
|
|
152 $dsn_options = $this->dsn_options($dsn);
|
|
|
153
|
|
|
154 // Connect
|
|
|
155 try {
|
|
|
156 // with this check we skip fatal error on PDO object creation
|
|
|
157 if (!class_exists('PDO', false)) {
|
|
|
158 throw new Exception('PDO extension not loaded. See http://php.net/manual/en/intro.pdo.php');
|
|
|
159 }
|
|
|
160
|
|
|
161 $this->conn_prepare($dsn);
|
|
|
162
|
|
|
163 $dbh = new PDO($dsn_string, $dsn['username'], $dsn['password'], $dsn_options);
|
|
|
164
|
|
|
165 // don't throw exceptions or warnings
|
|
|
166 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
|
|
|
167
|
|
|
168 $this->conn_configure($dsn, $dbh);
|
|
|
169 }
|
|
|
170 catch (Exception $e) {
|
|
|
171 $this->db_error = true;
|
|
|
172 $this->db_error_msg = $e->getMessage();
|
|
|
173
|
|
|
174 rcube::raise_error(array('code' => 500, 'type' => 'db',
|
|
|
175 'line' => __LINE__, 'file' => __FILE__,
|
|
|
176 'message' => $this->db_error_msg), true, false);
|
|
|
177
|
|
|
178 return null;
|
|
|
179 }
|
|
|
180
|
|
|
181 return $dbh;
|
|
|
182 }
|
|
|
183
|
|
|
184 /**
|
|
|
185 * Driver-specific preparation of database connection
|
|
|
186 *
|
|
|
187 * @param array $dsn DSN for DB connections
|
|
|
188 */
|
|
|
189 protected function conn_prepare($dsn)
|
|
|
190 {
|
|
|
191 }
|
|
|
192
|
|
|
193 /**
|
|
|
194 * Driver-specific configuration of database connection
|
|
|
195 *
|
|
|
196 * @param array $dsn DSN for DB connections
|
|
|
197 * @param PDO $dbh Connection handler
|
|
|
198 */
|
|
|
199 protected function conn_configure($dsn, $dbh)
|
|
|
200 {
|
|
|
201 }
|
|
|
202
|
|
|
203 /**
|
|
|
204 * Connect to appropriate database depending on the operation
|
|
|
205 *
|
|
|
206 * @param string $mode Connection mode (r|w)
|
|
|
207 * @param boolean $force Enforce using the given mode
|
|
|
208 */
|
|
|
209 public function db_connect($mode, $force = false)
|
|
|
210 {
|
|
|
211 // previous connection failed, don't attempt to connect again
|
|
|
212 if ($this->conn_failure) {
|
|
|
213 return;
|
|
|
214 }
|
|
|
215
|
|
|
216 // no replication
|
|
|
217 if ($this->db_dsnw == $this->db_dsnr) {
|
|
|
218 $mode = 'w';
|
|
|
219 }
|
|
|
220
|
|
|
221 // Already connected
|
|
|
222 if ($this->db_connected) {
|
|
|
223 // connected to db with the same or "higher" mode (if allowed)
|
|
|
224 if ($this->db_mode == $mode || $this->db_mode == 'w' && !$force && !$this->options['dsnw_noread']) {
|
|
|
225 return;
|
|
|
226 }
|
|
|
227 }
|
|
|
228
|
|
|
229 $dsn = ($mode == 'r') ? $this->db_dsnr_array : $this->db_dsnw_array;
|
|
|
230 $this->dsn_connect($dsn, $mode);
|
|
|
231
|
|
|
232 // use write-master when read-only fails
|
|
|
233 if (!$this->db_connected && $mode == 'r' && $this->is_replicated()) {
|
|
|
234 $this->dsn_connect($this->db_dsnw_array, 'w');
|
|
|
235 }
|
|
|
236
|
|
|
237 $this->conn_failure = !$this->db_connected;
|
|
|
238 }
|
|
|
239
|
|
|
240 /**
|
|
|
241 * Analyze the given SQL statement and select the appropriate connection to use
|
|
|
242 */
|
|
|
243 protected function dsn_select($query)
|
|
|
244 {
|
|
|
245 // no replication
|
|
|
246 if ($this->db_dsnw == $this->db_dsnr) {
|
|
|
247 return 'w';
|
|
|
248 }
|
|
|
249
|
|
|
250 // Read or write ?
|
|
|
251 $mode = preg_match('/^(select|show|set)/i', $query) ? 'r' : 'w';
|
|
|
252
|
|
|
253 $start = '[' . $this->options['identifier_start'] . self::DEFAULT_QUOTE . ']';
|
|
|
254 $end = '[' . $this->options['identifier_end'] . self::DEFAULT_QUOTE . ']';
|
|
|
255 $regex = '/(?:^|\s)(from|update|into|join)\s+'.$start.'?([a-z0-9._]+)'.$end.'?\s+/i';
|
|
|
256
|
|
|
257 // find tables involved in this query
|
|
|
258 if (preg_match_all($regex, $query, $matches, PREG_SET_ORDER)) {
|
|
|
259 foreach ($matches as $m) {
|
|
|
260 $table = $m[2];
|
|
|
261
|
|
|
262 // always use direct mapping
|
|
|
263 if ($this->options['table_dsn_map'][$table]) {
|
|
|
264 $mode = $this->options['table_dsn_map'][$table];
|
|
|
265 break; // primary table rules
|
|
|
266 }
|
|
|
267 else if ($mode == 'r') {
|
|
|
268 // connected to db with the same or "higher" mode for this table
|
|
|
269 $db_mode = $this->table_connections[$table];
|
|
|
270 if ($db_mode == 'w' && !$this->options['dsnw_noread']) {
|
|
|
271 $mode = $db_mode;
|
|
|
272 }
|
|
|
273 }
|
|
|
274 }
|
|
|
275
|
|
|
276 // remember mode chosen (for primary table)
|
|
|
277 $table = $matches[0][2];
|
|
|
278 $this->table_connections[$table] = $mode;
|
|
|
279 }
|
|
|
280
|
|
|
281 return $mode;
|
|
|
282 }
|
|
|
283
|
|
|
284 /**
|
|
|
285 * Activate/deactivate debug mode
|
|
|
286 *
|
|
|
287 * @param boolean $dbg True if SQL queries should be logged
|
|
|
288 */
|
|
|
289 public function set_debug($dbg = true)
|
|
|
290 {
|
|
|
291 $this->options['debug_mode'] = $dbg;
|
|
|
292 }
|
|
|
293
|
|
|
294 /**
|
|
|
295 * Writes debug information/query to 'sql' log file
|
|
|
296 *
|
|
|
297 * @param string $query SQL query
|
|
|
298 */
|
|
|
299 protected function debug($query)
|
|
|
300 {
|
|
|
301 if ($this->options['debug_mode']) {
|
|
|
302 if (($len = strlen($query)) > self::DEBUG_LINE_LENGTH) {
|
|
|
303 $diff = $len - self::DEBUG_LINE_LENGTH;
|
|
|
304 $query = substr($query, 0, self::DEBUG_LINE_LENGTH)
|
|
|
305 . "... [truncated $diff bytes]";
|
|
|
306 }
|
|
|
307 rcube::write_log('sql', '[' . (++$this->db_index) . '] ' . $query . ';');
|
|
|
308 }
|
|
|
309 }
|
|
|
310
|
|
|
311 /**
|
|
|
312 * Getter for error state
|
|
|
313 *
|
|
|
314 * @param mixed $result Optional query result
|
|
|
315 *
|
|
|
316 * @return string Error message
|
|
|
317 */
|
|
|
318 public function is_error($result = null)
|
|
|
319 {
|
|
|
320 if ($result !== null) {
|
|
|
321 return $result === false ? $this->db_error_msg : null;
|
|
|
322 }
|
|
|
323
|
|
|
324 return $this->db_error ? $this->db_error_msg : null;
|
|
|
325 }
|
|
|
326
|
|
|
327 /**
|
|
|
328 * Connection state checker
|
|
|
329 *
|
|
|
330 * @return boolean True if in connected state
|
|
|
331 */
|
|
|
332 public function is_connected()
|
|
|
333 {
|
|
|
334 return !is_object($this->dbh) ? false : $this->db_connected;
|
|
|
335 }
|
|
|
336
|
|
|
337 /**
|
|
|
338 * Is database replication configured?
|
|
|
339 *
|
|
|
340 * @return bool Returns true if dsnw != dsnr
|
|
|
341 */
|
|
|
342 public function is_replicated()
|
|
|
343 {
|
|
|
344 return !empty($this->db_dsnr) && $this->db_dsnw != $this->db_dsnr;
|
|
|
345 }
|
|
|
346
|
|
|
347 /**
|
|
|
348 * Get database runtime variables
|
|
|
349 *
|
|
|
350 * @param string $varname Variable name
|
|
|
351 * @param mixed $default Default value if variable is not set
|
|
|
352 *
|
|
|
353 * @return mixed Variable value or default
|
|
|
354 */
|
|
|
355 public function get_variable($varname, $default = null)
|
|
|
356 {
|
|
|
357 // to be implemented by driver class
|
|
|
358 return rcube::get_instance()->config->get('db_' . $varname, $default);
|
|
|
359 }
|
|
|
360
|
|
|
361 /**
|
|
|
362 * Execute a SQL query
|
|
|
363 *
|
|
|
364 * @param string SQL query to execute
|
|
|
365 * @param mixed Values to be inserted in query
|
|
|
366 *
|
|
|
367 * @return number Query handle identifier
|
|
|
368 */
|
|
|
369 public function query()
|
|
|
370 {
|
|
|
371 $params = func_get_args();
|
|
|
372 $query = array_shift($params);
|
|
|
373
|
|
|
374 // Support one argument of type array, instead of n arguments
|
|
|
375 if (count($params) == 1 && is_array($params[0])) {
|
|
|
376 $params = $params[0];
|
|
|
377 }
|
|
|
378
|
|
|
379 return $this->_query($query, 0, 0, $params);
|
|
|
380 }
|
|
|
381
|
|
|
382 /**
|
|
|
383 * Execute a SQL query with limits
|
|
|
384 *
|
|
|
385 * @param string SQL query to execute
|
|
|
386 * @param int Offset for LIMIT statement
|
|
|
387 * @param int Number of rows for LIMIT statement
|
|
|
388 * @param mixed Values to be inserted in query
|
|
|
389 *
|
|
|
390 * @return PDOStatement|bool Query handle or False on error
|
|
|
391 */
|
|
|
392 public function limitquery()
|
|
|
393 {
|
|
|
394 $params = func_get_args();
|
|
|
395 $query = array_shift($params);
|
|
|
396 $offset = array_shift($params);
|
|
|
397 $numrows = array_shift($params);
|
|
|
398
|
|
|
399 return $this->_query($query, $offset, $numrows, $params);
|
|
|
400 }
|
|
|
401
|
|
|
402 /**
|
|
|
403 * Execute a SQL query with limits
|
|
|
404 *
|
|
|
405 * @param string $query SQL query to execute
|
|
|
406 * @param int $offset Offset for LIMIT statement
|
|
|
407 * @param int $numrows Number of rows for LIMIT statement
|
|
|
408 * @param array $params Values to be inserted in query
|
|
|
409 *
|
|
|
410 * @return PDOStatement|bool Query handle or False on error
|
|
|
411 */
|
|
|
412 protected function _query($query, $offset, $numrows, $params)
|
|
|
413 {
|
|
|
414 $query = ltrim($query);
|
|
|
415
|
|
|
416 $this->db_connect($this->dsn_select($query), true);
|
|
|
417
|
|
|
418 // check connection before proceeding
|
|
|
419 if (!$this->is_connected()) {
|
|
|
420 return $this->last_result = false;
|
|
|
421 }
|
|
|
422
|
|
|
423 if ($numrows || $offset) {
|
|
|
424 $query = $this->set_limit($query, $numrows, $offset);
|
|
|
425 }
|
|
|
426
|
|
|
427 // replace self::DEFAULT_QUOTE with driver-specific quoting
|
|
|
428 $query = $this->query_parse($query);
|
|
|
429
|
|
|
430 // Because in Roundcube we mostly use queries that are
|
|
|
431 // executed only once, we will not use prepared queries
|
|
|
432 $pos = 0;
|
|
|
433 $idx = 0;
|
|
|
434
|
|
|
435 if (count($params)) {
|
|
|
436 while ($pos = strpos($query, '?', $pos)) {
|
|
|
437 if ($query[$pos+1] == '?') { // skip escaped '?'
|
|
|
438 $pos += 2;
|
|
|
439 }
|
|
|
440 else {
|
|
|
441 $val = $this->quote($params[$idx++]);
|
|
|
442 unset($params[$idx-1]);
|
|
|
443 $query = substr_replace($query, $val, $pos, 1);
|
|
|
444 $pos += strlen($val);
|
|
|
445 }
|
|
|
446 }
|
|
|
447 }
|
|
|
448
|
|
|
449 $query = rtrim($query, " \t\n\r\0\x0B;");
|
|
|
450
|
|
|
451 // replace escaped '?' and quotes back to normal, see self::quote()
|
|
|
452 $query = str_replace(
|
|
|
453 array('??', self::DEFAULT_QUOTE.self::DEFAULT_QUOTE),
|
|
|
454 array('?', self::DEFAULT_QUOTE),
|
|
|
455 $query
|
|
|
456 );
|
|
|
457
|
|
|
458 // log query
|
|
|
459 $this->debug($query);
|
|
|
460
|
|
|
461 return $this->query_execute($query);
|
|
|
462 }
|
|
|
463
|
|
|
464 /**
|
|
|
465 * Query execution
|
|
|
466 */
|
|
|
467 protected function query_execute($query)
|
|
|
468 {
|
|
|
469 // destroy reference to previous result, required for SQLite driver (#1488874)
|
|
|
470 $this->last_result = null;
|
|
|
471 $this->db_error_msg = null;
|
|
|
472
|
|
|
473 // send query
|
|
|
474 $result = $this->dbh->query($query);
|
|
|
475
|
|
|
476 if ($result === false) {
|
|
|
477 $result = $this->handle_error($query);
|
|
|
478 }
|
|
|
479
|
|
|
480 return $this->last_result = $result;
|
|
|
481 }
|
|
|
482
|
|
|
483 /**
|
|
|
484 * Parse SQL query and replace identifier quoting
|
|
|
485 *
|
|
|
486 * @param string $query SQL query
|
|
|
487 *
|
|
|
488 * @return string SQL query
|
|
|
489 */
|
|
|
490 protected function query_parse($query)
|
|
|
491 {
|
|
|
492 $start = $this->options['identifier_start'];
|
|
|
493 $end = $this->options['identifier_end'];
|
|
|
494 $quote = self::DEFAULT_QUOTE;
|
|
|
495
|
|
|
496 if ($start == $quote) {
|
|
|
497 return $query;
|
|
|
498 }
|
|
|
499
|
|
|
500 $pos = 0;
|
|
|
501 $in = false;
|
|
|
502
|
|
|
503 while ($pos = strpos($query, $quote, $pos)) {
|
|
|
504 if ($query[$pos+1] == $quote) { // skip escaped quote
|
|
|
505 $pos += 2;
|
|
|
506 }
|
|
|
507 else {
|
|
|
508 if ($in) {
|
|
|
509 $q = $end;
|
|
|
510 $in = false;
|
|
|
511 }
|
|
|
512 else {
|
|
|
513 $q = $start;
|
|
|
514 $in = true;
|
|
|
515 }
|
|
|
516
|
|
|
517 $query = substr_replace($query, $q, $pos, 1);
|
|
|
518 $pos++;
|
|
|
519 }
|
|
|
520 }
|
|
|
521
|
|
|
522 return $query;
|
|
|
523 }
|
|
|
524
|
|
|
525 /**
|
|
|
526 * Helper method to handle DB errors.
|
|
|
527 * This by default logs the error but could be overridden by a driver implementation
|
|
|
528 *
|
|
|
529 * @param string $query Query that triggered the error
|
|
|
530 *
|
|
|
531 * @return mixed Result to be stored and returned
|
|
|
532 */
|
|
|
533 protected function handle_error($query)
|
|
|
534 {
|
|
|
535 $error = $this->dbh->errorInfo();
|
|
|
536
|
|
|
537 if (empty($this->options['ignore_key_errors']) || !in_array($error[0], array('23000', '23505'))) {
|
|
|
538 $this->db_error = true;
|
|
|
539 $this->db_error_msg = sprintf('[%s] %s', $error[1], $error[2]);
|
|
|
540
|
|
|
541 if (empty($this->options['ignore_errors'])) {
|
|
|
542 rcube::raise_error(array(
|
|
|
543 'code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
|
|
|
544 'message' => $this->db_error_msg . " (SQL Query: $query)"
|
|
|
545 ), true, false);
|
|
|
546 }
|
|
|
547 }
|
|
|
548
|
|
|
549 return false;
|
|
|
550 }
|
|
|
551
|
|
|
552 /**
|
|
|
553 * Get number of affected rows for the last query
|
|
|
554 *
|
|
|
555 * @param mixed $result Optional query handle
|
|
|
556 *
|
|
|
557 * @return int Number of (matching) rows
|
|
|
558 */
|
|
|
559 public function affected_rows($result = null)
|
|
|
560 {
|
|
|
561 if ($result || ($result === null && ($result = $this->last_result))) {
|
|
|
562 if ($result !== true) {
|
|
|
563 return $result->rowCount();
|
|
|
564 }
|
|
|
565 }
|
|
|
566
|
|
|
567 return 0;
|
|
|
568 }
|
|
|
569
|
|
|
570 /**
|
|
|
571 * Get number of rows for a SQL query
|
|
|
572 * If no query handle is specified, the last query will be taken as reference
|
|
|
573 *
|
|
|
574 * @param mixed $result Optional query handle
|
|
|
575 *
|
|
|
576 * @return mixed Number of rows or false on failure
|
|
|
577 * @deprecated This method shows very poor performance and should be avoided.
|
|
|
578 */
|
|
|
579 public function num_rows($result = null)
|
|
|
580 {
|
|
|
581 if (($result || ($result === null && ($result = $this->last_result))) && $result !== true) {
|
|
|
582 // repeat query with SELECT COUNT(*) ...
|
|
|
583 if (preg_match('/^SELECT\s+(?:ALL\s+|DISTINCT\s+)?(?:.*?)\s+FROM\s+(.*)$/ims', $result->queryString, $m)) {
|
|
|
584 $query = $this->dbh->query('SELECT COUNT(*) FROM ' . $m[1], PDO::FETCH_NUM);
|
|
|
585 return $query ? intval($query->fetchColumn(0)) : false;
|
|
|
586 }
|
|
|
587 else {
|
|
|
588 $num = count($result->fetchAll());
|
|
|
589 $result->execute(); // re-execute query because there's no seek(0)
|
|
|
590 return $num;
|
|
|
591 }
|
|
|
592 }
|
|
|
593
|
|
|
594 return false;
|
|
|
595 }
|
|
|
596
|
|
|
597 /**
|
|
|
598 * Get last inserted record ID
|
|
|
599 *
|
|
|
600 * @param string $table Table name (to find the incremented sequence)
|
|
|
601 *
|
|
|
602 * @return mixed ID or false on failure
|
|
|
603 */
|
|
|
604 public function insert_id($table = '')
|
|
|
605 {
|
|
|
606 if (!$this->db_connected || $this->db_mode == 'r') {
|
|
|
607 return false;
|
|
|
608 }
|
|
|
609
|
|
|
610 if ($table) {
|
|
|
611 // resolve table name
|
|
|
612 $table = $this->table_name($table);
|
|
|
613 }
|
|
|
614
|
|
|
615 $id = $this->dbh->lastInsertId($table);
|
|
|
616
|
|
|
617 return $id;
|
|
|
618 }
|
|
|
619
|
|
|
620 /**
|
|
|
621 * Get an associative array for one row
|
|
|
622 * If no query handle is specified, the last query will be taken as reference
|
|
|
623 *
|
|
|
624 * @param mixed $result Optional query handle
|
|
|
625 *
|
|
|
626 * @return mixed Array with col values or false on failure
|
|
|
627 */
|
|
|
628 public function fetch_assoc($result = null)
|
|
|
629 {
|
|
|
630 return $this->_fetch_row($result, PDO::FETCH_ASSOC);
|
|
|
631 }
|
|
|
632
|
|
|
633 /**
|
|
|
634 * Get an index array for one row
|
|
|
635 * If no query handle is specified, the last query will be taken as reference
|
|
|
636 *
|
|
|
637 * @param mixed $result Optional query handle
|
|
|
638 *
|
|
|
639 * @return mixed Array with col values or false on failure
|
|
|
640 */
|
|
|
641 public function fetch_array($result = null)
|
|
|
642 {
|
|
|
643 return $this->_fetch_row($result, PDO::FETCH_NUM);
|
|
|
644 }
|
|
|
645
|
|
|
646 /**
|
|
|
647 * Get col values for a result row
|
|
|
648 *
|
|
|
649 * @param mixed $result Optional query handle
|
|
|
650 * @param int $mode Fetch mode identifier
|
|
|
651 *
|
|
|
652 * @return mixed Array with col values or false on failure
|
|
|
653 */
|
|
|
654 protected function _fetch_row($result, $mode)
|
|
|
655 {
|
|
|
656 if ($result || ($result === null && ($result = $this->last_result))) {
|
|
|
657 if ($result !== true) {
|
|
|
658 return $result->fetch($mode);
|
|
|
659 }
|
|
|
660 }
|
|
|
661
|
|
|
662 return false;
|
|
|
663 }
|
|
|
664
|
|
|
665 /**
|
|
|
666 * Adds LIMIT,OFFSET clauses to the query
|
|
|
667 *
|
|
|
668 * @param string $query SQL query
|
|
|
669 * @param int $limit Number of rows
|
|
|
670 * @param int $offset Offset
|
|
|
671 *
|
|
|
672 * @return string SQL query
|
|
|
673 */
|
|
|
674 protected function set_limit($query, $limit = 0, $offset = 0)
|
|
|
675 {
|
|
|
676 if ($limit) {
|
|
|
677 $query .= ' LIMIT ' . intval($limit);
|
|
|
678 }
|
|
|
679
|
|
|
680 if ($offset) {
|
|
|
681 $query .= ' OFFSET ' . intval($offset);
|
|
|
682 }
|
|
|
683
|
|
|
684 return $query;
|
|
|
685 }
|
|
|
686
|
|
|
687 /**
|
|
|
688 * Returns list of tables in a database
|
|
|
689 *
|
|
|
690 * @return array List of all tables of the current database
|
|
|
691 */
|
|
|
692 public function list_tables()
|
|
|
693 {
|
|
|
694 // get tables if not cached
|
|
|
695 if ($this->tables === null) {
|
|
|
696 $q = $this->query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"
|
|
|
697 . " WHERE TABLE_TYPE = 'BASE TABLE'"
|
|
|
698 . " ORDER BY TABLE_NAME");
|
|
|
699
|
|
|
700 $this->tables = $q ? $q->fetchAll(PDO::FETCH_COLUMN, 0) : array();
|
|
|
701 }
|
|
|
702
|
|
|
703 return $this->tables;
|
|
|
704 }
|
|
|
705
|
|
|
706 /**
|
|
|
707 * Returns list of columns in database table
|
|
|
708 *
|
|
|
709 * @param string $table Table name
|
|
|
710 *
|
|
|
711 * @return array List of table cols
|
|
|
712 */
|
|
|
713 public function list_cols($table)
|
|
|
714 {
|
|
|
715 $q = $this->query('SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?',
|
|
|
716 array($table));
|
|
|
717
|
|
|
718 if ($q) {
|
|
|
719 return $q->fetchAll(PDO::FETCH_COLUMN, 0);
|
|
|
720 }
|
|
|
721
|
|
|
722 return array();
|
|
|
723 }
|
|
|
724
|
|
|
725 /**
|
|
|
726 * Start transaction
|
|
|
727 *
|
|
|
728 * @return bool True on success, False on failure
|
|
|
729 */
|
|
|
730 public function startTransaction()
|
|
|
731 {
|
|
|
732 $this->db_connect('w', true);
|
|
|
733
|
|
|
734 // check connection before proceeding
|
|
|
735 if (!$this->is_connected()) {
|
|
|
736 return $this->last_result = false;
|
|
|
737 }
|
|
|
738
|
|
|
739 $this->debug('BEGIN TRANSACTION');
|
|
|
740
|
|
|
741 return $this->last_result = $this->dbh->beginTransaction();
|
|
|
742 }
|
|
|
743
|
|
|
744 /**
|
|
|
745 * Commit transaction
|
|
|
746 *
|
|
|
747 * @return bool True on success, False on failure
|
|
|
748 */
|
|
|
749 public function endTransaction()
|
|
|
750 {
|
|
|
751 $this->db_connect('w', true);
|
|
|
752
|
|
|
753 // check connection before proceeding
|
|
|
754 if (!$this->is_connected()) {
|
|
|
755 return $this->last_result = false;
|
|
|
756 }
|
|
|
757
|
|
|
758 $this->debug('COMMIT TRANSACTION');
|
|
|
759
|
|
|
760 return $this->last_result = $this->dbh->commit();
|
|
|
761 }
|
|
|
762
|
|
|
763 /**
|
|
|
764 * Rollback transaction
|
|
|
765 *
|
|
|
766 * @return bool True on success, False on failure
|
|
|
767 */
|
|
|
768 public function rollbackTransaction()
|
|
|
769 {
|
|
|
770 $this->db_connect('w', true);
|
|
|
771
|
|
|
772 // check connection before proceeding
|
|
|
773 if (!$this->is_connected()) {
|
|
|
774 return $this->last_result = false;
|
|
|
775 }
|
|
|
776
|
|
|
777 $this->debug('ROLLBACK TRANSACTION');
|
|
|
778
|
|
|
779 return $this->last_result = $this->dbh->rollBack();
|
|
|
780 }
|
|
|
781
|
|
|
782 /**
|
|
|
783 * Release resources related to the last query result.
|
|
|
784 * When we know we don't need to access the last query result we can destroy it
|
|
|
785 * and release memory. Useful especially if the query returned big chunk of data.
|
|
|
786 */
|
|
|
787 public function reset()
|
|
|
788 {
|
|
|
789 $this->last_result = null;
|
|
|
790 }
|
|
|
791
|
|
|
792 /**
|
|
|
793 * Terminate database connection.
|
|
|
794 */
|
|
|
795 public function closeConnection()
|
|
|
796 {
|
|
|
797 $this->db_connected = false;
|
|
|
798 $this->db_index = 0;
|
|
|
799
|
|
|
800 // release statement and connection resources
|
|
|
801 $this->last_result = null;
|
|
|
802 $this->dbh = null;
|
|
|
803 $this->dbhs = array();
|
|
|
804 }
|
|
|
805
|
|
|
806 /**
|
|
|
807 * Formats input so it can be safely used in a query
|
|
|
808 *
|
|
|
809 * @param mixed $input Value to quote
|
|
|
810 * @param string $type Type of data (integer, bool, ident)
|
|
|
811 *
|
|
|
812 * @return string Quoted/converted string for use in query
|
|
|
813 */
|
|
|
814 public function quote($input, $type = null)
|
|
|
815 {
|
|
|
816 // handle int directly for better performance
|
|
|
817 if ($type == 'integer' || $type == 'int') {
|
|
|
818 return intval($input);
|
|
|
819 }
|
|
|
820
|
|
|
821 if (is_null($input)) {
|
|
|
822 return 'NULL';
|
|
|
823 }
|
|
|
824
|
|
|
825 if ($input instanceof DateTime) {
|
|
|
826 return $this->quote($input->format($this->options['datetime_format']));
|
|
|
827 }
|
|
|
828
|
|
|
829 if ($type == 'ident') {
|
|
|
830 return $this->quote_identifier($input);
|
|
|
831 }
|
|
|
832
|
|
|
833 // create DB handle if not available
|
|
|
834 if (!$this->dbh) {
|
|
|
835 $this->db_connect('r');
|
|
|
836 }
|
|
|
837
|
|
|
838 if ($this->dbh) {
|
|
|
839 $map = array(
|
|
|
840 'bool' => PDO::PARAM_BOOL,
|
|
|
841 'integer' => PDO::PARAM_INT,
|
|
|
842 );
|
|
|
843
|
|
|
844 $type = isset($map[$type]) ? $map[$type] : PDO::PARAM_STR;
|
|
|
845
|
|
|
846 return strtr($this->dbh->quote($input, $type),
|
|
|
847 // escape ? and `
|
|
|
848 array('?' => '??', self::DEFAULT_QUOTE => self::DEFAULT_QUOTE.self::DEFAULT_QUOTE)
|
|
|
849 );
|
|
|
850 }
|
|
|
851
|
|
|
852 return 'NULL';
|
|
|
853 }
|
|
|
854
|
|
|
855 /**
|
|
|
856 * Escapes a string so it can be safely used in a query
|
|
|
857 *
|
|
|
858 * @param string $str A string to escape
|
|
|
859 *
|
|
|
860 * @return string Escaped string for use in a query
|
|
|
861 */
|
|
|
862 public function escape($str)
|
|
|
863 {
|
|
|
864 if (is_null($str)) {
|
|
|
865 return 'NULL';
|
|
|
866 }
|
|
|
867
|
|
|
868 return substr($this->quote($str), 1, -1);
|
|
|
869 }
|
|
|
870
|
|
|
871 /**
|
|
|
872 * Quotes a string so it can be safely used as a table or column name
|
|
|
873 *
|
|
|
874 * @param string $str Value to quote
|
|
|
875 *
|
|
|
876 * @return string Quoted string for use in query
|
|
|
877 * @deprecated Replaced by rcube_db::quote_identifier
|
|
|
878 * @see rcube_db::quote_identifier
|
|
|
879 */
|
|
|
880 public function quoteIdentifier($str)
|
|
|
881 {
|
|
|
882 return $this->quote_identifier($str);
|
|
|
883 }
|
|
|
884
|
|
|
885 /**
|
|
|
886 * Escapes a string so it can be safely used in a query
|
|
|
887 *
|
|
|
888 * @param string $str A string to escape
|
|
|
889 *
|
|
|
890 * @return string Escaped string for use in a query
|
|
|
891 * @deprecated Replaced by rcube_db::escape
|
|
|
892 * @see rcube_db::escape
|
|
|
893 */
|
|
|
894 public function escapeSimple($str)
|
|
|
895 {
|
|
|
896 return $this->escape($str);
|
|
|
897 }
|
|
|
898
|
|
|
899 /**
|
|
|
900 * Quotes a string so it can be safely used as a table or column name
|
|
|
901 *
|
|
|
902 * @param string $str Value to quote
|
|
|
903 *
|
|
|
904 * @return string Quoted string for use in query
|
|
|
905 */
|
|
|
906 public function quote_identifier($str)
|
|
|
907 {
|
|
|
908 $start = $this->options['identifier_start'];
|
|
|
909 $end = $this->options['identifier_end'];
|
|
|
910 $name = array();
|
|
|
911
|
|
|
912 foreach (explode('.', $str) as $elem) {
|
|
|
913 $elem = str_replace(array($start, $end), '', $elem);
|
|
|
914 $name[] = $start . $elem . $end;
|
|
|
915 }
|
|
|
916
|
|
|
917 return implode($name, '.');
|
|
|
918 }
|
|
|
919
|
|
|
920 /**
|
|
|
921 * Return SQL function for current time and date
|
|
|
922 *
|
|
|
923 * @param int $interval Optional interval (in seconds) to add/subtract
|
|
|
924 *
|
|
|
925 * @return string SQL function to use in query
|
|
|
926 */
|
|
|
927 public function now($interval = 0)
|
|
|
928 {
|
|
|
929 if ($interval) {
|
|
|
930 $add = ' ' . ($interval > 0 ? '+' : '-') . ' INTERVAL ';
|
|
|
931 $add .= $interval > 0 ? intval($interval) : intval($interval) * -1;
|
|
|
932 $add .= ' SECOND';
|
|
|
933 }
|
|
|
934
|
|
|
935 return "now()" . $add;
|
|
|
936 }
|
|
|
937
|
|
|
938 /**
|
|
|
939 * Return list of elements for use with SQL's IN clause
|
|
|
940 *
|
|
|
941 * @param array $arr Input array
|
|
|
942 * @param string $type Type of data (integer, bool, ident)
|
|
|
943 *
|
|
|
944 * @return string Comma-separated list of quoted values for use in query
|
|
|
945 */
|
|
|
946 public function array2list($arr, $type = null)
|
|
|
947 {
|
|
|
948 if (!is_array($arr)) {
|
|
|
949 return $this->quote($arr, $type);
|
|
|
950 }
|
|
|
951
|
|
|
952 foreach ($arr as $idx => $item) {
|
|
|
953 $arr[$idx] = $this->quote($item, $type);
|
|
|
954 }
|
|
|
955
|
|
|
956 return implode(',', $arr);
|
|
|
957 }
|
|
|
958
|
|
|
959 /**
|
|
|
960 * Return SQL statement to convert a field value into a unix timestamp
|
|
|
961 *
|
|
|
962 * This method is deprecated and should not be used anymore due to limitations
|
|
|
963 * of timestamp functions in Mysql (year 2038 problem)
|
|
|
964 *
|
|
|
965 * @param string $field Field name
|
|
|
966 *
|
|
|
967 * @return string SQL statement to use in query
|
|
|
968 * @deprecated
|
|
|
969 */
|
|
|
970 public function unixtimestamp($field)
|
|
|
971 {
|
|
|
972 return "UNIX_TIMESTAMP($field)";
|
|
|
973 }
|
|
|
974
|
|
|
975 /**
|
|
|
976 * Return SQL statement to convert from a unix timestamp
|
|
|
977 *
|
|
|
978 * @param int $timestamp Unix timestamp
|
|
|
979 *
|
|
|
980 * @return string Date string in db-specific format
|
|
|
981 * @deprecated
|
|
|
982 */
|
|
|
983 public function fromunixtime($timestamp)
|
|
|
984 {
|
|
|
985 return $this->quote(date($this->options['datetime_format'], $timestamp));
|
|
|
986 }
|
|
|
987
|
|
|
988 /**
|
|
|
989 * Return SQL statement for case insensitive LIKE
|
|
|
990 *
|
|
|
991 * @param string $column Field name
|
|
|
992 * @param string $value Search value
|
|
|
993 *
|
|
|
994 * @return string SQL statement to use in query
|
|
|
995 */
|
|
|
996 public function ilike($column, $value)
|
|
|
997 {
|
|
|
998 return $this->quote_identifier($column).' LIKE '.$this->quote($value);
|
|
|
999 }
|
|
|
1000
|
|
|
1001 /**
|
|
|
1002 * Abstract SQL statement for value concatenation
|
|
|
1003 *
|
|
|
1004 * @return string SQL statement to be used in query
|
|
|
1005 */
|
|
|
1006 public function concat(/* col1, col2, ... */)
|
|
|
1007 {
|
|
|
1008 $args = func_get_args();
|
|
|
1009 if (is_array($args[0])) {
|
|
|
1010 $args = $args[0];
|
|
|
1011 }
|
|
|
1012
|
|
|
1013 return '(' . join(' || ', $args) . ')';
|
|
|
1014 }
|
|
|
1015
|
|
|
1016 /**
|
|
|
1017 * Encodes non-UTF-8 characters in string/array/object (recursive)
|
|
|
1018 *
|
|
|
1019 * @param mixed $input Data to fix
|
|
|
1020 * @param bool $serialized Enable serialization
|
|
|
1021 *
|
|
|
1022 * @return mixed Properly UTF-8 encoded data
|
|
|
1023 */
|
|
|
1024 public static function encode($input, $serialized = false)
|
|
|
1025 {
|
|
|
1026 // use Base64 encoding to workaround issues with invalid
|
|
|
1027 // or null characters in serialized string (#1489142)
|
|
|
1028 if ($serialized) {
|
|
|
1029 return base64_encode(serialize($input));
|
|
|
1030 }
|
|
|
1031
|
|
|
1032 if (is_object($input)) {
|
|
|
1033 foreach (get_object_vars($input) as $idx => $value) {
|
|
|
1034 $input->$idx = self::encode($value);
|
|
|
1035 }
|
|
|
1036 return $input;
|
|
|
1037 }
|
|
|
1038 else if (is_array($input)) {
|
|
|
1039 foreach ($input as $idx => $value) {
|
|
|
1040 $input[$idx] = self::encode($value);
|
|
|
1041 }
|
|
|
1042
|
|
|
1043 return $input;
|
|
|
1044 }
|
|
|
1045
|
|
|
1046 return utf8_encode($input);
|
|
|
1047 }
|
|
|
1048
|
|
|
1049 /**
|
|
|
1050 * Decodes encoded UTF-8 string/object/array (recursive)
|
|
|
1051 *
|
|
|
1052 * @param mixed $input Input data
|
|
|
1053 * @param bool $serialized Enable serialization
|
|
|
1054 *
|
|
|
1055 * @return mixed Decoded data
|
|
|
1056 */
|
|
|
1057 public static function decode($input, $serialized = false)
|
|
|
1058 {
|
|
|
1059 // use Base64 encoding to workaround issues with invalid
|
|
|
1060 // or null characters in serialized string (#1489142)
|
|
|
1061 if ($serialized) {
|
|
|
1062 // Keep backward compatybility where base64 wasn't used
|
|
|
1063 if (strpos(substr($input, 0, 16), ':') !== false) {
|
|
|
1064 return self::decode(@unserialize($input));
|
|
|
1065 }
|
|
|
1066
|
|
|
1067 return @unserialize(base64_decode($input));
|
|
|
1068 }
|
|
|
1069
|
|
|
1070 if (is_object($input)) {
|
|
|
1071 foreach (get_object_vars($input) as $idx => $value) {
|
|
|
1072 $input->$idx = self::decode($value);
|
|
|
1073 }
|
|
|
1074 return $input;
|
|
|
1075 }
|
|
|
1076 else if (is_array($input)) {
|
|
|
1077 foreach ($input as $idx => $value) {
|
|
|
1078 $input[$idx] = self::decode($value);
|
|
|
1079 }
|
|
|
1080 return $input;
|
|
|
1081 }
|
|
|
1082
|
|
|
1083 return utf8_decode($input);
|
|
|
1084 }
|
|
|
1085
|
|
|
1086 /**
|
|
|
1087 * Return correct name for a specific database table
|
|
|
1088 *
|
|
|
1089 * @param string $table Table name
|
|
|
1090 * @param bool $quoted Quote table identifier
|
|
|
1091 *
|
|
|
1092 * @return string Translated table name
|
|
|
1093 */
|
|
|
1094 public function table_name($table, $quoted = false)
|
|
|
1095 {
|
|
|
1096 // let plugins alter the table name (#1489837)
|
|
|
1097 $plugin = rcube::get_instance()->plugins->exec_hook('db_table_name', array('table' => $table));
|
|
|
1098 $table = $plugin['table'];
|
|
|
1099
|
|
|
1100 // add prefix to the table name if configured
|
|
|
1101 if (($prefix = $this->options['table_prefix']) && strpos($table, $prefix) !== 0) {
|
|
|
1102 $table = $prefix . $table;
|
|
|
1103 }
|
|
|
1104
|
|
|
1105 if ($quoted) {
|
|
|
1106 $table = $this->quote_identifier($table);
|
|
|
1107 }
|
|
|
1108
|
|
|
1109 return $table;
|
|
|
1110 }
|
|
|
1111
|
|
|
1112 /**
|
|
|
1113 * Set class option value
|
|
|
1114 *
|
|
|
1115 * @param string $name Option name
|
|
|
1116 * @param mixed $value Option value
|
|
|
1117 */
|
|
|
1118 public function set_option($name, $value)
|
|
|
1119 {
|
|
|
1120 $this->options[$name] = $value;
|
|
|
1121 }
|
|
|
1122
|
|
|
1123 /**
|
|
|
1124 * Set DSN connection to be used for the given table
|
|
|
1125 *
|
|
|
1126 * @param string $table Table name
|
|
|
1127 * @param string $mode DSN connection ('r' or 'w') to be used
|
|
|
1128 */
|
|
|
1129 public function set_table_dsn($table, $mode)
|
|
|
1130 {
|
|
|
1131 $this->options['table_dsn_map'][$this->table_name($table)] = $mode;
|
|
|
1132 }
|
|
|
1133
|
|
|
1134 /**
|
|
|
1135 * MDB2 DSN string parser
|
|
|
1136 *
|
|
|
1137 * @param string $sequence Secuence name
|
|
|
1138 *
|
|
|
1139 * @return array DSN parameters
|
|
|
1140 */
|
|
|
1141 public static function parse_dsn($dsn)
|
|
|
1142 {
|
|
|
1143 if (empty($dsn)) {
|
|
|
1144 return null;
|
|
|
1145 }
|
|
|
1146
|
|
|
1147 // Find phptype and dbsyntax
|
|
|
1148 if (($pos = strpos($dsn, '://')) !== false) {
|
|
|
1149 $str = substr($dsn, 0, $pos);
|
|
|
1150 $dsn = substr($dsn, $pos + 3);
|
|
|
1151 }
|
|
|
1152 else {
|
|
|
1153 $str = $dsn;
|
|
|
1154 $dsn = null;
|
|
|
1155 }
|
|
|
1156
|
|
|
1157 // Get phptype and dbsyntax
|
|
|
1158 // $str => phptype(dbsyntax)
|
|
|
1159 if (preg_match('|^(.+?)\((.*?)\)$|', $str, $arr)) {
|
|
|
1160 $parsed['phptype'] = $arr[1];
|
|
|
1161 $parsed['dbsyntax'] = !$arr[2] ? $arr[1] : $arr[2];
|
|
|
1162 }
|
|
|
1163 else {
|
|
|
1164 $parsed['phptype'] = $str;
|
|
|
1165 $parsed['dbsyntax'] = $str;
|
|
|
1166 }
|
|
|
1167
|
|
|
1168 if (empty($dsn)) {
|
|
|
1169 return $parsed;
|
|
|
1170 }
|
|
|
1171
|
|
|
1172 // Get (if found): username and password
|
|
|
1173 // $dsn => username:password@protocol+hostspec/database
|
|
|
1174 if (($at = strrpos($dsn,'@')) !== false) {
|
|
|
1175 $str = substr($dsn, 0, $at);
|
|
|
1176 $dsn = substr($dsn, $at + 1);
|
|
|
1177 if (($pos = strpos($str, ':')) !== false) {
|
|
|
1178 $parsed['username'] = rawurldecode(substr($str, 0, $pos));
|
|
|
1179 $parsed['password'] = rawurldecode(substr($str, $pos + 1));
|
|
|
1180 }
|
|
|
1181 else {
|
|
|
1182 $parsed['username'] = rawurldecode($str);
|
|
|
1183 }
|
|
|
1184 }
|
|
|
1185
|
|
|
1186 // Find protocol and hostspec
|
|
|
1187
|
|
|
1188 // $dsn => proto(proto_opts)/database
|
|
|
1189 if (preg_match('|^([^(]+)\((.*?)\)/?(.*?)$|', $dsn, $match)) {
|
|
|
1190 $proto = $match[1];
|
|
|
1191 $proto_opts = $match[2] ? $match[2] : false;
|
|
|
1192 $dsn = $match[3];
|
|
|
1193 }
|
|
|
1194 // $dsn => protocol+hostspec/database (old format)
|
|
|
1195 else {
|
|
|
1196 if (strpos($dsn, '+') !== false) {
|
|
|
1197 list($proto, $dsn) = explode('+', $dsn, 2);
|
|
|
1198 }
|
|
|
1199 if ( strpos($dsn, '//') === 0
|
|
|
1200 && strpos($dsn, '/', 2) !== false
|
|
|
1201 && $parsed['phptype'] == 'oci8'
|
|
|
1202 ) {
|
|
|
1203 //oracle's "Easy Connect" syntax:
|
|
|
1204 //"username/password@[//]host[:port][/service_name]"
|
|
|
1205 //e.g. "scott/tiger@//mymachine:1521/oracle"
|
|
|
1206 $proto_opts = $dsn;
|
|
|
1207 $pos = strrpos($proto_opts, '/');
|
|
|
1208 $dsn = substr($proto_opts, $pos + 1);
|
|
|
1209 $proto_opts = substr($proto_opts, 0, $pos);
|
|
|
1210 }
|
|
|
1211 else if (strpos($dsn, '/') !== false) {
|
|
|
1212 list($proto_opts, $dsn) = explode('/', $dsn, 2);
|
|
|
1213 }
|
|
|
1214 else {
|
|
|
1215 $proto_opts = $dsn;
|
|
|
1216 $dsn = null;
|
|
|
1217 }
|
|
|
1218 }
|
|
|
1219
|
|
|
1220 // process the different protocol options
|
|
|
1221 $parsed['protocol'] = $proto ?: 'tcp';
|
|
|
1222 $proto_opts = rawurldecode($proto_opts);
|
|
|
1223 if (strpos($proto_opts, ':') !== false) {
|
|
|
1224 list($proto_opts, $parsed['port']) = explode(':', $proto_opts);
|
|
|
1225 }
|
|
|
1226 if ($parsed['protocol'] == 'tcp') {
|
|
|
1227 $parsed['hostspec'] = $proto_opts;
|
|
|
1228 }
|
|
|
1229 else if ($parsed['protocol'] == 'unix') {
|
|
|
1230 $parsed['socket'] = $proto_opts;
|
|
|
1231 }
|
|
|
1232
|
|
|
1233 // Get dabase if any
|
|
|
1234 // $dsn => database
|
|
|
1235 if ($dsn) {
|
|
|
1236 // /database
|
|
|
1237 if (($pos = strpos($dsn, '?')) === false) {
|
|
|
1238 $parsed['database'] = rawurldecode($dsn);
|
|
|
1239 // /database?param1=value1¶m2=value2
|
|
|
1240 }
|
|
|
1241 else {
|
|
|
1242 $parsed['database'] = rawurldecode(substr($dsn, 0, $pos));
|
|
|
1243 $dsn = substr($dsn, $pos + 1);
|
|
|
1244 if (strpos($dsn, '&') !== false) {
|
|
|
1245 $opts = explode('&', $dsn);
|
|
|
1246 }
|
|
|
1247 else { // database?param1=value1
|
|
|
1248 $opts = array($dsn);
|
|
|
1249 }
|
|
|
1250 foreach ($opts as $opt) {
|
|
|
1251 list($key, $value) = explode('=', $opt);
|
|
|
1252 if (!array_key_exists($key, $parsed) || false === $parsed[$key]) {
|
|
|
1253 // don't allow params overwrite
|
|
|
1254 $parsed[$key] = rawurldecode($value);
|
|
|
1255 }
|
|
|
1256 }
|
|
|
1257 }
|
|
|
1258 }
|
|
|
1259
|
|
|
1260 return $parsed;
|
|
|
1261 }
|
|
|
1262
|
|
|
1263 /**
|
|
|
1264 * Returns PDO DSN string from DSN array
|
|
|
1265 *
|
|
|
1266 * @param array $dsn DSN parameters
|
|
|
1267 *
|
|
|
1268 * @return string DSN string
|
|
|
1269 */
|
|
|
1270 protected function dsn_string($dsn)
|
|
|
1271 {
|
|
|
1272 $params = array();
|
|
|
1273 $result = $dsn['phptype'] . ':';
|
|
|
1274
|
|
|
1275 if ($dsn['hostspec']) {
|
|
|
1276 $params[] = 'host=' . $dsn['hostspec'];
|
|
|
1277 }
|
|
|
1278
|
|
|
1279 if ($dsn['port']) {
|
|
|
1280 $params[] = 'port=' . $dsn['port'];
|
|
|
1281 }
|
|
|
1282
|
|
|
1283 if ($dsn['database']) {
|
|
|
1284 $params[] = 'dbname=' . $dsn['database'];
|
|
|
1285 }
|
|
|
1286
|
|
|
1287 if (!empty($params)) {
|
|
|
1288 $result .= implode(';', $params);
|
|
|
1289 }
|
|
|
1290
|
|
|
1291 return $result;
|
|
|
1292 }
|
|
|
1293
|
|
|
1294 /**
|
|
|
1295 * Returns driver-specific connection options
|
|
|
1296 *
|
|
|
1297 * @param array $dsn DSN parameters
|
|
|
1298 *
|
|
|
1299 * @return array Connection options
|
|
|
1300 */
|
|
|
1301 protected function dsn_options($dsn)
|
|
|
1302 {
|
|
|
1303 $result = array();
|
|
|
1304
|
|
|
1305 if ($this->db_pconn) {
|
|
|
1306 $result[PDO::ATTR_PERSISTENT] = true;
|
|
|
1307 }
|
|
|
1308
|
|
|
1309 if (!empty($dsn['prefetch'])) {
|
|
|
1310 $result[PDO::ATTR_PREFETCH] = (int) $dsn['prefetch'];
|
|
|
1311 }
|
|
|
1312
|
|
|
1313 if (!empty($dsn['timeout'])) {
|
|
|
1314 $result[PDO::ATTR_TIMEOUT] = (int) $dsn['timeout'];
|
|
|
1315 }
|
|
|
1316
|
|
|
1317 return $result;
|
|
|
1318 }
|
|
|
1319
|
|
|
1320 /**
|
|
|
1321 * Execute the given SQL script
|
|
|
1322 *
|
|
|
1323 * @param string $sql SQL queries to execute
|
|
|
1324 *
|
|
|
1325 * @return boolen True on success, False on error
|
|
|
1326 */
|
|
|
1327 public function exec_script($sql)
|
|
|
1328 {
|
|
|
1329 $sql = $this->fix_table_names($sql);
|
|
|
1330 $buff = '';
|
|
|
1331 $exec = '';
|
|
|
1332
|
|
|
1333 foreach (explode("\n", $sql) as $line) {
|
|
|
1334 $trimmed = trim($line);
|
|
|
1335 if ($trimmed == '' || preg_match('/^--/', $trimmed)) {
|
|
|
1336 continue;
|
|
|
1337 }
|
|
|
1338
|
|
|
1339 if ($trimmed == 'GO') {
|
|
|
1340 $exec = $buff;
|
|
|
1341 }
|
|
|
1342 else if ($trimmed[strlen($trimmed)-1] == ';') {
|
|
|
1343 $exec = $buff . substr(rtrim($line), 0, -1);
|
|
|
1344 }
|
|
|
1345
|
|
|
1346 if ($exec) {
|
|
|
1347 $this->query($exec);
|
|
|
1348 $buff = '';
|
|
|
1349 $exec = '';
|
|
|
1350 if ($this->db_error) {
|
|
|
1351 break;
|
|
|
1352 }
|
|
|
1353 }
|
|
|
1354 else {
|
|
|
1355 $buff .= $line . "\n";
|
|
|
1356 }
|
|
|
1357 }
|
|
|
1358
|
|
|
1359 return !$this->db_error;
|
|
|
1360 }
|
|
|
1361
|
|
|
1362 /**
|
|
|
1363 * Parse SQL file and fix table names according to table prefix
|
|
|
1364 */
|
|
|
1365 protected function fix_table_names($sql)
|
|
|
1366 {
|
|
|
1367 if (!$this->options['table_prefix']) {
|
|
|
1368 return $sql;
|
|
|
1369 }
|
|
|
1370
|
|
|
1371 $sql = preg_replace_callback(
|
|
|
1372 '/((TABLE|TRUNCATE|(?<!ON )UPDATE|INSERT INTO|FROM'
|
|
|
1373 . '| ON(?! (DELETE|UPDATE))|REFERENCES|CONSTRAINT|FOREIGN KEY|INDEX)'
|
|
|
1374 . '\s+(IF (NOT )?EXISTS )?[`"]*)([^`"\( \r\n]+)/',
|
|
|
1375 array($this, 'fix_table_names_callback'),
|
|
|
1376 $sql
|
|
|
1377 );
|
|
|
1378
|
|
|
1379 return $sql;
|
|
|
1380 }
|
|
|
1381
|
|
|
1382 /**
|
|
|
1383 * Preg_replace callback for fix_table_names()
|
|
|
1384 */
|
|
|
1385 protected function fix_table_names_callback($matches)
|
|
|
1386 {
|
|
|
1387 return $matches[1] . $this->options['table_prefix'] . $matches[count($matches)-1];
|
|
|
1388 }
|
|
|
1389 }
|
