//  Benchmark 3: Run (bench3create.sql must be run to create tables)
//  Copyright  2002  -  FirstSQL, Inc.

import java.util.* ;
import java.io.* ;
import java.sql.* ;

public class Bench3Run
{
  public static final int ADD = 1 ;
  public static final int UPD = 2 ;
  public static final int DEL = 3 ;
  
  static final int DO_FACTOR = 4 ;

  public long id ;


  static final String text =
            "abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ" ;
  static final int textLen = text.length() ;

  Connection conn ;
  int custMax = 0 ;
  long cycles = 0 ;
  protected volatile boolean active = true ;

  Bench3Run parent ;
  protected String label ;

  public Bench3Run(long i, Connection c)
  {
    parent = this ;
    label = "Bench3" ;
    conn = c ;
  }
  void printResultSet(ResultSet results, String title) throws SQLException
  {
    postOutput(title + ":") ;
    int count = results.getMetaData().getColumnCount() ;
    StringBuffer buffer = new StringBuffer() ;
    while (results.next())
    {
      buffer.setLength(0) ;
      for (int i = 1 ; i <= count ; i++)
        buffer.append("  ").append(results.getObject(i)) ;
      postOutput(buffer.toString()) ;
    }
  }
  public int numCust(Statement statement) throws SQLException
  {
    ResultSet rs =
                statement.executeQuery("select id from surrogate_key " +
                                       "where table_name='customers'") ;
    int id = rs.next() ? rs.getInt(1) : 0 ;
    custMax = id ;
    rs.close() ;
    return id ;
  }
  public int addCust(Statement statement, int rand) throws SQLException
  {
    statement.executeUpdate("update surrogate_key set id = id + 1 " +
                            "where table_name='customers'") ;
    int id = numCust(statement) ;
    int size = rand % 128 ;
    int offset = size ;
    String name = randText(offset, size) ;
    String address = randText(offset += size, size) ;
    String city = randText(offset += size, size) ;
    offset = rand % (textLen - 1) ;
    statement.executeUpdate("insert into customers values(" + id + ",'" +
                            name + "','" + address + "','" + city + "','" +
                            text.substring(offset, offset + 2) + "','" +
                            rand + "',current_timestamp)") ;
    log(statement, id, 0, 0, 0, ADD) ;
    return id ;
  }
  public int numOrd(Statement statement, int cust) throws SQLException
  {
    ResultSet rs = statement.executeQuery("select max(ord_no) from orders" +
                                          " where cust_no = " + cust) ;
    rs.next() ;
    int id = rs.getInt(1) ;
    rs.close() ;
    return id ;
  }
  public int numOrdLines(Statement statement, int cust, int ord)
    throws SQLException
  {
    ResultSet rs = statement.executeQuery("select max(line_no)" +
                                          " from ord_lines" +
                                          " where cust_no = " + cust +
                                          " and ord_no = " + ord) ;
    rs.next() ;
    int id = rs.getInt(1) ;
    rs.close() ;
    return id ;
  }
  public int addOrd(Statement statement, int cust, int rand)
  throws SQLException
  {
    int id = numOrd(statement, cust) + 1 ;
    statement.executeUpdate("insert into orders values(" + cust + "," + id +
                            ",current_timestamp)") ;
    log(statement, cust, id, 0, 0, ADD) ;
    return id ;
  }
  public void addOrdLine(Statement statement, int cust, int ord, int line,
                        int prod, int qty)
  throws SQLException
  {
    statement.executeUpdate("insert into ord_lines values(" + cust + "," +
                            ord + "," + line + ",'" +
                            randText(prod, prod) + "'," + qty + ")") ;
    log(statement, cust, ord, 0, line, ADD) ;
  }
  public int addShip(Statement statement, int cust, int ord, int rand)
  throws SQLException
  {
    ResultSet rs = statement.executeQuery("select max(ship_no) from shipments" +
                                          " where cust_no = " + cust +
                                          " and ord_no = " + ord) ;
    rs.next() ;
    int id = rs.getInt(1) + 1 ;
    rs.close() ;
    statement.executeUpdate("insert into shipments values(" + cust + "," +
                            ord + "," + id + ",current_timestamp)") ;
    log(statement, cust, ord, id, 0, ADD) ;
    return id ;
  }
  public void addShipLine(Statement statement, int cust, int ord, int ship,
                          int line, int item, int qty)
  throws SQLException
  {
   
    statement.executeUpdate("insert into ship_lines values(" + cust + "," +
                            ord + "," + ship + "," + line + "," + item +
                            "," + qty + ")") ;
    log(statement, cust, ord, ship, line, ADD) ;
  }
  public boolean isCust(Statement statement, int cust) throws SQLException
  {
    ResultSet rs = statement.executeQuery("select*from customers " +
                                          "where cust_no = " + cust) ;
    boolean found = rs.next() ;
    rs.close() ;
    return found ;
  }
  public boolean delCust(Statement statement, int cust) throws SQLException
  {
    synchronized (conn)
    {
      conn.commit() ;
      boolean found ;
      if (found = isCust(statement, cust))
      {
        statement.executeUpdate("delete from customers " +
                                "where cust_no = " + cust) ;
        log(statement, cust, 0, 0, 0, DEL) ;
      }
      conn.commit() ;
      return found ;
    }
  }
  public boolean delOrd(Statement statement, int cust, int ord)
    throws SQLException
  {
    int result = statement.executeUpdate("delete from orders" +
                                         " where cust_no = " + cust +
                                         " and ord_no = " + ord) ;
    boolean success = result > 0 ;
    if (success)
      log(statement, cust, ord, 0, 0, DEL) ;
    return success ;
  }

