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!!!!!!!!!
# 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.
