package stress;

import java.io.File;
import java.io.FileDescriptor;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;

public class TestSQLStatementsMultiThreaded {

	static final String stresdata_tb = "data";
	
	static final String createtbl_prefix =
		"CREATE TABLE "+stresdata_tb+" (\n"
		+"  id int(11) NOT NULL default '0',\n"
		+"  t int(11) NOT NULL default '0',\n"
		+"  myvarchar varchar(64) default NULL,\n"
  		+"  mytext text default NULL,\n"
		;


	static final String fielddef = " bigint(20) NOT NULL default '0',\n";
	
	//static final String createtbl_suffix = " PRIMARY KEY (id,t)\n" + ") TYPE=MyISAM;";
	static final String createtbl_suffix = " PRIMARY KEY (id,t)\n" + ") TYPE=InnoDB;";

	public static void main(String[] args) throws Exception {
		if(args.length<1) {
			System.err.println("<propsfilename> <ps:true|false> [nf [nid [ntests [nbatches]]]]");
			System.exit(1);
		}

		int ai=0;
		String filename = args[ai++];
		boolean doPS =	Boolean.valueOf(args.length > ai ? args[ai++] : "true").booleanValue();
		int NF =	Integer.parseInt(args.length > ai ? args[ai++] : "200"); //different fields
		int N_ID =	Integer.parseInt(args.length > ai ? args[ai++] : "50"); //different ids
		int NTBB =	Integer.parseInt(args.length > ai ? args[ai++] : "10"); //tests for each id, per batch
		int NB =	Integer.parseInt(args.length > ai ? args[ai++] : "5"); //batches
		int NUMTESTS = NB * NTBB;
		
		//------
		
		Properties props = new Properties();
		props.load(new FileInputStream(filename));
		
		props.store(new FileOutputStream(FileDescriptor.err), "config file: "+filename);
		System.err.println("------------");
		
		String user = (String)props.remove("user");
		String pwd = (String)props.remove("pwd");
		
		String path = (String)props.remove("path");
		String url = (String)props.remove("url");
		String driverclass = (String)props.remove("driverclass");
		
		String spaths = (String)props.remove("paths");
		File[] paths = null;
		if(spaths!=null) {
			String[] sa = spaths.split(",");
			paths = new File[sa.length];
			for (int i = 0; i < sa.length; i++) {
				paths[i] = new File(sa[i]);
			}
		}
		
		props.store(new FileOutputStream(FileDescriptor.out), "actual parameters");
		System.out.println("------------");
		
		Connection[] conn = new Connection[N_ID];
		try {
			ArrayList ulist = new ArrayList();
			
			File f = new File(path.replace('/', File.separatorChar));
			System.out.println(f + " : " + f.exists());
			ulist.add(f.toURL());

			if (paths != null) {
				for (int i = 0; i < paths.length; i++) {
					ulist.add(paths[i].toURL());
				}
			}

			URL[] ua = (URL[])ulist.toArray(new URL[ulist.size()]);
			for (int i = 0; i < ua.length; i++) {
				System.out.println("url classpath: " + ua[i]);
			}
			URLClassLoader uc = new URLClassLoader(ua);
			Driver d = (Driver)uc.loadClass(driverclass).newInstance();

			props.setProperty("user", user);
			if (pwd != null)
				props.setProperty("password", pwd);
			
			//conn = d.connect(url, props);
			for(int i = 0; i < conn.length; i++ ) {
				conn[i] = d.connect(url, props);
			}
			
			//-------------------------------
			
			prepareDB(conn[0], NF);
			
			//testGetMetadataAndClose(conn);
			
			workIt(conn, NF, N_ID, NUMTESTS);
			
			//workItBatch(conn[0], NF, N_ID, NTBB, NB);

			//-------------------------------
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			for(int i=0; i<conn.length; i++) {
				if (conn[i] != null) {
					try {
						conn[i].close();
					} catch (Exception e) {
					}
				}
			}
		}
	}

