QueryBuilderTest.php 5.94 KB
Newer Older
1 2 3 4 5 6 7 8 9
<?php

namespace yiiunit\framework\db;

use yii\db\QueryBuilder;
use yii\db\Schema;
use yii\db\mysql\QueryBuilder as MysqlQueryBuilder;
use yii\db\sqlite\QueryBuilder as SqliteQueryBuilder;
use yii\db\mssql\QueryBuilder as MssqlQueryBuilder;
10
use yii\db\pgsql\QueryBuilder as PgsqlQueryBuilder;
Carsten Brandt committed
11
use yii\db\cubrid\QueryBuilder as CubridQueryBuilder;
12

13 14 15 16
/**
 * @group db
 * @group mysql
 */
17 18 19 20 21 22 23 24
class QueryBuilderTest extends DatabaseTestCase
{
	/**
	 * @throws \Exception
	 * @return QueryBuilder
	 */
	protected function getQueryBuilder()
	{
25
		switch ($this->driverName) {
26 27 28 29 30 31
			case 'mysql':
				return new MysqlQueryBuilder($this->getConnection());
			case 'sqlite':
				return new SqliteQueryBuilder($this->getConnection());
			case 'mssql':
				return new MssqlQueryBuilder($this->getConnection());
32 33
			case 'pgsql':
				return new PgsqlQueryBuilder($this->getConnection());
Carsten Brandt committed
34 35
			case 'cubrid':
				return new CubridQueryBuilder($this->getConnection());
36 37 38 39 40 41 42 43 44 45
		}
		throw new \Exception('Test is not implemented for ' . $this->driverName);
	}

