Changeset 4372

Timestamp:

05/17/08 13:01:48 (8 months ago)

Author:

romanb

Message:

Fixed #1039. bruno.p.reis: please take a look at the changeset and verify that the generated SQL is correct. We can't test against a real oracle db yet.

Location:

branches/0.11

Files:

• lib/Doctrine/Connection.php (modified) (1 diff)
• lib/Doctrine/Connection/Db2.php (modified) (1 diff)
• lib/Doctrine/Connection/Oracle.php (modified) (3 diffs)
• lib/Doctrine/Query.php (modified) (4 diffs)
• tests/Query/DriverTestCase.php (modified) (1 diff)

branches/0.11/lib/Doctrine/Connection.php

@@ -1504 +1504 @@
      * Some dbms require specific functionality for this. Check the other connection adapters for examples
      *
-     * @return void
+     * @return string
      */
     public function modifyLimitQuery($query, $limit = false, $offset = false, $isManip = false)
     {
         return $query;
+    }
+    
+    /**
+     * Creates dbms specific LIMIT/OFFSET SQL for the subqueries that are used in the
+     * context of the limit-subquery algorithm.
+     *
+     * @return string
+     */
+    public function modifyLimitSubquery(Doctrine_Table $rootTable, $query, $limit = false,
+            $offset = false, $isManip = false)
+    {
+        return $this->modifyLimitQuery($query, $limit, $offset, $isManip);
     }
 

branches/0.11/lib/Doctrine/Connection/Db2.php

@@ -56 +56 @@
 
             $sql = 'WITH OFFSET AS(' . $select . ', ROW_NUMBER() ' .
-               'OVER(ORDER BY ' . $col[1] . ') AS dctrn_rownum FROM ' . $table . ')' .
-               $select . 'FROM OFFSET WHERE dctrn_rownum BETWEEN ' . $offset .
+               'OVER(ORDER BY ' . $col[1] . ') AS doctrine_rownum FROM ' . $table . ')' .
+               $select . 'FROM OFFSET WHERE doctrine_rownum BETWEEN ' . $offset .
                    'AND ' . ($offset + $limit - 1);
             return $sql;

branches/0.11/lib/Doctrine/Connection/Oracle.php

@@ -83 +83 @@
     public function modifyLimitQuery($query, $limit = false, $offset = false, $isManip = false)
     {
+        return $this->_createLimitSubquery($query, $limit, $offset);
+    }
+    
+    private function _createLimitSubquery($query, $limit, $offset, $column = null)
+    {
         $limit = (int) $limit;
         $offset = (int) $offset;
@@ -90 +95 @@
             }
             if ($limit > 0) {
-                // taken from http://svn.ez.no/svn/ezcomponents/packages/Database
                 $max = $offset + $limit;
+                $column = $column === null ? '*' : $column;
                 if ($offset > 0) {
                     $min = $offset + 1;
-                    $query = 'SELECT b.* FROM (
-                                 SELECT a.*, ROWNUM AS doctrine_rownum FROM ('
-                                  . $query . ') a
-                              ) b
-                              WHERE b.doctrine_rownum BETWEEN ' . $min .  ' AND ' . $max;
+                    $query = 'SELECT b.'.$column.' FROM ('.
+                                 'SELECT a.*, ROWNUM AS doctrine_rownum FROM ('
+                                   . $query . ') a '.
+                              ') b '.
+                              'WHERE doctrine_rownum BETWEEN ' . $min .  ' AND ' . $max;
                 } else {
-                    $query = 'SELECT a.* FROM (' . $query .') a WHERE ROWNUM <= ' . $max;
+                    $query = 'SELECT a.'.$column.' FROM (' . $query .') a WHERE ROWNUM <= ' . $max;
                 }
             }
@@ -106 +111 @@
         return $query;
     }
+    
+    /**
+     * Creates the SQL for Oracle that can be used in the subquery for the limit-subquery
+     * algorithm.
+     */
+    public function modifyLimitSubquery(Doctrine_Table $rootTable, $query, $limit = false,
+            $offset = false, $isManip = false)
+    {
+        // NOTE: no composite key support
+        $columnNames = $rootTable->getIdentifierColumnNames();
+        if (count($columnNames) > 1) {
+            throw new Doctrine_Connection_Exception("Composite keys in LIMIT queries are "
+                    . "currently not supported.");
+        }
+        $column = $columnNames[0];
+        return $this->_createLimitSubquery($query, $limit, $offset, $column);
+    }
 }

branches/0.11/lib/Doctrine/Query.php

