1<?php
2
3namespace Doctrine\DBAL\Schema;
4
5use Doctrine\DBAL\Platforms\MariaDb1027Platform;
6use Doctrine\DBAL\Platforms\MySqlPlatform;
7use Doctrine\DBAL\Types\Type;
8
9use function array_change_key_case;
10use function array_shift;
11use function array_values;
12use function assert;
13use function explode;
14use function is_string;
15use function preg_match;
16use function strpos;
17use function strtok;
18use function strtolower;
19use function strtr;
20
21use const CASE_LOWER;
22
23/**
24 * Schema manager for the MySql RDBMS.
25 */
26class MySqlSchemaManager extends AbstractSchemaManager
27{
28    /**
29     * @see https://mariadb.com/kb/en/library/string-literals/#escape-sequences
30     */
31    private const MARIADB_ESCAPE_SEQUENCES = [
32        '\\0' => "\0",
33        "\\'" => "'",
34        '\\"' => '"',
35        '\\b' => "\b",
36        '\\n' => "\n",
37        '\\r' => "\r",
38        '\\t' => "\t",
39        '\\Z' => "\x1a",
40        '\\\\' => '\\',
41        '\\%' => '%',
42        '\\_' => '_',
43
44        // Internally, MariaDB escapes single quotes using the standard syntax
45        "''" => "'",
46    ];
47
48    /**
49     * {@inheritdoc}
50     */
51    protected function _getPortableViewDefinition($view)
52    {
53        return new View($view['TABLE_NAME'], $view['VIEW_DEFINITION']);
54    }
55
56    /**
57     * {@inheritdoc}
58     */
59    protected function _getPortableTableDefinition($table)
60    {
61        return array_shift($table);
62    }
63
64    /**
65     * {@inheritdoc}
66     */
67    protected function _getPortableUserDefinition($user)
68    {
69        return [
70            'user' => $user['User'],
71            'password' => $user['Password'],
72        ];
73    }
74
75    /**
76     * {@inheritdoc}
77     */
78    protected function _getPortableTableIndexesList($tableIndexes, $tableName = null)
79    {
80        foreach ($tableIndexes as $k => $v) {
81            $v = array_change_key_case($v, CASE_LOWER);
82            if ($v['key_name'] === 'PRIMARY') {
83                $v['primary'] = true;
84            } else {
85                $v['primary'] = false;
86            }
87
88            if (strpos($v['index_type'], 'FULLTEXT') !== false) {
89                $v['flags'] = ['FULLTEXT'];
90            } elseif (strpos($v['index_type'], 'SPATIAL') !== false) {
91                $v['flags'] = ['SPATIAL'];
92            }
93
94            $v['length'] = isset($v['sub_part']) ? (int) $v['sub_part'] : null;
95
96            $tableIndexes[$k] = $v;
97        }
98
99        return parent::_getPortableTableIndexesList($tableIndexes, $tableName);
100    }
101
102    /**
103     * {@inheritdoc}
104     */
105    protected function _getPortableDatabaseDefinition($database)
106    {
107        return $database['Database'];
108    }
109
110    /**
111     * {@inheritdoc}
112     */
113    protected function _getPortableTableColumnDefinition($tableColumn)
114    {
115        $tableColumn = array_change_key_case($tableColumn, CASE_LOWER);
116
117        $dbType = strtolower($tableColumn['type']);
118        $dbType = strtok($dbType, '(), ');
119        assert(is_string($dbType));
120
121        $length = $tableColumn['length'] ?? strtok('(), ');
122
123        $fixed = null;
124
125        if (! isset($tableColumn['name'])) {
126            $tableColumn['name'] = '';
127        }
128
129        $scale     = null;
130        $precision = null;
131
132        $type = $this->_platform->getDoctrineTypeMapping($dbType);
133
134        // In cases where not connected to a database DESCRIBE $table does not return 'Comment'
135        if (isset($tableColumn['comment'])) {
136            $type                   = $this->extractDoctrineTypeFromComment($tableColumn['comment'], $type);
137            $tableColumn['comment'] = $this->removeDoctrineTypeFromComment($tableColumn['comment'], $type);
138        }
139
140        switch ($dbType) {
141            case 'char':
142            case 'binary':
143                $fixed = true;
144                break;
145
146            case 'float':
147            case 'double':
148            case 'real':
149            case 'numeric':
150            case 'decimal':
151                if (preg_match('([A-Za-z]+\(([0-9]+)\,([0-9]+)\))', $tableColumn['type'], $match)) {
152                    $precision = $match[1];
153                    $scale     = $match[2];
154                    $length    = null;
155                }
156
157                break;
158
159            case 'tinytext':
160                $length = MySqlPlatform::LENGTH_LIMIT_TINYTEXT;
161                break;
162
163            case 'text':
164                $length = MySqlPlatform::LENGTH_LIMIT_TEXT;
165                break;
166
167            case 'mediumtext':
168                $length = MySqlPlatform::LENGTH_LIMIT_MEDIUMTEXT;
169                break;
170
171            case 'tinyblob':
172                $length = MySqlPlatform::LENGTH_LIMIT_TINYBLOB;
173                break;
174
175            case 'blob':
176                $length = MySqlPlatform::LENGTH_LIMIT_BLOB;
177                break;
178
179            case 'mediumblob':
180                $length = MySqlPlatform::LENGTH_LIMIT_MEDIUMBLOB;
181                break;
182
183            case 'tinyint':
184            case 'smallint':
185            case 'mediumint':
186            case 'int':
187            case 'integer':
188            case 'bigint':
189            case 'year':
190                $length = null;
191                break;
192        }
193
194        if ($this->_platform instanceof MariaDb1027Platform) {
195            $columnDefault = $this->getMariaDb1027ColumnDefault($this->_platform, $tableColumn['default']);
196        } else {
197            $columnDefault = $tableColumn['default'];
198        }
199
200        $options = [
201            'length'        => $length !== null ? (int) $length : null,
202            'unsigned'      => strpos($tableColumn['type'], 'unsigned') !== false,
203            'fixed'         => (bool) $fixed,
204            'default'       => $columnDefault,
205            'notnull'       => $tableColumn['null'] !== 'YES',
206            'scale'         => null,
207            'precision'     => null,
208            'autoincrement' => strpos($tableColumn['extra'], 'auto_increment') !== false,
209            'comment'       => isset($tableColumn['comment']) && $tableColumn['comment'] !== ''
210                ? $tableColumn['comment']
211                : null,
212        ];
213
214        if ($scale !== null && $precision !== null) {
215            $options['scale']     = (int) $scale;
216            $options['precision'] = (int) $precision;
217        }
218
219        $column = new Column($tableColumn['field'], Type::getType($type), $options);
220
221        if (isset($tableColumn['characterset'])) {
222            $column->setPlatformOption('charset', $tableColumn['characterset']);
223        }
224
225        if (isset($tableColumn['collation'])) {
226            $column->setPlatformOption('collation', $tableColumn['collation']);
227        }
228
229        return $column;
230    }
231
232    /**
233     * Return Doctrine/Mysql-compatible column default values for MariaDB 10.2.7+ servers.
234     *
235     * - Since MariaDb 10.2.7 column defaults stored in information_schema are now quoted
236     *   to distinguish them from expressions (see MDEV-10134).
237     * - CURRENT_TIMESTAMP, CURRENT_TIME, CURRENT_DATE are stored in information_schema
238     *   as current_timestamp(), currdate(), currtime()
239     * - Quoted 'NULL' is not enforced by Maria, it is technically possible to have
240     *   null in some circumstances (see https://jira.mariadb.org/browse/MDEV-14053)
241     * - \' is always stored as '' in information_schema (normalized)
242     *
243     * @link https://mariadb.com/kb/en/library/information-schema-columns-table/
244     * @link https://jira.mariadb.org/browse/MDEV-13132
245     *
246     * @param string|null $columnDefault default value as stored in information_schema for MariaDB >= 10.2.7
247     */
248    private function getMariaDb1027ColumnDefault(MariaDb1027Platform $platform, ?string $columnDefault): ?string
249    {
250        if ($columnDefault === 'NULL' || $columnDefault === null) {
251            return null;
252        }
253
254        if (preg_match('/^\'(.*)\'$/', $columnDefault, $matches)) {
255            return strtr($matches[1], self::MARIADB_ESCAPE_SEQUENCES);
256        }
257
258        switch ($columnDefault) {
259            case 'current_timestamp()':
260                return $platform->getCurrentTimestampSQL();
261
262            case 'curdate()':
263                return $platform->getCurrentDateSQL();
264
265            case 'curtime()':
266                return $platform->getCurrentTimeSQL();
267        }
268
269        return $columnDefault;
270    }
271
272    /**
273     * {@inheritdoc}
274     */
275    protected function _getPortableTableForeignKeysList($tableForeignKeys)
276    {
277        $list = [];
278        foreach ($tableForeignKeys as $value) {
279            $value = array_change_key_case($value, CASE_LOWER);
280            if (! isset($list[$value['constraint_name']])) {
281                if (! isset($value['delete_rule']) || $value['delete_rule'] === 'RESTRICT') {
282                    $value['delete_rule'] = null;
283                }
284
285                if (! isset($value['update_rule']) || $value['update_rule'] === 'RESTRICT') {
286                    $value['update_rule'] = null;
287                }
288
289                $list[$value['constraint_name']] = [
290                    'name' => $value['constraint_name'],
291                    'local' => [],
292                    'foreign' => [],
293                    'foreignTable' => $value['referenced_table_name'],
294                    'onDelete' => $value['delete_rule'],
295                    'onUpdate' => $value['update_rule'],
296                ];
297            }
298
299            $list[$value['constraint_name']]['local'][]   = $value['column_name'];
300            $list[$value['constraint_name']]['foreign'][] = $value['referenced_column_name'];
301        }
302
303        $result = [];
304        foreach ($list as $constraint) {
305            $result[] = new ForeignKeyConstraint(
306                array_values($constraint['local']),
307                $constraint['foreignTable'],
308                array_values($constraint['foreign']),
309                $constraint['name'],
310                [
311                    'onDelete' => $constraint['onDelete'],
312                    'onUpdate' => $constraint['onUpdate'],
313                ]
314            );
315        }
316
317        return $result;
318    }
319
320    /**
321     * {@inheritdoc}
322     */
323    public function listTableDetails($name)
324    {
325        $table = parent::listTableDetails($name);
326
327        $platform = $this->_platform;
328        assert($platform instanceof MySqlPlatform);
329        $sql = $platform->getListTableMetadataSQL($name);
330
331        $tableOptions = $this->_conn->fetchAssoc($sql);
332
333        if ($tableOptions === false) {
334            return $table;
335        }
336
337        $table->addOption('engine', $tableOptions['ENGINE']);
338
339        if ($tableOptions['TABLE_COLLATION'] !== null) {
340            $table->addOption('collation', $tableOptions['TABLE_COLLATION']);
341        }
342
343        if ($tableOptions['AUTO_INCREMENT'] !== null) {
344            $table->addOption('autoincrement', $tableOptions['AUTO_INCREMENT']);
345        }
346
347        $table->addOption('comment', $tableOptions['TABLE_COMMENT']);
348        $table->addOption('create_options', $this->parseCreateOptions($tableOptions['CREATE_OPTIONS']));
349
350        return $table;
351    }
352
353    /**
354     * @return string[]|true[]
355     */
356    private function parseCreateOptions(?string $string): array
357    {
358        $options = [];
359
360        if ($string === null || $string === '') {
361            return $options;
362        }
363
364        foreach (explode(' ', $string) as $pair) {
365            $parts = explode('=', $pair, 2);
366
367            $options[$parts[0]] = $parts[1] ?? true;
368        }
369
370        return $options;
371    }
372}
373