Author: shawkins Date: 2011-10-31 15:57:54 -0400 (Mon, 31 Oct 2011) New Revision: 3595 Modified: trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java trunk/build/kits/jboss-container/teiid-releasenotes.html trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/SQLConversionVisitor.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java trunk/documentation/reference/src/main/docbook/en-US/content/translators.xml Log: TEIID-669 adding support for JDBC native queries in metadata Modified: trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java =================================================================== --- trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java 2011-10-28 20:43:18 UTC (rev 3594) +++ trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java 2011-10-31 19:57:54 UTC (rev 3595) @@ -81,6 +81,7 @@ import org.teiid.language.SQLConstants.Tokens; import org.teiid.language.SortSpecification.Ordering; import org.teiid.metadata.AbstractMetadataRecord; +import org.teiid.metadata.Table; /** @@ -434,13 +435,7 @@ } public void visit(NamedTable obj) { - AbstractMetadataRecord groupID = obj.getMetadataObject(); - if(groupID != null) { - buffer.append(getName(groupID)); - } else { - buffer.append(obj.getName()); - } - + appendBaseName(obj); if (obj.getCorrelationName() != null) { buffer.append(Tokens.SPACE); if (useAsInGroupAlias()){ @@ -450,6 +445,15 @@ buffer.append(obj.getCorrelationName()); } } + + protected void appendBaseName(NamedTable obj) { + Table groupID = obj.getMetadataObject(); + if(groupID != null) { + buffer.append(getName(groupID)); + } else { + buffer.append(obj.getName()); + } + } /** * Indicates whether group alias should be of the form Modified: trunk/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-10-28 20:43:18 UTC (rev 3594) +++ trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-10-31 19:57:54 UTC (rev 3595) @@ -34,6 +34,7 @@ The memory buffer may be optional be configured as off-heap for better large memory performance - see the Admin Guide for more. Overhead is now tracked to help prevent memory errors when dealing with 10s of millions or more of batches/pages. Serialization and disk utilization were also improved. <LI><B>GSSAPI</B> - both the Teiid JDBC client/server and the ODBC pg backend can now support GSSAPI for single sign-on. <LI><B>Server-side Query Timeouts</B> - default query timeouts can be configured at both the VDB (via the query-timeout VDB property) and entire server (via the teiid-jboss-beans.xml queryTimeout property). + <LI><B>Native Queries</B> - added the ability to specify native query SQL for JDBC physical tables and stored procedures via extension metadata. </UL> <h2><a name="Compatibility">Compatibility Issues</a></h2> 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 2011-10-28 20:43:18 UTC (rev 3594) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/SQLConversionVisitor.java 2011-10-31 19:57:54 UTC (rev 3595) @@ -24,15 +24,20 @@ */ package org.teiid.translator.jdbc; +import static org.teiid.language.SQLConstants.Reserved.*; + import java.sql.Time; import java.sql.Timestamp; import java.text.DecimalFormat; import java.util.ArrayList; import java.util.Collections; import java.util.IdentityHashMap; +import java.util.LinkedList; import java.util.List; import java.util.Map; import java.util.Set; +import java.util.regex.Matcher; +import java.util.regex.Pattern; import org.teiid.language.Argument; import org.teiid.language.Call; @@ -45,6 +50,7 @@ import org.teiid.language.LanguageObject; import org.teiid.language.Like; import org.teiid.language.Literal; +import org.teiid.language.NamedTable; import org.teiid.language.SearchedCase; import org.teiid.language.SetClause; import org.teiid.language.Argument.Direction; @@ -52,6 +58,7 @@ import org.teiid.language.SQLConstants.Tokens; import org.teiid.language.SetQuery.Operation; import org.teiid.language.visitor.SQLStringVisitor; +import org.teiid.metadata.Procedure; import org.teiid.translator.ExecutionContext; import org.teiid.translator.TypeFacility; @@ -61,6 +68,9 @@ * to produce a SQL String. This class is expected to be subclassed. */ public class SQLConversionVisitor extends SQLStringVisitor{ + + public static final String TEIID_NATIVE_QUERY = "teiid:native-query"; //$NON-NLS-1$ + public static final String TEIID_NON_PREPARED = "teiid:non-prepared"; //$NON-NLS-1$ private static DecimalFormat DECIMAL_FORMAT = new DecimalFormat("#############################0.0#############################"); //$NON-NLS-1$ @@ -181,14 +191,71 @@ * @see org.teiid.language.visitor.SQLStringVisitor#visit(org.teiid.language.Call) */ public void visit(Call obj) { - this.prepared = true; - /* + Procedure p = obj.getMetadataObject(); + if (p != null) { + String nativeQuery = p.getProperties().get(TEIID_NATIVE_QUERY); + if (nativeQuery != null) { + List<Object> parts = parseNativeQueryParts(nativeQuery); + this.prepared = !Boolean.valueOf(p.getProperties().get(TEIID_NON_PREPARED)); + if (this.prepared) { + this.preparedValues = new ArrayList<Object>(); + } + for (Object o : parts) { + if (o instanceof String) { + buffer.append(o); + } else { + Integer i = (Integer)o; + if (obj.getArguments().size() <= i) { + throw new IllegalArgumentException("Invalid parameter {0}. Must be less than or equal to {1}."); + } + if (obj.getArguments().get(i).getDirection() != Direction.IN) { + throw new IllegalArgumentException("Native query procedures cannot use non IN parameters."); + } + visit(obj.getArguments().get(i)); + if (this.prepared) { + this.preparedValues.add(obj.getArguments()); + } + } + } + return; + } + } + this.prepared = true; + /* * preparedValues is now a list of procedure params instead of just values */ this.preparedValues = obj.getArguments(); buffer.append(generateSqlForStoredProcedure(obj)); } + private List<Object> parseNativeQueryParts(String nativeQuery) { + 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(); i++) { + if (!m.find(i)) { + parts.add(nativeQuery.substring(i).replaceAll("\\$\\$", "\\$")); //$NON-NLS-1$ //$NON-NLS-2$ + break; + } + if (m.start() != i) { + parts.add(nativeQuery.substring(i, m.start()).replaceAll("\\$\\$", "\\$")); //$NON-NLS-1$ //$NON-NLS-2$ + } + String match = m.group(); + int end = match.lastIndexOf('$'); + if ((end&0x1) == 1) { + //escaped + parts.add(match.replaceAll("\\$\\$", "\\$")); //$NON-NLS-1$ //$NON-NLS-2$ + } else { + if (end != 0) { + parts.add(match.substring(0, end/2)); + } + parts.add(Integer.parseInt(match.substring(end + 1))-1); + } + i = m.end(); + } + return parts; + } + /** * @see org.teiid.language.visitor.SQLStringVisitor#visit(org.teiid.language.Literal) */ @@ -355,4 +422,22 @@ return executionFactory.getLikeRegexString(); } + @Override + protected void appendBaseName(NamedTable obj) { + if (obj.getMetadataObject() != null) { + String nativeQuery = obj.getMetadataObject().getProperties().get(TEIID_NATIVE_QUERY); + if (nativeQuery != null) { + buffer.append(Tokens.LPAREN).append(nativeQuery).append(Tokens.RPAREN); + if (obj.getCorrelationName() == null) { + buffer.append(Tokens.SPACE); + if (useAsInGroupAlias()){ + buffer.append(AS).append(Tokens.SPACE); + } + } + } + } + super.appendBaseName(obj); + } + + } Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2011-10-28 20:43:18 UTC (rev 3594) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2011-10-31 19:57:54 UTC (rev 3595) @@ -41,20 +41,25 @@ import org.teiid.dqp.internal.datamgr.FakeExecutionContextImpl; import org.teiid.language.Command; import org.teiid.metadata.Column; +import org.teiid.metadata.ColumnSet; import org.teiid.metadata.MetadataStore; +import org.teiid.metadata.Procedure; +import org.teiid.metadata.ProcedureParameter; import org.teiid.metadata.Schema; import org.teiid.metadata.Table; import org.teiid.query.metadata.CompositeMetadataStore; import org.teiid.query.metadata.QueryMetadataInterface; import org.teiid.query.metadata.TransformationMetadata; +import org.teiid.query.sql.lang.SPParameter; import org.teiid.query.unittest.RealMetadataFactory; import org.teiid.translator.ExecutionContext; import org.teiid.translator.TranslatorException; import org.teiid.translator.jdbc.JDBCProcedureExecution; +import org.teiid.translator.jdbc.SQLConversionVisitor; import org.teiid.translator.jdbc.TranslatedCommand; import org.teiid.translator.jdbc.TranslationHelper; - +@SuppressWarnings("nls") public class TestOracleTranslator { private OracleExecutionFactory TRANSLATOR; @@ -716,6 +721,8 @@ MetadataStore metadataStore = new MetadataStore(); Schema foo = RealMetadataFactory.createPhysicalModel("BQT1", metadataStore); //$NON-NLS-1$ Table table = RealMetadataFactory.createPhysicalGroup("SmallA", foo); //$NON-NLS-1$ + Table x = RealMetadataFactory.createPhysicalGroup("x", foo); //$NON-NLS-1$ + x.setProperty(SQLConversionVisitor.TEIID_NATIVE_QUERY, "select c from d"); Table dual = RealMetadataFactory.createPhysicalGroup("DUAL", foo); //$NON-NLS-1$ table.setNameInSource("SmallishA");//$NON-NLS-1$ String[] elemNames = new String[] { @@ -728,12 +735,19 @@ DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.TIMESTAMP, }; + RealMetadataFactory.createElements(x, elemNames, elemTypes); List<Column> cols = RealMetadataFactory.createElements(table, elemNames, elemTypes); cols.get(1).setAutoIncremented(true); cols.get(1).setNameInSource("ID:SEQUENCE=MYSEQUENCE.nextVal"); //$NON-NLS-1$ cols.get(2).setNativeType("date"); //$NON-NLS-1$ RealMetadataFactory.createElements(dual, new String[] {"something"}, new String[] {DataTypeManager.DefaultDataTypes.STRING}); //$NON-NLS-1$ + ProcedureParameter in1 = RealMetadataFactory.createParameter("in1", SPParameter.IN, DataTypeManager.DefaultDataTypes.INTEGER); //$NON-NLS-1$ + ColumnSet<Procedure> rs3 = RealMetadataFactory.createResultSet("proc.rs1", new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.INTEGER }); //$NON-NLS-1$ //$NON-NLS-2$ + Procedure p = RealMetadataFactory.createStoredProcedure("proc", foo, Arrays.asList(in1)); + p.setResultSet(rs3); + p.setProperty(SQLConversionVisitor.TEIID_NATIVE_QUERY, "select x from y where z = $1"); + CompositeMetadataStore store = new CompositeMetadataStore(metadataStore); return new TransformationMetadata(null, store, null, RealMetadataFactory.SFM.getSystemFunctions(), null); } @@ -822,5 +836,23 @@ Mockito.verify(cs, Mockito.times(1)).registerOutParameter(1, OracleExecutionFactory.CURSOR_TYPE); Mockito.verify(cs, Mockito.times(1)).getObject(1); } + + @Test public void testNativeQuery() throws Exception { + String input = "SELECT (DoubleNum * 1.0) FROM x"; //$NON-NLS-1$ + String output = "SELECT (x.DoubleNum * 1.0) FROM (select c from d) x"; //$NON-NLS-1$ + QueryMetadataInterface metadata = getOracleSpecificMetadata(); + + helpTestVisitor(metadata, input, EMPTY_CONTEXT, null, output); + } + + @Test public void testNativeQueryProc() throws Exception { + String input = "call proc(2)"; //$NON-NLS-1$ + String output = "select x from y where z = 2"; //$NON-NLS-1$ + + QueryMetadataInterface metadata = getOracleSpecificMetadata(); + + helpTestVisitor(metadata, input, EMPTY_CONTEXT, null, output); + } + } Modified: trunk/documentation/reference/src/main/docbook/en-US/content/translators.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/translators.xml 2011-10-28 20:43:18 UTC (rev 3594) +++ trunk/documentation/reference/src/main/docbook/en-US/content/translators.xml 2011-10-31 19:57:54 UTC (rev 3595) @@ -520,6 +520,22 @@ </section> <section> + <title>Native Queries</title> + <para>Both physical tables and procedures may optionally have native queries associated with them. No validation of the native query is performed, it is simply used in a straight-forward manner to generate the source SQL. + For a physical table setting the teiid:native-query extension metadata to the desired query string will have Teiid execute the native query as an inline view in the source query. + This feature should only be used against sources that support inline views. For example on a physical table y with nameInSource=x and teiid:native-query=select c from g, the Teiid source query + "SELECT c FROM y" would generate the SQL query "SELECT c FROM (select c from g) as x". Note that the column names in the native query must match the nameInSource of the physical table columns for the resulting SQL + to be valid.</para> + <para>For physical procedures you may also set the teiid:native-query extension metadata to a desired query string with the added ability to positionally reference IN parameters. A parameter reference has the form + $integer, e.g. $1. Note that 1 based indexing is used and that only IN parameters may be referenced. Dollar-sign ($) is reserved in physical procedure native queries. To use a $ directly, it must be escaped with another $, e.g. $$. + By default bind values will be used for parameter values. In some situations you may wish to bind values directly into the resulting SQL. The teiid:non-prepared extension metadata property may be set to false to turn off + parameter binding. Note this option should be used with caution as inbound may allow for SQL injection attacks if not properly validated. The native query does not need to call a stored procedure. Any SQL that returns + a result set positionally matching the result set expected by the physical stored procedure metadata will work. For example on a stored procedure x with teiid:native-query=select c from g where c1 = $1 and c2 = '$$', the Teiid source query + "CALL x(?)" would generate the SQL query "select c from g where c1 = ? and c2 = '$'". Note that ? in this example will be replaced with the actual value bound to parameter 1. + </para> + </section> + + <section> <title>JCA Resource Adapter</title> <para>The resource adapter for this translator provided through data source in JBoss AS, Refer to Admin Guide for "JDBC Data Sources" configuration section.</para>