there's a nested set demo in the examples/ folder of the distribution, which uses MapperExtension as well as a little-used flag on the mapper called "batch=False". are you building off of that ? as far as multithread/process, the UPDATE statements which nested sets requires would result in a lock of most of the table, thus protecting it from concurrent updates. nested sets is extremely inefficient in a concurrent write environment.
There is an alternative "nested set" recipe which uses fractional boundaries and solves most of these problems, its at http://www.dbazine.com/oracle/or-articles/tropashko4 . it relies upon oracle stored procs which can be ported to postgres or mysql. also keep in mind that oracle, PG and firebird all support some kind of recursive query operator now (oracle has CONNECT BY) - SQLAlchemy plans on integrating these operators at some point, hopefully in a backend agnostic way, which would provide single-round trip access to adjacency list models. just that operator alone may render nested sets largely obsolete. Randy Syring wrote: > > 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 [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
