Michael, I looked at the code and I can not say more than that its very interesting, I have to see how it works and more importantly how It fits into my objects but it seems clear enough to do so. I really appreciate your work on SQLAlchemy and all the time you spend to help us "users" out.
Your solution is definitively one I could not have put together myself. Although I have tried. SQLAlchemy is so powerful that it is hard to find "the right options" for the job. Thank you again! Martijn On Feb 13, 2011, at 21:19 , Michael Bayer wrote: > > 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. >> > > <martijnmoeling.py>-- > 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.
