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'], |
||
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'])) { |
|
0 ignored issues
–
show
|
|||
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 |
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.