So I have two approaches here, besides the basic "postal_nl in the discriminator column" approach. One just puts a concrete wall at the bottom of PostalAddress. This is pretty easy, if you don't mind breaking the chain of inheritance on the DB side. This is postal_as_concrete.py.
The other illustrates the polymorphic-on-concatenation approach. As of yet, it
needs Table metadata to be configured before the classes, so that the
selectable can be constructed and configured with the Address mapper at
creation time. The semi-declarative approach is in
postal_as_joined_semi_decl.py. This is a style of configuration that I used
to think would be more common than it is in the earlier days, so uses well
established mapper configurational patterns, even though they are not widely
used.
If I have time, I may try to improve the "polymorphic_on" attribute so that it
can be set on an existing mapper - right now the internal state does not
configure itself correctly if you set it after the fact. This would allow a
straight declarative config, with a second step of setting "polymorphic_on",
"with_polymorphic" on the already-existing Address mapper.
These are tested on 0.7 but should work in 0.6, and most probably work more or
less in 0.5.8 (the Session import might need to be altered).
On Jan 31, 2011, at 10:58 AM, Michael Bayer wrote:
>
> On Jan 31, 2011, at 4:27 AM, Martijn Moeling wrote:
>
>> Hi,
>>
>> First I need to let you know I do everything declarative......
>>
>> To make things understandable I have chosen to use objects a bit more close
>> to real-life.
>>
>>
>> There is a base object Person with a one-to-many relationship to the table
>> addresses:
>>
>> class Person(Base):
>> __tablename__ = "person"
>> Id = Column(Integer, primary_key=True)
>> .....
>> Addresses = relation('Address',primaryjoin='Address.Person_Id ==
>> Person.Id', cascade="all")
>> ....
>>
>> Address is a polymorphic base type:
>>
>> class Address(Base):
>> __tablename__ = "adresses"
>> Id = Column(Integer,
>> primary_key=True)
>> discriminator = Column(Unicode(20))
>> __mapper_args__ = {'Polymorphic_on': discriminator}
>> ....
>> ...
>>
>> for a lot of address types to be appended to Person.Addresses like:
>>
>> class EmailAddress(Address):
>> __tablename__ = "emailaddresses"
>> __mapper_args__ = {'polymorphic_identity' : u'emailaddress'}
>> Id = Column(Integer,ForeignKey('addresses.Id'),
>> primary_key=True)
>> Value = Column(Unicode(100))
>>
>> class MSNAddress(Address):
>> __tablename__ = "msnaddresses"
>> __mapper_args__ = {'polymorphic_identity' : u'msnaddress'}
>> Id = Column(Integer,ForeignKey('addresses.Id'),
>> primary_key=True)
>>
>> class PostalAddress(Address):
>> __tablename__ = "postaladdresses"
>> Country = Column(Unicode(2),primary_key=True)
>> # Should "hold" the polymorphic Identity for
>> subclass like "NL","D","UK", "US"
>>
>> __mapper_args__ = {'polymorphic_identity' : u'postaladdress'}
>> # cannot add {'Polymorphic_on' : Counrty} to
>> this.... see below in text what happens if I do
>>
>> Id = Column(Integer,ForeignKey('addresses.Id'),
>> primary_key=True)
>>
>>
>>
>>
>> Now I want to make say a Dutch postaladdress, a german postaladdress.
>> The obvious way to do so is make the base class Polymorphic again but it
>> will "overwrite" the __mapper_args__ of the base class Address
>>
>> class NLPostalAddress(PostalAddress):
>> __tablename__ = "nlpostaladdress"
>> __mapper_args__ = {'polymorphic_identity' : u'NL'} # will
>> "overwrite" the polymorphic Identity of PostalAddress base class
>> Id = Column(Integer,ForeignKey('postaladdresses.Id'),
>> primary_key=True)
>> Street = Column(Unicode(100))
>> HouseNumber = Column(Unicode(10))
>> ...
>> ...
>>
>>
>> Person.Addresses.append(NLPostalAddress(Street = "somestreet", HouseNum
>> ......)) works but with: Address. discriminator stays empty. and
>> PostallAddress. Country will become 'NL'.
>> Which is logical (look at the comments in the "code") since the
>> polymorphic_on : Country makes the mapper "forget" it was polymorphic on
>> "Address.discriminator"
>>
>> PostalAddress.Id makes the ID part work well......
>>
>> Is there a way to "nest" polymorph classes in some way so the above works...
>>
>> One solution might be Concrete tables but I have not been able to get that
>> working either? since that would involve a "polymorphic union" I might be
>> missing something there... but I do not really understand the documentation
>> (I'm still using 0.5.8, upgrading is possible)
>>
>> Again this is a "fake" data model to make my point
>>
>> Any suggestions?
>
> The first thing to be aware of is that a Query against Address is going to
> emit a SELECT statement against the "addresses" table only. As rows come in,
> the "polymorphic_on" column is examined, and the resulting class, Address,
> EmailAddress, NLPostalAddress, is determined from this value. So already,
> having a second discriminator column in a sub-table will not work at this
> level, assuming you would like to query polymorphically against the base
> "Address" class. The reason mappers expect one and only one "polymorphic_on"
> column is because the value in this column represents the ultimate "type" of
> the object. The restriction being worked with here is that the resulting
> class is based on a single column in the result set.
>
> An object in any strongly typed language has only one "type" - the scheme
> here attempts to define the "type" of each row in terms of a derivation of
> two different "supertypes" - "discriminator" and "country". There's a way
> to get the mapper to see this, but it is more awkward.
>
> It is possible to have a polymorphic mapper query from a special selectable
> called the "with_polymorphic" selectable- in this case, you'd create one that
> queries against "addresses" and left outer joins to "postaladdress", creates
> a column that is a concantenation between "discriminator" and "country",
> then the mapper receives that. This is the kind of thing that you need to
> have Table objects declared outside of the class definitions (though you
> could still use declarative with __table__) in order to accomplish.
>
> So if you really wanted to see that I could show you, but it's not as
> practical of a solution - even simple queries against Address become nested
> select statements with a sub-performing OUTER JOIN built in. You'd probably
> need to have Table metadata defined outside of your declarative classes for
> best results (there might be ways around that but I'd have to scratch my head
> for awhile). "Country" and "discriminator" would be plain mapped columns
> that you'd need to populate yourself (here there are also ways to automate
> this population). Selectables at each level of inheritance often need to be
> defined, e.g. if you wanted to query from EmailAddress, PostalAddress. It's
> entirely customizable but once you go there you need to tell it everything.
> The documentation doesn't get into it very much since its an exotic use case.
>
> The other approach would be just to consider the "discriminator" column on
> "Address", to be the ultimate type of object being returned, not just one of
> its supertypes. This just means you're storing a discriminator in a form
> like "emailaddress_nl", "emailaddress_de". Since this column encapsulates
> information from both "discrminator" and "country" it is by definition
> denormalized. But it would offer the simplest configuration and the best
> performance.
>
> The "two discriminator" case is interesting though and I'll see if I can
> think of some recipes that make it work, with restrictions.
>
> If you want to limit which entities you can select from in the first place,
> i.e. every Query is only against EmailAddress or PostalAddress, never
> Address, that would also make it easier to come up with a solution.
>
>
>>
>>
>> Martijn
>>
>>
>>
>>
>>
>>
>> --
>> 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.
>
>
> --
> 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.
-- 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.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
discriminator = Column(String)
__mapper_args__ = {'polymorphic_on':discriminator}
class EmailAddress(Address):
__tablename__ = 'email_address'
id = Column(Integer, ForeignKey('address.id'), primary_key=True)
__mapper_args__ = {'polymorphic_identity':'email'}
class PostalAddress(Address):
__tablename__ = 'postal_address'
id = Column(Integer, primary_key=True)
country = Column(String)
__mapper_args__ = {'polymorphic_on':country, 'concrete':True}
class NLPostalAddress(PostalAddress):
__tablename__ = 'nl_postal_address'
id = Column(Integer, ForeignKey('postal_address.id'), primary_key=True)
__mapper_args__ = {'polymorphic_identity':'nl'}
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
EmailAddress(), NLPostalAddress()
])
s.commit()
print s.query(Address).all()
print s.query(PostalAddress).all()
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
address = Table('address', Base.metadata,
Column('id', Integer, primary_key=True),
Column('discriminator', String)
)
postal_address = Table('postal_address', Base.metadata,
Column('id', Integer, ForeignKey('address.id'), primary_key=True),
Column('country', String)
)
address_selectable = select([
address,
(address.c.discriminator + "|" +
case([(postal_address.c.country!=None,postal_address.c.country)], else_='')).label('type')
], use_labels=True).\
select_from(
address.outerjoin(postal_address)
).alias()
class SetDiscriminators(object):
def __init__(self, **kw):
super(SetDiscriminators, self).__init__(**kw)
for k, v in self.set_discriminators.items():
setattr(self, k, v)
class Address(SetDiscriminators, Base):
__table__ = address
set_discriminators = {'discriminator':'address'}
__mapper_args__ = {
'polymorphic_on':address_selectable.c.type,
'with_polymorphic':('*', address_selectable)
}
class EmailAddress(Address):
__tablename__ = 'email_address'
id = Column(Integer, ForeignKey('address.id'), primary_key=True)
set_discriminators = {'discriminator':'email'}
__mapper_args__ = {'polymorphic_identity':'email|'}
class PostalAddress(Address):
__table__ = postal_address
type = column_property('postal|' + postal_address.c.country)
set_discriminators = {'discriminator':'postal'}
__mapper_args__ = {'polymorphic_on':type.columns[0]}
class NLPostalAddress(PostalAddress):
__tablename__ = 'nl_postal_address'
id = Column(Integer, ForeignKey('postal_address.id'), primary_key=True)
set_discriminators = {'discriminator':'postal', 'country':'nl'}
__mapper_args__ = {'polymorphic_identity':'postal|nl'}
e = create_engine("sqlite://", echo='debug')
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
EmailAddress(), NLPostalAddress()
])
s.commit()
print s.query(Address).all()
print s.query(PostalAddress).all()
On Jan 31, 2011, at 10:58 AM, Michael Bayer wrote:
|
