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

3 4
namespace Doctrine\Tests\DBAL\Functional;

Sergei Morozov's avatar
Sergei Morozov committed
5
use DateTime;
jeroendedauw's avatar
jeroendedauw committed
6
use Doctrine\DBAL\Connection;
Sergei Morozov's avatar
Sergei Morozov committed
7 8
use Doctrine\DBAL\Driver\Mysqli\Driver as MySQLiDriver;
use Doctrine\DBAL\Driver\SQLSrv\Driver as SQLSrvDriver;
9 10
use Doctrine\DBAL\FetchMode;
use Doctrine\DBAL\ParameterType;
11
use Doctrine\DBAL\Platforms\SqlitePlatform;
12
use Doctrine\DBAL\Platforms\TrimMode;
Sergei Morozov's avatar
Sergei Morozov committed
13
use Doctrine\DBAL\Schema\AbstractSchemaManager;
14
use Doctrine\DBAL\Schema\Table;
Sergei Morozov's avatar
Sergei Morozov committed
15
use Doctrine\DBAL\Statement;
16
use Doctrine\DBAL\Types\Type;
Sergei Morozov's avatar
Sergei Morozov committed
17
use Doctrine\Tests\DbalFunctionalTestCase;
18 19 20 21 22 23 24 25 26 27 28
use const CASE_LOWER;
use const PHP_EOL;
use function array_change_key_case;
use function array_filter;
use function array_keys;
use function count;
use function date;
use function implode;
use function is_numeric;
use function json_encode;
use function property_exists;
Sergei Morozov's avatar
Sergei Morozov committed
29
use function sprintf;
30
use function strtotime;
31

Sergei Morozov's avatar
Sergei Morozov committed
32
class DataAccessTest extends DbalFunctionalTestCase
33
{
Sergei Morozov's avatar
Sergei Morozov committed
34
    /** @var bool */
35 36
    static private $generated = false;

37
    protected function setUp()
38 39 40
    {
        parent::setUp();

Sergei Morozov's avatar
Sergei Morozov committed
41 42 43
        if (self::$generated !== false) {
            return;
        }
44

Sergei Morozov's avatar
Sergei Morozov committed
45 46 47 48 49 50
        /** @var AbstractSchemaManager $sm */
        $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']);
51

Sergei Morozov's avatar
Sergei Morozov committed
52
        $sm = $this->connection->getSchemaManager();
Sergei Morozov's avatar
Sergei Morozov committed
53 54
        $sm->createTable($table);

Sergei Morozov's avatar
Sergei Morozov committed
55
        $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
56
        self::$generated = true;
57 58
    }

59 60
    public function testPrepareWithBindValue()
    {
Sergei Morozov's avatar
Sergei Morozov committed
61
        $sql  = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
62 63
        $stmt = $this->connection->prepare($sql);
        self::assertInstanceOf(Statement::class, $stmt);
64 65 66 67 68

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

69
        $row = $stmt->fetch(FetchMode::ASSOCIATIVE);
Sergei Morozov's avatar
Sergei Morozov committed
70 71
        $row = array_change_key_case($row, CASE_LOWER);
        self::assertEquals(['test_int' => 1, 'test_string' => 'foo'], $row);
72 73 74 75 76 77 78
    }

    public function testPrepareWithBindParam()
    {
        $paramInt = 1;
        $paramStr = 'foo';

Sergei Morozov's avatar
Sergei Morozov committed
79
        $sql  = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
80 81
        $stmt = $this->connection->prepare($sql);
        self::assertInstanceOf(Statement::class, $stmt);
82 83 84 85 86

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

87
        $row = $stmt->fetch(FetchMode::ASSOCIATIVE);
Sergei Morozov's avatar
Sergei Morozov committed
88 89
        $row = array_change_key_case($row, CASE_LOWER);
        self::assertEquals(['test_int' => 1, 'test_string' => 'foo'], $row);
90 91 92 93 94 95 96
    }

    public function testPrepareWithFetchAll()
    {
        $paramInt = 1;
        $paramStr = 'foo';

Sergei Morozov's avatar
Sergei Morozov committed
97
        $sql  = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
98 99
        $stmt = $this->connection->prepare($sql);
        self::assertInstanceOf(Statement::class, $stmt);
100 101 102 103 104

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

105
        $rows    = $stmt->fetchAll(FetchMode::ASSOCIATIVE);
Sergei Morozov's avatar
Sergei Morozov committed
106 107
        $rows[0] = array_change_key_case($rows[0], CASE_LOWER);
        self::assertEquals(['test_int' => 1, 'test_string' => 'foo'], $rows[0]);
108 109
    }

110 111 112 113 114 115 116 117
    /**
     * @group DBAL-228
     */
    public function testPrepareWithFetchAllBoth()
    {
        $paramInt = 1;
        $paramStr = 'foo';

Sergei Morozov's avatar
Sergei Morozov committed
118
        $sql  = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
119 120
        $stmt = $this->connection->prepare($sql);
        self::assertInstanceOf(Statement::class, $stmt);
121 122 123 124 125

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

126
        $rows    = $stmt->fetchAll(FetchMode::MIXED);
Sergei Morozov's avatar
Sergei Morozov committed
127 128
        $rows[0] = array_change_key_case($rows[0], CASE_LOWER);
        self::assertEquals(['test_int' => 1, 'test_string' => 'foo', 0 => 1, 1 => 'foo'], $rows[0]);
129 130
    }

131 132 133 134 135
    public function testPrepareWithFetchColumn()
    {
        $paramInt = 1;
        $paramStr = 'foo';

Sergei Morozov's avatar
Sergei Morozov committed
136
        $sql  = 'SELECT test_int FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
137 138
        $stmt = $this->connection->prepare($sql);
        self::assertInstanceOf(Statement::class, $stmt);
139 140 141 142 143 144

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

        $column = $stmt->fetchColumn();
145
        self::assertEquals(1, $column);
146 147
    }

148 149 150 151 152
    public function testPrepareWithIterator()
    {
        $paramInt = 1;
        $paramStr = 'foo';

Sergei Morozov's avatar
Sergei Morozov committed
153
        $sql  = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
154 155
        $stmt = $this->connection->prepare($sql);
        self::assertInstanceOf(Statement::class, $stmt);
156 157 158 159 160

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

Sergei Morozov's avatar
Sergei Morozov committed
161
        $rows = [];
162
        $stmt->setFetchMode(FetchMode::ASSOCIATIVE);
163
        foreach ($stmt as $row) {
Sergei Morozov's avatar
Sergei Morozov committed
164
            $rows[] = array_change_key_case($row, CASE_LOWER);
165 166
        }

Sergei Morozov's avatar
Sergei Morozov committed
167
        self::assertEquals(['test_int' => 1, 'test_string' => 'foo'], $rows[0]);
168 169
    }

170 171
    public function testPrepareWithQuoted()
    {
Sergei Morozov's avatar
Sergei Morozov committed
172
        $table    = 'fetch_table';
173 174 175
        $paramInt = 1;
        $paramStr = 'foo';

Sergei Morozov's avatar
Sergei Morozov committed
176 177 178 179 180 181 182
        $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);
183 184 185 186 187 188 189
    }

    public function testPrepareWithExecuteParams()
    {
        $paramInt = 1;
        $paramStr = 'foo';

Sergei Morozov's avatar
Sergei Morozov committed
190
        $sql  = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
191 192
        $stmt = $this->connection->prepare($sql);
        self::assertInstanceOf(Statement::class, $stmt);
Sergei Morozov's avatar
Sergei Morozov committed
193
        $stmt->execute([$paramInt, $paramStr]);
194

195
        $row = $stmt->fetch(FetchMode::ASSOCIATIVE);
Gabriel Caruso's avatar
Gabriel Caruso committed
196
        self::assertNotFalse($row);
Sergei Morozov's avatar
Sergei Morozov committed
197 198
        $row = array_change_key_case($row, CASE_LOWER);
        self::assertEquals(['test_int' => 1, 'test_string' => 'foo'], $row);
199 200
    }

