# TPC-B Benchmark - Schema Creation
# v.1.0.1 
#
# Run it once! Configure grinder.properties as follows:
#     grinder.processes=1 
#     grinder.threads=1
#     grinder.runs=1
#
# Copyright (C) 2005 meo@bogliolo.name
# Distributed under the terms of The Grinder license.

# Parameters
DB_type = "oracle" 	# DB_type= [ oracle | mysql | postgre | odbc | cache | db2 ]
DB_connect = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"
DB_user = "scott"
DB_password = "tiger"
DB_scale = 5

# Configuration samples:
#       "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "SCOTT", "TIGER"
#	"jdbc:mysql://127.0.0.1/bench?user=bench&password=bench", "bench", "bench"
#       "jdbc:postgresql://127.0.0.1:5432/bench", "bench", "bench"
#       "jdbc:db2:bench", "bench", "bench"
#       "jdbc:odbc:bench", "bench", "bench"
#	"jdbc:Cache://127.0.0.1:1972/SAMPLES", "_SYSTEM", "SYS"

from java.sql import DriverManager
from java.util import Random
from net.grinder.script.Grinder import grinder
from net.grinder.script import Test

if DB_type == "oracle":
    from oracle.jdbc import OracleDriver
    DriverManager.registerDriver(OracleDriver())
elif DB_type == "mysql":
    from com.mysql.jdbc import Driver
    DriverManager.registerDriver(Driver())
elif DB_type == "cache":
    from com.intersys.jdbc import CacheDriver
    DriverManager.registerDriver(CacheDriver())
elif DB_type == "postgres":
    from org.postgresql import Driver
    DriverManager.registerDriver(Driver())
elif DB_type == "db2":
    from com.ibm.db2.jdbc.app import DB2Driver
    # from com.ibm.db2.jdbc.net import DB2Driver
    DriverManager.registerDriver(DB2Driver())
elif DB_type == "odbc":
    from sun.jdbc.odbc import JdbcOdbcDriver
    DriverManager.registerDriver(JdbcOdbcDriver())

def getConnection():
    return DriverManager.getConnection(DB_connect, DB_user, DB_password)

connection = getConnection()
statement = connection.createStatement()

test1 = Test(1, "CREATE branches")
test2 = Test(2, "CREATE tellers")
test3 = Test(3, "CREATE accounts")
test4 = Test(4, "CREATE history")
test5 = Test(5, "COMMIT")
test6 = Test(6, "POPULATE branches")
test7 = Test(7, "POPULATE tellers")
test8 = Test(8, "POPULATE accounts")
test9 = Test(9, "COMMIT")

