| /** |
| * 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; |
| |
| import java.sql.Connection; |
| import java.sql.DriverManager; |
| import java.sql.PreparedStatement; |
| import java.sql.Statement; |
| import java.util.Arrays; |
| import java.util.concurrent.TimeUnit; |
| |
| import javax.swing.table.TableModel; |
| |
| import junit.framework.TestCase; |
| |
| import org.apache.metamodel.DataContext; |
| import org.apache.metamodel.MetaModelHelper; |
| 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.data.Row; |
| import org.apache.metamodel.jdbc.dialects.HsqldbQueryRewriter; |
| import org.apache.metamodel.jdbc.dialects.IQueryRewriter; |
| 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.Column; |
| import org.apache.metamodel.schema.ColumnType; |
| import org.apache.metamodel.schema.Schema; |
| import org.apache.metamodel.schema.Table; |
| |
| /** |
| * Test case that tests hsqldb interaction. The test uses an embedded copy of |
| * the "pentaho sampledata" sample database that can be found at |
| * http://pentaho.sourceforge.net. |
| */ |
| public class HsqldbTest extends TestCase { |
| |
| private static final String CONNECTION_STRING = "jdbc:hsqldb:res:metamodel"; |
| private static final String USERNAME = "SA"; |
| private static final String PASSWORD = ""; |
| private Connection _connection; |
| |
| @Override |
| protected void setUp() throws Exception { |
| super.setUp(); |
| Class.forName("org.hsqldb.jdbcDriver"); |
| _connection = DriverManager.getConnection(CONNECTION_STRING, USERNAME, PASSWORD); |
| } |
| |
| @Override |
| protected void tearDown() throws Exception { |
| super.tearDown(); |
| _connection.close(); |
| } |
| |
| public void testApproximateCount() throws Exception { |
| final JdbcDataContext dataContext = new JdbcDataContext(_connection); |
| final DataSet dataSet = dataContext.executeQuery("SELECT APPROXIMATE COUNT(*) FROM customers"); |
| assertTrue(dataSet.next()); |
| assertEquals(122, dataSet.getRow().getValue(0)); |
| assertFalse(dataSet.next()); |
| } |
| |
| public void testTimestampValueInsertSelect() throws Exception { |
| Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:" + getName(), USERNAME, PASSWORD); |
| JdbcTestTemplates.timestampValueInsertSelect(connection, TimeUnit.NANOSECONDS); |
| } |
| |
| public void testCreateInsertAndUpdate() throws Exception { |
| Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:" + getName(), USERNAME, PASSWORD); |
| 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); |
| assertEquals("[Schema[name=INFORMATION_SCHEMA], " + "Schema[name=PUBLIC]]", Arrays.toString(dc.getSchemas())); |
| |
| Schema defaultSchema = dc.getDefaultSchema(); |
| Schema publicSchema = dc.getSchemaByName("PUBLIC"); |
| 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], " |
| + "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]]", |
| Arrays.toString(empTable.getColumns())); |
| |
| 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 |
| assertEquals( |
| "[Relationship[primaryTable=PRODUCTS,primaryColumns=[PRODUCTCODE],foreignTable=ORDERFACT,foreignColumns=[PRODUCTCODE]]]", |
| Arrays.toString(publicSchema.getRelationships())); |
| } |
| |
| public void testExecuteQuery() throws Exception { |
| JdbcDataContext dc = new JdbcDataContext(_connection); |
| Schema schema = dc.getDefaultSchema(); |
| Table productsTable = schema.getTableByName("PRODUCTS"); |
| Table factTable = schema.getTableByName("ORDERFACT"); |
| |
| Query q = new Query().from(new FromItem(JoinType.INNER, productsTable.getRelationships(factTable)[0])).select( |
| productsTable.getColumns()[0], factTable.getColumns()[0]); |
| assertEquals( |
| "SELECT \"PRODUCTS\".\"PRODUCTCODE\", \"ORDERFACT\".\"ORDERNUMBER\" FROM PUBLIC.\"PRODUCTS\" INNER JOIN PUBLIC.\"ORDERFACT\" ON \"PRODUCTS\".\"PRODUCTCODE\" = \"ORDERFACT\".\"PRODUCTCODE\"", |
| q.toString()); |
| assertEquals(25000, dc.getFetchSizeCalculator().getFetchSize(q)); |
| |
| DataSet data = dc.executeQuery(q); |
| TableModel tableModel = new DataSetTableModel(data); |
| assertEquals(2, tableModel.getColumnCount()); |
| assertEquals(2996, tableModel.getRowCount()); |
| |
| assertEquals(110, MetaModelHelper.executeSingleRowQuery(dc, new Query().selectCount().from(productsTable)) |
| .getValue(0)); |
| } |
| |
| public void testLimit() throws Exception { |
| JdbcDataContext dc = new JdbcDataContext(_connection); |
| Schema schema = dc.getDefaultSchema(); |
| Table productsTable = schema.getTableByName("PRODUCTS"); |
| |
| DataSet ds = dc.query().from(productsTable).select("PRODUCTCODE").limit(2).execute(); |
| assertTrue(ds.next()); |
| assertEquals("Row[values=[S10_1678]]", ds.getRow().toString()); |
| assertTrue(ds.next()); |
| assertEquals("Row[values=[S10_1949]]", ds.getRow().toString()); |
| assertFalse(ds.next()); |
| ds.close(); |
| } |
| |
| public void testOffset() throws Exception { |
| JdbcDataContext dc = new JdbcDataContext(_connection); |
| Schema schema = dc.getDefaultSchema(); |
| Table productsTable = schema.getTableByName("PRODUCTS"); |
| |
| DataSet ds = dc.query().from(productsTable).select("PRODUCTCODE").offset(2).execute(); |
| assertTrue(ds.next()); |
| assertEquals("Row[values=[S10_2016]]", ds.getRow().toString()); |
| assertTrue(ds.next()); |
| assertEquals("Row[values=[S10_4698]]", ds.getRow().toString()); |
| assertTrue(ds.next()); |
| assertTrue(ds.next()); |
| assertTrue(ds.next()); |
| ds.close(); |
| } |
| |
| public void testLimitAndOffset() throws Exception { |
| JdbcDataContext dc = new JdbcDataContext(_connection); |
| Schema schema = dc.getDefaultSchema(); |
| Table productsTable = schema.getTableByName("PRODUCTS"); |
| |
| DataSet ds = dc.query().from(productsTable).select("PRODUCTCODE").limit(2).offset(2).execute(); |
| assertTrue(ds.next()); |
| assertEquals("Row[values=[S10_2016]]", ds.getRow().toString()); |
| assertTrue(ds.next()); |
| assertEquals("Row[values=[S10_4698]]", ds.getRow().toString()); |
| assertFalse(ds.next()); |
| |
| ds.close(); |
| } |
| |
| public void testQueryRewriterQuoteAliases() throws Exception { |
| JdbcDataContext dc = new JdbcDataContext(_connection); |
| IQueryRewriter queryRewriter = dc.getQueryRewriter(); |
| assertSame(HsqldbQueryRewriter.class, queryRewriter.getClass()); |
| |
| Schema schema = dc.getSchemaByName("PUBLIC"); |
| Table productsTable = schema.getTableByName("PRODUCTS"); |
| |
| Query q = new Query().from(productsTable, "pro-ducts").select( |
| new SelectItem(productsTable.getColumnByName("PRODUCTCODE")).setAlias("c|o|d|e")); |
| q.setMaxRows(5); |
| |
| 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\"", |
| 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\"", |
| queryString); |
| |
| // Test that the original query is still the same (ie. it has been |
| // cloned for execution) |
| assertEquals("SELECT pro-ducts.\"PRODUCTCODE\" AS c|o|d|e FROM PUBLIC.\"PRODUCTS\" pro-ducts", q.toString()); |
| |
| DataSet data = dc.executeQuery(q); |
| assertNotNull(data); |
| data.close(); |
| } |
| |
| public void testQualifiedLabel() throws Exception { |
| DataContext dc = new JdbcDataContext(_connection); |
| |
| 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("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()); |
| } |
| |
| public void testQuoteInWhereClause() throws Exception { |
| Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:quote_in_where", USERNAME, PASSWORD); |
| |
| { |
| Statement st = connection.createStatement(); |
| st.executeUpdate("CREATE TABLE testtable (name VARCHAR(255));"); |
| st.close(); |
| } |
| |
| { |
| PreparedStatement st = connection.prepareStatement("INSERT INTO testtable VALUES (?)"); |
| st.setString(1, "hello"); |
| st.executeUpdate(); |
| |
| st.setString(1, "hi"); |
| st.executeUpdate(); |
| |
| st.setString(1, "m'jello"); |
| st.executeUpdate(); |
| |
| st.close(); |
| } |
| |
| JdbcDataContext dc = new JdbcDataContext(connection); |
| |
| Table table = dc.getDefaultSchema().getTableByName("testtable"); |
| |
| Query q = dc.query().from(table).selectCount().toQuery(); |
| Row row = MetaModelHelper.executeSingleRowQuery(dc, q); |
| assertEquals(3, ((Number) row.getValue(0)).intValue()); |
| |
| 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)); |
| |
| row = MetaModelHelper.executeSingleRowQuery(dc, q); |
| assertEquals(1, ((Number) row.getValue(0)).intValue()); |
| } |
| |
| public void testWhereColInValues() throws Exception { |
| DataContext dc = new JdbcDataContext(_connection); |
| DataSet ds = dc.query().from("QUADRANT_ACTUALS").select("REGION").and("DEPARTMENT").where("DEPARTMENT") |
| .in("Sales", "Finance").execute(); |
| |
| assertTrue(ds.next()); |
| assertEquals("Row[values=[Central, Sales]]", ds.getRow().toString()); |
| |
| while (ds.next()) { |
| Object deptValue = ds.getRow().getValue(1); |
| assertTrue("Sales".equals(deptValue) || "Finance".equals(deptValue)); |
| } |
| ds.close(); |
| } |
| |
| public void testAutomaticConversionWhenInsertingString() throws Exception { |
| Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:auto_conversion", USERNAME, PASSWORD); |
| |
| JdbcTestTemplates.automaticConversionWhenInsertingString(connection); |
| } |
| |
| public void testWorkingWithDates() throws Exception { |
| Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:working_with_dates", USERNAME, PASSWORD); |
| |
| final JdbcDataContext dc = new JdbcDataContext(connection); |
| final Schema schema = dc.getDefaultSchema(); |
| JdbcTestTemplates.createInsertAndUpdateDateTypes(dc, schema, "test_table"); |
| } |
| |
| public void testCharOfSizeOn() throws Exception { |
| Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:char_of_size_one", USERNAME, PASSWORD); |
| JdbcTestTemplates.meaningOfOneSizeChar(connection); |
| } |
| |
| public void testInsertOfDifferentTypes() throws Exception { |
| Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:different_types_insert", USERNAME, |
| PASSWORD); |
| |
| 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("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()); |
| } |
| }); |
| |
| 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=[0, row 1]]", ds.getRow().toString()); |
| assertTrue(ds.next()); |
| assertEquals("Row[values=[1, row 2]]", ds.getRow().toString()); |
| assertTrue(ds.next()); |
| assertEquals("Row[values=[2, row 3]]", ds.getRow().toString()); |
| assertTrue(ds.next()); |
| assertEquals("Row[values=[3, row 4]]", ds.getRow().toString()); |
| assertTrue(ds.next()); |
| assertEquals("Row[values=[4, row 5]]", ds.getRow().toString()); |
| assertTrue(ds.next()); |
| assertEquals("Row[values=[5, row 6]]", ds.getRow().toString()); |
| assertTrue(ds.next()); |
| assertEquals("Row[values=[6, row 7]]", ds.getRow().toString()); |
| assertTrue(ds.next()); |
| assertEquals("Row[values=[7, row 8]]", ds.getRow().toString()); |
| assertFalse(ds.next()); |
| ds.close(); |
| } finally { |
| connection.createStatement().execute("DROP TABLE my_table"); |
| } |
| } |
| |
| public void testDifferentOperators() throws Exception { |
| Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:different_operators", USERNAME, PASSWORD); |
| JdbcTestTemplates.differentOperatorsTest(conn); |
| } |
| |
| public void testInterpretationOfNull() throws Exception { |
| Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:interpretation_of_null", USERNAME, PASSWORD); |
| JdbcTestTemplates.interpretationOfNulls(conn); |
| } |
| } |