Author: shawkins Date: 2009-08-06 12:34:42 -0400 (Thu, 06 Aug 2009) New Revision: 1216 Added: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/TestJDBCUpdateExecution.java 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/JDBCConnector.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/db2/DB2SQLTranslator.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/derby/DerbyCapabilities.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/derby/DerbySQLTranslator.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/mysql/MySQLTranslator.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/PostgreSQLCapabilities.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/translator/SQLConversionVisitor.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/Translator.java trunk/connectors/connector-jdbc/src/main/resources/connector-jdbc.xml trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/derby/TestDerbyCapabilities.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/mysql/TestMySQLTranslator.java trunk/test-integration/src/test/java/com/metamatrix/connector/jdbc/oracle/TestOracleSQLConversionVisitor.java Log: TEIID-715 TEIID-752 TEIID-753 adding some consistency to null ordering, addressing issues with mysql translator and with bulk updates 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-08-06 14:43:44 UTC (rev 1215) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCBaseExecution.java 2009-08-06 16:34:42 UTC (rev 1216) @@ -97,7 +97,7 @@ * Return true if this is a batched update */ protected void bindPreparedStatementValues(PreparedStatement stmt, TranslatedCommand tc, int rowCount) throws SQLException { - List params = tc.getPreparedValues(); + List<?> params = tc.getPreparedValues(); for (int row = 0; row < rowCount; row++) { for (int i = 0; i< params.size(); i++) { @@ -106,12 +106,12 @@ if (paramValue.isMultiValued()) { value = ((List<?>)value).get(row); } - Class paramType = paramValue.getType(); + Class<?> paramType = paramValue.getType(); sqlTranslator.bindValue(stmt, value, paramType, i+1); - if (rowCount > 1) { - stmt.addBatch(); - } - } + } + if (rowCount > 1) { + stmt.addBatch(); + } } } 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-08-06 14:43:44 UTC (rev 1215) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCConnector.java 2009-08-06 16:34:42 UTC (rev 1216) @@ -122,9 +122,11 @@ } catch (MetaMatrixCoreException e) { throw new ConnectorException(e); } + PropertiesUtils.setBeanProperties(sqlTranslator, environment.getProperties(), null); sqlTranslator.initialize(environment); capabilities = sqlTranslator.getConnectorCapabilities(); + PropertiesUtils.setBeanProperties(capabilities, environment.getProperties(), null); createDataSources(dataSourceClassName, connectionProps); 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-08-06 14:43:44 UTC (rev 1215) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCUpdateExecution.java 2009-08-06 16:34:42 UTC (rev 1216) @@ -89,10 +89,10 @@ boolean succeeded = false; boolean commitType = getAutoCommit(null); - ICommand[] commands = (ICommand[])batchedCommand.getUpdateCommands().toArray(new ICommand[batchedCommand.getUpdateCommands().size()]); + ICommand[] commands = batchedCommand.getUpdateCommands().toArray(new ICommand[batchedCommand.getUpdateCommands().size()]); int[] results = new int[commands.length]; - TranslatedCommand command = null; + TranslatedCommand tCommand = null; try { // temporarily turn the auto commit off, and set it back to what it was @@ -106,18 +106,18 @@ TranslatedCommand previousCommand = null; for (int i = 0; i < commands.length; i++) { - command = translateCommand(commands[i]); - if (command.isPrepared()) { + tCommand = translateCommand(commands[i]); + if (tCommand.isPrepared()) { PreparedStatement pstmt = null; - if (previousCommand != null && previousCommand.isPrepared() && previousCommand.getSql().equals(command.getSql())) { + if (previousCommand != null && previousCommand.isPrepared() && previousCommand.getSql().equals(tCommand.getSql())) { pstmt = (PreparedStatement)statement; } else { if (!executedCmds.isEmpty()) { executeBatch(i, results, executedCmds); } - pstmt = getPreparedStatement(command.getSql()); + pstmt = getPreparedStatement(tCommand.getSql()); } - bindPreparedStatementValues(pstmt, command, 1); + bindPreparedStatementValues(pstmt, tCommand, 1); pstmt.addBatch(); } else { if (previousCommand != null && previousCommand.isPrepared()) { @@ -127,17 +127,17 @@ if (statement == null) { getStatement(); } - statement.addBatch(command.getSql()); + statement.addBatch(tCommand.getSql()); } - executedCmds.add(command); - previousCommand = command; + executedCmds.add(tCommand); + previousCommand = tCommand; } if (!executedCmds.isEmpty()) { executeBatch(commands.length, results, executedCmds); } succeeded = true; } catch (SQLException e) { - throw new JDBCExecutionException(e, command); + throw new JDBCExecutionException(e, tCommand); } finally { if (commitType) { restoreAutoCommit(!succeeded, null); @@ -182,7 +182,7 @@ for (int i = 0; i< translatedComm.getPreparedValues().size(); i++) { ILiteral paramValue = (ILiteral)translatedComm.getPreparedValues().get(i); if (paramValue.isMultiValued()) { - rowCount = ((List<?>)paramValue).size(); + rowCount = ((List<?>)paramValue.getValue()).size(); break; } } @@ -220,11 +220,14 @@ * @return * @throws ConnectorException */ - private boolean getAutoCommit(TranslatedCommand command) throws ConnectorException { + private boolean getAutoCommit(TranslatedCommand tCommand) throws ConnectorException { + if (this.context.isTransactional()) { + return false; + } try { return connection.getAutoCommit(); } catch (SQLException err) { - throw new JDBCExecutionException(err, command); + throw new JDBCExecutionException(err, tCommand); } } @@ -236,18 +239,18 @@ * @throws ConnectorException */ private void restoreAutoCommit(boolean exceptionOccurred, - TranslatedCommand command) throws ConnectorException { + TranslatedCommand tCommand) throws ConnectorException { try { if (exceptionOccurred) { connection.rollback(); } } catch (SQLException err) { - throw new JDBCExecutionException(err, command); + throw new JDBCExecutionException(err, tCommand); } finally { try { connection.setAutoCommit(true); } catch (SQLException err) { - throw new JDBCExecutionException(err, command); + throw new JDBCExecutionException(err, tCommand); } } } 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-08-06 14:43:44 UTC (rev 1215) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/db2/DB2SQLTranslator.java 2009-08-06 16:34:42 UTC (rev 1216) @@ -176,5 +176,8 @@ return expr; } - + @Override + public NullOrder getDefaultNullOrder() { + return NullOrder.HIGH; + } } Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/derby/DerbyCapabilities.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/derby/DerbyCapabilities.java 2009-08-06 14:43:44 UTC (rev 1215) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/derby/DerbyCapabilities.java 2009-08-06 16:34:42 UTC (rev 1216) @@ -135,7 +135,7 @@ return supportedFunctions; } - public void setVersion(String version) { + public void setDatabaseVersion(String version) { this.version = version; } Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/derby/DerbySQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/derby/DerbySQLTranslator.java 2009-08-06 14:43:44 UTC (rev 1215) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/derby/DerbySQLTranslator.java 2009-08-06 16:34:42 UTC (rev 1216) @@ -37,8 +37,8 @@ */ public class DerbySQLTranslator extends DB2SQLTranslator { - public static final String DATABASE_VERSION = "DatabaseVersion"; //$NON-NLS-1$ - + private String version = DerbyCapabilities.TEN_1; + @Override public void initialize(ConnectorEnvironment env) throws ConnectorException { super.initialize(env); @@ -68,13 +68,12 @@ } @Override - public ConnectorCapabilities getConnectorCapabilities() - throws ConnectorException { - ConnectorCapabilities capabilities = super.getConnectorCapabilities(); - if (capabilities instanceof DerbyCapabilities) { - ((DerbyCapabilities)capabilities).setVersion(getEnvironment().getProperties().getProperty(DATABASE_VERSION, DerbyCapabilities.TEN_1)); - } - return capabilities; + public boolean supportsExplicitNullOrdering() { + return version.compareTo(DerbyCapabilities.TEN_4) >= 0; + } + + public void setDatabaseVersion(String version) { + this.version = version; } } Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/mysql/MySQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/mysql/MySQLTranslator.java 2009-08-06 14:43:44 UTC (rev 1215) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/mysql/MySQLTranslator.java 2009-08-06 16:34:42 UTC (rev 1216) @@ -64,7 +64,7 @@ @Override public String translateLiteralTimestamp(Timestamp timestampValue) { - return "TIMESTAMP('" + formatDateValue(timestampValue) + "')"; //$NON-NLS-1$//$NON-NLS-2$ + return "{ts '" + formatDateValue(timestampValue) + "'}"; //$NON-NLS-1$//$NON-NLS-2$ } @Override @@ -74,7 +74,7 @@ @Override public int getTimestampNanoPrecision() { - return 6; + return 0; } @Override 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-08-06 14:43:44 UTC (rev 1215) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java 2009-08-06 16:34:42 UTC (rev 1216) @@ -323,6 +323,11 @@ } @Override + public NullOrder getDefaultNullOrder() { + return NullOrder.HIGH; + } + + @Override public Class<? extends ConnectorCapabilities> getDefaultCapabilities() { return OracleCapabilities.class; } Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/PostgreSQLCapabilities.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/PostgreSQLCapabilities.java 2009-08-06 14:43:44 UTC (rev 1215) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/PostgreSQLCapabilities.java 2009-08-06 16:34:42 UTC (rev 1216) @@ -33,8 +33,15 @@ * @since 4.3 */ public class PostgreSQLCapabilities extends JDBCCapabilities { - - + + public static final String EIGHT_0 = "8.0"; //$NON-NLS-1$ + public static final String EIGHT_1 = "8.1"; //$NON-NLS-1$ + public static final String EIGHT_2 = "8.2"; //$NON-NLS-1$ + public static final String EIGHT_3 = "8.3"; //$NON-NLS-1$ + public static final String EIGHT_4 = "8.4"; //$NON-NLS-1$ + + private String version = EIGHT_0; + public List<String> getSupportedFunctions() { List<String> supportedFunctions = new ArrayList<String>(); supportedFunctions.addAll(super.getSupportedFunctions()); @@ -117,9 +124,11 @@ // supportedFunctions.add("PARSETIME"); //$NON-NLS-1$ // supportedFunctions.add("PARSETIMESTAMP"); //$NON-NLS-1$ supportedFunctions.add("QUARTER"); //$NON-NLS-1$ - supportedFunctions.add("SECOND"); //$NON-NLS-1$ -// supportedFunctions.add("TIMESTAMPADD"); //$NON-NLS-1$ -// supportedFunctions.add("TIMESTAMPDIFF"); //$NON-NLS-1$ + supportedFunctions.add("SECOND"); //$NON-NLS-1$ + if (this.version.compareTo(EIGHT_2) >= 0) { + supportedFunctions.add("TIMESTAMPADD"); //$NON-NLS-1$ + supportedFunctions.add("TIMESTAMPDIFF"); //$NON-NLS-1$ + } supportedFunctions.add("WEEK"); //$NON-NLS-1$ supportedFunctions.add("YEAR"); //$NON-NLS-1$ @@ -268,5 +277,9 @@ @Override public boolean supportsIntersect() { return true; - } + } + + public void setDatabaseVersion(String version) { + this.version = version; + } } 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-08-06 14:43:44 UTC (rev 1215) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/PostgreSQLTranslator.java 2009-08-06 16:34:42 UTC (rev 1216) @@ -52,7 +52,9 @@ * Translator class for PostgreSQL. Updated to expect a 8.0+ jdbc client * @since 4.3 */ -public class PostgreSQLTranslator extends Translator { +public class PostgreSQLTranslator extends Translator { + + private String version = PostgreSQLCapabilities.EIGHT_0; public void initialize(ConnectorEnvironment env) throws ConnectorException { //TODO: all of the functions (except for convert) can be handled through just the escape syntax @@ -158,6 +160,16 @@ } @Override + public NullOrder getDefaultNullOrder() { + return NullOrder.HIGH; + } + + @Override + public boolean supportsExplicitNullOrdering() { + return version.compareTo(PostgreSQLCapabilities.EIGHT_4) >= 0; + } + + @Override public Class<? extends ConnectorCapabilities> getDefaultCapabilities() { return PostgreSQLCapabilities.class; } 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-08-06 14:43:44 UTC (rev 1215) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/SQLConversionVisitor.java 2009-08-06 16:34:42 UTC (rev 1216) @@ -37,11 +37,13 @@ import org.teiid.connector.api.ExecutionContext; import org.teiid.connector.api.TypeFacility; +import org.teiid.connector.jdbc.translator.Translator.NullOrder; import org.teiid.connector.language.ICommand; import org.teiid.connector.language.IFunction; import org.teiid.connector.language.ILanguageObject; import org.teiid.connector.language.ILimit; import org.teiid.connector.language.ILiteral; +import org.teiid.connector.language.IOrderByItem; import org.teiid.connector.language.IParameter; import org.teiid.connector.language.IProcedure; import org.teiid.connector.language.IParameter.Direction; @@ -119,6 +121,22 @@ } } } + + @Override + public void visit(IOrderByItem obj) { + super.visit(obj); + if (!this.translator.supportsExplicitNullOrdering()) { + return; + } + NullOrder nullOrder = this.translator.getDefaultNullOrder(); + if (obj.getDirection() == IOrderByItem.ASC) { + if (nullOrder != NullOrder.LOW && nullOrder != NullOrder.FIRST) { + buffer.append(" NULLS FIRST"); //$NON-NLS-1$ + } + } else if (nullOrder != NullOrder.HIGH && nullOrder != NullOrder.LAST) { + buffer.append(" NULLS LAST"); //$NON-NLS-1$ + } + } /** * @param type 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-08-06 14:43:44 UTC (rev 1215) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/Translator.java 2009-08-06 16:34:42 UTC (rev 1216) @@ -56,6 +56,7 @@ import org.teiid.connector.language.IParameter; import org.teiid.connector.language.ISetQuery; import org.teiid.connector.language.IParameter.Direction; +import org.teiid.connector.visitor.util.SQLReservedWords; import com.metamatrix.common.util.PropertiesUtils; import com.metamatrix.core.util.ReflectionHelper; @@ -65,7 +66,14 @@ * Specific databases should override as necessary. */ public class Translator { - + + public enum NullOrder { + HIGH, + LOW, + FIRST, + LAST + } + // Because the retrieveValue() method will be hit for every value of // every JDBC result set returned, we do lots of weird special stuff here // to improve the performance (most importantly to remove big if/else checks @@ -296,6 +304,8 @@ if (getTimestampNanoPrecision() > 0) { int mask = 10^(9-getTimestampNanoPrecision()); newTs.setNanos(ts.getNanos()/mask*mask); + } else { + newTs.setNanos(0); } dateObject = newTs; } @@ -784,4 +794,16 @@ public boolean useParensForJoins() { return false; } + + /** + * get the default null ordering + * @return + */ + public NullOrder getDefaultNullOrder() { + return NullOrder.LOW; + } + + public boolean supportsExplicitNullOrdering() { + return true; + } } Modified: trunk/connectors/connector-jdbc/src/main/resources/connector-jdbc.xml =================================================================== --- trunk/connectors/connector-jdbc/src/main/resources/connector-jdbc.xml 2009-08-06 14:43:44 UTC (rev 1215) +++ trunk/connectors/connector-jdbc/src/main/resources/connector-jdbc.xml 2009-08-06 16:34:42 UTC (rev 1216) @@ -86,6 +86,7 @@ <PropertyDefinition Name="ConnectionSource" DisplayName="Connection Source Class" ShortDescription="Driver, DataSource, or XADataSource class name" DefaultValue="org.postgresql.Driver" IsRequired="true" /> <PropertyDefinition Name="URL" DisplayName="JDBC URL" ShortDescription="" DefaultValue="jdbc:postgresql://&lt;host&gt;:5432/&lt;databaseName&gt;" IsRequired="true" PropertyType="String" IsMasked="false" /> <PropertyDefinition Name="ExtensionTranslationClass" DisplayName="Extension SQL Translation Class" ShortDescription="" DefaultValue="org.teiid.connector.jdbc.postgresql.PostgreSQLTranslator" PropertyType="String" IsExpert="true" IsMasked="false" /> + <PropertyDefinition Name="DatabaseVersion" DisplayName="Database Version" ShortDescription="PostgreSQL Version i.e. 8.3" DefaultValue="8.0" PropertyType="String" /> </ComponentType> <ComponentType Name="PostgreSQL XA JDBC Connector" ComponentTypeCode="2" Deployable="true" Deprecated="false" Monitorable="false" SuperComponentType="JDBC Connector" ParentComponentType="Connectors" LastChangedBy="ConfigurationStartup" CreatedBy="ConfigurationStartup"> <PropertyDefinition Name="ConnectionSource" DisplayName="Connection Source Class" ShortDescription="Driver, DataSource, or XADataSource class name" DefaultValue="org.postgresql.xa.PGXADataSource" IsRequired="true" /> @@ -94,6 +95,7 @@ <PropertyDefinition Name="DatabaseName" DisplayName="Database Name" ShortDescription="" IsRequired="true" PropertyType="String" IsMasked="false" /> <PropertyDefinition Name="ExtensionTranslationClass" DisplayName="Extension SQL Translation Class" ShortDescription="" DefaultValue="org.teiid.connector.jdbc.postgresql.PostgreSQLTranslator" PropertyType="String" IsExpert="true" IsMasked="false" /> <PropertyDefinition Name="IsXA" DisplayName="Is XA" ShortDescription="Is XA" DefaultValue="true" IsRequired="true" PropertyType="Boolean" /> + <PropertyDefinition Name="DatabaseVersion" DisplayName="Database Version" ShortDescription="PostgreSQL Version i.e. 8.3" DefaultValue="8.0" PropertyType="String" /> </ComponentType> <ComponentType Name="Apache Derby Embedded Connector" ComponentTypeCode="2" Deployable="true" Deprecated="false" Monitorable="false" SuperComponentType="JDBC Connector" ParentComponentType="Connectors" LastChangedBy="ConfigurationStartup" CreatedBy="ConfigurationStartup"> <PropertyDefinition Name="ConnectionSource" DisplayName="Connection Source Class" ShortDescription="Driver, DataSource, or XADataSource class name" DefaultValue="org.apache.derby.jdbc.EmbeddedDriver" IsRequired="true" /> Added: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/TestJDBCUpdateExecution.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/TestJDBCUpdateExecution.java (rev 0) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/TestJDBCUpdateExecution.java 2009-08-06 16:34:42 UTC (rev 1216) @@ -0,0 +1,65 @@ +/* + * 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.Connection; +import java.sql.PreparedStatement; +import java.util.Arrays; +import java.util.Properties; + +import org.junit.Test; +import org.mockito.Mockito; +import org.teiid.connector.api.ConnectorLogger; +import org.teiid.connector.api.ExecutionContext; +import org.teiid.connector.jdbc.translator.Translator; +import org.teiid.connector.language.ICommand; +import org.teiid.connector.language.IInsert; +import org.teiid.connector.language.IInsertExpressionValueSource; +import org.teiid.connector.language.ILiteral; + +import com.metamatrix.cdk.api.EnvironmentUtility; + +public class TestJDBCUpdateExecution { + + @Test public void testBulkUpdate() throws Exception { + ICommand command = MetadataFactory.helpTranslate(MetadataFactory.BQT_VDB, "insert into bqt1.smalla (intkey, intnum) values (1, 2)"); //$NON-NLS-1$ + ILiteral value = ((ILiteral)((IInsertExpressionValueSource)((IInsert)command).getValueSource()).getValues().get(0)); + ILiteral value1 = ((ILiteral)((IInsertExpressionValueSource)((IInsert)command).getValueSource()).getValues().get(1)); + value.setMultiValued(true); + value.setBindValue(true); + value.setValue(Arrays.asList(1, 2)); + value1.setMultiValued(true); + value1.setBindValue(true); + value1.setValue(Arrays.asList(2, 3)); + Connection connection = Mockito.mock(Connection.class); + PreparedStatement p = Mockito.mock(PreparedStatement.class); + Mockito.stub(p.executeBatch()).toReturn(new int [] {1, 1}); + Mockito.stub(connection.prepareStatement("INSERT INTO SmallA (IntKey, IntNum) VALUES (?, ?)")).toReturn(p); //$NON-NLS-1$ + Translator sqlTranslator = new Translator(); + ExecutionContext context = EnvironmentUtility.createSecurityContext("user"); //$NON-NLS-1$ + JDBCUpdateExecution updateExecution = new JDBCUpdateExecution(command, connection, sqlTranslator, Mockito.mock(ConnectorLogger.class), new Properties(), context); + updateExecution.execute(); + Mockito.verify(p, Mockito.times(2)).addBatch(); + } + +} Property changes on: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/TestJDBCUpdateExecution.java ___________________________________________________________________ Name: svn:mime-type + text/plain Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/derby/TestDerbyCapabilities.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/derby/TestDerbyCapabilities.java 2009-08-06 14:43:44 UTC (rev 1215) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/derby/TestDerbyCapabilities.java 2009-08-06 16:34:42 UTC (rev 1216) @@ -31,14 +31,14 @@ @Test public void testLimitSupport() { DerbyCapabilities derbyCapabilities = new DerbyCapabilities(); assertFalse(derbyCapabilities.supportsRowLimit()); - derbyCapabilities.setVersion(DerbyCapabilities.TEN_5); + derbyCapabilities.setDatabaseVersion(DerbyCapabilities.TEN_5); assertTrue(derbyCapabilities.supportsRowLimit()); } @Test public void testFunctionSupport() { DerbyCapabilities derbyCapabilities = new DerbyCapabilities(); assertEquals(27, derbyCapabilities.getSupportedFunctions().size()); - derbyCapabilities.setVersion(DerbyCapabilities.TEN_4); + derbyCapabilities.setDatabaseVersion(DerbyCapabilities.TEN_4); assertEquals(43, derbyCapabilities.getSupportedFunctions().size()); } Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/mysql/TestMySQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/mysql/TestMySQLTranslator.java 2009-08-06 14:43:44 UTC (rev 1215) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/mysql/TestMySQLTranslator.java 2009-08-06 16:34:42 UTC (rev 1216) @@ -192,5 +192,14 @@ MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, input, output, TRANSLATOR); + } + + @Test public void testTimestampLiteral() throws Exception { + String input = "select smalla.intkey from bqt1.smalla where smalla.timestampvalue = '2009-08-06 12:23:34.999'"; //$NON-NLS-1$ + String output = "SELECT SmallA.IntKey FROM SmallA WHERE SmallA.TimestampValue = {ts '2009-08-06 12:23:34.0'}"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, + output, TRANSLATOR); } } Modified: trunk/test-integration/src/test/java/com/metamatrix/connector/jdbc/oracle/TestOracleSQLConversionVisitor.java =================================================================== --- trunk/test-integration/src/test/java/com/metamatrix/connector/jdbc/oracle/TestOracleSQLConversionVisitor.java 2009-08-06 14:43:44 UTC (rev 1215) +++ trunk/test-integration/src/test/java/com/metamatrix/connector/jdbc/oracle/TestOracleSQLConversionVisitor.java 2009-08-06 16:34:42 UTC (rev 1216) @@ -301,7 +301,7 @@ helpTestVisitor(getTestVDB(), "select part_id id FROM parts UNION ALL select part_name FROM parts UNION ALL select part_id FROM parts ORDER BY id", //$NON-NLS-1$ null, - "(SELECT g_2.PART_ID AS c_0 FROM PARTS g_2 UNION ALL SELECT g_1.PART_NAME AS c_0 FROM PARTS g_1) UNION ALL SELECT g_0.PART_ID AS c_0 FROM PARTS g_0 ORDER BY c_0", //$NON-NLS-1$ + "(SELECT g_2.PART_ID AS c_0 FROM PARTS g_2 UNION ALL SELECT g_1.PART_NAME AS c_0 FROM PARTS g_1) UNION ALL SELECT g_0.PART_ID AS c_0 FROM PARTS g_0 ORDER BY c_0 NULLS FIRST", //$NON-NLS-1$ true); } @@ -309,7 +309,7 @@ helpTestVisitor(getTestVDB(), "select part_id FROM parts UNION ALL select part_name FROM parts ORDER BY part_id", //$NON-NLS-1$ null, - "SELECT g_1.PART_ID AS c_0 FROM PARTS g_1 UNION ALL SELECT g_0.PART_NAME AS c_0 FROM PARTS g_0 ORDER BY c_0", //$NON-NLS-1$ + "SELECT g_1.PART_ID AS c_0 FROM PARTS g_1 UNION ALL SELECT g_0.PART_NAME AS c_0 FROM PARTS g_0 ORDER BY c_0 NULLS FIRST", //$NON-NLS-1$ true); } @@ -317,7 +317,7 @@ helpTestVisitor(getTestVDB(), "select part_id as p FROM parts UNION ALL select part_name FROM parts ORDER BY p", //$NON-NLS-1$ null, - "SELECT PARTS.PART_ID AS p FROM PARTS UNION ALL SELECT PARTS.PART_NAME FROM PARTS ORDER BY p"); //$NON-NLS-1$ + "SELECT PARTS.PART_ID AS p FROM PARTS UNION ALL SELECT PARTS.PART_NAME FROM PARTS ORDER BY p NULLS FIRST"); //$NON-NLS-1$ } @Test public void testUpdateWithFunction() throws Exception { @@ -458,7 +458,7 @@ @Test public void testLimitWithNestedInlineView() throws Exception { String input = "select max(intkey), stringkey from (select intkey, stringkey from bqt1.smalla order by intkey limit 100) x group by stringkey"; //$NON-NLS-1$ - String output = "SELECT MAX(x.intkey), x.stringkey FROM (SELECT * FROM (SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA ORDER BY intkey) WHERE ROWNUM <= 100) x GROUP BY x.stringkey"; //$NON-NLS-1$ + String output = "SELECT MAX(x.intkey), x.stringkey FROM (SELECT * FROM (SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA ORDER BY intkey NULLS FIRST) WHERE ROWNUM <= 100) x GROUP BY x.stringkey"; //$NON-NLS-1$ helpTestVisitor(FakeMetadataFactory.exampleBQTCached(), input, @@ -500,7 +500,7 @@ @Test public void testRowLimitWithUnionOrderBy() throws Exception { String input = "(select intkey from bqt1.smalla limit 50, 100) union select intnum from bqt1.smalla order by intkey"; //$NON-NLS-1$ - String output = "SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (SELECT g_1.IntKey AS c_0 FROM SmallA g_1) VIEW_FOR_LIMIT WHERE ROWNUM <= 150) WHERE ROWNUM_ > 50 UNION SELECT g_0.IntNum AS c_0 FROM SmallA g_0 ORDER BY c_0"; //$NON-NLS-1$ + String output = "SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (SELECT g_1.IntKey AS c_0 FROM SmallA g_1) VIEW_FOR_LIMIT WHERE ROWNUM <= 150) WHERE ROWNUM_ > 50 UNION SELECT g_0.IntNum AS c_0 FROM SmallA g_0 ORDER BY c_0 NULLS FIRST"; //$NON-NLS-1$ CommandBuilder commandBuilder = new CommandBuilder(FakeMetadataFactory.exampleBQTCached()); ICommand obj = commandBuilder.getCommand(input, true, true);