yiisoft /
db-oracle
| Total Complexity | 92 |
| Total Lines | 890 |
| Duplicated Lines | 0 % |
| Coverage | 95.45% |
| Changes | 1 | ||
| Bugs | 0 | Features | 0 |
Complex classes like SchemaPDOOracle 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.
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 SchemaPDOOracle, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 62 | final class SchemaPDOOracle extends Schema |
||
| 63 | { |
||
| 64 | 350 | public function __construct(private ConnectionPDOInterface $db, SchemaCache $schemaCache) |
|
| 65 | { |
||
| 66 | 350 | $this->defaultSchema = strtoupper($db->getDriver()->getUsername()); |
|
| 67 | 350 | parent::__construct($schemaCache); |
|
| 68 | } |
||
| 69 | |||
| 70 | 70 | protected function resolveTableName(string $name): TableSchema |
|
| 71 | { |
||
| 72 | 70 | $resolvedName = new TableSchema(); |
|
| 73 | |||
| 74 | 70 | $parts = explode('.', str_replace('"', '', $name)); |
|
| 75 | |||
| 76 | 70 | if (isset($parts[1])) { |
|
| 77 | $resolvedName->schemaName($parts[0]); |
||
| 78 | $resolvedName->name($parts[1]); |
||
| 79 | } else { |
||
| 80 | 70 | $resolvedName->schemaName($this->defaultSchema); |
|
| 81 | 70 | $resolvedName->name($name); |
|
| 82 | } |
||
| 83 | |||
| 84 | 70 | $fullName = ($resolvedName->getSchemaName() !== $this->defaultSchema |
|
| 85 | 70 | ? (string) $resolvedName->getSchemaName() . '.' : '') . $resolvedName->getName(); |
|
| 86 | |||
| 87 | 70 | $resolvedName->fullName($fullName); |
|
| 88 | |||
| 89 | 70 | return $resolvedName; |
|
| 90 | } |
||
| 91 | |||
| 92 | /** |
||
| 93 | * @see https://docs.oracle.com/cd/B28359_01/server.111/b28337/tdpsg_user_accounts.htm |
||
| 94 | */ |
||
| 95 | 1 | protected function findSchemaNames(): array |
|
| 96 | { |
||
| 97 | 1 | $sql = <<<SQL |
|
| 98 | SELECT "u"."USERNAME" |
||
| 99 | FROM "DBA_USERS" "u" |
||
| 100 | WHERE "u"."DEFAULT_TABLESPACE" NOT IN ('SYSTEM', 'SYSAUX') |
||
| 101 | ORDER BY "u"."USERNAME" ASC |
||
| 102 | SQL; |
||
| 103 | |||
| 104 | 1 | return $this->db->createCommand($sql)->queryColumn(); |
|
|
Bug
Best Practice
introduced
by
|
|||
| 105 | } |
||
| 106 | |||
| 107 | /** |
||
| 108 | * @param string $schema |
||
| 109 | * |
||
| 110 | * @throws Exception|InvalidConfigException|Throwable |
||
| 111 | * |
||
| 112 | * @return array |
||
| 113 | */ |
||
| 114 | 5 | protected function findTableNames(string $schema = ''): array |
|
| 115 | { |
||
| 116 | 5 | if ($schema === '') { |
|
| 117 | 5 | $sql = <<<SQL |
|
| 118 | SELECT TABLE_NAME |
||
| 119 | FROM USER_TABLES |
||
| 120 | UNION ALL |
||
| 121 | SELECT VIEW_NAME AS TABLE_NAME |
||
| 122 | FROM USER_VIEWS |
||
| 123 | UNION ALL |
||
| 124 | SELECT MVIEW_NAME AS TABLE_NAME |
||
| 125 | FROM USER_MVIEWS |
||
| 126 | ORDER BY TABLE_NAME |
||
| 127 | SQL; |
||
| 128 | |||
| 129 | 5 | $command = $this->db->createCommand($sql); |
|
| 130 | } else { |
||
| 131 | $sql = <<<SQL |
||
| 132 | SELECT OBJECT_NAME AS TABLE_NAME |
||
| 133 | FROM ALL_OBJECTS |
||
| 134 | WHERE OBJECT_TYPE IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW') AND OWNER = :schema |
||
| 135 | ORDER BY OBJECT_NAME |
||
| 136 | SQL; |
||
| 137 | $command = $this->db->createCommand($sql, [':schema' => $schema]); |
||
| 138 | } |
||
| 139 | |||
| 140 | 5 | $rows = $command->queryAll(); |
|
| 141 | 5 | $names = []; |
|
| 142 | |||
| 143 | /** @psalm-var string[][] $rows */ |
||
| 144 | 5 | foreach ($rows as $row) { |
|
| 145 | 5 | if ($this->db->getActivePDO()?->getAttribute(PDO::ATTR_CASE) === PDO::CASE_LOWER) { |
|
| 146 | 1 | $row = array_change_key_case($row, CASE_UPPER); |
|
| 147 | } |
||
| 148 | 5 | $names[] = $row['TABLE_NAME']; |
|
| 149 | } |
||
| 150 | |||
| 151 | 5 | return $names; |
|
| 152 | } |
||
| 153 | |||
| 154 | /** |
||
| 155 | * @param string $name |
||
| 156 | * |
||
| 157 | * @throws Exception|InvalidConfigException|Throwable |
||
| 158 | * |
||
| 159 | * @return TableSchema|null |
||
| 160 | */ |
||
| 161 | 84 | protected function loadTableSchema(string $name): ?TableSchema |
|
| 162 | { |
||
| 163 | 84 | $table = new TableSchema(); |
|
| 164 | |||
| 165 | 84 | $this->resolveTableNames($table, $name); |
|
| 166 | |||
| 167 | 84 | if ($this->findColumns($table)) { |
|
| 168 | 71 | $this->findConstraints($table); |
|
| 169 | 71 | return $table; |
|
| 170 | } |
||
| 171 | |||
| 172 | 21 | return null; |
|
| 173 | } |
||
| 174 | |||
| 175 | /** |
||
| 176 | * @param string $tableName |
||
| 177 | * |
||
| 178 | * @throws Exception|InvalidConfigException|NotSupportedException|Throwable |
||
| 179 | * |
||
| 180 | * @return Constraint|null |
||
| 181 | */ |
||
| 182 | 30 | protected function loadTablePrimaryKey(string $tableName): ?Constraint |
|
| 183 | { |
||
| 184 | /** @var mixed */ |
||
| 185 | 30 | $tablePrimaryKey = $this->loadTableConstraints($tableName, self::PRIMARY_KEY); |
|
| 186 | 30 | return $tablePrimaryKey instanceof Constraint ? $tablePrimaryKey : null; |
|
| 187 | } |
||
| 188 | |||
| 189 | /** |
||
| 190 | * @param string $tableName |
||
| 191 | * |
||
| 192 | * @throws Exception|InvalidConfigException|NotSupportedException|Throwable |
||
| 193 | * |
||
| 194 | * @return array |
||
| 195 | */ |
||
| 196 | 4 | protected function loadTableForeignKeys(string $tableName): array |
|
| 201 | } |
||
| 202 | |||
| 203 | /** |
||
| 204 | * @param string $tableName |
||
| 205 | * |
||
| 206 | * @throws Exception|InvalidConfigException|NotSupportedException|Throwable |
||
| 207 | * |
||
| 208 | * @return array |
||
| 209 | */ |
||
| 210 | 27 | protected function loadTableIndexes(string $tableName): array |
|
| 211 | { |
||
| 212 | 27 | $sql = <<<SQL |
|
| 213 | SELECT "ui"."INDEX_NAME" AS "name", "uicol"."COLUMN_NAME" AS "column_name", |
||
| 214 | CASE "ui"."UNIQUENESS" WHEN 'UNIQUE' THEN 1 ELSE 0 END AS "index_is_unique", |
||
| 215 | CASE WHEN "uc"."CONSTRAINT_NAME" IS NOT NULL THEN 1 ELSE 0 END AS "index_is_primary" |
||
| 216 | FROM "SYS"."USER_INDEXES" "ui" |
||
| 217 | LEFT JOIN "SYS"."USER_IND_COLUMNS" "uicol" |
||
| 218 | ON "uicol"."INDEX_NAME" = "ui"."INDEX_NAME" |
||
| 219 | LEFT JOIN "SYS"."USER_CONSTRAINTS" "uc" |
||
| 220 | ON "uc"."OWNER" = "ui"."TABLE_OWNER" AND "uc"."CONSTRAINT_NAME" = "ui"."INDEX_NAME" AND "uc"."CONSTRAINT_TYPE" = 'P' |
||
| 221 | WHERE "ui"."TABLE_OWNER" = :schemaName AND "ui"."TABLE_NAME" = :tableName |
||
| 222 | ORDER BY "uicol"."COLUMN_POSITION" ASC |
||
| 223 | SQL; |
||
| 224 | |||
| 225 | 27 | $resolvedName = $this->resolveTableName($tableName); |
|
| 226 | |||
| 227 | 27 | $indexes = $this->db->createCommand($sql, [ |
|
| 228 | 27 | ':schemaName' => $resolvedName->getSchemaName(), |
|
| 229 | 27 | ':tableName' => $resolvedName->getName(), |
|
| 230 | 27 | ])->queryAll(); |
|
| 231 | |||
| 232 | /** @psalm-var array[] $indexes */ |
||
| 233 | 27 | $indexes = $this->normalizePdoRowKeyCase($indexes, true); |
|
| 234 | 27 | $indexes = ArrayHelper::index($indexes, null, 'name'); |
|
| 235 | |||
| 236 | 27 | $result = []; |
|
| 237 | |||
| 238 | /** |
||
| 239 | * @psalm-var object|string|null $name |
||
| 240 | * @psalm-var array[] $index |
||
| 241 | */ |
||
| 242 | 27 | foreach ($indexes as $name => $index) { |
|
| 243 | 24 | $columnNames = ArrayHelper::getColumn($index, 'column_name'); |
|
| 244 | |||
| 245 | 24 | if ($columnNames[0] === null) { |
|
| 246 | 17 | $columnNames[0] = ''; |
|
| 247 | } |
||
| 248 | |||
| 249 | 24 | $result[] = (new IndexConstraint()) |
|
| 250 | 24 | ->primary((bool) $index[0]['index_is_primary']) |
|
| 251 | 24 | ->unique((bool) $index[0]['index_is_unique']) |
|
| 252 | 24 | ->name($name) |
|
| 253 | 24 | ->columnNames($columnNames); |
|
| 254 | } |
||
| 255 | |||
| 256 | 27 | return $result; |
|
| 257 | } |
||
| 258 | |||
| 259 | /** |
||
| 260 | * @param string $tableName |
||
| 261 | * |
||
| 262 | * @throws Exception|InvalidConfigException|NotSupportedException|Throwable |
||
| 263 | * |
||
| 264 | * @return array |
||
| 265 | */ |
||
| 266 | 13 | protected function loadTableUniques(string $tableName): array |
|
| 267 | { |
||
| 268 | /** @var mixed */ |
||
| 269 | 13 | $tableUniques = $this->loadTableConstraints($tableName, self::UNIQUES); |
|
| 270 | 13 | return is_array($tableUniques) ? $tableUniques : []; |
|
| 271 | } |
||
| 272 | |||
| 273 | /** |
||
| 274 | * @param string $tableName |
||
| 275 | * |
||
| 276 | * @throws Exception|InvalidConfigException|NotSupportedException|Throwable |
||
| 277 | * |
||
| 278 | * @return array |
||
| 279 | */ |
||
| 280 | 13 | protected function loadTableChecks(string $tableName): array |
|
| 281 | { |
||
| 282 | /** @var mixed */ |
||
| 283 | 13 | $tableCheck = $this->loadTableConstraints($tableName, self::CHECKS); |
|
| 284 | 13 | return is_array($tableCheck) ? $tableCheck : []; |
|
| 285 | } |
||
| 286 | |||
| 287 | /** |
||
| 288 | * @param string $tableName |
||
| 289 | * |
||
| 290 | * @throws NotSupportedException if this method is called. |
||
| 291 | * |
||
| 292 | * @return array |
||
| 293 | */ |
||
| 294 | 12 | protected function loadTableDefaultValues(string $tableName): array |
|
| 295 | { |
||
| 296 | 12 | throw new NotSupportedException('Oracle does not support default value constraints.'); |
|
| 297 | } |
||
| 298 | |||
| 299 | public function releaseSavepoint(string $name): void |
||
| 301 | /* does nothing as Oracle does not support this */ |
||
| 302 | } |
||
| 303 | |||
| 304 | /** |
||
| 305 | * Create a column schema builder instance giving the type and value precision. |
||
| 306 | * |
||
| 307 | * This method may be overridden by child classes to create a DBMS-specific column schema builder. |
||
| 308 | * |
||
| 309 | * @param string $type type of the column. See {@see ColumnSchemaBuilder::$type}. |
||
| 310 | * @param array|int|string|null $length length or precision of the column {@see ColumnSchemaBuilder::$length}. |
||
| 311 | * |
||
| 312 | * @return ColumnSchemaBuilder column schema builder instance |
||
| 313 | * |
||
| 314 | * @psalm-param string[]|int|string|null $length |
||
| 315 | */ |
||
| 316 | 3 | public function createColumnSchemaBuilder(string $type, array|int|string $length = null): ColumnSchemaBuilder |
|
| 317 | { |
||
| 318 | 3 | return new ColumnSchemaBuilder($type, $length); |
|
| 319 | } |
||
| 320 | |||
| 321 | /** |
||
| 322 | * Resolves the table name and schema name (if any). |
||
| 323 | * |
||
| 324 | * @param TableSchema $table the table metadata object |
||
| 325 | * @param string $name the table name |
||
| 326 | */ |
||
| 327 | 84 | protected function resolveTableNames(TableSchema $table, string $name): void |
|
| 328 | { |
||
| 329 | 84 | $parts = explode('.', str_replace('"', '', $name)); |
|
| 330 | |||
| 331 | 84 | if (isset($parts[1])) { |
|
| 332 | $table->schemaName($parts[0]); |
||
| 333 | $table->name($parts[1]); |
||
| 334 | } else { |
||
| 335 | 84 | $table->schemaName($this->defaultSchema); |
|
| 336 | 84 | $table->name($name); |
|
| 337 | } |
||
| 338 | |||
| 339 | 84 | $table->fullName($table->getSchemaName() !== $this->defaultSchema |
|
| 340 | 84 | ? (string) $table->getSchemaName() . '.' . $table->getName() : $table->getName()); |
|
| 341 | } |
||
| 342 | |||
| 343 | /** |
||
| 344 | * Collects the table column metadata. |
||
| 345 | * |
||
| 346 | * @param TableSchema $table the table schema. |
||
| 347 | * |
||
| 348 | * @throws Exception|Throwable |
||
| 349 | * |
||
| 350 | * @return bool whether the table exists. |
||
| 351 | */ |
||
| 352 | 84 | protected function findColumns(TableSchema $table): bool |
|
| 403 | } |
||
| 404 | |||
| 405 | /** |
||
| 406 | * Sequence name of table. |
||
| 407 | * |
||
| 408 | * @param string $tableName |
||
| 409 | * |
||
| 410 | * @throws Exception|InvalidConfigException|Throwable |
||
| 411 | * |
||
| 412 | * @return bool|int|string|null whether the sequence exists. |
||
| 413 | * |
||
| 414 | * @internal TableSchema `$table->getName()` the table schema. |
||
| 415 | */ |
||
| 416 | 53 | protected function getTableSequenceName(string $tableName): bool|string|int|null |
|
| 431 | } |
||
| 432 | |||
| 433 | /** |
||
| 434 | * @Overrides method in class 'Schema' |
||
| 435 | * |
||
| 436 | * {@see https://secure.php.net/manual/en/function.PDO-lastInsertId.php} -> Oracle does not support this. |
||
| 437 | * |
||
| 438 | * Returns the ID of the last inserted row or sequence value. |
||
| 439 | * |
||
| 440 | * @param string $sequenceName name of the sequence object (required by some DBMS) |
||
| 441 | * |
||
| 442 | * @throws Exception|InvalidCallException|InvalidConfigException|Throwable if the DB connection is not active. |
||
| 443 | * |
||
| 444 | * @return string the row ID of the last row inserted, or the last value retrieved from the sequence object. |
||
| 445 | */ |
||
| 446 | 3 | public function getLastInsertID(string $sequenceName = ''): string |
|
| 447 | { |
||
| 448 | 3 | if ($this->db->isActive()) { |
|
| 449 | /* get the last insert id from connection */ |
||
| 450 | 3 | $sequenceName = $this->db->getQuoter()->quoteSimpleTableName($sequenceName); |
|
| 451 | |||
| 452 | 3 | return $this->db->createCommand("SELECT $sequenceName.CURRVAL FROM DUAL")->queryScalar(); |
|
| 453 | } |
||
| 454 | |||
| 455 | throw new InvalidCallException('DB Connection is not active.'); |
||
| 456 | } |
||
| 457 | |||
| 458 | /** |
||
| 459 | * Creates ColumnSchema instance. |
||
| 460 | * |
||
| 461 | * @param array|string $column |
||
| 462 | * |
||
| 463 | * @return ColumnSchema |
||
| 464 | */ |
||
| 465 | 71 | protected function createColumn(array|string $column): ColumnSchema |
|
| 466 | { |
||
| 467 | 71 | $c = $this->createColumnSchema(); |
|
| 468 | |||
| 469 | /** |
||
| 470 | * @psalm-var array{ |
||
| 471 | * COLUMN_NAME: string, |
||
| 472 | * DATA_TYPE: string, |
||
| 473 | * DATA_PRECISION: string, |
||
| 474 | * DATA_SCALE: string, |
||
| 475 | * DATA_LENGTH: string, |
||
| 476 | * NULLABLE: string, |
||
| 477 | * DATA_DEFAULT: string|null, |
||
| 478 | * COLUMN_COMMENT: string|null |
||
| 479 | * } $column |
||
| 480 | */ |
||
| 481 | 71 | $c->name($column['COLUMN_NAME']); |
|
| 482 | 71 | $c->allowNull($column['NULLABLE'] === 'Y'); |
|
| 483 | 71 | $c->comment($column['COLUMN_COMMENT'] ?? ''); |
|
| 484 | 71 | $c->primaryKey(false); |
|
| 485 | |||
| 486 | 71 | $this->extractColumnType( |
|
| 487 | $c, |
||
| 488 | 71 | $column['DATA_TYPE'], |
|
| 489 | 71 | $column['DATA_PRECISION'], |
|
| 490 | 71 | $column['DATA_SCALE'], |
|
| 491 | 71 | $column['DATA_LENGTH'] |
|
| 492 | ); |
||
| 493 | |||
| 494 | 71 | $this->extractColumnSize( |
|
| 495 | $c, |
||
| 496 | 71 | $column['DATA_TYPE'], |
|
| 497 | 71 | $column['DATA_PRECISION'], |
|
| 498 | 71 | $column['DATA_SCALE'], |
|
| 499 | 71 | $column['DATA_LENGTH'] |
|
| 500 | ); |
||
| 501 | |||
| 502 | 71 | $c->phpType($this->getColumnPhpType($c)); |
|
| 503 | |||
| 504 | 71 | if (!$c->isPrimaryKey()) { |
|
| 505 | 71 | if ($column['DATA_DEFAULT'] !== null && stripos($column['DATA_DEFAULT'], 'timestamp') !== false) { |
|
| 506 | 14 | $c->defaultValue(null); |
|
| 507 | } else { |
||
| 508 | 71 | $defaultValue = $column['DATA_DEFAULT']; |
|
| 509 | |||
| 510 | 71 | if ($c->getType() === 'timestamp' && $defaultValue === 'CURRENT_TIMESTAMP') { |
|
| 511 | $c->defaultValue(new Expression('CURRENT_TIMESTAMP')); |
||
| 512 | } else { |
||
| 513 | 71 | if ($defaultValue !== null) { |
|
| 514 | 52 | if (($len = strlen($defaultValue)) > 2 && $defaultValue[0] === "'" |
|
| 515 | 52 | && $defaultValue[$len - 1] === "'" |
|
| 516 | ) { |
||
| 517 | 14 | $defaultValue = substr((string) $column['DATA_DEFAULT'], 1, -1); |
|
| 518 | } else { |
||
| 519 | 52 | $defaultValue = trim($defaultValue); |
|
| 520 | } |
||
| 521 | } |
||
| 522 | 71 | $c->defaultValue($c->phpTypecast($defaultValue)); |
|
| 523 | } |
||
| 524 | } |
||
| 525 | } |
||
| 526 | |||
| 527 | 71 | return $c; |
|
| 528 | } |
||
| 529 | |||
| 530 | /** |
||
| 531 | * Finds constraints and fills them into TableSchema object passed. |
||
| 532 | * |
||
| 533 | * @param TableSchema $table |
||
| 534 | * |
||
| 535 | * @throws Exception|InvalidConfigException|Throwable |
||
| 536 | * |
||
| 537 | * @psalm-suppress PossiblyNullArrayOffset |
||
| 538 | */ |
||
| 539 | 71 | protected function findConstraints(TableSchema $table): void |
|
| 540 | { |
||
| 541 | 71 | $sql = <<<SQL |
|
| 542 | SELECT |
||
| 543 | /*+ PUSH_PRED(C) PUSH_PRED(D) PUSH_PRED(E) */ |
||
| 544 | D.CONSTRAINT_NAME, |
||
| 545 | D.CONSTRAINT_TYPE, |
||
| 546 | C.COLUMN_NAME, |
||
| 547 | C.POSITION, |
||
| 548 | D.R_CONSTRAINT_NAME, |
||
| 549 | E.TABLE_NAME AS TABLE_REF, |
||
| 550 | F.COLUMN_NAME AS COLUMN_REF, |
||
| 551 | C.TABLE_NAME |
||
| 552 | FROM ALL_CONS_COLUMNS C |
||
| 553 | INNER JOIN ALL_CONSTRAINTS D ON D.OWNER = C.OWNER AND D.CONSTRAINT_NAME = C.CONSTRAINT_NAME |
||
| 554 | LEFT JOIN ALL_CONSTRAINTS E ON E.OWNER = D.R_OWNER AND E.CONSTRAINT_NAME = D.R_CONSTRAINT_NAME |
||
| 555 | LEFT JOIN ALL_CONS_COLUMNS F ON F.OWNER = E.OWNER AND F.CONSTRAINT_NAME = E.CONSTRAINT_NAME AND F.POSITION = C.POSITION |
||
| 556 | WHERE |
||
| 557 | C.OWNER = :schemaName |
||
| 558 | AND C.TABLE_NAME = :tableName |
||
| 559 | ORDER BY D.CONSTRAINT_NAME, C.POSITION |
||
| 560 | SQL; |
||
| 561 | |||
| 562 | /** |
||
| 563 | * @psalm-var array{ |
||
| 564 | * array{ |
||
| 565 | * CONSTRAINT_NAME: string, |
||
| 566 | * CONSTRAINT_TYPE: string, |
||
| 567 | * COLUMN_NAME: string, |
||
| 568 | * POSITION: string|null, |
||
| 569 | * R_CONSTRAINT_NAME: string|null, |
||
| 570 | * TABLE_REF: string|null, |
||
| 571 | * COLUMN_REF: string|null, |
||
| 572 | * TABLE_NAME: string |
||
| 573 | * } |
||
| 574 | * } $rows |
||
| 575 | */ |
||
| 576 | 71 | $rows = $this->db->createCommand( |
|
| 577 | $sql, |
||
| 578 | 71 | [':tableName' => $table->getName(), ':schemaName' => $table->getSchemaName()] |
|
| 579 | 71 | )->queryAll(); |
|
| 580 | |||
| 581 | 71 | $constraints = []; |
|
| 582 | |||
| 583 | 71 | foreach ($rows as $row) { |
|
| 584 | 65 | if ($this->db->getActivePDO()?->getAttribute(PDO::ATTR_CASE) === PDO::CASE_LOWER) { |
|
| 585 | 1 | $row = array_change_key_case($row, CASE_UPPER); |
|
| 586 | } |
||
| 587 | |||
| 588 | 65 | if ($row['CONSTRAINT_TYPE'] === 'P') { |
|
| 589 | 53 | $table->getColumns()[(string) $row['COLUMN_NAME']]->primaryKey(true); |
|
| 590 | 53 | $table->primaryKey((string) $row['COLUMN_NAME']); |
|
| 591 | |||
| 592 | 53 | if (empty($table->getSequenceName())) { |
|
| 593 | 53 | $table->sequenceName((string) $this->getTableSequenceName($table->getName())); |
|
| 594 | } |
||
| 595 | } |
||
| 596 | |||
| 597 | 65 | if ($row['CONSTRAINT_TYPE'] !== 'R') { |
|
| 598 | /** |
||
| 599 | * This condition is not checked in SQL WHERE because of an Oracle Bug: |
||
| 600 | * |
||
| 601 | * {@see https://github.com/yiisoft/yii2/pull/8844} |
||
| 602 | */ |
||
| 603 | 65 | continue; |
|
| 604 | } |
||
| 605 | |||
| 606 | 8 | $name = (string) $row['CONSTRAINT_NAME']; |
|
| 607 | |||
| 608 | 8 | if (!isset($constraints[$name])) { |
|
| 609 | 8 | $constraints[$name] = [ |
|
| 610 | 8 | 'tableName' => $row['TABLE_REF'], |
|
| 611 | 'columns' => [], |
||
| 612 | ]; |
||
| 613 | } |
||
| 614 | |||
| 615 | 8 | $constraints[$name]['columns'][$row['COLUMN_NAME']] = $row['COLUMN_REF']; |
|
| 616 | } |
||
| 617 | |||
| 618 | 71 | foreach ($constraints as $constraint) { |
|
| 619 | 8 | $table->foreignKey(array_merge([$constraint['tableName']], $constraint['columns'])); |
|
| 620 | } |
||
| 621 | } |
||
| 622 | |||
| 623 | /** |
||
| 624 | * Returns all unique indexes for the given table. |
||
| 625 | * |
||
| 626 | * Each array element is of the following structure:. |
||
| 627 | * |
||
| 628 | * ```php |
||
| 629 | * [ |
||
| 630 | * 'IndexName1' => ['col1' [, ...]], |
||
| 631 | * 'IndexName2' => ['col2' [, ...]], |
||
| 632 | * ] |
||
| 633 | * ``` |
||
| 634 | * |
||
| 635 | * @param TableSchema $table the table metadata. |
||
| 636 | * |
||
| 637 | * @throws Exception|InvalidConfigException|Throwable |
||
| 638 | * |
||
| 639 | * @return array all unique indexes for the given table. |
||
| 640 | */ |
||
| 641 | 1 | public function findUniqueIndexes(TableSchema $table): array |
|
| 642 | { |
||
| 643 | 1 | $query = <<<SQL |
|
| 644 | SELECT |
||
| 645 | DIC.INDEX_NAME, |
||
| 646 | DIC.COLUMN_NAME |
||
| 647 | FROM ALL_INDEXES DI |
||
| 648 | INNER JOIN ALL_IND_COLUMNS DIC ON DI.TABLE_NAME = DIC.TABLE_NAME AND DI.INDEX_NAME = DIC.INDEX_NAME |
||
| 649 | WHERE |
||
| 650 | DI.UNIQUENESS = 'UNIQUE' |
||
| 651 | AND DIC.TABLE_OWNER = :schemaName |
||
| 652 | AND DIC.TABLE_NAME = :tableName |
||
| 653 | ORDER BY DIC.TABLE_NAME, DIC.INDEX_NAME, DIC.COLUMN_POSITION |
||
| 654 | SQL; |
||
| 655 | 1 | $result = []; |
|
| 656 | |||
| 657 | 1 | $rows = $this->db->createCommand( |
|
| 658 | $query, |
||
| 659 | 1 | [':tableName' => $table->getName(), ':schemaName' => $table->getschemaName()] |
|
| 660 | 1 | )->queryAll(); |
|
| 661 | |||
| 662 | /** @psalm-var array<array{INDEX_NAME: string, COLUMN_NAME: string}> $rows */ |
||
| 663 | 1 | foreach ($rows as $row) { |
|
| 664 | 1 | $result[$row['INDEX_NAME']][] = $row['COLUMN_NAME']; |
|
| 665 | } |
||
| 666 | |||
| 667 | 1 | return $result; |
|
| 668 | } |
||
| 669 | |||
| 670 | /** |
||
| 671 | * Extracts the data types for the given column. |
||
| 672 | * |
||
| 673 | * @param ColumnSchema $column |
||
| 674 | * @param string $dbType DB type. |
||
| 675 | * @param string|null $precision total number of digits. |
||
| 676 | * @param string|null $scale number of digits on the right of the decimal separator. |
||
| 677 | * @param string $length length for character types. |
||
| 678 | */ |
||
| 679 | 71 | protected function extractColumnType( |
|
| 680 | ColumnSchema $column, |
||
| 681 | string $dbType, |
||
| 682 | ?string $precision, |
||
| 683 | ?string $scale, |
||
| 684 | string $length |
||
| 685 | ): void { |
||
| 686 | 71 | $column->dbType($dbType); |
|
| 687 | |||
| 688 | 71 | if (str_contains($dbType, 'FLOAT') || str_contains($dbType, 'DOUBLE')) { |
|
| 689 | 16 | $column->type('double'); |
|
| 690 | 71 | } elseif (str_contains($dbType, 'NUMBER')) { |
|
| 691 | 68 | if ($scale === null || $scale > 0) { |
|
| 692 | 17 | $column->type('decimal'); |
|
| 693 | } else { |
||
| 694 | 68 | $column->type('integer'); |
|
| 695 | } |
||
| 696 | 64 | } elseif (str_contains($dbType, 'INTEGER')) { |
|
| 697 | $column->type('integer'); |
||
| 698 | 64 | } elseif (str_contains($dbType, 'BLOB')) { |
|
| 699 | 18 | $column->type('binary'); |
|
| 700 | 61 | } elseif (str_contains($dbType, 'CLOB')) { |
|
| 701 | 22 | $column->type('text'); |
|
| 702 | 60 | } elseif (str_contains($dbType, 'TIMESTAMP')) { |
|
| 703 | 15 | $column->type('timestamp'); |
|
| 704 | } else { |
||
| 705 | 60 | $column->type('string'); |
|
| 706 | } |
||
| 707 | } |
||
| 708 | |||
| 709 | /** |
||
| 710 | * Extracts size, precision and scale information from column's DB type. |
||
| 711 | * |
||
| 712 | * @param ColumnSchema $column |
||
| 713 | * @param string $dbType the column's DB type. |
||
| 714 | * @param string|null $precision total number of digits. |
||
| 715 | * @param string|null $scale number of digits on the right of the decimal separator. |
||
| 716 | * @param string $length length for character types. |
||
| 717 | */ |
||
| 718 | 71 | protected function extractColumnSize( |
|
| 719 | ColumnSchema $column, |
||
| 720 | string $dbType, |
||
| 721 | ?string $precision, |
||
| 722 | ?string $scale, |
||
| 723 | string $length |
||
| 724 | ): void { |
||
| 725 | 71 | $column->size(trim($length) === '' ? null : (int) $length); |
|
| 726 | 71 | $column->precision(trim((string) $precision) === '' ? null : (int) $precision); |
|
| 727 | 71 | $column->scale($scale === '' || $scale === null ? null : (int) $scale); |
|
| 728 | } |
||
| 729 | |||
| 730 | /** |
||
| 731 | * Loads multiple types of constraints and returns the specified ones. |
||
| 732 | * |
||
| 733 | * @param string $tableName table name. |
||
| 734 | * @param string $returnType return type: |
||
| 735 | * - primaryKey |
||
| 736 | * - foreignKeys |
||
| 737 | * - uniques |
||
| 738 | * - checks |
||
| 739 | * |
||
| 740 | * @throws Exception|InvalidConfigException|NotSupportedException|Throwable |
||
| 741 | * |
||
| 742 | * @return mixed constraints. |
||
| 743 | */ |
||
| 744 | 60 | private function loadTableConstraints(string $tableName, string $returnType): mixed |
|
| 745 | { |
||
| 746 | 60 | $sql = <<<SQL |
|
| 747 | SELECT |
||
| 748 | "uc"."CONSTRAINT_NAME" AS "name", |
||
| 749 | "uccol"."COLUMN_NAME" AS "column_name", |
||
| 750 | "uc"."CONSTRAINT_TYPE" AS "type", |
||
| 751 | "fuc"."OWNER" AS "foreign_table_schema", |
||
| 752 | "fuc"."TABLE_NAME" AS "foreign_table_name", |
||
| 753 | "fuccol"."COLUMN_NAME" AS "foreign_column_name", |
||
| 754 | "uc"."DELETE_RULE" AS "on_delete", |
||
| 755 | "uc"."SEARCH_CONDITION" AS "check_expr" |
||
| 756 | FROM "USER_CONSTRAINTS" "uc" |
||
| 757 | INNER JOIN "USER_CONS_COLUMNS" "uccol" |
||
| 758 | ON "uccol"."OWNER" = "uc"."OWNER" AND "uccol"."CONSTRAINT_NAME" = "uc"."CONSTRAINT_NAME" |
||
| 759 | LEFT JOIN "USER_CONSTRAINTS" "fuc" |
||
| 760 | ON "fuc"."OWNER" = "uc"."R_OWNER" AND "fuc"."CONSTRAINT_NAME" = "uc"."R_CONSTRAINT_NAME" |
||
| 761 | LEFT JOIN "USER_CONS_COLUMNS" "fuccol" |
||
| 762 | ON "fuccol"."OWNER" = "fuc"."OWNER" AND "fuccol"."CONSTRAINT_NAME" = "fuc"."CONSTRAINT_NAME" AND "fuccol"."POSITION" = "uccol"."POSITION" |
||
| 763 | WHERE "uc"."OWNER" = :schemaName AND "uc"."TABLE_NAME" = :tableName |
||
| 764 | ORDER BY "uccol"."POSITION" ASC |
||
| 765 | SQL; |
||
| 766 | |||
| 767 | 60 | $resolvedName = $this->resolveTableName($tableName); |
|
| 768 | |||
| 769 | 60 | $constraints = $this->db->createCommand($sql, [ |
|
| 770 | 60 | ':schemaName' => $resolvedName->getSchemaName(), |
|
| 771 | 60 | ':tableName' => $resolvedName->getName(), |
|
| 772 | 60 | ])->queryAll(); |
|
| 773 | |||
| 774 | /** @var Constraint[] $constraints */ |
||
| 775 | 60 | $constraints = $this->normalizePdoRowKeyCase($constraints, true); |
|
| 776 | 60 | $constraints = ArrayHelper::index($constraints, null, ['type', 'name']); |
|
| 777 | |||
| 778 | 60 | $result = [ |
|
| 779 | 'primaryKey' => null, |
||
| 780 | 'foreignKeys' => [], |
||
| 781 | 'uniques' => [], |
||
| 782 | 'checks' => [], |
||
| 783 | ]; |
||
| 784 | |||
| 785 | /** |
||
| 786 | * @var string $type |
||
| 787 | * @var array $names |
||
| 788 | */ |
||
| 789 | 60 | foreach ($constraints as $type => $names) { |
|
| 790 | /** |
||
| 791 | * @psalm-var object|string|null $name |
||
| 792 | * @psalm-var ConstraintArray $constraint |
||
| 793 | */ |
||
| 794 | 60 | foreach ($names as $name => $constraint) { |
|
| 795 | 60 | switch ($type) { |
|
| 796 | 60 | case 'P': |
|
| 797 | 45 | $result['primaryKey'] = (new Constraint()) |
|
| 798 | 45 | ->name($name) |
|
| 799 | 45 | ->columnNames(ArrayHelper::getColumn($constraint, 'column_name')); |
|
| 800 | 45 | break; |
|
| 801 | 60 | case 'R': |
|
| 802 | 13 | $result['foreignKeys'][] = (new ForeignKeyConstraint()) |
|
| 803 | 13 | ->name($name) |
|
| 804 | 13 | ->columnNames(ArrayHelper::getColumn($constraint, 'column_name')) |
|
| 805 | 13 | ->foreignSchemaName($constraint[0]['foreign_table_schema']) |
|
| 806 | 13 | ->foreignTableName($constraint[0]['foreign_table_name']) |
|
| 807 | 13 | ->foreignColumnNames(ArrayHelper::getColumn($constraint, 'foreign_column_name')) |
|
| 808 | 13 | ->onDelete($constraint[0]['on_delete']) |
|
| 809 | 13 | ->onUpdate(null); |
|
| 810 | 13 | break; |
|
| 811 | 60 | case 'U': |
|
| 812 | 46 | $result['uniques'][] = (new Constraint()) |
|
| 813 | 46 | ->name($name) |
|
| 814 | 46 | ->columnNames(ArrayHelper::getColumn($constraint, 'column_name')); |
|
| 815 | 46 | break; |
|
| 816 | 60 | case 'C': |
|
| 817 | 60 | $result['checks'][] = (new CheckConstraint()) |
|
| 818 | 60 | ->name($name) |
|
| 819 | 60 | ->columnNames(ArrayHelper::getColumn($constraint, 'column_name')) |
|
| 820 | 60 | ->expression($constraint[0]['check_expr']); |
|
| 821 | 60 | break; |
|
| 822 | } |
||
| 823 | } |
||
| 824 | } |
||
| 825 | |||
| 826 | 60 | foreach ($result as $type => $data) { |
|
| 827 | 60 | $this->setTableMetadata($tableName, $type, $data); |
|
| 828 | } |
||
| 829 | |||
| 830 | 60 | return $result[$returnType]; |
|
| 831 | } |
||
| 832 | |||
| 833 | /** |
||
| 834 | * Creates a column schema for the database. |
||
| 835 | * |
||
| 836 | * This method may be overridden by child classes to create a DBMS-specific column schema. |
||
| 837 | * |
||
| 838 | * @return ColumnSchema column schema instance. |
||
| 839 | */ |
||
| 840 | 71 | protected function createColumnSchema(): ColumnSchema |
|
| 841 | { |
||
| 842 | 71 | return new ColumnSchema(); |
|
| 843 | } |
||
| 844 | |||
| 845 | 1 | public function rollBackSavepoint(string $name): void |
|
| 846 | { |
||
| 847 | 1 | $this->db->createCommand("ROLLBACK TO SAVEPOINT $name")->execute(); |
|
| 848 | } |
||
| 849 | |||
| 850 | 2 | public function setTransactionIsolationLevel(string $level): void |
|
| 851 | { |
||
| 852 | 2 | $this->db->createCommand("SET TRANSACTION ISOLATION LEVEL $level")->execute(); |
|
| 853 | } |
||
| 854 | |||
| 855 | /** |
||
| 856 | * Returns the actual name of a given table name. |
||
| 857 | * |
||
| 858 | * This method will strip off curly brackets from the given table name and replace the percentage character '%' with |
||
| 859 | * {@see ConnectionInterface::tablePrefix}. |
||
| 860 | * |
||
| 861 | * @param string $name the table name to be converted. |
||
| 862 | * |
||
| 863 | * @return string the real name of the given table name. |
||
| 864 | */ |
||
| 865 | 144 | public function getRawTableName(string $name): string |
|
| 866 | { |
||
| 867 | 144 | if (str_contains($name, '{{')) { |
|
| 868 | 21 | $name = preg_replace('/{{(.*?)}}/', '\1', $name); |
|
| 869 | |||
| 870 | 21 | return str_replace('%', $this->db->getTablePrefix(), $name); |
|
| 871 | } |
||
| 872 | |||
| 873 | 144 | return $name; |
|
| 874 | } |
||
| 875 | |||
| 876 | /** |
||
| 877 | * Returns the cache key for the specified table name. |
||
| 878 | * |
||
| 879 | * @param string $name the table name. |
||
| 880 | * |
||
| 881 | * @return array the cache key. |
||
| 882 | */ |
||
| 883 | 144 | protected function getCacheKey(string $name): array |
|
| 884 | { |
||
| 885 | return [ |
||
| 886 | 144 | __CLASS__, |
|
| 887 | 144 | $this->db->getDriver()->getDsn(), |
|
| 888 | 144 | $this->db->getDriver()->getUsername(), |
|
| 889 | 144 | $this->getRawTableName($name), |
|
| 890 | ]; |
||
| 891 | } |
||
| 892 | |||
| 893 | /** |
||
| 894 | * Returns the cache tag name. |
||
| 895 | * |
||
| 896 | * This allows {@see refresh()} to invalidate all cached table schemas. |
||
| 897 | * |
||
| 898 | * @return string the cache tag name. |
||
| 899 | */ |
||
| 900 | 144 | protected function getCacheTag(): string |
|
| 906 | ])); |
||
| 907 | } |
||
| 908 | |||
| 909 | /** |
||
| 910 | * Changes row's array key case to lower if PDO's one is set to uppercase. |
||
| 911 | * |
||
| 912 | * @param array $row row's array or an array of row's arrays. |
||
| 913 | * @param bool $multiple whether multiple rows or a single row passed. |
||
| 914 | * |
||
| 915 | * @throws Exception |
||
| 916 | * |
||
| 917 | * @return array normalized row or rows. |
||
| 918 | */ |
||
| 919 | 70 | protected function normalizePdoRowKeyCase(array $row, bool $multiple): array |
|
| 932 | } |
||
| 933 | |||
| 934 | /** |
||
| 935 | * @return bool whether this DBMS supports [savepoint](http://en.wikipedia.org/wiki/Savepoint). |
||
| 936 | */ |
||
| 937 | 2 | public function supportsSavepoint(): bool |
|
| 940 | } |
||
| 941 | |||
| 942 | /** |
||
| 943 | * Creates a new savepoint. |
||
| 944 | * |
||
| 945 | * @param string $name the savepoint name |
||
| 946 | * |
||
| 947 | * @throws Exception|InvalidConfigException|Throwable |
||
| 948 | */ |
||
| 949 | 1 | public function createSavepoint(string $name): void |
|
| 952 | } |
||
| 953 | } |
||
| 954 |
Loading history...
