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 SqlServerAdapter 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 SqlServerAdapter, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 30 | class SqlServerAdapter extends PdoAdapter |
||
| 31 | { |
||
| 32 | protected $schema = 'dbo'; |
||
| 33 | |||
| 34 | protected $signedColumnTypes = ['integer' => true, 'biginteger' => true, 'float' => true, 'decimal' => true]; |
||
| 35 | |||
| 36 | /** |
||
| 37 | * {@inheritDoc} |
||
| 38 | * |
||
| 39 | * @throws \InvalidArgumentException |
||
| 40 | * |
||
| 41 | * @return void |
||
| 42 | */ |
||
| 43 | public function connect() |
||
| 44 | { |
||
| 45 | if ($this->connection === null) { |
||
| 46 | if (!class_exists('PDO') || !in_array('sqlsrv', PDO::getAvailableDrivers(), true)) { |
||
| 47 | // try our connection via freetds (Mac/Linux) |
||
| 48 | $this->connectDblib(); |
||
| 49 | |||
| 50 | return; |
||
| 51 | } |
||
| 52 | |||
| 53 | $options = $this->getOptions(); |
||
| 54 | |||
| 55 | // if port is specified use it, otherwise use the SqlServer default |
||
| 56 | View Code Duplication | if (empty($options['port'])) { |
|
|
|
|||
| 57 | $dsn = 'sqlsrv:server=' . $options['host'] . ';database=' . $options['name']; |
||
| 58 | } else { |
||
| 59 | $dsn = 'sqlsrv:server=' . $options['host'] . ',' . $options['port'] . ';database=' . $options['name']; |
||
| 60 | } |
||
| 61 | $dsn .= ';MultipleActiveResultSets=false'; |
||
| 62 | |||
| 63 | $driverOptions = []; |
||
| 64 | |||
| 65 | // charset support |
||
| 66 | if (isset($options['charset'])) { |
||
| 67 | $driverOptions[PDO::SQLSRV_ATTR_ENCODING] = $options['charset']; |
||
| 68 | } |
||
| 69 | |||
| 70 | // use custom data fetch mode |
||
| 71 | View Code Duplication | if (!empty($options['fetch_mode'])) { |
|
| 72 | $driverOptions[PDO::ATTR_DEFAULT_FETCH_MODE] = constant('\PDO::FETCH_' . strtoupper($options['fetch_mode'])); |
||
| 73 | } |
||
| 74 | |||
| 75 | // support arbitrary \PDO::SQLSRV_ATTR_* driver options and pass them to PDO |
||
| 76 | // http://php.net/manual/en/ref.pdo-sqlsrv.php#pdo-sqlsrv.constants |
||
| 77 | View Code Duplication | foreach ($options as $key => $option) { |
|
| 78 | if (strpos($key, 'sqlsrv_attr_') === 0) { |
||
| 79 | $driverOptions[constant('\PDO::' . strtoupper($key))] = $option; |
||
| 80 | } |
||
| 81 | } |
||
| 82 | |||
| 83 | $db = $this->createPdoConnection($dsn, $options['user'], $options['pass'], $driverOptions); |
||
| 84 | |||
| 85 | $this->setConnection($db); |
||
| 86 | } |
||
| 87 | } |
||
| 88 | |||
| 89 | /** |
||
| 90 | * Connect to MSSQL using dblib/freetds. |
||
| 91 | * |
||
| 92 | * The "sqlsrv" driver is not available on Unix machines. |
||
| 93 | * |
||
| 94 | * @throws \InvalidArgumentException |
||
| 95 | * @throws \RuntimeException |
||
| 96 | * |
||
| 97 | * @return void |
||
| 98 | */ |
||
| 99 | protected function connectDblib() |
||
| 100 | { |
||
| 101 | if (!class_exists('PDO') || !in_array('dblib', PDO::getAvailableDrivers(), true)) { |
||
| 102 | // @codeCoverageIgnoreStart |
||
| 103 | throw new RuntimeException('You need to enable the PDO_Dblib extension for Phinx to run properly.'); |
||
| 104 | // @codeCoverageIgnoreEnd |
||
| 105 | } |
||
| 106 | |||
| 107 | $options = $this->getOptions(); |
||
| 108 | |||
| 109 | // if port is specified use it, otherwise use the SqlServer default |
||
| 110 | View Code Duplication | if (empty($options['port'])) { |
|
| 111 | $dsn = 'dblib:host=' . $options['host'] . ';dbname=' . $options['name']; |
||
| 112 | } else { |
||
| 113 | $dsn = 'dblib:host=' . $options['host'] . ':' . $options['port'] . ';dbname=' . $options['name']; |
||
| 114 | } |
||
| 115 | |||
| 116 | $driverOptions = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]; |
||
| 117 | |||
| 118 | try { |
||
| 119 | $db = new PDO($dsn, $options['user'], $options['pass'], $driverOptions); |
||
| 120 | } catch (PDOException $exception) { |
||
| 121 | throw new InvalidArgumentException(sprintf( |
||
| 122 | 'There was a problem connecting to the database: %s', |
||
| 123 | $exception->getMessage() |
||
| 124 | )); |
||
| 125 | } |
||
| 126 | |||
| 127 | $this->setConnection($db); |
||
| 128 | } |
||
| 129 | |||
| 130 | /** |
||
| 131 | * {@inheritDoc} |
||
| 132 | * |
||
| 133 | * @return void |
||
| 134 | */ |
||
| 135 | public function disconnect() |
||
| 136 | { |
||
| 137 | $this->connection = null; |
||
| 138 | } |
||
| 139 | |||
| 140 | /** |
||
| 141 | * @inheritDoc |
||
| 142 | */ |
||
| 143 | public function hasTransactions() |
||
| 144 | { |
||
| 145 | return true; |
||
| 146 | } |
||
| 147 | |||
| 148 | /** |
||
| 149 | * {@inheritDoc} |
||
| 150 | * |
||
| 151 | * @return void |
||
| 152 | */ |
||
| 153 | public function beginTransaction() |
||
| 154 | { |
||
| 155 | $this->execute('BEGIN TRANSACTION'); |
||
| 156 | } |
||
| 157 | |||
| 158 | /** |
||
| 159 | * {@inheritDoc} |
||
| 160 | * |
||
| 161 | * @return void |
||
| 162 | */ |
||
| 163 | public function commitTransaction() |
||
| 164 | { |
||
| 165 | $this->execute('COMMIT TRANSACTION'); |
||
| 166 | } |
||
| 167 | |||
| 168 | /** |
||
| 169 | * {@inheritDoc} |
||
| 170 | * |
||
| 171 | * @return void |
||
| 172 | */ |
||
| 173 | public function rollbackTransaction() |
||
| 174 | { |
||
| 175 | $this->execute('ROLLBACK TRANSACTION'); |
||
| 176 | } |
||
| 177 | |||
| 178 | /** |
||
| 179 | * @inheritDoc |
||
| 180 | */ |
||
| 181 | public function quoteTableName($tableName) |
||
| 182 | { |
||
| 183 | return str_replace('.', '].[', $this->quoteColumnName($tableName)); |
||
| 184 | } |
||
| 185 | |||
| 186 | /** |
||
| 187 | * @inheritDoc |
||
| 188 | */ |
||
| 189 | public function quoteColumnName($columnName) |
||
| 190 | { |
||
| 191 | return '[' . str_replace(']', '\]', $columnName) . ']'; |
||
| 192 | } |
||
| 193 | |||
| 194 | /** |
||
| 195 | * @inheritDoc |
||
| 196 | */ |
||
| 197 | public function hasTable($tableName) |
||
| 198 | { |
||
| 199 | if ($this->hasCreatedTable($tableName)) { |
||
| 200 | return true; |
||
| 201 | } |
||
| 202 | |||
| 203 | $result = $this->fetchRow(sprintf("SELECT count(*) as [count] FROM information_schema.tables WHERE table_name = '%s';", $tableName)); |
||
| 204 | |||
| 205 | return $result['count'] > 0; |
||
| 206 | } |
||
| 207 | |||
| 208 | /** |
||
| 209 | * {@inheritDoc} |
||
| 210 | * |
||
| 211 | * @return void |
||
| 212 | */ |
||
| 213 | public function createTable(Table $table, array $columns = [], array $indexes = []) |
||
| 214 | { |
||
| 215 | $options = $table->getOptions(); |
||
| 216 | |||
| 217 | // Add the default primary key |
||
| 218 | View Code Duplication | if (!isset($options['id']) || (isset($options['id']) && $options['id'] === true)) { |
|
| 219 | $options['id'] = 'id'; |
||
| 220 | } |
||
| 221 | |||
| 222 | View Code Duplication | if (isset($options['id']) && is_string($options['id'])) { |
|
| 223 | // Handle id => "field_name" to support AUTO_INCREMENT |
||
| 224 | $column = new Column(); |
||
| 225 | $column->setName($options['id']) |
||
| 226 | ->setType('integer') |
||
| 227 | ->setIdentity(true); |
||
| 228 | |||
| 229 | array_unshift($columns, $column); |
||
| 230 | if (isset($options['primary_key']) && $options['id'] !== $options['primary_key'] && [$options['id']] !== $options['primary_key']) { |
||
| 231 | throw new InvalidArgumentException('You cannot enable an auto incrementing ID field and a primary key'); |
||
| 232 | } |
||
| 233 | $options['primary_key'] = $options['id']; |
||
| 234 | } |
||
| 235 | |||
| 236 | $sql = 'CREATE TABLE '; |
||
| 237 | $sql .= $this->quoteTableName($table->getName()) . ' ('; |
||
| 238 | $sqlBuffer = []; |
||
| 239 | $columnsWithComments = []; |
||
| 240 | View Code Duplication | foreach ($columns as $column) { |
|
| 241 | $sqlBuffer[] = $this->quoteColumnName($column->getName()) . ' ' . $this->getColumnSqlDefinition($column); |
||
| 242 | |||
| 243 | // set column comments, if needed |
||
| 244 | if ($column->getComment()) { |
||
| 245 | $columnsWithComments[] = $column; |
||
| 246 | } |
||
| 247 | } |
||
| 248 | |||
| 249 | // set the primary key(s) |
||
| 250 | if (isset($options['primary_key'])) { |
||
| 251 | $pkSql = sprintf('CONSTRAINT PK_%s PRIMARY KEY (', $table->getName()); |
||
| 252 | if (is_string($options['primary_key'])) { // handle primary_key => 'id' |
||
| 253 | $pkSql .= $this->quoteColumnName($options['primary_key']); |
||
| 254 | } elseif (is_array($options['primary_key'])) { // handle primary_key => array('tag_id', 'resource_id') |
||
| 255 | $pkSql .= implode(',', array_map([$this, 'quoteColumnName'], $options['primary_key'])); |
||
| 256 | } |
||
| 257 | $pkSql .= ')'; |
||
| 258 | $sqlBuffer[] = $pkSql; |
||
| 259 | } |
||
| 260 | |||
| 261 | $sql .= implode(', ', $sqlBuffer); |
||
| 262 | $sql .= ');'; |
||
| 263 | |||
| 264 | // process column comments |
||
| 265 | foreach ($columnsWithComments as $column) { |
||
| 266 | $sql .= $this->getColumnCommentSqlDefinition($column, $table->getName()); |
||
| 267 | } |
||
| 268 | |||
| 269 | // set the indexes |
||
| 270 | foreach ($indexes as $index) { |
||
| 271 | $sql .= $this->getIndexSqlDefinition($index, $table->getName()); |
||
| 272 | } |
||
| 273 | |||
| 274 | // execute the sql |
||
| 275 | $this->execute($sql); |
||
| 276 | |||
| 277 | $this->addCreatedTable($table->getName()); |
||
| 278 | } |
||
| 279 | |||
| 280 | /** |
||
| 281 | * {@inheritDoc} |
||
| 282 | * |
||
| 283 | * @throws \InvalidArgumentException |
||
| 284 | */ |
||
| 285 | View Code Duplication | protected function getChangePrimaryKeyInstructions(Table $table, $newColumns) |
|
| 286 | { |
||
| 287 | $instructions = new AlterInstructions(); |
||
| 288 | |||
| 289 | // Drop the existing primary key |
||
| 290 | $primaryKey = $this->getPrimaryKey($table->getName()); |
||
| 291 | if (!empty($primaryKey['constraint'])) { |
||
| 292 | $sql = sprintf( |
||
| 293 | 'DROP CONSTRAINT %s', |
||
| 294 | $this->quoteColumnName($primaryKey['constraint']) |
||
| 295 | ); |
||
| 296 | $instructions->addAlter($sql); |
||
| 297 | } |
||
| 298 | |||
| 299 | // Add the primary key(s) |
||
| 300 | if (!empty($newColumns)) { |
||
| 301 | $sql = sprintf( |
||
| 302 | 'ALTER TABLE %s ADD CONSTRAINT %s PRIMARY KEY (', |
||
| 303 | $this->quoteTableName($table->getName()), |
||
| 304 | $this->quoteColumnName('PK_' . $table->getName()) |
||
| 305 | ); |
||
| 306 | if (is_string($newColumns)) { // handle primary_key => 'id' |
||
| 307 | $sql .= $this->quoteColumnName($newColumns); |
||
| 308 | } elseif (is_array($newColumns)) { // handle primary_key => array('tag_id', 'resource_id') |
||
| 309 | $sql .= implode(',', array_map([$this, 'quoteColumnName'], $newColumns)); |
||
| 310 | } else { |
||
| 311 | throw new InvalidArgumentException(sprintf( |
||
| 312 | 'Invalid value for primary key: %s', |
||
| 313 | json_encode($newColumns) |
||
| 314 | )); |
||
| 315 | } |
||
| 316 | $sql .= ')'; |
||
| 317 | $instructions->addPostStep($sql); |
||
| 318 | } |
||
| 319 | |||
| 320 | return $instructions; |
||
| 321 | } |
||
| 322 | |||
| 323 | /** |
||
| 324 | * {@inheritDoc} |
||
| 325 | * |
||
| 326 | * @throws \BadMethodCallException |
||
| 327 | * |
||
| 328 | * @return void |
||
| 329 | */ |
||
| 330 | protected function getChangeCommentInstructions(Table $table, $newComment) |
||
| 331 | { |
||
| 332 | throw new BadMethodCallException('SQLite does not have table comments'); |
||
| 333 | } |
||
| 334 | |||
| 335 | /** |
||
| 336 | * Gets the SqlServer Column Comment Defininition for a column object. |
||
| 337 | * |
||
| 338 | * @param \Phinx\Db\Table\Column $column Column |
||
| 339 | * @param string $tableName Table name |
||
| 340 | * |
||
| 341 | * @return string |
||
| 342 | */ |
||
| 343 | protected function getColumnCommentSqlDefinition(Column $column, $tableName) |
||
| 344 | { |
||
| 345 | // passing 'null' is to remove column comment |
||
| 346 | $currentComment = $this->getColumnComment($tableName, $column->getName()); |
||
| 347 | |||
| 348 | $comment = (strcasecmp($column->getComment(), 'NULL') !== 0) ? $this->getConnection()->quote($column->getComment()) : '\'\''; |
||
| 349 | $command = $currentComment === false ? 'sp_addextendedproperty' : 'sp_updateextendedproperty'; |
||
| 350 | |||
| 351 | return sprintf( |
||
| 352 | "EXECUTE %s N'MS_Description', N%s, N'SCHEMA', N'%s', N'TABLE', N'%s', N'COLUMN', N'%s';", |
||
| 353 | $command, |
||
| 354 | $comment, |
||
| 355 | $this->schema, |
||
| 356 | $tableName, |
||
| 357 | $column->getName() |
||
| 358 | ); |
||
| 359 | } |
||
| 360 | |||
| 361 | /** |
||
| 362 | * @inheritDoc |
||
| 363 | */ |
||
| 364 | View Code Duplication | protected function getRenameTableInstructions($tableName, $newTableName) |
|
| 365 | { |
||
| 366 | $this->updateCreatedTableName($tableName, $newTableName); |
||
| 367 | $sql = sprintf( |
||
| 368 | "EXEC sp_rename '%s', '%s'", |
||
| 369 | $tableName, |
||
| 370 | $newTableName |
||
| 371 | ); |
||
| 372 | |||
| 373 | return new AlterInstructions([], [$sql]); |
||
| 374 | } |
||
| 375 | |||
| 376 | /** |
||
| 377 | * @inheritDoc |
||
| 378 | */ |
||
| 379 | View Code Duplication | protected function getDropTableInstructions($tableName) |
|
| 380 | { |
||
| 381 | $this->removeCreatedTable($tableName); |
||
| 382 | $sql = sprintf('DROP TABLE %s', $this->quoteTableName($tableName)); |
||
| 383 | |||
| 384 | return new AlterInstructions([], [$sql]); |
||
| 385 | } |
||
| 386 | |||
| 387 | /** |
||
| 388 | * {@inheritDoc} |
||
| 389 | * |
||
| 390 | * @return void |
||
| 391 | */ |
||
| 392 | public function truncateTable($tableName) |
||
| 393 | { |
||
| 394 | $sql = sprintf( |
||
| 395 | 'TRUNCATE TABLE %s', |
||
| 396 | $this->quoteTableName($tableName) |
||
| 397 | ); |
||
| 398 | |||
| 399 | $this->execute($sql); |
||
| 400 | } |
||
| 401 | |||
| 402 | public function getColumnComment($tableName, $columnName) |
||
| 403 | { |
||
| 404 | $sql = sprintf("SELECT cast(extended_properties.[value] as nvarchar(4000)) comment |
||
| 405 | FROM sys.schemas |
||
| 406 | INNER JOIN sys.tables |
||
| 407 | ON schemas.schema_id = tables.schema_id |
||
| 408 | INNER JOIN sys.columns |
||
| 409 | ON tables.object_id = columns.object_id |
||
| 410 | INNER JOIN sys.extended_properties |
||
| 411 | ON tables.object_id = extended_properties.major_id |
||
| 412 | AND columns.column_id = extended_properties.minor_id |
||
| 413 | AND extended_properties.name = 'MS_Description' |
||
| 414 | WHERE schemas.[name] = '%s' AND tables.[name] = '%s' AND columns.[name] = '%s'", $this->schema, $tableName, $columnName); |
||
| 415 | $row = $this->fetchRow($sql); |
||
| 416 | |||
| 417 | if ($row) { |
||
| 418 | return trim($row['comment']); |
||
| 419 | } |
||
| 420 | |||
| 421 | return false; |
||
| 422 | } |
||
| 423 | |||
| 424 | /** |
||
| 425 | * @inheritDoc |
||
| 426 | */ |
||
| 427 | public function getColumns($tableName) |
||
| 428 | { |
||
| 429 | $columns = []; |
||
| 430 | $sql = sprintf( |
||
| 431 | "SELECT DISTINCT TABLE_SCHEMA AS [schema], TABLE_NAME as [table_name], COLUMN_NAME AS [name], DATA_TYPE AS [type], |
||
| 432 | IS_NULLABLE AS [null], COLUMN_DEFAULT AS [default], |
||
| 433 | CHARACTER_MAXIMUM_LENGTH AS [char_length], |
||
| 434 | NUMERIC_PRECISION AS [precision], |
||
| 435 | NUMERIC_SCALE AS [scale], ORDINAL_POSITION AS [ordinal_position], |
||
| 436 | COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as [identity] |
||
| 437 | FROM INFORMATION_SCHEMA.COLUMNS |
||
| 438 | WHERE TABLE_NAME = '%s' |
||
| 439 | ORDER BY ordinal_position", |
||
| 440 | $tableName |
||
| 441 | ); |
||
| 442 | $rows = $this->fetchAll($sql); |
||
| 443 | foreach ($rows as $columnInfo) { |
||
| 444 | try { |
||
| 445 | $type = $this->getPhinxType($columnInfo['type']); |
||
| 446 | } catch (UnsupportedColumnTypeException $e) { |
||
| 447 | $type = Literal::from($columnInfo['type']); |
||
| 448 | } |
||
| 449 | |||
| 450 | $column = new Column(); |
||
| 451 | $column->setName($columnInfo['name']) |
||
| 452 | ->setType($type) |
||
| 453 | ->setNull($columnInfo['null'] !== 'NO') |
||
| 454 | ->setDefault($this->parseDefault($columnInfo['default'])) |
||
| 455 | ->setIdentity($columnInfo['identity'] === '1') |
||
| 456 | ->setComment($this->getColumnComment($columnInfo['table_name'], $columnInfo['name'])); |
||
| 457 | |||
| 458 | if (!empty($columnInfo['char_length'])) { |
||
| 459 | $column->setLimit($columnInfo['char_length']); |
||
| 460 | } |
||
| 461 | |||
| 462 | $columns[$columnInfo['name']] = $column; |
||
| 463 | } |
||
| 464 | |||
| 465 | return $columns; |
||
| 466 | } |
||
| 467 | |||
| 468 | protected function parseDefault($default) |
||
| 469 | { |
||
| 470 | $default = preg_replace(["/\('(.*)'\)/", "/\(\((.*)\)\)/", "/\((.*)\)/"], '$1', $default); |
||
| 471 | |||
| 472 | if (strtoupper($default) === 'NULL') { |
||
| 473 | $default = null; |
||
| 474 | } elseif (is_numeric($default)) { |
||
| 475 | $default = (int)$default; |
||
| 476 | } |
||
| 477 | |||
| 478 | return $default; |
||
| 479 | } |
||
| 480 | |||
| 481 | /** |
||
| 482 | * @inheritDoc |
||
| 483 | */ |
||
| 484 | public function hasColumn($tableName, $columnName) |
||
| 485 | { |
||
| 486 | $sql = sprintf( |
||
| 487 | "SELECT count(*) as [count] |
||
| 488 | FROM information_schema.columns |
||
| 489 | WHERE table_name = '%s' AND column_name = '%s'", |
||
| 490 | $tableName, |
||
| 491 | $columnName |
||
| 492 | ); |
||
| 493 | $result = $this->fetchRow($sql); |
||
| 494 | |||
| 495 | return $result['count'] > 0; |
||
| 496 | } |
||
| 497 | |||
| 498 | /** |
||
| 499 | * @inheritDoc |
||
| 500 | */ |
||
| 501 | View Code Duplication | protected function getAddColumnInstructions(Table $table, Column $column) |
|
| 502 | { |
||
| 503 | $alter = sprintf( |
||
| 504 | 'ALTER TABLE %s ADD %s %s', |
||
| 505 | $table->getName(), |
||
| 506 | $this->quoteColumnName($column->getName()), |
||
| 507 | $this->getColumnSqlDefinition($column) |
||
| 508 | ); |
||
| 509 | |||
| 510 | return new AlterInstructions([], [$alter]); |
||
| 511 | } |
||
| 512 | |||
| 513 | /** |
||
| 514 | * {@inheritDoc} |
||
| 515 | * |
||
| 516 | * @throws \InvalidArgumentException |
||
| 517 | */ |
||
| 518 | protected function getRenameColumnInstructions($tableName, $columnName, $newColumnName) |
||
| 519 | { |
||
| 520 | if (!$this->hasColumn($tableName, $columnName)) { |
||
| 521 | throw new InvalidArgumentException("The specified column does not exist: $columnName"); |
||
| 522 | } |
||
| 523 | |||
| 524 | $instructions = new AlterInstructions(); |
||
| 525 | |||
| 526 | $oldConstraintName = "DF_{$tableName}_{$columnName}"; |
||
| 527 | $newConstraintName = "DF_{$tableName}_{$newColumnName}"; |
||
| 528 | $sql = <<<SQL |
||
| 529 | IF (OBJECT_ID('$oldConstraintName', 'D') IS NOT NULL) |
||
| 530 | BEGIN |
||
| 531 | EXECUTE sp_rename N'%s', N'%s', N'OBJECT' |
||
| 532 | END |
||
| 533 | SQL; |
||
| 534 | $instructions->addPostStep(sprintf( |
||
| 535 | $sql, |
||
| 536 | $oldConstraintName, |
||
| 537 | $newConstraintName |
||
| 538 | )); |
||
| 539 | |||
| 540 | $instructions->addPostStep(sprintf( |
||
| 541 | "EXECUTE sp_rename N'%s.%s', N'%s', 'COLUMN' ", |
||
| 542 | $tableName, |
||
| 543 | $columnName, |
||
| 544 | $newColumnName |
||
| 545 | )); |
||
| 546 | |||
| 547 | return $instructions; |
||
| 548 | } |
||
| 549 | |||
| 550 | /** |
||
| 551 | * Returns the instructions to change a column default value |
||
| 552 | * |
||
| 553 | * @param string $tableName The table where the column is |
||
| 554 | * @param \Phinx\Db\Table\Column $newColumn The column to alter |
||
| 555 | * |
||
| 556 | * @return \Phinx\Db\Util\AlterInstructions |
||
| 557 | */ |
||
| 558 | protected function getChangeDefault($tableName, Column $newColumn) |
||
| 559 | { |
||
| 560 | $constraintName = "DF_{$tableName}_{$newColumn->getName()}"; |
||
| 561 | $default = $newColumn->getDefault(); |
||
| 562 | $instructions = new AlterInstructions(); |
||
| 563 | |||
| 564 | if ($default === null) { |
||
| 565 | $default = 'DEFAULT NULL'; |
||
| 566 | } else { |
||
| 567 | $default = ltrim($this->getDefaultValueDefinition($default)); |
||
| 568 | } |
||
| 569 | |||
| 570 | if (empty($default)) { |
||
| 571 | return $instructions; |
||
| 572 | } |
||
| 573 | |||
| 574 | $instructions->addPostStep(sprintf( |
||
| 575 | 'ALTER TABLE %s ADD CONSTRAINT %s %s FOR %s', |
||
| 576 | $this->quoteTableName($tableName), |
||
| 577 | $constraintName, |
||
| 578 | $default, |
||
| 579 | $this->quoteColumnName($newColumn->getName()) |
||
| 580 | )); |
||
| 581 | |||
| 582 | return $instructions; |
||
| 583 | } |
||
| 584 | |||
| 585 | /** |
||
| 586 | * @inheritDoc |
||
| 587 | */ |
||
| 588 | protected function getChangeColumnInstructions($tableName, $columnName, Column $newColumn) |
||
| 589 | { |
||
| 590 | $columns = $this->getColumns($tableName); |
||
| 591 | $changeDefault = |
||
| 592 | $newColumn->getDefault() !== $columns[$columnName]->getDefault() || |
||
| 593 | $newColumn->getType() !== $columns[$columnName]->getType(); |
||
| 594 | |||
| 595 | $instructions = new AlterInstructions(); |
||
| 596 | |||
| 597 | if ($columnName !== $newColumn->getName()) { |
||
| 598 | $instructions->merge( |
||
| 599 | $this->getRenameColumnInstructions($tableName, $columnName, $newColumn->getName()) |
||
| 600 | ); |
||
| 601 | } |
||
| 602 | |||
| 603 | if ($changeDefault) { |
||
| 604 | $instructions->merge($this->getDropDefaultConstraint($tableName, $newColumn->getName())); |
||
| 605 | } |
||
| 606 | |||
| 607 | $instructions->addPostStep(sprintf( |
||
| 608 | 'ALTER TABLE %s ALTER COLUMN %s %s', |
||
| 609 | $this->quoteTableName($tableName), |
||
| 610 | $this->quoteColumnName($newColumn->getName()), |
||
| 611 | $this->getColumnSqlDefinition($newColumn, false) |
||
| 612 | )); |
||
| 613 | // change column comment if needed |
||
| 614 | if ($newColumn->getComment()) { |
||
| 615 | $instructions->addPostStep($this->getColumnCommentSqlDefinition($newColumn, $tableName)); |
||
| 616 | } |
||
| 617 | |||
| 618 | if ($changeDefault) { |
||
| 619 | $instructions->merge($this->getChangeDefault($tableName, $newColumn)); |
||
| 620 | } |
||
| 621 | |||
| 622 | return $instructions; |
||
| 623 | } |
||
| 624 | |||
| 625 | /** |
||
| 626 | * @inheritDoc |
||
| 627 | */ |
||
| 628 | protected function getDropColumnInstructions($tableName, $columnName) |
||
| 629 | { |
||
| 630 | $instructions = $this->getDropDefaultConstraint($tableName, $columnName); |
||
| 631 | |||
| 632 | $instructions->addPostStep(sprintf( |
||
| 633 | 'ALTER TABLE %s DROP COLUMN %s', |
||
| 634 | $this->quoteTableName($tableName), |
||
| 635 | $this->quoteColumnName($columnName) |
||
| 636 | )); |
||
| 637 | |||
| 638 | return $instructions; |
||
| 639 | } |
||
| 640 | |||
| 641 | /** |
||
| 642 | * @inheritDoc |
||
| 643 | */ |
||
| 644 | protected function getDropDefaultConstraint($tableName, $columnName) |
||
| 645 | { |
||
| 646 | $defaultConstraint = $this->getDefaultConstraint($tableName, $columnName); |
||
| 647 | |||
| 648 | if (!$defaultConstraint) { |
||
| 649 | return new AlterInstructions(); |
||
| 650 | } |
||
| 651 | |||
| 652 | return $this->getDropForeignKeyInstructions($tableName, $defaultConstraint); |
||
| 653 | } |
||
| 654 | |||
| 655 | protected function getDefaultConstraint($tableName, $columnName) |
||
| 656 | { |
||
| 657 | $sql = "SELECT |
||
| 658 | default_constraints.name |
||
| 659 | FROM |
||
| 660 | sys.all_columns |
||
| 661 | |||
| 662 | INNER JOIN |
||
| 663 | sys.tables |
||
| 664 | ON all_columns.object_id = tables.object_id |
||
| 665 | |||
| 666 | INNER JOIN |
||
| 667 | sys.schemas |
||
| 668 | ON tables.schema_id = schemas.schema_id |
||
| 669 | |||
| 670 | INNER JOIN |
||
| 671 | sys.default_constraints |
||
| 672 | ON all_columns.default_object_id = default_constraints.object_id |
||
| 673 | |||
| 674 | WHERE |
||
| 675 | schemas.name = 'dbo' |
||
| 676 | AND tables.name = '{$tableName}' |
||
| 677 | AND all_columns.name = '{$columnName}'"; |
||
| 678 | |||
| 679 | $rows = $this->fetchAll($sql); |
||
| 680 | |||
| 681 | return empty($rows) ? false : $rows[0]['name']; |
||
| 682 | } |
||
| 683 | |||
| 684 | protected function getIndexColums($tableId, $indexId) |
||
| 685 | { |
||
| 686 | $sql = "SELECT AC.[name] AS [column_name] |
||
| 687 | FROM sys.[index_columns] IC |
||
| 688 | INNER JOIN sys.[all_columns] AC ON IC.[column_id] = AC.[column_id] |
||
| 689 | WHERE AC.[object_id] = {$tableId} AND IC.[index_id] = {$indexId} AND IC.[object_id] = {$tableId} |
||
| 690 | ORDER BY IC.[key_ordinal];"; |
||
| 691 | |||
| 692 | $rows = $this->fetchAll($sql); |
||
| 693 | $columns = []; |
||
| 694 | foreach ($rows as $row) { |
||
| 695 | $columns[] = strtolower($row['column_name']); |
||
| 696 | } |
||
| 697 | |||
| 698 | return $columns; |
||
| 699 | } |
||
| 700 | |||
| 701 | /** |
||
| 702 | * Get an array of indexes from a particular table. |
||
| 703 | * |
||
| 704 | * @param string $tableName Table Name |
||
| 705 | * |
||
| 706 | * @return array |
||
| 707 | */ |
||
| 708 | public function getIndexes($tableName) |
||
| 725 | |||
| 726 | /** |
||
| 727 | * @inheritDoc |
||
| 728 | */ |
||
| 729 | View Code Duplication | public function hasIndex($tableName, $columns) |
|
| 748 | |||
| 749 | /** |
||
| 750 | * @inheritDoc |
||
| 751 | */ |
||
| 752 | View Code Duplication | public function hasIndexByName($tableName, $indexName) |
|
| 764 | |||
| 765 | /** |
||
| 766 | * @inheritDoc |
||
| 767 | */ |
||
| 768 | protected function getAddIndexInstructions(Table $table, Index $index) |
||
| 774 | |||
| 775 | /** |
||
| 776 | * {@inheritDoc} |
||
| 777 | * |
||
| 778 | * @throws \InvalidArgumentException |
||
| 779 | */ |
||
| 780 | protected function getDropIndexByColumnsInstructions($tableName, $columns) |
||
| 781 | { |
||
| 782 | if (is_string($columns)) { |
||
| 783 | $columns = [$columns]; // str to array |
||
| 784 | } |
||
| 785 | |||
| 786 | $indexes = $this->getIndexes($tableName); |
||
| 787 | $columns = array_map('strtolower', $columns); |
||
| 788 | $instructions = new AlterInstructions(); |
||
| 789 | |||
| 790 | foreach ($indexes as $indexName => $index) { |
||
| 791 | $a = array_diff($columns, $index['columns']); |
||
| 792 | View Code Duplication | if (empty($a)) { |
|
| 793 | $instructions->addPostStep(sprintf( |
||
| 794 | 'DROP INDEX %s ON %s', |
||
| 808 | |||
| 809 | /** |
||
| 810 | * {@inheritDoc} |
||
| 811 | * |
||
| 812 | * @throws \InvalidArgumentException |
||
| 813 | */ |
||
| 814 | protected function getDropIndexByNameInstructions($tableName, $indexName) |
||
| 836 | |||
| 837 | /** |
||
| 838 | * @inheritDoc |
||
| 839 | */ |
||
| 840 | View Code Duplication | public function hasPrimaryKey($tableName, $columns, $constraint = null) |
|
| 859 | |||
| 860 | /** |
||
| 861 | * Get the primary key from a particular table. |
||
| 862 | * |
||
| 863 | * @param string $tableName Table Name |
||
| 864 | * |
||
| 865 | * @return array |
||
| 866 | */ |
||
| 867 | View Code Duplication | public function getPrimaryKey($tableName) |
|
| 892 | |||
| 893 | /** |
||
| 894 | * @inheritDoc |
||
| 895 | */ |
||
| 896 | View Code Duplication | public function hasForeignKey($tableName, $columns, $constraint = null) |
|
| 897 | { |
||
| 898 | if (is_string($columns)) { |
||
| 899 | $columns = [$columns]; // str to array |
||
| 900 | } |
||
| 901 | $foreignKeys = $this->getForeignKeys($tableName); |
||
| 902 | if ($constraint) { |
||
| 903 | if (isset($foreignKeys[$constraint])) { |
||
| 904 | return !empty($foreignKeys[$constraint]); |
||
| 905 | } |
||
| 906 | |||
| 907 | return false; |
||
| 908 | } else { |
||
| 909 | foreach ($foreignKeys as $key) { |
||
| 910 | $a = array_diff($columns, $key['columns']); |
||
| 911 | if (empty($a)) { |
||
| 912 | return true; |
||
| 913 | } |
||
| 914 | } |
||
| 915 | |||
| 916 | return false; |
||
| 917 | } |
||
| 918 | } |
||
| 919 | |||
| 920 | /** |
||
| 921 | * Get an array of foreign keys from a particular table. |
||
| 922 | * |
||
| 923 | * @param string $tableName Table Name |
||
| 924 | * |
||
| 925 | * @return array |
||
| 926 | */ |
||
| 927 | View Code Duplication | protected function getForeignKeys($tableName) |
|
| 953 | |||
| 954 | /** |
||
| 955 | * @inheritDoc |
||
| 956 | */ |
||
| 957 | protected function getAddForeignKeyInstructions(Table $table, ForeignKey $foreignKey) |
||
| 968 | |||
| 969 | /** |
||
| 970 | * @inheritDoc |
||
| 971 | */ |
||
| 972 | protected function getDropForeignKeyInstructions($tableName, $constraint) |
||
| 983 | |||
| 984 | /** |
||
| 985 | * @inheritDoc |
||
| 986 | */ |
||
| 987 | protected function getDropForeignKeyByColumnsInstructions($tableName, $columns) |
||
| 1016 | |||
| 1017 | /** |
||
| 1018 | * {@inheritDoc} |
||
| 1019 | * |
||
| 1020 | * @throws \Phinx\Db\Adapter\UnsupportedColumnTypeException |
||
| 1021 | */ |
||
| 1022 | public function getSqlType($type, $limit = null) |
||
| 1066 | |||
| 1067 | /** |
||
| 1068 | * Returns Phinx type by SQL type |
||
| 1069 | * |
||
| 1070 | * @internal param string $sqlType SQL type |
||
| 1071 | * |
||
| 1072 | * @param string $sqlType SQL Type definition |
||
| 1073 | * |
||
| 1074 | * @throws \Phinx\Db\Adapter\UnsupportedColumnTypeException |
||
| 1075 | * |
||
| 1076 | * @return string Phinx type |
||
| 1077 | */ |
||
| 1078 | public function getPhinxType($sqlType) |
||
| 1125 | |||
| 1126 | /** |
||
| 1127 | * {@inheritDoc} |
||
| 1128 | * |
||
| 1129 | * @return void |
||
| 1130 | */ |
||
| 1131 | public function createDatabase($name, $options = []) |
||
| 1140 | |||
| 1141 | /** |
||
| 1142 | * @inheritDoc |
||
| 1143 | */ |
||
| 1144 | public function hasDatabase($name) |
||
| 1155 | |||
| 1156 | /** |
||
| 1157 | * {@inheritDoc} |
||
| 1158 | * |
||
| 1159 | * @return void |
||
| 1160 | */ |
||
| 1161 | public function dropDatabase($name) |
||
| 1171 | |||
| 1172 | /** |
||
| 1173 | * Gets the SqlServer Column Definition for a Column object. |
||
| 1174 | * |
||
| 1175 | * @param \Phinx\Db\Table\Column $column Column |
||
| 1176 | * @param bool $create Create column flag |
||
| 1177 | * |
||
| 1178 | * @return string |
||
| 1179 | */ |
||
| 1180 | protected function getColumnSqlDefinition(Column $column, $create = true) |
||
| 1227 | |||
| 1228 | /** |
||
| 1229 | * Gets the SqlServer Index Definition for an Index object. |
||
| 1230 | * |
||
| 1231 | * @param \Phinx\Db\Table\Index $index Index |
||
| 1232 | * @param string $tableName Table name |
||
| 1233 | * |
||
| 1234 | * @return string |
||
| 1235 | */ |
||
| 1236 | protected function getIndexSqlDefinition(Index $index, $tableName) |
||
| 1253 | |||
| 1254 | /** |
||
| 1255 | * Gets the SqlServer Foreign Key Definition for an ForeignKey object. |
||
| 1256 | * |
||
| 1257 | * @param \Phinx\Db\Table\ForeignKey $foreignKey Foreign key |
||
| 1258 | * @param string $tableName Table name |
||
| 1259 | * |
||
| 1260 | * @return string |
||
| 1261 | */ |
||
| 1262 | protected function getForeignKeySqlDefinition(ForeignKey $foreignKey, $tableName) |
||
| 1277 | |||
| 1278 | /** |
||
| 1279 | * @inheritDoc |
||
| 1280 | */ |
||
| 1281 | public function getColumnTypes() |
||
| 1285 | |||
| 1286 | /** |
||
| 1287 | * Records a migration being run. |
||
| 1288 | * |
||
| 1289 | * @param \Phinx\Migration\MigrationInterface $migration Migration |
||
| 1290 | * @param string $direction Direction |
||
| 1291 | * @param int $startTime Start Time |
||
| 1292 | * @param int $endTime End Time |
||
| 1293 | * |
||
| 1294 | * @return \Phinx\Db\Adapter\AdapterInterface |
||
| 1295 | */ |
||
| 1296 | public function migrated(MigrationInterface $migration, $direction, $startTime, $endTime) |
||
| 1303 | |||
| 1304 | /** |
||
| 1305 | * @inheritDoc |
||
| 1306 | */ |
||
| 1307 | View Code Duplication | public function getDecoratedConnection() |
|
| 1327 | } |
||
| 1328 |
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.