SqliteSchemaManager.php 16.3 KB
Newer Older
romanb's avatar
romanb committed
1 2
<?php

3
namespace Doctrine\DBAL\Schema;
romanb's avatar
romanb committed
4

5
use Doctrine\DBAL\DBALException;
6
use Doctrine\DBAL\DriverManager;
7
use Doctrine\DBAL\FetchMode;
8 9
use Doctrine\DBAL\Types\StringType;
use Doctrine\DBAL\Types\TextType;
10
use Doctrine\DBAL\Types\Type;
11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
use const CASE_LOWER;
use function array_change_key_case;
use function array_map;
use function array_reverse;
use function array_values;
use function explode;
use function file_exists;
use function preg_match;
use function preg_match_all;
use function preg_quote;
use function preg_replace;
use function rtrim;
use function sprintf;
use function str_replace;
use function strpos;
use function strtolower;
use function trim;
use function unlink;
use function usort;
30

romanb's avatar
romanb committed
31
/**
Benjamin Morel's avatar
Benjamin Morel committed
32
 * Sqlite SchemaManager.
romanb's avatar
romanb committed
33
 */
34
class SqliteSchemaManager extends AbstractSchemaManager
35
{
romanb's avatar
romanb committed
36 37 38 39
    /**
     * {@inheritdoc}
     */
    public function dropDatabase($database)
40
    {
41 42
        if (! file_exists($database)) {
            return;
43
        }
44 45

        unlink($database);
46 47
    }

romanb's avatar
romanb committed
48 49 50 51
    /**
     * {@inheritdoc}
     */
    public function createDatabase($database)
52
    {
53 54
        $params  = $this->_conn->getParams();
        $driver  = $params['driver'];
55
        $options = [
jwage's avatar
jwage committed
56
            'driver' => $driver,
57
            'path' => $database,
58
        ];
59
        $conn    = DriverManager::getConnection($options);
jwage's avatar
jwage committed
60 61
        $conn->connect();
        $conn->close();
62 63
    }

64 65 66 67 68
    /**
     * {@inheritdoc}
     */
    public function renameTable($name, $newName)
    {
69
        $tableDiff            = new TableDiff($name);
70
        $tableDiff->fromTable = $this->listTableDetails($name);
71
        $tableDiff->newName   = $newName;
72 73 74 75 76 77 78 79
        $this->alterTable($tableDiff);
    }

    /**
     * {@inheritdoc}
     */
    public function createForeignKey(ForeignKeyConstraint $foreignKey, $table)
    {
Sergei Morozov's avatar
Sergei Morozov committed
80
        $tableDiff                     = $this->getTableDiffForAlterForeignKey($table);
81 82 83 84 85 86 87 88 89 90
        $tableDiff->addedForeignKeys[] = $foreignKey;

        $this->alterTable($tableDiff);
    }

    /**
     * {@inheritdoc}
     */
    public function dropAndCreateForeignKey(ForeignKeyConstraint $foreignKey, $table)
    {
Sergei Morozov's avatar
Sergei Morozov committed
91
        $tableDiff                       = $this->getTableDiffForAlterForeignKey($table);
92 93 94 95 96 97 98 99 100 101
        $tableDiff->changedForeignKeys[] = $foreignKey;

        $this->alterTable($tableDiff);
    }

    /**
     * {@inheritdoc}
     */
    public function dropForeignKey($foreignKey, $table)
    {
Sergei Morozov's avatar
Sergei Morozov committed
102
        $tableDiff                       = $this->getTableDiffForAlterForeignKey($table);
103 104 105 106 107 108 109 110 111 112
        $tableDiff->removedForeignKeys[] = $foreignKey;

        $this->alterTable($tableDiff);
    }

    /**
     * {@inheritdoc}
     */
    public function listTableForeignKeys($table, $database = null)
    {
113
        if ($database === null) {
114 115
            $database = $this->_conn->getDatabase();
        }
116
        $sql              = $this->_platform->getListTableForeignKeysSQL($table, $database);
117 118
        $tableForeignKeys = $this->_conn->fetchAll($sql);

119
        if (! empty($tableForeignKeys)) {
120
            $createSql = $this->getCreateTableSQL($table);
121

122
            if (preg_match_all(
123
                '#
124 125 126 127 128 129 130 131
                    (?:CONSTRAINT\s+([^\s]+)\s+)?
                    (?:FOREIGN\s+KEY[^\)]+\)\s*)?
                    REFERENCES\s+[^\s]+\s+(?:\([^\)]+\))?
                    (?:
                        [^,]*?
                        (NOT\s+DEFERRABLE|DEFERRABLE)
                        (?:\s+INITIALLY\s+(DEFERRED|IMMEDIATE))?
                    )?#isx',
132 133
                $createSql,
                $match
134
            ) > 0) {
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 144
                $id                                        = $value['id'];
                $tableForeignKeys[$key]['constraint_name'] = isset($names[$id]) && $names[$id] !== '' ? $names[$id] : $id;
145 146
                $tableForeignKeys[$key]['deferrable']      = isset($deferrable[$id]) && strtolower($deferrable[$id]) === 'deferrable';
                $tableForeignKeys[$key]['deferred']        = isset($deferred[$id]) && strtolower($deferred[$id]) === 'deferred';
147 148 149 150 151 152
            }
        }

        return $this->_getPortableTableForeignKeysList($tableForeignKeys);
    }

