I read the documentation on transactions , but still a bit lost how should i really do it. Lets say i have class Book(name,content,left_key,right_key,level) in models.py with methods , which stores book content as nested sets. The main method i want use is
@transaction.commit_on_success
def add_node(self,right,level):
cursor = connection.cursor()
cursor.execute("UPDATE cms_Book SET left_key = left_key + 2, right_key = right_key + 2 WHERE left_key > %s",[right])
cursor.execute("UPDATE cms_Book SET right_key = right_key + 2 WHERE right_key >= %s AND left_key < %s ",[right,right])
cursor.execute("INSERT INTO cms_Book SET left_key = %s, right_key = %s + 1, level = %s + 1, content='roar'",[right,right,level])
But as insertion mainly depends on keys passed from template as parameters i need to check if correct keys are inserted .Even if for example wrong keys are inserted , node will still be added and nothing will break and no exception will occur but the logic will break which is not very good, in case i need to get specific nodes and not all of them.
So i have several other methods to check whether logic is correct or not. Here is one of them:
def check_count(self):
q = Book.objects.aggregate(min_key = Min('left_key'),max_key= Max('right_key'),count_all = Count('id'))
return q
What i need is kind of execute all the actions in add_node method , then check if the logic didn't break with check methods , if it didn't ,commit everything otherwise rollback. What i kind of don't understand is, if i use try except block i need db to produce some kind of error/exception which it won't , or for example to do:
if (check_my_table_for_all_different_keys == none):
transactions.commit
else:
transactions.rollback
check_my_table_for_all_different_keys - returns nothing if all keys are unique , otherwise returns id of objects which are identical or which have same keys. Also i am not sure how exactly should part look where i commit all 3 transactions , in case my logic on if - else structure is correct , which i think is not.
The django documentation on transaction management shows two possible ways. The examples assume you have a valid function (e.g. your check_my_table_for_all_different_keys) which validates the current state of the database, and returns false when the data is wrong.
use commit_on_success (as you currently do)
This way the pending transaction is committed once the function successfully returns. When an exception is raised it is rolled back.
@transaction.commit_on_success
def add_node(self, right, level):
# insert/update entries
if not valid():
raise Exception('invalid state')
The calling code needs to handle this exception, but knows if the node has been added successfully.
use commit_manually
This is more explicit, in a way that you have to commit or rollback when appropriate. Your example allready goes that direction.
@transaction.commit_manually
def add_node(self, right, level):
# insert/update entries
if valid():
transaction.commit()
else:
transaction.rollback()
No exception is raised, 'errors' are silenced. The transaction has been committed if everything was correct, or rolled back otherwise.