SqliteTest.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.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.concurrent.TimeUnit;

import junit.framework.TestCase;

import org.apache.metamodel.DataContext;
import org.apache.metamodel.UpdateCallback;
import org.apache.metamodel.UpdateScript;
import org.apache.metamodel.data.DataSet;
import org.apache.metamodel.jdbc.dialects.SQLiteQueryRewriter;
import org.apache.metamodel.query.OperatorType;
import org.apache.metamodel.query.Query;
import org.apache.metamodel.schema.Column;
import org.apache.metamodel.schema.ColumnType;
import org.apache.metamodel.schema.Schema;
import org.apache.metamodel.schema.Table;
import org.apache.metamodel.util.DateUtils;
import org.apache.metamodel.util.FileHelper;
import org.apache.metamodel.util.Month;

/**
 * Test case that tests SQLite interaction. The test uses an example database
 * from the trac project management system, src/test/resources/trac.db
 */
public class SqliteTest extends TestCase {

    private static final String CONNECTION_STRING = "jdbc:sqlite:";

    private Connection _connection;

    @Override
    protected void setUp() throws Exception {
        super.setUp();
        Class.forName("org.sqlite.JDBC");
        File sourceFile = new File("src/test/resources/trac.db");
        assert sourceFile.exists();

        File targetFile = new File("target/trac.db");
        FileHelper.copy(sourceFile, targetFile);

        _connection = DriverManager.getConnection(CONNECTION_STRING + targetFile.getAbsolutePath());
    }

    @Override
    protected void tearDown() throws Exception {
        super.tearDown();
        _connection.close();
    }

    public void testTimestampValueInsertSelect() throws Exception {
        JdbcTestTemplates.timestampValueInsertSelect(_connection, TimeUnit.SECONDS);
    }

