Thanks very much, Michael. I'll be looking into each point in detail.
Regarding the nested sets example, I've been scouring it since it is
much closer to my real use case which is hierarchical data. As tends
to happens more often than I like, I ended up finding out that I
basically re-invented an existing pattern (nested sets), albeit in a
slightly different form. I think in ordered treelists with indent
levels instead of nests, so my nodes have 'position', and
'position_of_bottom_leaf' instead of 'left' and 'right'. But after
looking into the nested sets pattern, I'm pretty sure they net the
same darned thing (verdict still out). I do think "bottom_leaf" is
much more intuitive to understand than twisting our perspectives to
match "right", but whatever. If I had seen nested sets in advance I
could have saved myself some brain ache for figuring out how to manage
my hierarchies!!
Anyway...
Sticking with the crude list bumping example that ends up doing a
multi-row update, and using some sql syntax like in your nested sets
example, my _InsertBefore method ends up working looking like this:
def _InsertBefore(self, name_to_bump):
objects = self.__table__
insertLoc = self._ormSess. \
query(OrderedObject.position). \
filter_by(name = name_to_bump). \
subquery().as_scalar()
#set the position of the new object...
# - decrement of 1 is odd, but is a result of the scalar
insertLoc,
# which will have been bumped by the time this object is flushed
self.position = insertLoc - 1
#and bump all subsequent object positions...
update = objects.update(objects.c.position >= insertLoc). \
values(position = objects.c.position + 1)
self._ormSess.execute(update)
The "insertLoc - 1" oddity is there because of me trying to save on an
emitted SQL statement by using subquery().as_scalar(). Although I
suppose it is not needed from a concurrency perspective since I'm in a
transaction and I can be sure no one else is updating it.
I think I'm closer to getting it right down at the database level
versus a high level abomination retrieving the entire table to achieve
an update. Can I get any lower, or are there any other remaining
abominations?
Russ
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
