vendor/doctrine/dbal/tests/Doctrine/Tests/DBAL/Functional/DataAccessTest.php

   1 <?php
   2
   3 namespace Doctrine\Tests\DBAL\Functional;
   4
   5 use Doctrine\DBAL\Types\Type;
   6 use Doctrine\DBAL\Connection;
   7 use PDO;
   8
   9 require_once __DIR__ . '/../../TestInit.php';
  10
  11 class DataAccessTest extends \Doctrine\Tests\DbalFunctionalTestCase
  12 {
  13     static private $generated = false;
  14
  15     public function setUp()
  16     {
  17         parent::setUp();
  18
  19         if (self::$generated === false) {
  20             /* @var $sm \Doctrine\DBAL\Schema\AbstractSchemaManager */
  21             $table = new \Doctrine\DBAL\Schema\Table("fetch_table");
  22             $table->addColumn('test_int', 'integer');
  23             $table->addColumn('test_string', 'string');
  24             $table->addColumn('test_datetime', 'datetime', array('notnull' => false));
  25             $table->setPrimaryKey(array('test_int'));
  26
  27             $sm = $this->_conn->getSchemaManager();
  28             $sm->createTable($table);
  29
  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     public function testPrepareWithBindValue()
  36     {
  37         $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
  38         $stmt = $this->_conn->prepare($sql);
  39         $this->assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
  40
  41         $stmt->bindValue(1, 1);
  42         $stmt->bindValue(2, 'foo');
  43         $stmt->execute();
  44
  45         $row = $stmt->fetch(\PDO::FETCH_ASSOC);
  46         $row = array_change_key_case($row, \CASE_LOWER);
  47         $this->assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $row);
  48     }
  49
  50     public function testPrepareWithBindParam()
  51     {
  52         $paramInt = 1;
  53         $paramStr = 'foo';
  54
  55         $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
  56         $stmt = $this->_conn->prepare($sql);
  57         $this->assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
  58
  59         $stmt->bindParam(1, $paramInt);
  60         $stmt->bindParam(2, $paramStr);
  61         $stmt->execute();
  62
  63         $row = $stmt->fetch(\PDO::FETCH_ASSOC);
  64         $row = array_change_key_case($row, \CASE_LOWER);
  65         $this->assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $row);
  66     }
  67
  68     public function testPrepareWithFetchAll()
  69     {
  70         $paramInt = 1;
  71         $paramStr = 'foo';
  72
  73         $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
  74         $stmt = $this->_conn->prepare($sql);
  75         $this->assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
  76
  77         $stmt->bindParam(1, $paramInt);
  78         $stmt->bindParam(2, $paramStr);
  79         $stmt->execute();
  80
  81         $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
  82         $rows[0] = array_change_key_case($rows[0], \CASE_LOWER);
  83         $this->assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $rows[0]);
  84     }
  85
  86     /**
  87      * @group DBAL-228
  88      */
  89     public function testPrepareWithFetchAllBoth()
  90     {
  91         $paramInt = 1;
  92         $paramStr = 'foo';
  93
  94         $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
  95         $stmt = $this->_conn->prepare($sql);
  96         $this->assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
  97
  98         $stmt->bindParam(1, $paramInt);
  99         $stmt->bindParam(2, $paramStr);
 100         $stmt->execute();
 101
 102         $rows = $stmt->fetchAll(\PDO::FETCH_BOTH);
 103         $rows[0] = array_change_key_case($rows[0], \CASE_LOWER);
 104         $this->assertEquals(array('test_int' => 1, 'test_string' => 'foo', 0 => 1, 1 => 'foo'), $rows[0]);
 105     }
 106
 107     public function testPrepareWithFetchColumn()
 108     {
 109         $paramInt = 1;
 110         $paramStr = 'foo';
 111
 112         $sql = "SELECT test_int FROM fetch_table WHERE test_int = ? AND test_string = ?";
 113         $stmt = $this->_conn->prepare($sql);
 114         $this->assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
 115
 116         $stmt->bindParam(1, $paramInt);
 117         $stmt->bindParam(2, $paramStr);
 118         $stmt->execute();
 119
 120         $column = $stmt->fetchColumn();
 121         $this->assertEquals(1, $column);
 122     }
 123
 124     public function testPrepareWithIterator()
 125     {
 126         $paramInt = 1;
 127         $paramStr = 'foo';
 128
 129         $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
 130         $stmt = $this->_conn->prepare($sql);
 131         $this->assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
 132
 133         $stmt->bindParam(1, $paramInt);
 134         $stmt->bindParam(2, $paramStr);
 135         $stmt->execute();
 136
 137         $rows = array();
 138         $stmt->setFetchMode(\PDO::FETCH_ASSOC);
 139         foreach ($stmt as $row) {
 140             $rows[] = array_change_key_case($row, \CASE_LOWER);
 141         }
 142
 143         $this->assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $rows[0]);
 144     }
 145
 146     public function testPrepareWithQuoted()
 147     {
 148         $table = 'fetch_table';
 149         $paramInt = 1;
 150         $paramStr = 'foo';
 151
 152         $sql = "SELECT test_int, test_string FROM " . $this->_conn->quoteIdentifier($table) . " ".
 153                "WHERE test_int = " . $this->_conn->quote($paramInt) . " AND test_string = " . $this->_conn->quote($paramStr);
 154         $stmt = $this->_conn->prepare($sql);
 155         $this->assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
 156     }
 157
 158     public function testPrepareWithExecuteParams()
 159     {
 160         $paramInt = 1;
 161         $paramStr = 'foo';
 162
 163         $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
 164         $stmt = $this->_conn->prepare($sql);
 165         $this->assertInstanceOf('Doctrine\DBAL\Statement', $stmt);
 166         $stmt->execute(array($paramInt, $paramStr));
 167
 168         $row = $stmt->fetch(\PDO::FETCH_ASSOC);
 169         $this->assertTrue($row !== false);
 170         $row = array_change_key_case($row, \CASE_LOWER);
 171         $this->assertEquals(array('test_int' => 1, 'test_string' => 'foo'), $row);
 172     }
 173
 174     public function testFetchAll()
 175     {
 176         $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
 177         $data = $this->_conn->fetchAll($sql, array(1, 'foo'));
 178
 179         $this->assertEquals(1, count($data));
 180
 181         $row = $data[0];
 182         $this->assertEquals(2, count($row));
 183
 184         $row = array_change_key_case($row, \CASE_LOWER);
 185         $this->assertEquals(1, $row['test_int']);
 186         $this->assertEquals('foo', $row['test_string']);
 187     }
 188
 189     public function testFetchBoth()
 190     {
 191         $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
 192         $row = $this->_conn->executeQuery($sql, array(1, 'foo'))->fetch(\PDO::FETCH_BOTH);
 193
 194         $this->assertTrue($row !== false);
 195
 196         $row = array_change_key_case($row, \CASE_LOWER);
 197
 198         $this->assertEquals(1, $row['test_int']);
 199         $this->assertEquals('foo', $row['test_string']);
 200         $this->assertEquals(1, $row[0]);
 201         $this->assertEquals('foo', $row[1]);
 202     }
 203
 204     public function testFetchRow()
 205     {
 206         $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         $this->assertTrue($row !== false);
 210
 211         $row = array_change_key_case($row, \CASE_LOWER);
 212
 213         $this->assertEquals(1, $row['test_int']);
 214         $this->assertEquals('foo', $row['test_string']);
 215     }
 216
 217     public function testFetchArray()
 218     {
 219         $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
 220         $row = $this->_conn->fetchArray($sql, array(1, 'foo'));
 221
 222         $this->assertEquals(1, $row[0]);
 223         $this->assertEquals('foo', $row[1]);
 224     }
 225
 226     public function testFetchColumn()
 227     {
 228         $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
 229         $testInt = $this->_conn->fetchColumn($sql, array(1, 'foo'), 0);
 230
 231         $this->assertEquals(1, $testInt);
 232
 233         $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
 234         $testString = $this->_conn->fetchColumn($sql, array(1, 'foo'), 1);
 235
 236         $this->assertEquals('foo', $testString);
 237     }
 238
 239     /**
 240      * @group DDC-697
 241      */
 242     public function testExecuteQueryBindDateTimeType()
 243     {
 244         $sql = 'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?';
 245         $stmt = $this->_conn->executeQuery($sql,
 246             array(1 => new \DateTime('2010-01-01 10:10:10')),
 247             array(1 => Type::DATETIME)
 248         );
 249
 250         $this->assertEquals(1, $stmt->fetchColumn());
 251     }
 252
 253     /**
 254      * @group DDC-697
 255      */
 256     public function testExecuteUpdateBindDateTimeType()
 257     {
 258         $datetime = new \DateTime('2010-02-02 20:20:20');
 259
 260         $sql = 'INSERT INTO fetch_table (test_int, test_string, test_datetime) VALUES (?, ?, ?)';
 261         $affectedRows = $this->_conn->executeUpdate($sql,
 262             array(1 => 50,              2 => 'foo',             3 => $datetime),
 263             array(1 => PDO::PARAM_INT,  2 => PDO::PARAM_STR,    3 => Type::DATETIME)
 264         );
 265
 266         $this->assertEquals(1, $affectedRows);
 267         $this->assertEquals(1, $this->_conn->executeQuery(
 268             'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?',
 269             array(1 => $datetime),
 270             array(1 => Type::DATETIME)
 271         )->fetchColumn());
 272     }
 273
 274     /**
 275      * @group DDC-697
 276      */
 277     public function testPrepareQueryBindValueDateTimeType()
 278     {
 279         $sql = 'SELECT count(*) AS c FROM fetch_table WHERE test_datetime = ?';
 280         $stmt = $this->_conn->prepare($sql);
 281         $stmt->bindValue(1, new \DateTime('2010-01-01 10:10:10'), Type::DATETIME);
 282         $stmt->execute();
 283
 284         $this->assertEquals(1, $stmt->fetchColumn());
 285     }
 286
 287     /**
 288      * @group DBAL-78
 289      */
 290     public function testNativeArrayListSupport()
 291     {
 292         for ($i = 100; $i < 110; $i++) {
 293             $this->_conn->insert('fetch_table', array('test_int' => $i, 'test_string' => 'foo' . $i, 'test_datetime' => '2010-01-01 10:10:10'));
 294         }
 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         $data = $stmt->fetchAll(PDO::FETCH_NUM);
 300         $this->assertEquals(5, count($data));
 301         $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         $data = $stmt->fetchAll(PDO::FETCH_NUM);
 307         $this->assertEquals(5, count($data));
 308         $this->assertEquals(array(array(100), array(101), array(102), array(103), array(104)), $data);
 309     }
 310
 311     /**
 312      * @group DDC-1014
 313      */
 314     public function testDateArithmetics()
 315     {
 316         $p = $this->_conn->getDatabasePlatform();
 317         $sql = 'SELECT ';
 318         $sql .= $p->getDateDiffExpression('test_datetime', $p->getCurrentTimestampSQL()) .' AS diff, ';
 319         $sql .= $p->getDateAddDaysExpression('test_datetime', 10) .' AS add_days, ';
 320         $sql .= $p->getDateSubDaysExpression('test_datetime', 10) .' AS sub_days, ';
 321         $sql .= $p->getDateAddMonthExpression('test_datetime', 2) .' AS add_month, ';
 322         $sql .= $p->getDateSubMonthExpression('test_datetime', 2) .' AS sub_month ';
 323         $sql .= 'FROM fetch_table';
 324
 325         $row = $this->_conn->fetchAssoc($sql);
 326         $row = array_change_key_case($row, CASE_LOWER);
 327
 328         $diff = floor( (strtotime('2010-01-01')-time()) / 3600 / 24);
 329         $this->assertEquals($diff, (int)$row['diff'], "Date difference should be approx. ".$diff." days.", 1);
 330         $this->assertEquals('2010-01-11', date('Y-m-d', strtotime($row['add_days'])), "Adding date should end up on 2010-01-11");
 331         $this->assertEquals('2009-12-22', date('Y-m-d', strtotime($row['sub_days'])), "Subtracting date should end up on 2009-12-22");
 332         $this->assertEquals('2010-03-01', date('Y-m-d', strtotime($row['add_month'])), "Adding month should end up on 2010-03-01");
 333         $this->assertEquals('2009-11-01', date('Y-m-d', strtotime($row['sub_month'])), "Adding month should end up on 2009-11-01");
 334     }
 335
 336     public function testQuoteSQLInjection()
 337     {
 338         $sql = "SELECT * FROM fetch_table WHERE test_string = " . $this->_conn->quote("bar' OR '1'='1");
 339         $rows = $this->_conn->fetchAll($sql);
 340
 341         $this->assertEquals(0, count($rows), "no result should be returned, otherwise SQL injection is possible");
 342     }
 343
 344     /**
 345      * @group DDC-1213
 346      */
 347     public function testBitComparisonExpressionSupport()
 348     {
 349         $this->_conn->executeQuery('DELETE FROM fetch_table')->execute();
 350         $platform = $this->_conn->getDatabasePlatform();
 351         $bitmap   = array();
 352
 353         for ($i = 2; $i < 9; $i = $i + 2) {
 354             $bitmap[$i] = array(
 355                 'bit_or'    => ($i | 2),
 356                 'bit_and'   => ($i & 2)
 357             );
 358             $this->_conn->insert('fetch_table', array(
 359                 'test_int'      => $i,
 360                 'test_string'   => json_encode($bitmap[$i]),
 361                 'test_datetime' => '2010-01-01 10:10:10'
 362             ));
 363         }
 364
 365         $sql[]  = 'SELECT ';
 366         $sql[]  = 'test_int, ';
 367         $sql[]  = 'test_string, ';
 368         $sql[]  = $platform->getBitOrComparisonExpression('test_int', 2) . ' AS bit_or, ';
 369         $sql[]  = $platform->getBitAndComparisonExpression('test_int', 2) . ' AS bit_and ';
 370         $sql[]  = 'FROM fetch_table';
 371
 372         $stmt   = $this->_conn->executeQuery(implode(PHP_EOL, $sql));
 373         $data   = $stmt->fetchAll(PDO::FETCH_ASSOC);
 374
 375
 376         $this->assertEquals(4, count($data));
 377         $this->assertEquals(count($bitmap), count($data));
 378         foreach ($data as $row) {
 379             $row = array_change_key_case($row, CASE_LOWER);
 380
 381             $this->assertArrayHasKey('test_int', $row);
 382
 383             $id = $row['test_int'];
 384
 385             $this->assertArrayHasKey($id, $bitmap);
 386             $this->assertArrayHasKey($id, $bitmap);
 387
 388             $this->assertArrayHasKey('bit_or', $row);
 389             $this->assertArrayHasKey('bit_and', $row);
 390
 391             $this->assertEquals($row['bit_or'], $bitmap[$id]['bit_or']);
 392             $this->assertEquals($row['bit_and'], $bitmap[$id]['bit_and']);
 393         }
 394     }
 395
 396     public function testSetDefaultFetchMode()
 397     {
 398         $stmt = $this->_conn->query("SELECT * FROM fetch_table");
 399         $stmt->setFetchMode(\PDO::FETCH_NUM);
 400
 401         $row = array_keys($stmt->fetch());
 402         $this->assertEquals(0, count( array_filter($row, function($v) { return ! is_numeric($v); })), "should be no non-numerical elements in the result.");
 403     }
 404
 405     /**
 406      * @group DBAL-196
 407      */
 408     public function testFetchAllSupportFetchClass()
 409     {
 410         $this->skipOci8AndMysqli();
 411         $this->setupFixture();
 412
 413         $sql    = "SELECT test_int, test_string, test_datetime FROM fetch_table";
 414         $stmt   = $this->_conn->prepare($sql);
 415         $stmt->execute();
 416
 417         $results = $stmt->fetchAll(
 418             \PDO::FETCH_CLASS,
 419             __NAMESPACE__.'\\MyFetchClass'
 420         );
 421
 422         $this->assertEquals(1, count($results));
 423         $this->assertInstanceOf(__NAMESPACE__.'\\MyFetchClass', $results[0]);
 424
 425         $this->assertEquals(1, $results[0]->test_int);
 426         $this->assertEquals('foo', $results[0]->test_string);
 427         $this->assertStringStartsWith('2010-01-01 10:10:10', $results[0]->test_datetime);
 428     }
 429
 430     /**
 431      * @group DBAL-241
 432      */
 433     public function testFetchAllStyleColumn()
 434     {
 435         $sql = "DELETE FROM fetch_table";
 436         $this->_conn->executeUpdate($sql);
 437
 438         $this->_conn->insert('fetch_table', array('test_int' => 1, 'test_string' => 'foo'));
 439         $this->_conn->insert('fetch_table', array('test_int' => 10, 'test_string' => 'foo'));
 440
 441         $sql = "SELECT test_int FROM fetch_table";
 442         $rows = $this->_conn->query($sql)->fetchAll(\PDO::FETCH_COLUMN);
 443
 444         $this->assertEquals(array(1, 10), $rows);
 445     }
 446
 447     /**
 448      * @group DBAL-214
 449      */
 450     public function testSetFetchModeClassFetchAll()
 451     {
 452         $this->skipOci8AndMysqli();
 453         $this->setupFixture();
 454
 455         $sql = "SELECT * FROM fetch_table";
 456         $stmt = $this->_conn->query($sql);
 457         $stmt->setFetchMode(\PDO::FETCH_CLASS, __NAMESPACE__ . '\\MyFetchClass', array());
 458
 459         $results = $stmt->fetchAll();
 460
 461         $this->assertEquals(1, count($results));
 462         $this->assertInstanceOf(__NAMESPACE__.'\\MyFetchClass', $results[0]);
 463
 464         $this->assertEquals(1, $results[0]->test_int);
 465         $this->assertEquals('foo', $results[0]->test_string);
 466         $this->assertStringStartsWith('2010-01-01 10:10:10', $results[0]->test_datetime);
 467     }
 468
 469     /**
 470      * @group DBAL-214
 471      */
 472     public function testSetFetchModeClassFetch()
 473     {
 474         $this->skipOci8AndMysqli();
 475         $this->setupFixture();
 476
 477         $sql = "SELECT * FROM fetch_table";
 478         $stmt = $this->_conn->query($sql);
 479         $stmt->setFetchMode(\PDO::FETCH_CLASS, __NAMESPACE__ . '\\MyFetchClass', array());
 480
 481         $results = array();
 482         while ($row = $stmt->fetch()) {
 483             $results[] = $row;
 484         }
 485
 486         $this->assertEquals(1, count($results));
 487         $this->assertInstanceOf(__NAMESPACE__.'\\MyFetchClass', $results[0]);
 488
 489         $this->assertEquals(1, $results[0]->test_int);
 490         $this->assertEquals('foo', $results[0]->test_string);
 491         $this->assertStringStartsWith('2010-01-01 10:10:10', $results[0]->test_datetime);
 492     }
 493
 494     /**
 495      * @group DBAL-257
 496      */
 497     public function testEmptyFetchColumnReturnsFalse()
 498     {
 499         $this->_conn->executeQuery('DELETE FROM fetch_table')->execute();
 500         $this->assertFalse($this->_conn->fetchColumn('SELECT test_int FROM fetch_table'));
 501         $this->assertFalse($this->_conn->query('SELECT test_int FROM fetch_table')->fetchColumn());
 502     }
 503
 504     /**
 505      * @group DBAL-339
 506      */
 507     public function testSetFetchModeOnDbalStatement()
 508     {
 509         $sql = "SELECT test_int, test_string FROM fetch_table WHERE test_int = ? AND test_string = ?";
 510         $stmt = $this->_conn->executeQuery($sql, array(1, "foo"));
 511         $stmt->setFetchMode(\PDO::FETCH_NUM);
 512
 513         while ($row = $stmt->fetch()) {
 514             $this->assertTrue(isset($row[0]));
 515             $this->assertTrue(isset($row[1]));
 516         }
 517     }
 518
 519     private function setupFixture()
 520     {
 521         $this->_conn->executeQuery('DELETE FROM fetch_table')->execute();
 522         $this->_conn->insert('fetch_table', array(
 523             'test_int'      => 1,
 524             'test_string'   => 'foo',
 525             'test_datetime' => '2010-01-01 10:10:10'
 526         ));
 527     }
 528
 529     private function skipOci8AndMysqli()
 530     {
 531         if (isset($GLOBALS['db_type']) && $GLOBALS['db_type'] == "oci8")  {
 532             $this->markTestSkipped("Not supported by OCI8");
 533         }
 534         if ('mysqli' == $this->_conn->getDriver()->getName()) {
 535             $this->markTestSkipped('Mysqli driver dont support this feature.');
 536         }
 537     }
 538 }
 539
 540 class MyFetchClass
 541 {
 542     public $test_int, $test_string, $test_datetime;
 543 }