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.....) 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.
