Fixed Issue #88 - SQLMap $Param$ re-evaluation bug

10 files changed, 219 insertions, 16 deletions

diff --git a/.gitattributes b/.gitattributes
index 9a8c88c2..223a642b 100644
--- a/.gitattributes
+++ b/.gitattributes
@@ -3456,6 +3456,11 @@ 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/SqlMap/AllTests.php -text
+tests/unit/Data/SqlMap/DynamicParameterTest.php -text
+tests/unit/Data/SqlMap/DynamicParameterTestMap.xml -text
+tests/unit/Data/SqlMap/fixtures/mysql5_reset.sql -text
+tests/unit/Data/SqlMap/fixtures/mysql5_setup.sql -text
 tests/unit/Data/TDbCommandTest.php -text
 tests/unit/Data/TDbConnectionTest.php -text
 tests/unit/Data/TDbDataReaderTest.php -text
diff --git a/HISTORY b/HISTORY
index c2647dce..360f7a89 100644
--- a/HISTORY
+++ b/HISTORY
@@ -1,5 +1,6 @@
 Version 3.1.5 (to be released)
 BUG: URL wildcard patterns didn't work with subfolders
+BUG: Issue#88 - SQLMap $Param$ re-evaluation bug (Yves)
 BUG: Issue#120 - TActiveDropDownList PromptText and PromptValue got lost during callback and data rebind (Yves)
 BUG: Issue#68 - TSqlMapConfig::createSqlMapGateway(): assign current connection to cached TSqlMapManager to avoid loosing active transaction (Yves)
 BUG: Issue#108 - clientscripts.php: prepending set_time_limit(0) call with an "@" to suppress PHP warning in safe mode (Yves)
@@ -17,7 +18,7 @@ ENH: Added caching of message files to TException (Michael)
 ENH: Updated to scriptaculous 1.8.2 & Prototype 1.6.0.3
 ENH: replace is_null() function calls with native language constuct (Yves)
 ENH: replace array_push() function calls with native language constuct if make sense (Yves)
-ENH: TBrowserLogRoute: add table-layout:auto and explicit set column with to avoid bad render behavior in table-layout:fixed environment, explicit set text color to increase readability
+ENH: TBrowserLogRoute: add table-layout:auto and explicit set column with to avoid bad render behavior in table-layout:fixed environment, explicit set text color to increase readability (Yves)
 
 Version 3.1.4 January 11, 2009
 BUG: Issue#9  - Global page properties are ignored when using external configuration (Christophe)
diff --git a/framework/Data/SqlMap/Statements/TPreparedCommand.php b/framework/Data/SqlMap/Statements/TPreparedCommand.php
index 99bb6eff..66ff2a70 100644
--- a/framework/Data/SqlMap/Statements/TPreparedCommand.php
+++ b/framework/Data/SqlMap/Statements/TPreparedCommand.php
@@ -25,9 +25,15 @@ class TPreparedCommand
 {

 	public function create(TSqlMapManager $manager, $connection, $statement, $parameterObject,$skip=null,$max=null)

 	{

-		$prepared = $statement->getSQLText()->getPreparedStatement($parameterObject);

+		$sqlText = $statement->getSQLText();

+

+		$prepared = $sqlText->getPreparedStatement($parameterObject);

 		$connection->setActive(true);

 		$sql = $prepared->getPreparedSql();

+

+		if($sqlText instanceof TSimpleDynamicSql)

+			$sql = $sqlText->replaceDynamicParameter($sql, $parameterObject);

+

 		if($max!==null || $skip!==null)

 		{

 			$builder = TDbMetaData::getInstance($connection)->createCommandBuilder();

@@ -35,6 +41,7 @@ class TPreparedCommand
 		}

 		$command = $connection->createCommand($sql);

 		$this->applyParameterMap($manager, $command, $prepared, $statement, $parameterObject);

+

 		return $command;

 	}

 

diff --git a/framework/Data/SqlMap/Statements/TSimpleDynamicSql.php b/framework/Data/SqlMap/Statements/TSimpleDynamicSql.php
index 910fd659..3e8969ba 100644
--- a/framework/Data/SqlMap/Statements/TSimpleDynamicSql.php
+++ b/framework/Data/SqlMap/Statements/TSimpleDynamicSql.php
@@ -27,23 +27,15 @@ class TSimpleDynamicSql extends TStaticSql
 		$this->_mappings = $mappings;

 	}

 