Benjamin Morel's avatar
Benjamin Morel committed
153 154 155
    /**
     * {@inheritdoc}
     */
156 157 158 159 160
    protected function _getPortableTableDefinition($table)
    {
        return $table['name'];
    }

161
    /**
Benjamin Morel's avatar
Benjamin Morel committed
162 163
     * {@inheritdoc}
     *
164 165
     * @link http://ezcomponents.org/docs/api/trunk/DatabaseSchema/ezcDbSchemaPgsqlReader.html
     */
166
    protected function _getPortableTableIndexesList($tableIndexes, $tableName = null)
167
    {
168
        $indexBuffer = [];
169 170

        // fetch primary
171 172 173 174
        $stmt       = $this->_conn->executeQuery(sprintf(
            'PRAGMA TABLE_INFO (%s)',
            $this->_conn->quote($tableName)
        ));
175
        $indexArray = $stmt->fetchAll(FetchMode::ASSOCIATIVE);
176

177 178
        usort($indexArray, static function ($a, $b) {
            if ($a['pk'] === $b['pk']) {
179 180
                return $a['cid'] - $b['cid'];
            }
181

182 183
            return $a['pk'] - $b['pk'];
        });
Steve Müller's avatar
Steve Müller committed
184
        foreach ($indexArray as $indexColumnRow) {
185 186
            if ($indexColumnRow['pk'] === '0') {
                continue;
187
            }
188 189 190 191 192 193 194

            $indexBuffer[] = [
                'key_name' => 'primary',
                'primary' => true,
                'non_unique' => false,
                'column_name' => $indexColumnRow['name'],
            ];
195 196 197
        }

        // fetch regular indexes
Steve Müller's avatar
Steve Müller committed
198
        foreach ($tableIndexes as $tableIndex) {
199
            // Ignore indexes with reserved names, e.g. autoindexes
200 201 202 203 204 205 206 207
            if (strpos($tableIndex['name'], 'sqlite_') === 0) {
                continue;
            }

            $keyName           = $tableIndex['name'];
            $idx               = [];
            $idx['key_name']   = $keyName;
            $idx['primary']    = false;
208
            $idx['non_unique'] = ! $tableIndex['unique'];
209

210 211 212 213 214
                $stmt       = $this->_conn->executeQuery(sprintf(
                    'PRAGMA INDEX_INFO (%s)',
                    $this->_conn->quote($keyName)
                ));
                $indexArray = $stmt->fetchAll(FetchMode::ASSOCIATIVE);
215 216 217 218

            foreach ($indexArray as $indexColumnRow) {
                $idx['column_name'] = $indexColumnRow['name'];
                $indexBuffer[]      = $idx;
219 220 221 222 223 224
            }
        }

        return parent::_getPortableTableIndexesList($indexBuffer, $tableName);
    }

