On Jan 3, 2011, at 12:55 AM, Eric Ongerth wrote: > > So now I'm curious, have you given much thought to advancing the > possibility of supporting multiple inheritance with SA's explicit > inheritance features? Or is that just too dangerous (or just too much > of a pain to work out). Or too tempting for people to brew up all > manner of ridiculous and unnecessary schema with it.
No because its not a pattern I've ever needed and without having spent a lot of time using it myself, I can't do justice to an adequate implementation of such. It's dangerous for me to add big new features without having worked through a real world application of them first (as evidenced by all the time I've had to spend walking back poorly considered features over the years, which continues in 0.7). Additionally, with joined inheritance, the intrinsic issue is that right off, you're joining quite a lot, and even simple queries between two related classes very quickly start producing joins that are nested such that queries start losing their scalability very quickly (or on MySQL, scalability is gone the moment you join anything). So doing that in a "multiple" way seems like it would almost immediately crank out queries that are too spread out and nested to be of general use. > > Thank you for your thoughts. > > > On Jan 2, 9:18 pm, Michael Bayer <mike...@zzzcomputing.com> wrote: >> On Jan 2, 2011, at 11:25 PM, Eric Ongerth wrote: >> >> >> >>> On Jan 2, 7:59 am, Michael Bayer <mike...@zzzcomputing.com> wrote: >>>> Curious here is what kind of scheme you have that requires a manual >>>> setting of the discriminator. >> >>> Forgive me, I tried to indicate that it doesn't, this is just a one- >>> time thing I have to do to get this database fixed up. Here's the >>> whole story in a nutshell. I had six tables (person, company, >>> product, item, etc.) that had little to do with each other. >>> Eventually I decided that the objects symbolized by the rows in those >>> tables had a cross-cutting concern: I want to be able to attach action >>> tickets (sort of like in Trac) and notes to them. The project I'm >>> working on has a certain limited amount of CRM/ERP type stuff as part >>> of its scope, thus tickets, notes, and so on. >> >>> So where these tables originally each had their own unique primary key >>> "id" columns, I realized that in order to be able to affix notes >>> equally to a person, a company, a product, etc., I wanted to have all >>> of these disparate tables pull their IDs from a common table holding a >>> single column, a column of "trackable object IDs". Sort of like the >>> POID concept used for certain concerns >>> inhttp://www.agiledata.org/essays/mappingObjects.html. >> >>> Seemed to me a simple way to make that work, without having to build a >>> bunch of ungainly trigger machinery, is make all of these tables >>> inherit from that common ID table; thus a joined table inheritance >>> setup. I successfully scripted all of the changes necessary so that >>> instead of their old ID columns, each table's ID column now is still >>> its primary key but is also a foreign key to the common ID table. >> >> oh. OK no, I wouldn't use "joined inheritance" for a "cross-cutting" >> concern. This is the polymorphic association pattern, which from a database >> table perspective, looks the same in fact. But the "cross cutting" concern >> is modeled at the ORM level via relationship. The blog post is extremely >> old at this point but it should get the idea across: >> http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-s... >> >> Also if the question about "using the ORM or not" refers to just the >> activity of migrating the data to the new structure, I'd possibly skip the >> ORM for a migration, since its a single operation that has to work only >> once. It sort of depends on what seems more straightforward and also the >> number of rows in play (if it were millions, the migration might need to be >> composed entirely as SQL statements without any need to load into memory, >> for example). >> >> >> >>> At that point I had sort of "Frankensteined" a joined-table >>> inheritance situation... I had managed to do all of the constraint >>> dropping and adding that was needed, I managed to keep all of the rows >>> in most of the tables (only had to rebuild some of them), and >>> everything was peachy. Now a given ID of any row within any of these >>> six tables, is unique over all six of the tables. Thus notes, >>> tickets, whatever other cross-cutting deals, can be attached to such >>> unique IDs or groups thereof. >> >>> The only remaining problem was that the parent table actually needs >>> more than just the ID column. It needs the discriminator column if >>> I'm going to really handle this as joined-table inheritance. >>> Everything else is in place except the discriminator column is not set >>> for any of my existing rows that made it through this refactoring. >>> The new setup works perfectly for newly added people, companies, >>> items, products, etc... the ID is assigned and inherited, the >>> discriminator is assigned. (Of course.) But I need a way to get in >>> there and set the discriminator column for my _existing_ rows that >>> came through from before. >> >>> I suppose I could just go with Concrete Table inheritance, but the >>> added difficulties with relationships are less enticing. Intuitively >>> the joined table idea seems to fit. >> >>> Should I just take a deep breath and get this done without the ORM? I >>> appreciate your answer regarding pre-0.7 vs. 0.7 approaches, that's >>> the sort of stuff I was looking for when I went ahead and asked this >>> question rather than quietly working around it. >> >>> Your answer also just jogged my brain in a way that I failed to do >>> last night. I could just delete and re-insert each of these pre- >>> existing rows that I need to fix up... now that's blindingly obvious. >>> I welcome any commentary, though, on the sanity or insanity of my >>> above paragraphs. Sqlalchemy rocks... >> >>> Thanks! >>> Eric >> >>>> On Jan 1, 2011, at 9:52 PM, Eric Ongerth wrote: >> >>>>> I must be doing something wrong but can't find it. >> >>>>> I'm doing some database refactoring and found a situation where I had >>>>> to set up a joined-table inheritance structure after the fact, with >>>>> some of the tables already populated. I created (successfully) a >>>>> script that got all of the primary and foreign keys right on all the >>>>> child tables and the parent table, and now I just need to correctly >>>>> populate the discriminator column on the parent table. >> >>>>> Each row of the parent table is referenced by exactly one row from one >>>>> of the six child tables. So i'm iterating through all child objects >>>>> (yes, doing this via the sa ORM) and setting the value of the >>>>> discriminator appropriately. Then I commit the Session. Afterward, I >>>>> go and look at the parent table directly using pgAdmin and I see that >>>>> the new values in the discriminator column were not saved. That's >>>>> strange because I didn't get an error either, nor am I doing anything >>>>> that would mask an error. >> >>>>> So I thought maybe the discriminator column on the parent table in a >>>>> joined-table inheritance scenario is just not watched by the Session / >>>>> UOW. I thought maybe it assumes that this column would only, >>>>> normally, be set during object instantiation and would typically not >>>>> change for the lifetime of the object and its associated database >>>>> rows. So I tried manually dirtying the object using >>>>> instance_state(obj).modified=True before committing the Session. >>>>> Still no success, the new values don't get saved. >> >>>>> Is going outside of the ORM the only way to get this done? I don't >>>>> mind doing so, but I'm just trying to understand better why my initial/ >>>>> intuitive approach didn't do the job. >> >>>>> Perhaps code / testcase will be requested and if I really can't figure >>>>> this out I'll produce them, but it's really a pain in this case >>>>> because it's a transient situation as part of a refactoring... I'm not >>>>> looking forward to trying to create this once-only situation where I >>>>> created the whole of a joined-table inheritance setup without telling >>>>> sqlalchemy about it, then table by table altered the keys and mappers >>>>> until everything was in place. Normally I wouldn't operate like >>>>> this. Poor excuse, perhaps, but I'll stand by it for today. >> >>>>> -- >>>>> You received this message because you are subscribed to the Google Groups >>>>> "sqlalchemy" group. >>>>> To post to this group, send email to sqlalch...@googlegroups.com. >>>>> To unsubscribe from this group, send email to >>>>> sqlalchemy+unsubscr...@googlegroups.com. >>>>> For more options, visit this group >>>>> athttp://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 sqlalch...@googlegroups.com. >>> To unsubscribe from this group, send email to >>> sqlalchemy+unsubscr...@googlegroups.com. >>> For more options, visit this group >>> athttp://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 sqlalch...@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 sqlalch...@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.
