Connection.php 15.8 KB
Newer Older
Crell's avatar
Crell committed
1 2 3 4 5 6
<?php

namespace Drupal\Core\Database\Driver\pgsql;

use Drupal\Core\Database\Database;
use Drupal\Core\Database\Connection as DatabaseConnection;
7
use Drupal\Core\Database\DatabaseAccessDeniedException;
8
use Drupal\Core\Database\DatabaseNotFoundException;
Crell's avatar
Crell committed
9 10

/**
11
 * @addtogroup database
Crell's avatar
Crell committed
12 13 14
 * @{
 */

15 16 17
/**
 * PostgreSQL implementation of \Drupal\Core\Database\Connection.
 */
Crell's avatar
Crell committed
18 19 20
class Connection extends DatabaseConnection {

  /**
21
   * The name by which to obtain a lock for retrieve the next insert id.
Crell's avatar
Crell committed
22 23 24
   */
  const POSTGRESQL_NEXTID_LOCK = 1000;

25 26 27 28 29
  /**
   * Error code for "Unknown database" error.
   */
  const DATABASE_NOT_FOUND = 7;

30 31 32 33 34 35 36 37
  /**
   * Error code for "Connection failure" errors.
   *
   * Technically this is an internal error code that will only be shown in the
   * PDOException message. It will need to get extracted.
   */
  const CONNECTION_FAILURE = '08006';

38 39 40 41 42 43 44 45 46 47 48 49 50
  /**
   * A map of condition operators to PostgreSQL operators.
   *
   * In PostgreSQL, 'LIKE' is case-sensitive. ILKE should be used for
   * case-insensitive statements.
   */
  protected static $postgresqlConditionOperatorMap = [
    'LIKE' => ['operator' => 'ILIKE'],
    'LIKE BINARY' => ['operator' => 'LIKE'],
    'NOT LIKE' => ['operator' => 'NOT ILIKE'],
    'REGEXP' => ['operator' => '~*'],
  ];

51 52 53 54 55 56
  /**
   * The list of PostgreSQL reserved key words.
   *
   * @see http://www.postgresql.org/docs/9.4/static/sql-keywords-appendix.html
   */
  protected $postgresqlReservedKeyWords = ['all', 'analyse', 'analyze', 'and',
57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
    'any', 'array', 'as', 'asc', 'asymmetric', 'authorization', 'binary', 'both',
    'case', 'cast', 'check', 'collate', 'collation', 'column', 'concurrently',
    'constraint', 'create', 'cross', 'current_catalog', 'current_date',
    'current_role', 'current_schema', 'current_time', 'current_timestamp',
    'current_user', 'default', 'deferrable', 'desc', 'distinct', 'do', 'else',
    'end', 'except', 'false', 'fetch', 'for', 'foreign', 'freeze', 'from', 'full',
    'grant', 'group', 'having', 'ilike', 'in', 'initially', 'inner', 'intersect',
    'into', 'is', 'isnull', 'join', 'lateral', 'leading', 'left', 'like', 'limit',
    'localtime', 'localtimestamp', 'natural', 'not', 'notnull', 'null', 'offset',
    'on', 'only', 'or', 'order', 'outer', 'over', 'overlaps', 'placing',
    'primary', 'references', 'returning', 'right', 'select', 'session_user',
    'similar', 'some', 'symmetric', 'table', 'then', 'to', 'trailing', 'true',
    'union', 'unique', 'user', 'using', 'variadic', 'verbose', 'when', 'where',
    'window', 'with',
  ];
72

73 74 75
  /**
   * Constructs a connection object.
   */
76
  public function __construct(\PDO $connection, array $connection_options) {
77 78
    parent::__construct($connection, $connection_options);

Crell's avatar
Crell committed
79 80 81 82 83 84 85
    // This driver defaults to transaction support, except if explicitly passed FALSE.
    $this->transactionSupport = !isset($connection_options['transactions']) || ($connection_options['transactions'] !== FALSE);

    // Transactional DDL is always available in PostgreSQL,
    // but we'll only enable it if standard transactions are.
    $this->transactionalDDLSupport = $this->transactionSupport;

86 87 88 89 90 91
    $this->connectionOptions = $connection_options;

    // Force PostgreSQL to use the UTF-8 character set by default.
    $this->connection->exec("SET NAMES 'UTF8'");

    // Execute PostgreSQL init_commands.
92 93 94
    if (isset($connection_options['init_commands'])) {
      $this->connection->exec(implode('; ', $connection_options['init_commands']));
    }
95 96 97 98 99
  }

