Schema::tables() - Code Metrics - Inspection of "added support for cross schema foreign keys" - vakata/database - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( e5c5d6...6b5dbf )

by Ivan

created 2020-11-06 15:00 UTC

Schema::tables() A

↳ Parent: Schema

Complexity

Conditions	2
Paths	1

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	20
rs	9.6
c	0
b	0
f	0
cc	2
nc	1
nop	0

<?php

namespace vakata\database\driver\mysql;

use \vakata\database\DBException;
use \vakata\database\DriverInterface;
use \vakata\database\DriverAbstract;
use \vakata\database\StatementInterface;
use \vakata\database\ResultInterface;
use \vakata\database\schema\Table;
use \vakata\database\schema\TableRelation;
use \vakata\collection\Collection;

trait Schema
{
    protected $connection;
    abstract public function query(string $sql, $par = null, bool $buff = true) : ResultInterface;

    public function table(string $table, bool $detectRelations = true) : Table
    {
        static $tables = [];

        $main = $this->connection['opts']['schema'] ?? $this->connection['name'];
        $schema = $main;
        if (strpos($table, '.')) {
            $temp = explode('.', $table, 2);
            $schema = $temp[0];
            $table = $temp[1];
        }

        if (isset($tables[$schema . '.' . $table])) {
            return $tables[$schema . '.' . $table];
        }

        static $comments = [];
        if (!isset($comments[$schema])) {
            $comments[$schema] = Collection::from(
                $this->query(
                    "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ?",
                    [ $schema ]
                )
            )
            ->mapKey(function ($v) {
                return $v['TABLE_NAME'];
            })
            ->pluck('TABLE_COMMENT')
            ->toArray();
        }

        static $relationsT = null;
        static $relationsR = null;
        if (!isset($relationsT) || !isset($relationsR)) {
            $relationsT = [];
            $relationsR = [];
            $additional = [];
            $col = Collection::from(
                $this->query(
                    "SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_TABLE_SCHEMA, REFERENCED_COLUMN_NAME
                     FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                     WHERE
                        (TABLE_SCHEMA = ? OR REFERENCED_TABLE_SCHEMA = ?) AND
                        TABLE_NAME IS NOT NULL AND REFERENCED_TABLE_NAME IS NOT NULL",
                    [ $main, $main ]
                )
            )->toArray();
            foreach ($col as $row) {
                if ($row['TABLE_SCHEMA'] !== $main) {
                    $additional[] = $row['TABLE_SCHEMA'];
                }
                if ($row['REFERENCED_TABLE_SCHEMA'] !== $main) {
                    $additional[] = $row['REFERENCED_TABLE_SCHEMA'];
                }
                $relationsT[$row['TABLE_SCHEMA'] . '.' . $row['TABLE_NAME']][] = $row;
                $relationsR[$row['REFERENCED_TABLE_SCHEMA'] . '.' . $row['REFERENCED_TABLE_NAME']][] = $row;
            }
            foreach (array_filter(array_unique($additional)) as $s) {
                $col = Collection::from(
                    $this->query(
                        "SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_TABLE_SCHEMA, REFERENCED_COLUMN_NAME
                        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                        WHERE
                            TABLE_SCHEMA = ? AND REFERENCED_TABLE_SCHEMA = ? AND
                            TABLE_NAME IS NOT NULL AND REFERENCED_TABLE_NAME IS NOT NULL",
                        [ $s, $s ]
                    )
                )->toArray();
                foreach ($col as $row) {
                    $relationsT[$row['TABLE_SCHEMA'] . '.' . $row['TABLE_NAME']][] = $row;
                    $relationsR[$row['REFERENCED_TABLE_SCHEMA'] . '.' . $row['REFERENCED_TABLE_NAME']][] = $row;
                }
            }
        }

        $columns = Collection::from($this->query("SHOW FULL COLUMNS FROM {$schema}.{$table}"));
        if (!count($columns)) {
            throw new DBException('Table not found by name');
        }
        $tables[$schema . '.' . $table] = $definition = (new Table($table, $schema))
            ->addColumns(
                $columns
                    ->clone()
                    ->mapKey(function ($v) {
                        return $v['Field'];
                    })
                    ->map(function ($v) {
                        $v['length'] = null;
                        if (!isset($v['Type'])) {
                            return $v;
                        }
                        $type = strtolower($v['Type']);
                        switch ($type) {
                            case 'tinytext':
                                $v['length'] = 255;
                                break;
                            case 'text':
                                $v['length'] = 65535;
                                break;
                            case 'mediumtext':
                                $v['length'] = 16777215;
                                break;
                            case 'longtext':
                                // treat this as no limit
                                break;
                            default:
                                if (strpos($type, 'char') !== false && strpos($type, '(') !== false) {
                                    // extract length from varchar
                                    $v['length'] = (int)explode(')', explode('(', $type)[1])[0];
                                    $v['length'] = $v['length'] > 0 ? $v['length'] : null;
                                }
                                break;
                        }
                        return $v;
                    })
                    ->toArray()
            )
            ->setPrimaryKey(
                $columns
                    ->clone()
                    ->filter(function ($v) {
                        return $v['Key'] === 'PRI';
                    })
                    ->pluck('Field')
                    ->toArray()
            )
            ->setComment($comments[$schema][$table] ?? '');

        if ($detectRelations) {
            // relations where the current table is referenced
            // assuming current table is on the "one" end having "many" records in the referencing table
            // resulting in a "hasMany" or "manyToMany" relationship (if a pivot table is detected)
            $relations = [];
            foreach ($relationsR[$schema . '.' . $table] ?? [] as $relation) {
                $relations[$relation['CONSTRAINT_NAME']]['table'] = $relation['TABLE_SCHEMA'] . '.' . $relation['TABLE_NAME'];
                $relations[$relation['CONSTRAINT_NAME']]['keymap'][$relation['REFERENCED_COLUMN_NAME']] =
                    $relation['COLUMN_NAME'];
            }
            foreach ($relations as $data) {
                $rtable = $this->table($data['table'], true);
                $columns = [];
                foreach ($rtable->getColumns() as $column) {
                    if (!in_array($column, $data['keymap'])) {
                        $columns[] = $column;
                    }
                }
                $foreign = [];
                $usedcol = [];
                if (count($columns)) {
                    foreach (Collection::from($relationsT[$data['table']] ?? [])
                        ->filter(function ($v) use ($columns) {
                            return in_array($v['COLUMN_NAME'], $columns);
                        })
                        ->map(function ($v) {
                            $new = [];
                            foreach ($v as $kk => $vv) {
                                $new[strtoupper($kk)] = $vv;
                            }
                            return $new;
                        }) as $relation
                    ) {
                        $foreign[$relation['CONSTRAINT_NAME']]['table'] = $relation['REFERENCED_TABLE_SCHEMA'] . '.' . $relation['REFERENCED_TABLE_NAME'];
                        $foreign[$relation['CONSTRAINT_NAME']]['keymap'][$relation['COLUMN_NAME']] =
                            $relation['REFERENCED_COLUMN_NAME'];
                        $usedcol[] = $relation['COLUMN_NAME'];
                    }
                }
                if (count($foreign) === 1 && !count(array_diff($columns, $usedcol))) {
                    $foreign = current($foreign);
                    $relname = $foreign['table'];
                    $temp = explode('.', $relname, 2);
                    if ($temp[0] == $main) {
                        $relname = $temp[1];
                    }
                    $orig = $relname;
                    $cntr = 1;
                    while ($definition->hasRelation($relname) || $definition->getName() == $relname) {
                        $relname = $orig . '_' . (++ $cntr);
                    }
                    $definition->addRelation(
                        new TableRelation(
                            $relname,
                            $this->table($foreign['table'], true),
                            $data['keymap'],
                            true,
                            $rtable,
                            $foreign['keymap']
                        )
                    );
                } else {
                    $relname = $data['table'];
                    $temp = explode('.', $relname, 2);
                    if ($temp[0] == $main) {
                        $relname = $temp[1];
                    }
                    $orig = $relname;
                    $cntr = 1;
                    while ($definition->hasRelation($relname) || $definition->getName() == $relname) {
                        $relname = $orig . '_' . (++ $cntr);
                    }
                    $definition->addRelation(
                        new TableRelation(
                            $relname,
                            $this->table($data['table'], true),
                            $data['keymap'],
                            true
                        )
                    );
                }
            }
            // relations where the current table references another table
            // assuming current table is linked to "one" record in the referenced table
            // resulting in a "belongsTo" relationship
            $relations = [];
            foreach (Collection::from($relationsT[$schema . '.' . $table] ?? [])
                ->map(function ($v) {
                    $new = [];
                    foreach ($v as $kk => $vv) {
                        $new[strtoupper($kk)] = $vv;
                    }
                    return $new;
                }) as $relation
            ) {
                $relations[$relation['CONSTRAINT_NAME']]['table'] = $relation['REFERENCED_TABLE_SCHEMA'] . '.' . $relation['REFERENCED_TABLE_NAME'];
                $relations[$relation['CONSTRAINT_NAME']]['keymap'][$relation['COLUMN_NAME']] =
                    $relation['REFERENCED_COLUMN_NAME'];
            }
            foreach ($relations as $name => $data) {
                $relname = $data['table'];
                $temp = explode('.', $relname, 2);
                if ($temp[0] == $main) {
                    $relname = $temp[1];
                }
                $orig = $relname;
                $cntr = 1;
                while ($definition->hasRelation($relname) || $definition->getName() == $relname) {
                    $relname = $orig . '_' . (++ $cntr);
                }
                $definition->addRelation(
                    new TableRelation(
                        $relname,
                        $this->table($data['table'], true),
                        $data['keymap'],
                        false
                    )
                );
            }
        }
        return $definition->toLowerCase();
    }
    public function tables() : array
    {
        return Collection::from($this
            ->query(
                "SELECT table_name FROM information_schema.tables where table_schema = ?",
                [$this->connection['opts']['schema'] ?? $this->connection['name']]
            ))
            ->map(function ($v) {
                $new = [];
                foreach ($v as $kk => $vv) {
                    $new[strtoupper($kk)] = $vv;
                }
                return $new;
            })
            ->pluck('TABLE_NAME')
            ->map(function ($v) {
                return $this->table($v);
            })
            ->toArray();
    }
}


