SqliteSchemaManagerTest.php

<?php

namespace Doctrine\DBAL\Tests\Functional\Schema;

use Doctrine\DBAL\Exception;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Platforms\SqlitePlatform;
use Doctrine\DBAL\Schema\Column;
use Doctrine\DBAL\Schema\ForeignKeyConstraint;
use Doctrine\DBAL\Schema\SqliteSchemaManager;
use Doctrine\DBAL\Schema\Table;
use Doctrine\DBAL\Schema\TableDiff;
use Doctrine\DBAL\Types\BlobType;
use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Types\Types;

use function array_keys;
use function array_map;
use function array_shift;
use function assert;
use function dirname;

class SqliteSchemaManagerTest extends SchemaManagerFunctionalTestCase
{
    protected function supportsPlatform(AbstractPlatform $platform): bool
    {
        return $platform instanceof SqlitePlatform;
    }

    /**
     * SQLITE does not support databases.
     */
    public function testListDatabases(): void
    {
        $this->expectException(Exception::class);

        $this->schemaManager->listDatabases();
    }

    public function testCreateAndDropDatabase(): void
    {
        $path = dirname(__FILE__) . '/test_create_and_drop_sqlite_database.sqlite';

        $this->schemaManager->createDatabase($path);
        self::assertFileExists($path);
        $this->schemaManager->dropDatabase($path);
        self::assertFileDoesNotExist($path);
    }

    public function createListTableColumns(): Table
    {
        $table = parent::createListTableColumns();
        $table->getColumn('id')->setAutoincrement(true);

        return $table;
    }

    public function testListForeignKeysFromExistingDatabase(): void
    {
        $this->connection->executeStatement(<<<'EOS'
CREATE TABLE user (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    page INTEGER CONSTRAINT FK_1 REFERENCES page (key) DEFERRABLE INITIALLY DEFERRED,
    parent INTEGER REFERENCES user(id) ON DELETE CASCADE,
    log INTEGER,
    CONSTRAINT FK_3 FOREIGN KEY (log) REFERENCES log ON UPDATE SET NULL NOT DEFERRABLE
)
EOS);

        $expected = [
            new ForeignKeyConstraint(
                ['page'],
                'page',
                ['key'],
                'FK_1',
                ['onUpdate' => 'NO ACTION', 'onDelete' => 'NO ACTION', 'deferrable' => true, 'deferred' => true],
            ),
            new ForeignKeyConstraint(
                ['parent'],
                'user',
                ['id'],
                null,
                ['onUpdate' => 'NO ACTION', 'onDelete' => 'CASCADE', 'deferrable' => false, 'deferred' => false],
            ),
            new ForeignKeyConstraint(
                ['log'],
                'log',
                [],
                'FK_3',
                ['onUpdate' => 'SET NULL', 'onDelete' => 'NO ACTION', 'deferrable' => false, 'deferred' => false],
            ),
        ];

        self::assertEquals($expected, $this->schemaManager->listTableForeignKeys('user'));
    }

    public function testColumnCollation(): void
    {
        $table = new Table('test_collation');
        $table->addColumn('id', Types::INTEGER);
        $table->addColumn('text', Types::TEXT);
        $table->addColumn('foo', Types::TEXT)->setPlatformOption('collation', 'BINARY');
        $table->addColumn('bar', Types::TEXT)->setPlatformOption('collation', 'NOCASE');
        $this->dropAndCreateTable($table);

        $columns = $this->schemaManager->listTableColumns('test_collation');

        self::assertArrayNotHasKey('collation', $columns['id']->getPlatformOptions());
        self::assertEquals('BINARY', $columns['text']->getPlatformOption('collation'));
        self::assertEquals('BINARY', $columns['foo']->getPlatformOption('collation'));
        self::assertEquals('NOCASE', $columns['bar']->getPlatformOption('collation'));
    }

    /**
     * SQLite stores BINARY columns as BLOB
     */
    protected function assertBinaryColumnIsValid(Table $table, string $columnName, int $expectedLength): void
    {
        self::assertInstanceOf(BlobType::class, $table->getColumn($columnName)->getType());
    }

    /**
     * SQLite stores VARBINARY columns as BLOB
     */
    protected function assertVarBinaryColumnIsValid(Table $table, string $columnName, int $expectedLength): void
    {
        self::assertInstanceOf(BlobType::class, $table->getColumn($columnName)->getType());
    }

