1<?php
2
3namespace Doctrine\DBAL\Schema;
4
5use Doctrine\DBAL\DriverManager;
6use Doctrine\DBAL\Exception;
7use Doctrine\DBAL\Types\StringType;
8use Doctrine\DBAL\Types\TextType;
9use Doctrine\DBAL\Types\Type;
10
11use function array_change_key_case;
12use function array_map;
13use function array_merge;
14use function array_reverse;
15use function array_values;
16use function explode;
17use function file_exists;
18use function preg_match;
19use function preg_match_all;
20use function preg_quote;
21use function preg_replace;
22use function rtrim;
23use function sprintf;
24use function str_replace;
25use function strpos;
26use function strtolower;
27use function trim;
28use function unlink;
29use function usort;
30
31use const CASE_LOWER;
32
33/**
34 * Sqlite SchemaManager.
35 */
36class SqliteSchemaManager extends AbstractSchemaManager
37{
38    /**
39     * {@inheritdoc}
40     */
41    public function dropDatabase($database)
42    {
43        if (! file_exists($database)) {
44            return;
45        }
46
47        unlink($database);
48    }
49
50    /**
51     * {@inheritdoc}
52     */
53    public function createDatabase($database)
54    {
55        $params = $this->_conn->getParams();
56
57        $params['path'] = $database;
58        unset($params['memory']);
59
60        $conn = DriverManager::getConnection($params);
61        $conn->connect();
62        $conn->close();
63    }
64
65    /**
66     * {@inheritdoc}
67     */
68    public function renameTable($name, $newName)
69    {
70        $tableDiff            = new TableDiff($name);
71        $tableDiff->fromTable = $this->listTableDetails($name);
72        $tableDiff->newName   = $newName;
73        $this->alterTable($tableDiff);
74    }
75
76    /**
77     * {@inheritdoc}
78     */
79    public function createForeignKey(ForeignKeyConstraint $foreignKey, $table)
80    {
81        $tableDiff                     = $this->getTableDiffForAlterForeignKey($table);
82        $tableDiff->addedForeignKeys[] = $foreignKey;
83
84        $this->alterTable($tableDiff);
85    }
86
87    /**
88     * {@inheritdoc}
89     */
90    public function dropAndCreateForeignKey(ForeignKeyConstraint $foreignKey, $table)
91    {
92        $tableDiff                       = $this->getTableDiffForAlterForeignKey($table);
93        $tableDiff->changedForeignKeys[] = $foreignKey;
94
95        $this->alterTable($tableDiff);
96    }
97
98    /**
99     * {@inheritdoc}
100     */
101    public function dropForeignKey($foreignKey, $table)
102    {
103        $tableDiff                       = $this->getTableDiffForAlterForeignKey($table);
104        $tableDiff->removedForeignKeys[] = $foreignKey;
105
106        $this->alterTable($tableDiff);
107    }
108
109    /**
110     * {@inheritdoc}
111     */
112    public function listTableForeignKeys($table, $database = null)
113    {
114        if ($database === null) {
115            $database = $this->_conn->getDatabase();
116        }
117
118        $sql              = $this->_platform->getListTableForeignKeysSQL($table, $database);
119        $tableForeignKeys = $this->_conn->fetchAllAssociative($sql);
120
121        if (! empty($tableForeignKeys)) {
122            $createSql = $this->getCreateTableSQL($table);
123
124            if (
125                $createSql !== null && preg_match_all(
126                    '#
127                    (?:CONSTRAINT\s+([^\s]+)\s+)?
128                    (?:FOREIGN\s+KEY[^\)]+\)\s*)?
129                    REFERENCES\s+[^\s]+\s+(?:\([^\)]+\))?
130                    (?:
131                        [^,]*?
132                        (NOT\s+DEFERRABLE|DEFERRABLE)
133                        (?:\s+INITIALLY\s+(DEFERRED|IMMEDIATE))?
134                    )?#isx',
135                    $createSql,
136                    $match
137                )
138            ) {
139                $names      = array_reverse($match[1]);
140                $deferrable = array_reverse($match[2]);
141                $deferred   = array_reverse($match[3]);
142            } else {
143                $names = $deferrable = $deferred = [];
144            }
145
146            foreach ($tableForeignKeys as $key => $value) {
147                $id = $value['id'];
148
149                $tableForeignKeys[$key] = array_merge($tableForeignKeys[$key], [
150                    'constraint_name' => isset($names[$id]) && $names[$id] !== '' ? $names[$id] : $id,
151                    'deferrable'      => isset($deferrable[$id]) && strtolower($deferrable[$id]) === 'deferrable',
152                    'deferred'        => isset($deferred[$id]) && strtolower($deferred[$id]) === 'deferred',
153                ]);
154            }
155        }
156
157        return $this->_getPortableTableForeignKeysList($tableForeignKeys);
158    }
159
160    /**
161     * {@inheritdoc}
162     */
163    protected function _getPortableTableDefinition($table)
164    {
165        return $table['name'];
166    }
167
168    /**
169     * {@inheritdoc}
170     *
171     * @link http://ezcomponents.org/docs/api/trunk/DatabaseSchema/ezcDbSchemaPgsqlReader.html
172     */
173    protected function _getPortableTableIndexesList($tableIndexes, $tableName = null)
174    {
175        $indexBuffer = [];
176
177        // fetch primary
178        $indexArray = $this->_conn->fetchAllAssociative(sprintf(
179            'PRAGMA TABLE_INFO (%s)',
180            $this->_conn->quote($tableName)
181        ));
182
183        usort(
184            $indexArray,
185            /**
186             * @param array<string,mixed> $a
187             * @param array<string,mixed> $b
188             */
189            static function (array $a, array $b): int {
190                if ($a['pk'] === $b['pk']) {
191                    return $a['cid'] - $b['cid'];
192                }
193
194                return $a['pk'] - $b['pk'];
195            }
196        );
197
198        foreach ($indexArray as $indexColumnRow) {
199            if ($indexColumnRow['pk'] === 0 || $indexColumnRow['pk'] === '0') {
200                continue;
201            }
202
203            $indexBuffer[] = [
204                'key_name' => 'primary',
205                'primary' => true,
206                'non_unique' => false,
207                'column_name' => $indexColumnRow['name'],
208            ];
209        }
210
211        // fetch regular indexes
212        foreach ($tableIndexes as $tableIndex) {
213            // Ignore indexes with reserved names, e.g. autoindexes
214            if (strpos($tableIndex['name'], 'sqlite_') === 0) {
215                continue;
216            }
217
218            $keyName           = $tableIndex['name'];
219            $idx               = [];
220            $idx['key_name']   = $keyName;
221            $idx['primary']    = false;
222            $idx['non_unique'] = ! $tableIndex['unique'];
223
224            $indexArray = $this->_conn->fetchAllAssociative(sprintf(
225                'PRAGMA INDEX_INFO (%s)',
226                $this->_conn->quote($keyName)
227            ));
228
229            foreach ($indexArray as $indexColumnRow) {
230                $idx['column_name'] = $indexColumnRow['name'];
231                $indexBuffer[]      = $idx;
232            }
233        }
234
235        return parent::_getPortableTableIndexesList($indexBuffer, $tableName);
236    }
237
238    /**
239     * @deprecated
240     *
241     * @param array<string, mixed> $tableIndex
242     *
243     * @return array<string, bool|string>
244     */
245    protected function _getPortableTableIndexDefinition($tableIndex)
246    {
247        return [
248            'name' => $tableIndex['name'],
249            'unique' => (bool) $tableIndex['unique'],
250        ];
251    }
252
253    /**
254     * {@inheritdoc}
255     */
256    protected function _getPortableTableColumnList($table, $database, $tableColumns)
257    {
258        $list = parent::_getPortableTableColumnList($table, $database, $tableColumns);
259
260        // find column with autoincrement
261        $autoincrementColumn = null;
262        $autoincrementCount  = 0;
263
264        foreach ($tableColumns as $tableColumn) {
265            if ($tableColumn['pk'] === 0 || $tableColumn['pk'] === '0') {
266                continue;
267            }
268
269            $autoincrementCount++;
270            if ($autoincrementColumn !== null || strtolower($tableColumn['type']) !== 'integer') {
271                continue;
272            }
273
274            $autoincrementColumn = $tableColumn['name'];
275        }
276
277        if ($autoincrementCount === 1 && $autoincrementColumn !== null) {
278            foreach ($list as $column) {
279                if ($autoincrementColumn !== $column->getName()) {
280                    continue;
281                }
282
283                $column->setAutoincrement(true);
284            }
285        }
286
287        // inspect column collation and comments
288        $createSql = $this->getCreateTableSQL($table) ?? '';
289
290        foreach ($list as $columnName => $column) {
291            $type = $column->getType();
292
293            if ($type instanceof StringType || $type instanceof TextType) {
294                $column->setPlatformOption(
295                    'collation',
296                    $this->parseColumnCollationFromSQL($columnName, $createSql) ?: 'BINARY'
297                );
298            }
299
300            $comment = $this->parseColumnCommentFromSQL($columnName, $createSql);
301
302            if ($comment === null) {
303                continue;
304            }
305
306            $type = $this->extractDoctrineTypeFromComment($comment, '');
307
308            if ($type !== '') {
309                $column->setType(Type::getType($type));
310
311                $comment = $this->removeDoctrineTypeFromComment($comment, $type);
312            }
313
314            $column->setComment($comment);
315        }
316
317        return $list;
318    }
319
320    /**
321     * {@inheritdoc}
322     */
323    protected function _getPortableTableColumnDefinition($tableColumn)
324    {
325        $parts               = explode('(', $tableColumn['type']);
326        $tableColumn['type'] = trim($parts[0]);
327        if (isset($parts[1])) {
328            $length                = trim($parts[1], ')');
329            $tableColumn['length'] = $length;
330        }
331
332        $dbType   = strtolower($tableColumn['type']);
333        $length   = $tableColumn['length'] ?? null;
334        $unsigned = false;
335
336        if (strpos($dbType, ' unsigned') !== false) {
337            $dbType   = str_replace(' unsigned', '', $dbType);
338            $unsigned = true;
339        }
340
341        $fixed   = false;
342        $type    = $this->_platform->getDoctrineTypeMapping($dbType);
343        $default = $tableColumn['dflt_value'];
344        if ($default === 'NULL') {
345            $default = null;
346        }
347
348        if ($default !== null) {
349            // SQLite returns the default value as a literal expression, so we need to parse it
350            if (preg_match('/^\'(.*)\'$/s', $default, $matches)) {
351                $default = str_replace("''", "'", $matches[1]);
352            }
353        }
354
355        $notnull = (bool) $tableColumn['notnull'];
356
357        if (! isset($tableColumn['name'])) {
358            $tableColumn['name'] = '';
359        }
360
361        $precision = null;
362        $scale     = null;
363
364        switch ($dbType) {
365            case 'char':
366                $fixed = true;
367                break;
368            case 'float':
369            case 'double':
370            case 'real':
371            case 'decimal':
372            case 'numeric':
373                if (isset($tableColumn['length'])) {
374                    if (strpos($tableColumn['length'], ',') === false) {
375                        $tableColumn['length'] .= ',0';
376                    }
377
378                    [$precision, $scale] = array_map('trim', explode(',', $tableColumn['length']));
379                }
380
381                $length = null;
382                break;
383        }
384
385        $options = [
386            'length'   => $length,
387            'unsigned' => $unsigned,
388            'fixed'    => $fixed,
389            'notnull'  => $notnull,
390            'default'  => $default,
391            'precision' => $precision,
392            'scale'     => $scale,
393            'autoincrement' => false,
394        ];
395
396        return new Column($tableColumn['name'], Type::getType($type), $options);
397    }
398
399    /**
400     * {@inheritdoc}
401     */
402    protected function _getPortableViewDefinition($view)
403    {
404        return new View($view['name'], $view['sql']);
405    }
406
407    /**
408     * {@inheritdoc}
409     */
410    protected function _getPortableTableForeignKeysList($tableForeignKeys)
411    {
412        $list = [];
413        foreach ($tableForeignKeys as $value) {
414            $value = array_change_key_case($value, CASE_LOWER);
415            $name  = $value['constraint_name'];
416            if (! isset($list[$name])) {
417                if (! isset($value['on_delete']) || $value['on_delete'] === 'RESTRICT') {
418                    $value['on_delete'] = null;
419                }
420
421                if (! isset($value['on_update']) || $value['on_update'] === 'RESTRICT') {
422                    $value['on_update'] = null;
423                }
424
425                $list[$name] = [
426                    'name' => $name,
427                    'local' => [],
428                    'foreign' => [],
429                    'foreignTable' => $value['table'],
430                    'onDelete' => $value['on_delete'],
431                    'onUpdate' => $value['on_update'],
432                    'deferrable' => $value['deferrable'],
433                    'deferred' => $value['deferred'],
434                ];
435            }
436
437            $list[$name]['local'][] = $value['from'];
438
439            if ($value['to'] === null) {
440                continue;
441            }
442
443            $list[$name]['foreign'][] = $value['to'];
444        }
445
446        $result = [];
447        foreach ($list as $constraint) {
448            $result[] = new ForeignKeyConstraint(
449                array_values($constraint['local']),
450                $constraint['foreignTable'],
451                array_values($constraint['foreign']),
452                $constraint['name'],
453                [
454                    'onDelete' => $constraint['onDelete'],
455                    'onUpdate' => $constraint['onUpdate'],
456                    'deferrable' => $constraint['deferrable'],
457                    'deferred' => $constraint['deferred'],
458                ]
459            );
460        }
461
462        return $result;
463    }
464
465    /**
466     * @param Table|string $table
467     *
468     * @return TableDiff
469     *
470     * @throws Exception
471     */
472    private function getTableDiffForAlterForeignKey($table)
473    {
474        if (! $table instanceof Table) {
475            $tableDetails = $this->tryMethod('listTableDetails', $table);
476
477            if ($tableDetails === false) {
478                throw new Exception(
479                    sprintf('Sqlite schema manager requires to modify foreign keys table definition "%s".', $table)
480                );
481            }
482
483            $table = $tableDetails;
484        }
485
486        $tableDiff            = new TableDiff($table->getName());
487        $tableDiff->fromTable = $table;
488
489        return $tableDiff;
490    }
491
492    private function parseColumnCollationFromSQL(string $column, string $sql): ?string
493    {
494        $pattern = '{(?:\W' . preg_quote($column) . '\W|\W'
495            . preg_quote($this->_platform->quoteSingleIdentifier($column))
496            . '\W)[^,(]+(?:\([^()]+\)[^,]*)?(?:(?:DEFAULT|CHECK)\s*(?:\(.*?\))?[^,]*)*COLLATE\s+["\']?([^\s,"\')]+)}is';
497
498        if (preg_match($pattern, $sql, $match) !== 1) {
499            return null;
500        }
501
502        return $match[1];
503    }
504
505    private function parseTableCommentFromSQL(string $table, string $sql): ?string
506    {
507        $pattern = '/\s* # Allow whitespace characters at start of line
508CREATE\sTABLE # Match "CREATE TABLE"
509(?:\W"' . preg_quote($this->_platform->quoteSingleIdentifier($table), '/') . '"\W|\W' . preg_quote($table, '/')
510            . '\W) # Match table name (quoted and unquoted)
511( # Start capture
512   (?:\s*--[^\n]*\n?)+ # Capture anything that starts with whitespaces followed by -- until the end of the line(s)
513)/ix';
514
515        if (preg_match($pattern, $sql, $match) !== 1) {
516            return null;
517        }
518
519        $comment = preg_replace('{^\s*--}m', '', rtrim($match[1], "\n"));
520
521        return $comment === '' ? null : $comment;
522    }
523
524    private function parseColumnCommentFromSQL(string $column, string $sql): ?string
525    {
526        $pattern = '{[\s(,](?:\W' . preg_quote($this->_platform->quoteSingleIdentifier($column))
527            . '\W|\W' . preg_quote($column) . '\W)(?:\([^)]*?\)|[^,(])*?,?((?:(?!\n))(?:\s*--[^\n]*\n?)+)}i';
528
529        if (preg_match($pattern, $sql, $match) !== 1) {
530            return null;
531        }
532
533        $comment = preg_replace('{^\s*--}m', '', rtrim($match[1], "\n"));
534
535        return $comment === '' ? null : $comment;
536    }
537
538    private function getCreateTableSQL(string $table): ?string
539    {
540        return $this->_conn->fetchColumn(
541            <<<'SQL'
542SELECT sql
543  FROM (
544      SELECT *
545        FROM sqlite_master
546   UNION ALL
547      SELECT *
548        FROM sqlite_temp_master
549  )
550WHERE type = 'table'
551AND name = ?
552SQL
553            ,
554            [$table]
555        ) ?: null;
556    }
557
558    /**
559     * @param string $name
560     */
561    public function listTableDetails($name): Table
562    {
563        $table = parent::listTableDetails($name);
564
565        $tableCreateSql = $this->getCreateTableSQL($name) ?? '';
566
567        $comment = $this->parseTableCommentFromSQL($name, $tableCreateSql);
568
569        if ($comment !== null) {
570            $table->addOption('comment', $comment);
571        }
572
573        return $table;
574    }
575}
576