Benjamin Morel's avatar
Benjamin Morel committed
225
    /**
226
     * @deprecated
227
     *
228 229
     * @param array<string, mixed> $tableIndex
     *
230
     * @return array<string, bool|string>
Benjamin Morel's avatar
Benjamin Morel committed
231
     */
232 233
    protected function _getPortableTableIndexDefinition($tableIndex)
    {
234
        return [
235
            'name' => $tableIndex['name'],
236
            'unique' => (bool) $tableIndex['unique'],
237
        ];
238 239
    }

Benjamin Morel's avatar
Benjamin Morel committed
240 241 242
    /**
     * {@inheritdoc}
     */
243 244 245
    protected function _getPortableTableColumnList($table, $database, $tableColumns)
    {
        $list = parent::_getPortableTableColumnList($table, $database, $tableColumns);
246 247

        // find column with autoincrement
248
        $autoincrementColumn = null;
249
        $autoincrementCount  = 0;
250

251
        foreach ($tableColumns as $tableColumn) {
252 253 254 255 256 257 258
            if ($tableColumn['pk'] === '0') {
                continue;
            }

            $autoincrementCount++;
            if ($autoincrementColumn !== null || strtolower($tableColumn['type']) !== 'integer') {
                continue;
259
            }
260 261

            $autoincrementColumn = $tableColumn['name'];
262 263
        }

264
        if ($autoincrementCount === 1 && $autoincrementColumn !== null) {
265
            foreach ($list as $column) {
266 267
                if ($autoincrementColumn !== $column->getName()) {
                    continue;
268
                }
269 270

                $column->setAutoincrement(true);
271 272 273
            }
        }

274
        // inspect column collation and comments
275
        $createSql = $this->getCreateTableSQL($table);
276 277 278 279 280

        foreach ($list as $columnName => $column) {
            $type = $column->getType();

            if ($type instanceof StringType || $type instanceof TextType) {
281 282 283 284
                $column->setPlatformOption(
                    'collation',
                    $this->parseColumnCollationFromSQL($columnName, $createSql) ?? 'BINARY'
                );
285
            }
286 287

            $comment = $this->parseColumnCommentFromSQL($columnName, $createSql);
Steve Müller's avatar
Steve Müller committed
288

289 290 291
            if ($comment === null) {
                continue;
            }
Steve Müller's avatar
Steve Müller committed
292

Sergei Morozov's avatar
Sergei Morozov committed
293
            $type = $this->extractDoctrineTypeFromComment($comment, '');
Steve Müller's avatar
Steve Müller committed
294

Sergei Morozov's avatar
Sergei Morozov committed
295
            if ($type !== '') {
296
                $column->setType(Type::getType($type));
297

298
                $comment = $this->removeDoctrineTypeFromComment($comment, $type);
299
            }
300 301

            $column->setComment($comment);
302 303
        }

304 305 306
        return $list;
    }

Benjamin Morel's avatar
Benjamin Morel committed
307 308 309
    /**
     * {@inheritdoc}
     */
