[teiid-commits] teiid SVN: r1264 - in trunk/connectors/connector-jdbc/src: main/java/org/teiid/connector/jdbc/derby and 10 other directories.

Author: loleary Date: 2009-08-19 15:50:56 -0400 (Wed, 19 Aug 2009) New Revision: 1264 Added: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/LOCATEFunctionModifier.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/LOCATEFunctionModifier.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/derby/TestDerbySQLTranslator.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/translator/TestLOCATEFunctionModifier.java Removed: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/LocateFunctionModifier.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/LocateFunctionModifier.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestLocateFunctionModifier.java Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/db2/DB2SQLTranslator.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/derby/DerbySQLTranslator.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/mysql/MySQLTranslator.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/PostgreSQLTranslator.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/db2/TestDB2SqlTranslator.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/mysql/TestMySQLTranslator.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/postgresql/TestPostgreSQLTranslator.java Log: TEIID-754: LOCATE() function isn't being translated correctly by Oracle Connector Created generic LOCATEFunctionModifier that could be used by all connectors. This ensures the implementation is for string index bases of 1. To handle the negative value issue the modify method uses a searched case expression for non-literal start indexes and and checks literal to ensure they are not less than 1. Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/db2/DB2SQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/db2/DB2SQLTranslator.java 2009-08-19 15:20:26 UTC (rev 1263) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/db2/DB2SQLTranslator.java 2009-08-19 19:50:56 UTC (rev 1264) @@ -34,6 +34,7 @@ import org.teiid.connector.api.TypeFacility; import org.teiid.connector.api.TypeFacility.RUNTIME_TYPES; import org.teiid.connector.jdbc.translator.AliasModifier; +import org.teiid.connector.jdbc.translator.LOCATEFunctionModifier; import org.teiid.connector.jdbc.translator.MODFunctionModifier; import org.teiid.connector.jdbc.translator.Translator; import org.teiid.connector.language.ICommand; @@ -49,8 +50,6 @@ import org.teiid.connector.visitor.framework.HierarchyVisitor; -/** - */ public class DB2SQLTranslator extends Translator { @Override @@ -60,6 +59,7 @@ registerFunctionModifier(SourceSystemFunctions.CHAR, new AliasModifier("chr")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new AliasModifier("day")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.IFNULL, new AliasModifier("coalesce")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.LOCATE, new LOCATEFunctionModifier(getLanguageFactory(), "LOCATE")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.SUBSTRING, new AliasModifier("substr")); //$NON-NLS-1$ List<Class<?>> supportedModTypes = new ArrayList<Class<?>>(3); Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/derby/DerbySQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/derby/DerbySQLTranslator.java 2009-08-19 15:20:26 UTC (rev 1263) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/derby/DerbySQLTranslator.java 2009-08-19 19:50:56 UTC (rev 1264) @@ -29,6 +29,7 @@ import org.teiid.connector.jdbc.db2.DB2SQLTranslator; import org.teiid.connector.jdbc.oracle.LeftOrRightFunctionModifier; import org.teiid.connector.jdbc.translator.EscapeSyntaxModifier; +import org.teiid.connector.jdbc.translator.LOCATEFunctionModifier; @@ -46,6 +47,7 @@ registerFunctionModifier(SourceSystemFunctions.TIMESTAMPADD, new EscapeSyntaxModifier()); registerFunctionModifier(SourceSystemFunctions.TIMESTAMPDIFF, new EscapeSyntaxModifier()); registerFunctionModifier(SourceSystemFunctions.LEFT, new LeftOrRightFunctionModifier(getLanguageFactory())); + registerFunctionModifier(SourceSystemFunctions.LOCATE, new LOCATEFunctionModifier(getLanguageFactory(), "LOCATE")); //$NON-NLS-1$ //overrides of db2 functions registerFunctionModifier(SourceSystemFunctions.CONCAT, new EscapeSyntaxModifier()); Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/mysql/MySQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/mysql/MySQLTranslator.java 2009-08-19 15:20:26 UTC (rev 1263) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/mysql/MySQLTranslator.java 2009-08-19 19:50:56 UTC (rev 1264) @@ -33,6 +33,7 @@ import org.teiid.connector.api.ConnectorEnvironment; import org.teiid.connector.api.ConnectorException; import org.teiid.connector.api.SourceSystemFunctions; +import org.teiid.connector.jdbc.translator.LOCATEFunctionModifier; import org.teiid.connector.jdbc.translator.Translator; @@ -50,6 +51,7 @@ registerFunctionModifier(SourceSystemFunctions.BITNOT, new BitFunctionModifier("~", getLanguageFactory())); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.BITOR, new BitFunctionModifier("|", getLanguageFactory())); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.BITXOR, new BitFunctionModifier("^", getLanguageFactory())); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.LOCATE, new LOCATEFunctionModifier(getLanguageFactory(), "LOCATE")); //$NON-NLS-1$ } @Override Deleted: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/LocateFunctionModifier.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/LocateFunctionModifier.java 2009-08-19 15:20:26 UTC (rev 1263) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/LocateFunctionModifier.java 2009-08-19 19:50:56 UTC (rev 1264) @@ -1,79 +0,0 @@ -/* - * JBoss, Home of Professional Open Source. - * See the COPYRIGHT.txt file distributed with this work for information - * regarding copyright ownership. Some portions may be licensed - * to Red Hat, Inc. under one or more contributor license agreements. - * - * This library is free software; you can redistribute it and/or - * modify it under the terms of the GNU Lesser General Public - * License as published by the Free Software Foundation; either - * version 2.1 of the License, or (at your option) any later version. - * - * This library is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU - * Lesser General Public License for more details. - * - * You should have received a copy of the GNU Lesser General Public - * License along with this library; if not, write to the Free Software - * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA - * 02110-1301 USA. - */ - -package org.teiid.connector.jdbc.oracle; - -import java.util.Arrays; -import java.util.List; - -import org.teiid.connector.jdbc.translator.BasicFunctionModifier; -import org.teiid.connector.jdbc.translator.FunctionModifier; -import org.teiid.connector.language.IExpression; -import org.teiid.connector.language.IFunction; -import org.teiid.connector.language.ILanguageFactory; -import org.teiid.connector.language.ILiteral; - - -/** - * Modify the locate function to use the Oracle instr function. - * - * locate(sub, str) -> instr(str, sub) - * - * locate(sub, str, start) -> instr(str, sub, start+1) - */ -public class LocateFunctionModifier extends BasicFunctionModifier implements FunctionModifier { - - private ILanguageFactory langFactory; - - public LocateFunctionModifier(ILanguageFactory langFactory) { - this.langFactory = langFactory; - } - - /* - * @see com.metamatrix.connector.jdbc.extension.FunctionModifier#modify(com.metamatrix.data.language.IFunction) - */ - public IExpression modify(IFunction function) { - function.setName("instr"); //$NON-NLS-1$ - List<IExpression> args = function.getParameters(); - IExpression expr = args.get(0); - args.set(0, args.get(1)); - args.set(1, expr); - if(args.size() == 3) { - if(args.get(2) instanceof ILiteral) { - ILiteral indexConst = (ILiteral)args.get(2); - if(indexConst.getValue() != null) { - // Just modify the constant - Integer index = (Integer) indexConst.getValue(); - args.set(2, langFactory.createLiteral(new Integer(index.intValue()+1), Integer.class)); - } - } else { - // Make plus function since this involves an element or function - IFunction plusFunction = langFactory.createFunction("+", //$NON-NLS-1$ - Arrays.asList( args.get(2), langFactory.createLiteral(new Integer(1), Integer.class) ), - Integer.class); - args.set(2, plusFunction); - } - } - return function; - } - -} Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java 2009-08-19 15:20:26 UTC (rev 1263) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java 2009-08-19 19:50:56 UTC (rev 1264) @@ -42,7 +42,9 @@ import org.teiid.connector.jdbc.JDBCPlugin; import org.teiid.connector.jdbc.translator.AliasModifier; import org.teiid.connector.jdbc.translator.ExtractFunctionModifier; +import org.teiid.connector.jdbc.translator.LOCATEFunctionModifier; import org.teiid.connector.jdbc.translator.Translator; +import org.teiid.connector.jdbc.translator.LOCATEFunctionModifier.ParameterOrder; import org.teiid.connector.language.ICommand; import org.teiid.connector.language.IElement; import org.teiid.connector.language.IFunction; @@ -96,7 +98,7 @@ registerFunctionModifier(SourceSystemFunctions.QUARTER, new DayWeekQuarterFunctionModifier(getLanguageFactory(), "Q"));//$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.DAYOFWEEK, new DayWeekQuarterFunctionModifier(getLanguageFactory(), "D"));//$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.DAYOFYEAR, new DayWeekQuarterFunctionModifier(getLanguageFactory(), "DDD"));//$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.LOCATE, new LocateFunctionModifier(getLanguageFactory())); + registerFunctionModifier(SourceSystemFunctions.LOCATE, new LOCATEFunctionModifier(getLanguageFactory(), "INSTR", ParameterOrder.SOURCE_SEARCH_INDEX)); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.SUBSTRING, new AliasModifier("substr"));//$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.LEFT, new LeftOrRightFunctionModifier(getLanguageFactory())); registerFunctionModifier(SourceSystemFunctions.RIGHT, new LeftOrRightFunctionModifier(getLanguageFactory())); Added: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/LOCATEFunctionModifier.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/LOCATEFunctionModifier.java (rev 0) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/LOCATEFunctionModifier.java 2009-08-19 19:50:56 UTC (rev 1264) @@ -0,0 +1,55 @@ +/* + * JBoss, Home of Professional Open Source. + * See the COPYRIGHT.txt file distributed with this work for information + * regarding copyright ownership. Some portions may be licensed + * to Red Hat, Inc. under one or more contributor license agreements. + * + * This library is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 2.1 of the License, or (at your option) any later version. + * + * This library is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public + * License along with this library; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA + * 02110-1301 USA. + */ + +package org.teiid.connector.jdbc.postgresql; + +import java.util.ArrayList; +import java.util.List; + +import org.teiid.connector.api.TypeFacility; +import org.teiid.connector.language.IExpression; +import org.teiid.connector.language.IFunction; +import org.teiid.connector.language.ILanguageFactory; + +public class LOCATEFunctionModifier extends org.teiid.connector.jdbc.translator.LOCATEFunctionModifier { + + public LOCATEFunctionModifier(ILanguageFactory factory) { + super(factory); + } + + @Override + public List<?> translate(IFunction function) { + List<Object> parts = new ArrayList<Object>(); + List<IExpression> params = function.getParameters(); + parts.add("position("); //$NON-NLS-1$ + parts.add(params.get(0)); + parts.add(" in "); //$NON-NLS-1$ + if (params.size() == 3) { + parts.add(this.getLanguageFactory().createFunction("substr", params.subList(1, 3), TypeFacility.RUNTIME_TYPES.STRING)); //$NON-NLS-1$ + } else { + parts.add(params.get(1)); + } + parts.add(")"); //$NON-NLS-1$ + return parts; + } + +} Property changes on: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/LOCATEFunctionModifier.java ___________________________________________________________________ Name: svn:mime-type + text/plain Deleted: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/LocateFunctionModifier.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/LocateFunctionModifier.java 2009-08-19 15:20:26 UTC (rev 1263) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/LocateFunctionModifier.java 2009-08-19 19:50:56 UTC (rev 1264) @@ -1,58 +0,0 @@ -/* - * JBoss, Home of Professional Open Source. - * See the COPYRIGHT.txt file distributed with this work for information - * regarding copyright ownership. Some portions may be licensed - * to Red Hat, Inc. under one or more contributor license agreements. - * - * This library is free software; you can redistribute it and/or - * modify it under the terms of the GNU Lesser General Public - * License as published by the Free Software Foundation; either - * version 2.1 of the License, or (at your option) any later version. - * - * This library is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU - * Lesser General Public License for more details. - * - * You should have received a copy of the GNU Lesser General Public - * License along with this library; if not, write to the Free Software - * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA - * 02110-1301 USA. - */ - -package org.teiid.connector.jdbc.postgresql; - -import java.util.ArrayList; -import java.util.List; - -import org.teiid.connector.api.TypeFacility; -import org.teiid.connector.jdbc.translator.BasicFunctionModifier; -import org.teiid.connector.language.IExpression; -import org.teiid.connector.language.IFunction; -import org.teiid.connector.language.ILanguageFactory; - -public class LocateFunctionModifier extends BasicFunctionModifier { - - private ILanguageFactory factory; - - public LocateFunctionModifier(ILanguageFactory factory) { - this.factory = factory; - } - - @Override - public List<?> translate(IFunction function) { - List<Object> parts = new ArrayList<Object>(); - List<IExpression> params = function.getParameters(); - parts.add("position("); //$NON-NLS-1$ - parts.add(params.get(0)); - parts.add(" in "); //$NON-NLS-1$ - if (params.size() == 3) { - parts.add(factory.createFunction("substr", params.subList(1, 3), TypeFacility.RUNTIME_TYPES.STRING)); //$NON-NLS-1$ - } else { - parts.add(params.get(1)); - } - parts.add(")"); //$NON-NLS-1$ - return parts; - } - -} Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/PostgreSQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/PostgreSQLTranslator.java 2009-08-19 15:20:26 UTC (rev 1263) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/PostgreSQLTranslator.java 2009-08-19 19:50:56 UTC (rev 1264) @@ -95,7 +95,7 @@ registerFunctionModifier(SourceSystemFunctions.IFNULL, new AliasModifier("coalesce")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.CONVERT, new PostgreSQLConvertModifier(getLanguageFactory())); - registerFunctionModifier(SourceSystemFunctions.LOCATE, new LocateFunctionModifier(getLanguageFactory())); + registerFunctionModifier(SourceSystemFunctions.LOCATE, new LOCATEFunctionModifier(getLanguageFactory())); } @Override Added: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/LOCATEFunctionModifier.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/LOCATEFunctionModifier.java (rev 0) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/LOCATEFunctionModifier.java 2009-08-19 19:50:56 UTC (rev 1264) @@ -0,0 +1,323 @@ +/* + * JBoss, Home of Professional Open Source. + * See the COPYRIGHT.txt file distributed with this work for information + * regarding copyright ownership. Some portions may be licensed + * to Red Hat, Inc. under one or more contributor license agreements. + * + * This library is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 2.1 of the License, or (at your option) any later version. + * + * This library is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public + * License along with this library; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA + * 02110-1301 USA. + */ + +package org.teiid.connector.jdbc.translator; + +import java.util.Arrays; +import java.util.List; + +import org.teiid.connector.jdbc.translator.BasicFunctionModifier; +import org.teiid.connector.jdbc.translator.FunctionModifier; +import org.teiid.connector.language.ICompareCriteria; +import org.teiid.connector.language.IExpression; +import org.teiid.connector.language.IFunction; +import org.teiid.connector.language.ILanguageFactory; +import org.teiid.connector.language.ILiteral; +import org.teiid.connector.language.ICompareCriteria.Operator; + + +/** + * A modifier class that can be used to translate the scalar function + * <code>locate(search_string, source_string)</code> and + * <code>locate(search_string, source_string, start_index)</code> to a function + * or expression that can be used at the data source. + * <p> + * If the default implementation is used, a function name of LOCATE will be used + * for the function name. + * <p> + * If the default implementation is used, the expression will not be modified if: + * <li><code>locate(search_string, source_string)</code> is used</li> + * <li><code>locate(search_string, source_string, start_index)</code> is used + * and <code>start_index</code> is a literal integer greater then 0</li> + * <li>the default function parameter order is used or unspecified</li> + * <p> + * If the default implementation is used, the expression will be modified if: + * <li><code>locate(search_string, source_string, start_index)</code> is used + * and <code>start_index</code> is a literal integer less then 1</li> + * <li><code>locate(search_string, source_string, start_index)</code> is used + * and <code>start_index</code> is not a literal integer</li> + * <li>the function parameter order is something other than the default</li> + * <p> + * If the default implementation is used and the expression is modified, it is + * modified to ensure that any literal integer value less than 1 is made equal + * to 1 and any non literal value is wrapped by a searched case expression + * to ensure that a value of less then 1 will be equal to 1 and the parameter + * order matches that of what the data source expects. + * <p> + * For example: + * <li><code>locate('a', 'abcdef')</code> --> <code>LOCATE('a', 'abcdef')</code></li> + * <li><code>locate('a', 'abcdef', 2)</code> --> <code>LOCATE('a', 'abcdef', 2)</code></li> + * <li><code>locate('a', 'abcdef', 0)</code> --> <code>LOCATE('a', 'abcdef', 1)</code></li> + * <li><code>locate('a', 'abcdef', intCol)</code> --> <code>LOCATE('a', 'abcdef', CASE WHEN intCol < 1 THEN 1 ELSE intCol END)</code></li> + * + * @since 6.2 + */ +public class LOCATEFunctionModifier extends BasicFunctionModifier implements FunctionModifier { + + /** + * An <code>enum</code> that defines the parameter orders that can be used + * with <code>LOCATEFunctionModifier</code> + * + */ + public static enum ParameterOrder { + /** + * Indicates that the parameter order should be consistent with the + * built-in system function <code>LOCATE(searchStr, sourceStr, startIndex)</code>. + */ + DEFAULT, + + /** + * Indicates that the parameter order should be changed from the default + * built-in system function <code>LOCATE(searchStr, sourceStr, startIndex)</code> + * to <code>LOCATE(sourceStr, searchStr, startIndex)</code>. + */ + SOURCE_SEARCH_INDEX, + + /** + * Indicates that the parameter order should be changed from the default + * built-in system function <code>LOCATE(searchStr, sourceStr, startIndex)</code> + * to <code>LOCATE(startIndex, sourceStr, searchStr)</code>. + */ + INDEX_SOURCE_SEARCH + + } + + private ILanguageFactory langFactory; + private String functionName = "LOCATE"; //$NON-NLS-1$ + private ParameterOrder parameterOrder = ParameterOrder.DEFAULT; + private final Integer systemStringIndexBase = 1; + + /** + * Constructs a {@link BasicFunctionModifier} object that can be used to + * translate the scalar function LOCATE() to a source specific scalar + * function or expression. + * <p> + * This constructor invokes {@link #LOCATEFunctionModifier(ILanguageFactory, String, ParameterOrder)} + * passing it <code>langFactory</code>, <code>null</code>, <code>null</code>. + * + * @param langFactory the language factory associated with translation + */ + public LOCATEFunctionModifier(ILanguageFactory langFactory) { + this(langFactory, null, null); + } + + /** + * Constructs a {@link BasicFunctionModifier} object that can be used to + * translate the scalar function LOCATE() to a source specific scalar + * function or expression. + * <p> + * This constructor invokes {@link #LOCATEFunctionModifier(ILanguageFactory, String, ParameterOrder)} + * passing it <code>langFactory</code>, <code>functionName</code>, + * <code>null</code>. + * + * @param langFactory the language factory associated with translation + * @param functionName the function name or alias to be used instead of LOCATE + */ + public LOCATEFunctionModifier(ILanguageFactory langFactory, final String functionName) { + this(langFactory, functionName, null); + } + + /** + * Constructs a {@link BasicFunctionModifier} object that can be used to + * translate the scalar function LOCATE() to a source specific scalar + * function or expression. + * <p> + * <code>functionName</code> should represent the default function name or + * alias used by the data source. If this value is <code>null</code> a + * default value will be used. + * <p> + * <code>paramOrder</code> should represent how the data source's version of + * the LOCATE() function expects its parameters. This value can be any + * supported value offered by {@link ParameterOrder}. If this value is + * <code>null</code> a default value will be used. + * <p> + * + * @param langFactory the language factory associated with translation + * @param functionName the function name or alias to be used instead of + * LOCATE, or <code>null</code> if the default should be used + * @param paramOrder the order in which parameters should be translated, or + * <code>null</code> if the default should be used + */ + public LOCATEFunctionModifier(ILanguageFactory langFactory, final String functionName, final ParameterOrder paramOrder) { + if (functionName != null) this.functionName = functionName; + if (paramOrder != null) this.parameterOrder = paramOrder; + this.langFactory = langFactory; + } + + /** + * Returns a version of <code>function</code> suitable for executing at the + * data source. + * <p> + * First, a default function name or the value specified during construction + * of <code>MODFunctionModifier</code> is set on <code>function</code>. + * <p> + * If <code>function</code> represents <code>LOCATE(searchStr, sourceStr, startIndex)</code> + * and <code>startIndex</code> is a literal value, it is translated for + * consistency between the built-in system function + * <code>LOCATE(searchStr, sourceStr, startIndex)</code> and the sources + * implementation. This is done by calling {@link #getStartIndexExpression(ILiteral)} + * and passing it the literal <code>startIndex</code> value. + * <p> + * If <code>function</code> represents <code>LOCATE(searchStr, sourceStr, startIndex)</code> + * and <code>startIndex</code> is not a literal value, it is translated for + * consistency between the built-in system function + * <code>LOCATE(searchStr, sourceStr, startIndex)</code> and the sources + * implementation. This is done by calling {@link #getStartIndexExpression(IExpression)} + * and passing it the non-literal <code>startIndex</code> value. + * <p> + * Finally, <code>function</code>'s parameters may be rearranged depending + * on the value specified by {@link ParameterOrder} during construction of + * <code>MODFunctionModifier</code>. + * <p> + * The translated <code>function</code> is then returned. + * <p> + * For example: + * <ul> + * <code>locate('a', 'abcdefg') ---> LOCATE('a', 'abcdefg')</code><br /> + * <code>locate('a', 'abcdefg', 1) ---> LOCATE('a', 'abcdefg', 1)</code><br /> + * <code>locate('a', 'abcdefg', 1) ---> INSTR('abcdefg', 'a', 1)</code><br /> + * <code>locate('a', 'abcdefg', -5) ---> INSTR('abcdefg', 'a', 1)</code><br /> + * <code>locate('a', 'abcdefg', 1) ---> FINDSTR('a', 'abcdefg', 1)</code><br /> + * <code>locate('a', 'abcdefg', myCol) ---> LOCATE('a', 'abcdefg', CASE WHEN myCol < 1 THEN 1 ELSE myCol END)</code> + * </ul> + * + * @param function the LOCATE function that may need to be modified + */ + public IExpression modify(IFunction function) { + function.setName(this.functionName); + List<IExpression> args = function.getParameters(); + IExpression searchStr = args.get(0); + IExpression sourceStr = args.get(1); + IExpression startIndex = (args.size() > 2 ? args.get(2) : null); + + // if startIndex was given then we may need to do additional work + if (startIndex != null) { + if (startIndex instanceof ILiteral) { + startIndex = this.getStartIndexExpression((ILiteral)startIndex); + } else { + startIndex = this.getStartIndexExpression((IExpression)startIndex); + } + } + switch (this.parameterOrder) { + case SOURCE_SEARCH_INDEX: + args.set(0, sourceStr); + args.set(1, searchStr); + if (startIndex != null) args.set(2, startIndex); + break; + case INDEX_SOURCE_SEARCH: + if (startIndex != null) args.set(0, startIndex); + args.set(1, sourceStr); + args.set(2, searchStr); + break; + case DEFAULT: + args.set(0, searchStr); + args.set(1, sourceStr); + if (startIndex != null) args.set(2, startIndex); + break; + } + return function; + } + + /** + * Return an expression that represents <code>startIndex</code> rewritten to + * be consistent with the built-in system function's + * <code>LOCATE(searchStr, sourceStr, startIndex)</code> <code>startIndex</code> + * parameter. + * <p> + * <code>startIndex</code> represents the unmodified parameter as passed to + * the <code>LOCATE(searchStr, sourceStr, startIndex)</code> function. The + * returned value will represent a normalized version of the expression that + * is consistent to the built-in system function. For example, a value for + * <code>startIndex</code> should not be less than <code>1</code>. + * <p> + * If this method is not overriden, the result will be: + * <p> + * <ul>If <code>startIndex</code> is <code>null</code>, <code>startIndex</code></ul> + * <ul>If <code>startIndex</code> is not <code>null</code> and its value is + * less than <code>1</code>, <code>1</code></ul> + * + * @param startIndex an expression representing the <code>startIndex</code> + * parameter used in <code>LOCATE(searchStr, sourceStr, startIndex)</code> + * @return an expression that represents a normalized <code>startIndex</code> + */ + protected IExpression getStartIndexExpression(ILiteral startIndex) { + if (startIndex.getValue() != null) { + if ((Integer)startIndex.getValue() < this.getSystemStringIndexBase()) { + startIndex.setValue(this.getSystemStringIndexBase()); + } + } + return startIndex; + } + + /** + * Return an expression that represents <code>startIndex</code> rewritten to + * be consistent with the built-in system function's + * <code>LOCATE(searchStr, sourceStr, startIndex)</code> <code>startIndex</code> + * parameter. + * <p> + * <code>startIndex</code> represents the unmodified parameter as passed to + * the <code>LOCATE(searchStr, sourceStr, startIndex)</code> function. The + * returned value will represent a normalized version of the expression that + * is consistent to the built-in system function. For example, a value for + * <code>startIndex</code> should not be less than <code>1</code>. + * <p> + * If this method is not overriden, the result will be: + * <p> + * <ul><code>CASE WHEN &lt;startIndex&gt; &lt; 1; THEN 1; ELSE &lt;startIndex&gt; END</code></ul> + * <p> + * For the default searched case expression to work, the source must support + * searched case. + * + * @param startIndex an expression representing the <code>startIndex</code> + * parameter used in <code>LOCATE(searchStr, sourceStr, startIndex)</code> + * @return an expression that represents a normalized <code>startIndex</code> + */ + protected IExpression getStartIndexExpression(IExpression startIndex) { + ICompareCriteria[] whenExpr = {langFactory.createCompareCriteria( + Operator.LT, + startIndex, + langFactory.createLiteral(this.getSystemStringIndexBase(), Integer.class) + )}; + ILiteral[] thenExpr = {langFactory.createLiteral(this.getSystemStringIndexBase(), Integer.class)}; + return langFactory.createSearchedCaseExpression(Arrays.asList(whenExpr), Arrays.asList(thenExpr), startIndex, Integer.class); + } + + /** + * Get the string index base used by built-in system functions. The value + * represents what is considered the first character of a string. + * + * @return the data source's string index base + */ + protected Integer getSystemStringIndexBase() { + return this.systemStringIndexBase; + } + + /** + * Get the instance of {@link ILanguageFactory} set during construction. + * + * @return the <code>ILanguageFactory</code> instance + */ + protected ILanguageFactory getLanguageFactory() { + return this.langFactory; + } + +} Property changes on: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/LOCATEFunctionModifier.java ___________________________________________________________________ Name: svn:mime-type + text/plain Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/db2/TestDB2SqlTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/db2/TestDB2SqlTranslator.java 2009-08-19 15:20:26 UTC (rev 1263) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/db2/TestDB2SqlTranslator.java 2009-08-19 19:50:56 UTC (rev 1264) @@ -30,6 +30,7 @@ import org.junit.Test; import org.teiid.connector.api.ConnectorException; import org.teiid.connector.api.ExecutionContext; +import org.teiid.connector.jdbc.MetadataFactory; import org.teiid.connector.jdbc.translator.TranslatedCommand; import org.teiid.connector.language.ICommand; @@ -117,5 +118,123 @@ output); } + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate() throws Exception { + String input = "SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT LOCATE(char(SmallA.IntNum), 'chimp', 1) FROM SmallA"; //$NON-NLS-1$ + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate2() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT LOCATE(SmallA.StringNum, 'chimp') FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26</code> + * + * @throws Exception + */ + @Test public void testLocate3() throws Exception { + String input = "SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26"; //$NON-NLS-1$ + String output = "SELECT LOCATE(char(SmallA.IntNum), '234567890', 1) FROM SmallA WHERE SmallA.IntKey = 26"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate('c', 'chimp', 1) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate4() throws Exception { + String input = "SELECT locate('c', 'chimp', 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT 1 FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate5() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT LOCATE(SmallA.StringNum, 'chimp', 1) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp', INTNUM) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate6() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', INTNUM) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT LOCATE(SmallA.StringNum, 'chimp', CASE WHEN SmallA.IntNum < 1 THEN 1 ELSE SmallA.IntNum END) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp', LOCATE(STRINGNUM, 'chimp') + 1) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate7() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', LOCATE(STRINGNUM, 'chimp') + 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT LOCATE(SmallA.StringNum, 'chimp', CASE WHEN (LOCATE(SmallA.StringNum, 'chimp') + 1) < 1 THEN 1 ELSE (LOCATE(SmallA.StringNum, 'chimp') + 1) END) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + } Added: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/derby/TestDerbySQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/derby/TestDerbySQLTranslator.java (rev 0) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/derby/TestDerbySQLTranslator.java 2009-08-19 19:50:56 UTC (rev 1264) @@ -0,0 +1,261 @@ +/* + * JBoss, Home of Professional Open Source. + * See the COPYRIGHT.txt file distributed with this work for information + * regarding copyright ownership. Some portions may be licensed + * to Red Hat, Inc. under one or more contributor license agreements. + * + * This library is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 2.1 of the License, or (at your option) any later version. + * + * This library is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public + * License along with this library; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA + * 02110-1301 USA. + */ + +package org.teiid.connector.jdbc.derby; + +import static org.junit.Assert.assertEquals; + +import java.util.Properties; + +import org.junit.BeforeClass; +import org.junit.Test; +import org.teiid.connector.api.ConnectorException; +import org.teiid.connector.api.ExecutionContext; +import org.teiid.connector.jdbc.MetadataFactory; +import org.teiid.connector.jdbc.translator.TranslatedCommand; +import org.teiid.connector.language.ICommand; + +import com.metamatrix.cdk.api.EnvironmentUtility; +import com.metamatrix.cdk.api.TranslationUtility; +import com.metamatrix.cdk.unittest.FakeTranslationFactory; +import com.metamatrix.core.util.UnitTestUtil; + +/** + */ +public class TestDerbySQLTranslator { + + private static DerbySQLTranslator TRANSLATOR; + + @BeforeClass + public static void setUp() throws ConnectorException { + TRANSLATOR = new DerbySQLTranslator(); + TRANSLATOR.initialize(EnvironmentUtility.createEnvironment(new Properties(), false)); + } + + public String getTestVDB() { + return UnitTestUtil.getTestDataPath() + "/PartsSupplier.vdb"; //$NON-NLS-1$ + } + + public void helpTestVisitor(TranslationUtility util, String input, String expectedOutput) throws ConnectorException { + // Convert from sql to objects + ICommand obj = util.parseCommand(input); + + ExecutionContext context = EnvironmentUtility.createSecurityContext("user"); //$NON-NLS-1$ + + TranslatedCommand tc = new TranslatedCommand(context, TRANSLATOR); + tc.translateCommand(obj); + + assertEquals("Did not get correct sql", expectedOutput, tc.getSql()); //$NON-NLS-1$ + } + + @Test + public void testRowLimit() throws Exception { + String input = "select intkey from bqt1.smalla limit 100"; //$NON-NLS-1$ + String output = "SELECT SmallA.IntKey FROM SmallA FETCH FIRST 100 ROWS ONLY"; //$NON-NLS-1$ + + helpTestVisitor(FakeTranslationFactory.getInstance().getBQTTranslationUtility(), + input, + output); + } + + @Test + public void testCrossJoin() throws Exception{ + String input = "SELECT bqt1.smalla.stringkey FROM bqt1.smalla cross join bqt1.smallb"; //$NON-NLS-1$ + String output = "SELECT SmallA.StringKey FROM SmallA INNER JOIN SmallB ON 1 = 1"; //$NON-NLS-1$ + + helpTestVisitor(FakeTranslationFactory.getInstance().getBQTTranslationUtility(), + input, + output); + } + + @Test + public void testConcat_useLiteral() throws Exception { + String input = "select concat(stringnum,'_xx') from BQT1.Smalla"; //$NON-NLS-1$ + String output = "SELECT {fn concat(SmallA.StringNum, '_xx')} FROM SmallA"; //$NON-NLS-1$ + + helpTestVisitor(FakeTranslationFactory.getInstance().getBQTTranslationUtility(), + input, + output); + + } + + @Test + public void testConcat() throws Exception { + String input = "select concat(stringnum, stringnum) from BQT1.Smalla"; //$NON-NLS-1$ + String output = "SELECT {fn concat(SmallA.StringNum, SmallA.StringNum)} FROM SmallA"; //$NON-NLS-1$ + + helpTestVisitor(FakeTranslationFactory.getInstance().getBQTTranslationUtility(), + input, + output); + } + + @Test + public void testConcat2_useLiteral() throws Exception { + String input = "select concat2(stringnum,'_xx') from BQT1.Smalla"; //$NON-NLS-1$ + String output = "SELECT {fn concat(coalesce(SmallA.StringNum, ''), '_xx')} FROM SmallA"; //$NON-NLS-1$ + + helpTestVisitor(FakeTranslationFactory.getInstance().getBQTTranslationUtility(), + input, + output); + + } + + @Test + public void testConcat2() throws Exception { + String input = "select concat2(stringnum, stringnum) from BQT1.Smalla"; //$NON-NLS-1$ + String output = "SELECT CASE WHEN SmallA.StringNum IS NULL THEN NULL ELSE {fn concat(coalesce(SmallA.StringNum, ''), coalesce(SmallA.StringNum, ''))} END FROM SmallA"; //$NON-NLS-1$ + + helpTestVisitor(FakeTranslationFactory.getInstance().getBQTTranslationUtility(), + input, + output); + } + + @Test + public void testSelectNullLiteral() throws Exception { + String input = "select null + 1 as x, null || 'a' from BQT1.Smalla"; //$NON-NLS-1$ + String output = "SELECT CAST(NULL AS INTEGER) AS x, CAST(NULL AS CHAR) FROM SmallA"; //$NON-NLS-1$ + + helpTestVisitor(FakeTranslationFactory.getInstance().getBQTTranslationUtility(), + input, + output); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate() throws Exception { + String input = "SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT LOCATE(char(SmallA.IntNum), 'chimp', 1) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate2() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT LOCATE(SmallA.StringNum, 'chimp') FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26</code> + * + * @throws Exception + */ + @Test public void testLocate3() throws Exception { + String input = "SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26"; //$NON-NLS-1$ + String output = "SELECT LOCATE(char(SmallA.IntNum), '234567890', 1) FROM SmallA WHERE SmallA.IntKey = 26"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate('c', 'chimp', 1) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate4() throws Exception { + String input = "SELECT locate('c', 'chimp', 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT 1 FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate5() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT LOCATE(SmallA.StringNum, 'chimp', 1) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp', INTNUM) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate6() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', INTNUM) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT LOCATE(SmallA.StringNum, 'chimp', CASE WHEN SmallA.IntNum < 1 THEN 1 ELSE SmallA.IntNum END) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp', LOCATE(STRINGNUM, 'chimp') + 1) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate7() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', LOCATE(STRINGNUM, 'chimp') + 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT LOCATE(SmallA.StringNum, 'chimp', CASE WHEN (LOCATE(SmallA.StringNum, 'chimp') + 1) < 1 THEN 1 ELSE (LOCATE(SmallA.StringNum, 'chimp') + 1) END) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + +} Property changes on: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/derby/TestDerbySQLTranslator.java ___________________________________________________________________ Name: svn:mime-type + text/plain Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/mysql/TestMySQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/mysql/TestMySQLTranslator.java 2009-08-19 15:20:26 UTC (rev 1263) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/mysql/TestMySQLTranslator.java 2009-08-19 19:50:56 UTC (rev 1264) @@ -50,7 +50,7 @@ return MetadataFactory.BQT_VDB; } - @Test public void testRewriteConversion1() throws Exception { + @Test public void testConversion1() throws Exception { String input = "SELECT char(convert(PART_WEIGHT, integer) + 100) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT char((convert(PARTS.PART_WEIGHT, SIGNED INTEGER) + 100)) FROM PARTS"; //$NON-NLS-1$ @@ -59,7 +59,7 @@ output, TRANSLATOR); } - @Test public void testRewriteConversion2() throws Exception { + @Test public void testConversion2() throws Exception { String input = "SELECT convert(PART_WEIGHT, long) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT convert(PARTS.PART_WEIGHT, SIGNED) FROM PARTS"; //$NON-NLS-1$ @@ -68,7 +68,7 @@ output, TRANSLATOR); } - @Test public void testRewriteConversion3() throws Exception { + @Test public void testConversion3() throws Exception { String input = "SELECT convert(convert(PART_WEIGHT, long), string) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT convert(convert(PARTS.PART_WEIGHT, SIGNED), CHAR) FROM PARTS"; //$NON-NLS-1$ @@ -77,7 +77,7 @@ output, TRANSLATOR); } - @Test public void testRewriteConversion4() throws Exception { + @Test public void testConversion4() throws Exception { String input = "SELECT convert(convert(PART_WEIGHT, date), string) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT date_format(DATE(PARTS.PART_WEIGHT), '%Y-%m-%d') FROM PARTS"; //$NON-NLS-1$ @@ -85,7 +85,7 @@ input, output, TRANSLATOR); } - @Test public void testRewriteConversion5() throws Exception { + @Test public void testConversion5() throws Exception { String input = "SELECT convert(convert(PART_WEIGHT, time), string) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT date_format(TIME(PARTS.PART_WEIGHT), '%H:%i:%S') FROM PARTS"; //$NON-NLS-1$ @@ -93,7 +93,7 @@ input, output, TRANSLATOR); } - @Test public void testRewriteConversion6() throws Exception { + @Test public void testConversion6() throws Exception { String input = "SELECT convert(convert(PART_WEIGHT, timestamp), string) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT date_format(TIMESTAMP(PARTS.PART_WEIGHT), '%Y-%m-%d %H:%i:%S.%f') FROM PARTS"; //$NON-NLS-1$ @@ -101,7 +101,7 @@ input, output, TRANSLATOR); } - @Test public void testRewriteConversion8() throws Exception { + @Test public void testConversion8() throws Exception { String input = "SELECT ifnull(PART_WEIGHT, 'otherString') FROM PARTS"; //$NON-NLS-1$ String output = "SELECT ifnull(PARTS.PART_WEIGHT, 'otherString') FROM PARTS"; //$NON-NLS-1$ @@ -109,7 +109,7 @@ input, output, TRANSLATOR); } - @Test public void testRewriteConversion7() throws Exception { + @Test public void testConversion7() throws Exception { String input = "SELECT convert(convert(PART_WEIGHT, integer), string) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT convert(convert(PARTS.PART_WEIGHT, SIGNED INTEGER), CHAR) FROM PARTS"; //$NON-NLS-1$ @@ -117,7 +117,7 @@ input, output, TRANSLATOR); } - @Test public void testRewriteInsert() throws Exception { + @Test public void testInsert() throws Exception { String input = "SELECT insert(PART_WEIGHT, 1, 5, 'chimp') FROM PARTS"; //$NON-NLS-1$ String output = "SELECT insert(PARTS.PART_WEIGHT, 1, 5, 'chimp') FROM PARTS"; //$NON-NLS-1$ @@ -125,15 +125,127 @@ input, output, TRANSLATOR); } - @Test public void testRewriteLocate() throws Exception { - String input = "SELECT locate(PART_WEIGHT, 'chimp', 1) FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT locate(PARTS.PART_WEIGHT, 'chimp', 1) FROM PARTS"; //$NON-NLS-1$ - MetadataFactory.helpTestVisitor(getTestVDB(), - input, - output, TRANSLATOR); + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate() throws Exception { + String input = "SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT LOCATE(convert(SmallA.IntNum, CHAR), 'chimp', 1) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate2() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT LOCATE(SmallA.StringNum, 'chimp') FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26</code> + * + * @throws Exception + */ + @Test public void testLocate3() throws Exception { + String input = "SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26"; //$NON-NLS-1$ + String output = "SELECT LOCATE(convert(SmallA.IntNum, CHAR), '234567890', 1) FROM SmallA WHERE SmallA.IntKey = 26"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate('c', 'chimp', 1) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate4() throws Exception { + String input = "SELECT locate('c', 'chimp', 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT 1 FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate5() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT LOCATE(SmallA.StringNum, 'chimp', 1) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); } - @Test public void testRewriteSubstring1() throws Exception { + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp', INTNUM) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate6() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', INTNUM) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT LOCATE(SmallA.StringNum, 'chimp', CASE WHEN SmallA.IntNum < 1 THEN 1 ELSE SmallA.IntNum END) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp', LOCATE(STRINGNUM, 'chimp') + 1) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate7() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', LOCATE(STRINGNUM, 'chimp') + 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT LOCATE(SmallA.StringNum, 'chimp', CASE WHEN (LOCATE(SmallA.StringNum, 'chimp') + 1) < 1 THEN 1 ELSE (LOCATE(SmallA.StringNum, 'chimp') + 1) END) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + @Test public void testSubstring1() throws Exception { String input = "SELECT substring(PART_WEIGHT, 1) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT substring(PARTS.PART_WEIGHT, 1) FROM PARTS"; //$NON-NLS-1$ @@ -141,7 +253,7 @@ input, output, TRANSLATOR); } - @Test public void testRewriteSubstring2() throws Exception { + @Test public void testSubstring2() throws Exception { String input = "SELECT substring(PART_WEIGHT, 1, 5) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT substring(PARTS.PART_WEIGHT, 1, 5) FROM PARTS"; //$NON-NLS-1$ @@ -149,7 +261,7 @@ input, output, TRANSLATOR); } - @Test public void testRewriteUnionWithOrderBy() throws Exception { + @Test public void testUnionWithOrderBy() throws Exception { String input = "SELECT PART_ID FROM PARTS UNION SELECT PART_ID FROM PARTS ORDER BY PART_ID"; //$NON-NLS-1$ String output = "(SELECT PARTS.PART_ID FROM PARTS) UNION (SELECT PARTS.PART_ID FROM PARTS) ORDER BY PART_ID"; //$NON-NLS-1$ Deleted: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestLocateFunctionModifier.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestLocateFunctionModifier.java 2009-08-19 15:20:26 UTC (rev 1263) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestLocateFunctionModifier.java 2009-08-19 19:50:56 UTC (rev 1264) @@ -1,108 +0,0 @@ -/* - * JBoss, Home of Professional Open Source. - * See the COPYRIGHT.txt file distributed with this work for information - * regarding copyright ownership. Some portions may be licensed - * to Red Hat, Inc. under one or more contributor license agreements. - * - * This library is free software; you can redistribute it and/or - * modify it under the terms of the GNU Lesser General Public - * License as published by the Free Software Foundation; either - * version 2.1 of the License, or (at your option) any later version. - * - * This library is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU - * Lesser General Public License for more details. - * - * You should have received a copy of the GNU Lesser General Public - * License along with this library; if not, write to the Free Software - * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA - * 02110-1301 USA. - */ - -package org.teiid.connector.jdbc.oracle; - -import java.util.Arrays; -import java.util.Properties; - -import org.teiid.connector.jdbc.oracle.LocateFunctionModifier; -import org.teiid.connector.jdbc.oracle.OracleSQLTranslator; -import org.teiid.connector.jdbc.translator.SQLConversionVisitor; -import org.teiid.connector.language.IExpression; -import org.teiid.connector.language.IFunction; -import org.teiid.connector.language.ILanguageFactory; - -import junit.framework.TestCase; - -import com.metamatrix.cdk.CommandBuilder; -import com.metamatrix.cdk.api.EnvironmentUtility; - -/** - */ -public class TestLocateFunctionModifier extends TestCase { - - private static final ILanguageFactory LANG_FACTORY = CommandBuilder.getLanuageFactory(); - - /** - * Constructor for TestLocateFunctionModifier. - * @param name - */ - public TestLocateFunctionModifier(String name) { - super(name); - } - - public IExpression helpTestMod(IExpression[] args, String expectedStr) throws Exception { - IFunction func = LANG_FACTORY.createFunction("hour", //$NON-NLS-1$ - Arrays.asList(args), - Integer.class); - - LocateFunctionModifier mod = new LocateFunctionModifier(LANG_FACTORY); - IExpression expr = mod.modify(func); - - OracleSQLTranslator trans = new OracleSQLTranslator(); - trans.initialize(EnvironmentUtility.createEnvironment(new Properties(), false)); - - SQLConversionVisitor sqlVisitor = trans.getSQLConversionVisitor(); - sqlVisitor.append(expr); - - assertEquals(expectedStr, sqlVisitor.toString()); - - return expr; - } - - public void testTwoArgs() throws Exception { - IExpression[] args = new IExpression[] { - LANG_FACTORY.createLiteral(".", String.class), //$NON-NLS-1$ - LANG_FACTORY.createLiteral("a.b.c", String.class) //$NON-NLS-1$ - }; - helpTestMod(args, "instr('a.b.c', '.')"); //$NON-NLS-1$ - } - - public void testThreeArgsWithConstant() throws Exception { - IExpression[] args = new IExpression[] { - LANG_FACTORY.createLiteral(".", String.class), //$NON-NLS-1$ - LANG_FACTORY.createLiteral("a.b.c", String.class), //$NON-NLS-1$ - LANG_FACTORY.createLiteral(new Integer(2), Integer.class) - }; - helpTestMod(args, "instr('a.b.c', '.', 3)"); //$NON-NLS-1$ - } - - public void testThreeArgsWithElement() throws Exception { - IExpression[] args = new IExpression[] { - LANG_FACTORY.createLiteral(".", String.class), //$NON-NLS-1$ - LANG_FACTORY.createLiteral("a.b.c", String.class), //$NON-NLS-1$ - LANG_FACTORY.createElement("e1", null, null, Integer.class) //$NON-NLS-1$ - }; - helpTestMod(args, "instr('a.b.c', '.', (e1 + 1))"); //$NON-NLS-1$ - } - - public void testThreeArgsWithNull() throws Exception { - IExpression[] args = new IExpression[] { - LANG_FACTORY.createLiteral(".", String.class), //$NON-NLS-1$ - LANG_FACTORY.createLiteral("a.b.c", String.class), //$NON-NLS-1$ - LANG_FACTORY.createLiteral(null, Integer.class) - }; - helpTestMod(args, "instr('a.b.c', '.', NULL)"); //$NON-NLS-1$ - } - -} Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java 2009-08-19 15:20:26 UTC (rev 1263) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java 2009-08-19 19:50:56 UTC (rev 1264) @@ -28,7 +28,6 @@ import org.junit.AfterClass; import org.junit.BeforeClass; -import org.junit.Ignore; import org.junit.Test; import org.teiid.connector.api.ConnectorException; import org.teiid.connector.jdbc.MetadataFactory; @@ -183,34 +182,66 @@ input, output, TRANSLATOR); } - @Ignore("TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is used as is") + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ @Test public void testLocate() throws Exception { - // TODO TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is used as is String input = "SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ - String output = "SELECT instr('chimp', to_char(SmallA.IntNum), 1) FROM SmallA"; //$NON-NLS-1$ + String output = "SELECT INSTR('chimp', to_char(SmallA.IntNum), 1) FROM SmallA"; //$NON-NLS-1$ MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, input, output, TRANSLATOR); } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA</code> + * + * @throws Exception + */ @Test public void testLocate2() throws Exception { String input = "SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA"; //$NON-NLS-1$ - String output = "SELECT instr('chimp', SmallA.StringNum) FROM SmallA"; //$NON-NLS-1$ + String output = "SELECT INSTR('chimp', SmallA.StringNum) FROM SmallA"; //$NON-NLS-1$ MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, input, output, TRANSLATOR); } - @Ignore("TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is used as is") + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26</code> + * + * @throws Exception + */ @Test public void testLocate3() throws Exception { - // TODO TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is used as is String input = "SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26"; //$NON-NLS-1$ - String output = "SELECT instr('234567890', to_char(SmallA.IntNum), 1) FROM SmallA WHERE SmallA.IntKey = 26"; //$NON-NLS-1$ + String output = "SELECT INSTR('234567890', to_char(SmallA.IntNum), 1) FROM SmallA WHERE SmallA.IntKey = 26"; //$NON-NLS-1$ MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, input, output, TRANSLATOR); } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate('c', 'chimp', 1) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ @Test public void testLocate4() throws Exception { String input = "SELECT locate('c', 'chimp', 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT 1 FROM SmallA"; //$NON-NLS-1$ @@ -219,16 +250,58 @@ input, output, TRANSLATOR); } - @Ignore("TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is 1 if a value of < 1 is given") + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ @Test public void testLocate5() throws Exception { - // TODO TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is 1 if a value of < 1 is given String input = "SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA"; //$NON-NLS-1$ - String output = "SELECT instr('chimp', SmallA.StringNum, 1) FROM SmallA"; //$NON-NLS-1$ + String output = "SELECT INSTR('chimp', SmallA.StringNum, 1) FROM SmallA"; //$NON-NLS-1$ MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, input, output, TRANSLATOR); } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp', INTNUM) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate6() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', INTNUM) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT INSTR('chimp', SmallA.StringNum, CASE WHEN SmallA.IntNum < 1 THEN 1 ELSE SmallA.IntNum END) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp', LOCATE(STRINGNUM, 'chimp') + 1) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate7() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', LOCATE(STRINGNUM, 'chimp') + 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT INSTR('chimp', SmallA.StringNum, CASE WHEN (INSTR('chimp', SmallA.StringNum) + 1) < 1 THEN 1 ELSE (INSTR('chimp', SmallA.StringNum) + 1) END) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + @Test public void testSubstring1() throws Exception { String input = "SELECT substring(StringNum, 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT substr(SmallA.StringNum, 1) FROM SmallA"; //$NON-NLS-1$ Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/postgresql/TestPostgreSQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/postgresql/TestPostgreSQLTranslator.java 2009-08-19 15:20:26 UTC (rev 1263) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/postgresql/TestPostgreSQLTranslator.java 2009-08-19 19:50:56 UTC (rev 1264) @@ -52,7 +52,7 @@ MetadataFactory.helpTestVisitor(vdb, input, expectedOutput, TRANSLATOR); } - @Test public void testRewriteConversion1() throws Exception { + @Test public void testConversion1() throws Exception { String input = "SELECT char(convert(PART_WEIGHT, integer) + 100) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT chr((cast(PARTS.PART_WEIGHT AS integer) + 100)) FROM PARTS"; //$NON-NLS-1$ @@ -61,7 +61,7 @@ output); } - @Test public void testRewriteConversion2() throws Exception { + @Test public void testConversion2() throws Exception { String input = "SELECT convert(PART_WEIGHT, long) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT cast(PARTS.PART_WEIGHT AS bigint) FROM PARTS"; //$NON-NLS-1$ @@ -70,7 +70,7 @@ output); } - @Test public void testRewriteConversion3() throws Exception { + @Test public void testConversion3() throws Exception { String input = "SELECT convert(PART_WEIGHT, short) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT cast(PARTS.PART_WEIGHT AS smallint) FROM PARTS"; //$NON-NLS-1$ @@ -79,7 +79,7 @@ output); } - @Test public void testRewriteConversion4() throws Exception { + @Test public void testConversion4() throws Exception { String input = "SELECT convert(PART_WEIGHT, float) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT cast(PARTS.PART_WEIGHT AS real) FROM PARTS"; //$NON-NLS-1$ @@ -87,7 +87,7 @@ input, output); } - @Test public void testRewriteConversion5() throws Exception { + @Test public void testConversion5() throws Exception { String input = "SELECT convert(PART_WEIGHT, double) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT cast(PARTS.PART_WEIGHT AS float8) FROM PARTS"; //$NON-NLS-1$ @@ -95,7 +95,7 @@ input, output); } - @Test public void testRewriteConversion6() throws Exception { + @Test public void testConversion6() throws Exception { String input = "SELECT convert(PART_WEIGHT, biginteger) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT cast(PARTS.PART_WEIGHT AS numeric) FROM PARTS"; //$NON-NLS-1$ @@ -103,7 +103,7 @@ input, output); } - @Test public void testRewriteConversion7() throws Exception { + @Test public void testConversion7() throws Exception { String input = "SELECT convert(PART_WEIGHT, bigdecimal) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT cast(PARTS.PART_WEIGHT AS decimal) FROM PARTS"; //$NON-NLS-1$ @@ -111,7 +111,7 @@ input, output); } - @Test public void testRewriteConversion8() throws Exception { + @Test public void testConversion8() throws Exception { String input = "SELECT convert(PART_WEIGHT, boolean) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT cast(PARTS.PART_WEIGHT AS boolean) FROM PARTS"; //$NON-NLS-1$ @@ -119,7 +119,7 @@ input, output); } - @Test public void testRewriteConversion9() throws Exception { + @Test public void testConversion9() throws Exception { String input = "SELECT convert(PART_WEIGHT, date) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD') FROM PARTS"; //$NON-NLS-1$ @@ -127,7 +127,7 @@ input, output); } - @Test public void testRewriteConversion10() throws Exception { + @Test public void testConversion10() throws Exception { String input = "SELECT convert(PART_WEIGHT, time) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT to_timestamp(('1970-01-01 ' || PARTS.PART_WEIGHT), 'YYYY-MM-DD HH24:MI:SS') FROM PARTS"; //$NON-NLS-1$ @@ -135,7 +135,7 @@ input, output); } - @Test public void testRewriteConversion11() throws Exception { + @Test public void testConversion11() throws Exception { String input = "SELECT convert(PART_WEIGHT, timestamp) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF') FROM PARTS"; //$NON-NLS-1$ @@ -143,7 +143,7 @@ input, output); } - @Test public void testRewriteConversion12() throws Exception { + @Test public void testConversion12() throws Exception { String input = "SELECT convert(convert(PART_WEIGHT, time), string) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT to_char(to_timestamp(('1970-01-01 ' || PARTS.PART_WEIGHT), 'YYYY-MM-DD HH24:MI:SS'), 'HH24:MI:SS') FROM PARTS"; //$NON-NLS-1$ @@ -151,7 +151,7 @@ input, output); } - @Test public void testRewriteConversion13() throws Exception { + @Test public void testConversion13() throws Exception { String input = "SELECT convert(convert(PART_WEIGHT, timestamp), string) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT to_char(to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF'), 'YYYY-MM-DD HH24:MI:SS.US') FROM PARTS"; //$NON-NLS-1$ @@ -159,7 +159,7 @@ input, output); } - @Test public void testRewriteConversion14() throws Exception { + @Test public void testConversion14() throws Exception { String input = "SELECT convert(convert(PART_WEIGHT, date), string) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT to_char(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD'), 'YYYY-MM-DD') FROM PARTS"; //$NON-NLS-1$ @@ -167,7 +167,7 @@ input, output); } - @Test public void testRewriteConversion15() throws Exception { + @Test public void testConversion15() throws Exception { String input = "SELECT convert(convert(PART_WEIGHT, timestamp), date) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT cast(to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF') AS date) FROM PARTS"; //$NON-NLS-1$ @@ -175,7 +175,7 @@ input, output); } - @Test public void testRewriteConversion16() throws Exception { + @Test public void testConversion16() throws Exception { String input = "SELECT convert(convert(PART_WEIGHT, timestamp), time) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT cast(to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF') AS time) FROM PARTS"; //$NON-NLS-1$ @@ -183,7 +183,7 @@ input, output); } - @Test public void testRewriteConversion17() throws Exception { + @Test public void testConversion17() throws Exception { String input = "SELECT convert(convert(PART_WEIGHT, time), timestamp) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT to_timestamp(to_char(to_timestamp(('1970-01-01 ' || PARTS.PART_WEIGHT), 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') FROM PARTS"; //$NON-NLS-1$ @@ -191,7 +191,7 @@ input, output); } - @Test public void testRewriteConversion18() throws Exception { + @Test public void testConversion18() throws Exception { String input = "SELECT convert(convert(PART_WEIGHT, date), timestamp) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT to_timestamp(to_char(to_date(PARTS.PART_WEIGHT, 'YYYY-MM-DD'), 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') FROM PARTS"; //$NON-NLS-1$ @@ -199,7 +199,7 @@ input, output); } - @Test public void testRewriteConversion19() throws Exception { + @Test public void testConversion19() throws Exception { String input = "SELECT convert(convert(PART_WEIGHT, boolean), string) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT CASE WHEN cast(PARTS.PART_WEIGHT AS boolean) = TRUE THEN '1' ELSE '0' END FROM PARTS"; //$NON-NLS-1$ @@ -208,7 +208,7 @@ output); } - @Test public void testRewriteLog() throws Exception { + @Test public void testLog() throws Exception { String input = "SELECT log(convert(PART_WEIGHT, double)) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT ln(cast(PARTS.PART_WEIGHT AS float8)) FROM PARTS"; //$NON-NLS-1$ @@ -223,7 +223,7 @@ output); } - @Test public void testRewriteLeft() throws Exception { + @Test public void testLeft() throws Exception { String input = "SELECT left(PART_WEIGHT, 2) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT SUBSTR(PARTS.PART_WEIGHT, 1, 2) FROM PARTS"; //$NON-NLS-1$ @@ -231,7 +231,7 @@ input, output); } - @Test public void testRewriteRight() throws Exception { + @Test public void testRight() throws Exception { String input = "SELECT right(PART_WEIGHT, 2) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT SUBSTR(PARTS.PART_WEIGHT, (-1 * 2)) FROM PARTS"; //$NON-NLS-1$ @@ -394,13 +394,123 @@ output); } + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ @Test public void testLocate() throws Exception { - String input = "select locate('a', stringkey), locate('b', stringkey, 2) from bqt1.smalla"; //$NON-NLS-1$ - String output = "SELECT position('a' in SmallA.StringKey), position('b' in substr(SmallA.StringKey, 2)) FROM SmallA"; //$NON-NLS-1$ - - helpTestVisitor(getTestBQTVDB(), - input, - output); + String input = "SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT position(cast(SmallA.IntNum AS varchar) in substr('chimp', 1)) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate2() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT position(SmallA.StringNum in 'chimp') FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26</code> + * + * @throws Exception + */ + @Test public void testLocate3() throws Exception { + String input = "SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26"; //$NON-NLS-1$ + String output = "SELECT position(cast(SmallA.IntNum AS varchar) in substr('234567890', 1)) FROM SmallA WHERE SmallA.IntKey = 26"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate('c', 'chimp', 1) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate4() throws Exception { + String input = "SELECT locate('c', 'chimp', 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT 1 FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate5() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT position(SmallA.StringNum in substr('chimp', 1)) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp', INTNUM) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate6() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', INTNUM) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT position(SmallA.StringNum in substr('chimp', CASE WHEN SmallA.IntNum < 1 THEN 1 ELSE SmallA.IntNum END)) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test the translator's ability to rewrite the LOCATE() function in a form + * suitable for the data source. + * <p> + * <code>SELECT locate(STRINGNUM, 'chimp', LOCATE(STRINGNUM, 'chimp') + 1) FROM BQT1.SMALLA</code> + * + * @throws Exception + */ + @Test public void testLocate7() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', LOCATE(STRINGNUM, 'chimp') + 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT position(SmallA.StringNum in substr('chimp', CASE WHEN (position(SmallA.StringNum in 'chimp') + 1) < 1 THEN 1 ELSE (position(SmallA.StringNum in 'chimp') + 1) END)) FROM SmallA"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } } Added: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/translator/TestLOCATEFunctionModifier.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/translator/TestLOCATEFunctionModifier.java (rev 0) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/translator/TestLOCATEFunctionModifier.java 2009-08-19 19:50:56 UTC (rev 1264) @@ -0,0 +1,413 @@ +/* + * JBoss, Home of Professional Open Source. + * See the COPYRIGHT.txt file distributed with this work for information + * regarding copyright ownership. Some portions may be licensed + * to Red Hat, Inc. under one or more contributor license agreements. + * + * This library is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 2.1 of the License, or (at your option) any later version. + * + * This library is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public + * License along with this library; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA + * 02110-1301 USA. + */ + +package org.teiid.connector.jdbc.translator; + +import static org.junit.Assert.*; + +import java.util.Arrays; +import java.util.Properties; + +import org.junit.Test; +import org.teiid.connector.api.ConnectorEnvironment; +import org.teiid.connector.api.ConnectorException; +import org.teiid.connector.api.SourceSystemFunctions; +import org.teiid.connector.jdbc.translator.LOCATEFunctionModifier.ParameterOrder; +import org.teiid.connector.language.IExpression; +import org.teiid.connector.language.IFunction; +import org.teiid.connector.language.ILanguageFactory; +import org.teiid.connector.language.ILiteral; + +import com.metamatrix.cdk.CommandBuilder; +import com.metamatrix.cdk.api.EnvironmentUtility; + +/** + * Test <code>LOCATEFunctionModifier</code> by invoking its methods with varying + * parameters to validate it performs as designed and expected. + */ +public class TestLOCATEFunctionModifier { + + private static final ILanguageFactory LANG_FACTORY = CommandBuilder.getLanuageFactory(); + + /** + * Create an expression containing a LOCATE function using <code>args</code> + * and pass it to the <code>Translator</code>'s LOCATE function modifier and + * compare the resulting expression to <code>expectedStr</code>. + * + * @param args An array of <code>IExpression</code>'s to use as the + * arguments to the LOCATE() function + * @param expectedStr A string representing the modified expression + * @return On success, the modified expression. + * @throws Exception + */ + public IExpression helpTestLocate(IExpression[] args, String expectedStr) throws Exception { + return this.helpTestLocate(null, null, args, expectedStr); + } + + /** + * Create an expression containing a LOCATE function using a function name of + * <code>locateFunctionName</code> with the parameter order of + * <code>parameterOrder</code> and a string index base of + * <code>stringIndexBase</code> and uses the arguments <code>args</code> and + * pass it to the <code>Translator</code>'s LOCATE function modifier and + * compare the resulting expression to <code>expectedStr</code>. + * + * @param locateFunctionName the name to use for the function modifier + * @param parameterOrder an <code>enum</code> value as defined by + * {@link ParameterOrder} which represents the parameter order to use + * for the modified LOCATE() function + * @param stringIndexBase the string index that represents the first character of a string + * @param args an array of <code>IExpression</code>'s to use as the + * arguments to the LOCATE() function + * @param expectedStr A string representing the modified expression + * @return On success, the modified expression. + * @throws Exception + */ + public IExpression helpTestLocate(final String locateFunctionName, final ParameterOrder parameterOrder, IExpression[] args, String expectedStr) throws Exception { + IExpression param1 = null; + IExpression param2 = null; + IExpression param3 = null; + + if (args.length > 0 ) param1 = args[0]; + if (args.length > 1 ) param2 = args[1]; + if (args.length > 2 ) param3 = args[2]; + + IFunction func = null; + + if (param3 != null) { + func = LANG_FACTORY.createFunction(SourceSystemFunctions.LOCATE, + Arrays.asList(param1, param2, param3), Integer.class); + } else { + func = LANG_FACTORY.createFunction(SourceSystemFunctions.LOCATE, + Arrays.asList(param1, param2), Integer.class); + } + + Translator trans = new Translator() { + @Override + public void initialize(ConnectorEnvironment env) + throws ConnectorException { + super.initialize(env); + registerFunctionModifier(SourceSystemFunctions.LOCATE, new LOCATEFunctionModifier(getLanguageFactory(), locateFunctionName, parameterOrder)); + } + }; + + trans.initialize(EnvironmentUtility.createEnvironment(new Properties(), false)); + + IExpression expr = trans.getFunctionModifiers().get(SourceSystemFunctions.LOCATE).modify(func); + + SQLConversionVisitor sqlVisitor = trans.getSQLConversionVisitor(); + sqlVisitor.append(expr); + + assertEquals("Modified function does not match", expectedStr, sqlVisitor.toString()); //$NON-NLS-1$ + + return expr; + } + + /** + * Test {@link LOCATEFunctionModifier#modify(IFunction)} to validate a call + * to LOCATE(search_str, source_str) using constants for both parameters + * returns LOCATE(search_str, source_str). + * <p> + * {@link LOCATEFunctionModifier} will be constructed without specifying a + * function name or parameter order. + * + * @throws Exception + */ + @Test public void testModifySimple() throws Exception { + IExpression[] args = new IExpression[] { + LANG_FACTORY.createLiteral("a", String.class), //$NON-NLS-1$ + LANG_FACTORY.createLiteral("abcdefg", String.class) //$NON-NLS-1$ + }; + // default / default + helpTestLocate(args, "LOCATE('a', 'abcdefg')"); //$NON-NLS-1$ + } + + /** + * Test {@link LOCATEFunctionModifier#modify(IFunction)} to validate a call + * to LOCATE(search_str, source_str) using constants for both parameters + * returns locate(search_str, source_str). + * <p> + * {@link LOCATEFunctionModifier} will be constructed specifying a function + * name of locate but no parameter order. + * + * @throws Exception + */ + @Test public void testModifySimple2() throws Exception { + IExpression[] args = new IExpression[] { + LANG_FACTORY.createLiteral("a", String.class), //$NON-NLS-1$ + LANG_FACTORY.createLiteral("abcdefg", String.class) //$NON-NLS-1$ + }; + // locate / default + helpTestLocate("locate", null, args, "locate('a', 'abcdefg')"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + /** + * Test {@link LOCATEFunctionModifier#modify(IFunction)} to validate a call + * to LOCATE(search_str, source_str) using constants for both parameters + * returns INSTR(source_str, search_str). + * <p> + * {@link LOCATEFunctionModifier} will be constructed specifying a function + * name of INSTR and a parameter order of {@link ParameterOrder#SOURCE_SEARCH_INDEX}. + * + * @throws Exception + */ + @Test public void testModifySimple3() throws Exception { + IExpression[] args = new IExpression[] { + LANG_FACTORY.createLiteral("a", String.class), //$NON-NLS-1$ + LANG_FACTORY.createLiteral("abcdefg", String.class) //$NON-NLS-1$ + }; + // INSTR / SOURCE_SEARCH_INDEX + helpTestLocate("INSTR", ParameterOrder.SOURCE_SEARCH_INDEX, args, "INSTR('abcdefg', 'a')"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + /** + * Test {@link LOCATEFunctionModifier#modify(IFunction)} to validate a call + * to LOCATE(search_str, source_str) using constants for both parameters + * returns locate(search_str, source_str). + * <p> + * {@link LOCATEFunctionModifier} will be constructed specifying a function + * name of locate and a parameter order of {@link ParameterOrder#DEFAULT}. + * + * @throws Exception + */ + @Test public void testModifySimple4() throws Exception { + IExpression[] args = new IExpression[] { + LANG_FACTORY.createLiteral("a", String.class), //$NON-NLS-1$ + LANG_FACTORY.createLiteral("abcdefg", String.class) //$NON-NLS-1$ + }; + // locate / DEFAULT + helpTestLocate("locate", ParameterOrder.DEFAULT, args, "locate('a', 'abcdefg')"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + /** + * Test {@link LOCATEFunctionModifier#modify(IFunction)} to validate a call + * to LOCATE(search_str, source_str, 1) using constants for all parameters + * returns INSTR(source_str, search_str, 1). + * <p> + * {@link LOCATEFunctionModifier} will be constructed specifying a function + * name of INSTR and a parameter order of {@link ParameterOrder#SOURCE_SEARCH_INDEX}. + * + * @throws Exception + */ + @Test public void testModifyWithStartIndex() throws Exception { + IExpression[] args = new IExpression[] { + LANG_FACTORY.createLiteral("a", String.class), //$NON-NLS-1$ + LANG_FACTORY.createLiteral("abcdefg", String.class), //$NON-NLS-1$ + LANG_FACTORY.createLiteral(1, Integer.class) + }; + // INSTR / SOURCE_SEARCH_INDEX + helpTestLocate("INSTR", ParameterOrder.SOURCE_SEARCH_INDEX, args, "INSTR('abcdefg', 'a', 1)"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + /** + * Test {@link LOCATEFunctionModifier#modify(IFunction)} to validate a call + * to LOCATE(search_str, source_str, 4) using constants for all parameters + * returns LOCATE(search_str, source_str, 5). + * <p> + * {@link LOCATEFunctionModifier} will be constructed specifying no function + * name or parameter order. + * + * @throws Exception + */ + @Test public void testModifyWithStartIndex2() throws Exception { + IExpression[] args = new IExpression[] { + LANG_FACTORY.createLiteral("a", String.class), //$NON-NLS-1$ + LANG_FACTORY.createLiteral("abcdefg", String.class), //$NON-NLS-1$ + LANG_FACTORY.createLiteral(4, Integer.class) + }; + // default / default + helpTestLocate(null, null, args, "LOCATE('a', 'abcdefg', 4)"); //$NON-NLS-1$ + } + + /** + * Test {@link LOCATEFunctionModifier#modify(IFunction)} to validate a call + * to LOCATE(search_str, source_str, -5) using constants for all parameters + * returns LOCATE(search_str, source_str, 1). + * <p> + * {@link LOCATEFunctionModifier} will be constructed specifying no function + * name or parameter order. + * + * @throws Exception + */ + @Test public void testModifyWithStartIndex3() throws Exception { + IExpression[] args = new IExpression[] { + LANG_FACTORY.createLiteral("a", String.class), //$NON-NLS-1$ + LANG_FACTORY.createLiteral("abcdefg", String.class), //$NON-NLS-1$ + LANG_FACTORY.createLiteral(-5, Integer.class) + }; + // default / default + helpTestLocate(null, null, args, "LOCATE('a', 'abcdefg', 1)"); //$NON-NLS-1$ + } + + /** + * Test {@link LOCATEFunctionModifier#modify(IFunction)} to validate a call + * to LOCATE(search_str, source_str, null) using constants for all parameters + * returns LOCATE(search_str, source_str, NULL). + * <p> + * {@link LOCATEFunctionModifier} will be constructed specifying no function + * name or parameter order. + * + * @throws Exception + */ + @Test public void testModifyWithStartIndex4() throws Exception { + IExpression[] args = new IExpression[] { + LANG_FACTORY.createLiteral("a", String.class), //$NON-NLS-1$ + LANG_FACTORY.createLiteral("abcdefg", String.class), //$NON-NLS-1$ + LANG_FACTORY.createLiteral(null, Integer.class) + }; + // default / default + helpTestLocate(null, null, args, "LOCATE('a', 'abcdefg', NULL)"); //$NON-NLS-1$ + } + + /** + * Test {@link LOCATEFunctionModifier#modify(IFunction)} to validate a call + * to LOCATE(search_str, source_str, e1) using an element for start index + * parameter returns INSTR(source_str, search_str, CASE WHEN e1 < 1 THEN 1 ELSE e1 END). + * <p> + * {@link LOCATEFunctionModifier} will be constructed specifying a function + * name of INSTR and a parameter order of {@link ParameterOrder#SOURCE_SEARCH_INDEX}. + * + * @throws Exception + */ + @Test public void testModifyWithElementStartIndex() throws Exception { + IExpression[] args = new IExpression[] { + LANG_FACTORY.createLiteral("a", String.class), //$NON-NLS-1$ + LANG_FACTORY.createLiteral("abcdefg", String.class), //$NON-NLS-1$ + LANG_FACTORY.createElement("e1", null, null, Integer.class) //$NON-NLS-1$ + }; + // INSTR / SOURCE_SEARCH_INDEX + helpTestLocate("INSTR", ParameterOrder.SOURCE_SEARCH_INDEX, args, "INSTR('abcdefg', 'a', CASE WHEN e1 < 1 THEN 1 ELSE e1 END)"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + /** + * Test {@link LOCATEFunctionModifier#modify(IFunction)} to validate a call + * to LOCATE(search_str, source_str, e1) using an element for start index + * parameter returns LOCATE(search_str, source_str, CASE WHEN e1 < 0 THEN 0 ELSE e1 END). + * <p> + * {@link LOCATEFunctionModifier} will be constructed specifying no function + * name and no parameter order. + * + * @throws Exception + */ + @Test public void testModifyWithElementStartIndex2() throws Exception { + IExpression[] args = new IExpression[] { + LANG_FACTORY.createLiteral("a", String.class), //$NON-NLS-1$ + LANG_FACTORY.createLiteral("abcdefg", String.class), //$NON-NLS-1$ + LANG_FACTORY.createElement("e1", null, null, Integer.class) //$NON-NLS-1$ + }; + // default / default + helpTestLocate(null, null, args, "LOCATE('a', 'abcdefg', CASE WHEN e1 < 1 THEN 1 ELSE e1 END)"); //$NON-NLS-1$ + } + + /** + * Test {@link LOCATEFunctionModifier#modify(IFunction)} to validate a call + * to LOCATE(search_str, source_str, e1) using an element for start index + * parameter returns LOCATE(search_str, source_str, e1). + * <p> + * {@link LOCATEFunctionModifier} will be constructed specifying no function + * name, no parameter order, and no string index base. The test explicitly + * overrides {@link LOCATEFunctionModifier#getStartIndexExpression(IExpression)} + * to produce output that does not use the searched case expression for the + * element. + * + * @throws Exception + */ + @Test public void testOverrideGetStartIndexExpression() throws Exception { + final String expectedStr = "LOCATE('a', 'abcdefg', e1)"; //$NON-NLS-1$ + + IExpression[] args = new IExpression[] { + LANG_FACTORY.createLiteral("a", String.class), //$NON-NLS-1$ + LANG_FACTORY.createLiteral("abcdefg", String.class), //$NON-NLS-1$ + LANG_FACTORY.createElement("e1", null, null, Integer.class) //$NON-NLS-1$ + }; + IFunction func = LANG_FACTORY.createFunction(SourceSystemFunctions.LOCATE, args, Integer.class); + + final Translator trans = new Translator() { + @Override + public void initialize(ConnectorEnvironment env) + throws ConnectorException { + super.initialize(env); + registerFunctionModifier(SourceSystemFunctions.LOCATE, new LOCATEFunctionModifier(getLanguageFactory(), null, null) { + @Override + protected IExpression getStartIndexExpression( + IExpression startIndex) { + return startIndex; + } + + }); + } + }; + + trans.initialize(EnvironmentUtility.createEnvironment(new Properties(), false)); + + IExpression expr = trans.getFunctionModifiers().get(SourceSystemFunctions.LOCATE).modify(func); + SQLConversionVisitor sqlVisitor = trans.getSQLConversionVisitor(); + sqlVisitor.append(expr); + assertEquals("Modified function does not match", expectedStr, sqlVisitor.toString()); //$NON-NLS-1$ + } + + /** + * Test {@link LOCATEFunctionModifier#modify(IFunction)} to validate a call + * to LOCATE(search_str, source_str, 1) using a literal for start index + * parameter returns LOCATE(search_str, source_str, (1 + 1)). + * <p> + * {@link LOCATEFunctionModifier} will be constructed specifying no function + * name, no parameter order. The test explicitly overrides {@link LOCATEFunctionModifier#getStartIndexExpression(ILiteral)} + * to produce output that adds <code>1</code> to the literal. + * + * @throws Exception + */ + @Test public void testOverrideGetStartIndexExpression2() throws Exception { + final String expectedStr = "LOCATE('a', 'abcdefg', (1 + 1))"; //$NON-NLS-1$ + + IExpression[] args = new IExpression[] { + LANG_FACTORY.createLiteral("a", String.class), //$NON-NLS-1$ + LANG_FACTORY.createLiteral("abcdefg", String.class), //$NON-NLS-1$ + LANG_FACTORY.createLiteral(1, Integer.class) + }; + IFunction func = LANG_FACTORY.createFunction(SourceSystemFunctions.LOCATE, args, Integer.class); + + final Translator trans = new Translator() { + @Override + public void initialize(ConnectorEnvironment env) + throws ConnectorException { + super.initialize(env); + registerFunctionModifier(SourceSystemFunctions.LOCATE, new LOCATEFunctionModifier(getLanguageFactory(), null, null) { + @Override + protected IExpression getStartIndexExpression( + ILiteral startIndex) { + return LANG_FACTORY.createFunction("+", Arrays.asList(startIndex, LANG_FACTORY.createLiteral(1, Integer.class)), Integer.class); //$NON-NLS-1$ + } + + }); + } + }; + + trans.initialize(EnvironmentUtility.createEnvironment(new Properties(), false)); + + IExpression expr = trans.getFunctionModifiers().get(SourceSystemFunctions.LOCATE).modify(func); + SQLConversionVisitor sqlVisitor = trans.getSQLConversionVisitor(); + sqlVisitor.append(expr); + assertEquals("Modified function does not match", expectedStr, sqlVisitor.toString()); //$NON-NLS-1$ + } + +} Property changes on: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/translator/TestLOCATEFunctionModifier.java ___________________________________________________________________ Name: svn:mime-type + text/plain