CommonQueryBuilderTest::testBuildFilterCondition() - 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-22 10:28 UTC

CommonQueryBuilderTest::testBuildFilterCondition() A

↳ Parent: CommonQueryBuilderTest

Complexity

Conditions	2
Paths	1

Size

Total Lines	16
Code Lines	9

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	2
eloc	9
nc	1
nop	3
dl	0
loc	16
rs	9.9666
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 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 testGetColumnType(): void
357			{
358			$db = $this->getConnection();
359
360			$columnTypes = (new ColumnTypesProvider())->columnTypes($db);
361			$qb = $db->getQueryBuilder();
362
363			foreach ($columnTypes as $item) {
364			[$column, $builder, $expected] = $item;
365
366			$driverName = $db->getName();
367
368			if (isset($item[3][$driverName])) {
369			$expectedColumnSchemaBuilder = $item[3][$driverName];
370			} elseif (isset($item[3]) && !is_array($item[3])) {
371			$expectedColumnSchemaBuilder = $item[3];
372			} else {
373			$expectedColumnSchemaBuilder = $column;
374			}
375
376			$this->assertSame($expectedColumnSchemaBuilder, $builder->__toString());
377			$this->assertSame($expected, $qb->getColumnType($column));
378			$this->assertSame($expected, $qb->getColumnType($builder));
379			}
380			}
381
382			/**
383			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::insert()
384			*/
385			public function testInsert(
386			string $table,
387			array\|QueryInterface $columns,
388			array $params,
389			string $expectedSQL,
390			array $expectedParams
391			): void {
392			$db = $this->getConnection();
393
394			$qb = $db->getQueryBuilder();
395
396			$this->assertSame($expectedSQL, $qb->insert($table, $columns, $params));
397			$this->assertSame($expectedParams, $params);
398			}
399
400			/**
401			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::insertEx()
402			*/
403			public function testInsertEx(
404			string $table,
405			array\|QueryInterface $columns,
406			array $params,
407			string $expectedSQL,
408			array $expectedParams
409			): void {
410			$db = $this->getConnectionWithData();
411
412			$qb = $db->getQueryBuilder();
413
414			$this->assertSame($expectedSQL, $qb->insertEx($table, $columns, $params));
415			$this->assertSame($expectedParams, $params);
416			}
417
418			/**
419			* @throws Exception
420			* @throws InvalidConfigException
421			* @throws Throwable
422			*/
423			public function testRenameTable(): void
424			{
425			$db = $this->getConnectionWithData();
426
427			$fromTableName = 'type';
428			$toTableName = 'new_type';
429			$command = $db->createCommand();
430			$qb = $db->getQueryBuilder();
431
432			if ($db->getSchema()->getTableSchema($toTableName) !== null) {
433			$command->dropTable($toTableName)->execute();
434			}
435
436			$this->assertNotNull($db->getSchema()->getTableSchema($fromTableName));
437			$this->assertNull($db->getSchema()->getTableSchema($toTableName));
438
439			$sql = $qb->renameTable($fromTableName, $toTableName);
440			$command->setSql($sql)->execute();
441
442			$this->assertNull($db->getSchema()->getTableSchema($fromTableName, true));
443			$this->assertNotNull($db->getSchema()->getTableSchema($toTableName, true));
444			}
445
446			/**
447			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::update()
448			*/
449			public function testUpdate(
450			string $table,
451			array $columns,
452			array\|string $condition,
453			string $expectedSQL,
454			array $expectedParams
455			): void {
456			$db = $this->getConnection();
457
458			$qb = $db->getQueryBuilder();
459			$actualParams = [];
460
461			$this->assertSame($expectedSQL, $qb->update($table, $columns, $condition, $actualParams));
462			$this->assertSame($expectedParams, $actualParams);
463			}
464
465			/**
466			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::upsert()
467			*
468			* @throws Exception
469			* @throws JsonException
470			* @throws NotSupportedException
471			*/
472			public function testUpsert(
473			string $table,
474			array\|QueryInterface $insertColumns,
475			array\|bool $updateColumns,
476			string\|array $expectedSQL,
477			array $expectedParams
478			): void {
479			$db = $this->getConnectionWithData();
480
481			$actualParams = [];
482			$actualSQL = $db->getQueryBuilder()->upsert($table, $insertColumns, $updateColumns, $actualParams);
483
484			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...
485			$this->assertSame($expectedSQL, $actualSQL);
486			} else {
487			$this->assertContains($actualSQL, $expectedSQL);
488			}
489
490			if (ArrayHelper::isAssociative($expectedParams)) {
491			$this->assertSame($expectedParams, $actualParams);
492			} else {
493			Assert::isOneOf($actualParams, $expectedParams);
494			}
495			}
496			}
497

yiisoft / db

Pull Request — master (#380)

CommonQueryBuilderTest::testBuildFilterCondition() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like