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