def test_lost_connection_during_session(self):
        k = 'test_rollback'
        mydb1 = BaseDB(myconfig)
        mydb2 = BaseDB(myconfig)
        autoid, rows = mydb1.insert("INSERT INTO test1(k,v) VALUES('%s', 1);" % escape(k))
        assert_equal(autoid, 1, "autoid")
        assert_equal(rows, 1, "rows")

        tid1, = mydb1.query_one("SELECT CONNECTION_ID();")
        try:
            with mydb1.session() as conn:
                rows = conn.execute("UPDATE test1 SET v=2 WHERE k='%s'" % escape(k))
                assert_equal(rows, 1, "rows")

                # create lost connection exception
                mydb2.execute("KILL %d" % int(tid1))

                # this statement will raise exception
                conn.execute("UPDATE test1 SET v=3 WHERE k='%s'" % escape(k))

        except OperationalError as e:
            if not is_lost_connection_exception(e):
                raise

        else:
            assert False, "Must get an exception."

        # nothing is committed
        v, = mydb1.query_one("SELECT v FROM test1 WHERE k='%s'" % escape(k))
        assert_equal(v, '1')
        v, = mydb2.query_one("SELECT v FROM test1 WHERE k='%s'" % escape(k))
        assert_equal(v, '1')
    def test_basic(self):
        mydb = BaseDB(myconfig)
        mydb2 = BaseDB(myconfig)

        aid, rows = mydb.insert("INSERT INTO test1(k,v) VALUES('%s','%s')" % ("aaa", "AAA"))
        assert_equal(aid, 1, 'autoid')
        assert rows == 1, "insert 1 row"

        rows = mydb.execute("UPDATE test1 SET v='%s' WHERE k='%s'" % ('BBB', 'aaa'))
        assert rows == 1, "update 1 row"

        # Read Committed
        values = mydb2.query("SELECT autoid, k, v FROM test1 LIMIT 10;")
        assert len(values) == 1, "only 1 record in table"
        aid, k, v = values[0]
        assert aid == '1' and k == 'aaa' and v == 'BBB', ("result must be commit: %s" % values)

        # Cannot Read uncommitted
        with mydb.session() as conn:
            values = conn.query("SELECT autoid, k, v FROM test1 WHERE k='aaa'")
            assert len(values) == 1, "only 1 record in table"
            aid, k, v = values[0]
            assert aid == '1' and k == 'aaa' and v == 'BBB', "result must be commit"

            rows = conn.execute("UPDATE test1 SET v='%s' WHERE k='%s'" % ('CCC', 'aaa'))
            assert rows == 1

            # read uncommitted
            values = mydb2.query("SELECT autoid, k, v FROM test1 LIMIT 10;")
            assert len(values) == 1, "only 1 record in table"
            aid, k, v = values[0]
            assert aid == '1' and k == 'aaa' and v == 'BBB', ("result is not commit: %s" % values)

        values = mydb2.query("SELECT autoid, k, v FROM test1 LIMIT 10;")
        assert len(values) == 1, "only 1 record in table"
        aid, k, v = values[0]
        assert aid == '1' and k == 'aaa' and v == 'CCC', "result is committed"
    def test_rollback(self):
        k = 'test_rollback'
        mydb1 = BaseDB(myconfig)
        mydb2 = BaseDB(myconfig)
        autoid, rows = mydb1.insert("INSERT INTO test1(k,v) VALUES('%s', 1);" % escape(k))
        assert_equal(autoid, 1, "autoid")
        assert_equal(rows, 1, "rows")

        try:
            with mydb1.session() as conn:
                rows = conn.execute("UPDATE test1 SET v=2 WHERE k='%s'" % escape(k))
                assert_equal(rows, 1, "rows")

                raise Exception("I want a rollback")
        except Exception as e:
            if str(e) != "I want a rollback":
                raise
        else:
            assert False, "Must get an exception."

        v, = mydb1.query_one("SELECT v FROM test1 WHERE k='%s'" % escape(k))
        assert_equal(v, '1')
        v, = mydb2.query_one("SELECT v FROM test1 WHERE k='%s'" % escape(k))
        assert_equal(v, '1')
