You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@metamodel.apache.org by ka...@apache.org on 2014/05/31 14:13:26 UTC
[1/4] METAMODEL-45: Made abstract JDBC integration test class and
implemented it for PostgreSQL integration test.
Repository: incubator-metamodel
Updated Branches:
refs/heads/master 2023997c9 -> 276c173a2
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/b6979189/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
new file mode 100644
index 0000000..cd8e550
--- /dev/null
+++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/PostgresqlTest.java
@@ -0,0 +1,904 @@
+/**
+ * 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.integrationtests;
+
+import java.lang.reflect.Method;
+import java.sql.Connection;
+import java.sql.DatabaseMetaData;
+import java.util.Arrays;
+import java.util.List;
+
+import javax.swing.table.TableModel;
+
+import org.apache.metamodel.BatchUpdateScript;
+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.data.DataSetTableModel;
+import org.apache.metamodel.insert.RowInsertionBuilder;
+import org.apache.metamodel.jdbc.JdbcDataContext;
+import org.apache.metamodel.jdbc.JdbcTestTemplates;
+import org.apache.metamodel.jdbc.QuerySplitter;
+import org.apache.metamodel.query.FilterItem;
+import org.apache.metamodel.query.FunctionType;
+import org.apache.metamodel.query.OperatorType;
+import org.apache.metamodel.query.OrderByItem;
+import org.apache.metamodel.query.Query;
+import org.apache.metamodel.query.SelectItem;
+import org.apache.metamodel.schema.Column;
+import org.apache.metamodel.schema.ColumnType;
+import org.apache.metamodel.schema.Relationship;
+import org.apache.metamodel.schema.Schema;
+import org.apache.metamodel.schema.Table;
+import org.junit.Ignore;
+
+/**
+ * Test case that tests postgresql interaction. The test requires the
+ * "dellstore2" sample database that can be found at pgfoundry.
+ *
+ * @see http://pgfoundry.org/projects/dbsamples/
+ */
+public class PostgresqlTest extends AbstractJdbIntegrationTest {
+
+ private static final String PROPERTY_LONGRUNNINGTESTS = "jdbc.postgresql.longrunningtests";
+
+ @Override
+ protected String getPropertyPrefix() {
+ return "postgresql";
+ }
+
+ public void testInterpretationOfNull() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ JdbcTestTemplates.interpretationOfNulls(getConnection());
+ }
+
+ private JdbcDataContext createLimitAndOffsetTestData() {
+ final JdbcDataContext dc = new JdbcDataContext(getConnection());
+
+ if (dc.getTableByQualifiedLabel("test_table") != null) {
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback callback) {
+ callback.dropTable("test_table").execute();
+ }
+ });
+ }
+
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback callback) {
+ Table table = callback.createTable(dc.getDefaultSchema(), "test_table").withColumn("foo")
+ .ofType(ColumnType.INTEGER).withColumn("bar").ofType(ColumnType.VARCHAR).execute();
+ callback.insertInto(table).value("foo", 1).value("bar", "hello").execute();
+ callback.insertInto(table).value("foo", 2).value("bar", "there").execute();
+ callback.insertInto(table).value("foo", 3).value("bar", "world").execute();
+ }
+ });
+
+ dc.refreshSchemas();
+
+ return dc;
+ }
+
+ public void testLimit() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ JdbcDataContext dc = createLimitAndOffsetTestData();
+ Schema schema = dc.getDefaultSchema();
+ Table productsTable = schema.getTableByName("test_table");
+
+ DataSet ds = dc.query().from(productsTable).select("foo").limit(2).execute();
+ assertTrue(ds.next());
+ assertEquals("Row[values=[1]]", ds.getRow().toString());
+ assertTrue(ds.next());
+ assertEquals("Row[values=[2]]", ds.getRow().toString());
+ assertFalse(ds.next());
+ ds.close();
+ }
+
+ public void testOffset() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ JdbcDataContext dc = createLimitAndOffsetTestData();
+ Schema schema = dc.getDefaultSchema();
+ Table productsTable = schema.getTableByName("test_table");
+
+ DataSet ds = dc.query().from(productsTable).select("foo").offset(1).execute();
+ assertTrue(ds.next());
+ assertEquals("Row[values=[2]]", ds.getRow().toString());
+ assertTrue(ds.next());
+ assertEquals("Row[values=[3]]", ds.getRow().toString());
+ assertFalse(ds.next());
+ ds.close();
+ }
+
+ public void testLimitAndOffset() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ JdbcDataContext dc = createLimitAndOffsetTestData();
+ Schema schema = dc.getDefaultSchema();
+ Table productsTable = schema.getTableByName("test_table");
+
+ DataSet ds = dc.query().from(productsTable).select("foo").limit(1).offset(1).execute();
+ assertTrue(ds.next());
+ assertEquals("Row[values=[2]]", ds.getRow().toString());
+ assertFalse(ds.next());
+
+ ds.close();
+ }
+
+ public void testQuotedInsertSyntax() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ final Connection connection = getConnection();
+
+ try {
+ connection.createStatement().execute("DROP TABLE my_table");
+ } catch (Exception e) {
+ // do nothing
+ }
+
+ JdbcDataContext dc = new JdbcDataContext(connection);
+ final Schema schema = dc.getDefaultSchema();
+
+ // create table
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ Table table = cb.createTable(schema, "my_table").withColumn("id").asPrimaryKey()
+ .ofType(ColumnType.INTEGER).ofNativeType("SERIAL").nullable(false).withColumn("name")
+ .ofType(ColumnType.VARCHAR).ofSize(10).withColumn("foo").ofType(ColumnType.BOOLEAN)
+ .nullable(true).withColumn("bar").ofType(ColumnType.BOOLEAN).nullable(true).execute();
+
+ assertEquals("my_table", table.getName());
+ }
+ });
+
+ assertTrue(dc.getColumnByQualifiedLabel("my_table.id").isPrimaryKey());
+ assertFalse(dc.getColumnByQualifiedLabel("my_table.name").isPrimaryKey());
+
+ // insert records
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback callback) {
+ RowInsertionBuilder builder = callback.insertInto("my_table").value("name", "row 1").value("foo", true);
+
+ try {
+ Method method = builder.getClass().getDeclaredMethod("createSqlStatement");
+ method.setAccessible(true);
+ Object result = method.invoke(builder);
+ assertEquals("INSERT INTO \"public\".\"my_table\" (name,foo) VALUES (?,?)", result.toString());
+ } catch (Exception e) {
+ throw new RuntimeException(e);
+ }
+
+ builder.execute();
+
+ callback.insertInto("my_table").value("name", "row 2").value("foo", false).execute();
+ }
+ });
+
+ // query
+ DataSet ds = dc.query().from("my_table").select("name").where("foo").eq(true).execute();
+ assertTrue(ds.next());
+ assertEquals("Row[values=[row 1]]", ds.getRow().toString());
+ assertFalse(ds.next());
+ ds.close();
+
+ // drop
+ dc.executeUpdate(new UpdateScript() {
+
+ @Override
+ public void run(UpdateCallback callback) {
+ callback.dropTable("my_table").execute();
+ }
+ });
+ }
+
+ public void testInsertOfDifferentTypes() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ final Connection connection = getConnection();
+
+ try {
+ connection.createStatement().execute("DROP TABLE my_table");
+ } catch (Exception e) {
+ // do nothing
+ }
+
+ JdbcDataContext dc = new JdbcDataContext(connection);
+ final Schema schema = dc.getDefaultSchema();
+
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
+ .ofNativeType("SERIAL").nullable(false).withColumn("name").ofType(ColumnType.VARCHAR)
+ .ofSize(10).withColumn("foo").ofType(ColumnType.BOOLEAN).nullable(true).withColumn("bar")
+ .ofType(ColumnType.BOOLEAN).nullable(true).execute();
+
+ assertEquals("my_table", table.getName());
+ }
+ });
+
+ try {
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback callback) {
+ callback.insertInto("my_table").value("name", "row 1").value("foo", true).execute();
+
+ callback.insertInto("my_table").value("name", "row 2").value("bar", true).execute();
+
+ callback.insertInto("my_table").value("name", "row 3").value("foo", true).execute();
+
+ callback.insertInto("my_table").value("name", "row 4").value("foo", true).execute();
+
+ callback.insertInto("my_table").value("name", "row 5").value("bar", true).execute();
+
+ callback.insertInto("my_table").value("name", "row 6").value("foo", true).value("bar", true)
+ .execute();
+
+ callback.insertInto("my_table").value("name", "row 7").value("foo", true).value("bar", true)
+ .execute();
+
+ callback.insertInto("my_table").value("name", "row 8").value("foo", false).value("bar", false)
+ .execute();
+ }
+ });
+
+ DataSet ds = dc.query().from("my_table").select("id").and("name").execute();
+ assertTrue(ds.next());
+ assertEquals("Row[values=[1, row 1]]", ds.getRow().toString());
+ assertTrue(ds.next());
+ assertEquals("Row[values=[2, row 2]]", ds.getRow().toString());
+ assertTrue(ds.next());
+ assertEquals("Row[values=[3, row 3]]", ds.getRow().toString());
+ assertTrue(ds.next());
+ assertEquals("Row[values=[4, row 4]]", ds.getRow().toString());
+ assertTrue(ds.next());
+ assertEquals("Row[values=[5, row 5]]", ds.getRow().toString());
+ assertTrue(ds.next());
+ assertEquals("Row[values=[6, row 6]]", ds.getRow().toString());
+ assertTrue(ds.next());
+ assertEquals("Row[values=[7, row 7]]", ds.getRow().toString());
+ assertTrue(ds.next());
+ assertEquals("Row[values=[8, row 8]]", ds.getRow().toString());
+ assertFalse(ds.next());
+ ds.close();
+ } finally {
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback callback) {
+ callback.dropTable("my_table").execute();
+ }
+ });
+ }
+ }
+
+ /**
+ * Tests some inconsistencies dealing with booleans.
+ *
+ * @see http://eobjects.org/trac/ticket/829
+ */
+ public void testBoolean() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ JdbcDataContext dc = new JdbcDataContext(getConnection());
+
+ final Schema schema = dc.getDefaultSchema();
+
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
+ .ofNativeType("SERIAL").nullable(false).withColumn("some_bool").ofType(ColumnType.BOOLEAN)
+ .nullable(false).execute();
+ assertEquals("my_table", table.getName());
+
+ cb.insertInto(table).value("id", 1).value("some_bool", true).execute();
+ cb.insertInto(table).value("id", 2).value("some_bool", false).execute();
+ }
+ });
+
+ DataSet ds = dc.query().from("my_table").select("some_bool").execute();
+
+ assertTrue(ds.next());
+ assertEquals("Row[values=[true]]", ds.getRow().toString());
+ assertTrue(ds.next());
+ assertEquals("Row[values=[false]]", ds.getRow().toString());
+ assertFalse(ds.next());
+
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ cb.dropTable("my_table").execute();
+ }
+ });
+ }
+
+ public void testBlob() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ JdbcDataContext dc = new JdbcDataContext(getConnection());
+ final Schema schema = dc.getDefaultSchema();
+
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
+ .ofNativeType("SERIAL").nullable(false).withColumn("some_bytes").ofType(ColumnType.BLOB)
+ .execute();
+ assertEquals("my_table", table.getName());
+ }
+ });
+
+ try {
+ dc.refreshSchemas();
+ final Column column = dc.getColumnByQualifiedLabel("my_table.some_bytes");
+ assertEquals("Column[name=some_bytes,columnNumber=1,type=BINARY,nullable=true,"
+ + "nativeType=bytea,columnSize=2147483647]", column.toString());
+
+ final Table table = column.getTable();
+
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback callback) {
+ callback.insertInto(table).value(column, new byte[] { 1, 2, 3 }).execute();
+ callback.insertInto(table).value(column, "hello world".getBytes()).execute();
+ }
+ });
+
+ byte[] bytes;
+
+ DataSet ds = dc.query().from(table).select(table.getColumns()).execute();
+
+ assertTrue(ds.next());
+ assertEquals(1, ds.getRow().getValue(0));
+ bytes = (byte[]) ds.getRow().getValue(1);
+ assertEquals(3, bytes.length);
+ assertEquals(1, bytes[0]);
+ assertEquals(2, bytes[1]);
+ assertEquals(3, bytes[2]);
+
+ assertTrue(ds.next());
+ assertEquals(2, ds.getRow().getValue(0));
+ bytes = (byte[]) ds.getRow().getValue(1);
+
+ assertEquals("hello world", new String(bytes));
+ assertFalse(ds.next());
+
+ } finally {
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ cb.dropTable("my_table").execute();
+ }
+ });
+ }
+ }
+
+ public void testCreateTableAndWriteRecords() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ JdbcDataContext dc = new JdbcDataContext(getConnection());
+ final Schema schema = dc.getDefaultSchema();
+ try {
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
+ .ofNativeType("SERIAL").nullable(false).withColumn("person name").ofSize(255)
+ .withColumn("age").ofType(ColumnType.INTEGER).execute();
+ assertEquals("[id, person name, age]", Arrays.toString(table.getColumnNames()));
+ assertEquals(
+ "Column[name=id,columnNumber=0,type=INTEGER,nullable=false,nativeType=serial,columnSize=10]",
+ table.getColumnByName("id").toString());
+ assertEquals(
+ "Column[name=person name,columnNumber=1,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=255]",
+ table.getColumnByName("person name").toString());
+ assertEquals(
+ "Column[name=age,columnNumber=2,type=INTEGER,nullable=true,nativeType=int4,columnSize=10]",
+ table.getColumnByName("age").toString());
+
+ cb.insertInto(table).value("person name", "John Doe").value("age", 42).execute();
+ cb.insertInto(table).value("age", 43).value("person name", "Jane Doe").execute();
+
+ }
+ });
+
+ final Table table = schema.getTableByName("my_table");
+ Query query = dc.query().from(table).select(table.getColumns()).toQuery();
+ DataSet ds = dc.executeQuery(query);
+ assertTrue(ds.next());
+ assertEquals("Row[values=[1, John Doe, 42]]", ds.getRow().toString());
+ assertTrue(ds.next());
+ assertEquals("Row[values=[2, Jane Doe, 43]]", ds.getRow().toString());
+ assertFalse(ds.next());
+ ds.close();
+
+ dc.executeUpdate(new UpdateScript() {
+
+ @Override
+ public void run(UpdateCallback callback) {
+ callback.update(table).value("age", 102).where("id").eq(1).execute();
+ callback.deleteFrom(table).where("id").eq(2).execute();
+ }
+ });
+
+ ds = dc.executeQuery(query);
+ assertTrue(ds.next());
+ assertEquals("Row[values=[1, John Doe, 102]]", ds.getRow().toString());
+ assertFalse(ds.next());
+ ds.close();
+ } finally {
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback callback) {
+ callback.dropTable("my_table").execute();
+ }
+ });
+ assertNull(dc.getTableByQualifiedLabel("my_table"));
+ }
+ }
+
+ public void testCreateTableInsertValueFloatForIntColumn() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ JdbcDataContext dc = new JdbcDataContext(getConnection());
+ final Schema schema = dc.getDefaultSchema();
+ try {
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
+ .ofNativeType("SERIAL").nullable(false).withColumn("person name").ofSize(255)
+ .withColumn("age").ofType(ColumnType.INTEGER).execute();
+ assertEquals("[id, person name, age]", Arrays.toString(table.getColumnNames()));
+ assertEquals(
+ "Column[name=id,columnNumber=0,type=INTEGER,nullable=false,nativeType=serial,columnSize=10]",
+ table.getColumnByName("id").toString());
+ assertEquals(
+ "Column[name=person name,columnNumber=1,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=255]",
+ table.getColumnByName("person name").toString());
+ assertEquals(
+ "Column[name=age,columnNumber=2,type=INTEGER,nullable=true,nativeType=int4,columnSize=10]",
+ table.getColumnByName("age").toString());
+
+ cb.insertInto(table).value("person name", "John Doe").value("age", 42.4673).execute();
+ cb.insertInto(table).value("age", 43.5673).value("person name", "Jane Doe").execute();
+ }
+ });
+
+ Table table = schema.getTableByName("my_table");
+ Query query = dc.query().from(table).select(table.getColumns()).toQuery();
+ DataSet ds = dc.executeQuery(query);
+ assertTrue(ds.next());
+ // Float value input will be rounded down into integer number.
+ assertEquals("Row[values=[1, John Doe, 42]]", ds.getRow().toString());
+ assertTrue(ds.next());
+ // The age will be incremented as float value input will be rounded
+ // up.
+ assertEquals("Row[values=[2, Jane Doe, 44]]", ds.getRow().toString());
+ assertFalse(ds.next());
+
+ ds.close();
+ } finally {
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ cb.dropTable("my_table").execute();
+ }
+ });
+ }
+ }
+
+ public void testInsertFailureForStringValueForIntegerColumn() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ JdbcDataContext dc = new JdbcDataContext(getConnection());
+ final Schema schema = dc.getDefaultSchema();
+ try {
+ dc.executeUpdate(new BatchUpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
+ .ofNativeType("SERIAL").nullable(false).withColumn("person name").ofSize(255)
+ .withColumn("age").ofType(ColumnType.INTEGER).execute();
+ assertEquals("[id, person name, age]", Arrays.toString(table.getColumnNames()));
+ assertEquals(
+ "Column[name=id,columnNumber=0,type=INTEGER,nullable=false,nativeType=serial,columnSize=10]",
+ table.getColumnByName("id").toString());
+ assertEquals(
+ "Column[name=person name,columnNumber=1,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=255]",
+ table.getColumnByName("person name").toString());
+ assertEquals(
+ "Column[name=age,columnNumber=2,type=INTEGER,nullable=true,nativeType=int4,columnSize=10]",
+ table.getColumnByName("age").toString());
+
+ cb.insertInto(table).value("person name", "John Doe").value("age", "42").execute();
+ }
+ });
+
+ } catch (Exception e) {
+ String message = e.getMessage().replaceAll("\n", " ");
+ assertEquals(
+ "Could not execute batch: INSERT INTO \"public\".\"my_table\" (\"person name\",age) VALUES ('John Doe','42'): Batch entry 0 INSERT INTO \"public\".\"my_table\" (\"person name\",age) VALUES ('John Doe','42') was aborted. Call getNextException to see the cause.",
+ message);
+ } finally {
+ dc.refreshSchemas();
+ if (dc.getTableByQualifiedLabel("my_table") != null) {
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ cb.dropTable("my_table").execute();
+ }
+ });
+ }
+ }
+ }
+
+ public void testDatabaseProductName() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ String databaseProductName = getConnection().getMetaData().getDatabaseProductName();
+ assertEquals(JdbcDataContext.DATABASE_PRODUCT_POSTGRESQL, databaseProductName);
+ }
+
+ public void testGetDefaultSchema() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ DataContext dc = new JdbcDataContext(getConnection());
+ Schema schema = dc.getDefaultSchema();
+ assertEquals("public", schema.getName());
+ }
+
+ public void testGetSchema() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ DataContext dc = new JdbcDataContext(getConnection());
+ Schema[] schemas = dc.getSchemas();
+ assertTrue(schemas.length >= 3);
+
+ assertNotNull(dc.getSchemaByName("information_schema"));
+ assertNotNull(dc.getSchemaByName("pg_catalog"));
+ assertNotNull(dc.getSchemaByName("public"));
+
+ Schema schema = dc.getSchemaByName("public");
+
+ assertEquals("[Table[name=categories,type=TABLE,remarks=null], "
+ + "Table[name=cust_hist,type=TABLE,remarks=null], " + "Table[name=customers,type=TABLE,remarks=null], "
+ + "Table[name=inventory,type=TABLE,remarks=null], "
+ + "Table[name=orderlines,type=TABLE,remarks=null], " + "Table[name=orders,type=TABLE,remarks=null], "
+ + "Table[name=products,type=TABLE,remarks=null], " + "Table[name=reorder,type=TABLE,remarks=null]]",
+ Arrays.toString(schema.getTables()));
+
+ Table productsTable = schema.getTableByName("products");
+ assertEquals(
+ "[Column[name=prod_id,columnNumber=0,type=INTEGER,nullable=false,nativeType=serial,columnSize=10], "
+ + "Column[name=category,columnNumber=1,type=INTEGER,nullable=false,nativeType=int4,columnSize=10], "
+ + "Column[name=title,columnNumber=2,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
+ + "Column[name=actor,columnNumber=3,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
+ + "Column[name=price,columnNumber=4,type=NUMERIC,nullable=false,nativeType=numeric,columnSize=12], "
+ + "Column[name=special,columnNumber=5,type=SMALLINT,nullable=true,nativeType=int2,columnSize=5], "
+ + "Column[name=common_prod_id,columnNumber=6,type=INTEGER,nullable=false,nativeType=int4,columnSize=10]]",
+ Arrays.toString(productsTable.getColumns()));
+ Table customersTable = schema.getTableByName("customers");
+ assertEquals(
+ "[Column[name=customerid,columnNumber=0,type=INTEGER,nullable=false,nativeType=serial,columnSize=10], "
+ + "Column[name=firstname,columnNumber=1,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
+ + "Column[name=lastname,columnNumber=2,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
+ + "Column[name=address1,columnNumber=3,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
+ + "Column[name=address2,columnNumber=4,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=50], "
+ + "Column[name=city,columnNumber=5,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
+ + "Column[name=state,columnNumber=6,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=50], "
+ + "Column[name=zip,columnNumber=7,type=INTEGER,nullable=true,nativeType=int4,columnSize=10], "
+ + "Column[name=country,columnNumber=8,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
+ + "Column[name=region,columnNumber=9,type=SMALLINT,nullable=false,nativeType=int2,columnSize=5], "
+ + "Column[name=email,columnNumber=10,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=50], "
+ + "Column[name=phone,columnNumber=11,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=50], "
+ + "Column[name=creditcardtype,columnNumber=12,type=INTEGER,nullable=false,nativeType=int4,columnSize=10], "
+ + "Column[name=creditcard,columnNumber=13,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
+ + "Column[name=creditcardexpiration,columnNumber=14,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
+ + "Column[name=username,columnNumber=15,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
+ + "Column[name=password,columnNumber=16,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
+ + "Column[name=age,columnNumber=17,type=SMALLINT,nullable=true,nativeType=int2,columnSize=5], "
+ + "Column[name=income,columnNumber=18,type=INTEGER,nullable=true,nativeType=int4,columnSize=10], "
+ + "Column[name=gender,columnNumber=19,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=1]]",
+ Arrays.toString(customersTable.getColumns()));
+ Relationship[] relations = customersTable.getRelationships();
+ assertEquals(2, relations.length);
+ assertEquals(
+ "[Relationship[primaryTable=customers,primaryColumns=[customerid],foreignTable=cust_hist,foreignColumns=[customerid]], "
+ + "Relationship[primaryTable=customers,primaryColumns=[customerid],foreignTable=orders,foreignColumns=[customerid]]]",
+ Arrays.toString(relations));
+ assertEquals("Table[name=customers,type=TABLE,remarks=null]", relations[0].getPrimaryTable().toString());
+ assertEquals("Table[name=cust_hist,type=TABLE,remarks=null]", relations[0].getForeignTable().toString());
+ assertEquals("Table[name=customers,type=TABLE,remarks=null]", relations[1].getPrimaryTable().toString());
+ assertEquals("Table[name=orders,type=TABLE,remarks=null]", relations[1].getForeignTable().toString());
+
+ Table ordersTable = schema.getTableByName("orderlines");
+ assertEquals(
+ "[Column[name=orderlineid,columnNumber=0,type=INTEGER,nullable=false,nativeType=int4,columnSize=10], "
+ + "Column[name=orderid,columnNumber=1,type=INTEGER,nullable=false,nativeType=int4,columnSize=10], "
+ + "Column[name=prod_id,columnNumber=2,type=INTEGER,nullable=false,nativeType=int4,columnSize=10], "
+ + "Column[name=quantity,columnNumber=3,type=SMALLINT,nullable=false,nativeType=int2,columnSize=5], "
+ + "Column[name=orderdate,columnNumber=4,type=DATE,nullable=false,nativeType=date,columnSize=13]]",
+ Arrays.toString(ordersTable.getColumns()));
+ }
+
+ public void testExecuteQueryInPublicSchema() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ DataContext dc = new JdbcDataContext(getConnection());
+ Query q = new Query();
+ Schema schema = dc.getSchemaByName("public");
+ Table productsTable = schema.getTableByName("products");
+ q.from(productsTable);
+
+ Column titleColumn = productsTable.getColumnByName("title");
+ Column productPriceColumn = productsTable.getColumnByName("price");
+ q.select(titleColumn, productPriceColumn);
+ q.getSelectClause().getItem(0).setAlias("product-title");
+
+ DataSet data = dc.executeQuery(q);
+ TableModel tableModel = new DataSetTableModel(data);
+ assertEquals(2, tableModel.getColumnCount());
+ assertEquals(10000, tableModel.getRowCount());
+
+ assertEquals("ACADEMY ACADEMY", tableModel.getValueAt(0, 0).toString());
+ assertEquals("25.99", tableModel.getValueAt(0, 1).toString());
+
+ assertEquals("ACADEMY HORN", tableModel.getValueAt(432, 0).toString());
+ assertEquals("16.99", tableModel.getValueAt(6346, 1).toString());
+
+ assertEquals("ALADDIN ZORRO", tableModel.getValueAt(9999, 0).toString());
+ assertEquals("10.99", tableModel.getValueAt(9999, 1).toString());
+
+ data = null;
+ tableModel = null;
+
+ Column prodIdColumn = productsTable.getColumnByName("prod_id");
+ Table orderlinesTable = schema.getTableByName("orderlines");
+ Column commonProdIdColumn = orderlinesTable.getColumnByName("prod_id");
+ Column quantityColumn = orderlinesTable.getColumnByName("quantity");
+
+ q.from(orderlinesTable);
+ q.where(new FilterItem(new SelectItem(prodIdColumn), OperatorType.EQUALS_TO, new SelectItem(commonProdIdColumn)));
+ q.groupBy(titleColumn);
+ q.getSelectClause().removeItem(q.getSelectClause().getSelectItem(productPriceColumn));
+ SelectItem quantitySum = new SelectItem(FunctionType.SUM, quantityColumn).setAlias("orderAmount");
+ q.select(quantitySum);
+ q.having(new FilterItem(quantitySum, OperatorType.GREATER_THAN, 25));
+ q.orderBy(new OrderByItem(q.getSelectClause().getItem(0)));
+
+ assertEquals("SELECT \"products\".\"title\" AS product-title, SUM(\"orderlines\".\"quantity\") AS orderAmount "
+ + "FROM public.\"products\", public.\"orderlines\" "
+ + "WHERE \"products\".\"prod_id\" = \"orderlines\".\"prod_id\" " + "GROUP BY \"products\".\"title\" "
+ + "HAVING SUM(\"orderlines\".\"quantity\") > 25 " + "ORDER BY \"products\".\"title\" ASC", q.toString());
+ data = dc.executeQuery(q);
+ tableModel = new DataSetTableModel(data);
+ assertEquals(2, tableModel.getColumnCount());
+ assertEquals(136, tableModel.getRowCount());
+
+ assertEquals("ACADEMY ALABAMA", tableModel.getValueAt(0, 0).toString());
+ assertEquals("27", tableModel.getValueAt(0, 1).toString());
+
+ assertEquals("AIRPORT MOURNING", tableModel.getValueAt(99, 0).toString());
+ assertEquals("29", tableModel.getValueAt(99, 1).toString());
+
+ assertEquals("ALADDIN WORKER", tableModel.getValueAt(135, 0).toString());
+ assertEquals("27", tableModel.getValueAt(135, 1).toString());
+ }
+
+ public void testWhiteSpaceColumns() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ DatabaseMetaData metaData = getConnection().getMetaData();
+ assertEquals("\"", metaData.getIdentifierQuoteString());
+ }
+
+ public void testCreateTableAndInsert1MRecords() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ if (!"true".equalsIgnoreCase(getProperties().getProperty(PROPERTY_LONGRUNNINGTESTS))) {
+ return;
+ }
+
+ JdbcDataContext dc = new JdbcDataContext(getConnection());
+ final Schema schema = dc.getDefaultSchema();
+ try {
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
+ .ofNativeType("SERIAL").nullable(false).withColumn("person name").ofSize(255)
+ .withColumn("age").ofType(ColumnType.INTEGER).execute();
+ assertEquals("[id, person name, age]", Arrays.toString(table.getColumnNames()));
+ assertEquals(
+ "Column[name=id,columnNumber=0,type=INTEGER,nullable=false,nativeType=serial,columnSize=10]",
+ table.getColumnByName("id").toString());
+ assertEquals(
+ "Column[name=person name,columnNumber=1,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=255]",
+ table.getColumnByName("person name").toString());
+ assertEquals(
+ "Column[name=age,columnNumber=2,type=INTEGER,nullable=true,nativeType=int4,columnSize=10]",
+ table.getColumnByName("age").toString());
+
+ for (int i = 0; i < 1000000; i++) {
+ cb.insertInto(table).value("person name", "John Doe").value("age", i + 10).execute();
+ }
+
+ }
+ });
+
+ Table table = schema.getTableByName("my_table");
+ Query query = dc.query().from(table).selectCount().toQuery();
+ DataSet ds = dc.executeQuery(query);
+ assertTrue(ds.next());
+ assertEquals("Row[values=[1000000]]", ds.getRow().toString());
+ assertFalse(ds.next());
+ ds.close();
+ } finally {
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ cb.dropTable("my_table").execute();
+ }
+ });
+ }
+ }
+
+ public void testCharOfSizeOne() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ JdbcTestTemplates.meaningOfOneSizeChar(getConnection());
+ }
+
+ /**
+ * Splits a huge query into 146 pieces and executes them to test that the
+ * collective result are equal to the original one in size
+ */
+ @Ignore
+ public void testSplitHugeQueryExecute146() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ if (!"true".equalsIgnoreCase(getProperties().getProperty(PROPERTY_LONGRUNNINGTESTS))) {
+ return;
+ }
+
+ DataContext dc = new JdbcDataContext(getConnection());
+ Query q = new Query();
+ Schema schema = dc.getSchemaByName("public");
+ Table productsTable = schema.getTableByName("products");
+ Table customerTable = schema.getTableByName("customers");
+ q.from(productsTable, "p").from(customerTable, "c");
+
+ Column titleColumn = productsTable.getColumnByName("title");
+ Column priceColumn = productsTable.getColumnByName("price");
+ Column cityColumn = customerTable.getColumnByName("city");
+ Column ageColumn = customerTable.getColumnByName("age");
+ q.select(titleColumn, priceColumn, cityColumn);
+
+ q.where(new FilterItem(new SelectItem(priceColumn), OperatorType.GREATER_THAN, 27));
+ q.where(new FilterItem(new SelectItem(ageColumn), OperatorType.GREATER_THAN, 55));
+
+ assertEquals(
+ "SELECT p.\"title\", p.\"price\", c.\"city\" FROM public.\"products\" p, public.\"customers\" c WHERE p.\"price\" > 27 AND c.\"age\" > 55",
+ q.toString());
+
+ QuerySplitter qs = new QuerySplitter(dc, q);
+ qs.setMaxRows(100000);
+ assertEquals(14072278, qs.getRowCount());
+
+ List<Query> splitQueries = qs.splitQuery();
+ assertEquals(146, splitQueries.size());
+ assertEquals(
+ "SELECT p.\"title\", p.\"price\", c.\"city\" FROM public.\"products\" p, public.\"customers\" c WHERE p.\"price\" > 27 AND c.\"age\" > 55 AND (c.\"customerid\" < 143 OR c.\"customerid\" IS NULL) AND (p.\"category\" < 8 OR p.\"category\" IS NULL)",
+ splitQueries.get(0).toString());
+ assertEquals(
+ "SELECT p.\"title\", p.\"price\", c.\"city\" FROM public.\"products\" p, public.\"customers\" c WHERE p.\"price\" > 27 AND c.\"age\" > 55 AND (c.\"customerid\" > 19739 OR c.\"customerid\" = 19739)",
+ splitQueries.get(145).toString());
+
+ assertEquals(
+ "[45954, 55752, 52122, 55480, 49770, 53410, 60434, 51590, 97284, 94336, 86966, 76648, 98758, 84018, 98758, 95810, 92862, 91388, 39798, 79596, "
+ + "91388, 48642, 60434, 106128, 94336, 94336, 86966, 79596, 85492, 94336, 104654, 97284, 84018, 101706, 109076, 89914, 110550, 107602, 98758, "
+ + "112024, 100232, 101706, 95810, 92862, 107602, 100232, 86966, 98758, 106128, 91388, 107602, 104654, 107602, 81070, 114972, 79596, 100232, 97284, "
+ + "103180, 98758, 113498, 103180, 89914, 104654, 97284, 109076, 114972, 103180, 86966, 106128, 101706, 95810, 103180, 88440, 112024, 91388, 106128, "
+ + "82544, 122342, 98758, 104654, 103180, 104654, 89914, 106128, 88440, 103180, 100232, 98758, 100232, 89914, 101706, 100232, 107602, 88440, 89914, "
+ + "91388, 103180, 100232, 104654, 120868, 106128, 100232, 107602, 97284, 103180, 106128, 91388, 100232, 106128, 100232, 109076, 94336, 106128, 94336, "
+ + "106128, 104654, 116446, 98758, 113498, 107602, 104654, 107602, 88440, 100232, 92862, 89914, 110550, 109076, 100232, 92862, 100232, 104654, 103180, "
+ + "89914, 103180, 103180, 107602, 85492, 112024, 85492, 101706, 92862, 86966, 104654, 201938]",
+ Arrays.toString(getCounts(dc, splitQueries)));
+ assertSameCount(dc, qs, splitQueries);
+
+ DataSet data = qs.executeQueries(splitQueries);
+ int count = 0;
+ while (data.next()) {
+ count++;
+ }
+ data.close();
+ assertEquals(14072278, count);
+ System.out.println("Successfully iterated 14072278 rows! :)");
+ }
+
+ /**
+ * Utility method for asserting that a query and it's splitted queries have
+ * the same total count
+ */
+ private void assertSameCount(DataContext dc, QuerySplitter qs, List<Query> queries) {
+ long count1 = qs.getRowCount();
+ long count2 = 0;
+ for (Query q : queries) {
+ count2 += getCount(dc, q);
+ }
+ assertEquals(count1, count2);
+ }
+
+ public long[] getCounts(DataContext dc, List<Query> queries) {
+ long[] result = new long[queries.size()];
+ for (int i = 0; i < result.length; i++) {
+ result[i] = getCount(dc, queries.get(i));
+ }
+ return result;
+ }
+
+ /**
+ * Gets the count of a query
+ */
+ private long getCount(DataContext dc, Query query) {
+ return new QuerySplitter(dc, query).getRowCount();
+ }
+}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/b6979189/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/package-info.java
----------------------------------------------------------------------
diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/package-info.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/package-info.java
new file mode 100644
index 0000000..8c555f9
--- /dev/null
+++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/package-info.java
@@ -0,0 +1,22 @@
+/**
+ * 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.
+ */
+/**
+ * Integration tests with DBMSes that run as separate processes.
+ */
+package org.apache.metamodel.jdbc.integrationtests;
\ No newline at end of file
[2/4] git commit: METAMODEL-45: Made abstract JDBC integration test
class and implemented it for PostgreSQL integration test.
Posted by ka...@apache.org.
METAMODEL-45: Made abstract JDBC integration test class and implemented
it for PostgreSQL integration test.
Project: http://git-wip-us.apache.org/repos/asf/incubator-metamodel/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-metamodel/commit/b6979189
Tree: http://git-wip-us.apache.org/repos/asf/incubator-metamodel/tree/b6979189
Diff: http://git-wip-us.apache.org/repos/asf/incubator-metamodel/diff/b6979189
Branch: refs/heads/master
Commit: b69791893fd141f466bac3991570a2df4a99bb02
Parents: 2023997
Author: Kasper Sørensen <i....@gmail.com>
Authored: Sat May 31 13:32:34 2014 +0200
Committer: Kasper Sørensen <i....@gmail.com>
Committed: Sat May 31 13:32:34 2014 +0200
----------------------------------------------------------------------
...del-integrationtest-configuration.properties | 9 +
.../org/apache/metamodel/PostgresqlTest.java | 828 -----------------
.../AbstractJdbIntegrationTest.java | 121 +++
.../jdbc/integrationtests/PostgresqlTest.java | 904 +++++++++++++++++++
.../jdbc/integrationtests/package-info.java | 22 +
5 files changed, 1056 insertions(+), 828 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/b6979189/example-metamodel-integrationtest-configuration.properties
----------------------------------------------------------------------
diff --git a/example-metamodel-integrationtest-configuration.properties b/example-metamodel-integrationtest-configuration.properties
index 40d0346..0dc032e 100644
--- a/example-metamodel-integrationtest-configuration.properties
+++ b/example-metamodel-integrationtest-configuration.properties
@@ -29,6 +29,15 @@
#hbase.zookeeper.hostname=localhost
#hbase.zookeeper.port=2181
+# -----------------------
+# JDBC module properties:
+# -----------------------
+#jdbc.postgresql.driver=org.postgresql.Driver
+#jdbc.postgresql.url=jdbc:postgresql://localhost/dellstore2
+#jdbc.postgresql.username=metamodel
+#jdbc.postgresql.password=metamodel
+#jdbc.postgresql.longrunningtests=false
+
# -----------------------------
# Salesforce module properties:
# -----------------------------
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/b6979189/jdbc/src/test/integrationtests/org/apache/metamodel/PostgresqlTest.java
----------------------------------------------------------------------
diff --git a/jdbc/src/test/integrationtests/org/apache/metamodel/PostgresqlTest.java b/jdbc/src/test/integrationtests/org/apache/metamodel/PostgresqlTest.java
deleted file mode 100644
index 7e7625c..0000000
--- a/jdbc/src/test/integrationtests/org/apache/metamodel/PostgresqlTest.java
+++ /dev/null
@@ -1,828 +0,0 @@
-/**
- * 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;
-
-import java.lang.reflect.Method;
-import java.sql.Connection;
-import java.sql.DatabaseMetaData;
-import java.sql.DriverManager;
-import java.util.Arrays;
-import java.util.List;
-
-import javax.swing.table.TableModel;
-
-import junit.framework.TestCase;
-
-import org.apache.metamodel.data.DataSet;
-import org.apache.metamodel.data.DataSetTableModel;
-import org.apache.metamodel.insert.RowInsertionBuilder;
-import org.apache.metamodel.jdbc.JdbcDataContext;
-import org.apache.metamodel.jdbc.JdbcTestTemplates;
-import org.apache.metamodel.jdbc.QuerySplitter;
-import org.apache.metamodel.query.FilterItem;
-import org.apache.metamodel.query.FunctionType;
-import org.apache.metamodel.query.OperatorType;
-import org.apache.metamodel.query.OrderByItem;
-import org.apache.metamodel.query.Query;
-import org.apache.metamodel.query.SelectItem;
-import org.apache.metamodel.schema.Column;
-import org.apache.metamodel.schema.ColumnType;
-import org.apache.metamodel.schema.Relationship;
-import org.apache.metamodel.schema.Schema;
-import org.apache.metamodel.schema.Table;
-import org.junit.Ignore;
-
-/**
- * Test case that tests postgresql interaction. The test requires the
- * "dellstore2" sample database that can be found at pgfoundry.
- *
- * @see http://pgfoundry.org/projects/dbsamples/
- */
-public class PostgresqlTest extends TestCase {
-
- private static final String CONNECTION_STRING = "jdbc:postgresql://localhost/dellstore2";
- private static final String USERNAME = "eobjects";
- private static final String PASSWORD = "eobjects";
- private Connection _connection;
-
- @Override
- protected void setUp() throws Exception {
- super.setUp();
- Class.forName("org.postgresql.Driver");
- _connection = DriverManager.getConnection(CONNECTION_STRING, USERNAME, PASSWORD);
- }
-
- @Override
- protected void tearDown() throws Exception {
- super.tearDown();
- _connection.close();
- }
-
- public void testInterpretationOfNull() throws Exception {
- JdbcTestTemplates.interpretationOfNulls(_connection);
- }
-
- private JdbcDataContext createLimitAndOffsetTestData() {
- final JdbcDataContext dc = new JdbcDataContext(_connection);
-
- if (dc.getTableByQualifiedLabel("test_table") != null) {
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback callback) {
- callback.dropTable("test_table").execute();
- }
- });
- }
-
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback callback) {
- Table table = callback.createTable(dc.getDefaultSchema(), "test_table").withColumn("foo")
- .ofType(ColumnType.INTEGER).withColumn("bar").ofType(ColumnType.VARCHAR).execute();
- callback.insertInto(table).value("foo", 1).value("bar", "hello").execute();
- callback.insertInto(table).value("foo", 2).value("bar", "there").execute();
- callback.insertInto(table).value("foo", 3).value("bar", "world").execute();
- }
- });
-
- dc.refreshSchemas();
-
- return dc;
- }
-
- public void testLimit() throws Exception {
- JdbcDataContext dc = createLimitAndOffsetTestData();
- Schema schema = dc.getDefaultSchema();
- Table productsTable = schema.getTableByName("test_table");
-
- DataSet ds = dc.query().from(productsTable).select("foo").limit(2).execute();
- assertTrue(ds.next());
- assertEquals("Row[values=[1]]", ds.getRow().toString());
- assertTrue(ds.next());
- assertEquals("Row[values=[2]]", ds.getRow().toString());
- assertFalse(ds.next());
- ds.close();
- }
-
- public void testOffset() throws Exception {
- JdbcDataContext dc = createLimitAndOffsetTestData();
- Schema schema = dc.getDefaultSchema();
- Table productsTable = schema.getTableByName("test_table");
-
- DataSet ds = dc.query().from(productsTable).select("foo").offset(1).execute();
- assertTrue(ds.next());
- assertEquals("Row[values=[2]]", ds.getRow().toString());
- assertTrue(ds.next());
- assertEquals("Row[values=[3]]", ds.getRow().toString());
- assertFalse(ds.next());
- ds.close();
- }
-
- public void testLimitAndOffset() throws Exception {
- JdbcDataContext dc = createLimitAndOffsetTestData();
- Schema schema = dc.getDefaultSchema();
- Table productsTable = schema.getTableByName("test_table");
-
- DataSet ds = dc.query().from(productsTable).select("foo").limit(1).offset(1).execute();
- assertTrue(ds.next());
- assertEquals("Row[values=[2]]", ds.getRow().toString());
- assertFalse(ds.next());
-
- ds.close();
- }
-
- public void testQuotedInsertSyntax() throws Exception {
- try {
- _connection.createStatement().execute("DROP TABLE my_table");
- } catch (Exception e) {
- // do nothing
- }
-
- JdbcDataContext dc = new JdbcDataContext(_connection);
- final Schema schema = dc.getDefaultSchema();
-
- // create table
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- Table table = cb.createTable(schema, "my_table").withColumn("id").asPrimaryKey()
- .ofType(ColumnType.INTEGER).ofNativeType("SERIAL").nullable(false).withColumn("name")
- .ofType(ColumnType.VARCHAR).ofSize(10).withColumn("foo").ofType(ColumnType.BOOLEAN)
- .nullable(true).withColumn("bar").ofType(ColumnType.BOOLEAN).nullable(true).execute();
-
- assertEquals("my_table", table.getName());
- }
- });
-
- assertTrue(dc.getColumnByQualifiedLabel("my_table.id").isPrimaryKey());
- assertFalse(dc.getColumnByQualifiedLabel("my_table.name").isPrimaryKey());
-
- // insert records
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback callback) {
- RowInsertionBuilder builder = callback.insertInto("my_table").value("name", "row 1").value("foo", true);
-
- try {
- Method method = builder.getClass().getDeclaredMethod("createSqlStatement");
- method.setAccessible(true);
- Object result = method.invoke(builder);
- assertEquals("INSERT INTO \"public\".\"my_table\" (name,foo) VALUES (?,?)", result.toString());
- } catch (Exception e) {
- throw new RuntimeException(e);
- }
-
- builder.execute();
-
- callback.insertInto("my_table").value("name", "row 2").value("foo", false).execute();
- }
- });
-
- // query
- DataSet ds = dc.query().from("my_table").select("name").where("foo").eq(true).execute();
- assertTrue(ds.next());
- assertEquals("Row[values=[row 1]]", ds.getRow().toString());
- assertFalse(ds.next());
- ds.close();
-
- // drop
- dc.executeUpdate(new UpdateScript() {
-
- @Override
- public void run(UpdateCallback callback) {
- callback.dropTable("my_table").execute();
- }
- });
- }
-
- public void testInsertOfDifferentTypes() throws Exception {
- try {
- _connection.createStatement().execute("DROP TABLE my_table");
- } catch (Exception e) {
- // do nothing
- }
-
- JdbcDataContext dc = new JdbcDataContext(_connection);
- final Schema schema = dc.getDefaultSchema();
-
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
- .ofNativeType("SERIAL").nullable(false).withColumn("name").ofType(ColumnType.VARCHAR)
- .ofSize(10).withColumn("foo").ofType(ColumnType.BOOLEAN).nullable(true).withColumn("bar")
- .ofType(ColumnType.BOOLEAN).nullable(true).execute();
-
- assertEquals("my_table", table.getName());
- }
- });
-
- try {
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback callback) {
- callback.insertInto("my_table").value("name", "row 1").value("foo", true).execute();
-
- callback.insertInto("my_table").value("name", "row 2").value("bar", true).execute();
-
- callback.insertInto("my_table").value("name", "row 3").value("foo", true).execute();
-
- callback.insertInto("my_table").value("name", "row 4").value("foo", true).execute();
-
- callback.insertInto("my_table").value("name", "row 5").value("bar", true).execute();
-
- callback.insertInto("my_table").value("name", "row 6").value("foo", true).value("bar", true)
- .execute();
-
- callback.insertInto("my_table").value("name", "row 7").value("foo", true).value("bar", true)
- .execute();
-
- callback.insertInto("my_table").value("name", "row 8").value("foo", false).value("bar", false)
- .execute();
- }
- });
-
- DataSet ds = dc.query().from("my_table").select("id").and("name").execute();
- assertTrue(ds.next());
- assertEquals("Row[values=[1, row 1]]", ds.getRow().toString());
- assertTrue(ds.next());
- assertEquals("Row[values=[2, row 2]]", ds.getRow().toString());
- assertTrue(ds.next());
- assertEquals("Row[values=[3, row 3]]", ds.getRow().toString());
- assertTrue(ds.next());
- assertEquals("Row[values=[4, row 4]]", ds.getRow().toString());
- assertTrue(ds.next());
- assertEquals("Row[values=[5, row 5]]", ds.getRow().toString());
- assertTrue(ds.next());
- assertEquals("Row[values=[6, row 6]]", ds.getRow().toString());
- assertTrue(ds.next());
- assertEquals("Row[values=[7, row 7]]", ds.getRow().toString());
- assertTrue(ds.next());
- assertEquals("Row[values=[8, row 8]]", ds.getRow().toString());
- assertFalse(ds.next());
- ds.close();
- } finally {
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback callback) {
- callback.dropTable("my_table").execute();
- }
- });
- }
- }
-
- /**
- * Tests some inconsistencies dealing with booleans.
- *
- * @see http://eobjects.org/trac/ticket/829
- */
- public void testBoolean() throws Exception {
- JdbcDataContext dc = new JdbcDataContext(_connection);
-
- final Schema schema = dc.getDefaultSchema();
-
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
- .ofNativeType("SERIAL").nullable(false).withColumn("some_bool").ofType(ColumnType.BOOLEAN)
- .nullable(false).execute();
- assertEquals("my_table", table.getName());
-
- cb.insertInto(table).value("id", 1).value("some_bool", true).execute();
- cb.insertInto(table).value("id", 2).value("some_bool", false).execute();
- }
- });
-
- DataSet ds = dc.query().from("my_table").select("some_bool").execute();
-
- assertTrue(ds.next());
- assertEquals("Row[values=[true]]", ds.getRow().toString());
- assertTrue(ds.next());
- assertEquals("Row[values=[false]]", ds.getRow().toString());
- assertFalse(ds.next());
-
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- cb.dropTable("my_table").execute();
- }
- });
- }
-
- public void testBlob() throws Exception {
- JdbcDataContext dc = new JdbcDataContext(_connection);
- final Schema schema = dc.getDefaultSchema();
-
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
- .ofNativeType("SERIAL").nullable(false).withColumn("some_bytes").ofType(ColumnType.BLOB)
- .execute();
- assertEquals("my_table", table.getName());
- }
- });
-
- try {
- dc.refreshSchemas();
- final Column column = dc.getColumnByQualifiedLabel("my_table.some_bytes");
- assertEquals("Column[name=some_bytes,columnNumber=1,type=BINARY,nullable=true,"
- + "nativeType=bytea,columnSize=2147483647]", column.toString());
-
- final Table table = column.getTable();
-
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback callback) {
- callback.insertInto(table).value(column, new byte[] { 1, 2, 3 }).execute();
- callback.insertInto(table).value(column, "hello world".getBytes()).execute();
- }
- });
-
- byte[] bytes;
-
- DataSet ds = dc.query().from(table).select(table.getColumns()).execute();
-
- assertTrue(ds.next());
- assertEquals(1, ds.getRow().getValue(0));
- bytes = (byte[]) ds.getRow().getValue(1);
- assertEquals(3, bytes.length);
- assertEquals(1, bytes[0]);
- assertEquals(2, bytes[1]);
- assertEquals(3, bytes[2]);
-
- assertTrue(ds.next());
- assertEquals(2, ds.getRow().getValue(0));
- bytes = (byte[]) ds.getRow().getValue(1);
-
- assertEquals("hello world", new String(bytes));
- assertFalse(ds.next());
-
- } finally {
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- cb.dropTable("my_table").execute();
- }
- });
- }
- }
-
- public void testCreateTableAndWriteRecords() throws Exception {
- JdbcDataContext dc = new JdbcDataContext(_connection);
- final Schema schema = dc.getDefaultSchema();
- try {
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
- .ofNativeType("SERIAL").nullable(false).withColumn("person name").ofSize(255)
- .withColumn("age").ofType(ColumnType.INTEGER).execute();
- assertEquals("[id, person name, age]", Arrays.toString(table.getColumnNames()));
- assertEquals(
- "Column[name=id,columnNumber=0,type=INTEGER,nullable=false,nativeType=serial,columnSize=10]",
- table.getColumnByName("id").toString());
- assertEquals(
- "Column[name=person name,columnNumber=1,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=255]",
- table.getColumnByName("person name").toString());
- assertEquals(
- "Column[name=age,columnNumber=2,type=INTEGER,nullable=true,nativeType=int4,columnSize=10]",
- table.getColumnByName("age").toString());
-
- cb.insertInto(table).value("person name", "John Doe").value("age", 42).execute();
- cb.insertInto(table).value("age", 43).value("person name", "Jane Doe").execute();
-
- }
- });
-
- final Table table = schema.getTableByName("my_table");
- Query query = dc.query().from(table).select(table.getColumns()).toQuery();
- DataSet ds = dc.executeQuery(query);
- assertTrue(ds.next());
- assertEquals("Row[values=[1, John Doe, 42]]", ds.getRow().toString());
- assertTrue(ds.next());
- assertEquals("Row[values=[2, Jane Doe, 43]]", ds.getRow().toString());
- assertFalse(ds.next());
- ds.close();
-
- dc.executeUpdate(new UpdateScript() {
-
- @Override
- public void run(UpdateCallback callback) {
- callback.update(table).value("age", 102).where("id").eq(1).execute();
- callback.deleteFrom(table).where("id").eq(2).execute();
- }
- });
-
- ds = dc.executeQuery(query);
- assertTrue(ds.next());
- assertEquals("Row[values=[1, John Doe, 102]]", ds.getRow().toString());
- assertFalse(ds.next());
- ds.close();
- } finally {
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback callback) {
- callback.dropTable("my_table").execute();
- }
- });
- assertNull(dc.getTableByQualifiedLabel("my_table"));
- }
- }
-
- public void testCreateTableInsertValueFloatForIntColumn() throws Exception {
- JdbcDataContext dc = new JdbcDataContext(_connection);
- final Schema schema = dc.getDefaultSchema();
- try {
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
- .ofNativeType("SERIAL").nullable(false).withColumn("person name").ofSize(255)
- .withColumn("age").ofType(ColumnType.INTEGER).execute();
- assertEquals("[id, person name, age]", Arrays.toString(table.getColumnNames()));
- assertEquals(
- "Column[name=id,columnNumber=0,type=INTEGER,nullable=false,nativeType=serial,columnSize=10]",
- table.getColumnByName("id").toString());
- assertEquals(
- "Column[name=person name,columnNumber=1,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=255]",
- table.getColumnByName("person name").toString());
- assertEquals(
- "Column[name=age,columnNumber=2,type=INTEGER,nullable=true,nativeType=int4,columnSize=10]",
- table.getColumnByName("age").toString());
-
- cb.insertInto(table).value("person name", "John Doe").value("age", 42.4673).execute();
- cb.insertInto(table).value("age", 43.5673).value("person name", "Jane Doe").execute();
- }
- });
-
- Table table = schema.getTableByName("my_table");
- Query query = dc.query().from(table).select(table.getColumns()).toQuery();
- DataSet ds = dc.executeQuery(query);
- assertTrue(ds.next());
- // Float value input will be rounded down into integer number.
- assertEquals("Row[values=[1, John Doe, 42]]", ds.getRow().toString());
- assertTrue(ds.next());
- // The age will be incremented as float value input will be rounded
- // up.
- assertEquals("Row[values=[2, Jane Doe, 44]]", ds.getRow().toString());
- assertFalse(ds.next());
-
- ds.close();
- } finally {
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- cb.dropTable("my_table").execute();
- }
- });
- }
- }
-
- public void testInsertFailureForStringValueForIntegerColumn() throws Exception {
- JdbcDataContext dc = new JdbcDataContext(_connection);
- final Schema schema = dc.getDefaultSchema();
- try {
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
- .ofNativeType("SERIAL").nullable(false).withColumn("person name").ofSize(255)
- .withColumn("age").ofType(ColumnType.INTEGER).execute();
- assertEquals("[id, person name, age]", Arrays.toString(table.getColumnNames()));
- assertEquals(
- "Column[name=id,columnNumber=0,type=INTEGER,nullable=false,nativeType=serial,columnSize=10]",
- table.getColumnByName("id").toString());
- assertEquals(
- "Column[name=person name,columnNumber=1,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=255]",
- table.getColumnByName("person name").toString());
- assertEquals(
- "Column[name=age,columnNumber=2,type=INTEGER,nullable=true,nativeType=int4,columnSize=10]",
- table.getColumnByName("age").toString());
-
- cb.insertInto(table).value("person name", "John Doe").value("age", "42").execute();
- }
- });
-
- } catch (Exception e) {
- assertEquals(
- "Could not execute batch: INSERT INTO \"public\".\"my_table\" (\"person name\",age) VALUES ('John Doe','42'): Batch entry 0 INSERT INTO \"public\".\"my_table\" (\"person name\",age) VALUES ('John Doe','42') was aborted. Call getNextException to see the cause.",
- e.getMessage());
- } finally {
- dc.refreshSchemas();
- if (dc.getTableByQualifiedLabel("my_table") != null) {
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- cb.dropTable("my_table").execute();
- }
- });
- }
- }
- }
-
- public void testDatabaseProductName() throws Exception {
- String databaseProductName = _connection.getMetaData().getDatabaseProductName();
- assertEquals(JdbcDataContext.DATABASE_PRODUCT_POSTGRESQL, databaseProductName);
- }
-
- public void testGetDefaultSchema() throws Exception {
- DataContext dc = new JdbcDataContext(_connection);
- Schema schema = dc.getDefaultSchema();
- assertEquals("public", schema.getName());
- }
-
- public void testGetSchema() throws Exception {
- DataContext dc = new JdbcDataContext(_connection);
- Schema[] schemas = dc.getSchemas();
- assertTrue(schemas.length >= 3);
-
- assertNotNull(dc.getSchemaByName("information_schema"));
- assertNotNull(dc.getSchemaByName("pg_catalog"));
- assertNotNull(dc.getSchemaByName("public"));
-
- Schema schema = dc.getSchemaByName("public");
-
- assertEquals("[Table[name=categories,type=TABLE,remarks=null], "
- + "Table[name=cust_hist,type=TABLE,remarks=null], " + "Table[name=customers,type=TABLE,remarks=null], "
- + "Table[name=inventory,type=TABLE,remarks=null], "
- + "Table[name=orderlines,type=TABLE,remarks=null], " + "Table[name=orders,type=TABLE,remarks=null], "
- + "Table[name=products,type=TABLE,remarks=null], " + "Table[name=reorder,type=TABLE,remarks=null]]",
- Arrays.toString(schema.getTables()));
-
- Table productsTable = schema.getTableByName("products");
- assertEquals(
- "[Column[name=prod_id,columnNumber=0,type=INTEGER,nullable=false,nativeType=serial,columnSize=10], "
- + "Column[name=category,columnNumber=1,type=INTEGER,nullable=false,nativeType=int4,columnSize=10], "
- + "Column[name=title,columnNumber=2,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
- + "Column[name=actor,columnNumber=3,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
- + "Column[name=price,columnNumber=4,type=NUMERIC,nullable=false,nativeType=numeric,columnSize=12], "
- + "Column[name=special,columnNumber=5,type=SMALLINT,nullable=true,nativeType=int2,columnSize=5], "
- + "Column[name=common_prod_id,columnNumber=6,type=INTEGER,nullable=false,nativeType=int4,columnSize=10]]",
- Arrays.toString(productsTable.getColumns()));
- Table customersTable = schema.getTableByName("customers");
- assertEquals(
- "[Column[name=customerid,columnNumber=0,type=INTEGER,nullable=false,nativeType=serial,columnSize=10], "
- + "Column[name=firstname,columnNumber=1,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
- + "Column[name=lastname,columnNumber=2,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
- + "Column[name=address1,columnNumber=3,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
- + "Column[name=address2,columnNumber=4,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=50], "
- + "Column[name=city,columnNumber=5,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
- + "Column[name=state,columnNumber=6,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=50], "
- + "Column[name=zip,columnNumber=7,type=INTEGER,nullable=true,nativeType=int4,columnSize=10], "
- + "Column[name=country,columnNumber=8,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
- + "Column[name=region,columnNumber=9,type=SMALLINT,nullable=false,nativeType=int2,columnSize=5], "
- + "Column[name=email,columnNumber=10,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=50], "
- + "Column[name=phone,columnNumber=11,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=50], "
- + "Column[name=creditcardtype,columnNumber=12,type=INTEGER,nullable=false,nativeType=int4,columnSize=10], "
- + "Column[name=creditcard,columnNumber=13,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
- + "Column[name=creditcardexpiration,columnNumber=14,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
- + "Column[name=username,columnNumber=15,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
- + "Column[name=password,columnNumber=16,type=VARCHAR,nullable=false,nativeType=varchar,columnSize=50], "
- + "Column[name=age,columnNumber=17,type=SMALLINT,nullable=true,nativeType=int2,columnSize=5], "
- + "Column[name=income,columnNumber=18,type=INTEGER,nullable=true,nativeType=int4,columnSize=10], "
- + "Column[name=gender,columnNumber=19,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=1]]",
- Arrays.toString(customersTable.getColumns()));
- Relationship[] relations = customersTable.getRelationships();
- assertEquals(2, relations.length);
- assertEquals(
- "[Relationship[primaryTable=customers,primaryColumns=[customerid],foreignTable=cust_hist,foreignColumns=[customerid]], "
- + "Relationship[primaryTable=customers,primaryColumns=[customerid],foreignTable=orders,foreignColumns=[customerid]]]",
- Arrays.toString(relations));
- assertEquals("Table[name=customers,type=TABLE,remarks=null]", relations[0].getPrimaryTable().toString());
- assertEquals("Table[name=cust_hist,type=TABLE,remarks=null]", relations[0].getForeignTable().toString());
- assertEquals("Table[name=customers,type=TABLE,remarks=null]", relations[1].getPrimaryTable().toString());
- assertEquals("Table[name=orders,type=TABLE,remarks=null]", relations[1].getForeignTable().toString());
-
- Table ordersTable = schema.getTableByName("orderlines");
- assertEquals(
- "[Column[name=orderlineid,columnNumber=0,type=INTEGER,nullable=false,nativeType=int4,columnSize=10], "
- + "Column[name=orderid,columnNumber=1,type=INTEGER,nullable=false,nativeType=int4,columnSize=10], "
- + "Column[name=prod_id,columnNumber=2,type=INTEGER,nullable=false,nativeType=int4,columnSize=10], "
- + "Column[name=quantity,columnNumber=3,type=SMALLINT,nullable=false,nativeType=int2,columnSize=5], "
- + "Column[name=orderdate,columnNumber=4,type=DATE,nullable=false,nativeType=date,columnSize=13]]",
- Arrays.toString(ordersTable.getColumns()));
- }
-
- public void testExecuteQueryInPublicSchema() throws Exception {
- DataContext dc = new JdbcDataContext(_connection);
- Query q = new Query();
- Schema schema = dc.getSchemaByName("public");
- Table productsTable = schema.getTableByName("products");
- q.from(productsTable);
-
- Column titleColumn = productsTable.getColumnByName("title");
- Column productPriceColumn = productsTable.getColumnByName("price");
- q.select(titleColumn, productPriceColumn);
- q.getSelectClause().getItem(0).setAlias("product-title");
-
- DataSet data = dc.executeQuery(q);
- TableModel tableModel = new DataSetTableModel(data);
- assertEquals(2, tableModel.getColumnCount());
- assertEquals(10000, tableModel.getRowCount());
-
- assertEquals("ACADEMY ACADEMY", tableModel.getValueAt(0, 0).toString());
- assertEquals("25.99", tableModel.getValueAt(0, 1).toString());
-
- assertEquals("ACADEMY HORN", tableModel.getValueAt(432, 0).toString());
- assertEquals("16.99", tableModel.getValueAt(6346, 1).toString());
-
- assertEquals("ALADDIN ZORRO", tableModel.getValueAt(9999, 0).toString());
- assertEquals("10.99", tableModel.getValueAt(9999, 1).toString());
-
- data = null;
- tableModel = null;
-
- Column prodIdColumn = productsTable.getColumnByName("prod_id");
- Table orderlinesTable = schema.getTableByName("orderlines");
- Column commonProdIdColumn = orderlinesTable.getColumnByName("prod_id");
- Column quantityColumn = orderlinesTable.getColumnByName("quantity");
-
- q.from(orderlinesTable);
- q.where(new FilterItem(new SelectItem(prodIdColumn), OperatorType.EQUALS_TO, new SelectItem(commonProdIdColumn)));
- q.groupBy(titleColumn);
- q.getSelectClause().removeItem(q.getSelectClause().getSelectItem(productPriceColumn));
- SelectItem quantitySum = new SelectItem(FunctionType.SUM, quantityColumn).setAlias("orderAmount");
- q.select(quantitySum);
- q.having(new FilterItem(quantitySum, OperatorType.GREATER_THAN, 25));
- q.orderBy(new OrderByItem(q.getSelectClause().getItem(0)));
-
- assertEquals("SELECT \"products\".\"title\" AS product-title, SUM(\"orderlines\".\"quantity\") AS orderAmount "
- + "FROM public.\"products\", public.\"orderlines\" "
- + "WHERE \"products\".\"prod_id\" = \"orderlines\".\"prod_id\" " + "GROUP BY \"products\".\"title\" "
- + "HAVING SUM(\"orderlines\".\"quantity\") > 25 " + "ORDER BY \"products\".\"title\" ASC", q.toString());
- data = dc.executeQuery(q);
- tableModel = new DataSetTableModel(data);
- assertEquals(2, tableModel.getColumnCount());
- assertEquals(136, tableModel.getRowCount());
-
- assertEquals("ACADEMY ALABAMA", tableModel.getValueAt(0, 0).toString());
- assertEquals("27", tableModel.getValueAt(0, 1).toString());
-
- assertEquals("AIRPORT MOURNING", tableModel.getValueAt(99, 0).toString());
- assertEquals("29", tableModel.getValueAt(99, 1).toString());
-
- assertEquals("ALADDIN WORKER", tableModel.getValueAt(135, 0).toString());
- assertEquals("27", tableModel.getValueAt(135, 1).toString());
- }
-
- public void testWhiteSpaceColumns() throws Exception {
- DatabaseMetaData metaData = _connection.getMetaData();
- assertEquals("\"", metaData.getIdentifierQuoteString());
- }
-
- public void testCreateTableAndInsert1MRecords() throws Exception {
- JdbcDataContext dc = new JdbcDataContext(_connection);
- final Schema schema = dc.getDefaultSchema();
- try {
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
- .ofNativeType("SERIAL").nullable(false).withColumn("person name").ofSize(255)
- .withColumn("age").ofType(ColumnType.INTEGER).execute();
- assertEquals("[id, person name, age]", Arrays.toString(table.getColumnNames()));
- assertEquals(
- "Column[name=id,columnNumber=0,type=INTEGER,nullable=false,nativeType=serial,columnSize=10]",
- table.getColumnByName("id").toString());
- assertEquals(
- "Column[name=person name,columnNumber=1,type=VARCHAR,nullable=true,nativeType=varchar,columnSize=255]",
- table.getColumnByName("person name").toString());
- assertEquals(
- "Column[name=age,columnNumber=2,type=INTEGER,nullable=true,nativeType=int4,columnSize=10]",
- table.getColumnByName("age").toString());
-
- for (int i = 0; i < 1000000; i++) {
- cb.insertInto(table).value("person name", "John Doe").value("age", i + 10).execute();
- }
-
- }
- });
-
- Table table = schema.getTableByName("my_table");
- Query query = dc.query().from(table).selectCount().toQuery();
- DataSet ds = dc.executeQuery(query);
- assertTrue(ds.next());
- assertEquals("Row[values=[1000000]]", ds.getRow().toString());
- assertFalse(ds.next());
- ds.close();
- } finally {
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- cb.dropTable("my_table").execute();
- }
- });
- }
- }
-
- public void testCharOfSizeOne() throws Exception {
- JdbcTestTemplates.meaningOfOneSizeChar(_connection);
- }
-
- /**
- * Splits a huge query into 146 pieces and executes them to test that the
- * collective result are equal to the original one in size
- */
- @Ignore
- public void testSplitHugeQueryExecute146() throws Exception {
- DataContext dc = new JdbcDataContext(_connection);
- Query q = new Query();
- Schema schema = dc.getSchemaByName("public");
- Table productsTable = schema.getTableByName("products");
- Table customerTable = schema.getTableByName("customers");
- q.from(productsTable, "p").from(customerTable, "c");
-
- Column titleColumn = productsTable.getColumnByName("title");
- Column priceColumn = productsTable.getColumnByName("price");
- Column cityColumn = customerTable.getColumnByName("city");
- Column ageColumn = customerTable.getColumnByName("age");
- q.select(titleColumn, priceColumn, cityColumn);
-
- q.where(new FilterItem(new SelectItem(priceColumn), OperatorType.GREATER_THAN, 27));
- q.where(new FilterItem(new SelectItem(ageColumn), OperatorType.GREATER_THAN, 55));
-
- assertEquals(
- "SELECT p.\"title\", p.\"price\", c.\"city\" FROM public.\"products\" p, public.\"customers\" c WHERE p.\"price\" > 27 AND c.\"age\" > 55",
- q.toString());
-
- QuerySplitter qs = new QuerySplitter(dc, q);
- qs.setMaxRows(100000);
- assertEquals(14072278, qs.getRowCount());
-
- List<Query> splitQueries = qs.splitQuery();
- assertEquals(146, splitQueries.size());
- assertEquals(
- "SELECT p.\"title\", p.\"price\", c.\"city\" FROM public.\"products\" p, public.\"customers\" c WHERE p.\"price\" > 27 AND c.\"age\" > 55 AND (c.\"customerid\" < 143 OR c.\"customerid\" IS NULL) AND (p.\"category\" < 8 OR p.\"category\" IS NULL)",
- splitQueries.get(0).toString());
- assertEquals(
- "SELECT p.\"title\", p.\"price\", c.\"city\" FROM public.\"products\" p, public.\"customers\" c WHERE p.\"price\" > 27 AND c.\"age\" > 55 AND (c.\"customerid\" > 19739 OR c.\"customerid\" = 19739)",
- splitQueries.get(145).toString());
-
- assertEquals(
- "[45954, 55752, 52122, 55480, 49770, 53410, 60434, 51590, 97284, 94336, 86966, 76648, 98758, 84018, 98758, 95810, 92862, 91388, 39798, 79596, "
- + "91388, 48642, 60434, 106128, 94336, 94336, 86966, 79596, 85492, 94336, 104654, 97284, 84018, 101706, 109076, 89914, 110550, 107602, 98758, "
- + "112024, 100232, 101706, 95810, 92862, 107602, 100232, 86966, 98758, 106128, 91388, 107602, 104654, 107602, 81070, 114972, 79596, 100232, 97284, "
- + "103180, 98758, 113498, 103180, 89914, 104654, 97284, 109076, 114972, 103180, 86966, 106128, 101706, 95810, 103180, 88440, 112024, 91388, 106128, "
- + "82544, 122342, 98758, 104654, 103180, 104654, 89914, 106128, 88440, 103180, 100232, 98758, 100232, 89914, 101706, 100232, 107602, 88440, 89914, "
- + "91388, 103180, 100232, 104654, 120868, 106128, 100232, 107602, 97284, 103180, 106128, 91388, 100232, 106128, 100232, 109076, 94336, 106128, 94336, "
- + "106128, 104654, 116446, 98758, 113498, 107602, 104654, 107602, 88440, 100232, 92862, 89914, 110550, 109076, 100232, 92862, 100232, 104654, 103180, "
- + "89914, 103180, 103180, 107602, 85492, 112024, 85492, 101706, 92862, 86966, 104654, 201938]",
- Arrays.toString(getCounts(dc, splitQueries)));
- assertSameCount(dc, qs, splitQueries);
-
- DataSet data = qs.executeQueries(splitQueries);
- int count = 0;
- while (data.next()) {
- count++;
- }
- data.close();
- assertEquals(14072278, count);
- System.out.println("Successfully iterated 14072278 rows! :)");
- }
-
- /**
- * Utility method for asserting that a query and it's splitted queries have
- * the same total count
- */
- private void assertSameCount(DataContext dc, QuerySplitter qs, List<Query> queries) {
- long count1 = qs.getRowCount();
- long count2 = 0;
- for (Query q : queries) {
- count2 += getCount(dc, q);
- }
- assertEquals(count1, count2);
- }
-
- public long[] getCounts(DataContext dc, List<Query> queries) {
- long[] result = new long[queries.size()];
- for (int i = 0; i < result.length; i++) {
- result[i] = getCount(dc, queries.get(i));
- }
- return result;
- }
-
- /**
- * Gets the count of a query
- */
- private long getCount(DataContext dc, Query query) {
- return new QuerySplitter(dc, query).getRowCount();
- }
-}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/b6979189/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/AbstractJdbIntegrationTest.java
----------------------------------------------------------------------
diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/AbstractJdbIntegrationTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/AbstractJdbIntegrationTest.java
new file mode 100644
index 0000000..10fc2b9
--- /dev/null
+++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/AbstractJdbIntegrationTest.java
@@ -0,0 +1,121 @@
+/**
+ * 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.integrationtests;
+
+import java.io.File;
+import java.io.FileReader;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.util.Properties;
+
+import org.apache.metamodel.util.FileHelper;
+
+import junit.framework.TestCase;
+
+/**
+ * Convenience super class for integration {@link TestCase}s.
+ */
+public abstract class AbstractJdbIntegrationTest extends TestCase {
+
+ private Properties _properties;
+ private boolean _configured;
+ private String _url;
+ private String _username;
+ private String _password;
+ private String _driver;
+ private Connection _connection;
+
+ @Override
+ protected final void setUp() throws Exception {
+ super.setUp();
+
+ // create property prefix of the form "jdbc.databasetype.property"
+ final String propertyPrefix = "jdbc." + getPropertyPrefix();
+
+ _properties = new Properties();
+ final File file = new File(getPropertyFilePath());
+ if (file.exists()) {
+ _properties.load(new FileReader(file));
+ _url = _properties.getProperty(propertyPrefix + ".url");
+ _driver = _properties.getProperty(propertyPrefix + ".driver");
+ _username = _properties.getProperty(propertyPrefix + ".username");
+ _password = _properties.getProperty(propertyPrefix + ".password");
+ _configured = _url != null && _driver != null;
+ } else {
+ _configured = false;
+ }
+ }
+
+ protected Properties getProperties() {
+ return _properties;
+ }
+
+ protected String getDriver() {
+ return _driver;
+ }
+
+ protected String getUsername() {
+ return _username;
+ }
+
+ protected String getPassword() {
+ return _password;
+ }
+
+ protected String getUrl() {
+ return _url;
+ }
+
+ @Override
+ protected final void tearDown() throws Exception {
+ FileHelper.safeClose(_connection);
+ _connection = null;
+ }
+
+ public boolean isConfigured() {
+ return _configured;
+ }
+
+ protected abstract String getPropertyPrefix();
+
+ protected Connection getConnection() {
+ final String className = getClass().getName();
+
+ if (!_configured) {
+ throw new IllegalStateException(className + " is not properly configured from file: "
+ + getPropertyFilePath());
+ }
+
+ if (_connection == null) {
+ try {
+ Class.forName(_driver);
+ _connection = DriverManager.getConnection(_url, _username, _password);
+ } catch (Exception e) {
+ throw new IllegalStateException("Failed to create JDBC connection for " + className, e);
+ }
+ }
+
+ return _connection;
+ }
+
+ private String getPropertyFilePath() {
+ String userHome = System.getProperty("user.home");
+ return userHome + "/metamodel-integrationtest-configuration.properties";
+ }
+}
[3/4] METAMODEL-45: Made JDBC integration tests part of regular test
suite, and added properties in .properties file to configure them.
Posted by ka...@apache.org.
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/276c173a/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
new file mode 100644
index 0000000..76b4d20
--- /dev/null
+++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/MysqlTest.java
@@ -0,0 +1,389 @@
+/**
+ * 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.integrationtests;
+
+import java.sql.DatabaseMetaData;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Arrays;
+import java.util.List;
+
+import javax.swing.table.TableModel;
+
+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.data.DataSetTableModel;
+import org.apache.metamodel.jdbc.JdbcDataContext;
+import org.apache.metamodel.jdbc.JdbcTestTemplates;
+import org.apache.metamodel.jdbc.QuerySplitter;
+import org.apache.metamodel.jdbc.dialects.MysqlQueryRewriter;
+import org.apache.metamodel.query.FilterItem;
+import org.apache.metamodel.query.FromItem;
+import org.apache.metamodel.query.OperatorType;
+import org.apache.metamodel.query.Query;
+import org.apache.metamodel.schema.Column;
+import org.apache.metamodel.schema.ColumnType;
+import org.apache.metamodel.schema.Schema;
+import org.apache.metamodel.schema.Table;
+import org.apache.metamodel.schema.TableType;
+
+/**
+ * Test case that tests mysql interaction. The test requires the "sakila" sample
+ * database that can be found at dev.mysql.com.
+ *
+ * @see http://dev.mysql.com/doc/sakila/en/sakila.html#sakila-installation
+ */
+public class MysqlTest extends AbstractJdbIntegrationTest {
+
+ @Override
+ protected String getPropertyPrefix() {
+ return "mysql";
+ }
+
+ public void testInterpretationOfNull() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ JdbcTestTemplates.interpretationOfNulls(getConnection());
+ }
+
+ public void testDatabaseProductName() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ String databaseProductName = getConnection().getMetaData().getDatabaseProductName();
+ assertEquals(JdbcDataContext.DATABASE_PRODUCT_MYSQL, databaseProductName);
+ }
+
+ public void testAutomaticConversionWhenInsertingString() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ assertNotNull(getConnection());
+
+ Statement st = getConnection().createStatement();
+ try {
+ // clean up, if nescesary
+ st.execute("DROP TABLE test_table");
+ st.close();
+ } catch (SQLException e) {
+ // do nothing
+ }
+
+ assertFalse(getConnection().isReadOnly());
+
+ JdbcDataContext dc = new JdbcDataContext(getConnection());
+ final Schema schema = dc.getDefaultSchema();
+ assertEquals("sakila", schema.getName());
+
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ Table table = cb.createTable(schema, "test_table").withColumn("id").ofType(ColumnType.INTEGER)
+ .asPrimaryKey().withColumn("birthdate").ofType(ColumnType.DATE).execute();
+
+ cb.insertInto(table).value("id", "1").execute();
+ cb.insertInto(table).value("id", 2).value("birthdate", "2011-12-21").execute();
+ }
+ });
+
+ assertTrue(dc.getColumnByQualifiedLabel("test_table.id").isPrimaryKey());
+ assertFalse(dc.getColumnByQualifiedLabel("test_table.birthdate").isPrimaryKey());
+
+ DataSet ds = dc.query().from("test_table").select("id").and("birthdate").execute();
+ assertTrue(ds.next());
+ assertEquals("Row[values=[1, null]]", ds.getRow().toString());
+ assertEquals("java.lang.Integer", ds.getRow().getValue(0).getClass().getName());
+ assertTrue(ds.next());
+ assertEquals("Row[values=[2, 2011-12-21]]", ds.getRow().toString());
+ assertEquals("java.sql.Date", ds.getRow().getValue(1).getClass().getName());
+ assertFalse(ds.next());
+ ds.close();
+
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback callback) {
+ callback.dropTable("test_table").execute();
+ }
+ });
+ }
+
+ public void testCharOfSizeOne() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ JdbcTestTemplates.meaningOfOneSizeChar(getConnection());
+ }
+
+ public void testAlternativeConnectionString() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ DataContext dc = new JdbcDataContext(getConnection(), TableType.DEFAULT_TABLE_TYPES, "sakila");
+ Schema[] schemas = dc.getSchemas();
+ assertEquals("[Schema[name=mysql], Schema[name=performance_schema], Schema[name=portal], "
+ + "Schema[name=sakila], Schema[name=world]]", Arrays.toString(schemas));
+
+ Table table = dc.getSchemaByName("sakila").getTableByName("film");
+ Query q = new Query().from(table).select(table.getColumns());
+ DataSet data = dc.executeQuery(q);
+ TableModel tableModel = new DataSetTableModel(data);
+ assertEquals(13, tableModel.getColumnCount());
+ assertEquals(1000, tableModel.getRowCount());
+ }
+
+ public void testGetCatalogNames() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ JdbcDataContext dataContext = new JdbcDataContext(getConnection());
+ assertTrue(dataContext.getQueryRewriter() instanceof MysqlQueryRewriter);
+ String[] catalogNames = dataContext.getCatalogNames();
+ assertEquals("[information_schema, mysql, performance_schema, portal, sakila, world]",
+ Arrays.toString(catalogNames));
+ }
+
+ public void testGetDefaultSchema() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ DataContext dc = new JdbcDataContext(getConnection());
+ Schema schema = dc.getDefaultSchema();
+ assertEquals("sakila", schema.getName());
+ }
+
+ public void testExecuteQuery() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ DataContext dc = new JdbcDataContext(getConnection());
+ Schema schema = dc.getDefaultSchema();
+ Table actorTable = schema.getTableByName("actor");
+ assertEquals(
+ "[Column[name=actor_id,columnNumber=0,type=SMALLINT,nullable=false,nativeType=SMALLINT UNSIGNED,columnSize=5], Column[name=first_name,columnNumber=1,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=45], Column[name=last_name,columnNumber=2,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=45], Column[name=last_update,columnNumber=3,type=TIMESTAMP,nullable=false,nativeType=TIMESTAMP,columnSize=19]]",
+ Arrays.toString(actorTable.getColumns()));
+ Table filmTable = schema.getTableByName("film");
+ assertEquals(
+ "[Column[name=film_id,columnNumber=0,type=SMALLINT,nullable=false,nativeType=SMALLINT UNSIGNED,columnSize=5], Column[name=title,columnNumber=1,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=255], Column[name=description,columnNumber=2,type=LONGVARCHAR,nullable=true,nativeType=TEXT,columnSize=65535], Column[name=release_year,columnNumber=3,type=DATE,nullable=true,nativeType=YEAR,columnSize=0], Column[name=language_id,columnNumber=4,type=TINYINT,nullable=false,nativeType=TINYINT UNSIGNED,columnSize=3], Column[name=original_language_id,columnNumber=5,type=TINYINT,nullable=true,nativeType=TINYINT UNSIGNED,columnSize=3], Column[name=rental_duration,columnNumber=6,type=TINYINT,nullable=false,nativeType=TINYINT UNSIGNED,columnSize=3], Column[name=rental_rate,columnNumber=7,type=DECIMAL,nullable=false,nativeType=DECIMAL,columnSize=4], Column[name=length,columnNumber=8,type=SMALLINT,nullable=true,nativeType=SMALLINT UNSIGNED,columnSize=5], Column[name=replacement_cost,columnNu
mber=9,type=DECIMAL,nullable=false,nativeType=DECIMAL,columnSize=5], Column[name=rating,columnNumber=10,type=CHAR,nullable=true,nativeType=ENUM,columnSize=5], Column[name=special_features,columnNumber=11,type=CHAR,nullable=true,nativeType=SET,columnSize=54], Column[name=last_update,columnNumber=12,type=TIMESTAMP,nullable=false,nativeType=TIMESTAMP,columnSize=19]]",
+ Arrays.toString(filmTable.getColumns()));
+ Table filmActorJoinTable = schema.getTableByName("film_actor");
+ assertEquals(
+ "[Column[name=actor_id,columnNumber=0,type=SMALLINT,nullable=false,nativeType=SMALLINT UNSIGNED,columnSize=5], "
+ + "Column[name=film_id,columnNumber=1,type=SMALLINT,nullable=false,nativeType=SMALLINT UNSIGNED,columnSize=5], "
+ + "Column[name=last_update,columnNumber=2,type=TIMESTAMP,nullable=false,nativeType=TIMESTAMP,columnSize=19]]",
+ Arrays.toString(filmActorJoinTable.getColumns()));
+
+ Query q = new Query();
+ q.from(new FromItem(actorTable).setAlias("a"));
+ q.select(actorTable.getColumns());
+ q.getSelectClause().getItem(0).setAlias("foo-bar");
+ assertEquals(
+ "SELECT a.`actor_id` AS foo-bar, a.`first_name`, a.`last_name`, a.`last_update` FROM sakila.`actor` a",
+ q.toString());
+ FilterItem f1 = new FilterItem(q.getSelectClause().getItem(0), OperatorType.EQUALS_TO, 5);
+ FilterItem f2 = new FilterItem(q.getSelectClause().getItem(0), OperatorType.EQUALS_TO, 8);
+ q.where(new FilterItem(f1, f2));
+
+ DataSet dataSet = dc.executeQuery(q);
+ TableModel tableModel = new DataSetTableModel(dataSet);
+ assertEquals(4, tableModel.getColumnCount());
+ assertEquals(2, tableModel.getRowCount());
+ assertEquals("LOLLOBRIGIDA", tableModel.getValueAt(0, 2));
+
+ q.setMaxRows(1);
+ dataSet = dc.executeQuery(q);
+ tableModel = new DataSetTableModel(dataSet);
+ assertEquals(4, tableModel.getColumnCount());
+ assertEquals(1, tableModel.getRowCount());
+ assertEquals("LOLLOBRIGIDA", tableModel.getValueAt(0, 2));
+
+ q.setMaxRows(1);
+ q.setFirstRow(2);
+ dataSet = dc.executeQuery(q);
+ tableModel = new DataSetTableModel(dataSet);
+ assertEquals(4, tableModel.getColumnCount());
+ assertEquals(1, tableModel.getRowCount());
+ assertEquals("JOHANSSON", tableModel.getValueAt(0, 2));
+
+ q.getWhereClause().removeItems();
+ q.setMaxRows(25);
+ q.setFirstRow(1);
+ dataSet = dc.executeQuery(q);
+ tableModel = new DataSetTableModel(dataSet);
+ assertEquals(4, tableModel.getColumnCount());
+ assertEquals(25, tableModel.getRowCount());
+ assertEquals("GUINESS", tableModel.getValueAt(0, 2).toString());
+ }
+
+ // Test to query the film table (caused troubles in DataCleaner)
+ public void testFilmQuery() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ DataContext dc = new JdbcDataContext(getConnection());
+ Table table = dc.getDefaultSchema().getTableByName("film");
+ Query q = new Query().select(table.getColumns()).from(table).setMaxRows(400);
+ dc.executeQuery(q);
+ }
+
+ public void testGetSchema() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ DataContext dc = new JdbcDataContext(getConnection());
+ Schema[] schemas = dc.getSchemas();
+ assertEquals(5, schemas.length);
+ Schema schema = dc.getDefaultSchema();
+
+ assertEquals("[Table[name=actor,type=TABLE,remarks=], " + "Table[name=address,type=TABLE,remarks=], "
+ + "Table[name=category,type=TABLE,remarks=], " + "Table[name=city,type=TABLE,remarks=], "
+ + "Table[name=country,type=TABLE,remarks=], " + "Table[name=customer,type=TABLE,remarks=], "
+ + "Table[name=film,type=TABLE,remarks=], " + "Table[name=film_actor,type=TABLE,remarks=], "
+ + "Table[name=film_category,type=TABLE,remarks=], " + "Table[name=film_text,type=TABLE,remarks=], "
+ + "Table[name=inventory,type=TABLE,remarks=], " + "Table[name=language,type=TABLE,remarks=], "
+ + "Table[name=payment,type=TABLE,remarks=], " + "Table[name=rental,type=TABLE,remarks=], "
+ + "Table[name=staff,type=TABLE,remarks=], " + "Table[name=store,type=TABLE,remarks=], "
+ + "Table[name=actor_info,type=VIEW,remarks=], " + "Table[name=customer_list,type=VIEW,remarks=], "
+ + "Table[name=film_list,type=VIEW,remarks=], "
+ + "Table[name=nicer_but_slower_film_list,type=VIEW,remarks=], "
+ + "Table[name=sales_by_film_category,type=VIEW,remarks=], "
+ + "Table[name=sales_by_store,type=VIEW,remarks=], " + "Table[name=staff_list,type=VIEW,remarks=]]",
+ Arrays.toString(schema.getTables()));
+
+ Table filmTable = schema.getTableByName("film");
+ assertEquals(
+ "[Column[name=film_id,columnNumber=0,type=SMALLINT,nullable=false,nativeType=SMALLINT UNSIGNED,columnSize=5], "
+ + "Column[name=title,columnNumber=1,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=255], "
+ + "Column[name=description,columnNumber=2,type=LONGVARCHAR,nullable=true,nativeType=TEXT,columnSize=65535], "
+ + "Column[name=release_year,columnNumber=3,type=DATE,nullable=true,nativeType=YEAR,columnSize=0], "
+ + "Column[name=language_id,columnNumber=4,type=TINYINT,nullable=false,nativeType=TINYINT UNSIGNED,columnSize=3], "
+ + "Column[name=original_language_id,columnNumber=5,type=TINYINT,nullable=true,nativeType=TINYINT UNSIGNED,columnSize=3], "
+ + "Column[name=rental_duration,columnNumber=6,type=TINYINT,nullable=false,nativeType=TINYINT UNSIGNED,columnSize=3], "
+ + "Column[name=rental_rate,columnNumber=7,type=DECIMAL,nullable=false,nativeType=DECIMAL,columnSize=4], "
+ + "Column[name=length,columnNumber=8,type=SMALLINT,nullable=true,nativeType=SMALLINT UNSIGNED,columnSize=5], "
+ + "Column[name=replacement_cost,columnNumber=9,type=DECIMAL,nullable=false,nativeType=DECIMAL,columnSize=5], "
+ + "Column[name=rating,columnNumber=10,type=CHAR,nullable=true,nativeType=ENUM,columnSize=5], "
+ + "Column[name=special_features,columnNumber=11,type=CHAR,nullable=true,nativeType=SET,columnSize=54], "
+ + "Column[name=last_update,columnNumber=12,type=TIMESTAMP,nullable=false,nativeType=TIMESTAMP,columnSize=19]]",
+ Arrays.toString(filmTable.getColumns()));
+ assertEquals(
+ "[Relationship[primaryTable=language,primaryColumns=[language_id],foreignTable=film,foreignColumns=[language_id]], Relationship[primaryTable=language,primaryColumns=[language_id],foreignTable=film,foreignColumns=[original_language_id]], Relationship[primaryTable=film,primaryColumns=[film_id],foreignTable=film_actor,foreignColumns=[film_id]], Relationship[primaryTable=film,primaryColumns=[film_id],foreignTable=film_category,foreignColumns=[film_id]], Relationship[primaryTable=film,primaryColumns=[film_id],foreignTable=inventory,foreignColumns=[film_id]]]",
+ Arrays.toString(filmTable.getRelationships()));
+
+ dc = new JdbcDataContext(getConnection(), TableType.DEFAULT_TABLE_TYPES, "sakila");
+ schemas = dc.getSchemas();
+ assertEquals(6, schemas.length);
+ assertEquals("[Table[name=actor,type=TABLE,remarks=], " + "Table[name=address,type=TABLE,remarks=], "
+ + "Table[name=category,type=TABLE,remarks=], " + "Table[name=city,type=TABLE,remarks=], "
+ + "Table[name=country,type=TABLE,remarks=], " + "Table[name=customer,type=TABLE,remarks=], "
+ + "Table[name=film,type=TABLE,remarks=], " + "Table[name=film_actor,type=TABLE,remarks=], "
+ + "Table[name=film_category,type=TABLE,remarks=], " + "Table[name=film_text,type=TABLE,remarks=], "
+ + "Table[name=inventory,type=TABLE,remarks=], " + "Table[name=language,type=TABLE,remarks=], "
+ + "Table[name=payment,type=TABLE,remarks=], " + "Table[name=rental,type=TABLE,remarks=], "
+ + "Table[name=staff,type=TABLE,remarks=], " + "Table[name=store,type=TABLE,remarks=], "
+ + "Table[name=actor_info,type=VIEW,remarks=], " + "Table[name=customer_list,type=VIEW,remarks=], "
+ + "Table[name=film_list,type=VIEW,remarks=], "
+ + "Table[name=nicer_but_slower_film_list,type=VIEW,remarks=], "
+ + "Table[name=sales_by_film_category,type=VIEW,remarks=], "
+ + "Table[name=sales_by_store,type=VIEW,remarks=], " + "Table[name=staff_list,type=VIEW,remarks=]]",
+ Arrays.toString(schema.getTables()));
+
+ Table staffView = schema.getTableByName("staff_list");
+ assertEquals(
+ "[Column[name=ID,columnNumber=0,type=TINYINT,nullable=false,nativeType=TINYINT UNSIGNED,columnSize=3], "
+ + "Column[name=name,columnNumber=1,type=VARCHAR,nullable=true,nativeType=VARCHAR,columnSize=91], "
+ + "Column[name=address,columnNumber=2,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=50], "
+ + "Column[name=zip code,columnNumber=3,type=VARCHAR,nullable=true,nativeType=VARCHAR,columnSize=10], "
+ + "Column[name=phone,columnNumber=4,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=20], "
+ + "Column[name=city,columnNumber=5,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=50], "
+ + "Column[name=country,columnNumber=6,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=50], "
+ + "Column[name=SID,columnNumber=7,type=TINYINT,nullable=false,nativeType=TINYINT UNSIGNED,columnSize=3]]",
+ Arrays.toString(staffView.getColumns()));
+ }
+
+ public void testSplitQuery() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ DataContext dc = new JdbcDataContext(getConnection(), TableType.DEFAULT_TABLE_TYPES, "sakila");
+ Schema schema = dc.getSchemaByName("sakila");
+ Table staffListTable = schema.getTableByName("staff_list");
+ assertNotNull(staffListTable);
+ Table paymentTable = schema.getTableByName("payment");
+ assertNotNull(paymentTable);
+ Column countryColumn = staffListTable.getColumnByName("country");
+ assertNotNull(countryColumn);
+ Column paymentColumn = paymentTable.getColumns()[0];
+ assertNotNull(paymentColumn);
+ Query q = new Query().from(staffListTable, "sl").from(paymentTable, "e").select(countryColumn, paymentColumn);
+ assertEquals("SELECT sl.`country`, e.`payment_id` FROM sakila.`staff_list` sl, sakila.`payment` e",
+ q.toString());
+
+ QuerySplitter qs = new QuerySplitter(dc, q);
+ assertEquals(32098, qs.getRowCount());
+ List<Query> splitQueries = qs.setMaxRows(8000).splitQuery();
+ assertEquals(7, splitQueries.size());
+ assertEquals(
+ "[SELECT sl.`country`, e.`payment_id` FROM sakila.`staff_list` sl, sakila.`payment` e WHERE (e.`rental_id` < 4013 OR e.`rental_id` IS NULL) AND (e.`customer_id` < 300 OR e.`customer_id` IS NULL), SELECT sl.`country`, e.`payment_id` FROM sakila.`staff_list` sl, sakila.`payment` e WHERE (e.`rental_id` < 4013 OR e.`rental_id` IS NULL) AND (e.`customer_id` > 300 OR e.`customer_id` = 300), SELECT sl.`country`, e.`payment_id` FROM sakila.`staff_list` sl, sakila.`payment` e WHERE (e.`rental_id` > 4013 OR e.`rental_id` = 4013) AND (e.`rental_id` < 8025 OR e.`rental_id` = 4013) AND (e.`payment_id` < 8025 OR e.`payment_id` IS NULL), SELECT sl.`country`, e.`payment_id` FROM sakila.`staff_list` sl, sakila.`payment` e WHERE (e.`rental_id` > 4013 OR e.`rental_id` = 4013) AND (e.`rental_id` < 8025 OR e.`rental_id` = 4013) AND (e.`payment_id` > 8025 OR e.`payment_id` = 8025), SELECT sl.`country`, e.`payment_id` FROM sakila.`staff_list` sl, sakila.`payment` e WHERE (e.`rental_id` > 8025 OR e.`re
ntal_id` = 8025) AND (e.`rental_id` < 12037 OR e.`rental_id` = 8025) AND (e.`amount` < 6 OR e.`amount` IS NULL), SELECT sl.`country`, e.`payment_id` FROM sakila.`staff_list` sl, sakila.`payment` e WHERE (e.`rental_id` > 8025 OR e.`rental_id` = 8025) AND (e.`rental_id` < 12037 OR e.`rental_id` = 8025) AND (e.`amount` > 6 OR e.`amount` = 6), SELECT sl.`country`, e.`payment_id` FROM sakila.`staff_list` sl, sakila.`payment` e WHERE (e.`rental_id` > 12037 OR e.`rental_id` = 12037)]",
+ Arrays.toString(splitQueries.toArray()));
+
+ DataSet data = qs.executeQueries();
+ int count = 0;
+ while (data.next()) {
+ count++;
+ }
+ data.close();
+ assertEquals(32098, count);
+ }
+
+ public void testQueryWithSingleQuote() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ DataContext dc = new JdbcDataContext(getConnection(), TableType.DEFAULT_TABLE_TYPES, "sakila");
+ Query q = dc.query().from("category").selectCount().where("name").eq("kasper's horror movies").toQuery();
+ DataSet ds = dc.executeQuery(q);
+ assertTrue(ds.next());
+ assertEquals(0, ((Number) ds.getRow().getValue(0)).intValue());
+ assertFalse(ds.next());
+ }
+
+ public void testWhiteSpaceColumns() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ DatabaseMetaData metaData = getConnection().getMetaData();
+ assertEquals("`", metaData.getIdentifierQuoteString());
+ }
+}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/276c173a/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
new file mode 100644
index 0000000..a8f8f10
--- /dev/null
+++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/OracleTest.java
@@ -0,0 +1,224 @@
+/**
+ * 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.integrationtests;
+
+import java.sql.ResultSet;
+import java.util.Arrays;
+
+import javax.swing.table.TableModel;
+
+import org.apache.metamodel.DataContext;
+import org.apache.metamodel.data.DataSet;
+import org.apache.metamodel.data.DataSetTableModel;
+import org.apache.metamodel.jdbc.JdbcDataContext;
+import org.apache.metamodel.query.FromItem;
+import org.apache.metamodel.query.JoinType;
+import org.apache.metamodel.query.Query;
+import org.apache.metamodel.schema.Relationship;
+import org.apache.metamodel.schema.Schema;
+import org.apache.metamodel.schema.Table;
+import org.apache.metamodel.schema.TableType;
+
+/**
+ * Test case that tests oracle interaction. An express edition of the oracle
+ * database can be used to run these tests.
+ *
+ * The test requires the "human resources" schema that is provided ass a sample
+ * schema for Oracle default installations.
+ *
+ * The script for installing it can be found in:
+ *
+ * <pre>
+ * $ORACLE_HOME / demo / schema / human_resources / hr_main.sql
+ * </pre>
+ *
+ * Install with something like:
+ *
+ * <pre>
+ * $ORACLE_HOME/bin/sqlplus -S "/ as sysdba" @hr_main.sql
+ * </pre>
+ *
+ * The JDBC driver is not available in the Maven repository so you will have to
+ * download and attach it to the eclipse project yourself.
+ *
+ * @see http://www.oracle.com/technology/products/bi/samples
+ * @see http
+ * ://www.oracle.com/technology/software/products/database/xe/index.html
+ */
+public class OracleTest extends AbstractJdbIntegrationTest {
+
+ @Override
+ protected String getPropertyPrefix() {
+ return "oracle";
+ }
+
+ /**
+ * Ticket #170: getIndexInfo causes SQLException. We test that resultsets
+ * are closed properly.
+ */
+ public void testIndexInfo() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ Schema schema = new JdbcDataContext(getConnection(), new TableType[] { TableType.TABLE }, null)
+ .getSchemaByName("SYS");
+ assertEquals(12, schema.getTableCount());
+ }
+
+ public void testGetSchemaNames() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ DataContext dc = new JdbcDataContext(getConnection());
+ String[] schemaNames = dc.getSchemaNames();
+
+ String concatSchemas = Arrays.toString(schemaNames);
+
+ // In order to allow the database to be used for other purposes than
+ // this integration test, we will not make an exact assertion as to
+ // which schema names exist, but just assert that HR and the default
+ // oracle schemas exist.
+ assertTrue(concatSchemas.indexOf("foobar_schema_that_does_not_exist") == -1);
+ assertTrue(concatSchemas.indexOf("HR") != -1);
+ assertTrue(concatSchemas.indexOf("SYSTEM") != -1);
+ assertTrue(concatSchemas.indexOf("XDB") != -1);
+ assertTrue(schemaNames.length > 8);
+
+ Schema schema = dc.getDefaultSchema();
+ assertEquals("HR", schema.getName());
+ }
+
+ /**
+ * Really only tests the JDBC implementation, used to help localize the
+ * cause for Ticket #144
+ */
+ public void testGetImportedKeys() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ ResultSet rs = getConnection().getMetaData().getImportedKeys(null, "HR", "EMPLOYEES");
+ int count = 0;
+ while (rs.next()) {
+ count++;
+ assertEquals("HR", rs.getString(2));
+ String pkTableName = rs.getString(3);
+ String pkColumnName = rs.getString(4);
+ String fkTableName = rs.getString(7);
+ assertEquals("EMPLOYEES", fkTableName);
+ String fkColumnName = rs.getString(8);
+ System.out.println("Found primary key relation: pkTableName=" + pkTableName + ",pkColumnName="
+ + pkColumnName + ",fkTableName=" + fkTableName + ",fkColumnName=" + fkColumnName);
+ }
+ rs.close();
+ assertEquals(3, count);
+
+ rs = getConnection().getMetaData().getImportedKeys(null, "HR", "DEPARTMENTS");
+ count = 0;
+ while (rs.next()) {
+ count++;
+ assertEquals("HR", rs.getString(2));
+ String pkTableName = rs.getString(3);
+ String pkColumnName = rs.getString(4);
+ String fkTableName = rs.getString(7);
+ assertEquals("DEPARTMENTS", fkTableName);
+ String fkColumnName = rs.getString(8);
+ System.out.println("Found primary key relation: pkTableName=" + pkTableName + ",pkColumnName="
+ + pkColumnName + ",fkTableName=" + fkTableName + ",fkColumnName=" + fkColumnName);
+ }
+ rs.close();
+ assertEquals(2, count);
+ }
+
+ public void testGetSchema() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ Schema schema = getDataContext().getSchemaByName("HR");
+ assertNotNull(schema);
+ assertEquals("{JdbcTable[name=COUNTRIES,type=TABLE,remarks=<null>],"
+ + "JdbcTable[name=DEPARTMENTS,type=TABLE,remarks=<null>]"
+ + ",JdbcTable[name=EMPLOYEES,type=TABLE,remarks=<null>]"
+ + ",JdbcTable[name=JOBS,type=TABLE,remarks=<null>]"
+ + ",JdbcTable[name=JOB_HISTORY,type=TABLE,remarks=<null>]"
+ + ",JdbcTable[name=LOCATIONS,type=TABLE,remarks=<null>]"
+ + ",JdbcTable[name=REGIONS,type=TABLE,remarks=<null>]"
+ + ",JdbcTable[name=EMP_DETAILS_VIEW,type=VIEW,remarks=<null>]}", Arrays.toString(schema.getTables()));
+
+ Relationship[] employeeRelationships = schema.getTableByName("EMPLOYEES").getRelationships();
+ assertEquals(
+ "{Relationship[primaryTable=EMPLOYEES,primaryColumns={EMPLOYEE_ID},foreignTable=DEPARTMENTS,foreignColumns={MANAGER_ID}],"
+ + "Relationship[primaryTable=DEPARTMENTS,primaryColumns={DEPARTMENT_ID},foreignTable=EMPLOYEES,foreignColumns={DEPARTMENT_ID}],"
+ + "Relationship[primaryTable=EMPLOYEES,primaryColumns={EMPLOYEE_ID},foreignTable=EMPLOYEES,foreignColumns={MANAGER_ID}],"
+ + "Relationship[primaryTable=JOBS,primaryColumns={JOB_ID},foreignTable=EMPLOYEES,foreignColumns={JOB_ID}],"
+ + "Relationship[primaryTable=EMPLOYEES,primaryColumns={EMPLOYEE_ID},foreignTable=JOB_HISTORY,foreignColumns={EMPLOYEE_ID}]}",
+ Arrays.toString(employeeRelationships));
+
+ assertEquals(
+ "{JdbcColumn[name=EMPLOYEE_ID,columnNumber=0,type=DECIMAL,nullable=false,nativeType=NUMBER,columnSize=6],"
+ + "JdbcColumn[name=FIRST_NAME,columnNumber=1,type=VARCHAR,nullable=true,nativeType=VARCHAR2,columnSize=20],"
+ + "JdbcColumn[name=LAST_NAME,columnNumber=2,type=VARCHAR,nullable=false,nativeType=VARCHAR2,columnSize=25],"
+ + "JdbcColumn[name=EMAIL,columnNumber=3,type=VARCHAR,nullable=false,nativeType=VARCHAR2,columnSize=25],"
+ + "JdbcColumn[name=PHONE_NUMBER,columnNumber=4,type=VARCHAR,nullable=true,nativeType=VARCHAR2,columnSize=20],"
+ + "JdbcColumn[name=HIRE_DATE,columnNumber=5,type=DATE,nullable=false,nativeType=DATE,columnSize=7],"
+ + "JdbcColumn[name=JOB_ID,columnNumber=6,type=VARCHAR,nullable=false,nativeType=VARCHAR2,columnSize=10],"
+ + "JdbcColumn[name=SALARY,columnNumber=7,type=DECIMAL,nullable=true,nativeType=NUMBER,columnSize=8],"
+ + "JdbcColumn[name=COMMISSION_PCT,columnNumber=8,type=DECIMAL,nullable=true,nativeType=NUMBER,columnSize=2],"
+ + "JdbcColumn[name=MANAGER_ID,columnNumber=9,type=DECIMAL,nullable=true,nativeType=NUMBER,columnSize=6],"
+ + "JdbcColumn[name=DEPARTMENT_ID,columnNumber=10,type=DECIMAL,nullable=true,nativeType=NUMBER,columnSize=4]}",
+ Arrays.toString(schema.getTableByName("EMPLOYEES").getColumns()));
+
+ assertEquals(
+ "{JdbcColumn[name=DEPARTMENT_ID,columnNumber=0,type=DECIMAL,nullable=false,nativeType=NUMBER,columnSize=4],"
+ + "JdbcColumn[name=DEPARTMENT_NAME,columnNumber=1,type=VARCHAR,nullable=false,nativeType=VARCHAR2,columnSize=30],"
+ + "JdbcColumn[name=MANAGER_ID,columnNumber=2,type=DECIMAL,nullable=true,nativeType=NUMBER,columnSize=6],"
+ + "JdbcColumn[name=LOCATION_ID,columnNumber=3,type=DECIMAL,nullable=true,nativeType=NUMBER,columnSize=4]}",
+ Arrays.toString(schema.getTableByName("DEPARTMENTS").getColumns()));
+ }
+
+ public void testExecuteQuery() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ Schema schema = getDataContext().getSchemaByName("HR");
+ Table employeeTable = schema.getTableByName("EMPLOYEES");
+ Table departmentsTable = schema.getTableByName("DEPARTMENTS");
+ Relationship relationship = employeeTable.getRelationships(departmentsTable)[0];
+ assertEquals(
+ "Relationship[primaryTable=EMPLOYEES,primaryColumns={EMPLOYEE_ID},foreignTable=DEPARTMENTS,foreignColumns={MANAGER_ID}]",
+ relationship.toString());
+
+ Query q = new Query().from(new FromItem(JoinType.INNER, relationship)).select(
+ employeeTable.getColumnByName("EMAIL"), departmentsTable.getColumnByName("DEPARTMENT_NAME"));
+ q.getSelectClause().getItem(0).setAlias("e-mail");
+
+ assertEquals(
+ "SELECT \"EMPLOYEES\".\"EMAIL\" AS e-mail, \"DEPARTMENTS\".\"DEPARTMENT_NAME\" FROM HR.\"EMPLOYEES\" INNER JOIN HR.\"DEPARTMENTS\" ON \"EMPLOYEES\".\"EMPLOYEE_ID\" = \"DEPARTMENTS\".\"MANAGER_ID\"",
+ q.toString());
+
+ DataSet data = getDataContext().executeQuery(q);
+ assertNotNull(data);
+ TableModel tableModel = new DataSetTableModel(data);
+ assertEquals(2, tableModel.getColumnCount());
+ assertEquals(11, tableModel.getRowCount());
+ assertEquals("JWHALEN", tableModel.getValueAt(0, 0).toString());
+ assertEquals("Administration", tableModel.getValueAt(0, 1).toString());
+ }
+
+}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/276c173a/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
new file mode 100644
index 0000000..1ad6a85
--- /dev/null
+++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/SQLServerJtdsDriverTest.java
@@ -0,0 +1,236 @@
+/**
+ * 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.integrationtests;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.Arrays;
+
+import org.apache.metamodel.UpdateCallback;
+import org.apache.metamodel.UpdateScript;
+import org.apache.metamodel.data.DataSet;
+import org.apache.metamodel.jdbc.JdbcDataContext;
+import org.apache.metamodel.jdbc.JdbcTestTemplates;
+import org.apache.metamodel.jdbc.dialects.IQueryRewriter;
+import org.apache.metamodel.jdbc.dialects.SQLServerQueryRewriter;
+import org.apache.metamodel.query.Query;
+import org.apache.metamodel.query.SelectItem;
+import org.apache.metamodel.schema.ColumnType;
+import org.apache.metamodel.schema.Schema;
+import org.apache.metamodel.schema.Table;
+import org.apache.metamodel.schema.TableType;
+
+/**
+ * Test case that tests MS SQL Server interaction. The test uses the
+ * "AdventureWorks" sample database which can be downloaded from codeplex.
+ *
+ * This testcase uses the JTDS driver.
+ *
+ * @link{http://www.codeplex.com/MSFTDBProdSamples
+ * */
+public class SQLServerJtdsDriverTest extends AbstractJdbIntegrationTest {
+
+ private static final String DATABASE_NAME = "AdventureWorks";
+
+ @Override
+ protected String getPropertyPrefix() {
+ return "sqlserver.jtds_driver";
+ }
+
+ public void testWorkingWithDates() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ final Connection connection = getConnection();
+ assertFalse(connection.isReadOnly());
+
+ JdbcDataContext dc = new JdbcDataContext(connection);
+ final Schema schema = dc.getSchemaByName("Person");
+
+ JdbcTestTemplates.createInsertAndUpdateDateTypes(dc, schema, "test_table");
+ }
+
+ public void testAutomaticConversionWhenInsertingString() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ final Connection connection = getConnection();
+ assertNotNull(connection);
+
+ try {
+ // clean up, if nescesary
+ connection.createStatement().execute("DROP TABLE Person.test_table");
+ } catch (SQLException e) {
+ // do nothing
+ }
+
+ assertFalse(connection.isReadOnly());
+
+ JdbcDataContext dc = new JdbcDataContext(connection);
+ final Schema schema = dc.getSchemaByName("Person");
+ assertEquals("Person", schema.getName());
+
+ dc.executeUpdate(new UpdateScript() {
+ @Override
+ public void run(UpdateCallback cb) {
+ Table table = cb.createTable(schema, "test_table").withColumn("id").asPrimaryKey()
+ .ofType(ColumnType.INTEGER).withColumn("birthdate").ofType(ColumnType.DATE).execute();
+
+ cb.insertInto(table).value("id", "1").execute();
+ cb.insertInto(table).value("id", 2).value("birthdate", "2011-12-21").execute();
+ }
+ });
+
+ Table table = schema.getTableByName("test_table");
+
+ assertTrue(table.getColumnByName("id").isPrimaryKey());
+ assertFalse(table.getColumnByName("birthdate").isPrimaryKey());
+
+ // the jdbc driver represents the date as a VARCHAR
+ assertEquals("[Column[name=id,columnNumber=0,type=INTEGER,nullable=false,nativeType=int,columnSize=10], "
+ + "Column[name=birthdate,columnNumber=1,type=VARCHAR,nullable=true,nativeType=date,columnSize=10]]",
+ Arrays.toString(table.getColumns()));
+
+ DataSet ds = dc.query().from(table).select("id").and("birthdate").execute();
+ assertTrue(ds.next());
+ assertEquals("Row[values=[1, null]]", ds.getRow().toString());
+ assertEquals("java.lang.Integer", ds.getRow().getValue(0).getClass().getName());
+ assertTrue(ds.next());
+ assertEquals("Row[values=[2, 2011-12-21]]", ds.getRow().toString());
+ assertEquals("java.lang.String", ds.getRow().getValue(1).getClass().getName());
+ assertFalse(ds.next());
+ ds.close();
+
+ connection.createStatement().execute("DROP TABLE Person.test_table");
+ }
+
+ public void testQueryUsingExpressions() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ JdbcDataContext strategy = new JdbcDataContext(getConnection(), new TableType[] { TableType.TABLE,
+ TableType.VIEW }, DATABASE_NAME);
+ Query q = new Query().select("Name").from("Production.Product").where("COlor IS NOT NULL").setMaxRows(5);
+ DataSet dataSet = strategy.executeQuery(q);
+ assertEquals("[Name]", Arrays.toString(dataSet.getSelectItems()));
+ assertTrue(dataSet.next());
+ assertEquals("Row[values=[LL Crankarm]]", dataSet.getRow().toString());
+ assertTrue(dataSet.next());
+ assertTrue(dataSet.next());
+ assertTrue(dataSet.next());
+ assertTrue(dataSet.next());
+ assertFalse(dataSet.next());
+ }
+
+ public void testGetSchemaNormalTableTypes() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ JdbcDataContext dc = new JdbcDataContext(getConnection(), new TableType[] { TableType.TABLE, TableType.VIEW },
+ DATABASE_NAME);
+ Schema[] schemas = dc.getSchemas();
+
+ assertEquals(8, schemas.length);
+ assertEquals("Schema[name=HumanResources]", schemas[0].toString());
+ assertEquals(13, schemas[0].getTableCount());
+ assertEquals("Schema[name=INFORMATION_SCHEMA]", schemas[1].toString());
+ assertEquals(20, schemas[1].getTableCount());
+ assertEquals("Schema[name=Person]", schemas[2].toString());
+ assertEquals(8, schemas[2].getTableCount());
+ assertEquals("Schema[name=Production]", schemas[3].toString());
+ assertEquals(28, schemas[3].getTableCount());
+ assertEquals("Schema[name=Purchasing]", schemas[4].toString());
+ assertEquals(8, schemas[4].getTableCount());
+ assertEquals("Schema[name=Sales]", schemas[5].toString());
+ assertEquals(27, schemas[5].getTableCount());
+
+ }
+
+ public void testGetSchemaAllTableTypes() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ JdbcDataContext strategy = new JdbcDataContext(getConnection(), new TableType[] { TableType.OTHER,
+ TableType.GLOBAL_TEMPORARY }, DATABASE_NAME);
+ Schema schema = strategy.getDefaultSchema();
+ assertEquals("dbo", schema.getName());
+
+ assertEquals("[Sales, HumanResources, dbo, Purchasing, sys, Production, INFORMATION_SCHEMA, Person]",
+ Arrays.toString(strategy.getSchemaNames()));
+ }
+
+ public void testQueryRewriterQuoteAliases() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ JdbcDataContext dc = new JdbcDataContext(getConnection(), TableType.DEFAULT_TABLE_TYPES, DATABASE_NAME);
+ IQueryRewriter queryRewriter = dc.getQueryRewriter();
+ assertSame(SQLServerQueryRewriter.class, queryRewriter.getClass());
+
+ Schema schema = dc.getSchemaByName("Sales");
+ Table customersTable = schema.getTableByName("CUSTOMER");
+
+ Query q = new Query().from(customersTable, "cus-tomers").select(
+ new SelectItem(customersTable.getColumnByName("AccountNumber")).setAlias("c|o|d|e"));
+ q.setMaxRows(5);
+
+ assertEquals("SELECT cus-tomers.\"AccountNumber\" AS c|o|d|e FROM Sales.\"Customer\" cus-tomers", q.toString());
+
+ String queryString = queryRewriter.rewriteQuery(q);
+ assertEquals(
+ "SELECT TOP 5 \"cus-tomers\".\"AccountNumber\" AS \"c|o|d|e\" FROM Sales.\"Customer\" \"cus-tomers\"",
+ queryString);
+
+ // We have to test that no additional quoting characters are added every
+ // time we run the rewriting
+ queryString = queryRewriter.rewriteQuery(q);
+ queryString = queryRewriter.rewriteQuery(q);
+ assertEquals(
+ "SELECT TOP 5 \"cus-tomers\".\"AccountNumber\" AS \"c|o|d|e\" FROM Sales.\"Customer\" \"cus-tomers\"",
+ queryString);
+
+ // Test that the original query is still the same (ie. it has been
+ // cloned for execution)
+ assertEquals("SELECT cus-tomers.\"AccountNumber\" AS c|o|d|e FROM Sales.\"Customer\" cus-tomers", q.toString());
+
+ DataSet data = dc.executeQuery(q);
+ assertNotNull(data);
+ data.close();
+ }
+
+ public void testQuotedString() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ JdbcDataContext dc = new JdbcDataContext(getConnection(), TableType.DEFAULT_TABLE_TYPES, DATABASE_NAME);
+ IQueryRewriter queryRewriter = dc.getQueryRewriter();
+ assertSame(SQLServerQueryRewriter.class, queryRewriter.getClass());
+
+ Query q = dc.query().from("Production", "Product").select("Name").where("Color").eq("R'ed").toQuery();
+
+ DataSet ds = dc.executeQuery(q);
+ assertNotNull(ds);
+ assertFalse(ds.next());
+ ds.close();
+
+ assertEquals(
+ "SELECT Production.\"Product\".\"Name\" FROM Production.\"Product\" WHERE Production.\"Product\".\"Color\" = 'R''ed'",
+ queryRewriter.rewriteQuery(q));
+ }
+}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/276c173a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/SQLServerMicrosoftDriverTest.java
----------------------------------------------------------------------
diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/SQLServerMicrosoftDriverTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/SQLServerMicrosoftDriverTest.java
new file mode 100644
index 0000000..2fcd0ff
--- /dev/null
+++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/SQLServerMicrosoftDriverTest.java
@@ -0,0 +1,163 @@
+/**
+ * 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.integrationtests;
+
+import java.util.Arrays;
+
+import org.apache.metamodel.data.DataSet;
+import org.apache.metamodel.jdbc.JdbcDataContext;
+import org.apache.metamodel.jdbc.dialects.IQueryRewriter;
+import org.apache.metamodel.jdbc.dialects.SQLServerQueryRewriter;
+import org.apache.metamodel.query.Query;
+import org.apache.metamodel.query.SelectItem;
+import org.apache.metamodel.schema.Schema;
+import org.apache.metamodel.schema.Table;
+import org.apache.metamodel.schema.TableType;
+
+/**
+ * Test case that tests MS SQL Server interaction. The test uses the
+ * "AdventureWorks" sample database which can be downloaded from codeplex.
+ *
+ * This testcase uses the official MS SQL Server driver.
+ *
+ * @link{http://www.codeplex.com/MSFTDBProdSamples
+ * */
+public class SQLServerMicrosoftDriverTest extends AbstractJdbIntegrationTest {
+
+ private static final String DATABASE_NAME = "AdventureWorks";
+
+ @Override
+ protected String getPropertyPrefix() {
+ return "sqlserver.microsoft_driver";
+ }
+
+ public void testQueryUsingExpressions() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ JdbcDataContext strategy = new JdbcDataContext(getConnection(),
+ new TableType[] { TableType.TABLE, TableType.VIEW }, DATABASE_NAME);
+ Query q = new Query().select("Name").from("Production.Product").where("COlor IS NOT NULL").setMaxRows(5);
+ DataSet dataSet = strategy.executeQuery(q);
+ assertEquals("[Name]", Arrays.toString(dataSet.getSelectItems()));
+ assertTrue(dataSet.next());
+ assertEquals("Row[values=[LL Crankarm]]", dataSet.getRow().toString());
+ assertTrue(dataSet.next());
+ assertTrue(dataSet.next());
+ assertTrue(dataSet.next());
+ assertTrue(dataSet.next());
+ assertFalse(dataSet.next());
+ }
+
+ public void testGetSchemaNormalTableTypes() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ JdbcDataContext dc = new JdbcDataContext(getConnection(), new TableType[] { TableType.TABLE, TableType.VIEW },
+ DATABASE_NAME);
+ Schema[] schemas = dc.getSchemas();
+
+ assertEquals(8, schemas.length);
+ assertEquals("Schema[name=HumanResources]", schemas[0].toString());
+ assertEquals(13, schemas[0].getTableCount());
+ assertEquals("Schema[name=INFORMATION_SCHEMA]", schemas[1].toString());
+ assertEquals(20, schemas[1].getTableCount());
+ assertEquals("Schema[name=Person]", schemas[2].toString());
+ assertEquals(8, schemas[2].getTableCount());
+ assertEquals("Schema[name=Production]", schemas[3].toString());
+ assertEquals(28, schemas[3].getTableCount());
+ assertEquals("Schema[name=Purchasing]", schemas[4].toString());
+ assertEquals(8, schemas[4].getTableCount());
+ assertEquals("Schema[name=Sales]", schemas[5].toString());
+ assertEquals(27, schemas[5].getTableCount());
+
+ }
+
+ public void testGetSchemaAllTableTypes() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ JdbcDataContext strategy = new JdbcDataContext(getConnection(), new TableType[] { TableType.OTHER,
+ TableType.GLOBAL_TEMPORARY }, DATABASE_NAME);
+
+ assertEquals("[Sales, HumanResources, dbo, Purchasing, sys, Production, INFORMATION_SCHEMA, Person]",
+ Arrays.toString(strategy.getSchemaNames()));
+
+ assertEquals("Schema[name=dbo]", strategy.getDefaultSchema().toString());
+ }
+
+ public void testQueryRewriterQuoteAliases() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ JdbcDataContext strategy = new JdbcDataContext(getConnection(), TableType.DEFAULT_TABLE_TYPES, DATABASE_NAME);
+ IQueryRewriter queryRewriter = strategy.getQueryRewriter();
+ assertSame(SQLServerQueryRewriter.class, queryRewriter.getClass());
+
+ Schema schema = strategy.getSchemaByName("Sales");
+ Table customersTable = schema.getTableByName("CUSTOMER");
+
+ Query q = new Query().from(customersTable, "cus-tomers").select(
+ new SelectItem(customersTable.getColumnByName("AccountNumber")).setAlias("c|o|d|e"));
+ q.setMaxRows(5);
+
+ assertEquals("SELECT cus-tomers.\"AccountNumber\" AS c|o|d|e FROM Sales.\"Customer\" cus-tomers", q.toString());
+
+ String queryString = queryRewriter.rewriteQuery(q);
+ assertEquals(
+ "SELECT TOP 5 \"cus-tomers\".\"AccountNumber\" AS \"c|o|d|e\" FROM Sales.\"Customer\" \"cus-tomers\"",
+ queryString);
+
+ // We have to test that no additional quoting characters are added every
+ // time we run the rewriting
+ queryString = queryRewriter.rewriteQuery(q);
+ queryString = queryRewriter.rewriteQuery(q);
+ assertEquals(
+ "SELECT TOP 5 \"cus-tomers\".\"AccountNumber\" AS \"c|o|d|e\" FROM Sales.\"Customer\" \"cus-tomers\"",
+ queryString);
+
+ // Test that the original query is still the same (ie. it has been
+ // cloned for execution)
+ assertEquals("SELECT cus-tomers.\"AccountNumber\" AS c|o|d|e FROM Sales.\"Customer\" cus-tomers", q.toString());
+
+ DataSet data = strategy.executeQuery(q);
+ assertNotNull(data);
+ data.close();
+ }
+
+ public void testQuotedString() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+
+ JdbcDataContext dc = new JdbcDataContext(getConnection(), TableType.DEFAULT_TABLE_TYPES, DATABASE_NAME);
+ IQueryRewriter queryRewriter = dc.getQueryRewriter();
+ assertSame(SQLServerQueryRewriter.class, queryRewriter.getClass());
+
+ Query q = dc.query().from("Production", "Product").select("Name").where("Color").eq("R'ed").toQuery();
+
+ assertEquals(
+ "SELECT \"Product\".\"Name\" FROM Production.\"Product\" Product WHERE Product.\"Color\" = 'R''ed'",
+ queryRewriter.rewriteQuery(q));
+ }
+}
\ No newline at end of file
[4/4] git commit: METAMODEL-45: Made JDBC integration tests part of
regular test suite,
and added properties in .properties file to configure them.
Posted by ka...@apache.org.
METAMODEL-45: Made JDBC integration tests part of regular test suite,
and added properties in .properties file to configure them.
Project: http://git-wip-us.apache.org/repos/asf/incubator-metamodel/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-metamodel/commit/276c173a
Tree: http://git-wip-us.apache.org/repos/asf/incubator-metamodel/tree/276c173a
Diff: http://git-wip-us.apache.org/repos/asf/incubator-metamodel/diff/276c173a
Branch: refs/heads/master
Commit: 276c173a241e9ac6f3d66d216ad07c1f748c9c83
Parents: b697918
Author: Kasper Sørensen <i....@gmail.com>
Authored: Sat May 31 14:12:30 2014 +0200
Committer: Kasper Sørensen <i....@gmail.com>
Committed: Sat May 31 14:12:30 2014 +0200
----------------------------------------------------------------------
...del-integrationtest-configuration.properties | 31 ++
.../org/apache/metamodel/DB2Test.java | 125 ------
.../org/apache/metamodel/FirebirdTest.java | 133 -------
.../org/apache/metamodel/MysqlTest.java | 352 -----------------
.../org/apache/metamodel/OracleTest.java | 240 ------------
.../metamodel/SQLServerJtdsDriverTest.java | 226 -----------
.../metamodel/SQLServerMicrosoftDriverTest.java | 159 --------
.../AbstractJdbIntegrationTest.java | 5 +
.../jdbc/integrationtests/DB2Test.java | 118 ++++++
.../jdbc/integrationtests/FirebirdTest.java | 121 ++++++
.../jdbc/integrationtests/MysqlTest.java | 389 +++++++++++++++++++
.../jdbc/integrationtests/OracleTest.java | 224 +++++++++++
.../SQLServerJtdsDriverTest.java | 236 +++++++++++
.../SQLServerMicrosoftDriverTest.java | 163 ++++++++
14 files changed, 1287 insertions(+), 1235 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/276c173a/example-metamodel-integrationtest-configuration.properties
----------------------------------------------------------------------
diff --git a/example-metamodel-integrationtest-configuration.properties b/example-metamodel-integrationtest-configuration.properties
index 0dc032e..1a4abbf 100644
--- a/example-metamodel-integrationtest-configuration.properties
+++ b/example-metamodel-integrationtest-configuration.properties
@@ -38,6 +38,37 @@
#jdbc.postgresql.password=metamodel
#jdbc.postgresql.longrunningtests=false
+#jdbc.mysql.driver=com.mysql.jdbc.Driver
+#jdbc.mysql.url=jdbc:mysql://localhost/sakila?defaultFetchSize=-2147483648
+#jdbc.mysql.username=
+#jdbc.mysql.password=
+
+#jdbc.oracle.driver=oracle.jdbc.OracleDriver
+#jdbc.oracle.url=jdbc:oracle:thin:@localhost:1521:XE
+#jdbc.oracle.username=HR
+#jdbc.oracle.password=
+
+#jdbc.db2.driver=com.ibm.db2.jcc.DB2Driver
+#jdbc.db2.url
+#jdbc.db2.username
+#jdbc.db2.password
+
+#jdbc.firebird.driver=org.firebirdsql.jdbc.FBDriver
+#jdbc.firebird.url=jdbc:firebirdsql:127.0.0.1:employee.fdb
+#jdbc.firebird.username=SYSDBA
+#jdbc.firebird.password=
+
+#jdbc.sqlserver.jtds_driver.driver=net.sourceforge.jtds.jdbc.Driver
+#jdbc.sqlserver.jtds_driver.url=jdbc:jtds:sqlserver://localhost:1433/AdventureWorks;instance=SQLEXPRESS
+#jdbc.sqlserver.jtds_driver.username=
+#jdbc.sqlserver.jtds_driver.password=
+
+
+#jdbc.sqlserver.microsoft_driver.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
+#jdbc.sqlserver.microsoft_driver.url=jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=AdventureWorks
+#jdbc.sqlserver.microsoft_driver.username=
+#jdbc.sqlserver.microsoft_driver.password=
+
# -----------------------------
# Salesforce module properties:
# -----------------------------
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/276c173a/jdbc/src/test/integrationtests/org/apache/metamodel/DB2Test.java
----------------------------------------------------------------------
diff --git a/jdbc/src/test/integrationtests/org/apache/metamodel/DB2Test.java b/jdbc/src/test/integrationtests/org/apache/metamodel/DB2Test.java
deleted file mode 100644
index f1f6a6d..0000000
--- a/jdbc/src/test/integrationtests/org/apache/metamodel/DB2Test.java
+++ /dev/null
@@ -1,125 +0,0 @@
-/**
- * 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;
-
-import java.sql.Connection;
-import java.sql.DriverManager;
-import java.util.Arrays;
-
-import junit.framework.TestCase;
-
-import org.apache.metamodel.data.DataSet;
-import org.apache.metamodel.jdbc.JdbcDataContext;
-import org.apache.metamodel.jdbc.JdbcTestTemplates;
-import org.apache.metamodel.query.Query;
-import org.apache.metamodel.schema.Schema;
-import org.apache.metamodel.schema.Table;
-
-/**
- * DB2 integration test. This is a read-only integration test, meant to be
- * modified for whatever server is available (even within Human Inference).
- */
-public class DB2Test extends TestCase {
-
- private static final String URL = "jdbc:db2://TODO:50000/TODO";
-
- private static final String USERNAME = "TODO";
- private static final String PASSWORD = "TODO";
- private Connection _connection;
-
- @Override
- protected void setUp() throws Exception {
- super.setUp();
- Class.forName("com.ibm.db2.jcc.DB2Driver");
- _connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
- }
-
- @Override
- protected void tearDown() throws Exception {
- super.tearDown();
- _connection.close();
- }
-
-
- public void testInterpretationOfNull() throws Exception {
- JdbcTestTemplates.interpretationOfNulls(_connection);
- }
-
- public void testDefaultSchema() throws Exception {
- JdbcDataContext dc = new JdbcDataContext(_connection);
- Schema schema = dc.getDefaultSchema();
- assertEquals(USERNAME.toUpperCase(), schema.getName());
-
- Table countryTable = schema.getTableByName("COUNTRY");
- assertNotNull(countryTable);
-
- DataSet ds = dc.query().from(countryTable).selectCount().execute();
- assertTrue(ds.next());
- assertEquals("Row[values=[1008]]", ds.getRow().toString());
- assertFalse(ds.next());
- ds.close();
- }
-
- public void testMaxRowsOnly() throws Exception {
- JdbcDataContext dc = new JdbcDataContext(_connection);
- Schema schema = dc.getDefaultSchema();
- String[] tableNames = schema.getTableNames();
- System.out.println("Tables: " + Arrays.toString(tableNames));
-
- Table countryTable = schema.getTableByName("COUNTRY");
- assertNotNull(countryTable);
-
- Query query = dc.query().from(countryTable).select("COUNTRYCODE").limit(200).toQuery();
- assertEquals("SELECT DB2INST1.\"COUNTRY\".\"COUNTRYCODE\" FROM DB2INST1.\"COUNTRY\" "
- + "FETCH FIRST 200 ROWS ONLY", dc.getQueryRewriter().rewriteQuery(query));
-
- DataSet ds = dc.executeQuery(query);
- for (int i = 0; i < 200; i++) {
- assertTrue(ds.next());
- assertEquals(1, ds.getRow().getValues().length);
- }
- assertFalse(ds.next());
- ds.close();
- }
-
- public void testMaxRowsAndOffset() throws Exception {
- JdbcDataContext dc = new JdbcDataContext(_connection);
- Schema schema = dc.getDefaultSchema();
- String[] tableNames = schema.getTableNames();
- System.out.println("Tables: " + Arrays.toString(tableNames));
-
- Table countryTable = schema.getTableByName("COUNTRY");
- assertNotNull(countryTable);
-
- Query query = dc.query().from(countryTable).select("COUNTRYCODE").limit(200).offset(200).toQuery();
- assertEquals(
- "SELECT metamodel_subquery.\"COUNTRYCODE\" FROM ("
- + "SELECT DB2INST1.\"COUNTRY\".\"COUNTRYCODE\", ROW_NUMBER() OVER() AS metamodel_row_number FROM DB2INST1.\"COUNTRY\""
- + ") metamodel_subquery WHERE metamodel_row_number BETWEEN 201 AND 400", dc.getQueryRewriter()
- .rewriteQuery(query));
-
- DataSet ds = dc.executeQuery(query);
- for (int i = 0; i < 200; i++) {
- assertTrue(ds.next());
- assertEquals(1, ds.getRow().getValues().length);
- }
- assertFalse(ds.next());
- ds.close();
- }
-}
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/276c173a/jdbc/src/test/integrationtests/org/apache/metamodel/FirebirdTest.java
----------------------------------------------------------------------
diff --git a/jdbc/src/test/integrationtests/org/apache/metamodel/FirebirdTest.java b/jdbc/src/test/integrationtests/org/apache/metamodel/FirebirdTest.java
deleted file mode 100644
index 0c7fdf5..0000000
--- a/jdbc/src/test/integrationtests/org/apache/metamodel/FirebirdTest.java
+++ /dev/null
@@ -1,133 +0,0 @@
-/**
- * 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;
-
-import java.sql.Connection;
-import java.sql.DriverManager;
-import java.text.SimpleDateFormat;
-import java.util.Arrays;
-import java.util.Date;
-
-import javax.swing.table.TableModel;
-
-import junit.framework.TestCase;
-
-import org.apache.metamodel.data.DataSet;
-import org.apache.metamodel.data.DataSetTableModel;
-import org.apache.metamodel.jdbc.JdbcDataContext;
-import org.apache.metamodel.query.FromItem;
-import org.apache.metamodel.query.JoinType;
-import org.apache.metamodel.query.Query;
-import org.apache.metamodel.query.SelectItem;
-import org.apache.metamodel.schema.Schema;
-import org.apache.metamodel.schema.Table;
-
-/**
- * Integrationtests for Firebird SQL.
- *
- * This test uses the "employee" sampledata shipped with Firebird. The JDBC
- * driver ("jaybird") is not available in the Maven repository so you will have
- * to download and attach it to the eclipse project yourself.
- *
- * @see http://www.firebirdsql.org/manual/qsg10-connecting.html
- * @see http://www.firebirdsql.org/index.php?op=files&id=jaybird
- */
-public class FirebirdTest extends TestCase {
-
- private static final String CONNECTION_STRING = "jdbc:firebirdsql:127.0.0.1:employee.fdb";
- private static final String USERNAME = "SYSDBA";
- private static final String PASSWORD = "eobjects";
- private Connection _connection;
- private DataContext _dataContext;
-
- @Override
- protected void setUp() throws Exception {
- super.setUp();
- Class.forName("org.firebirdsql.jdbc.FBDriver");
- _connection = DriverManager.getConnection(CONNECTION_STRING, USERNAME, PASSWORD);
- _connection.setReadOnly(true);
- _dataContext = new JdbcDataContext(_connection);
- }
-
- @Override
- protected void tearDown() throws Exception {
- super.tearDown();
- _connection.close();
- }
-
- public void testGetSchemas() throws Exception {
- Schema[] schemas = _dataContext.getSchemas();
- assertEquals(1, schemas.length);
- Schema schema = _dataContext.getDefaultSchema();
- assertEquals("{JdbcTable[name=COUNTRY,type=TABLE,remarks=<null>],"
- + "JdbcTable[name=CUSTOMER,type=TABLE,remarks=<null>],"
- + "JdbcTable[name=DEPARTMENT,type=TABLE,remarks=<null>],"
- + "JdbcTable[name=EMPLOYEE,type=TABLE,remarks=<null>],"
- + "JdbcTable[name=EMPLOYEE_PROJECT,type=TABLE,remarks=<null>],"
- + "JdbcTable[name=JOB,type=TABLE,remarks=<null>],"
- + "JdbcTable[name=PHONE_LIST,type=VIEW,remarks=<null>],"
- + "JdbcTable[name=PROJECT,type=TABLE,remarks=<null>],"
- + "JdbcTable[name=PROJ_DEPT_BUDGET,type=TABLE,remarks=<null>],"
- + "JdbcTable[name=SALARY_HISTORY,type=TABLE,remarks=<null>],"
- + "JdbcTable[name=SALES,type=TABLE,remarks=<null>]}", Arrays.toString(schema.getTables()));
-
- assertEquals(
- "{Relationship[primaryTable=COUNTRY,primaryColumns={COUNTRY},foreignTable=CUSTOMER,foreignColumns={COUNTRY}],"
- + "Relationship[primaryTable=COUNTRY,primaryColumns={COUNTRY},foreignTable=JOB,foreignColumns={JOB_COUNTRY}],"
- + "Relationship[primaryTable=CUSTOMER,primaryColumns={CUST_NO},foreignTable=SALES,foreignColumns={CUST_NO}],"
- + "Relationship[primaryTable=DEPARTMENT,primaryColumns={DEPT_NO},foreignTable=DEPARTMENT,foreignColumns={HEAD_DEPT}],"
- + "Relationship[primaryTable=DEPARTMENT,primaryColumns={DEPT_NO},foreignTable=EMPLOYEE,foreignColumns={DEPT_NO}],"
- + "Relationship[primaryTable=DEPARTMENT,primaryColumns={DEPT_NO},foreignTable=PROJ_DEPT_BUDGET,foreignColumns={DEPT_NO}],"
- + "Relationship[primaryTable=EMPLOYEE,primaryColumns={EMP_NO},foreignTable=DEPARTMENT,foreignColumns={MNGR_NO}],"
- + "Relationship[primaryTable=EMPLOYEE,primaryColumns={EMP_NO},foreignTable=EMPLOYEE_PROJECT,foreignColumns={EMP_NO}],"
- + "Relationship[primaryTable=EMPLOYEE,primaryColumns={EMP_NO},foreignTable=PROJECT,foreignColumns={TEAM_LEADER}],"
- + "Relationship[primaryTable=EMPLOYEE,primaryColumns={EMP_NO},foreignTable=SALARY_HISTORY,foreignColumns={EMP_NO}],"
- + "Relationship[primaryTable=EMPLOYEE,primaryColumns={EMP_NO},foreignTable=SALES,foreignColumns={SALES_REP}],"
- + "Relationship[primaryTable=JOB,primaryColumns={JOB_CODE},foreignTable=EMPLOYEE,foreignColumns={JOB_CODE}],"
- + "Relationship[primaryTable=JOB,primaryColumns={JOB_GRADE},foreignTable=EMPLOYEE,foreignColumns={JOB_GRADE}],"
- + "Relationship[primaryTable=JOB,primaryColumns={JOB_COUNTRY},foreignTable=EMPLOYEE,foreignColumns={JOB_COUNTRY}],"
- + "Relationship[primaryTable=PROJECT,primaryColumns={PROJ_ID},foreignTable=EMPLOYEE_PROJECT,foreignColumns={PROJ_ID}],"
- + "Relationship[primaryTable=PROJECT,primaryColumns={PROJ_ID},foreignTable=PROJ_DEPT_BUDGET,foreignColumns={PROJ_ID}]}",
- Arrays.toString(schema.getRelationships()));
- }
-
- public void testExecuteQuery() throws Exception {
- Schema schema = _dataContext.getDefaultSchema();
- Table departmentTable = schema.getTableByName("DEPARTMENT");
- Table employeeTable = schema.getTableByName("EMPLOYEE");
- Query q = new Query().from(new FromItem(JoinType.INNER, departmentTable.getRelationships(employeeTable)[0]));
- q.select(departmentTable.getColumns()[1]);
- q.select(new SelectItem(employeeTable.getColumns()[4]).setAlias("hire-date"));
- assertEquals(
- "SELECT \"DEPARTMENT\".\"DEPARTMENT\", \"EMPLOYEE\".\"HIRE_DATE\" AS hire-date FROM \"EMPLOYEE\" INNER JOIN \"DEPARTMENT\" ON \"EMPLOYEE\".\"EMP_NO\" = \"DEPARTMENT\".\"MNGR_NO\"",
- q.toString());
-
- DataSet data = _dataContext.executeQuery(q);
- assertNotNull(data);
-
- TableModel tableModel = new DataSetTableModel(data);
- assertEquals(2, tableModel.getColumnCount());
- assertEquals(17, tableModel.getRowCount());
- assertEquals("Quality Assurance", tableModel.getValueAt(4, 0).toString());
-
- Date date = (Date) tableModel.getValueAt(4, 1);
- assertEquals("1989-04-17 00:00:00.000000", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSSSS").format(date));
-
- }
-}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/276c173a/jdbc/src/test/integrationtests/org/apache/metamodel/MysqlTest.java
----------------------------------------------------------------------
diff --git a/jdbc/src/test/integrationtests/org/apache/metamodel/MysqlTest.java b/jdbc/src/test/integrationtests/org/apache/metamodel/MysqlTest.java
deleted file mode 100644
index 2a3c730..0000000
--- a/jdbc/src/test/integrationtests/org/apache/metamodel/MysqlTest.java
+++ /dev/null
@@ -1,352 +0,0 @@
-/**
- * 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;
-
-import java.sql.Connection;
-import java.sql.DatabaseMetaData;
-import java.sql.DriverManager;
-import java.sql.SQLException;
-import java.sql.Statement;
-import java.util.Arrays;
-import java.util.List;
-
-import javax.swing.table.TableModel;
-
-import junit.framework.TestCase;
-
-import org.apache.metamodel.data.DataSet;
-import org.apache.metamodel.data.DataSetTableModel;
-import org.apache.metamodel.jdbc.JdbcDataContext;
-import org.apache.metamodel.jdbc.JdbcTestTemplates;
-import org.apache.metamodel.jdbc.QuerySplitter;
-import org.apache.metamodel.jdbc.dialects.MysqlQueryRewriter;
-import org.apache.metamodel.query.FilterItem;
-import org.apache.metamodel.query.FromItem;
-import org.apache.metamodel.query.OperatorType;
-import org.apache.metamodel.query.Query;
-import org.apache.metamodel.schema.Column;
-import org.apache.metamodel.schema.ColumnType;
-import org.apache.metamodel.schema.Schema;
-import org.apache.metamodel.schema.Table;
-import org.apache.metamodel.schema.TableType;
-
-/**
- * Test case that tests mysql interaction. The test requires the "sakila" sample
- * database that can be found at dev.mysql.com.
- *
- * @see http://dev.mysql.com/doc/sakila/en/sakila.html#sakila-installation
- */
-public class MysqlTest extends TestCase {
-
- private static final String CONNECTION_STRING = "jdbc:mysql://localhost/sakila?defaultFetchSize=" + Integer.MIN_VALUE;
- private static final String USERNAME = "eobjects";
- private static final String PASSWORD = "eobjects";
- private Connection _connection;
-
- @Override
- protected void setUp() throws Exception {
- super.setUp();
- Class.forName("com.mysql.jdbc.Driver");
- _connection = DriverManager.getConnection(CONNECTION_STRING, USERNAME, PASSWORD);
- }
-
- public void testInterpretationOfNull() throws Exception {
- JdbcTestTemplates.interpretationOfNulls(_connection);
- }
-
- public void testDatabaseProductName() throws Exception {
- String databaseProductName = _connection.getMetaData().getDatabaseProductName();
- assertEquals(JdbcDataContext.DATABASE_PRODUCT_MYSQL, databaseProductName);
- }
-
- public void testAutomaticConversionWhenInsertingString() throws Exception {
- assertNotNull(_connection);
-
- Statement st = _connection.createStatement();
- try {
- // clean up, if nescesary
- st.execute("DROP TABLE test_table");
- st.close();
- } catch (SQLException e) {
- // do nothing
- }
-
- assertFalse(_connection.isReadOnly());
-
- JdbcDataContext dc = new JdbcDataContext(_connection);
- final Schema schema = dc.getDefaultSchema();
- assertEquals("sakila", schema.getName());
-
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- Table table = cb.createTable(schema, "test_table").withColumn("id").ofType(ColumnType.INTEGER)
- .asPrimaryKey().withColumn("birthdate").ofType(ColumnType.DATE).execute();
-
- cb.insertInto(table).value("id", "1").execute();
- cb.insertInto(table).value("id", 2).value("birthdate", "2011-12-21").execute();
- }
- });
-
- assertTrue(dc.getColumnByQualifiedLabel("test_table.id").isPrimaryKey());
- assertFalse(dc.getColumnByQualifiedLabel("test_table.birthdate").isPrimaryKey());
-
- DataSet ds = dc.query().from("test_table").select("id").and("birthdate").execute();
- assertTrue(ds.next());
- assertEquals("Row[values=[1, null]]", ds.getRow().toString());
- assertEquals("java.lang.Integer", ds.getRow().getValue(0).getClass().getName());
- assertTrue(ds.next());
- assertEquals("Row[values=[2, 2011-12-21]]", ds.getRow().toString());
- assertEquals("java.sql.Date", ds.getRow().getValue(1).getClass().getName());
- assertFalse(ds.next());
- ds.close();
-
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback callback) {
- callback.dropTable("test_table").execute();
- }
- });
- }
-
- public void testCharOfSizeOne() throws Exception {
- JdbcTestTemplates.meaningOfOneSizeChar(_connection);
- }
-
- public void testAlternativeConnectionString() throws Exception {
- _connection = DriverManager.getConnection("jdbc:mysql://localhost", USERNAME, PASSWORD);
- DataContext dc = new JdbcDataContext(_connection, TableType.DEFAULT_TABLE_TYPES, "sakila");
- Schema[] schemas = dc.getSchemas();
- assertEquals("[Schema[name=mysql], Schema[name=performance_schema], Schema[name=portal], "
- + "Schema[name=sakila], Schema[name=world]]", Arrays.toString(schemas));
-
- Table table = dc.getSchemaByName("sakila").getTableByName("film");
- Query q = new Query().from(table).select(table.getColumns());
- DataSet data = dc.executeQuery(q);
- TableModel tableModel = new DataSetTableModel(data);
- assertEquals(13, tableModel.getColumnCount());
- assertEquals(1000, tableModel.getRowCount());
- }
-
- @Override
- protected void tearDown() throws Exception {
- super.tearDown();
- _connection.close();
- }
-
- public void testGetCatalogNames() throws Exception {
- JdbcDataContext strategy = new JdbcDataContext(_connection);
- assertTrue(strategy.getQueryRewriter() instanceof MysqlQueryRewriter);
- String[] catalogNames = strategy.getCatalogNames();
- assertEquals("[information_schema, mysql, performance_schema, portal, sakila, world]",
- Arrays.toString(catalogNames));
- }
-
- public void testGetDefaultSchema() throws Exception {
- DataContext dc = new JdbcDataContext(_connection);
- Schema schema = dc.getDefaultSchema();
- assertEquals("sakila", schema.getName());
- }
-
- public void testExecuteQuery() throws Exception {
- DataContext dc = new JdbcDataContext(_connection);
- Schema schema = dc.getDefaultSchema();
- Table actorTable = schema.getTableByName("actor");
- assertEquals(
- "[Column[name=actor_id,columnNumber=0,type=SMALLINT,nullable=false,nativeType=SMALLINT UNSIGNED,columnSize=5], Column[name=first_name,columnNumber=1,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=45], Column[name=last_name,columnNumber=2,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=45], Column[name=last_update,columnNumber=3,type=TIMESTAMP,nullable=false,nativeType=TIMESTAMP,columnSize=19]]",
- Arrays.toString(actorTable.getColumns()));
- Table filmTable = schema.getTableByName("film");
- assertEquals(
- "[Column[name=film_id,columnNumber=0,type=SMALLINT,nullable=false,nativeType=SMALLINT UNSIGNED,columnSize=5], Column[name=title,columnNumber=1,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=255], Column[name=description,columnNumber=2,type=LONGVARCHAR,nullable=true,nativeType=TEXT,columnSize=65535], Column[name=release_year,columnNumber=3,type=DATE,nullable=true,nativeType=YEAR,columnSize=0], Column[name=language_id,columnNumber=4,type=TINYINT,nullable=false,nativeType=TINYINT UNSIGNED,columnSize=3], Column[name=original_language_id,columnNumber=5,type=TINYINT,nullable=true,nativeType=TINYINT UNSIGNED,columnSize=3], Column[name=rental_duration,columnNumber=6,type=TINYINT,nullable=false,nativeType=TINYINT UNSIGNED,columnSize=3], Column[name=rental_rate,columnNumber=7,type=DECIMAL,nullable=false,nativeType=DECIMAL,columnSize=4], Column[name=length,columnNumber=8,type=SMALLINT,nullable=true,nativeType=SMALLINT UNSIGNED,columnSize=5], Column[name=replacement_cost,columnNu
mber=9,type=DECIMAL,nullable=false,nativeType=DECIMAL,columnSize=5], Column[name=rating,columnNumber=10,type=CHAR,nullable=true,nativeType=ENUM,columnSize=5], Column[name=special_features,columnNumber=11,type=CHAR,nullable=true,nativeType=SET,columnSize=54], Column[name=last_update,columnNumber=12,type=TIMESTAMP,nullable=false,nativeType=TIMESTAMP,columnSize=19]]",
- Arrays.toString(filmTable.getColumns()));
- Table filmActorJoinTable = schema.getTableByName("film_actor");
- assertEquals(
- "[Column[name=actor_id,columnNumber=0,type=SMALLINT,nullable=false,nativeType=SMALLINT UNSIGNED,columnSize=5], "
- + "Column[name=film_id,columnNumber=1,type=SMALLINT,nullable=false,nativeType=SMALLINT UNSIGNED,columnSize=5], "
- + "Column[name=last_update,columnNumber=2,type=TIMESTAMP,nullable=false,nativeType=TIMESTAMP,columnSize=19]]",
- Arrays.toString(filmActorJoinTable.getColumns()));
-
- Query q = new Query();
- q.from(new FromItem(actorTable).setAlias("a"));
- q.select(actorTable.getColumns());
- q.getSelectClause().getItem(0).setAlias("foo-bar");
- assertEquals(
- "SELECT a.`actor_id` AS foo-bar, a.`first_name`, a.`last_name`, a.`last_update` FROM sakila.`actor` a",
- q.toString());
- FilterItem f1 = new FilterItem(q.getSelectClause().getItem(0), OperatorType.EQUALS_TO, 5);
- FilterItem f2 = new FilterItem(q.getSelectClause().getItem(0), OperatorType.EQUALS_TO, 8);
- q.where(new FilterItem(f1, f2));
-
- DataSet dataSet = dc.executeQuery(q);
- TableModel tableModel = new DataSetTableModel(dataSet);
- assertEquals(4, tableModel.getColumnCount());
- assertEquals(2, tableModel.getRowCount());
- assertEquals("LOLLOBRIGIDA", tableModel.getValueAt(0, 2));
-
- q.setMaxRows(1);
- dataSet = dc.executeQuery(q);
- tableModel = new DataSetTableModel(dataSet);
- assertEquals(4, tableModel.getColumnCount());
- assertEquals(1, tableModel.getRowCount());
- assertEquals("LOLLOBRIGIDA", tableModel.getValueAt(0, 2));
-
- q.setMaxRows(1);
- q.setFirstRow(2);
- dataSet = dc.executeQuery(q);
- tableModel = new DataSetTableModel(dataSet);
- assertEquals(4, tableModel.getColumnCount());
- assertEquals(1, tableModel.getRowCount());
- assertEquals("JOHANSSON", tableModel.getValueAt(0, 2));
-
- q.getWhereClause().removeItems();
- q.setMaxRows(25);
- q.setFirstRow(1);
- dataSet = dc.executeQuery(q);
- tableModel = new DataSetTableModel(dataSet);
- assertEquals(4, tableModel.getColumnCount());
- assertEquals(25, tableModel.getRowCount());
- assertEquals("GUINESS", tableModel.getValueAt(0, 2).toString());
- }
-
- // Test to query the film table (caused troubles in DataCleaner)
- public void testFilmQuery() throws Exception {
- DataContext dc = new JdbcDataContext(_connection);
- Table table = dc.getDefaultSchema().getTableByName("film");
- Query q = new Query().select(table.getColumns()).from(table).setMaxRows(400);
- dc.executeQuery(q);
- }
-
- public void testGetSchema() throws Exception {
- DataContext dc = new JdbcDataContext(_connection);
- Schema[] schemas = dc.getSchemas();
- assertEquals(5, schemas.length);
- Schema schema = dc.getDefaultSchema();
-
- assertEquals("[Table[name=actor,type=TABLE,remarks=], " + "Table[name=address,type=TABLE,remarks=], "
- + "Table[name=category,type=TABLE,remarks=], " + "Table[name=city,type=TABLE,remarks=], "
- + "Table[name=country,type=TABLE,remarks=], " + "Table[name=customer,type=TABLE,remarks=], "
- + "Table[name=film,type=TABLE,remarks=], " + "Table[name=film_actor,type=TABLE,remarks=], "
- + "Table[name=film_category,type=TABLE,remarks=], " + "Table[name=film_text,type=TABLE,remarks=], "
- + "Table[name=inventory,type=TABLE,remarks=], " + "Table[name=language,type=TABLE,remarks=], "
- + "Table[name=payment,type=TABLE,remarks=], " + "Table[name=rental,type=TABLE,remarks=], "
- + "Table[name=staff,type=TABLE,remarks=], " + "Table[name=store,type=TABLE,remarks=], "
- + "Table[name=actor_info,type=VIEW,remarks=], " + "Table[name=customer_list,type=VIEW,remarks=], "
- + "Table[name=film_list,type=VIEW,remarks=], "
- + "Table[name=nicer_but_slower_film_list,type=VIEW,remarks=], "
- + "Table[name=sales_by_film_category,type=VIEW,remarks=], "
- + "Table[name=sales_by_store,type=VIEW,remarks=], " + "Table[name=staff_list,type=VIEW,remarks=]]",
- Arrays.toString(schema.getTables()));
-
- Table filmTable = schema.getTableByName("film");
- assertEquals(
- "[Column[name=film_id,columnNumber=0,type=SMALLINT,nullable=false,nativeType=SMALLINT UNSIGNED,columnSize=5], "
- + "Column[name=title,columnNumber=1,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=255], "
- + "Column[name=description,columnNumber=2,type=LONGVARCHAR,nullable=true,nativeType=TEXT,columnSize=65535], "
- + "Column[name=release_year,columnNumber=3,type=DATE,nullable=true,nativeType=YEAR,columnSize=0], "
- + "Column[name=language_id,columnNumber=4,type=TINYINT,nullable=false,nativeType=TINYINT UNSIGNED,columnSize=3], "
- + "Column[name=original_language_id,columnNumber=5,type=TINYINT,nullable=true,nativeType=TINYINT UNSIGNED,columnSize=3], "
- + "Column[name=rental_duration,columnNumber=6,type=TINYINT,nullable=false,nativeType=TINYINT UNSIGNED,columnSize=3], "
- + "Column[name=rental_rate,columnNumber=7,type=DECIMAL,nullable=false,nativeType=DECIMAL,columnSize=4], "
- + "Column[name=length,columnNumber=8,type=SMALLINT,nullable=true,nativeType=SMALLINT UNSIGNED,columnSize=5], "
- + "Column[name=replacement_cost,columnNumber=9,type=DECIMAL,nullable=false,nativeType=DECIMAL,columnSize=5], "
- + "Column[name=rating,columnNumber=10,type=CHAR,nullable=true,nativeType=ENUM,columnSize=5], "
- + "Column[name=special_features,columnNumber=11,type=CHAR,nullable=true,nativeType=SET,columnSize=54], "
- + "Column[name=last_update,columnNumber=12,type=TIMESTAMP,nullable=false,nativeType=TIMESTAMP,columnSize=19]]",
- Arrays.toString(filmTable.getColumns()));
- assertEquals(
- "[Relationship[primaryTable=language,primaryColumns=[language_id],foreignTable=film,foreignColumns=[language_id]], Relationship[primaryTable=language,primaryColumns=[language_id],foreignTable=film,foreignColumns=[original_language_id]], Relationship[primaryTable=film,primaryColumns=[film_id],foreignTable=film_actor,foreignColumns=[film_id]], Relationship[primaryTable=film,primaryColumns=[film_id],foreignTable=film_category,foreignColumns=[film_id]], Relationship[primaryTable=film,primaryColumns=[film_id],foreignTable=inventory,foreignColumns=[film_id]]]",
- Arrays.toString(filmTable.getRelationships()));
-
- dc = new JdbcDataContext(_connection, TableType.DEFAULT_TABLE_TYPES, "sakila");
- schemas = dc.getSchemas();
- assertEquals(6, schemas.length);
- assertEquals("[Table[name=actor,type=TABLE,remarks=], " + "Table[name=address,type=TABLE,remarks=], "
- + "Table[name=category,type=TABLE,remarks=], " + "Table[name=city,type=TABLE,remarks=], "
- + "Table[name=country,type=TABLE,remarks=], " + "Table[name=customer,type=TABLE,remarks=], "
- + "Table[name=film,type=TABLE,remarks=], " + "Table[name=film_actor,type=TABLE,remarks=], "
- + "Table[name=film_category,type=TABLE,remarks=], " + "Table[name=film_text,type=TABLE,remarks=], "
- + "Table[name=inventory,type=TABLE,remarks=], " + "Table[name=language,type=TABLE,remarks=], "
- + "Table[name=payment,type=TABLE,remarks=], " + "Table[name=rental,type=TABLE,remarks=], "
- + "Table[name=staff,type=TABLE,remarks=], " + "Table[name=store,type=TABLE,remarks=], "
- + "Table[name=actor_info,type=VIEW,remarks=], " + "Table[name=customer_list,type=VIEW,remarks=], "
- + "Table[name=film_list,type=VIEW,remarks=], "
- + "Table[name=nicer_but_slower_film_list,type=VIEW,remarks=], "
- + "Table[name=sales_by_film_category,type=VIEW,remarks=], "
- + "Table[name=sales_by_store,type=VIEW,remarks=], " + "Table[name=staff_list,type=VIEW,remarks=]]",
- Arrays.toString(schema.getTables()));
-
- Table staffView = schema.getTableByName("staff_list");
- assertEquals(
- "[Column[name=ID,columnNumber=0,type=TINYINT,nullable=false,nativeType=TINYINT UNSIGNED,columnSize=3], "
- + "Column[name=name,columnNumber=1,type=VARCHAR,nullable=true,nativeType=VARCHAR,columnSize=91], "
- + "Column[name=address,columnNumber=2,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=50], "
- + "Column[name=zip code,columnNumber=3,type=VARCHAR,nullable=true,nativeType=VARCHAR,columnSize=10], "
- + "Column[name=phone,columnNumber=4,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=20], "
- + "Column[name=city,columnNumber=5,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=50], "
- + "Column[name=country,columnNumber=6,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=50], "
- + "Column[name=SID,columnNumber=7,type=TINYINT,nullable=false,nativeType=TINYINT UNSIGNED,columnSize=3]]",
- Arrays.toString(staffView.getColumns()));
- }
-
- public void testSplitQuery() throws Exception {
- DataContext dc = new JdbcDataContext(_connection, TableType.DEFAULT_TABLE_TYPES, "sakila");
- Schema schema = dc.getSchemaByName("sakila");
- Table staffListTable = schema.getTableByName("staff_list");
- assertNotNull(staffListTable);
- Table paymentTable = schema.getTableByName("payment");
- assertNotNull(paymentTable);
- Column countryColumn = staffListTable.getColumnByName("country");
- assertNotNull(countryColumn);
- Column paymentColumn = paymentTable.getColumns()[0];
- assertNotNull(paymentColumn);
- Query q = new Query().from(staffListTable, "sl").from(paymentTable, "e").select(countryColumn, paymentColumn);
- assertEquals("SELECT sl.`country`, e.`payment_id` FROM sakila.`staff_list` sl, sakila.`payment` e",
- q.toString());
-
- QuerySplitter qs = new QuerySplitter(dc, q);
- assertEquals(32098, qs.getRowCount());
- List<Query> splitQueries = qs.setMaxRows(8000).splitQuery();
- assertEquals(7, splitQueries.size());
- assertEquals(
- "[SELECT sl.`country`, e.`payment_id` FROM sakila.`staff_list` sl, sakila.`payment` e WHERE (e.`rental_id` < 4013 OR e.`rental_id` IS NULL) AND (e.`customer_id` < 300 OR e.`customer_id` IS NULL), SELECT sl.`country`, e.`payment_id` FROM sakila.`staff_list` sl, sakila.`payment` e WHERE (e.`rental_id` < 4013 OR e.`rental_id` IS NULL) AND (e.`customer_id` > 300 OR e.`customer_id` = 300), SELECT sl.`country`, e.`payment_id` FROM sakila.`staff_list` sl, sakila.`payment` e WHERE (e.`rental_id` > 4013 OR e.`rental_id` = 4013) AND (e.`rental_id` < 8025 OR e.`rental_id` = 4013) AND (e.`payment_id` < 8025 OR e.`payment_id` IS NULL), SELECT sl.`country`, e.`payment_id` FROM sakila.`staff_list` sl, sakila.`payment` e WHERE (e.`rental_id` > 4013 OR e.`rental_id` = 4013) AND (e.`rental_id` < 8025 OR e.`rental_id` = 4013) AND (e.`payment_id` > 8025 OR e.`payment_id` = 8025), SELECT sl.`country`, e.`payment_id` FROM sakila.`staff_list` sl, sakila.`payment` e WHERE (e.`rental_id` > 8025 OR e.`re
ntal_id` = 8025) AND (e.`rental_id` < 12037 OR e.`rental_id` = 8025) AND (e.`amount` < 6 OR e.`amount` IS NULL), SELECT sl.`country`, e.`payment_id` FROM sakila.`staff_list` sl, sakila.`payment` e WHERE (e.`rental_id` > 8025 OR e.`rental_id` = 8025) AND (e.`rental_id` < 12037 OR e.`rental_id` = 8025) AND (e.`amount` > 6 OR e.`amount` = 6), SELECT sl.`country`, e.`payment_id` FROM sakila.`staff_list` sl, sakila.`payment` e WHERE (e.`rental_id` > 12037 OR e.`rental_id` = 12037)]",
- Arrays.toString(splitQueries.toArray()));
-
- DataSet data = qs.executeQueries();
- int count = 0;
- while (data.next()) {
- count++;
- }
- data.close();
- assertEquals(32098, count);
- }
-
- public void testQueryWithSingleQuote() throws Exception {
- DataContext dc = new JdbcDataContext(_connection, TableType.DEFAULT_TABLE_TYPES, "sakila");
- Query q = dc.query().from("category").selectCount().where("name").eq("kasper's horror movies").toQuery();
- DataSet ds = dc.executeQuery(q);
- assertTrue(ds.next());
- assertEquals(0, ((Number) ds.getRow().getValue(0)).intValue());
- assertFalse(ds.next());
- }
-
- public void testWhiteSpaceColumns() throws Exception {
- DatabaseMetaData metaData = _connection.getMetaData();
- assertEquals("`", metaData.getIdentifierQuoteString());
- }
-}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/276c173a/jdbc/src/test/integrationtests/org/apache/metamodel/OracleTest.java
----------------------------------------------------------------------
diff --git a/jdbc/src/test/integrationtests/org/apache/metamodel/OracleTest.java b/jdbc/src/test/integrationtests/org/apache/metamodel/OracleTest.java
deleted file mode 100644
index 0d15dbf..0000000
--- a/jdbc/src/test/integrationtests/org/apache/metamodel/OracleTest.java
+++ /dev/null
@@ -1,240 +0,0 @@
-/**
- * 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;
-
-import java.sql.Connection;
-import java.sql.DriverManager;
-import java.sql.ResultSet;
-import java.util.Arrays;
-
-import javax.swing.table.TableModel;
-
-import junit.framework.TestCase;
-
-import org.apache.metamodel.data.DataSet;
-import org.apache.metamodel.data.DataSetTableModel;
-import org.apache.metamodel.jdbc.JdbcDataContext;
-import org.apache.metamodel.query.FromItem;
-import org.apache.metamodel.query.JoinType;
-import org.apache.metamodel.query.Query;
-import org.apache.metamodel.schema.Relationship;
-import org.apache.metamodel.schema.Schema;
-import org.apache.metamodel.schema.Table;
-import org.apache.metamodel.schema.TableType;
-
-/**
- * Test case that tests oracle interaction. An express edition of the oracle
- * database can be used to run these tests.
- *
- * The test requires the "human resources" schema that is provided ass a sample
- * schema for Oracle default installations.
- *
- * The script for installing it can be found in:
- *
- * <pre>
- * $ORACLE_HOME / demo / schema / human_resources / hr_main.sql
- * </pre>
- *
- * Install with something like:
- *
- * <pre>
- * $ORACLE_HOME/bin/sqlplus -S "/ as sysdba" @hr_main.sql
- * </pre>
- *
- * The JDBC driver is not available in the Maven repository so you will have to
- * download and attach it to the eclipse project yourself.
- *
- * @see http://www.oracle.com/technology/products/bi/samples
- * @see http
- * ://www.oracle.com/technology/software/products/database/xe/index.html
- */
-public class OracleTest extends TestCase {
-
- private static final String CONNECTION_STRING = "jdbc:oracle:thin:@localhost:1521:XE";
- private static final String USERNAME = "HR";
- private static final String PASSWORD = "eobjects";
- private Connection _connection;
- private DataContext _dataContext;
-
- @Override
- protected void setUp() throws Exception {
- super.setUp();
- Class.forName("oracle.jdbc.OracleDriver");
- _connection = DriverManager.getConnection(CONNECTION_STRING, USERNAME,
- PASSWORD);
- _connection.setReadOnly(true);
- _dataContext = new JdbcDataContext(_connection);
- }
-
- @Override
- protected void tearDown() throws Exception {
- super.tearDown();
- _connection.close();
- }
-
- /**
- * Ticket #170: getIndexInfo causes SQLException. We test that resultsets
- * are closed properly.
- */
- public void testIndexInfo() throws Exception {
- Schema schema = new JdbcDataContext(_connection,
- new TableType[] { TableType.TABLE }, null)
- .getSchemaByName("SYS");
- assertEquals(12, schema.getTableCount());
- }
-
- public void testGetSchemaNames() throws Exception {
- DataContext dc = new JdbcDataContext(_connection);
- String[] schemaNames = dc.getSchemaNames();
-
- String concatSchemas = Arrays.toString(schemaNames);
-
- // In order to allow the database to be used for other purposes than
- // this integration test, we will not make an exact assertion as to
- // which schema names exist, but just assert that HR and the default
- // oracle schemas exist.
- assertTrue(concatSchemas.indexOf("foobar_schema_that_does_not_exist") == -1);
- assertTrue(concatSchemas.indexOf("HR") != -1);
- assertTrue(concatSchemas.indexOf("SYSTEM") != -1);
- assertTrue(concatSchemas.indexOf("XDB") != -1);
- assertTrue(schemaNames.length > 8);
-
- Schema schema = dc.getDefaultSchema();
- assertEquals("HR", schema.getName());
- }
-
- /**
- * Really only tests the JDBC implementation, used to help localize the
- * cause for Ticket #144
- */
- public void testGetImportedKeys() throws Exception {
- ResultSet rs = _connection.getMetaData().getImportedKeys(null, "HR",
- "EMPLOYEES");
- int count = 0;
- while (rs.next()) {
- count++;
- assertEquals("HR", rs.getString(2));
- String pkTableName = rs.getString(3);
- String pkColumnName = rs.getString(4);
- String fkTableName = rs.getString(7);
- assertEquals("EMPLOYEES", fkTableName);
- String fkColumnName = rs.getString(8);
- System.out.println("Found primary key relation: pkTableName="
- + pkTableName + ",pkColumnName=" + pkColumnName
- + ",fkTableName=" + fkTableName + ",fkColumnName="
- + fkColumnName);
- }
- rs.close();
- assertEquals(3, count);
-
- rs = _connection.getMetaData().getImportedKeys(null, "HR",
- "DEPARTMENTS");
- count = 0;
- while (rs.next()) {
- count++;
- assertEquals("HR", rs.getString(2));
- String pkTableName = rs.getString(3);
- String pkColumnName = rs.getString(4);
- String fkTableName = rs.getString(7);
- assertEquals("DEPARTMENTS", fkTableName);
- String fkColumnName = rs.getString(8);
- System.out.println("Found primary key relation: pkTableName="
- + pkTableName + ",pkColumnName=" + pkColumnName
- + ",fkTableName=" + fkTableName + ",fkColumnName="
- + fkColumnName);
- }
- rs.close();
- assertEquals(2, count);
- }
-
- public void testGetSchema() throws Exception {
- Schema schema = _dataContext.getSchemaByName("HR");
- assertNotNull(schema);
- assertEquals(
- "{JdbcTable[name=COUNTRIES,type=TABLE,remarks=<null>],"
- + "JdbcTable[name=DEPARTMENTS,type=TABLE,remarks=<null>]"
- + ",JdbcTable[name=EMPLOYEES,type=TABLE,remarks=<null>]"
- + ",JdbcTable[name=JOBS,type=TABLE,remarks=<null>]"
- + ",JdbcTable[name=JOB_HISTORY,type=TABLE,remarks=<null>]"
- + ",JdbcTable[name=LOCATIONS,type=TABLE,remarks=<null>]"
- + ",JdbcTable[name=REGIONS,type=TABLE,remarks=<null>]"
- + ",JdbcTable[name=EMP_DETAILS_VIEW,type=VIEW,remarks=<null>]}",
- Arrays.toString(schema.getTables()));
-
- Relationship[] employeeRelationships = schema.getTableByName(
- "EMPLOYEES").getRelationships();
- assertEquals(
- "{Relationship[primaryTable=EMPLOYEES,primaryColumns={EMPLOYEE_ID},foreignTable=DEPARTMENTS,foreignColumns={MANAGER_ID}],"
- + "Relationship[primaryTable=DEPARTMENTS,primaryColumns={DEPARTMENT_ID},foreignTable=EMPLOYEES,foreignColumns={DEPARTMENT_ID}],"
- + "Relationship[primaryTable=EMPLOYEES,primaryColumns={EMPLOYEE_ID},foreignTable=EMPLOYEES,foreignColumns={MANAGER_ID}],"
- + "Relationship[primaryTable=JOBS,primaryColumns={JOB_ID},foreignTable=EMPLOYEES,foreignColumns={JOB_ID}],"
- + "Relationship[primaryTable=EMPLOYEES,primaryColumns={EMPLOYEE_ID},foreignTable=JOB_HISTORY,foreignColumns={EMPLOYEE_ID}]}",
- Arrays.toString(employeeRelationships));
-
- assertEquals(
- "{JdbcColumn[name=EMPLOYEE_ID,columnNumber=0,type=DECIMAL,nullable=false,nativeType=NUMBER,columnSize=6],"
- + "JdbcColumn[name=FIRST_NAME,columnNumber=1,type=VARCHAR,nullable=true,nativeType=VARCHAR2,columnSize=20],"
- + "JdbcColumn[name=LAST_NAME,columnNumber=2,type=VARCHAR,nullable=false,nativeType=VARCHAR2,columnSize=25],"
- + "JdbcColumn[name=EMAIL,columnNumber=3,type=VARCHAR,nullable=false,nativeType=VARCHAR2,columnSize=25],"
- + "JdbcColumn[name=PHONE_NUMBER,columnNumber=4,type=VARCHAR,nullable=true,nativeType=VARCHAR2,columnSize=20],"
- + "JdbcColumn[name=HIRE_DATE,columnNumber=5,type=DATE,nullable=false,nativeType=DATE,columnSize=7],"
- + "JdbcColumn[name=JOB_ID,columnNumber=6,type=VARCHAR,nullable=false,nativeType=VARCHAR2,columnSize=10],"
- + "JdbcColumn[name=SALARY,columnNumber=7,type=DECIMAL,nullable=true,nativeType=NUMBER,columnSize=8],"
- + "JdbcColumn[name=COMMISSION_PCT,columnNumber=8,type=DECIMAL,nullable=true,nativeType=NUMBER,columnSize=2],"
- + "JdbcColumn[name=MANAGER_ID,columnNumber=9,type=DECIMAL,nullable=true,nativeType=NUMBER,columnSize=6],"
- + "JdbcColumn[name=DEPARTMENT_ID,columnNumber=10,type=DECIMAL,nullable=true,nativeType=NUMBER,columnSize=4]}",
- Arrays.toString(schema.getTableByName("EMPLOYEES").getColumns()));
-
- assertEquals(
- "{JdbcColumn[name=DEPARTMENT_ID,columnNumber=0,type=DECIMAL,nullable=false,nativeType=NUMBER,columnSize=4],"
- + "JdbcColumn[name=DEPARTMENT_NAME,columnNumber=1,type=VARCHAR,nullable=false,nativeType=VARCHAR2,columnSize=30],"
- + "JdbcColumn[name=MANAGER_ID,columnNumber=2,type=DECIMAL,nullable=true,nativeType=NUMBER,columnSize=6],"
- + "JdbcColumn[name=LOCATION_ID,columnNumber=3,type=DECIMAL,nullable=true,nativeType=NUMBER,columnSize=4]}",
- Arrays.toString(schema.getTableByName("DEPARTMENTS")
- .getColumns()));
- }
-
- public void testExecuteQuery() throws Exception {
- Schema schema = _dataContext.getSchemaByName("HR");
- Table employeeTable = schema.getTableByName("EMPLOYEES");
- Table departmentsTable = schema.getTableByName("DEPARTMENTS");
- Relationship relationship = employeeTable
- .getRelationships(departmentsTable)[0];
- assertEquals(
- "Relationship[primaryTable=EMPLOYEES,primaryColumns={EMPLOYEE_ID},foreignTable=DEPARTMENTS,foreignColumns={MANAGER_ID}]",
- relationship.toString());
-
- Query q = new Query().from(new FromItem(JoinType.INNER, relationship))
- .select(employeeTable.getColumnByName("EMAIL"),
- departmentsTable.getColumnByName("DEPARTMENT_NAME"));
- q.getSelectClause().getItem(0).setAlias("e-mail");
-
- assertEquals(
- "SELECT \"EMPLOYEES\".\"EMAIL\" AS e-mail, \"DEPARTMENTS\".\"DEPARTMENT_NAME\" FROM HR.\"EMPLOYEES\" INNER JOIN HR.\"DEPARTMENTS\" ON \"EMPLOYEES\".\"EMPLOYEE_ID\" = \"DEPARTMENTS\".\"MANAGER_ID\"",
- q.toString());
-
- DataSet data = _dataContext.executeQuery(q);
- assertNotNull(data);
- TableModel tableModel = new DataSetTableModel(data);
- assertEquals(2, tableModel.getColumnCount());
- assertEquals(11, tableModel.getRowCount());
- assertEquals("JWHALEN", tableModel.getValueAt(0, 0).toString());
- assertEquals("Administration", tableModel.getValueAt(0, 1).toString());
- }
-}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/276c173a/jdbc/src/test/integrationtests/org/apache/metamodel/SQLServerJtdsDriverTest.java
----------------------------------------------------------------------
diff --git a/jdbc/src/test/integrationtests/org/apache/metamodel/SQLServerJtdsDriverTest.java b/jdbc/src/test/integrationtests/org/apache/metamodel/SQLServerJtdsDriverTest.java
deleted file mode 100644
index b365041..0000000
--- a/jdbc/src/test/integrationtests/org/apache/metamodel/SQLServerJtdsDriverTest.java
+++ /dev/null
@@ -1,226 +0,0 @@
-/**
- * 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;
-
-import java.sql.Connection;
-import java.sql.DriverManager;
-import java.sql.SQLException;
-import java.util.Arrays;
-
-import junit.framework.TestCase;
-
-import org.apache.metamodel.data.DataSet;
-import org.apache.metamodel.jdbc.JdbcDataContext;
-import org.apache.metamodel.jdbc.JdbcTestTemplates;
-import org.apache.metamodel.jdbc.dialects.IQueryRewriter;
-import org.apache.metamodel.jdbc.dialects.SQLServerQueryRewriter;
-import org.apache.metamodel.query.Query;
-import org.apache.metamodel.query.SelectItem;
-import org.apache.metamodel.schema.ColumnType;
-import org.apache.metamodel.schema.Schema;
-import org.apache.metamodel.schema.Table;
-import org.apache.metamodel.schema.TableType;
-
-/**
- * Test case that tests MS SQL Server interaction. The test uses the
- * "AdventureWorks" sample database which can be downloaded from codeplex.
- *
- * This testcase uses the JTDS driver.
- *
- * @link{http://www.codeplex.com/MSFTDBProdSamples
- * */
-public class SQLServerJtdsDriverTest extends TestCase {
-
- private Connection _connection;
- private String _databaseName = "AdventureWorks";
-
- @Override
- protected void setUp() throws Exception {
- super.setUp();
- Class.forName("net.sourceforge.jtds.jdbc.Driver");
- _connection = DriverManager.getConnection(
- "jdbc:jtds:sqlserver://localhost:1433/AdventureWorks;instance=SQLEXPRESS", "eobjects", "eobjects");
-
- }
-
- @Override
- protected void tearDown() throws Exception {
- super.tearDown();
- _connection.close();
- }
-
- public void testWorkingWithDates() throws Exception {
- assertFalse(_connection.isReadOnly());
-
- JdbcDataContext dc = new JdbcDataContext(_connection);
- final Schema schema = dc.getSchemaByName("Person");
-
- JdbcTestTemplates.createInsertAndUpdateDateTypes(dc, schema, "test_table");
- }
-
- public void testAutomaticConversionWhenInsertingString() throws Exception {
- assertNotNull(_connection);
-
- try {
- // clean up, if nescesary
- _connection.createStatement().execute("DROP TABLE Person.test_table");
- } catch (SQLException e) {
- // do nothing
- }
-
- assertFalse(_connection.isReadOnly());
-
- JdbcDataContext dc = new JdbcDataContext(_connection);
- final Schema schema = dc.getSchemaByName("Person");
- assertEquals("Person", schema.getName());
-
- dc.executeUpdate(new UpdateScript() {
- @Override
- public void run(UpdateCallback cb) {
- Table table = cb.createTable(schema, "test_table").withColumn("id").asPrimaryKey()
- .ofType(ColumnType.INTEGER).withColumn("birthdate").ofType(ColumnType.DATE).execute();
-
- cb.insertInto(table).value("id", "1").execute();
- cb.insertInto(table).value("id", 2).value("birthdate", "2011-12-21").execute();
- }
- });
-
- Table table = schema.getTableByName("test_table");
-
- assertTrue(table.getColumnByName("id").isPrimaryKey());
- assertFalse(table.getColumnByName("birthdate").isPrimaryKey());
-
- // the jdbc driver represents the date as a VARCHAR
- assertEquals(
- "[Column[name=id,columnNumber=0,type=INTEGER,nullable=false,nativeType=int,columnSize=10], "
- + "Column[name=birthdate,columnNumber=1,type=VARCHAR,nullable=true,nativeType=date,columnSize=10]]",
- Arrays.toString(table.getColumns()));
-
- DataSet ds = dc.query().from(table).select("id").and("birthdate").execute();
- assertTrue(ds.next());
- assertEquals("Row[values=[1, null]]", ds.getRow().toString());
- assertEquals("java.lang.Integer", ds.getRow().getValue(0).getClass().getName());
- assertTrue(ds.next());
- assertEquals("Row[values=[2, 2011-12-21]]", ds.getRow().toString());
- assertEquals("java.lang.String", ds.getRow().getValue(1).getClass().getName());
- assertFalse(ds.next());
- ds.close();
-
- _connection.createStatement().execute("DROP TABLE Person.test_table");
- }
-
- public void testQueryUsingExpressions() throws Exception {
- JdbcDataContext strategy = new JdbcDataContext(_connection,
- new TableType[] { TableType.TABLE, TableType.VIEW }, _databaseName);
- Query q = new Query().select("Name").from("Production.Product").where("COlor IS NOT NULL").setMaxRows(5);
- DataSet dataSet = strategy.executeQuery(q);
- assertEquals("[Name]", Arrays.toString(dataSet.getSelectItems()));
- assertTrue(dataSet.next());
- assertEquals("Row[values=[LL Crankarm]]", dataSet.getRow().toString());
- assertTrue(dataSet.next());
- assertTrue(dataSet.next());
- assertTrue(dataSet.next());
- assertTrue(dataSet.next());
- assertFalse(dataSet.next());
- }
-
- public void testGetSchemaNormalTableTypes() throws Exception {
- JdbcDataContext dc = new JdbcDataContext(_connection, new TableType[] { TableType.TABLE, TableType.VIEW },
- _databaseName);
- Schema[] schemas = dc.getSchemas();
-
- assertEquals(8, schemas.length);
- assertEquals("Schema[name=HumanResources]", schemas[0].toString());
- assertEquals(13, schemas[0].getTableCount());
- assertEquals("Schema[name=INFORMATION_SCHEMA]", schemas[1].toString());
- assertEquals(20, schemas[1].getTableCount());
- assertEquals("Schema[name=Person]", schemas[2].toString());
- assertEquals(8, schemas[2].getTableCount());
- assertEquals("Schema[name=Production]", schemas[3].toString());
- assertEquals(28, schemas[3].getTableCount());
- assertEquals("Schema[name=Purchasing]", schemas[4].toString());
- assertEquals(8, schemas[4].getTableCount());
- assertEquals("Schema[name=Sales]", schemas[5].toString());
- assertEquals(27, schemas[5].getTableCount());
-
- }
-
- public void testGetSchemaAllTableTypes() throws Exception {
- JdbcDataContext strategy = new JdbcDataContext(_connection, new TableType[] { TableType.OTHER,
- TableType.GLOBAL_TEMPORARY }, _databaseName);
- Schema schema = strategy.getDefaultSchema();
- assertEquals("dbo", schema.getName());
-
- assertEquals("[Sales, HumanResources, dbo, Purchasing, sys, Production, INFORMATION_SCHEMA, Person]",
- Arrays.toString(strategy.getSchemaNames()));
- }
-
- public void testQueryRewriterQuoteAliases() throws Exception {
- JdbcDataContext dc = new JdbcDataContext(_connection, TableType.DEFAULT_TABLE_TYPES, _databaseName);
- IQueryRewriter queryRewriter = dc.getQueryRewriter();
- assertSame(SQLServerQueryRewriter.class, queryRewriter.getClass());
-
- Schema schema = dc.getSchemaByName("Sales");
- Table customersTable = schema.getTableByName("CUSTOMER");
-
- Query q = new Query().from(customersTable, "cus-tomers").select(
- new SelectItem(customersTable.getColumnByName("AccountNumber")).setAlias("c|o|d|e"));
- q.setMaxRows(5);
-
- assertEquals("SELECT cus-tomers.\"AccountNumber\" AS c|o|d|e FROM Sales.\"Customer\" cus-tomers", q.toString());
-
- String queryString = queryRewriter.rewriteQuery(q);
- assertEquals(
- "SELECT TOP 5 \"cus-tomers\".\"AccountNumber\" AS \"c|o|d|e\" FROM Sales.\"Customer\" \"cus-tomers\"",
- queryString);
-
- // We have to test that no additional quoting characters are added every
- // time we run the rewriting
- queryString = queryRewriter.rewriteQuery(q);
- queryString = queryRewriter.rewriteQuery(q);
- assertEquals(
- "SELECT TOP 5 \"cus-tomers\".\"AccountNumber\" AS \"c|o|d|e\" FROM Sales.\"Customer\" \"cus-tomers\"",
- queryString);
-
- // Test that the original query is still the same (ie. it has been
- // cloned for execution)
- assertEquals("SELECT cus-tomers.\"AccountNumber\" AS c|o|d|e FROM Sales.\"Customer\" cus-tomers", q.toString());
-
- DataSet data = dc.executeQuery(q);
- assertNotNull(data);
- data.close();
- }
-
- public void testQuotedString() throws Exception {
- JdbcDataContext dc = new JdbcDataContext(_connection, TableType.DEFAULT_TABLE_TYPES, _databaseName);
- IQueryRewriter queryRewriter = dc.getQueryRewriter();
- assertSame(SQLServerQueryRewriter.class, queryRewriter.getClass());
-
- Query q = dc.query().from("Production", "Product").select("Name").where("Color").eq("R'ed").toQuery();
-
- DataSet ds = dc.executeQuery(q);
- assertNotNull(ds);
- assertFalse(ds.next());
- ds.close();
-
- assertEquals(
- "SELECT Production.\"Product\".\"Name\" FROM Production.\"Product\" WHERE Production.\"Product\".\"Color\" = 'R''ed'",
- queryRewriter.rewriteQuery(q));
- }
-}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/276c173a/jdbc/src/test/integrationtests/org/apache/metamodel/SQLServerMicrosoftDriverTest.java
----------------------------------------------------------------------
diff --git a/jdbc/src/test/integrationtests/org/apache/metamodel/SQLServerMicrosoftDriverTest.java b/jdbc/src/test/integrationtests/org/apache/metamodel/SQLServerMicrosoftDriverTest.java
deleted file mode 100644
index 1afd798..0000000
--- a/jdbc/src/test/integrationtests/org/apache/metamodel/SQLServerMicrosoftDriverTest.java
+++ /dev/null
@@ -1,159 +0,0 @@
-/**
- * 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;
-
-import java.sql.Connection;
-import java.sql.DriverManager;
-import java.util.Arrays;
-
-import junit.framework.TestCase;
-
-import org.apache.metamodel.data.DataSet;
-import org.apache.metamodel.jdbc.JdbcDataContext;
-import org.apache.metamodel.jdbc.dialects.IQueryRewriter;
-import org.apache.metamodel.jdbc.dialects.SQLServerQueryRewriter;
-import org.apache.metamodel.query.Query;
-import org.apache.metamodel.query.SelectItem;
-import org.apache.metamodel.schema.Schema;
-import org.apache.metamodel.schema.Table;
-import org.apache.metamodel.schema.TableType;
-
-/**
- * Test case that tests MS SQL Server interaction. The test uses the
- * "AdventureWorks" sample database which can be downloaded from codeplex.
- *
- * This testcase uses the official MS SQL Server driver.
- *
- * @link{http://www.codeplex.com/MSFTDBProdSamples
- * */
-public class SQLServerMicrosoftDriverTest extends TestCase {
-
- private Connection _connection;
- private String _databaseName = "AdventureWorks";
-
- @Override
- protected void setUp() throws Exception {
- super.setUp();
- Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
- _connection = DriverManager.getConnection("jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName="
- + _databaseName, "eobjects", "eobjects");
- _connection.setReadOnly(true);
-
- }
-
- @Override
- protected void tearDown() throws Exception {
- super.tearDown();
- _connection.close();
- }
-
- public void testQueryUsingExpressions() throws Exception {
- JdbcDataContext strategy = new JdbcDataContext(_connection,
- new TableType[] { TableType.TABLE, TableType.VIEW }, _databaseName);
- Query q = new Query().select("Name").from("Production.Product").where("COlor IS NOT NULL").setMaxRows(5);
- DataSet dataSet = strategy.executeQuery(q);
- assertEquals("[Name]", Arrays.toString(dataSet.getSelectItems()));
- assertTrue(dataSet.next());
- assertEquals("Row[values=[LL Crankarm]]", dataSet.getRow().toString());
- assertTrue(dataSet.next());
- assertTrue(dataSet.next());
- assertTrue(dataSet.next());
- assertTrue(dataSet.next());
- assertFalse(dataSet.next());
- }
-
- public void testGetSchemaNormalTableTypes() throws Exception {
- JdbcDataContext dc = new JdbcDataContext(_connection, new TableType[] { TableType.TABLE, TableType.VIEW },
- _databaseName);
- Schema[] schemas = dc.getSchemas();
-
- assertEquals(8, schemas.length);
- assertEquals("Schema[name=HumanResources]", schemas[0].toString());
- assertEquals(13, schemas[0].getTableCount());
- assertEquals("Schema[name=INFORMATION_SCHEMA]", schemas[1].toString());
- assertEquals(20, schemas[1].getTableCount());
- assertEquals("Schema[name=Person]", schemas[2].toString());
- assertEquals(8, schemas[2].getTableCount());
- assertEquals("Schema[name=Production]", schemas[3].toString());
- assertEquals(28, schemas[3].getTableCount());
- assertEquals("Schema[name=Purchasing]", schemas[4].toString());
- assertEquals(8, schemas[4].getTableCount());
- assertEquals("Schema[name=Sales]", schemas[5].toString());
- assertEquals(27, schemas[5].getTableCount());
-
- }
-
- public void testGetSchemaAllTableTypes() throws Exception {
- JdbcDataContext strategy = new JdbcDataContext(_connection, new TableType[] { TableType.OTHER,
- TableType.GLOBAL_TEMPORARY }, _databaseName);
-
- assertEquals("[Sales, HumanResources, dbo, Purchasing, sys, Production, INFORMATION_SCHEMA, Person]",
- Arrays.toString(strategy.getSchemaNames()));
-
- assertEquals("Schema[name=dbo]", strategy.getDefaultSchema().toString());
- }
-
- public void testQueryRewriterQuoteAliases() throws Exception {
- JdbcDataContext strategy = new JdbcDataContext(_connection, TableType.DEFAULT_TABLE_TYPES, _databaseName);
- IQueryRewriter queryRewriter = strategy.getQueryRewriter();
- assertSame(SQLServerQueryRewriter.class, queryRewriter.getClass());
-
- Schema schema = strategy.getSchemaByName("Sales");
- Table customersTable = schema.getTableByName("CUSTOMER");
-
- Query q = new Query().from(customersTable, "cus-tomers").select(
- new SelectItem(customersTable.getColumnByName("AccountNumber")).setAlias("c|o|d|e"));
- q.setMaxRows(5);
-
- assertEquals("SELECT cus-tomers.\"AccountNumber\" AS c|o|d|e FROM Sales.\"Customer\" cus-tomers", q.toString());
-
- String queryString = queryRewriter.rewriteQuery(q);
- assertEquals(
- "SELECT TOP 5 \"cus-tomers\".\"AccountNumber\" AS \"c|o|d|e\" FROM Sales.\"Customer\" \"cus-tomers\"",
- queryString);
-
- // We have to test that no additional quoting characters are added every
- // time we run the rewriting
- queryString = queryRewriter.rewriteQuery(q);
- queryString = queryRewriter.rewriteQuery(q);
- assertEquals(
- "SELECT TOP 5 \"cus-tomers\".\"AccountNumber\" AS \"c|o|d|e\" FROM Sales.\"Customer\" \"cus-tomers\"",
- queryString);
-
- // Test that the original query is still the same (ie. it has been
- // cloned for execution)
- assertEquals("SELECT cus-tomers.\"AccountNumber\" AS c|o|d|e FROM Sales.\"Customer\" cus-tomers", q.toString());
-
- DataSet data = strategy.executeQuery(q);
- assertNotNull(data);
- data.close();
- }
-
- public void testQuotedString() throws Exception {
- JdbcDataContext dc = new JdbcDataContext(_connection, TableType.DEFAULT_TABLE_TYPES, _databaseName);
- IQueryRewriter queryRewriter = dc.getQueryRewriter();
- assertSame(SQLServerQueryRewriter.class, queryRewriter.getClass());
-
- Query q = dc.query().from("Production", "Product").select("Name").where("Color").eq("R'ed").toQuery();
-
- assertEquals(
- "SELECT \"Product\".\"Name\" FROM Production.\"Product\" Product WHERE Product.\"Color\" = 'R''ed'",
- queryRewriter.rewriteQuery(q));
- }
-}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/276c173a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/AbstractJdbIntegrationTest.java
----------------------------------------------------------------------
diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/AbstractJdbIntegrationTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/AbstractJdbIntegrationTest.java
index 10fc2b9..6faec03 100644
--- a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/AbstractJdbIntegrationTest.java
+++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/AbstractJdbIntegrationTest.java
@@ -24,6 +24,7 @@ import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
+import org.apache.metamodel.jdbc.JdbcDataContext;
import org.apache.metamodel.util.FileHelper;
import junit.framework.TestCase;
@@ -113,6 +114,10 @@ public abstract class AbstractJdbIntegrationTest extends TestCase {
return _connection;
}
+
+ protected JdbcDataContext getDataContext() {
+ return new JdbcDataContext(getConnection());
+ }
private String getPropertyFilePath() {
String userHome = System.getProperty("user.home");
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/276c173a/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
new file mode 100644
index 0000000..6e28ee5
--- /dev/null
+++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/DB2Test.java
@@ -0,0 +1,118 @@
+/**
+ * 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.integrationtests;
+
+import java.util.Arrays;
+
+import org.apache.metamodel.data.DataSet;
+import org.apache.metamodel.jdbc.JdbcDataContext;
+import org.apache.metamodel.jdbc.JdbcTestTemplates;
+import org.apache.metamodel.query.Query;
+import org.apache.metamodel.schema.Schema;
+import org.apache.metamodel.schema.Table;
+
+/**
+ * DB2 integration test. This is a read-only integration test, meant to be
+ * modified for whatever server is available (even within Human Inference).
+ */
+public class DB2Test extends AbstractJdbIntegrationTest {
+
+ @Override
+ protected String getPropertyPrefix() {
+ return "db2";
+ }
+
+ public void testInterpretationOfNull() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ JdbcTestTemplates.interpretationOfNulls(getConnection());
+ }
+
+ public void testDefaultSchema() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ JdbcDataContext dc = new JdbcDataContext(getConnection());
+ Schema schema = dc.getDefaultSchema();
+ assertEquals(getUsername().toUpperCase(), schema.getName());
+
+ Table countryTable = schema.getTableByName("COUNTRY");
+ assertNotNull(countryTable);
+
+ DataSet ds = dc.query().from(countryTable).selectCount().execute();
+ assertTrue(ds.next());
+ assertEquals("Row[values=[1008]]", ds.getRow().toString());
+ assertFalse(ds.next());
+ ds.close();
+ }
+
+ public void testMaxRowsOnly() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ JdbcDataContext dc = new JdbcDataContext(getConnection());
+ Schema schema = dc.getDefaultSchema();
+ String[] tableNames = schema.getTableNames();
+ System.out.println("Tables: " + Arrays.toString(tableNames));
+
+ Table countryTable = schema.getTableByName("COUNTRY");
+ assertNotNull(countryTable);
+
+ Query query = dc.query().from(countryTable).select("COUNTRYCODE").limit(200).toQuery();
+ assertEquals("SELECT DB2INST1.\"COUNTRY\".\"COUNTRYCODE\" FROM DB2INST1.\"COUNTRY\" "
+ + "FETCH FIRST 200 ROWS ONLY", dc.getQueryRewriter().rewriteQuery(query));
+
+ DataSet ds = dc.executeQuery(query);
+ for (int i = 0; i < 200; i++) {
+ assertTrue(ds.next());
+ assertEquals(1, ds.getRow().getValues().length);
+ }
+ assertFalse(ds.next());
+ ds.close();
+ }
+
+ public void testMaxRowsAndOffset() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ JdbcDataContext dc = new JdbcDataContext(getConnection());
+ Schema schema = dc.getDefaultSchema();
+ String[] tableNames = schema.getTableNames();
+ System.out.println("Tables: " + Arrays.toString(tableNames));
+
+ Table countryTable = schema.getTableByName("COUNTRY");
+ assertNotNull(countryTable);
+
+ Query query = dc.query().from(countryTable).select("COUNTRYCODE").limit(200).offset(200).toQuery();
+ assertEquals(
+ "SELECT metamodel_subquery.\"COUNTRYCODE\" FROM ("
+ + "SELECT DB2INST1.\"COUNTRY\".\"COUNTRYCODE\", ROW_NUMBER() OVER() AS metamodel_row_number FROM DB2INST1.\"COUNTRY\""
+ + ") metamodel_subquery WHERE metamodel_row_number BETWEEN 201 AND 400", dc.getQueryRewriter()
+ .rewriteQuery(query));
+
+ DataSet ds = dc.executeQuery(query);
+ for (int i = 0; i < 200; i++) {
+ assertTrue(ds.next());
+ assertEquals(1, ds.getRow().getValues().length);
+ }
+ assertFalse(ds.next());
+ ds.close();
+ }
+}
http://git-wip-us.apache.org/repos/asf/incubator-metamodel/blob/276c173a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/FirebirdTest.java
----------------------------------------------------------------------
diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/FirebirdTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/FirebirdTest.java
new file mode 100644
index 0000000..f883e83
--- /dev/null
+++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/FirebirdTest.java
@@ -0,0 +1,121 @@
+/**
+ * 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.integrationtests;
+
+import java.text.SimpleDateFormat;
+import java.util.Arrays;
+import java.util.Date;
+
+import javax.swing.table.TableModel;
+
+import org.apache.metamodel.data.DataSet;
+import org.apache.metamodel.data.DataSetTableModel;
+import org.apache.metamodel.jdbc.JdbcDataContext;
+import org.apache.metamodel.query.FromItem;
+import org.apache.metamodel.query.JoinType;
+import org.apache.metamodel.query.Query;
+import org.apache.metamodel.query.SelectItem;
+import org.apache.metamodel.schema.Schema;
+import org.apache.metamodel.schema.Table;
+
+/**
+ * Integrationtests for Firebird SQL.
+ *
+ * This test uses the "employee" sampledata shipped with Firebird. The JDBC
+ * driver ("jaybird") is not available in the Maven repository so you will have
+ * to download and attach it to the eclipse project yourself.
+ *
+ * @see http://www.firebirdsql.org/manual/qsg10-connecting.html
+ * @see http://www.firebirdsql.org/index.php?op=files&id=jaybird
+ */
+public class FirebirdTest extends AbstractJdbIntegrationTest {
+
+ @Override
+ protected String getPropertyPrefix() {
+ return "firebird";
+ }
+
+ public void testGetSchemas() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ JdbcDataContext dataContext = getDataContext();
+ Schema[] schemas = dataContext.getSchemas();
+ assertEquals(1, schemas.length);
+ Schema schema = dataContext.getDefaultSchema();
+ assertEquals("{JdbcTable[name=COUNTRY,type=TABLE,remarks=<null>],"
+ + "JdbcTable[name=CUSTOMER,type=TABLE,remarks=<null>],"
+ + "JdbcTable[name=DEPARTMENT,type=TABLE,remarks=<null>],"
+ + "JdbcTable[name=EMPLOYEE,type=TABLE,remarks=<null>],"
+ + "JdbcTable[name=EMPLOYEE_PROJECT,type=TABLE,remarks=<null>],"
+ + "JdbcTable[name=JOB,type=TABLE,remarks=<null>],"
+ + "JdbcTable[name=PHONE_LIST,type=VIEW,remarks=<null>],"
+ + "JdbcTable[name=PROJECT,type=TABLE,remarks=<null>],"
+ + "JdbcTable[name=PROJ_DEPT_BUDGET,type=TABLE,remarks=<null>],"
+ + "JdbcTable[name=SALARY_HISTORY,type=TABLE,remarks=<null>],"
+ + "JdbcTable[name=SALES,type=TABLE,remarks=<null>]}", Arrays.toString(schema.getTables()));
+
+ assertEquals(
+ "{Relationship[primaryTable=COUNTRY,primaryColumns={COUNTRY},foreignTable=CUSTOMER,foreignColumns={COUNTRY}],"
+ + "Relationship[primaryTable=COUNTRY,primaryColumns={COUNTRY},foreignTable=JOB,foreignColumns={JOB_COUNTRY}],"
+ + "Relationship[primaryTable=CUSTOMER,primaryColumns={CUST_NO},foreignTable=SALES,foreignColumns={CUST_NO}],"
+ + "Relationship[primaryTable=DEPARTMENT,primaryColumns={DEPT_NO},foreignTable=DEPARTMENT,foreignColumns={HEAD_DEPT}],"
+ + "Relationship[primaryTable=DEPARTMENT,primaryColumns={DEPT_NO},foreignTable=EMPLOYEE,foreignColumns={DEPT_NO}],"
+ + "Relationship[primaryTable=DEPARTMENT,primaryColumns={DEPT_NO},foreignTable=PROJ_DEPT_BUDGET,foreignColumns={DEPT_NO}],"
+ + "Relationship[primaryTable=EMPLOYEE,primaryColumns={EMP_NO},foreignTable=DEPARTMENT,foreignColumns={MNGR_NO}],"
+ + "Relationship[primaryTable=EMPLOYEE,primaryColumns={EMP_NO},foreignTable=EMPLOYEE_PROJECT,foreignColumns={EMP_NO}],"
+ + "Relationship[primaryTable=EMPLOYEE,primaryColumns={EMP_NO},foreignTable=PROJECT,foreignColumns={TEAM_LEADER}],"
+ + "Relationship[primaryTable=EMPLOYEE,primaryColumns={EMP_NO},foreignTable=SALARY_HISTORY,foreignColumns={EMP_NO}],"
+ + "Relationship[primaryTable=EMPLOYEE,primaryColumns={EMP_NO},foreignTable=SALES,foreignColumns={SALES_REP}],"
+ + "Relationship[primaryTable=JOB,primaryColumns={JOB_CODE},foreignTable=EMPLOYEE,foreignColumns={JOB_CODE}],"
+ + "Relationship[primaryTable=JOB,primaryColumns={JOB_GRADE},foreignTable=EMPLOYEE,foreignColumns={JOB_GRADE}],"
+ + "Relationship[primaryTable=JOB,primaryColumns={JOB_COUNTRY},foreignTable=EMPLOYEE,foreignColumns={JOB_COUNTRY}],"
+ + "Relationship[primaryTable=PROJECT,primaryColumns={PROJ_ID},foreignTable=EMPLOYEE_PROJECT,foreignColumns={PROJ_ID}],"
+ + "Relationship[primaryTable=PROJECT,primaryColumns={PROJ_ID},foreignTable=PROJ_DEPT_BUDGET,foreignColumns={PROJ_ID}]}",
+ Arrays.toString(schema.getRelationships()));
+ }
+
+ public void testExecuteQuery() throws Exception {
+ if (!isConfigured()) {
+ return;
+ }
+ JdbcDataContext dataContext = getDataContext();
+ Schema schema = dataContext.getDefaultSchema();
+ Table departmentTable = schema.getTableByName("DEPARTMENT");
+ Table employeeTable = schema.getTableByName("EMPLOYEE");
+ Query q = new Query().from(new FromItem(JoinType.INNER, departmentTable.getRelationships(employeeTable)[0]));
+ q.select(departmentTable.getColumns()[1]);
+ q.select(new SelectItem(employeeTable.getColumns()[4]).setAlias("hire-date"));
+ assertEquals(
+ "SELECT \"DEPARTMENT\".\"DEPARTMENT\", \"EMPLOYEE\".\"HIRE_DATE\" AS hire-date FROM \"EMPLOYEE\" INNER JOIN \"DEPARTMENT\" ON \"EMPLOYEE\".\"EMP_NO\" = \"DEPARTMENT\".\"MNGR_NO\"",
+ q.toString());
+
+ DataSet data = dataContext.executeQuery(q);
+ assertNotNull(data);
+
+ TableModel tableModel = new DataSetTableModel(data);
+ assertEquals(2, tableModel.getColumnCount());
+ assertEquals(17, tableModel.getRowCount());
+ assertEquals("Quality Assurance", tableModel.getValueAt(4, 0).toString());
+
+ Date date = (Date) tableModel.getValueAt(4, 1);
+ assertEquals("1989-04-17 00:00:00.000000", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSSSS").format(date));
+
+ }
+}
\ No newline at end of file