package net.sf.opencabinet.core.sql;

import static java.lang.String.format;
import static net.sf.opencabinet.core.sql.Changeset.Delete;
import static net.sf.opencabinet.core.sql.Changeset.Insert;
import static net.sf.opencabinet.core.sql.Changeset.Update;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import junit.framework.TestCase;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;

public class ScenarioBenchmarks {

	private static final String TABLE_A = "scenario_benchmark_a";

	private static final String NAME = "name";

	public static void main(String[] argv) throws SQLException {
		DataSource ds = UpdateBenchmark.connect();
		AccessDataSourceByPrimaryKey ads = new AccessDataSourceByPrimaryKey(ds);
		setupTestData(ds);

		int runs = 10;
		String[] tests = new String[] { "insert,update,delete",
				"insert,update,delete (JDBC)",
				"insert,update,delete (JDBC:DataSource)",
				"insert,update,delete (JDBC:interpolate)",
				"insert,update,delete (DbUtils)", "S I S U S D S",
				"S I S U S D S (JDBC)", "S I S U S D S (JDBC:DataSource)",
				"S I S U S D S (JDBC:interpolate)", "S I S U S D S (DbUtils)" };
		long[] times = new long[tests.length];
		// run times

		for (int i = -1; i < runs; i++) {
			// three transactions: insert, update, delete
			times[0] += insertUpdateDelete(i, ads, false);
			times[1] += insertUpdateDeleteJDBC(i, ds, false);
			times[2] += insertUpdateDeleteJDBC_DS(i, ds, false);
			times[3] += insertUpdateDeleteJDBC_Interpolate(i, ds, false);
			times[4] += insertUpdateDeleteDbUtils(i, ds, false);

			// select, insert , select , update , select, delete ,
			// select
			times[5] += insertUpdateDelete(i, ads, true);
			times[6] += insertUpdateDeleteJDBC(i, ds, true);
			times[7] += insertUpdateDeleteJDBC_DS(i, ds, true);
			times[8] += insertUpdateDeleteJDBC_Interpolate(i, ds, true);
			times[9] += insertUpdateDeleteDbUtils(i, ds, true);

			// skip the first run
			if (i == -1) {
				System.out.println("preparation run:");
				for (int j = 0; j < tests.length; j++) {
					System.out
							.println(format("%s took %d", tests[j], times[j]));
				}
				System.out.println();
				times = new long[tests.length];
			}
		}

		System.out.println(format("times for %d runs:", runs));
		for (int i = 0; i < tests.length; i++) {
			System.out.println(format("%s took %d", tests[i], times[i]));
		}
		dropTestData(ds);
	}

	private static long insertUpdateDelete(int run,
			AccessDataSourceByPrimaryKey ds, boolean doSelects)
			throws SQLException {
		long start = System.currentTimeMillis();
		if (doSelects) {
			TestCase.assertNull(ds.selectString(TABLE_A, NAME, run));
		}
		ds.commit(Insert(TABLE_A, run, String.valueOf(run)));
		if (doSelects) {
			TestCase.assertEquals(String.valueOf(run), ds.selectString(TABLE_A,
					NAME, run));
		}
		ds.commit(Update(TABLE_A, run, "updated"));
		if (doSelects) {
			TestCase.assertEquals("updated", ds
					.selectString(TABLE_A, NAME, run));
		}
		ds.commit(Delete(TABLE_A, run));
		if (doSelects) {
			TestCase.assertNull(ds.selectString(TABLE_A, NAME, run));
		}
		return System.currentTimeMillis() - start;
	}

	private static String getName(PreparedStatement select) throws SQLException {
		ResultSet rs = select.executeQuery();
		try {
			if (rs.next())
				return rs.getString(1);
			return null;
		} finally {
			rs.close();
		}

	}

	private static long insertUpdateDeleteJDBC(int run, DataSource ds,
			boolean doSelects) throws SQLException {
		long start = System.currentTimeMillis();
		Connection conn = ds.getConnection();
		PreparedStatement select = null;
		if (doSelects) {
			select = conn.prepareStatement(format(
					"select name from %s where id = ?", TABLE_A));
			select.setInt(1, run);
			TestCase.assertNull(getName(select));
		}
		{
			PreparedStatement ps = conn.prepareStatement(format(
					"insert into %s values (?,?)", TABLE_A));
			ps.setInt(1, run);
			ps.setString(2, String.valueOf(run));
			ps.executeUpdate();
			ps.close();
		}
		if (doSelects)
			TestCase.assertEquals(String.valueOf(run), getName(select));
		{
			PreparedStatement ps = conn.prepareStatement(format(
					"update %s set name = ? where id = ?", TABLE_A));
			ps.setInt(2, run);
			ps.setString(1, "updated");
			ps.executeUpdate();
			ps.close();
		}
		if (doSelects)
			TestCase.assertEquals("updated", getName(select));
		{
			PreparedStatement ps = conn.prepareStatement(format(
					"delete from %s where id = ?", TABLE_A));
			ps.setInt(1, run);
			ps.executeUpdate();
			ps.close();
		}
		if (doSelects) {
			TestCase.assertNull(getName(select));
			select.close();
		}
		conn.close();
		return System.currentTimeMillis() - start;
	}