  /**
   * {@inheritdoc}
   */
100
  public static function open(array &$connection_options = []) {
Crell's avatar
Crell committed
101 102 103 104 105 106 107 108 109 110 111 112 113 114
    // Default to TCP connection on port 5432.
    if (empty($connection_options['port'])) {
      $connection_options['port'] = 5432;
    }

    // PostgreSQL in trust mode doesn't require a password to be supplied.
    if (empty($connection_options['password'])) {
      $connection_options['password'] = NULL;
    }
    // If the password contains a backslash it is treated as an escape character
    // http://bugs.php.net/bug.php?id=53217
    // so backslashes in the password need to be doubled up.
    // The bug was reported against pdo_pgsql 1.0.2, backslashes in passwords
    // will break on this doubling up when the bug is fixed, so check the version
115
    //elseif (phpversion('pdo_pgsql') < 'version_this_was_fixed_in') {
Crell's avatar
Crell committed
116 117 118 119
    else {
      $connection_options['password'] = str_replace('\\', '\\\\', $connection_options['password']);
    }

120
    $connection_options['database'] = (!empty($connection_options['database']) ? $connection_options['database'] : 'template1');
Crell's avatar
Crell committed
121 122 123
    $dsn = 'pgsql:host=' . $connection_options['host'] . ' dbname=' . $connection_options['database'] . ' port=' . $connection_options['port'];

    // Allow PDO options to be overridden.
124 125 126 127
    $connection_options += [
      'pdo' => [],
    ];
    $connection_options['pdo'] += [
128
      \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
Crell's avatar
Crell committed
129 130
      // Prepared statements are most effective for performance when queries
      // are recycled (used several times). However, if they are not re-used,
131
      // prepared statements become inefficient. Since most of Drupal's
Crell's avatar
Crell committed
132 133 134
      // prepared queries are not re-used, it should be faster to emulate
      // the preparation than to actually ready statements for re-use. If in
      // doubt, reset to FALSE and measure performance.
135
      \PDO::ATTR_EMULATE_PREPARES => TRUE,
Crell's avatar
Crell committed
136
      // Convert numeric values to strings when fetching.
137
      \PDO::ATTR_STRINGIFY_FETCHES => TRUE,
138
    ];
139 140 141 142 143 144 145 146 147 148 149 150 151 152 153

    try {
      $pdo = new \PDO($dsn, $connection_options['username'], $connection_options['password'], $connection_options['pdo']);
    }
    catch (\PDOException $e) {
      if (static::getSQLState($e) == static::CONNECTION_FAILURE) {
        if (strpos($e->getMessage(), 'password authentication failed for user') !== FALSE) {
          throw new DatabaseAccessDeniedException($e->getMessage(), $e->getCode(), $e);
        }
        elseif (strpos($e->getMessage(), 'database') !== FALSE && strpos($e->getMessage(), 'does not exist') !== FALSE) {
          throw new DatabaseNotFoundException($e->getMessage(), $e->getCode(), $e);
        }
      }
      throw $e;
    }
Crell's avatar
Crell committed
154

155
    return $pdo;
Crell's avatar
Crell committed
156 157
  }

158 159 160
  /**
   * {@inheritdoc}
   */
161
  public function query($query, array $args = [], $options = []) {
Crell's avatar
Crell committed
162 163
    $options += $this->defaultOptions();

164 165 166
    // The PDO PostgreSQL driver has a bug which doesn't type cast booleans
    // correctly when parameters are bound using associative arrays.
    // @see http://bugs.php.net/bug.php?id=48383
Crell's avatar
Crell committed
167 168 169 170 171 172
    foreach ($args as &$value) {
      if (is_bool($value)) {
        $value = (int) $value;
      }
    }

173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204
    // We need to wrap queries with a savepoint if:
    // - Currently in a transaction.
    // - A 'mimic_implicit_commit' does not exist already.
    // - The query is not a savepoint query.
    $wrap_with_savepoint = $this->inTransaction() &&
      !isset($this->transactionLayers['mimic_implicit_commit']) &&
      !(is_string($query) && (
        stripos($query, 'ROLLBACK TO SAVEPOINT ') === 0 ||
        stripos($query, 'RELEASE SAVEPOINT ') === 0 ||
        stripos($query, 'SAVEPOINT ') === 0
      )
    );
    if ($wrap_with_savepoint) {
      // Create a savepoint so we can rollback a failed query. This is so we can
      // mimic MySQL and SQLite transactions which don't fail if a single query
      // fails. This is important for tables that are created on demand. For
      // example, \Drupal\Core\Cache\DatabaseBackend.
      $this->addSavepoint();
      try {
        $return = parent::query($query, $args, $options);
        $this->releaseSavepoint();
      }
      catch (\Exception $e) {
        $this->rollbackSavepoint();
        throw $e;
      }
    }
    else {
      $return = parent::query($query, $args, $options);
    }

    return $return;
Crell's avatar
Crell committed
205 206
  }

207
  public function prepareQuery($query) {
208 209 210 211 212
    // mapConditionOperator converts some operations (LIKE, REGEXP, etc.) to
    // PostgreSQL equivalents (ILIKE, ~*, etc.). However PostgreSQL doesn't
    // automatically cast the fields to the right type for these operators,
    // so we need to alter the query and add the type-cast.
    return parent::prepareQuery(preg_replace('/ ([^ ]+) +(I*LIKE|NOT +I*LIKE|~\*) /i', ' ${1}::text ${2} ', $query));
213 214
  }

215
  public function queryRange($query, $from, $count, array $args = [], array $options = []) {
Crell's avatar
Crell committed
216 217 218
    return $this->query($query . ' LIMIT ' . (int) $count . ' OFFSET ' . (int) $from, $args, $options);
  }

219
  public function queryTemporary($query, array $args = [], array $options = []) {
Crell's avatar
Crell committed
220
    $tablename = $this->generateTemporaryTableName();
221
    $this->query('CREATE TEMPORARY TABLE {' . $tablename . '} AS ' . $query, $args, $options);
Crell's avatar
Crell committed
222 223 224
    return $tablename;
  }

225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246
  /**
   * {@inheritdoc}
   */
  public function escapeField($field) {
    $escaped = parent::escapeField($field);

    // Remove any invalid start character.
    $escaped = preg_replace('/^[^A-Za-z0-9_]/', '', $escaped);

    // The pgsql database driver does not support field names that contain
    // periods (supported by PostgreSQL server) because this method may be
    // called by a field with a table alias as part of SQL conditions or
    // order by statements. This will consider a period as a table alias
    // identifier, and split the string at the first period.
    if (preg_match('/^([A-Za-z0-9_]+)"?[.]"?([A-Za-z0-9_.]+)/', $escaped, $parts)) {
      $table = $parts[1];
      $column = $parts[2];

      // Use escape alias because escapeField may contain multiple periods that
      // need to be escaped.
      $escaped = $this->escapeTable($table) . '.' . $this->escapeAlias($column);
    }
247 248
    else {
      $escaped = $this->doEscape($escaped);
249
    }
250 251 252 253 254 255 256 257 258

    return $escaped;
  }