	static void prepareDB(Connection conn, int nfields) throws Exception {
		Statement st = conn.createStatement();

		try {
			st.execute("drop table "+stresdata_tb);
		} catch (Exception e) {
			System.out.println(e);
		}

		try {
			StringBuffer sb = new StringBuffer(createtbl_prefix);
			for (int i = 1; i <= nfields; i++) {
				sb.append("  field_").append(i).append("_abcdefghij").append(fielddef);
			}
			sb.append(createtbl_suffix);
			String sql = sb.toString();
			System.out.println(sql);
			st.execute(sql);
		} catch (Exception e) {
			System.out.println(e);
		}

	}

	static void testGetMetadataAndClose(Connection conn) throws Exception {
		long t1 = 0;
		long t2 = 0;
		int N = 1000000;

		for (int i = 1; i <= N; i++) {
			conn.getMetaData();
		}
		t1 = System.currentTimeMillis();
		for (int i = 1; i <= N; i++) {
			conn.getMetaData();
		}
		t2 = System.currentTimeMillis();
		System.out.println("getMetaData avg: " + 1000.0 * (t2 - t1) / N);

		for (int i = 1; i <= N; i++) {
			conn.isClosed();
		}
		t1 = System.currentTimeMillis();
		for (int i = 1; i <= N; i++) {
			conn.isClosed();
		}
		t2 = System.currentTimeMillis();
		System.out.println("isClosed avg: " + 1000.0 * (t2 - t1) / N);
	}


	//==== isolated methods, for optimizeit tracking only =====

	static final int insertByStatement(Statement st, String sql) throws Exception {
		return st.executeUpdate(sql.toString());
	}

	static final int insertByPreparedStatement(PreparedStatement pst) throws Exception {
		return pst.executeUpdate();
	}

	static final int updateByStatement(Statement st, String sql) throws Exception {
		return st.executeUpdate(sql.toString());
	}

	static final int updateByPreparedStatement(PreparedStatement pst) throws Exception {
		return pst.executeUpdate();
	}

	
	
	
	
	
	
	
	
	
	
