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

3 4
namespace Doctrine\Tests\DBAL\Functional;

5
use Doctrine\DBAL\Types\Type;
6
use Doctrine\DBAL\Connection;
7 8
use PDO;

9
require_once __DIR__ . '/../../TestInit.php';
10 11 12

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

15 16 17 18
    public function setUp()
    {
        parent::setUp();

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

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

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

35 36 37 38
    public function testPrepareWithBindValue()
    {
        $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
        $stmt = $this->_conn->prepare($sql);
39
        $this->assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56

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

        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        $row = array_change_key_case($row, \CASE_LOWER);
        $this->assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $row);
    }

    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);
57
        $this->assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74

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

        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        $row = array_change_key_case($row, \CASE_LOWER);
        $this->assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $row);
    }

    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);
75
        $this->assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
76 77 78 79 80 81 82 83 84 85

        $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);
        $this->assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $rows[0]);
    }

86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106
    /**
     * @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);
        $this->assertInstanceOf('Doctrine\DBAL\Statement', $stmt);

        $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);
        $this->assertEquals(array('test_int' => 1, 'test_string' => 'foo', 0 => 1, 1 => 'foo'), $rows[0]);
    }

107 108 109 110 111 112 113
    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);
114
        $this->assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
115 116 117 118 119 120 121 122 123

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

        $column = $stmt->fetchColumn();
        $this->assertEquals(1, $column);
    }

124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145
    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);
        $this->assertInstanceOf('Doctrine\DBAL\Statement', $stmt);

        $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);
        }

        $this->assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $rows[0]);
    }

146 147 148 149 150 151 152 153 154
    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);
155
        $this->assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
156 157 158 159 160 161 162 163 164
    }

    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);
165
        $this->assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
166 167 168
        $stmt->execute(array($paramInt, $paramStr));

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

174 175 176 177 178 179 180 181 182 183 184 185 186 187 188
    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'));

        $this->assertEquals(1, count($data));

        $row = $data[0];
        $this->assertEquals(2, count($row));

        $row = array_change_key_case($row, \CASE_LOWER);
        $this->assertEquals(1, $row['test_int']);
        $this->assertEquals('foo', $row['test_string']);
    }

189 190 191 192 193 194 195 196 197 198 199 200 201 202 203
    public function testFetchBoth()
    {
        $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
        $row = $this->_conn->executeQuery($sql, array(1, 'foo'))->fetch();

        $this->assertTrue($row !== false);

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

        $this->assertEquals(1, $row['test_int']);
        $this->assertEquals('foo', $row['test_string']);
        $this->assertEquals(1, $row[0]);
        $this->assertEquals('foo', $row[1]);
    }

204 205 206
    public function testFetchRow()
    {
        $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
207
        $row = $this->_conn->fetchAssoc($sql, array(1, 'foo'));
208

209 210
        $this->assertTrue($row !== false);

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

213 214 215 216 217 218 219 220 221 222 223 224 225
        $this->assertEquals(1, $row['test_int']);
        $this->assertEquals('foo', $row['test_string']);
    }

    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'));

        $this->assertEquals(1, $row[0]);
        $this->assertEquals('foo', $row[1]);
    }

226 227 228 229 230 231 232 233 234 235 236 237
    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);

        $this->assertEquals(1, $testInt);

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

        $this->assertEquals('foo', $testString);
    }
238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261

    /**
     * @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)
        );

        $this->assertEquals(1, $stmt->fetchColumn());
    }

    /**
     * @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,
262
            array(1 => 50,              2 => 'foo',             3 => $datetime),
263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285
            array(1 => PDO::PARAM_INT,  2 => PDO::PARAM_STR,    3 => Type::DATETIME)
        );

        $this->assertEquals(1, $affectedRows);
        $this->assertEquals(1, $this->_conn->executeQuery(
            '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();

        $this->assertEquals(1, $stmt->fetchColumn());
    }
286

287 288 289 290 291 292 293 294
    /**
     * @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'));
        }
295

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

299 300 301
        $data = $stmt->fetchAll(PDO::FETCH_NUM);
        $this->assertEquals(5, count($data));
        $this->assertEquals(array(array(100), array(101), array(102), array(103), array(104)), $data);
302

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

306 307 308 309
        $data = $stmt->fetchAll(PDO::FETCH_NUM);
        $this->assertEquals(5, count($data));
        $this->assertEquals(array(array(100), array(101), array(102), array(103), array(104)), $data);
    }
310 311 312 313 314 315 316 317

    /**
     * @group DDC-1014
     */
    public function testDateArithmetics()
    {
        $p = $this->_conn->getDatabasePlatform();
        $sql = 'SELECT ';
318
        $sql .= $p->getDateDiffExpression('test_datetime', $p->getCurrentTimestampSQL()) .' AS diff, ';
319 320 321 322 323 324 325
        $sql .= $p->getDateAddDaysExpression('test_datetime', 10) .' AS add_days, ';
        $sql .= $p->getDateSubDaysExpression('test_datetime', 10) .' AS sub_days, ';
        $sql .= $p->getDateAddMonthExpression('test_datetime', 2) .' AS add_month, ';
        $sql .= $p->getDateSubMonthExpression('test_datetime', 2) .' AS sub_month ';
        $sql .= 'FROM fetch_table';

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

328 329
        $diff = floor( (strtotime('2010-01-01')-time()) / 3600 / 24);
        $this->assertEquals($diff, (int)$row['diff'], "Date difference should be approx. ".$diff." days.", 1);
330 331 332 333 334
        $this->assertEquals('2010-01-11', date('Y-m-d', strtotime($row['add_days'])), "Adding date should end up on 2010-01-11");
        $this->assertEquals('2009-12-22', date('Y-m-d', strtotime($row['sub_days'])), "Subtracting date should end up on 2009-12-22");
        $this->assertEquals('2010-03-01', date('Y-m-d', strtotime($row['add_month'])), "Adding month should end up on 2010-03-01");
        $this->assertEquals('2009-11-01', date('Y-m-d', strtotime($row['sub_month'])), "Adding month should end up on 2009-11-01");
    }
