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