thanks for this example. There's several issues with this mapping. The most crucial, although not the issue in this specific example, is that the "relations" table is used both as the "secondary" table in a relation(), and is also mapped directly to the Relation class. SQLA does not track this fact and even in a working mapping will attempt to insert multiple, redundant rows into the table if you had, for example, appended to the "records" collection and also created a Relation object. This is mentioned at the bottom of http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_patterns_association but is also more strongly emphasized in the 0.5 docs, since its a very common mistake (its also not entirely a mistake if the mappings are used carefully or with the viewonly=True flag, hence we haven't built a check for this, although its probably something we should do).
The next issue which is the specific cause of the problem here is that SQLA's topological sort is based off of the relationships between classes and objects, and not directly the foreign key relationships between tables. Specifically, there is no stated relationship between the Record class and the Soup/Collection classes - yet you append a Record object to the "records" collection which is only meant to store "Soup" objects. SQLA sees no dependency between the Collection and Record mappers in this case, and the order of table insertion is undefined. This collection append is only possible due to the "enable_typechecks=False" setting which essentially causes SQLA to operate in a slightly "broken" mode to allow very specific use cases to work (which are not this one- hence SQLA's behavior is still undefined). "enable_typechecks" , as the initial error message implied when it mentioned "polymorphic mapping", is meant to be used only with inheritance scenarios, and only with objects that are subclasses of the collected object. It suggests that a certain degree of typechecking should remain even if "enable_typechecks" is set to False (something for me to consider in 0.5). I've considered someday doing a rewrite of UOW that ultimately bases topological off of ForeignKey and the actual rows to be inserted, and that's it. It's nothing that will happen anytime soon as its a huge job and our current UOW is extremely stable and does a spectacular job for almost two years at this point. But even then, while such an approach might prevent this specific symptom with this specific mapping, it seems like a bad idea in any case to support placing arbitrary, unrelated types into collections that have been defined as storing a certain type. I'm not sure at all if that approach to UOW wouldn't ultmately have all the same constraints as our current approach anyway. Fortunately, the solution here is very simple as your table setup is a pure classic joined table inheritance configuration. The attached script (just one script; sorry, all the buildout stuff seemed a little superfluous here) illustrates a straightforward mapping against these tables which only requires that Record and Collection subclass Soup (which is the nature of the joins on those tables). The joins themselves are generated automatically by SQLA so theres no need to spell those out. The "enable_typechecks" flag is still in use here in its stated use case; that you have a collection which can "flush" subtypes of Soup, but when queried later, will only return Soup objects. You can improve upon that by using a polymorphic discriminator (see the docs for info on that). The script illustrates using the "secondary" table in the "records" collection; this is what seems reasonable considering that there is no other meaningful data in the "relations" table (the surrogate PK in that table is also superfluous). If there are meaningful columns in your actual application's version of the table, then you'd want to do away with "secondary" and use the association object pattern. --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine('postgres://scott:[EMAIL PROTECTED]/test', echo=True) # set up session connection = engine.connect() Session = sessionmaker(autoflush=True, transactional=True) session = Session(bind=connection) # set up metadata metadata = MetaData(engine) class Soup(object): pass class Collection(Soup): pass class Relation(object): pass class Record(Soup): pass soup = Table( 'soup', metadata, Column('id', Integer, primary_key=True, autoincrement=True), Column('uuid', String(length=32), unique=True, index=True), Column('spec', String, index=True), ) # association table relations = Table( 'relations', metadata, Column('id', Integer, primary_key=True, autoincrement=True), Column('left', String(length=32), ForeignKey("soup.uuid"), index=True), Column('right', String(length=32), ForeignKey("soup.uuid")), ) # collection table collections = Table( 'collections', metadata, Column('id', Integer, ForeignKey("soup.id"), primary_key=True), ) # records table records = Table( 'records', metadata, Column('id', Integer, ForeignKey("soup.id"), primary_key=True), Column('name', Unicode), ) metadata.drop_all() metadata.create_all() # orm mapping mapper(Soup, soup) mapper(Record, records, inherits=Soup) mapper(Collection, collections, inherits=Soup, properties={ 'records':relation(Soup, secondary=relations, primaryjoin=soup.c.uuid==relations.c.left, secondaryjoin=relations.c.right==soup.c.uuid, enable_typechecks=False) }) record = Record() record.uuid = "R1" record.name = u"Record A" session.save(record) collection = Collection() collection.uuid = "C1" collection.records.append(record) session.save(collection) session.commit()
On Jun 11, 2008, at 6:24 PM, Malthe Borch wrote: > Michael Bayer wrote: >> you'd have to work this into a full self-contained script which I can >> run locally since it seems theres some specific usage pattern >> creating >> the issue. (i.e. its very difficult for me to piece together >> snippets >> and guess where the issue might be occuring). > > This is reasonably self-contained; I've tried to make it as short as > possible. > > src/example/tables.py: All tables and mappers > src/example/README.txt: Short demonstration which leads to error > > You can run the example using: > > $ python bootstrap.py > $ bin/buildout > $ bin/test > > Note that the example requires a Python with a working psycopg2; the > testrunner expects a database called "test" to be available on a > running > postgres. > > \malthe > > --~--~---------~--~----~------------~-------~--~----~ > 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 [EMAIL PROTECTED] > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en > -~----------~----~----~----~------~----~------~--~--- > > <example.tar.gz>