1			<?php
2
3			namespace vakata\database\driver\mysql;
4
5			use \vakata\database\DBException;
6			use \vakata\database\DriverInterface;
7			use \vakata\database\DriverAbstract;
8			use \vakata\database\StatementInterface;
9			use \vakata\database\ResultInterface;
10			use \vakata\database\schema\Table;
11			use \vakata\database\schema\TableRelation;
12			use \vakata\collection\Collection;
13
14			trait Schema
15			{
16			protected $connection;
17			abstract public function query(string $sql, $par = null, bool $buff = true) : ResultInterface;
18
19			public function table(string $table, bool $detectRelations = true) : Table
20			{
21			static $tables = [];
22
23			$main = $this->connection['opts']['schema'] ?? $this->connection['name'];
24			$schema = $main;
25			if (strpos($table, '.')) {
26			$temp = explode('.', $table, 2);
27			$schema = $temp[0];
28			$table = $temp[1];
29			}
30
31			if (isset($tables[$schema . '.' . $table])) {
32			return $tables[$schema . '.' . $table];
33			}
34
35			static $comments = [];
36			if (!isset($comments[$schema])) {
37			$comments[$schema] = Collection::from(
38			$this->query(
39			"SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ?",
40			[ $schema ]
41			)
42			)
43			->mapKey(function ($v) {
44			return $v['TABLE_NAME'];
45			})
46			->pluck('TABLE_COMMENT')
47			->toArray();
48			}
49
50			static $relationsT = null;
51			static $relationsR = null;
52			if (!isset($relationsT) \|\| !isset($relationsR)) {
53			$relationsT = [];
54			$relationsR = [];
55			$additional = [];
56			$col = Collection::from(
57			$this->query(
58			"SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_TABLE_SCHEMA, REFERENCED_COLUMN_NAME
59			FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
60			WHERE
61			(TABLE_SCHEMA = ? OR REFERENCED_TABLE_SCHEMA = ?) AND
62			TABLE_NAME IS NOT NULL AND REFERENCED_TABLE_NAME IS NOT NULL",
63			[ $main, $main ]
64			)
65			)->toArray();
66			foreach ($col as $row) {
67			if ($row['TABLE_SCHEMA'] !== $main) {
68			$additional[] = $row['TABLE_SCHEMA'];
69			}
70			if ($row['REFERENCED_TABLE_SCHEMA'] !== $main) {
71			$additional[] = $row['REFERENCED_TABLE_SCHEMA'];
72			}
73			$relationsT[$row['TABLE_SCHEMA'] . '.' . $row['TABLE_NAME']][] = $row;
74			$relationsR[$row['REFERENCED_TABLE_SCHEMA'] . '.' . $row['REFERENCED_TABLE_NAME']][] = $row;
75			}
76			foreach (array_filter(array_unique($additional)) as $s) {
77			$col = Collection::from(
78			$this->query(
79			"SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_TABLE_SCHEMA, REFERENCED_COLUMN_NAME
80			FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
81			WHERE
82			TABLE_SCHEMA = ? AND REFERENCED_TABLE_SCHEMA = ? AND
83			TABLE_NAME IS NOT NULL AND REFERENCED_TABLE_NAME IS NOT NULL",
84			[ $s, $s ]
85			)
86			)->toArray();
87			foreach ($col as $row) {
88			$relationsT[$row['TABLE_SCHEMA'] . '.' . $row['TABLE_NAME']][] = $row;
89			$relationsR[$row['REFERENCED_TABLE_SCHEMA'] . '.' . $row['REFERENCED_TABLE_NAME']][] = $row;
90			}
91			}
92			}
93
94			$columns = Collection::from($this->query("SHOW FULL COLUMNS FROM {$schema}.{$table}"));
95			if (!count($columns)) {
96			throw new DBException('Table not found by name');
97			}
98			$tables[$schema . '.' . $table] = $definition = (new Table($table, $schema))
99			->addColumns(
100			$columns
101			->clone()
102			->mapKey(function ($v) {
103			return $v['Field'];
104			})
105			->map(function ($v) {
106			$v['length'] = null;
107			if (!isset($v['Type'])) {
108			return $v;
109			}
110			$type = strtolower($v['Type']);
111			switch ($type) {
112			case 'tinytext':
113			$v['length'] = 255;
114			break;
115			case 'text':
116			$v['length'] = 65535;
117			break;
118			case 'mediumtext':
119			$v['length'] = 16777215;
120			break;
121			case 'longtext':
122			// treat this as no limit
123			break;
124			default:
125			if (strpos($type, 'char') !== false && strpos($type, '(') !== false) {
126			// extract length from varchar
127			$v['length'] = (int)explode(')', explode('(', $type)[1])[0];
128			$v['length'] = $v['length'] > 0 ? $v['length'] : null;
129			}
130			break;
131			}
132			return $v;
133			})
134			->toArray()
135			)
136			->setPrimaryKey(
137			$columns
138			->clone()
139			->filter(function ($v) {
140			return $v['Key'] === 'PRI';
141			})
142			->pluck('Field')
143			->toArray()
144			)
145			->setComment($comments[$schema][$table] ?? '');
146
147			if ($detectRelations) {
148			// relations where the current table is referenced
149			// assuming current table is on the "one" end having "many" records in the referencing table
150			// resulting in a "hasMany" or "manyToMany" relationship (if a pivot table is detected)
151			$relations = [];
152			foreach ($relationsR[$schema . '.' . $table] ?? [] as $relation) {
153			$relations[$relation['CONSTRAINT_NAME']]['table'] = $relation['TABLE_SCHEMA'] . '.' . $relation['TABLE_NAME'];
154			$relations[$relation['CONSTRAINT_NAME']]['keymap'][$relation['REFERENCED_COLUMN_NAME']] =
155			$relation['COLUMN_NAME'];
156			}
157			foreach ($relations as $data) {
158			$rtable = $this->table($data['table'], true);
159			$columns = [];
160			foreach ($rtable->getColumns() as $column) {
161			if (!in_array($column, $data['keymap'])) {
162			$columns[] = $column;
163			}
164			}
165			$foreign = [];
166			$usedcol = [];
167			if (count($columns)) {
168			foreach (Collection::from($relationsT[$data['table']] ?? [])
169			->filter(function ($v) use ($columns) {
170			return in_array($v['COLUMN_NAME'], $columns);
171			})
172			->map(function ($v) {
173			$new = [];
174			foreach ($v as $kk => $vv) {
175			$new[strtoupper($kk)] = $vv;
176			}
177			return $new;
178			}) as $relation
179			) {
180			$foreign[$relation['CONSTRAINT_NAME']]['table'] = $relation['REFERENCED_TABLE_SCHEMA'] . '.' . $relation['REFERENCED_TABLE_NAME'];
181			$foreign[$relation['CONSTRAINT_NAME']]['keymap'][$relation['COLUMN_NAME']] =
182			$relation['REFERENCED_COLUMN_NAME'];
183			$usedcol[] = $relation['COLUMN_NAME'];
184			}
185			}
186			if (count($foreign) === 1 && !count(array_diff($columns, $usedcol))) {
187			$foreign = current($foreign);
188			$relname = $foreign['table'];
189			$temp = explode('.', $relname, 2);
190			if ($temp[0] == $main) {
191			$relname = $temp[1];
192			}
193			$orig = $relname;
194			$cntr = 1;
195			while ($definition->hasRelation($relname) \|\| $definition->getName() == $relname) {
196			$relname = $orig . '_' . (++ $cntr);
197			}
198			$definition->addRelation(
199			new TableRelation(
200			$relname,
201			$this->table($foreign['table'], true),
202			$data['keymap'],
203			true,
204			$rtable,
205			$foreign['keymap']
206			)
207			);
208			} else {
209			$relname = $data['table'];
210			$temp = explode('.', $relname, 2);
211			if ($temp[0] == $main) {
212			$relname = $temp[1];
213			}
214			$orig = $relname;
215			$cntr = 1;
216			while ($definition->hasRelation($relname) \|\| $definition->getName() == $relname) {
217			$relname = $orig . '_' . (++ $cntr);
218			}
219			$definition->addRelation(
220			new TableRelation(
221			$relname,
222			$this->table($data['table'], true),
223			$data['keymap'],
224			true
225			)
226			);
227			}
228			}
229			// relations where the current table references another table
230			// assuming current table is linked to "one" record in the referenced table
231			// resulting in a "belongsTo" relationship
232			$relations = [];
233			foreach (Collection::from($relationsT[$schema . '.' . $table] ?? [])
234			->map(function ($v) {
235			$new = [];
236			foreach ($v as $kk => $vv) {
237			$new[strtoupper($kk)] = $vv;
238			}
239			return $new;
240			}) as $relation
241			) {
242			$relations[$relation['CONSTRAINT_NAME']]['table'] = $relation['REFERENCED_TABLE_SCHEMA'] . '.' . $relation['REFERENCED_TABLE_NAME'];
243			$relations[$relation['CONSTRAINT_NAME']]['keymap'][$relation['COLUMN_NAME']] =
244			$relation['REFERENCED_COLUMN_NAME'];
245			}
246			foreach ($relations as $name => $data) {
247			$relname = $data['table'];
248			$temp = explode('.', $relname, 2);
249			if ($temp[0] == $main) {
250			$relname = $temp[1];
251			}
252			$orig = $relname;
253			$cntr = 1;
254			while ($definition->hasRelation($relname) \|\| $definition->getName() == $relname) {
255			$relname = $orig . '_' . (++ $cntr);
256			}
257			$definition->addRelation(
258			new TableRelation(
259			$relname,
260			$this->table($data['table'], true),
261			$data['keymap'],
262			false
263			)
264			);
265			}
266			}
267			return $definition->toLowerCase();
268			}
269			public function tables() : array
270			{
271			return Collection::from($this
272			->query(
273			"SELECT table_name FROM information_schema.tables where table_schema = ?",
274			[$this->connection['opts']['schema'] ?? $this->connection['name']]
275			))
276			->map(function ($v) {
277			$new = [];
278			foreach ($v as $kk => $vv) {
279			$new[strtoupper($kk)] = $vv;
280			}
281			return $new;
282			})
283			->pluck('TABLE_NAME')
284			->map(function ($v) {
285			return $this->table($v);
286			})
287			->toArray();
288			}
289			}
290

vakata / database

Push — master ( e5c5d6...6b5dbf )

Schema::tables() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like