yiisoft /
db-mysql
| Total Complexity | 70 |
| Total Lines | 583 |
| Duplicated Lines | 0 % |
| Changes | 0 | ||
Complex classes like Schema 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 Schema, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 22 | class Schema extends AbstractSchema implements ConstraintFinderInterface |
||
| 23 | { |
||
| 24 | use ConstraintFinderTrait; |
||
| 25 | |||
| 26 | protected string $columnSchemaClass = ColumnSchema::class; |
||
| 27 | protected string $tableQuoteCharacter = '`'; |
||
| 28 | protected string $columnQuoteCharacter = '`'; |
||
| 29 | private bool $oldMysql; |
||
| 30 | private array $typeMap = [ |
||
| 31 | 'tinyint' => self::TYPE_TINYINT, |
||
| 32 | 'bit' => self::TYPE_INTEGER, |
||
| 33 | 'smallint' => self::TYPE_SMALLINT, |
||
| 34 | 'mediumint' => self::TYPE_INTEGER, |
||
| 35 | 'int' => self::TYPE_INTEGER, |
||
| 36 | 'integer' => self::TYPE_INTEGER, |
||
| 37 | 'bigint' => self::TYPE_BIGINT, |
||
| 38 | 'float' => self::TYPE_FLOAT, |
||
| 39 | 'double' => self::TYPE_DOUBLE, |
||
| 40 | 'real' => self::TYPE_FLOAT, |
||
| 41 | 'decimal' => self::TYPE_DECIMAL, |
||
| 42 | 'numeric' => self::TYPE_DECIMAL, |
||
| 43 | 'tinytext' => self::TYPE_TEXT, |
||
| 44 | 'mediumtext' => self::TYPE_TEXT, |
||
| 45 | 'longtext' => self::TYPE_TEXT, |
||
| 46 | 'longblob' => self::TYPE_BINARY, |
||
| 47 | 'blob' => self::TYPE_BINARY, |
||
| 48 | 'text' => self::TYPE_TEXT, |
||
| 49 | 'varchar' => self::TYPE_STRING, |
||
| 50 | 'string' => self::TYPE_STRING, |
||
| 51 | 'char' => self::TYPE_CHAR, |
||
| 52 | 'datetime' => self::TYPE_DATETIME, |
||
| 53 | 'year' => self::TYPE_DATE, |
||
| 54 | 'date' => self::TYPE_DATE, |
||
| 55 | 'time' => self::TYPE_TIME, |
||
| 56 | 'timestamp' => self::TYPE_TIMESTAMP, |
||
| 57 | 'enum' => self::TYPE_STRING, |
||
| 58 | 'varbinary' => self::TYPE_BINARY, |
||
| 59 | 'json' => self::TYPE_JSON, |
||
| 60 | ]; |
||
| 61 | |||
| 62 | protected function resolveTableName($name) |
||
| 63 | { |
||
| 64 | $resolvedName = new TableSchema(); |
||
| 65 | |||
| 66 | $parts = explode('.', str_replace('`', '', $name)); |
||
| 67 | |||
| 68 | if (isset($parts[1])) { |
||
| 69 | $resolvedName->schemaName($parts[0]); |
||
| 70 | $resolvedName->name($parts[1]); |
||
| 71 | } else { |
||
| 72 | $resolvedName->schemaName($this->defaultSchema); |
||
| 73 | $resolvedName->name($name); |
||
| 74 | } |
||
| 75 | |||
| 76 | $resolvedName->fullName(($resolvedName->getSchemaName() !== $this->defaultSchema ? |
||
| 77 | $resolvedName->getSchemaName() . '.' : '') . $resolvedName->getName()); |
||
| 78 | |||
| 79 | return $resolvedName; |
||
| 80 | } |
||
| 81 | |||
| 82 | protected function findTableNames($schema = '') |
||
| 83 | { |
||
| 84 | $sql = 'SHOW TABLES'; |
||
| 85 | |||
| 86 | if ($schema !== '') { |
||
| 87 | $sql .= ' FROM ' . $this->quoteSimpleTableName($schema); |
||
| 88 | } |
||
| 89 | |||
| 90 | return $this->getDb()->createCommand($sql)->queryColumn(); |
||
| 91 | } |
||
| 92 | |||
| 93 | protected function loadTableSchema(string $name): ?TableSchema |
||
| 94 | { |
||
| 95 | $table = new TableSchema(); |
||
| 96 | |||
| 97 | $this->resolveTableNames($table, $name); |
||
| 98 | |||
| 99 | if ($this->findColumns($table)) { |
||
| 100 | $this->findConstraints($table); |
||
| 101 | |||
| 102 | return $table; |
||
| 103 | } |
||
| 104 | |||
| 105 | return null; |
||
| 106 | } |
||
| 107 | |||
| 108 | protected function loadTablePrimaryKey(string $tableName): ?Constraint |
||
| 109 | { |
||
| 110 | return $this->loadTableConstraints($tableName, 'primaryKey'); |
||
| 111 | } |
||
| 112 | |||
| 113 | protected function loadTableForeignKeys(string $tableName): array |
||
| 116 | } |
||
| 117 | |||
| 118 | protected function loadTableIndexes(string $tableName): array |
||
| 119 | { |
||
| 120 | static $sql = <<<'SQL' |
||
| 121 | SELECT |
||
| 122 | `s`.`INDEX_NAME` AS `name`, |
||
| 123 | `s`.`COLUMN_NAME` AS `column_name`, |
||
| 124 | `s`.`NON_UNIQUE` ^ 1 AS `index_is_unique`, |
||
| 125 | `s`.`INDEX_NAME` = 'PRIMARY' AS `index_is_primary` |
||
| 126 | FROM `information_schema`.`STATISTICS` AS `s` |
||
| 127 | WHERE `s`.`TABLE_SCHEMA` = COALESCE(:schemaName, DATABASE()) AND `s`.`INDEX_SCHEMA` = `s`.`TABLE_SCHEMA` AND `s`.`TABLE_NAME` = :tableName |
||
| 128 | ORDER BY `s`.`SEQ_IN_INDEX` ASC |
||
| 129 | SQL; |
||
| 130 | |||
| 131 | $resolvedName = $this->resolveTableName($tableName); |
||
| 132 | |||
| 133 | $indexes = $this->getDb()->createCommand($sql, [ |
||
| 134 | ':schemaName' => $resolvedName->getSchemaName(), |
||
| 135 | ':tableName' => $resolvedName->getName(), |
||
| 136 | ])->queryAll(); |
||
| 137 | |||
| 138 | $indexes = $this->normalizePdoRowKeyCase($indexes, true); |
||
| 139 | $indexes = ArrayHelper::index($indexes, null, 'name'); |
||
| 140 | $result = []; |
||
| 141 | |||
| 142 | foreach ($indexes as $name => $index) { |
||
| 143 | $ic = new IndexConstraint(); |
||
| 144 | |||
| 145 | $ic->setIsPrimary((bool) $index[0]['index_is_primary']); |
||
| 146 | $ic->setIsUnique((bool) $index[0]['index_is_unique']); |
||
| 147 | $ic->setName($name !== 'PRIMARY' ? $name : null); |
||
| 148 | $ic->setColumnNames(ArrayHelper::getColumn($index, 'column_name')); |
||
| 149 | |||
| 150 | $result[] = $ic; |
||
| 151 | } |
||
| 152 | |||
| 153 | return $result; |
||
| 154 | } |
||
| 155 | |||
| 156 | protected function loadTableUniques(string $tableName): array |
||
| 157 | { |
||
| 158 | return $this->loadTableConstraints($tableName, 'uniques'); |
||
| 159 | } |
||
| 160 | |||
| 161 | protected function loadTableChecks(string $tableName): array |
||
|
|
|||
| 162 | { |
||
| 163 | throw new NotSupportedException('MySQL does not support check constraints.'); |
||
| 164 | } |
||
| 165 | |||
| 166 | protected function loadTableDefaultValues(string $tableName): array |
||
| 167 | { |
||
| 168 | throw new NotSupportedException('MySQL does not support default value constraints.'); |
||
| 169 | } |
||
| 170 | |||
| 171 | /** |
||
| 172 | * Creates a query builder for the MySQL database. |
||
| 173 | * |
||
| 174 | * @return QueryBuilder query builder instance |
||
| 175 | */ |
||
| 176 | public function createQueryBuilder() |
||
| 177 | { |
||
| 178 | return new QueryBuilder($this->getDb()); |
||
| 179 | } |
||
| 180 | |||
| 181 | /** |
||
| 182 | * Resolves the table name and schema name (if any). |
||
| 183 | * |
||
| 184 | * @param TableSchema $table the table metadata object. |
||
| 185 | * @param string $name the table name. |
||
| 186 | */ |
||
| 187 | protected function resolveTableNames($table, $name) |
||
| 188 | { |
||
| 189 | $parts = explode('.', str_replace('`', '', $name)); |
||
| 190 | |||
| 191 | if (isset($parts[1])) { |
||
| 192 | $table->schemaName($parts[0]); |
||
| 193 | $table->name($parts[1]); |
||
| 194 | $table->fullName($table->getSchemaName() . '.' . $table->getName()); |
||
| 195 | } else { |
||
| 196 | $table->name($parts[0]); |
||
| 197 | $table->fullName($parts[0]); |
||
| 198 | } |
||
| 199 | } |
||
| 200 | |||
| 201 | /** |
||
| 202 | * Loads the column information into a {@see ColumnSchema} object. |
||
| 203 | * |
||
| 204 | * @param array $info column information. |
||
| 205 | * |
||
| 206 | * @return ColumnSchema the column schema object. |
||
| 207 | */ |
||
| 208 | protected function loadColumnSchema(array $info): ColumnSchema |
||
| 209 | { |
||
| 210 | $column = $this->createColumnSchema(); |
||
| 211 | |||
| 212 | $column->name($info['field']); |
||
| 213 | $column->allowNull($info['null'] === 'YES'); |
||
| 214 | $column->isPrimaryKey(strpos($info['key'], 'PRI') !== false); |
||
| 215 | $column->autoIncrement(stripos($info['extra'], 'auto_increment') !== false); |
||
| 216 | $column->comment($info['comment']); |
||
| 217 | $column->dbType($info['type']); |
||
| 218 | $column->unsigned(stripos($column->getDbType(), 'unsigned') !== false); |
||
| 219 | $column->type(self::TYPE_STRING); |
||
| 220 | |||
| 221 | if (preg_match('/^(\w+)(?:\(([^)]+)\))?/', $column->getDbType(), $matches)) { |
||
| 222 | $type = strtolower($matches[1]); |
||
| 223 | if (isset($this->typeMap[$type])) { |
||
| 224 | $column->type($this->typeMap[$type]); |
||
| 225 | } |
||
| 226 | if (!empty($matches[2])) { |
||
| 227 | if ($type === 'enum') { |
||
| 228 | preg_match_all("/'[^']*'/", $matches[2], $values); |
||
| 229 | foreach ($values[0] as $i => $value) { |
||
| 230 | $values[$i] = trim($value, "'"); |
||
| 231 | } |
||
| 232 | $column->enumValues($values); |
||
| 233 | } else { |
||
| 234 | $values = explode(',', $matches[2]); |
||
| 235 | $column->precision((int) $values[0]); |
||
| 236 | $column->size((int) $values[0]); |
||
| 237 | if (isset($values[1])) { |
||
| 238 | $column->scale((int) $values[1]); |
||
| 239 | } |
||
| 240 | if ($column->getSize() === 1 && $type === 'bit') { |
||
| 241 | $column->type('boolean'); |
||
| 242 | } elseif ($type === 'bit') { |
||
| 243 | if ($column->getSize() > 32) { |
||
| 244 | $column->type('bigint'); |
||
| 245 | } elseif ($column->getSize() === 32) { |
||
| 246 | $column->type('integer'); |
||
| 247 | } |
||
| 248 | } |
||
| 249 | } |
||
| 250 | } |
||
| 251 | } |
||
| 252 | |||
| 253 | $column->phpType($this->getColumnPhpType($column)); |
||
| 254 | |||
| 255 | if (!$column->getIsPrimaryKey()) { |
||
| 256 | /** |
||
| 257 | * When displayed in the INFORMATION_SCHEMA.COLUMNS table, a default CURRENT TIMESTAMP is displayed |
||
| 258 | * as CURRENT_TIMESTAMP up until MariaDB 10.2.2, and as current_timestamp() from MariaDB 10.2.3. |
||
| 259 | * |
||
| 260 | * See details here: https://mariadb.com/kb/en/library/now/#description |
||
| 261 | */ |
||
| 262 | if ( |
||
| 263 | ($column->getType() === 'timestamp' || $column->getType() === 'datetime') |
||
| 264 | && preg_match('/^current_timestamp(?:\(([0-9]*)\))?$/i', (string) $info['default'], $matches) |
||
| 265 | ) { |
||
| 266 | $column->defaultValue(new Expression('CURRENT_TIMESTAMP' . (!empty($matches[1]) |
||
| 267 | ? '(' . $matches[1] . ')' : ''))); |
||
| 268 | } elseif (isset($type) && $type === 'bit') { |
||
| 269 | $column->defaultValue(bindec(trim((string) $info['default'], 'b\''))); |
||
| 270 | } else { |
||
| 271 | $column->defaultValue($column->phpTypecast($info['default'])); |
||
| 272 | } |
||
| 273 | } |
||
| 274 | |||
| 275 | return $column; |
||
| 276 | } |
||
| 277 | |||
| 278 | /** |
||
| 279 | * Collects the metadata of table columns. |
||
| 280 | * |
||
| 281 | * @param TableSchema $table the table metadata. |
||
| 282 | * |
||
| 283 | * @throws \Exception if DB query fails. |
||
| 284 | * |
||
| 285 | * @return bool whether the table exists in the database. |
||
| 286 | */ |
||
| 287 | protected function findColumns($table) |
||
| 288 | { |
||
| 289 | $sql = 'SHOW FULL COLUMNS FROM ' . $this->quoteTableName($table->getFullName()); |
||
| 290 | |||
| 291 | try { |
||
| 292 | $columns = $this->getDb()->createCommand($sql)->queryAll(); |
||
| 293 | } catch (\Exception $e) { |
||
| 294 | $previous = $e->getPrevious(); |
||
| 295 | |||
| 296 | if ($previous instanceof \PDOException && strpos($previous->getMessage(), 'SQLSTATE[42S02') !== false) { |
||
| 297 | /** |
||
| 298 | * table does not exist |
||
| 299 | * https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html#error_er_bad_table_error |
||
| 300 | */ |
||
| 301 | return false; |
||
| 302 | } |
||
| 303 | |||
| 304 | throw $e; |
||
| 305 | } |
||
| 306 | |||
| 307 | foreach ($columns as $info) { |
||
| 308 | if ($this->getDb()->getSlavePdo()->getAttribute(\PDO::ATTR_CASE) !== \PDO::CASE_LOWER) { |
||
| 309 | $info = array_change_key_case($info, CASE_LOWER); |
||
| 310 | } |
||
| 311 | |||
| 312 | $column = $this->loadColumnSchema($info); |
||
| 313 | $table->columns($column->getName(), $column); |
||
| 314 | |||
| 315 | if ($column->getIsPrimaryKey()) { |
||
| 316 | $table->primaryKey($column->getName()); |
||
| 317 | if ($column->getAutoIncrement()) { |
||
| 318 | $table->sequenceName(''); |
||
| 319 | } |
||
| 320 | } |
||
| 321 | } |
||
| 322 | |||
| 323 | return true; |
||
| 324 | } |
||
| 325 | |||
| 326 | /** |
||
| 327 | * Gets the CREATE TABLE sql string. |
||
| 328 | * |
||
| 329 | * @param TableSchema $table the table metadata. |
||
| 330 | * |
||
| 331 | * @return string $sql the result of 'SHOW CREATE TABLE'. |
||
| 332 | */ |
||
| 333 | protected function getCreateTableSql($table): string |
||
| 334 | { |
||
| 335 | $row = $this->getDb()->createCommand( |
||
| 336 | 'SHOW CREATE TABLE ' . $this->quoteTableName($table->getFullName()) |
||
| 337 | )->queryOne(); |
||
| 338 | |||
| 339 | if (isset($row['Create Table'])) { |
||
| 340 | $sql = $row['Create Table']; |
||
| 341 | } else { |
||
| 342 | $row = array_values($row); |
||
| 343 | $sql = $row[1]; |
||
| 344 | } |
||
| 345 | |||
| 346 | return $sql; |
||
| 347 | } |
||
| 348 | |||
| 349 | /** |
||
| 350 | * Collects the foreign key column details for the given table. |
||
| 351 | * |
||
| 352 | * @param TableSchema $table the table metadata. |
||
| 353 | * |
||
| 354 | * @throws \Exception |
||
| 355 | */ |
||
| 356 | protected function findConstraints($table) |
||
| 357 | { |
||
| 358 | $sql = <<<'SQL' |
||
| 359 | SELECT |
||
| 360 | kcu.constraint_name, |
||
| 361 | kcu.column_name, |
||
| 362 | kcu.referenced_table_name, |
||
| 363 | kcu.referenced_column_name |
||
| 364 | FROM information_schema.referential_constraints AS rc |
||
| 365 | JOIN information_schema.key_column_usage AS kcu ON |
||
| 366 | ( |
||
| 367 | kcu.constraint_catalog = rc.constraint_catalog OR |
||
| 368 | (kcu.constraint_catalog IS NULL AND rc.constraint_catalog IS NULL) |
||
| 369 | ) AND |
||
| 370 | kcu.constraint_schema = rc.constraint_schema AND |
||
| 371 | kcu.constraint_name = rc.constraint_name |
||
| 372 | WHERE rc.constraint_schema = database() AND kcu.table_schema = database() |
||
| 373 | AND rc.table_name = :tableName AND kcu.table_name = :tableName1 |
||
| 374 | SQL; |
||
| 375 | |||
| 376 | try { |
||
| 377 | $rows = $this->getDb()->createCommand( |
||
| 378 | $sql, |
||
| 379 | [':tableName' => $table->getName(), ':tableName1' => $table->getName()] |
||
| 380 | )->queryAll(); |
||
| 381 | |||
| 382 | $constraints = []; |
||
| 383 | |||
| 384 | foreach ($rows as $row) { |
||
| 385 | $constraints[$row['constraint_name']]['referenced_table_name'] = $row['referenced_table_name']; |
||
| 386 | $constraints[$row['constraint_name']]['columns'][$row['column_name']] = $row['referenced_column_name']; |
||
| 387 | } |
||
| 388 | |||
| 389 | $table->foreignKeys([]); |
||
| 390 | |||
| 391 | foreach ($constraints as $name => $constraint) { |
||
| 392 | $table->foreignKey($name, array_merge( |
||
| 393 | [$constraint['referenced_table_name']], |
||
| 394 | $constraint['columns'] |
||
| 395 | )); |
||
| 396 | } |
||
| 397 | } catch (\Exception $e) { |
||
| 398 | $previous = $e->getPrevious(); |
||
| 399 | |||
| 400 | if (!$previous instanceof \PDOException || strpos($previous->getMessage(), 'SQLSTATE[42S02') === false) { |
||
| 401 | throw $e; |
||
| 402 | } |
||
| 403 | |||
| 404 | // table does not exist, try to determine the foreign keys using the table creation sql |
||
| 405 | $sql = $this->getCreateTableSql($table); |
||
| 406 | $regexp = '/FOREIGN KEY\s+\(([^\)]+)\)\s+REFERENCES\s+([^\(^\s]+)\s*\(([^\)]+)\)/mi'; |
||
| 407 | |||
| 408 | if (preg_match_all($regexp, $sql, $matches, PREG_SET_ORDER)) { |
||
| 409 | foreach ($matches as $match) { |
||
| 410 | $fks = array_map('trim', explode(',', str_replace('`', '', $match[1]))); |
||
| 411 | $pks = array_map('trim', explode(',', str_replace('`', '', $match[3]))); |
||
| 412 | $constraint = [str_replace('`', '', $match[2])]; |
||
| 413 | |||
| 414 | foreach ($fks as $k => $name) { |
||
| 415 | $constraint[$name] = $pks[$k]; |
||
| 416 | } |
||
| 417 | |||
| 418 | $table->foreignKey(md5(serialize($constraint)), $constraint); |
||
| 419 | } |
||
| 420 | $table->foreignKeys(\array_values($table->getForeignKeys())); |
||
| 421 | } |
||
| 422 | } |
||
| 423 | } |
||
| 424 | |||
| 425 | /** |
||
| 426 | * Returns all unique indexes for the given table. |
||
| 427 | * |
||
| 428 | * Each array element is of the following structure: |
||
| 429 | * |
||
| 430 | * ```php |
||
| 431 | * [ |
||
| 432 | * 'IndexName1' => ['col1' [, ...]], |
||
| 433 | * 'IndexName2' => ['col2' [, ...]], |
||
| 434 | * ] |
||
| 435 | * ``` |
||
| 436 | * |
||
| 437 | * @param TableSchema $table the table metadata. |
||
| 438 | * |
||
| 439 | * @return array all unique indexes for the given table. |
||
| 440 | */ |
||
| 441 | public function findUniqueIndexes($table) |
||
| 442 | { |
||
| 443 | $sql = $this->getCreateTableSql($table); |
||
| 444 | |||
| 445 | $uniqueIndexes = []; |
||
| 446 | |||
| 447 | $regexp = '/UNIQUE KEY\s+\`(.+)\`\s*\((\`.+\`)+\)/mi'; |
||
| 448 | |||
| 449 | if (preg_match_all($regexp, $sql, $matches, PREG_SET_ORDER)) { |
||
| 450 | foreach ($matches as $match) { |
||
| 451 | $indexName = $match[1]; |
||
| 452 | $indexColumns = array_map('trim', explode('`,`', trim($match[2], '`'))); |
||
| 453 | $uniqueIndexes[$indexName] = $indexColumns; |
||
| 454 | } |
||
| 455 | } |
||
| 456 | |||
| 457 | return $uniqueIndexes; |
||
| 458 | } |
||
| 459 | |||
| 460 | public function createColumnSchemaBuilder($type, $length = null) |
||
| 461 | { |
||
| 462 | return new ColumnSchemaBuilder($type, $length, $this->getDb()); |
||
| 463 | } |
||
| 464 | |||
| 465 | /** |
||
| 466 | * @throws InvalidConfigException |
||
| 467 | * @throws Exception |
||
| 468 | * |
||
| 469 | * @return bool whether the version of the MySQL being used is older than 5.1. |
||
| 470 | */ |
||
| 471 | protected function isOldMysql() |
||
| 472 | { |
||
| 473 | if ($this->oldMysql === null) { |
||
| 474 | $version = $this->getDb()->getSlavePdo()->getAttribute(\PDO::ATTR_SERVER_VERSION); |
||
| 475 | |||
| 476 | $this->oldMysql = version_compare($version, '5.1', '<='); |
||
| 477 | } |
||
| 478 | |||
| 479 | return $this->oldMysql; |
||
| 480 | } |
||
| 481 | |||
| 482 | /** |
||
| 483 | * Loads multiple types of constraints and returns the specified ones. |
||
| 484 | * |
||
| 485 | * @param string $tableName table name. |
||
| 486 | * @param string $returnType return type: |
||
| 487 | * - primaryKey |
||
| 488 | * - foreignKeys |
||
| 489 | * - uniques |
||
| 490 | * |
||
| 491 | * @return mixed constraints. |
||
| 492 | */ |
||
| 493 | private function loadTableConstraints(string $tableName, string $returnType) |
||
| 593 | } |
||
| 594 | |||
| 595 | /** |
||
| 596 | * Creates a column schema for the database. |
||
| 597 | * |
||
| 598 | * This method may be overridden by child classes to create a DBMS-specific column schema. |
||
| 599 | * |
||
| 600 | * @return ColumnSchema column schema instance. |
||
| 601 | */ |
||
| 602 | protected function createColumnSchema(): ColumnSchema |
||
| 603 | { |
||
| 604 | return new ColumnSchema(); |
||
| 605 | } |
||
| 606 | } |
||
| 607 |
Loading history...
This check looks for parameters that have been defined for a function or method, but which are not used in the method body.