  public void log(Statement statement, int cust, int ord, int ship, int line,
                  int type)
  {
/*
    try
    {
      statement.executeUpdate("insert into log values(" + cust + "," + ord +
                              "," + ship + "," + line + "," + type +
                              ",current_timestamp)") ;
    }
    catch (SQLException ex)
    {
      postOutput("log: " + ex) ;
      postOutput("  cust="+cust+" ord="+ord+" ship="+ship+" line="+line+
                 " type="+type);
    }
*/
  }


  protected String randText(int offset, int size)
  {
    StringBuffer buffer = new StringBuffer() ;
    while (size > 0)
    {
      offset %= textLen ;
      int len = textLen - offset ;
      if (len > size)
        len = size ;
      size -= len ;
      buffer.append(text.substring(offset, offset + len)) ;
      offset += len ;
    }
    return buffer.toString() ;
  }
  protected void exception(String title, SQLException sex)
  {
    String text ;
    text = this + title + " failure " +
           "{"+sex.getSQLState()+","+sex.getErrorCode()+"} "+sex ;
    //text = this + title + " failure " + sex ;
    postOutput(text) ;
    parent.setException() ;
  }

  public String getLabel()
  {
    return label ;
  }

  public String prefix()
  {
    return "<" + id + "> " + label ;
  }
  public String toString()
  {
    return prefix() ;
  }

  protected void postChange(int op)
  {
  }
  protected synchronized void postOutput(String text)
  {
    parent.output("<" + id + "> "+text) ;
  }
  protected synchronized void postMain(String text)
  {
    parent.main("Bench3<" + id + ">: "+text) ;
  }


  protected void fail(SQLException ex)
  {
    exception("", ex) ;
    synchronized (conn)
    {
      try
      {
        conn.rollback() ;
      }
      catch (SQLException rex)
      {
        exception(" rollback", rex) ;
      }
    }
  }
  protected void addLines(Statement statement, int cust, int ord,
                          int[][] lines) throws SQLException
  {
    for (int i = 0 ; i < lines.length ; i++)
    {
      int[] line = lines[i] ;
      addOrdLine(statement, cust, ord, i + 1, line[0], line[1]) ;
    }
  }
  protected void addItems(Statement statement, int cust, int ord, int ship,
                          int[][] items) throws SQLException
  {
    for (int i = 0 ; i < items.length ; i++)
    {
      int[] item = items[i] ;
      addShipLine(statement, cust, ord, ship, item[0], i + 1, item[1]) ;
    }
  }