201 202
    public function testFetchAll()
    {
Sergei Morozov's avatar
Sergei Morozov committed
203
        $sql  = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
204
        $data = $this->connection->fetchAll($sql, [1, 'foo']);
205

Gabriel Caruso's avatar
Gabriel Caruso committed
206
        self::assertCount(1, $data);
207 208

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

Sergei Morozov's avatar
Sergei Morozov committed
211
        $row = array_change_key_case($row, CASE_LOWER);
212 213
        self::assertEquals(1, $row['test_int']);
        self::assertEquals('foo', $row['test_string']);
214 215
    }

root's avatar
root committed
216 217 218 219 220 221
    /**
     * @group DBAL-209
     */
    public function testFetchAllWithTypes()
    {
        $datetimeString = '2010-01-01 10:10:10';
Sergei Morozov's avatar
Sergei Morozov committed
222
        $datetime       = new DateTime($datetimeString);
223

224
        $sql  = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?';
Sergei Morozov's avatar
Sergei Morozov committed
225
        $data = $this->connection->fetchAll($sql, [1, $datetime], [ParameterType::STRING, Type::DATETIME]);
root's avatar
root committed
226

Gabriel Caruso's avatar
Gabriel Caruso committed
227
        self::assertCount(1, $data);
root's avatar
root committed
228 229

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

Sergei Morozov's avatar
Sergei Morozov committed
232
        $row = array_change_key_case($row, CASE_LOWER);
233 234
        self::assertEquals(1, $row['test_int']);
        self::assertStringStartsWith($datetimeString, $row['test_datetime']);
root's avatar
root committed
235
    }
236