@@ -1202 +1202 @@
         $modifyLimit = true;
         if ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) {
-
             if ($needsSubQuery) {
                 $subquery = $this->getLimitSubquery();
@@ -1210 +1209 @@
                     case 'mysql':
                         // mysql doesn't support LIMIT in subqueries
-                        $list     = $this->_conn->execute($subquery, $params)->fetchAll(Doctrine::FETCH_COLUMN);
+                        $list = $this->_conn->execute($subquery, $params)->fetchAll(Doctrine::FETCH_COLUMN);
                         $subquery = implode(', ', array_map(array($this->_conn, 'quote'), $list));
                         break;
@@ -1264 +1263 @@
     public function getLimitSubquery()
     {
-        $map    = reset($this->_queryComponents);
-        $table  = $map['table'];
+        $map = reset($this->_queryComponents);
+        $table = $map['table'];
         $componentAlias = key($this->_queryComponents);
 
         // get short alias
-        $alias      = $this->getTableAlias($componentAlias);
+        $alias = $this->getTableAlias($componentAlias);
         // what about composite keys?
         $primaryKey = $alias . '.' . $table->getColumnName($table->getIdentifier());
 
         // initialize the base of the subquery
-        $subquery   = 'SELECT DISTINCT ' . $this->_conn->quoteIdentifier($primaryKey);
+        $subquery = 'SELECT DISTINCT ' . $this->_conn->quoteIdentifier($primaryKey);
 
         $driverName = $this->_conn->getAttribute(Doctrine::ATTR_DRIVER_NAME);
@@ -1332 +1331 @@
 
         // add driver specific limit clause
-        $subquery = $this->_conn->modifyLimitQuery($subquery, $this->_sqlParts['limit'], $this->_sqlParts['offset']);
+        $subquery = $this->_conn->modifyLimitSubquery($table, $subquery, $this->_sqlParts['limit'], $this->_sqlParts['offset']);
 
         $parts = $this->_tokenizer->quoteExplode($subquery, ' ', "'", "'");

branches/0.11/tests/Query/DriverTestCase.php

@@ -100 +100 @@
         $q->from('User u')->limit(5)->offset(2);
 
-        $this->assertEqual($q->getSql(), 'SELECT * FROM (SELECT a.*, ROWNUM dctrn_rownum FROM (SELECT e.id AS e__id, e.name AS e__name, e.loginname AS e__loginname, e.password AS e__password, e.type AS e__type, e.created AS e__created, e.updated AS e__updated, e.email_id AS e__email_id FROM entity e WHERE (e.type = 0)) a WHERE ROWNUM <= 7) WHERE dctrn_rownum >= 3');
+        $this->assertEqual($q->getSql(), 'SELECT b.* FROM (SELECT a.*, ROWNUM AS doctrine_rownum FROM (SELECT e.id AS e__id, e.name AS e__name, e.loginname AS e__loginname, e.password AS e__password, e.type AS e__type, e.created AS e__created, e.updated AS e__updated, e.email_id AS e__email_id FROM entity e WHERE (e.type = 0)) a ) b WHERE doctrine_rownum BETWEEN 3 AND 7');
     }
+    
+    public function testLimitOffsetLimitSubqueriesForOracle()
+    {
+        $this->dbh = new Doctrine_Adapter_Mock('oracle');
+        $conn = $this->manager->openConnection($this->dbh);
+        $q = new Doctrine_Query($conn);
+        $q->from('User u')->innerJoin('u.Phonenumber p')->limit(5)->offset(2);
+        
+        $correctSql = "SELECT e.id AS e__id, e.name AS e__name, e.loginname AS e__loginname, "
+                            . "e.password AS e__password, e.type AS e__type, e.created AS e__created, "
+                            . "e.updated AS e__updated, e.email_id AS e__email_id, p.id AS p__id, "
+                            . "p.phonenumber AS p__phonenumber, p.entity_id AS p__entity_id "
+                            . "FROM entity e "
+                            . "INNER JOIN phonenumber p ON e.id = p.entity_id "
+                            . "WHERE e.id IN ("
+                                . "SELECT b.id FROM ("
+                                    . "SELECT a.*, ROWNUM AS doctrine_rownum "
+                                    . "FROM ("
+                                        . "SELECT DISTINCT e2.id "
+                                        . "FROM entity e2 "
+                                        . "INNER JOIN phonenumber p2 ON e2.id = p2.entity_id "
+                                        . "WHERE (e2.type = 0)"
+                                    . ") a"
+                                . " ) b "
+                                . "WHERE doctrine_rownum BETWEEN 3 AND 7"
+                            . ") AND (e.type = 0)";
+        
+        $this->assertEqual($q->getSql(), $correctSql);
+    }
+    
 }