H2databaseTest.java

/**
 * 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.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.concurrent.TimeUnit;

import org.apache.metamodel.UpdateCallback;
import org.apache.metamodel.UpdateScript;
import org.apache.metamodel.create.CreateTable;
import org.apache.metamodel.data.DataSet;
import org.apache.metamodel.data.Row;
import org.apache.metamodel.delete.DeleteFrom;
import org.apache.metamodel.drop.DropTable;
import org.apache.metamodel.insert.InsertInto;
import org.apache.metamodel.jdbc.dialects.IQueryRewriter;
import org.apache.metamodel.query.FunctionType;
import org.apache.metamodel.query.OperatorType;
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;
import org.apache.metamodel.update.Update;
import org.apache.metamodel.util.MutableRef;

import junit.framework.TestCase;

/**
 * Test case that tests interaction with the H2 embedded database
 */
public class H2databaseTest extends TestCase {
    
    public static final String DRIVER_CLASS = "org.h2.Driver";
    public static final String URL_MEMORY_DATABASE = "jdbc:h2:mem:";

    private final String[] FIRST_NAMES = { "Suzy", "Barbara", "John", "Ken", "Billy", "Larry", "Joe", "Margareth", "Bobby",
            "Elizabeth" };
    private final String[] LAST_NAMES = { "Doe", "Gates", "Jobs", "Ellison", "Trump" };

    private Connection conn;

    @Override
    protected void setUp() throws Exception {
        super.setUp();
        Class.forName(DRIVER_CLASS);
        conn = DriverManager.getConnection(URL_MEMORY_DATABASE);
    }