  /**
   * {@inheritdoc}
   */
  public function escapeAlias($field) {
    $escaped = preg_replace('/[^A-Za-z0-9_]+/', '', $field);
259
    $escaped = $this->doEscape($escaped);
260 261 262 263 264 265 266 267 268
    return $escaped;
  }

  /**
   * {@inheritdoc}
   */
  public function escapeTable($table) {
    $escaped = parent::escapeTable($table);

269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286
    // Ensure that each part (database, schema and table) of the table name is
    // properly and independently escaped.
    $parts = explode('.', $escaped);
    $parts = array_map([$this, 'doEscape'], $parts);
    $escaped = implode('.', $parts);

    return $escaped;
  }

  /**
   * Escape a string if needed.
   *
   * @param $string
   *   The string to escape.
   * @return string
   *   The escaped string.
   */
  protected function doEscape($string) {
287
    // Quote identifier to make it case-sensitive.
288 289
    if (preg_match('/[A-Z]/', $string)) {
      $string = '"' . $string . '"';
290
    }
291 292 293
    elseif (in_array(strtolower($string), $this->postgresqlReservedKeyWords)) {
      // Quote the string for PostgreSQL reserved key words.
      $string = '"' . $string . '"';
294
    }
295
    return $string;
296 297
  }

Crell's avatar
Crell committed
298 299 300 301 302 303 304 305
  public function driver() {
    return 'pgsql';
  }

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

306 307 308 309 310 311
  /**
   * Overrides \Drupal\Core\Database\Connection::createDatabase().
   *
   * @param string $database
   *   The name of the database to create.
   *
312
   * @throws \Drupal\Core\Database\DatabaseNotFoundException
313 314 315 316 317 318 319 320
   */
  public function createDatabase($database) {
    // Escape the database name.
    $database = Database::getConnection()->escapeDatabase($database);

    // If the PECL intl extension is installed, use it to determine the proper
    // locale.  Otherwise, fall back to en_US.
    if (class_exists('Locale')) {
321
      $locale = \Locale::getDefault();
322 323 324 325 326 327 328
    }
    else {
      $locale = 'en_US';
    }

    try {
      // Create the database and set it as active.
329
      $this->connection->exec("CREATE DATABASE $database WITH TEMPLATE template0 ENCODING='utf8' LC_CTYPE='$locale.utf8' LC_COLLATE='$locale.utf8'");
330 331 332 333 334 335
    }
    catch (\Exception $e) {
      throw new DatabaseNotFoundException($e->getMessage());
    }
  }

Crell's avatar
Crell committed
336
  public function mapConditionOperator($operator) {
337
    return isset(static::$postgresqlConditionOperatorMap[$operator]) ? static::$postgresqlConditionOperatorMap[$operator] : NULL;
Crell's avatar
Crell committed
338 339 340
  }

