On Jan 3, 2011, at 2:02 AM, Eric Ongerth wrote: > So I tried my solution of deleting and then re-adding each object > (row) in question. Didn't work quite like that; instead I had to > delete, make_transient(), and then re-add. Still didn't quite work; > for rows that had sa relationships via FKs to other tables, in order > to avoid errors I had to give the objects thus related a similar > treatment, calling make_transient() and then re-associating them. > > Lo and behold, for tables that fit that description it worked and the > discriminator is set properly now. But for the rest of the tables, > those which did not have relationships that needed taking care of in > that way to avoid errors, their rows still aren't getting the > discriminator set. I tried using "instance_state(obj).modified = > True" on them before flush, but that didn't help either. Still > haven't found a solution for the rest of those. > > I don't mean to annoy you in persisting with this approach when it's > not to your liking. It's just that at this point I'm trying to work > it all the way through in this manner just to come to a better > understanding of the ORM's workings.
You can't set the discriminator column in 0.6 with the ORM. The mapper will ignore it. You need to forego the ORM or use 0.7. > > > On Jan 2, 9:55 pm, Eric Ongerth <ericonge...@gmail.com> wrote: >> Ah! I did it again. You may or may not remember I asked you a >> different question yielding the same answer a couple of months ago! >> I've got to put a stop to that trend. >> >> To quote from your blog post: >> >> === >> In fact, while this table scheme is exactly joined table inheritance, >> and we could certainly go the "straight" route of creating an >> Addressable base class and mapper from which the User and Order >> classes/mappers derive, then creating the traditional SA "polymorphic >> mapping" using UNION ALL (or whatever surprise 0.4 has in store) with >> a relationship to Address, here we're going to do it differently. >> Namely, because we are still going to look at this association as a >> "cross-cutting concern" rather than an "is-a" relationship, and also >> because SA's explicit inheritance features only support single >> inheritance, and we'd rather not occupy the "inherits" slot with a >> relationship that is at best a mixin, not an "is-a". >> === >> >> I actually started modeling this exactly the way you're suggesting in >> that post. I added a "trackable_id" column to each of the six tables, >> as a foreign key to a table of association objects. But then I >> reasoned as follows: (1) great, now I have two unique ID columns in >> each of these tables and that's almost never a good thing. (2) great, >> now I'm going to have to add more program logic to explicitly create >> an association object every time I add a row to any of these tables. >> Clunky. >> >> Your blog post shows how to fix problem (2) there with some very cool >> python magic. Ok, it's not even magic, it's clear enough. But as >> I've noticed before, I still haven't absorbed your level of comfort >> with throwing that stuff around. I understand it well enough, but >> when I see underscores I start thinking "can't this be done another >> way without wizardry"? >> >> I suppose even that point is moot when I recognize that my solution >> using inheritance actually calls upon a lot of very clever programming >> within the guts of sqlalchemy. >> >> Your most salient point of all, for me, is where you note that that >> there's currently only one "slot" for inheritance and there isn't a >> very strong case for occupying it with a cross-cutting concern that is >> "at best a mix-in, not an 'is-a'. " >> >> 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. >> >> 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 >> >> ... >> >> read more ยป > > -- > 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.
