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