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 13defined('FOF_INCLUDED') or die; 14 15/** 16 * SQLite database driver 17 * 18 * @see http://php.net/pdo 19 * @since 12.1 20 */ 21class 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