SchemaLoader::parseInformationSchema() - Code Metrics - Inspection of "Merge branch 'main' of github.com:HexMakina/Crudit..." - HexMakina/Crudites - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — main ( 303b6c...b49db2 )

by Sammy

created 2024-12-02 23:29 UTC

SchemaLoader::parseInformationSchema() B

↳ Parent: SchemaLoader

Complexity

Conditions	6
Paths	6

Size

Total Lines	43
Code Lines	23

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	6
eloc	23
nc	6
nop	1
dl	0
loc	43
rs	8.9297
c	1
b	0
f	0

<?php

namespace HexMakina\Crudites\Schema;

use HexMakina\BlackBox\Database\SchemaInterface;
filter:
    dependency_paths: ["lib/*"]

/**
 * SchemaLoader
 * 
 * This class is responsible for loading the schema of a database.
 * 
 * The schema is loaded from the INFORMATION_SCHEMA database.
 * Using the tables and columns from the INFORMATION_SCHEMA database, 
 * such as TABLES, COLUMNS, KEY_COLUMN_USAGE, TABLE_CONSTRAINTS, and REFERENTIAL_CONSTRAINTS.
 * The class constructs an array of tables with their columns, primary keys, foreign keys, and unique keys.
 * 
 */

class SchemaLoader
{

    public static function cache(string $database): ?SchemaInterface
    {
        $cache_file = __DIR__ . '/cache/' . $database . '.php';
        if(!file_exists($cache_file)){
            return null;
        }

        return new Schema($database, require $cache_file);
    }

    public static function load(string $database, \PDO $pdo, string $schema_database = 'information_schema'): SchemaInterface
    {
        
        try {
            $pdo->beginTransaction();
            
            // switch to the INFORMATION_SCHEMA database
            if(false === $pdo->query(sprintf('USE %s;', $schema_database))){
                throw new CruditesException('SWICTH_TO_INFORMATION_SCHEMA');
filter:
    dependency_paths: ["lib/*"]
            }
            
            // get the schema information
            $res = $pdo->query(self::informationSchemaQuery($database));
            if(false === $res){
                throw new CruditesException('LOAD_INFORMATION_SCHEMA');
            }
            
            // switch back to the original database
            if(false === $pdo->query(sprintf('USE %s;', $database))){
                throw new CruditesException('SWICTH_BACK_TO_USER_DATABASE');
            }

            $pdo->commit();
            
        } catch (\Exception $e) {
            
            $pdo->rollBack();
            throw $e;
        }

        $res = $res->fetchAll();
        if($res === false){
            throw new CruditesException('SCHEMA_LOAD_FETCHALL');
        }
        
        if(empty($res)){
            throw new CruditesException('SCHEMA_LOAD_FETCHED_EMPTY_RESULTS');
        }


        return new Schema($database, self::parseInformationSchema($res));
    }
    
    /**
     * Parses the result of the INFORMATION_SCHEMA query and stores the information in arrays.
     *
     * @param array $information_schema_result The result of the INFORMATION_SCHEMA query.
     */
    private static function parseInformationSchema(array $information_schema_result): array
    {
        $tables = [];
        foreach ($information_schema_result as $res) {

            $table = $res['table'];
            $column = $res['column'];

            // initialize arrays
            $tables[$table] ??= ['columns' => [], 'primary' => [], 'foreign' => [], 'unique' => []];


            // store common column information once
            $tables[$table]['columns'][$column] ??= ['schema' => $res, 'unique' => []];

            // store primary keys, foreign keys, and unique keys
            if (!empty($res['constraint_type'])) {

                $constraint_name = $res['constraint'];
                switch ($res['constraint_type']) {

                    case 'PRIMARY KEY':
                        
                        $tables[$table]['primary'][] = $column;
                        break;

                    case 'FOREIGN KEY':
                        $reference = [$res['parent_table'], $res['parent_column'], $res['delete_rule'], $res['update_rule']];
                        $tables[$table]['foreign'][$column] ??= [];
                        $tables[$table]['foreign'][$column] = $reference;
                        break;

                    case 'UNIQUE':
                        $tables[$table]['unique'][$constraint_name] ??= [];
                        $tables[$table]['unique'][$constraint_name][] = $column;

                        $tables[$table]['columns'][$column]['unique'][] = $constraint_name;
                        break;
                }
            }
        }

        return $tables;
    }

