Doctrine

Timestamp:

07/10/09 13:53:48 (7 months ago)

Author:

jwage

Message:

[2.0] More work on the QueryBuilder? and Expr classes.

Location:

trunk

Files:

• lib/Doctrine/DBAL/Platforms/AbstractPlatform.php (modified) (1 diff)
• lib/Doctrine/DBAL/Platforms/MsSqlPlatform.php (modified) (1 diff)
• lib/Doctrine/DBAL/Platforms/OraclePlatform.php (modified) (1 diff)
• lib/Doctrine/ORM/Query/Expr.php (modified) (2 diffs)
• lib/Doctrine/ORM/Query/Expr/Andx.php (modified) (1 diff)
• lib/Doctrine/ORM/Query/Expr/Base.php (modified) (2 diffs)
• lib/Doctrine/ORM/Query/Expr/Comparison.php (modified) (1 diff)
• lib/Doctrine/ORM/Query/Expr/CountDistinctFunction.php (deleted)
• lib/Doctrine/ORM/Query/Expr/Func.php (modified) (1 diff)
• lib/Doctrine/ORM/Query/Expr/GroupBy.php (added)
• lib/Doctrine/ORM/Query/Expr/Math.php (modified) (1 diff)
• lib/Doctrine/ORM/Query/Expr/OrderBy.php (added)
• lib/Doctrine/ORM/Query/Expr/Orx.php (modified) (1 diff)
• lib/Doctrine/ORM/Query/Expr/Select.php (modified) (2 diffs)
• lib/Doctrine/ORM/Query/Expr/SelectField.php (deleted)
• lib/Doctrine/ORM/Query/SqlWalker.php (modified) (1 diff)
• lib/Doctrine/ORM/QueryBuilder.php (modified) (11 diffs)
• tests/Doctrine/Tests/ORM/Functional/AllTests.php (modified) (1 diff)
• tests/Doctrine/Tests/ORM/Functional/QueryBuilderTest.php (added)
• tests/Doctrine/Tests/ORM/Query/ExprTest.php (modified) (1 diff)
• tests/Doctrine/Tests/ORM/QueryBuilderTest.php (modified) (4 diffs)

trunk/lib/Doctrine/DBAL/Platforms/AbstractPlatform.php

@@ -1539 +1539 @@
     }
 