    public function testListTableColumnsWithWhitespacesInTypeDeclarations(): void
    {
        $sql = <<<'SQL'
CREATE TABLE dbal_1779 (
    foo VARCHAR (64) ,
    bar TEXT (100)
)
SQL;

        $this->connection->executeStatement($sql);

        $columns = $this->schemaManager->listTableColumns('dbal_1779');

        self::assertCount(2, $columns);

        self::assertArrayHasKey('foo', $columns);
        self::assertArrayHasKey('bar', $columns);

        self::assertSame(Type::getType(Types::STRING), $columns['foo']->getType());
        self::assertSame(Type::getType(Types::TEXT), $columns['bar']->getType());

        self::assertSame(64, $columns['foo']->getLength());
        self::assertSame(100, $columns['bar']->getLength());
    }

    public function testPrimaryKeyNoAutoIncrement(): void
    {
        $table = new Table('test_pk_auto_increment');
        $table->addColumn('id', Types::INTEGER);
        $table->addColumn('text', Types::TEXT);
        $table->setPrimaryKey(['id']);
        $this->dropAndCreateTable($table);

        $this->connection->insert('test_pk_auto_increment', ['text' => '1']);

        $this->connection->executeStatement('DELETE FROM test_pk_auto_increment');

        $this->connection->insert('test_pk_auto_increment', ['text' => '2']);

        $lastUsedIdAfterDelete = (int) $this->connection->fetchOne(
            'SELECT id FROM test_pk_auto_increment WHERE text = "2"',
        );

        // with an empty table, non autoincrement rowid is always 1
        self::assertEquals(1, $lastUsedIdAfterDelete);
    }

    public function testOnlyOwnCommentIsParsed(): void
    {
        $table = new Table('own_column_comment');
        $table->addColumn('col1', Types::STRING, ['length' => 16]);
        $table->addColumn('col2', Types::STRING, ['length' => 16, 'comment' => 'Column #2']);
        $table->addColumn('col3', Types::STRING, ['length' => 16]);

        $sm = $this->connection->getSchemaManager();
        $sm->createTable($table);

        self::assertNull($sm->introspectTable('own_column_comment')
            ->getColumn('col1')
            ->getComment());
    }

    public function testNonSimpleAlterTableCreatedFromDDL(): void
    {
        $this->dropTableIfExists('nodes');

        $ddl = <<<'DDL'
        CREATE TABLE nodes (
            id        INTEGER NOT NULL,
            parent_id INTEGER,
            name      TEXT,
            PRIMARY KEY (id),
            FOREIGN KEY (parent_id) REFERENCES nodes (id)
        )
        DDL;

        $this->connection->executeStatement($ddl);

        $schemaManager = $this->connection->createSchemaManager();

        $table1 = $schemaManager->introspectTable('nodes');
        $table2 = clone $table1;
        $table2->addIndex(['name'], 'idx_name');

        $comparator = $schemaManager->createComparator();
        $diff       = $comparator->diffTable($table1, $table2);
        self::assertNotFalse($diff);

        $schemaManager->alterTable($diff);

        $table = $schemaManager->introspectTable('nodes');
        $index = $table->getIndex('idx_name');
        self::assertSame(['name'], $index->getColumns());
    }

    public function testAlterTableWithSchema(): void
    {
        $databasePlatform = $this->connection->getDatabasePlatform();
        assert($databasePlatform instanceof SqlitePlatform);
        $databasePlatform->disableSchemaEmulation();

        $this->dropTableIfExists('t');

        $table = new Table('main.t');
        $table->addColumn('a', Types::INTEGER);
        $this->schemaManager->createTable($table);

        self::assertSame(['a'], array_keys($this->schemaManager->listTableColumns('t')));

        $tableDiff                      = new TableDiff('t');
        $tableDiff->fromTable           = $table;
        $tableDiff->renamedColumns['a'] = new Column('b', Type::getType(Types::INTEGER));
        $this->schemaManager->alterTable($tableDiff);

        self::assertSame(['b'], array_keys($this->schemaManager->listTableColumns('t')));
    }

