Connection.php 17.8 KB
Newer Older
ayelet_Cr's avatar
ayelet_Cr committed
1
2
<?php

3
namespace Drupal\Core\Database\Driver\sqlite;
ayelet_Cr's avatar
ayelet_Cr committed
4

5
use Drupal\Core\Database\Database;
6
use Drupal\Core\Database\DatabaseNotFoundException;
7
use Drupal\Core\Database\Connection as DatabaseConnection;
8
use Drupal\Core\Database\StatementInterface;
ayelet_Cr's avatar
ayelet_Cr committed
9
10

/**
11
 * SQLite implementation of \Drupal\Core\Database\Connection.
ayelet_Cr's avatar
ayelet_Cr committed
12
13
14
 */
class Connection extends DatabaseConnection {

15
16
17
18
19
  /**
   * Error code for "Unable to open database file" error.
   */
  const DATABASE_NOT_FOUND = 14;

20
21
22
23
24
25
26
27
28
29
  /**
   * {@inheritdoc}
   */
  protected $statementClass = NULL;

  /**
   * {@inheritdoc}
   */
  protected $statementWrapperClass = NULL;

ayelet_Cr's avatar
ayelet_Cr committed
30
31
32
  /**
   * Whether or not the active transaction (if any) will be rolled back.
   *
33
   * @var bool
ayelet_Cr's avatar
ayelet_Cr committed
34
35
36
   */
  protected $willRollback;

37
38
39
40
41
42
43
44
45
46
47
48
  /**
   * A map of condition operators to SQLite operators.
   *
   * We don't want to override any of the defaults.
   */
  protected static $sqliteConditionOperatorMap = [
    'LIKE' => ['postfix' => " ESCAPE '\\'"],
    'NOT LIKE' => ['postfix' => " ESCAPE '\\'"],
    'LIKE BINARY' => ['postfix' => " ESCAPE '\\'", 'operator' => 'GLOB'],
    'NOT LIKE BINARY' => ['postfix' => " ESCAPE '\\'", 'operator' => 'NOT GLOB'],
  ];

ayelet_Cr's avatar
ayelet_Cr committed
49
50
51
52
53
54
  /**
   * All databases attached to the current database. This is used to allow
   * prefixes to be safely handled without locking the table
   *
   * @var array
   */
55
  protected $attachedDatabases = [];
ayelet_Cr's avatar
ayelet_Cr committed
56
57
58
59
60
61
62
63
64

  /**
   * Whether or not a table has been dropped this request: the destructor will
   * only try to get rid of unnecessary databases if there is potential of them
   * being empty.
   *
   * This variable is set to public because Schema needs to
   * access it. However, it should not be manually set.
   *
65
   * @var bool
ayelet_Cr's avatar
ayelet_Cr committed
66
   */
alexpott's avatar
alexpott committed
67
  public $tableDropped = FALSE;
ayelet_Cr's avatar
ayelet_Cr committed
68

69
70
71
72
73
  /**
   * {@inheritdoc}
   */
  protected $transactionalDDLSupport = TRUE;

74
75
76
77
78
  /**
   * {@inheritdoc}
   */
  protected $identifierQuotes = ['"', '"'];

79
80
81
  /**
   * Constructs a \Drupal\Core\Database\Driver\sqlite\Connection object.
   */
82
  public function __construct(\PDO $connection, array $connection_options) {
83
84
    parent::__construct($connection, $connection_options);

ayelet_Cr's avatar
ayelet_Cr committed
85
86
    // Attach one database for each registered prefix.
    $prefixes = $this->prefixes;
87
    foreach ($prefixes as &$prefix) {
ayelet_Cr's avatar
ayelet_Cr committed
88
89
90
91
92
      // Empty prefix means query the main database -- no need to attach anything.
      if (!empty($prefix)) {
        // Only attach the database once.
        if (!isset($this->attachedDatabases[$prefix])) {
          $this->attachedDatabases[$prefix] = $prefix;
93
94
95
96
          if ($connection_options['database'] === ':memory:') {
            // In memory database use ':memory:' as database name. According to
            // http://www.sqlite.org/inmemorydb.html it will open a unique
            // database so attaching it twice is not a problem.
97
            $this->query('ATTACH DATABASE :database AS :prefix', [':database' => $connection_options['database'], ':prefix' => $prefix]);
98
99
          }
          else {
100
            $this->query('ATTACH DATABASE :database AS :prefix', [':database' => $connection_options['database'] . '-' . $prefix, ':prefix' => $prefix]);
101
          }
ayelet_Cr's avatar
ayelet_Cr committed
102
103
104
105
106
107
108
109
110
        }

        // Add a ., so queries become prefix.table, which is proper syntax for
        // querying an attached database.
        $prefix .= '.';
      }
    }
    // Regenerate the prefixes replacement table.
    $this->setPrefix($prefixes);
111
112
113
114
115
  }