+    public function modifyLimitQuery($query, $max, $first)
+    {
+        if ( ! is_null($first)) {
+            $query .= ' OFFSET ' . $first;
+        }
+
+        if ( ! is_null($max)) {
+            $query .= ' LIMIT ' . $max;
+        }
+
+        return $query;
+    }
+
     /**
      * Gets the SQL snippet used to declare a VARCHAR column type.

trunk/lib/Doctrine/DBAL/Platforms/MsSqlPlatform.php

@@ -426 +426 @@
         return 'mssql';
     }
+
+    /**
+     * Adds an adapter-specific LIMIT clause to the SELECT statement.
+     *
+     * @param string $query
+     * @param mixed $limit
+     * @param mixed $offset
+     * @link http://lists.bestpractical.com/pipermail/rt-devel/2005-June/007339.html
+     * @return string
+     */
+    public function modifyLimitQuery($query, $limit = false, $offset = false, $isManip = false)
+    {
+        if ($limit > 0) {
+            $count = intval($limit);
+            $offset = intval($offset);
+
+            if ($offset < 0) {
+                throw new Doctrine_Connection_Exception("LIMIT argument offset=$offset is not valid");
+            }
+
+            $orderby = stristr($query, 'ORDER BY');
+
+            if ($orderby !== false) {
+                // Ticket #1835: Fix for ORDER BY alias
+                // Ticket #2050: Fix for multiple ORDER BY clause
+                $order = str_ireplace('ORDER BY', '', $orderby);
+                $orders = explode(',', $order);
+
+                for ($i = 0; $i < count($orders); $i++) {
+                    $sorts[$i] = (stripos($orders[$i], ' desc') !== false) ? 'desc' : 'asc';
+                    $orders[$i] = trim(preg_replace('/\s+(ASC|DESC)$/i', '', $orders[$i]));
+
+                    // find alias in query string
+                    $helper_string = stristr($query, $orders[$i]);
+
+                    $from_clause_pos = strpos($helper_string, ' FROM ');
+                    $fields_string = substr($helper_string, 0, $from_clause_pos + 1);
+
+                    $field_array = explode(',', $fields_string);
+                    $field_array = array_shift($field_array);
+                    $aux2 = spliti(' as ', $field_array);
+
+                    $aliases[$i] = trim(end($aux2));
+                }
+            }
+
+            // Ticket #1259: Fix for limit-subquery in MSSQL
+            $selectRegExp = 'SELECT\s+';
+            $selectReplace = 'SELECT ';
+
+            if (preg_match('/^SELECT(\s+)DISTINCT/i', $query)) {
+                $selectRegExp .= 'DISTINCT\s+';
+                $selectReplace .= 'DISTINCT ';
+            }
+
+            $query = preg_replace('/^'.$selectRegExp.'/i', $selectReplace . 'TOP ' . ($count + $offset) . ' ', $query);
+            $query = 'SELECT * FROM (SELECT TOP ' . $count . ' * FROM (' . $query . ') AS ' . $this->quoteIdentifier('inner_tbl');
+
+            if ($orderby !== false) {
+                $query .= ' ORDER BY '; 
+
+                for ($i = 0, $l = count($orders); $i < $l; $i++) { 
+                    if ($i > 0) { // not first order clause 
+                        $query .= ', '; 
+                    } 
+
+                    $query .= $this->quoteIdentifier('inner_tbl') . '.' . $aliases[$i] . ' '; 
+                    $query .= (stripos($sorts[$i], 'asc') !== false) ? 'DESC' : 'ASC';
+                }
+            }
+
+            $query .= ') AS ' . $this->quoteIdentifier('outer_tbl');
+
+            if ($orderby !== false) {
+                $query .= ' ORDER BY '; 
+
+                for ($i = 0, $l = count($orders); $i < $l; $i++) { 
+                    if ($i > 0) { // not first order clause 
+                        $query .= ', '; 
+                    } 
+
+                    $query .= $this->quoteIdentifier('outer_tbl') . '.' . $aliases[$i] . ' ' . $sorts[$i];
+                }
+            }
+        }
+
+        return $query;
+    }
 }

trunk/lib/Doctrine/DBAL/Platforms/OraclePlatform.php

@@ -470 +470 @@
         return 'oracle';
     }
+
+    /**
+     * Adds an driver-specific LIMIT clause to the query
+     *
+     * @param string $query         query to modify
+     * @param integer $limit        limit the number of rows
+     * @param integer $offset       start reading from given offset
+     * @return string               the modified query
+     */
+    public function modifyLimitQuery($query, $limit = false, $offset = false, $isManip = false)
+    {
+        $limit = (int) $limit;
+        $offset = (int) $offset;
+        if (preg_match('/^\s*SELECT/i', $query)) {
+            if ( ! preg_match('/\sFROM\s/i', $query)) {
+                $query .= " FROM dual";
+            }
+            if ($limit > 0) {
+                $max = $offset + $limit;
+                $column = $column === null ? '*' : $this->quoteIdentifier($column);
+                if ($offset > 0) {
+                    $min = $offset + 1;
+                    $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.'.$column.' FROM (' . $query .') a WHERE ROWNUM <= ' . $max;
+                }
+            }
+        }
+        return $query;
+    }
 }

trunk/lib/Doctrine/ORM/Query/Expr.php

