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 * SQLite database driver
14 *
15 * @link https://secure.php.net/pdo
16 * @since 12.1
17 */
18 class JDatabaseDriverSqlite extends JDatabaseDriverPdo
19 {
20 /**
21 * The name of the database driver.
22 *
23 * @var string
24 * @since 12.1
25 */
26 public $name = 'sqlite';
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 = 'sqlite';
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 12.1
44 */
45 protected $nameQuote = '`';
46
47 /**
48 * Destructor.
49 *
50 * @since 12.1
51 */
52 public function __destruct()
53 {
54 $this->freeResult();
55 $this->connection = null;
56 }
57
58 /**
59 * Connects to the database if needed.
60 *
61 * @return void Returns void if the database connected successfully.
62 *
63 * @since 12.1
64 * @throws RuntimeException
65 */
66 public function connect()
67 {
68 if ($this->connection)
69 {
70 return;
71 }
72
73 parent::connect();
74
75 $this->connection->sqliteCreateFunction(
76 'ROW_NUMBER',
77 function($init = null)
78 {
79 static $rownum, $partition;
80
81 if ($init !== null)
82 {
83 $rownum = $init;
84 $partition = null;
85
86 return $rownum;
87 }
88
89 $args = func_get_args();
90 array_shift($args);
91
92 $partitionBy = $args ? implode(',', $args) : null;
93
94 if ($partitionBy === null || $partitionBy === $partition)
95 {
96 $rownum++;
97 }
98 else
99 {
100 $rownum = 1;
101 $partition = $partitionBy;
102 }
103
104 return $rownum;
105 }
106 );
107 }
108
109 /**
110 * Disconnects the database.
111 *
112 * @return void
113 *
114 * @since 12.1
115 */
116 public function disconnect()
117 {
118 $this->freeResult();
119 $this->connection = null;
120 }
121
122 /**
123 * Drops a table from the database.
124 *
125 * @param string $tableName The name of the database table to drop.
126 * @param boolean $ifExists Optionally specify that the table must exist before it is dropped.
127 *
128 * @return JDatabaseDriverSqlite Returns this object to support chaining.
129 *
130 * @since 12.1
131 */
132 public function dropTable($tableName, $ifExists = true)
133 {
134 $this->connect();
135
136 $query = $this->getQuery(true);
137
138 $this->setQuery('DROP TABLE ' . ($ifExists ? 'IF EXISTS ' : '') . $query->quoteName($tableName));
139
140 $this->execute();
141
142 return $this;
143 }
144
145 /**
146 * Method to escape a string for usage in an SQLite statement.
147 *
148 * Note: Using query objects with bound variables is
149 * preferable to the below.
150 *
151 * @param string $text The string to be escaped.
152 * @param boolean $extra Unused optional parameter to provide extra escaping.
153 *
154 * @return string The escaped string.
155 *
156 * @since 12.1
157 */
158 public function escape($text, $extra = false)
159 {
160 if (is_int($text) || is_float($text))
161 {
162 return $text;
163 }
164
165 return SQLite3::escapeString($text);
166 }
167
168 /**
169 * Method to get the database collation in use by sampling a text field of a table in the database.
170 *
171 * @return mixed The collation in use by the database or boolean false if not supported.
172 *
173 * @since 12.1
174 */
175 public function getCollation()
176 {
177 return $this->charset;
178 }
179
180 /**
181 * Method to get the database connection collation, as reported by the driver. If the connector doesn't support
182 * reporting this value please return an empty string.
183 *
184 * @return string
185 */
186 public function getConnectionCollation()
187 {
188 return $this->charset;
189 }
190
191 /**
192 * Shows the table CREATE statement that creates the given tables.
193 *
194 * Note: Doesn't appear to have support in SQLite
195 *
196 * @param mixed $tables A table name or a list of table names.
197 *
198 * @return array A list of the create SQL for the tables.
199 *
200 * @since 12.1
201 * @throws RuntimeException
202 */
203 public function getTableCreate($tables)
204 {
205 $this->connect();
206
207 // Sanitize input to an array and iterate over the list.
208 settype($tables, 'array');
209
210 return $tables;
211 }
212
213 /**
214 * Retrieves field information about a given table.
215 *
216 * @param string $table The name of the database table.
217 * @param boolean $typeOnly True to only return field types.
218 *
219 * @return array An array of fields for the database table.
220 *
221 * @since 12.1
222 * @throws RuntimeException
223 */
224 public function getTableColumns($table, $typeOnly = true)
225 {
226 $this->connect();
227
228 $columns = array();
229 $query = $this->getQuery(true);
230
231 $fieldCasing = $this->getOption(PDO::ATTR_CASE);
232
233 $this->setOption(PDO::ATTR_CASE, PDO::CASE_UPPER);
234
235 $table = strtoupper($table);
236
237 $query->setQuery('pragma table_info(' . $table . ')');
238
239 $this->setQuery($query);
240 $fields = $this->loadObjectList();
241
242 if ($typeOnly)
243 {
244 foreach ($fields as $field)
245 {
246 $columns[$field->NAME] = $field->TYPE;
247 }
248 }
249 else
250 {
251 foreach ($fields as $field)
252 {
253 // Do some dirty translation to MySQL output.
254 // TODO: Come up with and implement a standard across databases.
255 $columns[$field->NAME] = (object) array(
256 'Field' => $field->NAME,
257 'Type' => $field->TYPE,
258 'Null' => ($field->NOTNULL == '1' ? 'NO' : 'YES'),
259 'Default' => $field->DFLT_VALUE,
260 'Key' => ($field->PK != '0' ? 'PRI' : ''),
261 );
262 }
263 }
264
265 $this->setOption(PDO::ATTR_CASE, $fieldCasing);
266
267 return $columns;
268 }
269
270 /**
271 * Get the details list of keys for a table.
272 *
273 * @param string $table The name of the table.
274 *
275 * @return array An array of the column specification for the table.
276 *
277 * @since 12.1
278 * @throws RuntimeException
279 */
280 public function getTableKeys($table)
281 {
282 $this->connect();
283
284 $keys = array();
285 $query = $this->getQuery(true);
286
287 $fieldCasing = $this->getOption(PDO::ATTR_CASE);
288
289 $this->setOption(PDO::ATTR_CASE, PDO::CASE_UPPER);
290
291 $table = strtoupper($table);
292 $query->setQuery('pragma table_info( ' . $table . ')');
293
294 // $query->bind(':tableName', $table);
295
296 $this->setQuery($query);
297 $rows = $this->loadObjectList();
298
299 foreach ($rows as $column)
300 {
301 if ($column->PK == 1)
302 {
303 $keys[$column->NAME] = $column;
304 }
305 }
306
307 $this->setOption(PDO::ATTR_CASE, $fieldCasing);
308
309 return $keys;
310 }
311
312 /**
313 * Method to get an array of all tables in the database (schema).
314 *
315 * @return array An array of all the tables in the database.
316 *
317 * @since 12.1
318 * @throws RuntimeException
319 */
320 public function getTableList()
321 {
322 $this->connect();
323
324 $type = 'table';
325
326 $query = $this->getQuery(true)
327 ->select('name')
328 ->from('sqlite_master')
329 ->where('type = :type')
330 ->bind(':type', $type)
331 ->order('name');
332
333 $this->setQuery($query);
334
335 $tables = $this->loadColumn();
336
337 return $tables;
338 }
339
340 /**
341 * Get the version of the database connector.
342 *
343 * @return string The database connector version.
344 *
345 * @since 12.1
346 */
347 public function getVersion()
348 {
349 $this->connect();
350
351 $this->setQuery('SELECT sqlite_version()');
352
353 return $this->loadResult();
354 }
355
356 /**
357 * Select a database for use.
358 *
359 * @param string $database The name of the database to select for use.
360 *
361 * @return boolean True if the database was successfully selected.
362 *
363 * @since 12.1
364 * @throws RuntimeException
365 */
366 public function select($database)
367 {
368 $this->connect();
369
370 return true;
371 }
372
373 /**
374 * Set the connection to use UTF-8 character encoding.
375 *
376 * Returns false automatically for the Oracle driver since
377 * you can only set the character set when the connection
378 * is created.
379 *
380 * @return boolean True on success.
381 *
382 * @since 12.1
383 */
384 public function setUtf()
385 {
386 $this->connect();
387
388 return false;
389 }
390
391 /**
392 * Locks a table in the database.
393 *
394 * @param string $table The name of the table to unlock.
395 *
396 * @return JDatabaseDriverSqlite Returns this object to support chaining.
397 *
398 * @since 12.1
399 * @throws RuntimeException
400 */
401 public function lockTable($table)
402 {
403 return $this;
404 }
405
406 /**
407 * Renames a table in the database.
408 *
409 * @param string $oldTable The name of the table to be renamed
410 * @param string $newTable The new name for the table.
411 * @param string $backup Not used by Sqlite.
412 * @param string $prefix Not used by Sqlite.
413 *
414 * @return JDatabaseDriverSqlite Returns this object to support chaining.
415 *
416 * @since 12.1
417 * @throws RuntimeException
418 */
419 public function renameTable($oldTable, $newTable, $backup = null, $prefix = null)
420 {
421 $this->setQuery('ALTER TABLE ' . $oldTable . ' RENAME TO ' . $newTable)->execute();
422
423 return $this;
424 }
425
426 /**
427 * Unlocks tables in the database.
428 *
429 * @return JDatabaseDriverSqlite Returns this object to support chaining.
430 *
431 * @since 12.1
432 * @throws RuntimeException
433 */
434 public function unlockTables()
435 {
436 return $this;
437 }
438
439 /**
440 * Test to see if the PDO ODBC connector is available.
441 *
442 * @return boolean True on success, false otherwise.
443 *
444 * @since 12.1
445 */
446 public static function isSupported()
447 {
448 return class_exists('PDO') && in_array('sqlite', PDO::getAvailableDrivers());
449 }
450
451 /**
452 * Method to commit a transaction.
453 *
454 * @param boolean $toSavepoint If true, commit to the last savepoint.
455 *
456 * @return void
457 *
458 * @since 12.3
459 * @throws RuntimeException
460 */
461 public function transactionCommit($toSavepoint = false)
462 {
463 $this->connect();
464
465 if (!$toSavepoint || $this->transactionDepth <= 1)
466 {
467 parent::transactionCommit($toSavepoint);
468 }
469 else
470 {
471 $this->transactionDepth--;
472 }
473 }
474
475 /**
476 * Method to roll back a transaction.
477 *
478 * @param boolean $toSavepoint If true, rollback to the last savepoint.
479 *
480 * @return void
481 *
482 * @since 12.3
483 * @throws RuntimeException
484 */
485 public function transactionRollback($toSavepoint = false)
486 {
487 $this->connect();
488
489 if (!$toSavepoint || $this->transactionDepth <= 1)
490 {
491 parent::transactionRollback($toSavepoint);
492 }
493 else
494 {
495 $savepoint = 'SP_' . ($this->transactionDepth - 1);
496 $this->setQuery('ROLLBACK TO ' . $this->quoteName($savepoint));
497
498 if ($this->execute())
499 {
500 $this->transactionDepth--;
501 }
502 }
503 }
504
505 /**
506 * Method to initialize a transaction.
507 *
508 * @param boolean $asSavepoint If true and a transaction is already active, a savepoint will be created.
509 *
510 * @return void
511 *
512 * @since 12.3
513 * @throws RuntimeException
514 */
515 public function transactionStart($asSavepoint = false)
516 {
517 $this->connect();
518
519 if (!$asSavepoint || !$this->transactionDepth)
520 {
521 parent::transactionStart($asSavepoint);
522 }
523
524 $savepoint = 'SP_' . $this->transactionDepth;
525 $this->setQuery('SAVEPOINT ' . $this->quoteName($savepoint));
526
527 if ($this->execute())
528 {
529 $this->transactionDepth++;
530 }
531 }
532
533 /**
534 * Get the query strings to alter the character set and collation of a table.
535 *
536 * @param string $tableName The name of the table
537 *
538 * @return string[] The queries required to alter the table's character set and collation
539 *
540 * @since CMS 3.5.0
541 */
542 public function getAlterTableCharacterSet($tableName)
543 {
544 return array();
545 }
546
547 /**
548 * Return the query string to create new Database.
549 * Each database driver, other than MySQL, need to override this member to return correct string.
550 *
551 * @param stdClass $options Object used to pass user and database name to database driver.
552 * This object must have "db_name" and "db_user" set.
553 * @param boolean $utf True if the database supports the UTF-8 character set.
554 *
555 * @return string The query that creates database
556 *
557 * @since 12.2
558 */
559 protected function getCreateDatabaseQuery($options, $utf)
560 {
561 return 'CREATE DATABASE ' . $this->quoteName($options->db_name);
562 }
563 }
564