1 | <?php |
||
2 | /* |
||
3 | * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS |
||
4 | * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT |
||
5 | * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR |
||
6 | * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT |
||
7 | * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, |
||
8 | * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT |
||
9 | * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, |
||
10 | * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY |
||
11 | * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT |
||
12 | * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE |
||
13 | * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. |
||
14 | * |
||
15 | * This software consists of voluntary contributions made by many individuals |
||
16 | * and is licensed under the MIT license. For more information, see |
||
17 | * <http://www.doctrine-project.org>. |
||
18 | */ |
||
19 | |||
20 | namespace Doctrine\DBAL\Schema; |
||
21 | |||
22 | use Doctrine\DBAL\Exception\DriverException; |
||
23 | use Doctrine\DBAL\Types\Type; |
||
24 | |||
25 | /** |
||
26 | * PostgreSQL Schema Manager. |
||
27 | * |
||
28 | * @author Konsta Vesterinen <[email protected]> |
||
29 | * @author Lukas Smith <[email protected]> (PEAR MDB2 library) |
||
30 | * @author Benjamin Eberlei <[email protected]> |
||
31 | * @since 2.0 |
||
32 | */ |
||
33 | class PostgreSqlSchemaManager extends AbstractSchemaManager |
||
34 | { |
||
35 | /** |
||
36 | * @var array |
||
37 | */ |
||
38 | private $existingSchemaPaths; |
||
39 | |||
40 | /** |
||
41 | * Gets all the existing schema names. |
||
42 | * |
||
43 | * @return array |
||
44 | */ |
||
45 | public function getSchemaNames() |
||
46 | { |
||
47 | $rows = $this->_conn->fetchAll("SELECT nspname as schema_name FROM pg_namespace WHERE nspname !~ '^pg_.*' and nspname != 'information_schema'"); |
||
48 | |||
49 | return array_map(function ($v) { return $v['schema_name']; }, $rows); |
||
50 | } |
||
51 | |||
52 | /** |
||
53 | * Returns an array of schema search paths. |
||
54 | * |
||
55 | * This is a PostgreSQL only function. |
||
56 | * |
||
57 | * @return array |
||
58 | */ |
||
59 | public function getSchemaSearchPaths() |
||
60 | { |
||
61 | $params = $this->_conn->getParams(); |
||
62 | $schema = explode(",", $this->_conn->fetchColumn('SHOW search_path')); |
||
63 | |||
64 | if (isset($params['user'])) { |
||
65 | $schema = str_replace('"$user"', $params['user'], $schema); |
||
66 | } |
||
67 | |||
68 | return array_map('trim', $schema); |
||
69 | } |
||
70 | |||
71 | /** |
||
72 | * Gets names of all existing schemas in the current users search path. |
||
73 | * |
||
74 | * This is a PostgreSQL only function. |
||
75 | * |
||
76 | * @return array |
||
77 | */ |
||
78 | public function getExistingSchemaSearchPaths() |
||
79 | { |
||
80 | if ($this->existingSchemaPaths === null) { |
||
81 | $this->determineExistingSchemaSearchPaths(); |
||
82 | } |
||
83 | |||
84 | return $this->existingSchemaPaths; |
||
85 | } |
||
86 | |||
87 | /** |
||
88 | * Sets or resets the order of the existing schemas in the current search path of the user. |
||
89 | * |
||
90 | * This is a PostgreSQL only function. |
||
91 | * |
||
92 | * @return void |
||
93 | */ |
||
94 | public function determineExistingSchemaSearchPaths() |
||
95 | { |
||
96 | $names = $this->getSchemaNames(); |
||
97 | $paths = $this->getSchemaSearchPaths(); |
||
98 | |||
99 | $this->existingSchemaPaths = array_filter($paths, function ($v) use ($names) { |
||
100 | return in_array($v, $names); |
||
101 | }); |
||
102 | } |
||
103 | |||
104 | /** |
||
105 | * {@inheritdoc} |
||
106 | */ |
||
107 | public function dropDatabase($database) |
||
108 | { |
||
109 | try { |
||
110 | parent::dropDatabase($database); |
||
111 | } catch (DriverException $exception) { |
||
112 | // If we have a SQLSTATE 55006, the drop database operation failed |
||
113 | // because of active connections on the database. |
||
114 | // To force dropping the database, we first have to close all active connections |
||
115 | // on that database and issue the drop database operation again. |
||
116 | if ($exception->getSQLState() !== '55006') { |
||
117 | throw $exception; |
||
118 | } |
||
119 | |||
120 | $this->_execSql( |
||
121 | [ |
||
122 | $this->_platform->getDisallowDatabaseConnectionsSQL($database), |
||
123 | $this->_platform->getCloseActiveDatabaseConnectionsSQL($database), |
||
124 | ] |
||
125 | ); |
||
126 | |||
127 | parent::dropDatabase($database); |
||
128 | } |
||
129 | } |
||
130 | |||
131 | /** |
||
132 | * {@inheritdoc} |
||
133 | */ |
||
134 | protected function _getPortableTableForeignKeyDefinition($tableForeignKey) |
||
135 | { |
||
136 | $onUpdate = null; |
||
137 | $onDelete = null; |
||
138 | |||
139 | if (preg_match('(ON UPDATE ([a-zA-Z0-9]+( (NULL|ACTION|DEFAULT))?))', $tableForeignKey['condef'], $match)) { |
||
140 | $onUpdate = $match[1]; |
||
141 | } |
||
142 | if (preg_match('(ON DELETE ([a-zA-Z0-9]+( (NULL|ACTION|DEFAULT))?))', $tableForeignKey['condef'], $match)) { |
||
143 | $onDelete = $match[1]; |
||
144 | } |
||
145 | |||
146 | if (preg_match('/FOREIGN KEY \((.+)\) REFERENCES (.+)\((.+)\)/', $tableForeignKey['condef'], $values)) { |
||
147 | // PostgreSQL returns identifiers that are keywords with quotes, we need them later, don't get |
||
148 | // the idea to trim them here. |
||
149 | $localColumns = array_map('trim', explode(",", $values[1])); |
||
150 | $foreignColumns = array_map('trim', explode(",", $values[3])); |
||
151 | $foreignTable = $values[2]; |
||
152 | } |
||
153 | |||
154 | return new ForeignKeyConstraint( |
||
155 | $localColumns, $foreignTable, $foreignColumns, $tableForeignKey['conname'], |
||
0 ignored issues
–
show
Comprehensibility
Best Practice
introduced
by
![]() Comprehensibility
Best Practice
introduced
by
Comprehensibility
Best Practice
introduced
by
|
|||
156 | ['onUpdate' => $onUpdate, 'onDelete' => $onDelete] |
||
157 | ); |
||
158 | } |
||
159 | |||
160 | /** |
||
161 | * {@inheritdoc} |
||
162 | */ |
||
163 | protected function _getPortableTriggerDefinition($trigger) |
||
164 | { |
||
165 | return $trigger['trigger_name']; |
||
166 | } |
||
167 | |||
168 | /** |
||
169 | * {@inheritdoc} |
||
170 | */ |
||
171 | protected function _getPortableViewDefinition($view) |
||
172 | { |
||
173 | return new View($view['schemaname'].'.'.$view['viewname'], $view['definition']); |
||
174 | } |
||
175 | |||
176 | /** |
||
177 | * {@inheritdoc} |
||
178 | */ |
||
179 | protected function _getPortableUserDefinition($user) |
||
180 | { |
||
181 | return [ |
||
182 | 'user' => $user['usename'], |
||
183 | 'password' => $user['passwd'] |
||
184 | ]; |
||
185 | } |
||
186 | |||
187 | /** |
||
188 | * {@inheritdoc} |
||
189 | */ |
||
190 | protected function _getPortableTableDefinition($table) |
||
191 | { |
||
192 | $schemas = $this->getExistingSchemaSearchPaths(); |
||
193 | $firstSchema = array_shift($schemas); |
||
194 | |||
195 | if ($table['schema_name'] == $firstSchema) { |
||
196 | return $table['table_name']; |
||
197 | } else { |
||
198 | return $table['schema_name'] . "." . $table['table_name']; |
||
199 | } |
||
200 | } |
||
201 | |||
202 | /** |
||
203 | * {@inheritdoc} |
||
204 | * |
||
205 | * @license New BSD License |
||
206 | * @link http://ezcomponents.org/docs/api/trunk/DatabaseSchema/ezcDbSchemaPgsqlReader.html |
||
207 | */ |
||
208 | protected function _getPortableTableIndexesList($tableIndexes, $tableName=null) |
||
209 | { |
||
210 | $buffer = []; |
||
211 | foreach ($tableIndexes as $row) { |
||
212 | $colNumbers = explode(' ', $row['indkey']); |
||
213 | $colNumbersSql = 'IN (' . join(' ,', $colNumbers) . ' )'; |
||
214 | $columnNameSql = "SELECT attnum, attname FROM pg_attribute |
||
215 | WHERE attrelid={$row['indrelid']} AND attnum $colNumbersSql ORDER BY attnum ASC;"; |
||
216 | |||
217 | $stmt = $this->_conn->executeQuery($columnNameSql); |
||
218 | $indexColumns = $stmt->fetchAll(); |
||
219 | |||
220 | // required for getting the order of the columns right. |
||
221 | foreach ($colNumbers as $colNum) { |
||
222 | foreach ($indexColumns as $colRow) { |
||
223 | if ($colNum == $colRow['attnum']) { |
||
224 | $buffer[] = [ |
||
225 | 'key_name' => $row['relname'], |
||
226 | 'column_name' => trim($colRow['attname']), |
||
227 | 'non_unique' => !$row['indisunique'], |
||
228 | 'primary' => $row['indisprimary'], |
||
229 | 'where' => $row['where'], |
||
230 | ]; |
||
231 | } |
||
232 | } |
||
233 | } |
||
234 | } |
||
235 | |||
236 | return parent::_getPortableTableIndexesList($buffer, $tableName); |
||
237 | } |
||
238 | |||
239 | /** |
||
240 | * {@inheritdoc} |
||
241 | */ |
||
242 | protected function _getPortableDatabaseDefinition($database) |
||
243 | { |
||
244 | return $database['datname']; |
||
245 | } |
||
246 | |||
247 | /** |
||
248 | * {@inheritdoc} |
||
249 | */ |
||
250 | 1 | protected function _getPortableSequencesList($sequences) |
|
251 | { |
||
252 | 1 | $sequenceDefinitions = []; |
|
253 | |||
254 | 1 | foreach ($sequences as $sequence) { |
|
255 | 1 | View Code Duplication | if ($sequence['schemaname'] != 'public') { |
256 | 1 | $sequenceName = $sequence['schemaname'] . "." . $sequence['relname']; |
|
257 | } else { |
||
258 | $sequenceName = $sequence['relname']; |
||
259 | } |
||
260 | |||
261 | 1 | $sequenceDefinitions[$sequenceName] = $sequence; |
|
262 | } |
||
263 | |||
264 | 1 | $list = []; |
|
265 | |||
266 | 1 | foreach ($this->filterAssetNames(array_keys($sequenceDefinitions)) as $sequenceName) { |
|
267 | 1 | $list[] = $this->_getPortableSequenceDefinition($sequenceDefinitions[$sequenceName]); |
|
268 | } |
||
269 | |||
270 | 1 | return $list; |
|
271 | } |
||
272 | |||
273 | /** |
||
274 | * {@inheritdoc} |
||
275 | */ |
||
276 | protected function getPortableNamespaceDefinition(array $namespace) |
||
277 | { |
||
278 | return $namespace['nspname']; |
||
279 | } |
||
280 | |||
281 | /** |
||
282 | * {@inheritdoc} |
||
283 | */ |
||
284 | 1 | protected function _getPortableSequenceDefinition($sequence) |
|
285 | { |
||
286 | 1 | View Code Duplication | if ($sequence['schemaname'] != 'public') { |
287 | 1 | $sequenceName = $sequence['schemaname'] . "." . $sequence['relname']; |
|
288 | } else { |
||
289 | $sequenceName = $sequence['relname']; |
||
290 | } |
||
291 | |||
292 | 1 | $data = $this->_conn->fetchAll('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName)); |
|
293 | |||
294 | 1 | return new Sequence($sequenceName, $data[0]['increment_by'], $data[0]['min_value']); |
|
295 | } |
||
296 | |||
297 | /** |
||
298 | * {@inheritdoc} |
||
299 | */ |
||
300 | protected function _getPortableTableColumnDefinition($tableColumn) |
||
301 | { |
||
302 | $tableColumn = array_change_key_case($tableColumn, CASE_LOWER); |
||
303 | |||
304 | if (strtolower($tableColumn['type']) === 'varchar' || strtolower($tableColumn['type']) === 'bpchar') { |
||
305 | // get length from varchar definition |
||
306 | $length = preg_replace('~.*\(([0-9]*)\).*~', '$1', $tableColumn['complete_type']); |
||
307 | $tableColumn['length'] = $length; |
||
308 | } |
||
309 | |||
310 | $matches = []; |
||
311 | |||
312 | $autoincrement = false; |
||
313 | if (preg_match("/^nextval\('(.*)'(::.*)?\)$/", $tableColumn['default'], $matches)) { |
||
314 | $tableColumn['sequence'] = $matches[1]; |
||
315 | $tableColumn['default'] = null; |
||
316 | $autoincrement = true; |
||
317 | } |
||
318 | |||
319 | if (preg_match("/^['(](.*)[')]::.*$/", $tableColumn['default'], $matches)) { |
||
320 | $tableColumn['default'] = $matches[1]; |
||
321 | } |
||
322 | |||
323 | if (stripos($tableColumn['default'], 'NULL') === 0) { |
||
324 | $tableColumn['default'] = null; |
||
325 | } |
||
326 | |||
327 | $length = (isset($tableColumn['length'])) ? $tableColumn['length'] : null; |
||
328 | if ($length == '-1' && isset($tableColumn['atttypmod'])) { |
||
329 | $length = $tableColumn['atttypmod'] - 4; |
||
330 | } |
||
331 | if ((int) $length <= 0) { |
||
332 | $length = null; |
||
333 | } |
||
334 | $fixed = null; |
||
335 | |||
336 | if (!isset($tableColumn['name'])) { |
||
337 | $tableColumn['name'] = ''; |
||
338 | } |
||
339 | |||
340 | $precision = null; |
||
341 | $scale = null; |
||
342 | $jsonb = null; |
||
343 | |||
344 | $dbType = strtolower($tableColumn['type']); |
||
345 | if (strlen($tableColumn['domain_type']) && !$this->_platform->hasDoctrineTypeMappingFor($tableColumn['type'])) { |
||
346 | $dbType = strtolower($tableColumn['domain_type']); |
||
347 | $tableColumn['complete_type'] = $tableColumn['domain_complete_type']; |
||
348 | } |
||
349 | |||
350 | $type = $this->_platform->getDoctrineTypeMapping($dbType); |
||
351 | $type = $this->extractDoctrineTypeFromComment($tableColumn['comment'], $type); |
||
352 | $tableColumn['comment'] = $this->removeDoctrineTypeFromComment($tableColumn['comment'], $type); |
||
353 | |||
354 | switch ($dbType) { |
||
355 | case 'smallint': |
||
356 | View Code Duplication | case 'int2': |
|
357 | $tableColumn['default'] = $this->fixVersion94NegativeNumericDefaultValue($tableColumn['default']); |
||
358 | $length = null; |
||
359 | break; |
||
360 | case 'int': |
||
361 | case 'int4': |
||
362 | View Code Duplication | case 'integer': |
|
363 | $tableColumn['default'] = $this->fixVersion94NegativeNumericDefaultValue($tableColumn['default']); |
||
364 | $length = null; |
||
365 | break; |
||
366 | case 'bigint': |
||
367 | View Code Duplication | case 'int8': |
|
368 | $tableColumn['default'] = $this->fixVersion94NegativeNumericDefaultValue($tableColumn['default']); |
||
369 | $length = null; |
||
370 | break; |
||
371 | case 'bool': |
||
372 | case 'boolean': |
||
373 | if ($tableColumn['default'] === 'true') { |
||
374 | $tableColumn['default'] = true; |
||
375 | } |
||
376 | |||
377 | if ($tableColumn['default'] === 'false') { |
||
378 | $tableColumn['default'] = false; |
||
379 | } |
||
380 | |||
381 | $length = null; |
||
382 | break; |
||
383 | case 'text': |
||
384 | $fixed = false; |
||
385 | break; |
||
386 | case 'varchar': |
||
387 | case 'interval': |
||
388 | case '_varchar': |
||
389 | $fixed = false; |
||
390 | break; |
||
391 | case 'char': |
||
392 | case 'bpchar': |
||
393 | $fixed = true; |
||
394 | break; |
||
395 | case 'float': |
||
396 | case 'float4': |
||
397 | case 'float8': |
||
398 | case 'double': |
||
399 | case 'double precision': |
||
400 | case 'real': |
||
401 | case 'decimal': |
||
402 | case 'money': |
||
403 | case 'numeric': |
||
404 | $tableColumn['default'] = $this->fixVersion94NegativeNumericDefaultValue($tableColumn['default']); |
||
405 | |||
406 | View Code Duplication | if (preg_match('([A-Za-z]+\(([0-9]+)\,([0-9]+)\))', $tableColumn['complete_type'], $match)) { |
|
407 | $precision = $match[1]; |
||
408 | $scale = $match[2]; |
||
409 | $length = null; |
||
410 | } |
||
411 | break; |
||
412 | case 'year': |
||
413 | $length = null; |
||
414 | break; |
||
415 | |||
416 | // PostgreSQL 9.4+ only |
||
417 | case 'jsonb': |
||
418 | $jsonb = true; |
||
419 | break; |
||
420 | } |
||
421 | |||
422 | if ($tableColumn['default'] && preg_match("('([^']+)'::)", $tableColumn['default'], $match)) { |
||
423 | $tableColumn['default'] = $match[1]; |
||
424 | } |
||
425 | |||
426 | $options = [ |
||
427 | 'length' => $length, |
||
428 | 'notnull' => (bool) $tableColumn['isnotnull'], |
||
429 | 'default' => $tableColumn['default'], |
||
430 | 'primary' => (bool) ($tableColumn['pri'] == 't'), |
||
431 | 'precision' => $precision, |
||
432 | 'scale' => $scale, |
||
433 | 'fixed' => $fixed, |
||
434 | 'unsigned' => false, |
||
435 | 'autoincrement' => $autoincrement, |
||
436 | 'comment' => isset($tableColumn['comment']) && $tableColumn['comment'] !== '' |
||
437 | ? $tableColumn['comment'] |
||
438 | : null, |
||
439 | ]; |
||
440 | |||
441 | $column = new Column($tableColumn['field'], Type::getType($type), $options); |
||
442 | |||
443 | View Code Duplication | if (isset($tableColumn['collation']) && !empty($tableColumn['collation'])) { |
|
444 | $column->setPlatformOption('collation', $tableColumn['collation']); |
||
445 | } |
||
446 | |||
447 | if (in_array($column->getType()->getName(), [Type::JSON_ARRAY, Type::JSON], true)) { |
||
448 | $column->setPlatformOption('jsonb', $jsonb); |
||
449 | } |
||
450 | |||
451 | return $column; |
||
452 | } |
||
453 | |||
454 | /** |
||
455 | * PostgreSQL 9.4 puts parentheses around negative numeric default values that need to be stripped eventually. |
||
456 | * |
||
457 | * @param mixed $defaultValue |
||
458 | * |
||
459 | * @return mixed |
||
460 | */ |
||
461 | private function fixVersion94NegativeNumericDefaultValue($defaultValue) |
||
462 | { |
||
463 | if (strpos($defaultValue, '(') === 0) { |
||
464 | return trim($defaultValue, '()'); |
||
465 | } |
||
466 | |||
467 | return $defaultValue; |
||
468 | } |
||
469 | } |
||
470 |