Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
68.97% |
40 / 58 |
|
70.00% |
7 / 10 |
CRAP | |
0.00% |
0 / 1 |
| SchemaUtils | |
68.97% |
40 / 58 |
|
70.00% |
7 / 10 |
77.92 | |
0.00% |
0 / 1 |
| getTables | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| getTableFields | |
88.00% |
22 / 25 |
|
0.00% |
0 / 1 |
11.21 | |||
| firstHumanReadableField | |
62.50% |
5 / 8 |
|
0.00% |
0 / 1 |
9.58 | |||
| getTableFieldsWithIds | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| getUserIdField | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
3 | |||
| hasTable | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| hasTimestamps | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
3 | |||
| hasSoftDelete | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
2 | |||
| tableExists | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| getRealTableName | |
14.29% |
2 / 14 |
|
0.00% |
0 / 1 |
37.86 | |||
| 1 | <?php |
| 2 | |
| 3 | namespace San\Crud\Utils; |
| 4 | |
| 5 | use Illuminate\Support\Facades\DB; |
| 6 | |
| 7 | class SchemaUtils { |
| 8 | public static function getTables(string|array $exclude) { |
| 9 | $tables = DB::connection()->getDoctrineSchemaManager()->listTableNames(); |
| 10 | |
| 11 | return array_values(array_filter($tables, fn($table) => !in_array($table, (array) $exclude))); |
| 12 | } |
| 13 | |
| 14 | public static function getTableFields(string $tableName, array $excludedColumns = [], array $alwaysIgnoredColumns = ['id', 'created_at', 'updated_at', 'deleted_at']) { |
| 15 | // ugly enum hack as doctrine does not support enum types |
| 16 | // https://www.doctrine-project.org/projects/doctrine-orm/en/latest/cookbook/mysql-enums.html#solution-1-mapping-to-varchars |
| 17 | |
| 18 | $tableName = self::getRealTableName($tableName); |
| 19 | DB::connection()->getDoctrineSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'guid'); |
| 20 | |
| 21 | $columns = DB::getDoctrineSchemaManager()->listTableColumns($tableName); |
| 22 | $ignoredColumns = array_merge((array) $excludedColumns, (array) $alwaysIgnoredColumns); |
| 23 | $indexes = DB::getDoctrineSchemaManager()->listTableIndexes($tableName); |
| 24 | $uniqueColumns = []; |
| 25 | |
| 26 | foreach ($indexes as $index) { |
| 27 | if ($index->isUnique() && count($index->getColumns()) === 1) { |
| 28 | $uniqueColumns = array_merge($uniqueColumns, $index->getColumns()); |
| 29 | } |
| 30 | } |
| 31 | |
| 32 | foreach ($columns as $column) { |
| 33 | if (in_array($column->getName(), $ignoredColumns)) continue; |
| 34 | |
| 35 | $field = ['id' => $column->getName(), 'type' => $column->getType()->getName(), 'name' => \Str::title(str_replace('_', ' ', $column->getName())), 'nullable' => !$column->getNotnull()]; |
| 36 | |
| 37 | if ($field['type'] == 'guid') { |
| 38 | try { |
| 39 | $enums = DB::select("SHOW COLUMNS FROM $tableName WHERE Field = '$field[name]'"); |
| 40 | $field['values'] = explode(',', str_replace("'", '', substr($enums[0]->Type, 5, -1))); |
| 41 | } catch (\Throwable $e) { |
| 42 | } |
| 43 | } |
| 44 | |
| 45 | if (preg_match('/^(.*?)_id$/', $field['id'], $matches)) { |
| 46 | $relatedTable = \Str::plural($matches[1]); |
| 47 | if (self::tableExists($relatedTable)) { |
| 48 | $field['relation'] = $matches[1]; |
| 49 | $field['related_table'] = $relatedTable; |
| 50 | } |
| 51 | } |
| 52 | |
| 53 | //check if column is unique index |
| 54 | if (in_array($field['id'], $uniqueColumns)) { |
| 55 | $field['unique'] = TRUE; |
| 56 | } |
| 57 | |
| 58 | $fields[] = $field; |
| 59 | } |
| 60 | |
| 61 | return $fields ?? []; |
| 62 | } |
| 63 | |
| 64 | public static function firstHumanReadableField(string $table, string $key = NULL) { |
| 65 | $all = self::getTableFields($table); |
| 66 | if (empty($all)) return NULL; |
| 67 | |
| 68 | foreach ($all as $f) { |
| 69 | if (preg_match('/_id$/', $f['id'])) continue; |
| 70 | if (preg_match('/(string|text)/', $f['type'])) return $key ? $f[$key] : $f; |
| 71 | $last = $f['id']; |
| 72 | } |
| 73 | |
| 74 | $result = $last ?? $all[0]; |
| 75 | return $key ? $result[$key] : $result; |
| 76 | } |
| 77 | |
| 78 | public static function getTableFieldsWithIds(string $table, array $excludedColumns = []) { |
| 79 | return array_values(array_filter(self::getTableFields($table, $excludedColumns), fn($f) => !empty($f['relation']))); |
| 80 | } |
| 81 | |
| 82 | public static function getUserIdField(string $tableName, $userIdField = 'user_id') { |
| 83 | if (!self::tableExists($tableName)) return NULL; |
| 84 | return \Schema::hasColumn(self::getRealTableName($tableName), $userIdField) ? $userIdField : NULL; |
| 85 | } |
| 86 | |
| 87 | public static function hasTable(string $tableName) { |
| 88 | return self::tableExists($tableName); |
| 89 | } |
| 90 | |
| 91 | public static function hasTimestamps(string $tableName) { |
| 92 | if (!self::tableExists($tableName)) return FALSE; |
| 93 | return \Schema::hasColumn(self::getRealTableName($tableName), 'created_at') && \Schema::hasColumn(self::getRealTableName($tableName), 'updated_at'); |
| 94 | } |
| 95 | |
| 96 | public static function hasSoftDelete(string $tableName) { |
| 97 | if (!self::tableExists($tableName)) return FALSE; |
| 98 | return \Schema::hasColumn(self::getRealTableName($tableName), 'deleted_at'); |
| 99 | } |
| 100 | |
| 101 | public static function tableExists(string $tableName) { |
| 102 | return !!self::getRealTableName($tableName); |
| 103 | } |
| 104 | |
| 105 | public static function getRealTableName(string $tableName) { |
| 106 | if (\Schema::hasTable($tableName)) return $tableName; |
| 107 | //return false if it's not a mysql connection |
| 108 | if (DB::connection()->getDriverName() != 'mysql') return FALSE; |
| 109 | |
| 110 | if (empty($GLOBALS['view_tables'])) $GLOBALS['view_tables'] = []; |
| 111 | if (!empty($GLOBALS['view_tables'][$tableName])) return $GLOBALS['view_tables'][$tableName]; |
| 112 | |
| 113 | //check if there is a view |
| 114 | $views = DB::select('SHOW FULL TABLES WHERE Table_type = \'VIEW\''); |
| 115 | foreach ($views as $view) { |
| 116 | $key = 'Tables_in_' . DB::connection()->getDatabaseName(); |
| 117 | if ($view->$key == $tableName) { |
| 118 | //get the original table name |
| 119 | $results = DB::select("SHOW CREATE VIEW $tableName"); |
| 120 | $result = $results[0]->{'Create View'}; |
| 121 | preg_match('/ FROM `(.*?)` /i', $result, $matches); |
| 122 | $GLOBALS['view_tables'][$tableName] = $matches[1]; |
| 123 | return $matches[1]; |
| 124 | } |
| 125 | } |
| 126 | |
| 127 | return FALSE; |
| 128 | } |
| 129 | } |