SqlMySql::sanitizeDbStructure() - Code Metrics - Inspection of "Se añade también la comprobación de constraints en..." - rsanjoseo/alxarafe - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — main ( b4984c...46772d )

by Rafael

created 2023-02-05 08:59 UTC

SqlMySql::sanitizeDbStructure() B

↳ Parent: SqlMySql

Complexity

Conditions	10
Paths	10

Size

Total Lines	25
Code Lines	19

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	110

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	10
eloc	19
nc	10
nop	2
dl	0
loc	25
ccs	0
cts	10
cp	0
crap	110
rs	7.6666
c	1
b	0
f	0

How to fix Complexity

<?php
/**
 * Alxarafe. Development of PHP applications in a flash!
 * Copyright (C) 2018 Alxarafe <[email protected]>
 */

namespace Alxarafe\Database\SqlHelpers;

use Alxarafe\Core\Singletons\Debug;
use Alxarafe\Core\Utils\ArrayUtils;
use Alxarafe\Core\Singletons\Config;
use Alxarafe\Database\DB;
use Alxarafe\Database\Schema;
use Alxarafe\Database\SqlHelper;

/**
 * Class SqlMySql
 *
 * Soporte específico para la creación de comandos y consultas usando el motor MySQL.
 * Es usado directamente por la clase estática DB.
 *
 * @author  Rafael San José Tovar <[email protected]>
 * @version 2023.0108
 *
 * @package Alxarafe\Database\SqlHelpers
 */
class SqlMySql extends SqlHelper
{
    /**
     * Retorna las comillas que encierran al nombre de la tabla en una consulta SQL.
     *
     * @author  Rafael San José Tovar <[email protected]>
     * @version 2023.0108
     *
     * @return string
     */
    public static function getTableQuote(): string
    {
        return '`';
    }

    /**
     * Retorna las comillas que encierran al nombre de un campo en una consulta SQL
     *
     * @author  Rafael San José Tovar <[email protected]>
     * @version 2023.0108
     *
     * @return string
     */
    public static function getFieldQuote(): string
    {
        return '"';
    }

    /**
     * Retorna true si la tabla existe en la base de datos.
     *
     * @author  Rafael San José Tovar <[email protected]>
     * @version 2023.0106
     *
     * @param string $tableName
     *
     * @return bool
     */
    public static function tableExists(string $tableName): bool
    {
        $dbName = DB::$dbName;
        $sql = "SELECT COUNT(*) AS Total FROM information_schema.tables WHERE table_schema = '{$dbName}' AND table_name='{$tableName}'";

        $data = DB::select($sql);
        $result = reset($data);

        return $result['Total'] === '1';
    }

    /**
     * Retorna un array con la asociación de tipos del motor SQL para cada tipo definido
     * en el Schema.
     *
     * @author  Rafael San José Tovar <[email protected]>
     * @version 2023.0108
     *
     * @return array[]
     */
    public static function getDataTypes(): array
    {
        return [
            Schema::TYPE_INTEGER => ['tinyint', 'smallint', 'mediumint', 'int', 'bigint'],
            Schema::TYPE_FLOAT => ['real', 'double'],
            Schema::TYPE_DECIMAL => ['decimal', 'numeric'],
            Schema::TYPE_STRING => ['char', 'varchar'],
            Schema::TYPE_TEXT => ['tinytext', 'text', 'mediumtext', 'longtext', 'blob'],
            Schema::TYPE_DATE => ['date'],
            Schema::TYPE_TIME => ['time'],
            Schema::TYPE_DATETIME => ['datetime', 'timestamp'],
            Schema::TYPE_BOOLEAN => ['boolean'],
        ];
    }

    /**
     * Retorna un array con el nombre de todas las tablas de la base de datos.
     *
     * @return array
     */
    public static function getTables(): array
    {
        $query = 'SHOW TABLES';
        return ArrayUtils::flatArray(DB::select($query));
    }

    /**
     * Retorna el tipo de dato que se utiliza para los índices autoincrementados
     *
     * @author  Rafael San José Tovar <[email protected]>
     * @version 2023.0108
     *
     * @return string
     */
    public static function getIndexType(): string
    {
        return 'bigint(20) unsigned';
    }

    /**
     * Retorna los datos necesarios para definir un número enero, sabiendo cuántos
     * bytes tiene de tamaño y si tiene o no signo.
     *
     * @author Rafael San José Tovar <[email protected]>
     *
     * @param int  $size
     * @param bool $unsigned
     *
     * @return array
     */
    public static function getIntegerMinMax(int $size, bool $unsigned): array
    {
        switch ($size) {
            case 1:
                $type = 'tinyint';
                break;
            case 2:
                $type = 'smallint';
                break;
            case 3:
                $type = 'mediumint';
                break;
            case 4:
                $type = 'int';
                break;
            default:
                $type = 'bigint';
                $size = 8;
                break;
        }

        $bits = 8 * (int) $size;
        $physicalMaxLength = 2 ** $bits;

        /**
         * $minDataLength y $maxDataLength contendrán el mínimo y máximo valor que puede contener el campo.
         */
        $minDataLength = $unsigned ? 0 : -$physicalMaxLength / 2;
        $maxDataLength = ($unsigned ? $physicalMaxLength : $physicalMaxLength / 2) - 1;

        /**
         * De momento, se asignan los límites máximos por el tipo de dato.
         * En $min y $max, iremos arrastrando los límites conforme se vayan comprobando.
         * $min nunca podrá ser menor que $minDataLength.
         * $max nunca podrá ser mayor que $maxDataLength.
         */
        $min = $minDataLength;
        $max = $maxDataLength;

        return [
            'dbtype' => $type,
            'min' => $min,
            'max' => $max,
            'size' => $size,
            'unsigned' => $unsigned,
        ];
    }