@@ -34 +34 @@
 class Expr
 {
-    public static function andx()
+    public static function andx($x = null)
     {
         return new Expr\Andx(func_get_args());
     }
 
-    public static function orx()
+    public static function orx($x = null)
     {
         return new Expr\Orx(func_get_args());
     }
 
-    public static function select()
+    public static function select($select = null)
     {
         return new Expr\Select(func_get_args());
     }
 
-    public static function selectField($field, $alias = null)
-    {
-        return new Expr\SelectField($field, $alias);
+    public static function orderBy($sort = null, $order = null)
+    {
+        return new Expr\OrderBy($sort, $order);
+    }
+
+    public static function groupBy($groupBy = null)
+    {
+        return new Expr\GroupBy(func_get_args());
+    }
+
+    public static function having($having = null)
+    {
+        return new Expr\Having(func_get_args());
     }
 
@@ -106 +116 @@
     public static function countDistinct()
     {
-        return new Expr\CountDistinctFunction(func_get_args());
+        return 'COUNT(DISTINCT ' . implode(', ', func_get_args()) . ')';
     }
 

trunk/lib/Doctrine/ORM/Query/Expr/Andx.php

@@ -23 +23 @@
 
 /**
- * Expression class for building and clauses
+ * Expression class for building DQL and parts
  *
  * @author      Jonathan H. Wage <jonwage@gmail.com>

trunk/lib/Doctrine/ORM/Query/Expr/Base.php

@@ -23 +23 @@
 
 /**
- * Abstract class for building DQL expressions
+ * Abstract base Expr class for building DQL parts
  *
  * @author      Jonathan H. Wage <jonwage@gmail.com>
@@ -69 +69 @@
     }
 
-    public function __tostring()
+    public function __toString()
     {
         return $this->_preSeparator . implode($this->_separator, $this->_parts) . $this->_postSeparator;

trunk/lib/Doctrine/ORM/Query/Expr/Comparison.php

@@ -23 +23 @@
 
 /**
- * Expression class for comparison statements
+ * Expression class for DQL comparison expressions
  *
  * @author      Jonathan H. Wage <jonwage@gmail.com>

trunk/lib/Doctrine/ORM/Query/Expr/Func.php

@@ -23 +23 @@
 
 /**
- * Expression class for building comparison clauses
+ * Expression class for generating DQL functions
  *
  * @author      Jonathan H. Wage <jonwage@gmail.com>

trunk/lib/Doctrine/ORM/Query/Expr/Math.php

@@ -23 +23 @@
 
 /**
- * Expression class for math statements
+ * Expression class for DQL math statements
  *
  * @author      Jonathan H. Wage <jonwage@gmail.com>

trunk/lib/Doctrine/ORM/Query/Expr/Orx.php

@@ -23 +23 @@
 
 /**
- * Expression class for building and clauses
+ * Expression class for building DQL OR clauses
  *
  * @author      Jonathan H. Wage <jonwage@gmail.com>

trunk/lib/Doctrine/ORM/Query/Expr/Select.php

@@ -23 +23 @@
 
 /**
- * Expression class for building DQL select clauses
+ * Expression class for building DQL select statements
  *
  * @author      Jonathan H. Wage <jonwage@gmail.com>
@@ -36 +36 @@
     protected $_preSeparator = '';
     protected $_postSeparator = '';
-    protected $_allowedClasses = array(
-        'Doctrine\ORM\Query\Expr\SelectField'
-    );
 }

trunk/lib/Doctrine/ORM/Query/SqlWalker.php

@@ -135 +135 @@
         $sql .= $AST->getOrderByClause() ? $this->walkOrderByClause($AST->getOrderByClause()) : '';
 
+        $q = $this->getQuery();
+        $sql = $this->getConnection()->getDatabasePlatform()
+            ->modifyLimitQuery($sql, $q->getMaxResults(), $q->getFirstResult());
+
         return $sql;
     }

trunk/lib/Doctrine/ORM/QueryBuilder.php

@@ -50 +50 @@
      * @var EntityManager $em Instance of an EntityManager to use for query
      */
-    protected $_em;
+    private $_em;
 
     /**
      * @var array $dqlParts The array of DQL parts collected
      */
-    protected $_dqlParts = array(
+    private $_dqlParts = array(
         'select' => array(),
         'from' => array(),
@@ -61 +61 @@
         'groupBy' => array(),
         'having' => array(),
-        'orderBy' => array(),
-        'limit' => array(), 
-        'offset' => array()
+        'orderBy' => array()
     );
 
@@ -69 +67 @@
      * @var integer $type  The type of query this is. Can be select, update or delete
      */
-    protected $_type = self::SELECT;
+    private $_type = self::SELECT;
 
     /**
      * @var integer $state The state of the query object. Can be dirty or clean.
      */
-    protected $_state = self::STATE_CLEAN;
+    private $_state = self::STATE_CLEAN;
 
     /**
      * @var string $dql The complete DQL string for this query
      */
-    protected $_dql;
+    private $_dql;
 
     /**
      * @var array $params Parameters of this query.
      */
-    protected $_params = array();
+    private $_params = array();
+
+    /**
+     * @var integer The first result to return (the "offset").
+     */
+    private $_firstResult = null;
+    
+    /**
+     * @var integer The maximum number of results to return (the "limit").
+     */
+    private $_maxResults = null;
 
     public function __construct(EntityManager $entityManager)
@@ -144 +152 @@
         $q->setDql($this->getDql());
         $q->setParameters($this->getParameters());
+        $q->setFirstResult($this->getFirstResult());
+        $q->setMaxResults($this->getMaxResults());
 
         return $q;
@@ -225 +235 @@
     }
 
-    public function select($select)
+    public function select($select = null)
     {
         $selects = func_get_args();
@@ -350 +360 @@
     public function groupBy($groupBy)
     {
-        return $this->add('groupBy', $groupBy, false);
+        return $this->add('groupBy', Expr::groupBy($groupBy), false);
+    }
+
+    public function addGroupBy($groupBy)
+    {
+        return $this->add('groupBy', Expr::groupBy($groupBy), true);
     }
 
     public function having($having)
     {
-        return $this->add('having', $having, false);
+        return $this->add('having', Expr::having($having), false);
     }
 
@@ -364 +379 @@
         }
 
-        return $this->add('having', $having, true);
+        return $this->add('having', Expr::having($having), true);
     }
 
@@ -373 +388 @@
         }
 
