# 9/23/2011 Generate blocking row lock tests
import datetime

# generate sql write queries
def mysqlgen_select_for_update(k, kv, c, cv):
    print "select * from t where %s=%s for update;" % (k, kv)
def mysqlgen_select_for_update_range(k, c, where):
    print "select * from t where %s%s for update;" % (k, where)
def mysqlgen_update(k, kv, c, cv):
    print "update t set %s=%s where %s=%s;" % (c, c, k, kv);
def mysqlgen_update_range(k, c, where):
    print "update t set %s=%s where %s%s;" % (c, c, k, where);
def mysqlgen_insert_ignore(k, kv, c, cv):
    print "insert ignore t values(%s, %s);" % (kv, cv)
def mysqlgen_insert_on_dup_update(k, kv, c, cv):
    print "insert t values(%s, %s) on duplicate key update %s=%s;" % (kv, cv, c, c)
def mysqlgen_replace(k, kv, c, cv):
    print "replace t values(%s, %s);" % (kv, cv)

# genrate sql read queries
def mysqlgen_select_star():
    print "select * from t;"
def mysqlgen_select_where(k, where):
    print "select * from t where %s%s;" % (k, where)

# mysql test code generation
def mysqlgen_prepare():
    print "# prepare with some common parameters"
    print "connect(conn1, localhost, root);"
    print "set session transaction isolation level serializable;"
    print "connect(conn2, localhost, root);"
    print "set session transaction isolation level serializable;"
    print "connection conn1;"
    print ""
def mysqlgen_reload_table():
    print "# drop old table, generate new one. 4 rows"
    print "--disable_warnings"
    print "drop table if exists t;"
    print "--enable_warnings"
    print "create table t (a int primary key, b int) engine=tokudb;"
    for i in range(1, 7):
        mysqlgen_insert_ignore("a", i, "b", i*i)
    print ""
def mysqlgen_cleanup():
    print "# clean it all up"
    print "drop table t;"
    print ""
write_point_queries = [
        ("select for update", mysqlgen_select_for_update),
        ("update", mysqlgen_update),
        ("insert", mysqlgen_insert_ignore),
        ("replace", mysqlgen_replace) ]
write_range_queries = [
        ("select for update", mysqlgen_select_for_update_range),
        ("update", mysqlgen_update_range) ]
timeouts = [0, 500]