root's avatar
root committed
237 238 239 240 241 242
    /**
     * @group DBAL-209
     * @expectedException \Doctrine\DBAL\DBALException
     */
    public function testFetchAllWithMissingTypes()
    {
Sergei Morozov's avatar
Sergei Morozov committed
243 244
        if ($this->connection->getDriver() instanceof MySQLiDriver ||
            $this->connection->getDriver() instanceof SQLSrvDriver) {
Steve Müller's avatar
Steve Müller committed
245
            $this->markTestSkipped('mysqli and sqlsrv actually supports this');
246 247
        }

root's avatar
root committed
248
        $datetimeString = '2010-01-01 10:10:10';
Sergei Morozov's avatar
Sergei Morozov committed
249 250
        $datetime       = new DateTime($datetimeString);
        $sql            = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?';
Sergei Morozov's avatar
Sergei Morozov committed
251
        $this->connection->fetchAll($sql, [1, $datetime]);
root's avatar
root committed
252 253
    }

254 255
    public function testFetchBoth()
    {
256
        $sql = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
257
        $row = $this->connection->executeQuery($sql, [1, 'foo'])->fetch(FetchMode::MIXED);
258

Gabriel Caruso's avatar
Gabriel Caruso committed
259
        self::assertNotFalse($row);
260

Sergei Morozov's avatar
Sergei Morozov committed
261
        $row = array_change_key_case($row, CASE_LOWER);
262

263 264 265 266
        self::assertEquals(1, $row['test_int']);
        self::assertEquals('foo', $row['test_string']);
        self::assertEquals(1, $row[0]);
        self::assertEquals('foo', $row[1]);
267 268
    }

269 270
    public function testFetchNoResult()
    {
271
        self::assertFalse(
Sergei Morozov's avatar
Sergei Morozov committed
272
            $this->connection->executeQuery('SELECT test_int FROM fetch_table WHERE test_int = ?', [-1])->fetch()
273
        );
274 275
    }

276
    public function testFetchAssoc()
277
    {
Sergei Morozov's avatar
Sergei Morozov committed
278
        $sql = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
279
        $row = $this->connection->fetchAssoc($sql, [1, 'foo']);
280

Gabriel Caruso's avatar
Gabriel Caruso committed
281
        self::assertNotFalse($row);
282

Sergei Morozov's avatar
Sergei Morozov committed
283
        $row = array_change_key_case($row, CASE_LOWER);
284

285 286
        self::assertEquals(1, $row['test_int']);
        self::assertEquals('foo', $row['test_string']);
287 288
    }

289 290 291
    public function testFetchAssocWithTypes()
    {
        $datetimeString = '2010-01-01 10:10:10';
Sergei Morozov's avatar
Sergei Morozov committed
292
        $datetime       = new DateTime($datetimeString);
293

294
        $sql = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?';
Sergei Morozov's avatar
Sergei Morozov committed
295
        $row = $this->connection->fetchAssoc($sql, [1, $datetime], [ParameterType::STRING, Type::DATETIME]);
296

Gabriel Caruso's avatar
Gabriel Caruso committed
297
        self::assertNotFalse($row);
298

Sergei Morozov's avatar
Sergei Morozov committed
299
        $row = array_change_key_case($row, CASE_LOWER);
300

301 302
        self::assertEquals(1, $row['test_int']);
        self::assertStringStartsWith($datetimeString, $row['test_datetime']);
303 304 305 306 307 308 309
    }

    /**
     * @expectedException \Doctrine\DBAL\DBALException
     */
    public function testFetchAssocWithMissingTypes()
    {
Sergei Morozov's avatar
Sergei Morozov committed
310 311
        if ($this->connection->getDriver() instanceof MySQLiDriver ||
            $this->connection->getDriver() instanceof SQLSrvDriver) {
312 313 314 315
            $this->markTestSkipped('mysqli and sqlsrv actually supports this');
        }

        $datetimeString = '2010-01-01 10:10:10';
Sergei Morozov's avatar
Sergei Morozov committed
316 317
        $datetime       = new DateTime($datetimeString);
        $sql            = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?';
Sergei Morozov's avatar
Sergei Morozov committed
318
        $this->connection->fetchAssoc($sql, [1, $datetime]);
319 320
    }

321 322
    public function testFetchArray()
    {
Sergei Morozov's avatar
Sergei Morozov committed
323
        $sql = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
324
        $row = $this->connection->fetchArray($sql, [1, 'foo']);
325

326 327
        self::assertEquals(1, $row[0]);
        self::assertEquals('foo', $row[1]);
328 329
    }