  public int addCust(Statement statement, int select, int[][] lines)
  {
    int cust = 0 ;
    try
    {
      cust = addCust(statement, select) ;
      int ord = 0 ;
      if (cust > 0 && lines != null)
      {
        ord = addOrd(statement, cust, 0) ;
        if (ord > 0)
          addLines(statement, cust, ord, lines) ;
      }
      conn.commit() ;
      postOutput("New cust " + cust + " ord " + ord +
                 "(" + ((lines != null) ? lines.length : 0) + ")") ;
    }
    catch (SQLException ex)
    {
      fail(ex) ;
    }
    return cust ;
  }
  public int addOrd(Statement statement, int cust, int[][] lines)
  {
    int ord = 0 ;
    try
    {
      if (lines != null && isCust(statement, cust))
      {
        ord = addOrd(statement, cust, 0) ;
        if (ord > 0)
          addLines(statement, cust, ord, lines) ;
        else
          lines = null ;
      }
      else
        lines = null ;
      conn.commit() ;
      postOutput("New cust " + cust + " ord " + ord +
                 "(" + ((lines != null) ? lines.length : 0) + ")") ;
    }
    catch (SQLException ex)
    {
      fail(ex) ;
    }
    return ord ;
  }
  public int addShip(Statement statement, int cust, int ord, int[][] items)
  {
    int ship = 0 ;
    try
    {
      if (items != null && isCust(statement, cust))
      {
        ship = addShip(statement, cust, ord, 0) ;
        if (ship > 0)
          addItems(statement, cust, ord, ship, items) ;
        else
          items = null ;
      }
      else
        items = null ;
      conn.commit() ;
      postOutput("New cust " + cust + " ord " + ord + " ship " + ship +
                 "(" + ((items != null) ? items.length : 0) + ")") ;
    }
    catch (SQLException ex)
    {
      fail(ex) ;
    }
    return ord ;
  }
  public boolean changeAddress(Statement statement, int cust, int select)
    throws SQLException
  {
    boolean success = false ;
    try
    {
      synchronized (conn)
      {
        success = statement.executeUpdate("update customers set address = '" +
                                          randText(select, select) + "'" +
                                          "where cust_no = " + cust) > 0 ;
        conn.commit() ;
        postOutput("Change cust " + cust + " addr " + select + "   " +
                   success) ;
      }
    }
    catch (SQLException ex)
    {
      fail(ex) ;
    }
    return success ;
  }
  public boolean incrmQty(Statement statement, int cust, int ord, int line)
  throws SQLException
  {
    boolean success = false ;
    try
    {
      synchronized (conn)
      {
        success = statement.executeUpdate("update ord_lines" +
                                           " set qty = qty + 1" +
                                           " where cust_no = " + cust +
                                           " and ord_no = " + ord +
                                           " and line_no = " + line) > 0 ;
        conn.commit() ;
        postOutput("Incrm cust " + cust + " ord " + ord + " line " + line +
                   "   " + success) ;
      }
    }
    catch (SQLException ex)
    {
      fail(ex) ;
    }
    return success ;
  }
  public boolean cancelCust(Statement statement, int cust) throws SQLException
  {
    boolean success = false ;
    try
    {
      if (isCust(statement, cust))
      {
        int numOrd = numOrd(statement, cust) ;
        if (numOrd == 0)
          success = delCust(statement, cust) ;
      }
      postOutput("Cancel Cust " + cust + "   " + success) ;
    }
    catch (SQLException ex)
    {
      fail(ex) ;
    }
    return success ;
  }
  public boolean cancelOrd(Statement statement, int cust, int ord)
    throws SQLException
  {
    boolean success = false ;
    try
    {
      synchronized (conn)
      {
        int numLines = numOrdLines(statement, cust, ord) ;
        if (numLines == 0)
          success = delOrd(statement, cust, ord) ;
        conn.commit() ;
        postOutput("Cancel Order cust " + cust + ", ord " + ord +
                   "   " + success) ;
      }
    }
    catch (SQLException ex)
    {
      fail(ex) ;
    }
    return success ;
  }
  public void reportCust(Statement statement, int cust) throws SQLException
  {
    try
    {
      if (isCust(statement, cust))
      {
        ResultSet rs = statement.executeQuery(
                              "select cast(cust_no as smallint) as cust_no," +
                                 "cast(name as char(32)) as name," +
                                      "cast((select count(*) from orders " +
                                 "where customers.cust_no = orders.cust_no)" +
                                      "as smallint) as orders " +
                              "from customers where cust_no="+cust) ;
        printResultSet(rs, "Summary Cust " + cust);
        rs.close() ;
      }
      conn.commit() ;
    }
    catch (SQLException ex)
    {
      fail(ex) ;
    }
  }
  public void reportOrd(Statement statement, int cust) throws SQLException
  {
    try
    {
      if (isCust(statement, cust))
      {
        ResultSet rs = statement.executeQuery(
                   "select cast(cust_no as smallint) as cust_no," +
                   "       cast(ord_no as smallint) as ord_no," +
                   "       cast(line_no as smallint) as line_no," +
                   "       cast(item as varchar(24)) as item," +
                   "       cast(qty as smallint) as qty," +
                   "       cast((select sum(qty) from ship_lines" +
                   "            where ord_lines.cust_no = ship_lines.cust_no" +
                   "            and ord_lines.ord_no = ship_lines.ord_no" +
                   "            and ord_lines.line_no = ship_lines.ord_line)" +
                   "                     as smallint) as shipped " +
                   "from ord_lines where cust_no = "+cust +
                   " order by ord_no,line_no") ;
        printResultSet(rs, "Summary Orders cust " + cust);
        rs.close() ;
      }
      conn.commit() ;
    }
    catch (SQLException ex)
    {
      fail(ex) ;
    }
  }


