# TPC-B Benchmark - Grinder script
# v.1.0.2
#
# This script perform the TPC-B standard benchmark againts a JDBC
# compliant DBMS. It can be used to stress the DB with multiple users and run
# to simulate batch (think time=0) or interactive activities
#
# Configuration (grinder.properties):
#     Use multiple processes (grinder.processes=[1,2,5,...] grinder.threads=1)
#     Use enought run to ramp-up the bench (eg. grinder.runs=10000)
#     TPC-B Standard think time is 0 (grinder.sleepTimeFactor=0) but
#         a different value can be used to simulate interactive transactions
#
# Prepared Statements or Stored Procedures give better results...
#
# Copyright (C) 2005-2006 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()

if DB_type == "cache":
    statement.execute("SET TRANSACTION %COMMITMODE EXPLICIT")
    statement.execute("SET TRANSACTION READ WRITE")
    # statement.execute("SET TRANSACTION READ WRITE, ISOLATION LEVEL READ COMMITED")
elif DB_type == "mysql":
    statement.execute("SET session AUTOCOMMIT=0")
    # statement.execute("SET session TRANSACTION ISOLATION LEVEL READ COMMITTED")

test1 = Test(1, "UPDATE accounts")
test2 = Test(2, "SELECT accounts")
test3 = Test(3, "UPDATE tellers")
test4 = Test(4, "UPDATE branches")
test5 = Test(5, "INSERT history")
test6 = Test(6, "COMMIT")

random = Random()

class TestRunner:
    def __call__(self):

        try:
            # Think time (modify it with grinder.sleepTimeFactor)
	    grinder.sleep(10000)
            
	    if DB_scale >1:
                Bid = abs(random.nextInt()) % (DB_scale - 1)
            else:
                Bid = 0
	    Tid = abs(random.nextInt()) % 10 + 10 * Bid
	    Aid = abs(random.nextInt()) % 100000 + 100000 * Bid
	    delta = abs(random.nextInt()) % 1000

            testQuery = test1.wrap(statement)
            testQuery.execute("UPDATE ACCOUNTS SET Abalance = Abalance + %d WHERE Aid = %d"
			% (delta, Aid) )

            testQuery = test2.wrap(statement)
            testQuery.execute("SELECT Abalance from ACCOUNTS WHERE Aid = %d"
			%  Aid )

            testQuery = test3.wrap(statement)
            testQuery.execute("UPDATE TELLERS SET Tbalance = Tbalance + %d WHERE Tid = %d"
			% (delta, Tid) )

            testQuery = test4.wrap(statement)
            testQuery.execute("UPDATE BRANCHES SET Bbalance = Bbalance + %d WHERE Bid = %d"
			% (delta, Bid) )

            testQuery = test5.wrap(statement)
            if DB_type == "oracle":
                testQuery.execute("INSERT INTO HISTORY (Tid,Bid,Aid,delta,Xtime) VALUES (%d, %d, %d, %d, SYSDATE)"
			% (Tid, Bid, Aid, delta) )
            else:
                testQuery.execute("INSERT INTO HISTORY (tid,bid,aid,delta) VALUES (%d, %d, %d, %d)"
			% (Tid, Bid, Aid, delta) )

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

        finally:
	    grinder.sleep(10000)