SqlMySql::getSqlField() - Code Metrics - Inspection of "Pequeños cambios y documentación" - rsanjoseo/alxarafe - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — main ( 46772d...8239ff )

by Rafael

created 2023-02-08 19:31 UTC

SqlMySql::getSqlField() B

↳ Parent: SqlMySql

Complexity

Conditions	9
Paths	48

Size

Total Lines	37
Code Lines	26

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	90

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	9
eloc	26
c	1
b	0
f	0
nc	48
nop	1
dl	0
loc	37
ccs	0
cts	25
cp	0
crap	90
rs	8.0555

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

namespace Alxarafe\Database\SqlHelpers;

use Alxarafe\Core\Utils\ArrayUtils;
use Alxarafe\Core\Utils\MathUtils;
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 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 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 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 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;
    }

    /**
     * Retorna un array asociativo con los índices de la tabla.
     *
     * @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;
    }

    /**
     * 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;
        }

        $minMax = MathUtils::getMinMax($size, $unsigned);

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

    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' : '');
    }

    /**
     * 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];
    }

    /**
     * Retorna la sentencia SQL para la creación de un índice
     *
     * @author Rafael San José Tovar <[email protected]>
     *
     * @param string $tableName
     * @param string $index
     * @param array  $data
     *
     * @return string
     */
    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;
    }

    /**
     * Retorna la sentencia SQL para cambiar un índice o constraint
     *
     * @author Rafael San José Tovar <[email protected]>
     *
     * @param string $tableName
     * @param string $index
     * @param array  $oldData
     * @param array  $newData
     *
     * @return string
     */
    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);
    }

    /**
     * Retorna la sentencia SQL para la eliminación de un índice
     *
     * @author Rafael San José Tovar <[email protected]>
     *
     * @param string $tableName
     * @param string $index
     *
     * @return string
     */
    public static function removeIndex(string $tableName, string $index): string
    {
        $sql = "ALTER TABLE `$tableName` DROP CONSTRAINT `$index`;";

        return $sql;
    }

    /**
     * Recibe los datos del yaml de definición de un campo, y retorna la información
     * necesaria para la creación del campo en la base de datos.
     *
     * @author Rafael San José Tovar <[email protected]>
     *
     * @param array $data
     *
     * @return array
     */
    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;
    }

    /**
     * Recibe los datos del yaml de definición de los índices, y retorna la información
     * necesaria para la creación de los mismos en la base de datos.
     *
     * @author Rafael San José Tovar <[email protected]>
     *
     * @param array $data
     *
     * @return array
     */
    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'] ?? []);
    }

    /**
     * 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;
    }

    /**
     * Obtiene la secuencia SQL para la creación o edición de una columna
     *
     * @author Rafael San José Tovar <[email protected]>
     *
     * @param array $column
     *
     * @return string
     */
    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;
    }

    /**
     * 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) . ';
        ');
    }

    /**
     * Obtiene la secuencia SQL para listar los índices de la tabla.
     *
     * @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);
    }

    /**
     * Retorna un array con la información del índice, y de la constraint si existe.
     *
     * @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;
    }

    /**
     * Retorna la secuencia SQL para modificar un campo de la tabla
     *
     * @author Rafael San José Tovar <[email protected]>
     *
     * @param string $tableName
     * @param array  $oldField
     * @param array  $newField
     *
     * @return string
     */
    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;
    }

}


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

rsanjoseo / alxarafe

Push — main ( 46772d...8239ff )

SqlMySql::getSqlField() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like