Author: shawkins Date: 2012-03-05 23:07:16 -0500 (Mon, 05 Mar 2012) New Revision: 3914 Added: branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/ParseFormatFunctionModifier.java branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleFormatFunctionModifier.java branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/BaseSybaseExecutionFactory.java branches/7.7.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestFormatFunctionModifier.java Modified: branches/7.7.x/build/kits/jboss-container/teiid-releasenotes.html branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/access/AccessExecutionFactory.java branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/postgresql/PostgreSQLExecutionFactory.java branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java branches/7.7.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java branches/7.7.x/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java Log: TEIID-499 refining sql server support, adding missing access functions, adding sybase, pg, and oracle parse/format timestamp support Modified: branches/7.7.x/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- branches/7.7.x/build/kits/jboss-container/teiid-releasenotes.html 2012-03-05 20:30:07 UTC (rev 3913) +++ branches/7.7.x/build/kits/jboss-container/teiid-releasenotes.html 2012-03-06 04:07:16 UTC (rev 3914) @@ -32,6 +32,7 @@ <LI><B>Comparable LOBs</B> - the system property org.teiid.comparableLobs can be set to use CLOB and BLOB values in comparison/sorting/grouping operations. <LI><B>Padded String Comparison</B> - the system property org.teiid.padSpace can be set to effectively right pad strings to the same length for comparison. <LI><B>Copy LOBs</B> - added the copyLobs property to indicate that lob values should be copied by the engine rather than being held by reference. + <LI><B>Enhanced parse/format pushdown</B> - added more built-in support and extension points for parse/format function pushdown. Added parse/format timestamp handling for SQLServer, Sybase, Oracle, and PostgreSQL. </UL> <h2><a name="Compatibility">Compatibility Issues</a></h2> Added: branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/ParseFormatFunctionModifier.java =================================================================== --- branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/ParseFormatFunctionModifier.java (rev 0) +++ branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/ParseFormatFunctionModifier.java 2012-03-06 04:07:16 UTC (rev 3914) @@ -0,0 +1,52 @@ +/* + * 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; + +import java.util.Arrays; +import java.util.List; + +import org.teiid.language.Function; +import org.teiid.language.Literal; + +public abstract class ParseFormatFunctionModifier extends FunctionModifier { + + private String prefix; + + public ParseFormatFunctionModifier(String prefix) { + this.prefix = prefix; + } + + @Override + public List<?> translate(Function function) { + if (!(function.getParameters().get(1) instanceof Literal)) { + return null; //shouldn't happen + } + Literal l = (Literal)function.getParameters().get(1); + if (l.isMultiValued()) { + return null; //shouldn't happen + } + return Arrays.asList(prefix, function.getParameters().get(0), ", ", translateFormat((String) l.getValue()), ")" ); //$NON-NLS-1$ //$NON-NLS-2$ + } + + abstract protected Object translateFormat(String format); +} \ No newline at end of file Property changes on: branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/ParseFormatFunctionModifier.java ___________________________________________________________________ Added: svn:mime-type + text/plain Modified: branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/access/AccessExecutionFactory.java =================================================================== --- branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/access/AccessExecutionFactory.java 2012-03-05 20:30:07 UTC (rev 3913) +++ branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/access/AccessExecutionFactory.java 2012-03-06 04:07:16 UTC (rev 3914) @@ -24,17 +24,23 @@ */ package org.teiid.translator.jdbc.access; +import java.util.ArrayList; import java.util.List; import org.teiid.language.AggregateFunction; +import org.teiid.language.Function; import org.teiid.language.LanguageObject; import org.teiid.translator.ExecutionContext; +import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.Translator; +import org.teiid.translator.TranslatorException; +import org.teiid.translator.jdbc.AliasModifier; +import org.teiid.translator.jdbc.FunctionModifier; import org.teiid.translator.jdbc.JDBCExecutionFactory; -import org.teiid.translator.jdbc.sybase.SybaseExecutionFactory; +import org.teiid.translator.jdbc.sybase.BaseSybaseExecutionFactory; @Translator(name="access", description="A translator for Microsoft Access Database") -public class AccessExecutionFactory extends SybaseExecutionFactory { +public class AccessExecutionFactory extends BaseSybaseExecutionFactory { public AccessExecutionFactory() { setSupportsOrderBy(false); @@ -43,6 +49,28 @@ setMaxDependentInPredicates(10); //sql length length is 64k } + @Override + public void start() throws TranslatorException { + super.start(); + registerFunctionModifier(SourceSystemFunctions.ASCII, new AliasModifier("Asc")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.CHAR, new AliasModifier("Chr")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.CONCAT, new FunctionModifier() { + + @Override + public List<?> translate(Function function) { + List<Object> result = new ArrayList<Object>(function.getParameters().size()*2 - 1); + for (int i = 0; i < function.getParameters().size(); i++) { + if (i > 0) { + result.add(" & "); //$NON-NLS-1$ + } + result.add(function.getParameters().get(i)); + } + return result; + } + }); + registerFunctionModifier(SourceSystemFunctions.LENGTH, new AliasModifier("Len")); //$NON-NLS-1$ + } + @Override public String translateLiteralBoolean(Boolean booleanValue) { if(booleanValue.booleanValue()) { @@ -79,28 +107,24 @@ } @Override - public boolean supportsInsertWithQueryExpression() { - return false; - } - - @Override - public boolean supportsInlineViews() { - return false; - } - - @Override - public boolean supportsFunctionsInGroupBy() { - return false; - } - - @Override - public int getMaxFromGroups() { - return DEFAULT_MAX_FROM_GROUPS; - } - - @Override public List<String> getSupportedFunctions() { - return getDefaultSupportedFunctions(); + List<String> supportedFunctions = new ArrayList<String>(); + supportedFunctions.addAll(super.getSupportedFunctions()); + supportedFunctions.add(SourceSystemFunctions.ABS); + supportedFunctions.add(SourceSystemFunctions.EXP); + supportedFunctions.add(SourceSystemFunctions.ASCII); + supportedFunctions.add(SourceSystemFunctions.CHAR); + supportedFunctions.add(SourceSystemFunctions.CONCAT); + supportedFunctions.add(SourceSystemFunctions.LCASE); + supportedFunctions.add(SourceSystemFunctions.LEFT); + supportedFunctions.add(SourceSystemFunctions.LENGTH); + supportedFunctions.add(SourceSystemFunctions.LTRIM); + supportedFunctions.add(SourceSystemFunctions.RIGHT); + supportedFunctions.add(SourceSystemFunctions.RTRIM); + supportedFunctions.add(SourceSystemFunctions.TRIM); + supportedFunctions.add(SourceSystemFunctions.UCASE); + //TODO add support for formatting and use datepart for date methods + return supportedFunctions; } @Override Modified: branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java =================================================================== --- branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java 2012-03-05 20:30:07 UTC (rev 3913) +++ branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java 2012-03-06 04:07:16 UTC (rev 3914) @@ -20,8 +20,6 @@ * 02110-1301 USA. */ -/* - */ package org.teiid.translator.jdbc.oracle; import static org.teiid.translator.TypeFacility.RUNTIME_NAMES.*; @@ -133,6 +131,9 @@ registerFunctionModifier(OracleExecutionFactory.FILTER, new OracleSpatialFunctionModifier()); registerFunctionModifier(OracleExecutionFactory.WITHIN_DISTANCE, new OracleSpatialFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.PARSETIMESTAMP, new OracleFormatFunctionModifier("TO_TIMESTAMP(")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.FORMATTIMESTAMP, new OracleFormatFunctionModifier("TO_CHAR(")); //$NON-NLS-1$ + //add in type conversion ConvertModifier convertModifier = new ConvertModifier(); convertModifier.addTypeMapping("char(1)", FunctionModifier.CHAR); //$NON-NLS-1$ @@ -504,12 +505,8 @@ supportedFunctions.add("SECOND"); //$NON-NLS-1$ supportedFunctions.add("QUARTER"); //$NON-NLS-1$ supportedFunctions.add("WEEK"); //$NON-NLS-1$ - //supportedFunctions.add("FORMATDATE"); //$NON-NLS-1$ - //supportedFunctions.add("FORMATTIME"); //$NON-NLS-1$ - //supportedFunctions.add("FORMATTIMESTAMP"); //$NON-NLS-1$ - //supportedFunctions.add("PARSEDATE"); //$NON-NLS-1$ - //supportedFunctions.add("PARSETIME"); //$NON-NLS-1$ - //supportedFunctions.add("PARSETIMESTAMP"); //$NON-NLS-1$ + supportedFunctions.add(SourceSystemFunctions.FORMATTIMESTAMP); + supportedFunctions.add(SourceSystemFunctions.PARSETIMESTAMP); supportedFunctions.add("CAST"); //$NON-NLS-1$ supportedFunctions.add("CONVERT"); //$NON-NLS-1$ supportedFunctions.add("IFNULL"); //$NON-NLS-1$ @@ -623,4 +620,18 @@ return (ResultSet)statement.getObject(1); } + @Override + public boolean supportsOnlyFormatLiterals() { + return true; + } + + @Override + public boolean supportsFormatLiteral(String literal, + org.teiid.translator.ExecutionFactory.Format format) { + if (format == Format.NUMBER) { + return false; + } + return OracleFormatFunctionModifier.supportsLiteral(literal); + } + } Added: branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleFormatFunctionModifier.java =================================================================== --- branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleFormatFunctionModifier.java (rev 0) +++ branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleFormatFunctionModifier.java 2012-03-06 04:07:16 UTC (rev 3914) @@ -0,0 +1,120 @@ +/* + * 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.oracle; + +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +import org.teiid.core.util.StringUtil; +import org.teiid.translator.jdbc.ParseFormatFunctionModifier; + +public final class OracleFormatFunctionModifier extends + ParseFormatFunctionModifier { + + public static final Pattern tokenPattern = Pattern.compile("(G|y{1,4}|M{1,4}|D{1,3}|d{1,2}|E{1,4}|a{1,2}|H{1,2}|h{1,2}|m{1,2}|s{1,2}|S{1,3}|Z|[\\- /,.;:]+|(?:'[^'\"]*')+|[^'\"a-zA-Z]+)"); //$NON-NLS-1$ + + public OracleFormatFunctionModifier(String prefix) { + super(prefix); + } + + public static boolean supportsLiteral(String literal) { + Matcher m = tokenPattern.matcher(literal); + int end = 0; + while (m.find()) { + if (end != m.start()) { + return false; + } + end = m.end(); + } + return end == literal.length(); + } + + @Override + protected Object translateFormat(String format) { + Matcher m = tokenPattern.matcher(format); + StringBuilder sb = new StringBuilder(); + while (m.find()) { + if (m.group().length() == 0) { + continue; + } + String group = m.group(); + sb.append(convertToken(group)); + } + return sb.toString(); + } + + private Object convertToken(String group) { + switch (group.charAt(0)) { + case 'G': + return "AD"; //$NON-NLS-1$ + case 'y': + if (group.length() == 4) { + return "YYYY"; //$NON-NLS-1$ + } + return "YY"; //$NON-NLS-1$ + case 'M': + if (group.length() <= 2) { + return "MM"; //$NON-NLS-1$ + } + if (group.length() == 3) { + return "Mon"; //$NON-NLS-1$ + } + return "Month"; //$NON-NLS-1$ + case 'D': + return "DDD"; //$NON-NLS-1$ + case 'd': + return "DD"; //$NON-NLS-1$ + case 'E': + if (group.length() == 4) { + return "Day"; //$NON-NLS-1$ + } + return "Dy"; //$NON-NLS-1$ + case 'a': + return "PM"; //$NON-NLS-1$ + case 'H': + return "HH24"; //$NON-NLS-1$ + case 'h': + return "HH"; //$NON-NLS-1$ + case 'm': + return "MI"; //$NON-NLS-1$ + case 's': + return "SS"; //$NON-NLS-1$ + case 'S': + return "FF" + group.length(); //$NON-NLS-1$ + case 'Z': + return "TZHTZM";//$NON-NLS-1$ + case '\'': + return '"' + StringUtil.replace(group.substring(1, group.length() - 1), "''", "'") + '"'; //$NON-NLS-1$ //$NON-NLS-2$ + case ' ': + case '-': + case '/': + case ',': + case '.': + case ';': + case ':': + return group; + default: + return '"' + group + '"'; + } + } +} \ No newline at end of file Property changes on: branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleFormatFunctionModifier.java ___________________________________________________________________ Added: svn:mime-type + text/plain Modified: branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/postgresql/PostgreSQLExecutionFactory.java =================================================================== --- branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/postgresql/PostgreSQLExecutionFactory.java 2012-03-05 20:30:07 UTC (rev 3913) +++ branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/postgresql/PostgreSQLExecutionFactory.java 2012-03-06 04:07:16 UTC (rev 3914) @@ -52,6 +52,7 @@ import org.teiid.translator.jdbc.ModFunctionModifier; import org.teiid.translator.jdbc.oracle.LeftOrRightFunctionModifier; import org.teiid.translator.jdbc.oracle.MonthOrDayNameFunctionModifier; +import org.teiid.translator.jdbc.oracle.OracleFormatFunctionModifier; @@ -109,6 +110,9 @@ registerFunctionModifier(SourceSystemFunctions.LOCATE, new LocateFunctionModifier(getLanguageFactory())); registerFunctionModifier(SourceSystemFunctions.IFNULL, new AliasModifier("coalesce")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.PARSETIMESTAMP, new OracleFormatFunctionModifier("TO_TIMESTAMP(")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.FORMATTIMESTAMP, new OracleFormatFunctionModifier("TO_CHAR(")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.MOD, new ModFunctionModifier("%", getLanguageFactory(), Arrays.asList(TypeFacility.RUNTIME_TYPES.BIG_INTEGER, TypeFacility.RUNTIME_TYPES.BIG_DECIMAL))); //$NON-NLS-1$ //specific to 8.2 client or later @@ -457,6 +461,8 @@ // supportedFunctions.add(SourceSystemFunctions.ARRAY_GET); supportedFunctions.add(SourceSystemFunctions.ARRAY_LENGTH); + supportedFunctions.add(SourceSystemFunctions.FORMATTIMESTAMP); + supportedFunctions.add(SourceSystemFunctions.PARSETIMESTAMP); return supportedFunctions; } @@ -522,4 +528,18 @@ return true; } + @Override + public boolean supportsOnlyFormatLiterals() { + return true; + } + + @Override + public boolean supportsFormatLiteral(String literal, + org.teiid.translator.ExecutionFactory.Format format) { + if (format == Format.NUMBER) { + return false; + } + return OracleFormatFunctionModifier.supportsLiteral(literal); + } + } Modified: branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java =================================================================== --- branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java 2012-03-05 20:30:07 UTC (rev 3913) +++ branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java 2012-03-06 04:07:16 UTC (rev 3914) @@ -28,7 +28,6 @@ import java.sql.Timestamp; import java.util.ArrayList; import java.util.Arrays; -import java.util.HashMap; import java.util.HashSet; import java.util.List; import java.util.Map; @@ -37,13 +36,10 @@ import org.teiid.language.ColumnReference; import org.teiid.language.Function; import org.teiid.language.LanguageObject; -import org.teiid.language.Literal; import org.teiid.translator.ExecutionContext; import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.Translator; -import org.teiid.translator.TranslatorException; import org.teiid.translator.TypeFacility; -import org.teiid.translator.jdbc.FunctionModifier; import org.teiid.translator.jdbc.JDBCExecutionFactory; import org.teiid.translator.jdbc.sybase.SybaseExecutionFactory; @@ -53,67 +49,44 @@ @Translator(name="sqlserver", description="A translator for Microsoft SQL Server Database") public class SQLServerExecutionFactory extends SybaseExecutionFactory { - private class ParseFormatFunctionModifier extends FunctionModifier { - @Override - public List<?> translate(Function function) { - return Arrays.asList("CONVERT(" + getTarget() + ", ", function.getParameters().get(0), ", ", formatMap.get(((Literal)function.getParameters().get(1)).getValue()), ")" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ - } - - String getTarget() { - return "DATETIME"; //$NON-NLS-1$ - } - } - public static final String V_2005 = "2005"; //$NON-NLS-1$ public static final String V_2008 = "2008"; //$NON-NLS-1$ - private Map<String, Integer> formatMap = new HashMap<String, Integer>(); - //TEIID-31 remove mod modifier for SQL Server 2008 public SQLServerExecutionFactory() { setDatabaseVersion(V_2005); setMaxInCriteriaSize(JDBCExecutionFactory.DEFAULT_MAX_IN_CRITERIA); setMaxDependentInPredicates(JDBCExecutionFactory.DEFAULT_MAX_DEPENDENT_PREDICATES); + } + + @Override + protected void populateDateFormats() { formatMap.put("MM/dd/yy", 1); //$NON-NLS-1$ formatMap.put("yy.MM.dd", 2); //$NON-NLS-1$ formatMap.put("dd/MM/yy", 3); //$NON-NLS-1$ - formatMap.put("dd-MM-yy", 4); //$NON-NLS-1$ + formatMap.put("dd.MM.yy", 4); //$NON-NLS-1$ formatMap.put("dd-MM-yy", 5); //$NON-NLS-1$ formatMap.put("dd MMM yy", 6); //$NON-NLS-1$ formatMap.put("MMM dd, yy", 7); //$NON-NLS-1$ - formatMap.put("hh:mm:ss", 8); //$NON-NLS-1$ formatMap.put("MM-dd-yy", 10); //$NON-NLS-1$ formatMap.put("yy/MM/dd", 11); //$NON-NLS-1$ formatMap.put("yyMMdd", 12); //$NON-NLS-1$ - formatMap.put("kk:MM:ss:SSS", 14); //$NON-NLS-1$ for (Map.Entry<String, Integer> entry : new HashSet<Map.Entry<String, Integer>>(formatMap.entrySet())) { formatMap.put(entry.getKey().replace("yy", "yyyy"), entry.getValue() + 100); //$NON-NLS-1$ //$NON-NLS-2$ } - - formatMap.put("MMM dd yyyy hh:mma", 100); //$NON-NLS-1$ - formatMap.put("MMM dd yyyy hh:mm:ss:SSSa", 109); //$NON-NLS-1$ - formatMap.put("dd MMM yyyy kk:mm:ss:SSS", 113); //$NON-NLS-1$ - formatMap.put("yyyy-MM-dd kk:mm:ss", 120); //$NON-NLS-1$ - formatMap.put("yyyy-MM-dd kk:mm:ss.SSS", 121); //$NON-NLS-1$ - formatMap.put("yyyy-MM-dd'T'kk:mm:ss.SSS", 126); //$NON-NLS-1$ - formatMap.put("yyyy-MM-dd'T'kk:mm:ss.SSS'Z'", 127); //$NON-NLS-1$ - formatMap.put("dd MMM yyyy hh:mm:ss:SSSa", 130); //$NON-NLS-1$ - formatMap.put("dd/mm/yyyy hh:mm:ss:SSSa", 131); //$NON-NLS-1$ + + formatMap.put("MMM d yyyy hh:mma", 100); //$NON-NLS-1$ + formatMap.put("HH:mm:ss", 8); //$NON-NLS-1$ + formatMap.put("MMM d yyyy hh:mm:ss:SSSa", 109); //$NON-NLS-1$ + formatMap.put("dd MMM yyyy HH:mm:ss:SSS", 113); //$NON-NLS-1$ + formatMap.put("kk:MM:ss:SSS", 14); //$NON-NLS-1$ + formatMap.put("yyyy-MM-dd HH:mm:ss", 120); //$NON-NLS-1$ + formatMap.put("yyyy-MM-dd HH:mm:ss.SSS", 121); //$NON-NLS-1$ + formatMap.put("yyyy-MM-dd'T'HH:mm:ss.SSS", 126); //$NON-NLS-1$ + //formatMap.put("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", 127); //$NON-NLS-1$ } @Override - public void start() throws TranslatorException { - super.start(); - registerFunctionModifier(SourceSystemFunctions.PARSETIMESTAMP, new ParseFormatFunctionModifier()); - registerFunctionModifier(SourceSystemFunctions.FORMATTIMESTAMP, new ParseFormatFunctionModifier() { - @Override - String getTarget() { - return "VARCHAR"; //$NON-NLS-1$ - } - }); - } - - @Override protected List<Object> convertDateToString(Function function) { return Arrays.asList("replace(convert(varchar, ", function.getParameters().get(0), ", 102), '.', '-')"); //$NON-NLS-1$ //$NON-NLS-2$ } Added: branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/BaseSybaseExecutionFactory.java =================================================================== --- branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/BaseSybaseExecutionFactory.java (rev 0) +++ branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/BaseSybaseExecutionFactory.java 2012-03-06 04:07:16 UTC (rev 3914) @@ -0,0 +1,148 @@ +/* + * 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.sybase; + +import java.sql.CallableStatement; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.Arrays; +import java.util.List; + +import org.teiid.language.Command; +import org.teiid.language.LanguageObject; +import org.teiid.language.Limit; +import org.teiid.language.OrderBy; +import org.teiid.language.SetQuery; +import org.teiid.translator.ExecutionContext; +import org.teiid.translator.TypeFacility; +import org.teiid.translator.jdbc.JDBCExecutionFactory; +import org.teiid.translator.jdbc.db2.DB2ExecutionFactory; + +public class BaseSybaseExecutionFactory extends JDBCExecutionFactory { + + @Override + public boolean useAsInGroupAlias() { + return false; + } + + @Override + public boolean hasTimeType() { + return false; + } + + @Override + public int getTimestampNanoPrecision() { + return 3; + } + + /** + * SetQueries don't have a concept of TOP, an inline view is needed. + */ + @Override + public List<?> translateCommand(Command command, ExecutionContext context) { + if (!(command instanceof SetQuery)) { + return null; + } + SetQuery queryCommand = (SetQuery)command; + if (queryCommand.getLimit() == null) { + return null; + } + Limit limit = queryCommand.getLimit(); + OrderBy orderBy = queryCommand.getOrderBy(); + queryCommand.setLimit(null); + queryCommand.setOrderBy(null); + List<Object> parts = new ArrayList<Object>(6); + parts.add("SELECT "); //$NON-NLS-1$ + parts.addAll(translateLimit(limit, context)); + parts.add(" * FROM ("); //$NON-NLS-1$ + parts.add(queryCommand); + parts.add(") AS X"); //$NON-NLS-1$ + if (orderBy != null) { + parts.add(" "); //$NON-NLS-1$ + parts.add(orderBy); + } + return parts; + } + + @Override + public List<?> translate(LanguageObject obj, ExecutionContext context) { + if (!supportsCrossJoin()) { + DB2ExecutionFactory.convertCrossJoinToInner(obj, getLanguageFactory()); + } + return super.translate(obj, context); + } + + protected boolean supportsCrossJoin() { + return false; + } + + @SuppressWarnings("unchecked") + @Override + public List<?> translateLimit(Limit limit, ExecutionContext context) { + return Arrays.asList("TOP ", limit.getRowLimit()); //$NON-NLS-1$ + } + + @Override + public boolean useSelectLimit() { + return true; + } + + @Override + public Object retrieveValue(ResultSet results, int columnIndex, + Class<?> expectedType) throws SQLException { + if (expectedType == TypeFacility.RUNTIME_TYPES.BYTE) { + expectedType = TypeFacility.RUNTIME_TYPES.SHORT; + } + return super.retrieveValue(results, columnIndex, expectedType); + } + + @Override + public Object retrieveValue(CallableStatement results, int parameterIndex, + Class<?> expectedType) throws SQLException { + if (expectedType == TypeFacility.RUNTIME_TYPES.BYTE) { + expectedType = TypeFacility.RUNTIME_TYPES.SHORT; + } + return super.retrieveValue(results, parameterIndex, expectedType); + } + + @Override + public void bindValue(PreparedStatement stmt, Object param, + Class<?> paramType, int i) throws SQLException { + if (paramType == TypeFacility.RUNTIME_TYPES.BYTE) { + paramType = TypeFacility.RUNTIME_TYPES.SHORT; + param = ((Byte)param).shortValue(); + } + super.bindValue(stmt, param, paramType, i); + } + + public boolean nullPlusNonNullIsNull() { + return false; + } + + public boolean booleanNullable() { + return false; + } + +} Property changes on: branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/BaseSybaseExecutionFactory.java ___________________________________________________________________ Added: svn:mime-type + text/plain Modified: branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java =================================================================== --- branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java 2012-03-05 20:30:07 UTC (rev 3913) +++ branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java 2012-03-06 04:07:16 UTC (rev 3914) @@ -24,54 +24,79 @@ */ package org.teiid.translator.jdbc.sybase; -import java.sql.CallableStatement; import java.sql.Date; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; import java.sql.Timestamp; import java.util.ArrayList; import java.util.Arrays; +import java.util.HashMap; +import java.util.HashSet; import java.util.LinkedList; import java.util.List; +import java.util.Map; -import org.teiid.language.Command; import org.teiid.language.Expression; import org.teiid.language.Function; -import org.teiid.language.LanguageObject; -import org.teiid.language.Limit; import org.teiid.language.Literal; -import org.teiid.language.OrderBy; import org.teiid.language.SQLConstants; -import org.teiid.language.SetQuery; -import org.teiid.translator.ExecutionContext; import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.Translator; import org.teiid.translator.TranslatorException; -import org.teiid.translator.TypeFacility; import org.teiid.translator.jdbc.AliasModifier; import org.teiid.translator.jdbc.ConvertModifier; import org.teiid.translator.jdbc.EscapeSyntaxModifier; import org.teiid.translator.jdbc.FunctionModifier; -import org.teiid.translator.jdbc.JDBCExecutionFactory; import org.teiid.translator.jdbc.ModFunctionModifier; -import org.teiid.translator.jdbc.db2.DB2ExecutionFactory; +import org.teiid.translator.jdbc.ParseFormatFunctionModifier; import org.teiid.translator.jdbc.oracle.ConcatFunctionModifier; @Translator(name="sybase", description="A translator for Sybase Database") -public class SybaseExecutionFactory extends JDBCExecutionFactory { +public class SybaseExecutionFactory extends BaseSybaseExecutionFactory { public static final String TWELVE_5 = "12.5"; //$NON-NLS-1$ public static final String FIFTEEN_0_2 = "15.0.2"; //$NON-NLS-1$ public static final String FIFTEEN_5 = "15.5"; //$NON-NLS-1$ + protected Map<String, Integer> formatMap = new HashMap<String, Integer>(); + public SybaseExecutionFactory() { setDatabaseVersion(TWELVE_5); setSupportsFullOuterJoins(false); setMaxInCriteriaSize(250); setMaxDependentInPredicates(10); - } + populateDateFormats(); + } + + protected void populateDateFormats() { + formatMap.put("MM/dd/yy", 1); //$NON-NLS-1$ + formatMap.put("yy.MM.dd", 2); //$NON-NLS-1$ + formatMap.put("dd/MM/yy", 3); //$NON-NLS-1$ + formatMap.put("dd.MM.yy", 4); //$NON-NLS-1$ + formatMap.put("dd-MM-yy", 5); //$NON-NLS-1$ + formatMap.put("dd MMM yy", 6); //$NON-NLS-1$ + formatMap.put("MMM dd, yy", 7); //$NON-NLS-1$ + formatMap.put("MM-dd-yy", 10); //$NON-NLS-1$ + formatMap.put("yy/MM/dd", 11); //$NON-NLS-1$ + formatMap.put("yyMMdd", 12); //$NON-NLS-1$ + formatMap.put("yyddMM", 13); //$NON-NLS-1$ + formatMap.put("MM/yy/dd", 14); //$NON-NLS-1$ + formatMap.put("dd/yy/MM", 15); //$NON-NLS-1$ + formatMap.put("MMM dd yy HH:mm:ss", 16); //$NON-NLS-1$ + for (Map.Entry<String, Integer> entry : new HashSet<Map.Entry<String, Integer>>(formatMap.entrySet())) { + formatMap.put(entry.getKey().replace("yy", "yyyy"), entry.getValue() + 100); //$NON-NLS-1$ //$NON-NLS-2$ + } + + formatMap.put("MMM d yyyy hh:mma", 100); //$NON-NLS-1$ + formatMap.put("HH:mm:ss", 8); //$NON-NLS-1$ + formatMap.put("MMM d yyyy hh:mm:ss:SSSa", 109); //$NON-NLS-1$ + formatMap.put("hh:mma", 17); //$NON-NLS-1$ + formatMap.put("HH:mm", 18); //$NON-NLS-1$ + formatMap.put("hh:mm:ss:SSSa", 19); //$NON-NLS-1$ + formatMap.put("HH:mm:ss:SSS", 20); //$NON-NLS-1$ + formatMap.put("yy/MM/dd HH:mm:ss", 21); //$NON-NLS-1$ + formatMap.put("yy/MM/dd hh:mm:ssa", 22); //$NON-NLS-1$ + formatMap.put("yyyy-MM-dd'T'HH:mm:ss", 23); //$NON-NLS-1$ + } public void start() throws TranslatorException { super.start(); @@ -193,7 +218,19 @@ } }); convertModifier.addNumericBooleanConversions(); - registerFunctionModifier(SourceSystemFunctions.CONVERT, convertModifier); + registerFunctionModifier(SourceSystemFunctions.CONVERT, convertModifier); + registerFunctionModifier(SourceSystemFunctions.PARSETIMESTAMP, new ParseFormatFunctionModifier("CONVERT(DATETIME, ") { //$NON-NLS-1$ + @Override + protected Object translateFormat(String format) { + return formatMap.get(format); + } + }); + registerFunctionModifier(SourceSystemFunctions.FORMATTIMESTAMP, new ParseFormatFunctionModifier("CONVERT(VARCHAR, ") { //$NON-NLS-1$ + @Override + protected Object translateFormat(String format) { + return formatMap.get(format); + } + }); } private List<Object> convertTimeToString(Function function) { @@ -214,69 +251,6 @@ } @Override - public boolean useAsInGroupAlias() { - return false; - } - - @Override - public boolean hasTimeType() { - return false; - } - - @Override - public int getTimestampNanoPrecision() { - return 3; - } - - /** - * SetQueries don't have a concept of TOP, an inline view is needed. - */ - @Override - public List<?> translateCommand(Command command, ExecutionContext context) { - if (!(command instanceof SetQuery)) { - return null; - } - SetQuery queryCommand = (SetQuery)command; - if (queryCommand.getLimit() == null) { - return null; - } - Limit limit = queryCommand.getLimit(); - OrderBy orderBy = queryCommand.getOrderBy(); - queryCommand.setLimit(null); - queryCommand.setOrderBy(null); - List<Object> parts = new ArrayList<Object>(6); - parts.add("SELECT "); //$NON-NLS-1$ - parts.addAll(translateLimit(limit, context)); - parts.add(" * FROM ("); //$NON-NLS-1$ - parts.add(queryCommand); - parts.add(") AS X"); //$NON-NLS-1$ - if (orderBy != null) { - parts.add(" "); //$NON-NLS-1$ - parts.add(orderBy); - } - return parts; - } - - @Override - public List<?> translate(LanguageObject obj, ExecutionContext context) { - if (!supportsCrossJoin()) { - DB2ExecutionFactory.convertCrossJoinToInner(obj, getLanguageFactory()); - } - return super.translate(obj, context); - } - - @SuppressWarnings("unchecked") - @Override - public List<?> translateLimit(Limit limit, ExecutionContext context) { - return Arrays.asList("TOP ", limit.getRowLimit()); //$NON-NLS-1$ - } - - @Override - public boolean useSelectLimit() { - return true; - } - - @Override public List<String> getSupportedFunctions() { List<String> supportedFunctions = new ArrayList<String>(); supportedFunctions.addAll(super.getSupportedFunctions()); @@ -373,34 +347,6 @@ } @Override - public Object retrieveValue(ResultSet results, int columnIndex, - Class<?> expectedType) throws SQLException { - if (expectedType == TypeFacility.RUNTIME_TYPES.BYTE) { - expectedType = TypeFacility.RUNTIME_TYPES.SHORT; - } - return super.retrieveValue(results, columnIndex, expectedType); - } - - @Override - public Object retrieveValue(CallableStatement results, int parameterIndex, - Class<?> expectedType) throws SQLException { - if (expectedType == TypeFacility.RUNTIME_TYPES.BYTE) { - expectedType = TypeFacility.RUNTIME_TYPES.SHORT; - } - return super.retrieveValue(results, parameterIndex, expectedType); - } - - @Override - public void bindValue(PreparedStatement stmt, Object param, - Class<?> paramType, int i) throws SQLException { - if (paramType == TypeFacility.RUNTIME_TYPES.BYTE) { - paramType = TypeFacility.RUNTIME_TYPES.SHORT; - param = ((Byte)param).shortValue(); - } - super.bindValue(stmt, param, paramType, i); - } - - @Override public String translateLiteralTimestamp(Timestamp timestampValue) { return "CAST('" + formatDateValue(timestampValue) +"' AS DATETIME)"; //$NON-NLS-1$ //$NON-NLS-2$ } @@ -410,13 +356,14 @@ return "CAST('" + formatDateValue(dateValue) +"' AS DATE)"; //$NON-NLS-1$ //$NON-NLS-2$ } - protected boolean supportsCrossJoin() { - return false; - } - private boolean isFracSeconds(Function function) { Expression e = function.getParameters().get(0); return (e instanceof Literal && SQLConstants.NonReserved.SQL_TSI_FRAC_SECOND.equalsIgnoreCase((String)((Literal)e).getValue())); } + + @Override + public boolean supportsRowLimit() { + return getDatabaseVersion().compareTo(FIFTEEN_0_2) >= 0; + } } Added: branches/7.7.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestFormatFunctionModifier.java =================================================================== --- branches/7.7.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestFormatFunctionModifier.java (rev 0) +++ branches/7.7.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestFormatFunctionModifier.java 2012-03-06 04:07:16 UTC (rev 3914) @@ -0,0 +1,66 @@ +/* + * 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.oracle; + +import static org.junit.Assert.*; + +import org.junit.Test; + +@SuppressWarnings("nls") +public class TestFormatFunctionModifier { + + OracleFormatFunctionModifier offm = new OracleFormatFunctionModifier("TO_CHAR("); + + public void helpTest(String expected, String format) { + assertTrue(OracleFormatFunctionModifier.supportsLiteral(format)); + assertEquals(expected, offm.translateFormat(format)); + } + + @Test public void testQuoting() { + helpTest("\"a'\"\"123\"", "'a'''123"); + } + + @Test public void testDay() { + helpTest("DD DDD", "d D"); + } + + @Test public void testYear() { + helpTest("YY YY YY YYYY", "y yy yyy yyyy"); + } + + @Test public void testMonth() { + helpTest("MM MM Mon Month", "M MM MMM MMMM"); + } + + @Test public void testISO() { + helpTest("YYYY-MM-DD\"T\"HH24:MI:SS.FF3", "yyyy-MM-dd'T'HH:mm:ss.SSS"); + } + + @Test public void testSupports() { + assertFalse(OracleFormatFunctionModifier.supportsLiteral("\"")); + assertFalse(OracleFormatFunctionModifier.supportsLiteral("'")); + assertFalse(OracleFormatFunctionModifier.supportsLiteral("x")); + assertFalse(OracleFormatFunctionModifier.supportsLiteral("-ax")); + } + +} Property changes on: branches/7.7.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestFormatFunctionModifier.java ___________________________________________________________________ Added: svn:mime-type + text/plain Modified: branches/7.7.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java =================================================================== --- branches/7.7.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2012-03-05 20:30:07 UTC (rev 3913) +++ branches/7.7.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2012-03-06 04:07:16 UTC (rev 3914) @@ -901,5 +901,14 @@ Mockito.verify(cs, Mockito.never()).getObject(1); Mockito.verify(cs, Mockito.times(1)).setObject(1, 2, Types.INTEGER); } + + @Test public void testParseFormat() throws Exception { + String input = "select parsetimestamp(smalla.timestampvalue, 'yyyy.MM.dd'), formattimestamp(smalla.timestampvalue, 'yy.MM.dd') from bqt1.smalla"; //$NON-NLS-1$ + String output = "SELECT TO_TIMESTAMP(to_char(cast(g_0.TimestampValue AS timestamp), 'YYYY-MM-DD HH24:MI:SS.FF'), YYYY.MM.DD), TO_CHAR(g_0.TimestampValue, YY.MM.DD) FROM SmallA g_0"; //$NON-NLS-1$ + + CommandBuilder commandBuilder = new CommandBuilder(RealMetadataFactory.exampleBQTCached()); + Command obj = commandBuilder.getCommand(input, true, true); + TranslationHelper.helpTestVisitor(output, TRANSLATOR, obj); + } } Modified: branches/7.7.x/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml =================================================================== --- branches/7.7.x/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml 2012-03-05 20:30:07 UTC (rev 3913) +++ branches/7.7.x/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml 2012-03-06 04:07:16 UTC (rev 3914) @@ -1401,6 +1401,28 @@ <para>Translator supports aggregate conditions.</para> </entry> </row> + <row> + <entry> + <para>OnlyFormatLiterals</para> + </entry> + <entry> + <para>function support for a parse/format function and an implementation of the supportsFormatLiteral method.</para> + </entry> + <entry> + <para>Translator supports only literal format patterns that must be validated by the supportsFormatLiteral method</para> + </entry> + </row> + <row> + <entry> + <para>FormatLiteral</para> + </entry> + <entry> + <para>OnlyFormatLiterals</para> + </entry> + <entry> + <para>Translator supports the given literal format string.</para> + </entry> + </row> </tbody> </tgroup> </table> Modified: branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java =================================================================== --- branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java 2012-03-05 20:30:07 UTC (rev 3913) +++ branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java 2012-03-06 04:07:16 UTC (rev 3914) @@ -277,6 +277,7 @@ markInvalid(obj, obj.getName() + " literal parse " + c + " not supported by source"); //$NON-NLS-1$ //$NON-NLS-2$ return; } + c.setBindEligible(false); } } catch(QueryMetadataException e) { handleException(new TeiidComponentException(e));