    /**
     * Retorna un array asociativo con la información de cada columna de la tabla.
     * El resultado será dependiente del motor de base de datos.
     *
     * @author  Rafael San José Tovar <[email protected]>
     * @version 2023.0108
     *
     * @param string $tableName
     *
     * @return array
     */
    public static function getColumns(string $tableName): array
    {
        $query = 'SHOW COLUMNS FROM ' . self::quoteTableName($tableName) . ';';
        $rows = DB::select($query);
        $result = [];
        foreach ($rows as $row) {
            unset($row['Key']);  // Los índices están gestionados por separado
            $result[$row['Field']] = $row;
        }
        return $result;
    }

    public static function yamlFieldIntegerToDb(array $data): string
    {
        $type = $data['dbtype'];
        // TODO: Aunque lo que está comentado va, igual no hace falta si al comparar
        //       ignoramos el tamaño a mostrar para los integer

        $unsigned = $data['unsigned'] ?? false;
        /*
        switch ($type) {
            case 'tinyint':
                return $type . ($unsigned ? '(3) unsigned' : '(4)');
            case 'smallint':
                break;
            case 'mediumint':
                break;
            case 'int':
                return $type . ($unsigned ? '(10) unsigned' : '(11)');
            case 'bigint':
                $type .= '(20)';
        }
        */
        return $type . ($unsigned ? ' unsigned' : '');
    }

    public static function yamlFieldToDb(array $data): array
    {
        $nullable = strtolower($data['nullable']) !== 'no';

        $result = [];
        $result['Field'] = $data['name'];

        $type = $data['dbtype'];
        switch ($data['generictype']) {
            case Schema::TYPE_INTEGER:
                $type = self::yamlFieldIntegerToDb($data);
                break;
            case Schema::TYPE_FLOAT:
            case Schema::TYPE_DECIMAL:
                break;
            case Schema::TYPE_STRING:
                $type = 'varchar(' . $data['length'] . ')';
                break;
            case Schema::TYPE_TEXT:
            case Schema::TYPE_DATE:
            case Schema::TYPE_TIME:
            case Schema::TYPE_DATETIME:
                break;
            case Schema::TYPE_BOOLEAN:
                //                $type = 'tinyint(1)';
                break;
        }
        $result['Type'] = $type;
        $result['Null'] = $nullable ? 'YES' : 'NO';
        $result['Key'] = $data['type'] === 'autoincrement' ? 'PRI' : '';
        $result['Default'] = $data['default'] ?? null;
        $result['Extra'] = $data['type'] === 'autoincrement' ? 'auto_increment' : '';
        return $result;
    }

    public static function yamlIndexToDb(array $data): array
    {
        $result = [];
        foreach ($data['fields'] as $name => $field) {
            if ($field['type'] === 'autoincrement') {
                $result['PRIMARY'] = [
                    'column' => $name,
                    'primary' => 'yes',
                ];
            }
        }
        return array_merge($result, $data['indexes'] ?? []);
    }

    public static function getSqlField(array $column): string
    {
        $field = $column['Field'];
        $type = $column['Type'];
        $null = $column['Null'];
        $key = $column['Key'];

        $default = $column['Default'];
        $extra = $column['Extra'];

        $sql = self::quoteTableName($field) . ' ' . $type;
        $nulo = ($null === 'YES');
        if ($extra === 'auto_increment') {
            $nulo = false;
            $sql .= ' PRIMARY KEY AUTO_INCREMENT';
        }

        $sql .= ($nulo ? '' : ' NOT') . ' NULL';

        $defecto = '';
        if (isset($default)) {
            if ($default === 'CURRENT_TIMESTAMP') {
                $defecto = $default;
            } elseif (is_bool($default)) {
                $defecto = $default ? 1 : 0;
            } else {
                $defecto = "'$defecto'";
            }
        } else {
            if ($nulo) {
                $defecto = 'NULL';
            }
        }

        if (!empty($defecto)) {
            $sql .= ' DEFAULT ' . $defecto;
        }
        return $sql;
    }

    public static function _dbFieldToSchema(array $data): array
    {
        return $data;
    }

    public static function _dbFieldToYaml(array $data): array
    {
        return $data;
    }

    /**
     * Recibiendo un array con los datos de un campo tal y como lo retorna la base de
     * datos, devuelve la información normalizada para ser utilizada por Schema.
     *
     * @author  Rafael San José Tovar <[email protected]>
     * @version 2023.0108
     *
     * @param array $row
     *
     * @return array
     */
    public static function _normalizeDbField(array $row): array
    {
        $result = [];
        $result['Field'] = $row['key'];
        $result['Type'] = $row['type'];
        $result['Null'] = $row['nullable'] ? 'YES' : 'NO';
        $result['Key'] = $row['type'] === 'autoincrement' ? 'PRI' : '';
        $result['Default'] = $row['default'] ?? null;
        $result['Extra'] = $row['type'] === 'autoincrement' ? 'auto_increment' : '';
        return $result;
    }