  /**
   * {@inheritdoc}
   */
116
  public static function open(array &$connection_options = []) {
117
    // Allow PDO options to be overridden.
118
119
120
121
    $connection_options += [
      'pdo' => [],
    ];
    $connection_options['pdo'] += [
122
      \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
123
      // Convert numeric values to strings when fetching.
124
      \PDO::ATTR_STRINGIFY_FETCHES => TRUE,
125
    ];
126
127
128
129
130
131
132
133
134
135
136
137
138

    try {
      $pdo = new \PDO('sqlite:' . $connection_options['database'], '', '', $connection_options['pdo']);
    }
    catch (\PDOException $e) {
      if ($e->getCode() == static::DATABASE_NOT_FOUND) {
        throw new DatabaseNotFoundException($e->getMessage(), $e->getCode(), $e);
      }
      // SQLite doesn't have a distinct error code for access denied, so don't
      // deal with that case.
      throw $e;
    }

ayelet_Cr's avatar
ayelet_Cr committed
139
    // Create functions needed by SQLite.
140
141
    $pdo->sqliteCreateFunction('if', [__CLASS__, 'sqlFunctionIf']);
    $pdo->sqliteCreateFunction('greatest', [__CLASS__, 'sqlFunctionGreatest']);
142
    $pdo->sqliteCreateFunction('least', [__CLASS__, 'sqlFunctionLeast']);
143
    $pdo->sqliteCreateFunction('pow', 'pow', 2);
144
    $pdo->sqliteCreateFunction('exp', 'exp', 1);
145
146
    $pdo->sqliteCreateFunction('length', 'strlen', 1);
    $pdo->sqliteCreateFunction('md5', 'md5', 1);
147
148
149
150
151
152
    $pdo->sqliteCreateFunction('concat', [__CLASS__, 'sqlFunctionConcat']);
    $pdo->sqliteCreateFunction('concat_ws', [__CLASS__, 'sqlFunctionConcatWs']);
    $pdo->sqliteCreateFunction('substring', [__CLASS__, 'sqlFunctionSubstring'], 3);
    $pdo->sqliteCreateFunction('substring_index', [__CLASS__, 'sqlFunctionSubstringIndex'], 3);
    $pdo->sqliteCreateFunction('rand', [__CLASS__, 'sqlFunctionRand']);
    $pdo->sqliteCreateFunction('regexp', [__CLASS__, 'sqlFunctionRegexp']);
ayelet_Cr's avatar
ayelet_Cr committed
153

154
155
156
157
    // SQLite does not support the LIKE BINARY operator, so we overload the
    // non-standard GLOB operator for case-sensitive matching. Another option
    // would have been to override another non-standard operator, MATCH, but
    // that does not support the NOT keyword prefix.
158
    $pdo->sqliteCreateFunction('glob', [__CLASS__, 'sqlFunctionLikeBinary']);
159

160
    // Create a user-space case-insensitive collation with UTF-8 support.
161
    $pdo->sqliteCreateCollation('NOCASE_UTF8', ['Drupal\Component\Utility\Unicode', 'strcasecmp']);
162

163
164
165
    // Set SQLite init_commands if not already defined. Enable the Write-Ahead
    // Logging (WAL) for SQLite. See https://www.drupal.org/node/2348137 and
    // https://www.sqlite.org/wal.html.
166
167
168
169
    $connection_options += [
      'init_commands' => [],
    ];
    $connection_options['init_commands'] += [
170
      'wal' => "PRAGMA journal_mode=WAL",
171
    ];
172

ayelet_Cr's avatar
ayelet_Cr committed
173
174
    // Execute sqlite init_commands.
    if (isset($connection_options['init_commands'])) {
175
      $pdo->exec(implode('; ', $connection_options['init_commands']));
ayelet_Cr's avatar
ayelet_Cr committed
176
    }
177
178

    return $pdo;
ayelet_Cr's avatar
ayelet_Cr committed
179
180
181
182
183
184
185
186
187
188
189
190
191
192
  }