class TestRunner:
    def __call__(self):
        try:
	#  Dropping tables if they exist 
            try: statement.execute("DROP TABLE branches");
	    except: pass
            try: statement.execute("DROP TABLE tellers");
	    except: pass
            try: statement.execute("DROP TABLE accounts");
	    except: pass
            try: statement.execute("DROP TABLE history");
	    except: pass

	#  Creating tables
            if DB_type == "mysql":
                statement.execute("set session storage_engine=InnoDB");	# to have true transaction management

            testQuery = test1.wrap(statement)
            testQuery.execute("CREATE TABLE branches (Bid INTEGER NOT NULL, Bbalance FLOAT NOT NULL, filler   char(88) NULL) ");
            testQuery = test2.wrap(statement)
            testQuery.execute("CREATE TABLE tellers (Tid INTEGER NOT NULL, Bid INTEGER NOT NULL, Tbalance FLOAT NOT NULL, filler   CHAR(84) NULL) ");
            testQuery = test3.wrap(statement)
            testQuery.execute("CREATE TABLE accounts (Aid INTEGER NOT NULL, Bid INTEGER NOT NULL, Abalance FLOAT NOT NULL, filler   CHAR(84) NULL) ");
            testQuery = test4.wrap(statement)
            if DB_type == "oracle":
                testQuery.execute("CREATE TABLE history (Tid INTEGER NOT NULL, Bid INTEGER NOT NULL, Aid INTEGER NOT NULL, delta   FLOAT NOT NULL, Xtime date NOT NULL, filler  CHAR(22) NULL) ");
            elif DB_type == "mysql":
                testQuery.execute("CREATE TABLE history (Tid INTEGER NOT NULL, Bid INTEGER NOT NULL, Aid INTEGER NOT NULL, delta   FLOAT NOT NULL, Xtime timestamp NOT NULL, filler  CHAR(22) NULL) ");
            elif DB_type == "cache":
                testQuery.execute("CREATE TABLE history (Tid INTEGER NOT NULL, Bid INTEGER NOT NULL, Aid INTEGER NOT NULL, delta   FLOAT NOT NULL, Xtime TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL, filler  CHAR(22) NULL) ");
            else: 
                testQuery.execute("CREATE TABLE history (Tid INTEGER NOT NULL, Bid INTEGER NOT NULL, Aid INTEGER NOT NULL, delta   FLOAT NOT NULL, Xtime date, filler  CHAR(22) NULL) ");

            testQuery = test5.wrap(statement)
            testQuery.execute("COMMIT")

	#  Populating tables 
            for b in range(DB_scale):
                str = "INSERT INTO branches (Bid, Bbalance, filler) VALUES ( %i , 0, 'BThe quick brown fox runs over the lazy dog0123456789')" % b
                testQuery = test6.wrap(statement)
                testQuery.execute(str)

                for i in range(10):
                    str = "INSERT INTO tellers (Tid, Bid, Tbalance, filler) VALUES (  %i * 10 + %i , %i , 0, 'TThe quick brown fox runs over the lazy dog0123456789')" % (b, i, b)
            	    testQuery = test7.wrap(statement)
            	    testQuery.execute(str)

                for i in range(100000):
                    str = "INSERT INTO accounts (Aid, Bid, Abalance, filler) VALUES ( %i * 100000 + %i, %i , 0, 'AThe quick brown fox runs over the lazy dog0123456789')" % (b, i, b)
            	    testQuery = test8.wrap(statement)
            	    testQuery.execute(str)
	    
                testQuery = test9.wrap(statement)
                testQuery.execute("COMMIT")

	#  Creating Indexes
            try: statement.execute("create unique index PI_BRANCHES_BID on BRANCHES(BID)");
	    except: pass
            try: statement.execute("create unique index PI_TELLERS_TID on TELLERS(TID)");
	    except: pass
            try: statement.execute("create unique index PI_ACCOUNTS_AID on ACCOUNTS(AID)");
	    except: pass

	#  Creating stored procedure
            if DB_type == "oracle":
                statement.execute(
		    """CREATE OR REPLACE PROCEDURE tcp_b(Aidv INTEGER, Bidv INTEGER, Tidv INTEGER, deltav FLOAT, Abalancev OUT FLOAT) 
			as
			BEGIN 
			UPDATE accounts
 			  SET Abalance = Abalance + deltav
 			  WHERE Aid = Aidv;
			SELECT Abalance 
 			  INTO Abalancev
 			  FROM accounts
 			  WHERE Aid = Aidv;
			UPDATE tellers
 			  SET Tbalance = Tbalance + deltav 
 			  WHERE Tid = Tidv;
			UPDATE branches
 			  SET Bbalance = Bbalance + deltav 
 			  WHERE Bid = Bidv ;
			INSERT INTO history(Tid,Bid,Aid,delta,Xtime) VALUES (Tidv, Bidv, Aidv, deltav, SYSDATE);
			COMMIT ;
			END;""")
            elif DB_type == "mysql":
                statement.execute( "DROP PROCEDURE IF EXISTS tpc_b" )
                statement.execute(
                      """CREATE PROCEDURE tpc_b(Aidv INTEGER, Bidv INTEGER, Tidv INTEGER, deltav FLOAT, Abalancev OUT FLOAT) 
                      LANGUAGE SQL
                      NOT DETERMINISTIC 
                      SQL SECURITY DEFINER
                      COMMENT 'TPC-B Standard benchmarking procedure' 
                      BEGIN 
                      UPDATE accounts
                       SET Abalance = Abalance + deltav
                       WHERE Aid = Aidv;
                      SELECT Abalance 
                       INTO Abalancev
                       FROM accounts
                       WHERE Aid = Aidv;
                      UPDATE tellers
                       SET Tbalance = Tbalance + deltav 
                       WHERE Tid = Tidv;
                      UPDATE branches
                       SET Bbalance = Bbalance + deltav 
                       WHERE Bid = Bidv ;
                      INSERT INTO history (Tid,Bid,Aid,delta,Xtime) VALUES (Tidv, Bidv, Aidv, deltav, SYSDATE);
                      COMMIT ;
                      END""")

	#  Analyzing

        finally:
	    grinder.sleep(1000)