-        return $this->add('having', $having, true);
+        return $this->add('having', Expr::having($having), true);
     }
 
     public function orderBy($sort, $order)
     {
-        return $this->add('orderBy', $sort . ' ' . $order, false);
+        return $this->add('orderBy', Expr::orderBy($sort, $order), false);
     }
 
     public function addOrderBy($sort, $order)
     {
-        return $this->add('orderBy', $sort . ' ' . $order, true);
-    }
-
-    public function limit($limit)
-    {
-        return $this->add('limit', $limit);
-    }
-
-    public function offset($offset)
-    {
-        return $this->add('offset', $offset);
+        return $this->add('orderBy', Expr::orderBy($sort, $order), true);
+    }
+
+    /**
+     * Sets the position of the first result to retrieve (the "offset").
+     *
+     * @param integer $firstResult The first result to return.
+     * @return Query This query object.
+     */
+    public function setFirstResult($firstResult)
+    {
+        $this->_firstResult = $firstResult;
+        return $this;
+    }
+    
+    /**
+     * Gets the position of the first result the query object was set to retrieve (the "offset").
+     * Returns NULL if {@link setFirstResult} was not applied to this query.
+     * 
+     * @return integer The position of the first result.
+     */
+    public function getFirstResult()
+    {
+        return $this->_firstResult;
+    }
+
+    /**
+     * Sets the maximum number of results to retrieve (the "limit").
+     * 
+     * @param integer $maxResults
+     * @return Query This query object.
+     */
+    public function setMaxResults($maxResults)
+    {
+        $this->_maxResults = $maxResults;
+        return $this;
+    }
+    
+    /**
+     * Gets the maximum number of results the query object was set to retrieve (the "limit").
+     * Returns NULL if {@link setMaxResults} was not applied to this query.
+     * 
+     * @return integer Maximum number of results.
+     */
+    public function getMaxResults()
+    {
+        return $this->_maxResults;
     }
 