-	public function getPreparedStatement($parameter=null)

+	public function replaceDynamicParameter($sql, $parameter)

 	{

-		$statement = parent::getPreparedStatement($parameter);

-		if($parameter !== null)

-			$this->mapDynamicParameter($statement, $parameter);

-		return $statement;

-	}

-

-	protected function mapDynamicParameter($statement, $parameter)

-	{

-		$sql = $statement->getPreparedSql();

 		foreach($this->_mappings as $property)

 		{

 			$value = TPropertyAccess::get($parameter, $property);

 			$sql = preg_replace('/'.TSimpleDynamicParser::DYNAMIC_TOKEN.'/', $value, $sql, 1);

 		}

-		$statement->setPreparedSql($sql);

+

+		return $sql;

 	}

 }

 

diff --git a/tests/unit/AllTests.php b/tests/unit/AllTests.php
index 220b9366..62e127c8 100644
--- a/tests/unit/AllTests.php
+++ b/tests/unit/AllTests.php
@@ -16,6 +16,8 @@ require_once 'Security/AllTests.php';
 require_once 'Caching/AllTests.php';
 require_once 'Util/AllTests.php';
 require_once 'Data/DataGateway/AllTests.php';
+require_once 'Data/SqlMap/AllTests.php';
+
 
 require_once 'TComponentTest.php';
 