  /**
   * Destructor for the SQLite connection.
   *
   * We prune empty databases on destruct, but only if tables have been
   * dropped. This is especially needed when running the test suite, which
   * creates and destroy databases several times in a row.
   */
  public function __destruct() {
    if ($this->tableDropped && !empty($this->attachedDatabases)) {
      foreach ($this->attachedDatabases as $prefix) {
        // Check if the database is now empty, ignore the internal SQLite tables.
        try {
193
          $count = $this->query('SELECT COUNT(*) FROM ' . $prefix . '.sqlite_master WHERE type = :type AND name NOT LIKE :pattern', [':type' => 'table', ':pattern' => 'sqlite_%'])->fetchField();
ayelet_Cr's avatar
ayelet_Cr committed
194
195

          // We can prune the database file if it doesn't have any tables.
196
          if ($count == 0 && $this->connectionOptions['database'] != ':memory:' && file_exists($this->connectionOptions['database'] . '-' . $prefix)) {
197
198
199
            // Detach the database.
            $this->query('DETACH DATABASE :schema', [':schema' => $prefix]);
            // Destroy the database file.
ayelet_Cr's avatar
ayelet_Cr committed
200
201
202
            unlink($this->connectionOptions['database'] . '-' . $prefix);
          }
        }
203
        catch (\Exception $e) {
ayelet_Cr's avatar
ayelet_Cr committed
204
205
206
207
208
          // Ignore the exception and continue. There is nothing we can do here
          // to report the error or fail safe.
        }
      }
    }
209
    parent::__destruct();
ayelet_Cr's avatar
ayelet_Cr committed
210
211
  }

212
213
214
215
216
217
218
219
220
221
222
223
  /**
   * Gets all the attached databases.
   *
   * @return array
   *   An array of attached database names.
   *
   * @see \Drupal\Core\Database\Driver\sqlite\Connection::__construct()
   */
  public function getAttachedDatabases() {
    return $this->attachedDatabases;
  }

ayelet_Cr's avatar
ayelet_Cr committed
224
225
226
  /**
   * SQLite compatibility implementation for the IF() SQL function.
   */
227
  public static function sqlFunctionIf($condition, $expr1, $expr2 = NULL) {
ayelet_Cr's avatar
ayelet_Cr committed
228
229
230
231
232
233
    return $condition ? $expr1 : $expr2;
  }

  /**
   * SQLite compatibility implementation for the GREATEST() SQL function.
   */
234
  public static function sqlFunctionGreatest() {
ayelet_Cr's avatar
ayelet_Cr committed
235
    $args = func_get_args();
236
    foreach ($args as $v) {
ayelet_Cr's avatar
ayelet_Cr committed
237
238
239
240
241
242
243
244
245
246
247
248
      if (!isset($v)) {
        unset($args);
      }
    }
    if (count($args)) {
      return max($args);
    }
    else {
      return NULL;
    }
  }

249
250
251
252
253
254
255
256
257
258
  /**
   * SQLite compatibility implementation for the LEAST() SQL function.
   */
  public static function sqlFunctionLeast() {
    // Remove all NULL, FALSE and empty strings values but leaves 0 (zero) values.
    $values = array_filter(func_get_args(), 'strlen');

    return count($values) < 1 ? NULL : min($values);
  }

ayelet_Cr's avatar
ayelet_Cr committed
259
260
261
  /**
   * SQLite compatibility implementation for the CONCAT() SQL function.
   */
262
  public static function sqlFunctionConcat() {
ayelet_Cr's avatar
ayelet_Cr committed
263
264
265
266
    $args = func_get_args();
    return implode('', $args);
  }

267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
  /**
   * SQLite compatibility implementation for the CONCAT_WS() SQL function.
   *
   * @see http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_concat-ws
   */
  public static function sqlFunctionConcatWs() {
    $args = func_get_args();
    $separator = array_shift($args);
    // If the separator is NULL, the result is NULL.
    if ($separator === FALSE || is_null($separator)) {
      return NULL;
    }
    // Skip any NULL values after the separator argument.
    $args = array_filter($args, function ($value) {
      return !is_null($value);
    });
    return implode($separator, $args);
  }

ayelet_Cr's avatar
ayelet_Cr committed
286
287
288
  /**
   * SQLite compatibility implementation for the SUBSTRING() SQL function.
   */
289
  public static function sqlFunctionSubstring($string, $from, $length) {
ayelet_Cr's avatar
ayelet_Cr committed
290
291
292
293
294
295
    return substr($string, $from - 1, $length);
  }

  /**
   * SQLite compatibility implementation for the SUBSTRING_INDEX() SQL function.
   */
296
  public static function sqlFunctionSubstringIndex($string, $delimiter, $count) {
ayelet_Cr's avatar
ayelet_Cr committed
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
    // If string is empty, simply return an empty string.
    if (empty($string)) {
      return '';
    }
    $end = 0;
    for ($i = 0; $i < $count; $i++) {
      $end = strpos($string, $delimiter, $end + 1);
      if ($end === FALSE) {
        $end = strlen($string);
      }
    }
    return substr($string, 0, $end);
  }

