package dbtst;

import java.lang.Object;
import java.lang.String;
import java.lang.Class;
import java.lang.Thread;
import java.lang.Exception;
import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;

/**
 * Written by Carl E. McMillin
 * CarlCo, carlymac@earthlink.net
 * on 05/11/2004.
 * */
public class driver extends Object {
  public final static String
      jdbcDriver = "org.postgresql.Driver",
      dbUrl = "jdbc:postgresql://192.168.0.20/test?user=postgres&password=";

  public driver() {
  }

  /**
   * Thread to lock the table in this test.
   * */
  private class LockThread extends Thread {
    public LockThread() {
    }

    public void run() {
      System.out.println("Lock thread startup.");
      System.out.flush();

      Object waitObj = new Object();

      try {
        //Create the connection and set autocommit to false.
        Connection conn = DriverManager.getConnection(dbUrl);

        conn.setTransactionIsolation(conn.TRANSACTION_READ_COMMITTED);
        conn.setAutoCommit(false);

        System.out.println("Lock thread locking table.");
        System.out.flush();

        //Statement to explicitly lock the table for exclusive access.
        PreparedStatement ps1 =
            conn.prepareStatement("LOCK table1 IN ACCESS EXCLUSIVE MODE");

        ps1.execute();
        ps1.close();

        System.out.println("Lock thread selecting all records from table," +
                           " but never reading the ResultSet.");
        System.out.flush();

        /* Statement to read records for UPDATE.  This should force the lock
           to be active. */
        PreparedStatement ps2 =
            conn.prepareStatement("SELECT * FROM table1 FOR UPDATE");

        ResultSet rs = ps2.executeQuery();

        if (rs != null) {
          rs.next();

          synchronized (waitObj) {
            /* Wait 30 seconds. This is enough time to bracket both
               BlockedThread and TimeoutThread */
            waitObj.wait(30000L);
          }

          rs.close();
        }

        ps2.close();
        conn.close();
      }
      catch (Exception ex) {
        System.err.println("Lockout thread exception:");
        System.err.flush();

        ex.printStackTrace();
      }
      catch (Error err) {
        System.err.println("Lockout thread error:");
        System.err.flush();

        err.printStackTrace();
      }
      finally {
        System.out.println("Lock thread shutdown.");
        System.out.flush();
      }
    }
  }

  /**
   * Thread executing a statement client-side, guarded by a TimeoutThread.
   * The timeout thread will kill the statement (thus the executing thread)
   * if the timeout occurs before the statement completes.  In this test,
   * the statement NEVER completes before the timeout.
   * */
  private class BlockedThread extends Thread {
    public BlockedThread(Connection withConn,
                         PreparedStatement withStmt) {
      conn = withConn;
      stmt = withStmt;
    }

    public void run() {
      System.out.println("Blocked thread startup.");
      System.out.flush();

      Object waitObj = new Object();

      try {
        synchronized (waitObj) {
          //Wait ten seconds.
          waitObj.wait(10000L);
        }

        System.out.println("Blocked thread attempting to execute statement " +
                           "which should block since we locked the table.");
        System.out.flush();

        //Execute the statement which should be blocked.
        stmt.execute();
        stmt.close();

        //Commit changes.
        conn.commit();

        conn.close();
      }
      catch (Exception ex) {
        System.err.println("Blocked thread exception:");
        System.err.flush();

        ex.printStackTrace();
      }
      catch (Error err) {
        System.err.println("Blocked thread error:");
        System.err.flush();

        err.printStackTrace();
      }
      finally {
        System.out.println("Blocked thread shutdown.");
        System.out.flush();
      }
    }

    private Connection conn;
    private PreparedStatement stmt;
  }

  /**
   * Thread guarding a BlockedThread. The timeout thread will kill the blocked
   * thread if the timeout occurs before the blocked-thread completes.  In
   * this test, the blocked-thread NEVER completes before the timeout.
   * */
  private class TimeoutThread extends Thread {
    public TimeoutThread(Connection withConn,
                         PreparedStatement withStmt) {
      conn = withConn;
      stmt = withStmt;
    }

    public void run() {
      System.out.println("Timeout thread startup.");
      System.out.flush();

      Object waitObj = new Object();

      try {
        synchronized (waitObj) {
          /* Wait twenty seconds.  This gives the blocked thread time to
             try its execution. */
          waitObj.wait(20000L);
        }

        System.out.println("Timeout thread killing blocked thread statement.");
        System.out.flush();

        //Kill the statement.
        stmt.cancel();
        stmt.close();

        //Close the connection.
        conn.rollback();
        conn.close();
      }
      catch (Exception ex) {
        System.err.println("Timeout thread exception:");
        System.err.flush();

        ex.printStackTrace();
      }
      catch (Error err) {
        System.err.println("Timeout thread error:");
        System.err.flush();

        err.printStackTrace();
      }
      finally {
        System.out.println("Timeout thread shutdown.");
        System.out.flush();
      }
    }

    private Connection conn;
    private PreparedStatement stmt;
  }

  /**
   * Client-side test of killing a blocked SQL query-execution if it
   * exceeds its allowed running time.
   * */
  public void test() {
    Object waitObj = new Object();

    try {
      //Try to load JDBC driver.
      Class.forName(jdbcDriver).newInstance();

      //Create the connection and set autocommit to false.
      Connection conn = DriverManager.getConnection(dbUrl);

      conn.setTransactionIsolation(conn.TRANSACTION_READ_COMMITTED);
      conn.setAutoCommit(false);

      //Statement to insert a record into table.
      PreparedStatement ps =
          conn.prepareStatement("INSERT INTO table1 VALUES (?)");

      ps.setInt(1, 1);

      //Lock the table.
      new LockThread().start();

      //Wait a bit to make sure lock is definitely started.
      synchronized (waitObj) {
        waitObj.wait(5000L);
      }

      /* Create a thread to execute the insert-statement. The thread will
         never get a chance to finish executing the statement.  The lock-thread
         holds exclusive access to the table and the TimeoutThread is going
         to cancel the statement before the lock is released. */
      new BlockedThread(conn, ps).start();

      //Create a thread to time-out the blocked thread.
      new TimeoutThread(conn, ps).start();
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    catch (Error err) {
      err.printStackTrace();
    }
  }

  public static void main(String[] args) {
    new driver().test();
  }
}