On Feb 16, 2011, at 6:19 AM, Martijn Moeling wrote:
> Michael,
>
> I have it all working now!!!
>
> What I had to do was to filter out records in the Mapperextension with the
> data in the "Extra" record. Next to that I got a lot of errors since the
> relations where pointing to non-existant records not added in the
> "append_result".
> So I set up a MapperExtension on the relation records to filter out the
> relation records and so the run-time references.
>
> This is perfect for reading.
>
> I still have to test "adding" relations but in that case I "Know" something
> is going to be written and I can skip the file mapper tests. (If I only could
> somehow tell query to pass parameters to the MapperExtension.....)
append_result gets at the QueryContext, which has a dictionary "attributes".
from sqlalchemy.orm.interfaces import MapperOption
class MyOption(MapperOption):
def __init__(self, value):
self.value = value
def process_query(self, query):
query._attributes['x_myargument'] = self.value
s = query(Foo).options(MyOption('myvalue'))
# ...later ....
class MyExtension(...):
def append_result(self, mapper, context, row, instance,
result, **flags):
x_my_argument = context.attributes['x_myargument']
thats the pipeline for that
> I know 0.7 adds MapperExtension functionality in the form of "Events" and
> once 0.7 becomes Alpha/Released I might just change that bit of code.
>
> What by the way will happen if:
>
> a is instance of Class A
> b is instance of Class B(A)
> c is instance of Class C(A)
>
> d is an instance of class D and has a relation which (Remember polymorphic
> self referencing) basically it has:
>
> d.REL = [a,b,c] if no filtering is done.
>
> if filtering is done:
>
> d.REL = [a]
>
> next i do REL.append(e)
>
> commit()
>
> what will be the result of the next unfiltered Query?
>
> d.REL = [a,b,c,e] or
> d.REL = [a,e] (and relation proxy records b and c are removed from the
> database)
>
> If you have interesting comments on this please tell me, if not do not bother
> since I will test that (It is just not so easy to to test this quickly in a
> test program with all the code in the MappersExtension)
>
>
> Although my project has to do with chemistry and this whole effort has been
> made to be able to "define" possible molecules based on which atoms and
> molecules etc. can be combined. I used ACL filesystem like (Access Control
> Lists) to describe what I wanted. I have a scheme now which is perfectly able
> to define "Who" can access "What" no matter if classed (tables) are self
> referencing or not. By setting a parameter the results of the same query are
> "filtered". All this has given me some ideas I have to play with when this
> project is done and I happen to have some spare time.
>
> Anyway again a BIG THANK YOU for all your help
>
> 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 <[email protected]> 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"
>>>>>>> <[email protected]> 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 [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected].
>>> 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 [email protected].
>> To unsubscribe from this group, send email to
>> [email protected].
>> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