    @Override
    protected void tearDown() throws Exception {
        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 testTimestampValueInsertSelect() throws Exception {
        JdbcTestTemplates.timestampValueInsertSelect(conn, TimeUnit.NANOSECONDS);
    }

    public void testUsingSingleUpdates() throws Exception {
        final JdbcDataContext dc = new JdbcDataContext(conn);
        
        final Schema schema = dc.getDefaultSchema();
        dc.executeUpdate(new CreateTable(schema, "test_table").withColumn("id").ofType(ColumnType.VARCHAR));

        final Table table = schema.getTableByName("test_table");
        dc.executeUpdate(new InsertInto(table).value(0, "foo"));
        dc.executeUpdate(new InsertInto(table).value(0, "bar"));

        DataSet ds;

        ds = dc.query().from(table).selectCount().execute();
        assertTrue(ds.next());
        assertEquals("Row[values=[2]]", ds.getRow().toString());
        assertFalse(ds.next());
        ds.close();

        dc.executeUpdate(new DeleteFrom(table).where("id").eq("bar"));
        

        ds = dc.query().from(table).selectCount().execute();
        assertTrue(ds.next());
        assertEquals("Row[values=[1]]", ds.getRow().toString());
        assertFalse(ds.next());
        ds.close();

        dc.executeUpdate(new Update(table).where("id").eq("foo").value("id", "baz"));
        
        ds = dc.query().from(table).selectAll().execute();
        assertTrue(ds.next());
        assertEquals("Row[values=[baz]]", ds.getRow().toString());
        assertFalse(ds.next());
        ds.close();

        dc.executeUpdate(new DropTable(table));

        assertNull(schema.getTableByName("test_table"));
    }

    public void testScenario() throws Exception {
        int rowsAffected = conn.createStatement().executeUpdate(
                "CREATE TABLE test_table (id INTEGER AUTO_INCREMENT, name VARCHAR(255), age INTEGER)");
        assertEquals(0, rowsAffected);
        PreparedStatement p = conn.prepareStatement("INSERT INTO test_table (name, age) VALUES (?,?)");

        // insert 10,000 random names
        for (int i = 0; i < 10000; i++) {
            int randomAge = (int) (Math.random() * 100);
            String randomName = getRandomFirstName() + " " + getRandomLastName();
            insert(p, randomName, randomAge);
        }

        JdbcDataContext dc = new JdbcDataContext(conn);
        assertEquals("[INFORMATION_SCHEMA, PUBLIC]", Arrays.toString(dc.getSchemaNames()));

        Schema schema = dc.getDefaultSchema();
        assertEquals("PUBLIC", schema.getName());

        assertEquals("[TEST_TABLE]", Arrays.toString(schema.getTableNames()));

        Table table = schema.getTableByName("test_table");

        assertEquals("[ID, NAME, AGE]", Arrays.toString(table.getColumnNames()));

        Column idColumn = table.getColumnByName("ID");
        assertEquals("Column[name=ID,columnNumber=0,type=INTEGER,nullable=false,nativeType=INTEGER,columnSize=10]",
                idColumn.toString());
        Column nameColumn = table.getColumnByName("NAME");
        assertEquals("Column[name=NAME,columnNumber=1,type=VARCHAR,nullable=true,nativeType=VARCHAR,columnSize=255]",
                nameColumn.toString());
        Column ageColumn = table.getColumnByName("AGE");
        assertEquals("Column[name=AGE,columnNumber=2,type=INTEGER,nullable=true,nativeType=INTEGER,columnSize=10]",
                ageColumn.toString());

        Query q = dc.query().from(table).selectCount().and(FunctionType.MAX, ageColumn).and(FunctionType.MIN, ageColumn)
                .toQuery();
        assertEquals("SELECT COUNT(*), MAX(\"TEST_TABLE\".\"AGE\"), MIN(\"TEST_TABLE\".\"AGE\") FROM PUBLIC.\"TEST_TABLE\"",
                q.toSql());

        assertEquals(1, dc.getFetchSizeCalculator().getFetchSize(q));

        DataSet ds = dc.executeQuery(q);
        assertTrue(ds.next());
        Row row = ds.getRow();
        assertFalse(ds.next());

        assertEquals(10000, ((Number) row.getValue(0)).intValue());
        int maxAge = ((Number) row.getValue(1)).intValue();
        assertTrue("Maximum age was: " + maxAge, maxAge > 90 && maxAge <= 100);
        int minAge = ((Number) row.getValue(2)).intValue();
        assertTrue("Minimum age was: " + minAge, minAge < 10 && minAge >= 0);

        q = dc.query().from(table).as("t").select(ageColumn).selectCount().where(ageColumn).greaterThan(50).groupBy(ageColumn)
                .toQuery();
        assertEquals("SELECT t.\"AGE\", COUNT(*) FROM PUBLIC.\"TEST_TABLE\" t WHERE t.\"AGE\" > 50 GROUP BY t.\"AGE\"", q.toSql());

        ds = dc.executeQuery(q);
        List<Object[]> objectArrays = ds.toObjectArrays();
        assertTrue(objectArrays.size() <= 50);
        assertTrue(objectArrays.size() > 40);

        for (Object[] objects : objectArrays) {
            Integer age = (Integer) objects[0];
            assertTrue(age.intValue() > 50);
            Number count = (Number) objects[1];
            assertTrue(count.intValue() > 0);
        }
    }

    private String getRandomFirstName() {
        int randomIndex = (int) (Math.random() * FIRST_NAMES.length);
        return FIRST_NAMES[randomIndex];
    }

    private String getRandomLastName() {
        int randomIndex = (int) (Math.random() * LAST_NAMES.length);
        return LAST_NAMES[randomIndex];
    }

    private void insert(PreparedStatement p, String name, int age) throws SQLException {
        p.setString(1, name);
        p.setInt(2, age);
        p.executeUpdate();
    }

    public void testQueryRewriter() throws Exception {
        final JdbcDataContext dc = new JdbcDataContext(conn);

        final IQueryRewriter queryRewriter = dc.getQueryRewriter();
        assertEquals("H2QueryRewriter", queryRewriter.getClass().getSimpleName());

        assertTrue(queryRewriter.isFirstRowSupported());
        assertTrue(queryRewriter.isMaxRowsSupported());
    }

    public void testBothFirstRowAndMaxRows() throws Exception {
        final JdbcDataContext dc = new JdbcDataContext(conn);
        final Schema schema = dc.getDefaultSchema();
        dc.executeUpdate(new UpdateScript() {

            @Override
            public void run(UpdateCallback cb) {
                JdbcCreateTableBuilder createTableBuilder = (JdbcCreateTableBuilder) cb.createTable(schema, "test_table");
                Table writtenTable = createTableBuilder.withColumn("id").asPrimaryKey().ofType(ColumnType.INTEGER).execute();

                for (int i = 0; i < 10; i++) {
                    cb.insertInto(writtenTable).value("id", i + 1).execute();
                }
            }
        });

        Query q = dc.query().from("test_table").select("id").toQuery();
        q.setFirstRow(2);
        q.setMaxRows(4);
        DataSet ds = dc.executeQuery(q);
        assertTrue(ds.next());
        assertEquals("Row[values=[2]]", ds.getRow().toString());
        assertTrue(ds.next());
        assertEquals("Row[values=[3]]", ds.getRow().toString());
        assertTrue(ds.next());
        assertEquals("Row[values=[4]]", ds.getRow().toString());
        assertTrue(ds.next());
        assertEquals("Row[values=[5]]", ds.getRow().toString());
        assertFalse(ds.next());

        dc.executeUpdate(new UpdateScript() {
            @Override
            public void run(UpdateCallback cb) {
                cb.dropTable("test_table").execute();
            }
        });
    }

    public void testCreateTable() throws Exception {
        assertFalse(conn.isReadOnly());

        JdbcDataContext dc = new JdbcDataContext(conn);
        final Schema schema = dc.getDefaultSchema();
        Table readTable = dc.getDefaultSchema().getTableByName("test_table");
        assertNull(readTable);

        final MutableRef<Table> writtenTableRef = new MutableRef<Table>();
        dc.executeUpdate(new UpdateScript() {

            @Override
            public void run(UpdateCallback cb) {
                JdbcCreateTableBuilder createTableBuilder = (JdbcCreateTableBuilder) cb.createTable(schema, "test_table");
                Table writtenTable = createTableBuilder.withColumn("id").asPrimaryKey().ofType(ColumnType.INTEGER)
                        .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, name VARCHAR(255), age INTEGER, PRIMARY KEY(id))", sql);
                assertNotNull(writtenTable);
                assertEquals("[ID, NAME, AGE]", Arrays.toString(writtenTable.getColumnNames()));

                writtenTableRef.set(writtenTable);
            }
        });

        assertEquals("[TEST_TABLE]", Arrays.toString(dc.getDefaultSchema().getTableNames()));

        readTable = dc.getDefaultSchema().getTableByName("test_table");
        assertEquals("[ID, NAME, AGE]", Arrays.toString(readTable.getColumnNames()));
        assertEquals("[Column[name=ID,columnNumber=0,type=INTEGER,nullable=false,nativeType=INTEGER,columnSize=10]]",
                Arrays.toString(readTable.getPrimaryKeys()));
        assertEquals(writtenTableRef.get(), readTable);

        assertFalse(conn.isReadOnly());

        dc = new JdbcDataContext(conn);
        assertSame(conn, dc.getConnection());

        readTable = dc.getDefaultSchema().getTableByName("test_table");
        assertEquals("[ID, NAME, AGE]", Arrays.toString(readTable.getColumnNames()));
        assertTrue(writtenTableRef.get().getQualifiedLabel().equalsIgnoreCase(readTable.getQualifiedLabel()));

        dc.executeUpdate(new UpdateScript() {
            @Override
            public void run(UpdateCallback cb) {
                cb.insertInto(writtenTableRef.get()).value("age", 14).value("name", "hello").value("id", 1).execute();
                JdbcInsertBuilder insertBuilder = (JdbcInsertBuilder) cb.insertInto(writtenTableRef.get()).value("age", 15)
                        .value("name", "wor'ld").value("id", 2);
                assertEquals("INSERT INTO PUBLIC.\"TEST_TABLE\" (ID,NAME,AGE) VALUES (?,?,?)", insertBuilder.createSqlStatement());
                insertBuilder.execute();
                cb.insertInto(writtenTableRef.get()).value("age", 16).value("name", "escobar!").value("id", 3).execute();
            }
        });

        DataSet ds = dc.query().from(readTable).select(readTable.getColumns()).orderBy("id").execute();
        assertTrue(ds.next());
        assertEquals("Row[values=[1, hello, 14]]", ds.getRow().toString());
        assertTrue(ds.next());
        assertEquals("Row[values=[2, wor'ld, 15]]", ds.getRow().toString());
        assertTrue(ds.next());
        assertEquals("Row[values=[3, escobar!, 16]]", ds.getRow().toString());
        assertFalse(ds.next());
        ds.close();

        dc.executeUpdate(new UpdateScript() {
            @Override
            public void run(UpdateCallback callback) {
                JdbcUpdateBuilder updateCallback = (JdbcUpdateBuilder) callback.update("test_table").value("age", 18).where("id")
                        .greaterThan(1);
                assertEquals("UPDATE PUBLIC.\"TEST_TABLE\" SET AGE=? WHERE \"TEST_TABLE\".\"ID\" > ?",
                        updateCallback.createSqlStatement());
                updateCallback.execute();
            }
        });

        ds = dc.query().from(readTable).select(readTable.getColumns()).orderBy("id").execute();
        assertTrue(ds.next());
        assertEquals("Row[values=[1, hello, 14]]", ds.getRow().toString());
        assertTrue(ds.next());
        assertEquals("Row[values=[2, wor'ld, 18]]", ds.getRow().toString());
        assertTrue(ds.next());
        assertEquals("Row[values=[3, escobar!, 18]]", ds.getRow().toString());
        assertFalse(ds.next());
        ds.close();

        dc.executeUpdate(new UpdateScript() {
            @Override
            public void run(UpdateCallback callback) {
                callback.deleteFrom("test_table").where("age").greaterThan(15).execute();
            }
        });

        ds = dc.query().from(readTable).select(readTable.getColumns()).orderBy("id").execute();
        assertTrue(ds.next());
        assertEquals("Row[values=[1, hello, 14]]", ds.getRow().toString());
        assertFalse(ds.next());
        ds.close();

        assertEquals("[TEST_TABLE]", Arrays.toString(dc.getDefaultSchema().getTableNames()));

        dc.executeUpdate(new UpdateScript() {
            @Override
            public void run(UpdateCallback callback) {
                callback.dropTable("test_table").execute();
            }
        });

        assertEquals("[]", Arrays.toString(dc.getDefaultSchema().getTableNames()));
    }

