DataAccessTest.php 24.6 KB
Newer Older
1 2
<?php

3
namespace Doctrine\DBAL\Tests\Functional;
4

Sergei Morozov's avatar
Sergei Morozov committed
5
use DateTime;
jeroendedauw's avatar
jeroendedauw committed
6
use Doctrine\DBAL\Connection;
7
use Doctrine\DBAL\ParameterType;
8
use Doctrine\DBAL\Platforms\SqlitePlatform;
9
use Doctrine\DBAL\Platforms\TrimMode;
10
use Doctrine\DBAL\Schema\Table;
Sergei Morozov's avatar
Sergei Morozov committed
11
use Doctrine\DBAL\Statement;
12
use Doctrine\DBAL\Tests\FunctionalTestCase;
13
use Doctrine\DBAL\Types\Types;
14

15 16 17 18
use function array_change_key_case;
use function count;
use function date;
use function json_encode;
Sergei Morozov's avatar
Sergei Morozov committed
19
use function sprintf;
20
use function strtotime;
21

Grégoire Paris's avatar
Grégoire Paris committed
22
use const CASE_LOWER;
23

24
class DataAccessTest extends FunctionalTestCase
25
{
Sergei Morozov's avatar
Sergei Morozov committed
26
    /** @var bool */
27
    private static $generated = false;
28

29
    protected function setUp(): void
30 31 32
    {
        parent::setUp();

Sergei Morozov's avatar
Sergei Morozov committed
33 34 35
        if (self::$generated !== false) {
            return;
        }
36

Sergei Morozov's avatar
Sergei Morozov committed
37 38 39 40 41
        $table = new Table('fetch_table');
        $table->addColumn('test_int', 'integer');
        $table->addColumn('test_string', 'string');
        $table->addColumn('test_datetime', 'datetime', ['notnull' => false]);
        $table->setPrimaryKey(['test_int']);
42

Sergei Morozov's avatar
Sergei Morozov committed
43
        $sm = $this->connection->getSchemaManager();
Sergei Morozov's avatar
Sergei Morozov committed
44 45
        $sm->createTable($table);

Sergei Morozov's avatar
Sergei Morozov committed
46
        $this->connection->insert('fetch_table', ['test_int' => 1, 'test_string' => 'foo', 'test_datetime' => '2010-01-01 10:10:10']);
Sergei Morozov's avatar
Sergei Morozov committed
47
        self::$generated = true;
48 49
    }

50
    public function testPrepareWithBindValue(): void
51
    {
Sergei Morozov's avatar
Sergei Morozov committed
52
        $sql  = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
53 54
        $stmt = $this->connection->prepare($sql);
        self::assertInstanceOf(Statement::class, $stmt);
55 56 57 58

        $stmt->bindValue(1, 1);
        $stmt->bindValue(2, 'foo');

59 60
        $row = $stmt->execute()->fetchAssociative();

61
        self::assertIsArray($row);
Sergei Morozov's avatar
Sergei Morozov committed
62 63
        $row = array_change_key_case($row, CASE_LOWER);
        self::assertEquals(['test_int' => 1, 'test_string' => 'foo'], $row);
64 65
    }

66
    public function testPrepareWithBindParam(): void
67 68 69 70
    {
        $paramInt = 1;
        $paramStr = 'foo';

Sergei Morozov's avatar
Sergei Morozov committed
71
        $sql  = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
72 73
        $stmt = $this->connection->prepare($sql);
        self::assertInstanceOf(Statement::class, $stmt);
74 75 76 77

        $stmt->bindParam(1, $paramInt);
        $stmt->bindParam(2, $paramStr);

78 79
        $row = $stmt->execute()->fetchAssociative();

80
        self::assertIsArray($row);
Sergei Morozov's avatar
Sergei Morozov committed
81 82
        $row = array_change_key_case($row, CASE_LOWER);
        self::assertEquals(['test_int' => 1, 'test_string' => 'foo'], $row);
83 84
    }

85
    public function testPrepareWithFetchAllAssociative(): void
86 87 88 89
    {
        $paramInt = 1;
        $paramStr = 'foo';

Sergei Morozov's avatar
Sergei Morozov committed
90
        $sql  = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
91 92
        $stmt = $this->connection->prepare($sql);
        self::assertInstanceOf(Statement::class, $stmt);
93 94 95 96

        $stmt->bindParam(1, $paramInt);
        $stmt->bindParam(2, $paramStr);

97
        $rows    = $stmt->execute()->fetchAllAssociative();
Sergei Morozov's avatar
Sergei Morozov committed
98 99
        $rows[0] = array_change_key_case($rows[0], CASE_LOWER);
        self::assertEquals(['test_int' => 1, 'test_string' => 'foo'], $rows[0]);
100 101
    }

102
    public function testPrepareWithFetchOne(): void
103 104 105 106
    {
        $paramInt = 1;
        $paramStr = 'foo';

Sergei Morozov's avatar
Sergei Morozov committed
107
        $sql  = 'SELECT test_int FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
108 109
        $stmt = $this->connection->prepare($sql);
        self::assertInstanceOf(Statement::class, $stmt);
110 111 112 113

        $stmt->bindParam(1, $paramInt);
        $stmt->bindParam(2, $paramStr);

114
        $column = $stmt->execute()->fetchOne();
115
        self::assertEquals(1, $column);
116 117
    }

118
    public function testPrepareWithQuoted(): void
119
    {
Sergei Morozov's avatar
Sergei Morozov committed
120
        $table    = 'fetch_table';
121 122 123
        $paramInt = 1;
        $paramStr = 'foo';

Sergei Morozov's avatar
Sergei Morozov committed
124 125 126 127 128 129 130
        $stmt = $this->connection->prepare(sprintf(
            'SELECT test_int, test_string FROM %s WHERE test_int = %s AND test_string = %s',
            $this->connection->quoteIdentifier($table),
            $this->connection->quote($paramInt),
            $this->connection->quote($paramStr)
        ));
        self::assertInstanceOf(Statement::class, $stmt);
131 132
    }

133
    public function testPrepareWithExecuteParams(): void
134 135 136 137
    {
        $paramInt = 1;
        $paramStr = 'foo';

Sergei Morozov's avatar
Sergei Morozov committed
138
        $sql  = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
139 140
        $stmt = $this->connection->prepare($sql);
        self::assertInstanceOf(Statement::class, $stmt);
141
        $result = $stmt->execute([$paramInt, $paramStr]);
142

143
        $row = $result->fetchAssociative();
Gabriel Caruso's avatar
Gabriel Caruso committed
144
        self::assertNotFalse($row);
Sergei Morozov's avatar
Sergei Morozov committed
145 146
        $row = array_change_key_case($row, CASE_LOWER);
        self::assertEquals(['test_int' => 1, 'test_string' => 'foo'], $row);
147 148
    }

149
    public function testFetchAllAssociative(): void
150
    {
Sergei Morozov's avatar
Sergei Morozov committed
151
        $sql  = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
152
        $data = $this->connection->fetchAllAssociative($sql, [1, 'foo']);
153

Gabriel Caruso's avatar
Gabriel Caruso committed
154
        self::assertCount(1, $data);
155 156

        $row = $data[0];
Gabriel Caruso's avatar
Gabriel Caruso committed
157
        self::assertCount(2, $row);
158

Sergei Morozov's avatar
Sergei Morozov committed
159
        $row = array_change_key_case($row, CASE_LOWER);
160 161
        self::assertEquals(1, $row['test_int']);
        self::assertEquals('foo', $row['test_string']);
162 163
    }

root's avatar
root committed
164 165 166
    /**
     * @group DBAL-209
     */
167
    public function testFetchAllWithTypes(): void
root's avatar
root committed
168 169
    {
        $datetimeString = '2010-01-01 10:10:10';
Sergei Morozov's avatar
Sergei Morozov committed
170
        $datetime       = new DateTime($datetimeString);
171

172
        $sql  = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?';
173
        $data = $this->connection->fetchAllAssociative(
174 175 176 177
            $sql,
            [1, $datetime],
            [ParameterType::STRING, Types::DATETIME_MUTABLE]
        );
root's avatar
root committed
178

Gabriel Caruso's avatar
Gabriel Caruso committed
179
        self::assertCount(1, $data);
root's avatar
root committed
180 181

        $row = $data[0];
Gabriel Caruso's avatar
Gabriel Caruso committed
182
        self::assertCount(2, $row);
root's avatar
root committed
183

Sergei Morozov's avatar
Sergei Morozov committed
184
        $row = array_change_key_case($row, CASE_LOWER);
185 186
        self::assertEquals(1, $row['test_int']);
        self::assertStringStartsWith($datetimeString, $row['test_datetime']);
root's avatar
root committed
187
    }
188

189
    public function testFetchNoResult(): void
190
    {
191
        self::assertFalse(
192
            $this->connection->executeQuery('SELECT test_int FROM fetch_table WHERE test_int = ?', [-1])->fetchAssociative()
193
        );
194 195
    }

196
    public function testFetchAssociative(): void
197
    {
Sergei Morozov's avatar
Sergei Morozov committed
198
        $sql = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
199
        $row = $this->connection->fetchAssociative($sql, [1, 'foo']);
200

Gabriel Caruso's avatar
Gabriel Caruso committed
201
        self::assertNotFalse($row);
202

Sergei Morozov's avatar
Sergei Morozov committed
203
        $row = array_change_key_case($row, CASE_LOWER);
204

205 206
        self::assertEquals(1, $row['test_int']);
        self::assertEquals('foo', $row['test_string']);
207 208
    }

209
    public function testFetchAssocWithTypes(): void
210 211
    {
        $datetimeString = '2010-01-01 10:10:10';
Sergei Morozov's avatar
Sergei Morozov committed
212
        $datetime       = new DateTime($datetimeString);
213

214
        $sql = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?';
215
        $row = $this->connection->fetchAssociative(
216 217 218 219
            $sql,
            [1, $datetime],
            [ParameterType::STRING, Types::DATETIME_MUTABLE]
        );
220

Gabriel Caruso's avatar
Gabriel Caruso committed
221
        self::assertNotFalse($row);
222

Sergei Morozov's avatar
Sergei Morozov committed
223
        $row = array_change_key_case($row, CASE_LOWER);
224

225 226
        self::assertEquals(1, $row['test_int']);
        self::assertStringStartsWith($datetimeString, $row['test_datetime']);
227 228
    }

229
    public function testFetchArray(): void
230
    {
Sergei Morozov's avatar
Sergei Morozov committed
231
        $sql = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
232
        $row = $this->connection->fetchNumeric($sql, [1, 'foo']);
233

234 235
        self::assertEquals(1, $row[0]);
        self::assertEquals('foo', $row[1]);
236 237
    }

238
    public function testFetchArrayWithTypes(): void
239 240
    {
        $datetimeString = '2010-01-01 10:10:10';
Sergei Morozov's avatar
Sergei Morozov committed
241
        $datetime       = new DateTime($datetimeString);
242

243
        $sql = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?';
244
        $row = $this->connection->fetchNumeric(
245 246 247 248
            $sql,
            [1, $datetime],
            [ParameterType::STRING, Types::DATETIME_MUTABLE]
        );
249

Gabriel Caruso's avatar
Gabriel Caruso committed
250
        self::assertNotFalse($row);
251

Sergei Morozov's avatar
Sergei Morozov committed
252
        $row = array_change_key_case($row, CASE_LOWER);
253

254 255
        self::assertEquals(1, $row[0]);
        self::assertStringStartsWith($datetimeString, $row[1]);
256 257
    }

258
    public function testFetchColumn(): void
259
    {
260
        $sql     = 'SELECT test_int FROM fetch_table WHERE test_int = ? AND test_string = ?';
261
        $testInt = $this->connection->fetchOne($sql, [1, 'foo']);
262

263
        self::assertEquals(1, $testInt);
264

265
        $sql        = 'SELECT test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
266
        $testString = $this->connection->fetchOne($sql, [1, 'foo']);
267

268
        self::assertEquals('foo', $testString);
269
    }
270

271
    public function testFetchOneWithTypes(): void
272 273
    {
        $datetimeString = '2010-01-01 10:10:10';
Sergei Morozov's avatar
Sergei Morozov committed
274
        $datetime       = new DateTime($datetimeString);
275

276
        $sql    = 'SELECT test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?';
277
        $column = $this->connection->fetchOne(
278 279 280 281
            $sql,
            [1, $datetime],
            [ParameterType::STRING, Types::DATETIME_MUTABLE]
        );
282

283
        self::assertIsString($column);
284

285
        self::assertStringStartsWith($datetimeString, $column);
286 287
    }

288 289 290
    /**
     * @group DDC-697
     */
291
    public function testExecuteQueryBindDateTimeType(): void
292
    {
293 294
        $value = $this->connection->fetchOne(
            'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?',
Sergei Morozov's avatar
Sergei Morozov committed
295
            [1 => new DateTime('2010-01-01 10:10:10')],
296
            [1 => Types::DATETIME_MUTABLE]
297 298
        );

299
        self::assertEquals(1, $value);
300 301 302 303 304
    }

    /**
     * @group DDC-697
     */
305
    public function testExecuteUpdateBindDateTimeType(): void
306
    {
Sergei Morozov's avatar
Sergei Morozov committed
307
        $datetime = new DateTime('2010-02-02 20:20:20');
308

Sergei Morozov's avatar
Sergei Morozov committed
309
        $sql          = 'INSERT INTO fetch_table (test_int, test_string, test_datetime) VALUES (?, ?, ?)';
Sergei Morozov's avatar
Sergei Morozov committed
310
        $affectedRows = $this->connection->executeUpdate($sql, [
311 312 313 314 315 316
            1 => 50,
            2 => 'foo',
            3 => $datetime,
        ], [
            1 => ParameterType::INTEGER,
            2 => ParameterType::STRING,
317
            3 => Types::DATETIME_MUTABLE,
318
        ]);
319

320
        self::assertEquals(1, $affectedRows);
Sergei Morozov's avatar
Sergei Morozov committed
321
        self::assertEquals(1, $this->connection->executeQuery(
322
            'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?',
Sergei Morozov's avatar
Sergei Morozov committed
323
            [1 => $datetime],
324
            [1 => Types::DATETIME_MUTABLE]
325
        )->fetchOne());
326 327 328 329 330
    }

    /**
     * @group DDC-697
     */
331
    public function testPrepareQueryBindValueDateTimeType(): void
332
    {
Sergei Morozov's avatar
Sergei Morozov committed
333
        $sql  = 'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?';
Sergei Morozov's avatar
Sergei Morozov committed
334
        $stmt = $this->connection->prepare($sql);
335
        $stmt->bindValue(1, new DateTime('2010-01-01 10:10:10'), Types::DATETIME_MUTABLE);
336
        $result = $stmt->execute();
337

338
        self::assertEquals(1, $result->fetchOne());
339
    }
340

341 342 343
    /**
     * @group DBAL-78
     */
344
    public function testNativeArrayListSupport(): void
345 346
    {
        for ($i = 100; $i < 110; $i++) {
Sergei Morozov's avatar
Sergei Morozov committed
347
            $this->connection->insert('fetch_table', ['test_int' => $i, 'test_string' => 'foo' . $i, 'test_datetime' => '2010-01-01 10:10:10']);
348
        }
349

350
        $result = $this->connection->executeQuery(
Sergei Morozov's avatar
Sergei Morozov committed
351 352 353 354
            'SELECT test_int FROM fetch_table WHERE test_int IN (?)',
            [[100, 101, 102, 103, 104]],
            [Connection::PARAM_INT_ARRAY]
        );
355

356
        $data = $result->fetchAllNumeric();
Gabriel Caruso's avatar
Gabriel Caruso committed
357
        self::assertCount(5, $data);
Sergei Morozov's avatar
Sergei Morozov committed
358
        self::assertEquals([[100], [101], [102], [103], [104]], $data);
359

360
        $result = $this->connection->executeQuery(
Sergei Morozov's avatar
Sergei Morozov committed
361 362 363 364
            'SELECT test_int FROM fetch_table WHERE test_string IN (?)',
            [['foo100', 'foo101', 'foo102', 'foo103', 'foo104']],
            [Connection::PARAM_STR_ARRAY]
        );
365

366
        $data = $result->fetchAllNumeric();
Gabriel Caruso's avatar
Gabriel Caruso committed
367
        self::assertCount(5, $data);
Sergei Morozov's avatar
Sergei Morozov committed
368
        self::assertEquals([[100], [101], [102], [103], [104]], $data);
369
    }
370

371
    /**
372 373
     * @param string|false $char
     *
374 375
     * @dataProvider getTrimExpressionData
     */
376
    public function testTrimExpression(string $value, int $position, $char, string $expectedResult): void
377 378
    {
        $sql = 'SELECT ' .
Sergei Morozov's avatar
Sergei Morozov committed
379
            $this->connection->getDatabasePlatform()->getTrimExpression($value, $position, $char) . ' AS trimmed ' .
380 381
            'FROM fetch_table';

382
        $row = $this->connection->fetchAssociative($sql);
383 384
        $row = array_change_key_case($row, CASE_LOWER);

385
        self::assertEquals($expectedResult, $row['trimmed']);
386 387
    }

388 389 390
    /**
     * @return array<int, array<int, mixed>>
     */
391
    public static function getTrimExpressionData(): iterable
392
    {
Sergei Morozov's avatar
Sergei Morozov committed
393
        return [
394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429
            ['test_string', TrimMode::UNSPECIFIED, false, 'foo'],
            ['test_string', TrimMode::LEADING, false, 'foo'],
            ['test_string', TrimMode::TRAILING, false, 'foo'],
            ['test_string', TrimMode::BOTH, false, 'foo'],
            ['test_string', TrimMode::UNSPECIFIED, "'f'", 'oo'],
            ['test_string', TrimMode::UNSPECIFIED, "'o'", 'f'],
            ['test_string', TrimMode::UNSPECIFIED, "'.'", 'foo'],
            ['test_string', TrimMode::LEADING, "'f'", 'oo'],
            ['test_string', TrimMode::LEADING, "'o'", 'foo'],
            ['test_string', TrimMode::LEADING, "'.'", 'foo'],
            ['test_string', TrimMode::TRAILING, "'f'", 'foo'],
            ['test_string', TrimMode::TRAILING, "'o'", 'f'],
            ['test_string', TrimMode::TRAILING, "'.'", 'foo'],
            ['test_string', TrimMode::BOTH, "'f'", 'oo'],
            ['test_string', TrimMode::BOTH, "'o'", 'f'],
            ['test_string', TrimMode::BOTH, "'.'", 'foo'],
            ["' foo '", TrimMode::UNSPECIFIED, false, 'foo'],
            ["' foo '", TrimMode::LEADING, false, 'foo '],
            ["' foo '", TrimMode::TRAILING, false, ' foo'],
            ["' foo '", TrimMode::BOTH, false, 'foo'],
            ["' foo '", TrimMode::UNSPECIFIED, "'f'", ' foo '],
            ["' foo '", TrimMode::UNSPECIFIED, "'o'", ' foo '],
            ["' foo '", TrimMode::UNSPECIFIED, "'.'", ' foo '],
            ["' foo '", TrimMode::UNSPECIFIED, "' '", 'foo'],
            ["' foo '", TrimMode::LEADING, "'f'", ' foo '],
            ["' foo '", TrimMode::LEADING, "'o'", ' foo '],
            ["' foo '", TrimMode::LEADING, "'.'", ' foo '],
            ["' foo '", TrimMode::LEADING, "' '", 'foo '],
            ["' foo '", TrimMode::TRAILING, "'f'", ' foo '],
            ["' foo '", TrimMode::TRAILING, "'o'", ' foo '],
            ["' foo '", TrimMode::TRAILING, "'.'", ' foo '],
            ["' foo '", TrimMode::TRAILING, "' '", ' foo'],
            ["' foo '", TrimMode::BOTH, "'f'", ' foo '],
            ["' foo '", TrimMode::BOTH, "'o'", ' foo '],
            ["' foo '", TrimMode::BOTH, "'.'", ' foo '],
            ["' foo '", TrimMode::BOTH, "' '", 'foo'],
Sergei Morozov's avatar
Sergei Morozov committed
430
        ];
431 432
    }

433 434 435
    /**
     * @group DDC-1014
     */
436
    public function testDateArithmetics(): void
437
    {
Sergei Morozov's avatar
Sergei Morozov committed
438
        $p    = $this->connection->getDatabasePlatform();
Sergei Morozov's avatar
Sergei Morozov committed
439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455
        $sql  = 'SELECT ';
        $sql .= $p->getDateAddSecondsExpression('test_datetime', 1) . ' AS add_seconds, ';
        $sql .= $p->getDateSubSecondsExpression('test_datetime', 1) . ' AS sub_seconds, ';
        $sql .= $p->getDateAddMinutesExpression('test_datetime', 5) . ' AS add_minutes, ';
        $sql .= $p->getDateSubMinutesExpression('test_datetime', 5) . ' AS sub_minutes, ';
        $sql .= $p->getDateAddHourExpression('test_datetime', 3) . ' AS add_hour, ';
        $sql .= $p->getDateSubHourExpression('test_datetime', 3) . ' AS sub_hour, ';
        $sql .= $p->getDateAddDaysExpression('test_datetime', 10) . ' AS add_days, ';
        $sql .= $p->getDateSubDaysExpression('test_datetime', 10) . ' AS sub_days, ';
        $sql .= $p->getDateAddWeeksExpression('test_datetime', 1) . ' AS add_weeks, ';
        $sql .= $p->getDateSubWeeksExpression('test_datetime', 1) . ' AS sub_weeks, ';
        $sql .= $p->getDateAddMonthExpression('test_datetime', 2) . ' AS add_month, ';
        $sql .= $p->getDateSubMonthExpression('test_datetime', 2) . ' AS sub_month, ';
        $sql .= $p->getDateAddQuartersExpression('test_datetime', 3) . ' AS add_quarters, ';
        $sql .= $p->getDateSubQuartersExpression('test_datetime', 3) . ' AS sub_quarters, ';
        $sql .= $p->getDateAddYearsExpression('test_datetime', 6) . ' AS add_years, ';
        $sql .= $p->getDateSubYearsExpression('test_datetime', 6) . ' AS sub_years ';
456 457
        $sql .= 'FROM fetch_table';

458
        $row = $this->connection->fetchAssociative($sql);
459
        $row = array_change_key_case($row, CASE_LOWER);
460

Sergei Morozov's avatar
Sergei Morozov committed
461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476
        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');
        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');
        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');
        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');
        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');
        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');
        self::assertEquals('2010-01-11', date('Y-m-d', strtotime($row['add_days'])), 'Adding date should end up on 2010-01-11');
        self::assertEquals('2009-12-22', date('Y-m-d', strtotime($row['sub_days'])), 'Subtracting date should end up on 2009-12-22');
        self::assertEquals('2010-01-08', date('Y-m-d', strtotime($row['add_weeks'])), 'Adding week should end up on 2010-01-08');
        self::assertEquals('2009-12-25', date('Y-m-d', strtotime($row['sub_weeks'])), 'Subtracting week should end up on 2009-12-25');
        self::assertEquals('2010-03-01', date('Y-m-d', strtotime($row['add_month'])), 'Adding month should end up on 2010-03-01');
        self::assertEquals('2009-11-01', date('Y-m-d', strtotime($row['sub_month'])), 'Subtracting month should end up on 2009-11-01');
        self::assertEquals('2010-10-01', date('Y-m-d', strtotime($row['add_quarters'])), 'Adding quarters should end up on 2010-04-01');
        self::assertEquals('2009-04-01', date('Y-m-d', strtotime($row['sub_quarters'])), 'Subtracting quarters should end up on 2009-10-01');
        self::assertEquals('2016-01-01', date('Y-m-d', strtotime($row['add_years'])), 'Adding years should end up on 2016-01-01');
        self::assertEquals('2004-01-01', date('Y-m-d', strtotime($row['sub_years'])), 'Subtracting years should end up on 2004-01-01');
477
    }
478

479
    public function testSqliteDateArithmeticWithDynamicInterval(): void
480
    {
Sergei Morozov's avatar
Sergei Morozov committed
481
        $platform = $this->connection->getDatabasePlatform();
482 483

        if (! $platform instanceof SqlitePlatform) {
484
            self::markTestSkipped('test is for sqlite only');
485 486 487 488 489 490 491
        }

        $table = new Table('fetch_table_date_math');
        $table->addColumn('test_date', 'date');
        $table->addColumn('test_days', 'integer');
        $table->setPrimaryKey(['test_date']);

Sergei Morozov's avatar
Sergei Morozov committed
492
        $sm = $this->connection->getSchemaManager();
493 494
        $sm->createTable($table);

Sergei Morozov's avatar
Sergei Morozov committed
495 496
        $this->connection->insert('fetch_table_date_math', ['test_date' => '2010-01-01', 'test_days' => 10]);
        $this->connection->insert('fetch_table_date_math', ['test_date' => '2010-06-01', 'test_days' => 20]);
497 498 499 500

        $sql  = 'SELECT COUNT(*) FROM fetch_table_date_math WHERE ';
        $sql .= $platform->getDateSubDaysExpression('test_date', 'test_days') . " < '2010-05-12'";

501
        $rowCount = $this->connection->fetchOne($sql);
502

503
        self::assertEquals(1, $rowCount);
504 505
    }

506
    public function testLocateExpression(): void
507
    {
Sergei Morozov's avatar
Sergei Morozov committed
508
        $platform = $this->connection->getDatabasePlatform();
509

Sergei Morozov's avatar
Sergei Morozov committed
510 511 512 513 514 515 516 517 518 519
        $sql  = 'SELECT ';
        $sql .= $platform->getLocateExpression('test_string', "'oo'") . ' AS locate1, ';
        $sql .= $platform->getLocateExpression('test_string', "'foo'") . ' AS locate2, ';
        $sql .= $platform->getLocateExpression('test_string', "'bar'") . ' AS locate3, ';
        $sql .= $platform->getLocateExpression('test_string', 'test_string') . ' AS locate4, ';
        $sql .= $platform->getLocateExpression("'foo'", 'test_string') . ' AS locate5, ';
        $sql .= $platform->getLocateExpression("'barfoobaz'", 'test_string') . ' AS locate6, ';
        $sql .= $platform->getLocateExpression("'bar'", 'test_string') . ' AS locate7, ';
        $sql .= $platform->getLocateExpression('test_string', "'oo'", 2) . ' AS locate8, ';
        $sql .= $platform->getLocateExpression('test_string', "'oo'", 3) . ' AS locate9 ';
520 521
        $sql .= 'FROM fetch_table';

522
        $row = $this->connection->fetchAssociative($sql);
523 524
        $row = array_change_key_case($row, CASE_LOWER);

525 526 527 528 529 530 531 532 533
        self::assertEquals(2, $row['locate1']);
        self::assertEquals(1, $row['locate2']);
        self::assertEquals(0, $row['locate3']);
        self::assertEquals(1, $row['locate4']);
        self::assertEquals(1, $row['locate5']);
        self::assertEquals(4, $row['locate6']);
        self::assertEquals(0, $row['locate7']);
        self::assertEquals(2, $row['locate8']);
        self::assertEquals(0, $row['locate9']);
534 535
    }

536
    public function testQuoteSQLInjection(): void
537
    {
Sergei Morozov's avatar
Sergei Morozov committed
538
        $sql  = 'SELECT * FROM fetch_table WHERE test_string = ' . $this->connection->quote("bar' OR '1'='1");
539
        $rows = $this->connection->fetchAllAssociative($sql);
540

Sergei Morozov's avatar
Sergei Morozov committed
541
        self::assertCount(0, $rows, 'no result should be returned, otherwise SQL injection is possible');
542
    }
543

Fabio B. Silva's avatar
Fabio B. Silva committed
544 545 546
    /**
     * @group DDC-1213
     */
547
    public function testBitComparisonExpressionSupport(): void
Fabio B. Silva's avatar
Fabio B. Silva committed
548
    {
Sergei Morozov's avatar
Sergei Morozov committed
549 550
        $this->connection->exec('DELETE FROM fetch_table');
        $platform = $this->connection->getDatabasePlatform();
Sergei Morozov's avatar
Sergei Morozov committed
551
        $bitmap   = [];
552

Sergei Morozov's avatar
Sergei Morozov committed
553 554
        for ($i = 2; $i < 9; $i += 2) {
            $bitmap[$i] = [
Fabio B. Silva's avatar
Fabio B. Silva committed
555
                'bit_or'    => ($i | 2),
Sergei Morozov's avatar
Sergei Morozov committed
556 557
                'bit_and'   => ($i & 2),
            ];
Sergei Morozov's avatar
Sergei Morozov committed
558
            $this->connection->insert('fetch_table', [
559
                'test_int'      => $i,
Fabio B. Silva's avatar
Fabio B. Silva committed
560
                'test_string'   => json_encode($bitmap[$i]),
Sergei Morozov's avatar
Sergei Morozov committed
561 562
                'test_datetime' => '2010-01-01 10:10:10',
            ]);
Fabio B. Silva's avatar
Fabio B. Silva committed
563
        }
564

565 566 567 568
        $sql = 'SELECT test_int, test_string'
            . ', ' . $platform->getBitOrComparisonExpression('test_int', 2) . ' AS bit_or'
            . ', ' . $platform->getBitAndComparisonExpression('test_int', 2) . ' AS bit_and'
            . ' FROM fetch_table';
Fabio B. Silva's avatar
Fabio B. Silva committed
569

570
        $data = $this->connection->fetchAllAssociative($sql);
571

Gabriel Caruso's avatar
Gabriel Caruso committed
572
        self::assertCount(4, $data);
573
        self::assertEquals(count($bitmap), count($data));
Fabio B. Silva's avatar
Fabio B. Silva committed
574
        foreach ($data as $row) {
575 576
            $row = array_change_key_case($row, CASE_LOWER);

577
            self::assertArrayHasKey('test_int', $row);
578

Fabio B. Silva's avatar
Fabio B. Silva committed
579
            $id = $row['test_int'];
580

581 582
            self::assertArrayHasKey($id, $bitmap);
            self::assertArrayHasKey($id, $bitmap);
583

584 585
            self::assertArrayHasKey('bit_or', $row);
            self::assertArrayHasKey('bit_and', $row);
586

587 588
            self::assertEquals($row['bit_or'], $bitmap[$id]['bit_or']);
            self::assertEquals($row['bit_and'], $bitmap[$id]['bit_and']);
Fabio B. Silva's avatar
Fabio B. Silva committed
589 590
        }
    }
591

592 593 594
    /**
     * @group DBAL-241
     */
595
    public function testFetchAllStyleColumn(): void
596
    {
Sergei Morozov's avatar
Sergei Morozov committed
597
        $sql = 'DELETE FROM fetch_table';
Sergei Morozov's avatar
Sergei Morozov committed
598
        $this->connection->executeUpdate($sql);
599

Sergei Morozov's avatar
Sergei Morozov committed
600 601
        $this->connection->insert('fetch_table', ['test_int' => 1, 'test_string' => 'foo']);
        $this->connection->insert('fetch_table', ['test_int' => 10, 'test_string' => 'foo']);
602

603
        $sql    = 'SELECT test_int FROM fetch_table';
604
        $values = $this->connection->query($sql)->fetchFirstColumn();
605

606
        self::assertEquals([1, 10], $values);
607
    }
608

609 610 611
    /**
     * @group DBAL-257
     */
612
    public function testEmptyFetchOneReturnsFalse(): void
613
    {
Sergei Morozov's avatar
Sergei Morozov committed
614 615
        $this->connection->beginTransaction();
        $this->connection->exec('DELETE FROM fetch_table');
616 617
        self::assertFalse($this->connection->fetchOne('SELECT test_int FROM fetch_table'));
        self::assertFalse($this->connection->query('SELECT test_int FROM fetch_table')->fetchOne());
Sergei Morozov's avatar
Sergei Morozov committed
618
        $this->connection->rollBack();
619 620
    }

621 622 623
    /**
     * @group DBAL-435
     */
624
    public function testEmptyParameters(): void
625
    {
Sergei Morozov's avatar
Sergei Morozov committed
626
        $sql  = 'SELECT * FROM fetch_table WHERE test_int IN (?)';
627
        $rows = $this->connection->fetchAllAssociative($sql, [[]], [Connection::PARAM_INT_ARRAY]);
628

Sergei Morozov's avatar
Sergei Morozov committed
629
        self::assertEquals([], $rows);
630 631
    }

632 633 634
    /**
     * @group DBAL-1028
     */
635
    public function testFetchOneNoResult(): void
636
    {
637
        self::assertFalse(
638
            $this->connection->fetchOne('SELECT test_int FROM fetch_table WHERE test_int = ?', [-1])
639 640
        );
    }
641
}