SqliteSchemaManagerTest.php 9.71 KB
Newer Older
1 2
<?php

3
namespace Doctrine\DBAL\Tests\Functional\Schema;
4

5
use Doctrine\DBAL\DBALException;
Sergei Morozov's avatar
Sergei Morozov committed
6
use Doctrine\DBAL\Driver\Connection;
7
use Doctrine\DBAL\Schema;
Sergei Morozov's avatar
Sergei Morozov committed
8
use Doctrine\DBAL\Schema\Table;
Sergei Morozov's avatar
Sergei Morozov committed
9
use Doctrine\DBAL\Types\BlobType;
10
use Doctrine\DBAL\Types\Type;
11
use Doctrine\DBAL\Types\Types;
Sergei Morozov's avatar
Sergei Morozov committed
12
use SQLite3;
13

14 15 16 17
use function array_map;
use function dirname;
use function extension_loaded;
use function version_compare;
18

romanb's avatar
romanb committed
19
class SqliteSchemaManagerTest extends SchemaManagerFunctionalTestCase
20
{
romanb's avatar
romanb committed
21
    /**
22
     * SQLITE does not support databases.
romanb's avatar
romanb committed
23
     */
24
    public function testListDatabases(): void
25
    {
26 27
        $this->expectException(DBALException::class);

Sergei Morozov's avatar
Sergei Morozov committed
28
        $this->schemaManager->listDatabases();
29 30
    }

31
    public function testCreateAndDropDatabase(): void
32
    {
Sergei Morozov's avatar
Sergei Morozov committed
33
        $path = dirname(__FILE__) . '/test_create_and_drop_sqlite_database.sqlite';
34

Sergei Morozov's avatar
Sergei Morozov committed
35
        $this->schemaManager->createDatabase($path);
Gabriel Caruso's avatar
Gabriel Caruso committed
36
        self::assertFileExists($path);
Sergei Morozov's avatar
Sergei Morozov committed
37
        $this->schemaManager->dropDatabase($path);
38
        self::assertFileDoesNotExist($path);
jwage's avatar
jwage committed
39
    }
40

41 42 43
    /**
     * @group DBAL-1220
     */
44
    public function testDropsDatabaseWithActiveConnections(): void
45
    {
Sergei Morozov's avatar
Sergei Morozov committed
46
        $this->schemaManager->dropAndCreateDatabase('test_drop_database');
47

48
        self::assertFileExists('test_drop_database');
49

Sergei Morozov's avatar
Sergei Morozov committed
50
        $params           = $this->connection->getParams();
51 52
        $params['dbname'] = 'test_drop_database';

Sergei Morozov's avatar
Sergei Morozov committed
53
        $user     = $params['user'] ?? null;
54
        $password = $params['password'] ?? null;
55

Sergei Morozov's avatar
Sergei Morozov committed
56
        $connection = $this->connection->getDriver()->connect($params, $user, $password);
57

Sergei Morozov's avatar
Sergei Morozov committed
58
        self::assertInstanceOf(Connection::class, $connection);
59

Sergei Morozov's avatar
Sergei Morozov committed
60
        $this->schemaManager->dropDatabase('test_drop_database');
61

62
        self::assertFileDoesNotExist('test_drop_database');
63 64 65 66

        unset($connection);
    }

67
    public function testRenameTable(): void
68
    {
69
        $this->createTestTable('oldname');
Sergei Morozov's avatar
Sergei Morozov committed
70
        $this->schemaManager->renameTable('oldname', 'newname');
71

Sergei Morozov's avatar
Sergei Morozov committed
72
        $tables = $this->schemaManager->listTableNames();
73 74
        self::assertContains('newname', $tables);
        self::assertNotContains('oldname', $tables);
75
    }
76

77
    public function createListTableColumns(): Table
78
    {
79 80 81 82
        $table = parent::createListTableColumns();
        $table->getColumn('id')->setAutoincrement(true);

        return $table;
83
    }
84

85
    public function testListForeignKeysFromExistingDatabase(): void
86
    {
Sergei Morozov's avatar
Sergei Morozov committed
87
        $this->connection->exec(<<<EOS
88 89 90 91 92 93 94 95 96 97
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
        );

Sergei Morozov's avatar
Sergei Morozov committed
98 99 100 101
        $expected = [
            new Schema\ForeignKeyConstraint(
                ['log'],
                'log',
Grégoire Paris's avatar
Grégoire Paris committed
102
                [''],
Sergei Morozov's avatar
Sergei Morozov committed
103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120
                'FK_3',
                ['onUpdate' => 'SET NULL', 'onDelete' => 'NO ACTION', 'deferrable' => false, 'deferred' => false]
            ),
            new Schema\ForeignKeyConstraint(
                ['parent'],
                'user',
                ['id'],
                '1',
                ['onUpdate' => 'NO ACTION', 'onDelete' => 'CASCADE', 'deferrable' => false, 'deferred' => false]
            ),
            new Schema\ForeignKeyConstraint(
                ['page'],
                'page',
                ['key'],
                'FK_1',
                ['onUpdate' => 'NO ACTION', 'onDelete' => 'NO ACTION', 'deferrable' => true, 'deferred' => true]
            ),
        ];
121

Sergei Morozov's avatar
Sergei Morozov committed
122
        self::assertEquals($expected, $this->schemaManager->listTableForeignKeys('user'));
123
    }
Steve Müller's avatar
Steve Müller committed
124

125
    public function testColumnCollation(): void
126 127 128 129 130 131
    {
        $table = new Schema\Table('test_collation');
        $table->addColumn('id', 'integer');
        $table->addColumn('text', 'text');
        $table->addColumn('foo', 'text')->setPlatformOption('collation', 'BINARY');
        $table->addColumn('bar', 'text')->setPlatformOption('collation', 'NOCASE');
Sergei Morozov's avatar
Sergei Morozov committed
132
        $this->schemaManager->dropAndCreateTable($table);
133

Sergei Morozov's avatar
Sergei Morozov committed
134
        $columns = $this->schemaManager->listTableColumns('test_collation');
135

136 137 138 139
        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'));
140 141
    }

142
    public function testListTableWithBinary(): void
Steve Müller's avatar
Steve Müller committed
143 144 145
    {
        $tableName = 'test_binary_table';

Sergei Morozov's avatar
Sergei Morozov committed
146
        $table = new Table($tableName);
Steve Müller's avatar
Steve Müller committed
147
        $table->addColumn('id', 'integer');
Sergei Morozov's avatar
Sergei Morozov committed
148 149 150
        $table->addColumn('column_varbinary', 'binary', []);
        $table->addColumn('column_binary', 'binary', ['fixed' => true]);
        $table->setPrimaryKey(['id']);
Steve Müller's avatar
Steve Müller committed
151

Sergei Morozov's avatar
Sergei Morozov committed
152
        $this->schemaManager->createTable($table);
Steve Müller's avatar
Steve Müller committed
153

Sergei Morozov's avatar
Sergei Morozov committed
154
        $table = $this->schemaManager->listTableDetails($tableName);
Steve Müller's avatar
Steve Müller committed
155

Sergei Morozov's avatar
Sergei Morozov committed
156
        self::assertInstanceOf(BlobType::class, $table->getColumn('column_varbinary')->getType());
157
        self::assertFalse($table->getColumn('column_varbinary')->getFixed());
Steve Müller's avatar
Steve Müller committed
158

Sergei Morozov's avatar
Sergei Morozov committed
159
        self::assertInstanceOf(BlobType::class, $table->getColumn('column_binary')->getType());
160
        self::assertFalse($table->getColumn('column_binary')->getFixed());
Steve Müller's avatar
Steve Müller committed
161
    }
162

163
    public function testNonDefaultPKOrder(): void
164
    {
Sergei Morozov's avatar
Sergei Morozov committed
165
        if (! extension_loaded('sqlite3')) {
166
            self::markTestSkipped('This test requires the SQLite3 extension.');
167 168
        }

Sergei Morozov's avatar
Sergei Morozov committed
169 170
        $version = SQLite3::version();
        if (version_compare($version['versionString'], '3.7.16', '<')) {
171
            self::markTestSkipped('This version of sqlite doesn\'t return the order of the Primary Key.');
172
        }
Grégoire Paris's avatar
Grégoire Paris committed
173

Sergei Morozov's avatar
Sergei Morozov committed
174
        $this->connection->exec(<<<EOS
175 176 177 178 179 180 181 182
CREATE TABLE non_default_pk_order (
    id INTEGER,
    other_id INTEGER,
    PRIMARY KEY(other_id, id)
)
EOS
        );

Sergei Morozov's avatar
Sergei Morozov committed
183
        $tableIndexes = $this->schemaManager->listTableIndexes('non_default_pk_order');
184

Gabriel Caruso's avatar
Gabriel Caruso committed
185
         self::assertCount(1, $tableIndexes);
186

187
        self::assertArrayHasKey('primary', $tableIndexes, 'listTableIndexes() has to return a "primary" array key.');
Sergei Morozov's avatar
Sergei Morozov committed
188
        self::assertEquals(['other_id', 'id'], array_map('strtolower', $tableIndexes['primary']->getColumns()));
189
    }
190

191 192 193
    /**
     * @group DBAL-1779
     */
194
    public function testListTableColumnsWithWhitespacesInTypeDeclarations(): void
195 196 197 198 199 200 201 202
    {
        $sql = <<<SQL
CREATE TABLE dbal_1779 (
    foo VARCHAR (64) ,
    bar TEXT (100)
)
SQL;

Sergei Morozov's avatar
Sergei Morozov committed
203
        $this->connection->exec($sql);
204

Sergei Morozov's avatar
Sergei Morozov committed
205
        $columns = $this->schemaManager->listTableColumns('dbal_1779');
206

207
        self::assertCount(2, $columns);
208

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

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

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

219 220 221 222
    /**
     * @dataProvider getDiffListIntegerAutoincrementTableColumnsData
     * @group DBAL-924
     */
223
    public function testDiffListIntegerAutoincrementTableColumns(string $integerType, bool $unsigned, bool $expectedComparatorDiff): void
224 225 226
    {
        $tableName = 'test_int_autoincrement_table';

Sergei Morozov's avatar
Sergei Morozov committed
227 228 229
        $offlineTable = new Table($tableName);
        $offlineTable->addColumn('id', $integerType, ['autoincrement' => true, 'unsigned' => $unsigned]);
        $offlineTable->setPrimaryKey(['id']);
230

Sergei Morozov's avatar
Sergei Morozov committed
231
        $this->schemaManager->dropAndCreateTable($offlineTable);
232

Sergei Morozov's avatar
Sergei Morozov committed
233
        $onlineTable = $this->schemaManager->listTableDetails($tableName);
Sergei Morozov's avatar
Sergei Morozov committed
234 235
        $comparator  = new Schema\Comparator();
        $diff        = $comparator->diffTable($offlineTable, $onlineTable);
236 237

        if ($expectedComparatorDiff) {
Sergei Morozov's avatar
Sergei Morozov committed
238
            self::assertEmpty($this->schemaManager->getDatabasePlatform()->getAlterTableSQL($diff));
239
        } else {
240
            self::assertFalse($diff);
241 242 243 244
        }
    }

    /**
Sergei Morozov's avatar
Sergei Morozov committed
245
     * @return mixed[][]
246
     */
247
    public static function getDiffListIntegerAutoincrementTableColumnsData(): iterable
248
    {
Sergei Morozov's avatar
Sergei Morozov committed
249 250 251 252 253 254 255 256
        return [
            ['smallint', false, true],
            ['smallint', true, true],
            ['integer', false, false],
            ['integer', true, true],
            ['bigint', false, true],
            ['bigint', true, true],
        ];
257
    }
258 259 260 261

    /**
     * @group DBAL-2921
     */
262
    public function testPrimaryKeyNoAutoIncrement(): void
263 264 265 266 267
    {
        $table = new Schema\Table('test_pk_auto_increment');
        $table->addColumn('id', 'integer');
        $table->addColumn('text', 'text');
        $table->setPrimaryKey(['id']);
Sergei Morozov's avatar
Sergei Morozov committed
268
        $this->schemaManager->dropAndCreateTable($table);
269

Sergei Morozov's avatar
Sergei Morozov committed
270
        $this->connection->insert('test_pk_auto_increment', ['text' => '1']);
271

Sergei Morozov's avatar
Sergei Morozov committed
272
        $this->connection->query('DELETE FROM test_pk_auto_increment');
273

Sergei Morozov's avatar
Sergei Morozov committed
274
        $this->connection->insert('test_pk_auto_increment', ['text' => '2']);
275

276 277 278
        $result = $this->connection->query('SELECT id FROM test_pk_auto_increment WHERE text = "2"');

        $lastUsedIdAfterDelete = (int) $result->fetchOne();
279 280

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

284
    public function testOnlyOwnCommentIsParsed(): void
285 286 287 288 289 290 291 292 293
    {
        $table = new Table('own_column_comment');
        $table->addColumn('col1', 'string', ['length' => 16]);
        $table->addColumn('col2', 'string', ['length' => 16, 'comment' => 'Column #2']);
        $table->addColumn('col3', 'string', ['length' => 16]);

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

294
        self::assertNull($sm->listTableDetails('own_column_comment')
295 296 297
            ->getColumn('col1')
            ->getComment());
    }
298
}