import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Formatter;



public class H2Test {
	private static final String DRIVER_CLASS_NAME = "org.h2.Driver";
	private static final String DB_URL_MVCC_ENCRYPTED = "jdbc:h2:/tmp/encryptedDB;FILE_LOCK=SOCKET;MVCC=true;CIPHER=AES";
	private static final String DB_URL_MVCC_NOTENCRYPTED = "jdbc:h2:/tmp/unencryptedDB;FILE_LOCK=SOCKET;MVCC=true";
	private static final String DB_URL_ENCRYPTED = "jdbc:h2:/tmp/encryptedDB;FILE_LOCK=SOCKET;CIPHER=AES";
	private static final String DB_URL_NOTENCRYPTED = "jdbc:h2:/tmp/unencryptedDB;FILE_LOCK=SOCKET";
	private static final String DB_USER = "testuser";
	private static final String DB_PASS = "testpass";
	private static final String DB_PASS_ENCRYPT = "testpass foo";
	
	private static final String DROP_STMT = "DROP TABLE IF EXISTS foo";
	private static final String CREATE_STMT = "CREATE TABLE foo (bar INT PRIMARY KEY)";
	private static final String INSERT_STMT_FMT = "INSERT INTO foo (bar) VALUES (%d)";
	private static final String ALTER_STMT = "ALTER TABLE foo ADD baz INT NOT NULL DEFAULT 0";
	
	private static final int MAX_TEST_ROWS = 200000;
	private static final int TEST_STEP = 10000;
	
	public static void main(String[] args) throws Exception {
		
		// load the driver
		Class.forName(DRIVER_CLASS_NAME);
		
		for (int i=0; i < MAX_TEST_ROWS; i += TEST_STEP) {
			doTest("encryped=false MVCC=false, " + i + " rows", DB_URL_NOTENCRYPTED, DB_USER, DB_PASS, i);
//			doTest("encryped=true  MVCC=false, " + i + " rows", DB_URL_ENCRYPTED, DB_USER, DB_PASS_ENCRYPT, i);
			doTest("encryped=false MVCC=true,  " + i + " rows", DB_URL_MVCC_NOTENCRYPTED, DB_USER, DB_PASS, i);
//			doTest("encryped=true  MVCC=true,  " + i + " rows", DB_URL_MVCC_ENCRYPTED, DB_USER, DB_PASS_ENCRYPT, i);
		}
		
	}
	
	private static void doTest(String description, String url, String user, String pass, int numRows) throws Exception {
		Connection conn = DriverManager.getConnection(url, user, pass);
		conn.setAutoCommit(true);
		
		Statement s = conn.createStatement();
		long startTime = System.currentTimeMillis();
		s.execute(DROP_STMT);
		s.execute(CREATE_STMT);
		
		for (int i = 0; i < numRows; i++) {
			String insertStmt = new Formatter().format(INSERT_STMT_FMT, i).toString();
			s.execute(insertStmt);
		}
		long createsDoneTime = System.currentTimeMillis();

		// TODO: h2 - please explain the difference better, how these apply to ALTER TABLE statements
		s.execute("SET MAX_MEMORY_ROWS 1000000");
		s.execute("SET MAX_MEMORY_UNDO 1000000");
		s.execute("SET MAX_OPERATION_MEMORY 0");
		s.execute(ALTER_STMT);
		
		long alterDoneTime = System.currentTimeMillis();

		s.close();
		conn.close();
		
		long createDelta = createsDoneTime - startTime;
		float createAvg = numRows == 0 ? 0 : ((float) createDelta / numRows);
		long alterDelta = alterDoneTime - createsDoneTime;
		float alterAvg = numRows == 0 ? 0 : ((float) alterDelta / numRows);

		String output =
				new Formatter().format("%s: insert elapsed=%dms avg=%.3f, alter elapsed=%dms avg=%.3f", description,
						createDelta, createAvg, alterDelta, alterAvg).toString();
		
		System.out.println(output);
	}
	
}