    public void testSelectItemsThatReferencesDifferentFromItems() throws Exception {
        assertNotNull(conn);

        try {
            // clean up, if nescesary
            conn.createStatement().execute("DROP TABLE test_table");
        } catch (SQLException e) {
            // do nothing
        }

        final JdbcDataContext dc = new JdbcDataContext(conn);
        dc.executeUpdate(new UpdateScript() {
            @Override
            public void run(UpdateCallback callback) {
                Table table = callback.createTable(dc.getDefaultSchema(), "test_table").withColumn("foo")
                        .ofType(ColumnType.INTEGER).withColumn("bar").ofType(ColumnType.VARCHAR).execute();
                callback.insertInto(table).value("foo", 1).value("bar", "hello").execute();
                callback.insertInto(table).value("foo", 2).value("bar", "there").execute();
                callback.insertInto(table).value("foo", 3).value("bar", "world").execute();
            }
        });

        Table table = dc.getTableByQualifiedLabel("test_table");
        Query query = new Query().from(table, "a").from(table, "b");
        query.select(table.getColumnByName("foo"), query.getFromClause().getItem(0));
        query.select(table.getColumnByName("foo"), query.getFromClause().getItem(1));
        query.where(new SelectItem(table.getColumnByName("bar"), query.getFromClause().getItem(0)), OperatorType.EQUALS_TO,
                "hello");

        assertEquals(
                "SELECT a.\"FOO\", b.\"FOO\" FROM PUBLIC.\"TEST_TABLE\" a, PUBLIC.\"TEST_TABLE\" b WHERE a.\"BAR\" = 'hello'",
                query.toSql());

        DataSet ds = dc.executeQuery(query);
        assertTrue(ds.next());
        assertEquals("Row[values=[1, 1]]", ds.getRow().toString());
        assertTrue(ds.next());
        assertEquals("Row[values=[1, 2]]", ds.getRow().toString());
        assertTrue(ds.next());
        assertEquals("Row[values=[1, 3]]", ds.getRow().toString());
        assertFalse(ds.next());
        ds.close();

        dc.executeUpdate(new UpdateScript() {
            @Override
            public void run(UpdateCallback callback) {
                callback.dropTable("test_table").execute();
            }
        });
    }

