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

3 4
namespace Doctrine\Tests\DBAL\Functional;

jeroendedauw's avatar
jeroendedauw committed
5
use Doctrine\DBAL\Connection;
6
use Doctrine\DBAL\Platforms\AbstractPlatform;
7 8 9
use Doctrine\DBAL\Types\Type;
use PDO;

10 11
class DataAccessTest extends \Doctrine\Tests\DbalFunctionalTestCase
{
12 13
    static private $generated = false;

14
    protected function setUp()
15 16 17
    {
        parent::setUp();

18
        if (self::$generated === false) {
19 20 21 22
            /* @var $sm \Doctrine\DBAL\Schema\AbstractSchemaManager */
            $table = new \Doctrine\DBAL\Schema\Table("fetch_table");
            $table->addColumn('test_int', 'integer');
            $table->addColumn('test_string', 'string');
23
            $table->addColumn('test_datetime', 'datetime', array('notnull' => false));
24
            $table->setPrimaryKey(array('test_int'));
25 26 27 28

            $sm = $this->_conn->getSchemaManager();
            $sm->createTable($table);

29
            $this->_conn->insert('fetch_table', array('test_int' => 1, 'test_string' => 'foo', 'test_datetime' => '2010-01-01 10:10:10'));
30
            self::$generated = true;
31 32 33
        }
    }

34 35 36 37
    public function testPrepareWithBindValue()
    {
        $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
        $stmt = $this->_conn->prepare($sql);
38
        self::assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
39 40 41 42 43 44 45

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

        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        $row = array_change_key_case($row, \CASE_LOWER);
46
        self::assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $row);
47 48 49 50 51 52 53 54 55
    }

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

        $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
        $stmt = $this->_conn->prepare($sql);
56
        self::assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
57 58 59 60 61 62 63

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

        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        $row = array_change_key_case($row, \CASE_LOWER);
64
        self::assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $row);
65 66 67 68 69 70 71 72 73
    }

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

        $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
        $stmt = $this->_conn->prepare($sql);
74
        self::assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
75 76 77 78 79 80 81

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

        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        $rows[0] = array_change_key_case($rows[0], \CASE_LOWER);
82
        self::assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $rows[0]);
83 84
    }

85 86 87 88 89 90 91 92 93 94
    /**
     * @group DBAL-228
     */
    public function testPrepareWithFetchAllBoth()
    {
        $paramInt = 1;
        $paramStr = 'foo';

        $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
        $stmt = $this->_conn->prepare($sql);
95
        self::assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
96 97 98 99 100 101 102

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

        $rows = $stmt->fetchAll(\PDO::FETCH_BOTH);
        $rows[0] = array_change_key_case($rows[0], \CASE_LOWER);
103
        self::assertEquals(array('test_int' => 1, 'test_string' => 'foo', 0 => 1, 1 => 'foo'), $rows[0]);
104 105
    }

106 107 108 109 110 111 112
    public function testPrepareWithFetchColumn()
    {
        $paramInt = 1;
        $paramStr = 'foo';

        $sql = "SELECT test_int FROM fetch_table WHERE test_int = ? AND test_string = ?";
        $stmt = $this->_conn->prepare($sql);
113
        self::assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
114 115 116 117 118 119

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

        $column = $stmt->fetchColumn();
120
        self::assertEquals(1, $column);
121 122
    }

123 124 125 126 127 128 129
    public function testPrepareWithIterator()
    {
        $paramInt = 1;
        $paramStr = 'foo';

        $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
        $stmt = $this->_conn->prepare($sql);
130
        self::assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
131 132 133 134 135 136 137 138 139 140 141

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

        $rows = array();
        $stmt->setFetchMode(\PDO::FETCH_ASSOC);
        foreach ($stmt as $row) {
            $rows[] = array_change_key_case($row, \CASE_LOWER);
        }

142
        self::assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $rows[0]);
143 144
    }