class Test(unittest.TestCase):
    _DROP_TABLE = '''
    DROP TABLE IF EXISTS `test1`;
    '''
    _CREATE_TABLE = '''
    CREATE TABLE IF NOT EXISTS `test1` (
        autoid INT NOT NULL AUTO_INCREMENT,
        k VARCHAR(100) NOT NULL,
        v VARCHAR(100) NOT NULL,
        ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
        PRIMARY KEY (`autoid`),
        UNIQUE INDEX (`k`)
    )ENGINE=Innodb DEFAULT CHARSET='utf8';
    '''

    def setUp(self):
        self.mydb = BaseDB(myconfig)

        self.mydb.execute(self._DROP_TABLE)
        self.mydb.execute(self._CREATE_TABLE)

    def tearDown(self):
        self.mydb.execute(self._DROP_TABLE)
        self.mydb.close()

    def test_basic(self):
        mydb = BaseDB(myconfig)
        mydb2 = BaseDB(myconfig)

        aid, rows = mydb.insert("INSERT INTO test1(k,v) VALUES('%s','%s')" % ("aaa", "AAA"))
        assert_equal(aid, 1, 'autoid')
        assert rows == 1, "insert 1 row"

        rows = mydb.execute("UPDATE test1 SET v='%s' WHERE k='%s'" % ('BBB', 'aaa'))
        assert rows == 1, "update 1 row"

        # Read Committed
        values = mydb2.query("SELECT autoid, k, v FROM test1 LIMIT 10;")
        assert len(values) == 1, "only 1 record in table"
        aid, k, v = values[0]
        assert aid == '1' and k == 'aaa' and v == 'BBB', ("result must be commit: %s" % values)

        # Cannot Read uncommitted
        with mydb.session() as conn:
            values = conn.query("SELECT autoid, k, v FROM test1 WHERE k='aaa'")
            assert len(values) == 1, "only 1 record in table"
            aid, k, v = values[0]
            assert aid == '1' and k == 'aaa' and v == 'BBB', "result must be commit"

            rows = conn.execute("UPDATE test1 SET v='%s' WHERE k='%s'" % ('CCC', 'aaa'))
            assert rows == 1

            # read uncommitted
            values = mydb2.query("SELECT autoid, k, v FROM test1 LIMIT 10;")
            assert len(values) == 1, "only 1 record in table"
            aid, k, v = values[0]
            assert aid == '1' and k == 'aaa' and v == 'BBB', ("result is not commit: %s" % values)

        values = mydb2.query("SELECT autoid, k, v FROM test1 LIMIT 10;")
        assert len(values) == 1, "only 1 record in table"
        aid, k, v = values[0]
        assert aid == '1' and k == 'aaa' and v == 'CCC', "result is committed"

    def test_rollback(self):
        k = 'test_rollback'
        mydb1 = BaseDB(myconfig)
        mydb2 = BaseDB(myconfig)
        autoid, rows = mydb1.insert("INSERT INTO test1(k,v) VALUES('%s', 1);" % escape(k))
        assert_equal(autoid, 1, "autoid")
        assert_equal(rows, 1, "rows")

        try:
            with mydb1.session() as conn:
                rows = conn.execute("UPDATE test1 SET v=2 WHERE k='%s'" % escape(k))
                assert_equal(rows, 1, "rows")

                raise Exception("I want a rollback")
        except Exception as e:
            if str(e) != "I want a rollback":
                raise
        else:
            assert False, "Must get an exception."

        v, = mydb1.query_one("SELECT v FROM test1 WHERE k='%s'" % escape(k))
        assert_equal(v, '1')
        v, = mydb2.query_one("SELECT v FROM test1 WHERE k='%s'" % escape(k))
        assert_equal(v, '1')

    def test_thread_safety(self):
        mydb = BaseDB(myconfig)
        LOOP = 100
        THREAD = 10

        def work():
            for i in xrange(LOOP):
                mydb.query("SHOW TABLES")

        threads = []
        for tn in xrange(THREAD):
            t = threading.Thread(target=work)
            t.start()
            threads.append(t)

        for t in threads:
            t.join()

    def test_thread_transaction(self):
        myk = 'test_multi_thread_inserttion'
        autoid, rows = self.mydb.insert("INSERT INTO test1(k,v) VALUES('%s', 1);" % escape(myk))
        assert_equal(autoid, 1, "autoid")
        assert_equal(rows, 1, "rows")

        LOOP = 50
        THREAD = 10

        def work():
            mydb = BaseDB(myconfig)
            for i in xrange(LOOP):
                with mydb.session() as conn:
                    # MUST use `FOR UPDATE` to block reading
                    _, v = conn.query_one("SELECT autoid,v FROM test1 WHERE k='%s' FOR UPDATE;" % escape(myk))

                    rows2 = conn.execute("UPDATE test1 SET v=%d WHERE k='%s'" % (int(v)+1, escape(myk)))
                    assert rows2 == 1, (rows2, 1)

        threads = []
        for tn in xrange(THREAD):
            t = threading.Thread(target=work)
            t.start()
            threads.append(t)

        for t in threads:
            t.join()

        aid, v2 = self.mydb.query_one("SELECT autoid, v FROM test1 WHERE k='%s'" % escape(myk))
        assert_equal(int(v2), THREAD*LOOP+1, "finally v")
        assert_equal(int(aid), 1, "autoid")

    def test_lost_connection(self):
        mydb = BaseDB(myconfig)
        mydb2 = BaseDB(myconfig)
        tid1, = mydb.query_one("SELECT CONNECTION_ID();")

        mydb2.execute("KILL %d" % int(tid1))

        # connection is lost!!
        # but it can recover quietly.
        tid2, = mydb.query_one("SELECT CONNECTION_ID();")
        assert tid1 != tid2, ("Connection ID must be difference: %s", (tid1, tid2))

    def test_lost_connection_during_session(self):
        k = 'test_rollback'
        mydb1 = BaseDB(myconfig)
        mydb2 = BaseDB(myconfig)
        autoid, rows = mydb1.insert("INSERT INTO test1(k,v) VALUES('%s', 1);" % escape(k))
        assert_equal(autoid, 1, "autoid")
        assert_equal(rows, 1, "rows")

        tid1, = mydb1.query_one("SELECT CONNECTION_ID();")
        try:
            with mydb1.session() as conn:
                rows = conn.execute("UPDATE test1 SET v=2 WHERE k='%s'" % escape(k))
                assert_equal(rows, 1, "rows")

                # create lost connection exception
                mydb2.execute("KILL %d" % int(tid1))

                # this statement will raise exception
                conn.execute("UPDATE test1 SET v=3 WHERE k='%s'" % escape(k))

        except OperationalError as e:
            if not is_lost_connection_exception(e):
                raise

        else:
            assert False, "Must get an exception."

        # nothing is committed
        v, = mydb1.query_one("SELECT v FROM test1 WHERE k='%s'" % escape(k))
        assert_equal(v, '1')
        v, = mydb2.query_one("SELECT v FROM test1 WHERE k='%s'" % escape(k))
        assert_equal(v, '1')

    def test_idle_connection(self):
        set_db_connection_idle_timeout(1)

        def _connection_id_in_processlist(connid):
            db = BaseDB(myconfig)
            processlist = db.query("SHOW PROCESSLIST;")
            conns = set(int(x[0]) for x in processlist )
            return int(connid) in conns

        mydb = BaseDB(myconfig)
        tid1, = mydb.query_one("SELECT CONNECTION_ID();")
        assert _connection_id_in_processlist(tid1)
        time.sleep(1)
        tid2, = mydb.query_one("SELECT CONNECTION_ID();")
        assert not _connection_id_in_processlist(tid1)
        assert _connection_id_in_processlist(tid2)

        set_db_connection_idle_timeout(600)

    def test_thread_safety2(self):
        mydb = BaseDB(myconfig)
        THREAD = 10
        result = []
        
        def work():
            # print "#Thread(%s), DB(%s), Conn(%s)#" % (threading.currentThread().ident, id(mydb), id(mydb.conn))
            result.append((id(mydb), id(mydb.conn)))

        threads = []
        for tn in xrange(THREAD):
            t = threading.Thread(target=work)
            t.start()
            threads.append(t)

        for t in threads:
            t.join()

        assert_equal(len(result), THREAD, 'len of result')
        assert_equal(len(set(y for x, y in result)), THREAD, 'different connection')
        assert_equal(len(set(x for x, y in result)), 1, 'different BaseDB object')