# Here's where all the magic happens
print "# Tokutek"
print "# Blocking row lock tests;"
print "# Generated by %s on %s;" % (__file__, datetime.date.today())
print ""
mysqlgen_prepare()
mysqlgen_reload_table()
for timeout in timeouts:
    print "# testing with timeout %s" % timeout
    print "connection conn1;"
    print "set session tokudb_lock_timeout=%s;" % timeout
    print "connection conn2;"
    print "set session tokudb_lock_timeout=%s;" % timeout
    print ""
    print "# testing each point query vs each point query"
    for ta, qa in write_point_queries:
        # point vs point contention
        for tb, qb in write_point_queries:
            print "# testing conflict \"%s\" vs. \"%s\"" % (ta, tb)
            print "connection conn1;"
            print "begin;"
            print "# about to do qa.."
            qa("a", "1", "b", "100")
            print "connection conn2;"
            for k in range(1, 5):
                if k == 1:
                    print "--error ER_LOCK_WAIT_TIMEOUT"
                qb("a", k, "b", "100")
            # point write lock vs read query
            print "# make sure we can't read that row, but can read others."
            print "begin;"
            print "--error ER_LOCK_WAIT_TIMEOUT"
            mysqlgen_select_star()
            print "--error ER_LOCK_WAIT_TIMEOUT"
            mysqlgen_select_where("a", "=1")
            mysqlgen_select_where("a", ">=2")
            print "commit;"
            # Always check in the end that a commit
            # allows the other transaction full access
            print "connection conn1;"
            print "commit;"
            print "connection conn2;"
            qb("a", "1", "b", "100")
            print "begin;"
            mysqlgen_select_star()
            print "commit;"
            print "connection conn1;"
            print ""
            # test early commit
            if timeout > 0:
                print "# check that an early commit allows a blocked"
                print "# transaction to complete"
                print "connection conn1;"
                print "begin;"
                qa("a", "1", "b", "150")
                print "connection conn2;"
                # this makes the query asynchronous, so we can jump back
                # to the conn1 connection and commit it.
                print "send ",
                qb("a", "1", "b", "175")
                print "connection conn1;"
                print "commit;"
                print "connection conn2;"
                print "reap;"
       # point vs range contention
        for rt, rq in write_range_queries:
            print "# testing range query \"%s\" vs \"%s\"" % (rt, ta)
            print "connection conn1;"
            print "begin;"
            print ""
            qa("a", "1", "b", "100")
            print "connection conn2;"
            print "--error ER_LOCK_WAIT_TIMEOUT"
            rq("a", "b", "<=2")
            print "--error ER_LOCK_WAIT_TIMEOUT"
            rq("a", "b", ">=0")
            rq("a", "b", ">2")
            # write range lock vs read query
            print "# make sure we can't read that row, but can read others."
            print "begin;"
            print "--error ER_LOCK_WAIT_TIMEOUT"
            mysqlgen_select_star()
            print "--error ER_LOCK_WAIT_TIMEOUT"
            mysqlgen_select_where("a", "=1")
            mysqlgen_select_where("a", ">=2")
            print "commit;"
            # Always check in the end that a commit
            # allows the other transaction full access
            print "connection conn1;"
            print "commit;"
            print "connection conn2;"
            rq("a", "b", "<=2")
            rq("a", "b", ">=0")
            print "begin;"
            mysqlgen_select_star()
            print "commit;"
            print "connection conn1;"
            print ""
            # test early commit
            if timeout > 0:
                print "# check that an early commit allows a blocked"
                print "# transaction to complete"
                print "connection conn1;"
                print "begin;"
                qa("a", "1", "b", "150")
                print "connection conn2;"
                # this makes the query asynchronous, so we can jump back
                # to the conn1 connection and commit it.
                print "send ",
                rq("a", "b", "<=2")
                print "connection conn1;"
                print "commit;"
                print "connection conn2;"
                print "reap;"
    for rt, rq in write_range_queries:
        for rtb, rqb in write_range_queries:
            print "# testing range query \"%s\" vs range query \"%s\"" % (rt, rtb)
            print "connection conn1;"
            print "begin;"
            print ""
            rq("a", "b", ">=2 and a<=4")
            print "connection conn2;"
            print "--error ER_LOCK_WAIT_TIMEOUT"
            rqb("a", "b", ">=0 and a<=3")
            print "--error ER_LOCK_WAIT_TIMEOUT"
            rqb("a", "b", ">=3 and a<=6")
            print "--error ER_LOCK_WAIT_TIMEOUT"
            rqb("a", "b", "<=2")
            rqb("a", "b", ">=5")
            # point write lock vs read query
            print "# make sure we can't read that row, but can read others."
            print "begin;"
            print "--error ER_LOCK_WAIT_TIMEOUT"
            mysqlgen_select_star()
            print "--error ER_LOCK_WAIT_TIMEOUT"
            mysqlgen_select_where("a", "=2")
            print "--error ER_LOCK_WAIT_TIMEOUT"
            mysqlgen_select_where("a", ">=3 and a<=5")
            mysqlgen_select_where("a", ">=5")
            print "commit;"
            # Always check in the end that a commit
            # allows the other transaction full access
            print "connection conn1;"
            print "commit;"
            print "connection conn2;"
            rqb("a", "b", ">=0 and a<=3")
            rqb("a", "b", ">=3 and a<=6")
            rqb("a", "b", "<=2")
            print "begin;"
            mysqlgen_select_star()
            print "commit;"
            print "connection conn1;"
            print ""
            # test early commit
            if timeout > 0:
                print "# check that an early commit allows a blocked"
                print "# transaction to complete"
                print "connection conn1;"
                print "begin;"
                rq("a", "b", ">=2 and a<=4")
                print "connection conn2;"
                # this makes the query asynchronous, so we can jump back
                # to the conn1 connection and commit it.
                print "send ",
                rqb("a", "b", ">=0 and a<=3")
                print "connection conn1;"
                print "commit;"
                print "connection conn2;"
                print "reap;"
mysqlgen_cleanup()