145 146 147 148 149 150 151 152 153
    public function testPrepareWithQuoted()
    {
        $table = 'fetch_table';
        $paramInt = 1;
        $paramStr = 'foo';

        $sql = "SELECT test_int, test_string FROM " . $this->_conn->quoteIdentifier($table) . " ".
               "WHERE test_int = " . $this->_conn->quote($paramInt) . " AND test_string = " . $this->_conn->quote($paramStr);
        $stmt = $this->_conn->prepare($sql);
154
        self::assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
155 156 157 158 159 160 161 162 163
    }

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

        $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
        $stmt = $this->_conn->prepare($sql);
164
        self::assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
165 166 167
        $stmt->execute(array($paramInt, $paramStr));

        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
168
        self::assertTrue($row !== false);
169
        $row = array_change_key_case($row, \CASE_LOWER);
170
        self::assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $row);
171 172
    }

173 174 175 176 177
    public function testFetchAll()
    {
        $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
        $data = $this->_conn->fetchAll($sql, array(1, 'foo'));

178
        self::assertEquals(1, count($data));
179 180

        $row = $data[0];
181
        self::assertEquals(2, count($row));
182 183

        $row = array_change_key_case($row, \CASE_LOWER);
184 185
        self::assertEquals(1, $row['test_int']);
        self::assertEquals('foo', $row['test_string']);
186 187
    }

root's avatar
root committed
188 189 190 191 192 193 194 195 196 197
    /**
     * @group DBAL-209
     */
    public function testFetchAllWithTypes()
    {
        $datetimeString = '2010-01-01 10:10:10';
        $datetime = new \DateTime($datetimeString);
        $sql = "SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?";
        $data = $this->_conn->fetchAll($sql, array(1, $datetime), array(PDO::PARAM_STR, Type::DATETIME));

198
        self::assertEquals(1, count($data));
root's avatar
root committed
199 200

        $row = $data[0];
201
        self::assertEquals(2, count($row));
root's avatar
root committed
202 203

        $row = array_change_key_case($row, \CASE_LOWER);
204 205
        self::assertEquals(1, $row['test_int']);
        self::assertStringStartsWith($datetimeString, $row['test_datetime']);
root's avatar
root committed
206
    }
207

root's avatar
root committed
208 209 210 211 212 213
    /**
     * @group DBAL-209
     * @expectedException \Doctrine\DBAL\DBALException
     */
    public function testFetchAllWithMissingTypes()
    {
214 215
        if ($this->_conn->getDriver() instanceof \Doctrine\DBAL\Driver\Mysqli\Driver ||
            $this->_conn->getDriver() instanceof \Doctrine\DBAL\Driver\SQLSrv\Driver) {
Steve Müller's avatar
Steve Müller committed
216
            $this->markTestSkipped('mysqli and sqlsrv actually supports this');
217 218
        }

root's avatar
root committed
219 220 221 222 223 224
        $datetimeString = '2010-01-01 10:10:10';
        $datetime = new \DateTime($datetimeString);
        $sql = "SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?";
        $data = $this->_conn->fetchAll($sql, array(1, $datetime));
    }

