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 [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
-~----------~----~----~----~------~----~------~--~---
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 [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
> -~----------~----~----~----~------~----~------~--~---
>
> <example.tar.gz>
