[teiid-commits] teiid SVN: r2873 - in trunk: connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2 and 1 other directories.

Author: shawkins Date: 2011-01-24 14:30:06 -0500 (Mon, 24 Jan 2011) New Revision: 2873 Added: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/SubstringFunctionModifier.java Modified: trunk/build/kits/jboss-container/teiid-releasenotes.html trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/DB2ExecutionFactory.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/db2/TestDB2SqlTranslator.java Log: TEIID-1281 adding handling to ensure that the substring length parameter does not cause an exception for db2/derby Modified: trunk/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-01-22 08:16:41 UTC (rev 2872) +++ trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-01-24 19:30:06 UTC (rev 2873) @@ -123,6 +123,7 @@ <h2><a name="Other">Other Issues</a></h2> <ul> + <li>TEIID-1281 - Negative start indexing is not supported by DB2 and Derby databases. Usage of the Teiid SUBSTRING against these sources should not use negative start values. <li>TEIID-1008 - Most versions of Oracle and MySQL do not support deeply nested correlated references. There is currently no workaround for this issue. <li>For compatibility with the 7.0 release if a stored procedure parameter list begins with identifier=, then it will be parsed as a named parameter invocation even if the intent was to use a comparison predicate as the first parameter value. The workaround is to use nesting parens, e.g. call proc((identifier=value), ...), which clarifies that this is positional value. This workaround will not be needed in later releases. Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/DB2ExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/DB2ExecutionFactory.java 2011-01-22 08:16:41 UTC (rev 2872) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/DB2ExecutionFactory.java 2011-01-24 19:30:06 UTC (rev 2873) @@ -74,7 +74,7 @@ registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new AliasModifier("day")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.IFNULL, new AliasModifier("coalesce")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.LOCATE, new LocateFunctionModifier(getLanguageFactory())); - registerFunctionModifier(SourceSystemFunctions.SUBSTRING, new AliasModifier("substr")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.SUBSTRING, new SubstringFunctionModifier()); registerFunctionModifier(SourceSystemFunctions.MOD, new ModFunctionModifier("MOD", getLanguageFactory())); //$NON-NLS-1$ Added: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/SubstringFunctionModifier.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/SubstringFunctionModifier.java (rev 0) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/SubstringFunctionModifier.java 2011-01-24 19:30:06 UTC (rev 2873) @@ -0,0 +1,90 @@ +/* + * 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.translator.jdbc.db2; + +import java.util.ArrayList; +import java.util.Arrays; +import java.util.List; + +import org.teiid.language.Comparison; +import org.teiid.language.Expression; +import org.teiid.language.Function; +import org.teiid.language.Literal; +import org.teiid.language.SearchedCase; +import org.teiid.language.SearchedWhenClause; +import org.teiid.language.Comparison.Operator; +import org.teiid.translator.SourceSystemFunctions; +import org.teiid.translator.TypeFacility; +import org.teiid.translator.jdbc.AliasModifier; + +public class SubstringFunctionModifier extends AliasModifier { + + public SubstringFunctionModifier() { + super("substr"); //$NON-NLS-1$ + } + + @Override + public List<?> translate(Function function) { + this.modify(function); + if (function.getParameters().size() != 3) { + return null; + } + //case when length < 0 then null when length < LENGTH(string) - start + 1 then exp else LENGTH(string) - start + 1 + Expression length = function.getParameters().get(2); + List<SearchedWhenClause> clauses = new ArrayList<SearchedWhenClause>(2); + Boolean isNegative = null; + if (length instanceof Literal) { + Literal l = (Literal)length; + if (!l.isMultiValued()) { + int value = (Integer)l.getValue(); + isNegative = value < 0; + } + } + if (isNegative == null) { + clauses.add(new SearchedWhenClause(new Comparison(length, new Literal(0, TypeFacility.RUNTIME_TYPES.INTEGER), Operator.LT), new Literal(null, TypeFacility.RUNTIME_TYPES.INTEGER))); + } else if (isNegative) { + //TODO: could be done in the rewriter + function.getParameters().set(2, null); + return null; + } + Expression maxLength = new Function( + SourceSystemFunctions.SUBTRACT_OP, + Arrays.asList(new Function( + SourceSystemFunctions.LENGTH, + Arrays.asList(function.getParameters().get(0)), + TypeFacility.RUNTIME_TYPES.INTEGER), + new Function( + SourceSystemFunctions.ADD_OP, + Arrays.asList( + function.getParameters().get(1), + new Literal(1, TypeFacility.RUNTIME_TYPES.INTEGER)), + TypeFacility.RUNTIME_TYPES.INTEGER)), + TypeFacility.RUNTIME_TYPES.INTEGER); + clauses.add(new SearchedWhenClause(new Comparison(length, maxLength, Operator.LE), length)); + SearchedCase sc = new SearchedCase(clauses, + maxLength, TypeFacility.RUNTIME_TYPES.INTEGER); + function.getParameters().set(2, sc); + return null; + } + +} Property changes on: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/db2/SubstringFunctionModifier.java ___________________________________________________________________ Name: svn:mime-type + text/plain Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/db2/TestDB2SqlTranslator.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/db2/TestDB2SqlTranslator.java 2011-01-22 08:16:41 UTC (rev 2872) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/db2/TestDB2SqlTranslator.java 2011-01-24 19:30:06 UTC (rev 2873) @@ -242,5 +242,23 @@ input, output, TRANSLATOR); } + + @Test public void testSubstring() throws Exception { + String input = "SELECT substring(STRINGNUM, 2, 10) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT substr(SmallA.StringNum, 2, CASE WHEN 10 <= (length(SmallA.StringNum) - (2 + 1)) THEN 10 ELSE (length(SmallA.StringNum) - (2 + 1)) END) FROM SmallA"; //$NON-NLS-1$ + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, output, + TRANSLATOR); + } + + @Test public void testSubstring1() throws Exception { + String input = "SELECT substring(STRINGNUM, 2, intnum) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT substr(SmallA.StringNum, 2, CASE WHEN SmallA.IntNum < 0 THEN NULL WHEN SmallA.IntNum <= (length(SmallA.StringNum) - (2 + 1)) THEN SmallA.IntNum ELSE (length(SmallA.StringNum) - (2 + 1)) END) FROM SmallA"; //$NON-NLS-1$ + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, output, + TRANSLATOR); + } + }