330 331 332
    public function testFetchArrayWithTypes()
    {
        $datetimeString = '2010-01-01 10:10:10';
Sergei Morozov's avatar
Sergei Morozov committed
333
        $datetime       = new DateTime($datetimeString);
334

335
        $sql = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?';
Sergei Morozov's avatar
Sergei Morozov committed
336
        $row = $this->connection->fetchArray($sql, [1, $datetime], [ParameterType::STRING, Type::DATETIME]);
337

Gabriel Caruso's avatar
Gabriel Caruso committed
338
        self::assertNotFalse($row);
339

Sergei Morozov's avatar
Sergei Morozov committed
340
        $row = array_change_key_case($row, CASE_LOWER);
341

342 343
        self::assertEquals(1, $row[0]);
        self::assertStringStartsWith($datetimeString, $row[1]);
344 345 346 347 348 349 350
    }

    /**
     * @expectedException \Doctrine\DBAL\DBALException
     */
    public function testFetchArrayWithMissingTypes()
    {
Sergei Morozov's avatar
Sergei Morozov committed
351 352
        if ($this->connection->getDriver() instanceof MySQLiDriver ||
            $this->connection->getDriver() instanceof SQLSrvDriver) {
353 354 355 356
            $this->markTestSkipped('mysqli and sqlsrv actually supports this');
        }

        $datetimeString = '2010-01-01 10:10:10';
Sergei Morozov's avatar
Sergei Morozov committed
357 358
        $datetime       = new DateTime($datetimeString);
        $sql            = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?';
Sergei Morozov's avatar
Sergei Morozov committed
359
        $row            = $this->connection->fetchArray($sql, [1, $datetime]);
360 361
    }

362 363
    public function testFetchColumn()
    {
Sergei Morozov's avatar
Sergei Morozov committed
364
        $sql     = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
365
        $testInt = $this->connection->fetchColumn($sql, [1, 'foo'], 0);
366

367
        self::assertEquals(1, $testInt);
368

Sergei Morozov's avatar
Sergei Morozov committed
369
        $sql        = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
370
        $testString = $this->connection->fetchColumn($sql, [1, 'foo'], 1);
371

372
        self::assertEquals('foo', $testString);
373
    }
374

375 376 377
    public function testFetchColumnWithTypes()
    {
        $datetimeString = '2010-01-01 10:10:10';
Sergei Morozov's avatar
Sergei Morozov committed
378
        $datetime       = new DateTime($datetimeString);
379

380
        $sql    = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?';
Sergei Morozov's avatar
Sergei Morozov committed
381
        $column = $this->connection->fetchColumn($sql, [1, $datetime], 1, [ParameterType::STRING, Type::DATETIME]);
382

Gabriel Caruso's avatar
Gabriel Caruso committed
383
        self::assertNotFalse($column);
384

385
        self::assertStringStartsWith($datetimeString, $column);
386 387 388 389 390 391 392
    }

    /**
     * @expectedException \Doctrine\DBAL\DBALException
     */
    public function testFetchColumnWithMissingTypes()
    {
Sergei Morozov's avatar
Sergei Morozov committed
393 394
        if ($this->connection->getDriver() instanceof MySQLiDriver ||
            $this->connection->getDriver() instanceof SQLSrvDriver) {
395 396 397 398
            $this->markTestSkipped('mysqli and sqlsrv actually supports this');
        }

        $datetimeString = '2010-01-01 10:10:10';
Sergei Morozov's avatar
Sergei Morozov committed
399 400
        $datetime       = new DateTime($datetimeString);
        $sql            = 'SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?';
Sergei Morozov's avatar
Sergei Morozov committed
401
        $column         = $this->connection->fetchColumn($sql, [1, $datetime], 1);
402 403
    }

404 405 406 407 408
    /**
     * @group DDC-697
     */
    public function testExecuteQueryBindDateTimeType()
    {
Sergei Morozov's avatar
Sergei Morozov committed
409
        $sql  = 'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?';
Sergei Morozov's avatar
Sergei Morozov committed
410
        $stmt = $this->connection->executeQuery(
Sergei Morozov's avatar
Sergei Morozov committed
411 412 413
            $sql,
            [1 => new DateTime('2010-01-01 10:10:10')],
            [1 => Type::DATETIME]
414 415
        );

416
        self::assertEquals(1, $stmt->fetchColumn());
417 418 419 420 421 422 423
    }

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

Sergei Morozov's avatar
Sergei Morozov committed
426
        $sql          = 'INSERT INTO fetch_table (test_int, test_string, test_datetime) VALUES (?, ?, ?)';
Sergei Morozov's avatar
Sergei Morozov committed
427
        $affectedRows = $this->connection->executeUpdate($sql, [
428 429 430 431 432 433 434 435
            1 => 50,
            2 => 'foo',
            3 => $datetime,
        ], [
            1 => ParameterType::INTEGER,
            2 => ParameterType::STRING,
            3 => Type::DATETIME,
        ]);
436

437
        self::assertEquals(1, $affectedRows);
Sergei Morozov's avatar
Sergei Morozov committed
438
        self::assertEquals(1, $this->connection->executeQuery(
439
            'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?',
Sergei Morozov's avatar
Sergei Morozov committed
440 441
            [1 => $datetime],
            [1 => Type::DATETIME]
442 443 444 445 446 447 448 449
        )->fetchColumn());
    }

    /**
     * @group DDC-697
     */
    public function testPrepareQueryBindValueDateTimeType()
    {
Sergei Morozov's avatar
Sergei Morozov committed
450
        $sql  = 'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?';
Sergei Morozov's avatar
Sergei Morozov committed
451
        $stmt = $this->connection->prepare($sql);
Sergei Morozov's avatar
Sergei Morozov committed
452
        $stmt->bindValue(1, new DateTime('2010-01-01 10:10:10'), Type::DATETIME);
453 454
        $stmt->execute();

455
        self::assertEquals(1, $stmt->fetchColumn());
456
    }
