import java.sql.*;
import java.io.*;
import java.text.*;

public class PerfTest implements Closeable {

  static final String  URL = System.getProperty("jdbc.url", "jdbc:hsqldb:test2");
  static final String  USR = System.getProperty("jdbc.user", "sa");
  static final String  PWD = System.getProperty("jdbc.password", "");
  static final int     MAX = Integer.getInteger("rows", 25000000);
  static final boolean MEM = Boolean.getBoolean("memory");
  static final boolean DWR = Boolean.getBoolean("delayWrite");

  Connection connection;
  Statement statement;
  String url, user, password;

  final int max;
  final boolean persistent;
  final boolean delayWrite;

  public static void main(String[] args) throws Exception {
    if (args.length > 0 && ("-?".equals(args[0]) || "-h".equals(args[0]) 
      || "-help".equals(args[0]) || "--help".equals(args[0]))) {
      String usage = 
        "java -Djdbc.url=URL -Djdbc.user=USR -Djdbc.password=PWD -Drows=MAX PerfTest [-?|-h]%n" + 
        "  URL    the jdbc connection url, default jdbc:hsqldb:test2%n" +
        "  USR    the jdbc connection user, default sa%n" + 
        "  PWD    the jdbc connection password, default \"\"%n" +
        "  MAX    the performance test max rows, default 25000000";
      System.out.println(usage);
      System.exit(0);
    }

    String  url = URL, usr = USR, pwd = PWD;
    boolean pss = !MEM;
    // Load JDBC driver for the old hsqldb/h2 version
    if (url.startsWith("jdbc:hsqldb:")) {
      Class.forName("org.hsqldb.jdbcDriver");
    } else if (url.startsWith("jdbc:h2:")) {
      Class.forName("org.h2.Driver");
    }

    Connection connection = DriverManager.getConnection(url, usr, pwd);
    Statement  statement  = connection.createStatement();
    PerfTest   perf       = new PerfTest(connection, statement, url, usr, pwd, MAX, pss, DWR);
    try {
      perf.test();
    } finally {
      perf.close();
    }
  }

  public PerfTest(Connection connection, Statement statement, 
    String url, String user, String password,
    int maxRows, boolean persistent, boolean delayWrite) {
    this.connection = connection;
    this.statement  = statement;
    this.url        = url;
    this.user       = user;
    this.password   = password;
    this.max        = maxRows;
    this.persistent = persistent;
    this.delayWrite = delayWrite;
  }