    /**
     * Divide the data type of a MySQL field into its various components: type,
     * length, unsigned or zerofill, if applicable.
     *
     * @param string $originalType
     *
     * @return array
     */
    private static function _splitType(string $originalType): array

    {
        $explode = explode(' ', strtolower($originalType));

        $pos = strpos($explode[0], '(');

        $type = $pos ? substr($explode[0], 0, $pos) : $explode[0];
        $length = $pos ? intval(substr($explode[0], $pos + 1)) : null;

        $pos = array_search('unsigned', $explode);
        $unsigned = $pos ? 'unsigned' : null;

        $pos = array_search('zerofill', $explode);
        $zerofill = $pos ? 'zerofill' : null;

        return ['type' => $type, 'length' => $length, 'unsigned' => $unsigned, 'zerofill' => $zerofill];
    }

    /**
     * Returns an array with the index information, and if there are, also constraints.
     *
     * @param array $row
     *
     * @return array
     */
    public static function normalizeIndexes(array $row): array
    {
        $result = [];
        $result['index'] = $row['Key_name'];
        $result['column'] = $row['Column_name'];
        $result['unique'] = $row['Non_unique'] == '0' ? 1 : 0;
        $result['nullable'] = $row['Null'] == 'YES' ? 1 : 0;
        $constrait = self::getConstraintData($row['Table'], $row['Key_name']);
        if (count($constrait) > 0) {
            $result['constraint'] = $constrait[0]['CONSTRAINT_NAME'];
            $result['referencedtable'] = $constrait[0]['REFERENCED_TABLE_NAME'];
            $result['referencedfields'] = $constrait[0]['REFERENCED_COLUMN_NAME'];
        }
        $constrait = self::getConstraintRules($row['Table'], $row['Key_name']);
        if (count($constrait) > 0) {
            $result['matchoption'] = $constrait[0]['MATCH_OPTION'];
            $result['updaterule'] = $constrait[0]['UPDATE_RULE'];
            $result['deleterule'] = $constrait[0]['DELETE_RULE'];
        }
        return $result;
    }

