Repository: metamodel Updated Branches: refs/heads/master 499aca4d6 -> 5370703a7
METAMODEL-198: Fixed Closes #59 Project: http://git-wip-us.apache.org/repos/asf/metamodel/repo Commit: http://git-wip-us.apache.org/repos/asf/metamodel/commit/5370703a Tree: http://git-wip-us.apache.org/repos/asf/metamodel/tree/5370703a Diff: http://git-wip-us.apache.org/repos/asf/metamodel/diff/5370703a Branch: refs/heads/master Commit: 5370703a7379c654888e1f442166926be34b9b9f Parents: 499aca4 Author: Kasper Sørensen <[email protected]> Authored: Mon Oct 19 11:19:41 2015 +0200 Committer: Kasper Sørensen <[email protected]> Committed: Mon Oct 19 11:19:41 2015 +0200 ---------------------------------------------------------------------- CHANGES.md | 4 + jdbc/pom.xml | 4 +- .../apache/metamodel/jdbc/JdbcDataContext.java | 4 + .../org/apache/metamodel/jdbc/JdbcUtils.java | 33 +++- .../jdbc/dialects/DefaultQueryRewriter.java | 38 +++- .../jdbc/dialects/SQLiteQueryRewriter.java | 40 ++++ .../org/apache/metamodel/jdbc/DerbyTest.java | 26 ++- .../apache/metamodel/jdbc/H2databaseTest.java | 5 + .../org/apache/metamodel/jdbc/HsqldbTest.java | 6 + .../metamodel/jdbc/JdbcTestTemplates.java | 181 ++++++++++++++++++- .../org/apache/metamodel/jdbc/SqliteTest.java | 24 ++- .../jdbc/integrationtests/DB2Test.java | 4 +- .../jdbc/integrationtests/MysqlTest.java | 11 ++ .../jdbc/integrationtests/OracleTest.java | 11 ++ .../jdbc/integrationtests/PostgresqlTest.java | 10 + .../SQLServerJtdsDriverTest.java | 10 + 16 files changed, 381 insertions(+), 30 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/metamodel/blob/5370703a/CHANGES.md ---------------------------------------------------------------------- diff --git a/CHANGES.md b/CHANGES.md index a753469..ea13708 100644 --- a/CHANGES.md +++ b/CHANGES.md @@ -1,3 +1,7 @@ +### Apache MetaModel (work in progress) + + * [METAMODEL-198] - Fixed support for JDBC TIMESTAMP precision to match the underlying database's precision. + ### Apache MetaModel 4.4.0 * [METAMODEL-192] - Added support for Scalar functions. We have a basic set of datatype conversion functions as well as support for UDF via implementing the ScalarFunction interface. http://git-wip-us.apache.org/repos/asf/metamodel/blob/5370703a/jdbc/pom.xml ---------------------------------------------------------------------- diff --git a/jdbc/pom.xml b/jdbc/pom.xml index cc7aab2..56aee2e 100644 --- a/jdbc/pom.xml +++ b/jdbc/pom.xml @@ -72,9 +72,9 @@ <scope>test</scope> </dependency> <dependency> - <groupId>postgresql</groupId> + <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> - <version>9.1-901.jdbc4</version> + <version>9.3-1104-jdbc4</version> <scope>test</scope> </dependency> <dependency> http://git-wip-us.apache.org/repos/asf/metamodel/blob/5370703a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDataContext.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDataContext.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDataContext.java index db0ca11..27f638b 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDataContext.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDataContext.java @@ -54,6 +54,7 @@ import org.apache.metamodel.jdbc.dialects.MysqlQueryRewriter; import org.apache.metamodel.jdbc.dialects.OracleQueryRewriter; import org.apache.metamodel.jdbc.dialects.PostgresqlQueryRewriter; import org.apache.metamodel.jdbc.dialects.SQLServerQueryRewriter; +import org.apache.metamodel.jdbc.dialects.SQLiteQueryRewriter; import org.apache.metamodel.query.CompiledQuery; import org.apache.metamodel.query.Query; import org.apache.metamodel.query.SelectItem; @@ -87,6 +88,7 @@ public class JdbcDataContext extends AbstractDataContext implements UpdateableDa public static final String DATABASE_PRODUCT_DB2_PREFIX = "DB2/"; public static final String DATABASE_PRODUCT_ORACLE = "Oracle"; public static final String DATABASE_PRODUCT_HIVE = "Apache Hive"; + public static final String DATABASE_PRODUCT_SQLITE = "SQLite"; public static final ColumnType COLUMN_TYPE_CLOB_AS_STRING = new ColumnTypeImpl("CLOB", SuperColumnType.LITERAL_TYPE, String.class, true); @@ -234,6 +236,8 @@ public class JdbcDataContext extends AbstractDataContext implements UpdateableDa setQueryRewriter(new H2QueryRewriter(this)); } else if (DATABASE_PRODUCT_HIVE.equals(_databaseProductName)) { setQueryRewriter(new HiveQueryRewriter(this)); + } else if (DATABASE_PRODUCT_SQLITE.equals(_databaseProductName)) { + setQueryRewriter(new SQLiteQueryRewriter(this)); } else { setQueryRewriter(new DefaultQueryRewriter(this)); } http://git-wip-us.apache.org/repos/asf/metamodel/blob/5370703a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcUtils.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcUtils.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcUtils.java index ea06751..1073d6f 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcUtils.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcUtils.java @@ -25,6 +25,8 @@ import java.sql.Clob; import java.sql.NClob; import java.sql.PreparedStatement; import java.sql.SQLException; +import java.sql.Time; +import java.sql.Timestamp; import java.util.Calendar; import java.util.Date; import java.util.List; @@ -126,13 +128,11 @@ public final class JdbcUtils { cal.setTime((Date) value); st.setDate(valueIndex, new java.sql.Date(cal.getTimeInMillis()), cal); } else if (type == ColumnType.TIME && value instanceof Date) { - Calendar cal = Calendar.getInstance(); - cal.setTime((Date) value); - st.setTime(valueIndex, new java.sql.Time(cal.getTimeInMillis()), cal); + final Time time = toTime((Date) value); + st.setTime(valueIndex, time); } else if (type == ColumnType.TIMESTAMP && value instanceof Date) { - Calendar cal = Calendar.getInstance(); - cal.setTime((Date) value); - st.setTimestamp(valueIndex, new java.sql.Timestamp(cal.getTimeInMillis()), cal); + final Timestamp ts = toTimestamp((Date) value); + st.setTimestamp(valueIndex, ts); } else if (type == ColumnType.CLOB || type == ColumnType.NCLOB) { if (value instanceof InputStream) { InputStream inputStream = (InputStream) value; @@ -182,6 +182,24 @@ public final class JdbcUtils { } } + private static Time toTime(Date value) { + if (value instanceof Time) { + return (Time) value; + } + final Calendar cal = Calendar.getInstance(); + cal.setTime((Date) value); + return new java.sql.Time(cal.getTimeInMillis()); + } + + private static Timestamp toTimestamp(Date value) { + if (value instanceof Timestamp) { + return (Timestamp) value; + } + final Calendar cal = Calendar.getInstance(); + cal.setTime((Date) value); + return new Timestamp(cal.getTimeInMillis()); + } + public static String getValueAsSql(Column column, Object value, IQueryRewriter queryRewriter) { if (value == null) { return "NULL"; @@ -211,7 +229,8 @@ public final class JdbcUtils { if (!inlineValues) { if (isPreparedParameterCandidate(whereItem)) { // replace operator with parameter - whereItem = new FilterItem(whereItem.getSelectItem(), whereItem.getOperator(), new QueryParameter()); + whereItem = new FilterItem(whereItem.getSelectItem(), whereItem.getOperator(), + new QueryParameter()); } } final String whereItemLabel = queryRewriter.rewriteFilterItem(whereItem); http://git-wip-us.apache.org/repos/asf/metamodel/blob/5370703a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DefaultQueryRewriter.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DefaultQueryRewriter.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DefaultQueryRewriter.java index 56e10c3..13db9df 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DefaultQueryRewriter.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DefaultQueryRewriter.java @@ -18,6 +18,7 @@ */ package org.apache.metamodel.jdbc.dialects; +import java.sql.Timestamp; import java.util.List; import java.util.ListIterator; @@ -114,6 +115,9 @@ public class DefaultQueryRewriter extends AbstractQueryRewriter { FilterItem replacementFilterItem = new FilterItem(item.getSelectItem(), item.getOperator(), str); return super.rewriteFilterItem(replacementFilterItem); } + } else if (operand instanceof Timestamp) { + final String timestampLiteral = rewriteTimestamp((Timestamp) operand); + return rewriteFilterItemWithOperandLiteral(item, timestampLiteral); } else if (operand instanceof Iterable || operand.getClass().isArray()) { // operand is a set of values (typically in combination with an // IN operator). Each individual element must be escaped. @@ -145,7 +149,39 @@ public class DefaultQueryRewriter extends AbstractQueryRewriter { } return super.rewriteFilterItem(item); } - + + /** + * Rewrites a (non-compound) {@link FilterItem} when it's operand has + * already been rewritten into a SQL literal. + * + * @param item + * @param operandLiteral + * @return + */ + protected String rewriteFilterItemWithOperandLiteral(FilterItem item, String operandLiteral) { + final OperatorType operator = item.getOperator(); + final SelectItem selectItem = item.getSelectItem(); + final StringBuilder sb = new StringBuilder(); + sb.append(selectItem.getSameQueryAlias(false)); + FilterItem.appendOperator(sb, item.getOperand(), operator); + sb.append(operandLiteral); + return sb.toString(); + } + + /** + * Rewrites a {@link Timestamp} into it's literal representation as known by + * this SQL dialect. + * + * This default implementation returns the JDBC spec's escape syntax for a + * timestamp: {ts 'yyyy-mm-dd hh:mm:ss.f . . .'} + * + * @param ts + * @return + */ + protected String rewriteTimestamp(Timestamp ts) { + return "{ts '" + ts.toString() + "'}"; + } + @Override public boolean isScalarFunctionSupported(ScalarFunction function) { return false; http://git-wip-us.apache.org/repos/asf/metamodel/blob/5370703a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/SQLiteQueryRewriter.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/SQLiteQueryRewriter.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/SQLiteQueryRewriter.java new file mode 100644 index 0000000..c32b849 --- /dev/null +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/SQLiteQueryRewriter.java @@ -0,0 +1,40 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.metamodel.jdbc.dialects; + +import java.sql.Timestamp; + +import org.apache.metamodel.jdbc.JdbcDataContext; + +/** + * Query rewriter for SQLite database + */ +public class SQLiteQueryRewriter extends DefaultQueryRewriter { + + public SQLiteQueryRewriter(JdbcDataContext dataContext) { + super(dataContext); + } + + @Override + protected String rewriteTimestamp(Timestamp ts) { + // SQLite's driver does not support the JDBC escape syntax. + // see http://www.sqlite.org/lang_datefunc.html + return "'" + ts.toString() + "'"; + } +} http://git-wip-us.apache.org/repos/asf/metamodel/blob/5370703a/jdbc/src/test/java/org/apache/metamodel/jdbc/DerbyTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/DerbyTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/DerbyTest.java index cee8454..2d0de42 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/DerbyTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/DerbyTest.java @@ -23,6 +23,7 @@ import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Arrays; +import java.util.concurrent.TimeUnit; import junit.framework.TestCase; @@ -59,8 +60,8 @@ public class DerbyTest extends TestCase { File dbFile = new File("src/test/resources/derby_testdb.jar"); assertTrue(dbFile.exists()); Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); - _connection = DriverManager.getConnection("jdbc:derby:jar:(" + dbFile.getAbsolutePath() - + ")derby_testdb;territory=en"); + _connection = DriverManager + .getConnection("jdbc:derby:jar:(" + dbFile.getAbsolutePath() + ")derby_testdb;territory=en"); } @Override @@ -75,6 +76,11 @@ public class DerbyTest extends TestCase { } } + public void testTimestampValueInsertSelect() throws Exception { + Connection conn = DriverManager.getConnection("jdbc:derby:target/temp_derby;create=true"); + JdbcTestTemplates.timestampValueInsertSelect(conn, TimeUnit.NANOSECONDS); + } + public void testCreateInsertAndUpdate() throws Exception { Connection conn = DriverManager.getConnection("jdbc:derby:target/temp_derby;create=true"); JdbcDataContext dc = new JdbcDataContext(conn); @@ -89,7 +95,7 @@ public class DerbyTest extends TestCase { public void testDifferentOperators() throws Exception { Connection conn = DriverManager.getConnection("jdbc:derby:target/temp_derby;create=true"); - + JdbcTestTemplates.differentOperatorsTest(conn); } @@ -118,7 +124,8 @@ public class DerbyTest extends TestCase { } public void testQueryWithFilter() throws Exception { - JdbcDataContext dc = new JdbcDataContext(_connection, new TableType[] { TableType.TABLE, TableType.VIEW }, null); + JdbcDataContext dc = new JdbcDataContext(_connection, new TableType[] { TableType.TABLE, TableType.VIEW }, + null); Query q = dc.query().from("APP", "CUSTOMERS").select("CUSTOMERNUMBER").where("ADDRESSLINE2").isNotNull() .toQuery(); assertEquals(25000, dc.getFetchSizeCalculator().getFetchSize(q)); @@ -180,8 +187,7 @@ public class DerbyTest extends TestCase { assertEquals(11, schemas.length); assertEquals("Schema[name=APP]", schemas[0].toString()); assertEquals(13, schemas[0].getTableCount()); - assertEquals("[Table[name=CUSTOMERS,type=TABLE,remarks=], " - + "Table[name=CUSTOMER_W_TER,type=TABLE,remarks=], " + assertEquals("[Table[name=CUSTOMERS,type=TABLE,remarks=], " + "Table[name=CUSTOMER_W_TER,type=TABLE,remarks=], " + "Table[name=DEPARTMENT_MANAGERS,type=TABLE,remarks=], " + "Table[name=EMPLOYEES,type=TABLE,remarks=], " + "Table[name=OFFICES,type=TABLE,remarks=], " + "Table[name=ORDERDETAILS,type=TABLE,remarks=], " + "Table[name=ORDERFACT,type=TABLE,remarks=], " @@ -239,7 +245,8 @@ public class DerbyTest extends TestCase { } public void testQueryRewriterQuoteAliases() throws Exception { - JdbcDataContext dc = new JdbcDataContext(_connection, new TableType[] { TableType.TABLE, TableType.VIEW }, null); + JdbcDataContext dc = new JdbcDataContext(_connection, new TableType[] { TableType.TABLE, TableType.VIEW }, + null); IQueryRewriter queryRewriter = dc.getQueryRewriter(); assertSame(DefaultQueryRewriter.class, queryRewriter.getClass()); @@ -304,7 +311,8 @@ public class DerbyTest extends TestCase { .ofType(ColumnType.INTEGER).execute(); writtenTableRef.set(writtenTable); String sql = createTableBuilder.createSqlStatement(); - assertEquals("CREATE TABLE APP.test_table (id INTEGER, name VARCHAR(255), age INTEGER, PRIMARY KEY(id))", + assertEquals( + "CREATE TABLE APP.test_table (id INTEGER, name VARCHAR(255), age INTEGER, PRIMARY KEY(id))", sql.replaceAll("\"", "|")); assertNotNull(writtenTable); } @@ -393,7 +401,7 @@ public class DerbyTest extends TestCase { JdbcTestTemplates.convertClobToString(dc); } - + public void testInterpretationOfNull() throws Exception { Connection conn = DriverManager.getConnection("jdbc:derby:target/temp_derby;create=true"); JdbcTestTemplates.interpretationOfNulls(conn); http://git-wip-us.apache.org/repos/asf/metamodel/blob/5370703a/jdbc/src/test/java/org/apache/metamodel/jdbc/H2databaseTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/H2databaseTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/H2databaseTest.java index 118864c..6560247 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/H2databaseTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/H2databaseTest.java @@ -24,6 +24,7 @@ import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Arrays; import java.util.List; +import java.util.concurrent.TimeUnit; import junit.framework.TestCase; @@ -80,6 +81,10 @@ public class H2databaseTest extends TestCase { JdbcDataContext dc = new JdbcDataContext(conn); JdbcTestTemplates.compositeKeyCreation(dc, "metamodel_test_composite_keys"); } + + public void testTimestampValueInsertSelect() throws Exception { + JdbcTestTemplates.timestampValueInsertSelect(conn, TimeUnit.NANOSECONDS); + } public void testUsingSingleUpdates() throws Exception { final JdbcDataContext dc = new JdbcDataContext(conn); http://git-wip-us.apache.org/repos/asf/metamodel/blob/5370703a/jdbc/src/test/java/org/apache/metamodel/jdbc/HsqldbTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/HsqldbTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/HsqldbTest.java index 1d69d6f..db969c4 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/HsqldbTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/HsqldbTest.java @@ -23,6 +23,7 @@ import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.Statement; import java.util.Arrays; +import java.util.concurrent.TimeUnit; import javax.swing.table.TableModel; @@ -70,6 +71,11 @@ public class HsqldbTest extends TestCase { super.tearDown(); _connection.close(); } + + public void testTimestampValueInsertSelect() throws Exception { + Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:" + getName(), USERNAME, PASSWORD); + JdbcTestTemplates.timestampValueInsertSelect(connection, TimeUnit.NANOSECONDS); + } public void testCreateInsertAndUpdate() throws Exception { Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:" + getName(), USERNAME, PASSWORD); http://git-wip-us.apache.org/repos/asf/metamodel/blob/5370703a/jdbc/src/test/java/org/apache/metamodel/jdbc/JdbcTestTemplates.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/JdbcTestTemplates.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/JdbcTestTemplates.java index e04e4c6..2d5840f 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/JdbcTestTemplates.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/JdbcTestTemplates.java @@ -28,17 +28,21 @@ import java.io.StringReader; import java.sql.Clob; import java.sql.Connection; import java.sql.SQLException; +import java.sql.Statement; +import java.sql.Timestamp; import java.util.Arrays; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.Map; +import java.util.concurrent.TimeUnit; import org.apache.metamodel.BatchUpdateScript; import org.apache.metamodel.UpdateCallback; import org.apache.metamodel.UpdateScript; import org.apache.metamodel.create.ColumnCreationBuilder; import org.apache.metamodel.create.CreateTable; +import org.apache.metamodel.create.TableCreationBuilder; import org.apache.metamodel.data.DataSet; import org.apache.metamodel.data.Row; import org.apache.metamodel.drop.DropTable; @@ -49,6 +53,7 @@ import org.apache.metamodel.schema.Schema; import org.apache.metamodel.schema.Table; import org.apache.metamodel.update.Update; import org.apache.metamodel.util.DateUtils; +import org.apache.metamodel.util.FileHelper; import org.apache.metamodel.util.Month; import org.junit.Ignore; @@ -584,8 +589,9 @@ public class JdbcTestTemplates { } dataContext.executeUpdate(new CreateTable(defaultSchema, testTableName).withColumn("mykey1") - .ofType(ColumnType.INTEGER).nullable(false).asPrimaryKey().withColumn("mykey2").ofType(ColumnType.INTEGER) - .nullable(false).asPrimaryKey().withColumn("name").ofType(ColumnType.VARCHAR).ofSize(20)); + .ofType(ColumnType.INTEGER).nullable(false).asPrimaryKey().withColumn("mykey2") + .ofType(ColumnType.INTEGER).nullable(false).asPrimaryKey().withColumn("name") + .ofType(ColumnType.VARCHAR).ofSize(20)); try { final Table table = defaultSchema.getTableByName(testTableName); assertNotNull(table); @@ -633,4 +639,175 @@ public class JdbcTestTemplates { dataContext.executeUpdate(new DropTable(defaultSchema, testTableName)); } } + + /** + * + * @param conn + * @param databasePrecision + * the precision with which the database can handle timestamp + * values. Expected values: {@link TimeUnit#SECONDS}, + * {@link TimeUnit#MILLISECONDS}, {@link TimeUnit#MICROSECONDS} + * or {@link TimeUnit#NANOSECONDS}. + * + * @throws Exception + */ + public static void timestampValueInsertSelect(Connection conn, TimeUnit databasePrecision) throws Exception { + timestampValueInsertSelect(conn, databasePrecision, null); + } + + public static void timestampValueInsertSelect(Connection conn, TimeUnit databasePrecision, final String nativeType) + throws Exception { + assertNotNull(conn); + + final Statement statement = conn.createStatement(); + try { + // clean up, if nescesary + statement.execute("DROP TABLE test_table"); + } catch (SQLException e) { + // do nothing + } finally { + FileHelper.safeClose(statement); + } + + assertFalse(conn.isReadOnly()); + + JdbcDataContext dc = new JdbcDataContext(conn); + final Schema schema = dc.getDefaultSchema(); + + final Timestamp timestamp1; + switch (databasePrecision) { + case SECONDS: + timestamp1 = Timestamp.valueOf("2015-10-16 16:33:33"); + break; + case MILLISECONDS: + timestamp1 = Timestamp.valueOf("2015-10-16 16:33:33.456"); + break; + case MICROSECONDS: + timestamp1 = Timestamp.valueOf("2015-10-16 16:33:33.456001"); + break; + case NANOSECONDS: + timestamp1 = Timestamp.valueOf("2015-10-16 16:33:33.456001234"); + break; + default: + throw new UnsupportedOperationException("Unsupported database precision: " + databasePrecision); + } + + final Timestamp timestamp2; + switch (databasePrecision) { + case SECONDS: + timestamp2 = Timestamp.valueOf("2015-10-16 16:33:34"); + break; + case MILLISECONDS: + timestamp2 = Timestamp.valueOf("2015-10-16 16:33:34.683"); + break; + case MICROSECONDS: + timestamp2 = Timestamp.valueOf("2015-10-16 16:33:34.683005"); + break; + case NANOSECONDS: + timestamp2 = Timestamp.valueOf("2015-10-16 16:33:34.683005678"); + break; + default: + throw new UnsupportedOperationException("Unsupported database precision: " + databasePrecision); + } + + dc.executeUpdate(new UpdateScript() { + @Override + public void run(UpdateCallback cb) { + TableCreationBuilder tableBuilder = cb.createTable(schema, "test_table"); + tableBuilder.withColumn("id").ofType(ColumnType.INTEGER); + tableBuilder.withColumn("insertiontime").ofType(ColumnType.TIMESTAMP); + if (nativeType == null) { + tableBuilder.withColumn("insertiontime").ofType(ColumnType.TIMESTAMP); + } else { + tableBuilder.withColumn("insertiontime").ofType(ColumnType.TIMESTAMP).ofNativeType(nativeType); + } + Table table = tableBuilder.execute(); + + cb.insertInto(table).value("id", 1).value("insertiontime", timestamp1).execute(); + cb.insertInto(table).value("id", 2).value("insertiontime", timestamp2).execute(); + } + }); + + DataSet ds = dc.query().from("test_table").select("id").and("insertiontime").execute(); + assertTrue(ds.next()); + + switch (databasePrecision) { + case SECONDS: + assertEquals("Row[values=[1, 2015-10-16 16:33:33]]", ds.getRow().toString()); + break; + case MILLISECONDS: + assertEquals("Row[values=[1, 2015-10-16 16:33:33.456]]", ds.getRow().toString()); + break; + case MICROSECONDS: + assertEquals("Row[values=[1, 2015-10-16 16:33:33.456001]]", ds.getRow().toString()); + break; + case NANOSECONDS: + assertEquals("Row[values=[1, 2015-10-16 16:33:33.456001234]]", ds.getRow().toString()); + break; + default: + throw new UnsupportedOperationException("Unsupported database precision: " + databasePrecision); + } + assertTrue(ds.getRow().getValue(0) instanceof Number); + assertTrue(ds.next()); + + switch (databasePrecision) { + case SECONDS: + assertEquals("Row[values=[2, 2015-10-16 16:33:34]]", ds.getRow().toString()); + break; + case MILLISECONDS: + assertEquals("Row[values=[2, 2015-10-16 16:33:34.683]]", ds.getRow().toString()); + break; + case MICROSECONDS: + assertEquals("Row[values=[2, 2015-10-16 16:33:34.683005]]", ds.getRow().toString()); + break; + case NANOSECONDS: + assertEquals("Row[values=[2, 2015-10-16 16:33:34.683005678]]", ds.getRow().toString()); + break; + default: + throw new UnsupportedOperationException("Unsupported database precision: " + databasePrecision); + } + assertFalse(ds.next()); + ds.close(); + + if (databasePrecision != TimeUnit.SECONDS) { + Query query = dc.query().from("test_table").select("id").where("insertiontime").lessThan(timestamp2) + .toQuery(); + try { + ds = dc.executeQuery(query); + } catch (Exception e) { + System.out.println("Failing query was: " + dc.getQueryRewriter().rewriteQuery(query)); + throw e; + } + assertTrue(ds.next()); + assertEquals("Row[values=[1]]", ds.getRow().toString()); + assertFalse(ds.next()); + ds.close(); + + ds = dc.query().from("test_table").select("id").where("insertiontime").greaterThan(timestamp1).execute(); + assertTrue(ds.next()); + assertEquals("Row[values=[2]]", ds.getRow().toString()); + assertFalse(ds.next()); + ds.close(); + + dc.executeUpdate(new UpdateScript() { + @Override + public void run(UpdateCallback callback) { + callback.deleteFrom("test_table").where("insertiontime").eq(timestamp1).execute(); + } + }); + + ds = dc.query().from("test_table").selectCount().execute(); + assertTrue(ds.next()); + assertEquals("Row[values=[1]]", ds.getRow().toString()); + assertFalse(ds.next()); + ds.close(); + } + + dc.executeUpdate(new UpdateScript() { + @Override + public void run(UpdateCallback callback) { + callback.dropTable("test_table").execute(); + } + }); + } } http://git-wip-us.apache.org/repos/asf/metamodel/blob/5370703a/jdbc/src/test/java/org/apache/metamodel/jdbc/SqliteTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/SqliteTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/SqliteTest.java index c8cfd28..3a8d1a4 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/SqliteTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/SqliteTest.java @@ -24,6 +24,7 @@ import java.sql.DriverManager; import java.sql.SQLException; import java.util.Arrays; import java.util.List; +import java.util.concurrent.TimeUnit; import junit.framework.TestCase; @@ -31,6 +32,7 @@ import org.apache.metamodel.DataContext; import org.apache.metamodel.UpdateCallback; import org.apache.metamodel.UpdateScript; import org.apache.metamodel.data.DataSet; +import org.apache.metamodel.jdbc.dialects.SQLiteQueryRewriter; import org.apache.metamodel.query.OperatorType; import org.apache.metamodel.query.Query; import org.apache.metamodel.schema.Column; @@ -70,6 +72,10 @@ public class SqliteTest extends TestCase { _connection.close(); } + public void testTimestampValueInsertSelect() throws Exception { + JdbcTestTemplates.timestampValueInsertSelect(_connection, TimeUnit.SECONDS); + } + public void testCreateInsertAndUpdate() throws Exception { JdbcDataContext dc = new JdbcDataContext(_connection); JdbcTestTemplates.simpleCreateInsertUpdateAndDrop(dc, "metamodel_test_simple"); @@ -84,6 +90,11 @@ public class SqliteTest extends TestCase { JdbcTestTemplates.differentOperatorsTest(_connection); } + public void testGetQueryRewriter() throws Exception { + JdbcDataContext dc = new JdbcDataContext(_connection); + assertTrue(dc.getQueryRewriter() instanceof SQLiteQueryRewriter); + } + public void testGetSchemas() throws Exception { DataContext dc = new JdbcDataContext(_connection); String[] schemaNames = dc.getSchemaNames(); @@ -99,9 +110,8 @@ public class SqliteTest extends TestCase { + "Table[name=auth_cookie,type=TABLE,remarks=null], " + "Table[name=session,type=TABLE,remarks=null], " + "Table[name=session_attribute,type=TABLE,remarks=null], " + "Table[name=attachment,type=TABLE,remarks=null], " + "Table[name=wiki,type=TABLE,remarks=null], " - + "Table[name=revision,type=TABLE,remarks=null], " - + "Table[name=node_change,type=TABLE,remarks=null], " + "Table[name=ticket,type=TABLE,remarks=null], " - + "Table[name=ticket_change,type=TABLE,remarks=null], " + + "Table[name=revision,type=TABLE,remarks=null], " + "Table[name=node_change,type=TABLE,remarks=null], " + + "Table[name=ticket,type=TABLE,remarks=null], " + "Table[name=ticket_change,type=TABLE,remarks=null], " + "Table[name=ticket_custom,type=TABLE,remarks=null], " + "Table[name=enum,type=TABLE,remarks=null], " + "Table[name=component,type=TABLE,remarks=null], " + "Table[name=milestone,type=TABLE,remarks=null], " + "Table[name=version,type=TABLE,remarks=null], " + "Table[name=report,type=TABLE,remarks=null]]", @@ -137,8 +147,8 @@ public class SqliteTest extends TestCase { Table wikiTable = schema.getTableByName("WIKI"); - Query q = new Query().selectCount().from(wikiTable) - .where(wikiTable.getColumnByName("name"), OperatorType.LIKE, "Trac%"); + Query q = new Query().selectCount().from(wikiTable).where(wikiTable.getColumnByName("name"), OperatorType.LIKE, + "Trac%"); assertEquals("SELECT COUNT(*) FROM wiki WHERE wiki.name LIKE 'Trac%'", q.toString()); assertEquals(1, dc.getFetchSizeCalculator().getFetchSize(q)); assertEquals(37, dc.executeQuery(q).toObjectArrays().get(0)[0]); @@ -165,8 +175,8 @@ public class SqliteTest extends TestCase { assertEquals("wiki.name", nameColumn.getQualifiedLabel()); assertEquals( - "Column[name=name,columnNumber=0,type=VARCHAR,nullable=true,nativeType=TEXT,columnSize=2000000000]", dc - .getColumnByQualifiedLabel("wiki.name").toString()); + "Column[name=name,columnNumber=0,type=VARCHAR,nullable=true,nativeType=TEXT,columnSize=2000000000]", + dc.getColumnByQualifiedLabel("wiki.name").toString()); assertEquals("Table[name=wiki,type=TABLE,remarks=null]", dc.getTableByQualifiedLabel("WIKI").toString()); } http://git-wip-us.apache.org/repos/asf/metamodel/blob/5370703a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/DB2Test.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/DB2Test.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/DB2Test.java index c0aeb83..aef6f51 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/DB2Test.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/DB2Test.java @@ -45,12 +45,12 @@ public class DB2Test extends AbstractJdbIntegrationTest { JdbcTestTemplates.simpleCreateInsertUpdateAndDrop(getDataContext(), "metamodel_db2_test"); } - + public void testCompositePrimaryKeyCreation() throws Exception { if (!isConfigured()) { return; } - + JdbcTestTemplates.compositeKeyCreation(getDataContext(), "metamodel_test_composite_keys"); } http://git-wip-us.apache.org/repos/asf/metamodel/blob/5370703a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/MysqlTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/MysqlTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/MysqlTest.java index 4ac0947..ba273e3 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/MysqlTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/MysqlTest.java @@ -18,11 +18,13 @@ */ package org.apache.metamodel.jdbc.integrationtests; +import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.Arrays; import java.util.List; +import java.util.concurrent.TimeUnit; import javax.swing.table.TableModel; @@ -73,6 +75,15 @@ public class MysqlTest extends AbstractJdbIntegrationTest { JdbcTestTemplates.compositeKeyCreation(getDataContext(), "metamodel_test_composite_keys"); } + + public void testTimestampValueInsertSelect() throws Exception { + if (!isConfigured()) { + return; + } + + final Connection connection = getConnection(); + JdbcTestTemplates.timestampValueInsertSelect(connection, TimeUnit.MICROSECONDS, "TIMESTAMP(6)"); + } public void testInterpretationOfNull() throws Exception { if (!isConfigured()) { http://git-wip-us.apache.org/repos/asf/metamodel/blob/5370703a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/OracleTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/OracleTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/OracleTest.java index d16547f..6924362 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/OracleTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/OracleTest.java @@ -18,8 +18,10 @@ */ package org.apache.metamodel.jdbc.integrationtests; +import java.sql.Connection; import java.sql.ResultSet; import java.util.Arrays; +import java.util.concurrent.TimeUnit; import javax.swing.table.TableModel; @@ -87,6 +89,15 @@ public class OracleTest extends AbstractJdbIntegrationTest { JdbcTestTemplates.simpleCreateInsertUpdateAndDrop(getDataContext(), "metamodel_test_simple"); } + + public void testTimestampValueInsertSelect() throws Exception { + if (!isConfigured()) { + return; + } + + final Connection connection = getConnection(); + JdbcTestTemplates.timestampValueInsertSelect(connection, TimeUnit.MICROSECONDS, null); + } public void testCompositePrimaryKeyCreation() throws Exception { if (!isConfigured()) { http://git-wip-us.apache.org/repos/asf/metamodel/blob/5370703a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/PostgresqlTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/PostgresqlTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/PostgresqlTest.java index a68cc99..5cf6822 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/PostgresqlTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/PostgresqlTest.java @@ -23,6 +23,7 @@ import java.sql.Connection; import java.sql.DatabaseMetaData; import java.util.Arrays; import java.util.List; +import java.util.concurrent.TimeUnit; import javax.swing.table.TableModel; @@ -64,6 +65,15 @@ public class PostgresqlTest extends AbstractJdbIntegrationTest { protected String getPropertyPrefix() { return "postgresql"; } + + public void testTimestampValueInsertSelect() throws Exception { + if (!isConfigured()) { + return; + } + + final Connection connection = getConnection(); + JdbcTestTemplates.timestampValueInsertSelect(connection, TimeUnit.MICROSECONDS); + } public void testCreateInsertAndUpdate() throws Exception { if (!isConfigured()) { http://git-wip-us.apache.org/repos/asf/metamodel/blob/5370703a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/SQLServerJtdsDriverTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/SQLServerJtdsDriverTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/SQLServerJtdsDriverTest.java index 336814e..c72d2f3 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/SQLServerJtdsDriverTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/SQLServerJtdsDriverTest.java @@ -21,6 +21,7 @@ package org.apache.metamodel.jdbc.integrationtests; import java.sql.Connection; import java.sql.SQLException; import java.util.Arrays; +import java.util.concurrent.TimeUnit; import org.apache.commons.dbcp.BasicDataSource; import org.apache.metamodel.UpdateCallback; @@ -62,6 +63,15 @@ public class SQLServerJtdsDriverTest extends AbstractJdbIntegrationTest { JdbcTestTemplates.simpleCreateInsertUpdateAndDrop(getDataContext(), "metamodel_test_simple"); } + public void testTimestampValueInsertSelect() throws Exception { + if (!isConfigured()) { + return; + } + + final Connection connection = getConnection(); + JdbcTestTemplates.timestampValueInsertSelect(connection, TimeUnit.NANOSECONDS, "datetime"); + } + public void testCreateTableInUpdateScript() throws Exception { if (!isConfigured()) { return;
