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

public class MysqlTest {

  static String url = "jdbc:mysql://devl/totah?user=totah";
  static int testSize = 1000;
  
  java.sql.Connection con;
  
  public static void main(String[] args) {
    try { 
      if (args.length > 0) url = args[0];
      if (args.length > 1) testSize = Integer.parseInt(args[1]);
      new MysqlTest();
    }catch (Exception x) {
      x.printStackTrace();
    }
  }//main
  
  public MysqlTest() throws Exception {
    System.out.println("url: " + url);
    System.out.println("size: " + testSize);
    Class.forName("com.mysql.jdbc.Driver");
    con = DriverManager.getConnection(url);
    
    long started = System.currentTimeMillis();
    createData();
    System.out.println("create time: " + (System.currentTimeMillis()-started));
    
    started = System.currentTimeMillis();
    testRun();
    System.out.println("select time: " + (System.currentTimeMillis()-started));
  }//constructor

  public void testRun() throws Exception {
    //this is a simulation to what my application does:
    for (int cus=1; cus<testSize; cus++) { //for each customer
      loadCustomer(cus);
      List<Long> pairs = getPairs(cus);
      for (long p : pairs) {
        long otherCus = getPairOther(p, cus);
        loadCustomer(otherCus);
      }//for pair
    }//for cus
  }//testRun

  long getPairOther(long pair, long cus) throws Exception {
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT PAIR.FIRST, PAIR.SECOND, PAIR.STATUS, PAIR.COUNT FROM PAIR WHERE PAIR.ID=" + pair);
    rs.next();
    long first = rs.getLong(1);
    long second = rs.getLong(2);
    rs.getInt(3);
    rs.getInt(4);
//    rs.close();
    stmt.close();
    if (first == cus)
      return second;
    else
      return first;  
  }//getOtherPair
  
  void loadCustomer(long cus) throws Exception {
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT CUSTOMER.CODE, CUSTOMER.REPLACEBY, CUSTOMER.REPLACES, CUSTOMER.NAME FROM CUSTOMER WHERE CUSTOMER.ID=" + cus);
    rs.next();
    rs.getString(1);
    rs.getLong(2);
    rs.getInt(3);
    rs.getString(4);
//    rs.close();
    stmt.close();
  }//loadCustomer
  
  public List<Long> getPairs(long customer) throws Exception {
    Statement stmt = con.createStatement();
    List<Long> list = new java.util.ArrayList();
    ResultSet rs = stmt.executeQuery("SELECT DISTINCT PAIR.ID FROM PAIR WHERE PAIR.FIRST = " + customer);
    while (rs.next())
      list.add(rs.getLong(1));
//    rs.close();
    stmt.close();
    stmt = con.createStatement();
    rs = stmt.executeQuery("SELECT DISTINCT PAIR.ID FROM PAIR WHERE PAIR.SECOND = " + customer);
    while (rs.next())
      list.add(rs.getLong(1));
//    rs.close();
    stmt.close();
    return list;
  }//getPairs
  
  void createData() throws Exception {
    Statement stmt = con.createStatement();
    System.out.println("driver: " + con.getMetaData().getDriverVersion());
    for (String s : creates)
      stmt.execute(s);
    long pairID = 1;
    for (int i=1; i <= testSize; i++) {
      stmt.execute("INSERT INTO CUSTOMER VALUES(" + i + ",'I" + i + "',0,0,'NAME "+i + "')");
      //create dummy pairs for this customer.
      for (int j=i-1; j>0 && j > i-12; j--)
        stmt.execute("INSERT INTO PAIR VALUES(" + pairID++ + "," + j + "," + i + ",0,0)");
    }
    stmt.close();
  }//createData
  
  String[] creates = new String[] {
    "DROP TABLE IF EXISTS CUSTOMER" ,
    "CREATE TABLE CUSTOMER (ID bigint(20) NOT NULL default 0, " 
      + "CODE varchar(16) default NULL,"
      + "REPLACEBY bigint(20) NOT NULL default 0,"
      + "REPLACES int(11) NOT NULL default 0,"
      + "NAME varchar(80) default NULL,"
      + "PRIMARY KEY  (ID)) ENGINE=InnoDB DEFAULT CHARSET=utf8",
    "DROP TABLE IF EXISTS PAIR",
    "CREATE TABLE PAIR (ID bigint(20) NOT NULL default 0,"
      + "FIRST bigint(20) NOT NULL default 0,"
      + "SECOND bigint(20) NOT NULL default 0,"
      + "STATUS int(11) NOT NULL default 0,"
      + "COUNT int(11) NOT NULL default 0,"
      + "PRIMARY KEY  (ID), KEY K_FIRST (FIRST), KEY K_SECOND (SECOND)) "
      + "ENGINE=InnoDB DEFAULT CHARSET=utf8",
  };//creates
}//class