225 226 227
    public function testFetchBoth()
    {
        $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
228
        $row = $this->_conn->executeQuery($sql, array(1, 'foo'))->fetch(\PDO::FETCH_BOTH);
229

230
        self::assertTrue($row !== false);
231 232 233

        $row = array_change_key_case($row, \CASE_LOWER);

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

240 241
    public function testFetchNoResult()
    {
242 243 244
        self::assertFalse(
            $this->_conn->executeQuery('SELECT test_int FROM fetch_table WHERE test_int = ?', [-1])->fetch()
        );
245 246
    }

247
    public function testFetchAssoc()
248 249
    {
        $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
250
        $row = $this->_conn->fetchAssoc($sql, array(1, 'foo'));
251

252
        self::assertTrue($row !== false);
253

254
        $row = array_change_key_case($row, \CASE_LOWER);
255

256 257
        self::assertEquals(1, $row['test_int']);
        self::assertEquals('foo', $row['test_string']);
258 259
    }

260 261 262 263 264 265 266
    public function testFetchAssocWithTypes()
    {
        $datetimeString = '2010-01-01 10:10:10';
        $datetime = new \DateTime($datetimeString);
        $sql = "SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?";
        $row = $this->_conn->fetchAssoc($sql, array(1, $datetime), array(PDO::PARAM_STR, Type::DATETIME));

267
        self::assertTrue($row !== false);
268 269 270

        $row = array_change_key_case($row, \CASE_LOWER);

271 272
        self::assertEquals(1, $row['test_int']);
        self::assertStringStartsWith($datetimeString, $row['test_datetime']);
273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290
    }

    /**
     * @expectedException \Doctrine\DBAL\DBALException
     */
    public function testFetchAssocWithMissingTypes()
    {
        if ($this->_conn->getDriver() instanceof \Doctrine\DBAL\Driver\Mysqli\Driver ||
            $this->_conn->getDriver() instanceof \Doctrine\DBAL\Driver\SQLSrv\Driver) {
            $this->markTestSkipped('mysqli and sqlsrv actually supports this');
        }

        $datetimeString = '2010-01-01 10:10:10';
        $datetime = new \DateTime($datetimeString);
        $sql = "SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?";
        $row = $this->_conn->fetchAssoc($sql, array(1, $datetime));
    }

291 292 293 294 295
    public function testFetchArray()
    {
        $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
        $row = $this->_conn->fetchArray($sql, array(1, 'foo'));

296 297
        self::assertEquals(1, $row[0]);
        self::assertEquals('foo', $row[1]);
298 299
    }

300 301 302 303 304 305 306
    public function testFetchArrayWithTypes()
    {
        $datetimeString = '2010-01-01 10:10:10';
        $datetime = new \DateTime($datetimeString);
        $sql = "SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?";
        $row = $this->_conn->fetchArray($sql, array(1, $datetime), array(PDO::PARAM_STR, Type::DATETIME));

307
        self::assertTrue($row !== false);
308 309 310

        $row = array_change_key_case($row, \CASE_LOWER);

311 312
        self::assertEquals(1, $row[0]);
        self::assertStringStartsWith($datetimeString, $row[1]);
313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330
    }

    /**
     * @expectedException \Doctrine\DBAL\DBALException
     */
    public function testFetchArrayWithMissingTypes()
    {
        if ($this->_conn->getDriver() instanceof \Doctrine\DBAL\Driver\Mysqli\Driver ||
            $this->_conn->getDriver() instanceof \Doctrine\DBAL\Driver\SQLSrv\Driver) {
            $this->markTestSkipped('mysqli and sqlsrv actually supports this');
        }

        $datetimeString = '2010-01-01 10:10:10';
        $datetime = new \DateTime($datetimeString);
        $sql = "SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?";
        $row = $this->_conn->fetchArray($sql, array(1, $datetime));
    }

331 332 333 334 335
    public function testFetchColumn()
    {
        $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
        $testInt = $this->_conn->fetchColumn($sql, array(1, 'foo'), 0);

336
        self::assertEquals(1, $testInt);
337 338 339 340

        $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
        $testString = $this->_conn->fetchColumn($sql, array(1, 'foo'), 1);

341
        self::assertEquals('foo', $testString);
342
    }
343

344 345 346 347 348 349 350
    public function testFetchColumnWithTypes()
    {
        $datetimeString = '2010-01-01 10:10:10';
        $datetime = new \DateTime($datetimeString);
        $sql = "SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?";
        $column = $this->_conn->fetchColumn($sql, array(1, $datetime), 1, array(PDO::PARAM_STR, Type::DATETIME));

351
        self::assertTrue($column !== false);
352

353
        self::assertStringStartsWith($datetimeString, $column);
354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371
    }

    /**
     * @expectedException \Doctrine\DBAL\DBALException
     */
    public function testFetchColumnWithMissingTypes()
    {
        if ($this->_conn->getDriver() instanceof \Doctrine\DBAL\Driver\Mysqli\Driver ||
            $this->_conn->getDriver() instanceof \Doctrine\DBAL\Driver\SQLSrv\Driver) {
            $this->markTestSkipped('mysqli and sqlsrv actually supports this');
        }

        $datetimeString = '2010-01-01 10:10:10';
        $datetime = new \DateTime($datetimeString);
        $sql = "SELECT test_int, test_datetime FROM fetch_table WHERE test_int = ? AND test_datetime = ?";
        $column = $this->_conn->fetchColumn($sql, array(1, $datetime), 1);
    }

372 373 374 375 376 377 378 379 380 381 382
    /**
     * @group DDC-697
     */
    public function testExecuteQueryBindDateTimeType()
    {
        $sql = 'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?';
        $stmt = $this->_conn->executeQuery($sql,
            array(1 => new \DateTime('2010-01-01 10:10:10')),
            array(1 => Type::DATETIME)
        );

383
        self::assertEquals(1, $stmt->fetchColumn());
384 385 386 387 388 389 390 391 392 393 394
    }

    /**
     * @group DDC-697
     */
    public function testExecuteUpdateBindDateTimeType()
    {
        $datetime = new \DateTime('2010-02-02 20:20:20');

        $sql = 'INSERT INTO fetch_table (test_int, test_string, test_datetime) VALUES (?, ?, ?)';
        $affectedRows = $this->_conn->executeUpdate($sql,
395
            array(1 => 50,              2 => 'foo',             3 => $datetime),
396 397 398
            array(1 => PDO::PARAM_INT,  2 => PDO::PARAM_STR,    3 => Type::DATETIME)
        );

399 400
        self::assertEquals(1, $affectedRows);
        self::assertEquals(1, $this->_conn->executeQuery(
401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416
            'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?',
            array(1 => $datetime),
            array(1 => Type::DATETIME)
        )->fetchColumn());
    }

    /**
     * @group DDC-697
     */
    public function testPrepareQueryBindValueDateTimeType()
    {
        $sql = 'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?';
        $stmt = $this->_conn->prepare($sql);
        $stmt->bindValue(1, new \DateTime('2010-01-01 10:10:10'), Type::DATETIME);
        $stmt->execute();

417
        self::assertEquals(1, $stmt->fetchColumn());
418
    }
419

420 421 422 423 424 425 426 427
    /**
     * @group DBAL-78
     */
    public function testNativeArrayListSupport()
    {
        for ($i = 100; $i < 110; $i++) {
            $this->_conn->insert('fetch_table', array('test_int' => $i, 'test_string' => 'foo' . $i, 'test_datetime' => '2010-01-01 10:10:10'));
        }
428

429
        $stmt = $this->_conn->executeQuery('SELECT test_int FROM fetch_table WHERE test_int IN (?)',
430
            array(array(100, 101, 102, 103, 104)), array(Connection::PARAM_INT_ARRAY));
431

432
        $data = $stmt->fetchAll(PDO::FETCH_NUM);
433 434
        self::assertEquals(5, count($data));
        self::assertEquals(array(array(100), array(101), array(102), array(103), array(104)), $data);
435

436
        $stmt = $this->_conn->executeQuery('SELECT test_int FROM fetch_table WHERE test_string IN (?)',
437
            array(array('foo100', 'foo101', 'foo102', 'foo103', 'foo104')), array(Connection::PARAM_STR_ARRAY));
438

439
        $data = $stmt->fetchAll(PDO::FETCH_NUM);
440 441
        self::assertEquals(5, count($data));
        self::assertEquals(array(array(100), array(101), array(102), array(103), array(104)), $data);
442
    }
443

444 445 446 447 448 449 450 451 452 453 454 455
    /**
     * @dataProvider getTrimExpressionData
     */
    public function testTrimExpression($value, $position, $char, $expectedResult)
    {
        $sql = 'SELECT ' .
            $this->_conn->getDatabasePlatform()->getTrimExpression($value, $position, $char) . ' AS trimmed ' .
            'FROM fetch_table';

        $row = $this->_conn->fetchAssoc($sql);
        $row = array_change_key_case($row, CASE_LOWER);

456
        self::assertEquals($expectedResult, $row['trimmed']);
457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500
    }

    public function getTrimExpressionData()
    {
        return array(
            array('test_string', AbstractPlatform::TRIM_UNSPECIFIED, false, 'foo'),
            array('test_string', AbstractPlatform::TRIM_LEADING, false, 'foo'),
            array('test_string', AbstractPlatform::TRIM_TRAILING, false, 'foo'),
            array('test_string', AbstractPlatform::TRIM_BOTH, false, 'foo'),
            array('test_string', AbstractPlatform::TRIM_UNSPECIFIED, "'f'", 'oo'),
            array('test_string', AbstractPlatform::TRIM_UNSPECIFIED, "'o'", 'f'),
            array('test_string', AbstractPlatform::TRIM_UNSPECIFIED, "'.'", 'foo'),
            array('test_string', AbstractPlatform::TRIM_LEADING, "'f'", 'oo'),
            array('test_string', AbstractPlatform::TRIM_LEADING, "'o'", 'foo'),
            array('test_string', AbstractPlatform::TRIM_LEADING, "'.'", 'foo'),
            array('test_string', AbstractPlatform::TRIM_TRAILING, "'f'", 'foo'),
            array('test_string', AbstractPlatform::TRIM_TRAILING, "'o'", 'f'),
            array('test_string', AbstractPlatform::TRIM_TRAILING, "'.'", 'foo'),
            array('test_string', AbstractPlatform::TRIM_BOTH, "'f'", 'oo'),
            array('test_string', AbstractPlatform::TRIM_BOTH, "'o'", 'f'),
            array('test_string', AbstractPlatform::TRIM_BOTH, "'.'", 'foo'),
            array("' foo '", AbstractPlatform::TRIM_UNSPECIFIED, false, 'foo'),
            array("' foo '", AbstractPlatform::TRIM_LEADING, false, 'foo '),
            array("' foo '", AbstractPlatform::TRIM_TRAILING, false, ' foo'),
            array("' foo '", AbstractPlatform::TRIM_BOTH, false, 'foo'),
            array("' foo '", AbstractPlatform::TRIM_UNSPECIFIED, "'f'", ' foo '),
            array("' foo '", AbstractPlatform::TRIM_UNSPECIFIED, "'o'", ' foo '),
            array("' foo '", AbstractPlatform::TRIM_UNSPECIFIED, "'.'", ' foo '),
            array("' foo '", AbstractPlatform::TRIM_UNSPECIFIED, "' '", 'foo'),
            array("' foo '", AbstractPlatform::TRIM_LEADING, "'f'", ' foo '),
            array("' foo '", AbstractPlatform::TRIM_LEADING, "'o'", ' foo '),
            array("' foo '", AbstractPlatform::TRIM_LEADING, "'.'", ' foo '),
            array("' foo '", AbstractPlatform::TRIM_LEADING, "' '", 'foo '),
            array("' foo '", AbstractPlatform::TRIM_TRAILING, "'f'", ' foo '),
            array("' foo '", AbstractPlatform::TRIM_TRAILING, "'o'", ' foo '),
            array("' foo '", AbstractPlatform::TRIM_TRAILING, "'.'", ' foo '),
            array("' foo '", AbstractPlatform::TRIM_TRAILING, "' '", ' foo'),
            array("' foo '", AbstractPlatform::TRIM_BOTH, "'f'", ' foo '),
            array("' foo '", AbstractPlatform::TRIM_BOTH, "'o'", ' foo '),
            array("' foo '", AbstractPlatform::TRIM_BOTH, "'.'", ' foo '),
            array("' foo '", AbstractPlatform::TRIM_BOTH, "' '", 'foo'),
        );
    }

501 502 503 504 505 506 507
    /**
     * @group DDC-1014
     */
    public function testDateArithmetics()
    {
        $p = $this->_conn->getDatabasePlatform();
        $sql = 'SELECT ';
508
        $sql .= $p->getDateDiffExpression('test_datetime', $p->getCurrentTimestampSQL()) .' AS diff, ';
509 510 511 512
        $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, ';
513 514
        $sql .= $p->getDateAddHourExpression('test_datetime', 3) .' AS add_hour, ';
        $sql .= $p->getDateSubHourExpression('test_datetime', 3) .' AS sub_hour, ';
515 516
        $sql .= $p->getDateAddDaysExpression('test_datetime', 10) .' AS add_days, ';
        $sql .= $p->getDateSubDaysExpression('test_datetime', 10) .' AS sub_days, ';
517 518
        $sql .= $p->getDateAddWeeksExpression('test_datetime', 1) .' AS add_weeks, ';
        $sql .= $p->getDateSubWeeksExpression('test_datetime', 1) .' AS sub_weeks, ';
519
        $sql .= $p->getDateAddMonthExpression('test_datetime', 2) .' AS add_month, ';
520 521 522 523 524
        $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 ';
525 526 527
        $sql .= 'FROM fetch_table';

        $row = $this->_conn->fetchAssoc($sql);
528
        $row = array_change_key_case($row, CASE_LOWER);
529

Steve Müller's avatar
Steve Müller committed
530
        $diff = (strtotime('2010-01-01') - strtotime(date('Y-m-d'))) / 3600 / 24;
531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547
        self::assertEquals($diff, $row['diff'], "Date difference should be approx. ".$diff." days.", 1);
        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");
548
    }
549

550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568
    public function testLocateExpression()
    {
        $platform = $this->_conn->getDatabasePlatform();

        $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 ';
        $sql .= 'FROM fetch_table';

        $row = $this->_conn->fetchAssoc($sql);
        $row = array_change_key_case($row, CASE_LOWER);

569 570 571 572 573 574 575 576 577
        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']);
578 579
    }

580 581 582 583 584
    public function testQuoteSQLInjection()
    {
        $sql = "SELECT * FROM fetch_table WHERE test_string = " . $this->_conn->quote("bar' OR '1'='1");
        $rows = $this->_conn->fetchAll($sql);

585
        self::assertEquals(0, count($rows), "no result should be returned, otherwise SQL injection is possible");
586
    }
587

Fabio B. Silva's avatar
Fabio B. Silva committed
588 589 590 591 592
    /**
     * @group DDC-1213
     */
    public function testBitComparisonExpressionSupport()
    {
593
        $this->_conn->exec('DELETE FROM fetch_table');
Fabio B. Silva's avatar
Fabio B. Silva committed
594
        $platform = $this->_conn->getDatabasePlatform();
Fabio B. Silva's avatar
Fabio B. Silva committed
595
        $bitmap   = array();
596

Fabio B. Silva's avatar
Fabio B. Silva committed
597 598 599 600 601 602
        for ($i = 2; $i < 9; $i = $i + 2) {
            $bitmap[$i] = array(
                'bit_or'    => ($i | 2),
                'bit_and'   => ($i & 2)
            );
            $this->_conn->insert('fetch_table', array(
603
                'test_int'      => $i,
Fabio B. Silva's avatar
Fabio B. Silva committed
604 605 606 607
                'test_string'   => json_encode($bitmap[$i]),
                'test_datetime' => '2010-01-01 10:10:10'
            ));
        }
608

Fabio B. Silva's avatar
Fabio B. Silva committed
609 610 611 612 613 614 615 616 617
        $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';

        $stmt   = $this->_conn->executeQuery(implode(PHP_EOL, $sql));
        $data   = $stmt->fetchAll(PDO::FETCH_ASSOC);
618 619


620 621
        self::assertEquals(4, count($data));
        self::assertEquals(count($bitmap), count($data));
Fabio B. Silva's avatar
Fabio B. Silva committed
622
        foreach ($data as $row) {
623 624
            $row = array_change_key_case($row, CASE_LOWER);

625
            self::assertArrayHasKey('test_int', $row);
626

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

629 630
            self::assertArrayHasKey($id, $bitmap);
            self::assertArrayHasKey($id, $bitmap);
631

632 633
            self::assertArrayHasKey('bit_or', $row);
            self::assertArrayHasKey('bit_and', $row);
634

635 636
            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
637 638
        }
    }
639 640 641 642 643 644 645

    public function testSetDefaultFetchMode()
    {
        $stmt = $this->_conn->query("SELECT * FROM fetch_table");
        $stmt->setFetchMode(\PDO::FETCH_NUM);

        $row = array_keys($stmt->fetch());
646
        self::assertEquals(0, count( array_filter($row, function($v) { return ! is_numeric($v); })), "should be no non-numerical elements in the result.");
647
    }
Fabio B. Silva's avatar
Fabio B. Silva committed
648

649 650 651 652 653 654 655 656 657 658 659 660 661 662
    /**
     * @group DBAL-1091
     */
    public function testFetchAllStyleObject()
    {
        $this->setupFixture();

        $sql = 'SELECT test_int, test_string, test_datetime FROM fetch_table';
        $stmt = $this->_conn->prepare($sql);

        $stmt->execute();

        $results = $stmt->fetchAll(\PDO::FETCH_OBJ);

663 664
        self::assertCount(1, $results);
        self::assertInstanceOf('stdClass', $results[0]);
665

666
        self::assertEquals(
667 668 669
            1,
            property_exists($results[0], 'test_int') ? $results[0]->test_int : $results[0]->TEST_INT
        );
670
        self::assertEquals(
671 672 673
            'foo',
            property_exists($results[0], 'test_string') ? $results[0]->test_string : $results[0]->TEST_STRING
        );
674
        self::assertStringStartsWith(
675 676 677 678 679
            '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
680 681 682 683 684
    /**
     * @group DBAL-196
     */
    public function testFetchAllSupportFetchClass()
    {
685 686
        $this->skipOci8AndMysqli();
        $this->setupFixture();
Fabio B. Silva's avatar
Fabio B. Silva committed
687 688 689 690 691 692 693 694 695 696

        $sql    = "SELECT test_int, test_string, test_datetime FROM fetch_table";
        $stmt   = $this->_conn->prepare($sql);
        $stmt->execute();

        $results = $stmt->fetchAll(
            \PDO::FETCH_CLASS,
            __NAMESPACE__.'\\MyFetchClass'
        );

697 698
        self::assertEquals(1, count($results));
        self::assertInstanceOf(__NAMESPACE__.'\\MyFetchClass', $results[0]);
Benjamin Eberlei's avatar
Benjamin Eberlei committed
699

700 701 702
        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
703
    }
704 705 706 707 708 709 710 711 712 713 714 715 716 717 718

    /**
     * @group DBAL-241
     */
    public function testFetchAllStyleColumn()
    {
        $sql = "DELETE FROM fetch_table";
        $this->_conn->executeUpdate($sql);

        $this->_conn->insert('fetch_table', array('test_int' => 1, 'test_string' => 'foo'));
        $this->_conn->insert('fetch_table', array('test_int' => 10, 'test_string' => 'foo'));

        $sql = "SELECT test_int FROM fetch_table";
        $rows = $this->_conn->query($sql)->fetchAll(\PDO::FETCH_COLUMN);

719
        self::assertEquals(array(1, 10), $rows);
720
    }
721 722 723 724 725 726 727 728 729 730 731

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

        $sql = "SELECT * FROM fetch_table";
        $stmt = $this->_conn->query($sql);
732
        $stmt->setFetchMode(\PDO::FETCH_CLASS, __NAMESPACE__ . '\\MyFetchClass');
733 734 735

        $results = $stmt->fetchAll();

736 737
        self::assertEquals(1, count($results));
        self::assertInstanceOf(__NAMESPACE__.'\\MyFetchClass', $results[0]);
738

739 740 741
        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);
742 743 744 745 746 747 748 749 750 751 752 753
    }

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

        $sql = "SELECT * FROM fetch_table";
        $stmt = $this->_conn->query($sql);
754
        $stmt->setFetchMode(\PDO::FETCH_CLASS, __NAMESPACE__ . '\\MyFetchClass');
755 756 757 758 759 760

        $results = array();
        while ($row = $stmt->fetch()) {
            $results[] = $row;
        }

761 762
        self::assertEquals(1, count($results));
        self::assertInstanceOf(__NAMESPACE__.'\\MyFetchClass', $results[0]);
763

764 765 766
        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);
