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\Singletons\Config; |
11
|
|
|
use Alxarafe\Core\Singletons\DebugTool; |
|
|
|
|
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 = Config::$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 un array asociativo con la información de cada columna de la tabla. |
126
|
|
|
* El resultado será dependiente del motor de base de datos. |
127
|
|
|
* |
128
|
|
|
* @author Rafael San José Tovar <[email protected]> |
129
|
|
|
* @version 2023.0108 |
130
|
|
|
* |
131
|
|
|
* @param string $tableName |
132
|
|
|
* |
133
|
|
|
* @return array |
134
|
|
|
*/ |
135
|
|
|
public static function getColumns(string $tableName): array |
136
|
|
|
{ |
137
|
|
|
$query = 'SHOW COLUMNS FROM ' . self::quoteTableName($tableName) . ';'; |
138
|
|
|
$rows = DB::select($query); |
139
|
|
|
$result = []; |
140
|
|
|
foreach ($rows as $row) { |
141
|
|
|
$result[$row['Field']] = $row; |
142
|
|
|
} |
143
|
|
|
return $result; |
144
|
|
|
} |
145
|
|
|
|
146
|
|
|
public static function yamlFieldToSchema(array $data): array |
147
|
|
|
{ |
148
|
|
|
$column = []; |
149
|
|
|
$key = (string) $data['key']; |
150
|
|
|
$type = (string) $data['type']; |
151
|
|
|
$column['key'] = $key; |
152
|
|
|
|
153
|
|
|
/** |
154
|
|
|
* Entrada: |
155
|
|
|
* - type es el tipo lógico del campo y tiene que estar definido como índice en |
156
|
|
|
* TYPES, o ser uno de los predefinidos como 'autoincrement', 'relationship', etc. |
157
|
|
|
* |
158
|
|
|
* Salida: |
159
|
|
|
* - type queda intacto. |
160
|
|
|
* - dbtype es como queda definido en la tabla, por ejemplo, varchar(20) |
161
|
|
|
* - realtype es el tipo resultado, por ejemplo varchar (sin el tamaño) |
162
|
|
|
* - generictype es uno de los índices de TYPE. P.E. autoincrement se cambiará por integer |
163
|
|
|
* |
164
|
|
|
*/ |
165
|
|
|
|
166
|
|
|
$column['type'] = $type; |
167
|
|
|
switch ($type) { |
168
|
|
|
case 'autoincrement': |
169
|
|
|
case 'relationship': |
170
|
|
|
$colType = DB::getIndexType(); |
171
|
|
|
break; |
172
|
|
|
default: |
173
|
|
|
$colType = $type; |
174
|
|
|
} |
175
|
|
|
|
176
|
|
|
$typeArray = static::splitType($colType); |
177
|
|
|
/** |
178
|
|
|
* ^ array:4 [▼ |
179
|
|
|
* "type" => "bigint" |
180
|
|
|
* "length" => null |
181
|
|
|
* "unsigned" => "yes" |
182
|
|
|
* "zerofill" => "no" |
183
|
|
|
* ] |
184
|
|
|
*/ |
185
|
|
|
|
186
|
|
|
$type = $typeArray['type']; |
187
|
|
|
$length = $typeArray['length'] ?? $data['length']; |
188
|
|
|
$unsigned = $typeArray['unsigned'] === 'yes'; |
189
|
|
|
$zerofill = $typeArray['zerofill'] === 'yes'; |
|
|
|
|
190
|
|
|
$genericType = Schema::getTypeOf($type); |
191
|
|
|
|
192
|
|
|
$column['dbtype'] = $colType; |
193
|
|
|
$column['realtype'] = $type; |
194
|
|
|
$column['generictype'] = $genericType; |
195
|
|
|
|
196
|
|
|
$column['null'] = 'YES'; |
197
|
|
|
if ($data['null'] && mb_strtolower($data['null']) == 'no') { |
198
|
|
|
$column['null'] = 'NO'; |
199
|
|
|
} |
200
|
|
|
|
201
|
|
|
if (empty($data['default'])) { |
202
|
|
|
$column['default'] = null; |
203
|
|
|
} else { |
204
|
|
|
$column['default'] = (string) $data['default']; |
205
|
|
|
} |
206
|
|
|
|
207
|
|
|
/** |
208
|
|
|
* Pueden existir otras definiciones de limitaciones físicas como min y max |
209
|
|
|
* De existir, tienen que ser contempladas en el método test y tener mayor peso que |
210
|
|
|
* la limitación en plantilla. |
211
|
|
|
*/ |
212
|
|
|
foreach (['min', 'max'] as $field) { |
213
|
|
|
if (isset($data[$field])) { |
214
|
|
|
$column[$field] = (string) $data[$field]; |
215
|
|
|
} |
216
|
|
|
} |
217
|
|
|
|
218
|
|
|
if (isset($data['comment'])) { |
219
|
|
|
$column['comentario'] = (string) $data['comment']; |
220
|
|
|
} |
221
|
|
|
|
222
|
|
|
if (isset($data['default'])) { |
223
|
|
|
$column['default'] = trim($data['default'], " \"'`"); |
224
|
|
|
} |
225
|
|
|
|
226
|
|
|
switch ($genericType) { |
227
|
|
|
case 'text': |
228
|
|
|
$column['dbtype'] = 'varchar(' . $length . ')'; |
229
|
|
|
$column['maxlength'] = $length; |
230
|
|
|
break; |
231
|
|
|
case 'integer': |
232
|
|
|
/** |
233
|
|
|
* Lo primero es ver la capacidad física máxima según el tipo de dato. |
234
|
|
|
*/ |
235
|
|
|
$bytes = 4; |
236
|
|
|
switch ($type) { |
237
|
|
|
case 'tinyint': |
238
|
|
|
$bytes = 1; |
239
|
|
|
break; |
240
|
|
|
case 'smallint': |
241
|
|
|
$bytes = 2; |
242
|
|
|
break; |
243
|
|
|
case 'mediumint': |
244
|
|
|
$bytes = 3; |
245
|
|
|
break; |
246
|
|
|
case 'int': |
247
|
|
|
$bytes = 4; |
248
|
|
|
break; |
249
|
|
|
case 'bigint': |
250
|
|
|
$bytes = 8; |
251
|
|
|
break; |
252
|
|
|
} |
253
|
|
|
$bits = 8 * (int) $bytes; |
254
|
|
|
$physicalMaxLength = 2 ** $bits; |
255
|
|
|
|
256
|
|
|
/** |
257
|
|
|
* $minDataLength y $maxDataLength contendrán el mínimo y máximo valor que puede contener el campo. |
258
|
|
|
*/ |
259
|
|
|
$minDataLength = $unsigned ? 0 : -$physicalMaxLength / 2; |
260
|
|
|
$maxDataLength = ($unsigned ? $physicalMaxLength : $physicalMaxLength / 2) - 1; |
261
|
|
|
|
262
|
|
|
/** |
263
|
|
|
* De momento, se asignan los límites máximos por el tipo de dato. |
264
|
|
|
* En $min y $max, iremos arrastrando los límites conforme se vayan comprobando. |
265
|
|
|
* $min nunca podrá ser menor que $minDataLength. |
266
|
|
|
* $max nunca podrá ser mayor que $maxDataLength. |
267
|
|
|
*/ |
268
|
|
|
$min = $minDataLength; |
269
|
|
|
$max = $maxDataLength; |
270
|
|
|
|
271
|
|
|
/** |
272
|
|
|
* Se puede hacer una limitación física Se puede haber definido en el xml un min y un max. |
273
|
|
|
* A todos los efectos, lo definido en el XML como min o max se toma como limitación |
274
|
|
|
* física del campo. |
275
|
|
|
*/ |
276
|
|
|
if (isset($data['min'])) { |
277
|
|
|
$minXmlLength = $data['min']; |
278
|
|
|
if ($minXmlLength > $minDataLength) { |
279
|
|
|
$min = $minXmlLength; |
280
|
|
|
} else { |
281
|
|
|
Debug::message("({$key}): Se ha especificado un min {$minXmlLength} en el XML, pero por el tipo de datos, el mínimo es {$minDataLength}."); |
|
|
|
|
282
|
|
|
} |
283
|
|
|
} |
284
|
|
|
if (isset($data['max'])) { |
285
|
|
|
$maxXmlLength = $data['max']; |
286
|
|
|
if ($maxXmlLength < $maxDataLength) { |
287
|
|
|
$max = $maxXmlLength; |
288
|
|
|
} else { |
289
|
|
|
Debug::message("({$key}): Se ha especificado un min {$maxXmlLength} en el XML, pero por el tipo de datos, el máximo es {$maxDataLength}."); |
290
|
|
|
} |
291
|
|
|
} |
292
|
|
|
|
293
|
|
|
$column['min'] = $min; |
294
|
|
|
$column['max'] = $max; |
295
|
|
|
break; |
296
|
|
|
default: |
297
|
|
|
// ??? |
298
|
|
|
} |
299
|
|
|
return $column; |
300
|
|
|
} |
301
|
|
|
|
302
|
|
|
public static function yamlFieldToDb(array $data): array |
303
|
|
|
{ |
304
|
|
|
$result = []; |
305
|
|
|
$result['Field'] = $data['key']; |
306
|
|
|
$result['Type'] = $data['dbtype']; |
307
|
|
|
$result['Null'] = !isset($data['nullable']) || $data['nullable'] ? 'YES' : 'NO'; |
308
|
|
|
$result['Key'] = $data['type'] === 'autoincrement' ? 'PRI' : ''; |
309
|
|
|
$result['Default'] = $data['default'] ?? null; |
310
|
|
|
$result['Extra'] = $data['type'] === 'autoincrement' ? 'auto_increment' : ''; |
311
|
|
|
return $result; |
312
|
|
|
} |
313
|
|
|
|
314
|
|
|
public static function dbFieldToSchema(array $data): array |
315
|
|
|
{ |
316
|
|
|
return $data; |
317
|
|
|
} |
318
|
|
|
|
319
|
|
|
public static function dbFieldToYaml(array $data): array |
320
|
|
|
{ |
321
|
|
|
return $data; |
322
|
|
|
} |
323
|
|
|
|
324
|
|
|
/** |
325
|
|
|
* Recibiendo un array con los datos de un campo tal y como lo retorna la base de |
326
|
|
|
* datos, devuelve la información normalizada para ser utilizada por Schema. |
327
|
|
|
* |
328
|
|
|
* @author Rafael San José Tovar <[email protected]> |
329
|
|
|
* @version 2023.0108 |
330
|
|
|
* |
331
|
|
|
* @param array $row |
332
|
|
|
* |
333
|
|
|
* @return array |
334
|
|
|
*/ |
335
|
|
|
public static function normalizeDbField(array $row): array |
336
|
|
|
{ |
337
|
|
|
$result = []; |
338
|
|
|
$result['Field'] = $row['key']; |
339
|
|
|
$result['Type'] = $row['type']; |
340
|
|
|
$result['Null'] = $row['nullable'] ? 'YES' : 'NO'; |
341
|
|
|
$result['Key'] = $row['type'] === 'autoincrement' ? 'PRI' : ''; |
342
|
|
|
$result['Default'] = $row['default'] ?? null; |
343
|
|
|
$result['Extra'] = $row['type'] === 'autoincrement' ? 'auto_increment' : ''; |
344
|
|
|
return $result; |
345
|
|
|
} |
346
|
|
|
|
347
|
|
|
/** |
348
|
|
|
* Transforma la definición de un campo en el archivo yaml de definición de tablas en |
349
|
|
|
* la respuesta que se obtendría para el campo al consultar la estructura de en la base |
350
|
|
|
* de datos. |
351
|
|
|
* |
352
|
|
|
* @author Rafael San José Tovar <[email protected]> |
353
|
|
|
* @version 2023.0107 |
354
|
|
|
* |
355
|
|
|
* @param array $row |
356
|
|
|
* |
357
|
|
|
* @return array |
358
|
|
|
*/ |
359
|
|
|
public static function normalizeYamlField(array $row): array |
360
|
|
|
{ |
361
|
|
|
$column = []; |
362
|
|
|
$key = (string) $row['key']; |
363
|
|
|
$type = (string) $row['type']; |
364
|
|
|
$column['key'] = $key; |
365
|
|
|
|
366
|
|
|
/** |
367
|
|
|
* Entrada: |
368
|
|
|
* - type es el tipo lógico del campo y tiene que estar definido como índice en |
369
|
|
|
* TYPES, o ser uno de los predefinidos como 'autoincrement', 'relationship', etc. |
370
|
|
|
* |
371
|
|
|
* Salida: |
372
|
|
|
* - type queda intacto. |
373
|
|
|
* - dbtype es como queda definido en la tabla, por ejemplo, varchar(20) |
374
|
|
|
* - realtype es el tipo resultado, por ejemplo varchar (sin el tamaño) |
375
|
|
|
* - generictype es uno de los índices de TYPE. P.E. autoincrement se cambiará por integer |
376
|
|
|
* |
377
|
|
|
*/ |
378
|
|
|
|
379
|
|
|
$column['type'] = $type; |
380
|
|
|
switch ($type) { |
381
|
|
|
case 'autoincrement': |
382
|
|
|
case 'relationship': |
383
|
|
|
$colType = DB::getIndexType(); |
384
|
|
|
break; |
385
|
|
|
default: |
386
|
|
|
$colType = $type; |
387
|
|
|
} |
388
|
|
|
|
389
|
|
|
$typeArray = static::splitType($colType); |
390
|
|
|
/** |
391
|
|
|
* ^ array:4 [▼ |
392
|
|
|
* "type" => "bigint" |
393
|
|
|
* "length" => null |
394
|
|
|
* "unsigned" => "yes" |
395
|
|
|
* "zerofill" => "no" |
396
|
|
|
* ] |
397
|
|
|
*/ |
398
|
|
|
|
399
|
|
|
$type = $typeArray['type']; |
400
|
|
|
$length = $typeArray['length'] ?? $row['length']; |
401
|
|
|
$unsigned = $typeArray['unsigned'] === 'yes'; |
402
|
|
|
$zerofill = $typeArray['zerofill'] === 'yes'; |
|
|
|
|
403
|
|
|
$genericType = Schema::getTypeOf($type); |
404
|
|
|
|
405
|
|
|
$column['dbtype'] = $colType; |
406
|
|
|
$column['realtype'] = $type; |
407
|
|
|
$column['generictype'] = $genericType; |
408
|
|
|
|
409
|
|
|
$column['null'] = 'YES'; |
410
|
|
|
if ($row['null'] && mb_strtolower($row['null']) == 'no') { |
411
|
|
|
$column['null'] = 'NO'; |
412
|
|
|
} |
413
|
|
|
|
414
|
|
|
if (empty($row['default'])) { |
415
|
|
|
$column['default'] = null; |
416
|
|
|
} else { |
417
|
|
|
$column['default'] = (string) $row['default']; |
418
|
|
|
} |
419
|
|
|
|
420
|
|
|
/** |
421
|
|
|
* Pueden existir otras definiciones de limitaciones físicas como min y max |
422
|
|
|
* De existir, tienen que ser contempladas en el método test y tener mayor peso que |
423
|
|
|
* la limitación en plantilla. |
424
|
|
|
*/ |
425
|
|
|
foreach (['min', 'max'] as $field) { |
426
|
|
|
if (isset($row[$field])) { |
427
|
|
|
$column[$field] = (string) $row[$field]; |
428
|
|
|
} |
429
|
|
|
} |
430
|
|
|
|
431
|
|
|
if (isset($row['comment'])) { |
432
|
|
|
$column['comentario'] = (string) $row['comment']; |
433
|
|
|
} |
434
|
|
|
|
435
|
|
|
if (isset($row['default'])) { |
436
|
|
|
$column['default'] = trim($row['default'], " \"'`"); |
437
|
|
|
} |
438
|
|
|
|
439
|
|
|
switch ($genericType) { |
440
|
|
|
case 'text': |
441
|
|
|
$column['dbtype'] = 'varchar(' . $length . ')'; |
442
|
|
|
$column['maxlength'] = $length; |
443
|
|
|
break; |
444
|
|
|
case 'integer': |
445
|
|
|
/** |
446
|
|
|
* Lo primero es ver la capacidad física máxima según el tipo de dato. |
447
|
|
|
*/ |
448
|
|
|
$bytes = 4; |
449
|
|
|
switch ($type) { |
450
|
|
|
case 'tinyint': |
451
|
|
|
$bytes = 1; |
452
|
|
|
break; |
453
|
|
|
case 'smallint': |
454
|
|
|
$bytes = 2; |
455
|
|
|
break; |
456
|
|
|
case 'mediumint': |
457
|
|
|
$bytes = 3; |
458
|
|
|
break; |
459
|
|
|
case 'int': |
460
|
|
|
$bytes = 4; |
461
|
|
|
break; |
462
|
|
|
case 'bigint': |
463
|
|
|
$bytes = 8; |
464
|
|
|
break; |
465
|
|
|
} |
466
|
|
|
$bits = 8 * (int) $bytes; |
467
|
|
|
$physicalMaxLength = 2 ** $bits; |
468
|
|
|
|
469
|
|
|
/** |
470
|
|
|
* $minDataLength y $maxDataLength contendrán el mínimo y máximo valor que puede contener el campo. |
471
|
|
|
*/ |
472
|
|
|
$minDataLength = $unsigned ? 0 : -$physicalMaxLength / 2; |
473
|
|
|
$maxDataLength = ($unsigned ? $physicalMaxLength : $physicalMaxLength / 2) - 1; |
474
|
|
|
|
475
|
|
|
/** |
476
|
|
|
* De momento, se asignan los límites máximos por el tipo de dato. |
477
|
|
|
* En $min y $max, iremos arrastrando los límites conforme se vayan comprobando. |
478
|
|
|
* $min nunca podrá ser menor que $minDataLength. |
479
|
|
|
* $max nunca podrá ser mayor que $maxDataLength. |
480
|
|
|
*/ |
481
|
|
|
$min = $minDataLength; |
482
|
|
|
$max = $maxDataLength; |
483
|
|
|
|
484
|
|
|
/** |
485
|
|
|
* Se puede hacer una limitación física Se puede haber definido en el xml un min y un max. |
486
|
|
|
* A todos los efectos, lo definido en el XML como min o max se toma como limitación |
487
|
|
|
* física del campo. |
488
|
|
|
*/ |
489
|
|
|
if (isset($row['min'])) { |
490
|
|
|
$minXmlLength = $row['min']; |
491
|
|
|
if ($minXmlLength > $minDataLength) { |
492
|
|
|
$min = $minXmlLength; |
493
|
|
|
} else { |
494
|
|
|
Debug::message("({$key}): Se ha especificado un min {$minXmlLength} en el XML, pero por el tipo de datos, el mínimo es {$minDataLength}."); |
495
|
|
|
} |
496
|
|
|
} |
497
|
|
|
if (isset($row['max'])) { |
498
|
|
|
$maxXmlLength = $row['max']; |
499
|
|
|
if ($maxXmlLength < $maxDataLength) { |
500
|
|
|
$max = $maxXmlLength; |
501
|
|
|
} else { |
502
|
|
|
Debug::message("({$key}): Se ha especificado un min {$maxXmlLength} en el XML, pero por el tipo de datos, el máximo es {$maxDataLength}."); |
503
|
|
|
} |
504
|
|
|
} |
505
|
|
|
|
506
|
|
|
$column['min'] = $min; |
507
|
|
|
$column['max'] = $max; |
508
|
|
|
break; |
509
|
|
|
default: |
510
|
|
|
// ??? |
511
|
|
|
} |
512
|
|
|
|
513
|
|
|
dump([ |
514
|
|
|
$colType => $typeArray, |
515
|
|
|
'row' => $row, |
516
|
|
|
'column' => $column, |
517
|
|
|
]); |
518
|
|
|
|
519
|
|
|
return $column; |
520
|
|
|
} |
521
|
|
|
|
522
|
|
|
public static function normalizeDbField2(array $row): array |
523
|
|
|
{ |
524
|
|
|
/* |
525
|
|
|
|
526
|
|
|
Crear un método para cada uno que sea complejo |
527
|
|
|
Ver si es necesario crear el método inverso |
528
|
|
|
|
529
|
|
|
yaml->esquema base de datos->normalizado(incluyeno el esquema) |
530
|
|
|
normalizado->esquema base de datos |
531
|
|
|
posibilidad de comprar esquema con esquema para modificar datos . |
532
|
|
|
ver necesidades con campos en índices . |
533
|
|
|
Tratar de que el resultado se pueda adaptar a otros motores de base de datos |
534
|
|
|
*/ |
535
|
|
|
|
536
|
|
|
$result = []; |
537
|
|
|
$result['Field'] = $row['key']; |
538
|
|
|
$result['Type'] = $row['type']; |
539
|
|
|
$result['Null'] = $row['nullable'] ? 'YES' : 'NO'; |
540
|
|
|
$result['Key'] = $row['type'] === 'autoincrement' ? 'PRI' : ''; |
541
|
|
|
$result['Default'] = $row['default'] ?? null; |
542
|
|
|
$result['Extra'] = $row['type'] === 'autoincrement' ? 'auto_increment' : ''; |
543
|
|
|
return $result; |
544
|
|
|
} |
545
|
|
|
|
546
|
|
|
/** |
547
|
|
|
* Returns an array with all the columns of a table |
548
|
|
|
* |
549
|
|
|
* TODO: Review the types. The variants will depend on type + length. |
550
|
|
|
* |
551
|
|
|
* 'name_of_the_field' => { |
552
|
|
|
* (Required type and length or bytes) |
553
|
|
|
* 'type' => (string/integer/float/decimal/boolean/date/datetime/text/blob) |
554
|
|
|
* 'length' => It is the number of characters that the field needs (optional if bytes exists) |
555
|
|
|
* 'bytes' => Number of bytes occupied by the data (optional if length exists) |
556
|
|
|
* (Optional) |
557
|
|
|
* 'default' => Default value |
558
|
|
|
* 'nullable' => True if it can be null |
559
|
|
|
* 'primary' => True if it is the primary key |
560
|
|
|
* 'autoincrement' => True if it is an autoincremental number |
561
|
|
|
* 'zerofilled' => True if it completes zeros on the left |
562
|
|
|
* } |
563
|
|
|
* |
564
|
|
|
* @param string $tableName |
565
|
|
|
* |
566
|
|
|
* @return array |
567
|
|
|
*/ |
568
|
|
|
public static function getColumns2(string $tableName): array |
569
|
|
|
{ |
570
|
|
|
$query = 'SHOW COLUMNS FROM ' . self::quoteTableName($tableName) . ';'; |
571
|
|
|
$data = DB::select($query); |
572
|
|
|
$result = []; |
573
|
|
|
foreach ($data as $value) { |
574
|
|
|
$row = self::normalizeField($value); |
|
|
|
|
575
|
|
|
$result[$row['field']] = $row; |
576
|
|
|
} |
577
|
|
|
return $result; |
578
|
|
|
} |
579
|
|
|
|
580
|
|
|
/** |
581
|
|
|
* Transforma la definición de un campo en el archivo yaml de definición de tablas en |
582
|
|
|
* la respuesta que se obtendría para el campo al consultar la estructura de en la base |
583
|
|
|
* de datos. |
584
|
|
|
* |
585
|
|
|
* @author Rafael San José Tovar <[email protected]> |
586
|
|
|
* @version 2023.0107 |
587
|
|
|
* |
588
|
|
|
* @param array $row |
589
|
|
|
* |
590
|
|
|
* @return array |
591
|
|
|
*/ |
592
|
|
|
public static function normalizeYamlField2(array $row): array |
593
|
|
|
{ |
594
|
|
|
$result = []; |
595
|
|
|
$result['field'] = $row['Field']; |
596
|
|
|
|
597
|
|
|
dump($row); |
598
|
|
|
|
599
|
|
|
$type = self::splitType($row['type']); |
600
|
|
|
|
601
|
|
|
dump($type); |
602
|
|
|
|
603
|
|
|
/** |
604
|
|
|
* I thought that this would work |
605
|
|
|
* |
606
|
|
|
* $virtualType = array_search($type['type'], $this->fieldTypes); |
607
|
|
|
*/ |
608
|
|
|
$virtualType = $type['type']; |
609
|
|
|
foreach (self::getDataTypes() as $key => $values) { |
610
|
|
|
if (in_array($type['type'], $values)) { |
611
|
|
|
$virtualType = $key; |
612
|
|
|
break; |
613
|
|
|
} |
614
|
|
|
} |
615
|
|
|
|
616
|
|
|
$result['type'] = $virtualType; |
617
|
|
|
if ($virtualType === false) { |
|
|
|
|
618
|
|
|
$result['type'] = $type['type']; |
619
|
|
|
DebugTool::getInstance()->addMessage('Deprecated', 'Correct the data type ' . $type['type'] . ' in MySql database'); |
620
|
|
|
} |
621
|
|
|
$result['unsigned'] = $type['unsigned'] === 'unsigned'; |
622
|
|
|
$result['length'] = $type['length'] ?? null; |
623
|
|
|
$result['default'] = $row['Default'] ?? null; |
624
|
|
|
$result['nullable'] = $row['Null']; |
625
|
|
|
$result['primary'] = $row['Key']; |
626
|
|
|
$result['autoincrement'] = $row['Extra'] == 'auto_increment' ? 1 : 0; |
627
|
|
|
|
628
|
|
|
return $result; |
629
|
|
|
} |
630
|
|
|
|
631
|
|
|
/** |
632
|
|
|
* Divide the data type of a MySQL field into its various components: type, |
633
|
|
|
* length, unsigned or zerofill, if applicable. |
634
|
|
|
* |
635
|
|
|
* @param string $originalType |
636
|
|
|
* |
637
|
|
|
* @return array |
638
|
|
|
*/ |
639
|
|
|
private static function splitType(string $originalType): array |
640
|
|
|
{ |
641
|
|
|
$explode = explode(' ', strtolower($originalType)); |
642
|
|
|
|
643
|
|
|
$pos = strpos($explode[0], '('); |
644
|
|
|
|
645
|
|
|
$type = $pos ? substr($explode[0], 0, $pos) : $explode[0]; |
646
|
|
|
$length = $pos ? intval(substr($explode[0], $pos + 1)) : null; |
647
|
|
|
|
648
|
|
|
$pos = array_search('unsigned', $explode); |
649
|
|
|
$unsigned = $pos ? 'unsigned' : null; |
650
|
|
|
|
651
|
|
|
$pos = array_search('zerofill', $explode); |
652
|
|
|
$zerofill = $pos ? 'zerofill' : null; |
653
|
|
|
|
654
|
|
|
return ['type' => $type, 'length' => $length, 'unsigned' => $unsigned, 'zerofill' => $zerofill]; |
655
|
|
|
} |
656
|
|
|
|
657
|
|
|
/** |
658
|
|
|
* Returns an array with the index information, and if there are, also constraints. |
659
|
|
|
* |
660
|
|
|
* @param array $row |
661
|
|
|
* |
662
|
|
|
* @return array |
663
|
|
|
*/ |
664
|
|
|
public function normalizeIndexes(array $row): array |
665
|
|
|
{ |
666
|
|
|
$result = []; |
667
|
|
|
$result['index'] = $row['Key_name']; |
668
|
|
|
$result['column'] = $row['Column_name']; |
669
|
|
|
$result['unique'] = $row['Non_unique'] == '0' ? 1 : 0; |
670
|
|
|
$result['nullable'] = $row['Null'] == 'YES' ? 1 : 0; |
671
|
|
|
$constrait = $this->getConstraintData($row['Table'], $row['Key_name']); |
672
|
|
|
if (count($constrait) > 0) { |
673
|
|
|
$result['constraint'] = $constrait[0]['CONSTRAINT_NAME']; |
674
|
|
|
$result['referencedtable'] = $constrait[0]['REFERENCED_TABLE_NAME']; |
675
|
|
|
$result['referencedfield'] = $constrait[0]['REFERENCED_COLUMN_NAME']; |
676
|
|
|
} |
677
|
|
|
$constrait = $this->getConstraintRules($row['Table'], $row['Key_name']); |
678
|
|
|
if (count($constrait) > 0) { |
679
|
|
|
$result['matchoption'] = $constrait[0]['MATCH_OPTION']; |
680
|
|
|
$result['updaterule'] = $constrait[0]['UPDATE_RULE']; |
681
|
|
|
$result['deleterule'] = $constrait[0]['DELETE_RULE']; |
682
|
|
|
} |
683
|
|
|
return $result; |
684
|
|
|
} |
685
|
|
|
|
686
|
|
|
/** |
687
|
|
|
* The data about the constraint that is found in the KEY_COLUMN_USAGE table |
688
|
|
|
* is returned. |
689
|
|
|
* Attempting to return the consolidated data generates an extremely slow query |
690
|
|
|
* in some MySQL installations, so 2 additional simple queries are made. |
691
|
|
|
* |
692
|
|
|
* @param string $tableName |
693
|
|
|
* @param string $constraintName |
694
|
|
|
* |
695
|
|
|
* @return array |
696
|
|
|
*/ |
697
|
|
|
private function getConstraintData(string $tableName, string $constraintName): array |
698
|
|
|
{ |
699
|
|
|
$dbName = Config::getVar('dbName') ?? 'Unknown'; |
|
|
|
|
700
|
|
|
|
701
|
|
|
return DB::select(' |
702
|
|
|
SELECT |
703
|
|
|
TABLE_NAME, |
704
|
|
|
COLUMN_NAME, |
705
|
|
|
CONSTRAINT_NAME, |
706
|
|
|
REFERENCED_TABLE_NAME, |
707
|
|
|
REFERENCED_COLUMN_NAME |
708
|
|
|
FROM |
709
|
|
|
INFORMATION_SCHEMA.KEY_COLUMN_USAGE |
710
|
|
|
WHERE |
711
|
|
|
TABLE_SCHEMA = ' . $this->quoteFieldName($dbName) . ' AND |
712
|
|
|
TABLE_NAME = ' . $this->quoteFieldName($tableName) . ' AND |
713
|
|
|
constraint_name = ' . $this->quoteFieldName($constraintName) . ' AND |
714
|
|
|
REFERENCED_COLUMN_NAME IS NOT NULL; |
715
|
|
|
'); |
716
|
|
|
} |
717
|
|
|
|
718
|
|
|
/** |
719
|
|
|
* The rules for updating and deleting data with constraint (table |
720
|
|
|
* REFERENTIAL_CONSTRAINTS) are returned. |
721
|
|
|
* Attempting to return the consolidated data generates an extremely slow query |
722
|
|
|
* in some MySQL installations, so 2 additional simple queries are made. |
723
|
|
|
* |
724
|
|
|
* @param string $tableName |
725
|
|
|
* @param string $constraintName |
726
|
|
|
* |
727
|
|
|
* @return array |
728
|
|
|
*/ |
729
|
|
|
private function getConstraintRules(string $tableName, string $constraintName): array |
730
|
|
|
{ |
731
|
|
|
$dbName = Config::getVar('dbName') ?? 'Unknown'; |
|
|
|
|
732
|
|
|
|
733
|
|
|
return DB::selectselect(' |
|
|
|
|
734
|
|
|
SELECT |
735
|
|
|
MATCH_OPTION, |
736
|
|
|
UPDATE_RULE, |
737
|
|
|
DELETE_RULE |
738
|
|
|
FROM information_schema.REFERENTIAL_CONSTRAINTS |
739
|
|
|
WHERE |
740
|
|
|
constraint_schema = ' . $this->quoteFieldName($dbName) . ' AND |
741
|
|
|
table_name = ' . $this->quoteFieldName($tableName) . ' AND |
742
|
|
|
constraint_name = ' . $this->quoteFieldName($constraintName) . '; |
743
|
|
|
'); |
744
|
|
|
} |
745
|
|
|
|
746
|
|
|
/** |
747
|
|
|
* Obtain an array with the basic information about the indexes of the table, |
748
|
|
|
* which will be supplemented with the restrictions later. |
749
|
|
|
* |
750
|
|
|
* @param string $tableName |
751
|
|
|
* |
752
|
|
|
* @return string |
753
|
|
|
*/ |
754
|
|
|
public function getIndexesSql(string $tableName): string |
755
|
|
|
{ |
756
|
|
|
// https://stackoverflow.com/questions/5213339/how-to-see-indexes-for-a-database-or-table-in-mysql |
757
|
|
|
|
758
|
|
|
return 'SHOW INDEX FROM ' . Config::getInstance()->getSqlHelper()->quoteTableName($tableName); |
|
|
|
|
759
|
|
|
} |
760
|
|
|
|
761
|
|
|
public static function modify(string $tableName, array $oldField, array $newField): string |
762
|
|
|
{ |
763
|
|
|
$sql = 'ALTER TABLE ' . self::quoteTableName($tableName) . ' ' . $oldField['Field'] . ' ' . $newField['Field'] . ' '; |
764
|
|
|
$sql .= $newField['Type'] . ' '; |
765
|
|
|
if ($newField) { |
|
|
|
|
766
|
|
|
if ($oldField['Null'] === 'NO') { |
767
|
|
|
$sql .= 'NOT '; |
768
|
|
|
} |
769
|
|
|
} |
770
|
|
|
$sql .= 'NULL'; |
771
|
|
|
if ($newField['Default'] !== null) { |
772
|
|
|
$sql .= ' DEFAULT "' . $newField['Default'] . '"'; |
773
|
|
|
} |
774
|
|
|
$sql .= ';'; |
775
|
|
|
|
776
|
|
|
return $sql; |
777
|
|
|
} |
778
|
|
|
} |
779
|
|
|
|
The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g.
excluded_paths: ["lib/*"]
, you can move it to the dependency path list as follows:For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths