package com.db4o.bench;

import com.db4o.*;
import com.db4o.sql.*;  // library comes with db4o download
import com.db4o.lib.*;  // library comes with db4o download

import java.sql.*;
import java.io.File;
import java.util.Date;

public class BenchRelationalMismatch
{

	private static boolean skipBench = false;
	public static int runs = 50;
	static String db4oDbFile = "brm.yap";


	public static void run(Object con, boolean sql){

		if(skipBench){
			return;
		}

		Contract contract; // for insert

		Contract example = new Contract(); // for Query By Example
			example.contractNo = "BIGCC";
			example.customer = new Customer();
			example.customer.customerNo = "ESS";

		// SQL run
		if (con != null && sql){

			Connection connection = (Connection)con;
			try{
				dropTables(connection);
				createSQLTableModel(connection);
				connection.commit();
			}catch(Exception e){
				System.out.println("Exception setting up tables in constructor of BenchRelationalMismatch");
			}

			Statement statement = Sql.createStatement(connection);

			BenchMark.tagSQL();
			startInsert();
			for (int i = 0; i < runs; i ++){
				contract = new Contract(
					new Customer("ESS", "Enterprise Systems Solutions")
					,"BIGCC","Cooperation Contract");

				sqlInsert(contract, statement);
			}
			BenchMark.stop();

			startSelect();
			for (int i = 0; i < runs; i ++){
				sqlSelect(statement,example);
			}
			BenchMark.stop();

			try{
				connection.commit();
			} catch(Exception e){
					System.out.println("Commit failed in BenchRelationalMismatch.run");
			}
		}

		// db4o run
		if(! sql){
			new File(db4oDbFile).delete();
			ObjectContainer container = Db4o.openFile(db4oDbFile);
			BenchMark.tagDb4o();
			startInsert();
			for (int i = 0; i < runs; i ++){
				contract = new Contract(
					new Customer("ESS", "Enterprise Systems Solutions")
					,"BIGCC","Cooperation Contract");
				db4oInsert(contract, container);
			}
			BenchMark.stop();

			startSelect();
			for (int i = 0; i < runs; i ++){
				db4oSelect(container, example);
			}
			BenchMark.stop();

			container.close();

		}
	}

	static Contract[] db4oSelect(ObjectContainer db4o, Contract example){
		ObjectSet set = db4o.get(example);
		Contract[] contracts = new Contract[set.size()];
		int i = 0;
		while(set.hasNext()){
			contracts[i ++] = (Contract)set.next();
		}
		return contracts;
	}

	static Contract[] sqlSelect(Statement statement, Contract example){
		try{
			String  sql = "SELECT * FROM brm_CONTRACT, brm_DOCUMENT, brm_CUSTOMER, brm_PERSON WHERE ";
					sql += "DOCUMENTID = brm_DOCUMENT.ID AND ";
					sql += "CUSTOMERID = brm_CUSTOMER.ID AND ";
					sql += "PERSONID = brm_PERSON.ID AND ";
					sql += "CONTRACTNO = '" + example.contractNo + "' AND ";
					sql += "CUSTOMERNO = '" + example.customer.customerNo + "'";
			ResultSet rs = statement.executeQuery(sql);
			com.db4o.lib.Collection collection = new com.db4o.lib.Collection();
			while(rs.next()){
				Contract contract = new Contract();
				contract.contractNo = rs.getString("CONTRACTNO");
				contract.creationDate = new java.util.Date(new Long(rs.getString("CREATIONDATE")).longValue());
				contract.title = rs.getString("TITLE");
				contract.subject = rs.getString("SUBJECT");
				contract.customer = new Customer();
				contract.customer.customerNo = rs.getString("CUSTOMERNO");
				contract.customer.name = rs.getString("NAME");
				collection.add(contract);
			}
			Contract[] contracts = new Contract[collection.size()];
			contracts = (Contract[])collection.toArray(contracts);
			return contracts;
		} catch(Exception e){
			System.out.println("Exception in BenchRelationalMismatch:sqlSelect");
			return null;
		}
	}

	static void db4oInsert(Contract contract, ObjectContainer db4o){
		db4o.set(contract);
	}

