# EMP7 Benchmark - Grinder script
# v.1.0.3
#
# This script perform the easy EMP7 Bench which extracts 100M record using
# a self join query.
#
# Configuration (grinder.properties):
#     Single statement (grinder.processes=1 grinder.threads=1 grinder.runs=1)
#
# Your feedback is welcome! Please send Your results to meo@bogliolo.name
# specifing RDBMS version, OS version, processor type and Mhz
#
# 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"

# 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 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)

def ensureClosed(object):
    try: object.close()
    except: pass

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

try: statement.execute("drop table emp7")
except: pass

statement.execute("create table emp7(EMPNO integer not null,ENAME VARCHAR(10),JOB VARCHAR(9),MGR integer,HIREDATE DATE,SAL float,COMM float,DEPTNO integer)")
statement.execute("create unique index pkemp7 on emp7(EMPNO)")

for i in range(14):
    statement.execute("insert into emp7(empno, ename, deptno) values(%i,'SMITH',10)" %i)
statement.execute("commit")

# some Oracle versions use a poor execution plan if the EMP table has not been analyzed
if DB_type == "oracle":
    statement.execute("analyze table emp7 compute statistics")

ensureClosed(statement)
ensureClosed(connection)

test1 = Test(1, "Database EMP7 query")
    
class TestRunner:
    def __call__(self):
        connection = None
        statement = None

        try:
	    connection = getConnection()
	    statement = connection.createStatement()
            
            testInsert = test1.wrap(statement)
            testInsert.execute("select count(*) \
  from emp7 emp1, emp7 emp2, emp7 emp3, emp7 emp4, emp7 emp5, emp7 emp6, emp7 emp_7\
  where emp_7.deptno=10")

	finally:
            ensureClosed(statement)
	    ensureClosed(connection)