@@ -23,10 +25,10 @@ class AllTests {
   public static function main() {
     PHPUnit_TextUI_TestRunner::run(self::suite());
   }
-  
+
   public static function suite() {
     $suite = new PHPUnit_Framework_TestSuite('PRADO PHP Framework');
-    
+
 	$suite->addTest(Xml_AllTests::suite());
 	$suite->addTest(IO_AllTests::suite());
 	$suite->addTest(Collections_AllTests::suite());
@@ -38,7 +40,8 @@ class AllTests {
 	$suite->addTest(Caching_AllTests::suite());
 	$suite->addTest(Util_AllTests::suite());
 	$suite->addTest(Data_DataGateway_AllTests::suite());
-	
+	$suite->addTest(Data_SqlMap_AllTests::suite());
+
 	$suite->addTestSuite('TComponentTest');
 
     return $suite;
diff --git a/tests/unit/Data/SqlMap/AllTests.php b/tests/unit/Data/SqlMap/AllTests.php
new file mode 100644
index 00000000..187be17f
--- /dev/null
+++ b/tests/unit/Data/SqlMap/AllTests.php
@@ -0,0 +1,28 @@
+<?php

+require_once dirname(__FILE__).'/../../phpunit.php';

+

+if(!defined('PHPUnit_MAIN_METHOD')) {

+	define('PHPUnit_MAIN_METHOD', 'Data_SqlMap_AllTests::main');

+}

+

+require_once 'DynamicParameterTest.php';

+

+class Data_SqlMap_AllTests {

+

+	public static function main() {

+		PHPUnit_TextUI_TestRunner::run(self::suite());

+	}

+

+	public static function suite() {

+		$suite = new PHPUnit_Framework_TestSuite('System.Data.SqlMap');

+

+		$suite->addTestSuite('DynamicParameterTest');

+

+		return $suite;

+	}

+}

+

+if(PHPUnit_MAIN_METHOD == 'Data_SqlMap_AllTests::main') {

+	Data_SqlMap_AllTests::main();

+}

+?>
\ No newline at end of file
diff --git a/tests/unit/Data/SqlMap/DynamicParameterTest.php b/tests/unit/Data/SqlMap/DynamicParameterTest.php
new file mode 100644
index 00000000..ec37f4e0
--- /dev/null
+++ b/tests/unit/Data/SqlMap/DynamicParameterTest.php
@@ -0,0 +1,90 @@
+<?php

+require_once dirname(__FILE__).'/../../phpunit.php';

+

+Prado::using('System.Data.*');

+Prado::using('System.Data.SqlMap.*');

+

+/**

+ * @package System.Data.SqlMap

+ */

+class DynamicParameterTest extends PHPUnit_Framework_TestCase

+{

+

+	protected function getMysqlSqlMapManager()

+	{

+		static $conn;

+		static $sqlMapManager;

+

+		if($conn === null)

+			$conn = new TDbConnection('mysql:host=localhost;dbname=prado_system_data_sqlmap', 'prado_unitest', 'prado_system_data_sqlmap_unitest');

+

+		$conn->setActive(true);

+

+		if($sqlMapManager === null)

+		{

+			$sqlMapManager = new TSqlMapManager($conn);

+			$sqlMapManager->configureXml( dirname(__FILE__) . '/DynamicParameterTestMap.xml');

+		}

+

+		return $sqlMapManager;

+	}

+

+	public function testMysqlSelectStaticSql()

+	{

+		$mapper = $this->getMysqlSqlMapManager();

+		$gateway = $mapper->getSqlmapGateway();

+

+		$value = $gateway->queryForObject('SelectStaticSql1');

+		self::assertEquals('staticsql1', $value);

+

+		$value = $gateway->queryForObject('SelectStaticSql2');

+		self::assertEquals('staticsql2', $value);

+	}

+

+	public function testMysqlSelectDynamicTable()

+	{

+		$mapper = $this->getMysqlSqlMapManager();

+		$gateway = $mapper->getSqlmapGateway();

+

+		$value = $gateway->queryForObject('SelectDynamicTable', 'dynamicparametertest1');

+		self::assertEquals('dynamictableparametertest1', $value);

+

+		$value = $gateway->queryForObject('SelectDynamicTable', 'dynamicparametertest2');

+		self::assertEquals('dynamictableparametertest2', $value);

+	}

+

+	public function testMysqlSelectDynamicComplex()

+	{

+		$mapper = $this->getMysqlSqlMapManager();

+		$gateway = $mapper->getSqlmapGateway();

+

+		$aParams = array(

+			'tablename' => 'dynamicparametertest1',

+			'testname'	=> 'dynamictable'

+		);

+		$value = $gateway->queryForObject('SelectDynamicComplex', $aParams);

+		self::assertEquals('#dynamictableparametertest1$', $value);

+

+		$aParams = array(

+			'tablename' => 'dynamicparametertest2',

+			'testname'	=> 'dynamictable'

+		);

+		$value = $gateway->queryForObject('SelectDynamicComplex', $aParams);

+		self::assertEquals('#dynamictableparametertest2$', $value);

+	}

+

+	public function testMysqlSelectNoDynamic()

+	{

+		$mapper = $this->getMysqlSqlMapManager();

+		$gateway = $mapper->getSqlmapGateway();

+

+		$value = $gateway->queryForObject('SelectNoDynamic', 'dynamictable');

+		self::assertEquals('dynamictableparametertest1', $value);

+

+		$value = $gateway->queryForObject('SelectNoDynamic', 'staticsql');

+		self::assertEquals('staticsql1', $value);

+	}

+

+}

+

+?>
\ No newline at end of file
diff --git a/tests/unit/Data/SqlMap/DynamicParameterTestMap.xml b/tests/unit/Data/SqlMap/DynamicParameterTestMap.xml
new file mode 100644
index 00000000..65a91154
--- /dev/null
+++ b/tests/unit/Data/SqlMap/DynamicParameterTestMap.xml
@@ -0,0 +1,33 @@
+<?xml version="1.0" encoding="UTF-8" ?>

+<sqlMap namespace="DynamicParameterTestMap">

+	<select id="SelectStaticSql1" resultClass="string">

+	<![CDATA[

+		SELECT `teststring` FROM `dynamicparametertest1` WHERE `testname`="staticsql"

+	]]>

+	</select>

+

+	<select id="SelectStaticSql2" resultClass="string">

+	<![CDATA[

+		SELECT `teststring` FROM `dynamicparametertest2` WHERE `testname`="staticsql"

+	]]>

+	</select>

+	

+	<select id="SelectDynamicTable" parameterClass="string" resultClass="string">

+	<![CDATA[

+		SELECT `teststring` FROM `$value$` WHERE `testname`="dynamictable"

+	]]>

+	</select>

+

+	<select id="SelectDynamicComplex" parameterClass="array" resultClass="string">

+	<![CDATA[

+		SELECT CONCAT("##", `teststring`, "$$") FROM `$tablename$` WHERE `testname`=#testname, dbType=varchar#

+	]]>

+	</select>

+	

+	<select id="SelectNoDynamic" parameterClass="string" resultClass="string">

+	<![CDATA[

+		SELECT `teststring` FROM `dynamicparametertest1` WHERE `testname`=#value, dbType=varchar#

+	]]>

+	</select>

+

+</sqlMap>
\ No newline at end of file
diff --git a/tests/unit/Data/SqlMap/fixtures/mysql5_reset.sql b/tests/unit/Data/SqlMap/fixtures/mysql5_reset.sql
new file mode 100644
index 00000000..7544ce6b
--- /dev/null
+++ b/tests/unit/Data/SqlMap/fixtures/mysql5_reset.sql
@@ -0,0 +1,22 @@
+TRUNCATE TABLE `prado_system_data_sqlmap`.`dynamicparametertest1`;

+TRUNCATE TABLE `prado_system_data_sqlmap`.`dynamicparametertest2`;

+

+INSERT INTO `prado_system_data_sqlmap`.`dynamicparametertest1` (

+	`testname` ,

+	`teststring` ,

+	`testinteger`

+)

+VALUES

+('staticsql', 'staticsql1', '1'),

+('dynamictable', 'dynamictableparametertest1', '1')

+;

+

+INSERT INTO `prado_system_data_sqlmap`.`dynamicparametertest2` (

+	`testname` ,

+	`teststring` ,

+	`testinteger`

+)

+VALUES

+('staticsql', 'staticsql2', '2'),

+('dynamictable', 'dynamictableparametertest2', '2')

+;

diff --git a/tests/unit/Data/SqlMap/fixtures/mysql5_setup.sql b/tests/unit/Data/SqlMap/fixtures/mysql5_setup.sql
new file mode 100644
index 00000000..753b48ac
--- /dev/null
+++ b/tests/unit/Data/SqlMap/fixtures/mysql5_setup.sql
@@ -0,0 +1,22 @@
+CREATE DATABASE `prado_system_data_sqlmap`

+	DEFAULT CHARACTER SET utf8

+	COLLATE utf8_general_ci;

+

+GRANT ALL ON `prado_system_data_sqlmap`.*

+	TO 'prado_unitest'@'localhost'

+	IDENTIFIED BY 'prado_system_data_sqlmap_unitest';

+

+DROP TABLE IF EXISTS `dynamicparametertest1`;

+CREATE TABLE `dynamicparametertest1` (

+	`testname` varchar(50) NOT NULL,

+	`teststring` varchar(50) NOT NULL,

+	`testinteger` int(11) NOT NULL

+) ENGINE=MyISAM DEFAULT CHARSET=utf8;

+

+DROP TABLE IF EXISTS `dynamicparametertest2`;

+CREATE TABLE `dynamicparametertest2` (

+	`testname` varchar(50) NOT NULL,

+	`teststring` varchar(50) NOT NULL,

+	`testinteger` int(11) NOT NULL

+) ENGINE=MyISAM DEFAULT CHARSET=utf8;

+