    public void testCreateInsertAndUpdate() throws Exception {
        JdbcDataContext dc = new JdbcDataContext(_connection);
        JdbcTestTemplates.simpleCreateInsertUpdateAndDrop(dc, "metamodel_test_simple");
    }

    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);
    }

    public void testGetQueryRewriter() throws Exception {
        JdbcDataContext dc = new JdbcDataContext(_connection);
        assertTrue(dc.getQueryRewriter() instanceof SQLiteQueryRewriter);
    }

    public void testGetSchemas() throws Exception {
        DataContext dc = new JdbcDataContext(_connection);
        String[] schemaNames = dc.getSchemaNames();
        assertEquals("[null]", Arrays.toString(schemaNames));

        Schema schema = dc.getDefaultSchema();
        assertNotNull(schema);
        assertNull(schema.getName());

        Table[] tables = schema.getTables();
        assertEquals("[Table[name=system,type=TABLE,remarks=null], "
                + "Table[name=permission,type=TABLE,remarks=null], "
                + "Table[name=auth_cookie,type=TABLE,remarks=null], " + "Table[name=session,type=TABLE,remarks=null], "
                + "Table[name=session_attribute,type=TABLE,remarks=null], "
                + "Table[name=attachment,type=TABLE,remarks=null], " + "Table[name=wiki,type=TABLE,remarks=null], "
                + "Table[name=revision,type=TABLE,remarks=null], " + "Table[name=node_change,type=TABLE,remarks=null], "
                + "Table[name=ticket,type=TABLE,remarks=null], " + "Table[name=ticket_change,type=TABLE,remarks=null], "
                + "Table[name=ticket_custom,type=TABLE,remarks=null], " + "Table[name=enum,type=TABLE,remarks=null], "
                + "Table[name=component,type=TABLE,remarks=null], " + "Table[name=milestone,type=TABLE,remarks=null], "
                + "Table[name=version,type=TABLE,remarks=null], " + "Table[name=report,type=TABLE,remarks=null]]",
                Arrays.toString(tables));

        // Index- and key-info is not yet implemented in the JDBC driver

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

        Table wikiTable = schema.getTableByName("WIKI");
        assertEquals(
                "[Column[name=name,columnNumber=0,type=VARCHAR,nullable=true,nativeType=TEXT,columnSize=2000000000], "
                        + "Column[name=version,columnNumber=1,type=INTEGER,nullable=true,nativeType=INTEGER,columnSize=2000000000], "
                        + "Column[name=time,columnNumber=2,type=INTEGER,nullable=true,nativeType=INTEGER,columnSize=2000000000], "
                        + "Column[name=author,columnNumber=3,type=VARCHAR,nullable=true,nativeType=TEXT,columnSize=2000000000], "
                        + "Column[name=ipnr,columnNumber=4,type=VARCHAR,nullable=true,nativeType=TEXT,columnSize=2000000000], "
                        + "Column[name=text,columnNumber=5,type=VARCHAR,nullable=true,nativeType=TEXT,columnSize=2000000000], "
                        + "Column[name=comment,columnNumber=6,type=VARCHAR,nullable=true,nativeType=TEXT,columnSize=2000000000], "
                        + "Column[name=readonly,columnNumber=7,type=INTEGER,nullable=true,nativeType=INTEGER,columnSize=2000000000]]",
                Arrays.toString(wikiTable.getColumns()));

        Table permissionTable = schema.getTableByName("PERMISSION");
        assertEquals(
                "[Column[name=username,columnNumber=0,type=VARCHAR,nullable=true,nativeType=TEXT,columnSize=2000000000], "
                        + "Column[name=action,columnNumber=1,type=VARCHAR,nullable=true,nativeType=TEXT,columnSize=2000000000]]",
                Arrays.toString(permissionTable.getColumns()));
    }

    public void testExecuteQuery() throws Exception {
        JdbcDataContext dc = new JdbcDataContext(_connection);
        Schema schema = dc.getDefaultSchema();
        assertEquals("Schema[name=null]", schema.toString());

        Table wikiTable = schema.getTableByName("WIKI");

        Query q = new Query().selectCount().from(wikiTable).where(wikiTable.getColumnByName("name"), OperatorType.LIKE,
                "Trac%");
        assertEquals("SELECT COUNT(*) FROM wiki WHERE wiki.name LIKE 'Trac%'", q.toString());
        assertEquals(1, dc.getFetchSizeCalculator().getFetchSize(q));
        assertEquals(37, dc.executeQuery(q).toObjectArrays().get(0)[0]);

        Table permissionTable = schema.getTableByName("PERMISSION");
        Column typeColumn = permissionTable.getColumnByName("username");
        q = new Query().select(typeColumn).selectCount().from(permissionTable).groupBy(typeColumn).orderBy(typeColumn);
        assertEquals(
                "SELECT permission.username, COUNT(*) FROM permission GROUP BY permission.username ORDER BY permission.username ASC",
                q.toString());

        List<Object[]> data = dc.executeQuery(q).toObjectArrays();
        assertEquals(2, data.size());
        assertEquals("[anonymous, 12]", Arrays.toString(data.get(0)));
        assertEquals("[authenticated, 4]", Arrays.toString(data.get(1)));
    }

    public void testQualifiedLabel() throws Exception {
        DataContext dc = new JdbcDataContext(_connection);
        Schema schema = dc.getDefaultSchema();
        Table wikiTable = schema.getTableByName("WIKI");
        assertEquals("wiki", wikiTable.getQualifiedLabel());
        Column nameColumn = wikiTable.getColumnByName("name");
        assertEquals("wiki.name", nameColumn.getQualifiedLabel());

        assertEquals(
                "Column[name=name,columnNumber=0,type=VARCHAR,nullable=true,nativeType=TEXT,columnSize=2000000000]",
                dc.getColumnByQualifiedLabel("wiki.name").toString());
        assertEquals("Table[name=wiki,type=TABLE,remarks=null]", dc.getTableByQualifiedLabel("WIKI").toString());
    }

    public void testSingleQuoteInQuery() throws Exception {
        JdbcDataContext dc = new JdbcDataContext(_connection);
        Schema schema = dc.getDefaultSchema();
        Table wikiTable = schema.getTableByName("WIKI");
        Column nameColumn = wikiTable.getColumnByName("name");

        Query q = dc.query().from(wikiTable).select(nameColumn).where(nameColumn).isEquals("m'jello").toQuery();
        assertEquals(16384, dc.getFetchSizeCalculator().getFetchSize(q));

        assertEquals("SELECT wiki.name FROM wiki WHERE wiki.name = 'm'jello'", q.toSql());
        assertEquals("SELECT wiki.name FROM wiki WHERE wiki.name = 'm''jello'", dc.getQueryRewriter().rewriteQuery(q));

        DataSet ds = dc.executeQuery(q);
        assertFalse(ds.next());
    }

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

    public void testCharOfSizeOne() throws Exception {
        JdbcTestTemplates.meaningOfOneSizeChar(_connection);
    }

    public void testAutomaticConversionWhenInsertingString() throws Exception {
        Connection connection = _connection;

        assertNotNull(connection);

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

        assertFalse(connection.isReadOnly());

        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, "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();
                cb.insertInto(table).value("id", 3).value("birthdate", DateUtils.get(2011, Month.DECEMBER, 22))
                        .execute();
            }
        });

        dc.refreshSchemas();

        Column idColumn = dc.getColumnByQualifiedLabel("test_table.id");
        assertTrue(idColumn.isPrimaryKey());

        Column column = dc.getColumnByQualifiedLabel("test_table.birthdate");
        assertFalse(column.isPrimaryKey());

        // the jdbc driver represents the date as a VARCHAR
        assertEquals("Column[name=birthdate,columnNumber=1,type=VARCHAR,nullable=true,"
                + "nativeType=DATE,columnSize=2000000000]", column.toString());

        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.lang.String", ds.getRow().getValue(1).getClass().getName());
        assertTrue(ds.next());
        assertEquals("Row[values=[3, 2011-12-22]]", ds.getRow().toString());
        assertEquals("java.lang.String", ds.getRow().getValue(1).getClass().getName());
        assertFalse(ds.next());
        ds.close();

        connection.createStatement().execute("DROP TABLE test_table");
    }

    public void testConvertClobToString() throws Exception {
        JdbcDataContext dc = new JdbcDataContext(_connection);
        JdbcTestTemplates.convertClobToString(dc);
    }

    public void testInterpretationOfNull() throws Exception {
        JdbcTestTemplates.interpretationOfNulls(_connection);
    }
}