  /**
   * SQLite compatibility implementation for the RAND() SQL function.
   */
314
  public static function sqlFunctionRand($seed = NULL) {
ayelet_Cr's avatar
ayelet_Cr committed
315
316
317
318
319
320
    if (isset($seed)) {
      mt_srand($seed);
    }
    return mt_rand() / mt_getrandmax();
  }

321
322
323
  /**
   * SQLite compatibility implementation for the REGEXP SQL operator.
   *
324
325
326
   * The REGEXP operator is natively known, but not implemented by default.
   *
   * @see http://www.sqlite.org/lang_expr.html#regexp
327
   */
328
329
330
331
332
333
  public static function sqlFunctionRegexp($pattern, $subject) {
    // preg_quote() cannot be used here, since $pattern may contain reserved
    // regular expression characters already (such as ^, $, etc). Therefore,
    // use a rare character as PCRE delimiter.
    $pattern = '#' . addcslashes($pattern, '#') . '#i';
    return preg_match($pattern, $subject);
334
335
  }

336
337
338
339
340
341
342
343
344
345
346
347
348
349
  /**
   * SQLite compatibility implementation for the LIKE BINARY SQL operator.
   *
   * SQLite supports case-sensitive LIKE operations through the
   * 'case_sensitive_like' PRAGMA statement, but only for ASCII characters, so
   * we have to provide our own implementation with UTF-8 support.
   *
   * @see https://sqlite.org/pragma.html#pragma_case_sensitive_like
   * @see https://sqlite.org/lang_expr.html#like
   */
  public static function sqlFunctionLikeBinary($pattern, $subject) {
    // Replace the SQL LIKE wildcard meta-characters with the equivalent regular
    // expression meta-characters and escape the delimiter that will be used for
    // matching.
350
    $pattern = str_replace(['%', '_'], ['.*?', '.'], preg_quote($pattern, '/'));
351
352
353
    return preg_match('/^' . $pattern . '$/', $subject);
  }

ayelet_Cr's avatar
ayelet_Cr committed
354
  /**
355
   * {@inheritdoc}
ayelet_Cr's avatar
ayelet_Cr committed
356
   */
357
  public function prepare($statement, array $driver_options = []) {
358
    @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);
359
    return new Statement($this->connection, $this, $statement, $driver_options);
ayelet_Cr's avatar
ayelet_Cr committed
360
361
  }

362
363
364
  /**
   * {@inheritdoc}
   */
365
  protected function handleQueryException(\PDOException $e, $query, array $args = [], $options = []) {
366
367
368
369
370
371
372
373
374
375
376
377
378
    // The database schema might be changed by another process in between the
    // time that the statement was prepared and the time the statement was run
    // (e.g. usually happens when running tests). In this case, we need to
    // re-run the query.
    // @see http://www.sqlite.org/faq.html#q15
    // @see http://www.sqlite.org/rescode.html#schema
    if (!empty($e->errorInfo[1]) && $e->errorInfo[1] === 17) {
      return $this->query($query, $args, $options);
    }

    parent::handleQueryException($e, $query, $args, $options);
  }

379
  public function queryRange($query, $from, $count, array $args = [], array $options = []) {
ayelet_Cr's avatar
ayelet_Cr committed
380
381
382
    return $this->query($query . ' LIMIT ' . (int) $from . ', ' . (int) $count, $args, $options);
  }

383
  public function queryTemporary($query, array $args = [], array $options = []) {
ayelet_Cr's avatar
ayelet_Cr committed
384
385
386
387
388
389
390
    // Generate a new temporary table name and protect it from prefixing.
    // SQLite requires that temporary tables to be non-qualified.
    $tablename = $this->generateTemporaryTableName();
    $prefixes = $this->prefixes;
    $prefixes[$tablename] = '';
    $this->setPrefix($prefixes);

391
    $this->query('CREATE TEMPORARY TABLE ' . $tablename . ' AS ' . $query, $args, $options);
ayelet_Cr's avatar
ayelet_Cr committed
392
393
394
395
396
397
398
399
400
401
402
    return $tablename;
  }

  public function driver() {
    return 'sqlite';
  }

  public function databaseType() {
    return 'sqlite';
  }

