/*
 * Created on Apr 21, 2004
 */
package demonstratebug;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;

public class DemonstrateBug {
	private final static String	CONNECTION_STRING			= "jdbc:mysql://localhost/?socketFactory=com.mysql.jdbc.NamedPipeSocketFactory&cacheResultSetMetadata=true&cachePrepStmts=true&prepStmtCacheSize=50&prepStmtCacheSqlLimit=512";
	private final static String	DROP_DATABASE				= "DROP DATABASE IF EXISTS bugTstDER1;";
	private final static String	CREATE_DATABASE				= "CREATE DATABASE bugTstDER1;";
	private final static String	CREATE_INTERACTION_TABLE	= "CREATE TABLE bugTstDER1.interaction ( "
																	+ "  ID bigint(20) unsigned NOT NULL auto_increment,"
																	+ "  Site char(7) NOT NULL," + "  EventTime datetime NOT NULL,"
																	+ "  UserID varchar(25) NOT NULL,"
																	+ "  NationalReceiptTime datetime default NULL,"
																	+ "  LocalReceiptTime datetime default NULL,"
																	+ "  SuperceededAt datetime default NULL,"
																	+ "  NationalReceivedSuperceedAt datetime default NULL,"
																	+ "  LocalReceivedSuperceedAt datetime default NULL,"
																	+ "  PRIMARY KEY (ID,Site)"
																	+ ") ENGINE=InnoDB DEFAULT CHARSET=latin1;";
	public final static String	CREATE_PROVIDER_TABLE		= "CREATE TABLE bugTstDER1.provider ( "
																	+ "  ID varchar(20) NOT NULL,"
																	+ "  InteractionID bigint(20) unsigned NOT NULL,"
																	+ "  InteractionSite char(7) NOT NULL,"
																	+ "  LastName varchar(25) NOT NULL,"
																	+ "  FirstName varchar(25) NOT NULL,"
																	+ "  DOB datetime NOT NULL,"
																	+ "  Sex char(1) NOT NULL,"
																	+ "  Superceeded bit NOT NULL default '0',"
																	+ "  PRIMARY KEY (ID,InteractionID,InteractionSite),"
																	+ "  INDEX LookupProviderByInteraction (InteractionID,InteractionSite),"
																	+ "  INDEX LookupProviderByName (LastName,FirstName),"
																	+ "  FOREIGN KEY (InteractionID, InteractionSite) REFERENCES interaction (ID, Site)"
																	+ ") ENGINE=InnoDB DEFAULT CHARSET=latin1;";
	private static Random		theRNG						= new Random();

	private static String getRandomName() {
		char[] theName = new char[theRNG.nextInt(21) + 5];
		for (int i = 0; i < theName.length; i++) {
			theName[i] = Character.forDigit(theRNG.nextInt(26), 26);
		}
		return new String(theName);
	}

	public static long insertInteraction(Connection conn, Date theDate) throws SQLException {
		PreparedStatement theInteractionStmt = conn.prepareStatement(
				"INSERT INTO bugTstDER1.interaction (ID, UserID, Site, EventTime) VALUES( 0,?,?,?)", Statement.RETURN_GENERATED_KEYS);
		theInteractionStmt.clearParameters();
		theInteractionStmt.setString(1, "someUser");
		theInteractionStmt.setString(2, "someSit");
		theInteractionStmt.setDate(3, theDate);
		theInteractionStmt.executeUpdate();
		ResultSet theResults = theInteractionStmt.getGeneratedKeys();
		theResults.next();
		long theID = theResults.getLong(1);
		theInteractionStmt.close();
		return theID;
	}

	public static void insertProvider(Connection conn, Date theDate, long theInteractionID, long currentProviderID) throws SQLException {
		PreparedStatement theProviderInsertPS = conn.prepareStatement("INSERT INTO bugTstDER1.provider VALUES(?,?,?,?,?,?,?,0)");
		String theLastName = getRandomName();
		String theFirstName = getRandomName();
		theProviderInsertPS.clearParameters();
		theProviderInsertPS.setLong(1, currentProviderID);
		theProviderInsertPS.setLong(2, theInteractionID);
		theProviderInsertPS.setString(3, "someSit");
		theProviderInsertPS.setString(4, theLastName);
		theProviderInsertPS.setString(5, theFirstName);
		theProviderInsertPS.setDate(6, theDate);
		theProviderInsertPS.setString(7, "f");
		theProviderInsertPS.executeUpdate();
		theProviderInsertPS.close();
	}

	public static void main(String[] args) {
		try {
			System.out.println("Opening connection and creating schema");
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection conn = DriverManager.getConnection(CONNECTION_STRING, "root", "");
			Statement theS = conn.createStatement();
			theS.executeUpdate(DROP_DATABASE);
			theS.executeUpdate(CREATE_DATABASE);
			theS.executeUpdate(CREATE_INTERACTION_TABLE);
			theS.executeUpdate(CREATE_PROVIDER_TABLE);
			System.out.println("Closing connection (post schema creation)");
			conn.setAutoCommit(false);
			System.out.println("Creating prepared statements over and over 'pounding' the server commiting periodically.");
			long currentProviderID = 2000;
			for (int i = 0; i < 1000; i++) {
				for (int j = 0; j < 3000; j++) {
					currentProviderID++;
					Date now = new Date(System.currentTimeMillis());
					long interactionID = insertInteraction(conn, now);
					insertProvider(conn, now, interactionID, currentProviderID);
				}
				conn.commit();
			}
			System.out.println("Check memory usage - should be very large");
			Thread.sleep(60000);
			System.out.println("Check memory usage - should have shrunk way down");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}