  public void test() throws Exception {
    final long staMillis = System.currentTimeMillis();
    boolean hsql = this.url.startsWith("jdbc:hsqldb:");
    boolean h2   = false;
    if (!hsql) hsql = h2 = this.url.startsWith("jdbc:h2:");

    if (hsql) print("Cache, index and performance tests");
    else print("Table, index and performance tests");

    String s;
    if (hsql) s = "CREATE CACHED TABLE Addr(ID INT PRIMARY KEY, First VARCHAR(50), Name VARCHAR(50), ZIP INT, Create_At datetime not null)";
    else s = "CREATE TABLE Addr(ID INT PRIMARY KEY, First VARCHAR(50), Name VARCHAR(50), ZIP INT, Create_At datetime not null)";
    statement.execute(s);

    s = "CREATE INDEX addr_index_name ON Addr(Name asc, Name desc)"; // h2 default desc?
    statement.execute(s);
    s = "CREATE INDEX addr_index_create_at ON Addr(Create_At asc, Create_At desc)";
    statement.execute(s);
    if (hsql) {
      if (h2) s = "SET WRITE_DELAY "+ (delayWrite? "100": "0");
      else s = "SET WRITE_DELAY "+ (delayWrite? "TRUE": "FALSE");
      statement.execute(s);
    }

    long start = System.currentTimeMillis();
    int  max   = this.max;
    int statSpan = Math.max(max / 5, 100);
    DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    connection.setAutoCommit(false);
    for (int i = 0; i < max; i++) {
      s = "INSERT INTO Addr VALUES(" + i + ",'Marcel" + i + "',"
        + "'Renggli" + (max - i - (i % 31)) + "',"
        + (3000 + i % statSpan) + ",'" 
        + df.format(new java.util.Date()) + "')";

      if (statement.executeUpdate(s) != 1) {
        throw new Exception("Insert failed");
      }

      if (i % statSpan == 0) printStatus("Inserted  ", i, max, start);
      if ((i + 1) % 1000 == 0) connection.commit();
    }
    
    printStatus("Inserted  ", max, max, start);
    print("");
            
    connection.setAutoCommit(true);
    s = "SELECT COUNT(*) FROM Addr";
    ResultSet r = statement.executeQuery(s);
    r.next();
    int c = r.getInt(1);
    if (c != max) {
      throw new Exception("Count should be " + (max) + " but is "+ c);
    }

    if (persistent && hsql) {
      // close & reopen to test backup
      connection.close();
      connection = DriverManager.getConnection(url, user, password);
      statement = connection.createStatement();
      if (hsql) {
        s = "SET WRITE_DELAY "+ (delayWrite? "TRUE": "FALSE");
        if (h2) s = "SET WRITE_DELAY "+ (delayWrite? "100": "0"); 
        statement.execute(s);
      }
    }

    start = System.currentTimeMillis();
    connection.setAutoCommit(false);
    for (int i = 0; i < this.max; i++) {
      s = "UPDATE Addr SET Name='Robert" + (i + (i % 31)) + "' WHERE ID=" + i;
      if (statement.executeUpdate(s) != 1) {
        throw new Exception("Update failed");
      }

      if (i % statSpan == 0) printStatus("Updated   ", i, max, start);
      if ((i + 1) % 1000 == 0) connection.commit();
    }

    printStatus("Updated   ", max, max, start);
    print("");

    if (persistent && hsql) {
      s = "SHUTDOWN IMMEDIATELY";
      statement.execute(s);
      try {
        connection.close();
      } catch (SQLException e) {
        String sqlstate = e.getSQLState();
        if (!"90098".equals(sqlstate)) throw e;
        // Here h2-1.3.176
      }
      // open the database; it must be restored after shutdown
      connection = DriverManager.getConnection(url, user, password);
      statement = connection.createStatement();
      if (hsql) {
        s = "SET WRITE_DELAY "+ (delayWrite? "TRUE": "FALSE");
        if (h2) s = "SET WRITE_DELAY "+ (delayWrite? "100": "0"); 
        statement.execute(s);
      }
    }

    start = System.currentTimeMillis();
    connection.setAutoCommit(false);
    for (int i = 0; i < max; i++) {
      s = "DELETE FROM Addr WHERE ID=" + (max - 1 - i);
      if (statement.executeUpdate(s) != 1) {
        throw new Exception("Delete failed");
      }

      if (i % statSpan == 0) printStatus("Deleted   ", i, max, start);
      if ((i + 1) % 1000 == 0) connection.commit();
    }

    printStatus("Deleted   ", max, max, start);
    print("Cleaning ..");
    statement.execute("DROP TABLE Addr");
    final long endMillis = System.currentTimeMillis();
    print("OK(total "+ (endMillis - staMillis) + "ms).");
  }
  
  @Override
  public void close() {
    this.url  = null;
    this.user = this.password = null;
    try {
      try {
        statement.close();
      } finally {
        connection.close();
      }
    } catch (SQLException e) {
      // Ignore
    }
  }

  static void printStatus(String s, int i, int max, long start) {
    String status = String.format("%s: %d/%d %d%% ", 
        s, i, max, (100L * i / max));

    long now = System.currentTimeMillis();
    if (now > start) {
        status += (i * 1000L / (now - start));
    } else {
        status += "--";
    }
    
    System.out.printf("%s rows/s%n", status);
  }

  static void print(String s) {
    System.out.println(s);
  }
  
}