    /**
     * The data about the constraint that is found in the KEY_COLUMN_USAGE table
     * is returned.
     * Attempting to return the consolidated data generates an extremely slow query
     * in some MySQL installations, so 2 additional simple queries are made.
     *
     * @param string $tableName
     * @param string $constraintName
     *
     * @return array
     */
    private static function getConstraintData(string $tableName, string $constraintName): array
    {
        $dbName = DB::$dbName;

        return DB::select('
SELECT
	TABLE_NAME,
	COLUMN_NAME,
	CONSTRAINT_NAME,
	REFERENCED_TABLE_NAME,
	REFERENCED_COLUMN_NAME
FROM
	INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
	TABLE_SCHEMA = ' . self::quoteFieldName($dbName) . ' AND
	TABLE_NAME = ' . self::quoteFieldName($tableName) . ' AND
	constraint_name = ' . self::quoteFieldName($constraintName) . ' AND
	REFERENCED_COLUMN_NAME IS NOT NULL;
        ');
    }

    /**
     * The rules for updating and deleting data with constraint (table
     * REFERENTIAL_CONSTRAINTS) are returned.
     * Attempting to return the consolidated data generates an extremely slow query
     * in some MySQL installations, so 2 additional simple queries are made.
     *
     * @param string $tableName
     * @param string $constraintName
     *
     * @return array
     */
    private static function getConstraintRules(string $tableName, string $constraintName): array
    {
        $dbName = DB::$dbName;

        return DB::select('
SELECT
	MATCH_OPTION,
	UPDATE_RULE,
	DELETE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE
	constraint_schema = ' . self::quoteFieldName($dbName) . ' AND
	table_name = ' . self::quoteFieldName($tableName) . ' AND
	constraint_name = ' . self::quoteFieldName($constraintName) . ';
        ');
    }

    /**
     * Obtain an array with the basic information about the indexes of the table,
     * which will be supplemented with the restrictions later.
     *
     * @param string $tableName
     *
     * @return string
     */
    public static function getIndexesSql(string $tableName): string
    {
        // https://stackoverflow.com/questions/5213339/how-to-see-indexes-for-a-database-or-table-in-mysql

        return 'SHOW INDEX FROM ' . self::quoteTableName($tableName);
    }

    /**
     * Toma la estructura de un campo obtenida de la base de datos, y la retorna
     * de la misma forma en la que se usó al ser creada.
     * Esto es necesario, porque algunas bases de datos cambian tipos como boolean por
     * tinyint(1), o int por int(10)
     *
     * @author Rafael San José Tovar <[email protected]>
     *
     * @param string $genericType
     * @param array  $structure
     *
     * @return array
     */
    public static function sanitizeDbStructure(string $genericType, array $structure): array
    {
        $type = $structure['Type'];
        switch ($genericType) {
            // Tipos que no cambian
            case Schema::TYPE_FLOAT:
            case Schema::TYPE_DECIMAL:
            case Schema::TYPE_STRING:
            case Schema::TYPE_TEXT:
            case Schema::TYPE_DATE:
            case Schema::TYPE_TIME:
            case Schema::TYPE_DATETIME:
                break;
            // Tipos a los que hay que quitar los paréntesis
            case Schema::TYPE_INTEGER:
                $type = preg_replace("/\((.*?)\)/i", "", $type);
                break;
            // Tipos que cambian durante la creación
            case Schema::TYPE_BOOLEAN:
                $type = 'boolean'; // Se crea como boolean y se retorna como tinyint(1)
                $structure['Default'] = ($structure['Default'] === '1');
                break;
        }
        $structure['Type'] = $type;
        return $structure;
    }

    public static function modify(string $tableName, array $oldField, array $newField): string
    {
        $sql = 'ALTER TABLE ' . self::quoteTableName($tableName) . ' CHANGE ' . $oldField['Field'] . ' ' . $newField['Field'] . ' ';
        $sql .= $newField['Type'] . ' ';
        if (strtolower($newField['Null']) === 'no') {
            $sql .= 'NOT ';
        }
        $sql .= 'NULL';
        if ($newField['Default'] !== null) {
            if ($newField['Type'] === 'boolean') {
                $newField['Default'] = $newField['Default'] ? '1' : '0';
            }
            $sql .= ' DEFAULT "' . $newField['Default'] . '"';
        }
        $sql .= ';';
        return $sql;
    }

    /**
     * Obtains an array of indexes for a table
     *
     * @param string $tableName
     *
     * @return array
     * @throws \DebugBar\DebugBarException
     */
    public static function getIndexes(string $tableName): array
    {
        $query = self::getIndexesSql($tableName);
        $data = DB::select($query);
        $result = [];
        foreach ($data as $value) {
            $row = self::normalizeIndexes($value);
            $result[$row['index']] = $row;
        }

        return $result;
    }

    public static function createIndex(string $tableName, string $index, array $data): string
    {
        $name = $data['column'];

        // Si es una clave primaria, ya fue creada en la definición de la tabla
        if ($data['primary'] === 'yes') {
            return ''; // return "ALTER TABLE `$tableName` PRIMARY KEY ($name);";
        }

        $sql = "ALTER TABLE `$tableName` ADD CONSTRAINT `$index` ";
        if ($data['unique'] === 'yes') {
            return $sql . "UNIQUE ($name);";
        }

        if (!isset($data['referencedtable'])) {
            return $sql . "INDEX ($name);";
        }

        $referencedTable = $data['referencedtable'];
        $referencedFields = $data['referencedfields'];
        $updaterule = strtoupper($data['updaterule']);
        $deleterule = strtoupper($data['deleterule']);

        $sql .= "FOREIGN KEY ($name) REFERENCES $referencedTable ($referencedFields) ON DELETE $deleterule ON UPDATE $updaterule;";

        return $sql;
    }

    public static function changeIndex(string $tableName, string $index, array $oldData, array $newData): string
    {
        $oldPrimary = $oldData['index'] === 'PRIMARY';
        $oldUnique = $oldData['unique'] === 1;
        $oldReferencedTable = $oldData['referencedtable'] ?? null;

        $newPrimary = $newData['primary'] === 'yes';
        $newUnique = $newData['unique'] === 'yes';
        $newReferencedTable = $newData['referencedtable'] ?? null;

        $ok = true;
        $ok = $ok && ($oldData['column'] === $newData['column']);
        $ok = $ok && ($oldPrimary === $newPrimary);
        $ok = $ok && ($oldReferencedTable === $newReferencedTable);

        // Si es primaria, es unique siempre así que solo comprobamos si no es unique
        if ($ok && !$oldPrimary) {
            $ok = $ok && $oldUnique === $newUnique;
        }

        // No hay cambios y no hay constraint
        if ($ok && !isset($newReferencedTable)) {
            return '';
        }

        // Si hay constraint, entonces hay que verificar si ha cambiado.
        $oldReferencedFields = strtolower($oldData['referencedfields']) ?? '';
        $oldUpdateRule = strtolower($oldData['updaterule']) ?? '';
        $oldDeleteRule = strtolower($oldData['deleterule']) ?? '';

        $newReferencedFields = strtolower($newData['referencedfields']) ?? '';
        $newUpdateRule = strtolower($newData['updaterule']) ?? '';
        $newDeleteRule = strtolower($newData['deleterule']) ?? '';

        if ($oldReferencedFields === $newReferencedFields && $oldUpdateRule === $newUpdateRule && $oldDeleteRule === $newDeleteRule) {
            return '';
        }

        // Se elimina el índice y se vuelve a crear
        return self::removeIndex($tableName, $index) . self::createIndex($tableName, $index, $newData);
    }

    public static function removeIndex(string $tableName, string $index): string
    {
        $sql = "ALTER TABLE `$tableName` DROP CONSTRAINT `$index`;";

        return $sql;
    }

}


1			<?php
2			/**
3			* Alxarafe. Development of PHP applications in a flash!
4			* Copyright (C) 2018 Alxarafe <[email protected]>
5			*/
6
7			namespace Alxarafe\Database\SqlHelpers;
8
9			use Alxarafe\Core\Singletons\Debug;
10			use Alxarafe\Core\Utils\ArrayUtils;
11			use Alxarafe\Core\Singletons\Config;
12			use Alxarafe\Database\DB;
13			use Alxarafe\Database\Schema;
14			use Alxarafe\Database\SqlHelper;
15
16			/**
17			* Class SqlMySql
18			*
19			* Soporte específico para la creación de comandos y consultas usando el motor MySQL.
20			* Es usado directamente por la clase estática DB.
21			*
22			* @author Rafael San José Tovar <[email protected]>
23			* @version 2023.0108
24			*
25			* @package Alxarafe\Database\SqlHelpers
26			*/
27			class SqlMySql extends SqlHelper
28			{
29			/**
30			* Retorna las comillas que encierran al nombre de la tabla en una consulta SQL.
31			*
32			* @author Rafael San José Tovar <[email protected]>
33			* @version 2023.0108
34			*
35			* @return string
36			*/
37			public static function getTableQuote(): string
38			{
39			return '`';
40			}
41
42			/**
43			* Retorna las comillas que encierran al nombre de un campo en una consulta SQL
44			*
45			* @author Rafael San José Tovar <[email protected]>
46			* @version 2023.0108
47			*
48			* @return string
49			*/
50			public static function getFieldQuote(): string
51			{
52			return '"';
53			}
54
55			/**
56			* Retorna true si la tabla existe en la base de datos.
57			*
58			* @author Rafael San José Tovar <[email protected]>
59			* @version 2023.0106
60			*
61			* @param string $tableName
62			*
63			* @return bool
64			*/
65			public static function tableExists(string $tableName): bool
66			{
67			$dbName = DB::$dbName;
68			$sql = "SELECT COUNT(*) AS Total FROM information_schema.tables WHERE table_schema = '{$dbName}' AND table_name='{$tableName}'";
69
70			$data = DB::select($sql);
71			$result = reset($data);
72
73			return $result['Total'] === '1';
74			}
75
76			/**
77			* Retorna un array con la asociación de tipos del motor SQL para cada tipo definido
78			* en el Schema.
79			*
80			* @author Rafael San José Tovar <[email protected]>
81			* @version 2023.0108
82			*
83			* @return array[]
84			*/
85			public static function getDataTypes(): array
86			{
87			return [
88			Schema::TYPE_INTEGER => ['tinyint', 'smallint', 'mediumint', 'int', 'bigint'],
89			Schema::TYPE_FLOAT => ['real', 'double'],
90			Schema::TYPE_DECIMAL => ['decimal', 'numeric'],
91			Schema::TYPE_STRING => ['char', 'varchar'],
92			Schema::TYPE_TEXT => ['tinytext', 'text', 'mediumtext', 'longtext', 'blob'],
93			Schema::TYPE_DATE => ['date'],
94			Schema::TYPE_TIME => ['time'],
95			Schema::TYPE_DATETIME => ['datetime', 'timestamp'],
96			Schema::TYPE_BOOLEAN => ['boolean'],
97			];
98			}
99
100			/**
101			* Retorna un array con el nombre de todas las tablas de la base de datos.
102			*
103			* @return array
104			*/
105			public static function getTables(): array
106			{
107			$query = 'SHOW TABLES';
108			return ArrayUtils::flatArray(DB::select($query));
109			}
110
111			/**
112			* Retorna el tipo de dato que se utiliza para los índices autoincrementados
113			*
114			* @author Rafael San José Tovar <[email protected]>
115			* @version 2023.0108
116			*
117			* @return string
118			*/
119			public static function getIndexType(): string
120			{
121			return 'bigint(20) unsigned';
122			}
123
124			/**
125			* Retorna los datos necesarios para definir un número enero, sabiendo cuántos
126			* bytes tiene de tamaño y si tiene o no signo.
127			*
128			* @author Rafael San José Tovar <[email protected]>
129			*
130			* @param int $size
131			* @param bool $unsigned
132			*
133			* @return array
134			*/
135			public static function getIntegerMinMax(int $size, bool $unsigned): array
136			{
137			switch ($size) {
138			case 1:
139			$type = 'tinyint';
140			break;
141			case 2:
142			$type = 'smallint';
143			break;
144			case 3:
145			$type = 'mediumint';
146			break;
147			case 4:
148			$type = 'int';
149			break;
150			default:
151			$type = 'bigint';
152			$size = 8;
153			break;
154			}
155
156			$bits = 8 * (int) $size;
157			$physicalMaxLength = 2 ** $bits;
158
159			/**
160			* $minDataLength y $maxDataLength contendrán el mínimo y máximo valor que puede contener el campo.
161			*/
162			$minDataLength = $unsigned ? 0 : -$physicalMaxLength / 2;
163			$maxDataLength = ($unsigned ? $physicalMaxLength : $physicalMaxLength / 2) - 1;
164
165			/**
166			* De momento, se asignan los límites máximos por el tipo de dato.
167			* En $min y $max, iremos arrastrando los límites conforme se vayan comprobando.
168			* $min nunca podrá ser menor que $minDataLength.
169			* $max nunca podrá ser mayor que $maxDataLength.
170			*/
171			$min = $minDataLength;
172			$max = $maxDataLength;
173
174			return [
175			'dbtype' => $type,
176			'min' => $min,
177			'max' => $max,
178			'size' => $size,
179			'unsigned' => $unsigned,
180			];
181			}
182
183			/**
184			* Retorna un array asociativo con la información de cada columna de la tabla.
185			* El resultado será dependiente del motor de base de datos.
186			*
187			* @author Rafael San José Tovar <[email protected]>
188			* @version 2023.0108
189			*
190			* @param string $tableName
191			*
192			* @return array
193			*/
194			public static function getColumns(string $tableName): array
195			{
196			$query = 'SHOW COLUMNS FROM ' . self::quoteTableName($tableName) . ';';
197			$rows = DB::select($query);
198			$result = [];
199			foreach ($rows as $row) {
200			unset($row['Key']); // Los índices están gestionados por separado
201			$result[$row['Field']] = $row;
202			}
203			return $result;
204			}
205
206			public static function yamlFieldIntegerToDb(array $data): string
207			{
208			$type = $data['dbtype'];
209			// TODO: Aunque lo que está comentado va, igual no hace falta si al comparar
210			// ignoramos el tamaño a mostrar para los integer
211
212			$unsigned = $data['unsigned'] ?? false;
213			/*
214			switch ($type) {
215			case 'tinyint':
216			return $type . ($unsigned ? '(3) unsigned' : '(4)');
217			case 'smallint':
218			break;
219			case 'mediumint':
220			break;
221			case 'int':
222			return $type . ($unsigned ? '(10) unsigned' : '(11)');
223			case 'bigint':
224			$type .= '(20)';
225			}
226			*/
227			return $type . ($unsigned ? ' unsigned' : '');
228			}
229
230			public static function yamlFieldToDb(array $data): array
231			{
232			$nullable = strtolower($data['nullable']) !== 'no';
233
234			$result = [];
235			$result['Field'] = $data['name'];
236
237			$type = $data['dbtype'];
238			switch ($data['generictype']) {
239			case Schema::TYPE_INTEGER:
240			$type = self::yamlFieldIntegerToDb($data);
241			break;
242			case Schema::TYPE_FLOAT:
243			case Schema::TYPE_DECIMAL:
244			break;
245			case Schema::TYPE_STRING:
246			$type = 'varchar(' . $data['length'] . ')';
247			break;
248			case Schema::TYPE_TEXT:
249			case Schema::TYPE_DATE:
250			case Schema::TYPE_TIME:
251			case Schema::TYPE_DATETIME:
252			break;
253			case Schema::TYPE_BOOLEAN:
254			// $type = 'tinyint(1)';
255			break;
256			}
257			$result['Type'] = $type;
258			$result['Null'] = $nullable ? 'YES' : 'NO';
259			$result['Key'] = $data['type'] === 'autoincrement' ? 'PRI' : '';
260			$result['Default'] = $data['default'] ?? null;
261			$result['Extra'] = $data['type'] === 'autoincrement' ? 'auto_increment' : '';
262			return $result;
263			}
264
265			public static function yamlIndexToDb(array $data): array
266			{
267			$result = [];
268			foreach ($data['fields'] as $name => $field) {
269			if ($field['type'] === 'autoincrement') {
270			$result['PRIMARY'] = [
271			'column' => $name,
272			'primary' => 'yes',
273			];
274			}
275			}
276			return array_merge($result, $data['indexes'] ?? []);
277			}
278
279			public static function getSqlField(array $column): string
280			{
281			$field = $column['Field'];
282			$type = $column['Type'];
283			$null = $column['Null'];
284			$key = $column['Key'];
			0 ignored issues – show Unused Code introduced 2023-01-27 20:28 UTC by Report Bug Copy Issue Report The assignment to `$key` is dead and can be removed. Loading history...
285			$default = $column['Default'];
286			$extra = $column['Extra'];
287
288			$sql = self::quoteTableName($field) . ' ' . $type;
289			$nulo = ($null === 'YES');
290			if ($extra === 'auto_increment') {
291			$nulo = false;
292			$sql .= ' PRIMARY KEY AUTO_INCREMENT';
293			}
294
295			$sql .= ($nulo ? '' : ' NOT') . ' NULL';
296
297			$defecto = '';
298			if (isset($default)) {
299			if ($default === 'CURRENT_TIMESTAMP') {
300			$defecto = $default;
301			} elseif (is_bool($default)) {
302			$defecto = $default ? 1 : 0;
303			} else {
304			$defecto = "'$defecto'";
305			}
306			} else {
307			if ($nulo) {
308			$defecto = 'NULL';
309			}
310			}
311
312			if (!empty($defecto)) {
313			$sql .= ' DEFAULT ' . $defecto;
314			}
315			return $sql;
316			}
317
318			public static function _dbFieldToSchema(array $data): array
319			{
320			return $data;
321			}
322
323			public static function _dbFieldToYaml(array $data): array
324			{
325			return $data;
326			}
327
328			/**
329			* Recibiendo un array con los datos de un campo tal y como lo retorna la base de
330			* datos, devuelve la información normalizada para ser utilizada por Schema.
331			*
332			* @author Rafael San José Tovar <[email protected]>
333			* @version 2023.0108
334			*
335			* @param array $row
336			*
337			* @return array
338			*/
339			public static function _normalizeDbField(array $row): array
340			{
341			$result = [];
342			$result['Field'] = $row['key'];
343			$result['Type'] = $row['type'];
344			$result['Null'] = $row['nullable'] ? 'YES' : 'NO';
345			$result['Key'] = $row['type'] === 'autoincrement' ? 'PRI' : '';
346			$result['Default'] = $row['default'] ?? null;
347			$result['Extra'] = $row['type'] === 'autoincrement' ? 'auto_increment' : '';
348			return $result;
349			}
350
351			/**
352			* Divide the data type of a MySQL field into its various components: type,
353			* length, unsigned or zerofill, if applicable.
354			*
355			* @param string $originalType
356			*
357			* @return array
358			*/
359			private static function _splitType(string $originalType): array
			0 ignored issues – show Unused Code introduced 2023-01-27 20:28 UTC by Report Bug Copy Issue Report The method `_splitType()` is not used, and could be removed. This check looks for private methods that have been defined, but are not used inside the class. Loading history...
360			{
361			$explode = explode(' ', strtolower($originalType));
362
363			$pos = strpos($explode[0], '(');
364
365			$type = $pos ? substr($explode[0], 0, $pos) : $explode[0];
366			$length = $pos ? intval(substr($explode[0], $pos + 1)) : null;
367
368			$pos = array_search('unsigned', $explode);
369			$unsigned = $pos ? 'unsigned' : null;
370
371			$pos = array_search('zerofill', $explode);
372			$zerofill = $pos ? 'zerofill' : null;
373
374			return ['type' => $type, 'length' => $length, 'unsigned' => $unsigned, 'zerofill' => $zerofill];
375			}
376
377			/**
378			* Returns an array with the index information, and if there are, also constraints.
379			*
380			* @param array $row
381			*
382			* @return array
383			*/
384			public static function normalizeIndexes(array $row): array
385			{
386			$result = [];
387			$result['index'] = $row['Key_name'];
388			$result['column'] = $row['Column_name'];
389			$result['unique'] = $row['Non_unique'] == '0' ? 1 : 0;
390			$result['nullable'] = $row['Null'] == 'YES' ? 1 : 0;
391			$constrait = self::getConstraintData($row['Table'], $row['Key_name']);
392			if (count($constrait) > 0) {
393			$result['constraint'] = $constrait[0]['CONSTRAINT_NAME'];
394			$result['referencedtable'] = $constrait[0]['REFERENCED_TABLE_NAME'];
395			$result['referencedfields'] = $constrait[0]['REFERENCED_COLUMN_NAME'];
396			}
397			$constrait = self::getConstraintRules($row['Table'], $row['Key_name']);
398			if (count($constrait) > 0) {
399			$result['matchoption'] = $constrait[0]['MATCH_OPTION'];
400			$result['updaterule'] = $constrait[0]['UPDATE_RULE'];
401			$result['deleterule'] = $constrait[0]['DELETE_RULE'];
402			}
403			return $result;
404			}
405
406			/**
407			* The data about the constraint that is found in the KEY_COLUMN_USAGE table
408			* is returned.
409			* Attempting to return the consolidated data generates an extremely slow query
410			* in some MySQL installations, so 2 additional simple queries are made.
411			*
412			* @param string $tableName
413			* @param string $constraintName
414			*
415			* @return array
416			*/
417			private static function getConstraintData(string $tableName, string $constraintName): array
418			{
419			$dbName = DB::$dbName;
420
421			return DB::select('
422			SELECT
423			TABLE_NAME,
424			COLUMN_NAME,
425			CONSTRAINT_NAME,
426			REFERENCED_TABLE_NAME,
427			REFERENCED_COLUMN_NAME
428			FROM
429			INFORMATION_SCHEMA.KEY_COLUMN_USAGE
430			WHERE
431			TABLE_SCHEMA = ' . self::quoteFieldName($dbName) . ' AND
432			TABLE_NAME = ' . self::quoteFieldName($tableName) . ' AND
433			constraint_name = ' . self::quoteFieldName($constraintName) . ' AND
434			REFERENCED_COLUMN_NAME IS NOT NULL;
435			');
436			}
437
438			/**
439			* The rules for updating and deleting data with constraint (table
440			* REFERENTIAL_CONSTRAINTS) are returned.
441			* Attempting to return the consolidated data generates an extremely slow query
442			* in some MySQL installations, so 2 additional simple queries are made.
443			*
444			* @param string $tableName
445			* @param string $constraintName
446			*
447			* @return array
448			*/
449			private static function getConstraintRules(string $tableName, string $constraintName): array
450			{
451			$dbName = DB::$dbName;
452
453			return DB::select('
454			SELECT
455			MATCH_OPTION,
456			UPDATE_RULE,
457			DELETE_RULE
458			FROM information_schema.REFERENTIAL_CONSTRAINTS
459			WHERE
460			constraint_schema = ' . self::quoteFieldName($dbName) . ' AND
461			table_name = ' . self::quoteFieldName($tableName) . ' AND
462			constraint_name = ' . self::quoteFieldName($constraintName) . ';
463			');
464			}
465
466			/**
467			* Obtain an array with the basic information about the indexes of the table,
468			* which will be supplemented with the restrictions later.
469			*
470			* @param string $tableName
471			*
472			* @return string
473			*/
474			public static function getIndexesSql(string $tableName): string
475			{
476			// https://stackoverflow.com/questions/5213339/how-to-see-indexes-for-a-database-or-table-in-mysql
477
478			return 'SHOW INDEX FROM ' . self::quoteTableName($tableName);
479			}
480
481			/**
482			* Toma la estructura de un campo obtenida de la base de datos, y la retorna
483			* de la misma forma en la que se usó al ser creada.
484			* Esto es necesario, porque algunas bases de datos cambian tipos como boolean por
485			* tinyint(1), o int por int(10)
486			*
487			* @author Rafael San José Tovar <[email protected]>
488			*
489			* @param string $genericType
490			* @param array $structure
491			*
492			* @return array
493			*/
494			public static function sanitizeDbStructure(string $genericType, array $structure): array
495			{
496			$type = $structure['Type'];
497			switch ($genericType) {
498			// Tipos que no cambian
499			case Schema::TYPE_FLOAT:
500			case Schema::TYPE_DECIMAL:
501			case Schema::TYPE_STRING:
502			case Schema::TYPE_TEXT:
503			case Schema::TYPE_DATE:
504			case Schema::TYPE_TIME:
505			case Schema::TYPE_DATETIME:
506			break;
507			// Tipos a los que hay que quitar los paréntesis
508			case Schema::TYPE_INTEGER:
509			$type = preg_replace("/\((.*?)\)/i", "", $type);
510			break;
511			// Tipos que cambian durante la creación
512			case Schema::TYPE_BOOLEAN:
513			$type = 'boolean'; // Se crea como boolean y se retorna como tinyint(1)
514			$structure['Default'] = ($structure['Default'] === '1');
515			break;
516			}
517			$structure['Type'] = $type;
518			return $structure;
519			}
520
521			public static function modify(string $tableName, array $oldField, array $newField): string
522			{
523			$sql = 'ALTER TABLE ' . self::quoteTableName($tableName) . ' CHANGE ' . $oldField['Field'] . ' ' . $newField['Field'] . ' ';
524			$sql .= $newField['Type'] . ' ';
525			if (strtolower($newField['Null']) === 'no') {
526			$sql .= 'NOT ';
527			}
528			$sql .= 'NULL';
529			if ($newField['Default'] !== null) {
530			if ($newField['Type'] === 'boolean') {
531			$newField['Default'] = $newField['Default'] ? '1' : '0';
532			}
533			$sql .= ' DEFAULT "' . $newField['Default'] . '"';
534			}
535			$sql .= ';';
536			return $sql;
537			}
538
539			/**
540			* Obtains an array of indexes for a table
541			*
542			* @param string $tableName
543			*
544			* @return array
545			* @throws \DebugBar\DebugBarException
546			*/
547			public static function getIndexes(string $tableName): array
548			{
549			$query = self::getIndexesSql($tableName);
550			$data = DB::select($query);
551			$result = [];
552			foreach ($data as $value) {
553			$row = self::normalizeIndexes($value);
554			$result[$row['index']] = $row;
555			}
556
557			return $result;
558			}
559
560			public static function createIndex(string $tableName, string $index, array $data): string
561			{
562			$name = $data['column'];
563
564			// Si es una clave primaria, ya fue creada en la definición de la tabla
565			if ($data['primary'] === 'yes') {
566			return ''; // return "ALTER TABLE `$tableName` PRIMARY KEY ($name);";
567			}
568
569			$sql = "ALTER TABLE `$tableName` ADD CONSTRAINT `$index` ";
570			if ($data['unique'] === 'yes') {
571			return $sql . "UNIQUE ($name);";
572			}
573
574			if (!isset($data['referencedtable'])) {
575			return $sql . "INDEX ($name);";
576			}
577
578			$referencedTable = $data['referencedtable'];
579			$referencedFields = $data['referencedfields'];
580			$updaterule = strtoupper($data['updaterule']);
581			$deleterule = strtoupper($data['deleterule']);
582
583			$sql .= "FOREIGN KEY ($name) REFERENCES $referencedTable ($referencedFields) ON DELETE $deleterule ON UPDATE $updaterule;";
584
585			return $sql;
586			}
587
588			public static function changeIndex(string $tableName, string $index, array $oldData, array $newData): string
589			{
590			$oldPrimary = $oldData['index'] === 'PRIMARY';
591			$oldUnique = $oldData['unique'] === 1;
592			$oldReferencedTable = $oldData['referencedtable'] ?? null;
593
594			$newPrimary = $newData['primary'] === 'yes';
595			$newUnique = $newData['unique'] === 'yes';
596			$newReferencedTable = $newData['referencedtable'] ?? null;
597
598			$ok = true;
599			$ok = $ok && ($oldData['column'] === $newData['column']);
600			$ok = $ok && ($oldPrimary === $newPrimary);
601			$ok = $ok && ($oldReferencedTable === $newReferencedTable);
602
603			// Si es primaria, es unique siempre así que solo comprobamos si no es unique
604			if ($ok && !$oldPrimary) {
605			$ok = $ok && $oldUnique === $newUnique;
606			}
607
608			// No hay cambios y no hay constraint
609			if ($ok && !isset($newReferencedTable)) {
610			return '';
611			}
612
613			// Si hay constraint, entonces hay que verificar si ha cambiado.
614			$oldReferencedFields = strtolower($oldData['referencedfields']) ?? '';
615			$oldUpdateRule = strtolower($oldData['updaterule']) ?? '';
616			$oldDeleteRule = strtolower($oldData['deleterule']) ?? '';
617
618			$newReferencedFields = strtolower($newData['referencedfields']) ?? '';
619			$newUpdateRule = strtolower($newData['updaterule']) ?? '';
620			$newDeleteRule = strtolower($newData['deleterule']) ?? '';
621
622			if ($oldReferencedFields === $newReferencedFields && $oldUpdateRule === $newUpdateRule && $oldDeleteRule === $newDeleteRule) {
623			return '';
624			}
625
626			// Se elimina el índice y se vuelve a crear
627			return self::removeIndex($tableName, $index) . self::createIndex($tableName, $index, $newData);
628			}
629
630			public static function removeIndex(string $tableName, string $index): string
631			{
632			$sql = "ALTER TABLE `$tableName` DROP CONSTRAINT `$index`;";
633
634			return $sql;
635			}
636
637			}
638

rsanjoseo / alxarafe

Push — main ( b4984c...46772d )

SqlMySql::sanitizeDbStructure() B

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Complexity

Long Method

Duplication Side-by-Side

Filter issues like