@@ -415 +466 @@
               . $this->_getReducedDqlQueryPart('from', array('pre' => ' ', 'separator' => ' '))
               . $this->_getReducedDqlQueryPart('where', array('pre' => ' WHERE ', 'separator' => ' '))
-              . $this->_getReducedDqlQueryPart('orderBy', array('pre' => ' ORDER BY ', 'separator' => ', '))
-              . $this->_getReducedDqlQueryPart('limit', array('pre' => ' LIMIT ', 'separator' => ' '))
-              . $this->_getReducedDqlQueryPart('offset', array('pre' => ' OFFSET ', 'separator' => ' '));
+              . $this->_getReducedDqlQueryPart('orderBy', array('pre' => ' ORDER BY ', 'separator' => ', '));
     }
 
@@ -440 +489 @@
               . $this->_getReducedDqlQueryPart('set', array('pre' => ' SET ', 'separator' => ', '))
               . $this->_getReducedDqlQueryPart('where', array('pre' => ' WHERE ', 'separator' => ' '))
-              . $this->_getReducedDqlQueryPart('orderBy', array('pre' => ' ORDER BY ', 'separator' => ', '))
-              . $this->_getReducedDqlQueryPart('limit', array('pre' => ' LIMIT ', 'separator' => ' '))
-              . $this->_getReducedDqlQueryPart('offset', array('pre' => ' OFFSET ', 'separator' => ' '));
+              . $this->_getReducedDqlQueryPart('orderBy', array('pre' => ' ORDER BY ', 'separator' => ', '));
     }
 
@@ -470 +517 @@
               . $this->_getReducedDqlQueryPart('groupBy', array('pre' => ' GROUP BY ', 'separator' => ', '))
               . $this->_getReducedDqlQueryPart('having', array('pre' => ' HAVING ', 'separator' => ' '))
-              . $this->_getReducedDqlQueryPart('orderBy', array('pre' => ' ORDER BY ', 'separator' => ', '))
-              . $this->_getReducedDqlQueryPart('limit', array('pre' => ' LIMIT ', 'separator' => ' '))
-              . $this->_getReducedDqlQueryPart('offset', array('pre' => ' OFFSET ', 'separator' => ' '));
+              . $this->_getReducedDqlQueryPart('orderBy', array('pre' => ' ORDER BY ', 'separator' => ', '));
     }
 

trunk/tests/Doctrine/Tests/ORM/Functional/AllTests.php

@@ -35 +35 @@
         $suite->addTestSuite('Doctrine\Tests\ORM\Functional\OneToManySelfReferentialAssociationTest');
         $suite->addTestSuite('Doctrine\Tests\ORM\Functional\ManyToManySelfReferentialAssociationTest');
+        $suite->addTestSuite('Doctrine\Tests\ORM\Functional\QueryBuilderTest');
 
         return $suite;

trunk/tests/Doctrine/Tests/ORM/Query/ExprTest.php

