Author: shawkins Date: 2011-08-04 10:50:37 -0400 (Thu, 04 Aug 2011) New Revision: 3359 Modified: trunk/api/src/main/java/org/teiid/language/SQLConstants.java trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java trunk/api/src/main/java/org/teiid/translator/SourceSystemFunctions.java trunk/build/kits/jboss-container/teiid-releasenotes.html trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/DB2ExecutionFactory.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/derby/DerbyExecutionFactory.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/h2/H2ExecutionFactory.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/hsql/HsqlExecutionFactory.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/mysql/MySQLExecutionFactory.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/postgresql/PostgreSQLExecutionFactory.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teiid/TeiidExecutionFactory.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/db2/TestDB2SqlTranslator.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/derby/TestDerbyCapabilities.java trunk/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml trunk/engine/src/main/java/org/teiid/query/function/FunctionMethods.java trunk/engine/src/main/java/org/teiid/query/function/source/SystemSource.java trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverVisitor.java trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj trunk/engine/src/main/resources/org/teiid/query/i18n.properties trunk/engine/src/test/java/org/teiid/query/function/TestFunctionLibrary.java trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java trunk/engine/src/test/java/org/teiid/query/processor/eval/TestExpressionEvaluator.java trunk/engine/src/test/java/org/teiid/query/resolver/TestResolver.java trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java trunk/test-integration/common/src/test/java/org/teiid/connector/visitor/util/TestSQLStringVisitor.java Log: TEIID-1694 adding support for trim and standard substring syntax Modified: trunk/api/src/main/java/org/teiid/language/SQLConstants.java =================================================================== --- trunk/api/src/main/java/org/teiid/language/SQLConstants.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/api/src/main/java/org/teiid/language/SQLConstants.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -111,6 +111,8 @@ public static final String INSTEAD = "INSTEAD"; //$NON-NLS-1$ public static final String ENABLED = "ENABLED"; //$NON-NLS-1$ public static final String DISABLED = "DISABLED"; //$NON-NLS-1$ + + public static final String TRIM = "TRIM"; } public interface Reserved { Modified: trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java =================================================================== --- trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -410,7 +410,20 @@ } } buffer.append(Tokens.RPAREN); - + } else if (name.equalsIgnoreCase(NonReserved.TRIM)) { + buffer.append(name); + buffer.append(Tokens.LPAREN); + String value = (String)((Literal)args.get(0)).getValue(); + if (!value.equalsIgnoreCase(BOTH)) { + buffer.append(value); + buffer.append(Tokens.SPACE); + } + append(args.get(1)); + buffer.append(" "); //$NON-NLS-1$ + buffer.append(FROM); + buffer.append(" "); //$NON-NLS-1$ + buffer.append(args.get(2)); + buffer.append(")"); //$NON-NLS-1$ } else { buffer.append(obj.getName()) Modified: trunk/api/src/main/java/org/teiid/translator/SourceSystemFunctions.java =================================================================== --- trunk/api/src/main/java/org/teiid/translator/SourceSystemFunctions.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/api/src/main/java/org/teiid/translator/SourceSystemFunctions.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -58,6 +58,10 @@ public static final String SUBSTRING = "substring"; //$NON-NLS-1$ public static final String TO_BYTES = "to_bytes"; //$NON-NLS-1$ public static final String TO_CHARS = "to_chars"; //$NON-NLS-1$ + /** + * The trim function is only used for a non-space trim character + */ + public static final String TRIM = "trim"; //$NON-NLS-1$ public static final String UCASE = "ucase"; //$NON-NLS-1$ public static final String UNESCAPE = "unescape"; //$NON-NLS-1$ Modified: trunk/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-08-04 14:50:37 UTC (rev 3359) @@ -35,7 +35,7 @@ <LI><B>Streaming XQuery</B> - in situations where document projection applies if the XMLQUERY/XMLTABLE path expressions meet certain conditions, then the incoming document will not only be projected, but the independent subtrees will be processed without loading the entire document. This allows for nearly arbitrarily large XML documents to be processed. See the Reference for more. <LI><B>Logging Procedures</B> - added SYSADMIN.isLoggable and SYSADMIN.logMsg to aid in debugging procedure logic. <LI><B>ANSI OFFSET/FETCH FIRST</B> - instead of the limit clause, a standard OFFSET and/or FETCH FIRST/NEXT clause can be used to limit results. - <LI><B>ODBC Cursors</B> - Capability to use "UseDeclareFetch" with ODBC is added. This enables user to read the results in batches, especially useful when dealing with large row count of results. + <LI><B>ODBC Cursors</B> - added the capability to use "UseDeclareFetch" with ODBC. This enables users to read results in batches, which is especially useful when dealing with large row counts. <LI><B>Internal Materialized Views</B> <UL> <LI><B>Distributed Refresh</B> - When a internal materialized view is refreshed at one node, an event is issued to all other nodes in the cluster to asynchronously fetch the new contents. Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/DB2ExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/DB2ExecutionFactory.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/DB2ExecutionFactory.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -23,9 +23,16 @@ package org.teiid.translator.jdbc.db2; import java.util.ArrayList; +import java.util.Arrays; import java.util.List; +import org.teiid.language.Expression; +import org.teiid.language.Function; +import org.teiid.language.Literal; +import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.Translator; +import org.teiid.translator.TranslatorException; +import org.teiid.translator.jdbc.FunctionModifier; @Translator(name="db2", description="A translator for IBM DB2 Database") public class DB2ExecutionFactory extends BaseDB2ExecutionFactory { @@ -80,6 +87,7 @@ supportedFunctions.add("RIGHT"); //$NON-NLS-1$ supportedFunctions.add("RTRIM"); //$NON-NLS-1$ supportedFunctions.add("SUBSTRING"); //$NON-NLS-1$ + supportedFunctions.add(SourceSystemFunctions.TRIM); //supportedFunctions.add("TRANSLATE"); //$NON-NLS-1$ supportedFunctions.add("UCASE"); //$NON-NLS-1$ supportedFunctions.add("UPPER"); //$NON-NLS-1$ @@ -129,5 +137,18 @@ public boolean supportsElementaryOlapOperations() { return getDatabaseVersion().compareTo(NINE_1) >= 0; } - + + @Override + public void start() throws TranslatorException { + super.start(); + registerFunctionModifier(SourceSystemFunctions.TRIM, new FunctionModifier() { + + @Override + public List<?> translate(Function function) { + List<Expression> p = function.getParameters(); + return Arrays.asList("STRIP(", p.get(2), ", ", ((Literal)p.get(0)).getValue(), ", ", p.get(1), ")"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ + } + }); + } + } Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/derby/DerbyExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/derby/DerbyExecutionFactory.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/derby/DerbyExecutionFactory.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -40,6 +40,7 @@ public static final String TEN_1 = "10.1"; //$NON-NLS-1$ public static final String TEN_2 = "10.2"; //$NON-NLS-1$ + public static final String TEN_3 = "10.3"; //$NON-NLS-1$ public static final String TEN_4 = "10.4"; //$NON-NLS-1$ public static final String TEN_5 = "10.5"; //$NON-NLS-1$ @@ -130,6 +131,9 @@ //supportedFunctions.add("RPAD"); //$NON-NLS-1$ supportedFunctions.add("RTRIM"); //$NON-NLS-1$ supportedFunctions.add("SUBSTRING"); //$NON-NLS-1$ + if (getDatabaseVersion().compareTo(TEN_3) >= 0) { + supportedFunctions.add(SourceSystemFunctions.TRIM); + } supportedFunctions.add("UCASE"); //$NON-NLS-1$ // These are executed within the server and never pushed down Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/h2/H2ExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/h2/H2ExecutionFactory.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/h2/H2ExecutionFactory.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -28,9 +28,9 @@ import java.util.ArrayList; import java.util.List; +import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.Translator; import org.teiid.translator.TranslatorException; -import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.jdbc.AliasModifier; import org.teiid.translator.jdbc.ConvertModifier; import org.teiid.translator.jdbc.FunctionModifier; @@ -139,6 +139,7 @@ supportedFunctions.add(SourceSystemFunctions.RPAD); supportedFunctions.add(SourceSystemFunctions.RTRIM); supportedFunctions.add(SourceSystemFunctions.SUBSTRING); + supportedFunctions.add(SourceSystemFunctions.TRIM); supportedFunctions.add(SourceSystemFunctions.UCASE); supportedFunctions.add(SourceSystemFunctions.UNESCAPE); Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/hsql/HsqlExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/hsql/HsqlExecutionFactory.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/hsql/HsqlExecutionFactory.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -28,9 +28,9 @@ import java.util.ArrayList; import java.util.List; +import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.Translator; import org.teiid.translator.TranslatorException; -import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.jdbc.ConvertModifier; import org.teiid.translator.jdbc.FunctionModifier; import org.teiid.translator.jdbc.JDBCExecutionFactory; @@ -125,6 +125,7 @@ supportedFunctions.add(SourceSystemFunctions.RPAD); supportedFunctions.add(SourceSystemFunctions.RTRIM); supportedFunctions.add(SourceSystemFunctions.SUBSTRING); + supportedFunctions.add(SourceSystemFunctions.TRIM); supportedFunctions.add(SourceSystemFunctions.UCASE); supportedFunctions.add(SourceSystemFunctions.DAYNAME); Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/mysql/MySQLExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/mysql/MySQLExecutionFactory.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/mysql/MySQLExecutionFactory.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -177,6 +177,7 @@ supportedFunctions.add(SourceSystemFunctions.RPAD); supportedFunctions.add(SourceSystemFunctions.RTRIM); supportedFunctions.add(SourceSystemFunctions.SUBSTRING); + supportedFunctions.add(SourceSystemFunctions.TRIM); supportedFunctions.add(SourceSystemFunctions.UCASE); // These are executed within the server and never pushed down Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -454,6 +454,7 @@ supportedFunctions.add("RTRIM"); //$NON-NLS-1$ supportedFunctions.add("SUBSTRING"); //$NON-NLS-1$ supportedFunctions.add("TRANSLATE"); //$NON-NLS-1$ + supportedFunctions.add(SourceSystemFunctions.TRIM); supportedFunctions.add("UCASE"); //$NON-NLS-1$ supportedFunctions.add("UPPER"); //$NON-NLS-1$ supportedFunctions.add("HOUR"); //$NON-NLS-1$ Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/postgresql/PostgreSQLExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/postgresql/PostgreSQLExecutionFactory.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/postgresql/PostgreSQLExecutionFactory.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -302,6 +302,7 @@ supportedFunctions.add("RPAD"); //$NON-NLS-1$ supportedFunctions.add("RTRIM"); //$NON-NLS-1$ supportedFunctions.add("SUBSTRING"); //$NON-NLS-1$ + supportedFunctions.add(SourceSystemFunctions.TRIM); supportedFunctions.add("UCASE"); //$NON-NLS-1$ supportedFunctions.add("UPPER"); //$NON-NLS-1$ Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teiid/TeiidExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teiid/TeiidExecutionFactory.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teiid/TeiidExecutionFactory.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -145,14 +145,18 @@ if (getDatabaseVersion().compareTo(SEVEN_3) >= 0) { supportedFunctions.add(SourceSystemFunctions.UNESCAPE); - - if (getDatabaseVersion().compareTo(SEVEN_4) >= 0) { - supportedFunctions.add(SourceSystemFunctions.UUID); - supportedFunctions.add(SourceSystemFunctions.ARRAY_GET); - supportedFunctions.add(SourceSystemFunctions.ARRAY_LENGTH); - } } + if (getDatabaseVersion().compareTo(SEVEN_4) >= 0) { + supportedFunctions.add(SourceSystemFunctions.UUID); + supportedFunctions.add(SourceSystemFunctions.ARRAY_GET); + supportedFunctions.add(SourceSystemFunctions.ARRAY_LENGTH); + } + + if (getDatabaseVersion().compareTo(SEVEN_5) >= 0) { + supportedFunctions.add(SourceSystemFunctions.TRIM); + } + return supportedFunctions; } Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -251,6 +251,7 @@ supportedFunctions.add(SourceSystemFunctions.SQRT); supportedFunctions.add(SourceSystemFunctions.SUBSTRING); supportedFunctions.add(SourceSystemFunctions.TAN); + supportedFunctions.add(SourceSystemFunctions.TRIM); supportedFunctions.add(SourceSystemFunctions.UCASE); supportedFunctions.add(SourceSystemFunctions.YEAR); Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/db2/TestDB2SqlTranslator.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/db2/TestDB2SqlTranslator.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/db2/TestDB2SqlTranslator.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -270,5 +270,14 @@ input, output, TRANSLATOR); } + + @Test public void testTrim() throws Exception { + String input = "SELECT trim(leading 'x' from stringnum) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT STRIP(SmallA.StringNum, leading, 'x') FROM SmallA"; //$NON-NLS-1$ + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, output, + TRANSLATOR); + } + } Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/derby/TestDerbyCapabilities.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/derby/TestDerbyCapabilities.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/derby/TestDerbyCapabilities.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -22,9 +22,7 @@ package org.teiid.translator.jdbc.derby; -import static org.junit.Assert.assertEquals; -import static org.junit.Assert.assertFalse; -import static org.junit.Assert.assertTrue; +import static org.junit.Assert.*; import org.junit.Test; @@ -41,7 +39,7 @@ DerbyExecutionFactory derbyCapabilities = new DerbyExecutionFactory(); assertEquals(27, derbyCapabilities.getSupportedFunctions().size()); derbyCapabilities.setDatabaseVersion(DerbyExecutionFactory.TEN_4); - assertEquals(43, derbyCapabilities.getSupportedFunctions().size()); + assertEquals(44, derbyCapabilities.getSupportedFunctions().size()); } } Modified: trunk/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml 2011-08-04 14:50:37 UTC (rev 3359) @@ -1005,6 +1005,7 @@ <row> <entry> <para>SUBSTRING(x, y)</para> + <para>SUBSTRING(x FROM y)</para> </entry> <entry> <para>Get substring from x, from position y to the end of x @@ -1017,6 +1018,7 @@ <row> <entry> <para>SUBSTRING(x, y, z)</para> + <para>SUBSTRING(x FROM y FOR z)</para> </entry> <entry> <para>Get substring from x from position y with length z @@ -1063,6 +1065,18 @@ </row> <row> <entry> + <para>TRIM([[LEADING|TRAILING|BOTH] [x] FROM] y)</para> + </entry> + <entry> + <para>Trim the leading, trailing, or both ends of a string y of character x. + If LEADING/TRAILING/BOTH is not specified, BOTH is used. If no trim character x is specficed then the blank space ' ' is used.</para> + </entry> + <entry> + <para>x in {character}, y in {string}</para> + </entry> + </row> + <row> + <entry> <para>UCASE(x)</para> </entry> <entry> Modified: trunk/engine/src/main/java/org/teiid/query/function/FunctionMethods.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/function/FunctionMethods.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/engine/src/main/java/org/teiid/query/function/FunctionMethods.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -56,6 +56,7 @@ import org.teiid.core.types.InputStreamFactory.BlobInputStreamFactory; import org.teiid.core.types.InputStreamFactory.ClobInputStreamFactory; import org.teiid.core.util.TimestampWithTimezone; +import org.teiid.language.SQLConstants; import org.teiid.language.SQLConstants.NonReserved; import org.teiid.query.QueryPlugin; import org.teiid.query.util.CommandContext; @@ -694,14 +695,30 @@ return new Integer(str.indexOf(sub, start.intValue() - 1) + 1); } + public static String trim(String trimSpec, String trimChar, String string) throws FunctionExecutionException { + if (trimChar.length() != 1) { + throw new FunctionExecutionException(QueryPlugin.Util.getString("SQLParser.Invalid_char", "trim char", trimChar)); //$NON-NLS-1$ //$NON-NLS-2$ + } + if (!trimSpec.equalsIgnoreCase(SQLConstants.Reserved.LEADING)) { + string = rightTrim(string, trimChar.charAt(0)); + } + if (!trimSpec.equalsIgnoreCase(SQLConstants.Reserved.TRAILING)) { + string = leftTrim(string, trimChar.charAt(0)); + } + return string; + } + // ================== Function = lefttrim ===================== private static final char SPACE = ' '; - public static Object leftTrim(String string) { + public static String leftTrim(String string, char trimChar) { for(int i=0; i<string.length(); i++) { - if(string.charAt(i) != SPACE) { + if(string.charAt(i) != trimChar) { // end of trim, return what's left + if (i==0) { + return string; + } return new String(string.substring(i)); } } @@ -709,13 +726,21 @@ // All spaces, so trim it all return ""; //$NON-NLS-1$ } + + + public static String leftTrim(String string) { + return leftTrim(string, SPACE); + } // ================== Function = righttrim ===================== - public static Object rightTrim(String string) { + public static String rightTrim(String string, char trimChar) { for(int i=string.length()-1; i>=0; i--) { - if(string.charAt(i) != SPACE) { + if(string.charAt(i) != trimChar) { // end of trim, return what's left + if (i==string.length()-1) { + return string; + } return new String(string.substring(0, i+1)); } } @@ -723,6 +748,10 @@ // All spaces, so trim it all return ""; //$NON-NLS-1$ } + + public static Object rightTrim(String string) { + return rightTrim(string, SPACE); + } // ================== Function = replace ===================== Modified: trunk/engine/src/main/java/org/teiid/query/function/source/SystemSource.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/function/source/SystemSource.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/engine/src/main/java/org/teiid/query/function/source/SystemSource.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -29,8 +29,8 @@ import org.teiid.core.types.DataTypeManager; import org.teiid.metadata.FunctionParameter; +import org.teiid.metadata.FunctionMethod.Determinism; import org.teiid.metadata.FunctionMethod.PushDown; -import org.teiid.metadata.FunctionMethod.Determinism; import org.teiid.query.QueryPlugin; import org.teiid.query.function.FunctionLibrary; import org.teiid.query.function.FunctionMetadataSource; @@ -196,7 +196,18 @@ addUuidFunction(); addArrayGet(); addArrayLength(); + addTrimFunction(); } + + private void addTrimFunction() { + functions.add( + new FunctionMethod(SourceSystemFunctions.TRIM, QueryPlugin.Util.getString("SystemSource.trim_desc"), STRING, FUNCTION_CLASS, SourceSystemFunctions.TRIM,//$NON-NLS-1$ + new FunctionParameter[] { + new FunctionParameter("spec", DataTypeManager.DefaultDataTypes.STRING, QueryPlugin.Util.getString("SystemSource.trim_arg1")),//$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("trimChar", DataTypeManager.DefaultDataTypes.STRING, QueryPlugin.Util.getString("SystemSource.trim_arg2")),//$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("string", DataTypeManager.DefaultDataTypes.STRING, QueryPlugin.Util.getString("SystemSource.trim_arg3")) }, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.STRING, QueryPlugin.Util.getString("SystemSource.trim_result")) ) ); //$NON-NLS-1$ //$NON-NLS-2$ + } private void addArrayLength() { functions.add(new FunctionMethod(SourceSystemFunctions.ARRAY_LENGTH, QueryPlugin.Util.getString("SystemSource.array_length_desc"), MISCELLANEOUS, PushDown.CAN_PUSHDOWN, FUNCTION_CLASS, SourceSystemFunctions.ARRAY_LENGTH, //$NON-NLS-1$ Modified: trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverVisitor.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverVisitor.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -529,11 +529,11 @@ if(fd.getName().equalsIgnoreCase(FunctionLibrary.CONVERT) || fd.getName().equalsIgnoreCase(FunctionLibrary.CAST)) { String dataType = (String) ((Constant)args[1]).getValue(); - Class dataTypeClass = DataTypeManager.getDataTypeClass(dataType); + Class<?> dataTypeClass = DataTypeManager.getDataTypeClass(dataType); fd = library.findTypedConversionFunction(args[0].getType(), dataTypeClass); // Verify that the type conversion from src to type is even valid - Class srcTypeClass = args[0].getType(); + Class<?> srcTypeClass = args[0].getType(); if(srcTypeClass != null && dataTypeClass != null && !srcTypeClass.equals(dataTypeClass) && !DataTypeManager.isTransformable(srcTypeClass, dataTypeClass)) { @@ -545,7 +545,6 @@ fd = library.copyFunctionChangeReturnType(fd, lookup.getReturnElement().getType()); } - // Resolve the function function.setFunctionDescriptor(fd); function.setType(fd.getReturnType()); } Modified: trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -55,6 +55,7 @@ import org.teiid.core.types.Transform; import org.teiid.core.util.Assertion; import org.teiid.core.util.TimestampWithTimezone; +import org.teiid.language.SQLConstants; import org.teiid.language.SQLConstants.NonReserved; import org.teiid.query.QueryPlugin; import org.teiid.query.eval.Evaluator; @@ -2281,6 +2282,7 @@ FUNCTION_MAP.put(FunctionLibrary.PARSETIME.toLowerCase(), 7); FUNCTION_MAP.put(FunctionLibrary.FORMATDATE.toLowerCase(), 8); FUNCTION_MAP.put(FunctionLibrary.FORMATTIME.toLowerCase(), 9); + FUNCTION_MAP.put(SourceSystemFunctions.TRIM.toLowerCase(), 10); } private Expression rewriteFunction(Function function) throws TeiidComponentException, TeiidProcessingException{ @@ -2407,7 +2409,27 @@ function.getArgs()[0] = ResolverUtil.getConversion(function.getArg(0), DataTypeManager.getDataTypeName(function.getArg(0).getType()), DataTypeManager.DefaultDataTypes.TIMESTAMP, false, funcLibrary); break; } + case 10: { + if (new Constant(" ").equals(function.getArg(1))) { //$NON-NLS-1$ + String spec = (String)((Constant)function.getArg(0)).getValue(); + Expression string = function.getArg(2); + if (!SQLConstants.Reserved.TRAILING.equalsIgnoreCase(spec)) { + function = new Function(SourceSystemFunctions.LTRIM, new Expression[] {string}); + FunctionDescriptor descriptor = funcLibrary.findFunction(SourceSystemFunctions.LTRIM, new Class[] { DataTypeManager.DefaultDataClasses.STRING }); + function.setFunctionDescriptor(descriptor); + function.setType(DataTypeManager.DefaultDataClasses.STRING); + string = function; + } + if (!SQLConstants.Reserved.LEADING.equalsIgnoreCase(spec)) { + function = new Function(SourceSystemFunctions.RTRIM, new Expression[] {string}); + FunctionDescriptor descriptor = funcLibrary.findFunction(SourceSystemFunctions.RTRIM, new Class[] { DataTypeManager.DefaultDataClasses.STRING }); + function.setFunctionDescriptor(descriptor); + function.setType(DataTypeManager.DefaultDataClasses.STRING); + } + } + break; } + } } Expression[] args = function.getArgs(); Modified: trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -1304,6 +1304,20 @@ outputDisplayName((String)((Constant)args[0]).getValue()); registerNodes(args, 1); append(")"); //$NON-NLS-1$ + } else if (name.equalsIgnoreCase(SourceSystemFunctions.TRIM)) { + append(name); + append(SQLConstants.Tokens.LPAREN); + String value = (String)((Constant)args[0]).getValue(); + if (!value.equalsIgnoreCase(BOTH)) { + append(((Constant)args[0]).getValue()); + append(" "); //$NON-NLS-1$ + } + append(args[1]); + append(" "); //$NON-NLS-1$ + append(FROM); + append(" "); //$NON-NLS-1$ + append(args[2]); + append(")"); //$NON-NLS-1$ } else { append(name); append("("); //$NON-NLS-1$ Modified: trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj =================================================================== --- trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2011-08-04 14:50:37 UTC (rev 3359) @@ -3548,16 +3548,14 @@ expression = expression(info) { args.add(expression); - expression = null; } <COMMA> expression = dataType() { args.add(expression); - expression = null; } <RPAREN> - ) + ) | ( funcToken = <CAST> @@ -3565,30 +3563,83 @@ expression = expression(info) { args.add(expression); - expression = null; } <AS> expression = dataType() { args.add(expression); - expression = null; } <RPAREN> ) | + LOOKAHEAD(4, {getToken(1).image.equalsIgnoreCase("SUBSTRING")}) ( + funcName = nonReserved("SUBSTRING") + <LPAREN> + expression = expression(info) + { + args.add(expression); + } + <FROM> expression = expression(info) + { + args.add(expression); + } + [<FOR> expression = expression(info) + { + args.add(expression); + } + ] + <RPAREN> + ) + | + LOOKAHEAD(1, {getToken(1).image.equalsIgnoreCase("TRIM")}) ( + funcName= nonReserved("TRIM") + <LPAREN> + { funcToken = null; expression = null;} + [ + LOOKAHEAD(2) (((funcToken = <LEADING>| + funcToken = <TRAILING>| + funcToken = <BOTH>) + [expression = expression(info)]) + | + expression = expression(info)) + <FROM> + ] + { + if (funcToken == null) { + args.add(new Constant("BOTH")); + } else { + args.add(new Constant(funcToken.image)); + } + if (expression == null) { + args.add(new Constant(" ")); + } else { + if (expression instanceof Constant) { + Object value = ((Constant)expression).getValue(); + if (value instanceof String && ((String)value).length() != 1) { + throw new ParseException(QueryPlugin.Util.getString("SQLParser.Invalid_char", "TRIM CHAR", value)); //$NON-NLS-1$ + } + } + args.add(expression); + } + } + expression = expression(info) + { + args.add(expression); + } + <RPAREN> + ) + | LOOKAHEAD(<ID>, {matchesAny(getToken(1).image, "to_chars", "to_bytes") != null}) ( funcName = nonReserved("TO_CHARS", "TO_BYTES") <LPAREN> expression = expression(info) { args.add(expression); - expression = null; } <COMMA> expression = stringConstant() { args.add(expression); - expression = null; } <RPAREN> ) @@ -3599,19 +3650,16 @@ expression = intervalType() { args.add(expression); - expression = null; } <COMMA> expression = expression(info) { args.add(expression); - expression = null; } <COMMA> expression = expression(info) { args.add(expression); - expression = null; } <RPAREN> ) @@ -3654,13 +3702,11 @@ ) { args.add(expression); - expression = null; } [ <COMMA> expression = expression(info) { args.add(expression); - expression = null; } ] <RPAREN> @@ -3773,7 +3819,6 @@ (<COMMA> expression=expression(info) { content.add(expression); - expression = null; } )* <RPAREN> @@ -3795,12 +3840,10 @@ expression = derivedColumn(info) { args.add(expression); - expression = null; } (<COMMA> expression=derivedColumn(info) { args.add(expression); - expression = null; } )* <RPAREN> @@ -3823,12 +3866,10 @@ expression = derivedColumn(info) { args.add(expression); - expression = null; } (<COMMA> expression=derivedColumn(info) { args.add(expression); - expression = null; } )* <RPAREN> Modified: trunk/engine/src/main/resources/org/teiid/query/i18n.properties =================================================================== --- trunk/engine/src/main/resources/org/teiid/query/i18n.properties 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/engine/src/main/resources/org/teiid/query/i18n.properties 2011-08-04 14:50:37 UTC (rev 3359) @@ -395,6 +395,11 @@ SystemSource.Rand_result_desc=Generated Random Number SystemSource.uuid_desc=UUID SystemSource.uuid_result_desc=type 4 UUID +SystemSource.trim_desc=Trim characters from the front and/or back of a string +SystemSource.trim_arg1=Trim specification, can be one of TRAILING, LEADING, or BOTH +SystemSource.trim_arg2=Trim character - must be a single character +SystemSource.trim_arg3=String to trim +SystemSource.trim_result=Trimmed string SystemSource.Double_arg2=Number SystemSource.Atan_arg1=Number parameter1 SystemSource.Atan_arg2=Number parameter2 Modified: trunk/engine/src/test/java/org/teiid/query/function/TestFunctionLibrary.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/function/TestFunctionLibrary.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/engine/src/test/java/org/teiid/query/function/TestFunctionLibrary.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -1408,4 +1408,16 @@ assertEquals(1, helpInvokeMethod("array_get", new Class<?>[] {DefaultDataClasses.OBJECT, DefaultDataClasses.INTEGER}, new Object[] {new Object[] {1}, 1}, null)); //$NON-NLS-1$ } + @Test() public void testTrim() throws Exception { + helpInvokeMethod("trim", new Object[] {"leading", "x", "xaxx"}, "axx"); //$NON-NLS-1$ + } + + @Test() public void testTrim1() throws Exception { + helpInvokeMethod("trim", new Object[] {"both", " ", " a "}, "a"); //$NON-NLS-1$ + } + + @Test() public void testTrim2() throws Exception { + helpInvokeMethod("trim", new Object[] {"trailing", "x", "xaxx"}, "xa"); //$NON-NLS-1$ + } + } Modified: trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -6859,5 +6859,9 @@ query.setFrom(new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("g"))))); helpTest(sql, "SELECT ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) FROM g", query); } + + @Test public void testTrim1() { + helpException("select trim('xy' from e1) from pm1.g1"); + } } Modified: trunk/engine/src/test/java/org/teiid/query/processor/eval/TestExpressionEvaluator.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/eval/TestExpressionEvaluator.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/engine/src/test/java/org/teiid/query/processor/eval/TestExpressionEvaluator.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -432,4 +432,9 @@ assertEquals(Boolean.TRUE, Evaluator.evaluate(inc)); } + @Test public void testSubstring() throws Exception { + Expression ex = TestFunctionResolving.getExpression("substring('abcd' from 2 for 2)"); + assertEquals("bc", Evaluator.evaluate(ex)); + } + } Modified: trunk/engine/src/test/java/org/teiid/query/resolver/TestResolver.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/resolver/TestResolver.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/engine/src/test/java/org/teiid/query/resolver/TestResolver.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -2977,4 +2977,14 @@ helpResolveUpdateProcedure(procedure, userQuery); } + + @Test public void testTrim() { + Query query = (Query)helpResolve("select trim(e1) from pm1.g1"); + assertEquals(DataTypeManager.DefaultDataClasses.STRING, query.getProjectedSymbols().get(0).getType()); + } + + @Test public void testTrim1() { + helpResolve("select trim('x' from e1) from pm1.g1"); + } + } \ No newline at end of file Modified: trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -2464,4 +2464,12 @@ helpTestRewriteCommand("select max(1) from pm1.g1 group by e1", "SELECT 1 FROM pm1.g1 GROUP BY e1"); } + @Test public void testRewriteTrim() throws Exception { + helpTestRewriteExpression("trim(pm1.g1.e1)", "rtrim(ltrim(pm1.g1.e1))", RealMetadataFactory.example1Cached()); + } + + @Test public void testRewriteTrim1() throws Exception { + helpTestRewriteExpression("trim(leading from pm1.g1.e1)", "ltrim(pm1.g1.e1)", RealMetadataFactory.example1Cached()); + } + } Modified: trunk/test-integration/common/src/test/java/org/teiid/connector/visitor/util/TestSQLStringVisitor.java =================================================================== --- trunk/test-integration/common/src/test/java/org/teiid/connector/visitor/util/TestSQLStringVisitor.java 2011-08-04 12:09:55 UTC (rev 3358) +++ trunk/test-integration/common/src/test/java/org/teiid/connector/visitor/util/TestSQLStringVisitor.java 2011-08-04 14:50:37 UTC (rev 3359) @@ -432,5 +432,12 @@ Command command = FakeTranslationFactory.getInstance().getBQTTranslationUtility().parseCommand(sql, true, true); assertEquals("SELECT MAX(g_0.IntNum) OVER (ORDER BY g_0.IntKey NULLS FIRST) FROM SmallA AS g_0", command.toString()); //$NON-NLS-1$ } + + @Test public void testTrim() throws Exception { + String sql = "select trim(both 'x' from stringkey) from bqt1.smalla"; + + Command command = FakeTranslationFactory.getInstance().getBQTTranslationUtility().parseCommand(sql, true, true); + assertEquals("SELECT trim('x' FROM g_0.StringKey) FROM SmallA AS g_0", command.toString()); //$NON-NLS-1$ + } }