  public void process() throws SQLException
  {

    Statement statement = conn.createStatement() ;

    int cust1 = addCust(statement, 5, new int[][] { new int[] { 4, 5 } }) ;
    int cust2 = addCust(statement, 5, new int[][] { new int[] { 4, 5 } }) ;
    int cust3 = addCust(statement, 5, new int[][] { new int[] { 7, 8 } }) ;
    addCust(statement, 6, new int[][] { new int[] { 7, 8 } }) ;
    addCust(statement, 7, new int[][] { new int[] { 6, 7 } }) ;
    addCust(statement, 8, new int[][] { new int[] { 6, 7 } }) ;
    int ord2_2 = addOrd(statement, cust2, new int[][] { new int[] { 8, 9 },
                                                        new int[] { 11, 12 },
                                                        new int[] { 10, 11 },
                                                        new int[] { 13, 14 },
                                                       }) ;
    addShip(statement, cust3, 1, new int[][] { new int[] { 1, 2 } }) ;
    changeAddress(statement, cust3, 9) ;
    addShip(statement, cust2, ord2_2, new int[][] { new int[] { 2, 12 },
                                                    new int[] { 4, 10 },
                                                  }) ;
    int cust7 = addCust(statement, 10, null) ;
    reportOrd(statement, cust2) ;
    incrmQty(statement, cust2, 1, 1) ;
    int cust8 = addCust(statement, 8, new int[][] { new int[] { 9, 3 } }) ;
    addShip(statement, cust3, 1, new int[][] { new int[] { 1, 6 } }) ;

    int cust9 = addCust(statement, 11, new int[][] {}) ;

    addShip(statement, cust1, 1, new int[][] { new int[] { 1, 5 } }) ;

    reportCust(statement, cust7) ;

    statement.close() ;

  }


