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 in http://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. 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 at http://groups.google.com/group/sqlalchemy?hl=en.
