CommonQueryBuilderTest::testCreateTableColumnTypes() - Code Metrics - Inspection of "Rework tests #2." - yiisoft/db - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#380)

by Wilmer

created 2022-11-21 18:08 UTC

testCreateTableColumnTypes() B

↳ Parent: CommonQueryBuilderTest

Complexity

Conditions	8
Paths	6

Size

Total Lines	31
Code Lines	17

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	8
eloc	17
nc	6
nop	0
dl	0
loc	31
rs	8.4444
c	1
b	0
f	0

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Tests\Common;

use Closure;
use JsonException;
use Throwable;
use Yiisoft\Arrays\ArrayHelper;
use Yiisoft\Db\Exception\Exception;
use Yiisoft\Db\Exception\IntegrityException;
use Yiisoft\Db\Exception\InvalidArgumentException;
use Yiisoft\Db\Exception\InvalidConfigException;
use Yiisoft\Db\Exception\NotSupportedException;
use Yiisoft\Db\Expression\ExpressionInterface;
use Yiisoft\Db\Query\QueryInterface;
use Yiisoft\Db\Schema\ColumnSchemaBuilder;
use Yiisoft\Db\Schema\Schema;
use Yiisoft\Db\Tests\AbstractQueryBuilderTest;
use Yiisoft\Db\Tests\Provider\ColumnTypesProvider;
use Yiisoft\Db\Tests\Support\Assert;
use Yiisoft\Db\Tests\Support\DbHelper;
use Yiisoft\Db\Tests\Support\TestTrait;

use function is_array;
use function str_replace;
use function str_starts_with;
use function strncmp;

/**
 * @group mssql
 * @group mysql
 * @group pgsql
 * @group oracle
 * @group sqlite
 */