335 336 337 338 339 340 341 342

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

        $this->assertEquals(0, count($rows), "no result should be returned, otherwise SQL injection is possible");
    }
343

Fabio B. Silva's avatar
Fabio B. Silva committed
344 345 346 347 348 349
    /**
     * @group DDC-1213
     */
    public function testBitComparisonExpressionSupport()
    {
        $this->_conn->executeQuery('DELETE FROM fetch_table')->execute();
Fabio B. Silva's avatar
Fabio B. Silva committed
350
        $platform = $this->_conn->getDatabasePlatform();
Fabio B. Silva's avatar
Fabio B. Silva committed
351
        $bitmap   = array();
352

Fabio B. Silva's avatar
Fabio B. Silva committed
353 354 355 356 357 358
        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(
359
                'test_int'      => $i,
Fabio B. Silva's avatar
Fabio B. Silva committed
360 361 362 363
                'test_string'   => json_encode($bitmap[$i]),
                'test_datetime' => '2010-01-01 10:10:10'
            ));
        }
364

Fabio B. Silva's avatar
Fabio B. Silva committed
365 366 367 368 369 370 371 372 373
        $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);
374 375


Fabio B. Silva's avatar
Fabio B. Silva committed
376 377 378
        $this->assertEquals(4, count($data));
        $this->assertEquals(count($bitmap), count($data));
        foreach ($data as $row) {
379 380
            $row = array_change_key_case($row, CASE_LOWER);

Fabio B. Silva's avatar
Fabio B. Silva committed
381
            $this->assertArrayHasKey('test_int', $row);
382

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

Fabio B. Silva's avatar
Fabio B. Silva committed
385 386
            $this->assertArrayHasKey($id, $bitmap);
            $this->assertArrayHasKey($id, $bitmap);
387

Fabio B. Silva's avatar
Fabio B. Silva committed
388 389
            $this->assertArrayHasKey('bit_or', $row);
            $this->assertArrayHasKey('bit_and', $row);
390

Fabio B. Silva's avatar
Fabio B. Silva committed
391 392 393 394
            $this->assertEquals($row['bit_or'], $bitmap[$id]['bit_or']);
            $this->assertEquals($row['bit_and'], $bitmap[$id]['bit_and']);
        }
    }
395 396 397 398 399 400 401 402 403

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

        $row = array_keys($stmt->fetch());
        $this->assertEquals(0, count( array_filter($row, function($v) { return ! is_numeric($v); })), "should be no non-numerical elements in the result.");
    }
Fabio B. Silva's avatar
Fabio B. Silva committed
404 405 406 407 408 409

    /**
     * @group DBAL-196
     */
    public function testFetchAllSupportFetchClass()
    {
410
        $this->skipOci8AndMysqli();
Benjamin Eberlei's avatar
Benjamin Eberlei committed
411

Fabio B. Silva's avatar
Fabio B. Silva committed
412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429
        $this->_conn->executeQuery('DELETE FROM fetch_table')->execute();
        $this->_conn->insert('fetch_table', array(
            'test_int'      => 1,
            'test_string'   => 'foo',
            'test_datetime' => '2010-01-01 10:10:10'
        ));

        $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'
        );

        $this->assertEquals(1, count($results));
        $this->assertInstanceOf(__NAMESPACE__.'\\MyFetchClass', $results[0]);
Benjamin Eberlei's avatar
Benjamin Eberlei committed
430

Fabio B. Silva's avatar
Fabio B. Silva committed
431 432
        $this->assertEquals(1, $results[0]->test_int);
        $this->assertEquals('foo', $results[0]->test_string);
433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470
        $this->assertStringStartsWith('2010-01-01 10:10:10', $results[0]->test_datetime);
    }

    /**
     * @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);

        $this->assertEquals(array(1, 10), $rows);
    }

    /**
     * @group DBAL-257
     */
    public function testEmptyFetchColumnReturnsFalse()
    {
        $this->_conn->executeQuery('DELETE FROM fetch_table')->execute();
        $this->assertFalse($this->_conn->fetchColumn('SELECT test_int FROM fetch_table'));
        $this->assertFalse($this->_conn->query('SELECT test_int FROM fetch_table')->fetchColumn());
    }

    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
471 472 473 474 475 476
    }
}

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