403
404
405
406
407
408
  /**
   * Overrides \Drupal\Core\Database\Connection::createDatabase().
   *
   * @param string $database
   *   The name of the database to create.
   *
409
   * @throws \Drupal\Core\Database\DatabaseNotFoundException
410
411
412
   */
  public function createDatabase($database) {
    // Verify the database is writable.
413
    $db_directory = new \SplFileInfo(dirname($database));
414
    if (!$db_directory->isDir() && !\Drupal::service('file_system')->mkdir($db_directory->getPathName(), 0755, TRUE)) {
415
416
417
418
      throw new DatabaseNotFoundException('Unable to create database directory ' . $db_directory->getPathName());
    }
  }

ayelet_Cr's avatar
ayelet_Cr committed
419
  public function mapConditionOperator($operator) {
420
    return isset(static::$sqliteConditionOperatorMap[$operator]) ? static::$sqliteConditionOperatorMap[$operator] : NULL;
ayelet_Cr's avatar
ayelet_Cr committed
421
422
  }

423
424
425
  /**
   * {@inheritdoc}
   */
426
  public function prepareStatement(string $query, array $options): StatementInterface {
427
    $query = $this->prefixTables($query);
428
    if (!($options['allow_square_brackets'] ?? FALSE)) {
429
430
      $query = $this->quoteIdentifiers($query);
    }
431
    return new Statement($this->connection, $this, $query, $options['pdo'] ?? []);
432
433
  }

ayelet_Cr's avatar
ayelet_Cr committed
434
  public function nextId($existing_id = 0) {
435
    $this->startTransaction();
ayelet_Cr's avatar
ayelet_Cr committed
436
437
438
439
440
    // We can safely use literal queries here instead of the slower query
    // builder because if a given database breaks here then it can simply
    // override nextId. However, this is unlikely as we deal with short strings
    // and integers and no known databases require special handling for those
    // simple cases. If another transaction wants to write the same row, it will
441
442
443
    // wait until this transaction commits. Also, the return value needs to be
    // set to RETURN_AFFECTED as if it were a real update() query otherwise it
    // is not possible to get the row count properly.
444
    $affected = $this->query('UPDATE {sequences} SET value = GREATEST(value, :existing_id) + 1', [
ayelet_Cr's avatar
ayelet_Cr committed
445
      ':existing_id' => $existing_id,
446
    ], ['return' => Database::RETURN_AFFECTED]);
447
    if (!$affected) {
448
      $this->query('INSERT INTO {sequences} (value) VALUES (:existing_id + 1)', [
ayelet_Cr's avatar
ayelet_Cr committed
449
        ':existing_id' => $existing_id,
450
      ]);
ayelet_Cr's avatar
ayelet_Cr committed
451
452
453
454
455
456
    }
    // The transaction gets committed when the transaction object gets destroyed
    // because it gets out of scope.
    return $this->query('SELECT value FROM {sequences}')->fetchField();
  }

457
458
459
460
461
462
463
464
465
466
  /**
   * {@inheritdoc}
   */
  public function getFullQualifiedTableName($table) {
    $prefix = $this->tablePrefix($table);

    // Don't include the SQLite database file name as part of the table name.
    return $prefix . $table;
  }

467
468
469
470
471
472
473
474
475
476
477
478
  /**
   * {@inheritdoc}
   */
  public static function createConnectionOptionsFromUrl($url, $root) {
    $database = parent::createConnectionOptionsFromUrl($url, $root);

    // A SQLite database path with two leading slashes indicates a system path.
    // Otherwise the path is relative to the Drupal root.
    $url_components = parse_url($url);
    if ($url_components['path'][0] === '/') {
      $url_components['path'] = substr($url_components['path'], 1);
    }
479
    if ($url_components['path'][0] === '/' || $url_components['path'] === ':memory:') {
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
      $database['database'] = $url_components['path'];
    }
    else {
      $database['database'] = $root . '/' . $url_components['path'];
    }

    // User credentials and system port are irrelevant for SQLite.
    unset(
      $database['username'],
      $database['password'],
      $database['port']
    );

    return $database;
  }

  /**
   * {@inheritdoc}
   */
  public static function createUrlFromConnectionOptions(array $connection_options) {
    if (!isset($connection_options['driver'], $connection_options['database'])) {
      throw new \InvalidArgumentException("As a minimum, the connection options array must contain at least the 'driver' and 'database' keys");
    }

    $db_url = 'sqlite://localhost/' . $connection_options['database'];

    if (isset($connection_options['prefix']['default']) && $connection_options['prefix']['default'] !== NULL && $connection_options['prefix']['default'] !== '') {
      $db_url .= '#' . $connection_options['prefix']['default'];
    }

    return $db_url;
  }

ayelet_Cr's avatar
ayelet_Cr committed
513
}