Perhaps you have wondered how in Oracle you can create a unit test, modifying the database temporarily then rollback the changes when you have completed your test.
The goal:
- To test a procedure that will handle most of the work
Here are the barriers I encountered during the design:
A- Should I test strictly in the database with pluto, oracle's tools or via python?
B- What exactly is the behavior of the are transactions handled in Oracle?
C- How can I be sure what I use in python won't commit automagically?
D- How can I guarantee that even if it commits it will be rolled back?
E - Is it even possible to perform a nested transaction such that I can run my tests in a unit test
Analysis
A - In either case the result would be an acceptable test, but I made the decision to test the procedure with Python. Why? Because its use in this system would be run through python, therefore it would be superior if python is used because it is closer to the true
B,C,D,E - Oracle permits nested transactions. However, when it comes to verifying those changes, only the parent can read the changes uncommitted in memory. Therefore if I can run raw SQL, I should be able to nest things in a way that I know who the parent is and can roll it back.
I tried varied methods of attack. What I found is that for the cx_oracle package, you effectively must use the oracle cursor to set up transactions and raw sql.
F - Then I encountered a new barrier: How can I structure the transaction and setup statically so it doesn't have to be rerun each time and I keep the same session?
G - Also, how can I prevent implied commits and whether or not that is even required?
For F, I discovered that we basically can model a sort of singleton pattern where we create some static variables such that they can be initialized by the class. Now you may wonder why this is a question at all. The reason is that if we modify the __init__ of the class we risk breaking the normal functionality of how the inherited class is called. If we break that, how can we be sure that whatever unittesting system we put on top will be able to properly call our UnitTest.TestCase? People don't like breaking the build, but how about breaking the build system. Searching for this yeilds a lot of noise, so f* that noise. We dodge the __init__ with a static var checked and set in a function, which we put into the setUp that is called before each unit test.
class TestMagicBankTransferBasicCase_Sprint5_Item2(unittest.TestCase):
ranonce = None
def performOneTimeSetup(self):
if not TestMagicBankTransferBasicCase_Sprint5_Item2.ranonce:
#connnect to db
TestMagicBankTransferBasicCase_Sprint5_Item2.connection = somedbutil.connectToDev('cx_Oracle',password)
if not somdbutil.connected():
sys.exit('Error: Could not connect to db')
TestMagicBankTransferBasicCase_Sprint5_Item2.sql_every_function_needs = "call myprocedure.thisisfnnuts(%s,%s)"
TestMagicBankTransferBasicCase_Sprint5_Item2.rollback_directive = "ROLLBACK TO TMBTBC52"
def setUp(self):
#do this, it is the right thing to do
unittest.TestCase.setUp(self)
self.performOneTieSetUp()
#Part of F's answer, not tested to verify it is required
TestMagicBankTransferBasicCase_Sprint5_Item2.connection.autocommit = 0 #assuming this exposes the oracle connection object
self.conn = TestMagicBankTransferBasicCase_Sprint5_Item2.connection
cursor = self.conn.cursor()
savepoint_directive = "SAVEPOINT TMBTBC52" #not tested for naming rules in oracle
cursor.execute(savepoint_directive)
def test_transfered(self):
#call my proc
#run tests
def tearDown( self):
#again, do this, it is the right thing to do
unittest.TestCase.tearDown(self)
cursor = self.conn.cursor()
cursor.execute(TestMagicBankTransferBasicCase_Sprint5_Item2.rollback_directive)
Why did I blog on this?
Not a single reference to the actual use of savepoints with Oracle and Python.
Have fun storming the castle.