310 311
    protected function _getPortableTableColumnDefinition($tableColumn)
    {
312
        $parts               = explode('(', $tableColumn['type']);
313
        $tableColumn['type'] = trim($parts[0]);
314
        if (isset($parts[1])) {
315
            $length                = trim($parts[1], ')');
316 317 318
            $tableColumn['length'] = $length;
        }

Gabriel Caruso's avatar
Gabriel Caruso committed
319 320
        $dbType   = strtolower($tableColumn['type']);
        $length   = $tableColumn['length'] ?? null;
321 322 323
        $unsigned = false;

        if (strpos($dbType, ' unsigned') !== false) {
324
            $dbType   = str_replace(' unsigned', '', $dbType);
325 326 327
            $unsigned = true;
        }

328 329
        $fixed   = false;
        $type    = $this->_platform->getDoctrineTypeMapping($dbType);
330
        $default = $tableColumn['dflt_value'];
331
        if ($default === 'NULL') {
332 333
            $default = null;
        }
334

335
        if ($default !== null) {
336
            // SQLite returns the default value as a literal expression, so we need to parse it
337
            if (preg_match('/^\'(.*)\'$/s', $default, $matches) === 1) {
338 339
                $default = str_replace("''", "'", $matches[1]);
            }
340
        }
341

342 343
        $notnull = (bool) $tableColumn['notnull'];

344
        if (! isset($tableColumn['name'])) {
345 346 347
            $tableColumn['name'] = '';
        }

348
        $precision = null;
349
        $scale     = null;
350

351 352
        switch ($dbType) {
            case 'char':
353
                $fixed = true;
354 355 356 357 358 359
                break;
            case 'float':
            case 'double':
            case 'real':
            case 'decimal':
            case 'numeric':
360
                if (isset($tableColumn['length'])) {
361
                    if (strpos($tableColumn['length'], ',') === false) {
362
                        $tableColumn['length'] .= ',0';
Steve Müller's avatar
Steve Müller committed
363
                    }
364
                    [$precision, $scale] = array_map('trim', explode(',', $tableColumn['length']));
365
                }
366 367 368 369
                $length = null;
                break;
        }

370
        $options = [
371 372 373 374 375 376 377
            'length'   => $length,
            'unsigned' => (bool) $unsigned,
            'fixed'    => $fixed,
            'notnull'  => $notnull,
            'default'  => $default,
            'precision' => $precision,
            'scale'     => $scale,
378
            'autoincrement' => false,
379
        ];
380

381
        return new Column($tableColumn['name'], Type::getType($type), $options);
382
    }
383

Benjamin Morel's avatar
Benjamin Morel committed
384 385 386
    /**
     * {@inheritdoc}
     */
387 388 389 390
    protected function _getPortableViewDefinition($view)
    {
        return new View($view['name'], $view['sql']);
    }
391

Benjamin Morel's avatar
Benjamin Morel committed
392 393 394
    /**
     * {@inheritdoc}
     */
395 396
    protected function _getPortableTableForeignKeysList($tableForeignKeys)
    {
397
        $list = [];
Benjamin Morel's avatar
Benjamin Morel committed
398
        foreach ($tableForeignKeys as $value) {
399
            $value = array_change_key_case($value, CASE_LOWER);
400 401 402
            $name  = $value['constraint_name'];
            if (! isset($list[$name])) {
                if (! isset($value['on_delete']) || $value['on_delete'] === 'RESTRICT') {
403 404
                    $value['on_delete'] = null;
                }
405
                if (! isset($value['on_update']) || $value['on_update'] === 'RESTRICT') {
406 407 408
                    $value['on_update'] = null;
                }

409
                $list[$name] = [
410
                    'name' => $name,
411 412
                    'local' => [],
                    'foreign' => [],
413 414 415
                    'foreignTable' => $value['table'],
                    'onDelete' => $value['on_delete'],
                    'onUpdate' => $value['on_update'],
416 417
                    'deferrable' => $value['deferrable'],
                    'deferred'=> $value['deferred'],
418
                ];
419
            }
420
            $list[$name]['local'][]   = $value['from'];
421
            $list[$name]['foreign'][] = $value['to'];
422 423
        }

424
        $result = [];
Steve Müller's avatar
Steve Müller committed
425
        foreach ($list as $constraint) {
426
            $result[] = new ForeignKeyConstraint(
427 428 429 430
                array_values($constraint['local']),
                $constraint['foreignTable'],
                array_values($constraint['foreign']),
                $constraint['name'],
431
                [
432 433
                    'onDelete' => $constraint['onDelete'],
                    'onUpdate' => $constraint['onUpdate'],
434 435
                    'deferrable' => $constraint['deferrable'],
                    'deferred'=> $constraint['deferred'],
436
                ]
437 438 439 440 441 442
            );
        }

        return $result;
    }

Benjamin Morel's avatar
Benjamin Morel committed
443
    /**
444
     * @param Table|string $table
Benjamin Morel's avatar
Benjamin Morel committed
445
     *
446
     * @return TableDiff
Benjamin Morel's avatar
Benjamin Morel committed
447
     *
448
     * @throws DBALException
Benjamin Morel's avatar
Benjamin Morel committed
449
     */
