import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.h2.tools.DeleteDbFiles;


/**
 *
 */

public class MvccConflictVisibilityThreaded {
    public static final DB db = DB.H2;

    enum DB {
        H2("create table test(id int identity primary key, val int not null, unique(val))"),
        PG("create table test(id serial primary key, val int not null, unique(val))"),
        MY("create table test(id int primary key auto_increment, val int not null, unique(val)) type=innodb"),
        ;

        private final String tableString;

        private DB(String tableString) {
            this.tableString = tableString;
        }

        public Connection getConnection() throws SQLException {
            switch (this) {
                case H2:
                    return DriverManager.getConnection("jdbc:h2:~/test;MVCC=TRUE");
                case PG:
                    return DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", "test", "test");
                case MY:
                    return DriverManager.getConnection("jdbc:mysql://localhost/test", "test", "test");
            }
            throw new IllegalStateException("Unknown type: " + this);
        }

        public String getTableString() {
            return this.tableString;
        }

    }

    public static Connection getConnection() throws SQLException {
        return db.getConnection();
    }

    public static class FirstInserter extends Thread {
        public void run() {
            try {
                Connection conn = getConnection();
                conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
                conn.setAutoCommit(false);
                PreparedStatement insert = conn.prepareStatement("insert into test (val) values (?)");
                insert.setInt(1, 42);
                insert.execute();
                Thread.sleep(3000);
                conn.commit();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public static class SecondInserter extends Thread {
        public void run() {
            try {
                Connection conn = getConnection();
                PreparedStatement retr0 = conn.prepareStatement("select id from test where val = ?");
                PreparedStatement retr1 = conn.prepareStatement("select id from test where val = ?");
                PreparedStatement retr2 = conn.prepareStatement("select id from test where val = ?");
                conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
                conn.setAutoCommit(false);
                PreparedStatement insert = conn.prepareStatement("insert into test (val) values (?)");
                insert.setInt(1, 42);
                retr0.setInt(1, 42);
                retr1.setInt(1, 42);
                retr2.setInt(1, 42);
                // Make sure this execute happens after the first one, but before
                // the commit of the first one()
                Thread.sleep(1500);
                try {
                    ResultSet rs = retr0.executeQuery();
                    if (rs.next()) {
                        System.out.println("Unexpected found: " + rs.getInt(1));
                    } else {
                        // expected, not committed yet
                        System.out.println("Did not find yet as expected.");
                    }
                    insert.execute();
                    conn.commit();
                } catch (SQLException e) {
                    // assume unique constraint violation
                    conn.rollback();
                    ResultSet rs = retr1.executeQuery();
                    if (rs.next()) {
                        System.out.println("Recovered: " + rs.getInt(1));
                    } else {
                        System.out.println("Could not recover!");
                    }
                    rs.close();
                    // wait for the commit to happen
                    Thread.sleep(2000);
                    conn.rollback();
                    // retr1 seems to cache the result of the previous execution,
                    // which now is an incorrect value since the other thread
                    // committed, use retr2 instead
//                    rs = retr1.executeQuery();
//                    if (rs.next()) {
//                        System.out.println("Recovered AFTER commit: " + rs.getInt(1));
//                    } else {
//                        System.out.println("Could not recover after commit!");
//                    }
                    rs = retr2.executeQuery();
                    if (rs.next()) {
                        System.out.println("Recovered separate statement: " + rs.getInt(1));
                    } else {
                        System.out.println("Could not recover separate statement!");
                    }
                    rs.close();

                }
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * @param args
     */
    public static void main(String[] args) throws Exception {
        // delete the database named 'test' in the user home directory
        DeleteDbFiles.execute("~", "test", true);

        Class.forName("org.h2.Driver");
        Class.forName("org.postgresql.Driver");
        Class.forName("com.mysql.jdbc.Driver");

        Connection conn = getConnection();
        Statement stat = conn.createStatement();
        stat.execute("drop table if exists test");
        stat.execute(db.getTableString());
        conn.close();

        new FirstInserter().start();
        new SecondInserter().start();


    }

}