  /**
341
   * Retrieve a the next id in a sequence.
Crell's avatar
Crell committed
342 343 344 345 346 347
   *
   * PostgreSQL has built in sequences. We'll use these instead of inserting
   * and updating a sequences table.
   */
  public function nextId($existing = 0) {

348
    // Retrieve the name of the sequence. This information cannot be cached
Crell's avatar
Crell committed
349 350 351 352 353 354 355 356 357 358 359
    // because the prefix may change, for example, like it does in simpletests.
    $sequence_name = $this->makeSequenceName('sequences', 'value');

    // When PostgreSQL gets a value too small then it will lock the table,
    // retry the INSERT and if it's still too small then alter the sequence.
    $id = $this->query("SELECT nextval('" . $sequence_name . "')")->fetchField();
    if ($id > $existing) {
      return $id;
    }

    // PostgreSQL advisory locks are simply locks to be used by an
360
    // application such as Drupal. This will prevent other Drupal processes
Crell's avatar
Crell committed
361 362 363 364 365 366 367 368 369 370 371 372 373 374
    // from altering the sequence while we are.
    $this->query("SELECT pg_advisory_lock(" . self::POSTGRESQL_NEXTID_LOCK . ")");

    // While waiting to obtain the lock, the sequence may have been altered
    // so lets try again to obtain an adequate value.
    $id = $this->query("SELECT nextval('" . $sequence_name . "')")->fetchField();
    if ($id > $existing) {
      $this->query("SELECT pg_advisory_unlock(" . self::POSTGRESQL_NEXTID_LOCK . ")");
      return $id;
    }

    // Reset the sequence to a higher value than the existing id.
    $this->query("ALTER SEQUENCE " . $sequence_name . " RESTART WITH " . ($existing + 1));

375
    // Retrieve the next id. We know this will be as high as we want it.
Crell's avatar
Crell committed
376 377 378 379 380 381
    $id = $this->query("SELECT nextval('" . $sequence_name . "')")->fetchField();

    $this->query("SELECT pg_advisory_unlock(" . self::POSTGRESQL_NEXTID_LOCK . ")");

    return $id;
  }
382

383 384 385 386 387 388 389 390 391 392 393 394 395
  /**
   * {@inheritdoc}
   */
  public function getFullQualifiedTableName($table) {
    $options = $this->getConnectionOptions();
    $prefix = $this->tablePrefix($table);

    // The fully qualified table name in PostgreSQL is in the form of
    // <database>.<schema>.<table>, so we have to include the 'public' schema in
    // the return value.
    return $options['database'] . '.public.' . $prefix . $table;
  }

396 397 398 399 400 401 402 403 404 405
  /**
   * Add a new savepoint with an unique name.
   *
   * The main use for this method is to mimic InnoDB functionality, which
   * provides an inherent savepoint before any query in a transaction.
   *
   * @param $savepoint_name
   *   A string representing the savepoint name. By default,
   *   "mimic_implicit_commit" is used.
   *
406
   * @see Drupal\Core\Database\Connection::pushTransaction()
407 408 409 410 411 412 413 414 415 416 417 418 419 420
   */
  public function addSavepoint($savepoint_name = 'mimic_implicit_commit') {
    if ($this->inTransaction()) {
      $this->pushTransaction($savepoint_name);
    }
  }