	static void workIt(Connection[] connArray, final int NF, final int N_ID, final int NUMTESTS) throws Exception {

		final String myvarchar = "abcdefghijklmnopqrstuvwxyz012345abcdefghijklmnopqrstuvwxyz012345"; //64 chars
		final String mytext = myvarchar + myvarchar + myvarchar + myvarchar + myvarchar + myvarchar + myvarchar + myvarchar;//512 chars
		
		final StringBuffer insertStart = new StringBuffer("INSERT INTO "+stresdata_tb+" (id,t,myvarchar,mytext,");
		final StringBuffer prepInsert2 = new StringBuffer("?,?,?,?,");
		final StringBuffer prepUpd1 = new StringBuffer("UPDATE "+stresdata_tb+" SET ");
		
		for (int i = 1; i <= NF; i++) {
			insertStart.append("field_").append(i).append("_abcdefghij");
			prepInsert2.append('?');
			prepUpd1.append("field_").append(i).append("_abcdefghij=?");
			if (i < NF) {
				insertStart.append(',');
				prepInsert2.append(',');
				prepUpd1.append(',');
			}
		}
		insertStart.append(") VALUES (");

		final StringBuffer fullPrepInsert = new StringBuffer(insertStart.toString()).append(prepInsert2).append(')');
		System.out.println(fullPrepInsert.toString());

		final StringBuffer fullPrepUpd = new StringBuffer(prepUpd1.toString()).append(" WHERE (id=? AND t=?)");
		System.out.println(fullPrepUpd.toString());

		//System.out.println("pst_i: "+fullPrepInsert.toString().length());
		//System.out.println("pst_u: "+fullPrepUpd.toString().length());

		//////////////////////////////////////////////
		{
			Statement delete_st = connArray[0].createStatement();
			int ans = delete_st.executeUpdate("delete from "+stresdata_tb);
			delete_st.close();
			System.out.println("Delete: " + ans);
		}
		
		//-----
		
		final int N = N_ID * NUMTESTS;
		final Barrier bar = new Barrier(N_ID);
		
		//////////////////////////////////////////////
		
		
		for (int id = 1; id <= N_ID; id++) {
			//System.out.print(".");
			
			final int final_id = id;
			final Connection conn = connArray[id-1];
			
			Thread thr = new MyThread() {
				public void runImpl() throws Exception {
					Statement st = conn.createStatement();
					StringBuffer sql_i = new StringBuffer(insertStart.toString());
					int mark = sql_i.length();
			
					bar.listen();/////////////////////////////////////////
					try {
						for (int i = 1; i <= NUMTESTS; i++) {
							int t = i * 1000;
			
							//st
							sql_i.setLength(mark);
							sql_i.append(final_id).append(',');
							sql_i.append(t).append(',');
							
							sql_i.append('"').append(myvarchar).append('"').append(',');
							sql_i.append('"').append(mytext).append('"').append(',');
							
							for (int k = 1; k <= NF; k++) {
								sql_i.append(k);
								if (k < NF)
									sql_i.append(',');
							}
							sql_i.append(')');
							insertByStatement(st, sql_i.toString());
						}
					} finally {
						bar.finish();//////////////////////////////////////////
					}
					
					st.close();
				}
			};
			
			thr.start();
		}
		
		long d = bar.raiseAndWaitAll();
		System.out.println("st insert avg: " + 1000.0*d/N);
		Thread.sleep(1000);


		//////////////////////////////////////////////
		
		
		for (int id = 1; id <= N_ID; id++) {
			//System.out.print(".");
			
			final int final_id = id;
			final Connection conn = connArray[id-1];
			
			Thread thr = new MyThread() {
				public void runImpl() throws Exception {
			
					Statement st = conn.createStatement();
					StringBuffer sql_u = new StringBuffer();
					
					bar.listen();/////////////////////////////////////////
					try {
						for (int i = 1; i <= NUMTESTS; i++) {
							int t = i * 1000;
			
							sql_u.setLength(0);
			
							sql_u.append("UPDATE "+stresdata_tb+" SET ");
							for (int k = 1; k <= NF; k++) {
								sql_u.append("field_").append(k).append("_abcdefghij=").append(-k);
								if (k < NF)
									sql_u.append(',');
							}
							sql_u.append(" WHERE (id=").append(final_id);
							sql_u.append(" AND t=").append(t);
							sql_u.append(')');
							updateByStatement(st, sql_u.toString());
						}
					} finally {
						bar.finish();//////////////////////////////////////////
					}
					
					st.close();
				}
			};
			
			thr.start();
		}
		
		d = bar.raiseAndWaitAll();
		System.out.println("st update avg: " + 1000.0*d/N);
		Thread.sleep(1000);


		//////////////////////////////////////////////
		
		
		for (int id = 1; id <= N_ID; id++) {
			//System.out.print(".");
			
			final int final_id = id;
			final Connection conn = connArray[id-1];
			
			Thread thr = new MyThread() {
				public void runImpl() throws Exception {
					PreparedStatement pst_i = conn.prepareStatement(fullPrepInsert.toString());
					
					bar.listen();/////////////////////////////////////////
					try {
						for (int i = 1; i <= NUMTESTS; i++) {
							int t = i * 1000;
			
							//PreparedStatement pst_i = conn.prepareStatement(fullPrepInsert.toString()); //"insert into Data (id, t, f1, f2, f3, f4, f5) values(?,?,?,?,?,?,?)");
							
							pst_i.setInt(1, -final_id);
							pst_i.setInt(2, t);
							
							pst_i.setString(3, myvarchar);
							pst_i.setString(4, mytext);
							
							
							for (int k = 1; k <= NF; k++)
								pst_i.setLong(4 + k, k);
							insertByPreparedStatement(pst_i);
						}
					} finally {
						bar.finish();//////////////////////////////////////////
					}
					
					pst_i.close();
				}
			};
			
			thr.start();
		}
		
		d = bar.raiseAndWaitAll();
		System.out.println("pst insert avg: " + 1000.0*d/N);
		Thread.sleep(1000);
		
		
		//////////////////////////////////////////////
		
		
		for (int id = 1; id <= N_ID; id++) {
			//System.out.print(".");
			
			final int final_id = id;
			final Connection conn = connArray[id-1];
			
			Thread thr = new MyThread() {
				public void runImpl() throws Exception {
					PreparedStatement pst_u = conn.prepareStatement(fullPrepUpd.toString());
					
					bar.listen();/////////////////////////////////////////
					try {
						for (int i = 1; i <= NUMTESTS; i++) {
							int t = i * 1000;
							for (int k = 1; k <= NF; k++)
								pst_u.setLong(k, -k);
			
							pst_u.setInt(NF + 1, -final_id);
							pst_u.setInt(NF + 2, t);
							updateByPreparedStatement(pst_u);
						}
					} finally {
						bar.finish();//////////////////////////////////////////
					}
					pst_u.close();
				}
			};
			
			thr.start();
		}
		
		d = bar.raiseAndWaitAll();
		System.out.println("pst update avg: " + 1000.0*d/N);
		Thread.sleep(1000);
		
		//////////////////////////////////////////////
		
		
		System.out.println("done!");

	}

	
	
	
	//--------------------------