    /**
     * Returns the query to get the schema information from the INFORMATION_SCHEMA database.
     *
     * @param string $database The name of the database.
     */
    private static function informationSchemaQuery(string $database): string
    {
        return "SELECT 
    `t`.`TABLE_NAME` AS `table`,
    `c`.`COLUMN_NAME` AS `column`,
    `c`.`COLUMN_DEFAULT` AS `default`,
    `c`.`COLUMN_TYPE` AS `column_type`,

    CASE
        WHEN `c`.`IS_NULLABLE` = 'YES' THEN 1
        ELSE NULL
    END AS `nullable`,

    CASE
        WHEN `c`.`DATA_TYPE` = 'bit' AND `c`.`NUMERIC_PRECISION` = 1 THEN 'boolean'
        WHEN `c`.`DATA_TYPE` IN ('bit', 'tinyint', 'smallint', 'mediumint', 'int', 'bigint') THEN 'integer'
        WHEN `c`.`DATA_TYPE` IN ('float', 'double', 'real') THEN 'float'
        WHEN `c`.`DATA_TYPE` IN ('decimal', 'numeric') THEN 'decimal'
        WHEN `c`.`DATA_TYPE` IN ('char', 'varchar') THEN 'string'
        WHEN `c`.`DATA_TYPE` IN ('text', 'tinytext', 'mediumtext', 'longtext') THEN 'text'
        WHEN `c`.`DATA_TYPE` = 'date' THEN 'date'
        WHEN `c`.`DATA_TYPE` = 'datetime' THEN 'datetime'
        WHEN `c`.`DATA_TYPE` = 'timestamp' THEN 'timestamp'
        WHEN `c`.`DATA_TYPE` = 'time' THEN 'time'
        WHEN `c`.`DATA_TYPE` = 'year' THEN 'year'
        WHEN `c`.`DATA_TYPE` = 'enum' THEN 'enum'
        ELSE 'unknown'
    END AS `type`,

    `c`.`CHARACTER_MAXIMUM_LENGTH` AS `length`,
    `c`.`NUMERIC_PRECISION` AS `precision`,
    `c`.`NUMERIC_SCALE` AS `scale`,

    CASE 
        WHEN `c`.`EXTRA` = 'auto_increment' THEN 1
        ELSE NULL
    END AS `auto_increment`,

    `tc`.`CONSTRAINT_TYPE` AS `constraint_type`,
    `kcu`.`CONSTRAINT_NAME` AS `constraint`,
    `kcu`.`REFERENCED_TABLE_NAME` AS `parent_table`,
    `kcu`.`REFERENCED_COLUMN_NAME` AS `parent_column`,
    `rc`.`DELETE_RULE` AS `delete_rule`,
    `rc`.`UPDATE_RULE` AS `update_rule`
FROM 
    `INFORMATION_SCHEMA`.`TABLES` AS `t`
JOIN 
    `INFORMATION_SCHEMA`.`COLUMNS` AS `c`
    ON `t`.`TABLE_NAME` = `c`.`TABLE_NAME`
    AND `t`.`TABLE_SCHEMA` = `c`.`TABLE_SCHEMA`
LEFT JOIN 
    `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` AS `kcu`
    ON `t`.`TABLE_NAME` = `kcu`.`TABLE_NAME`
    AND `c`.`COLUMN_NAME` = `kcu`.`COLUMN_NAME`
    AND `t`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA`
LEFT JOIN 
    `INFORMATION_SCHEMA`.`TABLE_CONSTRAINTS` AS `tc`
    ON `kcu`.`CONSTRAINT_NAME` = `tc`.`CONSTRAINT_NAME`
    AND `kcu`.`TABLE_SCHEMA` = `tc`.`CONSTRAINT_SCHEMA`
    AND `t`.`TABLE_NAME` = `tc`.`TABLE_NAME`
LEFT JOIN 
    `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS` AS `rc`
    ON `kcu`.`CONSTRAINT_NAME` = `rc`.`CONSTRAINT_NAME`
    AND `kcu`.`TABLE_SCHEMA` = `rc`.`CONSTRAINT_SCHEMA`
    AND `t`.`TABLE_NAME` = `rc`.`TABLE_NAME` 
WHERE 
    `t`.`TABLE_SCHEMA` = '$database'
    AND `t`.`TABLE_TYPE` = 'BASE TABLE'
ORDER BY 
    `t`.`TABLE_NAME`, `c`.`ORDINAL_POSITION`;
";
    }
}


1			<?php
2
3			namespace HexMakina\Crudites\Schema;
4
5			use HexMakina\BlackBox\Database\SchemaInterface;
			0 ignored issues – show Bug introduced 2024-12-02 21:05 UTC by Report Bug Copy Issue Report The type `HexMakina\BlackBox\Database\SchemaInterface` was not found. Maybe you did not declare it correctly or list all dependencies? The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g. `excluded_paths: ["lib/"]`, you can move it to the dependency path list as follows: filter: dependency_paths: ["lib/"] For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths Loading history...
6
7			/**
8			* SchemaLoader
9			*
10			* This class is responsible for loading the schema of a database.
11			*
12			* The schema is loaded from the INFORMATION_SCHEMA database.
13			* Using the tables and columns from the INFORMATION_SCHEMA database,
14			* such as TABLES, COLUMNS, KEY_COLUMN_USAGE, TABLE_CONSTRAINTS, and REFERENTIAL_CONSTRAINTS.
15			* The class constructs an array of tables with their columns, primary keys, foreign keys, and unique keys.
16			*
17			*/
18
19			class SchemaLoader
20			{
21
22			public static function cache(string $database): ?SchemaInterface
23			{
24			$cache_file = __DIR__ . '/cache/' . $database . '.php';
25			if(!file_exists($cache_file)){
26			return null;
27			}
28
29			return new Schema($database, require $cache_file);
30			}
31
32			public static function load(string $database, \PDO $pdo, string $schema_database = 'information_schema'): SchemaInterface
33			{
34
35			try {
36			$pdo->beginTransaction();
37
38			// switch to the INFORMATION_SCHEMA database
39			if(false === $pdo->query(sprintf('USE %s;', $schema_database))){
40			throw new CruditesException('SWICTH_TO_INFORMATION_SCHEMA');
			0 ignored issues – show Bug introduced 2024-12-02 23:31 UTC by Report Bug Copy Issue Report The type `HexMakina\Crudites\Schema\CruditesException` was not found. Maybe you did not declare it correctly or list all dependencies? The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g. `excluded_paths: ["lib/"]`, you can move it to the dependency path list as follows: filter: dependency_paths: ["lib/"] For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths Loading history...
41			}
42
43			// get the schema information
44			$res = $pdo->query(self::informationSchemaQuery($database));
45			if(false === $res){
46			throw new CruditesException('LOAD_INFORMATION_SCHEMA');
47			}
48
49			// switch back to the original database
50			if(false === $pdo->query(sprintf('USE %s;', $database))){
51			throw new CruditesException('SWICTH_BACK_TO_USER_DATABASE');
52			}
53
54			$pdo->commit();
55
56			} catch (\Exception $e) {
57
58			$pdo->rollBack();
59			throw $e;
60			}
61
62			$res = $res->fetchAll();
63			if($res === false){
64			throw new CruditesException('SCHEMA_LOAD_FETCHALL');
65			}
66
67			if(empty($res)){
68			throw new CruditesException('SCHEMA_LOAD_FETCHED_EMPTY_RESULTS');
69			}
70
71
72			return new Schema($database, self::parseInformationSchema($res));
73			}
74
75			/**
76			* Parses the result of the INFORMATION_SCHEMA query and stores the information in arrays.
77			*
78			* @param array $information_schema_result The result of the INFORMATION_SCHEMA query.
79			*/
80			private static function parseInformationSchema(array $information_schema_result): array
81			{
82			$tables = [];
83			foreach ($information_schema_result as $res) {
84
85			$table = $res['table'];
86			$column = $res['column'];
87
88			// initialize arrays
89			$tables[$table] ??= ['columns' => [], 'primary' => [], 'foreign' => [], 'unique' => []];
90
91
92			// store common column information once
93			$tables[$table]['columns'][$column] ??= ['schema' => $res, 'unique' => []];
94
95			// store primary keys, foreign keys, and unique keys
96			if (!empty($res['constraint_type'])) {
97
98			$constraint_name = $res['constraint'];
99			switch ($res['constraint_type']) {
100
101			case 'PRIMARY KEY':
102
103			$tables[$table]['primary'][] = $column;
104			break;
105
106			case 'FOREIGN KEY':
107			$reference = [$res['parent_table'], $res['parent_column'], $res['delete_rule'], $res['update_rule']];
108			$tables[$table]['foreign'][$column] ??= [];
109			$tables[$table]['foreign'][$column] = $reference;
110			break;
111
112			case 'UNIQUE':
113			$tables[$table]['unique'][$constraint_name] ??= [];
114			$tables[$table]['unique'][$constraint_name][] = $column;
115
116			$tables[$table]['columns'][$column]['unique'][] = $constraint_name;
117			break;
118			}
119			}
120			}
121
122			return $tables;
123			}
124
125			/**
126			* Returns the query to get the schema information from the INFORMATION_SCHEMA database.
127			*
128			* @param string $database The name of the database.
129			*/
130			private static function informationSchemaQuery(string $database): string
131			{
132			return "SELECT
133			`t`.`TABLE_NAME` AS `table`,
134			`c`.`COLUMN_NAME` AS `column`,
135			`c`.`COLUMN_DEFAULT` AS `default`,
136			`c`.`COLUMN_TYPE` AS `column_type`,
137
138			CASE
139			WHEN `c`.`IS_NULLABLE` = 'YES' THEN 1
140			ELSE NULL
141			END AS `nullable`,
142
143			CASE
144			WHEN `c`.`DATA_TYPE` = 'bit' AND `c`.`NUMERIC_PRECISION` = 1 THEN 'boolean'
145			WHEN `c`.`DATA_TYPE` IN ('bit', 'tinyint', 'smallint', 'mediumint', 'int', 'bigint') THEN 'integer'
146			WHEN `c`.`DATA_TYPE` IN ('float', 'double', 'real') THEN 'float'
147			WHEN `c`.`DATA_TYPE` IN ('decimal', 'numeric') THEN 'decimal'
148			WHEN `c`.`DATA_TYPE` IN ('char', 'varchar') THEN 'string'
149			WHEN `c`.`DATA_TYPE` IN ('text', 'tinytext', 'mediumtext', 'longtext') THEN 'text'
150			WHEN `c`.`DATA_TYPE` = 'date' THEN 'date'
151			WHEN `c`.`DATA_TYPE` = 'datetime' THEN 'datetime'
152			WHEN `c`.`DATA_TYPE` = 'timestamp' THEN 'timestamp'
153			WHEN `c`.`DATA_TYPE` = 'time' THEN 'time'
154			WHEN `c`.`DATA_TYPE` = 'year' THEN 'year'
155			WHEN `c`.`DATA_TYPE` = 'enum' THEN 'enum'
156			ELSE 'unknown'
157			END AS `type`,
158
159			`c`.`CHARACTER_MAXIMUM_LENGTH` AS `length`,
160			`c`.`NUMERIC_PRECISION` AS `precision`,
161			`c`.`NUMERIC_SCALE` AS `scale`,
162
163			CASE
164			WHEN `c`.`EXTRA` = 'auto_increment' THEN 1
165			ELSE NULL
166			END AS `auto_increment`,
167
168			`tc`.`CONSTRAINT_TYPE` AS `constraint_type`,
169			`kcu`.`CONSTRAINT_NAME` AS `constraint`,
170			`kcu`.`REFERENCED_TABLE_NAME` AS `parent_table`,
171			`kcu`.`REFERENCED_COLUMN_NAME` AS `parent_column`,
172			`rc`.`DELETE_RULE` AS `delete_rule`,
173			`rc`.`UPDATE_RULE` AS `update_rule`
174			FROM
175			`INFORMATION_SCHEMA`.`TABLES` AS `t`
176			JOIN
177			`INFORMATION_SCHEMA`.`COLUMNS` AS `c`
178			ON `t`.`TABLE_NAME` = `c`.`TABLE_NAME`
179			AND `t`.`TABLE_SCHEMA` = `c`.`TABLE_SCHEMA`
180			LEFT JOIN
181			`INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` AS `kcu`
182			ON `t`.`TABLE_NAME` = `kcu`.`TABLE_NAME`
183			AND `c`.`COLUMN_NAME` = `kcu`.`COLUMN_NAME`
184			AND `t`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA`
185			LEFT JOIN
186			`INFORMATION_SCHEMA`.`TABLE_CONSTRAINTS` AS `tc`
187			ON `kcu`.`CONSTRAINT_NAME` = `tc`.`CONSTRAINT_NAME`
188			AND `kcu`.`TABLE_SCHEMA` = `tc`.`CONSTRAINT_SCHEMA`
189			AND `t`.`TABLE_NAME` = `tc`.`TABLE_NAME`
190			LEFT JOIN
191			`INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS` AS `rc`
192			ON `kcu`.`CONSTRAINT_NAME` = `rc`.`CONSTRAINT_NAME`
193			AND `kcu`.`TABLE_SCHEMA` = `rc`.`CONSTRAINT_SCHEMA`
194			AND `t`.`TABLE_NAME` = `rc`.`TABLE_NAME`
195			WHERE
196			`t`.`TABLE_SCHEMA` = '$database'
197			AND `t`.`TABLE_TYPE` = 'BASE TABLE'
198			ORDER BY
199			`t`.`TABLE_NAME`, `c`.`ORDINAL_POSITION`;
200			";
201			}
202			}
203

HexMakina / Crudites

Push — main ( 303b6c...b49db2 )

SchemaLoader::parseInformationSchema() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like