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