Repository: metamodel Updated Branches: refs/heads/master 1b4cd55cf -> 1bc0887ef
METAMODEL-131: Fixed Fixes #14 Project: http://git-wip-us.apache.org/repos/asf/metamodel/repo Commit: http://git-wip-us.apache.org/repos/asf/metamodel/commit/1bc0887e Tree: http://git-wip-us.apache.org/repos/asf/metamodel/tree/1bc0887e Diff: http://git-wip-us.apache.org/repos/asf/metamodel/diff/1bc0887e Branch: refs/heads/master Commit: 1bc0887ef3853d11903259133d45e4acce10213c Parents: 1b4cd55 Author: Hosur Narahari <hnr1992@@gmail.com> Authored: Thu Apr 9 10:59:15 2015 +0200 Committer: Kasper Sørensen <i.am.kasper.soren...@gmail.com> Committed: Thu Apr 9 10:59:15 2015 +0200 ---------------------------------------------------------------------- CHANGES.md | 1 + .../create/AbstractTableCreationBuilder.java | 19 +- .../create/AbstractCreateTableBuilderTest.java | 3 +- .../metamodel/jdbc/JdbcCreateTableBuilder.java | 19 +- .../org/apache/metamodel/jdbc/DerbyTest.java | 14 +- .../apache/metamodel/jdbc/H2databaseTest.java | 12 +- .../org/apache/metamodel/jdbc/HsqldbTest.java | 91 ++- .../metamodel/jdbc/JdbcTestTemplates.java | 319 ++++++---- .../org/apache/metamodel/jdbc/SqliteTest.java | 13 +- .../jdbc/integrationtests/DB2Test.java | 18 +- .../jdbc/integrationtests/MysqlTest.java | 608 ++++++++++--------- .../jdbc/integrationtests/OracleTest.java | 17 + .../jdbc/integrationtests/PostgresqlTest.java | 26 + .../SQLServerJtdsDriverTest.java | 16 + 14 files changed, 737 insertions(+), 439 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/metamodel/blob/1bc0887e/CHANGES.md ---------------------------------------------------------------------- diff --git a/CHANGES.md b/CHANGES.md index 1b4db2e..627eb5f 100644 --- a/CHANGES.md +++ b/CHANGES.md @@ -5,6 +5,7 @@ * [METAMODEL-124] - Invoked ElasticSearch cross-version incompatible methods via reflection * [METAMODEL-125] - Added support for comma-separated select items in Query.select(String) method argument. * [METAMODEL-128] - Fixed bug in DataSet ordering when aggregation functions are applied to non-JDBC modules. + * [METAMODEL-131] - Added support for composite primary keys in JDBC CREATE TABLE statements. ### Apache MetaModel 4.3.2 http://git-wip-us.apache.org/repos/asf/metamodel/blob/1bc0887e/core/src/main/java/org/apache/metamodel/create/AbstractTableCreationBuilder.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/metamodel/create/AbstractTableCreationBuilder.java b/core/src/main/java/org/apache/metamodel/create/AbstractTableCreationBuilder.java index 561eb6d..401be9f 100644 --- a/core/src/main/java/org/apache/metamodel/create/AbstractTableCreationBuilder.java +++ b/core/src/main/java/org/apache/metamodel/create/AbstractTableCreationBuilder.java @@ -123,9 +123,22 @@ public abstract class AbstractTableCreationBuilder<U extends UpdateCallback> imp && !column.isNullable().booleanValue()) { sb.append(" NOT NULL"); } - if (column.isPrimaryKey()) { - sb.append(" PRIMARY KEY"); - } + } + boolean primaryKeyExists = false; + for(int i = 0 ; i < columns.length ; i++) { + if(columns[i].isPrimaryKey()) { + if(!primaryKeyExists) { + sb.append(", PRIMARY KEY("); + sb.append(columns[i].getName()); + primaryKeyExists = true; + } else { + sb.append(","); + sb.append(columns[i].getName()); + } + } + } + if(primaryKeyExists) { + sb.append(")"); } sb.append(")"); return sb.toString(); http://git-wip-us.apache.org/repos/asf/metamodel/blob/1bc0887e/core/src/test/java/org/apache/metamodel/create/AbstractCreateTableBuilderTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/metamodel/create/AbstractCreateTableBuilderTest.java b/core/src/test/java/org/apache/metamodel/create/AbstractCreateTableBuilderTest.java index 1884a41..c4d320c 100644 --- a/core/src/test/java/org/apache/metamodel/create/AbstractCreateTableBuilderTest.java +++ b/core/src/test/java/org/apache/metamodel/create/AbstractCreateTableBuilderTest.java @@ -63,7 +63,8 @@ public class AbstractCreateTableBuilderTest extends TestCase { assertEquals("Column[name=baz,columnNumber=2,type=null,nullable=false,nativeType=null,columnSize=null]", table.getColumns()[2].toString()); - assertEquals("CREATE TABLE schema.tablename (foo VARCHAR(1234) PRIMARY KEY,bar,baz NOT NULL)", builder.toSql()); + System.out.println(builder.toSql()); + assertEquals("CREATE TABLE schema.tablename (foo VARCHAR(1234),bar,baz NOT NULL, PRIMARY KEY(foo))", builder.toSql()); } public void testLike() throws Exception { http://git-wip-us.apache.org/repos/asf/metamodel/blob/1bc0887e/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcCreateTableBuilder.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcCreateTableBuilder.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcCreateTableBuilder.java index 4d19bb9..c0a5999 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcCreateTableBuilder.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcCreateTableBuilder.java @@ -114,10 +114,23 @@ final class JdbcCreateTableBuilder extends AbstractTableCreationBuilder<JdbcUpda if (column.isNullable() != null && !column.isNullable().booleanValue()) { sb.append(" NOT NULL"); } - if (column.isPrimaryKey()) { - sb.append(" PRIMARY KEY"); - } } + boolean primaryKeyExists = false; + for(int i = 0 ; i < columns.length ; i++) { + if(columns[i].isPrimaryKey()) { + if(!primaryKeyExists) { + sb.append(", PRIMARY KEY("); + sb.append(columns[i].getName()); + primaryKeyExists = true; + } else { + sb.append(","); + sb.append(columns[i].getName()); + } + } + } + if(primaryKeyExists) { + sb.append(")"); + } sb.append(")"); return sb.toString(); } http://git-wip-us.apache.org/repos/asf/metamodel/blob/1bc0887e/jdbc/src/test/java/org/apache/metamodel/jdbc/DerbyTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/DerbyTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/DerbyTest.java index 3feb3f6..27e76a9 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/DerbyTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/DerbyTest.java @@ -75,6 +75,18 @@ public class DerbyTest extends TestCase { } } + public void testCreateInsertAndUpdate() throws Exception { + Connection conn = DriverManager.getConnection("jdbc:derby:target/temp_derby;create=true"); + JdbcDataContext dc = new JdbcDataContext(conn); + JdbcTestTemplates.simpleCreateInsertUpdateAndDrop(dc, "metamodel_test_simple"); + } + + public void testCompositePrimaryKeyCreation() throws Exception { + Connection conn = DriverManager.getConnection("jdbc:derby:target/temp_derby;create=true"); + JdbcDataContext dc = new JdbcDataContext(conn); + JdbcTestTemplates.compositeKeyCreation(dc, "metamodel_test_composite_keys"); + } + public void testDifferentOperators() throws Exception { Connection conn = DriverManager.getConnection("jdbc:derby:target/temp_derby;create=true"); @@ -291,7 +303,7 @@ public class DerbyTest extends TestCase { .ofType(ColumnType.INTEGER).execute(); writtenTableRef.set(writtenTable); String sql = createTableBuilder.createSqlStatement(); - assertEquals("CREATE TABLE APP.test_table (id INTEGER PRIMARY KEY, name VARCHAR(255), age INTEGER)", + assertEquals("CREATE TABLE APP.test_table (id INTEGER, name VARCHAR(255), age INTEGER, PRIMARY KEY(id))", sql.replaceAll("\"", "|")); assertNotNull(writtenTable); } http://git-wip-us.apache.org/repos/asf/metamodel/blob/1bc0887e/jdbc/src/test/java/org/apache/metamodel/jdbc/H2databaseTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/H2databaseTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/H2databaseTest.java index bfe6efb..118864c 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/H2databaseTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/H2databaseTest.java @@ -70,6 +70,16 @@ public class H2databaseTest extends TestCase { super.tearDown(); conn.close(); } + + public void testCreateInsertAndUpdate() throws Exception { + JdbcDataContext dc = new JdbcDataContext(conn); + JdbcTestTemplates.simpleCreateInsertUpdateAndDrop(dc, "metamodel_test_simple"); + } + + public void testCompositePrimaryKeyCreation() throws Exception { + JdbcDataContext dc = new JdbcDataContext(conn); + JdbcTestTemplates.compositeKeyCreation(dc, "metamodel_test_composite_keys"); + } public void testUsingSingleUpdates() throws Exception { final JdbcDataContext dc = new JdbcDataContext(conn); @@ -263,7 +273,7 @@ public class H2databaseTest extends TestCase { .withColumn("name").ofSize(255).ofType(ColumnType.VARCHAR).withColumn("age").ofType(ColumnType.INTEGER) .execute(); String sql = createTableBuilder.createSqlStatement(); - assertEquals("CREATE TABLE PUBLIC.test_table (id INTEGER PRIMARY KEY, name VARCHAR(255), age INTEGER)", sql); + assertEquals("CREATE TABLE PUBLIC.test_table (id INTEGER, name VARCHAR(255), age INTEGER, PRIMARY KEY(id))", sql); assertNotNull(writtenTable); assertEquals("[ID, NAME, AGE]", Arrays.toString(writtenTable.getColumnNames())); http://git-wip-us.apache.org/repos/asf/metamodel/blob/1bc0887e/jdbc/src/test/java/org/apache/metamodel/jdbc/HsqldbTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/HsqldbTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/HsqldbTest.java index f225526..1d69d6f 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/HsqldbTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/HsqldbTest.java @@ -71,6 +71,18 @@ public class HsqldbTest extends TestCase { _connection.close(); } + public void testCreateInsertAndUpdate() throws Exception { + Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:" + getName(), USERNAME, PASSWORD); + JdbcDataContext dc = new JdbcDataContext(connection); + JdbcTestTemplates.simpleCreateInsertUpdateAndDrop(dc, "metamodel_test_simple"); + } + + public void testCompositePrimaryKeyCreation() throws Exception { + Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:" + getName(), USERNAME, PASSWORD); + JdbcDataContext dc = new JdbcDataContext(connection); + JdbcTestTemplates.compositeKeyCreation(dc, "metamodel_test_composite_keys"); + } + public void testGetSchemas() throws Exception { assertNotNull(_connection); JdbcDataContext dc = new JdbcDataContext(_connection); @@ -81,26 +93,31 @@ public class HsqldbTest extends TestCase { assertSame(defaultSchema, publicSchema); Table[] tables = publicSchema.getTables(); assertEquals(13, tables.length); - assertEquals("[Table[name=CUSTOMERS,type=TABLE,remarks=null], " + "Table[name=CUSTOMER_W_TER,type=TABLE,remarks=null], " - + "Table[name=DEPARTMENT_MANAGERS,type=TABLE,remarks=null], " + "Table[name=DIM_TIME,type=TABLE,remarks=null], " - + "Table[name=EMPLOYEES,type=TABLE,remarks=null], " + "Table[name=OFFICES,type=TABLE,remarks=null], " - + "Table[name=ORDERDETAILS,type=TABLE,remarks=null], " + "Table[name=ORDERFACT,type=TABLE,remarks=null], " - + "Table[name=ORDERS,type=TABLE,remarks=null], " + "Table[name=PAYMENTS,type=TABLE,remarks=null], " - + "Table[name=PRODUCTS,type=TABLE,remarks=null], " + "Table[name=QUADRANT_ACTUALS,type=TABLE,remarks=null], " + assertEquals("[Table[name=CUSTOMERS,type=TABLE,remarks=null], " + + "Table[name=CUSTOMER_W_TER,type=TABLE,remarks=null], " + + "Table[name=DEPARTMENT_MANAGERS,type=TABLE,remarks=null], " + + "Table[name=DIM_TIME,type=TABLE,remarks=null], " + "Table[name=EMPLOYEES,type=TABLE,remarks=null], " + + "Table[name=OFFICES,type=TABLE,remarks=null], " + + "Table[name=ORDERDETAILS,type=TABLE,remarks=null], " + + "Table[name=ORDERFACT,type=TABLE,remarks=null], " + "Table[name=ORDERS,type=TABLE,remarks=null], " + + "Table[name=PAYMENTS,type=TABLE,remarks=null], " + "Table[name=PRODUCTS,type=TABLE,remarks=null], " + + "Table[name=QUADRANT_ACTUALS,type=TABLE,remarks=null], " + "Table[name=TRIAL_BALANCE,type=TABLE,remarks=null]]", Arrays.toString(tables)); Table empTable = publicSchema.getTableByName("EMPLOYEES"); - assertEquals("[Column[name=EMPLOYEENUMBER,columnNumber=0,type=INTEGER,nullable=false,nativeType=INTEGER,columnSize=0], " - + "Column[name=LASTNAME,columnNumber=1,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=50], " - + "Column[name=FIRSTNAME,columnNumber=2,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=50], " - + "Column[name=EXTENSION,columnNumber=3,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=10], " - + "Column[name=EMAIL,columnNumber=4,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=100], " - + "Column[name=OFFICECODE,columnNumber=5,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=20], " - + "Column[name=REPORTSTO,columnNumber=6,type=INTEGER,nullable=true,nativeType=INTEGER,columnSize=0], " - + "Column[name=JOBTITLE,columnNumber=7,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=50]]", + assertEquals( + "[Column[name=EMPLOYEENUMBER,columnNumber=0,type=INTEGER,nullable=false,nativeType=INTEGER,columnSize=0], " + + "Column[name=LASTNAME,columnNumber=1,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=50], " + + "Column[name=FIRSTNAME,columnNumber=2,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=50], " + + "Column[name=EXTENSION,columnNumber=3,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=10], " + + "Column[name=EMAIL,columnNumber=4,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=100], " + + "Column[name=OFFICECODE,columnNumber=5,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=20], " + + "Column[name=REPORTSTO,columnNumber=6,type=INTEGER,nullable=true,nativeType=INTEGER,columnSize=0], " + + "Column[name=JOBTITLE,columnNumber=7,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=50]]", Arrays.toString(empTable.getColumns())); - assertEquals("[Column[name=EMPLOYEENUMBER,columnNumber=0,type=INTEGER,nullable=false,nativeType=INTEGER,columnSize=0]]", + assertEquals( + "[Column[name=EMPLOYEENUMBER,columnNumber=0,type=INTEGER,nullable=false,nativeType=INTEGER,columnSize=0]]", Arrays.toString(empTable.getPrimaryKeys())); // Only a single relationship registered in the database @@ -127,7 +144,8 @@ public class HsqldbTest extends TestCase { assertEquals(2, tableModel.getColumnCount()); assertEquals(2996, tableModel.getRowCount()); - assertEquals(110, MetaModelHelper.executeSingleRowQuery(dc, new Query().selectCount().from(productsTable)).getValue(0)); + assertEquals(110, MetaModelHelper.executeSingleRowQuery(dc, new Query().selectCount().from(productsTable)) + .getValue(0)); } public void testLimit() throws Exception { @@ -190,14 +208,16 @@ public class HsqldbTest extends TestCase { assertEquals("SELECT pro-ducts.\"PRODUCTCODE\" AS c|o|d|e FROM PUBLIC.\"PRODUCTS\" pro-ducts", q.toString()); String queryString = queryRewriter.rewriteQuery(q); - assertEquals("SELECT TOP 5 \"pro-ducts\".\"PRODUCTCODE\" AS \"c|o|d|e\" FROM PUBLIC.\"PRODUCTS\" \"pro-ducts\"", + assertEquals( + "SELECT TOP 5 \"pro-ducts\".\"PRODUCTCODE\" AS \"c|o|d|e\" FROM PUBLIC.\"PRODUCTS\" \"pro-ducts\"", 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 \"pro-ducts\".\"PRODUCTCODE\" AS \"c|o|d|e\" FROM PUBLIC.\"PRODUCTS\" \"pro-ducts\"", + assertEquals( + "SELECT TOP 5 \"pro-ducts\".\"PRODUCTCODE\" AS \"c|o|d|e\" FROM PUBLIC.\"PRODUCTS\" \"pro-ducts\"", queryString); // Test that the original query is still the same (ie. it has been @@ -214,12 +234,15 @@ public class HsqldbTest extends TestCase { Column column = dc.getDefaultSchema().getTableByName("PRODUCTS").getColumnByName("PRODUCTCODE"); assertEquals("PUBLIC.PRODUCTS.PRODUCTCODE", column.getQualifiedLabel()); - assertEquals("Table[name=PRODUCTS,type=TABLE,remarks=null]", dc.getTableByQualifiedLabel("PUBLIC.PRODUCTS").toString()); + assertEquals("Table[name=PRODUCTS,type=TABLE,remarks=null]", dc.getTableByQualifiedLabel("PUBLIC.PRODUCTS") + .toString()); assertEquals("Table[name=PRODUCTS,type=TABLE,remarks=null]", dc.getTableByQualifiedLabel("PRODUCTS").toString()); - assertEquals("Column[name=PRODUCTCODE,columnNumber=0,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=50]", dc - .getColumnByQualifiedLabel("PUBLIC.PRODUCTS.PRODUCTCODE").toString()); - assertEquals("Column[name=PRODUCTCODE,columnNumber=0,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=50]", dc - .getColumnByQualifiedLabel("PRODUCTS.PRODUCTCODE").toString()); + assertEquals( + "Column[name=PRODUCTCODE,columnNumber=0,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=50]", + dc.getColumnByQualifiedLabel("PUBLIC.PRODUCTS.PRODUCTCODE").toString()); + assertEquals( + "Column[name=PRODUCTCODE,columnNumber=0,type=VARCHAR,nullable=false,nativeType=VARCHAR,columnSize=50]", + dc.getColumnByQualifiedLabel("PRODUCTS.PRODUCTCODE").toString()); } public void testQuoteInWhereClause() throws Exception { @@ -256,8 +279,8 @@ public class HsqldbTest extends TestCase { q = dc.query().from(table).selectCount().where("name").isEquals("m'jello").toQuery(); assertEquals("SELECT COUNT(*) FROM PUBLIC.\"TESTTABLE\" WHERE \"TESTTABLE\".\"NAME\" = 'm'jello'", q.toSql()); - assertEquals("SELECT COUNT(*) FROM PUBLIC.\"TESTTABLE\" WHERE \"TESTTABLE\".\"NAME\" = 'm''jello'", dc.getQueryRewriter() - .rewriteQuery(q)); + assertEquals("SELECT COUNT(*) FROM PUBLIC.\"TESTTABLE\" WHERE \"TESTTABLE\".\"NAME\" = 'm''jello'", dc + .getQueryRewriter().rewriteQuery(q)); row = MetaModelHelper.executeSingleRowQuery(dc, q); assertEquals(1, ((Number) row.getValue(0)).intValue()); @@ -298,7 +321,8 @@ public class HsqldbTest extends TestCase { } public void testInsertOfDifferentTypes() throws Exception { - Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:different_types_insert", USERNAME, PASSWORD); + Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:different_types_insert", USERNAME, + PASSWORD); try { connection.createStatement().execute("DROP TABLE my_table"); @@ -313,9 +337,9 @@ public class HsqldbTest extends TestCase { @Override public void run(UpdateCallback cb) { Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER) - .ofNativeType("IDENTITY").nullable(false).withColumn("name").ofType(ColumnType.VARCHAR).ofSize(10) - .withColumn("foo").ofType(ColumnType.BOOLEAN).nullable(true).withColumn("bar").ofType(ColumnType.BOOLEAN) - .nullable(true).execute(); + .ofNativeType("IDENTITY").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()); } @@ -335,11 +359,14 @@ public class HsqldbTest extends TestCase { 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 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 7").value("foo", true).value("bar", true) + .execute(); - callback.insertInto("my_table").value("name", "row 8").value("foo", false).value("bar", false).execute(); + callback.insertInto("my_table").value("name", "row 8").value("foo", false).value("bar", false) + .execute(); } }); http://git-wip-us.apache.org/repos/asf/metamodel/blob/1bc0887e/jdbc/src/test/java/org/apache/metamodel/jdbc/JdbcTestTemplates.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/JdbcTestTemplates.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/JdbcTestTemplates.java index 0e24d21..ae2d3c9 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/JdbcTestTemplates.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/JdbcTestTemplates.java @@ -38,13 +38,16 @@ import org.apache.metamodel.BatchUpdateScript; import org.apache.metamodel.UpdateCallback; import org.apache.metamodel.UpdateScript; import org.apache.metamodel.create.ColumnCreationBuilder; +import org.apache.metamodel.create.CreateTable; import org.apache.metamodel.data.DataSet; import org.apache.metamodel.data.Row; import org.apache.metamodel.drop.DropTable; 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.update.Update; import org.apache.metamodel.util.DateUtils; import org.apache.metamodel.util.Month; import org.junit.Ignore; @@ -63,41 +66,45 @@ public class JdbcTestTemplates { dc.executeUpdate(new DropTable(schema, "test_table")); } - dc.executeUpdate(new UpdateScript() { - @Override - public void run(UpdateCallback cb) { - ColumnCreationBuilder createTableBuilder = cb.createTable(schema, "test_table").withColumn("id") - .ofType(ColumnType.FLOAT).withColumn("code").ofType(ColumnType.VARCHAR).ofSize(10); - Table table = createTableBuilder.execute(); - - cb.insertInto(table).value("id", 1.0).value("code", "C01").execute(); - cb.insertInto(table).value("id", 2.0).value("code", "C02").execute(); - cb.insertInto(table).value("id", 3.0).value("code", null).execute(); - cb.insertInto(table).value("id", 4.0).value("code", "C02").execute(); - } - }); - - assertEquals(1, getCount(dc.query().from("test_table").selectCount().where("code").isNull().execute())); - assertEquals(3, getCount(dc.query().from("test_table").selectCount().where("code").isNotNull().execute())); - assertEquals(2, getCount(dc.query().from("test_table").selectCount().where("code").ne("C02").execute())); - - // we put the results into a map, because databases are not in agreement - // wrt. if NULL is greater than or less than other values, so ordering - // does not help final Map<Object, Object> map = new HashMap<Object, Object>(); + try { + dc.executeUpdate(new UpdateScript() { + @Override + public void run(UpdateCallback cb) { + ColumnCreationBuilder createTableBuilder = cb.createTable(schema, "test_table").withColumn("id") + .ofType(ColumnType.FLOAT).withColumn("code").ofType(ColumnType.VARCHAR).ofSize(10); + Table table = createTableBuilder.execute(); + + cb.insertInto(table).value("id", 1.0).value("code", "C01").execute(); + cb.insertInto(table).value("id", 2.0).value("code", "C02").execute(); + cb.insertInto(table).value("id", 3.0).value("code", null).execute(); + cb.insertInto(table).value("id", 4.0).value("code", "C02").execute(); + } + }); - DataSet ds = dc.query().from("test_table").select("code").selectCount().groupBy("code").execute(); - assertTrue(ds.next()); - map.put(ds.getRow().getValue(0), ds.getRow().getValue(1)); - assertTrue(ds.next()); - map.put(ds.getRow().getValue(0), ds.getRow().getValue(1)); - assertTrue(ds.next()); - map.put(ds.getRow().getValue(0), ds.getRow().getValue(1)); - assertFalse(ds.next()); - - ds.close(); - - dc.executeUpdate(new DropTable(schema, "test_table")); + assertEquals(1, getCount(dc.query().from("test_table").selectCount().where("code").isNull().execute())); + assertEquals(3, getCount(dc.query().from("test_table").selectCount().where("code").isNotNull().execute())); + assertEquals(2, getCount(dc.query().from("test_table").selectCount().where("code").ne("C02").execute())); + + // we put the results into a map, because databases are not in + // agreement + // wrt. if NULL is greater than or less than other values, so + // ordering + // does not help + + DataSet ds = dc.query().from("test_table").select("code").selectCount().groupBy("code").execute(); + assertTrue(ds.next()); + map.put(ds.getRow().getValue(0), ds.getRow().getValue(1)); + assertTrue(ds.next()); + map.put(ds.getRow().getValue(0), ds.getRow().getValue(1)); + assertTrue(ds.next()); + map.put(ds.getRow().getValue(0), ds.getRow().getValue(1)); + assertFalse(ds.next()); + + ds.close(); + } finally { + dc.executeUpdate(new DropTable(schema, "test_table")); + } assertEquals(1, ((Number) map.get(null)).intValue()); assertEquals(1, ((Number) map.get("C01")).intValue()); @@ -375,85 +382,89 @@ public class JdbcTestTemplates { } }); - dc.executeUpdate(new UpdateScript() { - @Override - public void run(UpdateCallback cb) { - cb.insertInto(schema.getTableByName(tableName)).value("id", 3).value("birthdate", "2011-12-21") - .value("wakemeup", "12:00").execute(); - } - }); - - DataSet ds = dc.query().from(schema.getTableByName(tableName)).select("id", "birthdate", "wakemeup") - .orderBy("id").execute(); - assertTrue(ds.next()); - assertEquals("1", ds.getRow().getValue(0).toString()); - assertEquals("1982-04-20", ds.getRow().getValue(1).toString()); - assertTrue("Actual value was: " + ds.getRow().getValue(2), - ds.getRow().getValue(2).toString().startsWith("07:55:00")); - - assertTrue(ds.next()); - assertEquals("2", ds.getRow().getValue(0).toString()); - assertEquals("1982-04-21", ds.getRow().getValue(1).toString()); - assertTrue("Actual value was: " + ds.getRow().getValue(2), - ds.getRow().getValue(2).toString().startsWith("18:35:00")); - - assertTrue(ds.next()); - assertEquals("3", ds.getRow().getValue(0).toString()); - assertEquals("2011-12-21", ds.getRow().getValue(1).toString()); - assertTrue("Actual value was: " + ds.getRow().getValue(2), - ds.getRow().getValue(2).toString().startsWith("12:00")); - - assertFalse(ds.next()); - ds.close(); - - dc.executeUpdate(new UpdateScript() { - @Override - public void run(UpdateCallback callback) { - // update record 1 - - // create a 08:00 time. - Calendar cal = Calendar.getInstance(); - cal.setTimeInMillis(0); - cal.set(Calendar.HOUR_OF_DAY, 8); - cal.set(Calendar.MINUTE, 00); - Date wakeUpTime = cal.getTime(); - - callback.update(schema.getTableByName(tableName)) - .value("birthdate", DateUtils.get(1982, Month.APRIL, 21)).value("wakemeup", wakeUpTime) - .where("birthdate").isEquals(DateUtils.get(1982, Month.APRIL, 20)).execute(); - } - }); - - ds = dc.query().from(schema.getTableByName(tableName)).select("id", "birthdate", "wakemeup").orderBy("id") - .execute(); - assertTrue(ds.next()); - assertEquals("1", ds.getRow().getValue(0).toString()); - assertEquals("1982-04-21", ds.getRow().getValue(1).toString()); - assertTrue("Actual value was: " + ds.getRow().getValue(2), - ds.getRow().getValue(2).toString().startsWith("08:00:00")); - - assertTrue(ds.next()); - assertEquals("2", ds.getRow().getValue(0).toString()); - assertEquals("1982-04-21", ds.getRow().getValue(1).toString()); - assertTrue("Actual value was: " + ds.getRow().getValue(2), - ds.getRow().getValue(2).toString().startsWith("18:35:00")); + try { - assertTrue(ds.next()); - assertEquals("3", ds.getRow().getValue(0).toString()); - assertEquals("2011-12-21", ds.getRow().getValue(1).toString()); - assertTrue("Actual value was: " + ds.getRow().getValue(2), - ds.getRow().getValue(2).toString().startsWith("12:00")); + dc.executeUpdate(new UpdateScript() { + @Override + public void run(UpdateCallback cb) { + cb.insertInto(schema.getTableByName(tableName)).value("id", 3).value("birthdate", "2011-12-21") + .value("wakemeup", "12:00").execute(); + } + }); - assertFalse(ds.next()); - ds.close(); + DataSet ds = dc.query().from(schema.getTableByName(tableName)).select("id", "birthdate", "wakemeup") + .orderBy("id").execute(); + assertTrue(ds.next()); + assertEquals("1", ds.getRow().getValue(0).toString()); + assertEquals("1982-04-20", ds.getRow().getValue(1).toString()); + assertTrue("Actual value was: " + ds.getRow().getValue(2), + ds.getRow().getValue(2).toString().startsWith("07:55:00")); + + assertTrue(ds.next()); + assertEquals("2", ds.getRow().getValue(0).toString()); + assertEquals("1982-04-21", ds.getRow().getValue(1).toString()); + assertTrue("Actual value was: " + ds.getRow().getValue(2), + ds.getRow().getValue(2).toString().startsWith("18:35:00")); + + assertTrue(ds.next()); + assertEquals("3", ds.getRow().getValue(0).toString()); + assertEquals("2011-12-21", ds.getRow().getValue(1).toString()); + assertTrue("Actual value was: " + ds.getRow().getValue(2), + ds.getRow().getValue(2).toString().startsWith("12:00")); + + assertFalse(ds.next()); + ds.close(); - if (schema.getTableByName(tableName) != null) { dc.executeUpdate(new UpdateScript() { @Override public void run(UpdateCallback callback) { - callback.dropTable(schema.getTableByName(tableName)).execute(); + // update record 1 + + // create a 08:00 time. + Calendar cal = Calendar.getInstance(); + cal.setTimeInMillis(0); + cal.set(Calendar.HOUR_OF_DAY, 8); + cal.set(Calendar.MINUTE, 00); + Date wakeUpTime = cal.getTime(); + + callback.update(schema.getTableByName(tableName)) + .value("birthdate", DateUtils.get(1982, Month.APRIL, 21)).value("wakemeup", wakeUpTime) + .where("birthdate").isEquals(DateUtils.get(1982, Month.APRIL, 20)).execute(); } }); + + ds = dc.query().from(schema.getTableByName(tableName)).select("id", "birthdate", "wakemeup").orderBy("id") + .execute(); + assertTrue(ds.next()); + assertEquals("1", ds.getRow().getValue(0).toString()); + assertEquals("1982-04-21", ds.getRow().getValue(1).toString()); + assertTrue("Actual value was: " + ds.getRow().getValue(2), + ds.getRow().getValue(2).toString().startsWith("08:00:00")); + + assertTrue(ds.next()); + assertEquals("2", ds.getRow().getValue(0).toString()); + assertEquals("1982-04-21", ds.getRow().getValue(1).toString()); + assertTrue("Actual value was: " + ds.getRow().getValue(2), + ds.getRow().getValue(2).toString().startsWith("18:35:00")); + + assertTrue(ds.next()); + assertEquals("3", ds.getRow().getValue(0).toString()); + assertEquals("2011-12-21", ds.getRow().getValue(1).toString()); + assertTrue("Actual value was: " + ds.getRow().getValue(2), + ds.getRow().getValue(2).toString().startsWith("12:00")); + + assertFalse(ds.next()); + ds.close(); + + } finally { + if (schema.getTableByName(tableName) != null) { + dc.executeUpdate(new UpdateScript() { + @Override + public void run(UpdateCallback callback) { + callback.dropTable(schema.getTableByName(tableName)).execute(); + } + }); + } } } @@ -511,4 +522,104 @@ public class JdbcTestTemplates { System.setProperty(JdbcDataContext.SYSTEM_PROPERTY_CONVERT_LOBS, ""); } + + public static void simpleCreateInsertUpdateAndDrop(final JdbcDataContext dataContext, final String testTableName) { + final Schema defaultSchema = dataContext.getDefaultSchema(); + + if (defaultSchema.getTableByName(testTableName) != null) { + // clean up before + dataContext.executeUpdate(new DropTable(defaultSchema, testTableName)); + } + + dataContext.executeUpdate(new CreateTable(defaultSchema, testTableName).withColumn("mykey") + .ofType(ColumnType.INTEGER).nullable(false).asPrimaryKey().withColumn("name") + .ofType(ColumnType.VARCHAR).ofSize(20)); + try { + final Table table = defaultSchema.getTableByName(testTableName); + assertNotNull(table); + + // insert + dataContext.executeUpdate(new UpdateScript() { + @Override + public void run(UpdateCallback callback) { + callback.insertInto(table).value("mykey", 1).value("name", "Apache").execute(); + callback.insertInto(table).value("mykey", 2).value("name", "MetaModel").execute(); + } + }); + + // update + dataContext.executeUpdate(new Update(table).value("name", "MM").where("mykey").eq(2)); + + DataSet ds = dataContext.query().from(table).selectAll().orderBy("mykey").execute(); + assertTrue(ds.next()); + assertEquals("Row[values=[1, Apache]]", ds.getRow().toString()); + assertTrue(ds.next()); + assertEquals("Row[values=[2, MM]]", ds.getRow().toString()); + assertFalse(ds.next()); + ds.close(); + + } finally { + // clean up after + dataContext.executeUpdate(new DropTable(defaultSchema, testTableName)); + } + } + + public static void compositeKeyCreation(JdbcDataContext dataContext, String testTableName) { + final Schema defaultSchema = dataContext.getDefaultSchema(); + + if (defaultSchema.getTableByName(testTableName) != null) { + // clean up before + dataContext.executeUpdate(new DropTable(defaultSchema, testTableName)); + } + + dataContext.executeUpdate(new CreateTable(defaultSchema, testTableName).withColumn("mykey1") + .ofType(ColumnType.INTEGER).nullable(false).asPrimaryKey().withColumn("mykey2").ofType(ColumnType.INTEGER) + .nullable(false).asPrimaryKey().withColumn("name").ofType(ColumnType.VARCHAR).ofSize(20)); + try { + final Table table = defaultSchema.getTableByName(testTableName); + assertNotNull(table); + + Column[] primaryKeys = table.getPrimaryKeys(); + assertEquals(2, primaryKeys.length); + assertEquals("mykey1", primaryKeys[0].getName().toLowerCase()); + assertEquals("mykey2", primaryKeys[1].getName().toLowerCase()); + + // insert two records with unique values on both keys + dataContext.executeUpdate(new UpdateScript() { + @Override + public void run(UpdateCallback callback) { + callback.insertInto(table).value("mykey1", 1).value("mykey2", 100).value("name", "Apache") + .execute(); + callback.insertInto(table).value("mykey1", 2).value("mykey2", 101).value("name", "MetaModel") + .execute(); + } + }); + + // insert a record with non-unique value on key 2 only + dataContext.executeUpdate(new UpdateScript() { + @Override + public void run(UpdateCallback callback) { + callback.insertInto(table).value("mykey1", 3).value("mykey2", 100).value("name", "Foo bar") + .execute(); + } + }); + + // update + dataContext.executeUpdate(new Update(table).value("name", "MM").where("mykey1").eq(2)); + + DataSet ds = dataContext.query().from(table).selectAll().orderBy("mykey1").execute(); + assertTrue(ds.next()); + assertEquals("Row[values=[1, 100, Apache]]", ds.getRow().toString()); + assertTrue(ds.next()); + assertEquals("Row[values=[2, 101, MM]]", ds.getRow().toString()); + assertTrue(ds.next()); + assertEquals("Row[values=[3, 100, Foo bar]]", ds.getRow().toString()); + assertFalse(ds.next()); + ds.close(); + + } finally { + // clean up after + dataContext.executeUpdate(new DropTable(defaultSchema, testTableName)); + } + } } http://git-wip-us.apache.org/repos/asf/metamodel/blob/1bc0887e/jdbc/src/test/java/org/apache/metamodel/jdbc/SqliteTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/SqliteTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/SqliteTest.java index fad6efe..c8cfd28 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/SqliteTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/SqliteTest.java @@ -70,6 +70,16 @@ public class SqliteTest extends TestCase { _connection.close(); } + public void testCreateInsertAndUpdate() throws Exception { + JdbcDataContext dc = new JdbcDataContext(_connection); + JdbcTestTemplates.simpleCreateInsertUpdateAndDrop(dc, "metamodel_test_simple"); + } + + public void testCompositePrimaryKeyCreation() throws Exception { + JdbcDataContext dc = new JdbcDataContext(_connection); + JdbcTestTemplates.compositeKeyCreation(dc, "metamodel_test_composite_keys"); + } + public void testDifferentOperators() throws Exception { JdbcTestTemplates.differentOperatorsTest(_connection); } @@ -248,8 +258,7 @@ public class SqliteTest extends TestCase { JdbcDataContext dc = new JdbcDataContext(_connection); JdbcTestTemplates.convertClobToString(dc); } - - + public void testInterpretationOfNull() throws Exception { JdbcTestTemplates.interpretationOfNulls(_connection); } http://git-wip-us.apache.org/repos/asf/metamodel/blob/1bc0887e/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/DB2Test.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/DB2Test.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/DB2Test.java index 6e28ee5..c0aeb83 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/DB2Test.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/DB2Test.java @@ -32,12 +32,28 @@ import org.apache.metamodel.schema.Table; * modified for whatever server is available (even within Human Inference). */ public class DB2Test extends AbstractJdbIntegrationTest { - + @Override protected String getPropertyPrefix() { return "db2"; } + public void testCreateInsertAndUpdate() throws Exception { + if (!isConfigured()) { + return; + } + + JdbcTestTemplates.simpleCreateInsertUpdateAndDrop(getDataContext(), "metamodel_db2_test"); + } + + public void testCompositePrimaryKeyCreation() throws Exception { + if (!isConfigured()) { + return; + } + + JdbcTestTemplates.compositeKeyCreation(getDataContext(), "metamodel_test_composite_keys"); + } + public void testInterpretationOfNull() throws Exception { if (!isConfigured()) { return; http://git-wip-us.apache.org/repos/asf/metamodel/blob/1bc0887e/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/MysqlTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/MysqlTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/MysqlTest.java index 76b4d20..4ac0947 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/MysqlTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/MysqlTest.java @@ -49,7 +49,7 @@ 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 + * @see http://dev.mysql.com/doc/sakila/en/sakila-installation.html */ public class MysqlTest extends AbstractJdbIntegrationTest { @@ -57,333 +57,359 @@ public class MysqlTest extends AbstractJdbIntegrationTest { protected String getPropertyPrefix() { return "mysql"; } - + + public void testCreateInsertAndUpdate() throws Exception { + if (!isConfigured()) { + return; + } + + JdbcTestTemplates.simpleCreateInsertUpdateAndDrop(getDataContext(), "metamodel_test_simple"); + } + + public void testCompositePrimaryKeyCreation() throws Exception { + if (!isConfigured()) { + return; + } + + JdbcTestTemplates.compositeKeyCreation(getDataContext(), "metamodel_test_composite_keys"); + } + public void testInterpretationOfNull() throws Exception { if (!isConfigured()) { return; } - + JdbcTestTemplates.interpretationOfNulls(getConnection()); } - public void testDatabaseProductName() throws Exception { - if (!isConfigured()) { + 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()) { + String databaseProductName = getConnection().getMetaData().getDatabaseProductName(); + assertEquals(JdbcDataContext.DATABASE_PRODUCT_MYSQL, databaseProductName); + } + + public void testCreateInsertAndUpdateDateTypes() 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()) { + + JdbcDataContext dataContext = getDataContext(); + JdbcTestTemplates.createInsertAndUpdateDateTypes(dataContext, dataContext.getDefaultSchema(), + "metamodel_mysql_test"); + } + + public void testAutomaticConversionWhenInsertingString() throws Exception { + if (!isConfigured()) { return; } - - JdbcTestTemplates.meaningOfOneSizeChar(getConnection()); - } - public void testAlternativeConnectionString() throws Exception { - if (!isConfigured()) { + 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; } - - 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()) { + + JdbcTestTemplates.meaningOfOneSizeChar(getConnection()); + } + + public void testAlternativeConnectionString() 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()) { + + 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; } - - DataContext dc = new JdbcDataContext(getConnection()); - Schema schema = dc.getDefaultSchema(); - assertEquals("sakila", schema.getName()); - } - - public void testExecuteQuery() throws Exception { - if (!isConfigured()) { + + 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(); - 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); + + 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_c ost,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())); + 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()) { + 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()) { + + 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()) { + + 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()) { + + 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` > 8 025 OR e.`rental_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()) { + + 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()); - } + + DatabaseMetaData metaData = getConnection().getMetaData(); + assertEquals("`", metaData.getIdentifierQuoteString()); + } } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/metamodel/blob/1bc0887e/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/OracleTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/OracleTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/OracleTest.java index a8f8f10..9794f4c 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/OracleTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/OracleTest.java @@ -27,6 +27,7 @@ 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.jdbc.JdbcTestTemplates; import org.apache.metamodel.query.FromItem; import org.apache.metamodel.query.JoinType; import org.apache.metamodel.query.Query; @@ -68,6 +69,22 @@ public class OracleTest extends AbstractJdbIntegrationTest { return "oracle"; } + public void testCreateInsertAndUpdate() throws Exception { + if (!isConfigured()) { + return; + } + + JdbcTestTemplates.simpleCreateInsertUpdateAndDrop(getDataContext(), "metamodel_test_simple"); + } + + public void testCompositePrimaryKeyCreation() throws Exception { + if (!isConfigured()) { + return; + } + + JdbcTestTemplates.compositeKeyCreation(getDataContext(), "metamodel_test_composite_keys"); + } + /** * Ticket #170: getIndexInfo causes SQLException. We test that resultsets * are closed properly. http://git-wip-us.apache.org/repos/asf/metamodel/blob/1bc0887e/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/PostgresqlTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/PostgresqlTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/PostgresqlTest.java index 2195122..f20ece4 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/PostgresqlTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/PostgresqlTest.java @@ -64,6 +64,22 @@ public class PostgresqlTest extends AbstractJdbIntegrationTest { return "postgresql"; } + public void testCreateInsertAndUpdate() throws Exception { + if (!isConfigured()) { + return; + } + + JdbcTestTemplates.simpleCreateInsertUpdateAndDrop(getDataContext(), "metamodel_test_simple"); + } + + public void testCompositePrimaryKeyCreation() throws Exception { + if (!isConfigured()) { + return; + } + + JdbcTestTemplates.compositeKeyCreation(getDataContext(), "metamodel_test_composite_keys"); + } + public void testInterpretationOfNull() throws Exception { if (!isConfigured()) { return; @@ -408,6 +424,16 @@ public class PostgresqlTest extends AbstractJdbIntegrationTest { } } + public void testCreateInsertAndUpdateDateTypes() throws Exception { + if (!isConfigured()) { + return; + } + + JdbcDataContext dataContext = getDataContext(); + JdbcTestTemplates.createInsertAndUpdateDateTypes(dataContext, dataContext.getDefaultSchema(), + "metamodel_postgresql_test"); + } + public void testCreateTableAndWriteRecords() throws Exception { if (!isConfigured()) { return; http://git-wip-us.apache.org/repos/asf/metamodel/blob/1bc0887e/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/SQLServerJtdsDriverTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/SQLServerJtdsDriverTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/SQLServerJtdsDriverTest.java index 1ad6a85..52219c0 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/SQLServerJtdsDriverTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/SQLServerJtdsDriverTest.java @@ -52,6 +52,22 @@ public class SQLServerJtdsDriverTest extends AbstractJdbIntegrationTest { protected String getPropertyPrefix() { return "sqlserver.jtds_driver"; } + + public void testCreateInsertAndUpdate() throws Exception { + if (!isConfigured()) { + return; + } + + JdbcTestTemplates.simpleCreateInsertUpdateAndDrop(getDataContext(), "metamodel_test_simple"); + } + + public void testCompositePrimaryKeyCreation() throws Exception { + if (!isConfigured()) { + return; + } + + JdbcTestTemplates.compositeKeyCreation(getDataContext(), "metamodel_test_composite_keys"); + } public void testWorkingWithDates() throws Exception { if (!isConfigured()) {
