On Feb 14, 2011, at 2:02 PM, Martijn Moeling wrote:
> Michael,
>
> I have implemented your example into my code and although it sort of works,
> It does not fit into the whole system as expected, somehow I think it is way
> to complex for what I want.
> It should be much more simple....
>
> In the basis I want a class Extra, in this example I'll use queries to
> define/explain the relationships
>
>
> Class User(Base): This class is global and loaded
> Id = 0
> groups = []
>
> Me = User(Id=5, Groups = [1,2,3,6])
>
>
> class Extra(Base):
> __tablename__ = 'Extra'
>
> Id = Column(Integer,
> primary_key=True) # this is only to identify the EXTRA record
>
> Table = Column(Unicode(20))
> TableId = Column(Integer)
> #ForeignKey to self.Table+'.Id' !!!!!!THIS IS MY REAL
> PROBLEM!!!!!!!!!
Yeah that's just not how "foreign keys" work in relational databases. A
foreign key references one and only one table. What you're doing here is a
rails-style polymophic association. You can use the code in
examples/poly_assoc/poly_assoc.py, which doesn't use a "foreign key", but does
the switching in Python code as to which table is joined using the tablename in
the join condition.
The recipe I gave you is nearly the same, it just adds one more table so that
regular referential integrity can be used.
>
> # can @validates help out? I do
> not understand the use very well.
>
> OwnerID = Column(Integer,
> ForeignKey('UsersAndGroup.Id'))
>
> SomeRecord =
> Session.Query('self.Table').filter(str(self.Table.Id)+' ==
> '+str(Self.TableId)).one() (Or this be a backref, seems even better)
>
> Read = Column(Boolean)
> Write = Column(Boolean)
> ....... = Column(what_ever_type)
>
> Class ExtraProperties(object):
>
> @declared_attr
> __table_args__(self):
> return
> (ForeignKeyConstaint(['Id',self.__tablename__],['Extra.TableId','Extra.Table']),{})
>
> def Extras(self, Me)
> should be @declared_attr, # Not sure how to
> handle the reference to Me...... yet
> def Extras(self),
> return relation........
> The Query.....
> return Session.query(Extra).filter(
>
> and_(
>
> Extra.Table == self.__tablename__,
>
> Extra.Id == self.Id,
>
> in_(Extra.OwnerId, Me.groups.append(Me.Id))
>
> , Extra.Read==True).all() # in this case [1,2,3,6,5] ,
> This can be done in the MapperExtension before_append
>
>
>
> def __del__(self)
> session.delete(Extra).filter(
>
> and_(
>
> Extra.Table == self.__tablename__,
>
> Extra.TableId == self.Id)
> # defines the cascade for deletion.... nothing more ,just Every Extra to
> this record
>
>
> Class SomeTable(Base, ExtraProperties)
> __tablename__ = 'SomeTable'
> Id = Column(Integer,
> primary_key=True)
>
>
> I have tried doing this but can not seem to set the relationship right. If
> manage to get the relation not to moan about determing primarykeys, I get
> NULL Identity errors on flush() in the commit....
> The SomeTable could be a polymorphic inheritance or any basic table.
>
> As you might remember I even tried this with MapperExtension in the
> before_append and before_insert etc. but then I run into instance of
> <whatever> is deleted..... amongst others
>
> I might be on the wrong foot but spending 7 days to get such a stupid thing
> ..... It should not be hard.
>
> I could very well make wrapper functions for the Queries, but that does not
> help with Integrity of Extra records.... I want them to be deleted on
> SomeClass deletion...
> I still like the MapperExtrention approach where I can pythonically check the
> Extras.Read etc.
>
> SomeClass being Polymorphic or not does not seem to be relevant.....
>
> Martijn
>
>
>
>
> On Feb 14, 2011, at 14:04 , Martijn Moeling wrote:
>
>> Eric (and Michael),
>>
>> Thank you for your comments, I agree with you totally.
>> I am not much of a database guy and never have been.
>> During my education I did not pay much attention to those
>> lessons either, I was just interested in positive grades.
>>
>> I dropped the database stuff as quickly as possible and concentrated on
>> (OS) kernel and protocol related technologies.
>>
>> The case I am working on now requires a very (To me) complex datamodel with
>> complex relations.
>> Right now I have multiple polymorphic classes with both self reference and
>> references to each other.
>> Those references are polymorphic too!!
>> Now I had to make a class which had to reference all of the above with the
>> right cascading integrity.
>> To be honest this turned out way over my head not in the first place because
>> the SQLAlchemy learning
>> curve gets steeper and steeper when technologies have to be mixed to get
>> stuff working.
>> Me being not really interested in Databases during the first 33 years of my
>> experience in software development
>> is certainly a drawback in terms of understanding the SQLAlchemy
>> documentation.
>> If I do not know what a AssociationProxy is, I do not search for it but what
>> If I need it to get my problem out of the way?
>>
>> Michael has been a great help and I have been looking over this mailing list
>> to help him out answering the "simple" questions.
>> It turned out not that easy and I need a lot more knowledge about the
>> SQLALchemy internals.
>>
>> I have been an Open Source guy for a very long time and did work on many
>> things, I still remember the good old Linux-Kernel days and (although I am
>> the only user) I have made an adapter for running MOD_PYTHON code on top of
>> MOD_WSGI (as an alternative MP package). It is running my production code
>> made for MP on top of MOD_WSGI very well.... I personally like the way of
>> "the Request" object.
>>
>> If I can contribute to SA I will be happy to do so, I have the feeling I
>> have lightened up something which could be improved. That is a first step...
>> (not a big one :-)
>>
>> I have the feeling that SA is mainly a "German" project. Although I get
>> confused by the "working" hours of Michael, He never responded to my
>> question if he ever sleeps.......
>> The German names of people seem to make me think that, I am not sure though.
>>
>> German product turn out be be great and I love Germany for many reasons (I
>> even Worked in Germany). If I cannot afford a German made car, I'll walk!!
>>
>> as I said I am thinking about contributing in some manner to pay back for
>> being able to use SA and for the great help the users get from Michael and
>> others.
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> On Feb 14, 2011, at 05:11 , Eric Ongerth wrote:
>>
>>> Polymorphic associations pop up a lot around here, don't they! I
>>> suppose it's partly because they would be so much more difficult to
>>> handle, or even come close to handling, conveniently, with most other
>>> ORM packages.
>>>
>>> Martijn, after running into the wall on polymorphic associations
>>> approximately once a year since Michael wrote that blog article, I
>>> finally got it all straight in my mind, and I realized there are two
>>> fundamental tricks to seeing the PA phenomenon clearly. One is learn
>>> to sense when it's around; it's almost like a certain smell in your
>>> code. There is a particular feeling that distinguishes it from other
>>> data modeling problems. Two, when you notice a polymorphic
>>> association pattern beginning to appear, attempt to think about the
>>> relations involved in the exact reverse direction. This sounds too
>>> trivial to be a real piece of advice, but it works every time for me.
>>> Each time I perceive a problem with a polymorphic association but then
>>> I reverse my perspective, the perceived problem vanishes.
>>>
>>> I could probably do just as well by binding a rug around my head or
>>> something, but that's just me. And I'm not going to find out.
>>>
>>> - Eric
>>>
>>>
>>> On Feb 13, 1:57 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>>>> a polymorphic association is hard. that's why I have three examples of
>>>> them and soon a fourth. Though they are a subset of a larger batch of
>>>> "tricks" that I've been using in my own work with declarative for the past
>>>> year to automate lots of different kinds of patterns, perhaps there's a
>>>> learning curve but once three or four techniques are mastered they come
>>>> pretty easily.
>>>>
>>>> On Feb 13, 2011, at 4:35 PM, Martijn Moeling wrote:
>>>>
>>>>> 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
>>>>>> athttp://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-s....
>>>>
>>>>>> 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"
>>>>
>>>> ...
>>>>
>>>> read more ยป
>>>
>>> --
>>> 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.
