[Summary view] [Print] [Text view]

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