1<?php 2 3namespace Doctrine\DBAL\Schema; 4 5use Doctrine\DBAL\Exception\DriverException; 6use Doctrine\DBAL\FetchMode; 7use Doctrine\DBAL\Platforms\PostgreSqlPlatform; 8use Doctrine\DBAL\Types\Type; 9use Doctrine\DBAL\Types\Types; 10 11use function array_change_key_case; 12use function array_filter; 13use function array_keys; 14use function array_map; 15use function array_shift; 16use function assert; 17use function explode; 18use function implode; 19use function in_array; 20use function preg_match; 21use function preg_replace; 22use function sprintf; 23use function str_replace; 24use function strlen; 25use function strpos; 26use function strtolower; 27use function trim; 28 29use const CASE_LOWER; 30 31/** 32 * PostgreSQL Schema Manager. 33 */ 34class PostgreSqlSchemaManager extends AbstractSchemaManager 35{ 36 /** @var string[]|null */ 37 private $existingSchemaPaths; 38 39 /** 40 * Gets all the existing schema names. 41 * 42 * @return string[] 43 */ 44 public function getSchemaNames() 45 { 46 $statement = $this->_conn->executeQuery( 47 "SELECT nspname FROM pg_namespace WHERE nspname !~ '^pg_.*' AND nspname != 'information_schema'" 48 ); 49 50 return $statement->fetchAll(FetchMode::COLUMN); 51 } 52 53 /** 54 * Returns an array of schema search paths. 55 * 56 * This is a PostgreSQL only function. 57 * 58 * @return string[] 59 */ 60 public function getSchemaSearchPaths() 61 { 62 $params = $this->_conn->getParams(); 63 64 $searchPaths = $this->_conn->fetchColumn('SHOW search_path'); 65 assert($searchPaths !== false); 66 67 $schema = explode(',', $searchPaths); 68 69 if (isset($params['user'])) { 70 $schema = str_replace('"$user"', $params['user'], $schema); 71 } 72 73 return array_map('trim', $schema); 74 } 75 76 /** 77 * Gets names of all existing schemas in the current users search path. 78 * 79 * This is a PostgreSQL only function. 80 * 81 * @return string[] 82 */ 83 public function getExistingSchemaSearchPaths() 84 { 85 if ($this->existingSchemaPaths === null) { 86 $this->determineExistingSchemaSearchPaths(); 87 } 88 89 return $this->existingSchemaPaths; 90 } 91 92 /** 93 * Sets or resets the order of the existing schemas in the current search path of the user. 94 * 95 * This is a PostgreSQL only function. 96 * 97 * @return void 98 */ 99 public function determineExistingSchemaSearchPaths() 100 { 101 $names = $this->getSchemaNames(); 102 $paths = $this->getSchemaSearchPaths(); 103 104 $this->existingSchemaPaths = array_filter($paths, static function ($v) use ($names) { 105 return in_array($v, $names); 106 }); 107 } 108 109 /** 110 * {@inheritdoc} 111 */ 112 public function dropDatabase($database) 113 { 114 try { 115 parent::dropDatabase($database); 116 } catch (DriverException $exception) { 117 // If we have a SQLSTATE 55006, the drop database operation failed 118 // because of active connections on the database. 119 // To force dropping the database, we first have to close all active connections 120 // on that database and issue the drop database operation again. 121 if ($exception->getSQLState() !== '55006') { 122 throw $exception; 123 } 124 125 assert($this->_platform instanceof PostgreSqlPlatform); 126 127 $this->_execSql( 128 [ 129 $this->_platform->getDisallowDatabaseConnectionsSQL($database), 130 $this->_platform->getCloseActiveDatabaseConnectionsSQL($database), 131 ] 132 ); 133 134 parent::dropDatabase($database); 135 } 136 } 137 138 /** 139 * {@inheritdoc} 140 */ 141 protected function _getPortableTableForeignKeyDefinition($tableForeignKey) 142 { 143 $onUpdate = null; 144 $onDelete = null; 145 $localColumns = []; 146 $foreignColumns = []; 147 $foreignTable = null; 148 149 if (preg_match('(ON UPDATE ([a-zA-Z0-9]+( (NULL|ACTION|DEFAULT))?))', $tableForeignKey['condef'], $match)) { 150 $onUpdate = $match[1]; 151 } 152 153 if (preg_match('(ON DELETE ([a-zA-Z0-9]+( (NULL|ACTION|DEFAULT))?))', $tableForeignKey['condef'], $match)) { 154 $onDelete = $match[1]; 155 } 156 157 $result = preg_match('/FOREIGN KEY \((.+)\) REFERENCES (.+)\((.+)\)/', $tableForeignKey['condef'], $values); 158 assert($result === 1); 159 160 // PostgreSQL returns identifiers that are keywords with quotes, we need them later, don't get 161 // the idea to trim them here. 162 $localColumns = array_map('trim', explode(',', $values[1])); 163 $foreignColumns = array_map('trim', explode(',', $values[3])); 164 $foreignTable = $values[2]; 165 166 return new ForeignKeyConstraint( 167 $localColumns, 168 $foreignTable, 169 $foreignColumns, 170 $tableForeignKey['conname'], 171 ['onUpdate' => $onUpdate, 'onDelete' => $onDelete] 172 ); 173 } 174 175 /** 176 * {@inheritdoc} 177 */ 178 protected function _getPortableTriggerDefinition($trigger) 179 { 180 return $trigger['trigger_name']; 181 } 182 183 /** 184 * {@inheritdoc} 185 */ 186 protected function _getPortableViewDefinition($view) 187 { 188 return new View($view['schemaname'] . '.' . $view['viewname'], $view['definition']); 189 } 190 191 /** 192 * {@inheritdoc} 193 */ 194 protected function _getPortableUserDefinition($user) 195 { 196 return [ 197 'user' => $user['usename'], 198 'password' => $user['passwd'], 199 ]; 200 } 201 202 /** 203 * {@inheritdoc} 204 */ 205 protected function _getPortableTableDefinition($table) 206 { 207 $schemas = $this->getExistingSchemaSearchPaths(); 208 $firstSchema = array_shift($schemas); 209 210 if ($table['schema_name'] === $firstSchema) { 211 return $table['table_name']; 212 } 213 214 return $table['schema_name'] . '.' . $table['table_name']; 215 } 216 217 /** 218 * {@inheritdoc} 219 * 220 * @link http://ezcomponents.org/docs/api/trunk/DatabaseSchema/ezcDbSchemaPgsqlReader.html 221 */ 222 protected function _getPortableTableIndexesList($tableIndexes, $tableName = null) 223 { 224 $buffer = []; 225 foreach ($tableIndexes as $row) { 226 $colNumbers = array_map('intval', explode(' ', $row['indkey'])); 227 $columnNameSql = sprintf( 228 'SELECT attnum, attname FROM pg_attribute WHERE attrelid=%d AND attnum IN (%s) ORDER BY attnum ASC', 229 $row['indrelid'], 230 implode(' ,', $colNumbers) 231 ); 232 233 $indexColumns = $this->_conn->fetchAllAssociative($columnNameSql); 234 235 // required for getting the order of the columns right. 236 foreach ($colNumbers as $colNum) { 237 foreach ($indexColumns as $colRow) { 238 if ($colNum !== $colRow['attnum']) { 239 continue; 240 } 241 242 $buffer[] = [ 243 'key_name' => $row['relname'], 244 'column_name' => trim($colRow['attname']), 245 'non_unique' => ! $row['indisunique'], 246 'primary' => $row['indisprimary'], 247 'where' => $row['where'], 248 ]; 249 } 250 } 251 } 252 253 return parent::_getPortableTableIndexesList($buffer, $tableName); 254 } 255 256 /** 257 * {@inheritdoc} 258 */ 259 protected function _getPortableDatabaseDefinition($database) 260 { 261 return $database['datname']; 262 } 263 264 /** 265 * {@inheritdoc} 266 */ 267 protected function _getPortableSequencesList($sequences) 268 { 269 $sequenceDefinitions = []; 270 271 foreach ($sequences as $sequence) { 272 if ($sequence['schemaname'] !== 'public') { 273 $sequenceName = $sequence['schemaname'] . '.' . $sequence['relname']; 274 } else { 275 $sequenceName = $sequence['relname']; 276 } 277 278 $sequenceDefinitions[$sequenceName] = $sequence; 279 } 280 281 $list = []; 282 283 foreach ($this->filterAssetNames(array_keys($sequenceDefinitions)) as $sequenceName) { 284 $list[] = $this->_getPortableSequenceDefinition($sequenceDefinitions[$sequenceName]); 285 } 286 287 return $list; 288 } 289 290 /** 291 * {@inheritdoc} 292 */ 293 protected function getPortableNamespaceDefinition(array $namespace) 294 { 295 return $namespace['nspname']; 296 } 297 298 /** 299 * {@inheritdoc} 300 */ 301 protected function _getPortableSequenceDefinition($sequence) 302 { 303 if ($sequence['schemaname'] !== 'public') { 304 $sequenceName = $sequence['schemaname'] . '.' . $sequence['relname']; 305 } else { 306 $sequenceName = $sequence['relname']; 307 } 308 309 if (! isset($sequence['increment_by'], $sequence['min_value'])) { 310 /** @var string[] $data */ 311 $data = $this->_conn->fetchAssoc( 312 'SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName) 313 ); 314 315 $sequence += $data; 316 } 317 318 return new Sequence($sequenceName, (int) $sequence['increment_by'], (int) $sequence['min_value']); 319 } 320 321 /** 322 * {@inheritdoc} 323 */ 324 protected function _getPortableTableColumnDefinition($tableColumn) 325 { 326 $tableColumn = array_change_key_case($tableColumn, CASE_LOWER); 327 328 if (strtolower($tableColumn['type']) === 'varchar' || strtolower($tableColumn['type']) === 'bpchar') { 329 // get length from varchar definition 330 $length = preg_replace('~.*\(([0-9]*)\).*~', '$1', $tableColumn['complete_type']); 331 $tableColumn['length'] = $length; 332 } 333 334 $matches = []; 335 336 $autoincrement = false; 337 if (preg_match("/^nextval\('(.*)'(::.*)?\)$/", $tableColumn['default'], $matches)) { 338 $tableColumn['sequence'] = $matches[1]; 339 $tableColumn['default'] = null; 340 $autoincrement = true; 341 } 342 343 if (preg_match("/^['(](.*)[')]::/", $tableColumn['default'], $matches)) { 344 $tableColumn['default'] = $matches[1]; 345 } elseif (preg_match('/^NULL::/', $tableColumn['default'])) { 346 $tableColumn['default'] = null; 347 } 348 349 $length = $tableColumn['length'] ?? null; 350 if ($length === '-1' && isset($tableColumn['atttypmod'])) { 351 $length = $tableColumn['atttypmod'] - 4; 352 } 353 354 if ((int) $length <= 0) { 355 $length = null; 356 } 357 358 $fixed = null; 359 360 if (! isset($tableColumn['name'])) { 361 $tableColumn['name'] = ''; 362 } 363 364 $precision = null; 365 $scale = null; 366 $jsonb = null; 367 368 $dbType = strtolower($tableColumn['type']); 369 if ( 370 strlen($tableColumn['domain_type']) 371 && ! $this->_platform->hasDoctrineTypeMappingFor($tableColumn['type']) 372 ) { 373 $dbType = strtolower($tableColumn['domain_type']); 374 $tableColumn['complete_type'] = $tableColumn['domain_complete_type']; 375 } 376 377 $type = $this->_platform->getDoctrineTypeMapping($dbType); 378 $type = $this->extractDoctrineTypeFromComment($tableColumn['comment'], $type); 379 $tableColumn['comment'] = $this->removeDoctrineTypeFromComment($tableColumn['comment'], $type); 380 381 switch ($dbType) { 382 case 'smallint': 383 case 'int2': 384 $tableColumn['default'] = $this->fixVersion94NegativeNumericDefaultValue($tableColumn['default']); 385 $length = null; 386 break; 387 388 case 'int': 389 case 'int4': 390 case 'integer': 391 $tableColumn['default'] = $this->fixVersion94NegativeNumericDefaultValue($tableColumn['default']); 392 $length = null; 393 break; 394 395 case 'bigint': 396 case 'int8': 397 $tableColumn['default'] = $this->fixVersion94NegativeNumericDefaultValue($tableColumn['default']); 398 $length = null; 399 break; 400 401 case 'bool': 402 case 'boolean': 403 if ($tableColumn['default'] === 'true') { 404 $tableColumn['default'] = true; 405 } 406 407 if ($tableColumn['default'] === 'false') { 408 $tableColumn['default'] = false; 409 } 410 411 $length = null; 412 break; 413 414 case 'text': 415 case '_varchar': 416 case 'varchar': 417 $tableColumn['default'] = $this->parseDefaultExpression($tableColumn['default']); 418 $fixed = false; 419 break; 420 case 'interval': 421 $fixed = false; 422 break; 423 424 case 'char': 425 case 'bpchar': 426 $fixed = true; 427 break; 428 429 case 'float': 430 case 'float4': 431 case 'float8': 432 case 'double': 433 case 'double precision': 434 case 'real': 435 case 'decimal': 436 case 'money': 437 case 'numeric': 438 $tableColumn['default'] = $this->fixVersion94NegativeNumericDefaultValue($tableColumn['default']); 439 440 if (preg_match('([A-Za-z]+\(([0-9]+)\,([0-9]+)\))', $tableColumn['complete_type'], $match)) { 441 $precision = $match[1]; 442 $scale = $match[2]; 443 $length = null; 444 } 445 446 break; 447 448 case 'year': 449 $length = null; 450 break; 451 452 // PostgreSQL 9.4+ only 453 case 'jsonb': 454 $jsonb = true; 455 break; 456 } 457 458 if ($tableColumn['default'] && preg_match("('([^']+)'::)", $tableColumn['default'], $match)) { 459 $tableColumn['default'] = $match[1]; 460 } 461 462 $options = [ 463 'length' => $length, 464 'notnull' => (bool) $tableColumn['isnotnull'], 465 'default' => $tableColumn['default'], 466 'precision' => $precision, 467 'scale' => $scale, 468 'fixed' => $fixed, 469 'unsigned' => false, 470 'autoincrement' => $autoincrement, 471 'comment' => isset($tableColumn['comment']) && $tableColumn['comment'] !== '' 472 ? $tableColumn['comment'] 473 : null, 474 ]; 475 476 $column = new Column($tableColumn['field'], Type::getType($type), $options); 477 478 if (isset($tableColumn['collation']) && ! empty($tableColumn['collation'])) { 479 $column->setPlatformOption('collation', $tableColumn['collation']); 480 } 481 482 if (in_array($column->getType()->getName(), [Types::JSON_ARRAY, Types::JSON], true)) { 483 $column->setPlatformOption('jsonb', $jsonb); 484 } 485 486 return $column; 487 } 488 489 /** 490 * PostgreSQL 9.4 puts parentheses around negative numeric default values that need to be stripped eventually. 491 * 492 * @param mixed $defaultValue 493 * 494 * @return mixed 495 */ 496 private function fixVersion94NegativeNumericDefaultValue($defaultValue) 497 { 498 if (strpos($defaultValue, '(') === 0) { 499 return trim($defaultValue, '()'); 500 } 501 502 return $defaultValue; 503 } 504 505 /** 506 * Parses a default value expression as given by PostgreSQL 507 */ 508 private function parseDefaultExpression(?string $default): ?string 509 { 510 if ($default === null) { 511 return $default; 512 } 513 514 return str_replace("''", "'", $default); 515 } 516 517 /** 518 * {@inheritdoc} 519 */ 520 public function listTableDetails($name): Table 521 { 522 $table = parent::listTableDetails($name); 523 524 $platform = $this->_platform; 525 assert($platform instanceof PostgreSqlPlatform); 526 $sql = $platform->getListTableMetadataSQL($name); 527 528 $tableOptions = $this->_conn->fetchAssoc($sql); 529 530 if ($tableOptions !== false) { 531 $table->addOption('comment', $tableOptions['table_comment']); 532 } 533 534 return $table; 535 } 536} 537