3 namespace Doctrine\Tests\DBAL\Functional;
5 use Doctrine\DBAL\Types\Type;
6 use Doctrine\DBAL\Connection;
9 require_once __DIR__ . '/../../TestInit.php';
11 class ModifyLimitQueryTest extends \Doctrine\Tests\DbalFunctionalTestCase
13 private static $tableCreated = false;
15 public function setUp()
19 if (!self::$tableCreated) {
20 /* @var $sm \Doctrine\DBAL\Schema\AbstractSchemaManager */
21 $table = new \Doctrine\DBAL\Schema\Table("modify_limit_table");
22 $table->addColumn('test_int', 'integer');
23 $table->setPrimaryKey(array('test_int'));
25 $table2 = new \Doctrine\DBAL\Schema\Table("modify_limit_table2");
26 $table2->addColumn('id', 'integer', array('autoincrement' => true));
27 $table2->addColumn('test_int', 'integer');
28 $table2->setPrimaryKey(array('id'));
30 $sm = $this->_conn->getSchemaManager();
31 $sm->createTable($table);
32 $sm->createTable($table2);
33 self::$tableCreated = true;
35 $this->_conn->exec($this->_conn->getDatabasePlatform()->getTruncateTableSQL('modify_limit_table'));
36 $this->_conn->exec($this->_conn->getDatabasePlatform()->getTruncateTableSQL('modify_limit_table2'));
39 public function testModifyLimitQuerySimpleQuery()
41 $this->_conn->insert('modify_limit_table', array('test_int' => 1));
42 $this->_conn->insert('modify_limit_table', array('test_int' => 2));
43 $this->_conn->insert('modify_limit_table', array('test_int' => 3));
44 $this->_conn->insert('modify_limit_table', array('test_int' => 4));
46 $sql = "SELECT * FROM modify_limit_table";
48 $this->assertLimitResult(array(1, 2, 3, 4), $sql, 10, 0);
49 $this->assertLimitResult(array(1, 2), $sql, 2, 0);
50 $this->assertLimitResult(array(3, 4), $sql, 2, 2);
53 public function testModifyLimitQueryJoinQuery()
55 $this->_conn->insert('modify_limit_table', array('test_int' => 1));
56 $this->_conn->insert('modify_limit_table', array('test_int' => 2));
58 $this->_conn->insert('modify_limit_table2', array('test_int' => 1));
59 $this->_conn->insert('modify_limit_table2', array('test_int' => 1));
60 $this->_conn->insert('modify_limit_table2', array('test_int' => 1));
61 $this->_conn->insert('modify_limit_table2', array('test_int' => 2));
62 $this->_conn->insert('modify_limit_table2', array('test_int' => 2));
64 $sql = "SELECT modify_limit_table.test_int FROM modify_limit_table INNER JOIN modify_limit_table2 ON modify_limit_table.test_int = modify_limit_table2.test_int";
66 $this->assertLimitResult(array(1, 1, 1, 2, 2), $sql, 10, 0);
67 $this->assertLimitResult(array(1, 1, 1), $sql, 3, 0);
68 $this->assertLimitResult(array(2, 2), $sql, 2, 3);
71 public function testModifyLimitQueryOrderBy()
73 $this->_conn->insert('modify_limit_table', array('test_int' => 1));
74 $this->_conn->insert('modify_limit_table', array('test_int' => 2));
75 $this->_conn->insert('modify_limit_table', array('test_int' => 3));
76 $this->_conn->insert('modify_limit_table', array('test_int' => 4));
78 $sql = "SELECT * FROM modify_limit_table ORDER BY test_int DESC";
80 $this->assertLimitResult(array(4, 3, 2, 1), $sql, 10, 0);
81 $this->assertLimitResult(array(4, 3), $sql, 2, 0);
82 $this->assertLimitResult(array(2, 1), $sql, 2, 2);
85 public function testModifyLimitQueryGroupBy()
87 $this->_conn->insert('modify_limit_table', array('test_int' => 1));
88 $this->_conn->insert('modify_limit_table', array('test_int' => 2));
90 $this->_conn->insert('modify_limit_table2', array('test_int' => 1));
91 $this->_conn->insert('modify_limit_table2', array('test_int' => 1));
92 $this->_conn->insert('modify_limit_table2', array('test_int' => 1));
93 $this->_conn->insert('modify_limit_table2', array('test_int' => 2));
94 $this->_conn->insert('modify_limit_table2', array('test_int' => 2));
96 $sql = "SELECT modify_limit_table.test_int FROM modify_limit_table " .
97 "INNER JOIN modify_limit_table2 ON modify_limit_table.test_int = modify_limit_table2.test_int ".
98 "GROUP BY modify_limit_table.test_int";
99 $this->assertLimitResult(array(1, 2), $sql, 10, 0);
100 $this->assertLimitResult(array(1), $sql, 1, 0);
101 $this->assertLimitResult(array(2), $sql, 1, 1);
104 public function assertLimitResult($expectedResults, $sql, $limit, $offset)
106 $p = $this->_conn->getDatabasePlatform();
108 foreach ($this->_conn->fetchAll($p->modifyLimitQuery($sql, $limit, $offset)) AS $row) {
109 $row = array_change_key_case($row, CASE_LOWER);
110 $data[] = $row['test_int'];
112 $this->assertEquals($expectedResults, $data);