abstract class CommonQueryBuilderTest extends AbstractQueryBuilderTest
{
    use TestTrait;

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::addDropChecks()
     */
    public function testAddDropCheck(string $sql, Closure $builder): void
    {
        $db = $this->getConnection();

        $qb = $db->getQueryBuilder();

        $this->assertSame($db->getQuoter()->quoteSql($sql), $builder($qb));
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::addDropForeignKeys()
     */
    public function testAddDropForeignKey(string $sql, Closure $builder): void
    {
        $db = $this->getConnection();

        $qb = $db->getQueryBuilder();

        $this->assertSame($db->getQuoter()->quoteSql($sql), $builder($qb));
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::addDropPrimaryKeys()
     */
    public function testAddDropPrimaryKey(string $sql, Closure $builder): void
    {
        $db = $this->getConnection();

        $qb = $db->getQueryBuilder();

        $this->assertSame($db->getQuoter()->quoteSql($sql), $builder($qb));
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::addDropUniques()
     */
    public function testAddDropUnique(string $sql, Closure $builder): void
    {
        $db = $this->getConnection();

        $qb = $db->getQueryBuilder();

        $this->assertSame($db->getQuoter()->quoteSql($sql), $builder($qb));
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::alterColumn()
     */
    public function testAlterColumn(
        string $table,
        string $column,
        ColumnSchemaBuilder|string $type,
        string $expected
    ): void {
        $db = $this->getConnection();

        $qb = $db->getQueryBuilder();
        $sql = $qb->alterColumn($table, $column, $type);

        $this->assertSame($expected, $sql);
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::batchInsert()
     */
    public function testBatchInsert(
        string $table,
        array $columns,
        array $value,
        string|null $expected,
        array $expectedParams = []
    ): void {
        $db = $this->getConnectionWithData();

        $qb = $db->getQueryBuilder();
        $params = [];
        $sql = $qb->batchInsert($table, $columns, $value, $params);

        $this->assertSame($expected, $sql);
        $this->assertEquals($expectedParams, $params);
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::buildConditions()
     */
    public function testBuildCondition(
        array|ExpressionInterface|string $conditions,
        string $expected,
        array $expectedParams = []
    ): void {
        $db = $this->getConnection();

        $qb = $db->getQueryBuilder();
        $query = $this->getQuery($db)->where($conditions);

        [$sql, $params] = $qb->build($query);

        $this->assertSame(
            'SELECT *' . (
                empty($expected) ? '' : ' WHERE ' . DbHelper::replaceQuotes($expected, $db->getName())
            ),
            $sql,
        );
        $this->assertSame($expectedParams, $params);
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::buildFilterConditions()
     */
    public function testBuildFilterCondition(array $condition, string $expected, array $expectedParams): void
    {
        $db = $this->getConnection();

        $qb = $db->getQueryBuilder();
        $query = $this->getQuery($db)->filterWhere($condition);

        [$sql, $params] = $qb->build($query);

        $this->assertSame(
            'SELECT *' . (
                empty($expected) ? '' : ' WHERE ' . DbHelper::replaceQuotes($expected, $db->getName())
            ),
            $sql,
        );
        $this->assertSame($expectedParams, $params);
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::buildFrom()
     */
    public function testBuildFrom(string $table, string $expected): void
    {
        $db = $this->getConnection();

        $qb = $db->getQueryBuilder();
        $params = [];
        $sql = $qb->buildFrom([$table], $params);
        $replacedQuotes = DbHelper::replaceQuotes($expected, $db->getName());

        $this->assertIsString($replacedQuotes);
        $this->assertSame('FROM ' . $replacedQuotes, $sql);
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::buildLikeConditions()
     *
     * @throws Exception
     * @throws InvalidArgumentException
     * @throws InvalidConfigException
     * @throws NotSupportedException
     */
    public function testBuildLikeCondition(
        array|ExpressionInterface $condition,
        string $expected,
        array $expectedParams
    ): void {
        $db = $this->getConnection();

        $query = $this->getQuery($db)->where($condition);

        [$sql, $params] = $db->getQueryBuilder()->build($query);

        $replacedQuotes = DbHelper::replaceQuotes($expected, $db->getName());

        $this->assertIsString($replacedQuotes);
        $this->assertEquals('SELECT *' . (empty($expected) ? '' : ' WHERE ' . $replacedQuotes), $sql);
        $this->assertEquals($expectedParams, $params);
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::buildWhereExists()
     */
    public function testBuildWhereExists(string $cond, string $expectedQuerySql): void
    {
        $db = $this->getConnection();

        $qb = $db->getQueryBuilder();
        $expectedQueryParams = [];
        $subQuery = $this->getQuery($db)->select('1')->from('Website w');
        $query = $this->getQuery($db)->select('id')->from('TotalExample t')->where([$cond, $subQuery]);

        [$actualQuerySql, $actualQueryParams] = $qb->build($query);

        $this->assertSame($expectedQuerySql, $actualQuerySql);
        $this->assertSame($expectedQueryParams, $actualQueryParams);
    }

    public function testCheckIntegrity(): void
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $qb = $db->getQueryBuilder();
        $sql = $qb->checkIntegrity('schema', 'table');

        $this->assertSame(0, $command->setSql($sql)->execute());
    }

    public function testCheckIntegrityExecuteException(): void
    {
        $db = $this->getConnectionWithData();

        $command = $db->createCommand();
        $qb = $db->getQueryBuilder();
        $schemaName = 'dbo';
        $tableName = 'T_constraints_3';
        $command->setSql($qb->checkIntegrity($schemaName, $tableName, false))->execute();
        $command->setSql(
            <<<SQL
            INSERT INTO {{{$tableName}}} ([[C_id]], [[C_fk_id_1]], [[C_fk_id_2]]) VALUES (1, 2, 3)
            SQL
        )->execute();
        $command->setSql($qb->checkIntegrity($schemaName, $tableName))->execute();

        $this->expectException(IntegrityException::class);

        $command->setSql(
            <<<SQL
            INSERT INTO {{{$tableName}}} ([[C_id]], [[C_fk_id_1]], [[C_fk_id_2]]) VALUES (1, 2, 3)
            SQL
        )->execute();
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::createDropIndex()
     */
    public function testCreateDropIndex(string $sql, Closure $builder): void
    {
        $db = $this->getConnection();

        $qb = $db->getQueryBuilder();

        $this->assertSame($db->getQuoter()->quoteSql($sql), $builder($qb));
    }

    /**
     * @throws Exception
     * @throws InvalidConfigException
     * @throws Throwable
     */
    public function testCreateTable(): void
    {
        $db = $this->getConnection();

        $command = $db->createCommand();
        $qb = $db->getQueryBuilder();

        if ($db->getSchema()->getTableSchema('testCreateTable', true) !== null) {
            $command->dropTable('testCreateTable')->execute();
        }

        $sql = $qb->createTable('testCreateTable', ['id' => Schema::TYPE_PK, 'bar' => Schema::TYPE_INTEGER]);
        $command->setSql($sql)->execute();
        $command->insert('testCreateTable', ['bar' => 1])->execute();
        $records = $command->setSql(
            <<<SQL
            SELECT [[id]], [[bar]] FROM {{testCreateTable}};
            SQL
        )->queryAll();

        $this->assertEquals([['id' => 1, 'bar' => 1]], $records);
    }

    public function testCreateTableColumnTypes(): void
    {
        $db = $this->getConnectionWithData();

        $qb = $db->getQueryBuilder();

        if ($db->getTableSchema('column_type_table', true) !== null) {
            $db->createCommand($qb->dropTable('column_type_table'))->execute();
        }

        $columnTypes = (new ColumnTypesProvider())->columnTypes($db);
        $columns = [];
        $i = 0;

        foreach ($columnTypes as [$column, $builder, $expected]) {
            if (
                !(
                    strncmp($column, Schema::TYPE_PK, 2) === 0 ||
                    strncmp($column, Schema::TYPE_UPK, 3) === 0 ||
                    strncmp($column, Schema::TYPE_BIGPK, 5) === 0 ||
                    strncmp($column, Schema::TYPE_UBIGPK, 6) === 0 ||
                    str_starts_with(substr($column, -5), 'FIRST')
                )
            ) {
                $columns['col' . ++$i] = str_replace('CHECK (value', 'CHECK ([[col' . $i . ']]', $column);
            }
        }

        $db->createCommand($qb->createTable('column_type_table', $columns))->execute();

        $this->assertNotEmpty($db->getTableSchema('column_type_table', true));
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::delete()
     */
    public function testDelete(string $table, array|string $condition, string $expectedSQL, array $expectedParams): void
    {
        $db = $this->getConnection();

        $qb = $db->getQueryBuilder();
        $actualParams = [];
        $actualSQL = $qb->delete($table, $condition, $actualParams);

        $this->assertSame($expectedSQL, $actualSQL);
        $this->assertSame($expectedParams, $actualParams);
    }

    public function testDropCommentFromColumn(): void
    {
        $db = $this->getConnectionWithData();

        $command = $db->createCommand();
        $qb = $db->getQueryBuilder();
        $sql = $qb->addCommentOnColumn('customer', 'id', 'Primary key.');
        $command->setSql($sql)->execute();
        $commentOnColumn = DbHelper::getCommmentsFromColumn('customer', 'id', $db);

        $this->assertSame('Primary key.', $commentOnColumn);

        $sql = $qb->dropCommentFromColumn('customer', 'id');
        $command->setSql($sql)->execute();
        $commentOnColumn = DbHelper::getCommmentsFromColumn('customer', 'id', $db);

        $this->assertSame([], $commentOnColumn);
    }

    public function testDropCommentFromTable(): void
    {
        $db = $this->getConnectionWithData();

        $command = $db->createCommand();
        $qb = $db->getQueryBuilder();
        $sql = $qb->addCommentOnTable('customer', 'Customer table.');
        $command->setSql($sql)->execute();
        $commentOnTable = DbHelper::getCommmentsFromTable('customer', $db);

        $this->assertSame('Customer table.', $commentOnTable);

        $sql = $qb->dropCommentFromTable('customer');
        $command->setSql($sql)->execute();
        $commentOnTable = DbHelper::getCommmentsFromTable('customer', $db);

        $this->assertSame([], $commentOnTable);
    }

    public function testGetColumnType(): void
    {
        $db = $this->getConnection();

        $columnTypes = (new ColumnTypesProvider())->columnTypes($db);
        $qb = $db->getQueryBuilder();

        foreach ($columnTypes as $item) {
            [$column, $builder, $expected] = $item;

            $driverName = $db->getName();

            if (isset($item[3][$driverName])) {
                $expectedColumnSchemaBuilder = $item[3][$driverName];
            } elseif (isset($item[3]) && !is_array($item[3])) {
                $expectedColumnSchemaBuilder = $item[3];
            } else {
                $expectedColumnSchemaBuilder = $column;
            }

            $this->assertSame($expectedColumnSchemaBuilder, $builder->__toString());
            $this->assertSame($expected, $qb->getColumnType($column));
            $this->assertSame($expected, $qb->getColumnType($builder));
        }
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::insert()
     */
    public function testInsert(
        string $table,
        array|QueryInterface $columns,
        array $params,
        string $expectedSQL,
        array $expectedParams
    ): void {
        $db = $this->getConnection();

        $qb = $db->getQueryBuilder();

        $this->assertSame($expectedSQL, $qb->insert($table, $columns, $params));
        $this->assertSame($expectedParams, $params);
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::insertEx()
     */
    public function testInsertEx(
        string $table,
        array|QueryInterface $columns,
        array $params,
        string $expectedSQL,
        array $expectedParams
    ): void {
        $db = $this->getConnectionWithData();

        $qb = $db->getQueryBuilder();

        $this->assertSame($expectedSQL, $qb->insertEx($table, $columns, $params));
        $this->assertSame($expectedParams, $params);
    }

    /**
     * @throws Exception
     * @throws InvalidConfigException
     * @throws Throwable
     */
    public function testRenameTable(): void
    {
        $db = $this->getConnectionWithData();

        $fromTableName = 'type';
        $toTableName = 'new_type';
        $command = $db->createCommand();
        $qb = $db->getQueryBuilder();

        if ($db->getSchema()->getTableSchema($toTableName) !== null) {
            $command->dropTable($toTableName)->execute();
        }

        $this->assertNotNull($db->getSchema()->getTableSchema($fromTableName));
        $this->assertNull($db->getSchema()->getTableSchema($toTableName));

        $sql = $qb->renameTable($fromTableName, $toTableName);
        $command->setSql($sql)->execute();

        $this->assertNull($db->getSchema()->getTableSchema($fromTableName, true));
        $this->assertNotNull($db->getSchema()->getTableSchema($toTableName, true));
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::update()
     */
    public function testUpdate(
        string $table,
        array $columns,
        array|string $condition,
        string $expectedSQL,
        array $expectedParams
    ): void {
        $db = $this->getConnection();

        $qb = $db->getQueryBuilder();
        $actualParams = [];

        $this->assertSame($expectedSQL, $qb->update($table, $columns, $condition, $actualParams));
        $this->assertSame($expectedParams, $actualParams);
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::upsert()
     *
     * @throws Exception
     * @throws JsonException
     * @throws NotSupportedException
     */
    public function testUpsert(
        string $table,
        array|QueryInterface $insertColumns,
        array|bool $updateColumns,
        string|array $expectedSQL,
        array $expectedParams
    ): void {
        $db = $this->getConnectionWithData();

        $actualParams = [];
        $actualSQL = $db->getQueryBuilder()->upsert($table, $insertColumns, $updateColumns, $actualParams);

        if (is_string($expectedSQL)) {

            $this->assertSame($expectedSQL, $actualSQL);
        } else {
            $this->assertContains($actualSQL, $expectedSQL);
        }

        if (ArrayHelper::isAssociative($expectedParams)) {
            $this->assertSame($expectedParams, $actualParams);
        } else {
            Assert::isOneOf($actualParams, $expectedParams);
        }
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace Yiisoft\Db\Tests\Common;
6
7			use Closure;
8			use JsonException;
9			use Throwable;
10			use Yiisoft\Arrays\ArrayHelper;
11			use Yiisoft\Db\Exception\Exception;
12			use Yiisoft\Db\Exception\IntegrityException;
13			use Yiisoft\Db\Exception\InvalidArgumentException;
14			use Yiisoft\Db\Exception\InvalidConfigException;
15			use Yiisoft\Db\Exception\NotSupportedException;
16			use Yiisoft\Db\Expression\ExpressionInterface;
17			use Yiisoft\Db\Query\QueryInterface;
18			use Yiisoft\Db\Schema\ColumnSchemaBuilder;
19			use Yiisoft\Db\Schema\Schema;
20			use Yiisoft\Db\Tests\AbstractQueryBuilderTest;
21			use Yiisoft\Db\Tests\Provider\ColumnTypesProvider;
22			use Yiisoft\Db\Tests\Support\Assert;
23			use Yiisoft\Db\Tests\Support\DbHelper;
24			use Yiisoft\Db\Tests\Support\TestTrait;
25
26			use function is_array;
27			use function str_replace;
28			use function str_starts_with;
29			use function strncmp;
30
31			/**
32			* @group mssql
33			* @group mysql
34			* @group pgsql
35			* @group oracle
36			* @group sqlite
37			*/
38			abstract class CommonQueryBuilderTest extends AbstractQueryBuilderTest
39			{
40			use TestTrait;
41
42			/**
43			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::addDropChecks()
44			*/
45			public function testAddDropCheck(string $sql, Closure $builder): void
46			{
47			$db = $this->getConnection();
48
49			$qb = $db->getQueryBuilder();
50
51			$this->assertSame($db->getQuoter()->quoteSql($sql), $builder($qb));
52			}
53
54			/**
55			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::addDropForeignKeys()
56			*/
57			public function testAddDropForeignKey(string $sql, Closure $builder): void
58			{
59			$db = $this->getConnection();
60
61			$qb = $db->getQueryBuilder();
62
63			$this->assertSame($db->getQuoter()->quoteSql($sql), $builder($qb));
64			}
65
66			/**
67			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::addDropPrimaryKeys()
68			*/
69			public function testAddDropPrimaryKey(string $sql, Closure $builder): void
70			{
71			$db = $this->getConnection();
72
73			$qb = $db->getQueryBuilder();
74
75			$this->assertSame($db->getQuoter()->quoteSql($sql), $builder($qb));
76			}
77
78			/**
79			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::addDropUniques()
80			*/
81			public function testAddDropUnique(string $sql, Closure $builder): void
82			{
83			$db = $this->getConnection();
84
85			$qb = $db->getQueryBuilder();
86
87			$this->assertSame($db->getQuoter()->quoteSql($sql), $builder($qb));
88			}
89
90			/**
91			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::alterColumn()
92			*/
93			public function testAlterColumn(
94			string $table,
95			string $column,
96			ColumnSchemaBuilder\|string $type,
97			string $expected
98			): void {
99			$db = $this->getConnection();
100
101			$qb = $db->getQueryBuilder();
102			$sql = $qb->alterColumn($table, $column, $type);
103
104			$this->assertSame($expected, $sql);
105			}
106
107			/**
108			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::batchInsert()
109			*/
110			public function testBatchInsert(
111			string $table,
112			array $columns,
113			array $value,
114			string\|null $expected,
115			array $expectedParams = []
116			): void {
117			$db = $this->getConnectionWithData();
118
119			$qb = $db->getQueryBuilder();
120			$params = [];
121			$sql = $qb->batchInsert($table, $columns, $value, $params);
122
123			$this->assertSame($expected, $sql);
124			$this->assertEquals($expectedParams, $params);
125			}
126
127			/**
128			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::buildConditions()
129			*/
130			public function testBuildCondition(
131			array\|ExpressionInterface\|string $conditions,
132			string $expected,
133			array $expectedParams = []
134			): void {
135			$db = $this->getConnection();
136
137			$qb = $db->getQueryBuilder();
138			$query = $this->getQuery($db)->where($conditions);
139
140			[$sql, $params] = $qb->build($query);
141
142			$this->assertSame(
143			'SELECT *' . (
144			empty($expected) ? '' : ' WHERE ' . DbHelper::replaceQuotes($expected, $db->getName())
145			),
146			$sql,
147			);
148			$this->assertSame($expectedParams, $params);
149			}
150
151			/**
152			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::buildFilterConditions()
153			*/
154			public function testBuildFilterCondition(array $condition, string $expected, array $expectedParams): void
155			{
156			$db = $this->getConnection();
157
158			$qb = $db->getQueryBuilder();
159			$query = $this->getQuery($db)->filterWhere($condition);
160
161			[$sql, $params] = $qb->build($query);
162
163			$this->assertSame(
164			'SELECT *' . (
165			empty($expected) ? '' : ' WHERE ' . DbHelper::replaceQuotes($expected, $db->getName())
166			),
167			$sql,
168			);
169			$this->assertSame($expectedParams, $params);
170			}
171
172			/**
173			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::buildFrom()
174			*/
175			public function testBuildFrom(string $table, string $expected): void
176			{
177			$db = $this->getConnection();
178
179			$qb = $db->getQueryBuilder();
180			$params = [];
181			$sql = $qb->buildFrom([$table], $params);
182			$replacedQuotes = DbHelper::replaceQuotes($expected, $db->getName());
183
184			$this->assertIsString($replacedQuotes);
185			$this->assertSame('FROM ' . $replacedQuotes, $sql);
186			}
187
188			/**
189			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::buildLikeConditions()
190			*
191			* @throws Exception
192			* @throws InvalidArgumentException
193			* @throws InvalidConfigException
194			* @throws NotSupportedException
195			*/
196			public function testBuildLikeCondition(
197			array\|ExpressionInterface $condition,
198			string $expected,
199			array $expectedParams
200			): void {
201			$db = $this->getConnection();
202
203			$query = $this->getQuery($db)->where($condition);
204
205			[$sql, $params] = $db->getQueryBuilder()->build($query);
206
207			$replacedQuotes = DbHelper::replaceQuotes($expected, $db->getName());
208
209			$this->assertIsString($replacedQuotes);
210			$this->assertEquals('SELECT *' . (empty($expected) ? '' : ' WHERE ' . $replacedQuotes), $sql);
211			$this->assertEquals($expectedParams, $params);
212			}
213
214			/**
215			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::buildWhereExists()
216			*/
217			public function testBuildWhereExists(string $cond, string $expectedQuerySql): void
218			{
219			$db = $this->getConnection();
220
221			$qb = $db->getQueryBuilder();
222			$expectedQueryParams = [];
223			$subQuery = $this->getQuery($db)->select('1')->from('Website w');
224			$query = $this->getQuery($db)->select('id')->from('TotalExample t')->where([$cond, $subQuery]);
225
226			[$actualQuerySql, $actualQueryParams] = $qb->build($query);
227
228			$this->assertSame($expectedQuerySql, $actualQuerySql);
229			$this->assertSame($expectedQueryParams, $actualQueryParams);
230			}
231
232			public function testCheckIntegrity(): void
233			{
234			$db = $this->getConnection();
235
236			$command = $db->createCommand();
237			$qb = $db->getQueryBuilder();
238			$sql = $qb->checkIntegrity('schema', 'table');
239
240			$this->assertSame(0, $command->setSql($sql)->execute());
241			}
242
243			public function testCheckIntegrityExecuteException(): void
244			{
245			$db = $this->getConnectionWithData();
246
247			$command = $db->createCommand();
248			$qb = $db->getQueryBuilder();
249			$schemaName = 'dbo';
250			$tableName = 'T_constraints_3';
251			$command->setSql($qb->checkIntegrity($schemaName, $tableName, false))->execute();
252			$command->setSql(
253			<<<SQL
254			INSERT INTO {{{$tableName}}} ([[C_id]], [[C_fk_id_1]], [[C_fk_id_2]]) VALUES (1, 2, 3)
255			SQL
256			)->execute();
257			$command->setSql($qb->checkIntegrity($schemaName, $tableName))->execute();
258
259			$this->expectException(IntegrityException::class);
260
261			$command->setSql(
262			<<<SQL
263			INSERT INTO {{{$tableName}}} ([[C_id]], [[C_fk_id_1]], [[C_fk_id_2]]) VALUES (1, 2, 3)
264			SQL
265			)->execute();
266			}
267
268			/**
269			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::createDropIndex()
270			*/
271			public function testCreateDropIndex(string $sql, Closure $builder): void
272			{
273			$db = $this->getConnection();
274
275			$qb = $db->getQueryBuilder();
276
277			$this->assertSame($db->getQuoter()->quoteSql($sql), $builder($qb));
278			}
279
280			/**
281			* @throws Exception
282			* @throws InvalidConfigException
283			* @throws Throwable
284			*/
285			public function testCreateTable(): void
286			{
287			$db = $this->getConnection();
288
289			$command = $db->createCommand();
290			$qb = $db->getQueryBuilder();
291
292			if ($db->getSchema()->getTableSchema('testCreateTable', true) !== null) {
293			$command->dropTable('testCreateTable')->execute();
294			}
295
296			$sql = $qb->createTable('testCreateTable', ['id' => Schema::TYPE_PK, 'bar' => Schema::TYPE_INTEGER]);
297			$command->setSql($sql)->execute();
298			$command->insert('testCreateTable', ['bar' => 1])->execute();
299			$records = $command->setSql(
300			<<<SQL
301			SELECT [[id]], [[bar]] FROM {{testCreateTable}};
302			SQL
303			)->queryAll();
304
305			$this->assertEquals([['id' => 1, 'bar' => 1]], $records);
306			}
307
308			public function testCreateTableColumnTypes(): void
309			{
310			$db = $this->getConnectionWithData();
311
312			$qb = $db->getQueryBuilder();
313
314			if ($db->getTableSchema('column_type_table', true) !== null) {
315			$db->createCommand($qb->dropTable('column_type_table'))->execute();
316			}
317
318			$columnTypes = (new ColumnTypesProvider())->columnTypes($db);
319			$columns = [];
320			$i = 0;
321
322			foreach ($columnTypes as [$column, $builder, $expected]) {
323			if (
324			!(
325			strncmp($column, Schema::TYPE_PK, 2) === 0 \|\|
326			strncmp($column, Schema::TYPE_UPK, 3) === 0 \|\|
327			strncmp($column, Schema::TYPE_BIGPK, 5) === 0 \|\|
328			strncmp($column, Schema::TYPE_UBIGPK, 6) === 0 \|\|
329			str_starts_with(substr($column, -5), 'FIRST')
330			)
331			) {
332			$columns['col' . ++$i] = str_replace('CHECK (value', 'CHECK ([[col' . $i . ']]', $column);
333			}
334			}
335
336			$db->createCommand($qb->createTable('column_type_table', $columns))->execute();
337
338			$this->assertNotEmpty($db->getTableSchema('column_type_table', true));
339			}
340
341			/**
342			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::delete()
343			*/
344			public function testDelete(string $table, array\|string $condition, string $expectedSQL, array $expectedParams): void
345			{
346			$db = $this->getConnection();
347
348			$qb = $db->getQueryBuilder();
349			$actualParams = [];
350			$actualSQL = $qb->delete($table, $condition, $actualParams);
351
352			$this->assertSame($expectedSQL, $actualSQL);
353			$this->assertSame($expectedParams, $actualParams);
354			}
355
356			public function testDropCommentFromColumn(): void
357			{
358			$db = $this->getConnectionWithData();
359
360			$command = $db->createCommand();
361			$qb = $db->getQueryBuilder();
362			$sql = $qb->addCommentOnColumn('customer', 'id', 'Primary key.');
363			$command->setSql($sql)->execute();
364			$commentOnColumn = DbHelper::getCommmentsFromColumn('customer', 'id', $db);
365
366			$this->assertSame('Primary key.', $commentOnColumn);
367
368			$sql = $qb->dropCommentFromColumn('customer', 'id');
369			$command->setSql($sql)->execute();
370			$commentOnColumn = DbHelper::getCommmentsFromColumn('customer', 'id', $db);
371
372			$this->assertSame([], $commentOnColumn);
373			}
374
375			public function testDropCommentFromTable(): void
376			{
377			$db = $this->getConnectionWithData();
378
379			$command = $db->createCommand();
380			$qb = $db->getQueryBuilder();
381			$sql = $qb->addCommentOnTable('customer', 'Customer table.');
382			$command->setSql($sql)->execute();
383			$commentOnTable = DbHelper::getCommmentsFromTable('customer', $db);
384
385			$this->assertSame('Customer table.', $commentOnTable);
386
387			$sql = $qb->dropCommentFromTable('customer');
388			$command->setSql($sql)->execute();
389			$commentOnTable = DbHelper::getCommmentsFromTable('customer', $db);
390
391			$this->assertSame([], $commentOnTable);
392			}
393
394			public function testGetColumnType(): void
395			{
396			$db = $this->getConnection();
397
398			$columnTypes = (new ColumnTypesProvider())->columnTypes($db);
399			$qb = $db->getQueryBuilder();
400
401			foreach ($columnTypes as $item) {
402			[$column, $builder, $expected] = $item;
403
404			$driverName = $db->getName();
405
406			if (isset($item[3][$driverName])) {
407			$expectedColumnSchemaBuilder = $item[3][$driverName];
408			} elseif (isset($item[3]) && !is_array($item[3])) {
409			$expectedColumnSchemaBuilder = $item[3];
410			} else {
411			$expectedColumnSchemaBuilder = $column;
412			}
413
414			$this->assertSame($expectedColumnSchemaBuilder, $builder->__toString());
415			$this->assertSame($expected, $qb->getColumnType($column));
416			$this->assertSame($expected, $qb->getColumnType($builder));
417			}
418			}
419
420			/**
421			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::insert()
422			*/
423			public function testInsert(
424			string $table,
425			array\|QueryInterface $columns,
426			array $params,
427			string $expectedSQL,
428			array $expectedParams
429			): void {
430			$db = $this->getConnection();
431
432			$qb = $db->getQueryBuilder();
433
434			$this->assertSame($expectedSQL, $qb->insert($table, $columns, $params));
435			$this->assertSame($expectedParams, $params);
436			}
437
438			/**
439			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::insertEx()
440			*/
441			public function testInsertEx(
442			string $table,
443			array\|QueryInterface $columns,
444			array $params,
445			string $expectedSQL,
446			array $expectedParams
447			): void {
448			$db = $this->getConnectionWithData();
449
450			$qb = $db->getQueryBuilder();
451
452			$this->assertSame($expectedSQL, $qb->insertEx($table, $columns, $params));
453			$this->assertSame($expectedParams, $params);
454			}
455
456			/**
457			* @throws Exception
458			* @throws InvalidConfigException
459			* @throws Throwable
460			*/
461			public function testRenameTable(): void
462			{
463			$db = $this->getConnectionWithData();
464
465			$fromTableName = 'type';
466			$toTableName = 'new_type';
467			$command = $db->createCommand();
468			$qb = $db->getQueryBuilder();
469
470			if ($db->getSchema()->getTableSchema($toTableName) !== null) {
471			$command->dropTable($toTableName)->execute();
472			}
473
474			$this->assertNotNull($db->getSchema()->getTableSchema($fromTableName));
475			$this->assertNull($db->getSchema()->getTableSchema($toTableName));
476
477			$sql = $qb->renameTable($fromTableName, $toTableName);
478			$command->setSql($sql)->execute();
479
480			$this->assertNull($db->getSchema()->getTableSchema($fromTableName, true));
481			$this->assertNotNull($db->getSchema()->getTableSchema($toTableName, true));
482			}
483
484			/**
485			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::update()
486			*/
487			public function testUpdate(
488			string $table,
489			array $columns,
490			array\|string $condition,
491			string $expectedSQL,
492			array $expectedParams
493			): void {
494			$db = $this->getConnection();
495
496			$qb = $db->getQueryBuilder();
497			$actualParams = [];
498
499			$this->assertSame($expectedSQL, $qb->update($table, $columns, $condition, $actualParams));
500			$this->assertSame($expectedParams, $actualParams);
501			}
502
503			/**
504			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::upsert()
505			*
506			* @throws Exception
507			* @throws JsonException
508			* @throws NotSupportedException
509			*/
510			public function testUpsert(
511			string $table,
512			array\|QueryInterface $insertColumns,
513			array\|bool $updateColumns,
514			string\|array $expectedSQL,
515			array $expectedParams
516			): void {
517			$db = $this->getConnectionWithData();
518
519			$actualParams = [];
520			$actualSQL = $db->getQueryBuilder()->upsert($table, $insertColumns, $updateColumns, $actualParams);
521
522			if (is_string($expectedSQL)) {
			0 ignored issues – show introduced 2022-11-15 20:41 UTC by Report Bug Copy Issue Report The condition `is_string($expectedSQL)` is always `false`. Loading history...
523			$this->assertSame($expectedSQL, $actualSQL);
524			} else {
525			$this->assertContains($actualSQL, $expectedSQL);
526			}
527
528			if (ArrayHelper::isAssociative($expectedParams)) {
529			$this->assertSame($expectedParams, $actualParams);
530			} else {
531			Assert::isOneOf($actualParams, $expectedParams);
532			}
533			}
534			}
535

yiisoft / db

Pull Request — master (#380)

testCreateTableColumnTypes() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like