class gDBPoolTests(unittest.TestCase): def setUp(self): print "\n================================================================================\nRunning: %s\n================================================================================" % ( self._testMethodName) self.ipool = DBInteractionPool(dsn, pool_size=16, do_log=True) def tearDown(self): self.ipool.__del__() def test_connect_nonexisting_host_port(self): with self.assertRaises(DBPoolConnectionException): dsn = "host=127.0.0.1 port=6432 user=postgres dbname=gdbpool_test" fail_ipool = DBInteractionPool(dsn, pool_size=1, do_log=False) def test_connect_nonexisting_db(self): with self.assertRaises(DBPoolConnectionException): dsn = "host=127.0.0.1 port=5432 user=postgres dbname=gdbpool_test_not_here" fail_ipool = DBInteractionPool(dsn, pool_size=1, do_log=False) def _test_connect_pool_size_too_big(self): pass def test_invalid_query(self): """Test running an invalid SQL interactions on the DBInteractionPool""" sql = """ ESLECT val1, count(id) FROM test_values GROUP BY val1 order by val1; """ with self.assertRaises(DBInteractionException): res = self.ipool.run(sql) print res.get() def test_select_ip_query(self): """ Test running a bunch of random queries as SQL interactions on the DBInteractionPool """ sql1 = """ SELECT val1, count(id) FROM test_values WHERE val2 = %s GROUP BY val1 order by val1; """ sql2 = """ SELECT pg_sleep( %s ); """ sql3 = """ SELECT val1, count(id) FROM test_values GROUP BY val1 order by val1; """ import random from time import time greenlets = [] def tests(val2): stt = time() ran = random.random() if ran <= 0.33: sql = sql1 res = self.ipool.run(sql, [val2]) elif ran <= 0.66: sql = sql2 res = self.ipool.run(sql, [0.5]) else: sql = sql3 res = self.ipool.run(sql) r = res.get() et = time() logger.info(r[0]) for i in xrange(1, 10): greenlets.append(gevent.spawn(tests, i)) gevent.joinall(greenlets, timeout=10) def test_select_ip_interaction(self): def interaction(conn): curs = conn.cursor() sql = """ SELECT val1, val2, count(id) FROM test_values GROUP BY val1, val2 order by val1, val2; """ curs.execute(sql) res = curs.fetchall() curs.close() return res res = self.ipool.run(interaction).get() # logger.info( res ) def test_select_ip_interactions(self): def interaction(conn): curs = conn.cursor() sql = """ SELECT val1, count(id) FROM test_values GROUP BY val1 order by val1; """ curs.execute(sql) res = curs.fetchall() curs.close() return res def print_res(res): logger.info("?") while 1: try: r = res.get().get_nowait() logger.info(r) break except gevent.Timeout: gevent.sleep(0.01) greenlets = [] for i in xrange(5): greenlets.append(gevent.spawn(self.ipool.run, interaction)) greenlets[i].link(print_res) gevent.joinall(greenlets, timeout=10, raise_error=True) gevent.sleep(1) def test_listen_on(self): def run_insert(wait): gevent.sleep(0.1) sql = """ INSERT INTO test_values ( val1, val2 ) VALUES ( %s, %s ); SELECT pg_sleep( %s ); """ val1 = random.randint(1, 10) val2 = random.randint(1, 100) sleep_time = wait + random.random() res = self.ipool.run(sql, [val1, val2, sleep_time]) r = res.get() res.get() def run_update(wait): gevent.sleep(0.1) sql = """ UPDATE test_values SET val1 = 11, val2 = %s WHERE id = %s; SELECT pg_sleep( %s ); """ val2 = random.randint(1, 100) id = random.randint(1, 1000000) sleep_time = wait + random.random() res = self.ipool.run(sql, [val2, id, sleep_time]) r = res.get() def listen(): runtime = 0.0 rq = Queue(maxsize=None) stop_event = gevent.event.Event() gevent.spawn(self.ipool.listen_on, result_queue=rq, channel_name='notify_test_values', cancel_event=stop_event) import time while 1: st = time.time() if runtime > 5.0: break try: notify = rq.get_nowait() print "NOTIFY", notify except QueueEmptyException: gevent.sleep(0.001) tt = time.time() - st runtime += tt stop_event.set() gevent.sleep(1) for i in xrange(5): gevent.spawn(listen) greenlets = [] for i in xrange(5): greenlets.append(gevent.spawn(run_insert, i)) greenlets.append(gevent.spawn(run_update, i)) gevent.joinall(greenlets) gevent.sleep(1) def test_partial_run(self): def interaction_part1(conn, cursor): # cursor = conn.cursor() sql = """ SELECT * FROM test_values WHERE id = 20000 FOR UPDATE; """ cursor.execute(sql) res = cursor.fetchone() return res # setting commit=false i want to get back not only the result, but also # the connection and cursor (that might hold any locks) as well txn_part1 = self.ipool.run(interaction_part1, partial_txn=True).get() print "result from partial txn 1:", txn_part1 data = txn_part1['result'] conn = txn_part1['connection'] cursor = txn_part1['cursor'] #TODO: do this inside the test - not manually print "try running:\nUPDATE test_values SET val2 = val2 + 100 WHERE id = %s;\nand check that the result will be %s. the current value for val2 is %s" % ( data['id'], data['val2'] + 200, data['val2']) gevent.sleep(5) def interaction_part2(conn, cursor, pk, val2): try: sql = """ UPDATE test_values SET val2 = %s WHERE id = %s; """ cursor.execute(sql, [val2, pk]) res = cursor.fetchall() conn.commit() except Exception, e: res = e conn.rollback() return res txn_part2 = self.ipool.run(interaction_part2, conn=conn, cursor=cursor, pk=data['id'], val2=data['val2'] + 100).get() print "result from partial txn 2:", txn_part2
class gDBPoolTests(unittest.TestCase): def setUp(self): print "\n================================================================================\nRunning: %s\n================================================================================" % ( self._testMethodName ) self.ipool = DBInteractionPool(dsn, pool_size=16, do_log=True) def tearDown(self): self.ipool.__del__() def test_connect_nonexisting_host_port(self): with self.assertRaises(DBPoolConnectionException): dsn = "host=127.0.0.1 port=6432 user=postgres dbname=gdbpool_test" fail_ipool = DBInteractionPool(dsn, pool_size=1, do_log=False) def test_connect_nonexisting_db(self): with self.assertRaises(DBPoolConnectionException): dsn = "host=127.0.0.1 port=5432 user=postgres dbname=gdbpool_test_not_here" fail_ipool = DBInteractionPool(dsn, pool_size=1, do_log=False) def _test_connect_pool_size_too_big(self): pass def test_invalid_query(self): """Test running an invalid SQL interactions on the DBInteractionPool""" sql = """ ESLECT val1, count(id) FROM test_values GROUP BY val1 order by val1; """ with self.assertRaises(DBInteractionException): res = self.ipool.run(sql) print res.get() def test_select_ip_query(self): """ Test running a bunch of random queries as SQL interactions on the DBInteractionPool """ sql1 = """ SELECT val1, count(id) FROM test_values WHERE val2 = %s GROUP BY val1 order by val1; """ sql2 = """ SELECT pg_sleep( %s ); """ sql3 = """ SELECT val1, count(id) FROM test_values GROUP BY val1 order by val1; """ import random from time import time greenlets = [] def tests(val2): stt = time() ran = random.random() if ran <= 0.33: sql = sql1 res = self.ipool.run(sql, [val2]) elif ran <= 0.66: sql = sql2 res = self.ipool.run(sql, [0.5]) else: sql = sql3 res = self.ipool.run(sql) r = res.get() et = time() logger.info(r[0]) for i in xrange(1, 10): greenlets.append(gevent.spawn(tests, i)) gevent.joinall(greenlets, timeout=10) def test_select_ip_interaction(self): def interaction(conn): curs = conn.cursor() sql = """ SELECT val1, val2, count(id) FROM test_values GROUP BY val1, val2 order by val1, val2; """ curs.execute(sql) res = curs.fetchall() curs.close() return res res = self.ipool.run(interaction).get() # logger.info( res ) def test_select_ip_interactions(self): def interaction(conn): curs = conn.cursor() sql = """ SELECT val1, count(id) FROM test_values GROUP BY val1 order by val1; """ curs.execute(sql) res = curs.fetchall() curs.close() return res def print_res(res): logger.info("?") while 1: try: r = res.get().get_nowait() logger.info(r) break except gevent.Timeout: gevent.sleep(0.01) greenlets = [] for i in xrange(5): greenlets.append(gevent.spawn(self.ipool.run, interaction)) greenlets[i].link(print_res) gevent.joinall(greenlets, timeout=10, raise_error=True) gevent.sleep(1) def test_listen_on(self): def run_insert(wait): gevent.sleep(0.1) sql = """ INSERT INTO test_values ( val1, val2 ) VALUES ( %s, %s ); SELECT pg_sleep( %s ); """ val1 = random.randint(1, 10) val2 = random.randint(1, 100) sleep_time = wait + random.random() res = self.ipool.run(sql, [val1, val2, sleep_time]) r = res.get() res.get() def run_update(wait): gevent.sleep(0.1) sql = """ UPDATE test_values SET val1 = 11, val2 = %s WHERE id = %s; SELECT pg_sleep( %s ); """ val2 = random.randint(1, 100) id = random.randint(1, 1000000) sleep_time = wait + random.random() res = self.ipool.run(sql, [val2, id, sleep_time]) r = res.get() def listen(): runtime = 0.0 rq = Queue(maxsize=None) stop_event = gevent.event.Event() gevent.spawn( self.ipool.listen_on, result_queue=rq, channel_name="notify_test_values", cancel_event=stop_event ) import time while 1: st = time.time() if runtime > 5.0: break try: notify = rq.get_nowait() print "NOTIFY", notify except QueueEmptyException: gevent.sleep(0.001) tt = time.time() - st runtime += tt stop_event.set() gevent.sleep(1) for i in xrange(5): gevent.spawn(listen) greenlets = [] for i in xrange(5): greenlets.append(gevent.spawn(run_insert, i)) greenlets.append(gevent.spawn(run_update, i)) gevent.joinall(greenlets) gevent.sleep(1) def test_partial_run(self): def interaction_part1(conn, cursor): # cursor = conn.cursor() sql = """ SELECT * FROM test_values WHERE id = 20000 FOR UPDATE; """ cursor.execute(sql) res = cursor.fetchone() return res # setting commit=false i want to get back not only the result, but also # the connection and cursor (that might hold any locks) as well txn_part1 = self.ipool.run(interaction_part1, partial_txn=True).get() print "result from partial txn 1:", txn_part1 data = txn_part1["result"] conn = txn_part1["connection"] cursor = txn_part1["cursor"] # TODO: do this inside the test - not manually print "try running:\nUPDATE test_values SET val2 = val2 + 100 WHERE id = %s;\nand check that the result will be %s. the current value for val2 is %s" % ( data["id"], data["val2"] + 200, data["val2"], ) gevent.sleep(5) def interaction_part2(conn, cursor, pk, val2): try: sql = """ UPDATE test_values SET val2 = %s WHERE id = %s; """ cursor.execute(sql, [val2, pk]) res = cursor.fetchall() conn.commit() except Exception, e: res = e conn.rollback() return res txn_part2 = self.ipool.run( interaction_part2, conn=conn, cursor=cursor, pk=data["id"], val2=data["val2"] + 100 ).get() print "result from partial txn 2:", txn_part2