457

458 459 460 461 462 463
    /**
     * @group DBAL-78
     */
    public function testNativeArrayListSupport()
    {
        for ($i = 100; $i < 110; $i++) {
Sergei Morozov's avatar
Sergei Morozov committed
464
            $this->connection->insert('fetch_table', ['test_int' => $i, 'test_string' => 'foo' . $i, 'test_datetime' => '2010-01-01 10:10:10']);
465
        }
466

Sergei Morozov's avatar
Sergei Morozov committed
467
        $stmt = $this->connection->executeQuery(
Sergei Morozov's avatar
Sergei Morozov committed
468 469 470 471
            'SELECT test_int FROM fetch_table WHERE test_int IN (?)',
            [[100, 101, 102, 103, 104]],
            [Connection::PARAM_INT_ARRAY]
        );
472

473
        $data = $stmt->fetchAll(FetchMode::NUMERIC);
Gabriel Caruso's avatar
Gabriel Caruso committed
474
        self::assertCount(5, $data);
Sergei Morozov's avatar
Sergei Morozov committed
475
        self::assertEquals([[100], [101], [102], [103], [104]], $data);
476

Sergei Morozov's avatar
Sergei Morozov committed
477
        $stmt = $this->connection->executeQuery(
Sergei Morozov's avatar
Sergei Morozov committed
478 479 480 481
            'SELECT test_int FROM fetch_table WHERE test_string IN (?)',
            [['foo100', 'foo101', 'foo102', 'foo103', 'foo104']],
            [Connection::PARAM_STR_ARRAY]
        );
482

483
        $data = $stmt->fetchAll(FetchMode::NUMERIC);
Gabriel Caruso's avatar
Gabriel Caruso committed
484
        self::assertCount(5, $data);
Sergei Morozov's avatar
Sergei Morozov committed
485
        self::assertEquals([[100], [101], [102], [103], [104]], $data);
486
    }
487

488 489 490 491 492 493
    /**
     * @dataProvider getTrimExpressionData
     */
    public function testTrimExpression($value, $position, $char, $expectedResult)
    {
        $sql = 'SELECT ' .
Sergei Morozov's avatar
Sergei Morozov committed
494
            $this->connection->getDatabasePlatform()->getTrimExpression($value, $position, $char) . ' AS trimmed ' .
495 496
            'FROM fetch_table';

Sergei Morozov's avatar
Sergei Morozov committed
497
        $row = $this->connection->fetchAssoc($sql);
498 499
        $row = array_change_key_case($row, CASE_LOWER);

500
        self::assertEquals($expectedResult, $row['trimmed']);
501 502 503 504
    }

    public function getTrimExpressionData()
    {
Sergei Morozov's avatar
Sergei Morozov committed
505
        return [
506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541
            ['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
542
        ];
543 544
    }

545 546 547 548 549
    /**
     * @group DDC-1014
     */
    public function testDateArithmetics()
    {
Sergei Morozov's avatar
Sergei Morozov committed
550
        $p    = $this->connection->getDatabasePlatform();
Sergei Morozov's avatar
Sergei Morozov committed
551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567
        $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 ';
568 569
        $sql .= 'FROM fetch_table';

Sergei Morozov's avatar
Sergei Morozov committed
570
        $row = $this->connection->fetchAssoc($sql);
571
        $row = array_change_key_case($row, CASE_LOWER);
572

Sergei Morozov's avatar
Sergei Morozov committed
573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588
        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');
589
    }
590

591 592
    public function testSqliteDateArithmeticWithDynamicInterval()
    {
Sergei Morozov's avatar
Sergei Morozov committed
593
        $platform = $this->connection->getDatabasePlatform();
594 595 596 597 598 599 600 601 602 603

        if (! $platform instanceof SqlitePlatform) {
            $this->markTestSkipped('test is for sqlite only');
        }

        $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
604
        /** @var AbstractSchemaManager $sm */
Sergei Morozov's avatar
Sergei Morozov committed
605
        $sm = $this->connection->getSchemaManager();
606 607
        $sm->createTable($table);

Sergei Morozov's avatar
Sergei Morozov committed
608 609
        $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]);
610 611 612 613

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

Sergei Morozov's avatar
Sergei Morozov committed
614
        $rowCount = $this->connection->fetchColumn($sql, [], 0);
615 616 617 618

        $this->assertEquals(1, $rowCount);
    }

619 620
    public function testLocateExpression()
    {
Sergei Morozov's avatar
Sergei Morozov committed
621
        $platform = $this->connection->getDatabasePlatform();
622

Sergei Morozov's avatar
Sergei Morozov committed
623 624 625 626 627 628 629 630 631 632
        $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 ';
633 634
        $sql .= 'FROM fetch_table';

Sergei Morozov's avatar
Sergei Morozov committed
635
        $row = $this->connection->fetchAssoc($sql);
636 637
        $row = array_change_key_case($row, CASE_LOWER);

638 639 640 641 642 643 644 645 646
        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']);
647 648
    }

