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