767 768
    }

769 770 771 772 773
    /**
     * @group DBAL-257
     */
    public function testEmptyFetchColumnReturnsFalse()
    {
Luís Cobucci's avatar
Luís Cobucci committed
774
        $this->_conn->beginTransaction();
775
        $this->_conn->exec('DELETE FROM fetch_table');
776 777
        self::assertFalse($this->_conn->fetchColumn('SELECT test_int FROM fetch_table'));
        self::assertFalse($this->_conn->query('SELECT test_int FROM fetch_table')->fetchColumn());
Luís Cobucci's avatar
Luís Cobucci committed
778
        $this->_conn->rollBack();
779 780
    }

781 782 783 784 785 786 787 788 789
    /**
     * @group DBAL-339
     */
    public function testSetFetchModeOnDbalStatement()
    {
        $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
        $stmt = $this->_conn->executeQuery($sql, array(1, "foo"));
        $stmt->setFetchMode(\PDO::FETCH_NUM);

Luís Cobucci's avatar
Luís Cobucci committed
790 791 792 793 794
        $row = $stmt->fetch();

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

797 798 799 800 801 802 803 804 805
    /**
     * @group DBAL-435
     */
    public function testEmptyParameters()
    {
        $sql = "SELECT * FROM fetch_table WHERE test_int IN (?)";
        $stmt = $this->_conn->executeQuery($sql, array(array()), array(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY));
        $rows = $stmt->fetchAll();

806
        self::assertEquals(array(), $rows);
807 808
    }

809 810 811 812 813 814 815
    /**
     * @group DBAL-1028
     */
    public function testFetchColumnNullValue()
    {
        $this->_conn->executeUpdate(
            'INSERT INTO fetch_table (test_int, test_string) VALUES (?, ?)',
Luís Cobucci's avatar
Luís Cobucci committed
816
            array(2, 'foo')
817 818
        );

819
        self::assertNull(
Luís Cobucci's avatar
Luís Cobucci committed
820
            $this->_conn->fetchColumn('SELECT test_datetime FROM fetch_table WHERE test_int = ?', array(2))
821 822 823 824 825 826 827 828 829 830 831 832 833 834
        );
    }

    /**
     * @group DBAL-1028
     */
    public function testFetchColumnNonExistingIndex()
    {
        if ($this->_conn->getDriver()->getName() === 'pdo_sqlsrv') {
            $this->markTestSkipped(
                'Test does not work for pdo_sqlsrv driver as it throws a fatal error for a non-existing column index.'
            );
        }

835
        self::assertNull(
836 837 838 839 840 841 842 843 844
            $this->_conn->fetchColumn('SELECT test_int FROM fetch_table WHERE test_int = ?', array(1), 1)
        );
    }

    /**
     * @group DBAL-1028
     */
    public function testFetchColumnNoResult()
    {
845
        self::assertFalse(
846 847 848 849
            $this->_conn->fetchColumn('SELECT test_int FROM fetch_table WHERE test_int = ?', array(-1))
        );
    }

850 851
    private function setupFixture()
    {
852
        $this->_conn->exec('DELETE FROM fetch_table');
853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868
        $this->_conn->insert('fetch_table', array(
            'test_int'      => 1,
            'test_string'   => 'foo',
            'test_datetime' => '2010-01-01 10:10:10'
        ));
    }

    private function skipOci8AndMysqli()
    {
        if (isset($GLOBALS['db_type']) && $GLOBALS['db_type'] == "oci8")  {
            $this->markTestSkipped("Not supported by OCI8");
        }
        if ('mysqli' == $this->_conn->getDriver()->getName()) {
            $this->markTestSkipped('Mysqli driver dont support this feature.');
        }
    }
Fabio B. Silva's avatar
Fabio B. Silva committed
869 870 871 872 873
}

class MyFetchClass
{
    public $test_int, $test_string, $test_datetime;
874
}