	static void workItBatch(Connection conn, int NF, int N_ID, int NB, int NTBB) throws Exception {

//		StringBuffer insertStart = new StringBuffer("INSERT INTO "+stresdata_tb+" (id,t,");
//		StringBuffer prepInsert2 = new StringBuffer("?,?,");
//		StringBuffer prepUpd1 = new StringBuffer("UPDATE "+stresdata_tb+" SET ");
		
		String myvarchar = "abcdefghijklmnopqrstuvwxyz012345abcdefghijklmnopqrstuvwxyz012345"; //64 chars
		String mytext = myvarchar + myvarchar + myvarchar + myvarchar;//256 chars
		mytext = mytext + mytext;//512 chars
		
		StringBuffer insertStart = new StringBuffer("INSERT INTO "+stresdata_tb+" (id,t,myvarchar,mytext,");
		StringBuffer prepInsert2 = new StringBuffer("?,?,?,?,");
		StringBuffer prepUpd1 = new StringBuffer("UPDATE "+stresdata_tb+" SET ");
		
		
		
		for (int i = 1; i <= NF; i++) {
			insertStart.append("field_").append(i).append("_abcdefghij");
			prepInsert2.append('?');
			prepUpd1.append("field_").append(i).append("_abcdefghij=?");
			if (i < NF) {
				insertStart.append(',');
				prepInsert2.append(',');
				prepUpd1.append(',');
			}
		}
		insertStart.append(") VALUES (");

		StringBuffer fullPrepInsert = new StringBuffer(insertStart.toString()).append(prepInsert2).append(')');
		System.out.println(fullPrepInsert.toString());

		StringBuffer fullPrepUpd = new StringBuffer(prepUpd1.toString()).append(" WHERE (id=? AND t=?)");
		System.out.println(fullPrepUpd.toString());

		PreparedStatement pst_i = conn.prepareStatement(fullPrepInsert.toString()); //"insert into Data (id, t, f1, f2, f3, f4, f5) values(?,?,?,?,?,?,?)");
		PreparedStatement pst_u = conn.prepareStatement(fullPrepUpd.toString()); //"update Data set f1=?, f2=?, f3=?, f4=?, f5=? where (id=? AND t=?)");
		Statement st = conn.createStatement();

		//---------

		int N = N_ID * NB * NTBB;
		StringBuffer sql_i = new StringBuffer(insertStart.toString());
		int mark = sql_i.length();

		StringBuffer sql_u = new StringBuffer();
		long t1 = 0;
		long t2 = 0;

		int ans = st.executeUpdate("delete from "+stresdata_tb);
		System.out.println("Delete: " + ans);
		System.out.print("Waiting, 5");
		Thread.sleep(1000);
		System.out.print(", 4");
		Thread.sleep(1000);
		System.out.print(", 3");
		Thread.sleep(1000);
		System.out.print(", 2");
		Thread.sleep(1000);
		System.out.print(", 1");
		Thread.sleep(1000);
		System.out.println(" go!");


		conn.setAutoCommit(false);

		//-----


		t1 = System.currentTimeMillis();
		for (int id = 1; id <= N_ID; id++) {
			System.out.print(".");
			for (int b = 1; b <= NB; b++) {
				for (int i = 1; i <= NTBB; i++) {
					int t = (b * NTBB + i) * 1000;

					//st
					sql_i.setLength(mark);
					sql_i.append(id).append(',');
					sql_i.append(t).append(',');
				
					sql_i.append('"').append(myvarchar).append('"').append(',');
					sql_i.append('"').append(mytext).append('"').append(',');
					
					for (int k = 1; k <= NF; k++) {
						sql_i.append(k);
						if (k < NF)
							sql_i.append(',');
					}
					sql_i.append(')');

					st.addBatch(sql_i.toString());
				}

				st.executeBatch();
				conn.commit();
			}
		}
		t2 = System.currentTimeMillis();
		System.out.println("st batch insert avg: " + 1000.0 * (t2 - t1) / N);

		t1 = System.currentTimeMillis();
		for (int id = 1; id <= N_ID; id++) {
			System.out.print(".");
			for (int b = 1; b <= NB; b++) {
				for (int i = 1; i <= NTBB; i++) {
					int t = (b * NTBB + i) * 1000;

					sql_u.setLength(0);

					sql_u.append("UPDATE "+stresdata_tb+" SET ");
					for (int k = 1; k <= NF; k++) {
						sql_u.append("field_").append(k).append("_abcdefghij=").append(-k);
						if (k < NF)
							sql_u.append(',');
					}
					sql_u.append(" WHERE (id=").append(id);
					sql_u.append(" AND t=").append(t);
					sql_u.append(')');

					st.addBatch(sql_u.toString());
				}

				st.executeBatch();
				conn.commit();
			}
		}
		t2 = System.currentTimeMillis();
		System.out.println("st batch update avg: " + 1000.0 * (t2 - t1) / N);

		//-----

		t1 = System.currentTimeMillis();
		for (int id = 1; id <= N_ID; id++) {
			System.out.print(".");
			for (int b = 1; b <= NB; b++) {
				for (int i = 1; i <= NTBB; i++) {
					int t = (b * NTBB + i) * 1000;

					//ps
					pst_i.setInt(1, -id);
					pst_i.setInt(2, t);
					
					pst_i.setString(3, myvarchar);
					pst_i.setString(4, mytext);
					
					for (int k = 1; k <= NF; k++)
						pst_i.setLong(4 + k, k);

					pst_i.addBatch();
				}

				pst_i.executeBatch();
				conn.commit();
			}
		}
		t2 = System.currentTimeMillis();
		System.out.println("pst batch insert avg: " + 1000.0 * (t2 - t1) / N);

		t1 = System.currentTimeMillis();
		for (int id = 1; id <= N_ID; id++) {
			System.out.print(".");
			for (int b = 1; b <= NB; b++) {
				for (int i = 1; i <= NTBB; i++) {
					int t = (b * NTBB + i) * 1000;

					for (int k = 1; k <= NF; k++)
						pst_u.setLong(k, -k);

					pst_u.setInt(NF + 1, -id);
					pst_u.setInt(NF + 2, t);

					pst_u.addBatch();
				}

				pst_u.executeBatch();
				conn.commit();
			}
		}
		t2 = System.currentTimeMillis();
		System.out.println("pst batch update avg: " + 1000.0 * (t2 - t1) / N);

		//-------

		conn.setAutoCommit(true);

		System.out.println("done!");

	}

	
	
	
	static abstract class MyThread extends Thread {
		public void run() {
			try {runImpl();}
			catch(Exception e) {
				e.printStackTrace();
			}
		}
		
		abstract void runImpl() throws Exception;
	}
	
	
	static class Barrier {
		int N;
		int count = 0;
		Object startline = new Object();
		
		Barrier(int n) {
			this.N = n;
		}
		
		synchronized void listen() throws InterruptedException {
			count++;
			//System.out.println(count);
			synchronized(startline) {
				startline.notify();
			}
			wait();
		}
		
		synchronized void finish() {
			count--;
			if(count<=0) {//last racer
				notifyAll();
				count = 0;
			}
		}
		
		long raiseAndWaitAll() throws InterruptedException {
			synchronized(startline) {
				while(count!=N) {
					startline.wait();
				}
			}
			
			synchronized(this) {
				//System.out.println("Go!!!");
				notifyAll();
				long t = System.currentTimeMillis();
				wait();
				t = System.currentTimeMillis() - t;
				count = 0;
				return t;
			}
		}
	}
}