CommandTest.php 7.26 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

10 11 12 13
/**
 * @group db
 * @group mysql
 */
Alexander Makarov committed
14
class CommandTest extends DatabaseTestCase
w  
Qiang Xue committed
15
{
Alexander Makarov committed
16
	public function testConstruct()
w  
Qiang Xue committed
17
	{
Qiang Xue committed
18 19
		$db = $this->getConnection(false);

Qiang Xue committed
20
		// null
Qiang Xue committed
21
		$command = $db->createCommand();
Qiang Xue committed
22
		$this->assertEquals(null, $command->sql);
w  
Qiang Xue committed
23

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

Alexander Makarov committed
30
	public function testGetSetSql()
w  
Qiang Xue committed
31
	{
Qiang Xue committed
32
		$db = $this->getConnection(false);
w  
Qiang Xue committed
33

Qiang Xue committed
34
		$sql = 'SELECT * FROM tbl_customer';
Qiang Xue committed
35 36
		$command = $db->createCommand($sql);
		$this->assertEquals($sql, $command->sql);
w  
Qiang Xue committed
37

Qiang Xue committed
38
		$sql2 = 'SELECT * FROM tbl_order';
Qiang Xue committed
39 40
		$command->sql = $sql2;
		$this->assertEquals($sql2, $command->sql);
w  
Qiang Xue committed
41 42
	}

Alexander Makarov committed
43
	public function testAutoQuoting()
44 45 46 47 48 49 50 51
	{
		$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);
	}

Alexander Makarov committed
52
	public function testPrepareCancel()
w  
Qiang Xue committed
53
	{
Qiang Xue committed
54
		$db = $this->getConnection(false);
w  
Qiang Xue committed
55

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

Alexander Makarov committed
64
	public function testExecute()
w  
Qiang Xue committed
65
	{
Qiang Xue committed
66
		$db = $this->getConnection();
w  
Qiang Xue committed
67

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

Qiang Xue committed
72
		$sql = 'SELECT COUNT(*) FROM tbl_customer WHERE name =\'user4\'';
Qiang Xue committed
73 74
		$command = $db->createCommand($sql);
		$this->assertEquals(1, $command->queryScalar());
w  
Qiang Xue committed
75

Qiang Xue committed
76 77 78
		$command = $db->createCommand('bad SQL');
		$this->setExpectedException('\yii\db\Exception');
		$command->execute();
w  
Qiang Xue committed
79 80
	}

Alexander Makarov committed
81
	public function testQuery()
w  
Qiang Xue committed
82
	{
Qiang Xue committed
83
		$db = $this->getConnection();
w  
Qiang Xue committed
84

Qiang Xue committed
85
		// query
Qiang Xue committed
86
		$sql = 'SELECT * FROM tbl_customer';
Qiang Xue committed
87 88
		$reader = $db->createCommand($sql)->query();
		$this->assertTrue($reader instanceof DataReader);
w  
Qiang Xue committed
89

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

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

100
		// queryOne
Qiang Xue committed
101
		$sql = 'SELECT * FROM tbl_customer ORDER BY id';
102
		$row = $db->createCommand($sql)->queryOne();
Qiang Xue committed
103
		$this->assertEquals(1, $row['id']);
Qiang Xue committed
104
		$this->assertEquals('user1', $row['name']);
w  
Qiang Xue committed
105

Qiang Xue committed
106
		$sql = 'SELECT * FROM tbl_customer ORDER BY id';
Qiang Xue committed
107
		$command = $db->createCommand($sql);
w  
Qiang Xue committed
108
		$command->prepare();
109
		$row = $command->queryOne();
Qiang Xue committed
110
		$this->assertEquals(1, $row['id']);
Qiang Xue committed
111
		$this->assertEquals('user1', $row['name']);
w  
Qiang Xue committed
112

Qiang Xue committed
113
		$sql = 'SELECT * FROM tbl_customer WHERE id=10';
Qiang Xue committed
114
		$command = $db->createCommand($sql);
115
		$this->assertFalse($command->queryOne());
w  
Qiang Xue committed
116

Qiang Xue committed
117
		// queryColumn
Qiang Xue committed
118
		$sql = 'SELECT * FROM tbl_customer';
Qiang Xue committed
119
		$column = $db->createCommand($sql)->queryColumn();
Qiang Xue committed
120
		$this->assertEquals(range(1, 3), $column);
w  
Qiang Xue committed
121

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

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

Qiang Xue committed
129
		$sql = 'SELECT id FROM tbl_customer ORDER BY id';
Qiang Xue committed
130
		$command = $db->createCommand($sql);
w  
Qiang Xue committed
131
		$command->prepare();
Qiang Xue committed
132 133
		$this->assertEquals(1, $command->queryScalar());

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

Qiang Xue committed
137 138
		$command = $db->createCommand('bad SQL');
		$this->setExpectedException('\yii\db\Exception');
w  
Qiang Xue committed
139 140 141
		$command->query();
	}

Alexander Makarov committed
142
	public function testBindParamValue()
w  
Qiang Xue committed
143
	{
Qiang Xue committed
144
		$db = $this->getConnection();
w  
Qiang Xue committed
145

Qiang Xue committed
146
		// bindParam
resurtm committed
147
		$sql = 'INSERT INTO tbl_customer(email, name, address) VALUES (:email, :name, :address)';
Qiang Xue committed
148 149 150 151 152 153 154
		$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
155 156
		$command->execute();

Qiang Xue committed
157
		$sql = 'SELECT name FROM tbl_customer WHERE email=:email';
Qiang Xue committed
158
		$command = $db->createCommand($sql);
Qiang Xue committed
159 160
		$command->bindParam(':email', $email);
		$this->assertEquals($name, $command->queryScalar());
w  
Qiang Xue committed
161

Qiang Xue committed
162
		$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
163 164 165 166 167 168 169 170 171 172 173 174 175 176 177
		$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
178
		$sql = 'SELECT * FROM tbl_type';
179
		$row = $db->createCommand($sql)->queryOne();
Qiang Xue committed
180 181 182 183 184 185 186
		$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
187
		$sql = 'INSERT INTO tbl_customer(email, name, address) VALUES (:email, \'user5\', \'address5\')';
Qiang Xue committed
188
		$command = $db->createCommand($sql);
Qiang Xue committed
189
		$command->bindValue(':email', 'user5@example.com');
Qiang Xue committed
190
		$command->execute();
w  
Qiang Xue committed
191

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

Alexander Makarov committed
198
	public function testFetchMode()
w  
Qiang Xue committed
199
	{
Qiang Xue committed
200
		$db = $this->getConnection();
w  
Qiang Xue committed
201

Qiang Xue committed
202
		// default: FETCH_ASSOC
Qiang Xue committed
203
		$sql = 'SELECT * FROM tbl_customer';
Qiang Xue committed
204
		$command = $db->createCommand($sql);
205
		$result = $command->queryOne();
Qiang Xue committed
206
		$this->assertTrue(is_array($result) && isset($result['id']));
w  
Qiang Xue committed
207

Qiang Xue committed
208
		// FETCH_OBJ, customized via fetchMode property
Qiang Xue committed
209
		$sql = 'SELECT * FROM tbl_customer';
Qiang Xue committed
210 211
		$command = $db->createCommand($sql);
		$command->fetchMode = \PDO::FETCH_OBJ;
212
		$result = $command->queryOne();
w  
Qiang Xue committed
213
		$this->assertTrue(is_object($result));
Qiang Xue committed
214 215

		// FETCH_NUM, customized in query method
Qiang Xue committed
216
		$sql = 'SELECT * FROM tbl_customer';
Qiang Xue committed
217
		$command = $db->createCommand($sql);
218
		$result = $command->queryOne(array(), \PDO::FETCH_NUM);
Qiang Xue committed
219
		$this->assertTrue(is_array($result) && isset($result[0]));
w  
Qiang Xue committed
220
	}
Qiang Xue committed
221

Alexander Makarov committed
222
	public function testInsert()
Qiang Xue committed
223 224 225
	{
	}

Alexander Makarov committed
226
	public function testUpdate()
Qiang Xue committed
227 228 229
	{
	}

Alexander Makarov committed
230
	public function testDelete()
Qiang Xue committed
231 232 233
	{
	}

Alexander Makarov committed
234
	public function testCreateTable()
Qiang Xue committed
235 236 237
	{
	}

Alexander Makarov committed
238
	public function testRenameTable()
Qiang Xue committed
239 240 241
	{
	}

Alexander Makarov committed
242
	public function testDropTable()
Qiang Xue committed
243 244 245
	{
	}

Alexander Makarov committed
246
	public function testTruncateTable()
Qiang Xue committed
247 248 249
	{
	}

Alexander Makarov committed
250
	public function testAddColumn()
Qiang Xue committed
251 252 253
	{
	}

Alexander Makarov committed
254
	public function testDropColumn()
Qiang Xue committed
255 256 257
	{
	}

Alexander Makarov committed
258
	public function testRenameColumn()
Qiang Xue committed
259 260 261
	{
	}

Alexander Makarov committed
262
	public function testAlterColumn()
Qiang Xue committed
263 264 265
	{
	}

Alexander Makarov committed
266
	public function testAddForeignKey()
Qiang Xue committed
267 268 269
	{
	}

Alexander Makarov committed
270
	public function testDropForeignKey()
Qiang Xue committed
271 272 273
	{
	}

Alexander Makarov committed
274
	public function testCreateIndex()
Qiang Xue committed
275 276 277
	{
	}

Alexander Makarov committed
278
	public function testDropIndex()
Qiang Xue committed
279 280
	{
	}
Zander Baldwin committed
281
}