	static void sqlInsert(Contract contract, Statement statement){
		try{
			String sql = "";
			int personID = generateID(statement);
			sql = "INSERT INTO brm_PERSON (ID, NAME) VALUES ("
				+ Integer.toString(personID)
				+  ", '" + contract.customer.name + "'"
				+ ")";
			statement.execute(sql);
			int customerID = generateID(statement);
			sql = "INSERT INTO brm_CUSTOMER (ID, PERSONID, CUSTOMERNO) VALUES ("
				+ Integer.toString(customerID)
				+  ", " + Integer.toString(personID)
				+  ", '" + contract.customer.customerNo + "'"
				+ ")";
			statement.execute(sql);
			int documentID = generateID(statement);
			sql = "INSERT INTO brm_DOCUMENT (ID, TITLE, CREATIONDATE) VALUES ("
				+ Integer.toString(documentID)
 				+  ", '" + contract.title + "'"
				+  ", '" + Long.toString(contract.creationDate.getTime()) + "'"
				+ ")";
			statement.execute(sql);
			int contractID = generateID(statement);
			sql = "INSERT INTO brm_CONTRACT (ID, DOCUMENTID, CONTRACTNO, SUBJECT, CUSTOMERID) VALUES ("
				+ Integer.toString(contractID)
 				+  ", " + Integer.toString(documentID)
 				+  ", '" + contract.contractNo + "'"
  				+  ", '" + contract.subject + "'"
				+  ", " + Integer.toString(customerID)
				+ ")";
			statement.execute(sql);
		} catch(Exception e){
			System.out.println("Exception in BenchRelationalMismatch:sqlInsert");
		}
	}

	static int generateID(Statement statement) throws SQLException{
		String sql = "SELECT IDGEN FROM brm_IDGENERATOR";
		ResultSet rs = statement.executeQuery(sql);
		rs.next();
		int id = rs.getInt(1) + 1;
		sql = "UPDATE brm_IDGENERATOR SET IDGEN = " + Integer.toString(id);
		statement.execute(sql);
		return id;
	}

	static void dropTables(Connection connection){
		Statement statement = Sql.createStatement(connection);
		BenchSql.execute(statement,"drop table brm_IDGENERATOR");
		BenchSql.execute(statement,"drop table brm_CONTRACT");
		BenchSql.execute(statement,"drop table brm_CUSTOMER");
		BenchSql.execute(statement,"drop table brm_DOCUMENT");
		BenchSql.execute(statement,"drop table brm_PERSON");
		try{
			connection.commit();
		}
		catch (Exception e){
			System.out.println("Commit failed in BenchRelationalMismatch:clearAll");
		}
	}

	static void createSQLTableModel(Connection connection){
		String sql = "";
		Statement statement = Sql.createStatement(connection);
		try{
			sql = "create table brm_CONTRACT (";
				sql += "ID Integer, ";
				sql += "DOCUMENTID INTEGER, ";
				sql += "CONTRACTNO VARCHAR(10), ";
				sql += "SUBJECT VARCHAR(100), ";
				sql += "CUSTOMERID INTEGER)";
			statement.execute(sql);
			sql = "create table brm_CUSTOMER (";
				sql += "ID INTEGER, ";
				sql += "PERSONID INTEGER, ";
				sql += "CUSTOMERNO VARCHAR(10))";
			statement.execute(sql);
			sql = "create table brm_DOCUMENT (";
				sql += "ID INTEGER, ";
				sql += "CREATIONDATE VARCHAR(19), ";
				sql += "TITLE VARCHAR(100))";
			statement.execute(sql);
			sql = "create table brm_PERSON (";
				sql += "ID INTEGER, ";
				sql += "NAME VARCHAR(100))";
			statement.execute(sql);
			sql = "create table brm_IDGENERATOR (IDGEN Integer)";
			statement.execute(sql);
			sql = "insert into brm_IDGENERATOR (IDGEN) values (0)";
			statement.execute(sql);
		}
		catch (Exception e){
			System.out.println("Failed: " + sql);
		}
		try{
			connection.commit();
		}
		catch (Exception e){
			System.out.println("Commit failed in BenchRelationalMismatch:createSQLTableModel");
		}
	}

	static void startInsert(){
		BenchMark.start(Integer.toString(runs) + " complex inserts");
	}

	static void startSelect(){
		BenchMark.start(Integer.toString(runs) + " complex selects");
	}

}