How to test transactions

[wpmccormick]

What is the easiest way to test SQL transactions?

I'm looking for some way a non-programmer QA tester can affect series of named queries within a transaction to validate that the transaction as a whole is working as intended.

Cheers!

[pascal.fragnoud]

I guess you could put an insert query in a transaction, then make a page with a form to input the query parameters, and throw a table on that page to display the contents of the db table you inserted into ?

[wpmccormick]

I should have mentioned this is on an existing system, where previous developers were somewhat nonchalant when it came to using NQ's to make DB updates. So we're having to go back into existing code and wrap the NQ's in transactions.

My best solution so far is to lock a table (make it RO) so the NQ fails.

[bkarabinchak.psi]

Hopefully some of these tables have some data integrity rules on it, either uniqueness or non-allowed values. Try inserting something that violates one of those to cause a SQL error to make sure your rollback works correctly.

If there is nothing like this, you can always add a temporary insert/update trigger that always raises an error no matter what to induce a SQL error and ensure your code is handling SQL exceptions/rolling back correctly.

[pascal.fragnoud]

I'm not sure I can help you with how to test a transaction, to be frank I'm not sure what exactly you want to test.... but

@wpmccormick

So we're having to go back into existing code and wrap the NQ's in transactions.

I had to wrap a bunch of queries into transactions. I made a transaction decorator so I wouldn't have to do it for EVERY function. Maybe this can give you ideas if you're wondering how to speed up the process.

[bkarabinchak.psi]

Hard agree on this, a decorator is a very nice way to handle a transaction - then your business logic functions should boil down to the series of named queries you are running in a row essentially.

Here's an example I've posted before about a decorator that handles a transaction and returns a GUI-agnostic response object so that you can then decide what to do with the results -

Script Benchmarking with Decorators Example

I made something similar to what you are aiming for but its also made for managing database transactions and errors in a standard way by creating a Response object that then I inspect after. import functools import timeit import traceback from forms2.structs import Response import java.lang.Throwable class handleTransaction(object): def __init__(self, logger): self.logger = logger def __call__(self, inner_func): """ We do 'tx' not in kwargs a few times here. If a fucntion has tx=None…

There's a few other examples as well across the forum with decorators handling transactions, and you may want to modify it for your use case, but this is the gist of it.

[pascal.fragnoud]

Mine is... quite different.
I use function style instead of class, and I raise custom errors instead of returning a response.

[PGriffith]

As long as we're on this tangent...
I have plans to add a first-party Transaction context manager, so something like:

with system.db.transaction(settings...) as tx:
    tx.runPrepUpdate()
    tx.commit()

To at least avoid the explicit begin and close transaction calls, the need to repeat the transaction ID, etc.

[pascal.fragnoud]

I actually tried to go the context manager way before implementing the decorator, but it somehow wouldn't work :X

[bkarabinchak.psi]

Yea I am partial the class style now - makes feeding things into the decorator easier (for me) like the LOGGER for whatever scripting library I am calling it in / using the __init__ part of the class to default a logger if needed (though I don't think I do that in the version I linked).

Ha, I tried the making a context manager style too originally but couldn't get it working either.

[pascal.fragnoud]

I get what you mean for decorator parameters.
But in this case I don't use any, I declare the logger like this:

logger = system.util.getLogger("transaction_".format(func.__name__))