    public function testIntrospectMultipleAnonymousForeignKeyConstraints(): void
    {
        $this->dropTableIfExists('album');
        $this->dropTableIfExists('song');

        $ddl = <<<'DDL'
        CREATE TABLE artist(
          id INTEGER,
          name TEXT,
          PRIMARY KEY(id)
        );

        CREATE TABLE album(
          id INTEGER,
          name TEXT,
          PRIMARY KEY(id)
        );

        CREATE TABLE song(
          id     INTEGER,
          album_id INTEGER,
          artist_id INTEGER,
          FOREIGN KEY(album_id) REFERENCES album(id),
          FOREIGN KEY(artist_id) REFERENCES artist(id)
        );
        DDL;

        $this->connection->executeStatement($ddl);

        $schemaManager = $this->connection->createSchemaManager();

        $song        = $schemaManager->introspectTable('song');
        $foreignKeys = $song->getForeignKeys();
        self::assertCount(2, $foreignKeys);

        $foreignKey1 = array_shift($foreignKeys);
        self::assertEmpty($foreignKey1->getName());

        self::assertSame(['album_id'], $foreignKey1->getLocalColumns());
        self::assertSame(['id'], $foreignKey1->getForeignColumns());

        $foreignKey2 = array_shift($foreignKeys);
        self::assertEmpty($foreignKey2->getName());

        self::assertSame(['artist_id'], $foreignKey2->getLocalColumns());
        self::assertSame(['id'], $foreignKey2->getForeignColumns());
    }

    public function testNoWhitespaceInForeignKeyReference(): void
    {
        $this->dropTableIfExists('notes');
        $this->dropTableIfExists('users');

        $ddl = <<<'DDL'
        CREATE TABLE "users" (
            "id" INTEGER
        );

        CREATE TABLE "notes" (
            "id" INTEGER,
            "created_by" INTEGER,
            FOREIGN KEY("created_by") REFERENCES "users"("id"));
        DDL;

        $this->connection->executeStatement($ddl);
        $notes = $this->schemaManager->listTableDetails('notes');

        $foreignKeys = $notes->getForeignKeys();
        self::assertCount(1, $foreignKeys);

        $foreignKey = array_shift($foreignKeys);
        self::assertSame(['created_by'], $foreignKey->getLocalColumns());
        self::assertSame('users', $foreignKey->getForeignTableName());
        self::assertSame(['id'], $foreignKey->getForeignColumns());
    }

    public function testShorthandInForeignKeyReference(): void
    {
        $this->dropTableIfExists('artist');
        $this->dropTableIfExists('track');

        $ddl = <<<'DDL'
        CREATE TABLE artist(
            artistid INTEGER PRIMARY KEY,
            artistname TEXT
        );

        CREATE TABLE track(
            trackid INTEGER,
            trackname TEXT,
            trackartist INTEGER REFERENCES artist
        );
        DDL;

        $this->connection->executeStatement($ddl);

        $schemaManager = $this->connection->createSchemaManager();

        $song        = $schemaManager->introspectTable('track');
        $foreignKeys = $song->getForeignKeys();
        self::assertCount(1, $foreignKeys);

        $foreignKey1 = array_shift($foreignKeys);
        self::assertEmpty($foreignKey1->getName());

        self::assertSame(['trackartist'], $foreignKey1->getLocalColumns());
        self::assertSame(['artistid'], $foreignKey1->getForeignColumns());
    }

    public function testShorthandInForeignKeyReferenceWithMultipleColumns(): void
    {
        $this->dropTableIfExists('artist');
        $this->dropTableIfExists('track');

        $ddl = <<<'DDL'
        CREATE TABLE artist(
            artistid INTEGER,
            isrc TEXT,
            artistname TEXT,
            PRIMARY KEY (artistid, isrc)
        );

        CREATE TABLE track(
            trackid INTEGER,
            trackname TEXT,
            trackartist INTEGER REFERENCES artist
        );
        DDL;

        $this->connection->executeStatement($ddl);

        $schemaManager = $this->connection->createSchemaManager();

        $track       = $schemaManager->introspectTable('track');
        $foreignKeys = $track->getForeignKeys();
        self::assertCount(1, $foreignKeys);

        $foreignKey1 = array_shift($foreignKeys);
        self::assertEmpty($foreignKey1->getName());

        self::assertSame(['trackartist'], $foreignKey1->getLocalColumns());
        self::assertSame(['artistid', 'isrc'], $foreignKey1->getForeignColumns());

        $createTableTrackSql = $this->connection->getDatabasePlatform()->getCreateTableSQL($track);

        self::assertSame(
            [
                'CREATE TABLE track (trackartist INTEGER DEFAULT NULL, trackid INTEGER DEFAULT NULL, trackname CLOB'
                . ' DEFAULT NULL COLLATE "BINARY", FOREIGN KEY (trackartist) REFERENCES artist (artistid, isrc) ON'
                . ' UPDATE NO ACTION ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE)',
                'CREATE INDEX IDX_D6E3F8A6FB96D8BC ON track (trackartist)',
            ],
            $createTableTrackSql,
        );
    }

