1 | <?php |
||
2 | |||
3 | namespace Doctrine\Tests\DBAL\Functional; |
||
4 | |||
5 | use Doctrine\DBAL\Connection; |
||
6 | use Doctrine\DBAL\Platforms\AbstractPlatform; |
||
7 | use Doctrine\DBAL\Types\Type; |
||
8 | use PDO; |
||
9 | |||
10 | class DataAccessTest extends \Doctrine\Tests\DbalFunctionalTestCase |
||
11 | { |
||
12 | static private $generated = false; |
||
13 | |||
14 | protected function setUp() |
||
15 | { |
||
16 | parent::setUp(); |
||
17 | |||
18 | if (self::$generated === false) { |
||
19 | /* @var $sm \Doctrine\DBAL\Schema\AbstractSchemaManager */ |
||
20 | $table = new \Doctrine\DBAL\Schema\Table("fetch_table"); |
||
21 | $table->addColumn('test_int', 'integer'); |
||
22 | $table->addColumn('test_string', 'string'); |
||
23 | $table->addColumn('test_datetime', 'datetime', array('notnull' => false)); |
||
24 | $table->setPrimaryKey(array('test_int')); |
||
25 | |||
26 | $sm = $this->_conn->getSchemaManager(); |
||
27 | $sm->createTable($table); |
||
28 | |||
29 | $this->_conn->insert('fetch_table', array('test_int' => 1, 'test_string' => 'foo', 'test_datetime' => '2010-01-01 10:10:10')); |
||
30 | self::$generated = true; |
||
31 | } |
||
32 | } |
||
33 | |||
34 | public function testPrepareWithBindValue() |
||
35 | { |
||
36 | $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?"; |
||
37 | $stmt = $this->_conn->prepare($sql); |
||
38 | self::assertInstanceOf('Doctrine\DBAL\Statement', $stmt); |
||
39 | |||
40 | $stmt->bindValue(1, 1); |
||
41 | $stmt->bindValue(2, 'foo'); |
||
42 | $stmt->execute(); |
||
43 | |||
44 | $row = $stmt->fetch(\PDO::FETCH_ASSOC); |
||
45 | $row = array_change_key_case($row, \CASE_LOWER); |
||
46 | self::assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $row); |
||
47 | } |
||
48 | |||
49 | public function testPrepareWithBindParam() |
||
50 | { |
||
51 | $paramInt = 1; |
||
52 | $paramStr = 'foo'; |
||
53 | |||
54 | $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?"; |
||
55 | $stmt = $this->_conn->prepare($sql); |
||
56 | self::assertInstanceOf('Doctrine\DBAL\Statement', $stmt); |
||
57 | |||
58 | $stmt->bindParam(1, $paramInt); |
||
59 | $stmt->bindParam(2, $paramStr); |
||
60 | $stmt->execute(); |
||
61 | |||
62 | $row = $stmt->fetch(\PDO::FETCH_ASSOC); |
||
63 | $row = array_change_key_case($row, \CASE_LOWER); |
||
64 | self::assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $row); |
||
65 | } |
||
66 | |||
67 | public function testPrepareWithFetchAll() |
||
68 | { |
||
69 | $paramInt = 1; |
||
70 | $paramStr = 'foo'; |
||
71 | |||
72 | $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?"; |
||
73 | $stmt = $this->_conn->prepare($sql); |
||
74 | self::assertInstanceOf('Doctrine\DBAL\Statement', $stmt); |
||
75 | |||
76 | $stmt->bindParam(1, $paramInt); |
||
77 | $stmt->bindParam(2, $paramStr); |
||
78 | $stmt->execute(); |
||
79 | |||
80 | $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC); |
||
81 | $rows[0] = array_change_key_case($rows[0], \CASE_LOWER); |
||
82 | self::assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $rows[0]); |
||
83 | } |
||
84 | |||
85 | /** |
||
86 | * @group DBAL-228 |
||
87 | */ |
||
88 | public function testPrepareWithFetchAllBoth() |
||
89 | { |
||
90 | $paramInt = 1; |
||
91 | $paramStr = 'foo'; |
||
92 | |||
93 | $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?"; |
||
94 | $stmt = $this->_conn->prepare($sql); |
||
95 | self::assertInstanceOf('Doctrine\DBAL\Statement', $stmt); |
||
96 | |||
97 | $stmt->bindParam(1, $paramInt); |
||
98 | $stmt->bindParam(2, $paramStr); |
||
99 | $stmt->execute(); |
||
100 | |||
101 | $rows = $stmt->fetchAll(\PDO::FETCH_BOTH); |
||
102 | $rows[0] = array_change_key_case($rows[0], \CASE_LOWER); |
||
103 | self::assertEquals(array('test_int' => 1, 'test_string' => 'foo', 0 => 1, 1 => 'foo'), $rows[0]); |
||
104 | } |
||
105 | |||
106 | public function testPrepareWithFetchColumn() |
||
107 | { |
||
108 | $paramInt = 1; |
||
109 | $paramStr = 'foo'; |
||
110 | |||
111 | $sql = "SELECT test_int FROM fetch_table WHERE test_int = ? AND test_string = ?"; |
||
112 | $stmt = $this->_conn->prepare($sql); |
||
113 | self::assertInstanceOf('Doctrine\DBAL\Statement', $stmt); |
||
114 | |||
115 | $stmt->bindParam(1, $paramInt); |
||
116 | $stmt->bindParam(2, $paramStr); |
||
117 | $stmt->execute(); |
||
118 | |||
119 | $column = $stmt->fetchColumn(); |
||
120 | self::assertEquals(1, $column); |
||
121 | } |
||
122 | |||
123 | public function testPrepareWithIterator() |
||
124 | { |
||
125 | $paramInt = 1; |
||
126 | $paramStr = 'foo'; |
||
127 | |||
128 | $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?"; |
||
129 | $stmt = $this->_conn->prepare($sql); |
||
130 | self::assertInstanceOf('Doctrine\DBAL\Statement', $stmt); |
||
131 | |||
132 | $stmt->bindParam(1, $paramInt); |
||
133 | $stmt->bindParam(2, $paramStr); |
||
134 | $stmt->execute(); |
||
135 | |||
136 | $rows = array(); |
||
137 | $stmt->setFetchMode(\PDO::FETCH_ASSOC); |
||
138 | foreach ($stmt as $row) { |
||
139 | $rows[] = array_change_key_case($row, \CASE_LOWER); |
||
140 | } |
||
141 | |||
142 | self::assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $rows[0]); |
||
143 | } |
||
144 | |||
145 | public function testPrepareWithQuoted() |
||
146 | { |
||
147 | $table = 'fetch_table'; |
||
148 | $paramInt = 1; |
||
149 | $paramStr = 'foo'; |
||
150 | |||
151 | $sql = "SELECT test_int, test_string FROM " . $this->_conn->quoteIdentifier($table) . " ". |
||
152 | "WHERE test_int = " . $this->_conn->quote($paramInt) . " AND test_string = " . $this->_conn->quote($paramStr); |
||
153 | $stmt = $this->_conn->prepare($sql); |
||
154 | self::assertInstanceOf('Doctrine\DBAL\Statement', $stmt); |
||
155 | } |
||
156 | |||
157 | public function testPrepareWithExecuteParams() |
||
158 | { |
||
159 | $paramInt = 1; |
||
160 | $paramStr = 'foo'; |
||
161 | |||
162 | $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?"; |
||
163 | $stmt = $this->_conn->prepare($sql); |
||
164 | self::assertInstanceOf('Doctrine\DBAL\Statement', $stmt); |
||
165 | $stmt->execute(array($paramInt, $paramStr)); |
||
166 | |||
167 | $row = $stmt->fetch(\PDO::FETCH_ASSOC); |
||
168 | self::assertTrue($row !== false); |
||
169 | $row = array_change_key_case($row, \CASE_LOWER); |
||
170 | self::assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $row); |
||
171 | } |
||
172 | |||
173 | public function testFetchAll() |
||
174 | { |
||
175 | $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?"; |
||
176 | $data = $this->_conn->fetchAll($sql, array(1, 'foo')); |
||
177 | |||
178 | self::assertEquals(1, count($data)); |
||
179 | |||
180 | $row = $data[0]; |
||
181 | self::assertEquals(2, count($row)); |
||
182 | |||
183 | $row = array_change_key_case($row, \CASE_LOWER); |
||
184 | self::assertEquals(1, $row['test_int']); |
||
185 | self::assertEquals('foo', $row['test_string']); |
||
186 | } |
||
187 | |||
188 | /** |
||
189 | * @group DBAL-209 |
||
190 | */ |
||
191 | public function testFetchAllWithTypes() |
||
192 | { |
||
193 | $datetimeString = '2010-01-01 10:10:10'; |
||
194 | $datetime = new \DateTime($datetimeString); |
||
195 | $sql = "SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?"; |
||
196 | $data = $this->_conn->fetchAll($sql, array(1, $datetime), array(PDO::PARAM_STR, Type::DATETIME)); |
||
197 | |||
198 | self::assertEquals(1, count($data)); |
||
199 | |||
200 | $row = $data[0]; |
||
201 | self::assertEquals(2, count($row)); |
||
202 | |||
203 | $row = array_change_key_case($row, \CASE_LOWER); |
||
204 | self::assertEquals(1, $row['test_int']); |
||
205 | self::assertStringStartsWith($datetimeString, $row['test_datetime']); |
||
206 | } |
||
207 | |||
208 | /** |
||
209 | * @group DBAL-209 |
||
210 | * @expectedException \Doctrine\DBAL\DBALException |
||
211 | */ |
||
212 | View Code Duplication | public function testFetchAllWithMissingTypes() |
|
0 ignored issues
–
show
|
|||
213 | { |
||
214 | if ($this->_conn->getDriver() instanceof \Doctrine\DBAL\Driver\Mysqli\Driver || |
||
215 | $this->_conn->getDriver() instanceof \Doctrine\DBAL\Driver\SQLSrv\Driver) { |
||
216 | $this->markTestSkipped('mysqli and sqlsrv actually supports this'); |
||
217 | } |
||
218 | |||
219 | $datetimeString = '2010-01-01 10:10:10'; |
||
220 | $datetime = new \DateTime($datetimeString); |
||
221 | $sql = "SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?"; |
||
222 | $data = $this->_conn->fetchAll($sql, array(1, $datetime)); |
||
0 ignored issues
–
show
|
|||
223 | } |
||
224 | |||
225 | public function testFetchBoth() |
||
226 | { |
||
227 | $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?"; |
||
228 | $row = $this->_conn->executeQuery($sql, array(1, 'foo'))->fetch(\PDO::FETCH_BOTH); |
||
229 | |||
230 | self::assertTrue($row !== false); |
||
231 | |||
232 | $row = array_change_key_case($row, \CASE_LOWER); |
||
233 | |||
234 | self::assertEquals(1, $row['test_int']); |
||
235 | self::assertEquals('foo', $row['test_string']); |
||
236 | self::assertEquals(1, $row[0]); |
||
237 | self::assertEquals('foo', $row[1]); |
||
238 | } |
||
239 | |||
240 | public function testFetchNoResult() |
||
241 | { |
||
242 | self::assertFalse( |
||
243 | $this->_conn->executeQuery('SELECT test_int FROM fetch_table WHERE test_int = ?', [-1])->fetch() |
||
244 | ); |
||
245 | } |
||
246 | |||
247 | public function testFetchAssoc() |
||
248 | { |
||
249 | $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?"; |
||
250 | $row = $this->_conn->fetchAssoc($sql, array(1, 'foo')); |
||
251 | |||
252 | self::assertTrue($row !== false); |
||
253 | |||
254 | $row = array_change_key_case($row, \CASE_LOWER); |
||
255 | |||
256 | self::assertEquals(1, $row['test_int']); |
||
257 | self::assertEquals('foo', $row['test_string']); |
||
258 | } |
||
259 | |||
260 | View Code Duplication | public function testFetchAssocWithTypes() |
|
261 | { |
||
262 | $datetimeString = '2010-01-01 10:10:10'; |
||
263 | $datetime = new \DateTime($datetimeString); |
||
264 | $sql = "SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?"; |
||
265 | $row = $this->_conn->fetchAssoc($sql, array(1, $datetime), array(PDO::PARAM_STR, Type::DATETIME)); |
||
266 | |||
267 | self::assertTrue($row !== false); |
||
268 | |||
269 | $row = array_change_key_case($row, \CASE_LOWER); |
||
270 | |||
271 | self::assertEquals(1, $row['test_int']); |
||
272 | self::assertStringStartsWith($datetimeString, $row['test_datetime']); |
||
273 | } |
||
274 | |||
275 | /** |
||
276 | * @expectedException \Doctrine\DBAL\DBALException |
||
277 | */ |
||
278 | View Code Duplication | public function testFetchAssocWithMissingTypes() |
|
279 | { |
||
280 | if ($this->_conn->getDriver() instanceof \Doctrine\DBAL\Driver\Mysqli\Driver || |
||
281 | $this->_conn->getDriver() instanceof \Doctrine\DBAL\Driver\SQLSrv\Driver) { |
||
282 | $this->markTestSkipped('mysqli and sqlsrv actually supports this'); |
||
283 | } |
||
284 | |||
285 | $datetimeString = '2010-01-01 10:10:10'; |
||
286 | $datetime = new \DateTime($datetimeString); |
||
287 | $sql = "SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?"; |
||
288 | $row = $this->_conn->fetchAssoc($sql, array(1, $datetime)); |
||
289 | } |
||
290 | |||
291 | public function testFetchArray() |
||
292 | { |
||
293 | $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?"; |
||
294 | $row = $this->_conn->fetchArray($sql, array(1, 'foo')); |
||
295 | |||
296 | self::assertEquals(1, $row[0]); |
||
297 | self::assertEquals('foo', $row[1]); |
||
298 | } |
||
299 | |||
300 | View Code Duplication | public function testFetchArrayWithTypes() |
|
301 | { |
||
302 | $datetimeString = '2010-01-01 10:10:10'; |
||
303 | $datetime = new \DateTime($datetimeString); |
||
304 | $sql = "SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?"; |
||
305 | $row = $this->_conn->fetchArray($sql, array(1, $datetime), array(PDO::PARAM_STR, Type::DATETIME)); |
||
306 | |||
307 | self::assertTrue($row !== false); |
||
308 | |||
309 | $row = array_change_key_case($row, \CASE_LOWER); |
||
310 | |||
311 | self::assertEquals(1, $row[0]); |
||
312 | self::assertStringStartsWith($datetimeString, $row[1]); |
||
313 | } |
||
314 | |||
315 | /** |
||
316 | * @expectedException \Doctrine\DBAL\DBALException |
||
317 | */ |
||
318 | View Code Duplication | public function testFetchArrayWithMissingTypes() |
|
319 | { |
||
320 | if ($this->_conn->getDriver() instanceof \Doctrine\DBAL\Driver\Mysqli\Driver || |
||
321 | $this->_conn->getDriver() instanceof \Doctrine\DBAL\Driver\SQLSrv\Driver) { |
||
322 | $this->markTestSkipped('mysqli and sqlsrv actually supports this'); |
||
323 | } |
||
324 | |||
325 | $datetimeString = '2010-01-01 10:10:10'; |
||
326 | $datetime = new \DateTime($datetimeString); |
||
327 | $sql = "SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?"; |
||
328 | $row = $this->_conn->fetchArray($sql, array(1, $datetime)); |
||
329 | } |
||
330 | |||
331 | public function testFetchColumn() |
||
332 | { |
||
333 | $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?"; |
||
334 | $testInt = $this->_conn->fetchColumn($sql, array(1, 'foo'), 0); |
||
335 | |||
336 | self::assertEquals(1, $testInt); |
||
337 | |||
338 | $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?"; |
||
339 | $testString = $this->_conn->fetchColumn($sql, array(1, 'foo'), 1); |
||
340 | |||
341 | self::assertEquals('foo', $testString); |
||
342 | } |
||
343 | |||
344 | public function testFetchColumnWithTypes() |
||
345 | { |
||
346 | $datetimeString = '2010-01-01 10:10:10'; |
||
347 | $datetime = new \DateTime($datetimeString); |
||
348 | $sql = "SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?"; |
||
349 | $column = $this->_conn->fetchColumn($sql, array(1, $datetime), 1, array(PDO::PARAM_STR, Type::DATETIME)); |
||
350 | |||
351 | self::assertTrue($column !== false); |
||
352 | |||
353 | self::assertStringStartsWith($datetimeString, $column); |
||
354 | } |
||
355 | |||
356 | /** |
||
357 | * @expectedException \Doctrine\DBAL\DBALException |
||
358 | */ |
||
359 | View Code Duplication | public function testFetchColumnWithMissingTypes() |
|
360 | { |
||
361 | if ($this->_conn->getDriver() instanceof \Doctrine\DBAL\Driver\Mysqli\Driver || |
||
362 | $this->_conn->getDriver() instanceof \Doctrine\DBAL\Driver\SQLSrv\Driver) { |
||
363 | $this->markTestSkipped('mysqli and sqlsrv actually supports this'); |
||
364 | } |
||
365 | |||
366 | $datetimeString = '2010-01-01 10:10:10'; |
||
367 | $datetime = new \DateTime($datetimeString); |
||
368 | $sql = "SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?"; |
||
369 | $column = $this->_conn->fetchColumn($sql, array(1, $datetime), 1); |
||
370 | } |
||
371 | |||
372 | /** |
||
373 | * @group DDC-697 |
||
374 | */ |
||
375 | public function testExecuteQueryBindDateTimeType() |
||
376 | { |
||
377 | $sql = 'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?'; |
||
378 | $stmt = $this->_conn->executeQuery($sql, |
||
379 | array(1 => new \DateTime('2010-01-01 10:10:10')), |
||
380 | array(1 => Type::DATETIME) |
||
381 | ); |
||
382 | |||
383 | self::assertEquals(1, $stmt->fetchColumn()); |
||
384 | } |
||
385 | |||
386 | /** |
||
387 | * @group DDC-697 |
||
388 | */ |
||
389 | public function testExecuteUpdateBindDateTimeType() |
||
390 | { |
||
391 | $datetime = new \DateTime('2010-02-02 20:20:20'); |
||
392 | |||
393 | $sql = 'INSERT INTO fetch_table (test_int, test_string, test_datetime) VALUES (?, ?, ?)'; |
||
394 | $affectedRows = $this->_conn->executeUpdate($sql, |
||
395 | array(1 => 50, 2 => 'foo', 3 => $datetime), |
||
396 | array(1 => PDO::PARAM_INT, 2 => PDO::PARAM_STR, 3 => Type::DATETIME) |
||
397 | ); |
||
398 | |||
399 | self::assertEquals(1, $affectedRows); |
||
400 | self::assertEquals(1, $this->_conn->executeQuery( |
||
401 | 'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?', |
||
402 | array(1 => $datetime), |
||
403 | array(1 => Type::DATETIME) |
||
404 | )->fetchColumn()); |
||
405 | } |
||
406 | |||
407 | /** |
||
408 | * @group DDC-697 |
||
409 | */ |
||
410 | public function testPrepareQueryBindValueDateTimeType() |
||
411 | { |
||
412 | $sql = 'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?'; |
||
413 | $stmt = $this->_conn->prepare($sql); |
||
414 | $stmt->bindValue(1, new \DateTime('2010-01-01 10:10:10'), Type::DATETIME); |
||
415 | $stmt->execute(); |
||
416 | |||
417 | self::assertEquals(1, $stmt->fetchColumn()); |
||
418 | } |
||
419 | |||
420 | /** |
||
421 | * @group DBAL-78 |
||
422 | */ |
||
423 | public function testNativeArrayListSupport() |
||
424 | { |
||
425 | for ($i = 100; $i < 110; $i++) { |
||
426 | $this->_conn->insert('fetch_table', array('test_int' => $i, 'test_string' => 'foo' . $i, 'test_datetime' => '2010-01-01 10:10:10')); |
||
427 | } |
||
428 | |||
429 | $stmt = $this->_conn->executeQuery('SELECT test_int FROM fetch_table WHERE test_int IN (?)', |
||
430 | array(array(100, 101, 102, 103, 104)), array(Connection::PARAM_INT_ARRAY)); |
||
431 | |||
432 | $data = $stmt->fetchAll(PDO::FETCH_NUM); |
||
433 | self::assertEquals(5, count($data)); |
||
434 | self::assertEquals(array(array(100), array(101), array(102), array(103), array(104)), $data); |
||
435 | |||
436 | $stmt = $this->_conn->executeQuery('SELECT test_int FROM fetch_table WHERE test_string IN (?)', |
||
437 | array(array('foo100', 'foo101', 'foo102', 'foo103', 'foo104')), array(Connection::PARAM_STR_ARRAY)); |
||
438 | |||
439 | $data = $stmt->fetchAll(PDO::FETCH_NUM); |
||
440 | self::assertEquals(5, count($data)); |
||
441 | self::assertEquals(array(array(100), array(101), array(102), array(103), array(104)), $data); |
||
442 | } |
||
443 | |||
444 | /** |
||
445 | * @dataProvider getTrimExpressionData |
||
446 | */ |
||
447 | public function testTrimExpression($value, $position, $char, $expectedResult) |
||
448 | { |
||
449 | $sql = 'SELECT ' . |
||
450 | $this->_conn->getDatabasePlatform()->getTrimExpression($value, $position, $char) . ' AS trimmed ' . |
||
451 | 'FROM fetch_table'; |
||
452 | |||
453 | $row = $this->_conn->fetchAssoc($sql); |
||
454 | $row = array_change_key_case($row, CASE_LOWER); |
||
455 | |||
456 | self::assertEquals($expectedResult, $row['trimmed']); |
||
457 | } |
||
458 | |||
459 | public function getTrimExpressionData() |
||
460 | { |
||
461 | return array( |
||
462 | array('test_string', AbstractPlatform::TRIM_UNSPECIFIED, false, 'foo'), |
||
463 | array('test_string', AbstractPlatform::TRIM_LEADING, false, 'foo'), |
||
464 | array('test_string', AbstractPlatform::TRIM_TRAILING, false, 'foo'), |
||
465 | array('test_string', AbstractPlatform::TRIM_BOTH, false, 'foo'), |
||
466 | array('test_string', AbstractPlatform::TRIM_UNSPECIFIED, "'f'", 'oo'), |
||
467 | array('test_string', AbstractPlatform::TRIM_UNSPECIFIED, "'o'", 'f'), |
||
468 | array('test_string', AbstractPlatform::TRIM_UNSPECIFIED, "'.'", 'foo'), |
||
469 | array('test_string', AbstractPlatform::TRIM_LEADING, "'f'", 'oo'), |
||
470 | array('test_string', AbstractPlatform::TRIM_LEADING, "'o'", 'foo'), |
||
471 | array('test_string', AbstractPlatform::TRIM_LEADING, "'.'", 'foo'), |
||
472 | array('test_string', AbstractPlatform::TRIM_TRAILING, "'f'", 'foo'), |
||
473 | array('test_string', AbstractPlatform::TRIM_TRAILING, "'o'", 'f'), |
||
474 | array('test_string', AbstractPlatform::TRIM_TRAILING, "'.'", 'foo'), |
||
475 | array('test_string', AbstractPlatform::TRIM_BOTH, "'f'", 'oo'), |
||
476 | array('test_string', AbstractPlatform::TRIM_BOTH, "'o'", 'f'), |
||
477 | array('test_string', AbstractPlatform::TRIM_BOTH, "'.'", 'foo'), |
||
478 | array("' foo '", AbstractPlatform::TRIM_UNSPECIFIED, false, 'foo'), |
||
479 | array("' foo '", AbstractPlatform::TRIM_LEADING, false, 'foo '), |
||
480 | array("' foo '", AbstractPlatform::TRIM_TRAILING, false, ' foo'), |
||
481 | array("' foo '", AbstractPlatform::TRIM_BOTH, false, 'foo'), |
||
482 | array("' foo '", AbstractPlatform::TRIM_UNSPECIFIED, "'f'", ' foo '), |
||
483 | array("' foo '", AbstractPlatform::TRIM_UNSPECIFIED, "'o'", ' foo '), |
||
484 | array("' foo '", AbstractPlatform::TRIM_UNSPECIFIED, "'.'", ' foo '), |
||
485 | array("' foo '", AbstractPlatform::TRIM_UNSPECIFIED, "' '", 'foo'), |
||
486 | array("' foo '", AbstractPlatform::TRIM_LEADING, "'f'", ' foo '), |
||
487 | array("' foo '", AbstractPlatform::TRIM_LEADING, "'o'", ' foo '), |
||
488 | array("' foo '", AbstractPlatform::TRIM_LEADING, "'.'", ' foo '), |
||
489 | array("' foo '", AbstractPlatform::TRIM_LEADING, "' '", 'foo '), |
||
490 | array("' foo '", AbstractPlatform::TRIM_TRAILING, "'f'", ' foo '), |
||
491 | array("' foo '", AbstractPlatform::TRIM_TRAILING, "'o'", ' foo '), |
||
492 | array("' foo '", AbstractPlatform::TRIM_TRAILING, "'.'", ' foo '), |
||
493 | array("' foo '", AbstractPlatform::TRIM_TRAILING, "' '", ' foo'), |
||
494 | array("' foo '", AbstractPlatform::TRIM_BOTH, "'f'", ' foo '), |
||
495 | array("' foo '", AbstractPlatform::TRIM_BOTH, "'o'", ' foo '), |
||
496 | array("' foo '", AbstractPlatform::TRIM_BOTH, "'.'", ' foo '), |
||
497 | array("' foo '", AbstractPlatform::TRIM_BOTH, "' '", 'foo'), |
||
498 | ); |
||
499 | } |
||
500 | |||
501 | /** |
||
502 | * @group DDC-1014 |
||
503 | */ |
||
504 | public function testDateArithmetics() |
||
505 | { |
||
506 | $p = $this->_conn->getDatabasePlatform(); |
||
507 | $sql = 'SELECT '; |
||
508 | $sql .= $p->getDateDiffExpression('test_datetime', $p->getCurrentTimestampSQL()) .' AS diff, '; |
||
509 | $sql .= $p->getDateAddSecondsExpression('test_datetime', 1) .' AS add_seconds, '; |
||
510 | $sql .= $p->getDateSubSecondsExpression('test_datetime', 1) .' AS sub_seconds, '; |
||
511 | $sql .= $p->getDateAddMinutesExpression('test_datetime', 5) .' AS add_minutes, '; |
||
512 | $sql .= $p->getDateSubMinutesExpression('test_datetime', 5) .' AS sub_minutes, '; |
||
513 | $sql .= $p->getDateAddHourExpression('test_datetime', 3) .' AS add_hour, '; |
||
514 | $sql .= $p->getDateSubHourExpression('test_datetime', 3) .' AS sub_hour, '; |
||
515 | $sql .= $p->getDateAddDaysExpression('test_datetime', 10) .' AS add_days, '; |
||
516 | $sql .= $p->getDateSubDaysExpression('test_datetime', 10) .' AS sub_days, '; |
||
517 | $sql .= $p->getDateAddWeeksExpression('test_datetime', 1) .' AS add_weeks, '; |
||
518 | $sql .= $p->getDateSubWeeksExpression('test_datetime', 1) .' AS sub_weeks, '; |
||
519 | $sql .= $p->getDateAddMonthExpression('test_datetime', 2) .' AS add_month, '; |
||
520 | $sql .= $p->getDateSubMonthExpression('test_datetime', 2) .' AS sub_month, '; |
||
521 | $sql .= $p->getDateAddQuartersExpression('test_datetime', 3) .' AS add_quarters, '; |
||
522 | $sql .= $p->getDateSubQuartersExpression('test_datetime', 3) .' AS sub_quarters, '; |
||
523 | $sql .= $p->getDateAddYearsExpression('test_datetime', 6) .' AS add_years, '; |
||
524 | $sql .= $p->getDateSubYearsExpression('test_datetime', 6) .' AS sub_years '; |
||
525 | $sql .= 'FROM fetch_table'; |
||
526 | |||
527 | $row = $this->_conn->fetchAssoc($sql); |
||
528 | $row = array_change_key_case($row, CASE_LOWER); |
||
529 | |||
530 | $diff = (strtotime('2010-01-01') - strtotime(date('Y-m-d'))) / 3600 / 24; |
||
531 | self::assertEquals($diff, $row['diff'], "Date difference should be approx. ".$diff." days.", 1); |
||
532 | self::assertEquals('2010-01-01 10:10:11', date('Y-m-d H:i:s', strtotime($row['add_seconds'])), "Adding second should end up on 2010-01-01 10:10:11"); |
||
533 | self::assertEquals('2010-01-01 10:10:09', date('Y-m-d H:i:s', strtotime($row['sub_seconds'])), "Subtracting second should end up on 2010-01-01 10:10:09"); |
||
534 | self::assertEquals('2010-01-01 10:15:10', date('Y-m-d H:i:s', strtotime($row['add_minutes'])), "Adding minutes should end up on 2010-01-01 10:15:10"); |
||
535 | self::assertEquals('2010-01-01 10:05:10', date('Y-m-d H:i:s', strtotime($row['sub_minutes'])), "Subtracting minutes should end up on 2010-01-01 10:05:10"); |
||
536 | self::assertEquals('2010-01-01 13:10', date('Y-m-d H:i', strtotime($row['add_hour'])), "Adding date should end up on 2010-01-01 13:10"); |
||
537 | self::assertEquals('2010-01-01 07:10', date('Y-m-d H:i', strtotime($row['sub_hour'])), "Subtracting date should end up on 2010-01-01 07:10"); |
||
538 | self::assertEquals('2010-01-11', date('Y-m-d', strtotime($row['add_days'])), "Adding date should end up on 2010-01-11"); |
||
539 | self::assertEquals('2009-12-22', date('Y-m-d', strtotime($row['sub_days'])), "Subtracting date should end up on 2009-12-22"); |
||
540 | self::assertEquals('2010-01-08', date('Y-m-d', strtotime($row['add_weeks'])), "Adding week should end up on 2010-01-08"); |
||
541 | self::assertEquals('2009-12-25', date('Y-m-d', strtotime($row['sub_weeks'])), "Subtracting week should end up on 2009-12-25"); |
||
542 | self::assertEquals('2010-03-01', date('Y-m-d', strtotime($row['add_month'])), "Adding month should end up on 2010-03-01"); |
||
543 | self::assertEquals('2009-11-01', date('Y-m-d', strtotime($row['sub_month'])), "Subtracting month should end up on 2009-11-01"); |
||
544 | self::assertEquals('2010-10-01', date('Y-m-d', strtotime($row['add_quarters'])), "Adding quarters should end up on 2010-04-01"); |
||
545 | self::assertEquals('2009-04-01', date('Y-m-d', strtotime($row['sub_quarters'])), "Subtracting quarters should end up on 2009-10-01"); |
||
546 | self::assertEquals('2016-01-01', date('Y-m-d', strtotime($row['add_years'])), "Adding years should end up on 2016-01-01"); |
||
547 | self::assertEquals('2004-01-01', date('Y-m-d', strtotime($row['sub_years'])), "Subtracting years should end up on 2004-01-01"); |
||
548 | } |
||
549 | |||
550 | public function testLocateExpression() |
||
551 | { |
||
552 | $platform = $this->_conn->getDatabasePlatform(); |
||
553 | |||
554 | $sql = 'SELECT '; |
||
555 | $sql .= $platform->getLocateExpression('test_string', "'oo'") .' AS locate1, '; |
||
556 | $sql .= $platform->getLocateExpression('test_string', "'foo'") .' AS locate2, '; |
||
557 | $sql .= $platform->getLocateExpression('test_string', "'bar'") .' AS locate3, '; |
||
558 | $sql .= $platform->getLocateExpression('test_string', 'test_string') .' AS locate4, '; |
||
559 | $sql .= $platform->getLocateExpression("'foo'", 'test_string') .' AS locate5, '; |
||
560 | $sql .= $platform->getLocateExpression("'barfoobaz'", 'test_string') .' AS locate6, '; |
||
561 | $sql .= $platform->getLocateExpression("'bar'", 'test_string') .' AS locate7, '; |
||
562 | $sql .= $platform->getLocateExpression('test_string', "'oo'", 2) .' AS locate8, '; |
||
563 | $sql .= $platform->getLocateExpression('test_string', "'oo'", 3) .' AS locate9 '; |
||
564 | $sql .= 'FROM fetch_table'; |
||
565 | |||
566 | $row = $this->_conn->fetchAssoc($sql); |
||
567 | $row = array_change_key_case($row, CASE_LOWER); |
||
568 | |||
569 | self::assertEquals(2, $row['locate1']); |
||
570 | self::assertEquals(1, $row['locate2']); |
||
571 | self::assertEquals(0, $row['locate3']); |
||
572 | self::assertEquals(1, $row['locate4']); |
||
573 | self::assertEquals(1, $row['locate5']); |
||
574 | self::assertEquals(4, $row['locate6']); |
||
575 | self::assertEquals(0, $row['locate7']); |
||
576 | self::assertEquals(2, $row['locate8']); |
||
577 | self::assertEquals(0, $row['locate9']); |
||
578 | } |
||
579 | |||
580 | View Code Duplication | public function testQuoteSQLInjection() |
|
581 | { |
||
582 | $sql = "SELECT * FROM fetch_table WHERE test_string = " . $this->_conn->quote("bar' OR '1'='1"); |
||
583 | $rows = $this->_conn->fetchAll($sql); |
||
584 | |||
585 | self::assertEquals(0, count($rows), "no result should be returned, otherwise SQL injection is possible"); |
||
586 | } |
||
587 | |||
588 | /** |
||
589 | * @group DDC-1213 |
||
590 | */ |
||
591 | public function testBitComparisonExpressionSupport() |
||
592 | { |
||
593 | $this->_conn->exec('DELETE FROM fetch_table'); |
||
594 | $platform = $this->_conn->getDatabasePlatform(); |
||
595 | $bitmap = array(); |
||
596 | |||
597 | for ($i = 2; $i < 9; $i = $i + 2) { |
||
598 | $bitmap[$i] = array( |
||
599 | 'bit_or' => ($i | 2), |
||
600 | 'bit_and' => ($i & 2) |
||
601 | ); |
||
602 | $this->_conn->insert('fetch_table', array( |
||
603 | 'test_int' => $i, |
||
604 | 'test_string' => json_encode($bitmap[$i]), |
||
605 | 'test_datetime' => '2010-01-01 10:10:10' |
||
606 | )); |
||
607 | } |
||
608 | |||
609 | $sql[] = 'SELECT '; |
||
610 | $sql[] = 'test_int, '; |
||
611 | $sql[] = 'test_string, '; |
||
612 | $sql[] = $platform->getBitOrComparisonExpression('test_int', 2) . ' AS bit_or, '; |
||
613 | $sql[] = $platform->getBitAndComparisonExpression('test_int', 2) . ' AS bit_and '; |
||
614 | $sql[] = 'FROM fetch_table'; |
||
615 | |||
616 | $stmt = $this->_conn->executeQuery(implode(PHP_EOL, $sql)); |
||
617 | $data = $stmt->fetchAll(PDO::FETCH_ASSOC); |
||
618 | |||
619 | |||
620 | self::assertEquals(4, count($data)); |
||
621 | self::assertEquals(count($bitmap), count($data)); |
||
622 | foreach ($data as $row) { |
||
623 | $row = array_change_key_case($row, CASE_LOWER); |
||
624 | |||
625 | self::assertArrayHasKey('test_int', $row); |
||
626 | |||
627 | $id = $row['test_int']; |
||
628 | |||
629 | self::assertArrayHasKey($id, $bitmap); |
||
630 | self::assertArrayHasKey($id, $bitmap); |
||
631 | |||
632 | self::assertArrayHasKey('bit_or', $row); |
||
633 | self::assertArrayHasKey('bit_and', $row); |
||
634 | |||
635 | self::assertEquals($row['bit_or'], $bitmap[$id]['bit_or']); |
||
636 | self::assertEquals($row['bit_and'], $bitmap[$id]['bit_and']); |
||
637 | } |
||
638 | } |
||
639 | |||
640 | public function testSetDefaultFetchMode() |
||
641 | { |
||
642 | $stmt = $this->_conn->query("SELECT * FROM fetch_table"); |
||
643 | $stmt->setFetchMode(\PDO::FETCH_NUM); |
||
644 | |||
645 | $row = array_keys($stmt->fetch()); |
||
646 | self::assertEquals(0, count( array_filter($row, function($v) { return ! is_numeric($v); })), "should be no non-numerical elements in the result."); |
||
647 | } |
||
648 | |||
649 | /** |
||
650 | * @group DBAL-1091 |
||
651 | */ |
||
652 | public function testFetchAllStyleObject() |
||
653 | { |
||
654 | $this->setupFixture(); |
||
655 | |||
656 | $sql = 'SELECT test_int, test_string, test_datetime FROM fetch_table'; |
||
657 | $stmt = $this->_conn->prepare($sql); |
||
658 | |||
659 | $stmt->execute(); |
||
660 | |||
661 | $results = $stmt->fetchAll(\PDO::FETCH_OBJ); |
||
662 | |||
663 | self::assertCount(1, $results); |
||
664 | self::assertInstanceOf('stdClass', $results[0]); |
||
665 | |||
666 | self::assertEquals( |
||
667 | 1, |
||
668 | property_exists($results[0], 'test_int') ? $results[0]->test_int : $results[0]->TEST_INT |
||
669 | ); |
||
670 | self::assertEquals( |
||
671 | 'foo', |
||
672 | property_exists($results[0], 'test_string') ? $results[0]->test_string : $results[0]->TEST_STRING |
||
673 | ); |
||
674 | self::assertStringStartsWith( |
||
675 | '2010-01-01 10:10:10', |
||
676 | property_exists($results[0], 'test_datetime') ? $results[0]->test_datetime : $results[0]->TEST_DATETIME |
||
677 | ); |
||
678 | } |
||
679 | |||
680 | /** |
||
681 | * @group DBAL-196 |
||
682 | */ |
||
683 | View Code Duplication | public function testFetchAllSupportFetchClass() |
|
684 | { |
||
685 | $this->skipOci8AndMysqli(); |
||
686 | $this->setupFixture(); |
||
687 | |||
688 | $sql = "SELECT test_int, test_string, test_datetime FROM fetch_table"; |
||
689 | $stmt = $this->_conn->prepare($sql); |
||
690 | $stmt->execute(); |
||
691 | |||
692 | $results = $stmt->fetchAll( |
||
693 | \PDO::FETCH_CLASS, |
||
694 | __NAMESPACE__.'\\MyFetchClass' |
||
695 | ); |
||
696 | |||
697 | self::assertEquals(1, count($results)); |
||
698 | self::assertInstanceOf(__NAMESPACE__.'\\MyFetchClass', $results[0]); |
||
699 | |||
700 | self::assertEquals(1, $results[0]->test_int); |
||
701 | self::assertEquals('foo', $results[0]->test_string); |
||
702 | self::assertStringStartsWith('2010-01-01 10:10:10', $results[0]->test_datetime); |
||
703 | } |
||
704 | |||
705 | /** |
||
706 | * @group DBAL-241 |
||
707 | */ |
||
708 | public function testFetchAllStyleColumn() |
||
709 | { |
||
710 | $sql = "DELETE FROM fetch_table"; |
||
711 | $this->_conn->executeUpdate($sql); |
||
712 | |||
713 | $this->_conn->insert('fetch_table', array('test_int' => 1, 'test_string' => 'foo')); |
||
714 | $this->_conn->insert('fetch_table', array('test_int' => 10, 'test_string' => 'foo')); |
||
715 | |||
716 | $sql = "SELECT test_int FROM fetch_table"; |
||
717 | $rows = $this->_conn->query($sql)->fetchAll(\PDO::FETCH_COLUMN); |
||
718 | |||
719 | self::assertEquals(array(1, 10), $rows); |
||
720 | } |
||
721 | |||
722 | /** |
||
723 | * @group DBAL-214 |
||
724 | */ |
||
725 | View Code Duplication | public function testSetFetchModeClassFetchAll() |
|
726 | { |
||
727 | $this->skipOci8AndMysqli(); |
||
728 | $this->setupFixture(); |
||
729 | |||
730 | $sql = "SELECT * FROM fetch_table"; |
||
731 | $stmt = $this->_conn->query($sql); |
||
732 | $stmt->setFetchMode(\PDO::FETCH_CLASS, __NAMESPACE__ . '\\MyFetchClass'); |
||
733 | |||
734 | $results = $stmt->fetchAll(); |
||
735 | |||
736 | self::assertEquals(1, count($results)); |
||
737 | self::assertInstanceOf(__NAMESPACE__.'\\MyFetchClass', $results[0]); |
||
738 | |||
739 | self::assertEquals(1, $results[0]->test_int); |
||
740 | self::assertEquals('foo', $results[0]->test_string); |
||
741 | self::assertStringStartsWith('2010-01-01 10:10:10', $results[0]->test_datetime); |
||
742 | } |
||
743 | |||
744 | /** |
||
745 | * @group DBAL-214 |
||
746 | */ |
||
747 | public function testSetFetchModeClassFetch() |
||
748 | { |
||
749 | $this->skipOci8AndMysqli(); |
||
750 | $this->setupFixture(); |
||
751 | |||
752 | $sql = "SELECT * FROM fetch_table"; |
||
753 | $stmt = $this->_conn->query($sql); |
||
754 | $stmt->setFetchMode(\PDO::FETCH_CLASS, __NAMESPACE__ . '\\MyFetchClass'); |
||
755 | |||
756 | $results = array(); |
||
757 | while ($row = $stmt->fetch()) { |
||
758 | $results[] = $row; |
||
759 | } |
||
760 | |||
761 | self::assertEquals(1, count($results)); |
||
762 | self::assertInstanceOf(__NAMESPACE__.'\\MyFetchClass', $results[0]); |
||
763 | |||
764 | self::assertEquals(1, $results[0]->test_int); |
||
765 | self::assertEquals('foo', $results[0]->test_string); |
||
766 | self::assertStringStartsWith('2010-01-01 10:10:10', $results[0]->test_datetime); |
||
767 | } |
||
768 | |||
769 | /** |
||
770 | * @group DBAL-257 |
||
771 | */ |
||
772 | public function testEmptyFetchColumnReturnsFalse() |
||
773 | { |
||
774 | $this->_conn->beginTransaction(); |
||
775 | $this->_conn->exec('DELETE FROM fetch_table'); |
||
776 | self::assertFalse($this->_conn->fetchColumn('SELECT test_int FROM fetch_table')); |
||
777 | self::assertFalse($this->_conn->query('SELECT test_int FROM fetch_table')->fetchColumn()); |
||
778 | $this->_conn->rollBack(); |
||
779 | } |
||
780 | |||
781 | /** |
||
782 | * @group DBAL-339 |
||
783 | */ |
||
784 | public function testSetFetchModeOnDbalStatement() |
||
785 | { |
||
786 | $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?"; |
||
787 | $stmt = $this->_conn->executeQuery($sql, array(1, "foo")); |
||
788 | $stmt->setFetchMode(\PDO::FETCH_NUM); |
||
789 | |||
790 | $row = $stmt->fetch(); |
||
791 | |||
792 | self::assertArrayHasKey(0, $row); |
||
793 | self::assertArrayHasKey(1, $row); |
||
794 | self::assertFalse($stmt->fetch()); |
||
795 | } |
||
796 | |||
797 | /** |
||
798 | * @group DBAL-435 |
||
799 | */ |
||
800 | public function testEmptyParameters() |
||
801 | { |
||
802 | $sql = "SELECT * FROM fetch_table WHERE test_int IN (?)"; |
||
803 | $stmt = $this->_conn->executeQuery($sql, array(array()), array(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY)); |
||
804 | $rows = $stmt->fetchAll(); |
||
805 | |||
806 | self::assertEquals(array(), $rows); |
||
807 | } |
||
808 | |||
809 | /** |
||
810 | * @group DBAL-1028 |
||
811 | */ |
||
812 | public function testFetchColumnNullValue() |
||
813 | { |
||
814 | $this->_conn->executeUpdate( |
||
815 | 'INSERT INTO fetch_table (test_int, test_string) VALUES (?, ?)', |
||
816 | array(2, 'foo') |
||
817 | ); |
||
818 | |||
819 | self::assertNull( |
||
820 | $this->_conn->fetchColumn('SELECT test_datetime FROM fetch_table WHERE test_int = ?', array(2)) |
||
821 | ); |
||
822 | } |
||
823 | |||
824 | /** |
||
825 | * @group DBAL-1028 |
||
826 | */ |
||
827 | public function testFetchColumnNonExistingIndex() |
||
828 | { |
||
829 | if ($this->_conn->getDriver()->getName() === 'pdo_sqlsrv') { |
||
830 | $this->markTestSkipped( |
||
831 | 'Test does not work for pdo_sqlsrv driver as it throws a fatal error for a non-existing column index.' |
||
832 | ); |
||
833 | } |
||
834 | |||
835 | self::assertNull( |
||
836 | $this->_conn->fetchColumn('SELECT test_int FROM fetch_table WHERE test_int = ?', array(1), 1) |
||
837 | ); |
||
838 | } |
||
839 | |||
840 | /** |
||
841 | * @group DBAL-1028 |
||
842 | */ |
||
843 | public function testFetchColumnNoResult() |
||
844 | { |
||
845 | self::assertFalse( |
||
846 | $this->_conn->fetchColumn('SELECT test_int FROM fetch_table WHERE test_int = ?', array(-1)) |
||
847 | ); |
||
848 | } |
||
849 | |||
850 | private function setupFixture() |
||
851 | { |
||
852 | $this->_conn->exec('DELETE FROM fetch_table'); |
||
853 | $this->_conn->insert('fetch_table', array( |
||
854 | 'test_int' => 1, |
||
855 | 'test_string' => 'foo', |
||
856 | 'test_datetime' => '2010-01-01 10:10:10' |
||
857 | )); |
||
858 | } |
||
859 | |||
860 | private function skipOci8AndMysqli() |
||
861 | { |
||
862 | if (isset($GLOBALS['db_type']) && $GLOBALS['db_type'] == "oci8") { |
||
863 | $this->markTestSkipped("Not supported by OCI8"); |
||
864 | } |
||
865 | if ('mysqli' == $this->_conn->getDriver()->getName()) { |
||
866 | $this->markTestSkipped('Mysqli driver dont support this feature.'); |
||
867 | } |
||
868 | } |
||
869 | } |
||
870 | |||
871 | class MyFetchClass |
||
872 | { |
||
873 | public $test_int, $test_string, $test_datetime; |
||
874 | } |
||
875 |
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.