Oh, whoops. Also I figured out why my solution of deleting, making transient, then re-adding appeared not to work. It actually did work, I just had some dead rows in the parent table that needed to be weeded out. Now I can stop with this refactoring / migration madness and get back to developing! Meanwhile the alternative solution in sa's SQL Expression language turned out to be far simpler too, required 5 lines of code instead of ~20.
Thanks again! On Jan 2, 11:32 pm, Eric Ongerth <ericonge...@gmail.com> wrote: > Right, you made that clear before. > > I was no longer talking about setting the discriminator column here in > 0.6.5. I was talking about deleting, making transient, and then re- > adding all of the objects in question. And how this worked on some of > them but not all. > > And your reasons for not bothering with multiple inheritance in sa are > great, thanks for describing them. > > On Jan 2, 11:09 pm, Michael Bayer <mike...@zzzcomputing.com> wrote: > > > 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 > > ... > > 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.
