vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/PostgreSqlPlatform.php line 874

Open in your IDE?
  1. <?php
  3. namespace Doctrine\DBAL\Platforms;
  5. use Doctrine\DBAL\Schema\Column;
  6. use Doctrine\DBAL\Schema\ColumnDiff;
  7. use Doctrine\DBAL\Schema\ForeignKeyConstraint;
  8. use Doctrine\DBAL\Schema\Identifier;
  9. use Doctrine\DBAL\Schema\Index;
  10. use Doctrine\DBAL\Schema\Sequence;
  11. use Doctrine\DBAL\Schema\TableDiff;
  12. use Doctrine\DBAL\Types\BigIntType;
  13. use Doctrine\DBAL\Types\BinaryType;
  14. use Doctrine\DBAL\Types\BlobType;
  15. use Doctrine\DBAL\Types\IntegerType;
  16. use Doctrine\DBAL\Types\Type;
  17. use Doctrine\Deprecations\Deprecation;
  18. use UnexpectedValueException;
  20. use function array_diff;
  21. use function array_merge;
  22. use function array_unique;
  23. use function array_values;
  24. use function count;
  25. use function explode;
  26. use function implode;
  27. use function in_array;
  28. use function is_array;
  29. use function is_bool;
  30. use function is_numeric;
  31. use function is_string;
  32. use function sprintf;
  33. use function strpos;
  34. use function strtolower;
  35. use function trim;
  37. /**
  38. * PostgreSqlPlatform.
  39. *
  40. * @deprecated Use PostgreSQL 9.4 or newer
  41. *
  42. * @todo Rename: PostgreSQLPlatform
  43. */
  44. class PostgreSqlPlatform extends AbstractPlatform
  45. {
  46. /** @var bool */
  47. private $useBooleanTrueFalseStrings = true;
  49. /** @var string[][] PostgreSQL booleans literals */
  50. private $booleanLiterals = [
  51. 'true' => [
  52. 't',
  53. 'true',
  54. 'y',
  55. 'yes',
  56. 'on',
  57. '1',
  58. ],
  59. 'false' => [
  60. 'f',
  61. 'false',
  62. 'n',
  63. 'no',
  64. 'off',
  65. '0',
  66. ],
  67. ];
  69. /**
  70. * PostgreSQL has different behavior with some drivers
  71. * with regard to how booleans have to be handled.
  72. *
  73. * Enables use of 'true'/'false' or otherwise 1 and 0 instead.
  74. *
  75. * @param bool $flag
  76. *
  77. * @return void
  78. */
  79. public function setUseBooleanTrueFalseStrings($flag)
  80. {
  81. $this->useBooleanTrueFalseStrings = (bool) $flag;
  82. }
  84. /**
  85. * {@inheritDoc}
  86. */
  87. public function getSubstringExpression($string, $start, $length = null)
  88. {
  89. if ($length === null) {
  90. return 'SUBSTRING(' . $string . ' FROM ' . $start . ')';
  91. }
  93. return 'SUBSTRING(' . $string . ' FROM ' . $start . ' FOR ' . $length . ')';
  94. }
  96. /**
  97. * {@inheritDoc}
  98. */
  99. public function getNowExpression()
  100. {
  101. return 'LOCALTIMESTAMP(0)';
  102. }
  104. /**
  105. * {@inheritDoc}
  106. */
  107. public function getRegexpExpression()
  108. {
  109. return 'SIMILAR TO';
  110. }
  112. /**
  113. * {@inheritDoc}
  114. */
  115. public function getLocateExpression($str, $substr, $startPos = false)
  116. {
  117. if ($startPos !== false) {
  118. $str = $this->getSubstringExpression($str, $startPos);
  120. return 'CASE WHEN (POSITION(' . $substr . ' IN ' . $str . ') = 0) THEN 0'
  121. . ' ELSE (POSITION(' . $substr . ' IN ' . $str . ') + ' . ($startPos - 1) . ') END';
  122. }
  124. return 'POSITION(' . $substr . ' IN ' . $str . ')';
  125. }
  127. /**
  128. * {@inheritdoc}
  129. */
  130. protected function getDateArithmeticIntervalExpression($date, $operator, $interval, $unit)
  131. {
  132. if ($unit === DateIntervalUnit::QUARTER) {
  133. $interval *= 3;
  134. $unit = DateIntervalUnit::MONTH;
  135. }
  137. return '(' . $date . ' ' . $operator . ' (' . $interval . " || ' " . $unit . "')::interval)";
  138. }
  140. /**
  141. * {@inheritDoc}
  142. */
  143. public function getDateDiffExpression($date1, $date2)
  144. {
  145. return '(DATE(' . $date1 . ')-DATE(' . $date2 . '))';
  146. }
  148. /**
  149. * {@inheritDoc}
  150. */
  151. public function supportsSequences()
  152. {
  153. return true;
  154. }
  156. /**
  157. * {@inheritDoc}
  158. */
  159. public function supportsSchemas()
  160. {
  161. return true;
  162. }
  164. /**
  165. * {@inheritdoc}
  166. */
  167. public function getDefaultSchemaName()
  168. {
  169. return 'public';
  170. }
  172. /**
  173. * {@inheritDoc}
  174. */
  175. public function supportsIdentityColumns()
  176. {
  177. return true;
  178. }
  180. /**
  181. * {@inheritdoc}
  182. */
  183. public function supportsPartialIndexes()
  184. {
  185. return true;
  186. }
  188. /**
  189. * {@inheritdoc}
  190. */
  191. public function usesSequenceEmulatedIdentityColumns()
  192. {
  193. return true;
  194. }
  196. /**
  197. * {@inheritdoc}
  198. */
  199. public function getIdentitySequenceName($tableName, $columnName)
  200. {
  201. return $tableName . '_' . $columnName . '_seq';
  202. }
  204. /**
  205. * {@inheritDoc}
  206. */
  207. public function supportsCommentOnStatement()
  208. {
  209. return true;
  210. }
  212. /**
  213. * {@inheritDoc}
  214. *
  215. * @deprecated
  216. */
  217. public function prefersSequences()
  218. {
  219. Deprecation::trigger(
  220. 'doctrine/dbal',
  221. 'https://github.com/doctrine/dbal/pull/4229',
  222. 'AbstractPlatform::prefersSequences() is deprecated without replacement and removed in DBAL 3.0'
  223. );
  225. return true;
  226. }
  228. /**
  229. * {@inheritDoc}
  230. */
  231. public function hasNativeGuidType()
  232. {
  233. return true;
  234. }
  236. /**
  237. * {@inheritDoc}
  238. */
  239. public function getListDatabasesSQL()
  240. {
  241. return 'SELECT datname FROM pg_database';
  242. }
  244. /**
  245. * {@inheritDoc}
  246. */
  247. public function getListNamespacesSQL()
  248. {
  249. return "SELECT schema_name AS nspname
  250. FROM information_schema.schemata
  251. WHERE schema_name NOT LIKE 'pg\_%'
  252. AND schema_name != 'information_schema'";
  253. }
  255. /**
  256. * {@inheritDoc}
  257. */
  258. public function getListSequencesSQL($database)
  259. {
  260. return "SELECT sequence_name AS relname,
  261. sequence_schema AS schemaname
  262. FROM information_schema.sequences
  263. WHERE sequence_schema NOT LIKE 'pg\_%'
  264. AND sequence_schema != 'information_schema'";
  265. }
  267. /**
  268. * {@inheritDoc}
  269. */
  270. public function getListTablesSQL()
  271. {
  272. return "SELECT quote_ident(table_name) AS table_name,
  273. table_schema AS schema_name
  274. FROM information_schema.tables
  275. WHERE table_schema NOT LIKE 'pg\_%'
  276. AND table_schema != 'information_schema'
  277. AND table_name != 'geometry_columns'
  278. AND table_name != 'spatial_ref_sys'
  279. AND table_type != 'VIEW'";
  280. }
  282. /**
  283. * {@inheritDoc}
  284. */
  285. public function getListViewsSQL($database)
  286. {
  287. return 'SELECT quote_ident(table_name) AS viewname,
  288. table_schema AS schemaname,
  289. view_definition AS definition
  290. FROM information_schema.views
  291. WHERE view_definition IS NOT NULL';
  292. }
  294. /**
  295. * @param string $table
  296. * @param string|null $database
  297. *
  298. * @return string
  299. */
  300. public function getListTableForeignKeysSQL($table, $database = null)
  301. {
  302. return 'SELECT quote_ident(r.conname) as conname, pg_catalog.pg_get_constraintdef(r.oid, true) as condef
  303. FROM pg_catalog.pg_constraint r
  304. WHERE r.conrelid =
  305. (
  306. SELECT c.oid
  307. FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
  308. WHERE ' . $this->getTableWhereClause($table) . " AND n.oid = c.relnamespace
  309. )
  310. AND r.contype = 'f'";
  311. }
  313. /**
  314. * {@inheritDoc}
  315. */
  316. public function getCreateViewSQL($name, $sql)
  317. {
  318. return 'CREATE VIEW ' . $name . ' AS ' . $sql;
  319. }
  321. /**
  322. * {@inheritDoc}
  323. */
  324. public function getDropViewSQL($name)
  325. {
  326. return 'DROP VIEW ' . $name;
  327. }
  329. /**
  330. * {@inheritDoc}
  331. */
  332. public function getListTableConstraintsSQL($table)
  333. {
  334. $table = new Identifier($table);
  335. $table = $this->quoteStringLiteral($table->getName());
  337. return sprintf(
  338. <<<'SQL'
  339. SELECT
  340. quote_ident(relname) as relname
  341. FROM
  342. pg_class
  343. WHERE oid IN (
  344. SELECT indexrelid
  345. FROM pg_index, pg_class
  346. WHERE pg_class.relname = %s
  347. AND pg_class.oid = pg_index.indrelid
  348. AND (indisunique = 't' OR indisprimary = 't')
  349. )
  350. SQL
  351. ,
  352. $table
  353. );
  354. }
  356. /**
  357. * {@inheritDoc}
  358. *
  359. * @link http://ezcomponents.org/docs/api/trunk/DatabaseSchema/ezcDbSchemaPgsqlReader.html
  360. */
  361. public function getListTableIndexesSQL($table, $database = null)
  362. {
  363. return 'SELECT quote_ident(relname) as relname, pg_index.indisunique, pg_index.indisprimary,
  364. pg_index.indkey, pg_index.indrelid,
  365. pg_get_expr(indpred, indrelid) AS where
  366. FROM pg_class, pg_index
  367. WHERE oid IN (
  368. SELECT indexrelid
  369. FROM pg_index si, pg_class sc, pg_namespace sn
  370. WHERE ' . $this->getTableWhereClause($table, 'sc', 'sn') . '
  371. AND sc.oid=si.indrelid AND sc.relnamespace = sn.oid
  372. ) AND pg_index.indexrelid = oid';
  373. }
  375. /**
  376. * @param string $table
  377. * @param string $classAlias
  378. * @param string $namespaceAlias
  379. *
  380. * @return string
  381. */
  382. private function getTableWhereClause($table, $classAlias = 'c', $namespaceAlias = 'n')
  383. {
  384. $whereClause = $namespaceAlias . ".nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND ";
  385. if (strpos($table, '.') !== false) {
  386. [$schema, $table] = explode('.', $table);
  387. $schema = $this->quoteStringLiteral($schema);
  388. } else {
  389. $schema = 'ANY(current_schemas(false))';
  390. }
  392. $table = new Identifier($table);
  393. $table = $this->quoteStringLiteral($table->getName());
  395. return $whereClause . sprintf(
  396. '%s.relname = %s AND %s.nspname = %s',
  397. $classAlias,
  398. $table,
  399. $namespaceAlias,
  400. $schema
  401. );
  402. }
  404. /**
  405. * {@inheritDoc}
  406. */
  407. public function getListTableColumnsSQL($table, $database = null)
  408. {
  409. return "SELECT
  410. a.attnum,
  411. quote_ident(a.attname) AS field,
  412. t.typname AS type,
  413. format_type(a.atttypid, a.atttypmod) AS complete_type,
  414. (SELECT t1.typname FROM pg_catalog.pg_type t1 WHERE t1.oid = t.typbasetype) AS domain_type,
  415. (SELECT format_type(t2.typbasetype, t2.typtypmod) FROM
  416. pg_catalog.pg_type t2 WHERE t2.typtype = 'd' AND t2.oid = a.atttypid) AS domain_complete_type,
  417. a.attnotnull AS isnotnull,
  418. (SELECT 't'
  419. FROM pg_index
  420. WHERE c.oid = pg_index.indrelid
  421. AND pg_index.indkey[0] = a.attnum
  422. AND pg_index.indisprimary = 't'
  423. ) AS pri,
  424. (SELECT pg_get_expr(adbin, adrelid)
  425. FROM pg_attrdef
  426. WHERE c.oid = pg_attrdef.adrelid
  427. AND pg_attrdef.adnum=a.attnum
  428. ) AS default,
  429. (SELECT pg_description.description
  430. FROM pg_description WHERE pg_description.objoid = c.oid AND a.attnum = pg_description.objsubid
  431. ) AS comment
  432. FROM pg_attribute a, pg_class c, pg_type t, pg_namespace n
  433. WHERE " . $this->getTableWhereClause($table, 'c', 'n') . '
  434. AND a.attnum > 0
  435. AND a.attrelid = c.oid
  436. AND a.atttypid = t.oid
  437. AND n.oid = c.relnamespace
  438. ORDER BY a.attnum';
  439. }
  441. /**
  442. * {@inheritDoc}
  443. */
  444. public function getCreateDatabaseSQL($name)
  445. {
  446. return 'CREATE DATABASE ' . $name;
  447. }
  449. /**
  450. * Returns the SQL statement for disallowing new connections on the given database.
  451. *
  452. * This is useful to force DROP DATABASE operations which could fail because of active connections.
  453. *
  454. * @deprecated
  455. *
  456. * @param string $database The name of the database to disallow new connections for.
  457. *
  458. * @return string
  459. */
  460. public function getDisallowDatabaseConnectionsSQL($database)
  461. {
  462. return "UPDATE pg_database SET datallowconn = 'false' WHERE datname = " . $this->quoteStringLiteral($database);
  463. }
  465. /**
  466. * Returns the SQL statement for closing currently active connections on the given database.
  467. *
  468. * This is useful to force DROP DATABASE operations which could fail because of active connections.
  469. *
  470. * @deprecated
  471. *
  472. * @param string $database The name of the database to close currently active connections for.
  473. *
  474. * @return string
  475. */
  476. public function getCloseActiveDatabaseConnectionsSQL($database)
  477. {
  478. return 'SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = '
  479. . $this->quoteStringLiteral($database);
  480. }
  482. /**
  483. * {@inheritDoc}
  484. */
  485. public function getAdvancedForeignKeyOptionsSQL(ForeignKeyConstraint $foreignKey)
  486. {
  487. $query = '';
  489. if ($foreignKey->hasOption('match')) {
  490. $query .= ' MATCH ' . $foreignKey->getOption('match');
  491. }
  493. $query .= parent::getAdvancedForeignKeyOptionsSQL($foreignKey);
  495. if ($foreignKey->hasOption('deferrable') && $foreignKey->getOption('deferrable') !== false) {
  496. $query .= ' DEFERRABLE';
  497. } else {
  498. $query .= ' NOT DEFERRABLE';
  499. }
  501. if (
  502. ($foreignKey->hasOption('feferred') && $foreignKey->getOption('feferred') !== false)
  503. || ($foreignKey->hasOption('deferred') && $foreignKey->getOption('deferred') !== false)
  504. ) {
  505. $query .= ' INITIALLY DEFERRED';
  506. } else {
  507. $query .= ' INITIALLY IMMEDIATE';
  508. }
  510. return $query;
  511. }
  513. /**
  514. * {@inheritDoc}
  515. */
  516. public function getAlterTableSQL(TableDiff $diff)
  517. {
  518. $sql = [];
  519. $commentsSQL = [];
  520. $columnSql = [];
  522. foreach ($diff->addedColumns as $column) {
  523. if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) {
  524. continue;
  525. }
  527. $query = 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray());
  528. $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query;
  530. $comment = $this->getColumnComment($column);
  532. if ($comment === null || $comment === '') {
  533. continue;
  534. }
  536. $commentsSQL[] = $this->getCommentOnColumnSQL(
  537. $diff->getName($this)->getQuotedName($this),
  538. $column->getQuotedName($this),
  539. $comment
  540. );
  541. }
  543. foreach ($diff->removedColumns as $column) {
  544. if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) {
  545. continue;
  546. }
  548. $query = 'DROP ' . $column->getQuotedName($this);
  549. $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query;
  550. }
  552. foreach ($diff->changedColumns as $columnDiff) {
  553. if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) {
  554. continue;
  555. }
  557. if ($this->isUnchangedBinaryColumn($columnDiff)) {
  558. continue;
  559. }
  561. $oldColumnName = $columnDiff->getOldColumnName()->getQuotedName($this);
  562. $column = $columnDiff->column;
  564. if (
  565. $columnDiff->hasChanged('type')
  566. || $columnDiff->hasChanged('precision')
  567. || $columnDiff->hasChanged('scale')
  568. || $columnDiff->hasChanged('fixed')
  569. ) {
  570. $type = $column->getType();
  572. // SERIAL/BIGSERIAL are not "real" types and we can't alter a column to that type
  573. $columnDefinition = $column->toArray();
  574. $columnDefinition['autoincrement'] = false;
  576. // here was a server version check before, but DBAL API does not support this anymore.
  577. $query = 'ALTER ' . $oldColumnName . ' TYPE ' . $type->getSQLDeclaration($columnDefinition, $this);
  578. $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query;
  579. }
  581. if ($columnDiff->hasChanged('default') || $this->typeChangeBreaksDefaultValue($columnDiff)) {
  582. $defaultClause = $column->getDefault() === null
  583. ? ' DROP DEFAULT'
  584. : ' SET' . $this->getDefaultValueDeclarationSQL($column->toArray());
  585. $query = 'ALTER ' . $oldColumnName . $defaultClause;
  586. $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query;
  587. }
  589. if ($columnDiff->hasChanged('notnull')) {
  590. $query = 'ALTER ' . $oldColumnName . ' ' . ($column->getNotnull() ? 'SET' : 'DROP') . ' NOT NULL';
  591. $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query;
  592. }
  594. if ($columnDiff->hasChanged('autoincrement')) {
  595. if ($column->getAutoincrement()) {
  596. // add autoincrement
  597. $seqName = $this->getIdentitySequenceName($diff->name, $oldColumnName);
  599. $sql[] = 'CREATE SEQUENCE ' . $seqName;
  600. $sql[] = "SELECT setval('" . $seqName . "', (SELECT MAX(" . $oldColumnName . ') FROM '
  601. . $diff->getName($this)->getQuotedName($this) . '))';
  602. $query = 'ALTER ' . $oldColumnName . " SET DEFAULT nextval('" . $seqName . "')";
  603. $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query;
  604. } else {
  605. // Drop autoincrement, but do NOT drop the sequence. It might be re-used by other tables or have
  606. $query = 'ALTER ' . $oldColumnName . ' DROP DEFAULT';
  607. $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query;
  608. }
  609. }
  611. $newComment = $this->getColumnComment($column);
  612. $oldComment = $this->getOldColumnComment($columnDiff);
  614. if (
  615. $columnDiff->hasChanged('comment')
  616. || ($columnDiff->fromColumn !== null && $oldComment !== $newComment)
  617. ) {
  618. $commentsSQL[] = $this->getCommentOnColumnSQL(
  619. $diff->getName($this)->getQuotedName($this),
  620. $column->getQuotedName($this),
  621. $newComment
  622. );
  623. }
  625. if (! $columnDiff->hasChanged('length')) {
  626. continue;
  627. }
  629. $query = 'ALTER ' . $oldColumnName . ' TYPE '
  630. . $column->getType()->getSQLDeclaration($column->toArray(), $this);
  631. $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query;
  632. }
  634. foreach ($diff->renamedColumns as $oldColumnName => $column) {
  635. if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) {
  636. continue;
  637. }
  639. $oldColumnName = new Identifier($oldColumnName);
  641. $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) .
  642. ' RENAME COLUMN ' . $oldColumnName->getQuotedName($this) . ' TO ' . $column->getQuotedName($this);
  643. }
  645. $tableSql = [];
  647. if (! $this->onSchemaAlterTable($diff, $tableSql)) {
  648. $sql = array_merge($sql, $commentsSQL);
  650. $newName = $diff->getNewName();
  652. if ($newName !== false) {
  653. $sql[] = sprintf(
  654. 'ALTER TABLE %s RENAME TO %s',
  655. $diff->getName($this)->getQuotedName($this),
  656. $newName->getQuotedName($this)
  657. );
  658. }
  660. $sql = array_merge(
  661. $this->getPreAlterTableIndexForeignKeySQL($diff),
  662. $sql,
  663. $this->getPostAlterTableIndexForeignKeySQL($diff)
  664. );
  665. }
  667. return array_merge($sql, $tableSql, $columnSql);
  668. }
  670. /**
  671. * Checks whether a given column diff is a logically unchanged binary type column.
  672. *
  673. * Used to determine whether a column alteration for a binary type column can be skipped.
  674. * Doctrine's {@link BinaryType} and {@link BlobType} are mapped to the same database column type on this platform
  675. * as this platform does not have a native VARBINARY/BINARY column type. Therefore the comparator
  676. * might detect differences for binary type columns which do not have to be propagated
  677. * to database as there actually is no difference at database level.
  678. *
  679. * @param ColumnDiff $columnDiff The column diff to check against.
  680. *
  681. * @return bool True if the given column diff is an unchanged binary type column, false otherwise.
  682. */
  683. private function isUnchangedBinaryColumn(ColumnDiff $columnDiff)
  684. {
  685. $columnType = $columnDiff->column->getType();
  687. if (! $columnType instanceof BinaryType && ! $columnType instanceof BlobType) {
  688. return false;
  689. }
  691. $fromColumn = $columnDiff->fromColumn instanceof Column ? $columnDiff->fromColumn : null;
  693. if ($fromColumn) {
  694. $fromColumnType = $fromColumn->getType();
  696. if (! $fromColumnType instanceof BinaryType && ! $fromColumnType instanceof BlobType) {
  697. return false;
  698. }
  700. return count(array_diff($columnDiff->changedProperties, ['type', 'length', 'fixed'])) === 0;
  701. }
  703. if ($columnDiff->hasChanged('type')) {
  704. return false;
  705. }
  707. return count(array_diff($columnDiff->changedProperties, ['length', 'fixed'])) === 0;
  708. }
  710. /**
  711. * {@inheritdoc}
  712. */
  713. protected function getRenameIndexSQL($oldIndexName, Index $index, $tableName)
  714. {
  715. if (strpos($tableName, '.') !== false) {
  716. [$schema] = explode('.', $tableName);
  717. $oldIndexName = $schema . '.' . $oldIndexName;
  718. }
  720. return ['ALTER INDEX ' . $oldIndexName . ' RENAME TO ' . $index->getQuotedName($this)];
  721. }
  723. /**
  724. * {@inheritdoc}
  725. */
  726. public function getCommentOnColumnSQL($tableName, $columnName, $comment)
  727. {
  728. $tableName = new Identifier($tableName);
  729. $columnName = new Identifier($columnName);
  730. $comment = $comment === null ? 'NULL' : $this->quoteStringLiteral($comment);
  732. return sprintf(
  733. 'COMMENT ON COLUMN %s.%s IS %s',
  734. $tableName->getQuotedName($this),
  735. $columnName->getQuotedName($this),
  736. $comment
  737. );
  738. }
  740. /**
  741. * {@inheritDoc}
  742. */
  743. public function getCreateSequenceSQL(Sequence $sequence)
  744. {
  745. return 'CREATE SEQUENCE ' . $sequence->getQuotedName($this) .
  746. ' INCREMENT BY ' . $sequence->getAllocationSize() .
  747. ' MINVALUE ' . $sequence->getInitialValue() .
  748. ' START ' . $sequence->getInitialValue() .
  749. $this->getSequenceCacheSQL($sequence);
  750. }
  752. /**
  753. * {@inheritDoc}
  754. */
  755. public function getAlterSequenceSQL(Sequence $sequence)
  756. {
  757. return 'ALTER SEQUENCE ' . $sequence->getQuotedName($this) .
  758. ' INCREMENT BY ' . $sequence->getAllocationSize() .
  759. $this->getSequenceCacheSQL($sequence);
  760. }
  762. /**
  763. * Cache definition for sequences
  764. *
  765. * @return string
  766. */
  767. private function getSequenceCacheSQL(Sequence $sequence)
  768. {
  769. if ($sequence->getCache() > 1) {
  770. return ' CACHE ' . $sequence->getCache();
  771. }
  773. return '';
  774. }
  776. /**
  777. * {@inheritDoc}
  778. */
  779. public function getDropSequenceSQL($sequence)
  780. {
  781. if ($sequence instanceof Sequence) {
  782. $sequence = $sequence->getQuotedName($this);
  783. }
  785. return 'DROP SEQUENCE ' . $sequence . ' CASCADE';
  786. }
  788. /**
  789. * {@inheritDoc}
  790. */
  791. public function getCreateSchemaSQL($schemaName)
  792. {
  793. return 'CREATE SCHEMA ' . $schemaName;
  794. }
  796. /**
  797. * {@inheritDoc}
  798. */
  799. public function getDropForeignKeySQL($foreignKey, $table)
  800. {
  801. return $this->getDropConstraintSQL($foreignKey, $table);
  802. }
  804. /**
  805. * {@inheritDoc}
  806. */
  807. protected function _getCreateTableSQL($name, array $columns, array $options = [])
  808. {
  809. $queryFields = $this->getColumnDeclarationListSQL($columns);
  811. if (isset($options['primary']) && ! empty($options['primary'])) {
  812. $keyColumns = array_unique(array_values($options['primary']));
  813. $queryFields .= ', PRIMARY KEY(' . implode(', ', $keyColumns) . ')';
  814. }
  816. $query = 'CREATE TABLE ' . $name . ' (' . $queryFields . ')';
  818. $sql = [$query];
  820. if (isset($options['indexes']) && ! empty($options['indexes'])) {
  821. foreach ($options['indexes'] as $index) {
  822. $sql[] = $this->getCreateIndexSQL($index, $name);
  823. }
  824. }
  826. if (isset($options['foreignKeys'])) {
  827. foreach ((array) $options['foreignKeys'] as $definition) {
  828. $sql[] = $this->getCreateForeignKeySQL($definition, $name);
  829. }
  830. }
  832. return $sql;
  833. }
  835. /**
  836. * Converts a single boolean value.
  837. *
  838. * First converts the value to its native PHP boolean type
  839. * and passes it to the given callback function to be reconverted
  840. * into any custom representation.
  841. *
  842. * @param mixed $value The value to convert.
  843. * @param callable $callback The callback function to use for converting the real boolean value.
  844. *
  845. * @return mixed
  846. *
  847. * @throws UnexpectedValueException
  848. */
  849. private function convertSingleBooleanValue($value, $callback)
  850. {
  851. if ($value === null) {
  852. return $callback(null);
  853. }
  855. if (is_bool($value) || is_numeric($value)) {
  856. return $callback((bool) $value);
  857. }
  859. if (! is_string($value)) {
  860. return $callback(true);
  861. }
  863. /**
  864. * Better safe than sorry: http://php.net/in_array#106319
  865. */
  866. if (in_array(strtolower(trim($value)), $this->booleanLiterals['false'], true)) {
  867. return $callback(false);
  868. }
  870. if (in_array(strtolower(trim($value)), $this->booleanLiterals['true'], true)) {
  871. return $callback(true);
  872. }
  874. throw new UnexpectedValueException("Unrecognized boolean literal '${value}'");
  875. }
  877. /**
  878. * Converts one or multiple boolean values.
  879. *
  880. * First converts the value(s) to their native PHP boolean type
  881. * and passes them to the given callback function to be reconverted
  882. * into any custom representation.
  883. *
  884. * @param mixed $item The value(s) to convert.
  885. * @param callable $callback The callback function to use for converting the real boolean value(s).
  886. *
  887. * @return mixed
  888. */
  889. private function doConvertBooleans($item, $callback)
  890. {
  891. if (is_array($item)) {
  892. foreach ($item as $key => $value) {
  893. $item[$key] = $this->convertSingleBooleanValue($value, $callback);
  894. }
  896. return $item;
  897. }
  899. return $this->convertSingleBooleanValue($item, $callback);
  900. }
  902. /**
  903. * {@inheritDoc}
  904. *
  905. * Postgres wants boolean values converted to the strings 'true'/'false'.
  906. */
  907. public function convertBooleans($item)
  908. {
  909. if (! $this->useBooleanTrueFalseStrings) {
  910. return parent::convertBooleans($item);
  911. }
  913. return $this->doConvertBooleans(
  914. $item,
  915. /**
  916. * @param mixed $value
  917. */
  918. static function ($value) {
  919. if ($value === null) {
  920. return 'NULL';
  921. }
  923. return $value === true ? 'true' : 'false';
  924. }
  925. );
  926. }
  928. /**
  929. * {@inheritDoc}
  930. */
  931. public function convertBooleansToDatabaseValue($item)
  932. {
  933. if (! $this->useBooleanTrueFalseStrings) {
  934. return parent::convertBooleansToDatabaseValue($item);
  935. }
  937. return $this->doConvertBooleans(
  938. $item,
  939. /**
  940. * @param mixed $value
  941. */
  942. static function ($value) {
  943. return $value === null ? null : (int) $value;
  944. }
  945. );
  946. }
  948. /**
  949. * {@inheritDoc}
  950. */
  951. public function convertFromBoolean($item)
  952. {
  953. if ($item !== null && in_array(strtolower($item), $this->booleanLiterals['false'], true)) {
  954. return false;
  955. }
  957. return parent::convertFromBoolean($item);
  958. }
  960. /**
  961. * {@inheritDoc}
  962. */
  963. public function getSequenceNextValSQL($sequence)
  964. {
  965. return "SELECT NEXTVAL('" . $sequence . "')";
  966. }
  968. /**
  969. * {@inheritDoc}
  970. */
  971. public function getSetTransactionIsolationSQL($level)
  972. {
  973. return 'SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL '
  974. . $this->_getTransactionIsolationLevelSQL($level);
  975. }
  977. /**
  978. * {@inheritDoc}
  979. */
  980. public function getBooleanTypeDeclarationSQL(array $column)
  981. {
  982. return 'BOOLEAN';
  983. }
  985. /**
  986. * {@inheritDoc}
  987. */
  988. public function getIntegerTypeDeclarationSQL(array $column)
  989. {
  990. if (! empty($column['autoincrement'])) {
  991. return 'SERIAL';
  992. }
  994. return 'INT';
  995. }
  997. /**
  998. * {@inheritDoc}
  999. */
  1000. public function getBigIntTypeDeclarationSQL(array $column)
  1001. {
  1002. if (! empty($column['autoincrement'])) {
  1003. return 'BIGSERIAL';
  1004. }
  1006. return 'BIGINT';
  1007. }
  1009. /**
  1010. * {@inheritDoc}
  1011. */
  1012. public function getSmallIntTypeDeclarationSQL(array $column)
  1013. {
  1014. return 'SMALLINT';
  1015. }
  1017. /**
  1018. * {@inheritDoc}
  1019. */
  1020. public function getGuidTypeDeclarationSQL(array $column)
  1021. {
  1022. return 'UUID';
  1023. }
  1025. /**
  1026. * {@inheritDoc}
  1027. */
  1028. public function getDateTimeTypeDeclarationSQL(array $column)
  1029. {
  1030. return 'TIMESTAMP(0) WITHOUT TIME ZONE';
  1031. }
  1033. /**
  1034. * {@inheritDoc}
  1035. */
  1036. public function getDateTimeTzTypeDeclarationSQL(array $column)
  1037. {
  1038. return 'TIMESTAMP(0) WITH TIME ZONE';
  1039. }
  1041. /**
  1042. * {@inheritDoc}
  1043. */
  1044. public function getDateTypeDeclarationSQL(array $column)
  1045. {
  1046. return 'DATE';
  1047. }
  1049. /**
  1050. * {@inheritDoc}
  1051. */
  1052. public function getTimeTypeDeclarationSQL(array $column)
  1053. {
  1054. return 'TIME(0) WITHOUT TIME ZONE';
  1055. }
  1057. /**
  1058. * {@inheritDoc}
  1059. *
  1060. * @deprecated Use application-generated UUIDs instead
  1061. */
  1062. public function getGuidExpression()
  1063. {
  1064. return 'UUID_GENERATE_V4()';
  1065. }
  1067. /**
  1068. * {@inheritDoc}
  1069. */
  1070. protected function _getCommonIntegerTypeDeclarationSQL(array $column)
  1071. {
  1072. return '';
  1073. }
  1075. /**
  1076. * {@inheritDoc}
  1077. */
  1078. protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed)
  1079. {
  1080. return $fixed ? ($length ? 'CHAR(' . $length . ')' : 'CHAR(255)')
  1081. : ($length ? 'VARCHAR(' . $length . ')' : 'VARCHAR(255)');
  1082. }
  1084. /**
  1085. * {@inheritdoc}
  1086. */
  1087. protected function getBinaryTypeDeclarationSQLSnippet($length, $fixed)
  1088. {
  1089. return 'BYTEA';
  1090. }
  1092. /**
  1093. * {@inheritDoc}
  1094. */
  1095. public function getClobTypeDeclarationSQL(array $column)
  1096. {
  1097. return 'TEXT';
  1098. }
  1100. /**
  1101. * {@inheritDoc}
  1102. */
  1103. public function getName()
  1104. {
  1105. return 'postgresql';
  1106. }
  1108. /**
  1109. * {@inheritDoc}
  1110. *
  1111. * PostgreSQL returns all column names in SQL result sets in lowercase.
  1112. *
  1113. * @deprecated
  1114. */
  1115. public function getSQLResultCasing($column)
  1116. {
  1117. return strtolower($column);
  1118. }
  1120. /**
  1121. * {@inheritDoc}
  1122. */
  1123. public function getDateTimeTzFormatString()
  1124. {
  1125. return 'Y-m-d H:i:sO';
  1126. }
  1128. /**
  1129. * {@inheritDoc}
  1130. */
  1131. public function getEmptyIdentityInsertSQL($quotedTableName, $quotedIdentifierColumnName)
  1132. {
  1133. return 'INSERT INTO ' . $quotedTableName . ' (' . $quotedIdentifierColumnName . ') VALUES (DEFAULT)';
  1134. }
  1136. /**
  1137. * {@inheritDoc}
  1138. */
  1139. public function getTruncateTableSQL($tableName, $cascade = false)
  1140. {
  1141. $tableIdentifier = new Identifier($tableName);
  1142. $sql = 'TRUNCATE ' . $tableIdentifier->getQuotedName($this);
  1144. if ($cascade) {
  1145. $sql .= ' CASCADE';
  1146. }
  1148. return $sql;
  1149. }
  1151. /**
  1152. * {@inheritDoc}
  1153. */
  1154. public function getReadLockSQL()
  1155. {
  1156. return 'FOR SHARE';
  1157. }
  1159. /**
  1160. * {@inheritDoc}
  1161. */
  1162. protected function initializeDoctrineTypeMappings()
  1163. {
  1164. $this->doctrineTypeMapping = [
  1165. 'smallint' => 'smallint',
  1166. 'int2' => 'smallint',
  1167. 'serial' => 'integer',
  1168. 'serial4' => 'integer',
  1169. 'int' => 'integer',
  1170. 'int4' => 'integer',
  1171. 'integer' => 'integer',
  1172. 'bigserial' => 'bigint',
  1173. 'serial8' => 'bigint',
  1174. 'bigint' => 'bigint',
  1175. 'int8' => 'bigint',
  1176. 'bool' => 'boolean',
  1177. 'boolean' => 'boolean',
  1178. 'text' => 'text',
  1179. 'tsvector' => 'text',
  1180. 'varchar' => 'string',
  1181. 'interval' => 'string',
  1182. '_varchar' => 'string',
  1183. 'char' => 'string',
  1184. 'bpchar' => 'string',
  1185. 'inet' => 'string',
  1186. 'date' => 'date',
  1187. 'datetime' => 'datetime',
  1188. 'timestamp' => 'datetime',
  1189. 'timestamptz' => 'datetimetz',
  1190. 'time' => 'time',
  1191. 'timetz' => 'time',
  1192. 'float' => 'float',
  1193. 'float4' => 'float',
  1194. 'float8' => 'float',
  1195. 'double' => 'float',
  1196. 'double precision' => 'float',
  1197. 'real' => 'float',
  1198. 'decimal' => 'decimal',
  1199. 'money' => 'decimal',
  1200. 'numeric' => 'decimal',
  1201. 'year' => 'date',
  1202. 'uuid' => 'guid',
  1203. 'bytea' => 'blob',
  1204. ];
  1205. }
  1207. /**
  1208. * {@inheritDoc}
  1209. */
  1210. public function getVarcharMaxLength()
  1211. {
  1212. return 65535;
  1213. }
  1215. /**
  1216. * {@inheritdoc}
  1217. */
  1218. public function getBinaryMaxLength()
  1219. {
  1220. return 0;
  1221. }
  1223. /**
  1224. * {@inheritdoc}
  1225. */
  1226. public function getBinaryDefaultLength()
  1227. {
  1228. return 0;
  1229. }
  1231. /**
  1232. * {@inheritDoc}
  1233. */
  1234. protected function getReservedKeywordsClass()
  1235. {
  1236. return Keywords\PostgreSQLKeywords::class;
  1237. }
  1239. /**
  1240. * {@inheritDoc}
  1241. */
  1242. public function getBlobTypeDeclarationSQL(array $column)
  1243. {
  1244. return 'BYTEA';
  1245. }
  1247. /**
  1248. * {@inheritdoc}
  1249. */
  1250. public function getDefaultValueDeclarationSQL($column)
  1251. {
  1252. if ($this->isSerialColumn($column)) {
  1253. return '';
  1254. }
  1256. return parent::getDefaultValueDeclarationSQL($column);
  1257. }
  1259. /**
  1260. * @param mixed[] $column
  1261. */
  1262. private function isSerialColumn(array $column): bool
  1263. {
  1264. return isset($column['type'], $column['autoincrement'])
  1265. && $column['autoincrement'] === true
  1266. && $this->isNumericType($column['type']);
  1267. }
  1269. /**
  1270. * Check whether the type of a column is changed in a way that invalidates the default value for the column
  1271. */
  1272. private function typeChangeBreaksDefaultValue(ColumnDiff $columnDiff): bool
  1273. {
  1274. if (! $columnDiff->fromColumn) {
  1275. return $columnDiff->hasChanged('type');
  1276. }
  1278. $oldTypeIsNumeric = $this->isNumericType($columnDiff->fromColumn->getType());
  1279. $newTypeIsNumeric = $this->isNumericType($columnDiff->column->getType());
  1281. // default should not be changed when switching between numeric types and the default comes from a sequence
  1282. return $columnDiff->hasChanged('type')
  1283. && ! ($oldTypeIsNumeric && $newTypeIsNumeric && $columnDiff->column->getAutoincrement());
  1284. }
  1286. private function isNumericType(Type $type): bool
  1287. {
  1288. return $type instanceof IntegerType || $type instanceof BigIntType;
  1289. }
  1291. private function getOldColumnComment(ColumnDiff $columnDiff): ?string
  1292. {
  1293. return $columnDiff->fromColumn ? $this->getColumnComment($columnDiff->fromColumn) : null;
  1294. }
  1296. public function getListTableMetadataSQL(string $table, ?string $schema = null): string
  1297. {
  1298. if ($schema !== null) {
  1299. $table = $schema . '.' . $table;
  1300. }
  1302. return sprintf(
  1303. <<<'SQL'
  1304. SELECT obj_description(%s::regclass) AS table_comment;
  1305. SQL
  1306. ,
  1307. $this->quoteStringLiteral($table)
  1308. );
  1309. }
  1310. }