1<?php 2 3namespace Drupal\Core\Database\Driver\sqlite; 4 5use Drupal\Core\Database\Database; 6use Drupal\Core\Database\DatabaseNotFoundException; 7use Drupal\Core\Database\Connection as DatabaseConnection; 8use Drupal\Core\Database\StatementInterface; 9 10/** 11 * SQLite implementation of \Drupal\Core\Database\Connection. 12 */ 13class Connection extends DatabaseConnection { 14 15 /** 16 * Error code for "Unable to open database file" error. 17 */ 18 const DATABASE_NOT_FOUND = 14; 19 20 /** 21 * {@inheritdoc} 22 */ 23 protected $statementClass = NULL; 24 25 /** 26 * {@inheritdoc} 27 */ 28 protected $statementWrapperClass = NULL; 29 30 /** 31 * Whether or not the active transaction (if any) will be rolled back. 32 * 33 * @var bool 34 */ 35 protected $willRollback; 36 37 /** 38 * A map of condition operators to SQLite operators. 39 * 40 * We don't want to override any of the defaults. 41 */ 42 protected static $sqliteConditionOperatorMap = [ 43 'LIKE' => ['postfix' => " ESCAPE '\\'"], 44 'NOT LIKE' => ['postfix' => " ESCAPE '\\'"], 45 'LIKE BINARY' => ['postfix' => " ESCAPE '\\'", 'operator' => 'GLOB'], 46 'NOT LIKE BINARY' => ['postfix' => " ESCAPE '\\'", 'operator' => 'NOT GLOB'], 47 ]; 48 49 /** 50 * All databases attached to the current database. 51 * 52 * This is used to allow prefixes to be safely handled without locking the 53 * table. 54 * 55 * @var array 56 */ 57 protected $attachedDatabases = []; 58 59 /** 60 * Whether or not a table has been dropped this request. 61 * 62 * The destructor will only try to get rid of unnecessary databases if there 63 * is potential of them being empty. 64 * 65 * This variable is set to public because Schema needs to 66 * access it. However, it should not be manually set. 67 * 68 * @var bool 69 */ 70 public $tableDropped = FALSE; 71 72 /** 73 * {@inheritdoc} 74 */ 75 protected $transactionalDDLSupport = TRUE; 76 77 /** 78 * {@inheritdoc} 79 */ 80 protected $identifierQuotes = ['"', '"']; 81 82 /** 83 * Constructs a \Drupal\Core\Database\Driver\sqlite\Connection object. 84 */ 85 public function __construct(\PDO $connection, array $connection_options) { 86 parent::__construct($connection, $connection_options); 87 88 // Attach one database for each registered prefix. 89 $prefixes = $this->prefixes; 90 foreach ($prefixes as &$prefix) { 91 // Empty prefix means query the main database -- no need to attach anything. 92 if (!empty($prefix)) { 93 // Only attach the database once. 94 if (!isset($this->attachedDatabases[$prefix])) { 95 $this->attachedDatabases[$prefix] = $prefix; 96 if ($connection_options['database'] === ':memory:') { 97 // In memory database use ':memory:' as database name. According to 98 // http://www.sqlite.org/inmemorydb.html it will open a unique 99 // database so attaching it twice is not a problem. 100 $this->query('ATTACH DATABASE :database AS :prefix', [':database' => $connection_options['database'], ':prefix' => $prefix]); 101 } 102 else { 103 $this->query('ATTACH DATABASE :database AS :prefix', [':database' => $connection_options['database'] . '-' . $prefix, ':prefix' => $prefix]); 104 } 105 } 106 107 // Add a ., so queries become prefix.table, which is proper syntax for 108 // querying an attached database. 109 $prefix .= '.'; 110 } 111 } 112 // Regenerate the prefixes replacement table. 113 $this->setPrefix($prefixes); 114 } 115 116 /** 117 * {@inheritdoc} 118 */ 119 public static function open(array &$connection_options = []) { 120 // Allow PDO options to be overridden. 121 $connection_options += [ 122 'pdo' => [], 123 ]; 124 $connection_options['pdo'] += [ 125 \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, 126 // Convert numeric values to strings when fetching. 127 \PDO::ATTR_STRINGIFY_FETCHES => TRUE, 128 ]; 129 130 try { 131 $pdo = new \PDO('sqlite:' . $connection_options['database'], '', '', $connection_options['pdo']); 132 } 133 catch (\PDOException $e) { 134 if ($e->getCode() == static::DATABASE_NOT_FOUND) { 135 throw new DatabaseNotFoundException($e->getMessage(), $e->getCode(), $e); 136 } 137 // SQLite doesn't have a distinct error code for access denied, so don't 138 // deal with that case. 139 throw $e; 140 } 141 142 // Create functions needed by SQLite. 143 $pdo->sqliteCreateFunction('if', [__CLASS__, 'sqlFunctionIf']); 144 $pdo->sqliteCreateFunction('greatest', [__CLASS__, 'sqlFunctionGreatest']); 145 $pdo->sqliteCreateFunction('least', [__CLASS__, 'sqlFunctionLeast']); 146 $pdo->sqliteCreateFunction('pow', 'pow', 2); 147 $pdo->sqliteCreateFunction('exp', 'exp', 1); 148 $pdo->sqliteCreateFunction('length', 'strlen', 1); 149 $pdo->sqliteCreateFunction('md5', 'md5', 1); 150 $pdo->sqliteCreateFunction('concat', [__CLASS__, 'sqlFunctionConcat']); 151 $pdo->sqliteCreateFunction('concat_ws', [__CLASS__, 'sqlFunctionConcatWs']); 152 $pdo->sqliteCreateFunction('substring', [__CLASS__, 'sqlFunctionSubstring'], 3); 153 $pdo->sqliteCreateFunction('substring_index', [__CLASS__, 'sqlFunctionSubstringIndex'], 3); 154 $pdo->sqliteCreateFunction('rand', [__CLASS__, 'sqlFunctionRand']); 155 $pdo->sqliteCreateFunction('regexp', [__CLASS__, 'sqlFunctionRegexp']); 156 157 // SQLite does not support the LIKE BINARY operator, so we overload the 158 // non-standard GLOB operator for case-sensitive matching. Another option 159 // would have been to override another non-standard operator, MATCH, but 160 // that does not support the NOT keyword prefix. 161 $pdo->sqliteCreateFunction('glob', [__CLASS__, 'sqlFunctionLikeBinary']); 162 163 // Create a user-space case-insensitive collation with UTF-8 support. 164 $pdo->sqliteCreateCollation('NOCASE_UTF8', ['Drupal\Component\Utility\Unicode', 'strcasecmp']); 165 166 // Set SQLite init_commands if not already defined. Enable the Write-Ahead 167 // Logging (WAL) for SQLite. See https://www.drupal.org/node/2348137 and 168 // https://www.sqlite.org/wal.html. 169 $connection_options += [ 170 'init_commands' => [], 171 ]; 172 $connection_options['init_commands'] += [ 173 'wal' => "PRAGMA journal_mode=WAL", 174 ]; 175 176 // Execute sqlite init_commands. 177 if (isset($connection_options['init_commands'])) { 178 $pdo->exec(implode('; ', $connection_options['init_commands'])); 179 } 180 181 return $pdo; 182 } 183 184 /** 185 * Destructor for the SQLite connection. 186 * 187 * We prune empty databases on destruct, but only if tables have been 188 * dropped. This is especially needed when running the test suite, which 189 * creates and destroy databases several times in a row. 190 */ 191 public function __destruct() { 192 if ($this->tableDropped && !empty($this->attachedDatabases)) { 193 foreach ($this->attachedDatabases as $prefix) { 194 // Check if the database is now empty, ignore the internal SQLite tables. 195 try { 196 $count = $this->query('SELECT COUNT(*) FROM ' . $prefix . '.sqlite_master WHERE type = :type AND name NOT LIKE :pattern', [':type' => 'table', ':pattern' => 'sqlite_%'])->fetchField(); 197 198 // We can prune the database file if it doesn't have any tables. 199 if ($count == 0 && $this->connectionOptions['database'] != ':memory:' && file_exists($this->connectionOptions['database'] . '-' . $prefix)) { 200 // Detach the database. 201 $this->query('DETACH DATABASE :schema', [':schema' => $prefix]); 202 // Destroy the database file. 203 unlink($this->connectionOptions['database'] . '-' . $prefix); 204 } 205 } 206 catch (\Exception $e) { 207 // Ignore the exception and continue. There is nothing we can do here 208 // to report the error or fail safe. 209 } 210 } 211 } 212 parent::__destruct(); 213 } 214 215 /** 216 * Gets all the attached databases. 217 * 218 * @return array 219 * An array of attached database names. 220 * 221 * @see \Drupal\Core\Database\Driver\sqlite\Connection::__construct() 222 */ 223 public function getAttachedDatabases() { 224 return $this->attachedDatabases; 225 } 226 227 /** 228 * SQLite compatibility implementation for the IF() SQL function. 229 */ 230 public static function sqlFunctionIf($condition, $expr1, $expr2 = NULL) { 231 return $condition ? $expr1 : $expr2; 232 } 233 234 /** 235 * SQLite compatibility implementation for the GREATEST() SQL function. 236 */ 237 public static function sqlFunctionGreatest() { 238 $args = func_get_args(); 239 foreach ($args as $v) { 240 if (!isset($v)) { 241 unset($args); 242 } 243 } 244 if (count($args)) { 245 return max($args); 246 } 247 else { 248 return NULL; 249 } 250 } 251 252 /** 253 * SQLite compatibility implementation for the LEAST() SQL function. 254 */ 255 public static function sqlFunctionLeast() { 256 // Remove all NULL, FALSE and empty strings values but leaves 0 (zero) values. 257 $values = array_filter(func_get_args(), 'strlen'); 258 259 return count($values) < 1 ? NULL : min($values); 260 } 261 262 /** 263 * SQLite compatibility implementation for the CONCAT() SQL function. 264 */ 265 public static function sqlFunctionConcat() { 266 $args = func_get_args(); 267 return implode('', $args); 268 } 269 270 /** 271 * SQLite compatibility implementation for the CONCAT_WS() SQL function. 272 * 273 * @see http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_concat-ws 274 */ 275 public static function sqlFunctionConcatWs() { 276 $args = func_get_args(); 277 $separator = array_shift($args); 278 // If the separator is NULL, the result is NULL. 279 if ($separator === FALSE || is_null($separator)) { 280 return NULL; 281 } 282 // Skip any NULL values after the separator argument. 283 $args = array_filter($args, function ($value) { 284 return !is_null($value); 285 }); 286 return implode($separator, $args); 287 } 288 289 /** 290 * SQLite compatibility implementation for the SUBSTRING() SQL function. 291 */ 292 public static function sqlFunctionSubstring($string, $from, $length) { 293 return substr($string, $from - 1, $length); 294 } 295 296 /** 297 * SQLite compatibility implementation for the SUBSTRING_INDEX() SQL function. 298 */ 299 public static function sqlFunctionSubstringIndex($string, $delimiter, $count) { 300 // If string is empty, simply return an empty string. 301 if (empty($string)) { 302 return ''; 303 } 304 $end = 0; 305 for ($i = 0; $i < $count; $i++) { 306 $end = strpos($string, $delimiter, $end + 1); 307 if ($end === FALSE) { 308 $end = strlen($string); 309 } 310 } 311 return substr($string, 0, $end); 312 } 313 314 /** 315 * SQLite compatibility implementation for the RAND() SQL function. 316 */ 317 public static function sqlFunctionRand($seed = NULL) { 318 if (isset($seed)) { 319 mt_srand($seed); 320 } 321 return mt_rand() / mt_getrandmax(); 322 } 323 324 /** 325 * SQLite compatibility implementation for the REGEXP SQL operator. 326 * 327 * The REGEXP operator is natively known, but not implemented by default. 328 * 329 * @see http://www.sqlite.org/lang_expr.html#regexp 330 */ 331 public static function sqlFunctionRegexp($pattern, $subject) { 332 // preg_quote() cannot be used here, since $pattern may contain reserved 333 // regular expression characters already (such as ^, $, etc). Therefore, 334 // use a rare character as PCRE delimiter. 335 $pattern = '#' . addcslashes($pattern, '#') . '#i'; 336 return preg_match($pattern, $subject); 337 } 338 339 /** 340 * SQLite compatibility implementation for the LIKE BINARY SQL operator. 341 * 342 * SQLite supports case-sensitive LIKE operations through the 343 * 'case_sensitive_like' PRAGMA statement, but only for ASCII characters, so 344 * we have to provide our own implementation with UTF-8 support. 345 * 346 * @see https://sqlite.org/pragma.html#pragma_case_sensitive_like 347 * @see https://sqlite.org/lang_expr.html#like 348 */ 349 public static function sqlFunctionLikeBinary($pattern, $subject) { 350 // Replace the SQL LIKE wildcard meta-characters with the equivalent regular 351 // expression meta-characters and escape the delimiter that will be used for 352 // matching. 353 $pattern = str_replace(['%', '_'], ['.*?', '.'], preg_quote($pattern, '/')); 354 return preg_match('/^' . $pattern . '$/', $subject); 355 } 356 357 /** 358 * {@inheritdoc} 359 */ 360 public function prepare($statement, array $driver_options = []) { 361 @trigger_error('Connection::prepare() is deprecated in drupal:9.1.0 and is removed from drupal:10.0.0. Database drivers should instantiate \PDOStatement objects by calling \PDO::prepare in their Connection::prepareStatement method instead. \PDO::prepare should not be called outside of driver code. See https://www.drupal.org/node/3137786', E_USER_DEPRECATED); 362 return new Statement($this->connection, $this, $statement, $driver_options); 363 } 364 365 /** 366 * {@inheritdoc} 367 */ 368 protected function handleQueryException(\PDOException $e, $query, array $args = [], $options = []) { 369 // The database schema might be changed by another process in between the 370 // time that the statement was prepared and the time the statement was run 371 // (e.g. usually happens when running tests). In this case, we need to 372 // re-run the query. 373 // @see http://www.sqlite.org/faq.html#q15 374 // @see http://www.sqlite.org/rescode.html#schema 375 if (!empty($e->errorInfo[1]) && $e->errorInfo[1] === 17) { 376 @trigger_error('Connection::handleQueryException() is deprecated in drupal:9.2.0 and is removed in drupal:10.0.0. Get a handler through $this->exceptionHandler() instead, and use one of its methods. See https://www.drupal.org/node/3187222', E_USER_DEPRECATED); 377 return $this->query($query, $args, $options); 378 } 379 380 parent::handleQueryException($e, $query, $args, $options); 381 } 382 383 public function queryRange($query, $from, $count, array $args = [], array $options = []) { 384 return $this->query($query . ' LIMIT ' . (int) $from . ', ' . (int) $count, $args, $options); 385 } 386 387 public function queryTemporary($query, array $args = [], array $options = []) { 388 // Generate a new temporary table name and protect it from prefixing. 389 // SQLite requires that temporary tables to be non-qualified. 390 $tablename = $this->generateTemporaryTableName(); 391 $prefixes = $this->prefixes; 392 $prefixes[$tablename] = ''; 393 $this->setPrefix($prefixes); 394 395 $this->query('CREATE TEMPORARY TABLE ' . $tablename . ' AS ' . $query, $args, $options); 396 return $tablename; 397 } 398 399 public function driver() { 400 return 'sqlite'; 401 } 402 403 public function databaseType() { 404 return 'sqlite'; 405 } 406 407 /** 408 * Overrides \Drupal\Core\Database\Connection::createDatabase(). 409 * 410 * @param string $database 411 * The name of the database to create. 412 * 413 * @throws \Drupal\Core\Database\DatabaseNotFoundException 414 */ 415 public function createDatabase($database) { 416 // Verify the database is writable. 417 $db_directory = new \SplFileInfo(dirname($database)); 418 if (!$db_directory->isDir() && !\Drupal::service('file_system')->mkdir($db_directory->getPathName(), 0755, TRUE)) { 419 throw new DatabaseNotFoundException('Unable to create database directory ' . $db_directory->getPathName()); 420 } 421 } 422 423 public function mapConditionOperator($operator) { 424 return isset(static::$sqliteConditionOperatorMap[$operator]) ? static::$sqliteConditionOperatorMap[$operator] : NULL; 425 } 426 427 /** 428 * {@inheritdoc} 429 */ 430 public function prepareStatement(string $query, array $options): StatementInterface { 431 try { 432 $query = $this->preprocessStatement($query, $options); 433 $statement = new Statement($this->connection, $this, $query, $options['pdo'] ?? []); 434 } 435 catch (\Exception $e) { 436 $this->exceptionHandler()->handleStatementException($e, $query, $options); 437 } 438 return $statement; 439 } 440 441 public function nextId($existing_id = 0) { 442 $this->startTransaction(); 443 // We can safely use literal queries here instead of the slower query 444 // builder because if a given database breaks here then it can simply 445 // override nextId. However, this is unlikely as we deal with short strings 446 // and integers and no known databases require special handling for those 447 // simple cases. If another transaction wants to write the same row, it will 448 // wait until this transaction commits. Also, the return value needs to be 449 // set to RETURN_AFFECTED as if it were a real update() query otherwise it 450 // is not possible to get the row count properly. 451 $affected = $this->query('UPDATE {sequences} SET value = GREATEST(value, :existing_id) + 1', [ 452 ':existing_id' => $existing_id, 453 ], ['return' => Database::RETURN_AFFECTED]); 454 if (!$affected) { 455 $this->query('INSERT INTO {sequences} (value) VALUES (:existing_id + 1)', [ 456 ':existing_id' => $existing_id, 457 ]); 458 } 459 // The transaction gets committed when the transaction object gets destroyed 460 // because it gets out of scope. 461 return $this->query('SELECT value FROM {sequences}')->fetchField(); 462 } 463 464 /** 465 * {@inheritdoc} 466 */ 467 public function getFullQualifiedTableName($table) { 468 $prefix = $this->tablePrefix($table); 469 470 // Don't include the SQLite database file name as part of the table name. 471 return $prefix . $table; 472 } 473 474 /** 475 * {@inheritdoc} 476 */ 477 public static function createConnectionOptionsFromUrl($url, $root) { 478 $database = parent::createConnectionOptionsFromUrl($url, $root); 479 480 // A SQLite database path with two leading slashes indicates a system path. 481 // Otherwise the path is relative to the Drupal root. 482 $url_components = parse_url($url); 483 if ($url_components['path'][0] === '/') { 484 $url_components['path'] = substr($url_components['path'], 1); 485 } 486 if ($url_components['path'][0] === '/' || $url_components['path'] === ':memory:') { 487 $database['database'] = $url_components['path']; 488 } 489 else { 490 $database['database'] = $root . '/' . $url_components['path']; 491 } 492 493 // User credentials and system port are irrelevant for SQLite. 494 unset( 495 $database['username'], 496 $database['password'], 497 $database['port'] 498 ); 499 500 return $database; 501 } 502 503 /** 504 * {@inheritdoc} 505 */ 506 public static function createUrlFromConnectionOptions(array $connection_options) { 507 if (!isset($connection_options['driver'], $connection_options['database'])) { 508 throw new \InvalidArgumentException("As a minimum, the connection options array must contain at least the 'driver' and 'database' keys"); 509 } 510 511 $db_url = 'sqlite://localhost/' . $connection_options['database']; 512 513 if (isset($connection_options['prefix']['default']) && $connection_options['prefix']['default'] !== NULL && $connection_options['prefix']['default'] !== '') { 514 $db_url .= '#' . $connection_options['prefix']['default']; 515 } 516 517 return $db_url; 518 } 519 520} 521