Test Failed
Push — main ( 8846ad...255e1c )
by Rafael
05:35
created

Schema::compare_columns()   F

Complexity

Conditions 26
Paths 317

Size

Total Lines 92
Code Lines 48

Duplication

Lines 0
Ratio 0 %

Importance

Changes 1
Bugs 0 Features 0
Metric Value
cc 26
eloc 48
nc 317
nop 3
dl 0
loc 92
rs 1.8708
c 1
b 0
f 0

How to fix   Long Method    Complexity   

Long Method

Small methods make your code easier to understand, in particular if combined with a good name. Besides, if your method is small, finding a good name is usually much easier.

For example, if you find yourself adding comments to a method's body, this is usually a good sign to extract the commented part to a new method, and use the comment as a starting point when coming up with a good name for this new method.

Commonly applied refactorings include:

1
<?php
2
/**
3
 * Copyright (C) 2022-2023  Rafael San José Tovar   <[email protected]>
4
 *
5
 * This program is free software; you can redistribute it and/or modify
6
 * it under the terms of the GNU General Public License as published by
7
 * the Free Software Foundation; either version 3 of the License, or
8
 * (at your option) any later version.
9
 *
10
 * This program is distributed in the hope that it will be useful,
11
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
13
 * GNU General Public License for more details.
14
 *
15
 * You should have received a copy of the GNU General Public License
16
 * along with this program. If not, see <https://www.gnu.org/licenses/>.
17
 */
18
19
namespace Alxarafe\Database;
20
21
use Alxarafe\Core\Singletons\Config;
22
use Alxarafe\Core\Singletons\Debug;
23
use Alxarafe\Core\Singletons\PhpFileCache;
24
use DebugBar\DebugBarException;
25
use Symfony\Component\Yaml\Yaml;
26
use function Alxarafe\Core\Helpers\count;
0 ignored issues
show
introduced by
The function Alxarafe\Core\Helpers\count was not found. Maybe you did not declare it correctly or list all dependencies?
Loading history...
27
use const Alxarafe\Core\Helpers\DEFAULT_INTEGER_SIZE;
0 ignored issues
show
Bug introduced by
The constant Alxarafe\Core\Helpers\DEFAULT_INTEGER_SIZE was not found. Maybe you did not declare it correctly or list all dependencies?
Loading history...
28
use const Alxarafe\Core\Helpers\DEFAULT_STRING_LENGTH;
0 ignored issues
show
Bug introduced by
The constant Alxarafe\Core\Helpers\DEFAULT_STRING_LENGTH was not found. Maybe you did not declare it correctly or list all dependencies?
Loading history...
29
30
/**
31
 * The Schema class contains static methods that allow you to manipulate the
32
 * database. It is used to create and modify tables and indexes in the database.
33
 */