Sergei Morozov's avatar
Sergei Morozov committed
450
    private function getTableDiffForAlterForeignKey($table)
451
    {
452
        if (! $table instanceof Table) {
453
            $tableDetails = $this->tryMethod('listTableDetails', $table);
Sergei Morozov's avatar
Sergei Morozov committed
454 455

            if ($tableDetails === false) {
Benjamin Morel's avatar
Benjamin Morel committed
456
                throw new DBALException(sprintf('Sqlite schema manager requires to modify foreign keys table definition "%s".', $table));
457 458 459 460 461
            }

            $table = $tableDetails;
        }

462
        $tableDiff            = new TableDiff($table->getName());
463 464 465 466
        $tableDiff->fromTable = $table;

        return $tableDiff;
    }
467

468
    private function parseColumnCollationFromSQL(string $column, string $sql) : ?string
469
    {
470
        $pattern = '{(?:\W' . preg_quote($column) . '\W|\W' . preg_quote($this->_platform->quoteSingleIdentifier($column))
471
            . '\W)[^,(]+(?:\([^()]+\)[^,]*)?(?:(?:DEFAULT|CHECK)\s*(?:\(.*?\))?[^,]*)*COLLATE\s+["\']?([^\s,"\')]+)}is';
472

473 474
        if (preg_match($pattern, $sql, $match) !== 1) {
            return null;
475 476
        }

477
        return $match[1];
478
    }
479

480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498
    private function parseTableCommentFromSQL(string $table, string $sql) : ?string
    {
        $pattern = '/\s* # Allow whitespace characters at start of line
CREATE\sTABLE # Match "CREATE TABLE"
(?:\W"' . preg_quote($this->_platform->quoteSingleIdentifier($table), '/') . '"\W|\W' . preg_quote($table, '/')
            . '\W) # Match table name (quoted and unquoted)
( # Start capture
   (?:\s*--[^\n]*\n?)+ # Capture anything that starts with whitespaces followed by -- until the end of the line(s)
)/ix';

        if (preg_match($pattern, $sql, $match) !== 1) {
            return null;
        }

        $comment = preg_replace('{^\s*--}m', '', rtrim($match[1], "\n"));

        return $comment === '' ? null : $comment;
    }

499
    private function parseColumnCommentFromSQL(string $column, string $sql) : ?string
500
    {
501
        $pattern = '{[\s(,](?:\W' . preg_quote($this->_platform->quoteSingleIdentifier($column)) . '\W|\W' . preg_quote($column)
502
            . '\W)(?:\([^)]*?\)|[^,(])*?,?((?:(?!\n))(?:\s*--[^\n]*\n?)+)}i';
503

504 505
        if (preg_match($pattern, $sql, $match) !== 1) {
            return null;
506 507
        }

508 509
        $comment = preg_replace('{^\s*--}m', '', rtrim($match[1], "\n"));

510
        return $comment === '' ? null : $comment;
511
    }
512

513
    private function getCreateTableSQL(string $table) : string
514
    {
515
        $sql = $this->_conn->fetchColumn(
516 517 518 519 520 521 522 523 524 525 526 527 528 529
            <<<'SQL'
SELECT sql
  FROM (
      SELECT *
        FROM sqlite_master
   UNION ALL
      SELECT *
        FROM sqlite_temp_master
  )
WHERE type = 'table'
AND name = ?
SQL
            ,
            [$table]
530 531 532 533 534 535 536
        );

        if ($sql !== false) {
            return $sql;
        }

        return '';
537
    }
538 539 540 541 542 543 544 545

    /**
     * @param string $tableName
     */
    public function listTableDetails($tableName) : Table
    {
        $table = parent::listTableDetails($tableName);

546
        $tableCreateSql = $this->getCreateTableSQL($tableName);
547 548 549 550 551 552 553 554 555

        $comment = $this->parseTableCommentFromSQL($tableName, $tableCreateSql);

        if ($comment !== null) {
            $table->addOption('comment', $comment);
        }

        return $table;
    }
556
}