    public function testListTableNoSchemaEmulation(): void
    {
        $databasePlatform = $this->connection->getDatabasePlatform();
        assert($databasePlatform instanceof SqlitePlatform);
        $databasePlatform->disableSchemaEmulation();

        $this->dropTableIfExists('`list_table_no_schema_emulation.test`');

        $this->connection->executeStatement(<<<'DDL'
            CREATE TABLE `list_table_no_schema_emulation.test` (
                id INTEGER,
                parent_id INTEGER,
                PRIMARY KEY (id),
                FOREIGN KEY (parent_id) REFERENCES `list_table_no_schema_emulation.test` (id)
            );
            DDL);

        $this->connection->executeStatement(<<<'DDL'
            CREATE INDEX i ON `list_table_no_schema_emulation.test` (parent_id);
            DDL);

        $customSqliteSchemaManager = new class ($this->connection, $databasePlatform) extends SqliteSchemaManager {
            /** @return list<array<string, mixed>> */
            public function selectTableColumnsWithSchema(): array
            {
                return $this->selectTableColumns('main', 'list_table_no_schema_emulation.test')
                    ->fetchAllAssociative();
            }

            /** @return list<array<string, mixed>> */
            public function selectIndexColumnsWithSchema(): array
            {
                return $this->selectIndexColumns('main', 'list_table_no_schema_emulation.test')
                    ->fetchAllAssociative();
            }

            /** @return list<array<string, mixed>> */
            public function selectForeignKeyColumnsWithSchema(): array
            {
                return $this->selectForeignKeyColumns('main', 'list_table_no_schema_emulation.test')
                    ->fetchAllAssociative();
            }
        };

        self::assertSame(
            [
                ['list_table_no_schema_emulation.test', 'id'],
                ['list_table_no_schema_emulation.test', 'parent_id'],
            ],
            array_map(
                static fn (array $row) => [$row['table_name'], $row['name']],
                $customSqliteSchemaManager->selectTableColumnsWithSchema(),
            ),
        );

        self::assertSame(
            [
                ['list_table_no_schema_emulation.test', 'i'],
            ],
            array_map(
                static fn (array $row) => [$row['table_name'], $row['name']],
                $customSqliteSchemaManager->selectIndexColumnsWithSchema(),
            ),
        );

        self::assertSame(
            [
                ['list_table_no_schema_emulation.test', 'parent_id', 'id'],
            ],
            array_map(
                static fn (array $row) => [$row['table_name'], $row['from'], $row['to']],
                $customSqliteSchemaManager->selectForeignKeyColumnsWithSchema(),
            ),
        );
    }

    /**
     * This test duplicates {@see parent::testCommentInTable()} with the only difference that the name of the table
     * being created is quoted. It is only meant to cover the logic of parsing the SQLite CREATE TABLE statement
     * when the table name is quoted.
     *
     * Running the same test for all platforms, on the one hand, won't produce additional coverage, and on the other,
     * is not feasible due to the differences in case sensitivity depending on whether the name is quoted.
     *
     * Once all identifiers are quoted by default, this test can be removed.
     */
    public function testCommentInQuotedTable(): void
    {
        $table = new Table('"table_with_comment"');
        $table->addColumn('id', Types::INTEGER);
        $table->setComment('This is a comment');
        $this->dropAndCreateTable($table);

        $table = $this->schemaManager->introspectTable('table_with_comment');
        self::assertSame('This is a comment', $table->getComment());
    }
}