[teiid-commits] teiid SVN: r499 - in trunk: connector-api/src/main/java/com/metamatrix/connector/visitor/util and 14 other directories.

Author: shawkins Date: 2009-02-25 13:27:23 -0500 (Wed, 25 Feb 2009) New Revision: 499 Added: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCExecutionException.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/ReplacementVisitor.java trunk/test-integration/teiid/ Removed: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/FunctionReplacementVisitor.java Modified: trunk/connector-api/src/main/java/com/metamatrix/connector/visitor/framework/HierarchyVisitor.java trunk/connector-api/src/main/java/com/metamatrix/connector/visitor/util/SQLStringVisitor.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCBaseExecution.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCConnector.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCQueryExecution.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCUpdateExecution.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/access/AccessSQLTranslator.java 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/oracle/OracleSQLTranslator.java 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/sybase/SybaseSQLTranslator.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/SQLConversionVisitor.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/TranslatedCommand.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/Translator.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/access/TestAccessSQLTranslator.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sqlserver/TestSqlServerConversionVisitor.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sybase/TestSybaseSQLConversionVisitor.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/util/TestFunctionReplacementVisitor.java trunk/connectors/sandbox/connector-oracle-spatial/src/main/java/com/metamatrix/connector/jdbc/oracle/spatial/OracleSpatialSQLTranslator.java trunk/test-integration/src/test/java/com/metamatrix/connector/jdbc/extension/TestSQLConversionVisitor.java Log: TEIID-164 fixes from rewiring integration tests Modified: trunk/connector-api/src/main/java/com/metamatrix/connector/visitor/framework/HierarchyVisitor.java =================================================================== --- trunk/connector-api/src/main/java/com/metamatrix/connector/visitor/framework/HierarchyVisitor.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/connector-api/src/main/java/com/metamatrix/connector/visitor/framework/HierarchyVisitor.java 2009-02-25 18:27:23 UTC (rev 499) @@ -27,11 +27,9 @@ import com.metamatrix.connector.language.ICompareCriteria; import com.metamatrix.connector.language.ICompoundCriteria; import com.metamatrix.connector.language.IDelete; -import com.metamatrix.connector.language.IElement; import com.metamatrix.connector.language.IExistsCriteria; import com.metamatrix.connector.language.IFrom; import com.metamatrix.connector.language.IFunction; -import com.metamatrix.connector.language.IGroup; import com.metamatrix.connector.language.IGroupBy; import com.metamatrix.connector.language.IInCriteria; import com.metamatrix.connector.language.IInlineView; @@ -39,11 +37,8 @@ import com.metamatrix.connector.language.IIsNullCriteria; import com.metamatrix.connector.language.IJoin; import com.metamatrix.connector.language.ILikeCriteria; -import com.metamatrix.connector.language.ILiteral; import com.metamatrix.connector.language.INotCriteria; import com.metamatrix.connector.language.IOrderBy; -import com.metamatrix.connector.language.IOrderByItem; -import com.metamatrix.connector.language.IParameter; import com.metamatrix.connector.language.IProcedure; import com.metamatrix.connector.language.IQuery; import com.metamatrix.connector.language.IScalarSubquery; @@ -70,7 +65,14 @@ */ public abstract class HierarchyVisitor extends AbstractLanguageVisitor { - public HierarchyVisitor() { + private boolean visitSubcommands; + + public HierarchyVisitor() { + this(true); + } + + public HierarchyVisitor(boolean visitSubcommands) { + this.visitSubcommands = visitSubcommands; } public void visit(IAggregate obj) { @@ -95,15 +97,14 @@ visitNode(obj.getCriteria()); } - public void visit(IElement obj) { - } - public void visit(IProcedure obj) { visitNodes(obj.getParameters()); } public void visit(IExistsCriteria obj) { - visitNode(obj.getQuery()); + if (visitSubcommands) { + visitNode(obj.getQuery()); + } } public void visit(IFrom obj) { @@ -114,12 +115,6 @@ visitNodes(obj.getParameters()); } - public void visit(IGroup obj) { - } - -// public void visit(IGroup obj) { -// } - public void visit(IGroupBy obj) { visitNodes(obj.getElements()); } @@ -152,9 +147,6 @@ visitNode(obj.getRightExpression()); } - public void visit(ILiteral obj) { - } - public void visit(INotCriteria obj) { visitNode(obj.getCriteria()); } @@ -163,12 +155,6 @@ visitNodes(obj.getItems()); } - public void visit(IOrderByItem obj) { - } - - public void visit(IParameter obj) { - } - public void visit(IQuery obj) { visitNode(obj.getSelect()); visitNode(obj.getFrom()); @@ -180,7 +166,9 @@ } public void visit(IScalarSubquery obj) { - visitNode(obj.getQuery()); + if (visitSubcommands) { + visitNode(obj.getQuery()); + } } public void visit(ISearchedCaseExpression obj) { @@ -202,17 +190,23 @@ public void visit(ISubqueryCompareCriteria obj) { visitNode(obj.getLeftExpression()); - visitNode(obj.getQuery()); + if (visitSubcommands) { + visitNode(obj.getQuery()); + } } public void visit(ISubqueryInCriteria obj) { visitNode(obj.getLeftExpression()); - visitNode(obj.getQuery()); + if (visitSubcommands) { + visitNode(obj.getQuery()); + } } public void visit(ISetQuery obj) { - visitNode(obj.getLeftQuery()); - visitNode(obj.getRightQuery()); + if (visitSubcommands) { + visitNode(obj.getLeftQuery()); + visitNode(obj.getRightQuery()); + } visitNode(obj.getOrderBy()); visitNode(obj.getLimit()); } @@ -225,7 +219,9 @@ @Override public void visit(IInlineView obj) { - visitNode(obj.getQuery()); + if (visitSubcommands) { + visitNode(obj.getQuery()); + } } @Override Modified: trunk/connector-api/src/main/java/com/metamatrix/connector/visitor/util/SQLStringVisitor.java =================================================================== --- trunk/connector-api/src/main/java/com/metamatrix/connector/visitor/util/SQLStringVisitor.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/connector-api/src/main/java/com/metamatrix/connector/visitor/util/SQLStringVisitor.java 2009-02-25 18:27:23 UTC (rev 499) @@ -780,7 +780,7 @@ String name = getShortName(obj.getName()); buffer.append(name); } else if (obj.getElement() != null) { - visit(obj.getElement()); + append(obj.getElement()); } else { buffer.append(UNDEFINED); } @@ -879,7 +879,6 @@ } append(obj.getSelectSymbols()); } - protected String getSourceComment(ICommand command) { return ""; //$NON-NLS-1$ Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCBaseExecution.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCBaseExecution.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCBaseExecution.java 2009-02-25 18:27:23 UTC (rev 499) @@ -28,8 +28,7 @@ import java.sql.SQLException; import java.sql.SQLWarning; import java.sql.Statement; -import java.util.Iterator; -import java.util.List; +import java.util.Arrays; import java.util.Properties; import org.teiid.connector.jdbc.translator.TranslatedCommand; @@ -95,91 +94,6 @@ // Methods // =========================================================================================================================== - private void addSql(TranslatedCommand command, - StringBuffer message) { - String sql = command.getSql(); - int ndx = sql.indexOf('?'); - if (ndx >= 0) { - message.append(sql.substring(0, ndx)); - int len = sql.length(); - for (Iterator itr = command.getPreparedValues().iterator(); itr.hasNext() && ndx < len;) { - message.append(itr.next()); - int nextNdx = sql.indexOf('?', ++ndx); - if (nextNdx >= 0) { - message.append(sql.substring(ndx, nextNdx)); - } else { - message.append(sql.substring(ndx)); - } - ndx = nextNdx; - } - } else { - message.append(sql); - } - } - - /** - * @param error - * @param command - * @return - * @since 5.5 - */ - protected ConnectorException createAndLogError(SQLException error, - TranslatedCommand command) { - ConnectorException connectorErr = createError(error, command); - this.logger.logError(connectorErr.getMessage()); - return connectorErr; - } - - /** - * @param error - * @param messageKey - * @param commands - * @return - * @throws ConnectorException - * @since 5.5 - */ - protected ConnectorException createAndLogError(Throwable error, - String messageKey, - List commands) throws ConnectorException { - String msg; - if (commands.isEmpty()) { - msg = error.getMessage(); - } else { - msg = JDBCPlugin.Util.getString(messageKey, error.getMessage()); - StringBuffer buf = new StringBuffer(msg); - for (Iterator itr = commands.iterator(); itr.hasNext();) { - buf.append("\n "); //$NON-NLS-1$ - addSql((TranslatedCommand)itr.next(), buf); - } - msg = buf.toString(); - } - this.logger.logError(msg); - if (error instanceof ConnectorException) { - error = ((ConnectorException)error).getCause(); - } - throw new ConnectorException(error, msg); - } - - /** - * @param error - * @param command - * @return - * @since 5.5 - */ - protected ConnectorException createError(SQLException error, - TranslatedCommand command) { - String msg = (command == null ? error.getMessage() - : JDBCPlugin.Util.getString("JDBCQueryExecution.Error_executing_query__1", //$NON-NLS-1$ - error.getMessage(), createSql(command))); - return new ConnectorException(error, msg); - } - - private String createSql(TranslatedCommand command) { - StringBuffer msg = new StringBuffer(); - addSql(command, msg); - return msg.toString(); - } - protected TranslatedCommand translateCommand(ICommand command) throws ConnectorException { TranslatedCommand translatedCommand = new TranslatedCommand(context, sqlTranslator); translatedCommand.translateCommand(command); Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCConnector.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCConnector.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCConnector.java 2009-02-25 18:27:23 UTC (rev 499) @@ -20,8 +20,6 @@ * 02110-1301 USA. */ -/* - */ package org.teiid.connector.jdbc; import java.lang.reflect.InvocationHandler; Added: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCExecutionException.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCExecutionException.java (rev 0) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCExecutionException.java 2009-02-25 18:27:23 UTC (rev 499) @@ -0,0 +1,41 @@ +/* + * 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; + +import java.sql.SQLException; +import java.util.Arrays; + +import org.teiid.connector.jdbc.translator.TranslatedCommand; + +import com.metamatrix.connector.api.ConnectorException; + +public class JDBCExecutionException extends ConnectorException { + + public JDBCExecutionException(SQLException error, + TranslatedCommand... commands) { + super(commands == null ? error.getMessage() : JDBCPlugin.Util + .getString("JDBCQueryExecution.Error_executing_query__1", //$NON-NLS-1$ + error.getMessage(), Arrays.toString(commands))); + } + +} Property changes on: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCExecutionException.java ___________________________________________________________________ Name: svn:mime-type + text/plain Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCQueryExecution.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCQueryExecution.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCQueryExecution.java 2009-02-25 18:27:23 UTC (rev 499) @@ -105,7 +105,7 @@ initResultSetInfo(); } catch (SQLException e) { - throw createAndLogError(e, translatedComm); + throw new JDBCExecutionException(e, translatedComm); } } Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCUpdateExecution.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCUpdateExecution.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCUpdateExecution.java 2009-02-25 18:27:23 UTC (rev 499) @@ -94,6 +94,8 @@ ICommand[] commands = (ICommand[])batchedCommand.getUpdateCommands().toArray(new ICommand[batchedCommand.getUpdateCommands().size()]); int[] results = new int[commands.length]; + TranslatedCommand command = null; + try { // temporarily turn the auto commit off, and set it back to what it was // before at the end of the command execution. @@ -101,12 +103,12 @@ connection.setAutoCommit(false); } - List executedCmds = new ArrayList(); - + List<TranslatedCommand> executedCmds = new ArrayList<TranslatedCommand>(); + TranslatedCommand previousCommand = null; for (int i = 0; i < commands.length; i++) { - TranslatedCommand command = translateCommand(commands[i]); + command = translateCommand(commands[i]); if (command.isPrepared()) { PreparedStatement pstmt = null; if (previousCommand != null && previousCommand.isPrepared() && previousCommand.getSql().equals(command.getSql())) { @@ -137,7 +139,7 @@ } succeeded = true; } catch (SQLException e) { - throw createAndLogError(e, null); + throw new JDBCExecutionException(e, command); } finally { if (commitType) { restoreAutoCommit(!succeeded, null); @@ -176,7 +178,7 @@ addStatementWarnings(); succeeded = true; } catch (SQLException e) { - throw createAndLogError(e, translatedComm); + throw new JDBCExecutionException(e, translatedComm); } finally { if (commitType) { restoreAutoCommit(!succeeded, translatedComm); @@ -187,7 +189,7 @@ private void executeBatch(int commandCount, int[] results, - List commands) throws ConnectorException { + List<TranslatedCommand> commands) throws ConnectorException { try { int[] batchResults = statement.executeBatch(); addStatementWarnings(); @@ -196,7 +198,7 @@ } commands.clear(); } catch (SQLException err) { - throw createAndLogError(err, "JDBCQueryExecution.Error_executing_query__3", commands); //$NON-NLS-1$ + throw new JDBCExecutionException(err, commands.toArray(new TranslatedCommand[commands.size()])); //$NON-NLS-1$ } } @@ -221,7 +223,7 @@ addStatementWarnings(); return updateCount; } catch (SQLException err) { - throw createError(err, translatedComm); + throw new JDBCExecutionException(err, translatedComm); } } @@ -234,7 +236,7 @@ try { return connection.getAutoCommit(); } catch (SQLException err) { - throw createAndLogError(err, command); + throw new JDBCExecutionException(err, command); } } @@ -253,7 +255,7 @@ } connection.setAutoCommit(true); } catch (SQLException err) { - throw createAndLogError(err, command); + throw new JDBCExecutionException(err, command); } } Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/access/AccessSQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/access/AccessSQLTranslator.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/access/AccessSQLTranslator.java 2009-02-25 18:27:23 UTC (rev 499) @@ -24,9 +24,16 @@ */ package org.teiid.connector.jdbc.access; +import java.util.ArrayList; +import java.util.List; + import org.teiid.connector.jdbc.translator.Translator; +import com.metamatrix.connector.api.ExecutionContext; +import com.metamatrix.connector.language.ICommand; import com.metamatrix.connector.language.ILimit; +import com.metamatrix.connector.language.IOrderBy; +import com.metamatrix.connector.language.IQueryCommand; public class AccessSQLTranslator extends Translator { @@ -44,12 +51,30 @@ } @Override - public String addLimitString(String queryCommand, ILimit limit) { - int index = queryCommand.startsWith("SELECT DISTINCT")?15:6; - return new StringBuffer(queryCommand.length() + 8).append(queryCommand) - .insert(index, " TOP " + limit.getRowLimit()).toString(); + public List<?> translateCommand(ICommand command, ExecutionContext context) { + if (!(command instanceof IQueryCommand)) { + return null; + } + IQueryCommand queryCommand = (IQueryCommand)command; + if (queryCommand.getLimit() == null) { + return null; + } + ILimit limit = queryCommand.getLimit(); + IOrderBy orderBy = queryCommand.getOrderBy(); + queryCommand.setLimit(null); + queryCommand.setOrderBy(null); + List<Object> parts = new ArrayList<Object>(6); + parts.add("SELECT TOP "); + parts.add(limit.getRowLimit()); + parts.add(" * FROM ("); + parts.add(queryCommand); + parts.add(") AS X"); + if (orderBy != null) { + parts.add(orderBy); + } + return parts; } - + @Override public boolean addSourceComment() { return false; 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-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/db2/DB2SQLTranslator.java 2009-02-25 18:27:23 UTC (rev 499) @@ -20,10 +20,11 @@ * 02110-1301 USA. */ -/* - */ package org.teiid.connector.jdbc.db2; +import java.util.Arrays; +import java.util.List; + import org.teiid.connector.jdbc.translator.AliasModifier; import org.teiid.connector.jdbc.translator.Translator; @@ -36,6 +37,7 @@ import com.metamatrix.connector.language.IJoin; import com.metamatrix.connector.language.ILimit; import com.metamatrix.connector.language.ILiteral; +import com.metamatrix.connector.language.IQuery; import com.metamatrix.connector.language.ICompareCriteria.Operator; import com.metamatrix.connector.language.IJoin.JoinType; import com.metamatrix.connector.visitor.framework.HierarchyVisitor; @@ -53,28 +55,30 @@ registerFunctionModifier(SourceSystemFunctions.IFNULL, new AliasModifier("coalesce")); //$NON-NLS-1$ //$NON-NLS-2$ registerFunctionModifier(SourceSystemFunctions.SUBSTRING, new AliasModifier("substr")); //$NON-NLS-1$ //$NON-NLS-2$ } - + + @SuppressWarnings("unchecked") @Override - public String addLimitString(String queryCommand, ILimit limit) { - return queryCommand + " FETCH FIRST " + limit.getRowLimit() + " ROWS ONLY"; //$NON-NLS-1$ + public List<?> translateLimit(ILimit limit, ExecutionContext context) { + return Arrays.asList("FETCH FIRST ", limit.getRowLimit(), " ROWS ONLY"); //$NON-NLS-1$ //$NON-NLS-2$ } @Override - public ICommand modifyCommand(ICommand command, ExecutionContext context) - throws ConnectorException { - HierarchyVisitor hierarchyVisitor = new HierarchyVisitor() { - @Override - public void visit(IJoin obj) { - if (obj.getJoinType() != JoinType.CROSS_JOIN) { - return; + public ICommand modifyCommand(ICommand command, ExecutionContext context) { + if (command instanceof IQuery) { + HierarchyVisitor hierarchyVisitor = new HierarchyVisitor(false) { + @Override + public void visit(IJoin obj) { + if (obj.getJoinType() != JoinType.CROSS_JOIN) { + return; + } + ILiteral one = getLanguageFactory().createLiteral(1, TypeFacility.RUNTIME_TYPES.INTEGER); + obj.getCriteria().add(getLanguageFactory().createCompareCriteria(Operator.EQ, one, one)); + obj.setJoinType(JoinType.INNER_JOIN); } - ILiteral one = getLanguageFactory().createLiteral(1, TypeFacility.RUNTIME_TYPES.INTEGER); - obj.getCriteria().add(getLanguageFactory().createCompareCriteria(Operator.EQ, one, one)); - obj.setJoinType(JoinType.INNER_JOIN); - } - }; - - command.acceptVisitor(hierarchyVisitor); + }; + + command.acceptVisitor(hierarchyVisitor); + } return command; } 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-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java 2009-02-25 18:27:23 UTC (rev 499) @@ -30,6 +30,7 @@ import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; +import java.util.ArrayList; import java.util.List; import org.teiid.connector.jdbc.JDBCPlugin; @@ -45,6 +46,7 @@ import com.metamatrix.connector.language.IGroup; import com.metamatrix.connector.language.IInsert; import com.metamatrix.connector.language.ILimit; +import com.metamatrix.connector.language.IQueryCommand; import com.metamatrix.connector.language.ISetQuery.Operation; import com.metamatrix.connector.metadata.runtime.Element; import com.metamatrix.connector.visitor.util.SQLReservedWords; @@ -88,8 +90,7 @@ } @Override - public ICommand modifyCommand(ICommand command, ExecutionContext context) - throws ConnectorException { + public ICommand modifyCommand(ICommand command, ExecutionContext context) throws ConnectorException { if (!(command instanceof IInsert)) { return command; } @@ -143,28 +144,38 @@ insert.getValues().add(index, sequenceElement); } return command; - } - + } + @Override - public String addLimitString(String queryCommand, ILimit limit) { - StringBuffer limitQuery = new StringBuffer(queryCommand.length()); + public List<?> translateCommand(ICommand command, ExecutionContext context) { + if (!(command instanceof IQueryCommand)) { + return null; + } + IQueryCommand queryCommand = (IQueryCommand)command; + if (queryCommand.getLimit() == null) { + return null; + } + ILimit limit = queryCommand.getLimit(); + queryCommand.setLimit(null); + List<Object> parts = new ArrayList<Object>(); if (limit.getRowOffset() > 0) { - limitQuery.append("SELECT * FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM ("); + parts.add("SELECT * FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM ("); } else { - limitQuery.append("SELECT * FROM ("); + parts.add("SELECT * FROM ("); } - limitQuery.append(queryCommand); + parts.add(queryCommand); if (limit.getRowOffset() > 0) { - limitQuery.append(") VIEW_FOR_LIMIT WHERE ROWNUM <= ").append( - limit.getRowLimit() + limit.getRowOffset()).append(") WHERE ROWNUM_ > ").append( - limit.getRowOffset()); + parts.add(") VIEW_FOR_LIMIT WHERE ROWNUM <= "); + parts.add(limit.getRowLimit() + limit.getRowOffset()); + parts.add(") WHERE ROWNUM_ > "); + parts.add(limit.getRowOffset()); } else { - limitQuery.append(") WHERE ROWNUM <= ").append( - limit.getRowLimit()); + parts.add(") WHERE ROWNUM <= "); + parts.add(limit.getRowLimit()); } - return limitQuery.toString(); + return parts; } - + @Override public boolean useAsInGroupAlias(){ return false; 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-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/PostgreSQLTranslator.java 2009-02-25 18:27:23 UTC (rev 499) @@ -25,6 +25,8 @@ import java.sql.Date; import java.sql.Time; import java.sql.Timestamp; +import java.util.Arrays; +import java.util.List; import org.teiid.connector.jdbc.oracle.LeftOrRightFunctionModifier; import org.teiid.connector.jdbc.oracle.MonthOrDayNameFunctionModifier; @@ -39,6 +41,8 @@ import com.metamatrix.connector.language.IAggregate; import com.metamatrix.connector.language.ICommand; import com.metamatrix.connector.language.ILimit; +import com.metamatrix.connector.language.IOrderBy; +import com.metamatrix.connector.language.ISetQuery; import com.metamatrix.connector.visitor.framework.HierarchyVisitor; import com.metamatrix.connector.visitor.util.SQLReservedWords; @@ -107,14 +111,13 @@ return 6; } - @Override - public String addLimitString(String queryCommand, ILimit limit) { - StringBuffer sb = new StringBuffer(queryCommand); - sb.append(" LIMIT ").append(limit.getRowLimit()); - if (limit.getRowOffset() > 0) { - sb.append(" OFFSET ").append(limit.getRowOffset()); - } - return sb.toString(); + @SuppressWarnings("unchecked") + @Override + public List<?> translateLimit(ILimit limit, ExecutionContext context) { + if (limit.getRowOffset() > 0) { + return Arrays.asList("LIMIT ", limit.getRowLimit(), " OFFSET ", limit.getRowOffset()); //$NON-NLS-1$ //$NON-NLS-2$ + } + return null; } /** Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sybase/SybaseSQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sybase/SybaseSQLTranslator.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sybase/SybaseSQLTranslator.java 2009-02-25 18:27:23 UTC (rev 499) @@ -24,14 +24,21 @@ */ package org.teiid.connector.jdbc.sybase; +import java.util.ArrayList; +import java.util.List; + import org.teiid.connector.jdbc.translator.AliasModifier; import org.teiid.connector.jdbc.translator.SubstringFunctionModifier; import org.teiid.connector.jdbc.translator.Translator; import com.metamatrix.connector.api.ConnectorEnvironment; import com.metamatrix.connector.api.ConnectorException; +import com.metamatrix.connector.api.ExecutionContext; import com.metamatrix.connector.api.SourceSystemFunctions; +import com.metamatrix.connector.language.ICommand; import com.metamatrix.connector.language.ILimit; +import com.metamatrix.connector.language.IOrderBy; +import com.metamatrix.connector.language.IQueryCommand; /** */ @@ -67,8 +74,28 @@ } @Override - public String addLimitString(String queryCommand, ILimit limit) { - return "SELECT TOP " + limit.getRowLimit() + " * FROM (" + queryCommand + ") AS X"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + public List<?> translateCommand(ICommand command, ExecutionContext context) { + if (!(command instanceof IQueryCommand)) { + return null; + } + IQueryCommand queryCommand = (IQueryCommand)command; + if (queryCommand.getLimit() == null) { + return null; + } + ILimit limit = queryCommand.getLimit(); + IOrderBy orderBy = queryCommand.getOrderBy(); + queryCommand.setLimit(null); + queryCommand.setOrderBy(null); + List<Object> parts = new ArrayList<Object>(6); + parts.add("SELECT TOP "); + parts.add(limit.getRowLimit()); + parts.add(" * FROM ("); + parts.add(queryCommand); + parts.add(") AS X"); + if (orderBy != null) { + parts.add(orderBy); + } + return parts; } } Deleted: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/FunctionReplacementVisitor.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/FunctionReplacementVisitor.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/FunctionReplacementVisitor.java 2009-02-25 18:27:23 UTC (rev 499) @@ -1,204 +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.translator; - -import java.util.List; -import java.util.Map; - - -import com.metamatrix.connector.language.IAggregate; -import com.metamatrix.connector.language.ICompareCriteria; -import com.metamatrix.connector.language.IExpression; -import com.metamatrix.connector.language.IFunction; -import com.metamatrix.connector.language.IGroupBy; -import com.metamatrix.connector.language.IInCriteria; -import com.metamatrix.connector.language.IInlineView; -import com.metamatrix.connector.language.IInsert; -import com.metamatrix.connector.language.IIsNullCriteria; -import com.metamatrix.connector.language.ILikeCriteria; -import com.metamatrix.connector.language.ISearchedCaseExpression; -import com.metamatrix.connector.language.ISelectSymbol; -import com.metamatrix.connector.language.ISubqueryCompareCriteria; -import com.metamatrix.connector.language.ISubqueryInCriteria; -import com.metamatrix.connector.visitor.framework.HierarchyVisitor; - -/** - */ -public class FunctionReplacementVisitor extends HierarchyVisitor { - - private Map functionModifiers; - - /** - * Set the functon modifiers. - * @param Map of function names to function modifiers. - */ - public FunctionReplacementVisitor(Map functionModifiers){ - super(); - this.functionModifiers = functionModifiers; - } - - /* - * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.IAggregate) - */ - public void visit(IAggregate obj) { - super.visit(obj); - obj.setExpression(replaceFunction(obj.getExpression())); - } - - public void visit(IInlineView obj) { - visitNode(obj.getQuery()); - } - - /* - * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.ICompareCriteria) - */ - public void visit(ICompareCriteria obj) { - super.visit(obj); - obj.setLeftExpression(replaceFunction(obj.getLeftExpression())); - obj.setRightExpression(replaceFunction(obj.getRightExpression())); - } - - /** - * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.connector.language.IFunction) - */ - public void visit(IFunction obj) { - super.visit(obj); - List<IExpression> args = obj.getParameters(); - for(int i=0; i<args.size(); i++) { - args.set(i, replaceFunction(args.get(i))); - } - } - - /** - * @see com.metamatrix.connector.visitor.framework.HierarchyVisitor#visit(com.metamatrix.connector.language.IGroupBy) - * @since 4.3 - */ - public void visit(IGroupBy obj) { - super.visit(obj); - List<IExpression> expressions = obj.getElements(); - - for (int i=0; i<expressions.size(); i++) { - IExpression expression = (IExpression)expressions.get(i); - expressions.set(i, replaceFunction(expression)); - } - } - - /* - * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.IInCriteria) - */ - public void visit(IInCriteria obj) { - super.visit(obj); - obj.setLeftExpression(replaceFunction(obj.getLeftExpression())); - List<IExpression> rightExprs = obj.getRightExpressions(); - - for(int i=0; i<rightExprs.size(); i++) { - IExpression expr = (IExpression) rightExprs.get(i); - rightExprs.set(i, replaceFunction(expr)); - } - } - - /** - * @see com.metamatrix.data.visitor.SQLStringVisitor#visit(com.metamatrix.connector.language.IInsert) - */ - public void visit(IInsert obj) { - super.visit(obj); - List<IExpression> values = obj.getValues(); - - for(int i=0; i<values.size(); i++) { - IExpression expr = (IExpression) values.get(i); - values.set(i, replaceFunction(expr)); - } - } - - /* - * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.IIsNullCriteria) - */ - public void visit(IIsNullCriteria obj) { - super.visit(obj); - obj.setExpression(replaceFunction(obj.getExpression())); - } - - /* - * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.ILikeCriteria) - */ - public void visit(ILikeCriteria obj) { - super.visit(obj); - obj.setLeftExpression(replaceFunction(obj.getLeftExpression())); - obj.setRightExpression(replaceFunction(obj.getRightExpression())); - } - - /* - * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.ISearchedCaseExpression) - */ - public void visit(ISearchedCaseExpression obj) { - super.visit(obj); - int whenCount = obj.getWhenCount(); - for(int i=0; i<whenCount; i++) { - obj.setThenExpression(i, replaceFunction(obj.getThenExpression(i))); - } - obj.setElseExpression(replaceFunction(obj.getElseExpression())); - } - - /* - * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.ISelectSymbol) - */ - public void visit(ISelectSymbol obj) { - super.visit(obj); - obj.setExpression(replaceFunction(obj.getExpression())); - } - - /* - * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.ISubqueryCompareCriteria) - */ - public void visit(ISubqueryCompareCriteria obj) { - super.visit(obj); - obj.setLeftExpression(replaceFunction(obj.getLeftExpression())); - } - - /* - * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.ISubqueryInCriteria) - */ - public void visit(ISubqueryInCriteria obj) { - super.visit(obj); - obj.setLeftExpression(replaceFunction(obj.getLeftExpression())); - } - - private IExpression replaceFunction(IExpression expression) { - if(functionModifiers != null && expression != null && expression instanceof IFunction){ - // Look for function modifier - IFunction function = (IFunction) expression; - String key = function.getName().toLowerCase(); - if(functionModifiers.containsKey(key)) { - FunctionModifier modifier = (FunctionModifier) functionModifiers.get(key); - - // Modify function and return it - return modifier.modify(function); - } - } - - // Fall through and return original expression - return expression; - } - - -} Copied: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/ReplacementVisitor.java (from rev 493, trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/FunctionReplacementVisitor.java) =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/ReplacementVisitor.java (rev 0) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/ReplacementVisitor.java 2009-02-25 18:27:23 UTC (rev 499) @@ -0,0 +1,221 @@ +/* + * 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.List; +import java.util.Map; + +import com.metamatrix.connector.api.ConnectorException; +import com.metamatrix.connector.api.ExecutionContext; +import com.metamatrix.connector.language.IAggregate; +import com.metamatrix.connector.language.ICompareCriteria; +import com.metamatrix.connector.language.IExpression; +import com.metamatrix.connector.language.IFunction; +import com.metamatrix.connector.language.IGroupBy; +import com.metamatrix.connector.language.IInCriteria; +import com.metamatrix.connector.language.IInlineView; +import com.metamatrix.connector.language.IInsert; +import com.metamatrix.connector.language.IIsNullCriteria; +import com.metamatrix.connector.language.ILikeCriteria; +import com.metamatrix.connector.language.IQueryCommand; +import com.metamatrix.connector.language.IScalarSubquery; +import com.metamatrix.connector.language.ISearchedCaseExpression; +import com.metamatrix.connector.language.ISelectSymbol; +import com.metamatrix.connector.language.ISubqueryCompareCriteria; +import com.metamatrix.connector.language.ISubqueryInCriteria; +import com.metamatrix.connector.visitor.framework.AbstractLanguageVisitor; +import com.metamatrix.core.MetaMatrixRuntimeException; + +/** + */ +public class ReplacementVisitor extends AbstractLanguageVisitor { + + private Translator translator; + private Map<String, FunctionModifier> functionModifiers; + private ExecutionContext context; + + /** + * Set the functon modifiers. + * @param Map of function names to function modifiers. + */ + public ReplacementVisitor(ExecutionContext context, Translator translator){ + this.translator = translator; + this.functionModifiers = translator.getFunctionModifiers(); + this.context = context; + } + + /* + * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.IAggregate) + */ + public void visit(IAggregate obj) { + obj.setExpression(replaceFunction(obj.getExpression())); + } + + public void visit(IInlineView obj) { + try { + obj.setQuery((IQueryCommand)translator.modifyCommand(obj.getQuery(), context)); + } catch (ConnectorException e) { + throw new MetaMatrixRuntimeException(e); + } + } + + /* + * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.ICompareCriteria) + */ + public void visit(ICompareCriteria obj) { + obj.setLeftExpression(replaceFunction(obj.getLeftExpression())); + obj.setRightExpression(replaceFunction(obj.getRightExpression())); + } + + /** + * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.connector.language.IFunction) + */ + public void visit(IFunction obj) { + List<IExpression> args = obj.getParameters(); + for(int i=0; i<args.size(); i++) { + args.set(i, replaceFunction(args.get(i))); + } + } + + /** + * @see com.metamatrix.connector.visitor.framework.HierarchyVisitor#visit(com.metamatrix.connector.language.IGroupBy) + * @since 4.3 + */ + public void visit(IGroupBy obj) { + List<IExpression> expressions = obj.getElements(); + + for (int i=0; i<expressions.size(); i++) { + IExpression expression = (IExpression)expressions.get(i); + expressions.set(i, replaceFunction(expression)); + } + } + + /* + * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.IInCriteria) + */ + public void visit(IInCriteria obj) { + obj.setLeftExpression(replaceFunction(obj.getLeftExpression())); + List<IExpression> rightExprs = obj.getRightExpressions(); + + for(int i=0; i<rightExprs.size(); i++) { + IExpression expr = (IExpression) rightExprs.get(i); + rightExprs.set(i, replaceFunction(expr)); + } + } + + /** + * @see com.metamatrix.data.visitor.SQLStringVisitor#visit(com.metamatrix.connector.language.IInsert) + */ + public void visit(IInsert obj) { + List<IExpression> values = obj.getValues(); + + for(int i=0; i<values.size(); i++) { + IExpression expr = (IExpression) values.get(i); + values.set(i, replaceFunction(expr)); + } + } + + /* + * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.IIsNullCriteria) + */ + public void visit(IIsNullCriteria obj) { + obj.setExpression(replaceFunction(obj.getExpression())); + } + + /* + * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.ILikeCriteria) + */ + public void visit(ILikeCriteria obj) { + obj.setLeftExpression(replaceFunction(obj.getLeftExpression())); + obj.setRightExpression(replaceFunction(obj.getRightExpression())); + } + + /* + * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.ISearchedCaseExpression) + */ + public void visit(ISearchedCaseExpression obj) { + int whenCount = obj.getWhenCount(); + for(int i=0; i<whenCount; i++) { + obj.setThenExpression(i, replaceFunction(obj.getThenExpression(i))); + } + obj.setElseExpression(replaceFunction(obj.getElseExpression())); + } + + /* + * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.ISelectSymbol) + */ + public void visit(ISelectSymbol obj) { + obj.setExpression(replaceFunction(obj.getExpression())); + } + + /* + * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.ISubqueryCompareCriteria) + */ + public void visit(ISubqueryCompareCriteria obj) { + try { + obj.setQuery((IQueryCommand)translator.modifyCommand(obj.getQuery(), context)); + } catch (ConnectorException e) { + throw new MetaMatrixRuntimeException(e); + } + obj.setLeftExpression(replaceFunction(obj.getLeftExpression())); + } + + @Override + public void visit(IScalarSubquery obj) { + try { + obj.setQuery((IQueryCommand)translator.modifyCommand(obj.getQuery(), context)); + } catch (ConnectorException e) { + throw new MetaMatrixRuntimeException(e); + } + } + + /* + * @see com.metamatrix.data.visitor.LanguageObjectVisitor#visit(com.metamatrix.data.language.ISubqueryInCriteria) + */ + public void visit(ISubqueryInCriteria obj) { + try { + obj.setQuery((IQueryCommand)translator.modifyCommand(obj.getQuery(), context)); + } catch (ConnectorException e) { + throw new MetaMatrixRuntimeException(e); + } + obj.setLeftExpression(replaceFunction(obj.getLeftExpression())); + } + + private IExpression replaceFunction(IExpression expression) { + if(functionModifiers != null && expression != null && expression instanceof IFunction){ + // Look for function modifier + IFunction function = (IFunction) expression; + String key = function.getName().toLowerCase(); + if(functionModifiers.containsKey(key)) { + FunctionModifier modifier = (FunctionModifier) functionModifiers.get(key); + + // Modify function and return it + return modifier.modify(function); + } + } + + // Fall through and return original expression + return expression; + } + +} Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/SQLConversionVisitor.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/SQLConversionVisitor.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/SQLConversionVisitor.java 2009-02-25 18:27:23 UTC (rev 499) @@ -28,9 +28,12 @@ import java.sql.Timestamp; import java.text.DecimalFormat; import java.util.ArrayList; +import java.util.Collections; +import java.util.IdentityHashMap; import java.util.Iterator; import java.util.List; import java.util.Map; +import java.util.Set; import com.metamatrix.connector.api.ExecutionContext; import com.metamatrix.connector.api.TypeFacility; @@ -43,9 +46,6 @@ import com.metamatrix.connector.language.ILiteral; import com.metamatrix.connector.language.IParameter; import com.metamatrix.connector.language.IProcedure; -import com.metamatrix.connector.language.IQuery; -import com.metamatrix.connector.language.IQueryCommand; -import com.metamatrix.connector.language.ISetQuery; import com.metamatrix.connector.language.IParameter.Direction; import com.metamatrix.connector.language.ISetQuery.Operation; import com.metamatrix.connector.metadata.runtime.RuntimeMetadata; @@ -64,7 +64,6 @@ private static double SCIENTIC_LOW = Math.pow(10, -3); private static double SCIENTIC_HIGH = Math.pow(10, 7); - private Map<String, FunctionModifier> modifiers; private ExecutionContext context; private Translator translator; @@ -73,11 +72,55 @@ private List preparedValues = new ArrayList(); private List preparedTypes = new ArrayList(); + private Set<ILanguageObject> recursionObjects = Collections.newSetFromMap(new IdentityHashMap<ILanguageObject, Boolean>()); + public SQLConversionVisitor(Translator translator) { this.translator = translator; this.prepared = translator.usePreparedStatements(); - this.modifiers = translator.getFunctionModifiers(); } + + @Override + public void append(ILanguageObject obj) { + if ((obj instanceof IFunction || obj instanceof ICommand || obj instanceof ILimit) && !recursionObjects.contains(obj)) { + recursionObjects.add(obj); + try { + List<?> parts = null; + if (obj instanceof IFunction) { + IFunction function = (IFunction)obj; + Map<String, FunctionModifier> functionModifiers = translator.getFunctionModifiers(); + if (functionModifiers != null) { + FunctionModifier modifier = functionModifiers.get(function.getName().toLowerCase()); + if (modifier != null) { + parts = modifier.translate(function); + } + } + } else if (obj instanceof ICommand) { + parts = translator.translateCommand((ICommand)obj, context); + } else if (obj instanceof ILimit) { + parts = translator.translateLimit((ILimit)obj, context); + } + if (parts != null) { + this.appendParts(parts); + return; + } + } finally { + recursionObjects.remove(obj); + } + } + super.append(obj); + } + + private void appendParts(List parts) { + Iterator iter = parts.iterator(); + while(iter.hasNext()) { + Object part = iter.next(); + if(part instanceof ILanguageObject) { + append((ILanguageObject)part); + } else { + buffer.append(part); + } + } + } public void visit(IBulkInsert obj) { this.prepared = true; @@ -150,37 +193,10 @@ * preparedValues is now a list of procedure params instead of just values */ this.preparedValues = obj.getParameters(); - super.buffer.append(generateSqlForStoredProcedure(obj)); + buffer.append(generateSqlForStoredProcedure(obj)); } /** - * @see com.metamatrix.connector.visitor.util.SQLStringVisitor#visit(com.metamatrix.connector.language.IFunction) - */ - public void visit(IFunction obj) { - if(this.modifiers != null) { - FunctionModifier functionModifier = (FunctionModifier)this.modifiers.get(obj.getName().toLowerCase()); - if(functionModifier != null) { - List parts = functionModifier.translate(obj); - - // null means the FunctionModifier will rely on default translation - if (parts != null) { - Iterator iter = parts.iterator(); - while(iter.hasNext()) { - Object part = iter.next(); - if(part instanceof String) { - buffer.append(part); - } else { - append((ILanguageObject)part); - } - } - return; - } - } - } - super.visit(obj); - } - - /** * @see com.metamatrix.connector.visitor.util.SQLStringVisitor#visit(com.metamatrix.connector.language.ILiteral) */ public void visit(ILiteral obj) { @@ -292,41 +308,12 @@ protected boolean useAsInGroupAlias() { return this.translator.useAsInGroupAlias(); } - + @Override - public void visit(IQuery obj) { - if (obj.getLimit() != null) { - handleLimit(obj); - } else { - super.visit(obj); - } - } - - @Override - public void visit(ISetQuery obj) { - if (obj.getLimit() != null) { - handleLimit(obj); - } else { - super.visit(obj); - } - } - - @Override protected boolean useParensForSetQueries() { return translator.useParensForSetQueries(); } - - private void handleLimit(IQueryCommand obj) { - ILimit limit = obj.getLimit(); - obj.setLimit(null); - StringBuffer current = this.buffer; - this.buffer = new StringBuffer(); - append(obj); - current.append(this.translator.addLimitString(this.buffer.toString(), limit)); - this.buffer = current; - obj.setLimit(limit); - } - + @Override protected String replaceElementName(String group, String element) { return translator.replaceElementName(group, element); Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/TranslatedCommand.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/TranslatedCommand.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/TranslatedCommand.java 2009-02-25 18:27:23 UTC (rev 499) @@ -25,14 +25,13 @@ package org.teiid.connector.jdbc.translator; import java.util.List; -import java.util.Map; - import com.metamatrix.connector.api.ConnectorException; import com.metamatrix.connector.api.ExecutionContext; import com.metamatrix.connector.api.TypeFacility; import com.metamatrix.connector.language.ICommand; import com.metamatrix.connector.language.ILiteral; +import com.metamatrix.connector.visitor.framework.DelegatingHierarchyVisitor; import com.metamatrix.connector.visitor.util.CollectorVisitor; /** @@ -48,7 +47,7 @@ private List preparedTypes; private SQLConversionVisitor sqlConversionVisitor; - private FunctionReplacementVisitor functionVisitor; + private ReplacementVisitor functionVisitor; private ExecutionContext context; private Translator sqlTranslator; @@ -59,20 +58,11 @@ public TranslatedCommand(ExecutionContext context, Translator sqlTranslator){ this.context = context; this.sqlTranslator = sqlTranslator; - - Map<String, FunctionModifier> modifiers = sqlTranslator.getFunctionModifiers(); this.sqlConversionVisitor = sqlTranslator.getSQLConversionVisitor(); - sqlConversionVisitor.setExecutionContext(context); - this.functionVisitor = new FunctionReplacementVisitor(modifiers); + this.sqlConversionVisitor.setExecutionContext(context); + this.functionVisitor = new ReplacementVisitor(context, sqlTranslator); } - public TranslatedCommand(ExecutionContext context, Translator sqlTranslator, SQLConversionVisitor sqlConversionVisitor, FunctionReplacementVisitor functionVisitor) { - this.context = context; - this.sqlTranslator = sqlTranslator; - this.sqlConversionVisitor = sqlConversionVisitor; - this.functionVisitor = functionVisitor; - } - /** * The method to cause this object to do it's thing. This method should * be called right after the constructor; afterward, all of the getter methods @@ -89,7 +79,7 @@ private String getSQL(ICommand command) throws ConnectorException { command = sqlTranslator.modifyCommand(command, context); - command.acceptVisitor(functionVisitor); + command.acceptVisitor(new DelegatingHierarchyVisitor(null, this.functionVisitor)); if (sqlTranslator.usePreparedStatements() || hasBindValue(command)) { this.sqlConversionVisitor.setPrepared(true); @@ -167,5 +157,15 @@ public boolean isPrepared() { return prepared; } + + @Override + public String toString() { + StringBuffer sb = new StringBuffer(); + if (prepared) { + sb.append("Prepared Values: ").append(preparedValues).append(" "); //$NON-NLS-1$ //$NON-NLS-2$ + } + sb.append("SQL: ").append(sql); //$NON-NLS-1$ + return sb.toString(); + } } Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/Translator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/Translator.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/Translator.java 2009-02-25 18:27:23 UTC (rev 499) @@ -52,7 +52,9 @@ import com.metamatrix.connector.api.ValueTranslator; import com.metamatrix.connector.internal.ConnectorPropertyNames; import com.metamatrix.connector.language.ICommand; +import com.metamatrix.connector.language.IFunction; import com.metamatrix.connector.language.ILanguageFactory; +import com.metamatrix.connector.language.ILanguageObject; import com.metamatrix.connector.language.ILimit; import com.metamatrix.connector.language.IParameter; import com.metamatrix.connector.language.ISetQuery; @@ -192,6 +194,14 @@ return command; } + public List<?> translateCommand(ICommand command, ExecutionContext context) { + return null; + } + + public List<?> translateLimit(ILimit limit, ExecutionContext context) { + return null; + } + /** * Return a map of function name in lower case to FunctionModifier. * @return Map of function name to FunctionModifier. @@ -287,10 +297,6 @@ return useComments; } - public String addLimitString(String queryCommand, ILimit limit) { - return queryCommand + " " + limit.toString(); //$NON-NLS-1$ - } - /** * Indicates whether group alias should be of the form * "...FROM groupA AS X" or "...FROM groupA X". Certain @@ -562,13 +568,25 @@ return new Float(value); } case TIME_CODE: { - return results.getTime(columnIndex, getDatabaseCalendar()); + Calendar cal = getDatabaseCalendar(); + if (cal != null) { + return results.getTime(columnIndex, cal); + } + return results.getTime(columnIndex); } case DATE_CODE: { - return results.getDate(columnIndex, getDatabaseCalendar()); + Calendar cal = getDatabaseCalendar(); + if (cal != null) { + return results.getDate(columnIndex, cal); + } + return results.getDate(columnIndex); } case TIMESTAMP_CODE: { - return results.getTimestamp(columnIndex, getDatabaseCalendar()); + Calendar cal = getDatabaseCalendar(); + if (cal != null) { + return results.getTimestamp(columnIndex, cal); + } + return results.getTimestamp(columnIndex); } case BLOB_CODE: { try { @@ -635,13 +653,25 @@ return new Float(value); } case TIME_CODE: { - return results.getTime(parameterIndex, getDatabaseCalendar()); + Calendar cal = getDatabaseCalendar(); + if (cal != null) { + return results.getTime(parameterIndex, cal); + } + return results.getTime(parameterIndex); } case DATE_CODE: { - return results.getDate(parameterIndex, getDatabaseCalendar()); + Calendar cal = getDatabaseCalendar(); + if (cal != null) { + return results.getDate(parameterIndex, cal); + } + return results.getDate(parameterIndex); } case TIMESTAMP_CODE: { - return results.getTimestamp(parameterIndex, getDatabaseCalendar()); + Calendar cal = getDatabaseCalendar(); + if (cal != null) { + return results.getTimestamp(parameterIndex, cal); + } + return results.getTimestamp(parameterIndex); } case BLOB_CODE: { try { @@ -703,5 +733,5 @@ public SQLConversionVisitor getSQLConversionVisitor() { return new SQLConversionVisitor(this); } - + } Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/access/TestAccessSQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/access/TestAccessSQLTranslator.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/access/TestAccessSQLTranslator.java 2009-02-25 18:27:23 UTC (rev 499) @@ -66,7 +66,7 @@ public void testRowLimit() throws Exception { String input = "select intkey from bqt1.smalla limit 100"; //$NON-NLS-1$ - String output = "SELECT TOP 100 SmallA.IntKey FROM SmallA"; //$NON-NLS-1$ + String output = "SELECT TOP 100 * FROM (SELECT SmallA.IntKey FROM SmallA) AS X"; //$NON-NLS-1$ helpTestVisitor( input, @@ -76,7 +76,7 @@ public void testRowLimit1() throws Exception { String input = "select distinct intkey from bqt1.smalla limit 100"; //$NON-NLS-1$ - String output = "SELECT DISTINCT TOP 100 SmallA.IntKey FROM SmallA"; //$NON-NLS-1$ + String output = "SELECT TOP 100 * FROM (SELECT DISTINCT SmallA.IntKey FROM SmallA) AS X"; //$NON-NLS-1$ helpTestVisitor( input, Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sqlserver/TestSqlServerConversionVisitor.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sqlserver/TestSqlServerConversionVisitor.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sqlserver/TestSqlServerConversionVisitor.java 2009-02-25 18:27:23 UTC (rev 499) @@ -22,48 +22,29 @@ package org.teiid.connector.jdbc.sqlserver; -import java.util.Map; import java.util.Properties; +import org.junit.Assert; +import org.junit.BeforeClass; +import org.junit.Test; import org.teiid.connector.jdbc.MetadataFactory; -import org.teiid.connector.jdbc.sqlserver.SqlServerSQLTranslator; -import org.teiid.connector.jdbc.translator.FunctionReplacementVisitor; -import org.teiid.connector.jdbc.translator.SQLConversionVisitor; import org.teiid.connector.jdbc.translator.TranslatedCommand; -import junit.framework.TestCase; - import com.metamatrix.cdk.api.EnvironmentUtility; import com.metamatrix.connector.api.ConnectorException; import com.metamatrix.connector.language.ICommand; /** */ -public class TestSqlServerConversionVisitor extends TestCase { +public class TestSqlServerConversionVisitor { - private static final Properties EMPTY_PROPERTIES = new Properties(); - private static Map MODIFIERS; + private static SqlServerSQLTranslator trans = new SqlServerSQLTranslator(); - static { - SqlServerSQLTranslator trans = new SqlServerSQLTranslator(); - - try { - trans.initialize(EnvironmentUtility.createEnvironment(new Properties(), false)); - } catch(ConnectorException e) { - e.printStackTrace(); - } - - MODIFIERS = trans.getFunctionModifiers(); + @BeforeClass + public static void setup() throws ConnectorException { + trans.initialize(EnvironmentUtility.createEnvironment(new Properties(), false)); } - /** - * Constructor for TestSqlServerConversionVisitor. - * @param name - */ - public TestSqlServerConversionVisitor(String name) { - super(name); - } - public String getTestVDB() { return MetadataFactory.PARTS_VDB; } @@ -72,67 +53,54 @@ return MetadataFactory.BQT_VDB; } - public void helpTestVisitor(String vdb, String input, Map modifiers, String expectedOutput) throws ConnectorException { - helpTestVisitor(vdb, input, modifiers, new String[] {expectedOutput}, EMPTY_PROPERTIES); + public void helpTestVisitor(String vdb, String input, String expectedOutput) throws ConnectorException { + helpTestVisitor(vdb, input, new String[] {expectedOutput}); } - public void helpTestVisitor(String vdb, String input, Map modifiers, String[] expectedOutputs, Properties props) throws ConnectorException { - // Convert from sql to objects + public void helpTestVisitor(String vdb, String input, String[] expectedOutputs) throws ConnectorException { ICommand obj = MetadataFactory.helpTranslate(vdb, input); - - // Apply function replacement - FunctionReplacementVisitor funcVisitor = new FunctionReplacementVisitor(modifiers); - - SqlServerSQLTranslator trans = new SqlServerSQLTranslator(); - trans.initialize(EnvironmentUtility.createEnvironment(props, false)); - // Convert back to SQL - SQLConversionVisitor sqlVisitor = trans.getSQLConversionVisitor(); - - TranslatedCommand tc = new TranslatedCommand(EnvironmentUtility.createSecurityContext("user"), trans, sqlVisitor, funcVisitor); //$NON-NLS-1$ + TranslatedCommand tc = new TranslatedCommand(EnvironmentUtility.createSecurityContext("user"), trans); //$NON-NLS-1$ tc.translateCommand(obj); - - // Check stuff - - assertEquals("Did not get correct sql", expectedOutputs[0], tc.getSql()); //$NON-NLS-1$ + Assert.assertEquals("Did not get correct sql", expectedOutputs[0], tc.getSql()); //$NON-NLS-1$ } + @Test public void testModFunction() throws Exception { String input = "SELECT mod(CONVERT(PART_ID, INTEGER), 13) FROM parts"; //$NON-NLS-1$ String output = "SELECT (convert(int, PARTS.PART_ID) % 13) FROM PARTS"; //$NON-NLS-1$ helpTestVisitor(getTestVDB(), input, - MODIFIERS, output); } + @Test public void testConcatFunction() throws Exception { String input = "SELECT concat(part_name, 'b') FROM PARTS"; //$NON-NLS-1$ String output = "SELECT (PARTS.PART_NAME + 'b') FROM PARTS"; //$NON-NLS-1$ helpTestVisitor(getTestVDB(), input, - MODIFIERS, output); } + @Test public void testDayOfMonthFunction() throws Exception { String input = "SELECT dayofmonth(convert(PARTS.PART_ID, date)) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT day(convert(datetime, PARTS.PART_ID)) FROM PARTS"; //$NON-NLS-1$ helpTestVisitor(getTestVDB(), input, - MODIFIERS, output); } + @Test public void testRowLimit() throws Exception { String input = "select intkey from bqt1.smalla limit 100"; //$NON-NLS-1$ String output = "SELECT TOP 100 * FROM (SELECT SmallA.IntKey FROM SmallA) AS X"; //$NON-NLS-1$ helpTestVisitor(getBQTVDB(), input, - MODIFIERS, output); } Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sybase/TestSybaseSQLConversionVisitor.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sybase/TestSybaseSQLConversionVisitor.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sybase/TestSybaseSQLConversionVisitor.java 2009-02-25 18:27:23 UTC (rev 499) @@ -22,30 +22,28 @@ package org.teiid.connector.jdbc.sybase; +import static org.junit.Assert.assertEquals; + import java.util.Properties; +import org.junit.BeforeClass; +import org.junit.Test; import org.teiid.connector.jdbc.MetadataFactory; -import org.teiid.connector.jdbc.sybase.SybaseSQLTranslator; -import org.teiid.connector.jdbc.translator.FunctionReplacementVisitor; -import org.teiid.connector.jdbc.translator.SQLConversionVisitor; import org.teiid.connector.jdbc.translator.TranslatedCommand; -import junit.framework.TestCase; - import com.metamatrix.cdk.api.EnvironmentUtility; import com.metamatrix.connector.api.ConnectorException; import com.metamatrix.connector.language.ICommand; /** */ -public class TestSybaseSQLConversionVisitor extends TestCase { +public class TestSybaseSQLConversionVisitor { - /** - * Constructor for TestSqlServerConversionVisitor. - * @param name - */ - public TestSybaseSQLConversionVisitor(String name) { - super(name); + private static SybaseSQLTranslator trans = new SybaseSQLTranslator(); + + @BeforeClass + public static void setup() throws ConnectorException { + trans.initialize(EnvironmentUtility.createEnvironment(new Properties(), false)); } public String getTestVDB() { @@ -60,30 +58,17 @@ // Convert from sql to objects ICommand obj = MetadataFactory.helpTranslate(vdb, input); - // Apply function replacement - SybaseSQLTranslator trans = new SybaseSQLTranslator(); - try { - trans.initialize(EnvironmentUtility.createEnvironment(new Properties(), false)); - } catch (ConnectorException e1) { - throw new RuntimeException(e1); - } - FunctionReplacementVisitor funcVisitor = new FunctionReplacementVisitor(trans.getFunctionModifiers()); - - // Convert back to SQL - SQLConversionVisitor sqlVisitor = trans.getSQLConversionVisitor(); - TranslatedCommand tc = new TranslatedCommand(EnvironmentUtility.createSecurityContext("user"), trans, sqlVisitor, funcVisitor); + TranslatedCommand tc = new TranslatedCommand(EnvironmentUtility.createSecurityContext("user"), trans); try { tc.translateCommand(obj); } catch (ConnectorException e) { throw new RuntimeException(e); } - // Check stuff -// System.out.println("in: " + input); //$NON-NLS-1$ - //System.out.println("out: " + tc.getSql()); //$NON-NLS-1$ assertEquals("Did not get correct sql", expectedOutput, tc.getSql()); //$NON-NLS-1$ } + @Test public void testModFunction() { String input = "SELECT mod(CONVERT(PART_ID, INTEGER), 13) FROM parts"; //$NON-NLS-1$ //String output = "SELECT (PARTS.PART_ID % 13) FROM PARTS"; //$NON-NLS-1$ @@ -94,6 +79,7 @@ output); } + @Test public void testConcatFunction() { String input = "SELECT concat(part_name, 'b') FROM PARTS"; //$NON-NLS-1$ String output = "SELECT (PARTS.PART_NAME + 'b') FROM PARTS"; //$NON-NLS-1$ @@ -103,6 +89,7 @@ output); } + @Test public void testLcaseFunction() { String input = "SELECT lcase(PART_NAME) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT lower(PARTS.PART_NAME) FROM PARTS"; //$NON-NLS-1$ @@ -111,6 +98,7 @@ output); } + @Test public void testUcaseFunction() { String input = "SELECT ucase(PART_NAME) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT upper(PARTS.PART_NAME) FROM PARTS"; //$NON-NLS-1$ @@ -120,6 +108,7 @@ output); } + @Test public void testLengthFunction() { String input = "SELECT length(PART_NAME) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT char_length(PARTS.PART_NAME) FROM PARTS"; //$NON-NLS-1$ @@ -129,6 +118,7 @@ output); } + @Test public void testSubstring2ArgFunction() { String input = "SELECT substring(PART_NAME, 3) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT substring(PARTS.PART_NAME, 3, char_length(PARTS.PART_NAME)) FROM PARTS"; //$NON-NLS-1$ @@ -138,6 +128,7 @@ output); } + @Test public void testSubstring3ArgFunction() { String input = "SELECT substring(PART_NAME, 3, 5) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT substring(PARTS.PART_NAME, 3, 5) FROM PARTS"; //$NON-NLS-1$ @@ -147,6 +138,7 @@ output); } + @Test public void testConvertFunctionInteger() { String input = "SELECT convert(PARTS.PART_ID, integer) FROM PARTS"; //$NON-NLS-1$ //String output = "SELECT PARTS.PART_ID FROM PARTS"; //$NON-NLS-1$ @@ -157,6 +149,7 @@ output); } + @Test public void testConvertFunctionChar() { String input = "SELECT convert(PART_NAME, char) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT convert(char, PARTS.PART_NAME) FROM PARTS"; //$NON-NLS-1$ @@ -166,6 +159,7 @@ output); } + @Test public void testConvertFunctionBoolean() { String input = "SELECT convert(PART_ID, boolean) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT convert(bit, PARTS.PART_ID) FROM PARTS"; //$NON-NLS-1$ @@ -175,6 +169,7 @@ output); } + @Test public void testIfNullFunction() { String input = "SELECT ifnull(PART_NAME, 'abc') FROM PARTS"; //$NON-NLS-1$ String output = "SELECT isnull(PARTS.PART_NAME, 'abc') FROM PARTS"; //$NON-NLS-1$ @@ -184,24 +179,28 @@ output); } + @Test public void testDateLiteral() { helpTestVisitor(getTestVDB(), "select {d'2002-12-31'} FROM parts", //$NON-NLS-1$ "SELECT {d'2002-12-31'} FROM PARTS"); //$NON-NLS-1$ } + @Test public void testTimeLiteral() { helpTestVisitor(getTestVDB(), "select {t'13:59:59'} FROM parts", //$NON-NLS-1$ "SELECT {ts'1970-01-01 13:59:59'} FROM PARTS"); //$NON-NLS-1$ } + @Test public void testTimestampLiteral() { helpTestVisitor(getTestVDB(), "select {ts'2002-12-31 13:59:59'} FROM parts", //$NON-NLS-1$ "SELECT {ts'2002-12-31 13:59:59.0'} FROM PARTS"); //$NON-NLS-1$ } + @Test public void testDefect12120() { helpTestVisitor(getBQTVDB(), "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA WHERE BQT1.SmallA.BooleanValue IN ({b'false'}, {b'true'}) ORDER BY IntKey", //$NON-NLS-1$ @@ -209,6 +208,7 @@ } + @Test public void testConvertFunctionString() throws Exception { String input = "SELECT convert(PARTS.PART_ID, integer) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT convert(int, PARTS.PART_ID) FROM PARTS"; //$NON-NLS-1$ @@ -217,7 +217,8 @@ input, output); } - + + @Test public void testNonIntMod() throws Exception { String input = "select mod(intkey/1.5, 3) from bqt1.smalla"; //$NON-NLS-1$ String output = "SELECT ((convert(float, SmallA.IntKey) / 1.5) - (floor(((convert(float, SmallA.IntKey) / 1.5) / 3.0)) * 3.0)) FROM SmallA"; //$NON-NLS-1$ Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/util/TestFunctionReplacementVisitor.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/util/TestFunctionReplacementVisitor.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/util/TestFunctionReplacementVisitor.java 2009-02-25 18:27:23 UTC (rev 499) @@ -27,11 +27,14 @@ import org.teiid.connector.jdbc.MetadataFactory; import org.teiid.connector.jdbc.translator.DropFunctionModifier; -import org.teiid.connector.jdbc.translator.FunctionReplacementVisitor; +import org.teiid.connector.jdbc.translator.FunctionModifier; +import org.teiid.connector.jdbc.translator.ReplacementVisitor; +import org.teiid.connector.jdbc.translator.Translator; import junit.framework.TestCase; import com.metamatrix.connector.language.ICommand; +import com.metamatrix.connector.visitor.framework.DelegatingHierarchyVisitor; /** */ @@ -43,7 +46,6 @@ */ public TestFunctionReplacementVisitor(String name) { super(name); - System.setProperty("metamatrix.config.none", "true"); } public Map getModifierSet1() { @@ -58,11 +60,16 @@ return MetadataFactory.PARTS_VDB; } - public void helpTestVisitor(String vdb, String input, Map modifiers, String expectedOutput) { + public void helpTestVisitor(String vdb, String input, final Map modifiers, String expectedOutput) { ICommand obj = MetadataFactory.helpTranslate(vdb, input); - FunctionReplacementVisitor visitor = new FunctionReplacementVisitor(modifiers); - obj.acceptVisitor(visitor); + ReplacementVisitor visitor = new ReplacementVisitor(null, new Translator() { + @Override + public Map<String, FunctionModifier> getFunctionModifiers() { + return modifiers; + } + }); + obj.acceptVisitor(new DelegatingHierarchyVisitor(null, visitor)); //System.out.println(obj); assertEquals("Did not get expected sql", expectedOutput, obj.toString()); //$NON-NLS-1$ Modified: trunk/connectors/sandbox/connector-oracle-spatial/src/main/java/com/metamatrix/connector/jdbc/oracle/spatial/OracleSpatialSQLTranslator.java =================================================================== --- trunk/connectors/sandbox/connector-oracle-spatial/src/main/java/com/metamatrix/connector/jdbc/oracle/spatial/OracleSpatialSQLTranslator.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/connectors/sandbox/connector-oracle-spatial/src/main/java/com/metamatrix/connector/jdbc/oracle/spatial/OracleSpatialSQLTranslator.java 2009-02-25 18:27:23 UTC (rev 499) @@ -70,7 +70,7 @@ * This method is overridden to modify the incoming command to add the hint to the ISelect in the command. */ @Override - public ICommand modifyCommand(ICommand command, com.metamatrix.connector.api.ExecutionContext context) throws ConnectorException { + public ICommand modifyCommand(ICommand command, ExecutionContext context) throws ConnectorException { if (command instanceof IQuery) { IQuery query = (IQuery)command; Modified: trunk/test-integration/src/test/java/com/metamatrix/connector/jdbc/extension/TestSQLConversionVisitor.java =================================================================== --- trunk/test-integration/src/test/java/com/metamatrix/connector/jdbc/extension/TestSQLConversionVisitor.java 2009-02-25 18:12:40 UTC (rev 498) +++ trunk/test-integration/src/test/java/com/metamatrix/connector/jdbc/extension/TestSQLConversionVisitor.java 2009-02-25 18:27:23 UTC (rev 499) @@ -22,18 +22,15 @@ package com.metamatrix.connector.jdbc.extension; -import java.util.HashMap; -import java.util.Map; import java.util.Properties; +import junit.framework.TestCase; + import org.teiid.connector.jdbc.JDBCPropertyNames; -import org.teiid.connector.jdbc.translator.FunctionReplacementVisitor; import org.teiid.connector.jdbc.translator.SQLConversionVisitor; import org.teiid.connector.jdbc.translator.TranslatedCommand; import org.teiid.connector.jdbc.translator.Translator; -import junit.framework.TestCase; - import com.metamatrix.cdk.api.EnvironmentUtility; import com.metamatrix.cdk.api.TranslationUtility; import com.metamatrix.connector.api.ConnectorException; @@ -96,20 +93,20 @@ return util.parseCommand(sql); } - public void helpTestVisitor(String vdb, String input, Map modifiers, String expectedOutput) { - helpTestVisitor(vdb, input, modifiers, expectedOutput, false); + public void helpTestVisitor(String vdb, String input, String expectedOutput) { + helpTestVisitor(vdb, input, expectedOutput, false); } - public void helpTestVisitor(String vdb, String input, Map modifiers, String expectedOutput, boolean useMetadata) { - helpTestVisitor(vdb, input, modifiers, expectedOutput, useMetadata, false); + public void helpTestVisitor(String vdb, String input, String expectedOutput, boolean useMetadata) { + helpTestVisitor(vdb, input, expectedOutput, useMetadata, false); } - public void helpTestVisitor(String vdb, String input, Map modifiers, String expectedOutput, boolean useMetadata, boolean usePreparedStatement) { + public void helpTestVisitor(String vdb, String input, String expectedOutput, boolean useMetadata, boolean usePreparedStatement) { // Convert from sql to objects ICommand obj = helpTranslate(vdb, input); try { - helpTestVisitorWithCommand(modifiers, expectedOutput, obj, useMetadata, usePreparedStatement); + helpTestVisitorWithCommand(expectedOutput, obj, useMetadata, usePreparedStatement); } catch (ConnectorException e) { throw new RuntimeException(e); } @@ -128,28 +125,24 @@ } /** - * @param modifiers * @param expectedOutput - * @param obj + * @param obj * @throws ConnectorException * @since 4.2 */ - private void helpTestVisitorWithCommand(Map modifiers, - String expectedOutput, + private void helpTestVisitorWithCommand(String expectedOutput, ICommand obj, - boolean useMetadata, boolean usePreparedStatement) throws ConnectorException { + boolean useMetadata, + boolean usePreparedStatement) throws ConnectorException { // Apply function replacement - FunctionReplacementVisitor funcVisitor = new FunctionReplacementVisitor(modifiers); Translator trans = new Translator(); Properties p = new Properties(); if (usePreparedStatement) { p.setProperty(JDBCPropertyNames.USE_BIND_VARIABLES, Boolean.TRUE.toString()); } trans.initialize(EnvironmentUtility.createEnvironment(p, false)); - // Convert back to SQL - SQLConversionVisitor sqlVisitor = trans.getSQLConversionVisitor(); - TranslatedCommand tc = new TranslatedCommand(new FakeExecutionContextImpl(), trans, sqlVisitor, funcVisitor); + TranslatedCommand tc = new TranslatedCommand(new FakeExecutionContextImpl(), trans); tc.translateCommand(obj); assertEquals("Did not get correct sql", expectedOutput, tc.getSql()); //$NON-NLS-1$ @@ -158,252 +151,216 @@ public void testSimple() { helpTestVisitor(getTestVDB(), "select part_name from parts", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS"); //$NON-NLS-1$ } public void testAliasInSelect() { helpTestVisitor(getTestVDB(), "select part_name as x from parts", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME AS x FROM PARTS"); //$NON-NLS-1$ } public void testAliasedGroup() { helpTestVisitor(getTestVDB(), "select y.part_name from parts y", //$NON-NLS-1$ - new HashMap(), "SELECT y.PART_NAME FROM PARTS AS y"); //$NON-NLS-1$ } public void testAliasedGroupAndElement() { helpTestVisitor(getTestVDB(), "select y.part_name AS z from parts y", //$NON-NLS-1$ - new HashMap(), "SELECT y.PART_NAME AS z FROM PARTS AS y"); //$NON-NLS-1$ } public void testLiteralString() { helpTestVisitor(getTestVDB(), "select 'x' from parts", //$NON-NLS-1$ - new HashMap(), "SELECT 'x' FROM PARTS"); //$NON-NLS-1$ } public void testLiteralInteger() { helpTestVisitor(getTestVDB(), "select 5 from parts", //$NON-NLS-1$ - new HashMap(), "SELECT 5 FROM PARTS"); //$NON-NLS-1$ } public void testLiteralFloat() { helpTestVisitor(getTestVDB(), "select 5.2 from parts", //$NON-NLS-1$ - new HashMap(), "SELECT 5.2 FROM PARTS"); //$NON-NLS-1$ } public void testLiteralLowFloat() { helpTestVisitor(getTestVDB(), "select 0.012 from parts", //$NON-NLS-1$ - new HashMap(), "SELECT 0.012 FROM PARTS"); //$NON-NLS-1$ } public void testLiteralLowFloat2() { helpTestVisitor(getTestVDB(), "select 0.00012 from parts", //$NON-NLS-1$ - new HashMap(), "SELECT 0.00012 FROM PARTS"); //$NON-NLS-1$ } public void testLiteralHighFloat() { helpTestVisitor(getTestVDB(), "select 12345.123 from parts", //$NON-NLS-1$ - new HashMap(), "SELECT 12345.123 FROM PARTS"); //$NON-NLS-1$ } public void testLiteralHighFloat2() { helpTestVisitor(getTestVDB(), "select 1234567890.1234567 from parts", //$NON-NLS-1$ - new HashMap(), "SELECT 1234567890.1234567 FROM PARTS"); //$NON-NLS-1$ } public void testLiteralBoolean() { helpTestVisitor(getTestVDB(), "select {b'true'}, {b'false'} from parts", //$NON-NLS-1$ - new HashMap(), "SELECT 1, 0 FROM PARTS"); //$NON-NLS-1$ } public void testLiteralDate() { helpTestVisitor(getTestVDB(), "select {d'2003-12-31'} from parts", //$NON-NLS-1$ - new HashMap(), "SELECT {d'2003-12-31'} FROM PARTS"); //$NON-NLS-1$ } public void testLiteralTime() { helpTestVisitor(getTestVDB(), "select {t'23:59:59'} from parts", //$NON-NLS-1$ - new HashMap(), "SELECT {t'23:59:59'} FROM PARTS"); //$NON-NLS-1$ } public void testLiteralNull() { helpTestVisitor(getTestVDB(), "select null from parts", //$NON-NLS-1$ - new HashMap(), "SELECT NULL FROM PARTS"); //$NON-NLS-1$ } public void testLiteralTimestamp() { helpTestVisitor(getTestVDB(), "select {ts'2003-12-31 23:59:59.123'} from parts", //$NON-NLS-1$ - new HashMap(), "SELECT {ts'2003-12-31 23:59:59.123'} FROM PARTS"); //$NON-NLS-1$ } public void testSQL89Join() { helpTestVisitor(getTestVDB(), "select p.part_name from parts p, supplier_parts s where p.part_id = s.part_id", //$NON-NLS-1$ - new HashMap(), "SELECT p.PART_NAME FROM PARTS AS p, SUPPLIER_PARTS AS s WHERE p.PART_ID = s.PART_ID"); //$NON-NLS-1$ } public void testSQL92Join() { helpTestVisitor(getTestVDB(), "select p.part_name from parts p join supplier_parts s on p.part_id = s.part_id", //$NON-NLS-1$ - new HashMap(), "SELECT p.PART_NAME FROM PARTS AS p INNER JOIN SUPPLIER_PARTS AS s ON p.PART_ID = s.PART_ID"); //$NON-NLS-1$ } public void testSelfJoin() { helpTestVisitor(getTestVDB(), "select p.part_name from parts p join parts p2 on p.part_id = p2.part_id", //$NON-NLS-1$ - new HashMap(), "SELECT p.PART_NAME FROM PARTS AS p INNER JOIN PARTS AS p2 ON p.PART_ID = p2.PART_ID"); //$NON-NLS-1$ } public void testRightOuterJoin() { helpTestVisitor(getTestVDB(), "select p.part_name from parts p right join supplier_parts s on p.part_id = s.part_id", //$NON-NLS-1$ - new HashMap(), "SELECT p.PART_NAME FROM SUPPLIER_PARTS AS s LEFT OUTER JOIN PARTS AS p ON p.PART_ID = s.PART_ID"); //$NON-NLS-1$ } public void testLeftOuterJoin() { helpTestVisitor(getTestVDB(), "select p.part_name from parts p left join supplier_parts s on p.part_id = s.part_id", //$NON-NLS-1$ - new HashMap(), "SELECT p.PART_NAME FROM PARTS AS p LEFT OUTER JOIN SUPPLIER_PARTS AS s ON p.PART_ID = s.PART_ID"); //$NON-NLS-1$ } public void testFullOuterJoin() { helpTestVisitor(getTestVDB(), "select p.part_name from parts p full join supplier_parts s on p.part_id = s.part_id", //$NON-NLS-1$ - new HashMap(), "SELECT p.PART_NAME FROM PARTS AS p FULL OUTER JOIN SUPPLIER_PARTS AS s ON p.PART_ID = s.PART_ID"); //$NON-NLS-1$ } public void testCompare1() { helpTestVisitor(getTestVDB(), "select part_name from parts where part_id = 'x'", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID = 'x'"); //$NON-NLS-1$ } public void testCompare2() { helpTestVisitor(getTestVDB(), "select part_name from parts where part_id <> 'x'", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID <> 'x'"); //$NON-NLS-1$ } public void testCompare3() { helpTestVisitor(getTestVDB(), "select part_name from parts where part_id < 'x'", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID < 'x'"); //$NON-NLS-1$ } public void testCompare4() { helpTestVisitor(getTestVDB(), "select part_name from parts where part_id <= 'x'", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID <= 'x'"); //$NON-NLS-1$ } public void testCompare5() { helpTestVisitor(getTestVDB(), "select part_name from parts where part_id > 'x'", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID > 'x'"); //$NON-NLS-1$ } public void testCompare6() { helpTestVisitor(getTestVDB(), "select part_name from parts where part_id >= 'x'", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID >= 'x'"); //$NON-NLS-1$ } public void testIn1() { helpTestVisitor(getTestVDB(), "select part_name from parts where part_id in ('x')", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID = 'x'"); //$NON-NLS-1$ } public void testIn2() { helpTestVisitor(getTestVDB(), "select part_name from parts where part_id in ('x', 'y')", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID IN ('x', 'y')"); //$NON-NLS-1$ } public void testIn3() { helpTestVisitor(getTestVDB(), "select part_name from parts where part_id not in ('x', 'y')", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID NOT IN ('x', 'y')"); //$NON-NLS-1$ } public void testIsNull1() { helpTestVisitor(getTestVDB(), "select part_name from parts where part_id is null", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID IS NULL"); //$NON-NLS-1$ } public void testIsNull2() { helpTestVisitor(getTestVDB(), "select part_name from parts where part_id is not null", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID IS NOT NULL"); //$NON-NLS-1$ } public void testInsertNull() { helpTestVisitor(getTestVDB(), "insert into parts (part_id, part_name, part_color, part_weight) values ('a', null, 'c', 'd')", //$NON-NLS-1$ - new HashMap(), "INSERT INTO PARTS (PART_ID, PART_NAME, PART_COLOR, PART_WEIGHT) VALUES ('a', NULL, 'c', 'd')"); //$NON-NLS-1$ } public void testUpdateNull() { helpTestVisitor(getTestVDB(), "update parts set part_weight = null where part_color = 'b'", //$NON-NLS-1$ - new HashMap(), "UPDATE PARTS SET PART_WEIGHT = NULL WHERE PARTS.PART_COLOR = 'b'"); //$NON-NLS-1$ } public void testUpdateWhereNull() { helpTestVisitor(getTestVDB(), "update parts set part_weight = 'a' where part_weight = null", //$NON-NLS-1$ - new HashMap(), "UPDATE PARTS SET PART_WEIGHT = 'a' WHERE NULL <> NULL"); //$NON-NLS-1$ } @@ -427,41 +384,41 @@ ICommand result = new LanguageBridgeFactory(metadata).translate(command); - helpTestVisitorWithCommand(new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS GROUP BY concat(PARTS.PART_ID, 'a')", - result, //$NON-NLS-1$ - false, false); + helpTestVisitorWithCommand("SELECT PARTS.PART_NAME FROM PARTS GROUP BY concat(PARTS.PART_ID, 'a')", result, + false, //$NON-NLS-1$ + false); } public void testPreparedStatementCreationWithUpdate() { helpTestVisitor(getTestVDB(), "update parts set part_weight = 'a' where part_weight < 5", //$NON-NLS-1$ - new HashMap(), "UPDATE PARTS SET PART_WEIGHT = ? WHERE PARTS.PART_WEIGHT < ?", - false, true); //$NON-NLS-1$ + false, + true); //$NON-NLS-1$ } public void testPreparedStatementCreationWithInsert() { helpTestVisitor(getTestVDB(), "insert into parts (part_weight) values (5)", //$NON-NLS-1$ - new HashMap(), "INSERT INTO PARTS (PART_WEIGHT) VALUES (?)", - false, true); //$NON-NLS-1$ + false, + true); //$NON-NLS-1$ } public void testPreparedStatementCreationWithSelect() { helpTestVisitor(getTestVDB(), "select part_name from parts where part_id not in ('x', 'y') and part_weight < 6", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS WHERE (PARTS.PART_ID NOT IN (?, ?)) AND (PARTS.PART_WEIGHT < ?)", - false, true); //$NON-NLS-1$ + false, + true); //$NON-NLS-1$ } public void testPreparedStatementCreationWithLike() { helpTestVisitor(getTestVDB(), "select part_name from parts where part_name like '%foo'", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_NAME LIKE ?", - false, true); //$NON-NLS-1$ + false, + true); //$NON-NLS-1$ } /** @@ -471,9 +428,9 @@ public void testPreparedStatementCreationWithLeftConstant() { helpTestVisitor(getTestVDB(), "select part_name from parts where 'x' = 'y'", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS WHERE 1 = ?", - false, true); //$NON-NLS-1$ + false, + true); //$NON-NLS-1$ } /** @@ -483,17 +440,17 @@ public void testPreparedStatementCreationWithFunction() { helpTestVisitor(getTestVDB(), "select part_name from parts where concat(part_name, 'x') = concat('y', part_weight)", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS WHERE concat(PARTS.PART_NAME, 'x') = concat('y', PARTS.PART_WEIGHT)", - false, true); //$NON-NLS-1$ + false, + true); //$NON-NLS-1$ } public void testPreparedStatementCreationWithCase() { helpTestVisitor(getTestVDB(), "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_WEIGHT = CASE WHEN PARTS.PART_NAME='a' THEN 'b' ELSE 'c' END", //$NON-NLS-1$ - new HashMap(), "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_WEIGHT = CASE WHEN PARTS.PART_NAME = ? THEN 'b' ELSE 'c' END", - false, true); //$NON-NLS-1$ + false, + true); //$NON-NLS-1$ } public void testVisitIDeleteWithComment() throws Exception {