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-18 13:14 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;

    public function testAddCommentOnColumn(): 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);
    }

    public function testAddCommentOnTable(): 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);
    }

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

yiisoft / db

Pull Request — master (#380)

testCreateTableColumnTypes() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like