package squill.tests;

import squill.db.DatabaseDialect;
import squill.db.UpdateStatementCallback;
import squill.query.QueryContextImpl;
import squill.query.QueryContext;

import java.sql.*;

import static org.junit.Assert.assertTrue;
import static org.junit.Assert.assertEquals;
import org.junit.Test;

/**
 * @author Michael Hunger
 * @since 07.04.2009
 */
public class AutoIdTest {
    @Test
    public void testPostgres() throws Exception {
        Class.forName("org.postgresql.Driver");
        final Connection con = DriverManager.getConnection("jdbc:postgresql://localhost/squill", "squill", "squill");

        this.<Integer>checkAutoId(con, "create table test (id SERIAL, name varchar(20))");
    }

    @Test
    public void testHsqldb() throws Exception {

        Class.forName("org.hsqldb.jdbcDriver");
        final Connection con = DriverManager.getConnection("jdbc:hsqldb:mem:squill", "sa", "");

        this.<Integer>checkAutoId(con, "create table test (id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 100) PRIMARY KEY, name varchar(20))");
    }

    @Test
    public void testMysql() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        final Connection con = DriverManager.getConnection("jdbc:mysql://localhost/squill", "squill", "squill");

        this.<Long>checkAutoId(con, "create table test (id INTEGER AUTO_INCREMENT, name varchar(20), primary key(id))");
    }

    private <T extends Number> void checkAutoId(final Connection con, final String createTableDDL) throws Exception {
        final Statement stmt = con.createStatement();
        try {
            con.createStatement().execute("drop table test");
        } catch (SQLException e) { // ignore
        }
        stmt.execute(createTableDDL);
        final QueryContext queryContext = new QueryContextImpl(QueryContext.QueryType.INSERT);
        queryContext.setReturnId();
        final T id = DatabaseDialect.from(con).execute(con, "insert into test (name) values('squill')", null, null, queryContext, new UpdateStatementCallback<T>());
        assertTrue("serial id", id.longValue() > 0);
        final ResultSet rs = stmt.executeQuery("select name from test where id = " + id);
        assertTrue("satz gefunden", rs.next());
        assertEquals("squill", rs.getString("name"));
    }
}