database.inc 7.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223
  1. <?php
  2. /**
  3. * @file
  4. * Database interface code for PostgreSQL database servers.
  5. */
  6. /**
  7. * @addtogroup database
  8. * @{
  9. */
  10. /**
  11. * The name by which to obtain a lock for retrive the next insert id.
  12. */
  13. define('POSTGRESQL_NEXTID_LOCK', 1000);
  14. class DatabaseConnection_pgsql extends DatabaseConnection {
  15. public function __construct(array $connection_options = array()) {
  16. // This driver defaults to transaction support, except if explicitly passed FALSE.
  17. $this->transactionSupport = !isset($connection_options['transactions']) || ($connection_options['transactions'] !== FALSE);
  18. // Transactional DDL is always available in PostgreSQL,
  19. // but we'll only enable it if standard transactions are.
  20. $this->transactionalDDLSupport = $this->transactionSupport;
  21. // Default to TCP connection on port 5432.
  22. if (empty($connection_options['port'])) {
  23. $connection_options['port'] = 5432;
  24. }
  25. // PostgreSQL in trust mode doesn't require a password to be supplied.
  26. if (empty($connection_options['password'])) {
  27. $connection_options['password'] = NULL;
  28. }
  29. // If the password contains a backslash it is treated as an escape character
  30. // http://bugs.php.net/bug.php?id=53217
  31. // so backslashes in the password need to be doubled up.
  32. // The bug was reported against pdo_pgsql 1.0.2, backslashes in passwords
  33. // will break on this doubling up when the bug is fixed, so check the version
  34. //elseif (phpversion('pdo_pgsql') < 'version_this_was_fixed_in') {
  35. else {
  36. $connection_options['password'] = str_replace('\\', '\\\\', $connection_options['password']);
  37. }
  38. $this->connectionOptions = $connection_options;
  39. $dsn = 'pgsql:host=' . $connection_options['host'] . ' dbname=' . $connection_options['database'] . ' port=' . $connection_options['port'];
  40. // Allow PDO options to be overridden.
  41. $connection_options += array(
  42. 'pdo' => array(),
  43. );
  44. $connection_options['pdo'] += array(
  45. // Prepared statements are most effective for performance when queries
  46. // are recycled (used several times). However, if they are not re-used,
  47. // prepared statements become ineffecient. Since most of Drupal's
  48. // prepared queries are not re-used, it should be faster to emulate
  49. // the preparation than to actually ready statements for re-use. If in
  50. // doubt, reset to FALSE and measure performance.
  51. PDO::ATTR_EMULATE_PREPARES => TRUE,
  52. // Convert numeric values to strings when fetching.
  53. PDO::ATTR_STRINGIFY_FETCHES => TRUE,
  54. );
  55. parent::__construct($dsn, $connection_options['username'], $connection_options['password'], $connection_options['pdo']);
  56. // Force PostgreSQL to use the UTF-8 character set by default.
  57. $this->exec("SET NAMES 'UTF8'");
  58. // Execute PostgreSQL init_commands.
  59. if (isset($connection_options['init_commands'])) {
  60. $this->exec(implode('; ', $connection_options['init_commands']));
  61. }
  62. }
  63. public function prepareQuery($query) {
  64. // mapConditionOperator converts LIKE operations to ILIKE for consistency
  65. // with MySQL. However, Postgres does not support ILIKE on bytea (blobs)
  66. // fields.
  67. // To make the ILIKE operator work, we type-cast bytea fields into text.
  68. // @todo This workaround only affects bytea fields, but the involved field
  69. // types involved in the query are unknown, so there is no way to
  70. // conditionally execute this for affected queries only.
  71. return parent::prepareQuery(preg_replace('/ ([^ ]+) +(I*LIKE|NOT +I*LIKE) /i', ' ${1}::text ${2} ', $query));
  72. }
  73. public function query($query, array $args = array(), $options = array()) {
  74. $options += $this->defaultOptions();
  75. // The PDO PostgreSQL driver has a bug which
  76. // doesn't type cast booleans correctly when
  77. // parameters are bound using associative
  78. // arrays.
  79. // See http://bugs.php.net/bug.php?id=48383
  80. foreach ($args as &$value) {
  81. if (is_bool($value)) {
  82. $value = (int) $value;
  83. }
  84. }
  85. try {
  86. if ($query instanceof DatabaseStatementInterface) {
  87. $stmt = $query;
  88. $stmt->execute(NULL, $options);
  89. }
  90. else {
  91. $this->expandArguments($query, $args);
  92. $stmt = $this->prepareQuery($query);
  93. $stmt->execute($args, $options);
  94. }
  95. switch ($options['return']) {
  96. case Database::RETURN_STATEMENT:
  97. return $stmt;
  98. case Database::RETURN_AFFECTED:
  99. return $stmt->rowCount();
  100. case Database::RETURN_INSERT_ID:
  101. return $this->lastInsertId($options['sequence_name']);
  102. case Database::RETURN_NULL:
  103. return;
  104. default:
  105. throw new PDOException('Invalid return directive: ' . $options['return']);
  106. }
  107. }
  108. catch (PDOException $e) {
  109. if ($options['throw_exception']) {
  110. // Add additional debug information.
  111. if ($query instanceof DatabaseStatementInterface) {
  112. $e->query_string = $stmt->getQueryString();
  113. }
  114. else {
  115. $e->query_string = $query;
  116. }
  117. $e->args = $args;
  118. throw $e;
  119. }
  120. return NULL;
  121. }
  122. }
  123. public function queryRange($query, $from, $count, array $args = array(), array $options = array()) {
  124. return $this->query($query . ' LIMIT ' . (int) $count . ' OFFSET ' . (int) $from, $args, $options);
  125. }
  126. public function queryTemporary($query, array $args = array(), array $options = array()) {
  127. $tablename = $this->generateTemporaryTableName();
  128. $this->query(preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE {' . $tablename . '} AS SELECT', $query), $args, $options);
  129. return $tablename;
  130. }
  131. public function driver() {
  132. return 'pgsql';
  133. }
  134. public function databaseType() {
  135. return 'pgsql';
  136. }
  137. public function mapConditionOperator($operator) {
  138. static $specials;
  139. // Function calls not allowed in static declarations, thus this method.
  140. if (!isset($specials)) {
  141. $specials = array(
  142. // In PostgreSQL, 'LIKE' is case-sensitive. For case-insensitive LIKE
  143. // statements, we need to use ILIKE instead.
  144. 'LIKE' => array('operator' => 'ILIKE'),
  145. 'NOT LIKE' => array('operator' => 'NOT ILIKE'),
  146. );
  147. }
  148. return isset($specials[$operator]) ? $specials[$operator] : NULL;
  149. }
  150. /**
  151. * Retrive a the next id in a sequence.
  152. *
  153. * PostgreSQL has built in sequences. We'll use these instead of inserting
  154. * and updating a sequences table.
  155. */
  156. public function nextId($existing = 0) {
  157. // Retrive the name of the sequence. This information cannot be cached
  158. // because the prefix may change, for example, like it does in simpletests.
  159. $sequence_name = $this->makeSequenceName('sequences', 'value');
  160. // When PostgreSQL gets a value too small then it will lock the table,
  161. // retry the INSERT and if it's still too small then alter the sequence.
  162. $id = $this->query("SELECT nextval('" . $sequence_name . "')")->fetchField();
  163. if ($id > $existing) {
  164. return $id;
  165. }
  166. // PostgreSQL advisory locks are simply locks to be used by an
  167. // application such as Drupal. This will prevent other Drupal proccesses
  168. // from altering the sequence while we are.
  169. $this->query("SELECT pg_advisory_lock(" . POSTGRESQL_NEXTID_LOCK . ")");
  170. // While waiting to obtain the lock, the sequence may have been altered
  171. // so lets try again to obtain an adequate value.
  172. $id = $this->query("SELECT nextval('" . $sequence_name . "')")->fetchField();
  173. if ($id > $existing) {
  174. $this->query("SELECT pg_advisory_unlock(" . POSTGRESQL_NEXTID_LOCK . ")");
  175. return $id;
  176. }
  177. // Reset the sequence to a higher value than the existing id.
  178. $this->query("ALTER SEQUENCE " . $sequence_name . " RESTART WITH " . ($existing + 1));
  179. // Retrive the next id. We know this will be as high as we want it.
  180. $id = $this->query("SELECT nextval('" . $sequence_name . "')")->fetchField();
  181. $this->query("SELECT pg_advisory_unlock(" . POSTGRESQL_NEXTID_LOCK . ")");
  182. return $id;
  183. }
  184. }
  185. /**
  186. * @} End of "addtogroup database".
  187. */