import java.sql.*;

/**
 * Author:  Ming Fang
 * Date:    7/19/15.
 * Email:   mingf@cs.cmu.edu
 */
public class PelotonTest {
  private final String url = "jdbc:postgresql://localhost:57721/";
  private final String username = "postgres";
  private final String pass = "postgres";

  private final String DROP = "DROP TABLE IF EXISTS peloton_test";
  private final String DDL = "CREATE TABLE peloton_test (id INT PRIMARY KEY, data TEXT)";
  private final String INSERT = "INSERT INTO peloton_test VALUES (?,?)";
  private final String SEQSCAN = "SELECT * FROM peloton_test WHERE id != ?";
  private final String INDEXSCAN = "SELECT * FROM peloton_test WHERE id = ?";
  private final String BITMAPSCAN = "SELECT * FROM peloton_test WHERE id < ?";
  private final String UPDATE_BY_INDEXSCAN = "UPDATE peloton_test SET data=? WHERE id=?";
  private final String UPDATE_BY_SCANSCAN = "UPDATE peloton_test SET data=?";

  private final Connection conn;

  public PelotonTest() throws SQLException {
    try {
      Class.forName("org.postgresql.Driver");
    } catch(ClassNotFoundException e) {
      e.printStackTrace();
    }
    conn = this.makeConnection();
    return;
  }

  private Connection makeConnection() throws SQLException {
    Connection conn = DriverManager.getConnection(url, username, pass);
    return conn;
  }

  public void Close() throws SQLException {
    conn.close();
  }

  /**
   * Drop if exists and create testing database
   * @throws SQLException
   */
  public void Init() throws SQLException {
    conn.setAutoCommit(true);
    Statement stmt = conn.createStatement();
    stmt.execute(DROP);
    stmt.execute(DDL);
    conn.setAutoCommit(false);
    System.out.println("Test db created.");
  }

  /**
   * Insert a record
   * @param i the id of the record
   * @throws SQLException
   */
  public void Insert(int i) throws SQLException {
    PreparedStatement stmt = conn.prepareStatement(INSERT);
    String data = "Ming says hello world and id = " + i;
    stmt.setInt(1, i);
    stmt.setString(2, data);
    stmt.executeUpdate();
    System.out.println("Inserted: id: " + i + ", data: " + data);
    return;

  }

  public void SeqScan() throws SQLException {
    System.out.println("SeqScan Test:");
    System.out.println("Query: " + SEQSCAN);
    PreparedStatement stmt = conn.prepareStatement(SEQSCAN);
    stmt.setInt(1, 3);
    ResultSet r = stmt.executeQuery();
    while (r.next()) {
      System.out.println("SeqScan: " + r.getString(2));
    }
  }

  /**
   * Perform Index Scan with a simple equal qualifier
   * @param i the param for the equal qualifier
   * @throws SQLException
   */
  public void IndexScan(int i) throws SQLException {
    System.out.println("IndexScan Test: ? = " + i);
    System.out.println("Query: " + INDEXSCAN);
    PreparedStatement stmt = conn.prepareStatement(INDEXSCAN);
    stmt.setInt(1, i);
    ResultSet r = stmt.executeQuery();
    while (r.next()) {
      System.out.println("IndexScanTest got tuple: id: " + r.getString(1) + ", data: " + r.getString(2));
    }
  }

  /**
   * Perform Index Scan with a simple equal qualifier
   * @param i the param for the equal qualifier
   * @throws SQLException
   */
  public void BitmapScan(int i) throws SQLException {
    System.out.println("BitmapScan Test: ? = " + i);
    System.out.println("Query: " + BITMAPSCAN);
    PreparedStatement stmt = conn.prepareStatement(BITMAPSCAN);
    stmt.setInt(1, i);
    ResultSet r = stmt.executeQuery();
    while (r.next()) {
      System.out.println("BitmapScanTest got tuple: id: " + r.getString(1) + ", data: " + r.getString(2));
    }
  }

  public void UpdateByIndex(int i) throws SQLException {
    System.out.println("Update Test: ? = " + i);
    System.out.println("Query: " + UPDATE_BY_INDEXSCAN);
    PreparedStatement stmt = conn.prepareStatement(UPDATE_BY_INDEXSCAN);
    stmt.setInt(2, i);
    stmt.setString(1, "Updated");
    stmt.executeUpdate();
    System.out.println("Inserted: id: " + i + ", data: Updated");

  }


  public void UpdateBySeqScan() throws SQLException {
    System.out.println("Update Test: ");
    System.out.println("Query: " + UPDATE_BY_SCANSCAN);
    PreparedStatement stmt = conn.prepareStatement(UPDATE_BY_SCANSCAN);
    stmt.setString(1, "Updated");
    stmt.executeUpdate();
    System.out.println("Inserted: data: Updated");

  }


  static public void main(String[] args) throws Exception {
    PelotonTest pt = new PelotonTest();
    pt.Init();
    for (int i = 0; i < 10; i++) {
      pt.Insert(i);
    }
    pt.BitmapScan(5);
    //pt.UpdateBySeqScan();
    //pt.IndexScan(3);
    pt.Close();
  }
}

@@ -734,16 +734,27 @@
 HERE id 
+%3E ? and id 
 %3C ?%22;%0A  
@@ -918,16 +918,102 @@
 data=?%22;
+%0A  private final String DELETE_BY_INDEXSCAN = %22DELETE FROM peloton_test WHERE id = ?%22;
 %0A%0A  priv
@@ -3304,32 +3304,39 @@
 BitmapScan(int i
+, int j
 ) throws SQLExce
@@ -3385,32 +3385,43 @@
  Test: ? = %22 + i
+ + %22, %22 + j
 );%0A    System.ou
@@ -3534,32 +3534,55 @@
 t.setInt(1, i);%0A
+    stmt.setInt(2, j);%0A
     ResultSet r 
@@ -4478,16 +4478,348 @@
 %0A%0A  %7D%0A%0A%0A
+  public void DeleteByIndexScan(int i) throws SQLException %7B%0A    System.out.println(%22Delete Test: %22);%0A    System.out.println(%22Query: %22 + DELETE_BY_INDEXSCAN);%0A    PreparedStatement stmt = conn.prepareStatement(DELETE_BY_INDEXSCAN);%0A    stmt.setInt(1, i);%0A    stmt.executeUpdate();%0A    System.out.println(%22Deleted: id = %22 + i);%0A%0A  %7D%0A
   static
@@ -4866,24 +4866,24 @@
 Exception %7B%0A
-
     PelotonT
@@ -4990,16 +4990,18 @@
   %7D%0A    
+//
 pt.Bitma
@@ -5006,17 +5006,85 @@
 mapScan(
-5
+2, 5);%0A    pt.SeqScan();%0A    pt.DeleteByIndexScan(3);%0A    pt.SeqScan(
 );%0A    /

add delete test