  /**
   * Release a savepoint by name.
   *
   * @param $savepoint_name
   *   A string representing the savepoint name. By default,
   *   "mimic_implicit_commit" is used.
   *
421
   * @see Drupal\Core\Database\Connection::popTransaction()
422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437
   */
  public function releaseSavepoint($savepoint_name = 'mimic_implicit_commit') {
    if (isset($this->transactionLayers[$savepoint_name])) {
      $this->popTransaction($savepoint_name);
    }
  }

  /**
   * Rollback a savepoint by name if it exists.
   *
   * @param $savepoint_name
   *   A string representing the savepoint name. By default,
   *   "mimic_implicit_commit" is used.
   */
  public function rollbackSavepoint($savepoint_name = 'mimic_implicit_commit') {
    if (isset($this->transactionLayers[$savepoint_name])) {
438
      $this->rollBack($savepoint_name);
439 440
    }
  }
441 442 443 444

  /**
   * {@inheritdoc}
   */
445
  public function upsert($table, array $options = []) {
446 447 448 449 450 451 452 453 454 455 456
    // Use the (faster) native Upsert implementation for PostgreSQL >= 9.5.
    if (version_compare($this->version(), '9.5', '>=')) {
      $class = $this->getDriverClass('NativeUpsert');
    }
    else {
      $class = $this->getDriverClass('Upsert');
    }

    return new $class($this, $table, $options);
  }

Crell's avatar
Crell committed
457 458 459
}

/**
460
 * @} End of "addtogroup database".
Crell's avatar
Crell committed
461
 */