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