  static boolean exception = false ;
  static synchronized void setException()
  {
    exception = true ;
  }
  static PrintWriter main = new PrintWriter(System.out, true) ;
  static String name = null ;
  static String password = "" ;
  static boolean delay = false ;
  public static void main(String[] argv)
    throws SQLException, ClassNotFoundException
  {
    Connection conn = null ;
    int count = 1 ;
    String url = "dbcp://local;user=demo" ;
    String driver = "COM.FirstSQL.Dbcp.DbcpDriver" ;
    boolean auto = false ;
    int argi = 0 ;
    while (argv.length > argi && argv[argi].startsWith("-"))
      argi = processToggle(argv, argi) ;
    if (argv.length > argi)
    {
      driver = argv[argi++] ;
      if (argv.length > argi)
      {
        url = argv[argi++] ;
        if (argv.length > argi)
        {
          try
          {
            count = Integer.parseInt(argv[argi++]) ;
            if (argv.length > argi)
              auto = argv[argi++].equalsIgnoreCase("auto") ;
          }
          catch (NumberFormatException ex)
          {
          }
        }
      }
    }
    main.println("Benchmark 3 (Version 1.02) : Run  " +
                 "(run bench3create.sql first to create tables)") ;
    main.println("Bench3: count="+count+" url="+url) ;
    main.println("Bench3: driver="+driver) ;

    try
    {
      out = new PrintWriter(new FileWriter("bench3.out"), true) ;
    }
    catch (IOException ex)
    {
    }
    try
    {

      Class.forName(driver) ;

      if (name != null)
      {
        main.println("Bench3: name="+name+" password="+password) ;
        conn = DriverManager.getConnection(url, name, password) ;
      }
      else
       conn = DriverManager.getConnection(url) ;

      // find out product name
      try
      {
        main.println("Bench3: Database Product: " + 
                    conn.getMetaData().getDatabaseProductName() + " " +
                    conn.getMetaData().getDatabaseProductVersion()) ;
      }
      catch (Exception ex)
      {
      }

      main.println("Bench3: Java Version=" +
                   System.getProperty("java.version") +
                   " JVM=" + System.getProperty("java.vm.name") + " (" +
                   System.getProperty("java.vm.version") + ")");

      // set auto-commit
      if (!auto)
        try
        {
          conn.setAutoCommit(false) ;
        }
        catch (SQLException ex)
        {
          main.println("Bench3: setAutoCommit Fails: " + ex.getMessage()) ;
        }
      boolean isAutoCommit = true ;
      try
      {
        isAutoCommit = conn.getAutoCommit() ;
      }
      catch (SQLException ex)
      {
        main.println("Bench3: getAutoCommit Fails: " + ex.getMessage()) ;
      }
      main.println("Bench3: autoCommit=" + isAutoCommit) ;

      Bench3Run run = new Bench3Run(1, conn) ;

      long initialTime = System.currentTimeMillis() ;
      long endTime = initialTime ;
      for (int i = 0 ; i < count ; i++)
      {
        long startTime = endTime ;
        run.process() ;
        endTime = System.currentTimeMillis() ;
        run.postMain("Run " + i + "  " + (endTime - startTime) + "ms") ;
      }

      run.postMain(count + " runs " +
                     (endTime - initialTime) + "ms" + "  --- " +
                     " avg run time " + ((endTime - initialTime) / count)) ;
      if (exception)
        run.postMain("*** Warning Exceptions found (see bench3.out) ***") ;
    }
    finally
    {
      if (conn != null)
        try
        {
          if (delay)
          {
            main.println("...Delay before disconnecting; hit Enter to exit") ;
            try
            {
              System.in.read() ;
            }
            catch (IOException ex)
            {
            }
          }
          conn.close() ;
        }
        catch (SQLException ex)
        {
        }
      if (out != null)
        out.close() ;
    }
  }

  static PrintWriter out = null ;
  public static void output(String msg)
  {
    if (out != null)
      out.println(msg) ;
  }
  public static void main(String msg)
  {
    output(msg) ;
    main.println(msg) ;
  }

  protected static int processToggle(String[] argv, int argi)
  {
    StringBuffer buf = new StringBuffer() ;
    if (argv[argi].length() > 1)
      switch (argv[argi].charAt(1))
      {
        case '?' :
          main.println("java  Bench3Run  [toggles] driver url count auto") ;
          main.println("  toggles:") ;
          main.println("    -U user-name") ;
          main.println("    -P password") ;
          main.println("    -D delay before disconnection") ;
          main.println("    -? ..show usage") ;
          System.exit(0) ;
        case 'U' :
          argi = processToggle(argv, argi, buf) ;
          if (buf.length() > 0)
            name = buf.toString() ;
          break ;
        case 'P' :
          argi = processToggle(argv, argi, buf) ;
          if (buf.length() > 0)
            password = buf.toString() ;
          break ;
        case 'D' :
          delay = true ;
          ++argi ;
          break ;
        default :
          main.println("Invalid toggle: " + argv[argi]) ;
          System.exit(0) ;
      }
    else
      ++argi ;
    return argi ;
  }
  protected static int processToggle(String[] argv, int argi,
                                     StringBuffer buf)
  {
    buf.setLength(0) ;
    if (argv[argi].length() == 2)
    {
      if (argv.length > argi + 1)
        buf.append(argv[++argi]) ;
    }
    else
      buf.append(argv[argi].substring(2)) ;
    return argi + 1 ;
  }
}