From bb60bcd2032e887e010904999655d2e5999e9878 Mon Sep 17 00:00:00 2001 From: knut <> Date: Wed, 6 Aug 2008 13:33:38 +0000 Subject: fixed #899 --- .gitattributes | 2 + framework/Data/DataGateway/TSqlCriteria.php | 53 ++++++++--- tests/unit/AllTests.php | 2 + tests/unit/Data/DataGateway/AllTests.php | 28 ++++++ tests/unit/Data/DataGateway/TSqlCriteriaTest.php | 111 +++++++++++++++++++++++ 5 files changed, 185 insertions(+), 11 deletions(-) create mode 100644 tests/unit/Data/DataGateway/AllTests.php create mode 100644 tests/unit/Data/DataGateway/TSqlCriteriaTest.php diff --git a/.gitattributes b/.gitattributes index 403ae6b8..01b4064e 100644 --- a/.gitattributes +++ b/.gitattributes @@ -3422,6 +3422,8 @@ tests/unit/Collections/TPagedDataSourceTest.php -text tests/unit/Collections/TPagedListTest.php -text tests/unit/Collections/TQueueTest.php -text tests/unit/Collections/TStackTest.php -text +tests/unit/Data/DataGateway/AllTests.php -text +tests/unit/Data/DataGateway/TSqlCriteriaTest.php -text tests/unit/Data/TDbCommandTest.php -text tests/unit/Data/TDbConnectionTest.php -text tests/unit/Data/TDbDataReaderTest.php -text diff --git a/framework/Data/DataGateway/TSqlCriteria.php b/framework/Data/DataGateway/TSqlCriteria.php index 004f35d1..4ebdeb48 100644 --- a/framework/Data/DataGateway/TSqlCriteria.php +++ b/framework/Data/DataGateway/TSqlCriteria.php @@ -4,7 +4,7 @@ * * @author Wei Zhuo * @link http://www.pradosoft.com/ - * @copyright Copyright © 2005-2008 PradoSoft + * @copyright Copyright © 2005-2008 PradoSoft * @license http://www.pradosoft.com/license/ * @version $Id: TDbSqlCriteria.php 1835 2007-04-03 01:38:15Z wei $ * @package System.Data.DataGateway @@ -15,7 +15,7 @@ * * Criteria object for data gateway finder methods. Usage: * - * $criteria = new TDbSqlCriteria; + * $criteria = new TSqlCriteria(); * $criteria->Parameters[':name'] = 'admin'; * $criteria->Parameters[':pass'] = 'prado'; * $criteria->OrdersBy['level'] = 'desc'; @@ -68,15 +68,46 @@ class TSqlCriteria extends TComponent * @param string search conditions. */ public function setCondition($value) - { - if(!empty($value) && preg_match('/ORDER\s+BY\s+(.*?)$/i',$value,$matches)>0) - { - // condition contains ORDER BY, we need to strip it output - $this->_condition=substr($value,0,strpos($value,$matches[0])); - $this->setOrdersBy($matches[1]); + { + if(empty($value)) { + return; } - else - $this->_condition=$value; + + // supporting the following SELECT-syntax: + // [ORDER BY {col_name | expr | position} + // [ASC | DESC], ...] + // [LIMIT {[offset,] row_count | row_count OFFSET offset}] + // See: http://dev.mysql.com/doc/refman/5.0/en/select.html + + if(preg_match('/ORDER\s+BY\s+(.*?)(?=LIMIT)|ORDER\s+BY\s+(.*?)$/i', $value, $matches) > 0) { + // condition contains ORDER BY + $value = str_replace($matches[0], '', $value); + if(strlen($matches[1]) > 0) { + $this->setOrdersBy($matches[1]); + } else if(strlen($matches[2]) > 0) { + $this->setOrdersBy($matches[2]); + } + } + + if(preg_match('/LIMIT\s+([\d\s,]+)/i', $value, $matches) > 0) { + // condition contains limit + $value = str_replace($matches[0], '', $value); // remove limit from query + if(strpos($matches[1], ',')) { // both offset and limit given + list($offset, $limit) = explode(',', $matches[1]); + $this->_limit = (int)$limit; + $this->_offset = (int)$offset; + } else { // only limit given + $this->_limit = (int)$matches[1]; + } + } + + if(preg_match('/OFFSET\s+(\d+)/i', $value, $matches) > 0) { + // condition contains offset + $value = str_replace($matches[0], '', $value); // remove offset from query + $this->_offset = (int)$matches[1]; // set offset in criteria + } + + $this->_condition = trim($value); } /** @@ -192,4 +223,4 @@ class TSqlCriteria extends TComponent } } -?> +?> diff --git a/tests/unit/AllTests.php b/tests/unit/AllTests.php index bbd53a66..220b9366 100644 --- a/tests/unit/AllTests.php +++ b/tests/unit/AllTests.php @@ -15,6 +15,7 @@ require_once 'Web/UI/ActiveControls/AllTests.php'; require_once 'Security/AllTests.php'; require_once 'Caching/AllTests.php'; require_once 'Util/AllTests.php'; +require_once 'Data/DataGateway/AllTests.php'; require_once 'TComponentTest.php'; @@ -36,6 +37,7 @@ class AllTests { $suite->addTest(Security_AllTests::suite()); $suite->addTest(Caching_AllTests::suite()); $suite->addTest(Util_AllTests::suite()); + $suite->addTest(Data_DataGateway_AllTests::suite()); $suite->addTestSuite('TComponentTest'); diff --git a/tests/unit/Data/DataGateway/AllTests.php b/tests/unit/Data/DataGateway/AllTests.php new file mode 100644 index 00000000..f3405613 --- /dev/null +++ b/tests/unit/Data/DataGateway/AllTests.php @@ -0,0 +1,28 @@ +addTestSuite('TSqlCriteriaTest'); + + return $suite; + } +} + +if(PHPUnit_MAIN_METHOD == 'Data_DataGateway_AllTests::main') { + Data_DataGateway_AllTests::main(); +} +?> diff --git a/tests/unit/Data/DataGateway/TSqlCriteriaTest.php b/tests/unit/Data/DataGateway/TSqlCriteriaTest.php new file mode 100644 index 00000000..043520e7 --- /dev/null +++ b/tests/unit/Data/DataGateway/TSqlCriteriaTest.php @@ -0,0 +1,111 @@ +Condition = "SELECT * FROM table_references ORDER BY field1 ASC, field2 DESC"; + self::assertEquals("SELECT * FROM table_references", $criteria->Condition); + self::assertEquals(true, isset($criteria->OrdersBy['field1'])); + self::assertEquals('ASC', $criteria->OrdersBy['field1']); + self::assertEquals(true, isset($criteria->OrdersBy['field2'])); + self::assertEquals('DESC', $criteria->OrdersBy['field2']); + } + + public function testConditionWithOrderByExpression() { + $criteria = new TSqlCriteria(); + $criteria->Condition = "SELECT * FROM table_references ORDER BY RAND()"; + self::assertEquals("SELECT * FROM table_references", $criteria->Condition); + self::assertEquals(true, isset($criteria->OrdersBy['RAND()'])); + self::assertEquals('asc', $criteria->OrdersBy['RAND()']); + } + + public function testConditionWithOrderByAndLimit() { + $criteria = new TSqlCriteria(); + $criteria->Condition = "SELECT * FROM table_references ORDER BY field1 ASC, field2 DESC LIMIT 2"; + self::assertEquals("SELECT * FROM table_references", $criteria->Condition); + self::assertEquals(2, $criteria->Limit); + } + + public function testConditionWithOrderByAndLimitAndOffset() { + $criteria = new TSqlCriteria(); + $criteria->Condition = "SELECT * FROM table_references ORDER BY field1 ASC, field2 DESC LIMIT 3, 2"; + self::assertEquals("SELECT * FROM table_references", $criteria->Condition); + self::assertEquals(2, $criteria->Limit); + self::assertEquals(3, $criteria->Offset); + } + + public function testConditionWithOrderByAndLimitAndOffsetVariant() { + $criteria = new TSqlCriteria(); + $criteria->Condition = "SELECT * FROM table_references ORDER BY field1 ASC, field2 DESC LIMIT 2 OFFSET 3"; + self::assertEquals("SELECT * FROM table_references", $criteria->Condition); + self::assertEquals(2, $criteria->Limit); + self::assertEquals(3, $criteria->Offset); + } + + public function testConditionWithLimit() { + $criteria = new TSqlCriteria(); + $criteria->Condition = "SELECT * FROM table_references LIMIT 2"; + self::assertEquals("SELECT * FROM table_references", $criteria->Condition); + self::assertEquals(2, $criteria->Limit); + } + + public function testConditionWithLimitAndOffset() { + $criteria = new TSqlCriteria(); + $criteria->Condition = "SELECT * FROM table_references LIMIT 3, 2"; + self::assertEquals("SELECT * FROM table_references", $criteria->Condition); + self::assertEquals(2, $criteria->Limit); + self::assertEquals(3, $criteria->Offset); + } + + public function testConditionWithLimitAndOffsetVariant() { + $criteria = new TSqlCriteria(); + $criteria->Condition = "SELECT * FROM table_references LIMIT 2 OFFSET 3"; + self::assertEquals("SELECT * FROM table_references", $criteria->Condition); + self::assertEquals(2, $criteria->Limit); + self::assertEquals(3, $criteria->Offset); + } + + public function testParameters() { + throw new PHPUnit_Framework_IncompleteTestError(); + } + + public function testIsNamedParameters() { + throw new PHPUnit_Framework_IncompleteTestError(); + } + + public function testOrdersBy() { + throw new PHPUnit_Framework_IncompleteTestError(); + } + + public function testLimit() { + throw new PHPUnit_Framework_IncompleteTestError(); + } + + public function testOffset() { + throw new PHPUnit_Framework_IncompleteTestError(); + } + + public function testToString() { + throw new PHPUnit_Framework_IncompleteTestError(); + } + +} + +?> -- cgit v1.2.3