I have a nested set implementation that I am working on. Currently, the steps involved to make a change to the table are:
1) Retrieve parent node (ORM Object) 2) Create new node (same ORM object as #1) 3) Calculate UPDATE boundaries, etc. from anchor node 4) Create UPDATE SQL based on #3 5) Execute #4 using session.execute() 6) Set corrected nested set related values on new node 7) issue session.flush() to INSERT new node 8) set treeid of new node to node.id if node is "root" node 9) session.commit() (or rollback if needed) This works so far, but I have two issues I would appreciate your help with. First, when I do multiple inserts in a row, I have problems unless I put commits() between each insert: http://paste.pocoo.org/show/110607/ (code example) If I don't put commits, then the ORM objects are stale (presumably b/c my UPDATE statement are affecting the underlying data) and subsequent inserts have the wrong values. So, ASSUMING my current thread is the only thing updating the table, it seems all I would need to do is somehow flag the session to make all ORM objects update from the DB before an attribute it used from that object. But, the assumption I just made isn't really valid. I would like this nested sets table/implementation to be able to be used by more than one thread, multiple processes, or even a different application altogether. How would I go about making the above method "safe" in a multi thread/process/application environment. Basically, worst case scenario would be that the node table is updated by a different application between steps #1 and #2 above. If that happens, when step #5 is executed, the node structure in the table would be hosed. The same could happen with a different thread or processes. I was thinking that I could lock the table before step #1 and unlock after step #9, but was wondering if that would work and also if there was a better option. I am currently testing this with SQLite, but would like it to work on mysql, postgres, or MSSQL as well. I *really appreciate* any input you can give. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