	/**
	 * this is not used as a dataprovider for testGetColumnType to speed up the test
	 * when used as dataprovider every single line will cause a reconnect with the database which is not needed here
	 */
	public function columnTypes()
	{
Alexander Makarov committed
46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104
		return [
			[Schema::TYPE_PK, 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY'],
			[Schema::TYPE_PK . '(8)', 'int(8) NOT NULL AUTO_INCREMENT PRIMARY KEY'],
			[Schema::TYPE_PK . ' CHECK (value > 5)', 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY CHECK (value > 5)'],
			[Schema::TYPE_PK . '(8) CHECK (value > 5)', 'int(8) NOT NULL AUTO_INCREMENT PRIMARY KEY CHECK (value > 5)'],
			[Schema::TYPE_STRING, 'varchar(255)'],
			[Schema::TYPE_STRING . '(32)', 'varchar(32)'],
			[Schema::TYPE_STRING . ' CHECK (value LIKE "test%")', 'varchar(255) CHECK (value LIKE "test%")'],
			[Schema::TYPE_STRING . '(32) CHECK (value LIKE "test%")', 'varchar(32) CHECK (value LIKE "test%")'],
			[Schema::TYPE_STRING . ' NOT NULL', 'varchar(255) NOT NULL'],
			[Schema::TYPE_TEXT, 'text'],
			[Schema::TYPE_TEXT . '(255)', 'text'],
			[Schema::TYPE_TEXT . ' CHECK (value LIKE "test%")', 'text CHECK (value LIKE "test%")'],
			[Schema::TYPE_TEXT . '(255) CHECK (value LIKE "test%")', 'text CHECK (value LIKE "test%")'],
			[Schema::TYPE_TEXT . ' NOT NULL', 'text NOT NULL'],
			[Schema::TYPE_TEXT . '(255) NOT NULL', 'text NOT NULL'],
			[Schema::TYPE_SMALLINT, 'smallint(6)'],
			[Schema::TYPE_SMALLINT . '(8)', 'smallint(8)'],
			[Schema::TYPE_INTEGER, 'int(11)'],
			[Schema::TYPE_INTEGER . '(8)', 'int(8)'],
			[Schema::TYPE_INTEGER . ' CHECK (value > 5)', 'int(11) CHECK (value > 5)'],
			[Schema::TYPE_INTEGER . '(8) CHECK (value > 5)', 'int(8) CHECK (value > 5)'],
			[Schema::TYPE_INTEGER . ' NOT NULL', 'int(11) NOT NULL'],
			[Schema::TYPE_BIGINT, 'bigint(20)'],
			[Schema::TYPE_BIGINT . '(8)', 'bigint(8)'],
			[Schema::TYPE_BIGINT . ' CHECK (value > 5)', 'bigint(20) CHECK (value > 5)'],
			[Schema::TYPE_BIGINT . '(8) CHECK (value > 5)', 'bigint(8) CHECK (value > 5)'],
			[Schema::TYPE_BIGINT . ' NOT NULL', 'bigint(20) NOT NULL'],
			[Schema::TYPE_FLOAT, 'float'],
			[Schema::TYPE_FLOAT . '(16,5)', 'float'],
			[Schema::TYPE_FLOAT . ' CHECK (value > 5.6)', 'float CHECK (value > 5.6)'],
			[Schema::TYPE_FLOAT . '(16,5) CHECK (value > 5.6)', 'float CHECK (value > 5.6)'],
			[Schema::TYPE_FLOAT . ' NOT NULL', 'float NOT NULL'],
			[Schema::TYPE_DECIMAL, 'decimal(10,0)'],
			[Schema::TYPE_DECIMAL . '(12,4)', 'decimal(12,4)'],
			[Schema::TYPE_DECIMAL . ' CHECK (value > 5.6)', 'decimal(10,0) CHECK (value > 5.6)'],
			[Schema::TYPE_DECIMAL . '(12,4) CHECK (value > 5.6)', 'decimal(12,4) CHECK (value > 5.6)'],
			[Schema::TYPE_DECIMAL . ' NOT NULL', 'decimal(10,0) NOT NULL'],
			[Schema::TYPE_DATETIME, 'datetime'],
			[Schema::TYPE_DATETIME . " CHECK(value BETWEEN '2011-01-01' AND '2013-01-01')", "datetime CHECK(value BETWEEN '2011-01-01' AND '2013-01-01')"],
			[Schema::TYPE_DATETIME . ' NOT NULL', 'datetime NOT NULL'],
			[Schema::TYPE_TIMESTAMP, 'timestamp'],
			[Schema::TYPE_TIMESTAMP . " CHECK(value BETWEEN '2011-01-01' AND '2013-01-01')", "timestamp CHECK(value BETWEEN '2011-01-01' AND '2013-01-01')"],
			[Schema::TYPE_TIMESTAMP . ' NOT NULL', 'timestamp NOT NULL'],
			[Schema::TYPE_TIME, 'time'],
			[Schema::TYPE_TIME . " CHECK(value BETWEEN '12:00:00' AND '13:01:01')", "time CHECK(value BETWEEN '12:00:00' AND '13:01:01')"],
			[Schema::TYPE_TIME . ' NOT NULL', 'time NOT NULL'],
			[Schema::TYPE_DATE, 'date'],
			[Schema::TYPE_DATE . " CHECK(value BETWEEN '2011-01-01' AND '2013-01-01')", "date CHECK(value BETWEEN '2011-01-01' AND '2013-01-01')"],
			[Schema::TYPE_DATE . ' NOT NULL', 'date NOT NULL'],
			[Schema::TYPE_BINARY, 'blob'],
			[Schema::TYPE_BOOLEAN, 'tinyint(1)'],
			[Schema::TYPE_BOOLEAN . ' NOT NULL DEFAULT 1', 'tinyint(1) NOT NULL DEFAULT 1'],
			[Schema::TYPE_MONEY, 'decimal(19,4)'],
			[Schema::TYPE_MONEY . '(16,2)', 'decimal(16,2)'],
			[Schema::TYPE_MONEY . ' CHECK (value > 0.0)', 'decimal(19,4) CHECK (value > 0.0)'],
			[Schema::TYPE_MONEY . '(16,2) CHECK (value > 0.0)', 'decimal(16,2) CHECK (value > 0.0)'],
			[Schema::TYPE_MONEY . ' NOT NULL', 'decimal(19,4) NOT NULL'],
		];
105 106 107 108 109
	}

	public function testGetColumnType()
	{
		$qb = $this->getQueryBuilder();
110
		foreach ($this->columnTypes() as $item) {
111 112 113 114
			list ($column, $expected) = $item;
			$this->assertEquals($expected, $qb->getColumnType($column));
		}
	}
115

116
	public function testAddDropPrimaryKey()
117 118 119 120 121 122
	{
		$tableName = 'tbl_constraints';
		$pkeyName = $tableName . "_pkey";
		
		// ADD
		$qb = $this->getQueryBuilder();
Alexander Makarov committed
123
		$qb->db->createCommand()->addPrimaryKey($pkeyName, $tableName, ['id'])->execute();
124 125 126 127
		$tableSchema = $qb->db->getSchema()->getTableSchema($tableName);
		$this->assertEquals(1, count($tableSchema->primaryKey));

		//DROP
128
		$qb->db->createCommand()->dropPrimaryKey($pkeyName, $tableName)->execute();
129 130
		$qb = $this->getQueryBuilder(); // resets the schema
		$tableSchema = $qb->db->getSchema()->getTableSchema($tableName);
Alexander Makarov committed
131
		$this->assertEquals(0, count($tableSchema->primaryKey));
132
	}
133
}