/*
 Copyright (c) 2002, 2010, Oracle and/or its affiliates. All rights reserved.
 

  The MySQL Connector/J is licensed under the terms of the GPLv2
  <http://www.gnu.org/licenses/old-licenses/gpl-2.0.html>, like most MySQL Connectors.
  There are special exceptions to the terms and conditions of the GPLv2 as it is applied to
  this software, see the FLOSS License Exception
  <http://www.mysql.com/about/legal/licensing/foss-exception.html>.

  This program 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; version 2
  of the License.

  This program 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 this
  program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth
  Floor, Boston, MA 02110-1301  USA



 */
package testsuite.perf;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.NumberFormat;

import testsuite.BaseTestCase;

/**
 * Simple performance testing unit test.
 * 
 * @author Mark Matthews
 */
public class LoadStorePerfTest extends BasePerfTest {
	/** The table type to use (only for MySQL), 'HEAP' by default */
	private String tableType = "HEAP";

	private boolean takeMeasurements = false;

	private boolean useColumnNames = false;

	private boolean largeResults = false;

	/**
	 * Constructor for LoadStorePerfTest.
	 * 
	 * @param name
	 *            the name of the test to run
	 */
	public LoadStorePerfTest(String name) {
		super(name);

		String newTableType = System
				.getProperty("com.mysql.jdbc.test.tabletype");

		this.largeResults = "TRUE"
				.equalsIgnoreCase(System
						.getProperty("com.mysql.jdbc.testsuite.loadstoreperf.useBigResults"));

		if ((newTableType != null) && (newTableType.length() > 0)) {
			this.tableType = newTableType;

			System.out.println("Using specified table type of '"
					+ this.tableType + "'");
		}
	}

	/**
	 * Runs all tests in this test case
	 * 
	 * @param args
	 *            ignored
	 * 
	 * @throws Exception
	 *             if an error occurs
	 */
	public static void main(String[] args) throws Exception {
		new LoadStorePerfTest("test1000Transactions").run();
	}

	/**
	 * @see junit.framework.TestCase#setUp()
	 */
	public void setUp() throws Exception {
		super.setUp();

		try {
			this.stmt.executeUpdate("DROP TABLE perfLoadStore");
		} catch (SQLException sqlEx) {
			// ignore
		}

		String dateTimeType = "DATETIME";

		if (BaseTestCase.dbUrl.indexOf("oracle") != -1) {
			dateTimeType = "TIMESTAMP";
		}

		//
		// Approximate a run-of-the-mill entity in a business application
		//
		String query = "CREATE TABLE perfLoadStore (priKey INT NOT NULL, "
				+ "fk1 INT NOT NULL, " + "fk2 INT NOT NULL, " + "dtField "
				+ dateTimeType + ", " + "charField1 CHAR(32), "
				+ "charField2 CHAR(32), " + "charField3 CHAR(32), "
				+ "charField4 CHAR(32), " + "intField1 INT, "
				+ "intField2 INT, " + "intField3 INT, " + "intField4 INT, "
				+ "doubleField1 DECIMAL," + "doubleField2 DOUBLE,"
				+ "doubleField3 DOUBLE," + "doubleField4 DOUBLE,"
				+ "PRIMARY KEY (priKey))";

		if (BaseTestCase.dbUrl.indexOf("mysql") != -1) {
			query += (getTableTypeDecl() + " =" + this.tableType);
		}

		this.stmt.executeUpdate(query);

		String currentDateValue = "NOW()";

		if (BaseTestCase.dbUrl.indexOf("sqlserver") != -1) {
			currentDateValue = "GETDATE()";
		}

		if (BaseTestCase.dbUrl.indexOf("oracle") != -1) {
			currentDateValue = "CURRENT_TIMESTAMP";
		}

		int numLoops = 1;

		if (this.largeResults) {
			numLoops = 32;
		}

		System.out.println("Inserting " + numLoops + " rows to retrieve...");

		for (int i = 0; i < numLoops; i++) {
			this.stmt.executeUpdate("INSERT INTO perfLoadStore (" + "priKey, "
					+ "fk1, " + "fk2, " + "dtField, " + "charField1, "
					+ "charField2, " + "charField3, " + "charField4, "
					+ "intField1, " + "intField2, " + "intField3, "
					+ "intField4, " + "doubleField1," + "doubleField2,"
					+ "doubleField3," + "doubleField4" + ") VALUES (" + i + "," // priKey
					+ "2," // fk1
					+ "3," // fk2
					+ currentDateValue + "," // dtField
					+ "'0123456789ABCDEF0123456789ABCDEF'," // charField1
					+ "'0123456789ABCDEF0123456789ABCDEF'," // charField2
					+ "'0123456789ABCDEF0123456789ABCDEF'," // charField3
					+ "'0123456789ABCDEF0123456789ABCDEF'," // charField4
					+ "7," // intField1
					+ "8," // intField2
					+ "9," // intField3
					+ "10," // intField4
					+ "1.20," // doubleField1
					+ "2.30," // doubleField2
					+ "3.40," // doubleField3
					+ "4.50" // doubleField4
					+ ")");
		}
	}