    private JdbcDataContext createLimitAndOffsetTestData() {
        final JdbcDataContext dc = new JdbcDataContext(conn);

        if (dc.getTableByQualifiedLabel("test_table") != null) {
            dc.executeUpdate(new UpdateScript() {
                @Override
                public void run(UpdateCallback callback) {
                    callback.dropTable("test_table").execute();
                }
            });
        }

        dc.executeUpdate(new UpdateScript() {
            @Override
            public void run(UpdateCallback callback) {
                Table table = callback.createTable(dc.getDefaultSchema(), "test_table").withColumn("foo")
                        .ofType(ColumnType.INTEGER).withColumn("bar").ofType(ColumnType.VARCHAR).execute();
                callback.insertInto(table).value("foo", 1).value("bar", "hello").execute();
                callback.insertInto(table).value("foo", 2).value("bar", "there").execute();
                callback.insertInto(table).value("foo", 3).value("bar", "world").execute();
            }
        });

        dc.refreshSchemas();

        return dc;
    }

    public void testLimit() throws Exception {
        JdbcDataContext dc = createLimitAndOffsetTestData();
        Schema schema = dc.getDefaultSchema();
        Table productsTable = schema.getTableByName("test_table");

        DataSet ds = dc.query().from(productsTable).select("foo").limit(2).execute();
        assertTrue(ds.next());
        assertEquals("Row[values=[1]]", ds.getRow().toString());
        assertTrue(ds.next());
        assertEquals("Row[values=[2]]", ds.getRow().toString());
        assertFalse(ds.next());
        ds.close();
    }

