fixed #899

5 files changed, 185 insertions, 11 deletions

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 <weizhuo[at]gmail[dot]com>

  * @link http://www.pradosoft.com/

- * @copyright Copyright &copy; 2005-2008 PradoSoft
+ * @copyright Copyright &copy; 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:

  * <code>

- * $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 @@
+<?php
+require_once dirname(__FILE__).'/../../phpunit.php';
+
+if(!defined('PHPUnit_MAIN_METHOD')) {
+	define('PHPUnit_MAIN_METHOD', 'Data_DataGateway_AllTests::main');
+}
+
+require_once 'TSqlCriteriaTest.php';
+
+class Data_DataGateway_AllTests {
+
+	public static function main() {
+		PHPUnit_TextUI_TestRunner::run(self::suite());
+	}
+  
+	public static function suite() {
+		$suite = new PHPUnit_Framework_TestSuite('System.Data.DataGateway');
+    
+		$suite->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 @@
+<?php
+require_once dirname(__FILE__).'/../../phpunit.php';
+
+Prado::using('System.Data.DataGateway.TSqlCriteria');
+
+/**
+ * @package System.Data.DataGateway
+ */
+class TSqlCriteriaTest extends PHPUnit_Framework_TestCase {
+
+	public function setUp() {
+	}
+
+	public function tearDown() {
+	}
+
+	public function testConstruct() {
+		throw new PHPUnit_Framework_IncompleteTestError();
+	}
+	
+	public function testConditionWithOrderByColumnNames() {
+		$criteria = new TSqlCriteria();
+		$criteria->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();
+	}
+
+}
+
+?>