SqliteSchemaManagerTest::testCreatingATableWithAForeignKey() - Code Metrics - Inspection of "Use proper check in acceptForeignKey()" - doctrine/dbal - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — 2.10.x (#3994)

by Grégoire

created 2020-05-05 17:58 UTC

testCreatingATableWithAForeignKey() A

↳ Parent: SqliteSchemaManagerTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	17
Code Lines	11

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	11
c	0
b	0
f	0
dl	0
loc	17
rs	9.9
cc	1
nc	1
nop	0

<?php

namespace Doctrine\Tests\DBAL\Functional\Schema;

use Doctrine\DBAL\DBALException;
use Doctrine\DBAL\Driver\Connection;
use Doctrine\DBAL\Schema;
use Doctrine\DBAL\Schema\Table;
use Doctrine\DBAL\Types\BlobType;
use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Types\Types;
use SQLite3;
use function array_map;
use function dirname;
use function extension_loaded;
use function version_compare;

class SqliteSchemaManagerTest extends SchemaManagerFunctionalTestCase
{
    /**
     * SQLITE does not support databases.
     */
    public function testListDatabases() : void
    {
        $this->expectException(DBALException::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::assertFileNotExists($path);
    }

    /**
     * @group DBAL-1220
     */
    public function testDropsDatabaseWithActiveConnections() : void
    {
        $this->schemaManager->dropAndCreateDatabase('test_drop_database');

        self::assertFileExists('test_drop_database');

        $params           = $this->connection->getParams();
        $params['dbname'] = 'test_drop_database';

        $user     = $params['user'] ?? null;
        $password = $params['password'] ?? null;

        $connection = $this->connection->getDriver()->connect($params, $user, $password);

        self::assertInstanceOf(Connection::class, $connection);

        $this->schemaManager->dropDatabase('test_drop_database');

        self::assertFileNotExists('test_drop_database');

        unset($connection);
    }

    public function testRenameTable() : void
    {
        $this->createTestTable('oldname');
        $this->schemaManager->renameTable('oldname', 'newname');

        $tables = $this->schemaManager->listTableNames();
        self::assertContains('newname', $tables);
        self::assertNotContains('oldname', $tables);
    }

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

        return $table;
    }

    public function testListForeignKeysFromExistingDatabase() : void
    {
        $this->connection->exec(<<<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 Schema\ForeignKeyConstraint(
                ['log'],
                'log',
                [null],
                '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]
            ),
        ];

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

    public function testColumnCollation() : void
    {
        $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');
        $this->schemaManager->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'));
    }

    public function testListTableWithBinary() : void
    {
        $tableName = 'test_binary_table';

        $table = new Table($tableName);
        $table->addColumn('id', 'integer');
        $table->addColumn('column_varbinary', 'binary', []);
        $table->addColumn('column_binary', 'binary', ['fixed' => true]);
        $table->setPrimaryKey(['id']);

        $this->schemaManager->createTable($table);

        $table = $this->schemaManager->listTableDetails($tableName);

        self::assertInstanceOf(BlobType::class, $table->getColumn('column_varbinary')->getType());
        self::assertFalse($table->getColumn('column_varbinary')->getFixed());

        self::assertInstanceOf(BlobType::class, $table->getColumn('column_binary')->getType());
        self::assertFalse($table->getColumn('column_binary')->getFixed());
    }

    public function testNonDefaultPKOrder() : void
    {
        if (! extension_loaded('sqlite3')) {
            $this->markTestSkipped('This test requires the SQLite3 extension.');
        }

        $version = SQLite3::version();
        if (version_compare($version['versionString'], '3.7.16', '<')) {
            $this->markTestSkipped('This version of sqlite doesn\'t return the order of the Primary Key.');
        }

        $this->connection->exec(<<<EOS
CREATE TABLE non_default_pk_order (
    id INTEGER,
    other_id INTEGER,
    PRIMARY KEY(other_id, id)
)
EOS
        );

        $tableIndexes = $this->schemaManager->listTableIndexes('non_default_pk_order');

         self::assertCount(1, $tableIndexes);

        self::assertArrayHasKey('primary', $tableIndexes, 'listTableIndexes() has to return a "primary" array key.');
        self::assertEquals(['other_id', 'id'], array_map('strtolower', $tableIndexes['primary']->getColumns()));
    }

    /**
     * @group DBAL-1779
     */
    public function testListTableColumnsWithWhitespacesInTypeDeclarations() : void
    {
        $sql = <<<SQL
CREATE TABLE dbal_1779 (
    foo VARCHAR (64) ,
    bar TEXT (100)
)
SQL;

        $this->connection->exec($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());
    }

    /**
     * @dataProvider getDiffListIntegerAutoincrementTableColumnsData
     * @group DBAL-924
     */
    public function testDiffListIntegerAutoincrementTableColumns(string $integerType, bool $unsigned, bool $expectedComparatorDiff) : void
    {
        $tableName = 'test_int_autoincrement_table';

        $offlineTable = new Table($tableName);
        $offlineTable->addColumn('id', $integerType, ['autoincrement' => true, 'unsigned' => $unsigned]);
        $offlineTable->setPrimaryKey(['id']);

        $this->schemaManager->dropAndCreateTable($offlineTable);

        $onlineTable = $this->schemaManager->listTableDetails($tableName);
        $comparator  = new Schema\Comparator();
        $diff        = $comparator->diffTable($offlineTable, $onlineTable);

        if ($expectedComparatorDiff) {
            self::assertEmpty($this->schemaManager->getDatabasePlatform()->getAlterTableSQL($diff));
        } else {
            self::assertFalse($diff);
        }
    }

    /**
     * @return mixed[][]
     */
    public static function getDiffListIntegerAutoincrementTableColumnsData() : iterable
    {
        return [
            ['smallint', false, true],
            ['smallint', true, true],
            ['integer', false, false],
            ['integer', true, true],
            ['bigint', false, true],
            ['bigint', true, true],
        ];
    }

    /**
     * @group DBAL-2921
     */
    public function testPrimaryKeyNoAutoIncrement() : void
    {
        $table = new Schema\Table('test_pk_auto_increment');
        $table->addColumn('id', 'integer');
        $table->addColumn('text', 'text');
        $table->setPrimaryKey(['id']);
        $this->schemaManager->dropAndCreateTable($table);

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

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

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

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

        // with an empty table, non autoincrement rowid is always 1
        $this->assertEquals(1, $lastUsedIdAfterDelete);
    }

    public function testOnlyOwnCommentIsParsed() : void
    {
        $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);

        $this->assertNull($sm->listTableDetails('own_column_comment')
            ->getColumn('col1')
            ->getComment());
    }

    /**
     * @doesNotPerformAssertions
     */
    public function testCreatingATableWithAForeignKey() : void
    {
        $schema = new Schema\Schema();

        $referencedTable = $schema->createTable('referenced');
        $referencedTable->addColumn('id', 'integer');

        $referencingTable = $schema->createTable('referencing');
        $referencingTable->addColumn('id', 'integer');
        $referencingTable->addColumn('referenced_id', 'integer');
        $referencingTable->addForeignKeyConstraint(
            $referencedTable,
            ['referenced_id'],
            ['id']
        );

        $schema->toSql($this->connection->getDatabasePlatform());
    }
}


1			<?php
2
3			namespace Doctrine\Tests\DBAL\Functional\Schema;
4
5			use Doctrine\DBAL\DBALException;
6			use Doctrine\DBAL\Driver\Connection;
7			use Doctrine\DBAL\Schema;
8			use Doctrine\DBAL\Schema\Table;
9			use Doctrine\DBAL\Types\BlobType;
10			use Doctrine\DBAL\Types\Type;
11			use Doctrine\DBAL\Types\Types;
12			use SQLite3;
13			use function array_map;
14			use function dirname;
15			use function extension_loaded;
16			use function version_compare;
17
18			class SqliteSchemaManagerTest extends SchemaManagerFunctionalTestCase
19			{
20			/**
21			* SQLITE does not support databases.
22			*/
23			public function testListDatabases() : void
24			{
25			$this->expectException(DBALException::class);
26
27			$this->schemaManager->listDatabases();
28			}
29
30			public function testCreateAndDropDatabase() : void
31			{
32			$path = dirname(__FILE__) . '/test_create_and_drop_sqlite_database.sqlite';
33
34			$this->schemaManager->createDatabase($path);
35			self::assertFileExists($path);
36			$this->schemaManager->dropDatabase($path);
37			self::assertFileNotExists($path);
38			}
39
40			/**
41			* @group DBAL-1220
42			*/
43			public function testDropsDatabaseWithActiveConnections() : void
44			{
45			$this->schemaManager->dropAndCreateDatabase('test_drop_database');
46
47			self::assertFileExists('test_drop_database');
48
49			$params = $this->connection->getParams();
50			$params['dbname'] = 'test_drop_database';
51
52			$user = $params['user'] ?? null;
53			$password = $params['password'] ?? null;
54
55			$connection = $this->connection->getDriver()->connect($params, $user, $password);
56
57			self::assertInstanceOf(Connection::class, $connection);
58
59			$this->schemaManager->dropDatabase('test_drop_database');
60
61			self::assertFileNotExists('test_drop_database');
62
63			unset($connection);
64			}
65
66			public function testRenameTable() : void
67			{
68			$this->createTestTable('oldname');
69			$this->schemaManager->renameTable('oldname', 'newname');
70
71			$tables = $this->schemaManager->listTableNames();
72			self::assertContains('newname', $tables);
73			self::assertNotContains('oldname', $tables);
74			}
75
76			public function createListTableColumns() : Table
77			{
78			$table = parent::createListTableColumns();
79			$table->getColumn('id')->setAutoincrement(true);
80
81			return $table;
82			}
83
84			public function testListForeignKeysFromExistingDatabase() : void
85			{
86			$this->connection->exec(<<<EOS
87			CREATE TABLE user (
88			id INTEGER PRIMARY KEY AUTOINCREMENT,
89			page INTEGER CONSTRAINT FK_1 REFERENCES page (key) DEFERRABLE INITIALLY DEFERRED,
90			parent INTEGER REFERENCES user(id) ON DELETE CASCADE,
91			log INTEGER,
92			CONSTRAINT FK_3 FOREIGN KEY (log) REFERENCES log ON UPDATE SET NULL NOT DEFERRABLE
93			)
94			EOS
95			);
96
97			$expected = [
98			new Schema\ForeignKeyConstraint(
99			['log'],
100			'log',
101			[null],
102			'FK_3',
103			['onUpdate' => 'SET NULL', 'onDelete' => 'NO ACTION', 'deferrable' => false, 'deferred' => false]
104			),
105			new Schema\ForeignKeyConstraint(
106			['parent'],
107			'user',
108			['id'],
109			'1',
110			['onUpdate' => 'NO ACTION', 'onDelete' => 'CASCADE', 'deferrable' => false, 'deferred' => false]
111			),
112			new Schema\ForeignKeyConstraint(
113			['page'],
114			'page',
115			['key'],
116			'FK_1',
117			['onUpdate' => 'NO ACTION', 'onDelete' => 'NO ACTION', 'deferrable' => true, 'deferred' => true]
118			),
119			];
120
121			self::assertEquals($expected, $this->schemaManager->listTableForeignKeys('user'));
122			}
123
124			public function testColumnCollation() : void
125			{
126			$table = new Schema\Table('test_collation');
127			$table->addColumn('id', 'integer');
128			$table->addColumn('text', 'text');
129			$table->addColumn('foo', 'text')->setPlatformOption('collation', 'BINARY');
130			$table->addColumn('bar', 'text')->setPlatformOption('collation', 'NOCASE');
131			$this->schemaManager->dropAndCreateTable($table);
132
133			$columns = $this->schemaManager->listTableColumns('test_collation');
134
135			self::assertArrayNotHasKey('collation', $columns['id']->getPlatformOptions());
136			self::assertEquals('BINARY', $columns['text']->getPlatformOption('collation'));
137			self::assertEquals('BINARY', $columns['foo']->getPlatformOption('collation'));
138			self::assertEquals('NOCASE', $columns['bar']->getPlatformOption('collation'));
139			}
140
141			public function testListTableWithBinary() : void
142			{
143			$tableName = 'test_binary_table';
144
145			$table = new Table($tableName);
146			$table->addColumn('id', 'integer');
147			$table->addColumn('column_varbinary', 'binary', []);
148			$table->addColumn('column_binary', 'binary', ['fixed' => true]);
149			$table->setPrimaryKey(['id']);
150
151			$this->schemaManager->createTable($table);
152
153			$table = $this->schemaManager->listTableDetails($tableName);
154
155			self::assertInstanceOf(BlobType::class, $table->getColumn('column_varbinary')->getType());
156			self::assertFalse($table->getColumn('column_varbinary')->getFixed());
157
158			self::assertInstanceOf(BlobType::class, $table->getColumn('column_binary')->getType());
159			self::assertFalse($table->getColumn('column_binary')->getFixed());
160			}
161
162			public function testNonDefaultPKOrder() : void
163			{
164			if (! extension_loaded('sqlite3')) {
165			$this->markTestSkipped('This test requires the SQLite3 extension.');
166			}
167
168			$version = SQLite3::version();
169			if (version_compare($version['versionString'], '3.7.16', '<')) {
170			$this->markTestSkipped('This version of sqlite doesn\'t return the order of the Primary Key.');
171			}
172
173			$this->connection->exec(<<<EOS
174			CREATE TABLE non_default_pk_order (
175			id INTEGER,
176			other_id INTEGER,
177			PRIMARY KEY(other_id, id)
178			)
179			EOS
180			);
181
182			$tableIndexes = $this->schemaManager->listTableIndexes('non_default_pk_order');
183
184			self::assertCount(1, $tableIndexes);
185
186			self::assertArrayHasKey('primary', $tableIndexes, 'listTableIndexes() has to return a "primary" array key.');
187			self::assertEquals(['other_id', 'id'], array_map('strtolower', $tableIndexes['primary']->getColumns()));
188			}
189
190			/**
191			* @group DBAL-1779
192			*/
193			public function testListTableColumnsWithWhitespacesInTypeDeclarations() : void
194			{
195			$sql = <<<SQL
196			CREATE TABLE dbal_1779 (
197			foo VARCHAR (64) ,
198			bar TEXT (100)
199			)
200			SQL;
201
202			$this->connection->exec($sql);
203
204			$columns = $this->schemaManager->listTableColumns('dbal_1779');
205
206			self::assertCount(2, $columns);
207
208			self::assertArrayHasKey('foo', $columns);
209			self::assertArrayHasKey('bar', $columns);
210
211			self::assertSame(Type::getType(Types::STRING), $columns['foo']->getType());
212			self::assertSame(Type::getType(Types::TEXT), $columns['bar']->getType());
213
214			self::assertSame(64, $columns['foo']->getLength());
215			self::assertSame(100, $columns['bar']->getLength());
216			}
217
218			/**
219			* @dataProvider getDiffListIntegerAutoincrementTableColumnsData
220			* @group DBAL-924
221			*/
222			public function testDiffListIntegerAutoincrementTableColumns(string $integerType, bool $unsigned, bool $expectedComparatorDiff) : void
223			{
224			$tableName = 'test_int_autoincrement_table';
225
226			$offlineTable = new Table($tableName);
227			$offlineTable->addColumn('id', $integerType, ['autoincrement' => true, 'unsigned' => $unsigned]);
228			$offlineTable->setPrimaryKey(['id']);
229
230			$this->schemaManager->dropAndCreateTable($offlineTable);
231
232			$onlineTable = $this->schemaManager->listTableDetails($tableName);
233			$comparator = new Schema\Comparator();
234			$diff = $comparator->diffTable($offlineTable, $onlineTable);
235
236			if ($expectedComparatorDiff) {
237			self::assertEmpty($this->schemaManager->getDatabasePlatform()->getAlterTableSQL($diff));
238			} else {
239			self::assertFalse($diff);
240			}
241			}
242
243			/**
244			* @return mixed[][]
245			*/
246			public static function getDiffListIntegerAutoincrementTableColumnsData() : iterable
247			{
248			return [
249			['smallint', false, true],
250			['smallint', true, true],
251			['integer', false, false],
252			['integer', true, true],
253			['bigint', false, true],
254			['bigint', true, true],
255			];
256			}
257
258			/**
259			* @group DBAL-2921
260			*/
261			public function testPrimaryKeyNoAutoIncrement() : void
262			{
263			$table = new Schema\Table('test_pk_auto_increment');
264			$table->addColumn('id', 'integer');
265			$table->addColumn('text', 'text');
266			$table->setPrimaryKey(['id']);
267			$this->schemaManager->dropAndCreateTable($table);
268
269			$this->connection->insert('test_pk_auto_increment', ['text' => '1']);
270
271			$this->connection->query('DELETE FROM test_pk_auto_increment');
272
273			$this->connection->insert('test_pk_auto_increment', ['text' => '2']);
274
275			$query = $this->connection->query('SELECT id FROM test_pk_auto_increment WHERE text = "2"');
276			$query->execute();
277			$lastUsedIdAfterDelete = (int) $query->fetchColumn();
278
279			// with an empty table, non autoincrement rowid is always 1
280			$this->assertEquals(1, $lastUsedIdAfterDelete);
281			}
282
283			public function testOnlyOwnCommentIsParsed() : void
284			{
285			$table = new Table('own_column_comment');
286			$table->addColumn('col1', 'string', ['length' => 16]);
287			$table->addColumn('col2', 'string', ['length' => 16, 'comment' => 'Column #2']);
288			$table->addColumn('col3', 'string', ['length' => 16]);
289
290			$sm = $this->connection->getSchemaManager();
291			$sm->createTable($table);
292
293			$this->assertNull($sm->listTableDetails('own_column_comment')
294			->getColumn('col1')
295			->getComment());
296			}
297
298			/**
299			* @doesNotPerformAssertions
300			*/
301			public function testCreatingATableWithAForeignKey() : void
302			{
303			$schema = new Schema\Schema();
304
305			$referencedTable = $schema->createTable('referenced');
306			$referencedTable->addColumn('id', 'integer');
307
308			$referencingTable = $schema->createTable('referencing');
309			$referencingTable->addColumn('id', 'integer');
310			$referencingTable->addColumn('referenced_id', 'integer');
311			$referencingTable->addForeignKeyConstraint(
312			$referencedTable,
313			['referenced_id'],
314			['id']
315			);
316
317			$schema->toSql($this->connection->getDatabasePlatform());
318			}
319			}
320

doctrine / dbal

Pull Request — 2.10.x (#3994)

testCreatingATableWithAForeignKey() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like