1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
declare(strict_types=1); |
4
|
|
|
|
5
|
|
|
namespace Doctrine\DBAL\Tests\Functional; |
6
|
|
|
|
7
|
|
|
use DateTime; |
8
|
|
|
use Doctrine\DBAL\Connection; |
9
|
|
|
use Doctrine\DBAL\DBALException; |
10
|
|
|
use Doctrine\DBAL\Driver\Mysqli\Driver as MySQLiDriver; |
11
|
|
|
use Doctrine\DBAL\Driver\SQLSrv\Driver as SQLSrvDriver; |
12
|
|
|
use Doctrine\DBAL\FetchMode; |
13
|
|
|
use Doctrine\DBAL\ParameterType; |
14
|
|
|
use Doctrine\DBAL\Platforms\AbstractPlatform; |
15
|
|
|
use Doctrine\DBAL\Platforms\SqlitePlatform; |
16
|
|
|
use Doctrine\DBAL\Platforms\TrimMode; |
17
|
|
|
use Doctrine\DBAL\Schema\Table; |
18
|
|
|
use Doctrine\DBAL\Statement; |
19
|
|
|
use Doctrine\DBAL\Tests\FunctionalTestCase; |
20
|
|
|
use Doctrine\DBAL\Types\Types; |
21
|
|
|
use InvalidArgumentException; |
22
|
|
|
use function array_change_key_case; |
23
|
|
|
use function array_filter; |
24
|
|
|
use function array_keys; |
25
|
|
|
use function assert; |
26
|
|
|
use function count; |
27
|
|
|
use function date; |
28
|
|
|
use function is_array; |
29
|
|
|
use function is_numeric; |
30
|
|
|
use function json_encode; |
31
|
|
|
use function sprintf; |
32
|
|
|
use function strtotime; |
33
|
|
|
use const CASE_LOWER; |
34
|
|
|
|
35
|
|
|
class DataAccessTest extends FunctionalTestCase |
36
|
|
|
{ |
37
|
|
|
/** @var bool */ |
38
|
|
|
private static $generated = false; |
39
|
|
|
|
40
|
|
|
protected function setUp() : void |
41
|
|
|
{ |
42
|
|
|
parent::setUp(); |
43
|
|
|
|
44
|
|
|
if (self::$generated !== false) { |
45
|
|
|
return; |
46
|
|
|
} |
47
|
|
|
|
48
|
|
|
$table = new Table('fetch_table'); |
49
|
|
|
$table->addColumn('test_int', 'integer'); |
50
|
|
|
$table->addColumn('test_string', 'string', ['length' => 32]); |
51
|
|
|
$table->addColumn('test_datetime', 'datetime', ['notnull' => false]); |
52
|
|
|
$table->setPrimaryKey(['test_int']); |
53
|
|
|
|
54
|
|
|
$sm = $this->connection->getSchemaManager(); |
55
|
|
|
$sm->createTable($table); |
56
|
|
|
|
57
|
|
|
$this->connection->insert('fetch_table', ['test_int' => 1, 'test_string' => 'foo', 'test_datetime' => '2010-01-01 10:10:10']); |
58
|
|
|
self::$generated = true; |
59
|
|
|
} |
60
|
|
|
|
61
|
|
|
public function testPrepareWithBindValue() : void |
62
|
|
|
{ |
63
|
|
|
$sql = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?'; |
64
|
|
|
$stmt = $this->connection->prepare($sql); |
65
|
|
|
self::assertInstanceOf(Statement::class, $stmt); |
66
|
|
|
|
67
|
|
|
$stmt->bindValue(1, 1); |
68
|
|
|
$stmt->bindValue(2, 'foo'); |
69
|
|
|
$stmt->execute(); |
70
|
|
|
|
71
|
|
|
$row = $stmt->fetch(FetchMode::ASSOCIATIVE); |
72
|
|
|
$row = array_change_key_case($row, CASE_LOWER); |
73
|
|
|
self::assertEquals(['test_int' => 1, 'test_string' => 'foo'], $row); |
74
|
|
|
} |
75
|
|
|
|
76
|
|
|
public function testPrepareWithBindParam() : void |
77
|
|
|
{ |
78
|
|
|
$paramInt = 1; |
79
|
|
|
$paramStr = 'foo'; |
80
|
|
|
|
81
|
|
|
$sql = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?'; |
82
|
|
|
$stmt = $this->connection->prepare($sql); |
83
|
|
|
self::assertInstanceOf(Statement::class, $stmt); |
84
|
|
|
|
85
|
|
|
$stmt->bindParam(1, $paramInt); |
86
|
|
|
$stmt->bindParam(2, $paramStr); |
87
|
|
|
$stmt->execute(); |
88
|
|
|
|
89
|
|
|
$row = $stmt->fetch(FetchMode::ASSOCIATIVE); |
90
|
|
|
$row = array_change_key_case($row, CASE_LOWER); |
91
|
|
|
self::assertEquals(['test_int' => 1, 'test_string' => 'foo'], $row); |
92
|
|
|
} |
93
|
|
|
|
94
|
|
|
public function testPrepareWithFetchAll() : void |
95
|
|
|
{ |
96
|
|
|
$paramInt = 1; |
97
|
|
|
$paramStr = 'foo'; |
98
|
|
|
|
99
|
|
|
$sql = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?'; |
100
|
|
|
$stmt = $this->connection->prepare($sql); |
101
|
|
|
self::assertInstanceOf(Statement::class, $stmt); |
102
|
|
|
|
103
|
|
|
$stmt->bindParam(1, $paramInt); |
104
|
|
|
$stmt->bindParam(2, $paramStr); |
105
|
|
|
$stmt->execute(); |
106
|
|
|
|
107
|
|
|
$rows = $stmt->fetchAll(FetchMode::ASSOCIATIVE); |
108
|
|
|
$rows[0] = array_change_key_case($rows[0], CASE_LOWER); |
109
|
|
|
self::assertEquals(['test_int' => 1, 'test_string' => 'foo'], $rows[0]); |
110
|
|
|
} |
111
|
|
|
|
112
|
|
|
/** |
113
|
|
|
* @group DBAL-228 |
114
|
|
|
*/ |
115
|
|
|
public function testPrepareWithFetchAllBoth() : void |
116
|
|
|
{ |
117
|
|
|
$paramInt = 1; |
118
|
|
|
$paramStr = 'foo'; |
119
|
|
|
|
120
|
|
|
$sql = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?'; |
121
|
|
|
$stmt = $this->connection->prepare($sql); |
122
|
|
|
self::assertInstanceOf(Statement::class, $stmt); |
123
|
|
|
|
124
|
|
|
$stmt->bindParam(1, $paramInt); |
125
|
|
|
$stmt->bindParam(2, $paramStr); |
126
|
|
|
$stmt->execute(); |
127
|
|
|
|
128
|
|
|
$rows = $stmt->fetchAll(FetchMode::MIXED); |
129
|
|
|
$rows[0] = array_change_key_case($rows[0], CASE_LOWER); |
130
|
|
|
self::assertEquals(['test_int' => 1, 'test_string' => 'foo', 0 => 1, 1 => 'foo'], $rows[0]); |
131
|
|
|
} |
132
|
|
|
|
133
|
|
|
public function testPrepareWithFetchColumn() : void |
134
|
|
|
{ |
135
|
|
|
$paramInt = 1; |
136
|
|
|
$paramStr = 'foo'; |
137
|
|
|
|
138
|
|
|
$sql = 'SELECT test_int FROM fetch_table WHERE test_int = ? AND test_string = ?'; |
139
|
|
|
$stmt = $this->connection->prepare($sql); |
140
|
|
|
self::assertInstanceOf(Statement::class, $stmt); |
141
|
|
|
|
142
|
|
|
$stmt->bindParam(1, $paramInt); |
143
|
|
|
$stmt->bindParam(2, $paramStr); |
144
|
|
|
$stmt->execute(); |
145
|
|
|
|
146
|
|
|
$column = $stmt->fetchColumn(); |
147
|
|
|
self::assertEquals(1, $column); |
148
|
|
|
} |
149
|
|
|
|
150
|
|
|
public function testPrepareWithIterator() : void |
151
|
|
|
{ |
152
|
|
|
$paramInt = 1; |
153
|
|
|
$paramStr = 'foo'; |
154
|
|
|
|
155
|
|
|
$sql = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?'; |
156
|
|
|
$stmt = $this->connection->prepare($sql); |
157
|
|
|
self::assertInstanceOf(Statement::class, $stmt); |
158
|
|
|
|
159
|
|
|
$stmt->bindParam(1, $paramInt); |
160
|
|
|
$stmt->bindParam(2, $paramStr); |
161
|
|
|
$stmt->execute(); |
162
|
|
|
|
163
|
|
|
$rows = []; |
164
|
|
|
$stmt->setFetchMode(FetchMode::ASSOCIATIVE); |
165
|
|
|
foreach ($stmt as $row) { |
166
|
|
|
$rows[] = array_change_key_case($row, CASE_LOWER); |
167
|
|
|
} |
168
|
|
|
|
169
|
|
|
self::assertEquals(['test_int' => 1, 'test_string' => 'foo'], $rows[0]); |
170
|
|
|
} |
171
|
|
|
|
172
|
|
|
public function testPrepareWithQuoted() : void |
173
|
|
|
{ |
174
|
|
|
$table = 'fetch_table'; |
175
|
|
|
$paramInt = 1; |
176
|
|
|
$paramStr = 'foo'; |
177
|
|
|
|
178
|
|
|
$stmt = $this->connection->prepare(sprintf( |
179
|
|
|
'SELECT test_int, test_string FROM %s WHERE test_int = %d AND test_string = %s', |
180
|
|
|
$this->connection->quoteIdentifier($table), |
181
|
|
|
$paramInt, |
182
|
|
|
$this->connection->quote($paramStr) |
183
|
|
|
)); |
184
|
|
|
self::assertInstanceOf(Statement::class, $stmt); |
185
|
|
|
} |
186
|
|
|
|
187
|
|
|
public function testPrepareWithExecuteParams() : void |
188
|
|
|
{ |
189
|
|
|
$paramInt = 1; |
190
|
|
|
$paramStr = 'foo'; |
191
|
|
|
|
192
|
|
|
$sql = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?'; |
193
|
|
|
$stmt = $this->connection->prepare($sql); |
194
|
|
|
self::assertInstanceOf(Statement::class, $stmt); |
195
|
|
|
$stmt->execute([$paramInt, $paramStr]); |
196
|
|
|
|
197
|
|
|
$row = $stmt->fetch(FetchMode::ASSOCIATIVE); |
198
|
|
|
self::assertNotFalse($row); |
199
|
|
|
$row = array_change_key_case($row, CASE_LOWER); |
200
|
|
|
self::assertEquals(['test_int' => 1, 'test_string' => 'foo'], $row); |
201
|
|
|
} |
202
|
|
|
|
203
|
|
|
public function testFetchAll() : void |
204
|
|
|
{ |
205
|
|
|
$sql = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?'; |
206
|
|
|
$data = $this->connection->fetchAll($sql, [1, 'foo']); |
207
|
|
|
|
208
|
|
|
self::assertCount(1, $data); |
209
|
|
|
|
210
|
|
|
$row = $data[0]; |
211
|
|
|
self::assertCount(2, $row); |
212
|
|
|
|
213
|
|
|
$row = array_change_key_case($row, CASE_LOWER); |
214
|
|
|
self::assertEquals(1, $row['test_int']); |
215
|
|
|
self::assertEquals('foo', $row['test_string']); |
216
|
|
|
} |
217
|
|
|
|
218
|
|
|
/** |
219
|
|
|
* @group DBAL-209 |
220
|
|
|
*/ |
221
|
|
|
public function testFetchAllWithTypes() : void |
222
|
|
|
{ |
223
|
|
|
$datetimeString = '2010-01-01 10:10:10'; |
224
|
|
|
$datetime = new DateTime($datetimeString); |
225
|
|
|
|
226
|
|
|
$sql = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?'; |
227
|
|
|
$data = $this->connection->fetchAll( |
228
|
|
|
$sql, |
229
|
|
|
[1, $datetime], |
230
|
|
|
[ParameterType::STRING, Types::DATETIME_MUTABLE] |
231
|
|
|
); |
232
|
|
|
|
233
|
|
|
self::assertCount(1, $data); |
234
|
|
|
|
235
|
|
|
$row = $data[0]; |
236
|
|
|
self::assertCount(2, $row); |
237
|
|
|
|
238
|
|
|
$row = array_change_key_case($row, CASE_LOWER); |
239
|
|
|
self::assertEquals(1, $row['test_int']); |
240
|
|
|
self::assertStringStartsWith($datetimeString, $row['test_datetime']); |
241
|
|
|
} |
242
|
|
|
|
243
|
|
|
/** |
244
|
|
|
* @group DBAL-209 |
245
|
|
|
*/ |
246
|
|
|
public function testFetchAllWithMissingTypes() : void |
247
|
|
|
{ |
248
|
|
|
if ($this->connection->getDriver() instanceof MySQLiDriver || |
249
|
|
|
$this->connection->getDriver() instanceof SQLSrvDriver) { |
250
|
|
|
self::markTestSkipped('mysqli and sqlsrv actually supports this'); |
251
|
|
|
} |
252
|
|
|
|
253
|
|
|
$datetimeString = '2010-01-01 10:10:10'; |
254
|
|
|
$datetime = new DateTime($datetimeString); |
255
|
|
|
$sql = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?'; |
256
|
|
|
|
257
|
|
|
$this->expectException(DBALException::class); |
258
|
|
|
|
259
|
|
|
$this->connection->fetchAll($sql, [1, $datetime]); |
260
|
|
|
} |
261
|
|
|
|
262
|
|
|
public function testFetchBoth() : void |
263
|
|
|
{ |
264
|
|
|
$sql = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?'; |
265
|
|
|
$row = $this->connection->executeQuery($sql, [1, 'foo'])->fetch(FetchMode::MIXED); |
266
|
|
|
|
267
|
|
|
self::assertNotFalse($row); |
268
|
|
|
|
269
|
|
|
$row = array_change_key_case($row, CASE_LOWER); |
270
|
|
|
|
271
|
|
|
self::assertEquals(1, $row['test_int']); |
272
|
|
|
self::assertEquals('foo', $row['test_string']); |
273
|
|
|
self::assertEquals(1, $row[0]); |
274
|
|
|
self::assertEquals('foo', $row[1]); |
275
|
|
|
} |
276
|
|
|
|
277
|
|
|
public function testFetchNoResult() : void |
278
|
|
|
{ |
279
|
|
|
self::assertFalse( |
280
|
|
|
$this->connection->executeQuery('SELECT test_int FROM fetch_table WHERE test_int = ?', [-1])->fetch() |
281
|
|
|
); |
282
|
|
|
} |
283
|
|
|
|
284
|
|
|
public function testFetchAssoc() : void |
285
|
|
|
{ |
286
|
|
|
$sql = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?'; |
287
|
|
|
$row = $this->connection->fetchAssoc($sql, [1, 'foo']); |
288
|
|
|
|
289
|
|
|
self::assertNotFalse($row); |
290
|
|
|
|
291
|
|
|
$row = array_change_key_case($row, CASE_LOWER); |
|
|
|
|
292
|
|
|
|
293
|
|
|
self::assertEquals(1, $row['test_int']); |
294
|
|
|
self::assertEquals('foo', $row['test_string']); |
295
|
|
|
} |
296
|
|
|
|
297
|
|
|
public function testFetchAssocWithTypes() : void |
298
|
|
|
{ |
299
|
|
|
$datetimeString = '2010-01-01 10:10:10'; |
300
|
|
|
$datetime = new DateTime($datetimeString); |
301
|
|
|
|
302
|
|
|
$sql = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?'; |
303
|
|
|
$row = $this->connection->fetchAssoc( |
304
|
|
|
$sql, |
305
|
|
|
[1, $datetime], |
306
|
|
|
[ParameterType::STRING, Types::DATETIME_MUTABLE] |
307
|
|
|
); |
308
|
|
|
|
309
|
|
|
self::assertNotFalse($row); |
310
|
|
|
|
311
|
|
|
$row = array_change_key_case($row, CASE_LOWER); |
|
|
|
|
312
|
|
|
|
313
|
|
|
self::assertEquals(1, $row['test_int']); |
314
|
|
|
self::assertStringStartsWith($datetimeString, $row['test_datetime']); |
315
|
|
|
} |
316
|
|
|
|
317
|
|
|
public function testFetchAssocWithMissingTypes() : void |
318
|
|
|
{ |
319
|
|
|
if ($this->connection->getDriver() instanceof MySQLiDriver || |
320
|
|
|
$this->connection->getDriver() instanceof SQLSrvDriver) { |
321
|
|
|
self::markTestSkipped('mysqli and sqlsrv actually supports this'); |
322
|
|
|
} |
323
|
|
|
|
324
|
|
|
$datetimeString = '2010-01-01 10:10:10'; |
325
|
|
|
$datetime = new DateTime($datetimeString); |
326
|
|
|
$sql = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?'; |
327
|
|
|
|
328
|
|
|
$this->expectException(DBALException::class); |
329
|
|
|
|
330
|
|
|
$this->connection->fetchAssoc($sql, [1, $datetime]); |
331
|
|
|
} |
332
|
|
|
|
333
|
|
|
public function testFetchArray() : void |
334
|
|
|
{ |
335
|
|
|
$sql = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?'; |
336
|
|
|
$row = $this->connection->fetchArray($sql, [1, 'foo']); |
337
|
|
|
|
338
|
|
|
self::assertIsArray($row); |
339
|
|
|
self::assertEquals(1, $row[0]); |
340
|
|
|
self::assertEquals('foo', $row[1]); |
341
|
|
|
} |
342
|
|
|
|
343
|
|
|
public function testFetchArrayWithTypes() : void |
344
|
|
|
{ |
345
|
|
|
$datetimeString = '2010-01-01 10:10:10'; |
346
|
|
|
$datetime = new DateTime($datetimeString); |
347
|
|
|
|
348
|
|
|
$sql = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?'; |
349
|
|
|
$row = $this->connection->fetchArray( |
350
|
|
|
$sql, |
351
|
|
|
[1, $datetime], |
352
|
|
|
[ParameterType::STRING, Types::DATETIME_MUTABLE] |
353
|
|
|
); |
354
|
|
|
|
355
|
|
|
self::assertNotFalse($row); |
356
|
|
|
|
357
|
|
|
$row = array_change_key_case($row, CASE_LOWER); |
|
|
|
|
358
|
|
|
|
359
|
|
|
self::assertEquals(1, $row[0]); |
360
|
|
|
self::assertStringStartsWith($datetimeString, $row[1]); |
361
|
|
|
} |
362
|
|
|
|
363
|
|
|
public function testFetchArrayWithMissingTypes() : void |
364
|
|
|
{ |
365
|
|
|
if ($this->connection->getDriver() instanceof MySQLiDriver || |
366
|
|
|
$this->connection->getDriver() instanceof SQLSrvDriver) { |
367
|
|
|
self::markTestSkipped('mysqli and sqlsrv actually supports this'); |
368
|
|
|
} |
369
|
|
|
|
370
|
|
|
$datetimeString = '2010-01-01 10:10:10'; |
371
|
|
|
$datetime = new DateTime($datetimeString); |
372
|
|
|
$sql = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?'; |
373
|
|
|
|
374
|
|
|
$this->expectException(DBALException::class); |
375
|
|
|
|
376
|
|
|
$this->connection->fetchArray($sql, [1, $datetime]); |
377
|
|
|
} |
378
|
|
|
|
379
|
|
|
public function testFetchColumn() : void |
380
|
|
|
{ |
381
|
|
|
$sql = 'SELECT test_int FROM fetch_table WHERE test_int = ? AND test_string = ?'; |
382
|
|
|
$testInt = $this->connection->fetchColumn($sql, [1, 'foo']); |
383
|
|
|
|
384
|
|
|
self::assertEquals(1, $testInt); |
385
|
|
|
|
386
|
|
|
$sql = 'SELECT test_string FROM fetch_table WHERE test_int = ? AND test_string = ?'; |
387
|
|
|
$testString = $this->connection->fetchColumn($sql, [1, 'foo']); |
388
|
|
|
|
389
|
|
|
self::assertEquals('foo', $testString); |
390
|
|
|
} |
391
|
|
|
|
392
|
|
|
public function testFetchColumnWithTypes() : void |
393
|
|
|
{ |
394
|
|
|
$datetimeString = '2010-01-01 10:10:10'; |
395
|
|
|
$datetime = new DateTime($datetimeString); |
396
|
|
|
|
397
|
|
|
$sql = 'SELECT test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?'; |
398
|
|
|
$column = $this->connection->fetchColumn( |
399
|
|
|
$sql, |
400
|
|
|
[1, $datetime], |
401
|
|
|
[ParameterType::STRING, Types::DATETIME_MUTABLE] |
402
|
|
|
); |
403
|
|
|
|
404
|
|
|
self::assertIsString($column); |
405
|
|
|
|
406
|
|
|
self::assertStringStartsWith($datetimeString, $column); |
|
|
|
|
407
|
|
|
} |
408
|
|
|
|
409
|
|
|
public function testFetchColumnWithMissingTypes() : void |
410
|
|
|
{ |
411
|
|
|
if ($this->connection->getDriver() instanceof MySQLiDriver || |
412
|
|
|
$this->connection->getDriver() instanceof SQLSrvDriver) { |
413
|
|
|
self::markTestSkipped('mysqli and sqlsrv actually supports this'); |
414
|
|
|
} |
415
|
|
|
|
416
|
|
|
$datetimeString = '2010-01-01 10:10:10'; |
417
|
|
|
$datetime = new DateTime($datetimeString); |
418
|
|
|
$sql = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?'; |
419
|
|
|
|
420
|
|
|
$this->expectException(DBALException::class); |
421
|
|
|
|
422
|
|
|
$this->connection->fetchColumn($sql, [1, $datetime]); |
423
|
|
|
} |
424
|
|
|
|
425
|
|
|
/** |
426
|
|
|
* @group DDC-697 |
427
|
|
|
*/ |
428
|
|
|
public function testExecuteQueryBindDateTimeType() : void |
429
|
|
|
{ |
430
|
|
|
$sql = 'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?'; |
431
|
|
|
$stmt = $this->connection->executeQuery( |
432
|
|
|
$sql, |
433
|
|
|
[1 => new DateTime('2010-01-01 10:10:10')], |
434
|
|
|
[1 => Types::DATETIME_MUTABLE] |
435
|
|
|
); |
436
|
|
|
|
437
|
|
|
self::assertEquals(1, $stmt->fetchColumn()); |
438
|
|
|
} |
439
|
|
|
|
440
|
|
|
/** |
441
|
|
|
* @group DDC-697 |
442
|
|
|
*/ |
443
|
|
|
public function testExecuteUpdateBindDateTimeType() : void |
444
|
|
|
{ |
445
|
|
|
$datetime = new DateTime('2010-02-02 20:20:20'); |
446
|
|
|
|
447
|
|
|
$sql = 'INSERT INTO fetch_table (test_int, test_string, test_datetime) VALUES (?, ?, ?)'; |
448
|
|
|
$affectedRows = $this->connection->executeUpdate($sql, [ |
449
|
|
|
1 => 50, |
450
|
|
|
2 => 'foo', |
451
|
|
|
3 => $datetime, |
452
|
|
|
], [ |
453
|
|
|
1 => ParameterType::INTEGER, |
454
|
|
|
2 => ParameterType::STRING, |
455
|
|
|
3 => Types::DATETIME_MUTABLE, |
456
|
|
|
]); |
457
|
|
|
|
458
|
|
|
self::assertEquals(1, $affectedRows); |
459
|
|
|
self::assertEquals(1, $this->connection->executeQuery( |
460
|
|
|
'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?', |
461
|
|
|
[1 => $datetime], |
462
|
|
|
[1 => Types::DATETIME_MUTABLE] |
463
|
|
|
)->fetchColumn()); |
464
|
|
|
} |
465
|
|
|
|
466
|
|
|
/** |
467
|
|
|
* @group DDC-697 |
468
|
|
|
*/ |
469
|
|
|
public function testPrepareQueryBindValueDateTimeType() : void |
470
|
|
|
{ |
471
|
|
|
$sql = 'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?'; |
472
|
|
|
$stmt = $this->connection->prepare($sql); |
473
|
|
|
$stmt->bindValue(1, new DateTime('2010-01-01 10:10:10'), Types::DATETIME_MUTABLE); |
474
|
|
|
$stmt->execute(); |
475
|
|
|
|
476
|
|
|
self::assertEquals(1, $stmt->fetchColumn()); |
477
|
|
|
} |
478
|
|
|
|
479
|
|
|
/** |
480
|
|
|
* @group DBAL-78 |
481
|
|
|
*/ |
482
|
|
|
public function testNativeArrayListSupport() : void |
483
|
|
|
{ |
484
|
|
|
for ($i = 100; $i < 110; $i++) { |
485
|
|
|
$this->connection->insert('fetch_table', ['test_int' => $i, 'test_string' => 'foo' . $i, 'test_datetime' => '2010-01-01 10:10:10']); |
486
|
|
|
} |
487
|
|
|
|
488
|
|
|
$stmt = $this->connection->executeQuery( |
489
|
|
|
'SELECT test_int FROM fetch_table WHERE test_int IN (?)', |
490
|
|
|
[[100, 101, 102, 103, 104]], |
491
|
|
|
[Connection::PARAM_INT_ARRAY] |
492
|
|
|
); |
493
|
|
|
|
494
|
|
|
$data = $stmt->fetchAll(FetchMode::NUMERIC); |
495
|
|
|
self::assertCount(5, $data); |
496
|
|
|
self::assertEquals([[100], [101], [102], [103], [104]], $data); |
497
|
|
|
|
498
|
|
|
$stmt = $this->connection->executeQuery( |
499
|
|
|
'SELECT test_int FROM fetch_table WHERE test_string IN (?)', |
500
|
|
|
[['foo100', 'foo101', 'foo102', 'foo103', 'foo104']], |
501
|
|
|
[Connection::PARAM_STR_ARRAY] |
502
|
|
|
); |
503
|
|
|
|
504
|
|
|
$data = $stmt->fetchAll(FetchMode::NUMERIC); |
505
|
|
|
self::assertCount(5, $data); |
506
|
|
|
self::assertEquals([[100], [101], [102], [103], [104]], $data); |
507
|
|
|
} |
508
|
|
|
|
509
|
|
|
/** |
510
|
|
|
* @dataProvider getTrimExpressionData |
511
|
|
|
*/ |
512
|
|
|
public function testTrimExpression(string $value, int $position, ?string $char, string $expectedResult) : void |
513
|
|
|
{ |
514
|
|
|
$sql = 'SELECT ' . |
515
|
|
|
$this->connection->getDatabasePlatform()->getTrimExpression($value, $position, $char) . ' AS trimmed ' . |
516
|
|
|
'FROM fetch_table'; |
517
|
|
|
|
518
|
|
|
$row = $this->connection->fetchAssoc($sql); |
519
|
|
|
self::assertIsArray($row); |
520
|
|
|
|
521
|
|
|
$row = array_change_key_case($row, CASE_LOWER); |
|
|
|
|
522
|
|
|
|
523
|
|
|
self::assertEquals($expectedResult, $row['trimmed']); |
524
|
|
|
} |
525
|
|
|
|
526
|
|
|
/** |
527
|
|
|
* @return array<int, array<int, mixed>> |
528
|
|
|
*/ |
529
|
|
|
public static function getTrimExpressionData() : iterable |
530
|
|
|
{ |
531
|
|
|
return [ |
532
|
|
|
['test_string', TrimMode::UNSPECIFIED, null, 'foo'], |
533
|
|
|
['test_string', TrimMode::LEADING, null, 'foo'], |
534
|
|
|
['test_string', TrimMode::TRAILING, null, 'foo'], |
535
|
|
|
['test_string', TrimMode::BOTH, null, 'foo'], |
536
|
|
|
['test_string', TrimMode::UNSPECIFIED, "'f'", 'oo'], |
537
|
|
|
['test_string', TrimMode::UNSPECIFIED, "'o'", 'f'], |
538
|
|
|
['test_string', TrimMode::UNSPECIFIED, "'.'", 'foo'], |
539
|
|
|
['test_string', TrimMode::LEADING, "'f'", 'oo'], |
540
|
|
|
['test_string', TrimMode::LEADING, "'o'", 'foo'], |
541
|
|
|
['test_string', TrimMode::LEADING, "'.'", 'foo'], |
542
|
|
|
['test_string', TrimMode::TRAILING, "'f'", 'foo'], |
543
|
|
|
['test_string', TrimMode::TRAILING, "'o'", 'f'], |
544
|
|
|
['test_string', TrimMode::TRAILING, "'.'", 'foo'], |
545
|
|
|
['test_string', TrimMode::BOTH, "'f'", 'oo'], |
546
|
|
|
['test_string', TrimMode::BOTH, "'o'", 'f'], |
547
|
|
|
['test_string', TrimMode::BOTH, "'.'", 'foo'], |
548
|
|
|
["' foo '", TrimMode::UNSPECIFIED, null, 'foo'], |
549
|
|
|
["' foo '", TrimMode::LEADING, null, 'foo '], |
550
|
|
|
["' foo '", TrimMode::TRAILING, null, ' foo'], |
551
|
|
|
["' foo '", TrimMode::BOTH, null, 'foo'], |
552
|
|
|
["' foo '", TrimMode::UNSPECIFIED, "'f'", ' foo '], |
553
|
|
|
["' foo '", TrimMode::UNSPECIFIED, "'o'", ' foo '], |
554
|
|
|
["' foo '", TrimMode::UNSPECIFIED, "'.'", ' foo '], |
555
|
|
|
["' foo '", TrimMode::UNSPECIFIED, "' '", 'foo'], |
556
|
|
|
["' foo '", TrimMode::LEADING, "'f'", ' foo '], |
557
|
|
|
["' foo '", TrimMode::LEADING, "'o'", ' foo '], |
558
|
|
|
["' foo '", TrimMode::LEADING, "'.'", ' foo '], |
559
|
|
|
["' foo '", TrimMode::LEADING, "' '", 'foo '], |
560
|
|
|
["' foo '", TrimMode::TRAILING, "'f'", ' foo '], |
561
|
|
|
["' foo '", TrimMode::TRAILING, "'o'", ' foo '], |
562
|
|
|
["' foo '", TrimMode::TRAILING, "'.'", ' foo '], |
563
|
|
|
["' foo '", TrimMode::TRAILING, "' '", ' foo'], |
564
|
|
|
["' foo '", TrimMode::BOTH, "'f'", ' foo '], |
565
|
|
|
["' foo '", TrimMode::BOTH, "'o'", ' foo '], |
566
|
|
|
["' foo '", TrimMode::BOTH, "'.'", ' foo '], |
567
|
|
|
["' foo '", TrimMode::BOTH, "' '", 'foo'], |
568
|
|
|
]; |
569
|
|
|
} |
570
|
|
|
|
571
|
|
|
public function testTrimExpressionInvalidMode() : void |
572
|
|
|
{ |
573
|
|
|
$this->expectException(InvalidArgumentException::class); |
574
|
|
|
$this->connection->getDatabasePlatform()->getTrimExpression('Trim me!', 0xBEEF); |
575
|
|
|
} |
576
|
|
|
|
577
|
|
|
/** |
578
|
|
|
* @dataProvider modeProvider |
579
|
|
|
*/ |
580
|
|
|
public function testDateAddSeconds(callable $buildQuery, callable $bindParams) : void |
581
|
|
|
{ |
582
|
|
|
$this->assertDateExpression( |
583
|
|
|
$buildQuery, |
584
|
|
|
$bindParams, |
585
|
|
|
static function (AbstractPlatform $platform, string $interval) : string { |
586
|
|
|
return $platform->getDateAddSecondsExpression('test_datetime', $interval); |
587
|
|
|
}, |
588
|
|
|
1, |
589
|
|
|
'2010-01-01 10:10:11' |
590
|
|
|
); |
591
|
|
|
} |
592
|
|
|
|
593
|
|
|
/** |
594
|
|
|
* @dataProvider modeProvider |
595
|
|
|
*/ |
596
|
|
|
public function testDateSubSeconds(callable $buildQuery, callable $bindParams) : void |
597
|
|
|
{ |
598
|
|
|
$this->assertDateExpression( |
599
|
|
|
$buildQuery, |
600
|
|
|
$bindParams, |
601
|
|
|
static function (AbstractPlatform $platform, string $interval) : string { |
602
|
|
|
return $platform->getDateSubSecondsExpression('test_datetime', $interval); |
603
|
|
|
}, |
604
|
|
|
1, |
605
|
|
|
'2010-01-01 10:10:09' |
606
|
|
|
); |
607
|
|
|
} |
608
|
|
|
|
609
|
|
|
/** |
610
|
|
|
* @dataProvider modeProvider |
611
|
|
|
*/ |
612
|
|
|
public function testDateAddMinutes(callable $buildQuery, callable $bindParams) : void |
613
|
|
|
{ |
614
|
|
|
$this->assertDateExpression( |
615
|
|
|
$buildQuery, |
616
|
|
|
$bindParams, |
617
|
|
|
static function (AbstractPlatform $platform, string $interval) : string { |
618
|
|
|
return $platform->getDateAddMinutesExpression('test_datetime', $interval); |
619
|
|
|
}, |
620
|
|
|
5, |
621
|
|
|
'2010-01-01 10:15:10' |
622
|
|
|
); |
623
|
|
|
} |
624
|
|
|
|
625
|
|
|
/** |
626
|
|
|
* @dataProvider modeProvider |
627
|
|
|
*/ |
628
|
|
|
public function testDateSubMinutes(callable $buildQuery, callable $bindParams) : void |
629
|
|
|
{ |
630
|
|
|
$this->assertDateExpression( |
631
|
|
|
$buildQuery, |
632
|
|
|
$bindParams, |
633
|
|
|
static function (AbstractPlatform $platform, string $interval) : string { |
634
|
|
|
return $platform->getDateSubMinutesExpression('test_datetime', $interval); |
635
|
|
|
}, |
636
|
|
|
5, |
637
|
|
|
'2010-01-01 10:05:10' |
638
|
|
|
); |
639
|
|
|
} |
640
|
|
|
|
641
|
|
|
/** |
642
|
|
|
* @dataProvider modeProvider |
643
|
|
|
*/ |
644
|
|
|
public function testDateAddHours(callable $buildQuery, callable $bindParams) : void |
645
|
|
|
{ |
646
|
|
|
$this->assertDateExpression( |
647
|
|
|
$buildQuery, |
648
|
|
|
$bindParams, |
649
|
|
|
static function (AbstractPlatform $platform, string $interval) : string { |
650
|
|
|
return $platform->getDateAddHourExpression('test_datetime', $interval); |
651
|
|
|
}, |
652
|
|
|
3, |
653
|
|
|
'2010-01-01 13:10:10' |
654
|
|
|
); |
655
|
|
|
} |
656
|
|
|
|
657
|
|
|
/** |
658
|
|
|
* @dataProvider modeProvider |
659
|
|
|
*/ |
660
|
|
|
public function testDateSubHours(callable $buildQuery, callable $bindParams) : void |
661
|
|
|
{ |
662
|
|
|
$this->assertDateExpression( |
663
|
|
|
$buildQuery, |
664
|
|
|
$bindParams, |
665
|
|
|
static function (AbstractPlatform $platform, string $interval) : string { |
666
|
|
|
return $platform->getDateSubHourExpression('test_datetime', $interval); |
667
|
|
|
}, |
668
|
|
|
3, |
669
|
|
|
'2010-01-01 07:10:10' |
670
|
|
|
); |
671
|
|
|
} |
672
|
|
|
|
673
|
|
|
/** |
674
|
|
|
* @dataProvider modeProvider |
675
|
|
|
*/ |
676
|
|
|
public function testDateAddDays(callable $buildQuery, callable $bindParams) : void |
677
|
|
|
{ |
678
|
|
|
$this->assertDateExpression( |
679
|
|
|
$buildQuery, |
680
|
|
|
$bindParams, |
681
|
|
|
static function (AbstractPlatform $platform, string $interval) : string { |
682
|
|
|
return $platform->getDateAddDaysExpression('test_datetime', $interval); |
683
|
|
|
}, |
684
|
|
|
10, |
685
|
|
|
'2010-01-11 10:10:10' |
686
|
|
|
); |
687
|
|
|
} |
688
|
|
|
|
689
|
|
|
/** |
690
|
|
|
* @dataProvider modeProvider |
691
|
|
|
*/ |
692
|
|
|
public function testDateSubDays(callable $buildQuery, callable $bindParams) : void |
693
|
|
|
{ |
694
|
|
|
$this->assertDateExpression( |
695
|
|
|
$buildQuery, |
696
|
|
|
$bindParams, |
697
|
|
|
static function (AbstractPlatform $platform, string $interval) : string { |
698
|
|
|
return $platform->getDateSubDaysExpression('test_datetime', $interval); |
699
|
|
|
}, |
700
|
|
|
10, |
701
|
|
|
'2009-12-22 10:10:10' |
702
|
|
|
); |
703
|
|
|
} |
704
|
|
|
|
705
|
|
|
/** |
706
|
|
|
* @dataProvider modeProvider |
707
|
|
|
*/ |
708
|
|
|
public function testDateAddWeeks(callable $buildQuery, callable $bindParams) : void |
709
|
|
|
{ |
710
|
|
|
$this->assertDateExpression( |
711
|
|
|
$buildQuery, |
712
|
|
|
$bindParams, |
713
|
|
|
static function (AbstractPlatform $platform, string $interval) : string { |
714
|
|
|
return $platform->getDateAddWeeksExpression('test_datetime', $interval); |
715
|
|
|
}, |
716
|
|
|
1, |
717
|
|
|
'2010-01-08 10:10:10' |
718
|
|
|
); |
719
|
|
|
} |
720
|
|
|
|
721
|
|
|
/** |
722
|
|
|
* @dataProvider modeProvider |
723
|
|
|
*/ |
724
|
|
|
public function testDateSubWeeks(callable $buildQuery, callable $bindParams) : void |
725
|
|
|
{ |
726
|
|
|
$this->assertDateExpression( |
727
|
|
|
$buildQuery, |
728
|
|
|
$bindParams, |
729
|
|
|
static function (AbstractPlatform $platform, string $interval) : string { |
730
|
|
|
return $platform->getDateSubWeeksExpression('test_datetime', $interval); |
731
|
|
|
}, |
732
|
|
|
1, |
733
|
|
|
'2009-12-25 10:10:10' |
734
|
|
|
); |
735
|
|
|
} |
736
|
|
|
|
737
|
|
|
/** |
738
|
|
|
* @dataProvider modeProvider |
739
|
|
|
*/ |
740
|
|
|
public function testDateAddMonths(callable $buildQuery, callable $bindParams) : void |
741
|
|
|
{ |
742
|
|
|
$this->assertDateExpression( |
743
|
|
|
$buildQuery, |
744
|
|
|
$bindParams, |
745
|
|
|
static function (AbstractPlatform $platform, string $interval) : string { |
746
|
|
|
return $platform->getDateAddMonthExpression('test_datetime', $interval); |
747
|
|
|
}, |
748
|
|
|
2, |
749
|
|
|
'2010-03-01 10:10:10' |
750
|
|
|
); |
751
|
|
|
} |
752
|
|
|
|
753
|
|
|
/** |
754
|
|
|
* @dataProvider modeProvider |
755
|
|
|
*/ |
756
|
|
|
public function testDateSubMonths(callable $buildQuery, callable $bindParams) : void |
757
|
|
|
{ |
758
|
|
|
$this->assertDateExpression( |
759
|
|
|
$buildQuery, |
760
|
|
|
$bindParams, |
761
|
|
|
static function (AbstractPlatform $platform, string $interval) : string { |
762
|
|
|
return $platform->getDateSubMonthExpression('test_datetime', $interval); |
763
|
|
|
}, |
764
|
|
|
2, |
765
|
|
|
'2009-11-01 10:10:10' |
766
|
|
|
); |
767
|
|
|
} |
768
|
|
|
|
769
|
|
|
/** |
770
|
|
|
* @dataProvider modeProvider |
771
|
|
|
*/ |
772
|
|
|
public function testDateAddQuarters(callable $buildQuery, callable $bindParams) : void |
773
|
|
|
{ |
774
|
|
|
$this->assertDateExpression( |
775
|
|
|
$buildQuery, |
776
|
|
|
$bindParams, |
777
|
|
|
static function (AbstractPlatform $platform, string $interval) : string { |
778
|
|
|
return $platform->getDateAddQuartersExpression('test_datetime', $interval); |
779
|
|
|
}, |
780
|
|
|
3, |
781
|
|
|
'2010-10-01 10:10:10' |
782
|
|
|
); |
783
|
|
|
} |
784
|
|
|
|
785
|
|
|
/** |
786
|
|
|
* @dataProvider modeProvider |
787
|
|
|
*/ |
788
|
|
|
public function testDateSubQuarters(callable $buildQuery, callable $bindParams) : void |
789
|
|
|
{ |
790
|
|
|
$this->assertDateExpression( |
791
|
|
|
$buildQuery, |
792
|
|
|
$bindParams, |
793
|
|
|
static function (AbstractPlatform $platform, string $interval) : string { |
794
|
|
|
return $platform->getDateSubQuartersExpression('test_datetime', $interval); |
795
|
|
|
}, |
796
|
|
|
3, |
797
|
|
|
'2009-04-01 10:10:10' |
798
|
|
|
); |
799
|
|
|
} |
800
|
|
|
|
801
|
|
|
/** |
802
|
|
|
* @dataProvider modeProvider |
803
|
|
|
*/ |
804
|
|
|
public function testDateAddYears(callable $buildQuery, callable $bindParams) : void |
805
|
|
|
{ |
806
|
|
|
$this->assertDateExpression( |
807
|
|
|
$buildQuery, |
808
|
|
|
$bindParams, |
809
|
|
|
static function (AbstractPlatform $platform, string $interval) : string { |
810
|
|
|
return $platform->getDateAddYearsExpression('test_datetime', $interval); |
811
|
|
|
}, |
812
|
|
|
6, |
813
|
|
|
'2016-01-01 10:10:10' |
814
|
|
|
); |
815
|
|
|
} |
816
|
|
|
|
817
|
|
|
/** |
818
|
|
|
* @dataProvider modeProvider |
819
|
|
|
*/ |
820
|
|
|
public function testDateSubYears(callable $buildQuery, callable $bindParams) : void |
821
|
|
|
{ |
822
|
|
|
$this->assertDateExpression( |
823
|
|
|
$buildQuery, |
824
|
|
|
$bindParams, |
825
|
|
|
static function (AbstractPlatform $platform, string $interval) : string { |
826
|
|
|
return $platform->getDateSubYearsExpression('test_datetime', $interval); |
827
|
|
|
}, |
828
|
|
|
6, |
829
|
|
|
'2004-01-01 10:10:10' |
830
|
|
|
); |
831
|
|
|
} |
832
|
|
|
|
833
|
|
|
/** |
834
|
|
|
* @param callable $buildQuery Builds the portion of the query representing the interval value |
835
|
|
|
* @param callable $bindParams Binds the interval value to the statement |
836
|
|
|
* @param callable $expression Builds the platform-specific interval expression |
837
|
|
|
* @param int $interval Interval value |
838
|
|
|
* @param string $expected Expected value |
839
|
|
|
*/ |
840
|
|
|
private function assertDateExpression(callable $buildQuery, callable $bindParams, callable $expression, int $interval, string $expected) : void |
841
|
|
|
{ |
842
|
|
|
$connection = $this->connection; |
843
|
|
|
$platform = $connection->getDatabasePlatform(); |
844
|
|
|
|
845
|
|
|
$query = sprintf('SELECT %s FROM fetch_table', $expression($platform, $buildQuery($interval))); |
846
|
|
|
$stmt = $connection->prepare($query); |
847
|
|
|
$bindParams($stmt, $interval); |
848
|
|
|
|
849
|
|
|
$stmt->execute(); |
850
|
|
|
|
851
|
|
|
$date = $stmt->fetchColumn(); |
852
|
|
|
|
853
|
|
|
self::assertEquals($expected, date('Y-m-d H:i:s', strtotime($date))); |
|
|
|
|
854
|
|
|
} |
855
|
|
|
|
856
|
|
|
/** |
857
|
|
|
* @return mixed[][] |
858
|
|
|
*/ |
859
|
|
|
public static function modeProvider() : array |
860
|
|
|
{ |
861
|
|
|
return [ |
862
|
|
|
'bind' => [ |
863
|
|
|
static function (int $interval) : string { |
|
|
|
|
864
|
|
|
return '?'; |
865
|
|
|
}, |
866
|
|
|
static function (Statement $stmt, int $interval) : void { |
867
|
|
|
$stmt->bindParam(1, $interval, ParameterType::INTEGER); |
868
|
|
|
}, |
869
|
|
|
], |
870
|
|
|
'literal' => [ |
871
|
|
|
static function (int $interval) : string { |
872
|
|
|
return sprintf('%d', $interval); |
873
|
|
|
}, |
874
|
|
|
static function (Statement $stmt, int $interval) : void { |
|
|
|
|
875
|
|
|
}, |
876
|
|
|
], |
877
|
|
|
'expression' => [ |
878
|
|
|
static function (int $interval) : string { |
879
|
|
|
return sprintf('(0 + %d)', $interval); |
880
|
|
|
}, |
881
|
|
|
static function (Statement $stmt, int $interval) : void { |
|
|
|
|
882
|
|
|
}, |
883
|
|
|
], |
884
|
|
|
]; |
885
|
|
|
} |
886
|
|
|
|
887
|
|
|
public function testSqliteDateArithmeticWithDynamicInterval() : void |
888
|
|
|
{ |
889
|
|
|
$platform = $this->connection->getDatabasePlatform(); |
890
|
|
|
|
891
|
|
|
if (! $platform instanceof SqlitePlatform) { |
892
|
|
|
self::markTestSkipped('test is for sqlite only'); |
893
|
|
|
} |
894
|
|
|
|
895
|
|
|
$table = new Table('fetch_table_date_math'); |
896
|
|
|
$table->addColumn('test_date', 'date'); |
897
|
|
|
$table->addColumn('test_days', 'integer'); |
898
|
|
|
$table->setPrimaryKey(['test_date']); |
899
|
|
|
|
900
|
|
|
$sm = $this->connection->getSchemaManager(); |
901
|
|
|
$sm->createTable($table); |
902
|
|
|
|
903
|
|
|
$this->connection->insert('fetch_table_date_math', ['test_date' => '2010-01-01', 'test_days' => 10]); |
904
|
|
|
$this->connection->insert('fetch_table_date_math', ['test_date' => '2010-06-01', 'test_days' => 20]); |
905
|
|
|
|
906
|
|
|
$sql = 'SELECT COUNT(*) FROM fetch_table_date_math WHERE '; |
907
|
|
|
$sql .= $platform->getDateSubDaysExpression('test_date', 'test_days') . " < '2010-05-12'"; |
908
|
|
|
|
909
|
|
|
$rowCount = $this->connection->fetchColumn($sql); |
910
|
|
|
|
911
|
|
|
self::assertEquals(1, $rowCount); |
912
|
|
|
} |
913
|
|
|
|
914
|
|
|
public function testLocateExpression() : void |
915
|
|
|
{ |
916
|
|
|
$platform = $this->connection->getDatabasePlatform(); |
917
|
|
|
|
918
|
|
|
$sql = 'SELECT '; |
919
|
|
|
$sql .= $platform->getLocateExpression('test_string', "'oo'") . ' AS locate1, '; |
920
|
|
|
$sql .= $platform->getLocateExpression('test_string', "'foo'") . ' AS locate2, '; |
921
|
|
|
$sql .= $platform->getLocateExpression('test_string', "'bar'") . ' AS locate3, '; |
922
|
|
|
$sql .= $platform->getLocateExpression('test_string', 'test_string') . ' AS locate4, '; |
923
|
|
|
$sql .= $platform->getLocateExpression("'foo'", 'test_string') . ' AS locate5, '; |
924
|
|
|
$sql .= $platform->getLocateExpression("'barfoobaz'", 'test_string') . ' AS locate6, '; |
925
|
|
|
$sql .= $platform->getLocateExpression("'bar'", 'test_string') . ' AS locate7, '; |
926
|
|
|
$sql .= $platform->getLocateExpression('test_string', "'oo'", '2') . ' AS locate8, '; |
927
|
|
|
$sql .= $platform->getLocateExpression('test_string', "'oo'", '3') . ' AS locate9 '; |
928
|
|
|
$sql .= 'FROM fetch_table'; |
929
|
|
|
|
930
|
|
|
$row = $this->connection->fetchAssoc($sql); |
931
|
|
|
assert(is_array($row)); |
932
|
|
|
|
933
|
|
|
$row = array_change_key_case($row, CASE_LOWER); |
934
|
|
|
|
935
|
|
|
self::assertEquals(2, $row['locate1']); |
936
|
|
|
self::assertEquals(1, $row['locate2']); |
937
|
|
|
self::assertEquals(0, $row['locate3']); |
938
|
|
|
self::assertEquals(1, $row['locate4']); |
939
|
|
|
self::assertEquals(1, $row['locate5']); |
940
|
|
|
self::assertEquals(4, $row['locate6']); |
941
|
|
|
self::assertEquals(0, $row['locate7']); |
942
|
|
|
self::assertEquals(2, $row['locate8']); |
943
|
|
|
self::assertEquals(0, $row['locate9']); |
944
|
|
|
} |
945
|
|
|
|
946
|
|
|
/** |
947
|
|
|
* @dataProvider substringExpressionProvider |
948
|
|
|
*/ |
949
|
|
|
public function testSubstringExpression(string $string, string $start, ?string $length, string $expected) : void |
950
|
|
|
{ |
951
|
|
|
$platform = $this->connection->getDatabasePlatform(); |
952
|
|
|
|
953
|
|
|
$query = $platform->getDummySelectSQL( |
954
|
|
|
$platform->getSubstringExpression($string, $start, $length) |
955
|
|
|
); |
956
|
|
|
|
957
|
|
|
self::assertEquals($expected, $this->connection->fetchColumn($query)); |
958
|
|
|
} |
959
|
|
|
|
960
|
|
|
/** |
961
|
|
|
* @return mixed[][] |
962
|
|
|
*/ |
963
|
|
|
public static function substringExpressionProvider() : iterable |
964
|
|
|
{ |
965
|
|
|
return [ |
966
|
|
|
'start-no-length' => [ |
967
|
|
|
"'abcdef'", |
968
|
|
|
'3', |
969
|
|
|
null, |
970
|
|
|
'cdef', |
971
|
|
|
], |
972
|
|
|
'start-with-length' => [ |
973
|
|
|
"'abcdef'", |
974
|
|
|
'2', |
975
|
|
|
'4', |
976
|
|
|
'bcde', |
977
|
|
|
], |
978
|
|
|
'expressions' => [ |
979
|
|
|
"'abcdef'", |
980
|
|
|
'1 + 1', |
981
|
|
|
'1 + 1', |
982
|
|
|
'bc', |
983
|
|
|
], |
984
|
|
|
]; |
985
|
|
|
} |
986
|
|
|
|
987
|
|
|
public function testQuoteSQLInjection() : void |
988
|
|
|
{ |
989
|
|
|
$sql = 'SELECT * FROM fetch_table WHERE test_string = ' . $this->connection->quote("bar' OR '1'='1"); |
990
|
|
|
$rows = $this->connection->fetchAll($sql); |
991
|
|
|
|
992
|
|
|
self::assertCount(0, $rows, 'no result should be returned, otherwise SQL injection is possible'); |
993
|
|
|
} |
994
|
|
|
|
995
|
|
|
/** |
996
|
|
|
* @group DDC-1213 |
997
|
|
|
*/ |
998
|
|
|
public function testBitComparisonExpressionSupport() : void |
999
|
|
|
{ |
1000
|
|
|
$this->connection->exec('DELETE FROM fetch_table'); |
1001
|
|
|
$platform = $this->connection->getDatabasePlatform(); |
1002
|
|
|
$bitmap = []; |
1003
|
|
|
|
1004
|
|
|
for ($i = 2; $i < 9; $i += 2) { |
1005
|
|
|
$bitmap[$i] = [ |
1006
|
|
|
'bit_or' => ($i | 2), |
1007
|
|
|
'bit_and' => ($i & 2), |
1008
|
|
|
]; |
1009
|
|
|
$this->connection->insert('fetch_table', [ |
1010
|
|
|
'test_int' => $i, |
1011
|
|
|
'test_string' => json_encode($bitmap[$i]), |
1012
|
|
|
'test_datetime' => '2010-01-01 10:10:10', |
1013
|
|
|
]); |
1014
|
|
|
} |
1015
|
|
|
|
1016
|
|
|
$sql = 'SELECT test_int, test_string' |
1017
|
|
|
. ', ' . $platform->getBitOrComparisonExpression('test_int', '2') . ' AS bit_or' |
1018
|
|
|
. ', ' . $platform->getBitAndComparisonExpression('test_int', '2') . ' AS bit_and' |
1019
|
|
|
. ' FROM fetch_table'; |
1020
|
|
|
|
1021
|
|
|
$stmt = $this->connection->executeQuery($sql); |
1022
|
|
|
$data = $stmt->fetchAll(FetchMode::ASSOCIATIVE); |
1023
|
|
|
|
1024
|
|
|
self::assertCount(4, $data); |
1025
|
|
|
self::assertEquals(count($bitmap), count($data)); |
1026
|
|
|
foreach ($data as $row) { |
1027
|
|
|
$row = array_change_key_case($row, CASE_LOWER); |
1028
|
|
|
|
1029
|
|
|
self::assertArrayHasKey('test_int', $row); |
1030
|
|
|
|
1031
|
|
|
$id = $row['test_int']; |
1032
|
|
|
|
1033
|
|
|
self::assertArrayHasKey($id, $bitmap); |
1034
|
|
|
self::assertArrayHasKey($id, $bitmap); |
1035
|
|
|
|
1036
|
|
|
self::assertArrayHasKey('bit_or', $row); |
1037
|
|
|
self::assertArrayHasKey('bit_and', $row); |
1038
|
|
|
|
1039
|
|
|
self::assertEquals($row['bit_or'], $bitmap[$id]['bit_or']); |
1040
|
|
|
self::assertEquals($row['bit_and'], $bitmap[$id]['bit_and']); |
1041
|
|
|
} |
1042
|
|
|
} |
1043
|
|
|
|
1044
|
|
|
public function testSetDefaultFetchMode() : void |
1045
|
|
|
{ |
1046
|
|
|
$stmt = $this->connection->query('SELECT * FROM fetch_table'); |
1047
|
|
|
$stmt->setFetchMode(FetchMode::NUMERIC); |
1048
|
|
|
|
1049
|
|
|
$row = array_keys($stmt->fetch()); |
1050
|
|
|
self::assertCount(0, array_filter($row, static function ($v) : bool { |
1051
|
|
|
return ! is_numeric($v); |
1052
|
|
|
}), 'should be no non-numerical elements in the result.'); |
1053
|
|
|
} |
1054
|
|
|
|
1055
|
|
|
/** |
1056
|
|
|
* @group DBAL-241 |
1057
|
|
|
*/ |
1058
|
|
|
public function testFetchAllStyleColumn() : void |
1059
|
|
|
{ |
1060
|
|
|
$sql = 'DELETE FROM fetch_table'; |
1061
|
|
|
$this->connection->executeUpdate($sql); |
1062
|
|
|
|
1063
|
|
|
$this->connection->insert('fetch_table', ['test_int' => 1, 'test_string' => 'foo']); |
1064
|
|
|
$this->connection->insert('fetch_table', ['test_int' => 10, 'test_string' => 'foo']); |
1065
|
|
|
|
1066
|
|
|
$sql = 'SELECT test_int FROM fetch_table'; |
1067
|
|
|
$rows = $this->connection->query($sql)->fetchAll(FetchMode::COLUMN); |
1068
|
|
|
|
1069
|
|
|
self::assertEquals([1, 10], $rows); |
1070
|
|
|
} |
1071
|
|
|
|
1072
|
|
|
/** |
1073
|
|
|
* @group DBAL-257 |
1074
|
|
|
*/ |
1075
|
|
|
public function testEmptyFetchColumnReturnsFalse() : void |
1076
|
|
|
{ |
1077
|
|
|
$this->connection->beginTransaction(); |
1078
|
|
|
$this->connection->exec('DELETE FROM fetch_table'); |
1079
|
|
|
self::assertFalse($this->connection->fetchColumn('SELECT test_int FROM fetch_table')); |
1080
|
|
|
self::assertFalse($this->connection->query('SELECT test_int FROM fetch_table')->fetchColumn()); |
1081
|
|
|
$this->connection->rollBack(); |
1082
|
|
|
} |
1083
|
|
|
|
1084
|
|
|
/** |
1085
|
|
|
* @group DBAL-339 |
1086
|
|
|
*/ |
1087
|
|
|
public function testSetFetchModeOnDbalStatement() : void |
1088
|
|
|
{ |
1089
|
|
|
$sql = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?'; |
1090
|
|
|
$stmt = $this->connection->executeQuery($sql, [1, 'foo']); |
1091
|
|
|
$stmt->setFetchMode(FetchMode::NUMERIC); |
1092
|
|
|
|
1093
|
|
|
$row = $stmt->fetch(); |
1094
|
|
|
|
1095
|
|
|
self::assertArrayHasKey(0, $row); |
1096
|
|
|
self::assertArrayHasKey(1, $row); |
1097
|
|
|
self::assertFalse($stmt->fetch()); |
1098
|
|
|
} |
1099
|
|
|
|
1100
|
|
|
/** |
1101
|
|
|
* @group DBAL-435 |
1102
|
|
|
*/ |
1103
|
|
|
public function testEmptyParameters() : void |
1104
|
|
|
{ |
1105
|
|
|
$sql = 'SELECT * FROM fetch_table WHERE test_int IN (?)'; |
1106
|
|
|
$stmt = $this->connection->executeQuery($sql, [[]], [Connection::PARAM_INT_ARRAY]); |
1107
|
|
|
$rows = $stmt->fetchAll(); |
1108
|
|
|
|
1109
|
|
|
self::assertEquals([], $rows); |
1110
|
|
|
} |
1111
|
|
|
|
1112
|
|
|
/** |
1113
|
|
|
* @group DBAL-1028 |
1114
|
|
|
*/ |
1115
|
|
|
public function testFetchColumnNoResult() : void |
1116
|
|
|
{ |
1117
|
|
|
self::assertFalse( |
1118
|
|
|
$this->connection->fetchColumn('SELECT test_int FROM fetch_table WHERE test_int = ?', [-1]) |
1119
|
|
|
); |
1120
|
|
|
} |
1121
|
|
|
} |
1122
|
|
|
|