34
class Schema
35
{
36
    public const TYPE_INTEGER = 'integer';
37
    public const TYPE_FLOAT = 'float';
38
    public const TYPE_DECIMAL = 'decimal';
39
    public const TYPE_STRING = 'string';
40
    public const TYPE_TEXT = 'text';
41
    public const TYPE_DATE = 'date';
42
    public const TYPE_TIME = 'time';
43
    public const TYPE_DATETIME = 'datetime';
44
    public const TYPE_BOOLEAN = 'bool';
45
46
    public const TYPES = [
47
        self::TYPE_INTEGER => ['tinyint', 'smallint', 'mediumint', 'int', 'bigint'],
48
        self::TYPE_FLOAT => ['real', 'double'],
49
        self::TYPE_DECIMAL => ['decimal', 'numeric'],
50
        self::TYPE_STRING => ['char', 'varchar'],
51
        self::TYPE_TEXT => ['tinytext', 'text', 'mediumtext', 'longtext', 'blob'],
52
        self::TYPE_DATE => ['date'],
53
        self::TYPE_TIME => ['time'],
54
        self::TYPE_DATETIME => ['datetime', 'timestamp'],
55
        self::TYPE_BOOLEAN => ['boolean'],
56
    ];
57
    public const YAML_CACHE_TABLES_FOLDER = 'models';
58
59
    /**
60
     * Carriage Return and Line Feed
61
     */
62
    const CRLF = "\r\n";
63
    const DB_INDEX_TYPE = 'bigint (20) unsigned';
64
65
    public static array $tables = [];
66
67
    /**
68
     * Contains the database structure data.
69
     * Each table is an index of the associative array.
70
     *
71
     * @var array
72
     */
73
    public static array $bbddStructure;
74
75
    /**
76
     * Return true if $tableName exists in database
77
     *
78
     * @param string $tableName
79
     *
80
     * @return bool
81
     * @throws DebugBarException
82
     */
83
    public static function tableExists($tableName): bool
84
    {
85
        $tableNameWithPrefix = Config::$dbPrefix . $tableName;
86
        $dbName = Config::$dbName;
87
        $sql = "SELECT COUNT(*) AS Total FROM information_schema.tables WHERE table_schema = '{$dbName}' AND table_name='{$tableNameWithPrefix}'";
88
89
        $data = Engine::select($sql);
90
        $result = reset($data);
91
92
        return $result['Total'] === '1';
93
    }
94
95
    private static function getFields($tableName): array
96
    {
97
        $yamlFilename = PhpFileCache::getYamlFileName(self::YAML_CACHE_TABLES_FOLDER, $tableName);
98
        if (file_exists($yamlFilename)) {
99
            return PhpFileCache::loadYamlFile(self::YAML_CACHE_TABLES_FOLDER, $tableName);
100
        }
101
102
        if (empty(self::$tables)) {
103
            self::$tables = YamlSchema::getTables();
104
        }
105
106
        $yamlSourceFilename = self::$tables[$tableName];
107
        if (!file_exists($yamlSourceFilename)) {
108
            dump('No existe el archivo ' . $yamlSourceFilename);
109
        }
110
111
        $data = Yaml::parseFile($yamlSourceFilename);
112
113
        $result = [];
114
        foreach ($data as $key => $datum) {
115
            $datum['key'] = $key;
116
            $result[$key] = Schema::normalize($datum);
117
        }
118
119
        /*
120
        Igual conviene crear una clase:
121
        - DBSchema (con los datos de la base de datos real)
122
        - DefinedSchema (con los datos definidos)
123
        y que Schema cree o adapte según los datos de ambas. Que cada una lleve lo suyo
124
125
        Que el resultado se guarde en el yaml y que se encargue de realizar las conversines
126
    oportunas siempre que no suponga una pérdida de datos.
127
        */
128
129
        return $result;
130
    }
131
132
    private static function getIndexes($tableName): array
0 ignored issues
show
Unused Code introduced by
The parameter $tableName is not used and could be removed. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-unused  annotation

132
    private static function getIndexes(/** @scrutinizer ignore-unused */ $tableName): array

This check looks for parameters that have been defined for a function or method, but which are not used in the method body.

Loading history...
133
    {
134
        $result = [];
135
        return $result;
136
    }
137
138
    private static function getRelated($tableName): array
0 ignored issues
show
Unused Code introduced by
The parameter $tableName is not used and could be removed. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-unused  annotation

138
    private static function getRelated(/** @scrutinizer ignore-unused */ $tableName): array

This check looks for parameters that have been defined for a function or method, but which are not used in the method body.

Loading history...
139
    {
140
        $result = [];
141
        return $result;
142
    }
143
144
    private static function getSeed($tableName): array
0 ignored issues
show
Unused Code introduced by
The parameter $tableName is not used and could be removed. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-unused  annotation

144
    private static function getSeed(/** @scrutinizer ignore-unused */ $tableName): array

This check looks for parameters that have been defined for a function or method, but which are not used in the method body.

Loading history...
145
    {
146
        $result = [];
147
        return $result;
148
    }
149
150
    public static function checkStructure(string $tableName, bool $create)
151
    {
152
        if (!empty(self::$bbddStructure[$tableName])) {
153
            return;
154
        }
155
156
        $structure = [];
157
        $structure['fields'] = self::getFields($tableName);
158
        $structure['indexes'] = self::getIndexes($tableName);
159
        $structure['related'] = self::getRelated($tableName);
160
        if ($create) {
161
            $structure['seed'] = self::getSeed($tableName);
162
        }
163
        self::$bbddStructure[$tableName] = $structure;
164
    }
165
166
    private static function getTypeOf(string $type): string
167
    {
168
        foreach (self::TYPES as $index => $types) {
169
            if (in_array(strtolower($type), $types)) {
170
                return $index;
171
            }
172
        }
173
        Debug::addMessage('messages', $type . ' not found in DBSchema::getTypeOf()');
174
        return 'text';
175
    }
176
177
    private static function splitType(string $originalType): array
178
    {
179
        $replacesSources = [
180
            'character varying',
181
            // 'timestamp without time zone',
182
            'double precision',
183
        ];
184
        $replacesDestination = [
185
            'varchar',
186
            // 'timestamp',
187
            'double',
188
        ];
189
        $modifiedType = (str_replace($replacesSources, $replacesDestination, $originalType));
190
191
        if ($originalType !== $modifiedType) {
192
            Debug::addMessage('messages', "XML: Uso de '{$originalType}' en lugar de '{$modifiedType}'.");
193
        }
194
        $explode = explode(' ', strtolower($modifiedType));
195
196
        $pos = strpos($explode[0], '(');
197
        if ($pos > 0) {
198
            $begin = $pos + 1;
199
            $end = strpos($explode[0], ')');
200
            $type = substr($explode[0], 0, $pos);
201
            $length = substr($explode[0], $begin, $end - $begin);
202
        } else {
203
            $type = $explode[0];
204
            $length = null;
205
        }
206
207
        $pos = array_search('unsigned', $explode, true);
208
        $unsigned = $pos ? 'yes' : 'no';
209
210
        $pos = array_search('zerofill', $explode, true);
211
        $zerofill = $pos ? 'yes' : 'no';
212
213
        return ['type' => $type, 'length' => $length, 'unsigned' => $unsigned, 'zerofill' => $zerofill];
214
    }
215
216
    /**
217
     * Toma los datos del fichero de definición de una tabla y genera el definitivo.
218
     *
219
     * @author  Rafael San José Tovar <[email protected]>
220
     * @version 2022.1224
221
     *
222
     * @param array $structure
223
     *
224
     * @return array
225
     */
226
    protected static function normalize(array $structure): array
227
    {
228
        $column = [];
229
        $key = (string) $structure['key'];
230
        $type = (string) $structure['type'];
231
        $column['key'] = $key;
232
233
        /**
234
         * Entrada:
235
         * - type es el tipo lógico del campo y tiene que estar definido como índice en
236
         *   TYPES, o ser uno de los predefinidos como 'autoincrement', 'relationship', etc.
237
         *
238
         * Salida:
239
         * - type queda intacto.
240
         * - dbtype es como queda definido en la tabla, por ejemplo, varchar(20)
241
         * - realtype es el tipo resultado, por ejemplo varchar (sin el tamaño)
242
         * - generictype es uno de los índices de TYPE. P.E. autoincrement se cambiará por integer
243
         *
244
         */
245
246
        $column['type'] = $type;
247
        switch ($type) {
248
            case 'autoincrement':
249
            case 'relationship':
250
                $colType = self::DB_INDEX_TYPE;
251
                break;
252
            case 'boolean':
253
                $colType = 'tinyint(1) unsigned';
254
                break;
255
            default:
256
                $colType = $type;
257
        }
258
259
        $typeArray = static::splitType($colType);
260
        /**
261
         * ^ array:4 [▼
262
         *        "type" => "bigint"
263
         *        "length" => null
264
         *        "unsigned" => "yes"
265
         *        "zerofill" => "no"
266
         * ]
267
         */
268
        $type = $typeArray['type'];
269
        $length = $typeArray['length'] ?? $structure['length'];
270
        $unsigned = $typeArray['unsigned'] === 'yes';
271
        $zerofill = $typeArray['zerofill'] === 'yes';
0 ignored issues
show
Unused Code introduced by
The assignment to $zerofill is dead and can be removed.
Loading history...
272
        $genericType = static::getTypeOf($type);
273
274
        $column['dbtype'] = $colType;
275
        $column['realtype'] = $type;
276
        $column['generictype'] = $genericType;
277
278
        $column['null'] = 'YES';
279
        if ($structure['null'] && mb_strtolower($structure['null']) == 'no') {
280
            $column['null'] = 'NO';
281
        }
282
283
        if (empty($structure['default'])) {
284
            $column['default'] = null;
285
        } else {
286
            $column['default'] = (string) $structure['default'];
287
        }
288
289
        /**
290
         * Pueden existir otras definiciones de limitaciones físicas como min y max
291
         * De existir, tienen que ser contempladas en el método test y tener mayor peso que
292
         * la limitación en plantilla.
293
         */
294
        foreach (['min', 'max'] as $field) {
295
            if (isset($structure[$field])) {
296
                $column[$field] = (string) $structure[$field];
297
            }
298
        }
299
300
        if (isset($structure['comment'])) {
301
            $column['comentario'] = (string) $structure['comment'];
302
        }
303
304
        if (isset($structure['default'])) {
305
            $column['default'] = trim($structure['default'], " \"'`");
306
        }
307
308
        switch ($genericType) {
309
            case 'text':
310
                $column['dbtype'] = 'varchar(' . $length . ')';
311
                $column['maxlength'] = $length;
312
                break;
313
            case 'integer':
314
                /**
315
                 * Lo primero es ver la capacidad física máxima según el tipo de dato.
316
                 */
317
                $bytes = 4;
318
                switch ($type) {
319
                    case 'tinyint':
320
                        $bytes = 1;
321
                        break;
322
                    case 'smallint':
323
                        $bytes = 2;
324
                        break;
325
                    case 'mediumint':
326
                        $bytes = 3;
327
                        break;
328
                    case 'int':
329
                        $bytes = 4;
330
                        break;
331
                    case 'bigint':
332
                        $bytes = 8;
333
                        break;
334
                }
335
                $bits = 8 * (int) $bytes;
336
                $physicalMaxLength = 2 ** $bits;
337
338
                /**
339
                 * $minDataLength y $maxDataLength contendrán el mínimo y máximo valor que puede contener el campo.
340
                 */
341
                $minDataLength = $unsigned ? 0 : -$physicalMaxLength / 2;
342
                $maxDataLength = ($unsigned ? $physicalMaxLength : $physicalMaxLength / 2) - 1;
343
344
                /**
345
                 * De momento, se asignan los límites máximos por el tipo de dato.
346
                 * En $min y $max, iremos arrastrando los límites conforme se vayan comprobando.
347
                 * $min nunca podrá ser menor que $minDataLength.
348
                 * $max nunca podrá ser mayor que $maxDataLength.
349
                 */
350
                $min = $minDataLength;
351
                $max = $maxDataLength;
352
353
                /**
354
                 * Se puede hacer una limitación física Se puede haber definido en el xml un min y un max.
355
                 * A todos los efectos, lo definido en el XML como min o max se toma como limitación
356
                 * física del campo.
357
                 */
358
                if (isset($structure['min'])) {
359
                    $minXmlLength = $structure['min'];
360
                    if ($minXmlLength > $minDataLength) {
361
                        $min = $minXmlLength;
362
                    } else {
363
                        Debug::addMessage('messages', "({$key}): Se ha especificado un min {$minXmlLength} en el XML, pero por el tipo de datos, el mínimo es {$minDataLength}.");
364
                    }
365
                }
366
                if (isset($structure['max'])) {
367
                    $maxXmlLength = $structure['max'];
368
                    if ($maxXmlLength < $maxDataLength) {
369
                        $max = $maxXmlLength;
370
                    } else {
371
                        Debug::addMessage('messages', "({$key}): Se ha especificado un min {$maxXmlLength} en el XML, pero por el tipo de datos, el máximo es {$maxDataLength}.");
372
                    }
373
                }
374
375
                $column['min'] = $min;
376
                $column['max'] = $max;
377
                break;
378
            default:
379
                // ???
380
        }
381
382
        return $column;
383
    }
384
385
    public function compare_columns($table_name, $xml_cols, $db_cols)
386
    {
387
        $sql = '';
388
389
        foreach ($xml_cols as $xml_col) {
390
            if (mb_strtolower($xml_col['tipo']) == 'integer') {
391
                /**
392
                 * Desde la pestaña avanzado el panel de control se puede cambiar
393
                 * el tipo de entero a usar en las columnas.
394
                 */
395
                $xml_col['tipo'] = FS_DB_INTEGER;
0 ignored issues
show
Bug introduced by
The constant Alxarafe\Database\FS_DB_INTEGER was not found. Maybe you did not declare it correctly or list all dependencies?
Loading history...
396
            }
397
398
            /**
399
             * Si el campo no está en la tabla, procedemos a su creación
400
             */
401
            $db_col = $this->search_in_array($db_cols, 'name', $xml_col['nombre']);
0 ignored issues
show
Bug introduced by
The method search_in_array() does not exist on Alxarafe\Database\Schema. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-call  annotation

401
            /** @scrutinizer ignore-call */ 
402
            $db_col = $this->search_in_array($db_cols, 'name', $xml_col['nombre']);

This check looks for calls to methods that do not seem to exist on a given type. It looks for the method on the type itself as well as in inherited classes or implemented interfaces.

This is most likely a typographical error or the method has been renamed.

Loading history...
402
            if (empty($db_col)) {
403
                $sql .= 'ALTER TABLE `' . $table_name . '` ADD `' . $xml_col['nombre'] . '` ';
404
                if ($xml_col['tipo'] == 'serial') {
405
                    $sql .= '`' . $xml_col['nombre'] . '` ' . constant('FS_DB_INTEGER') . ' NOT NULL AUTO_INCREMENT;';
406
                    continue;
407
                }
408
                if ($xml_col['tipo'] == 'autoincrement') {
409
                    $sql .= '`' . $xml_col['nombre'] . '` ' . constant('DB_INDEX_TYPE') . ' NOT NULL AUTO_INCREMENT;';
410
                    continue;
411
                }
412
                if ($xml_col['tipo'] == 'relationship') {
413
                    $xml_col['tipo'] = constant('DB_INDEX_TYPE');
414
                }
415
416
                $sql .= $xml_col['tipo'];
417
                $sql .= ($xml_col['nulo'] == 'NO') ? " NOT NULL" : " NULL";
418
419
                if ($xml_col['defecto'] !== null) {
420
                    $sql .= " DEFAULT " . $xml_col['defecto'];
421
                } elseif ($xml_col['nulo'] == 'YES') {
422
                    $sql .= " DEFAULT NULL";
423
                }
424
425
                $sql .= ';';
426
427
                continue;
428
            }
429
430
            /**
431
             * Si el campo es un autoincremental o relacionado a uno, asignamos el tipo correcto para la constraint.
432
             * Si además es el índice, nos aseguramos de que no pueda ser nulo.
433
             */
434
            if (in_array($xml_col['tipo'], ['autoincrement', 'relationship'])) {
435
                if ($xml_col['tipo'] === 'autoincrement') {
436
                    $xml_col['nulo'] = 'NO';
437
                }
438
                $xml_col['tipo'] = constant('DB_INDEX_TYPE');
439
            }
440
441
            /// columna ya presente en db_cols. La modificamos
442
            if (!$this->compare_data_types($db_col['type'], $xml_col['tipo'])) {
0 ignored issues
show
Bug introduced by
The method compare_data_types() does not exist on Alxarafe\Database\Schema. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-call  annotation

442
            if (!$this->/** @scrutinizer ignore-call */ compare_data_types($db_col['type'], $xml_col['tipo'])) {

This check looks for calls to methods that do not seem to exist on a given type. It looks for the method on the type itself as well as in inherited classes or implemented interfaces.

This is most likely a typographical error or the method has been renamed.

Loading history...
443
                // Buscar todas las constraints relacionadas con este campo y eliminarlas
444
                foreach ($this->get_referenced_field_constraint($table_name, $xml_col['nombre']) as $pos => $constraint) {
0 ignored issues
show
Bug introduced by
The method get_referenced_field_constraint() does not exist on Alxarafe\Database\Schema. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-call  annotation

444
                foreach ($this->/** @scrutinizer ignore-call */ get_referenced_field_constraint($table_name, $xml_col['nombre']) as $pos => $constraint) {

This check looks for calls to methods that do not seem to exist on a given type. It looks for the method on the type itself as well as in inherited classes or implemented interfaces.

This is most likely a typographical error or the method has been renamed.

Loading history...
445
                    $sql .= "ALTER TABLE `" . $constraint['TABLE_NAME'] . "` DROP FOREIGN KEY " . $constraint['CONSTRAINT_NAME'] . ";";
446
                }
447
                $sql .= 'ALTER TABLE `' . $table_name . '` MODIFY `' . $xml_col['nombre'] . '` ' . $xml_col['tipo'] . ';';
448
            }
449
450
            if ($db_col['is_nullable'] == $xml_col['nulo']) {
451
                /// do nothing
452
            } elseif ($xml_col['nulo'] == 'YES') {
453
                $sql .= 'ALTER TABLE `' . $table_name . '` MODIFY `' . $xml_col['nombre'] . '` ' . $xml_col['tipo'] . ' NULL;';
454
            } else {
455
                $sql .= 'ALTER TABLE `' . $table_name . '` MODIFY `' . $xml_col['nombre'] . '` ' . $xml_col['tipo'] . ' NOT NULL;';
456
            }
457
458
            if ($this->compare_defaults($db_col['default'], $xml_col['defecto'])) {
0 ignored issues
show
Bug introduced by
The method compare_defaults() does not exist on Alxarafe\Database\Schema. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-call  annotation

458
            if ($this->/** @scrutinizer ignore-call */ compare_defaults($db_col['default'], $xml_col['defecto'])) {

This check looks for calls to methods that do not seem to exist on a given type. It looks for the method on the type itself as well as in inherited classes or implemented interfaces.

This is most likely a typographical error or the method has been renamed.

Loading history...
459
                /// do nothing
460
            } elseif (is_null($xml_col['defecto'])) {
461
                if ($this->exists_index($table_name, $db_col['name']) && !$this->unique_equals($table_name, $db_col, $xml_col)) {
0 ignored issues
show
Bug introduced by
The method exists_index() does not exist on Alxarafe\Database\Schema. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-call  annotation

461
                if ($this->/** @scrutinizer ignore-call */ exists_index($table_name, $db_col['name']) && !$this->unique_equals($table_name, $db_col, $xml_col)) {

This check looks for calls to methods that do not seem to exist on a given type. It looks for the method on the type itself as well as in inherited classes or implemented interfaces.

This is most likely a typographical error or the method has been renamed.

Loading history...
Bug introduced by
The method unique_equals() does not exist on Alxarafe\Database\Schema. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-call  annotation

461
                if ($this->exists_index($table_name, $db_col['name']) && !$this->/** @scrutinizer ignore-call */ unique_equals($table_name, $db_col, $xml_col)) {

This check looks for calls to methods that do not seem to exist on a given type. It looks for the method on the type itself as well as in inherited classes or implemented interfaces.

This is most likely a typographical error or the method has been renamed.

Loading history...
462
                    $sql .= 'ALTER TABLE `' . $table_name . '` ALTER `' . $xml_col['nombre'] . '` DROP DEFAULT;';
463
                }
464
            } elseif (mb_strtolower(substr($xml_col['defecto'], 0, 9)) == "nextval('") { /// nextval es para postgresql
465
                if ($db_col['extra'] != 'auto_increment') {
466
                    $sql .= 'ALTER TABLE `' . $table_name . '` MODIFY `' . $xml_col['nombre'] . '` ' . $xml_col['tipo'];
467
                    $sql .= ($xml_col['nulo'] == 'YES') ? ' NULL AUTO_INCREMENT;' : ' NOT NULL AUTO_INCREMENT;';
468
                }
469
            } else {
470
                if ($db_col['default'] != $xml_col['defecto'] && ($db_col['default'] != null && $xml_col['defecto'] == 'NULL')) {
471
                    $sql .= 'ALTER TABLE `' . $table_name . '` ALTER `' . $xml_col['nombre'] . '` SET DEFAULT ' . $xml_col['defecto'] . ";";
472
                }
473
            }
474
        }
475
476
        return $this->fix_postgresql($sql);
0 ignored issues
show
Bug introduced by
The method fix_postgresql() does not exist on Alxarafe\Database\Schema. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-call  annotation

476
        return $this->/** @scrutinizer ignore-call */ fix_postgresql($sql);

This check looks for calls to methods that do not seem to exist on a given type. It looks for the method on the type itself as well as in inherited classes or implemented interfaces.

This is most likely a typographical error or the method has been renamed.

Loading history...
477
    }
478
479
    /**
480
     * Create a table in the database.
481
     * Build the default fields, indexes and values defined in the model.
482
     *
483
     * @param string $tableName
484
     *
485
     * @return bool
486
     * @throws DebugBarException
487
     */
488
489
    public static function createTable(string $tableName): bool
490
    {
491
        $tabla = self::$bbddStructure[$tableName];
492
        $sql = self::createFields($tableName, $tabla['fields']);
493
494
        foreach ($tabla['keys'] as $name => $index) {
495
            $sql .= self::createIndex($tableName, $name, $index);
496
        }
497
        if (isset($tabla['values'])) {
498
            $sql .= self::setValues($tableName, $tabla['values']);
499
        }
500
501
        return Engine::exec($sql);
502
    }
503
504
    /**
505
     * Build the SQL statement to create the fields in the table.
506
     * It can also create the primary key if the auto_increment attribute is defined.
507
     *
508
     * @param string $tablename
509
     * @param array  $fieldList
510
     *
511
     * @return string
512
     */
513
    protected static function createFields(string $tablename, array $fieldList): string
514
    {
515
        $tablenameWithPrefix = Config::$dbPrefix . $tablename;
516
517
        $sql = "CREATE TABLE $tablenameWithPrefix ( ";
518
        foreach ($fieldList as $index => $col) {
519
            if (!isset($col['dbtype'])) {
520
                die('Tipo no especificado en createTable ' . $index);
0 ignored issues
show
Best Practice introduced by
Using exit here is not recommended.

In general, usage of exit should be done with care and only when running in a scripting context like a CLI script.

Loading history...
Bug Best Practice introduced by
In this branch, the function will implicitly return null which is incompatible with the type-hinted return string. Consider adding a return statement or allowing null as return value.

For hinted functions/methods where all return statements with the correct type are only reachable via conditions, ?null? gets implicitly returned which may be incompatible with the hinted type. Let?s take a look at an example:

interface ReturnsInt {
    public function returnsIntHinted(): int;
}

class MyClass implements ReturnsInt {
    public function returnsIntHinted(): int
    {
        if (foo()) {
            return 123;
        }
        // here: null is implicitly returned
    }
}
Loading history...
521
            }
522
523
            $sql .= '`' . $index . '` ' . $col['dbtype'];
524
            $nulo = isset($col['null']) && $col['null'];
525
526
            $sql .= ($nulo ? '' : ' NOT') . ' NULL';
527
528
            if (isset($col['extra']) && (strtolower($col['extra']) == 'auto_increment')) {
529
                $sql .= ' PRIMARY KEY AUTO_INCREMENT';
530
            }
531
532
            $tmpDefecto = $col['default'] ?? null;
533
            $defecto = '';
534
            if (isset($tmpDefecto)) {
535
                if ($tmpDefecto == 'CURRENT_TIMESTAMP') {
536
                    $defecto = "$tmpDefecto";
537
                } else {
538
                    $defecto = "'$tmpDefecto'";
539
                }
540
            } else {
541
                if ($nulo) {
542
                    $defecto = 'NULL';
543
                }
544
            }
545
546
            if ($defecto != '') {
547
                $sql .= ' DEFAULT ' . $defecto;
548
            }
549
550
            $sql .= ', ';
551
        }
552
        $sql = substr($sql, 0, -2); // Quitamos la coma y el espacio del final
553
        $sql .= ') ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;' . self::CRLF;
554
555
        return $sql;
556
    }
557
558
    /**
559
     * Create the SQL statements for the construction of one index.
560
     * In the case of the primary index, it is not necessary if it is auto_increment.
561
     *
562
     * TODO:
563
     *
564
     * Moreover, it should not be defined if it is auto_increment because it would
565
     * generate an error when it already exists.
566
     *
567
     * @param string $tableName
568
     * @param string $indexname
569
     * @param array  $indexData
570
     *
571
     * @return string
572
     */
573
    protected static function createIndex($tableName, $indexname, $indexData)
574
    {
575
        $sql = "ALTER TABLE $tableName ADD CONSTRAINT $indexname ";
576
577
        $command = '';
578
        // https://www.w3schools.com/sql/sql_primarykey.asp
579
        // ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
580
        if (isset($indexData['PRIMARY'])) {
581
            $command = 'PRIMARY KEY ';
582
            $fields = $indexData['PRIMARY'];
583
        }
584
585
        // https://www.w3schools.com/sql/sql_create_index.asp
586
        // CREATE INDEX idx_pname ON Persons (LastName, FirstName);
587
        if (isset($indexData['INDEX'])) {
588
            $command = 'INDEX ';
589
            $fields = $indexData['INDEX'];
590
        }
591
592
        // https://www.w3schools.com/sql/sql_unique.asp
593
        // ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
594
        if (isset($indexData['UNIQUE'])) {
595
            $command = 'UNIQUE INDEX ';
596
            $fields = $indexData['UNIQUE'];
597
        }
598
599
        if ($command == '') {
600
            // https://www.w3schools.com/sql/sql_foreignkey.asp
601
            // ALTER TABLE Orders ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
602
            if (isset($indexData['FOREIGN'])) {
603
                $command = 'FOREIGN KEY ';
604
                $foreignField = $indexData['FOREIGN'];
605
                if (isset($indexData['REFERENCES'])) {
606
                    $references = $indexData['REFERENCES'];
607
                    if (!is_array($references)) {
608
                        die('Esperaba un array en REFERENCES: ' . $tableName . '/' . $indexname);
0 ignored issues
show
Best Practice introduced by
Using exit here is not recommended.

In general, usage of exit should be done with care and only when running in a scripting context like a CLI script.

Loading history...
609
                    }
610
                    if (count($references) != 1) {
611
                        die('Esperaba un array de 1 elemento en REFERENCES: ' . $tableName . '/' . $indexname);
0 ignored issues
show
Best Practice introduced by
Using exit here is not recommended.

In general, usage of exit should be done with care and only when running in a scripting context like a CLI script.

Loading history...
612
                    }
613
                    $refTable = key($references);
614
                    $fields = '(' . implode(',', $references) . ')';
615
                } else {
616
                    die('FOREIGN necesita REFERENCES en ' . $tableName . '/' . $indexname);
0 ignored issues
show
Best Practice introduced by
Using exit here is not recommended.

In general, usage of exit should be done with care and only when running in a scripting context like a CLI script.

Loading history...
617
                }
618
619
                $sql .= $command . ' ' . $foreignField . ' REFERENCES ' . $refTable . $fields;
620
621
                if (isset($indexData['ON']) && is_array($indexData['ON'])) {
622
                    foreach ($indexData['ON'] as $key => $value) {
623
                        $sql .= ' ON ' . $key . ' ' . $value . ', ';
624
                    }
625
                    $sql = substr($sql, 0, -2); // Quitamos el ', ' de detrás
626
                }
627
            }
628
        } else {
629
            if (is_array($fields)) {
0 ignored issues
show
Comprehensibility Best Practice introduced by
The variable $fields does not seem to be defined for all execution paths leading up to this point.
Loading history...
630
                $fields = '(' . implode(',', $fields) . ')';
631
            } else {
632
                $fields = "($fields)";
633
            }
634
635
            if ($command == 'INDEX ') {
636
                $sql = "CREATE INDEX {$indexname} ON {$tableName}" . $fields;
637
            } else {
638
                $sql .= $command . ' ' . $fields;
639
            }
640
        }
641
642
        return $sql . ';' . self::CRLF;
643
    }
644
645
    /**
646
     * Create the SQL statements to fill the table with default data.
647
     *
648
     * @param string $tableName
649
     * @param array  $values
650
     *
651
     * @return string
652
     */
653
    protected static function setValues(string $tableName, array $values): string
654
    {
655
        $sql = "INSERT INTO $tableName ";
656
        $header = true;
657
        foreach ($values as $value) {
658
            $fields = "(";
659
            $datos = "(";
660
            foreach ($value as $fname => $fvalue) {
661
                $fields .= $fname . ", ";
662
                $datos .= "'$fvalue'" . ", ";
663
            }
664
            $fields = substr($fields, 0, -2) . ") ";
665
            $datos = substr($datos, 0, -2) . "), ";
666
667
            if ($header) {
668
                $sql .= $fields . " VALUES ";
669
                $header = false;
670
            }
671
672
            $sql .= $datos;
673
        }
674
675
        return substr($sql, 0, -2) . self::CRLF;
676
    }
677
}
678