On Feb 13, 2011, at 6:14 AM, Martijn Moeling wrote:
> You are right in the misunderstood relation.
>
> I see the primary key in extra to be wrong, extra should have it's own I'd
> column being an auto number. In extra it should be possible to have many
> records pointing to 1 ext variant. Sorry for that.
>
> The extra, should also work with tables without a discriminator, there the
> link should be made to table name which is in my case always class.__name__
> ...... On those tables, the relation needs to be different since on of the
> "local" columns, discriminator is not present and it somehow should be linked
> to __table name__
OK what you are trying to do is exactly a "polymorphic association". The
technique of placing "tablename" in the table of "related" records, then using
that "tablename" to indicate which parent table should be matched at query
time, is a common, but IMHO relationally incorrect pattern. I blogged
extensively about the Ruby on Rails approach, how to duplicate Rails' approach
in SQLAlchemy, and then an alternate system which maintains referential
integrity, four years ago at
http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/
.
That's a really old example and we're into 0.7 now, so I've created a new
version of the poly assoc example that uses declarative techniques and the
association proxy, which is attached. I'm going to further fix up this example
and add it to the distribution as a fourth example of polymorphic association,
which is in examples/poly_assoc/.
Additionally I'd like the association proxy to work more smoothly in queries so
I've added ticket #2054 for some of those issues which weren't accounted for
when we first added any(), contains() operators to the association proxy.
>
> It is all part of the ACL examples it talked about walker, where extra must
> be seen as the ACL. That is where the mapper extension comes in....
>
> It is getting a "all technologies" mixed in situation
>
> Verstuurd vanaf mijn iPad
>
> Op Feb 12, 2011 om 17:05 heeft "Michael Bayer" <mike...@zzzcomputing.com> het
> volgende geschreven:
>
>> OK I can show you the version of your code that does most of this but there
>> are some fundamental relational misunderstandings in this schema if I am
>> interpreting correctly.
>>
>> Extra:
>>
>> tableid tablename
>> ------- ---------
>> 1 ext1
>> 2 ext1
>> 3 ext2
>> 4 ext2
>> 5 ext3
>>
>> ext1:
>>
>> id discriminator (-> FK to Extra.tableid, Extra.tablename)
>> -- -------------
>> 1 ext1
>> 2 ext1
>> 3 ext2
>> 4 ext2
>> 5 ext3
>>
>> ext2:
>>
>> id
>> --
>> 3
>> 4
>>
>> ext3:
>>
>> id
>> --
>>
>> 5
>>
>> given ext1 ID #3, discriminator "ext2" - how can more than one Extra row be
>> referenced? Why is "extras" assumed to be one-to-many when it can only be
>> many-to-one ?
>>
>>
>>
>>
>>
>>
>> On Feb 12, 2011, at 9:57 AM, Martijn Moeling wrote:
>>
>>> This whole thing is driving me crazy, What I want:
>>>
>>>
>>>
>>> class Extra(Base):
>>> __tablename__ = "extra"
>>> # Primary key consists of two different columns !!!
>>> tableId = Column(Integer, primary_key=true)
>>> tablename = Column(Unicode(20), primary_key=True)
>>>
>>> info = Column(........) #Not relevant
>>>
>>>
>>>
>>>
>>> class ex1(Base):
>>> Id = Column(Integer, primary_key=True)
>>> discriminator = Column(Unicode(20))
>>>
>>> @declared_attr
>>> def __tablename__(self):
>>> return self.__name__.lower()
>>>
>>> @declared_attr
>>> def __mapper_args__(self):
>>> if self.__name__ == 'ext1':
>>> return {'polymorphic_on': self.discriminator,
>>> 'polymorphic_identity':unicode(self.__name__.lower()),
>>> 'extension': FilePropertiesMapperExtension(),
>>> 'batch' : False}
>>> else:
>>> return {'polymorphic_identity':unicode(self.__name__.lower()),
>>> 'inherit_condition': self.Id == extra.Id,
>>> #needed for something else in this config (multiple self reference)
>>> 'extension': FilePropertiesMapperExtension(),
>>> #Needed for something else, not relevant for this sample
>>> 'batch' : False}
>>> # ,, ,, ,, ,,
>>>
>>>
>>> # Set up foreignkey and relation to Extra....
>>> __table_args__ = (ForeignKeyConstraint(['discriminator', 'Id'],
>>> ['extra.Table','extra.TableId']),{})
>>>
>>>
>>> extras = relation('Extra', cascade="all", lazy="dynamic"
>>> backref="owner")
>>>
>>>
>>> ....
>>> ....
>>>
>>>
>>> class ext2(ext1):
>>> Id = Column(Integer,ForeignKey('ext1.Id'),
>>> primary_key=True)
>>> ......
>>>
>>>
>>> class ext3(ext1):
>>> Id = Column(Integer,ForeignKey('ext1.Id'),
>>> primary_key=True)
>>> .....
>>>
>>>
>>>
>>>
>>> Now I want:
>>>
>>> Ext2 = ext2()
>>> Extra_info = extra()
>>> Ext2.extras.append(Extra_Info)
>>>
>>>
>>> Ext2.discriminator should be "ext2"
>>> Ext2.Id should be 1 for the first record
>>>
>>> Extra_Info should be created in the database, with its columns : id set to
>>> the Ext2.id and tablename to Ext.discriminator ......
>>> Extra_Info.owner would point to Ext2
>>>
>>> If Ext2 is deleted, all related extrainfo record would be delete too
>>>
>>> if one Extra_Info is deleted, Extra_Info.owner should stay in place as well
>>> as all other related
>>>
>>> Extra is many to one polymorhic version of ext1
>>>
>>> I hope this clarifies more what I want....
>>>
>>> I really need the @declared_attr way of doing stuff and that is not related
>>> to this question but might influence this question so I left it in......
>>>
>>> Martijn
>>>
>>>
>>> On Feb 10, 2011, at 18:13 , Michael Bayer wrote:
>>>
>>>>
>>>> On Feb 10, 2011, at 4:20 AM, Martijn Moeling wrote:
>>>>
>>>>> Another small thing:
>>>>>
>>>>> I took a look at:
>>>>>
>>>>> ForeignKeyConstraint(['invoice_id', 'ref_num'], ['invoice.invoice_id',
>>>>> 'invoice.ref_num'])
>>>>>
>>>>> Now for Polymorphic tables:
>>>>>
>>>>>
>>>>> in baseclass:
>>>>>
>>>>> baseclass.discriminator happens to be the __tablename__ of the
>>>>> polymorphic
>>>>>
>>>>> ForeignKeyConstraint('['baseclass.disciminator', baseclass.Id'],
>>>>> ['someotherclass.tablename','someotherclass.tableId']
>>>>> relationship('someotherclass', backref=baseclass, cascade="all",
>>>>> lazy="dynamic")
>>>>>
>>>>>
>>>>> in someotheclass:
>>>>>
>>>>> tablename = column(Unicode(20), primary_key=True)
>>>>> tableId = column(Integer, primary_key=True)
>>>>>
>>>>> seems Ok to me.
>>>>>
>>>>> Now I need to make someotherclass work with non-polymorphic tables too!!
>>>>>
>>>>> anotherclass:
>>>>> Id = column(Integer, primary_key=True)
>>>>>
>>>>> ForeignKeyConstaint('[anotherclass.__tablename__,'anotherclass.Id'],['someotherclass.tablename','someotherclass.tableId'])
>>>>> relation('someotherclass', backref=baseclass, cascade="all",
>>>>> lazy="dynamic")
>>>>>
>>>>> Is there any way to get this working without configuring it as
>>>>> polymorphic an do no Inhiritance, I do not want each anotherclass record
>>>>> to have a column discriminator with its own tablename!
>>>>>
>>>>> or can I use anotherclass.__tablename__ in the ForeignKeyConstaint?
>>>>>
>>>>> This has to do with the someotherclass being the "ACL" I talked about in
>>>>> a previous post if that gives extra info. I am trying to implement the
>>>>> MapperExtension.before_append where I need to refer to the "ACL" records
>>>>> in a way like:
>>>>>
>>>>> for A in instance.ACL:
>>>>> .....
>>>>
>>>> yeah, sorry, this use case continues to be 98% opaque to me. I don't
>>>> understand what you mean by "make someotherclass work with non-polymorphic
>>>> tables", a class is mapped in just one way, either with or without a
>>>> discriminator column. A single class can't be mapped in both ways. If
>>>> there's no discriminator, there's just one class that can be used for
>>>> returned rows.
>>>>
>>>> If you could create a small test that illustrates a mapping and an
>>>> expected result, perhaps I can attempt to find a way to get the ORM
>>>> behavior you're looking for.
>>>>
>>>> Your mapper extension would continue to be used normally with a "dynamic"
>>>> relationship since it uses query(cls) just like a regular query.
>>>>
>>>>
>>>>
>>>> --
>>>> 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.
>>
>
> --
> 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 Column, Integer, create_engine, ForeignKey, Unicode
from sqlalchemy.orm import Session, relationship, validates, subqueryload
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.ext.associationproxy import association_proxy
import re
class Base(object):
@classmethod
def _identifier(cls):
return cls.__name__[0].lower() + re.sub(
'([A-Z])',lambda m:"_" + m.group(1).lower(),
cls.__name__[1:])
@declared_attr
def __tablename__(cls):
return cls._identifier()
@declared_attr
def id(cls):
return Column(Integer, primary_key=True)
Base = declarative_base(cls=Base)
class Extra(Base):
"""Target object which can be associated with many
kinds of parents."""
info = Column(Unicode)
assoc_id = Column(Integer, ForeignKey('extra_association.id'))
@property
def owner(self):
return getattr(self.association,
'_backref_%s' %
self.association.discriminator)[0]
class ExtraAssociation(Base):
"""Associates a parent with a collection of Extra."""
def __init__(self, targets):
self.targets = targets
discriminator = Column(Unicode(20))
targets = relationship(Extra,
backref="association",
lazy="joined",
innerjoin=True,
cascade="all, delete-orphan")
class HasExtra(object):
"""Identifies a parent which relates to Extra."""
@declared_attr
def _extra_assoc(cls):
return relationship(ExtraAssociation,
backref='_backref_%s' % cls._identifier(),
cascade="all, delete-orphan",
single_parent=True)
@validates('_extra_assoc')
def _set_extra_assoc(self, key, value):
value.discriminator = self._identifier()
return value
@declared_attr
def assoc_id(cls):
return Column(Integer, ForeignKey('extra_association.id'))
extras = association_proxy('_extra_assoc', 'targets')
class Ext1(HasExtra, Base):
"""example 1, polymorphic base."""
discriminator = Column(Unicode(20))
data = Column(Unicode)
@declared_attr
def __mapper_args__(cls):
args = {'polymorphic_identity':cls._identifier()}
if cls.__name__ == 'Ext1':
args['polymorphic_on'] = cls.discriminator
return args
class Ext2(Ext1):
id = Column(Integer,ForeignKey('ext1.id'), primary_key=True)
class Ext3(Ext1):
id = Column(Integer,ForeignKey('ext1.id'), primary_key=True)
class D1(HasExtra, Base):
"""example 2, non-polymorphic base."""
data = Column(Unicode)
class D2(HasExtra, Base):
data = Column(Unicode)
e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
s = Session(e)
# 1. persistence
s.add_all([
Ext2(data='ext2_1', extras=[Extra(info='e1'),
Extra(info='e2')]),
Ext3(data='ext3_1'),
Ext1(data='ext1_1',extras=[Extra(info='e3')]),
Ext3(data='ext3_2', extras=[Extra(info='e4'),
Extra(info='e5'),
Extra(info='e6')]),
D1(data='d1_1'),
D2(data='d2_1',extras=[Extra(info='e7')]),
D1(data='d1_2',extras=[Extra(info='e9')]),
])
s.commit()
s.close()
# 2. relationship traversal
d1_2 = s.query(D1).filter_by(data='d1_2').first()
assert d1_2.extras[0].info == 'e9'
assert d1_2.extras[0].owner is d1_2
ext3_2 = s.query(Ext3).\
options(subqueryload('_extra_assoc')).\
filter_by(data='ext3_2').first()
assert [e.info for e in ext3_2.extras] == ['e4', 'e5', 'e6']
# 3. parent deletion
assert s.query(Extra).filter(Extra.info.in_(['e1', 'e2'])).count() == 2
ext2_1 = s.query(Ext2).filter_by(data='ext2_1').first()
s.delete(ext2_1)
s.commit()
assert s.query(Extra).filter(Extra.info.in_(['e1', 'e2'])).count() == 0
# 4. query parent objects in terms of Extra
e5 = s.query(Extra).filter_by(info='e5').one()
# 4a. explicit through the ExtraAssociation
assert s.query(Ext1.data).join('_extra_assoc').filter(ExtraAssociation.targets.contains(e5)).scalar() == 'ext3_2'
# 4b. implicit. Need to fix the association proxy for these, will work
# when [ticket:2054] is fixed.
# assert s.query(Ext1.data).filter(Ext1.extras.contains(e5)).scalar() == 'ext3_2'
# assert s.query(Ext1.data).filter(Ext1.extras.any(Extra.info == 'e5')).scalar() == 'ext3_2'
--
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.
