// This file is part of OpenCabinet.
// Copyright 2008, Thilo Planz
//
// OpenCabinet is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// OpenCabinet is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with OpenCabinet.  If not, see <http://www.gnu.org/licenses/>.
// 
//
package net.sf.opencabinet.core.sql;

import static java.lang.String.format;

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

import javax.sql.DataSource;

import org.apache.tomcat.dbcp.dbcp.ConnectionFactory;
import org.apache.tomcat.dbcp.dbcp.DriverManagerConnectionFactory;
import org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory;
import org.apache.tomcat.dbcp.dbcp.PoolingDataSource;
import org.apache.tomcat.dbcp.pool.impl.GenericObjectPool;
import org.hsqldb.jdbc.jdbcDataSource;
import org.postgresql.ds.PGPoolingDataSource;

import com.mysql.jdbc.Driver;

public class UpdateBenchmark {

	public static void main(String[] argv) throws SQLException {

		int batchSize = 1000;

		DataSource ds = connect();
		setupTestData(ds, batchSize);

		// run four times

		for (int i = 0; i < 4; i++) {
			connectNoBinds(ds, batchSize);
			connectBinds(ds, batchSize);
			autoCommit(ds, batchSize);
			manualCommit(ds, batchSize);
			batchUpdate(ds, batchSize);

		}

		dropTestData(ds);

	}

	private static void connectNoBinds(DataSource ds, int batchSize)
			throws SQLException {
		// get a Connection from the DataSource every time,
		// run SQL with interpolated data

		long start = System.currentTimeMillis();
		for (int i = 0; i < batchSize; i++) {
			Connection conn = ds.getConnection();

			Statement st = conn.createStatement();
			st.executeUpdate(format(
					"update update_benchmark set name = '%s' where id = %d",
					"A", i + 1));
			st.close();
			conn.close();

		}
		System.out.println(format("A: connectNoBinds took %d ms", System
				.currentTimeMillis()
				- start));

	}

	private static void connectBinds(DataSource ds, int batchSize)
			throws SQLException {
		// get a Connection from the DataSource every time,
		// run a prepared statement with bind variables
		long start = System.currentTimeMillis();
		for (int i = 0; i < batchSize; i++) {
			Connection conn = ds.getConnection();
			PreparedStatement ps = conn
					.prepareStatement("update update_benchmark set name = ? where id = ?");
			ps.setString(1, "B");
			ps.setInt(2, i + 1);
			ps.executeUpdate();
			ps.close();
			conn.close();

		}
		System.out.println(format("B: connectBinds took %d ms", System
				.currentTimeMillis()
				- start));

	}

	private static void autoCommit(DataSource ds, int batchSize)
			throws SQLException {
		// get a Connection and prepare the statement only once,
		// keep autoCommit on
		long start = System.currentTimeMillis();
		Connection conn = ds.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("update update_benchmark set name = ? where id = ?");
		for (int i = 0; i < batchSize; i++) {
			ps.setString(1, "C");
			ps.setInt(2, i + 1);
			ps.executeUpdate();

		}
		ps.close();
		conn.close();
		System.out.println(format("C: autoCommit took %d ms", System
				.currentTimeMillis()
				- start));

	}

	private static void manualCommit(DataSource ds, int batchSize)
			throws SQLException {
		// get a Connection and prepare the statement only once,
		// turn autoCommit off and commit only once per batch
		long start = System.currentTimeMillis();
		Connection conn = ds.getConnection();
		conn.setAutoCommit(false);
		PreparedStatement ps = conn
				.prepareStatement("update update_benchmark set name = ? where id = ?");
		for (int i = 0; i < batchSize; i++) {
			ps.setString(1, "D");
			ps.setInt(2, i + 1);
			ps.executeUpdate();

		}
		conn.commit();
		conn.setAutoCommit(true);
		ps.close();
		conn.close();
		System.out.println(format("D: manualCommit took %d ms", System
				.currentTimeMillis()
				- start));

	}

	private static void batchUpdate(DataSource ds, int batchSize)
			throws SQLException {
		// get a Connection and prepare the statement only once,
		// run a batched update
		long start = System.currentTimeMillis();
		Connection conn = ds.getConnection();
		// turn off auto-commit, because if it is on, some databases commit
		// halfway through the batch
		conn.setAutoCommit(false);
		PreparedStatement ps = conn
				.prepareStatement("update update_benchmark set name = ? where id = ?");
		for (int i = 0; i < batchSize; i++) {
			ps.setString(1, "E");
			ps.setInt(2, i + 1);
			ps.addBatch();
		}
		ps.executeBatch();
		ps.close();
		conn.commit();
		conn.setAutoCommit(true);
		conn.close();
		System.out.println(format("E: batchUpdate took %d ms", System
				.currentTimeMillis()
				- start));
	}

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

		PreparedStatement ps = conn
				.prepareStatement("insert into update_benchmark values(?,?)");
		for (int i = 1; i <= batchSize; i++) {
			ps.setInt(1, i);
			ps.setString(2, "?");
			ps.execute();
		}
		ps.close();
		conn.close();
	}

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

	}

	private static DataSource connect() throws SQLException {

		if (false) {
			// in-memory Hsqldb
			jdbcDataSource ds = new jdbcDataSource();
			ds.setDatabase("jdbc:hsqldb:mem:benchmark");
			ds.setUser("sa");
			return ds;
		}
		if (true) {
			// Postgresql
			PGPoolingDataSource source = new PGPoolingDataSource();
			source.setServerName("192.168.0.3");
			source.setDatabaseName("test");
			source.setUser("test");
			source.setPassword("test");

			return source;
		}
		{
			// Mysql
			new Driver();
			GenericObjectPool connectionPool = new GenericObjectPool(null);
			connectionPool.setMinIdle(2);
			ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
					"jdbc:mysql://192.168.0.3/test", "test", "test");
			new PoolableConnectionFactory(connectionFactory, connectionPool,
					null, null, false, true);
			PoolingDataSource ds = new PoolingDataSource(connectionPool);

			return ds;
		}

	}
}