	private static long insertUpdateDeleteJDBC_DS(int run, DataSource ds,
			boolean doSelects) throws SQLException {
		long start = System.currentTimeMillis();

		if (doSelects) {
			Connection conn = ds.getConnection();
			PreparedStatement select = conn.prepareStatement(format(
					"select name from %s where id = ?", TABLE_A));
			select.setInt(1, run);
			TestCase.assertNull(getName(select));
			select.close();
			conn.close();
		}

		{
			Connection conn = ds.getConnection();

			PreparedStatement ps = conn.prepareStatement(format(
					"insert into %s values (?,?)", TABLE_A));
			ps.setInt(1, run);
			ps.setString(2, String.valueOf(run));
			ps.executeUpdate();
			ps.close();
			conn.close();
		}

		if (doSelects) {
			Connection conn = ds.getConnection();
			PreparedStatement select = conn.prepareStatement(format(
					"select name from %s where id = ?", TABLE_A));
			select.setInt(1, run);
			TestCase.assertEquals(String.valueOf(run), getName(select));
			select.close();
			conn.close();
		}
		{
			Connection conn = ds.getConnection();

			PreparedStatement ps = conn.prepareStatement(format(
					"update %s set name = ? where id = ?", TABLE_A));
			ps.setInt(2, run);
			ps.setString(1, "updated");
			ps.executeUpdate();
			ps.close();
			conn.close();
		}
		if (doSelects) {
			Connection conn = ds.getConnection();
			PreparedStatement select = conn.prepareStatement(format(
					"select name from %s where id = ?", TABLE_A));
			select.setInt(1, run);
			TestCase.assertEquals("updated", getName(select));
			select.close();
			conn.close();
		}
		{
			Connection conn = ds.getConnection();

			PreparedStatement ps = conn.prepareStatement(format(
					"delete from %s where id = ?", TABLE_A));
			ps.setInt(1, run);
			ps.executeUpdate();
			ps.close();
			conn.close();
		}
		if (doSelects) {
			Connection conn = ds.getConnection();
			PreparedStatement select = conn.prepareStatement(format(
					"select name from %s where id = ?", TABLE_A));
			select.setInt(1, run);
			TestCase.assertNull(getName(select));
			select.close();
			conn.close();
		}
		return System.currentTimeMillis() - start;
	}

	private static String getName(Statement select, int id) throws SQLException {
		ResultSet rs = select.executeQuery(format(
				"select name from %s where id = %s", TABLE_A, id));
		try {
			if (rs.next())
				return rs.getString(1);
			return null;
		} finally {
			rs.close();
		}

	}

	private static long insertUpdateDeleteJDBC_Interpolate(int run,
			DataSource ds, boolean doSelects) throws SQLException {
		long start = System.currentTimeMillis();
		Connection conn = ds.getConnection();
		Statement ps = conn.createStatement();
		if (doSelects)
			TestCase.assertNull(getName(ps, run));
		ps.executeUpdate(format("insert into %s values (%d, '%d')", TABLE_A,
				run, run));
		if (doSelects)
			TestCase.assertEquals(String.valueOf(run), getName(ps, run));
		ps.executeUpdate(format("update %s  set name = '%s' where id = %s",
				TABLE_A, "updated", run));
		if (doSelects)
			TestCase.assertEquals("updated", getName(ps, run));
		ps.executeUpdate(format("delete from %s where id = %s", TABLE_A, run));
		if (doSelects)
			TestCase.assertNull(getName(ps, run));
		ps.close();
		conn.close();
		return System.currentTimeMillis() - start;
	}

	private static long insertUpdateDeleteDbUtils(int run, DataSource ds,
			boolean doSelects) throws SQLException {
		long start = System.currentTimeMillis();
		Connection conn = ds.getConnection();
		QueryRunner q = new QueryRunner();
		if (doSelects)
			TestCase.assertNull(q.query(conn, format(
					"select name from %s where id = ?", TABLE_A), run,
					new ScalarHandler()));
		q.update(conn, format("insert into %s values (?, ?)", TABLE_A),
				new Object[] { run, String.valueOf(run) });
		if (doSelects)
			TestCase.assertEquals(String.valueOf(run), q.query(conn, format(
					"select name from %s where id = ?", TABLE_A), run,
					new ScalarHandler()));
		q.update(conn, format("update %s  set name = ? where id = ?", TABLE_A),
				new Object[] { "updated", run });
		if (doSelects)
			TestCase.assertEquals("updated", q.query(conn, format(
					"select name from %s where id = ?", TABLE_A), run,
					new ScalarHandler()));
		q.update(conn, format("delete from %s where id = ?", TABLE_A), run);
		if (doSelects)
			TestCase.assertNull(q.query(conn, format(
					"select name from %s where id = ?", TABLE_A), run,
					new ScalarHandler()));
		conn.close();
		return System.currentTimeMillis() - start;
	}

	private static void setupTestData(DataSource ds) throws SQLException {
		Connection conn = ds.getConnection();
		conn
				.createStatement()
				.execute(
						"create table scenario_benchmark_a ( id integer not null primary key, name char(20))");

		conn.close();
	}

	private static void dropTestData(DataSource ds) throws SQLException {
		Connection conn = ds.getConnection();
		conn.createStatement().execute("drop table scenario_benchmark_a");
		conn.close();

	}

}