@@ -246 +246 @@
     {
         $selectExpr = Expr::select();
-        $selectExpr->add(Expr::selectField('u.id'));
-        $selectExpr->add(Expr::selectField('u.username', 'my_test'));
-
-        $this->assertEquals('u.id, u.username AS my_test', (string) $selectExpr);
+        $selectExpr->add('u.id');
+        $selectExpr->add('u.username');
+
+        $this->assertEquals('u.id, u.username', (string) $selectExpr);
+    }
+
+    public function testExprBaseCount()
+    {
+        $selectExpr = Expr::select();
+        $selectExpr->add('u.id');
+        $selectExpr->add('u.username');
+
+        $this->assertEquals($selectExpr->count(), 2);
+    }
+
+    public function testOrderByCountExpr()
+    {
+        $orderByExpr = Expr::orderBy();
+        $orderByExpr->add('u.username', 'DESC');
+
+        $this->assertEquals($orderByExpr->count(), 1);
+        $this->assertEquals('u.username DESC', (string) $orderByExpr);
+    }
+
+    public function testOrderByOrder()
+    {
+        $orderByExpr = Expr::orderBy('u.username', 'DESC');
+        $this->assertEquals('u.username DESC', (string) $orderByExpr);
+    }
+
+    public function testOrderByDefaultOrderIsAsc()
+    {
+        $orderByExpr = Expr::orderBy('u.username');
+        $this->assertEquals('u.username ASC', (string) $orderByExpr);
+    }
+
+    /**
+     * @expectedException Doctrine\Common\DoctrineException
+     */
+    public function testAddThrowsException()
+    {
+        $orExpr = Expr::orx();
+        $orExpr->add(Expr::quot(5, 2));
     }
 }

trunk/tests/Doctrine/Tests/ORM/QueryBuilderTest.php

@@ -66 +66 @@
     }
 
+    public function testEmptySelectSetsType()
+    {
+        $qb = QueryBuilder::create($this->_em)
+            ->delete('Doctrine\Tests\Models\CMS\CmsUser', 'u')
+            ->select();
+
+        $this->assertEquals($qb->getType(), QueryBuilder::SELECT);
+    }
+
     public function testDeleteSetsType()
     {
@@ -211 +220 @@
             ->select('u')
             ->from('Doctrine\Tests\Models\CMS\CmsUser', 'u')
-            ->groupBy('u.id');
-
-        $this->assertValidQueryBuilder($qb, 'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u GROUP BY u.id');
+            ->groupBy('u.id')
+            ->addGroupBy('u.username');
+
+        $this->assertValidQueryBuilder($qb, 'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u GROUP BY u.id, u.username');
     }
 
@@ -310 +320 @@
     }
 
+
+    public function testGetParameters()
+    {
+        $qb = QueryBuilder::create($this->_em)
+            ->select('u')
+            ->from('Doctrine\Tests\Models\CMS\CmsUser', 'u')
+            ->where('u.id = :id');
+
+        $qb->setParameters(array('id' => 1));
+        $this->assertEquals(array('id' => 1, 'test' => 1), $qb->getParameters(array('test' => 1)));
+    }
+
+    public function testGetParameter()
+    {
+        $qb = QueryBuilder::create($this->_em)
+            ->select('u')
+            ->from('Doctrine\Tests\Models\CMS\CmsUser', 'u')
+            ->where('u.id = :id');
+
+        $qb->setParameters(array('id' => 1));
+        $this->assertEquals(1, $qb->getParameter('id'));
+    }
+
     public function testMultipleWhere()
     {
@@ -354 +387 @@
     }
 
-    public function testLimit()
-    {
-        /*
-        TODO: Limit fails. Is this not implemented in the DQL parser? Will look tomorrow.
-        $qb = QueryBuilder::create($this->_em)
-            ->select('u')
-            ->from('Doctrine\Tests\Models\CMS\CmsUser', 'u')
-            ->limit(10)
-            ->offset(0);
-
-        $this->assertValidQueryBuilder($qb, 'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u LIMIT 10');
-        */
+    public function testGetEntityManager()
+    {
+        $qb = QueryBuilder::create($this->_em);
+        $this->assertEquals($this->_em, $qb->getEntityManager());
+    }
+
+    public function testInitialStateIsClean()
+    {
+        $qb = QueryBuilder::create($this->_em);
+        $this->assertEquals(QueryBuilder::STATE_CLEAN, $qb->getState());
+    }
+
+    public function testAlteringQueryChangesStateToDirty()
+    {
+        $qb = QueryBuilder::create($this->_em)
+            ->select('u')
+            ->from('Doctrine\Tests\Models\CMS\CmsUser', 'u');
+
+        $this->assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState());
     }
 }