	/**
	 * @see junit.framework.TestCase#tearDown()
	 */
	public void tearDown() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE perfLoadStore");
		} catch (SQLException sqlEx) {
			// ignore
		}

		super.tearDown();
	}

	/**
	 * Tests and times 1000 load/store type transactions
	 * 
	 * @throws Exception
	 *             if an error occurs
	 */
	public void test1000Transactions() throws Exception {
		this.takeMeasurements = false;
		warmUp();
		this.takeMeasurements = true;
		doIterations(29);

		reportResults("\n\nResults for instance # 1: ");
	}

	/**
	 * Runs one iteration of the test.
	 * 
	 * @see testsuite.perf.BasePerfTest#doOneIteration()
	 */
	protected void doOneIteration() throws Exception {
		PreparedStatement pStmtStore = this.conn
				.prepareStatement("UPDATE perfLoadStore SET " + "priKey = ?, "
						+ "fk1 = ?, " + "fk2 = ?, " + "dtField = ?, "
						+ "charField1 = ?, " + "charField2 = ?, "
						+ "charField3 = ?, " + "charField4 = ?, "
						+ "intField1 = ?, " + "intField2 = ?, "
						+ "intField3 = ?, " + "intField4 = ?, "
						+ "doubleField1 = ?," + "doubleField2 = ?,"
						+ "doubleField3 = ?," + "doubleField4 = ?"
						+ " WHERE priKey=?");
		PreparedStatement pStmtCheck = this.conn
				.prepareStatement("SELECT COUNT(*) FROM perfLoadStore WHERE priKey=?");
		PreparedStatement pStmtLoad = null;

		if (this.largeResults) {
			pStmtLoad = this.conn.prepareStatement("SELECT " + "priKey, "
					+ "fk1, " + "fk2, " + "dtField, " + "charField1, "
					+ "charField2, " + "charField3, " + "charField4, "
					+ "intField1, " + "intField2, " + "intField3, "
					+ "intField4, " + "doubleField1," + "doubleField2, "
					+ "doubleField3," + "doubleField4" + " FROM perfLoadStore");
		} else {
			pStmtLoad = this.conn.prepareStatement("SELECT " + "priKey, "
					+ "fk1, " + "fk2, " + "dtField, " + "charField1, "
					+ "charField2, " + "charField3, " + "charField4, "
					+ "intField1, " + "intField2, " + "intField3, "
					+ "intField4, " + "doubleField1," + "doubleField2, "
					+ "doubleField3," + "doubleField4"
					+ " FROM perfLoadStore WHERE priKey=?");
		}

		NumberFormat numFormatter = NumberFormat.getInstance();
		numFormatter.setMaximumFractionDigits(4);
		numFormatter.setMinimumFractionDigits(4);

		int transactionCount = 5000;

		if (this.largeResults) {
			transactionCount = 50;
		}

		long begin = System.currentTimeMillis();

		for (int i = 0; i < transactionCount; i++) {
			this.conn.setAutoCommit(false);
			pStmtCheck.setInt(1, 1);
			this.rs = pStmtCheck.executeQuery();

			while (this.rs.next()) {
				this.rs.getInt(1);
			}

			this.rs.close();

			if (!this.largeResults) {
				pStmtLoad.setInt(1, 1);
			}

			this.rs = pStmtLoad.executeQuery();

			if (this.rs.next()) {
				int key = this.rs.getInt(1);

				if (!this.useColumnNames) {
					pStmtStore.setInt(1, key); // priKey
					pStmtStore.setInt(2, this.rs.getInt(2)); // fk1
					pStmtStore.setInt(3, this.rs.getInt(3)); // fk2
					pStmtStore.setTimestamp(4, this.rs.getTimestamp(4)); // dtField
					pStmtStore.setString(5, this.rs.getString(5)); // charField1
					pStmtStore.setString(6, this.rs.getString(7)); // charField2
					pStmtStore.setString(7, this.rs.getString(7)); // charField3
					pStmtStore.setString(8, this.rs.getString(8)); // charField4
					pStmtStore.setInt(9, this.rs.getInt(9)); // intField1
					pStmtStore.setInt(10, this.rs.getInt(10)); // intField2
					pStmtStore.setInt(11, this.rs.getInt(11)); // intField3
					pStmtStore.setInt(12, this.rs.getInt(12)); // intField4
					pStmtStore.setDouble(13, this.rs.getDouble(13)); // doubleField1
					pStmtStore.setDouble(14, this.rs.getDouble(14)); // doubleField2
					pStmtStore.setDouble(15, this.rs.getDouble(15)); // doubleField3
					pStmtStore.setDouble(16, this.rs.getDouble(16)); // doubleField4

					pStmtStore.setInt(17, key);
				} else {
					/*
					 * "UPDATE perfLoadStore SET " + "priKey = ?, " + "fk1 = ?, " +
					 * "fk2 = ?, " + "dtField = ?, " + "charField1 = ?, " +
					 * "charField2 = ?, " + "charField3 = ?, " + "charField4 = ?, " +
					 * "intField1 = ?, " + "intField2 = ?, " + "intField3 = ?, " +
					 * "intField4 = ?, " + "doubleField1 = ?," + "doubleField2 =
					 * ?," + "doubleField3 = ?," + "doubleField4 = ?" + " WHERE
					 * priKey=?");
					 */
					pStmtStore.setInt(1, key); // priKey
					pStmtStore.setInt(2, this.rs.getInt("fk1")); // fk1
					pStmtStore.setInt(3, this.rs.getInt("fk2")); // fk2
					pStmtStore.setTimestamp(4, this.rs.getTimestamp("dtField")); // dtField
					pStmtStore.setString(5, this.rs.getString("charField1")); // charField1
					pStmtStore.setString(6, this.rs.getString("charField2")); // charField2
					pStmtStore.setString(7, this.rs.getString("charField3")); // charField3
					pStmtStore.setString(8, this.rs.getString("charField4")); // charField4
					pStmtStore.setInt(9, this.rs.getInt("intField1")); // intField1
					pStmtStore.setInt(10, this.rs.getInt("intField2")); // intField2
					pStmtStore.setInt(11, this.rs.getInt("intField3")); // intField3
					pStmtStore.setInt(12, this.rs.getInt("intField4")); // intField4
					pStmtStore.setDouble(13, this.rs.getDouble("doubleField1")); // doubleField1
					pStmtStore.setDouble(14, this.rs.getDouble("doubleField2")); // doubleField2
					pStmtStore.setDouble(15, this.rs.getDouble("doubleField3")); // doubleField3
					pStmtStore.setDouble(16, this.rs.getDouble("doubleField4")); // doubleField4

					pStmtStore.setInt(17, key);
				}

				pStmtStore.executeUpdate();
			}

			this.rs.close();

			this.conn.commit();
			this.conn.setAutoCommit(true);
		}

		pStmtStore.close();
		pStmtCheck.close();
		pStmtLoad.close();

		long end = System.currentTimeMillis();

		long timeElapsed = (end - begin);

		double timeElapsedSeconds = (double) timeElapsed / 1000;
		double tps = transactionCount / timeElapsedSeconds;

		if (this.takeMeasurements) {
			addResult(tps);
			System.out.print("1 [ " + numFormatter.format(getMeanValue())
					+ " ] ");
		} else {
			System.out.println("Warm-up: " + tps + " trans/sec");
		}
	}

	/**
	 * Runs the test 10 times to get JIT going, and GC going
	 * 
	 * @throws Exception
	 *             if an error occurs.
	 */
	protected void warmUp() throws Exception {
		try {
			System.out.print("Warm-up period (10 iterations)");

			for (int i = 0; i < 10; i++) {
				doOneIteration();
				System.out.print(".");
			}

			System.out.println();
			System.out.println("Warm-up period ends");
			System.out.println("\nUnits for this test are transactions/sec.");
		} catch (Exception ex) {
			ex.printStackTrace();

			throw ex;
		}
	}
}