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