Author: shawkins Date: 2012-10-03 12:54:06 -0400 (Wed, 03 Oct 2012) New Revision: 4510 Added: trunk/connectors/translator-olap/src/test/ trunk/connectors/translator-olap/src/test/java/ trunk/connectors/translator-olap/src/test/java/org/ trunk/connectors/translator-olap/src/test/java/org/teiid/ trunk/connectors/translator-olap/src/test/java/org/teiid/translator/ trunk/connectors/translator-olap/src/test/java/org/teiid/translator/olap/ trunk/connectors/translator-olap/src/test/java/org/teiid/translator/olap/TestOlapTranslator.java Modified: trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/SQLConversionVisitor.java trunk/connectors/translator-jpa/src/main/java/org/teiid/translator/jpa/JPA2ExecutionFactory.java trunk/connectors/translator-jpa/src/main/java/org/teiid/translator/jpa/JPQLDirectQueryExecution.java trunk/connectors/translator-olap/src/main/java/org/teiid/translator/olap/OlapQueryExecution.java trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/DirectQueryExecution.java trunk/test-integration/common/src/test/java/org/teiid/connector/visitor/util/TestSQLStringVisitor.java Log: TEIID-2177 adding jpa canned queries Modified: trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java =================================================================== --- trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java 2012-10-03 14:27:31 UTC (rev 4509) +++ trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java 2012-10-03 16:54:06 UTC (rev 4510) @@ -26,7 +26,6 @@ import java.util.Arrays; import java.util.HashSet; -import java.util.LinkedList; import java.util.List; import java.util.Set; import java.util.regex.Matcher; @@ -980,26 +979,29 @@ return false; } - public static List<Object> parseNativeQueryParts(String nativeQuery, List<Argument> list) { + public interface Substitutor { + void substitute(Argument arg, StringBuilder builder, int index); + } + + public static void parseNativeQueryParts(String nativeQuery, List<Argument> list, StringBuilder stringBuilder, Substitutor substitutor) { Pattern pattern = Pattern.compile("\\$+\\d+"); //$NON-NLS-1$ - List<Object> parts = new LinkedList<Object>(); Matcher m = pattern.matcher(nativeQuery); for (int i = 0; i < nativeQuery.length();) { if (!m.find(i)) { - parts.add(nativeQuery.substring(i)); + stringBuilder.append(nativeQuery.substring(i)); break; } if (m.start() != i) { - parts.add(nativeQuery.substring(i, m.start())); + stringBuilder.append(nativeQuery.substring(i, m.start())); } String match = m.group(); int end = match.lastIndexOf('$'); if ((end&0x1) == 1) { //escaped - parts.add(match.substring((end+1)/2)); + stringBuilder.append(match.substring((end+1)/2)); } else { if (end != 0) { - parts.add(match.substring(0, end/2)); + stringBuilder.append(match.substring(0, end/2)); } int index = Integer.parseInt(match.substring(end + 1))-1; if (index < 0 || index >= list.size()) { @@ -1009,10 +1011,9 @@ if (arg.getDirection() != Direction.IN) { throw new IllegalArgumentException(JDBCPlugin.Util.getString("SQLConversionVisitor.not_in_parameter", index+1)); //$NON-NLS-1$ } - parts.add(index); + substitutor.substitute(arg, stringBuilder, index); } i = m.end(); } - return parts; } } Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/SQLConversionVisitor.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/SQLConversionVisitor.java 2012-10-03 14:27:31 UTC (rev 4509) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/SQLConversionVisitor.java 2012-10-03 16:54:06 UTC (rev 4510) @@ -52,7 +52,7 @@ * This visitor takes an ICommand and does DBMS-specific conversion on it * to produce a SQL String. This class is expected to be subclassed. */ -public class SQLConversionVisitor extends SQLStringVisitor{ +public class SQLConversionVisitor extends SQLStringVisitor implements SQLStringVisitor.Substitutor { public static final String TEIID_NON_PREPARED = AbstractMetadataRecord.RELATIONAL_URI + "non-prepared"; //$NON-NLS-1$ private static DecimalFormat DECIMAL_FORMAT = @@ -178,23 +178,11 @@ if (p != null) { String nativeQuery = p.getProperty(TEIID_NATIVE_QUERY, false); if (nativeQuery != null) { - List<Object> parts = parseNativeQueryParts(nativeQuery, obj.getArguments()); this.prepared = !Boolean.valueOf(p.getProperty(TEIID_NON_PREPARED, false)); if (this.prepared) { this.preparedValues = new ArrayList<Object>(); } - for (Object o : parts) { - if (o instanceof String) { - buffer.append(o); - } else { - if (this.prepared) { - buffer.append('?'); - this.preparedValues = obj.getArguments(); - } else { - visit(obj.getArguments().get((Integer)o)); - } - } - } + parseNativeQueryParts(nativeQuery, obj.getArguments(), buffer, this); return; } } @@ -394,5 +382,15 @@ } super.appendBaseName(obj); } + + @Override + public void substitute(Argument arg, StringBuilder builder, int index) { + if (this.prepared) { + buffer.append('?'); + this.preparedValues.add(arg); + } else { + visit(arg); + } + } } Modified: trunk/connectors/translator-jpa/src/main/java/org/teiid/translator/jpa/JPA2ExecutionFactory.java =================================================================== --- trunk/connectors/translator-jpa/src/main/java/org/teiid/translator/jpa/JPA2ExecutionFactory.java 2012-10-03 14:27:31 UTC (rev 4509) +++ trunk/connectors/translator-jpa/src/main/java/org/teiid/translator/jpa/JPA2ExecutionFactory.java 2012-10-03 16:54:06 UTC (rev 4510) @@ -35,6 +35,7 @@ import org.teiid.language.Call; import org.teiid.language.Command; import org.teiid.language.QueryExpression; +import org.teiid.language.visitor.SQLStringVisitor; import org.teiid.metadata.MetadataFactory; import org.teiid.metadata.RuntimeMetadata; import org.teiid.translator.ExecutionContext; @@ -87,7 +88,11 @@ @Override public ProcedureExecution createProcedureExecution(Call command, ExecutionContext executionContext, RuntimeMetadata metadata, EntityManager connection) throws TranslatorException { - return super.createProcedureExecution(command, executionContext, metadata, connection); + String nativeQuery = command.getMetadataObject().getProperty(SQLStringVisitor.TEIID_NATIVE_QUERY, false); + if (nativeQuery != null) { + return new JPQLDirectQueryExecution(command.getArguments(), command, executionContext, metadata, connection, nativeQuery, false); + } + throw new TranslatorException("Missing native-query extension metadata."); //$NON-NLS-1$ } @Override @@ -97,10 +102,9 @@ @Override public ProcedureExecution createDirectExecution(List<Argument> arguments, Command command, ExecutionContext executionContext, RuntimeMetadata metadata, EntityManager connection) throws TranslatorException { - return new JPQLDirectQueryExecution(arguments, command, executionContext, metadata, connection); + return new JPQLDirectQueryExecution(arguments.subList(1, arguments.size()), command, executionContext, metadata, connection, (String)arguments.get(0).getArgumentValue().getValue(), true); } - @Override public void getMetadata(MetadataFactory mf, EntityManager em) throws TranslatorException { JPAMetadataProcessor metadataProcessor = new JPAMetadataProcessor(); Modified: trunk/connectors/translator-jpa/src/main/java/org/teiid/translator/jpa/JPQLDirectQueryExecution.java =================================================================== --- trunk/connectors/translator-jpa/src/main/java/org/teiid/translator/jpa/JPQLDirectQueryExecution.java 2012-10-03 14:27:31 UTC (rev 4509) +++ trunk/connectors/translator-jpa/src/main/java/org/teiid/translator/jpa/JPQLDirectQueryExecution.java 2012-10-03 16:54:06 UTC (rev 4510) @@ -21,8 +21,8 @@ */ package org.teiid.translator.jpa; -import java.util.ArrayList; import java.util.Arrays; +import java.util.Collections; import java.util.Iterator; import java.util.List; @@ -31,6 +31,8 @@ import org.teiid.language.Argument; import org.teiid.language.Command; +import org.teiid.language.Literal; +import org.teiid.language.visitor.SQLStringVisitor; import org.teiid.logging.LogConstants; import org.teiid.logging.LogManager; import org.teiid.metadata.RuntimeMetadata; @@ -42,41 +44,50 @@ public class JPQLDirectQueryExecution extends JPQLBaseExecution implements ProcedureExecution{ private Iterator<?> resultsIterator; private List<Argument> arguments; - private int updateCount = -1; - private boolean updateQuery; + private boolean returnsArray = true; + private String query; @SuppressWarnings("unused") - public JPQLDirectQueryExecution(List<Argument> arguments, Command command, ExecutionContext executionContext, RuntimeMetadata metadata, EntityManager em) { + public JPQLDirectQueryExecution(List<Argument> arguments, Command command, ExecutionContext executionContext, RuntimeMetadata metadata, EntityManager em, String query, boolean returnsArray) { super(executionContext, metadata, em); this.arguments = arguments; + this.returnsArray = returnsArray; + this.query = query; } @Override public void execute() throws TranslatorException { - String query = (String)arguments.get(0).getArgumentValue().getValue(); if (query.length() < 7) { throw new TranslatorException(JPAPlugin.Util.gs(JPAPlugin.Event.TEIID14008)); } - String firstToken = query.substring(0, 6); + String firstToken = query.substring(0, 7); String jpql = query.substring(7); LogManager.logTrace(LogConstants.CTX_CONNECTOR, "JPA Source-Query:", jpql); //$NON-NLS-1$ - if (firstToken.equalsIgnoreCase("search")) { // //$NON-NLS-1$ + if (firstToken.equalsIgnoreCase("search;")) { // //$NON-NLS-1$ + StringBuilder buffer = new StringBuilder(); + SQLStringVisitor.parseNativeQueryParts(jpql, arguments, buffer, new SQLStringVisitor.Substitutor() { + + @Override + public void substitute(Argument arg, StringBuilder builder, int index) { + Literal argumentValue = arg.getArgumentValue(); + builder.append(argumentValue); + } + }); + jpql = buffer.toString(); Query queryCommand = this.enityManager.createQuery(jpql); List<?> results = queryCommand.getResultList(); this.resultsIterator = results.iterator(); } - else if (firstToken.equalsIgnoreCase("create")) { // //$NON-NLS-1$ - Object entity = arguments.get(1).getArgumentValue().getValue(); + else if (firstToken.equalsIgnoreCase("create;")) { // //$NON-NLS-1$ + Object entity = arguments.get(0).getArgumentValue().getValue(); this.enityManager.merge(entity); - this.updateCount = 1; - this.updateQuery = true; + this.resultsIterator = Arrays.asList(1).iterator(); } - else if (firstToken.equalsIgnoreCase("update") || firstToken.equalsIgnoreCase("delete")) { // //$NON-NLS-1$ //$NON-NLS-2$ + else if (firstToken.equalsIgnoreCase("update;") || firstToken.equalsIgnoreCase("delete;")) { // //$NON-NLS-1$ //$NON-NLS-2$ Query queryCmd = this.enityManager.createQuery(jpql); - this.updateCount = queryCmd.executeUpdate(); - this.updateQuery = true; + this.resultsIterator = Arrays.asList(queryCmd.executeUpdate()).iterator(); } else { throw new TranslatorException(JPAPlugin.Util.gs(JPAPlugin.Event.TEIID14008)); } @@ -84,27 +95,20 @@ @Override public List<?> next() throws TranslatorException, DataNotAvailableException { - - // for insert/update/delete clauses - if (this.updateQuery) { - if (this.updateCount != -1) { - List<Object[]> row = new ArrayList<Object[]>(1); - row.add(new Object[] {this.updateCount}); - this.updateCount = -1; - return row; - } - return null; - } - if (this.resultsIterator != null && this.resultsIterator.hasNext()) { Object obj = this.resultsIterator.next(); if (obj instanceof Object[]) { - List<Object[]> row = new ArrayList<Object[]>(1); - row.add((Object[])obj); - return row; + if (returnsArray) { + return Arrays.asList(obj); + } + return Arrays.asList((Object[])obj); } - return Arrays.asList(new Object[] {obj}); + if (returnsArray) { + return Collections.singletonList(new Object[] {obj}); + } + return Arrays.asList(obj); } + this.resultsIterator = null; return null; } Modified: trunk/connectors/translator-olap/src/main/java/org/teiid/translator/olap/OlapQueryExecution.java =================================================================== --- trunk/connectors/translator-olap/src/main/java/org/teiid/translator/olap/OlapQueryExecution.java 2012-10-03 14:27:31 UTC (rev 4509) +++ trunk/connectors/translator-olap/src/main/java/org/teiid/translator/olap/OlapQueryExecution.java 2012-10-03 16:54:06 UTC (rev 4510) @@ -24,6 +24,7 @@ import java.sql.SQLException; import java.util.ArrayList; import java.util.Arrays; +import java.util.Date; import java.util.List; import java.util.ListIterator; @@ -37,6 +38,7 @@ import org.olap4j.metadata.Member; import org.teiid.language.Argument; import org.teiid.language.Command; +import org.teiid.language.Literal; import org.teiid.language.visitor.SQLStringVisitor; import org.teiid.logging.LogConstants; import org.teiid.logging.LogManager; @@ -65,15 +67,24 @@ this.mdxQuery = mdxQuery; if (arguments.size() > 0 || !returnsArray) { //TODO this is a hack at backwards compatibility StringBuilder buffer = new StringBuilder(); - List<Object> parts = SQLStringVisitor.parseNativeQueryParts(this.mdxQuery, arguments); - for (Object o : parts) { - if (o instanceof String) { - buffer.append(o); - } else { - Integer i = (Integer)o; - buffer.append(arguments.get(i).getArgumentValue().getValue()); + SQLStringVisitor.parseNativeQueryParts(mdxQuery, arguments, buffer, new SQLStringVisitor.Substitutor() { + + @Override + public void substitute(Argument arg, StringBuilder builder, int index) { + Literal argumentValue = arg.getArgumentValue(); + Object value = argumentValue.getValue(); + if (value == null || value instanceof Number || value instanceof Boolean || value instanceof String) { + builder.append(argumentValue); + } else if (value instanceof Date) { + //bind as a string literal + builder.append(new Literal(value.toString(), String.class)); + } else { + //bind as a string literal using the teiid format - this is likely not what the user wants + builder.append(new Literal(argumentValue.toString(), String.class)); + } } - } + }); + this.mdxQuery = buffer.toString(); } this.command = command; this.connection = connection; Added: trunk/connectors/translator-olap/src/test/java/org/teiid/translator/olap/TestOlapTranslator.java =================================================================== --- trunk/connectors/translator-olap/src/test/java/org/teiid/translator/olap/TestOlapTranslator.java (rev 0) +++ trunk/connectors/translator-olap/src/test/java/org/teiid/translator/olap/TestOlapTranslator.java 2012-10-03 16:54:06 UTC (rev 4510) @@ -0,0 +1,74 @@ +/* + * 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.olap; + +import static org.junit.Assert.*; + +import java.sql.Connection; + +import org.junit.Test; +import org.mockito.Mockito; +import org.olap4j.OlapConnection; +import org.olap4j.OlapStatement; +import org.olap4j.OlapWrapper; +import org.teiid.cdk.CommandBuilder; +import org.teiid.core.TeiidRuntimeException; +import org.teiid.dqp.internal.datamgr.RuntimeMetadataImpl; +import org.teiid.language.Call; +import org.teiid.language.Command; +import org.teiid.query.metadata.TransformationMetadata; +import org.teiid.query.unittest.RealMetadataFactory; +import org.teiid.translator.ExecutionContext; +import org.teiid.translator.ProcedureExecution; + +@SuppressWarnings("nls") +public class TestOlapTranslator { + + @Test public void testCannedProcedure() throws Exception { + String ddl = "create foreign procedure proc(arg integer, arg1 date) returns table (x string) options (\"teiid_rel:native-query\" '$2 $1 something')"; + String query = "exec proc(2, {d'1970-01-01'})"; + + TransformationMetadata tm = RealMetadataFactory.fromDDL(ddl, "x", "phy"); + + CommandBuilder commandBuilder = new CommandBuilder(tm); + Command obj = commandBuilder.getCommand(query); + + OlapExecutionFactory oef = new OlapExecutionFactory(); + Connection mock = Mockito.mock(java.sql.Connection.class); + OlapWrapper mock2 = Mockito.mock(OlapWrapper.class); + OlapConnection mock3 = Mockito.mock(OlapConnection.class); + OlapStatement mock4 = Mockito.mock(OlapStatement.class); + Mockito.stub(mock4.executeOlapQuery(Mockito.anyString())).toThrow(new TeiidRuntimeException()); + Mockito.stub(mock3.createStatement()).toReturn(mock4); + Mockito.stub(mock2.unwrap(OlapConnection.class)).toReturn(mock3); + Mockito.stub(mock.unwrap(OlapWrapper.class)).toReturn(mock2); + ProcedureExecution pe = oef.createProcedureExecution((Call)obj, Mockito.mock(ExecutionContext.class), new RuntimeMetadataImpl(tm), mock); + try { + pe.execute(); + fail(); + } catch (TeiidRuntimeException e) { + Mockito.verify(mock4).executeOlapQuery("'1970-01-01' 2 something"); + } + } + +} Property changes on: trunk/connectors/translator-olap/src/test/java/org/teiid/translator/olap/TestOlapTranslator.java ___________________________________________________________________ Added: svn:mime-type + text/plain Modified: trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/DirectQueryExecution.java =================================================================== --- trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/DirectQueryExecution.java 2012-10-03 14:27:31 UTC (rev 4509) +++ trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/DirectQueryExecution.java 2012-10-03 16:54:06 UTC (rev 4510) @@ -32,6 +32,7 @@ import org.teiid.language.Argument; import org.teiid.language.Command; +import org.teiid.language.Literal; import org.teiid.language.visitor.SQLStringVisitor; import org.teiid.metadata.RuntimeMetadata; import org.teiid.translator.DataNotAvailableException; @@ -89,15 +90,14 @@ public void execute() throws TranslatorException { if (query.startsWith(SEARCH)) { StringBuilder buffer = new StringBuilder(); - List<Object> parts = SQLStringVisitor.parseNativeQueryParts(query, arguments); - for (Object o : parts) { - if (o instanceof String) { - buffer.append(o); - } else { - Integer i = (Integer)o; - CriteriaVisitor.appendLiteralValue(buffer, arguments.get(i).getArgumentValue()); + SQLStringVisitor.parseNativeQueryParts(query, arguments, buffer, new SQLStringVisitor.Substitutor() { + + @Override + public void substitute(Argument arg, StringBuilder builder, int index) { + Literal argumentValue = arg.getArgumentValue(); + CriteriaVisitor.appendLiteralValue(builder, argumentValue); } - } + }); doSelect(buffer.toString().substring(7)); } else if (query.startsWith("create;")) { //$NON-NLS-1$ Modified: trunk/test-integration/common/src/test/java/org/teiid/connector/visitor/util/TestSQLStringVisitor.java =================================================================== --- trunk/test-integration/common/src/test/java/org/teiid/connector/visitor/util/TestSQLStringVisitor.java 2012-10-03 14:27:31 UTC (rev 4509) +++ trunk/test-integration/common/src/test/java/org/teiid/connector/visitor/util/TestSQLStringVisitor.java 2012-10-03 16:54:06 UTC (rev 4510) @@ -37,6 +37,7 @@ import org.teiid.language.Argument.Direction; import org.teiid.language.SQLConstants.NonReserved; import org.teiid.language.visitor.SQLStringVisitor; +import org.teiid.language.visitor.SQLStringVisitor.Substitutor; import org.teiid.metadata.RuntimeMetadata; import org.teiid.query.sql.lang.CompareCriteria; import org.teiid.query.sql.lang.JoinType; @@ -410,14 +411,34 @@ @Test public void testNativeParsing() throws Exception { String sql = "select $1 from $2"; - List<Object> parts = SQLStringVisitor.parseNativeQueryParts(sql, Arrays.asList(new Argument(Direction.IN, null, String.class, null), new Argument(Direction.IN, null, String.class, null))); - assertEquals(Arrays.asList((Object)"select ", 0, " from ", 1), parts); + String expected = "select *0 from *1"; + helpTestNativeParsing(sql, expected); } + + private void helpTestNativeParsing(String sql, String expected) { + StringBuilder sb = new StringBuilder(); + + Substitutor sub = new Substitutor() { + @Override + public void substitute(Argument arg, StringBuilder builder, int index) { + builder.append("*").append(index); + } + }; + + SQLStringVisitor.parseNativeQueryParts(sql, Arrays.asList(new Argument(Direction.IN, null, String.class, null), new Argument(Direction.IN, null, String.class, null)), sb, sub); + assertEquals(expected, sb.toString()); + } @Test public void testNativeParsing1() throws Exception { String sql = "select $$1 from $$$2"; - List<Object> parts = SQLStringVisitor.parseNativeQueryParts(sql, Arrays.asList(new Argument(Direction.IN, null, String.class, null), new Argument(Direction.IN, null, String.class, null))); - assertEquals(Arrays.asList((Object)"select ", "$1", " from ", "$", 1), parts); + String expected = "select $1 from $*1"; + helpTestNativeParsing(sql, expected); } + + @Test(expected=IllegalArgumentException.class) public void testNativeParsing2() throws Exception { + String sql = "select $$1 from $$$3"; + String expected = "select $1 from $*1"; + helpTestNativeParsing(sql, expected); + } }