cakephp /
phinx
Duplicate code is one of the most pungent code smells. A rule that is often used is to re-structure code once it is duplicated in three or more places.
Common duplication problems, and corresponding solutions are:
Complex classes like PostgresAdapter often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes. You can also have a look at the cohesion graph to spot any un-connected, or weakly-connected components.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use PostgresAdapter, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 38 | class PostgresAdapter extends PdoAdapter implements AdapterInterface |
||
| 39 | { |
||
| 40 | const INT_SMALL = 65535; |
||
| 41 | |||
| 42 | /** |
||
| 43 | * Columns with comments |
||
| 44 | * |
||
| 45 | * @var array |
||
| 46 | */ |
||
| 47 | protected $columnsWithComments = []; |
||
| 48 | |||
| 49 | /** |
||
| 50 | 68 | * {@inheritdoc} |
|
| 51 | */ |
||
| 52 | 68 | public function connect() |
|
| 53 | 68 | { |
|
| 54 | if ($this->connection === null) { |
||
| 55 | if (!class_exists('PDO') || !in_array('pgsql', \PDO::getAvailableDrivers(), true)) { |
||
| 56 | // @codeCoverageIgnoreStart |
||
| 57 | throw new \RuntimeException('You need to enable the PDO_Pgsql extension for Phinx to run properly.'); |
||
| 58 | // @codeCoverageIgnoreEnd |
||
| 59 | 68 | } |
|
| 60 | 68 | ||
| 61 | $db = null; |
||
| 62 | $options = $this->getOptions(); |
||
| 63 | 68 | ||
| 64 | 68 | // if port is specified use it, otherwise use the PostgreSQL default |
|
| 65 | 68 | View Code Duplication | if (isset($options['port'])) { |
|
|
|||
| 66 | 1 | $dsn = 'pgsql:host=' . $options['host'] . ';port=' . $options['port'] . ';dbname=' . $options['name']; |
|
| 67 | } else { |
||
| 68 | $dsn = 'pgsql:host=' . $options['host'] . ';dbname=' . $options['name']; |
||
| 69 | } |
||
| 70 | 68 | ||
| 71 | 68 | try { |
|
| 72 | 1 | $db = new \PDO($dsn, $options['user'], $options['pass'], [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]); |
|
| 73 | 1 | } catch (\PDOException $exception) { |
|
| 74 | 1 | throw new \InvalidArgumentException(sprintf( |
|
| 75 | 1 | 'There was a problem connecting to the database: %s', |
|
| 76 | $exception->getMessage() |
||
| 77 | ), $exception->getCode(), $exception); |
||
| 78 | 68 | } |
|
| 79 | 68 | ||
| 80 | 68 | try { |
|
| 81 | if (isset($options['schema'])) { |
||
| 82 | $db->exec('SET search_path TO ' . $options['schema']); |
||
| 83 | } |
||
| 84 | } catch (\PDOException $exception) { |
||
| 85 | 68 | throw new \InvalidArgumentException( |
|
| 86 | sprintf('Schema does not exists: %s', $options['schema']), |
||
| 87 | 68 | $exception->getCode(), |
|
| 88 | 68 | $exception |
|
| 89 | ); |
||
| 90 | } |
||
| 91 | |||
| 92 | $this->setConnection($db); |
||
| 93 | } |
||
| 94 | } |
||
| 95 | |||
| 96 | /** |
||
| 97 | * {@inheritdoc} |
||
| 98 | */ |
||
| 99 | public function disconnect() |
||
| 100 | { |
||
| 101 | $this->connection = null; |
||
| 102 | } |
||
| 103 | |||
| 104 | /** |
||
| 105 | * {@inheritdoc} |
||
| 106 | */ |
||
| 107 | public function hasTransactions() |
||
| 108 | { |
||
| 109 | return true; |
||
| 110 | } |
||
| 111 | |||
| 112 | /** |
||
| 113 | * {@inheritdoc} |
||
| 114 | */ |
||
| 115 | public function beginTransaction() |
||
| 116 | { |
||
| 117 | $this->execute('BEGIN'); |
||
| 118 | } |
||
| 119 | |||
| 120 | /** |
||
| 121 | * {@inheritdoc} |
||
| 122 | */ |
||
| 123 | public function commitTransaction() |
||
| 124 | { |
||
| 125 | $this->execute('COMMIT'); |
||
| 126 | } |
||
| 127 | |||
| 128 | 68 | /** |
|
| 129 | * {@inheritdoc} |
||
| 130 | 68 | */ |
|
| 131 | public function rollbackTransaction() |
||
| 132 | { |
||
| 133 | $this->execute('ROLLBACK'); |
||
| 134 | } |
||
| 135 | |||
| 136 | 68 | /** |
|
| 137 | * Quotes a schema name for use in a query. |
||
| 138 | 68 | * |
|
| 139 | * @param string $schemaName Schema Name |
||
| 140 | * @return string |
||
| 141 | */ |
||
| 142 | public function quoteSchemaName($schemaName) |
||
| 143 | { |
||
| 144 | 68 | return $this->quoteColumnName($schemaName); |
|
| 145 | } |
||
| 146 | 68 | ||
| 147 | /** |
||
| 148 | * {@inheritdoc} |
||
| 149 | */ |
||
| 150 | public function quoteTableName($tableName) |
||
| 151 | { |
||
| 152 | 68 | $parts = $this->getSchemaName($tableName); |
|
| 153 | |||
| 154 | 68 | return $this->quoteSchemaName($parts['schema']) . '.' . $this->quoteColumnName($parts['table']); |
|
| 155 | 68 | } |
|
| 156 | |||
| 157 | /** |
||
| 158 | * {@inheritdoc} |
||
| 159 | 68 | */ |
|
| 160 | 68 | public function quoteColumnName($columnName) |
|
| 161 | 68 | { |
|
| 162 | 68 | return '"' . $columnName . '"'; |
|
| 163 | 68 | } |
|
| 164 | |||
| 165 | 68 | /** |
|
| 166 | * {@inheritdoc} |
||
| 167 | */ |
||
| 168 | public function hasTable($tableName) |
||
| 169 | { |
||
| 170 | $parts = $this->getSchemaName($tableName); |
||
| 171 | 68 | $result = $this->getConnection()->query( |
|
| 172 | sprintf( |
||
| 173 | 68 | 'SELECT * |
|
| 174 | FROM information_schema.tables |
||
| 175 | WHERE table_schema = %s |
||
| 176 | 68 | AND table_name = %s', |
|
| 177 | 68 | $this->getConnection()->quote($parts['schema']), |
|
| 178 | 48 | $this->getConnection()->quote($parts['table']) |
|
| 179 | 48 | ) |
|
| 180 | 48 | ); |
|
| 181 | 48 | ||
| 182 | return $result->rowCount() === 1; |
||
| 183 | 48 | } |
|
| 184 | 48 | ||
| 185 | 68 | /** |
|
| 186 | * {@inheritdoc} |
||
| 187 | 2 | */ |
|
| 188 | 2 | public function createTable(Table $table, array $columns = [], array $indexes = []) |
|
| 189 | 2 | { |
|
| 190 | 2 | $options = $table->getOptions(); |
|
| 191 | $parts = $this->getSchemaName($table->getName()); |
||
| 192 | 2 | ||
| 193 | 2 | // Add the default primary key |
|
| 194 | 2 | View Code Duplication | if (!isset($options['id']) || (isset($options['id']) && $options['id'] === true)) { |
| 195 | $column = new Column(); |
||
| 196 | $column->setName('id') |
||
| 197 | 68 | ->setType('integer') |
|
| 198 | 68 | ->setIdentity(true); |
|
| 199 | |||
| 200 | 68 | array_unshift($columns, $column); |
|
| 201 | 68 | $options['primary_key'] = 'id'; |
|
| 202 | 68 | } elseif (isset($options['id']) && is_string($options['id'])) { |
|
| 203 | // Handle id => "field_name" to support AUTO_INCREMENT |
||
| 204 | $column = new Column(); |
||
| 205 | 68 | $column->setName($options['id']) |
|
| 206 | 6 | ->setType('integer') |
|
| 207 | 6 | ->setIdentity(true); |
|
| 208 | 68 | ||
| 209 | array_unshift($columns, $column); |
||
| 210 | $options['primary_key'] = $options['id']; |
||
| 211 | 68 | } |
|
| 212 | 68 | ||
| 213 | 68 | // TODO - process table options like collation etc |
|
| 214 | 68 | $sql = 'CREATE TABLE '; |
|
| 215 | 68 | $sql .= $this->quoteTableName($table->getName()) . ' ('; |
|
| 216 | 68 | ||
| 217 | $this->columnsWithComments = []; |
||
| 218 | View Code Duplication | foreach ($columns as $column) { |
|
| 219 | 1 | $sql .= $this->quoteColumnName($column->getName()) . ' ' . $this->getColumnSqlDefinition($column) . ', '; |
|
| 220 | 1 | ||
| 221 | 1 | // set column comments, if needed |
|
| 222 | 1 | if ($column->getComment()) { |
|
| 223 | 1 | $this->columnsWithComments[] = $column; |
|
| 224 | 1 | } |
|
| 225 | 1 | } |
|
| 226 | 1 | ||
| 227 | 1 | // set the primary key(s) |
|
| 228 | 1 | View Code Duplication | if (isset($options['primary_key'])) { |
| 229 | 68 | $sql = rtrim($sql); |
|
| 230 | 68 | $sql .= sprintf(' CONSTRAINT %s PRIMARY KEY (', $this->quoteColumnName($parts['table'] . '_pkey')); |
|
| 231 | 2 | if (is_string($options['primary_key'])) { // handle primary_key => 'id' |
|
| 232 | $sql .= $this->quoteColumnName($options['primary_key']); |
||
| 233 | } elseif (is_array($options['primary_key'])) { // handle primary_key => array('tag_id', 'resource_id') |
||
| 234 | $sql .= implode(',', array_map([$this, 'quoteColumnName'], $options['primary_key'])); |
||
| 235 | 68 | } |
|
| 236 | 68 | $sql .= ')'; |
|
| 237 | 1 | } else { |
|
| 238 | 1 | $sql = rtrim($sql, ', '); // no primary keys |
|
| 239 | 1 | } |
|
| 240 | 1 | ||
| 241 | $sql .= ');'; |
||
| 242 | 68 | ||
| 243 | // process column comments |
||
| 244 | if (!empty($this->columnsWithComments)) { |
||
| 245 | 68 | foreach ($this->columnsWithComments as $column) { |
|
| 246 | 6 | $sql .= $this->getColumnCommentSqlDefinition($column, $table->getName()); |
|
| 247 | 6 | } |
|
| 248 | 6 | } |
|
| 249 | 6 | ||
| 250 | // set the indexes |
||
| 251 | if (!empty($indexes)) { |
||
| 252 | foreach ($indexes as $index) { |
||
| 253 | 68 | $sql .= $this->getIndexSqlDefinition($index, $table->getName()); |
|
| 254 | 68 | } |
|
| 255 | 5 | } |
|
| 256 | 5 | ||
| 257 | 5 | // execute the sql |
|
| 258 | 5 | $this->execute($sql); |
|
| 259 | |||
| 260 | // process table comments |
||
| 261 | 68 | if (isset($options['comment'])) { |
|
| 262 | $sql = sprintf( |
||
| 263 | 'COMMENT ON TABLE %s IS %s', |
||
| 264 | 68 | $this->quoteTableName($table->getName()), |
|
| 265 | 1 | $this->getConnection()->quote($options['comment']) |
|
| 266 | 1 | ); |
|
| 267 | 1 | $this->execute($sql); |
|
| 268 | 1 | } |
|
| 269 | 1 | } |
|
| 270 | 1 | ||
| 271 | 1 | /** |
|
| 272 | 68 | * {@inheritdoc} |
|
| 273 | */ |
||
| 274 | View Code Duplication | protected function getRenameTableInstructions($tableName, $newTableName) |
|
| 275 | { |
||
| 276 | $sql = sprintf( |
||
| 277 | 1 | 'ALTER TABLE %s RENAME TO %s', |
|
| 278 | $this->quoteTableName($tableName), |
||
| 279 | 1 | $this->quoteColumnName($newTableName) |
|
| 280 | 1 | ); |
|
| 281 | 1 | ||
| 282 | 1 | return new AlterInstructions([], [$sql]); |
|
| 283 | 1 | } |
|
| 284 | 1 | ||
| 285 | 1 | /** |
|
| 286 | * {@inheritdoc} |
||
| 287 | */ |
||
| 288 | protected function getDropTableInstructions($tableName) |
||
| 289 | { |
||
| 290 | 1 | $sql = sprintf('DROP TABLE %s', $this->quoteTableName($tableName)); |
|
| 291 | |||
| 292 | 1 | return new AlterInstructions([], [$sql]); |
|
| 293 | 1 | } |
|
| 294 | |||
| 295 | /** |
||
| 296 | * {@inheritdoc} |
||
| 297 | */ |
||
| 298 | 1 | public function truncateTable($tableName) |
|
| 299 | { |
||
| 300 | 1 | $sql = sprintf( |
|
| 301 | 1 | 'TRUNCATE TABLE %s', |
|
| 302 | 1 | $this->quoteTableName($tableName) |
|
| 303 | 1 | ); |
|
| 304 | |||
| 305 | 1 | $this->execute($sql); |
|
| 306 | 1 | } |
|
| 307 | |||
| 308 | /** |
||
| 309 | * {@inheritdoc} |
||
| 310 | */ |
||
| 311 | 9 | public function getColumns($tableName) |
|
| 312 | { |
||
| 313 | 9 | $parts = $this->getSchemaName($tableName); |
|
| 314 | 9 | $columns = []; |
|
| 315 | $sql = sprintf( |
||
| 316 | "SELECT column_name, data_type, udt_name, is_identity, is_nullable, |
||
| 317 | column_default, character_maximum_length, numeric_precision, numeric_scale, |
||
| 318 | 9 | datetime_precision |
|
| 319 | FROM information_schema.columns |
||
| 320 | 9 | WHERE table_schema = %s AND table_name = %s", |
|
| 321 | 9 | $this->getConnection()->quote($parts['schema']), |
|
| 322 | $this->getConnection()->quote($parts['table']) |
||
| 323 | 9 | ); |
|
| 324 | 9 | $columnsInfo = $this->fetchAll($sql); |
|
| 325 | 9 | ||
| 326 | 9 | foreach ($columnsInfo as $columnInfo) { |
|
| 327 | 9 | $isUserDefined = strtoupper(trim($columnInfo['data_type'])) === 'USER-DEFINED'; |
|
| 328 | 9 | ||
| 329 | 9 | if ($isUserDefined) { |
|
| 330 | 9 | $columnType = Literal::from($columnInfo['udt_name']); |
|
| 331 | 9 | } else { |
|
| 332 | $columnType = $this->getPhinxType($columnInfo['data_type']); |
||
| 333 | 9 | } |
|
| 334 | 1 | ||
| 335 | 1 | // If the default value begins with a ' or looks like a function mark it as literal |
|
| 336 | if (isset($columnInfo['column_default'][0]) && $columnInfo['column_default'][0] === "'") { |
||
| 337 | 9 | if (preg_match('/^\'(.*)\'::[^:]+$/', $columnInfo['column_default'], $match)) { |
|
| 338 | 5 | // '' and \' are replaced with a single ' |
|
| 339 | 5 | $columnDefault = preg_replace('/[\'\\\\]\'/', "'", $match[1]); |
|
| 340 | 9 | } else { |
|
| 341 | 9 | $columnDefault = Literal::from($columnInfo['column_default']); |
|
| 342 | 9 | } |
|
| 343 | } elseif (preg_match('/^\D[a-z_\d]*\(.*\)$/', $columnInfo['column_default'])) { |
||
| 344 | $columnDefault = Literal::from($columnInfo['column_default']); |
||
| 345 | } else { |
||
| 346 | $columnDefault = $columnInfo['column_default']; |
||
| 347 | } |
||
| 348 | 24 | ||
| 349 | $column = new Column(); |
||
| 350 | 24 | $column->setName($columnInfo['column_name']) |
|
| 351 | ->setType($columnType) |
||
| 352 | ->setNull($columnInfo['is_nullable'] === 'YES') |
||
| 353 | 24 | ->setDefault($columnDefault) |
|
| 354 | 24 | ->setIdentity($columnInfo['is_identity'] === 'YES') |
|
| 355 | 24 | ->setScale($columnInfo['numeric_scale']); |
|
| 356 | |||
| 357 | 24 | if (preg_match('/\bwith time zone$/', $columnInfo['data_type'])) { |
|
| 358 | $column->setTimezone(true); |
||
| 359 | 24 | } |
|
| 360 | 24 | ||
| 361 | if (isset($columnInfo['character_maximum_length'])) { |
||
| 362 | $column->setLimit($columnInfo['character_maximum_length']); |
||
| 363 | } |
||
| 364 | |||
| 365 | if (in_array($columnType, [static::PHINX_TYPE_TIME, static::PHINX_TYPE_DATETIME])) { |
||
| 366 | 18 | $column->setPrecision($columnInfo['datetime_precision']); |
|
| 367 | } else { |
||
| 368 | 18 | $column->setPrecision($columnInfo['numeric_precision']); |
|
| 369 | 18 | } |
|
| 370 | 18 | ||
| 371 | 18 | $columns[] = $column; |
|
| 372 | 18 | } |
|
| 373 | 18 | ||
| 374 | return $columns; |
||
| 375 | 18 | } |
|
| 376 | 18 | ||
| 377 | /** |
||
| 378 | * {@inheritdoc} |
||
| 379 | */ |
||
| 380 | public function hasColumn($tableName, $columnName) |
||
| 381 | 3 | { |
|
| 382 | $parts = $this->getSchemaName($tableName); |
||
| 383 | 3 | $sql = sprintf( |
|
| 384 | "SELECT count(*) |
||
| 385 | FROM information_schema.columns |
||
| 386 | 3 | WHERE table_schema = %s AND table_name = %s AND column_name = %s", |
|
| 387 | 3 | $this->getConnection()->quote($parts['schema']), |
|
| 388 | $this->getConnection()->quote($parts['table']), |
||
| 389 | 3 | $this->getConnection()->quote($columnName) |
|
| 390 | 3 | ); |
|
| 391 | 3 | ||
| 392 | 1 | $result = $this->fetchRow($sql); |
|
| 393 | |||
| 394 | 2 | return $result['count'] > 0; |
|
| 395 | 2 | } |
|
| 396 | 2 | ||
| 397 | 2 | /** |
|
| 398 | 2 | * {@inheritdoc} |
|
| 399 | 2 | */ |
|
| 400 | 2 | protected function getAddColumnInstructions(Table $table, Column $column) |
|
| 401 | 2 | { |
|
| 402 | 2 | $instructions = new AlterInstructions(); |
|
| 403 | $instructions->addAlter(sprintf( |
||
| 404 | 'ADD %s %s', |
||
| 405 | $this->quoteColumnName($column->getName()), |
||
| 406 | $this->getColumnSqlDefinition($column) |
||
| 407 | 5 | )); |
|
| 408 | |||
| 409 | if ($column->getComment()) { |
||
| 410 | $instructions->addPostStep($this->getColumnCommentSqlDefinition($column, $table->getName())); |
||
| 411 | 5 | } |
|
| 412 | 5 | ||
| 413 | 5 | return $instructions; |
|
| 414 | 5 | } |
|
| 415 | 5 | ||
| 416 | 5 | /** |
|
| 417 | * {@inheritdoc} |
||
| 418 | 5 | */ |
|
| 419 | 5 | protected function getRenameColumnInstructions($tableName, $columnName, $newColumnName) |
|
| 420 | { |
||
| 421 | 5 | $parts = $this->getSchemaName($tableName); |
|
| 422 | 5 | $sql = sprintf( |
|
| 423 | "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS column_exists |
||
| 424 | 5 | FROM information_schema.columns |
|
| 425 | 5 | WHERE table_schema = %s AND table_name = %s AND column_name = %s", |
|
| 426 | 5 | $this->getConnection()->quote($parts['schema']), |
|
| 427 | 5 | $this->getConnection()->quote($parts['table']), |
|
| 428 | 5 | $this->getConnection()->quote($columnName) |
|
| 429 | 5 | ); |
|
| 430 | 2 | ||
| 431 | 2 | $result = $this->fetchRow($sql); |
|
| 432 | 4 | if (!(bool)$result['column_exists']) { |
|
| 433 | throw new \InvalidArgumentException("The specified column does not exist: $columnName"); |
||
| 434 | 5 | } |
|
| 435 | 5 | ||
| 436 | $instructions = new AlterInstructions(); |
||
| 437 | 1 | $instructions->addPostStep( |
|
| 438 | 1 | sprintf( |
|
| 439 | 1 | 'ALTER TABLE %s RENAME COLUMN %s TO %s', |
|
| 440 | 1 | $tableName, |
|
| 441 | 1 | $this->quoteColumnName($columnName), |
|
| 442 | 1 | $this->quoteColumnName($newColumnName) |
|
| 443 | 1 | ) |
|
| 444 | 1 | ); |
|
| 445 | 1 | ||
| 446 | return $instructions; |
||
| 447 | 4 | } |
|
| 448 | 4 | ||
| 449 | 4 | /** |
|
| 450 | 4 | * {@inheritdoc} |
|
| 451 | 4 | */ |
|
| 452 | 4 | protected function getChangeColumnInstructions($tableName, $columnName, Column $newColumn) |
|
| 453 | 4 | { |
|
| 454 | $instructions = new AlterInstructions(); |
||
| 455 | |||
| 456 | 5 | $sql = sprintf( |
|
| 457 | 1 | 'ALTER COLUMN %s TYPE %s', |
|
| 458 | 1 | $this->quoteColumnName($columnName), |
|
| 459 | 1 | $this->getColumnSqlDefinition($newColumn) |
|
| 460 | 1 | ); |
|
| 461 | 1 | // |
|
| 462 | 1 | //NULL and DEFAULT cannot be set while changing column type |
|
| 463 | 1 | $sql = preg_replace('/ NOT NULL/', '', $sql); |
|
| 464 | 1 | $sql = preg_replace('/ NULL/', '', $sql); |
|
| 465 | 1 | //If it is set, DEFAULT is the last definition |
|
| 466 | $sql = preg_replace('/DEFAULT .*/', '', $sql); |
||
| 467 | |||
| 468 | 5 | $instructions->addAlter($sql); |
|
| 469 | 2 | ||
| 470 | 2 | // process null |
|
| 471 | 2 | $sql = sprintf( |
|
| 472 | 5 | 'ALTER COLUMN %s', |
|
| 473 | $this->quoteColumnName($columnName) |
||
| 474 | ); |
||
| 475 | |||
| 476 | if ($newColumn->isNull()) { |
||
| 477 | 1 | $sql .= ' DROP NOT NULL'; |
|
| 478 | } else { |
||
| 479 | 1 | $sql .= ' SET NOT NULL'; |
|
| 480 | 1 | } |
|
| 481 | 1 | ||
| 482 | 1 | $instructions->addAlter($sql); |
|
| 483 | 1 | ||
| 484 | 1 | if (!is_null($newColumn->getDefault())) { |
|
| 485 | 1 | $instructions->addAlter(sprintf( |
|
| 486 | 1 | 'ALTER COLUMN %s SET %s', |
|
| 487 | $this->quoteColumnName($columnName), |
||
| 488 | $this->getDefaultValueDefinition($newColumn->getDefault(), $newColumn->getType()) |
||
| 489 | )); |
||
| 490 | } else { |
||
| 491 | //drop default |
||
| 492 | $instructions->addAlter(sprintf( |
||
| 493 | 'ALTER COLUMN %s DROP DEFAULT', |
||
| 494 | 9 | $this->quoteColumnName($columnName) |
|
| 495 | )); |
||
| 496 | 9 | } |
|
| 497 | |||
| 498 | // rename column |
||
| 499 | if ($columnName !== $newColumn->getName()) { |
||
| 500 | $instructions->addPostStep(sprintf( |
||
| 501 | 'ALTER TABLE %s RENAME COLUMN %s TO %s', |
||
| 502 | $this->quoteTableName($tableName), |
||
| 503 | $this->quoteColumnName($columnName), |
||
| 504 | $this->quoteColumnName($newColumn->getName()) |
||
| 505 | )); |
||
| 506 | } |
||
| 507 | |||
| 508 | // change column comment if needed |
||
| 509 | if ($newColumn->getComment()) { |
||
| 510 | $instructions->addPostStep($this->getColumnCommentSqlDefinition($newColumn, $tableName)); |
||
| 511 | } |
||
| 512 | |||
| 513 | return $instructions; |
||
| 514 | 9 | } |
|
| 515 | 9 | ||
| 516 | 9 | /** |
|
| 517 | 9 | * {@inheritdoc} |
|
| 518 | 9 | */ |
|
| 519 | 9 | protected function getDropColumnInstructions($tableName, $columnName) |
|
| 520 | 9 | { |
|
| 521 | 9 | $alter = sprintf( |
|
| 522 | 9 | 'DROP COLUMN %s', |
|
| 523 | $this->quoteColumnName($columnName) |
||
| 524 | ); |
||
| 525 | |||
| 526 | return new AlterInstructions([$alter]); |
||
| 527 | } |
||
| 528 | 9 | ||
| 529 | /** |
||
| 530 | 9 | * Get an array of indexes from a particular table. |
|
| 531 | 4 | * |
|
| 532 | 4 | * @param string $tableName Table Name |
|
| 533 | 9 | * @return array |
|
| 534 | 9 | */ |
|
| 535 | 9 | protected function getIndexes($tableName) |
|
| 536 | 9 | { |
|
| 537 | 9 | $parts = $this->getSchemaName($tableName); |
|
| 538 | |||
| 539 | 8 | $indexes = []; |
|
| 540 | 8 | $sql = sprintf( |
|
| 541 | "SELECT |
||
| 542 | i.relname AS index_name, |
||
| 543 | a.attname AS column_name |
||
| 544 | FROM |
||
| 545 | pg_class t, |
||
| 546 | 1 | pg_class i, |
|
| 547 | pg_index ix, |
||
| 548 | 1 | pg_attribute a, |
|
| 549 | 1 | pg_namespace nsp |
|
| 550 | 1 | WHERE |
|
| 551 | 1 | t.oid = ix.indrelid |
|
| 552 | AND i.oid = ix.indexrelid |
||
| 553 | AND a.attrelid = t.oid |
||
| 554 | AND a.attnum = ANY(ix.indkey) |
||
| 555 | AND t.relnamespace = nsp.oid |
||
| 556 | AND nsp.nspname = %s |
||
| 557 | AND t.relkind = 'r' |
||
| 558 | AND t.relname = %s |
||
| 559 | ORDER BY |
||
| 560 | 2 | t.relname, |
|
| 561 | i.relname", |
||
| 562 | 2 | $this->getConnection()->quote($parts['schema']), |
|
| 563 | 2 | $this->getConnection()->quote($parts['table']) |
|
| 564 | 2 | ); |
|
| 565 | $rows = $this->fetchAll($sql); |
||
| 566 | foreach ($rows as $row) { |
||
| 567 | if (!isset($indexes[$row['index_name']])) { |
||
| 568 | $indexes[$row['index_name']] = ['columns' => []]; |
||
| 569 | 1 | } |
|
| 570 | $indexes[$row['index_name']]['columns'][] = $row['column_name']; |
||
| 571 | 1 | } |
|
| 572 | 1 | ||
| 573 | 1 | return $indexes; |
|
| 574 | } |
||
| 575 | 1 | ||
| 576 | 1 | /** |
|
| 577 | * {@inheritdoc} |
||
| 578 | 1 | */ |
|
| 579 | 1 | View Code Duplication | public function hasIndex($tableName, $columns) |
| 580 | 1 | { |
|
| 581 | 1 | if (is_string($columns)) { |
|
| 582 | 1 | $columns = [$columns]; |
|
| 583 | 1 | } |
|
| 584 | 1 | $indexes = $this->getIndexes($tableName); |
|
| 585 | 1 | foreach ($indexes as $index) { |
|
| 586 | 1 | if (array_diff($index['columns'], $columns) === array_diff($columns, $index['columns'])) { |
|
| 587 | return true; |
||
| 588 | 1 | } |
|
| 589 | } |
||
| 590 | |||
| 591 | return false; |
||
| 592 | } |
||
| 593 | |||
| 594 | /** |
||
| 595 | * {@inheritdoc} |
||
| 596 | 1 | */ |
|
| 597 | View Code Duplication | public function hasIndexByName($tableName, $indexName) |
|
| 598 | 1 | { |
|
| 599 | 1 | $indexes = $this->getIndexes($tableName); |
|
| 600 | foreach ($indexes as $name => $index) { |
||
| 601 | 1 | if ($name === $indexName) { |
|
| 602 | 1 | return true; |
|
| 603 | 1 | } |
|
| 604 | } |
||
| 605 | |||
| 606 | return false; |
||
| 607 | } |
||
| 608 | 3 | ||
| 609 | /** |
||
| 610 | 3 | * {@inheritdoc} |
|
| 611 | 1 | */ |
|
| 612 | 1 | protected function getAddIndexInstructions(Table $table, Index $index) |
|
| 613 | 3 | { |
|
| 614 | 3 | $instructions = new AlterInstructions(); |
|
| 615 | $instructions->addPostStep($this->getIndexSqlDefinition($index, $table->getName())); |
||
| 616 | |||
| 617 | return $instructions; |
||
| 618 | } |
||
| 619 | |||
| 620 | 3 | /** |
|
| 621 | 3 | * {@inheritdoc} |
|
| 622 | 3 | */ |
|
| 623 | 3 | protected function getDropIndexByColumnsInstructions($tableName, $columns) |
|
| 624 | { |
||
| 625 | 1 | $parts = $this->getSchemaName($tableName); |
|
| 626 | 1 | ||
| 627 | if (is_string($columns)) { |
||
| 628 | $columns = [$columns]; // str to array |
||
| 629 | } |
||
| 630 | |||
| 631 | $indexes = $this->getIndexes($tableName); |
||
| 632 | foreach ($indexes as $indexName => $index) { |
||
| 633 | $a = array_diff($columns, $index['columns']); |
||
| 634 | if (empty($a)) { |
||
| 635 | return new AlterInstructions([], [sprintf( |
||
| 636 | 3 | 'DROP INDEX IF EXISTS %s', |
|
| 637 | '"' . ($parts['schema'] . '".' . $this->quoteColumnName($indexName)) |
||
| 638 | 3 | )]); |
|
| 639 | 3 | } |
|
| 640 | } |
||
| 641 | |||
| 642 | throw new \InvalidArgumentException(sprintf( |
||
| 643 | "The specified index on columns '%s' does not exist", |
||
| 644 | implode(',', $columns) |
||
| 645 | )); |
||
| 646 | } |
||
| 647 | |||
| 648 | /** |
||
| 649 | * {@inheritdoc} |
||
| 650 | 3 | */ |
|
| 651 | protected function getDropIndexByNameInstructions($tableName, $indexName) |
||
| 652 | 3 | { |
|
| 653 | 3 | $parts = $this->getSchemaName($tableName); |
|
| 654 | 3 | ||
| 655 | 3 | $sql = sprintf( |
|
| 656 | 3 | 'DROP INDEX IF EXISTS %s', |
|
| 657 | 3 | '"' . ($parts['schema'] . '".' . $this->quoteColumnName($indexName)) |
|
| 658 | 3 | ); |
|
| 659 | 3 | ||
| 660 | return new AlterInstructions([], [$sql]); |
||
| 661 | } |
||
| 662 | |||
| 663 | /** |
||
| 664 | * {@inheritdoc} |
||
| 665 | 2 | */ |
|
| 666 | View Code Duplication | public function hasForeignKey($tableName, $columns, $constraint = null) |
|
| 667 | 2 | { |
|
| 668 | 2 | if (is_string($columns)) { |
|
| 669 | 2 | $columns = [$columns]; // str to array |
|
| 670 | 2 | } |
|
| 671 | 2 | $foreignKeys = $this->getForeignKeys($tableName); |
|
| 672 | 2 | if ($constraint) { |
|
| 673 | 2 | if (isset($foreignKeys[$constraint])) { |
|
| 674 | return !empty($foreignKeys[$constraint]); |
||
| 675 | } |
||
| 676 | |||
| 677 | return false; |
||
| 678 | 1 | } else { |
|
| 679 | foreach ($foreignKeys as $key) { |
||
| 680 | 1 | $a = array_diff($columns, $key['columns']); |
|
| 681 | if (empty($a)) { |
||
| 682 | return true; |
||
| 683 | } |
||
| 684 | 1 | } |
|
| 685 | 1 | ||
| 686 | 1 | return false; |
|
| 687 | 1 | } |
|
| 688 | 1 | } |
|
| 689 | |||
| 690 | 1 | /** |
|
| 691 | 1 | * Get an array of foreign keys from a particular table. |
|
| 692 | 1 | * |
|
| 693 | 1 | * @param string $tableName Table Name |
|
| 694 | 1 | * @return array |
|
| 695 | */ |
||
| 696 | protected function getForeignKeys($tableName) |
||
| 697 | { |
||
| 698 | $parts = $this->getSchemaName($tableName); |
||
| 699 | $foreignKeys = []; |
||
| 700 | $rows = $this->fetchAll(sprintf( |
||
| 701 | 1 | "SELECT |
|
| 702 | 1 | tc.constraint_name, |
|
| 703 | tc.table_name, kcu.column_name, |
||
| 704 | 1 | ccu.table_name AS referenced_table_name, |
|
| 705 | ccu.column_name AS referenced_column_name |
||
| 706 | 1 | FROM |
|
| 707 | 1 | information_schema.table_constraints AS tc |
|
| 708 | 1 | JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name |
|
| 709 | 1 | JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name |
|
| 710 | WHERE constraint_type = 'FOREIGN KEY' AND tc.table_schema = %s AND tc.table_name = %s |
||
| 711 | 1 | ORDER BY kcu.position_in_unique_constraint", |
|
| 712 | $this->getConnection()->quote($parts['schema']), |
||
| 713 | $this->getConnection()->quote($parts['table']) |
||
| 714 | )); |
||
| 715 | foreach ($rows as $row) { |
||
| 716 | 68 | $foreignKeys[$row['constraint_name']]['table'] = $row['table_name']; |
|
| 717 | $foreignKeys[$row['constraint_name']]['columns'][] = $row['column_name']; |
||
| 718 | $foreignKeys[$row['constraint_name']]['referenced_table'] = $row['referenced_table_name']; |
||
| 719 | 68 | $foreignKeys[$row['constraint_name']]['referenced_columns'][] = $row['referenced_column_name']; |
|
| 720 | 14 | } |
|
| 721 | |||
| 722 | 1 | return $foreignKeys; |
|
| 723 | } |
||
| 724 | 1 | ||
| 725 | /** |
||
| 726 | 14 | * {@inheritdoc} |
|
| 727 | 68 | */ |
|
| 728 | 68 | View Code Duplication | protected function getAddForeignKeyInstructions(Table $table, ForeignKey $foreignKey) |
| 729 | 68 | { |
|
| 730 | 68 | $alter = sprintf( |
|
| 731 | 68 | 'ADD %s', |
|
| 732 | 68 | $this->getForeignKeySqlDefinition($foreignKey, $table->getName()) |
|
| 733 | 68 | ); |
|
| 734 | 68 | ||
| 735 | 68 | return new AlterInstructions([$alter]); |
|
| 736 | 68 | } |
|
| 737 | 68 | ||
| 738 | 68 | /** |
|
| 739 | 2 | * {@inheritdoc} |
|
| 740 | 68 | */ |
|
| 741 | 68 | protected function getDropForeignKeyInstructions($tableName, $constraint) |
|
| 742 | 68 | { |
|
| 743 | $alter = sprintf( |
||
| 744 | 68 | 'DROP CONSTRAINT %s', |
|
| 745 | 68 | $this->quoteColumnName($constraint) |
|
| 746 | 68 | ); |
|
| 747 | 1 | ||
| 748 | 68 | return new AlterInstructions([$alter]); |
|
| 749 | 68 | } |
|
| 750 | 68 | ||
| 751 | 15 | /** |
|
| 752 | 15 | * {@inheritdoc} |
|
| 753 | 1 | */ |
|
| 754 | protected function getDropForeignKeyByColumnsInstructions($tableName, $columns) |
||
| 755 | { |
||
| 756 | $instructions = new AlterInstructions(); |
||
| 757 | |||
| 758 | 14 | $parts = $this->getSchemaName($tableName); |
|
| 759 | |||
| 760 | foreach ($columns as $column) { |
||
| 761 | 14 | $rows = $this->fetchAll(sprintf( |
|
| 762 | "SELECT CONSTRAINT_NAME |
||
| 763 | FROM information_schema.KEY_COLUMN_USAGE |
||
| 764 | 14 | WHERE TABLE_SCHEMA = %s |
|
| 765 | AND TABLE_NAME IS NOT NULL |
||
| 766 | AND TABLE_NAME = %s |
||
| 767 | 14 | AND COLUMN_NAME = %s |
|
| 768 | ORDER BY POSITION_IN_UNIQUE_CONSTRAINT", |
||
| 769 | $this->getConnection()->quote($parts['schema']), |
||
| 770 | 14 | $this->getConnection()->quote($parts['table']), |
|
| 771 | 14 | $this->getConnection()->quote($column) |
|
| 772 | 13 | )); |
|
| 773 | |||
| 774 | foreach ($rows as $row) { |
||
| 775 | 1 | $newInstr = $this->getDropForeignKeyInstructions($tableName, $row['constraint_name']); |
|
| 776 | 14 | $instructions->merge($newInstr); |
|
| 777 | } |
||
| 778 | } |
||
| 779 | |||
| 780 | return $instructions; |
||
| 781 | } |
||
| 782 | |||
| 783 | /** |
||
| 784 | * {@inheritdoc} |
||
| 785 | 10 | */ |
|
| 786 | public function getSqlType($type, $limit = null) |
||
| 787 | { |
||
| 788 | 10 | switch ($type) { |
|
| 789 | 10 | case static::PHINX_TYPE_TEXT: |
|
| 790 | 6 | case static::PHINX_TYPE_TIME: |
|
| 791 | 10 | case static::PHINX_TYPE_DATE: |
|
| 792 | 10 | case static::PHINX_TYPE_BOOLEAN: |
|
| 793 | case static::PHINX_TYPE_JSON: |
||
| 794 | 10 | case static::PHINX_TYPE_JSONB: |
|
| 795 | 2 | case static::PHINX_TYPE_UUID: |
|
| 796 | 10 | case static::PHINX_TYPE_CIDR: |
|
| 797 | case static::PHINX_TYPE_INET: |
||
| 798 | 10 | case static::PHINX_TYPE_MACADDR: |
|
| 799 | case static::PHINX_TYPE_TIMESTAMP: |
||
| 800 | 10 | return ['name' => $type]; |
|
| 801 | case static::PHINX_TYPE_INTEGER: |
||
| 802 | 1 | if ($limit && $limit == static::INT_SMALL) { |
|
| 803 | return [ |
||
| 804 | 1 | 'name' => 'smallint', |
|
| 805 | 10 | 'limit' => static::INT_SMALL |
|
| 806 | 10 | ]; |
|
| 807 | 10 | } |
|
| 808 | 9 | ||
| 809 | 5 | return ['name' => $type]; |
|
| 810 | 5 | case static::PHINX_TYPE_DECIMAL: |
|
| 811 | 3 | return ['name' => $type, 'precision' => 18, 'scale' => 0]; |
|
| 812 | 4 | case static::PHINX_TYPE_STRING: |
|
| 813 | 4 | return ['name' => 'character varying', 'limit' => 255]; |
|
| 814 | 2 | case static::PHINX_TYPE_CHAR: |
|
| 815 | 4 | return ['name' => 'character', 'limit' => 255]; |
|
| 816 | 4 | case static::PHINX_TYPE_BIG_INTEGER: |
|
| 817 | 2 | return ['name' => 'bigint']; |
|
| 818 | 4 | case static::PHINX_TYPE_FLOAT: |
|
| 819 | 1 | return ['name' => 'real']; |
|
| 820 | case static::PHINX_TYPE_DATETIME: |
||
| 821 | 4 | return ['name' => 'timestamp']; |
|
| 822 | 4 | case static::PHINX_TYPE_BLOB: |
|
| 823 | 4 | case static::PHINX_TYPE_BINARY: |
|
| 824 | 4 | return ['name' => 'bytea']; |
|
| 825 | 3 | case static::PHINX_TYPE_INTERVAL: |
|
| 826 | 4 | return ['name' => 'interval']; |
|
| 827 | 2 | // Geospatial database types |
|
| 828 | 4 | // Spatial storage in Postgres is done via the PostGIS extension, |
|
| 829 | 4 | // which enables the use of the "geography" type in combination |
|
| 830 | 4 | // with SRID 4326. |
|
| 831 | 4 | case static::PHINX_TYPE_GEOMETRY: |
|
| 832 | 3 | return ['name' => 'geography', 'type' => 'geometry', 'srid' => 4326]; |
|
| 833 | 3 | case static::PHINX_TYPE_POINT: |
|
| 834 | 3 | return ['name' => 'geography', 'type' => 'point', 'srid' => 4326]; |
|
| 835 | 3 | case static::PHINX_TYPE_LINESTRING: |
|
| 836 | 1 | return ['name' => 'geography', 'type' => 'linestring', 'srid' => 4326]; |
|
| 837 | 1 | case static::PHINX_TYPE_POLYGON: |
|
| 838 | return ['name' => 'geography', 'type' => 'polygon', 'srid' => 4326]; |
||
| 839 | default: |
||
| 840 | if ($this->isArrayType($type)) { |
||
| 841 | return ['name' => $type]; |
||
| 842 | } |
||
| 843 | // Return array type |
||
| 844 | throw new \RuntimeException('The type: "' . $type . '" is not supported'); |
||
| 845 | } |
||
| 846 | } |
||
| 847 | |||
| 848 | /** |
||
| 849 | * Returns Phinx type by SQL type |
||
| 850 | * |
||
| 851 | * @param string $sqlType SQL type |
||
| 852 | 1 | * @returns string Phinx type |
|
| 853 | */ |
||
| 854 | 1 | public function getPhinxType($sqlType) |
|
| 855 | 1 | { |
|
| 856 | 1 | switch ($sqlType) { |
|
| 857 | case 'character varying': |
||
| 858 | case 'varchar': |
||
| 859 | return static::PHINX_TYPE_STRING; |
||
| 860 | case 'character': |
||
| 861 | 2 | case 'char': |
|
| 862 | return static::PHINX_TYPE_CHAR; |
||
| 863 | 2 | case 'text': |
|
| 864 | 2 | return static::PHINX_TYPE_TEXT; |
|
| 865 | 2 | case 'json': |
|
| 866 | return static::PHINX_TYPE_JSON; |
||
| 867 | case 'jsonb': |
||
| 868 | return static::PHINX_TYPE_JSONB; |
||
| 869 | case 'smallint': |
||
| 870 | return [ |
||
| 871 | 1 | 'name' => 'smallint', |
|
| 872 | 'limit' => static::INT_SMALL |
||
| 873 | 1 | ]; |
|
| 874 | 1 | case 'int': |
|
| 875 | 1 | case 'int4': |
|
| 876 | 1 | case 'integer': |
|
| 877 | return static::PHINX_TYPE_INTEGER; |
||
| 878 | case 'decimal': |
||
| 879 | case 'numeric': |
||
| 880 | return static::PHINX_TYPE_DECIMAL; |
||
| 881 | case 'bigint': |
||
| 882 | case 'int8': |
||
| 883 | return static::PHINX_TYPE_BIG_INTEGER; |
||
| 884 | 68 | case 'real': |
|
| 885 | case 'float4': |
||
| 886 | 68 | return static::PHINX_TYPE_FLOAT; |
|
| 887 | 4 | case 'bytea': |
|
| 888 | 68 | return static::PHINX_TYPE_BINARY; |
|
| 889 | 68 | case 'interval': |
|
| 890 | 68 | return static::PHINX_TYPE_INTERVAL; |
|
| 891 | 68 | case 'time': |
|
| 892 | case 'timetz': |
||
| 893 | case 'time with time zone': |
||
| 894 | case 'time without time zone': |
||
| 895 | return static::PHINX_TYPE_TIME; |
||
| 896 | case 'date': |
||
| 897 | return static::PHINX_TYPE_DATE; |
||
| 898 | case 'timestamp': |
||
| 899 | case 'timestamptz': |
||
| 900 | 68 | case 'timestamp with time zone': |
|
| 901 | case 'timestamp without time zone': |
||
| 902 | 68 | return static::PHINX_TYPE_DATETIME; |
|
| 903 | 68 | case 'bool': |
|
| 904 | 50 | case 'boolean': |
|
| 905 | 50 | return static::PHINX_TYPE_BOOLEAN; |
|
| 906 | 68 | case 'uuid': |
|
| 907 | 68 | return static::PHINX_TYPE_UUID; |
|
| 908 | case 'cidr': |
||
| 909 | 68 | return static::PHINX_TYPE_CIDR; |
|
| 910 | 1 | case 'inet': |
|
| 911 | 1 | return static::PHINX_TYPE_INET; |
|
| 912 | 1 | case 'macaddr': |
|
| 913 | 1 | return static::PHINX_TYPE_MACADDR; |
|
| 914 | 1 | default: |
|
| 915 | 68 | throw new \RuntimeException('The PostgreSQL type: "' . $sqlType . '" is not supported'); |
|
| 916 | } |
||
| 917 | } |
||
| 918 | |||
| 919 | /** |
||
| 920 | * {@inheritdoc} |
||
| 921 | */ |
||
| 922 | 68 | public function createDatabase($name, $options = []) |
|
| 923 | 68 | { |
|
| 924 | 68 | $charset = isset($options['charset']) ? $options['charset'] : 'utf8'; |
|
| 925 | 68 | $this->execute(sprintf("CREATE DATABASE %s WITH ENCODING = '%s'", $name, $charset)); |
|
| 926 | 68 | } |
|
| 927 | |||
| 928 | /** |
||
| 929 | 68 | * {@inheritdoc} |
|
| 930 | 68 | */ |
|
| 931 | 68 | public function hasDatabase($name) |
|
| 932 | 68 | { |
|
| 933 | 1 | $sql = sprintf("SELECT count(*) FROM pg_database WHERE datname = '%s'", $name); |
|
| 934 | 1 | $result = $this->fetchRow($sql); |
|
| 935 | |||
| 936 | return $result['count'] > 0; |
||
| 937 | 68 | } |
|
| 938 | |||
| 939 | 68 | /** |
|
| 940 | 68 | * {@inheritdoc} |
|
| 941 | 68 | */ |
|
| 942 | public function dropDatabase($name) |
||
| 943 | 68 | { |
|
| 944 | $this->disconnect(); |
||
| 945 | $this->execute(sprintf('DROP DATABASE IF EXISTS %s', $name)); |
||
| 946 | $this->connect(); |
||
| 947 | } |
||
| 948 | |||
| 949 | /** |
||
| 950 | * Get the defintion for a `DEFAULT` statement. |
||
| 951 | * |
||
| 952 | * @param mixed $default default value |
||
| 953 | 6 | * @param string $columnType column type added |
|
| 954 | * @return string |
||
| 955 | */ |
||
| 956 | 6 | View Code Duplication | protected function getDefaultValueDefinition($default, $columnType = null) |
| 957 | 6 | { |
|
| 958 | 6 | if (is_string($default) && 'CURRENT_TIMESTAMP' !== $default) { |
|
| 959 | $default = $this->getConnection()->quote($default); |
||
| 960 | 6 | } elseif (is_bool($default)) { |
|
| 961 | 6 | $default = $this->castToBool($default); |
|
| 962 | 6 | } elseif ($columnType === static::PHINX_TYPE_BOOLEAN) { |
|
| 963 | 6 | $default = $this->castToBool((bool)$default); |
|
| 964 | } |
||
| 965 | 6 | ||
| 966 | return isset($default) ? 'DEFAULT ' . $default : ''; |
||
| 967 | } |
||
| 968 | |||
| 969 | /** |
||
| 970 | * Gets the PostgreSQL Column Definition for a Column object. |
||
| 971 | * |
||
| 972 | * @param \Phinx\Db\Table\Column $column Column |
||
| 973 | * @return string |
||
| 974 | */ |
||
| 975 | 7 | protected function getColumnSqlDefinition(Column $column) |
|
| 976 | { |
||
| 977 | 7 | $buffer = []; |
|
| 978 | 3 | if ($column->isIdentity()) { |
|
| 979 | 3 | $buffer[] = $column->getType() == 'biginteger' ? 'BIGSERIAL' : 'SERIAL'; |
|
| 980 | 5 | } elseif ($column->getType() instanceof Literal) { |
|
| 981 | 5 | $buffer[] = (string)$column->getType(); |
|
| 982 | } else { |
||
| 983 | $sqlType = $this->getSqlType($column->getType(), $column->getLimit()); |
||
| 984 | 5 | $buffer[] = strtoupper($sqlType['name']); |
|
| 985 | |||
| 986 | 7 | // integers cant have limits in postgres |
|
| 987 | 7 | if (static::PHINX_TYPE_DECIMAL === $sqlType['name'] && ($column->getPrecision() || $column->getScale())) { |
|
| 988 | 7 | $buffer[] = sprintf( |
|
| 989 | 7 | '(%s, %s)', |
|
| 990 | 7 | $column->getPrecision() ?: $sqlType['precision'], |
|
| 991 | 7 | $column->getScale() ?: $sqlType['scale'] |
|
| 992 | 7 | ); |
|
| 993 | 7 | } elseif (in_array($sqlType['name'], ['geography'])) { |
|
| 994 | // geography type must be written with geometry type and srid, like this: geography(POLYGON,4326) |
||
| 995 | $buffer[] = sprintf( |
||
| 996 | '(%s,%s)', |
||
| 997 | strtoupper($sqlType['type']), |
||
| 998 | $sqlType['srid'] |
||
| 999 | ); |
||
| 1000 | } elseif (!in_array($sqlType['name'], ['integer', 'smallint', 'bigint', 'boolean'])) { |
||
| 1001 | if ($column->getLimit() || isset($sqlType['limit'])) { |
||
| 1002 | $buffer[] = sprintf('(%s)', $column->getLimit() ?: $sqlType['limit']); |
||
| 1003 | 3 | } |
|
| 1004 | } |
||
| 1005 | 3 | ||
| 1006 | 3 | $timeTypes = [ |
|
| 1007 | 3 | 'time', |
|
| 1008 | 3 | 'timestamp', |
|
| 1009 | ]; |
||
| 1010 | |||
| 1011 | 3 | if (in_array($sqlType['name'], $timeTypes) && is_numeric($column->getPrecision())) { |
|
| 1012 | $buffer[] = sprintf('(%s)', $column->getPrecision()); |
||
| 1013 | } |
||
| 1014 | 3 | ||
| 1015 | if (in_array($sqlType['name'], $timeTypes) && $column->isTimezone()) { |
||
| 1016 | $buffer[] = strtoupper('with time zone'); |
||
| 1017 | } |
||
| 1018 | } |
||
| 1019 | |||
| 1020 | 68 | $buffer[] = $column->isNull() ? 'NULL' : 'NOT NULL'; |
|
| 1021 | |||
| 1022 | if (!is_null($column->getDefault())) { |
||
| 1023 | 68 | $buffer[] = $this->getDefaultValueDefinition($column->getDefault(), $column->getType()); |
|
| 1024 | 67 | } |
|
| 1025 | 67 | ||
| 1026 | return implode(' ', $buffer); |
||
| 1027 | 68 | } |
|
| 1028 | |||
| 1029 | 68 | /** |
|
| 1030 | 68 | * Gets the PostgreSQL Column Comment Definition for a column object. |
|
| 1031 | * |
||
| 1032 | * @param \Phinx\Db\Table\Column $column Column |
||
| 1033 | * @param string $tableName Table name |
||
| 1034 | * @return string |
||
| 1035 | */ |
||
| 1036 | protected function getColumnCommentSqlDefinition(Column $column, $tableName) |
||
| 1037 | { |
||
| 1038 | 68 | // passing 'null' is to remove column comment |
|
| 1039 | $comment = (strcasecmp($column->getComment(), 'NULL') !== 0) |
||
| 1040 | 68 | ? $this->getConnection()->quote($column->getComment()) |
|
| 1041 | 68 | : 'NULL'; |
|
| 1042 | 68 | ||
| 1043 | return sprintf( |
||
| 1044 | 'COMMENT ON COLUMN %s.%s IS %s;', |
||
| 1045 | $this->quoteTableName($tableName), |
||
| 1046 | $this->quoteColumnName($column->getName()), |
||
| 1047 | $comment |
||
| 1048 | ); |
||
| 1049 | } |
||
| 1050 | 68 | ||
| 1051 | /** |
||
| 1052 | 68 | * Gets the PostgreSQL Index Definition for an Index object. |
|
| 1053 | * |
||
| 1054 | * @param \Phinx\Db\Table\Index $index Index |
||
| 1055 | 68 | * @param string $tableName Table name |
|
| 1056 | * @return string |
||
| 1057 | 68 | */ |
|
| 1058 | 68 | protected function getIndexSqlDefinition(Index $index, $tableName) |
|
| 1059 | 68 | { |
|
| 1060 | $parts = $this->getSchemaName($tableName); |
||
| 1061 | |||
| 1062 | if (is_string($index->getName())) { |
||
| 1063 | $indexName = $index->getName(); |
||
| 1064 | } else { |
||
| 1065 | $columnNames = $index->getColumns(); |
||
| 1066 | $indexName = sprintf('%s_%s', $parts['table'], implode('_', $columnNames)); |
||
| 1067 | } |
||
| 1068 | 68 | $def = sprintf( |
|
| 1069 | "CREATE %s INDEX %s ON %s (%s);", |
||
| 1070 | 68 | ($index->getType() === Index::UNIQUE ? 'UNIQUE' : ''), |
|
| 1071 | 68 | $this->quoteColumnName($indexName), |
|
| 1072 | 68 | $this->quoteTableName($tableName), |
|
| 1073 | implode(',', array_map([$this, 'quoteColumnName'], $index->getColumns())) |
||
| 1074 | ); |
||
| 1075 | |||
| 1076 | return $def; |
||
| 1077 | } |
||
| 1078 | |||
| 1079 | 68 | /** |
|
| 1080 | * Gets the MySQL Foreign Key Definition for an ForeignKey object. |
||
| 1081 | 68 | * |
|
| 1082 | 68 | * @param \Phinx\Db\Table\ForeignKey $foreignKey |
|
| 1083 | 68 | * @param string $tableName Table name |
|
| 1084 | 68 | * @return string |
|
| 1085 | */ |
||
| 1086 | protected function getForeignKeySqlDefinition(ForeignKey $foreignKey, $tableName) |
||
| 1087 | { |
||
| 1088 | $parts = $this->getSchemaName($tableName); |
||
| 1089 | |||
| 1090 | $constraintName = $foreignKey->getConstraint() ?: ($parts['table'] . '_' . implode('_', $foreignKey->getColumns()) . '_fkey'); |
||
| 1091 | 68 | $def = ' CONSTRAINT ' . $this->quoteColumnName($constraintName) . |
|
| 1092 | ' FOREIGN KEY ("' . implode('", "', $foreignKey->getColumns()) . '")' . |
||
| 1093 | " REFERENCES {$this->quoteTableName($foreignKey->getReferencedTable()->getName())} (\"" . |
||
| 1094 | implode('", "', $foreignKey->getReferencedColumns()) . '")'; |
||
| 1095 | 68 | if ($foreignKey->getOnDelete()) { |
|
| 1096 | 68 | $def .= " ON DELETE {$foreignKey->getOnDelete()}"; |
|
| 1097 | 68 | } |
|
| 1098 | 68 | if ($foreignKey->getOnUpdate()) { |
|
| 1099 | 68 | $def .= " ON UPDATE {$foreignKey->getOnUpdate()}"; |
|
| 1100 | 68 | } |
|
| 1101 | 68 | ||
| 1102 | return $def; |
||
| 1103 | } |
||
| 1104 | |||
| 1105 | /** |
||
| 1106 | * {@inheritdoc} |
||
| 1107 | 73 | */ |
|
| 1108 | public function createSchemaTable() |
||
| 1119 | |||
| 1120 | /** |
||
| 1121 | * Creates the specified schema. |
||
| 1122 | * |
||
| 1123 | * @param string $schemaName Schema Name |
||
| 1124 | * @return void |
||
| 1125 | */ |
||
| 1126 | public function createSchema($schemaName = 'public') |
||
| 1127 | 14 | { |
|
| 1128 | // from postgres 9.3 we can use "CREATE SCHEMA IF NOT EXISTS schema_name" |
||
| 1129 | 14 | $sql = sprintf('CREATE SCHEMA %s;', $this->quoteSchemaName($schemaName)); |
|
| 1130 | 1 | $this->execute($sql); |
|
| 1131 | } |
||
| 1132 | |||
| 1133 | 13 | /** |
|
| 1134 | 13 | * Checks to see if a schema exists. |
|
| 1135 | * |
||
| 1136 | * @param string $schemaName Schema Name |
||
| 1137 | * @return bool |
||
| 1138 | */ |
||
| 1139 | public function hasSchema($schemaName) |
||
| 1140 | { |
||
| 1141 | $sql = sprintf( |
||
| 1142 | 68 | "SELECT count(*) |
|
| 1143 | FROM pg_namespace |
||
| 1144 | 68 | WHERE nspname = %s", |
|
| 1145 | 68 | $this->getConnection()->quote($schemaName) |
|
| 1146 | ); |
||
| 1147 | $result = $this->fetchRow($sql); |
||
| 1148 | |||
| 1149 | return $result['count'] > 0; |
||
| 1150 | } |
||
| 1151 | 68 | ||
| 1152 | /** |
||
| 1153 | 68 | * Drops the specified schema table. |
|
| 1154 | * |
||
| 1155 | * @param string $schemaName Schema name |
||
| 1156 | * @return void |
||
| 1157 | */ |
||
| 1158 | public function dropSchema($schemaName) |
||
| 1159 | { |
||
| 1160 | $sql = sprintf("DROP SCHEMA IF EXISTS %s CASCADE;", $this->quoteSchemaName($schemaName)); |
||
| 1161 | $this->execute($sql); |
||
| 1163 | |||
| 1164 | /** |
||
| 1165 | * Drops all schemas. |
||
| 1166 | * |
||
| 1167 | * @return void |
||
| 1168 | */ |
||
| 1169 | public function dropAllSchemas() |
||
| 1175 | |||
| 1176 | /** |
||
| 1177 | * Returns schemas. |
||
| 1178 | * |
||
| 1179 | * @return array |
||
| 1180 | */ |
||
| 1181 | public function getAllSchemas() |
||
| 1194 | |||
| 1195 | /** |
||
| 1196 | * {@inheritdoc} |
||
| 1197 | */ |
||
| 1198 | public function getColumnTypes() |
||
| 1202 | |||
| 1203 | /** |
||
| 1204 | * {@inheritdoc} |
||
| 1205 | */ |
||
| 1206 | public function isValidColumnType(Column $column) |
||
| 1211 | |||
| 1212 | /** |
||
| 1213 | * Check if the given column is an array of a valid type. |
||
| 1214 | * |
||
| 1215 | * @param string $columnType |
||
| 1216 | * @return bool |
||
| 1217 | */ |
||
| 1218 | protected function isArrayType($columnType) |
||
| 1228 | |||
| 1229 | /** |
||
| 1230 | * @param string $tableName Table name |
||
| 1231 | * @return array |
||
| 1232 | */ |
||
| 1233 | private function getSchemaName($tableName) |
||
| 1246 | |||
| 1247 | /** |
||
| 1248 | * Gets the schema name. |
||
| 1249 | * |
||
| 1250 | * @return string |
||
| 1251 | */ |
||
| 1252 | private function getGlobalSchemaName() |
||
| 1258 | |||
| 1259 | /** |
||
| 1260 | * {@inheritdoc} |
||
| 1261 | */ |
||
| 1262 | public function castToBool($value) |
||
| 1263 | { |
||
| 1264 | return (bool)$value ? 'TRUE' : 'FALSE'; |
||
| 1265 | } |
||
| 1266 | } |
||
| 1267 |
Loading history...
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.