From: <hp...@us...> - 2009-09-04 22:44:00
|
Revision: 9810 http://wonder.svn.sourceforge.net/wonder/?rev=9810&view=rev Author: hprange Date: 2009-09-04 22:43:52 +0000 (Fri, 04 Sep 2009) Log Message: ----------- Fixes WONDER-342: There is no MicrosoftSQLHelper implementation for generating limit expressions It is a simple implementation, but supports queries with and without ORDER BY clauses. Also tested with a real D2W application. Modified Paths: -------------- trunk/Wonder/Frameworks/Core/ERExtensions/Sources/er/extensions/jdbc/ERXSQLHelper.java trunk/Wonder/Frameworks/Core/ERExtensions/Tests/er/extensions/ERExtensionsTest.java Added Paths: ----------- trunk/Wonder/Frameworks/Core/ERExtensions/Tests/er/extensions/jdbc/ trunk/Wonder/Frameworks/Core/ERExtensions/Tests/er/extensions/jdbc/MicrosoftSQLHelperTest.java Modified: trunk/Wonder/Frameworks/Core/ERExtensions/Sources/er/extensions/jdbc/ERXSQLHelper.java =================================================================== --- trunk/Wonder/Frameworks/Core/ERExtensions/Sources/er/extensions/jdbc/ERXSQLHelper.java 2009-09-04 16:07:31 UTC (rev 9809) +++ trunk/Wonder/Frameworks/Core/ERExtensions/Sources/er/extensions/jdbc/ERXSQLHelper.java 2009-09-04 22:43:52 UTC (rev 9810) @@ -2169,6 +2169,48 @@ protected Pattern commentPattern() { return Pattern.compile("^--"); } + + @Override + public String limitExpressionForSQL( EOSQLExpression expression, EOFetchSpecification fetchSpecification, String sql, long start, long end ) { + if( sql == null || "".equals( sql ) ) + { + return sql; + } + + String originalSql = sql.toLowerCase(); + + String orderBy; + + int indexOfOrderByClause = originalSql.indexOf( " order by " ); + + if( indexOfOrderByClause > 0) + { + orderBy = originalSql.substring( indexOfOrderByClause + 1, originalSql.length() ); + + originalSql = originalSql.substring( 0, indexOfOrderByClause ); + } + else + { + String columns = originalSql.substring( originalSql.indexOf( "select " ) + 7, originalSql.indexOf( " from " ) ); + + orderBy = "order by " + columns.split( "," )[0]; + } + + StringBuilder limitSqlBuilder = new StringBuilder( originalSql ); + + limitSqlBuilder.insert( 0, "select * from (" ); + + String rowNumberClause = ", row_number() over (" + orderBy + ") eo_rownum"; + + limitSqlBuilder.insert( limitSqlBuilder.lastIndexOf( " from " ), rowNumberClause ); + limitSqlBuilder.append( ") as temp_row_number where eo_rownum >= " ); + limitSqlBuilder.append( start + 1 ); + limitSqlBuilder.append( " and eo_rownum < " ); + limitSqlBuilder.append( end + 1 ); + limitSqlBuilder.append( " order by eo_rownum" ); + + return limitSqlBuilder.toString(); + } } } Modified: trunk/Wonder/Frameworks/Core/ERExtensions/Tests/er/extensions/ERExtensionsTest.java =================================================================== --- trunk/Wonder/Frameworks/Core/ERExtensions/Tests/er/extensions/ERExtensionsTest.java 2009-09-04 16:07:31 UTC (rev 9809) +++ trunk/Wonder/Frameworks/Core/ERExtensions/Tests/er/extensions/ERExtensionsTest.java 2009-09-04 22:43:52 UTC (rev 9810) @@ -10,8 +10,8 @@ */ public class ERExtensionsTest extends TestSuite { - public static Test suite() { - TestSuite suite = new TestSuite("Tests for ERExtensions"); + public static Test suite() { + TestSuite suite = new TestSuite("Tests for ERExtensions"); //$JUnit-BEGIN$ suite.addTestSuite(com.webobjects.foundation.NSArrayTest.class); suite.addTestSuite(com.webobjects.foundation.NSDictionaryTest.class); @@ -25,8 +25,9 @@ suite.addTestSuite(er.extensions.foundation.ERXMutableArrayTest.class); suite.addTestSuite(er.extensions.foundation.ERXThreadStorageTest.class); suite.addTestSuite(er.extensions.foundation.ERXUtilitiesTest.class); + suite.addTestSuite(er.extensions.jdbc.MicrosoftSQLHelperTest.class); suite.addTestSuite(er.extensions.statistics.ERXMetricsTest.class); //$JUnit-END$ - return suite; + return suite; } } Added: trunk/Wonder/Frameworks/Core/ERExtensions/Tests/er/extensions/jdbc/MicrosoftSQLHelperTest.java =================================================================== --- trunk/Wonder/Frameworks/Core/ERExtensions/Tests/er/extensions/jdbc/MicrosoftSQLHelperTest.java (rev 0) +++ trunk/Wonder/Frameworks/Core/ERExtensions/Tests/er/extensions/jdbc/MicrosoftSQLHelperTest.java 2009-09-04 22:43:52 UTC (rev 9810) @@ -0,0 +1,60 @@ +package er.extensions.jdbc; + +import junit.framework.TestCase; +import er.extensions.jdbc.ERXSQLHelper.MicrosoftSQLHelper; + +public class MicrosoftSQLHelperTest extends TestCase +{ + private MicrosoftSQLHelper helper; + + @Override + protected void setUp() throws Exception + { + super.setUp(); + + helper = new ERXSQLHelper.MicrosoftSQLHelper(); + } + + public void testLimitExpressionFixPaginationRange() throws Exception + { + String result = helper.limitExpressionForSQL( null, null, "select t1.column1 from table1 as t1", 0, 20 ); + + assertTrue( result.contains( "eo_rownum >= 1" ) ); + assertTrue( result.contains( "eo_rownum < 21" ) ); + } + + public void testLimitExpressionForEmptySql() throws Exception + { + String result = helper.limitExpressionForSQL( null, null, "", 0, 0 ); + + assertEquals( "", result ); + } + + public void testLimitExpressionForNullSql() throws Exception + { + String result = helper.limitExpressionForSQL( null, null, null, 0, 0 ); + + assertNull( result ); + } + + public void testLimitExpressionForSimpleSql() throws Exception + { + String result = helper.limitExpressionForSQL( null, null, "select t1.column1 from table1 as t1", 0, 5 ); + + assertEquals( "select * from (select t1.column1, row_number() over (order by t1.column1) eo_rownum from table1 as t1) as temp_row_number where eo_rownum >= 1 and eo_rownum < 6 order by eo_rownum", result ); + } + + public void testLimitExpressionForSqlWithOrderByClause() throws Exception + { + String result = helper.limitExpressionForSQL( null, null, "select t1.column1, t1.column2 from table1 as t1 order by t1.column2", 0, 5 ); + + assertEquals( "select * from (select t1.column1, t1.column2, row_number() over (order by t1.column2) eo_rownum from table1 as t1) as temp_row_number where eo_rownum >= 1 and eo_rownum < 6 order by eo_rownum", result ); + } + + public void testLimitExpressionIsCaseInsensitive() throws Exception + { + String result = helper.limitExpressionForSQL( null, null, "SeLecT t1.column1 FrOM table1 AS t1", 0, 5 ); + + assertEquals( "select * from (select t1.column1, row_number() over (order by t1.column1) eo_rownum from table1 as t1) as temp_row_number where eo_rownum >= 1 and eo_rownum < 6 order by eo_rownum", result ); + } +} Property changes on: trunk/Wonder/Frameworks/Core/ERExtensions/Tests/er/extensions/jdbc/MicrosoftSQLHelperTest.java ___________________________________________________________________ Added: svn:mime-type + text/plain This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |