Hi,
I don't know if what I'm trying to do is wrong-headed or needlessly pedantic,
but I've just spent most of a day discovering that ordering_list is erroring
because it doesn't like my schema. And this just when I've decided SQLAlchemy
must finally be the tool that supports properly normalised data! :-)
I'm hoping for two outcomes from this: the first, a note somewhere so that the
next person doesn't struggle for quite as long as I did, and second, some
advice on how I can improve my situation (short of the pragmatic "use a
synthetic key" approach).
Based on the demo in the doctests:
>>> metadata = MetaData()
>>> users = Table('users', metadata,
... Column('id', Integer, primary_key=True))
>>> blurbs = Table('user_top_ten_list', metadata,
... Column('id', Integer, primary_key=True),
... Column('user_id', Integer, ForeignKey('users.id')),
... Column('position', Integer),
... Column('blurb', String(80)))
>>> class User(object):
... pass
...
>>> class Blurb(object):
... def __init__(self, blurb):
... self.blurb = blurb
...
>>> mapper(User, users, properties={
... 'topten': relation(Blurb, collection_class=ordering_list('position'),
... order_by=[blurbs.c.position])})
I try to keep my databases normalised, so on blurbs I'd prefer to have a
natural primary key:
>>> blurbs = Table('user_top_ten_list', metadata,
... Column('user_id', Integer, ForeignKey('users.id'),
primary_key=True),
... Column('position', Integer, primary_key=True),
... Column('blurb', String(80)))
But when I do this, all appears to be fine until I attempt:
>>> u.topten.insert(1, Blurb('I am the new Number Two.'))
Now, the insert() succeeds, but if an engine is bound the next session.commit()
will fail with a cryptic error message (New instance conflicts with persistent
instance).
I've never been a fan of having to corrupt my schema to suit the tools, though
that would certainly be the easiest solution here. Looking at
ext/orderinglist.py, I have no idea how it should be modified to behave
properly with my schema. It looks like a workaround would be to use a Float
for 'position' and provide an ordering_func .. but I'm a little hazy what
edge-cases this might face me with. Alternatively, I might be better off
writing my own custom collection class -- at least this way I ought to learn
something useful about SA.
Any suggestions?
thanks,
d
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
