1 <?php
2 /**
3 * @package FrameworkOnFramework
4 * @subpackage database
5 * @copyright Copyright (C) 2010-2016 Nicholas K. Dionysopoulos / Akeeba Ltd. All rights reserved.
6 * @license GNU General Public License version 2 or later; see LICENSE.txt
7 *
8 * This file is adapted from the Joomla! Platform. It is used to iterate a database cursor returning FOFTable objects
9 * instead of plain stdClass objects
10 */
11
12 // Protect from unauthorized access
13 defined('FOF_INCLUDED') or die;
14
15 /**
16 * MySQL database driver supporting PDO based connections
17 *
18 * @package Joomla.Platform
19 * @subpackage Database
20 * @see http://php.net/manual/en/ref.pdo-mysql.php
21 * @since 3.4
22 */
23 class FOFDatabaseDriverPdomysql extends FOFDatabaseDriverPdo
24 {
25 /**
26 * The name of the database driver.
27 *
28 * @var string
29 * @since 3.4
30 */
31 public $name = 'pdomysql';
32
33 /**
34 * The type of the database server family supported by this driver.
35 *
36 * @var string
37 * @since CMS 3.5.0
38 */
39 public $serverType = 'mysql';
40
41 /**
42 * The character(s) used to quote SQL statement names such as table names or field names,
43 * etc. The child classes should define this as necessary. If a single character string the
44 * same character is used for both sides of the quoted name, else the first character will be
45 * used for the opening quote and the second for the closing quote.
46 *
47 * @var string
48 * @since 3.4
49 */
50 protected $nameQuote = '`';
51
52 /**
53 * The null or zero representation of a timestamp for the database driver. This should be
54 * defined in child classes to hold the appropriate value for the engine.
55 *
56 * @var string
57 * @since 3.4
58 */
59 protected $nullDate = '0000-00-00 00:00:00';
60
61 /**
62 * The minimum supported database version.
63 *
64 * @var string
65 * @since 3.4
66 */
67 protected static $dbMinimum = '5.0.4';
68
69 /**
70 * Constructor.
71 *
72 * @param array $options Array of database options with keys: host, user, password, database, select.
73 *
74 * @since 3.4
75 */
76 public function __construct($options)
77 {
78 /**
79 * Pre-populate the UTF-8 Multibyte compatibility flag. Unfortuantely PDO won't report the server version
80 * unless we're connected to it and we cannot connect to it unless we know if it supports utf8mb4 which requires
81 * us knowing the server version. Between this chicken and egg issue we _assume_ it's supported and we'll just
82 * catch any problems at connection time.
83 */
84 $this->utf8mb4 = true;
85
86 // Get some basic values from the options.
87 $options['driver'] = 'mysql';
88 $options['charset'] = (isset($options['charset'])) ? $options['charset'] : 'utf8';
89
90 if ($this->utf8mb4 && ($options['charset'] == 'utf8'))
91 {
92 $options['charset'] = 'utf8mb4';
93 }
94
95 $this->charset = $options['charset'];
96
97 // Finalize initialisation.
98 parent::__construct($options);
99 }
100
101 /**
102 * Connects to the database if needed.
103 *
104 * @return void Returns void if the database connected successfully.
105 *
106 * @since 3.4
107 * @throws RuntimeException
108 */
109 public function connect()
110 {
111 try
112 {
113 // Try to connect to MySQL
114 parent::connect();
115 }
116 catch (\RuntimeException $e)
117 {
118 // If the connection failed but not because of the wrong character set bubble up the exception
119 if (!$this->utf8mb4 || ($this->options['charset'] != 'utf8mb4'))
120 {
121 throw $e;
122 }
123
124 /**
125 * If the connection failed and I was trying to use the utf8mb4 charset then it is likely that the server
126 * doesn't support utf8mb4 despite claiming otherwise.
127 *
128 * This happens on old MySQL server versions (less than 5.5.3) using the mysqlnd PHP driver. Since mysqlnd
129 * masks the server version and reports only its own we can not be sure if the server actually does support
130 * UTF-8 Multibyte (i.e. it's MySQL 5.5.3 or later). Since the utf8mb4 charset is undefined in this case we
131 * catch the error and determine that utf8mb4 is not supported!
132 */
133 $this->utf8mb4 = false;
134 $this->options['charset'] = 'utf8';
135
136 parent::connect();
137 }
138
139 $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
140 $this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
141 }
142
143 /**
144 * Test to see if the MySQL connector is available.
145 *
146 * @return boolean True on success, false otherwise.
147 *
148 * @since 3.4
149 */
150 public static function isSupported()
151 {
152 return class_exists('PDO') && in_array('mysql', PDO::getAvailableDrivers());
153 }
154
155 /**
156 * Drops a table from the database.
157 *
158 * @param string $tableName The name of the database table to drop.
159 * @param boolean $ifExists Optionally specify that the table must exist before it is dropped.
160 *
161 * @return FOFDatabaseDriverPdomysql Returns this object to support chaining.
162 *
163 * @since 3.4
164 * @throws RuntimeException
165 */
166 public function dropTable($tableName, $ifExists = true)
167 {
168 $this->connect();
169
170 $query = $this->getQuery(true);
171
172 $query->setQuery('DROP TABLE ' . ($ifExists ? 'IF EXISTS ' : '') . $this->quoteName($tableName));
173
174 $this->setQuery($query);
175
176 $this->execute();
177
178 return $this;
179 }
180
181 /**
182 * Select a database for use.
183 *
184 * @param string $database The name of the database to select for use.
185 *
186 * @return boolean True if the database was successfully selected.
187 *
188 * @since 3.4
189 * @throws RuntimeException
190 */
191 public function select($database)
192 {
193 $this->connect();
194
195 $this->setQuery('USE ' . $this->quoteName($database));
196
197 $this->execute();
198
199 return $this;
200 }
201
202 /**
203 * Method to get the database collation in use by sampling a text field of a table in the database.
204 *
205 * @return mixed The collation in use by the database (string) or boolean false if not supported.
206 *
207 * @since 3.4
208 * @throws RuntimeException
209 */
210 public function getCollation()
211 {
212 $this->connect();
213
214 // Attempt to get the database collation by accessing the server system variable.
215 $this->setQuery('SHOW VARIABLES LIKE "collation_database"');
216 $result = $this->loadObject();
217
218 if (property_exists($result, 'Value'))
219 {
220 return $result->Value;
221 }
222 else
223 {
224 return false;
225 }
226 }
227
228 /**
229 * Method to get the database connection collation, as reported by the driver. If the connector doesn't support
230 * reporting this value please return an empty string.
231 *
232 * @return string
233 */
234 public function getConnectionCollation()
235 {
236 $this->connect();
237
238 // Attempt to get the database collation by accessing the server system variable.
239 $this->setQuery('SHOW VARIABLES LIKE "collation_connection"');
240 $result = $this->loadObject();
241
242 if (property_exists($result, 'Value'))
243 {
244 return $result->Value;
245 }
246 else
247 {
248 return false;
249 }
250 }
251
252 /**
253 * Shows the table CREATE statement that creates the given tables.
254 *
255 * @param mixed $tables A table name or a list of table names.
256 *
257 * @return array A list of the create SQL for the tables.
258 *
259 * @since 3.4
260 * @throws RuntimeException
261 */
262 public function getTableCreate($tables)
263 {
264 $this->connect();
265
266 // Initialise variables.
267 $result = array();
268
269 // Sanitize input to an array and iterate over the list.
270 settype($tables, 'array');
271
272 foreach ($tables as $table)
273 {
274 $this->setQuery('SHOW CREATE TABLE ' . $this->quoteName($table));
275
276 $row = $this->loadRow();
277
278 // Populate the result array based on the create statements.
279 $result[$table] = $row[1];
280 }
281
282 return $result;
283 }
284
285 /**
286 * Retrieves field information about a given table.
287 *
288 * @param string $table The name of the database table.
289 * @param boolean $typeOnly True to only return field types.
290 *
291 * @return array An array of fields for the database table.
292 *
293 * @since 3.4
294 * @throws RuntimeException
295 */
296 public function getTableColumns($table, $typeOnly = true)
297 {
298 $this->connect();
299
300 $result = array();
301
302 // Set the query to get the table fields statement.
303 $this->setQuery('SHOW FULL COLUMNS FROM ' . $this->quoteName($table));
304
305 $fields = $this->loadObjectList();
306
307 // If we only want the type as the value add just that to the list.
308 if ($typeOnly)
309 {
310 foreach ($fields as $field)
311 {
312 $result[$field->Field] = preg_replace("/[(0-9)]/", '', $field->Type);
313 }
314 }
315 // If we want the whole field data object add that to the list.
316 else
317 {
318 foreach ($fields as $field)
319 {
320 $result[$field->Field] = $field;
321 }
322 }
323
324 return $result;
325 }
326
327 /**
328 * Get the details list of keys for a table.
329 *
330 * @param string $table The name of the table.
331 *
332 * @return array An array of the column specification for the table.
333 *
334 * @since 3.4
335 * @throws RuntimeException
336 */
337 public function getTableKeys($table)
338 {
339 $this->connect();
340
341 // Get the details columns information.
342 $this->setQuery('SHOW KEYS FROM ' . $this->quoteName($table));
343
344 $keys = $this->loadObjectList();
345
346 return $keys;
347 }
348
349 /**
350 * Method to get an array of all tables in the database.
351 *
352 * @return array An array of all the tables in the database.
353 *
354 * @since 3.4
355 * @throws RuntimeException
356 */
357 public function getTableList()
358 {
359 $this->connect();
360
361 // Set the query to get the tables statement.
362 $this->setQuery('SHOW TABLES');
363 $tables = $this->loadColumn();
364
365 return $tables;
366 }
367
368 /**
369 * Get the version of the database connector.
370 *
371 * @return string The database connector version.
372 *
373 * @since 3.4
374 */
375 public function getVersion()
376 {
377 $this->connect();
378
379 return $this->getOption(PDO::ATTR_SERVER_VERSION);
380 }
381
382 /**
383 * Locks a table in the database.
384 *
385 * @param string $table The name of the table to unlock.
386 *
387 * @return FOFDatabaseDriverPdomysql Returns this object to support chaining.
388 *
389 * @since 3.4
390 * @throws RuntimeException
391 */
392 public function lockTable($table)
393 {
394 $this->setQuery('LOCK TABLES ' . $this->quoteName($table) . ' WRITE')->execute();
395
396 return $this;
397 }
398
399 /**
400 * Renames a table in the database.
401 *
402 * @param string $oldTable The name of the table to be renamed
403 * @param string $newTable The new name for the table.
404 * @param string $backup Not used by MySQL.
405 * @param string $prefix Not used by MySQL.
406 *
407 * @return FOFDatabaseDriverPdomysql Returns this object to support chaining.
408 *
409 * @since 3.4
410 * @throws RuntimeException
411 */
412 public function renameTable($oldTable, $newTable, $backup = null, $prefix = null)
413 {
414 $this->setQuery('RENAME TABLE ' . $this->quoteName($oldTable) . ' TO ' . $this->quoteName($newTable));
415
416 $this->execute();
417
418 return $this;
419 }
420
421 /**
422 * Method to escape a string for usage in an SQL statement.
423 *
424 * Oracle escaping reference:
425 * http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_escape_special_characters_when_writing_SQL_queries.3F
426 *
427 * SQLite escaping notes:
428 * http://www.sqlite.org/faq.html#q14
429 *
430 * Method body is as implemented by the Zend Framework
431 *
432 * Note: Using query objects with bound variables is
433 * preferable to the below.
434 *
435 * @param string $text The string to be escaped.
436 * @param boolean $extra Unused optional parameter to provide extra escaping.
437 *
438 * @return string The escaped string.
439 *
440 * @since 3.4
441 */
442 public function escape($text, $extra = false)
443 {
444 $this->connect();
445
446 if (is_int($text) || is_float($text))
447 {
448 return $text;
449 }
450
451 $result = substr($this->connection->quote($text), 1, -1);
452
453 if ($extra)
454 {
455 $result = addcslashes($result, '%_');
456 }
457
458 return $result;
459 }
460
461 /**
462 * Unlocks tables in the database.
463 *
464 * @return FOFDatabaseDriverPdomysql Returns this object to support chaining.
465 *
466 * @since 3.4
467 * @throws RuntimeException
468 */
469 public function unlockTables()
470 {
471 $this->setQuery('UNLOCK TABLES')->execute();
472
473 return $this;
474 }
475
476 /**
477 * Method to commit a transaction.
478 *
479 * @param boolean $toSavepoint If true, commit to the last savepoint.
480 *
481 * @return void
482 *
483 * @since 3.4
484 * @throws RuntimeException
485 */
486 public function transactionCommit($toSavepoint = false)
487 {
488 $this->connect();
489
490 if (!$toSavepoint || $this->transactionDepth <= 1)
491 {
492 parent::transactionCommit($toSavepoint);
493 }
494 else
495 {
496 $this->transactionDepth--;
497 }
498 }
499
500 /**
501 * Method to roll back a transaction.
502 *
503 * @param boolean $toSavepoint If true, rollback to the last savepoint.
504 *
505 * @return void
506 *
507 * @since 3.4
508 * @throws RuntimeException
509 */
510 public function transactionRollback($toSavepoint = false)
511 {
512 $this->connect();
513
514 if (!$toSavepoint || $this->transactionDepth <= 1)
515 {
516 parent::transactionRollback($toSavepoint);
517 }
518 else
519 {
520 $savepoint = 'SP_' . ($this->transactionDepth - 1);
521 $this->setQuery('ROLLBACK TO SAVEPOINT ' . $this->quoteName($savepoint));
522
523 if ($this->execute())
524 {
525 $this->transactionDepth--;
526 }
527 }
528 }
529
530 /**
531 * Method to initialize a transaction.
532 *
533 * @param boolean $asSavepoint If true and a transaction is already active, a savepoint will be created.
534 *
535 * @return void
536 *
537 * @since 3.4
538 * @throws RuntimeException
539 */
540 public function transactionStart($asSavepoint = false)
541 {
542 $this->connect();
543
544 if (!$asSavepoint || !$this->transactionDepth)
545 {
546 parent::transactionStart($asSavepoint);
547 }
548 else
549 {
550 $savepoint = 'SP_' . $this->transactionDepth;
551 $this->setQuery('SAVEPOINT ' . $this->quoteName($savepoint));
552
553 if ($this->execute())
554 {
555 $this->transactionDepth++;
556 }
557 }
558 }
559 }
560