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

/**
 * Sample program to reproduce the DERBY-388.  It's a bit
 * contrived, but it shows the problem.  It creates two
 * simple tables and then creates a trigger on T1 that
 * has a dependency on T2.  It then starts a bunch of
 * UPDATE statements going to keep the trigger firing,
 * and at some points does an "alter table" on T2, which
 * forces the trigger to be re-compiled while it's firing.
 * In this situation, one of two failures will occur:
 * either an NPE or an incorrect SYNTAX error.  When
 * this problem is fixed, this program will run through
 * to completion without any failures.
 *
 * NOTE: While this particular repro is contrived, this
 * problem CAN show up in a normal application environment
 * because Derby sometimes chooses to recompile triggers
 * automatically.  If it does so while a trigger is firing,
 * the result will be the same as witnessed by this program.
 */
public class go {

	private Connection conn;
	public static void main(String[] args) throws Exception {

		go sim = new go();
		sim.run();

	}
	
	public go() throws Exception {

		Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
		conn = DriverManager.getConnection("jdbc:derby:trigDB;create=true");

		conn.setAutoCommit(false);
			
		// Clean up from any previous runs.
		Statement s = conn.createStatement();
		try {
			s.execute("DROP TABLE T1");
		} catch (Exception e) {}
		try {
			s.execute("DROP TABLE T2");
		} catch (Exception e) {}

		// Create our objects.
		s.execute("CREATE TABLE T1 (ID INT)");
		s.execute("CREATE TABLE T2 (ID_2 INT)");
	    s.execute(
			"CREATE TRIGGER TRIG1 AFTER UPDATE OF ID ON T1" +
			"	REFERENCING NEW AS N_ROW OLD AS O_ROW FOR EACH ROW MODE DB2SQL" +
			"	UPDATE T2" +
			"	SET ID_2 = " +
			"	  CASE WHEN (N_ROW.ID <= 0) THEN N_ROW.ID" +
			"	  ELSE 6 END " +
			"   WHERE N_ROW.ID < ID_2"
		);

		conn.commit();
	}

	public void run() {

		try {

			// Statement to insert into T1.
			PreparedStatement ps1 = conn.prepareStatement(
				"INSERT INTO T1 VALUES (?)");

			// Statement to insert into T2.
			PreparedStatement ps2 = conn.prepareStatement(
				"INSERT INTO T2(ID_2) VALUES (?)");

			// Statement that will cause the trigger to fire.
			PreparedStatement ps3 = conn.prepareStatement(
				"UPDATE T1 SET ID=5");

			// Start a separate thread that will wait about
			// 5 seconds and then alter table T2.  That will
			// make it so that the trigger statement has to
			// be recompiled.
			Thread tDrop = new Thread(new CompileForcer());
			tDrop.start();

			for(int i=0;i<10;i++) {

				System.out.println("exec "+i);
				for(int id=0;id<10;id++) {

					ps2.setInt(1, id);
					ps2.executeUpdate();

					ps1.setInt(1, 2*id);
					ps1.executeUpdate();
					
					conn.commit();
					Thread.sleep(10);
				}
				
				System.out.println("Doing update to fire the trigger...");
				ps3.executeUpdate();
				
				conn.commit();
			}
			conn.close();
			tDrop.join();
			System.out.println("Done");
			
		}
		catch(java.sql.SQLException e) {
			e.printStackTrace();
			e.getNextException().printStackTrace();
		}
		catch(Exception e) {
			e.printStackTrace();
		}
	}

	public class CompileForcer implements Runnable {

		public void run() {

			try {
				System.out.println("-> CompileForcer thread going to sleep...");
				Thread.sleep(5000);
			} catch (Exception e) {}

			try {

				Connection conn = DriverManager.getConnection(
					"jdbc:derby:trigDB");

				System.out.println("-> CompileForcer about to alter T2, forcing recompile...");
				Statement st = conn.createStatement();
				st.execute("alter table t2 add column dummy int");

			} catch (Exception e) {
				e.printStackTrace(System.out);
			}
		}

	}
}