import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.io.StringWriter;
import java.io.PrintWriter;

public class RollbackTest {

	private String m_driverClass = "com.mysql.jdbc.Driver";
	private String m_jdbcUrl = 
		"jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8";
	private String m_dbUser = "";
	private String m_dbPass = "";
	
	private String m_dropDDL = 
		"DROP TABLE IF EXISTS `test`.`name_value_pairs` ";
	
	private String m_createDDL = 
	    "CREATE TABLE  `test`.`name_value_pairs` (" +
		"	  `PAIR_ID` int(10) unsigned NOT NULL auto_increment, " +
		"	  `PAIR_NAME` varchar(45) NOT NULL default '', " + 
		"	  `PAIR_VALUE` varchar(45) NOT NULL default '', " +
		"	  PRIMARY KEY  (`PAIR_ID`) " +
		"	) ENGINE=InnoDB;";
	
	private int m_insertQuota = 5;
	private int m_dieAfterInsert = 3;
	
	public RollbackTest() {
		//
	}
	
	/**
	 * Executes an algorithm for testing transaction management
	 * techniques. Prints an error message if the database
	 * is in an invalid state after a call to execTxn().
	 *
	 */
	public void exec() {
		
		try {
			
		  execDDL();
		
		  try {
		    execTxn();
		  } catch ( Throwable t ) { // trap so we always call verifyState()
			  say("exec() trapped an execTxn() Throwable", t);
		  }
		
		  verifyState();
		  
		  say("Test successful");
		  
		} catch ( Exception e ) {
			
			say("Test execution failed", e);
			
		}
		
	}
	
	/**
	 * Drops and creates the table targeted by execTxn().
	 * 
	 * @throws SQLException
	 */
	protected void execDDL() throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		try {
			
			conn = newConnection();
			stmt = conn.createStatement();
			stmt.executeUpdate(m_dropDDL);
			stmt.executeUpdate(m_createDDL);
			
		} catch ( ClassNotFoundException e ) {
			throw new RuntimeException("System error executing DDL", e);
		} finally {
			try {
				stmt.close();
			} catch (Exception e) {}
			try {
				conn.close();
			} catch (Exception e) {}
		}
	}
	
	/**
	 * Executes a series of db write statements, possibly failing before
	 * completing all scheduled work. 
	 * 
	 * @throws SQLException
	 */
	protected void execTxn() throws SQLException {
		
		Connection conn = null;
		Statement stmt = null;
		try {
			
			say("execTxn()'s insert quota is [" + m_insertQuota + "]");
			say("execTxn() will fail after insert [" + m_dieAfterInsert + "]");
			conn = newConnection();
			say("execTxn() retrieved Connection. [auto-commit: " + conn.getAutoCommit()+ "]");
			say("execTxn() starting transaction");
			conn.setAutoCommit(false);
			say("execTxn() retrieved Connection. [auto-commit: " + conn.getAutoCommit()+ "]");
			
			stmt = conn.createStatement();
			for ( int p =0; p < m_insertQuota; p++ ) {
				
				say("execTxn() executing row insert [count: " + (p + 1) + "]");
				
				stmt.executeUpdate(
						"INSERT INTO name_value_pairs (PAIR_NAME,PAIR_VALUE) VALUES ('PAIR_NAME " + 
						p + "', 'PAIR VALUE " + p + "')");
				if (m_dieAfterInsert == p + 1) {
					say("execTxn() failing with an OutOfMemoryError");
					throw new OutOfMemoryError();
				}
			}
			
			conn.commit();
			
		} catch ( Exception e ) {
			
			try {
				say("execTxn() rolling back db operations");
				conn.rollback();
			} catch ( Exception ee ) {
				say("Failed to roll back transaction", ee);
			}
			
			if ( e instanceof SQLException ) {
				throw (SQLException) e;
			} else if ( e instanceof RuntimeException ) {
				throw (RuntimeException) e;
			} else {
				throw new RuntimeException("failed to complete transaction", e);
			}
			
		} finally {
			
			try {
				stmt.close();
			} catch ( Exception e ) {}
			
			try {
				say("execTxn() enabling auto commit and closing connection");
				conn.setAutoCommit(true);
				conn.close();
			} catch ( Exception e ) {}
			
			
		}
		
	}
	
	/**
	 * Determins if the database is in a valid state assuming
	 * execTxn() has been executed once against an empty
	 * target table. "Valid" means that if execTxn() should have
	 * been expected to fail and rollback, no records should
	 * exist in the target table.
	 * 
	 * 
	 * @throws IllegalStateException if the database is not
	 *   in a valid state. 
	 * @throws SQLException if database connectivity or queries
	 *   fail
	 */
	protected void verifyState() throws SQLException, IllegalStateException {
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = newConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT COUNT(PAIR_ID) FROM name_value_pairs");
			rs.next();
			int cnt = rs.getInt(1);
			boolean shouldBeEmpty = 
				m_dieAfterInsert > 0 && m_dieAfterInsert <= m_insertQuota;
			
			if ( shouldBeEmpty ) {
				if ( cnt != 0 ) {
					throw new IllegalStateException("Found [" + cnt + 
							"] rows but expected [0]");
				}
			} else {
				if ( cnt != m_insertQuota ) {
					throw new IllegalStateException("Found [" + cnt + 
							"] but rows expected [" + m_insertQuota + "]");
				} 
			}
				
			
				
			
		
		} catch ( ClassNotFoundException e ) {
			
			throw new RuntimeException("System error verifying database state", e);
			
		} finally {
			
			try {
				rs.close();
			} catch ( Exception e ) {}
			try {
				stmt.close();
			} catch ( Exception e ) {}
			try {
				conn.close();
			} catch ( Exception e ) {}
			
		}
		
	}
	
	protected Connection newConnection() 
	throws ClassNotFoundException, SQLException {
		
		Class.forName(m_driverClass);
		return DriverManager.getConnection(m_jdbcUrl, m_dbUser, m_dbPass);
		
	}
	
	protected void say(String _line) {
		System.out.println(_line);
	}
	
	protected void say(String _line, Throwable _err) {
		say(_line);
		StringWriter sw = new StringWriter();
		_err.printStackTrace(new PrintWriter(sw));
		say(sw.toString());
	}
	
	public static void main(String[] _args) {
		RollbackTest test = new RollbackTest();
		test.exec();
	}
	
	
}