def test_context_manager_no_commit(self):
with TRN:
sql = """INSERT INTO labcontrol.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
TRN.execute()
self._assert_sql_equal([])
self._assert_sql_equal([('insert1', True, 1), ('insert2', True, 2),
('insert3', True, 3)])
self.assertEqual(TRN._connection.get_transaction_status(),
TRANSACTION_STATUS_IDLE)
def test_context_manager_rollback(self):
try:
with TRN:
sql = """INSERT INTO labcontrol.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
TRN.execute()
raise ValueError("Force exiting the context manager")
except ValueError:
pass
self._assert_sql_equal([])
self.assertEqual(TRN._connection.get_transaction_status(),
TRANSACTION_STATUS_IDLE)
def test_context_manager_checker(self):
with self.assertRaises(RuntimeError):
TRN.add("SELECT 42")
with self.assertRaises(RuntimeError):
TRN.execute()
with self.assertRaises(RuntimeError):
TRN.commit()
with self.assertRaises(RuntimeError):
TRN.rollback()
with TRN:
TRN.add("SELECT 42")
with self.assertRaises(RuntimeError):
TRN.execute()
def test_index(self):
with TRN:
self.assertEqual(TRN.index, 0)
TRN.add("SELECT 42")
self.assertEqual(TRN.index, 1)
sql = "INSERT INTO labcontrol.test_table (int_column) VALUES (%s)"
args = [[1], [2], [3]]
TRN.add(sql, args, many=True)
self.assertEqual(TRN.index, 4)
TRN.execute()
self.assertEqual(TRN.index, 4)
TRN.add(sql, args, many=True)
self.assertEqual(TRN.index, 7)
self.assertEqual(TRN.index, 0)
def test_execute_return(self):
with TRN:
sql = """INSERT INTO labcontrol.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
TRN.add(sql, ['test_insert', 2])
sql = """UPDATE labcontrol.test_table SET bool_column = %s
WHERE str_column = %s RETURNING int_column"""
TRN.add(sql, [False, 'test_insert'])
obs = TRN.execute()
self.assertEqual(obs, [[['test_insert', 2]], [[2]]])
def test_execute_huge_transaction(self):
with TRN:
# Add a lot of inserts to the transaction
sql = "INSERT INTO labcontrol.test_table (int_column) VALUES (%s)"
for i in range(1000):
TRN.add(sql, [i])
# Add some updates to the transaction
sql = """UPDATE labcontrol.test_table SET bool_column = %s
WHERE int_column = %s"""
for i in range(500):
TRN.add(sql, [False, i])
# Make the transaction fail with the last insert
sql = """INSERT INTO labcontrol.table_to_make (the_trans_to_fail)
VALUES (1)"""
TRN.add(sql)
with self.assertRaises(ValueError):
TRN.execute()
# make sure rollback correctly
self._assert_sql_equal([])
def test_execute(self):
with TRN:
sql = """INSERT INTO labcontrol.test_table (str_column, int_column)
VALUES (%s, %s)"""
TRN.add(sql, ["test_insert", 2])
sql = """UPDATE labcontrol.test_table
SET int_column = %s, bool_column = %s
WHERE str_column = %s"""
TRN.add(sql, [20, False, "test_insert"])
obs = TRN.execute()
self.assertEqual(obs, [None, None])
self._assert_sql_equal([])
self._assert_sql_equal([("test_insert", False, 20)])
def test_execute_commit_false_wipe_queries(self):
with TRN:
sql = """INSERT INTO labcontrol.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
obs = TRN.execute()
exp = [[['insert1', 1]], [['insert2', 2]], [['insert3', 3]]]
self.assertEqual(obs, exp)
self._assert_sql_equal([])
sql = """UPDATE labcontrol.test_table SET bool_column = %s
WHERE str_column = %s"""
args = [False, 'insert2']
TRN.add(sql, args)
self.assertEqual(TRN._queries, [(sql, args)])
TRN.execute()
self._assert_sql_equal([])
self._assert_sql_equal([('insert1', True, 1), ('insert3', True, 3),
('insert2', False, 2)])
def test_execute_commit_false_rollback(self):
with TRN:
sql = """INSERT INTO labcontrol.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
obs = TRN.execute()
exp = [[['insert1', 1]], [['insert2', 2]], [['insert3', 3]]]
self.assertEqual(obs, exp)
self._assert_sql_equal([])
TRN.rollback()
self._assert_sql_equal([])
def test_execute_many(self):
with TRN:
sql = """INSERT INTO labcontrol.test_table (str_column, int_column)
VALUES (%s, %s)"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
sql = """UPDATE labcontrol.test_table
SET int_column = %s, bool_column = %s
WHERE str_column = %s"""
TRN.add(sql, [20, False, 'insert2'])
obs = TRN.execute()
self.assertEqual(obs, [None, None, None, None])
self._assert_sql_equal([])
self._assert_sql_equal([('insert1', True, 1), ('insert3', True, 3),
('insert2', False, 20)])
def test_execute_return_many(self):
with TRN:
sql = """INSERT INTO labcontrol.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
sql = """UPDATE labcontrol.test_table SET bool_column = %s
WHERE str_column = %s"""
TRN.add(sql, [False, 'insert2'])
sql = "SELECT * FROM labcontrol.test_table"
TRN.add(sql)
obs = TRN.execute()
exp = [
[['insert1', 1]], # First query of the many query
[['insert2', 2]], # Second query of the many query
[['insert3', 3]], # Third query of the many query
None, # Update query
[
['insert1', True, 1], # First result select
['insert3', True, 3], # Second result select
['insert2', False, 2]
]
] # Third result select
self.assertEqual(obs, exp)
msg = ('labcontrol.library_prep_shotgun_process contains the '
'constraint name fk_shotgun_library_prep_process_kapa. '
'Skipping...')
logging.debug(msg)
#
# rename index idx_shotgun_library_prep_process_kappa
#
sql = """ALTER INDEX IF EXISTS idx_shotgun_library_prep_process_kappa
RENAME TO idx_shotgun_library_prep_process_kapa;"""
logging.debug(sql)
statements.append(sql)
#
# update description string
#
sql = """UPDATE labcontrol.reagent_composition_type SET description =
'KAPA HyperPlus kit' where description =
'kappa hyper plus kit';"""
statements.append(sql)
# slightly inefficient, but TRN.add() does perform checking in addition to
# appending each SQL statement to its queue.
for statement in statements:
TRN.add(statement)
TRN.execute()
TRN.commit()