649 650
    public function testQuoteSQLInjection()
    {
Sergei Morozov's avatar
Sergei Morozov committed
651 652
        $sql  = 'SELECT * FROM fetch_table WHERE test_string = ' . $this->connection->quote("bar' OR '1'='1");
        $rows = $this->connection->fetchAll($sql);
653

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

Fabio B. Silva's avatar
Fabio B. Silva committed
657 658 659 660 661
    /**
     * @group DDC-1213
     */
    public function testBitComparisonExpressionSupport()
    {
Sergei Morozov's avatar
Sergei Morozov committed
662 663
        $this->connection->exec('DELETE FROM fetch_table');
        $platform = $this->connection->getDatabasePlatform();
Sergei Morozov's avatar
Sergei Morozov committed
664
        $bitmap   = [];
665

Sergei Morozov's avatar
Sergei Morozov committed
666 667
        for ($i = 2; $i < 9; $i += 2) {
            $bitmap[$i] = [
Fabio B. Silva's avatar
Fabio B. Silva committed
668
                'bit_or'    => ($i | 2),
Sergei Morozov's avatar
Sergei Morozov committed
669 670
                'bit_and'   => ($i & 2),
            ];
Sergei Morozov's avatar
Sergei Morozov committed
671
            $this->connection->insert('fetch_table', [
672
                'test_int'      => $i,
Fabio B. Silva's avatar
Fabio B. Silva committed
673
                'test_string'   => json_encode($bitmap[$i]),
Sergei Morozov's avatar
Sergei Morozov committed
674 675
                'test_datetime' => '2010-01-01 10:10:10',
            ]);
Fabio B. Silva's avatar
Fabio B. Silva committed
676
        }
677

Sergei Morozov's avatar
Sergei Morozov committed
678 679 680 681 682 683
        $sql[] = 'SELECT ';
        $sql[] = 'test_int, ';
        $sql[] = 'test_string, ';
        $sql[] = $platform->getBitOrComparisonExpression('test_int', 2) . ' AS bit_or, ';
        $sql[] = $platform->getBitAndComparisonExpression('test_int', 2) . ' AS bit_and ';
        $sql[] = 'FROM fetch_table';
Fabio B. Silva's avatar
Fabio B. Silva committed
684

Sergei Morozov's avatar
Sergei Morozov committed
685
        $stmt = $this->connection->executeQuery(implode(PHP_EOL, $sql));
686
        $data = $stmt->fetchAll(FetchMode::ASSOCIATIVE);
687

Gabriel Caruso's avatar
Gabriel Caruso committed
688
        self::assertCount(4, $data);
689
        self::assertEquals(count($bitmap), count($data));
Fabio B. Silva's avatar
Fabio B. Silva committed
690
        foreach ($data as $row) {
691 692
            $row = array_change_key_case($row, CASE_LOWER);

693
            self::assertArrayHasKey('test_int', $row);
694

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

697 698
            self::assertArrayHasKey($id, $bitmap);
            self::assertArrayHasKey($id, $bitmap);
699

700 701
            self::assertArrayHasKey('bit_or', $row);
            self::assertArrayHasKey('bit_and', $row);
702

703 704
            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
705 706
        }
    }
707 708 709

    public function testSetDefaultFetchMode()
    {
Sergei Morozov's avatar
Sergei Morozov committed
710
        $stmt = $this->connection->query('SELECT * FROM fetch_table');
711
        $stmt->setFetchMode(FetchMode::NUMERIC);
712 713

        $row = array_keys($stmt->fetch());
Sergei Morozov's avatar
Sergei Morozov committed
714 715 716
        self::assertCount(0, array_filter($row, static function ($v) {
            return ! is_numeric($v);
        }), 'should be no non-numerical elements in the result.');
717
    }
Fabio B. Silva's avatar
Fabio B. Silva committed
718

719 720 721 722 723 724 725
    /**
     * @group DBAL-1091
     */
    public function testFetchAllStyleObject()
    {
        $this->setupFixture();

Sergei Morozov's avatar
Sergei Morozov committed
726
        $sql  = 'SELECT test_int, test_string, test_datetime FROM fetch_table';
Sergei Morozov's avatar
Sergei Morozov committed
727
        $stmt = $this->connection->prepare($sql);
728 729 730

        $stmt->execute();

731
        $results = $stmt->fetchAll(FetchMode::STANDARD_OBJECT);
732

733 734
        self::assertCount(1, $results);
        self::assertInstanceOf('stdClass', $results[0]);
735

736
        self::assertEquals(
737 738 739
            1,
            property_exists($results[0], 'test_int') ? $results[0]->test_int : $results[0]->TEST_INT
        );
740
        self::assertEquals(
741 742 743
            'foo',
            property_exists($results[0], 'test_string') ? $results[0]->test_string : $results[0]->TEST_STRING
        );
744
        self::assertStringStartsWith(
745 746 747 748 749
            '2010-01-01 10:10:10',
            property_exists($results[0], 'test_datetime') ? $results[0]->test_datetime : $results[0]->TEST_DATETIME
        );
    }

Fabio B. Silva's avatar
Fabio B. Silva committed
750 751 752 753 754
    /**
     * @group DBAL-196
     */
    public function testFetchAllSupportFetchClass()
    {
755 756
        $this->skipOci8AndMysqli();
        $this->setupFixture();
Fabio B. Silva's avatar
Fabio B. Silva committed
757

Sergei Morozov's avatar
Sergei Morozov committed
758
        $sql  = 'SELECT test_int, test_string, test_datetime FROM fetch_table';
Sergei Morozov's avatar
Sergei Morozov committed
759
        $stmt = $this->connection->prepare($sql);
Fabio B. Silva's avatar
Fabio B. Silva committed
760 761 762
        $stmt->execute();

        $results = $stmt->fetchAll(
763
            FetchMode::CUSTOM_OBJECT,
Sergei Morozov's avatar
Sergei Morozov committed
764
            MyFetchClass::class
Fabio B. Silva's avatar
Fabio B. Silva committed
765 766
        );

Gabriel Caruso's avatar
Gabriel Caruso committed
767
        self::assertCount(1, $results);
Sergei Morozov's avatar
Sergei Morozov committed
768
        self::assertInstanceOf(MyFetchClass::class, $results[0]);
Benjamin Eberlei's avatar
Benjamin Eberlei committed
769

770 771 772
        self::assertEquals(1, $results[0]->test_int);
        self::assertEquals('foo', $results[0]->test_string);
        self::assertStringStartsWith('2010-01-01 10:10:10', $results[0]->test_datetime);
Fabio B. Silva's avatar
Fabio B. Silva committed
773
    }
774 775 776 777 778 779

    /**
     * @group DBAL-241
     */
    public function testFetchAllStyleColumn()
    {
Sergei Morozov's avatar
Sergei Morozov committed
780
        $sql = 'DELETE FROM fetch_table';
Sergei Morozov's avatar
Sergei Morozov committed
781
        $this->connection->executeUpdate($sql);
782

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

Sergei Morozov's avatar
Sergei Morozov committed
786
        $sql  = 'SELECT test_int FROM fetch_table';
Sergei Morozov's avatar
Sergei Morozov committed
787
        $rows = $this->connection->query($sql)->fetchAll(FetchMode::COLUMN);
788

Sergei Morozov's avatar
Sergei Morozov committed
789
        self::assertEquals([1, 10], $rows);
790
    }
791 792 793 794 795 796 797 798 799

    /**
     * @group DBAL-214
     */
    public function testSetFetchModeClassFetchAll()
    {
        $this->skipOci8AndMysqli();
        $this->setupFixture();

Sergei Morozov's avatar
Sergei Morozov committed
800
        $sql  = 'SELECT * FROM fetch_table';
Sergei Morozov's avatar
Sergei Morozov committed
801 802
        $stmt = $this->connection->query($sql);
        $stmt->setFetchMode(FetchMode::CUSTOM_OBJECT, MyFetchClass::class);
803 804 805

        $results = $stmt->fetchAll();

Gabriel Caruso's avatar
Gabriel Caruso committed
806
        self::assertCount(1, $results);
Sergei Morozov's avatar
Sergei Morozov committed
807
        self::assertInstanceOf(MyFetchClass::class, $results[0]);
808

809 810 811
        self::assertEquals(1, $results[0]->test_int);
        self::assertEquals('foo', $results[0]->test_string);
        self::assertStringStartsWith('2010-01-01 10:10:10', $results[0]->test_datetime);
812 813 814 815 816 817 818 819 820 821
    }

    /**
     * @group DBAL-214
     */
    public function testSetFetchModeClassFetch()
    {
        $this->skipOci8AndMysqli();
        $this->setupFixture();

Sergei Morozov's avatar
Sergei Morozov committed
822
        $sql  = 'SELECT * FROM fetch_table';
Sergei Morozov's avatar
Sergei Morozov committed
823 824
        $stmt = $this->connection->query($sql);
        $stmt->setFetchMode(FetchMode::CUSTOM_OBJECT, MyFetchClass::class);
825

Sergei Morozov's avatar
Sergei Morozov committed
826
        $results = [];
827 828 829 830
        while ($row = $stmt->fetch()) {
            $results[] = $row;
        }

Gabriel Caruso's avatar
Gabriel Caruso committed
831
        self::assertCount(1, $results);
Sergei Morozov's avatar
Sergei Morozov committed
832
        self::assertInstanceOf(MyFetchClass::class, $results[0]);
833

834 835 836
        self::assertEquals(1, $results[0]->test_int);
        self::assertEquals('foo', $results[0]->test_string);
        self::assertStringStartsWith('2010-01-01 10:10:10', $results[0]->test_datetime);
837 838
    }

839 840 841 842 843
    /**
     * @group DBAL-257
     */
    public function testEmptyFetchColumnReturnsFalse()
    {
Sergei Morozov's avatar
Sergei Morozov committed
844 845 846 847 848
        $this->connection->beginTransaction();
        $this->connection->exec('DELETE FROM fetch_table');
        self::assertFalse($this->connection->fetchColumn('SELECT test_int FROM fetch_table'));
        self::assertFalse($this->connection->query('SELECT test_int FROM fetch_table')->fetchColumn());
        $this->connection->rollBack();
849 850
    }

851 852 853 854 855
    /**
     * @group DBAL-339
     */
    public function testSetFetchModeOnDbalStatement()
    {
Sergei Morozov's avatar
Sergei Morozov committed
856
        $sql  = 'SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?';
Sergei Morozov's avatar
Sergei Morozov committed
857
        $stmt = $this->connection->executeQuery($sql, [1, 'foo']);
858
        $stmt->setFetchMode(FetchMode::NUMERIC);
859

Luís Cobucci's avatar
Luís Cobucci committed
860 861 862 863 864
        $row = $stmt->fetch();

        self::assertArrayHasKey(0, $row);
        self::assertArrayHasKey(1, $row);
        self::assertFalse($stmt->fetch());
865 866
    }

867 868 869 870 871
    /**
     * @group DBAL-435
     */
    public function testEmptyParameters()
    {
Sergei Morozov's avatar
Sergei Morozov committed
872
        $sql  = 'SELECT * FROM fetch_table WHERE test_int IN (?)';
Sergei Morozov's avatar
Sergei Morozov committed
873
        $stmt = $this->connection->executeQuery($sql, [[]], [Connection::PARAM_INT_ARRAY]);
874 875
        $rows = $stmt->fetchAll();

Sergei Morozov's avatar
Sergei Morozov committed
876
        self::assertEquals([], $rows);
877 878
    }

879 880 881 882 883
    /**
     * @group DBAL-1028
     */
    public function testFetchColumnNullValue()
    {
Sergei Morozov's avatar
Sergei Morozov committed
884
        $this->connection->executeUpdate(
885
            'INSERT INTO fetch_table (test_int, test_string) VALUES (?, ?)',
Sergei Morozov's avatar
Sergei Morozov committed
886
            [2, 'foo']
887 888
        );

889
        self::assertNull(
Sergei Morozov's avatar
Sergei Morozov committed
890
            $this->connection->fetchColumn('SELECT test_datetime FROM fetch_table WHERE test_int = ?', [2])
891 892 893 894 895 896 897 898
        );
    }

    /**
     * @group DBAL-1028
     */
    public function testFetchColumnNonExistingIndex()
    {
Sergei Morozov's avatar
Sergei Morozov committed
899
        if ($this->connection->getDriver()->getName() === 'pdo_sqlsrv') {
900 901 902 903 904
            $this->markTestSkipped(
                'Test does not work for pdo_sqlsrv driver as it throws a fatal error for a non-existing column index.'
            );
        }

905
        self::assertNull(
Sergei Morozov's avatar
Sergei Morozov committed
906
            $this->connection->fetchColumn('SELECT test_int FROM fetch_table WHERE test_int = ?', [1], 1)
907 908 909 910 911 912 913 914
        );
    }

    /**
     * @group DBAL-1028
     */
    public function testFetchColumnNoResult()
    {
915
        self::assertFalse(
Sergei Morozov's avatar
Sergei Morozov committed
916
            $this->connection->fetchColumn('SELECT test_int FROM fetch_table WHERE test_int = ?', [-1])
917 918 919
        );
    }

920 921
    private function setupFixture()
    {
Sergei Morozov's avatar
Sergei Morozov committed
922 923
        $this->connection->exec('DELETE FROM fetch_table');
        $this->connection->insert('fetch_table', [
924 925
            'test_int'      => 1,
            'test_string'   => 'foo',
Sergei Morozov's avatar
Sergei Morozov committed
926 927
            'test_datetime' => '2010-01-01 10:10:10',
        ]);
928 929 930 931
    }

    private function skipOci8AndMysqli()
    {
Sergei Morozov's avatar
Sergei Morozov committed
932 933
        if (isset($GLOBALS['db_type']) && $GLOBALS['db_type'] === 'oci8') {
            $this->markTestSkipped('Not supported by OCI8');
934
        }
Sergei Morozov's avatar
Sergei Morozov committed
935
        if ($this->connection->getDriver()->getName() !== 'mysqli') {
Sergei Morozov's avatar
Sergei Morozov committed
936
            return;
937
        }
Sergei Morozov's avatar
Sergei Morozov committed
938 939

        $this->markTestSkipped('Mysqli driver dont support this feature.');
940
    }
Fabio B. Silva's avatar
Fabio B. Silva committed
941 942 943 944
}

class MyFetchClass
{
Sergei Morozov's avatar
Sergei Morozov committed
945 946 947 948 949 950 951 952
    /** @var int */
    public $test_int;

    /** @var string */
    public $test_string;

    /** @var string */
    public $test_datetime;
953
}