    public void testOffset() throws Exception {
        JdbcDataContext dc = createLimitAndOffsetTestData();
        Schema schema = dc.getDefaultSchema();
        Table productsTable = schema.getTableByName("test_table");

        DataSet ds = dc.query().from(productsTable).select("foo").offset(1).execute();
        assertTrue(ds.next());
        assertEquals("Row[values=[2]]", ds.getRow().toString());
        assertTrue(ds.next());
        assertEquals("Row[values=[3]]", ds.getRow().toString());
        assertFalse(ds.next());
        ds.close();
    }

    public void testLimitAndOffset() throws Exception {
        JdbcDataContext dc = createLimitAndOffsetTestData();
        Schema schema = dc.getDefaultSchema();
        Table productsTable = schema.getTableByName("test_table");

        DataSet ds = dc.query().from(productsTable).select("foo").limit(1).offset(1).execute();
        assertTrue(ds.next());
        assertEquals("Row[values=[2]]", ds.getRow().toString());
        assertFalse(ds.next());

        ds.close();
    }

    public void testConvertClobToString() throws Exception {
        JdbcDataContext dc = new JdbcDataContext(conn);

        JdbcTestTemplates.convertClobToString(dc);
    }

    public void testDifferentOperators() throws Exception {
        JdbcTestTemplates.differentOperatorsTest(conn);
    }

    public void testWorkingWithDates() throws Exception {
        final JdbcDataContext dc = new JdbcDataContext(conn);
        final Schema schema = dc.getDefaultSchema();
        JdbcTestTemplates.createInsertAndUpdateDateTypes(dc, schema, "test_table");
    }

    public void testAutomaticConversionWhenInsertingString() throws Exception {
        JdbcTestTemplates.automaticConversionWhenInsertingString(conn);
    }

    public void testCharOfSizeOne() throws Exception {
        JdbcTestTemplates.meaningOfOneSizeChar(conn);
    }
    
    public void testInterpretationOfNull() throws Exception {
        JdbcTestTemplates.interpretationOfNulls(conn);
    }
}