CommandTest.php 7.08 KB
Newer Older
w  
Qiang Xue committed
1 2
<?php

Qiang Xue committed
3
namespace yiiunit\framework\db;
Qiang Xue committed
4

Qiang Xue committed
5 6 7 8
use yii\db\Connection;
use yii\db\Command;
use yii\db\Query;
use yii\db\DataReader;
w  
Qiang Xue committed
9

Alexander Makarov committed
10
class CommandTest extends DatabaseTestCase
w  
Qiang Xue committed
11 12 13
{
	function testConstruct()
	{
Qiang Xue committed
14 15
		$db = $this->getConnection(false);

Qiang Xue committed
16
		// null
Qiang Xue committed
17
		$command = $db->createCommand();
Qiang Xue committed
18
		$this->assertEquals(null, $command->sql);
w  
Qiang Xue committed
19

Qiang Xue committed
20
		// string
Qiang Xue committed
21
		$sql = 'SELECT * FROM tbl_customer';
Qiang Xue committed
22
		$command = $db->createCommand($sql);
w  
Qiang Xue committed
23 24 25 26 27
		$this->assertEquals($sql, $command->sql);
	}

	function testGetSetSql()
	{
Qiang Xue committed
28
		$db = $this->getConnection(false);
w  
Qiang Xue committed
29

Qiang Xue committed
30
		$sql = 'SELECT * FROM tbl_customer';
Qiang Xue committed
31 32
		$command = $db->createCommand($sql);
		$this->assertEquals($sql, $command->sql);
w  
Qiang Xue committed
33

Qiang Xue committed
34
		$sql2 = 'SELECT * FROM tbl_order';
Qiang Xue committed
35 36
		$command->sql = $sql2;
		$this->assertEquals($sql2, $command->sql);
w  
Qiang Xue committed
37 38
	}

39 40 41 42 43 44 45 46 47
	function testAutoQuoting()
	{
		$db = $this->getConnection(false);

		$sql = 'SELECT [[id]], [[t.name]] FROM {{tbl_customer}} t';
		$command = $db->createCommand($sql);
		$this->assertEquals("SELECT `id`, `t`.`name` FROM `tbl_customer` t", $command->sql);
	}

Qiang Xue committed
48
	function testPrepareCancel()
w  
Qiang Xue committed
49
	{
Qiang Xue committed
50
		$db = $this->getConnection(false);
w  
Qiang Xue committed
51

Qiang Xue committed
52
		$command = $db->createCommand('SELECT * FROM tbl_customer');
Qiang Xue committed
53 54 55 56 57
		$this->assertEquals(null, $command->pdoStatement);
		$command->prepare();
		$this->assertNotEquals(null, $command->pdoStatement);
		$command->cancel();
		$this->assertEquals(null, $command->pdoStatement);
w  
Qiang Xue committed
58 59 60 61
	}

	function testExecute()
	{
Qiang Xue committed
62
		$db = $this->getConnection();
w  
Qiang Xue committed
63

Qiang Xue committed
64
		$sql = 'INSERT INTO tbl_customer(email, name , address) VALUES (\'user4@example.com\', \'user4\', \'address4\')';
Qiang Xue committed
65 66
		$command = $db->createCommand($sql);
		$this->assertEquals(1, $command->execute());
w  
Qiang Xue committed
67

Qiang Xue committed
68
		$sql = 'SELECT COUNT(*) FROM tbl_customer WHERE name =\'user4\'';
Qiang Xue committed
69 70
		$command = $db->createCommand($sql);
		$this->assertEquals(1, $command->queryScalar());
w  
Qiang Xue committed
71

Qiang Xue committed
72 73 74
		$command = $db->createCommand('bad SQL');
		$this->setExpectedException('\yii\db\Exception');
		$command->execute();
w  
Qiang Xue committed
75 76
	}

Qiang Xue committed
77
	function testQuery()
w  
Qiang Xue committed
78
	{
Qiang Xue committed
79
		$db = $this->getConnection();
w  
Qiang Xue committed
80

Qiang Xue committed
81
		// query
Qiang Xue committed
82
		$sql = 'SELECT * FROM tbl_customer';
Qiang Xue committed
83 84
		$reader = $db->createCommand($sql)->query();
		$this->assertTrue($reader instanceof DataReader);
w  
Qiang Xue committed
85

Qiang Xue committed
86
		// queryAll
Qiang Xue committed
87 88
		$rows = $db->createCommand('SELECT * FROM tbl_customer')->queryAll();
		$this->assertEquals(3, count($rows));
Qiang Xue committed
89 90
		$row = $rows[2];
		$this->assertEquals(3, $row['id']);
Qiang Xue committed
91
		$this->assertEquals('user3', $row['name']);
w  
Qiang Xue committed
92

Qiang Xue committed
93
		$rows = $db->createCommand('SELECT * FROM tbl_customer WHERE id=10')->queryAll();
Qiang Xue committed
94
		$this->assertEquals(array(), $rows);
w  
Qiang Xue committed
95

Qiang Xue committed
96
		// queryRow
Qiang Xue committed
97
		$sql = 'SELECT * FROM tbl_customer ORDER BY id';
Qiang Xue committed
98 99
		$row = $db->createCommand($sql)->queryRow();
		$this->assertEquals(1, $row['id']);
Qiang Xue committed
100
		$this->assertEquals('user1', $row['name']);
w  
Qiang Xue committed
101

Qiang Xue committed
102
		$sql = 'SELECT * FROM tbl_customer ORDER BY id';
Qiang Xue committed
103
		$command = $db->createCommand($sql);
w  
Qiang Xue committed
104
		$command->prepare();
Qiang Xue committed
105 106
		$row = $command->queryRow();
		$this->assertEquals(1, $row['id']);
Qiang Xue committed
107
		$this->assertEquals('user1', $row['name']);
w  
Qiang Xue committed
108

Qiang Xue committed
109
		$sql = 'SELECT * FROM tbl_customer WHERE id=10';
Qiang Xue committed
110 111
		$command = $db->createCommand($sql);
		$this->assertFalse($command->queryRow());
w  
Qiang Xue committed
112

Qiang Xue committed
113
		// queryColumn
Qiang Xue committed
114
		$sql = 'SELECT * FROM tbl_customer';
Qiang Xue committed
115
		$column = $db->createCommand($sql)->queryColumn();
Qiang Xue committed
116
		$this->assertEquals(range(1, 3), $column);
w  
Qiang Xue committed
117

Qiang Xue committed
118
		$command = $db->createCommand('SELECT id FROM tbl_customer WHERE id=10');
Qiang Xue committed
119
		$this->assertEquals(array(), $command->queryColumn());
w  
Qiang Xue committed
120

Qiang Xue committed
121
		// queryScalar
Qiang Xue committed
122
		$sql = 'SELECT * FROM tbl_customer ORDER BY id';
Qiang Xue committed
123
		$this->assertEquals($db->createCommand($sql)->queryScalar(), 1);
w  
Qiang Xue committed
124

Qiang Xue committed
125
		$sql = 'SELECT id FROM tbl_customer ORDER BY id';
Qiang Xue committed
126
		$command = $db->createCommand($sql);
w  
Qiang Xue committed
127
		$command->prepare();
Qiang Xue committed
128 129
		$this->assertEquals(1, $command->queryScalar());

Qiang Xue committed
130
		$command = $db->createCommand('SELECT id FROM tbl_customer WHERE id=10');
Qiang Xue committed
131
		$this->assertFalse($command->queryScalar());
w  
Qiang Xue committed
132

Qiang Xue committed
133 134
		$command = $db->createCommand('bad SQL');
		$this->setExpectedException('\yii\db\Exception');
w  
Qiang Xue committed
135 136 137
		$command->query();
	}

Qiang Xue committed
138
	function testBindParamValue()
w  
Qiang Xue committed
139
	{
Qiang Xue committed
140
		$db = $this->getConnection();
w  
Qiang Xue committed
141

Qiang Xue committed
142
		// bindParam
resurtm committed
143
		$sql = 'INSERT INTO tbl_customer(email, name, address) VALUES (:email, :name, :address)';
Qiang Xue committed
144 145 146 147 148 149 150
		$command = $db->createCommand($sql);
		$email = 'user4@example.com';
		$name = 'user4';
		$address = 'address4';
		$command->bindParam(':email', $email);
		$command->bindParam(':name', $name);
		$command->bindParam(':address', $address);
w  
Qiang Xue committed
151 152
		$command->execute();

Qiang Xue committed
153
		$sql = 'SELECT name FROM tbl_customer WHERE email=:email';
Qiang Xue committed
154
		$command = $db->createCommand($sql);
Qiang Xue committed
155 156
		$command->bindParam(':email', $email);
		$this->assertEquals($name, $command->queryScalar());
w  
Qiang Xue committed
157

Qiang Xue committed
158
		$sql = 'INSERT INTO tbl_type (int_col, char_col, float_col, blob_col, numeric_col, bool_col) VALUES (:int_col, :char_col, :float_col, :blob_col, :numeric_col, :bool_col)';
Qiang Xue committed
159 160 161 162 163 164 165 166 167 168 169 170 171 172 173
		$command = $db->createCommand($sql);
		$intCol = 123;
		$charCol = 'abc';
		$floatCol = 1.23;
		$blobCol = "\x10\x11\x12";
		$numericCol = '1.23';
		$boolCol = false;
		$command->bindParam(':int_col', $intCol);
		$command->bindParam(':char_col', $charCol);
		$command->bindParam(':float_col', $floatCol);
		$command->bindParam(':blob_col', $blobCol);
		$command->bindParam(':numeric_col', $numericCol);
		$command->bindParam(':bool_col', $boolCol);
		$this->assertEquals(1, $command->execute());

Qiang Xue committed
174
		$sql = 'SELECT * FROM tbl_type';
Qiang Xue committed
175 176 177 178 179 180 181 182
		$row = $db->createCommand($sql)->queryRow();
		$this->assertEquals($intCol, $row['int_col']);
		$this->assertEquals($charCol, $row['char_col']);
		$this->assertEquals($floatCol, $row['float_col']);
		$this->assertEquals($blobCol, $row['blob_col']);
		$this->assertEquals($numericCol, $row['numeric_col']);

		// bindValue
Qiang Xue committed
183
		$sql = 'INSERT INTO tbl_customer(email, name, address) VALUES (:email, \'user5\', \'address5\')';
Qiang Xue committed
184
		$command = $db->createCommand($sql);
Qiang Xue committed
185
		$command->bindValue(':email', 'user5@example.com');
Qiang Xue committed
186
		$command->execute();
w  
Qiang Xue committed
187

Qiang Xue committed
188
		$sql = 'SELECT email FROM tbl_customer WHERE name=:name';
Qiang Xue committed
189
		$command = $db->createCommand($sql);
Qiang Xue committed
190 191
		$command->bindValue(':name', 'user5');
		$this->assertEquals('user5@example.com', $command->queryScalar());
w  
Qiang Xue committed
192 193
	}

Qiang Xue committed
194
	function testFetchMode()
w  
Qiang Xue committed
195
	{
Qiang Xue committed
196
		$db = $this->getConnection();
w  
Qiang Xue committed
197

Qiang Xue committed
198
		// default: FETCH_ASSOC
Qiang Xue committed
199
		$sql = 'SELECT * FROM tbl_customer';
Qiang Xue committed
200
		$command = $db->createCommand($sql);
w  
Qiang Xue committed
201
		$result = $command->queryRow();
Qiang Xue committed
202
		$this->assertTrue(is_array($result) && isset($result['id']));
w  
Qiang Xue committed
203

Qiang Xue committed
204
		// FETCH_OBJ, customized via fetchMode property
Qiang Xue committed
205
		$sql = 'SELECT * FROM tbl_customer';
Qiang Xue committed
206 207
		$command = $db->createCommand($sql);
		$command->fetchMode = \PDO::FETCH_OBJ;
w  
Qiang Xue committed
208 209
		$result = $command->queryRow();
		$this->assertTrue(is_object($result));
Qiang Xue committed
210 211

		// FETCH_NUM, customized in query method
Qiang Xue committed
212
		$sql = 'SELECT * FROM tbl_customer';
Qiang Xue committed
213 214 215
		$command = $db->createCommand($sql);
		$result = $command->queryRow(array(), \PDO::FETCH_NUM);
		$this->assertTrue(is_array($result) && isset($result[0]));
w  
Qiang Xue committed
216
	}
Qiang Xue committed
217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291

	function testInsert()
	{

	}

	function testUpdate()
	{

	}

	function testDelete()
	{

	}

	function testCreateTable()
	{

	}

	function testRenameTable()
	{

	}

	function testDropTable()
	{

	}

	function testTruncateTable()
	{

	}

	function testAddColumn()
	{

	}

	function testDropColumn()
	{

	}

	function testRenameColumn()
	{

	}

	function testAlterColumn()
	{

	}

	function testAddForeignKey()
	{

	}

	function testDropForeignKey()
	{

	}

	function testCreateIndex()
	{

	}

	function testDropIndex()
	{

	}
Zander Baldwin committed
292
}