headers
Eric Ongerth | 2 Jan 2011 03:52
Picon

change of discriminator column apparently not saved by session?

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.
(Continue reading)

Permalink | Reply | 9 comments |
headers
Michael Bayer | 2 Jan 2011 16:59
Gravatar

Re: change of discriminator column apparently not saved by session?

Up until 0.7, the discriminator column value is set within the flush process as a direct mapping of the
object's type straight into the parameters passed to the INSERT, and can't be affected otherwise.  0.7
simplifies this behavior so that the discriminator is auto-populated at the time of __init__ for the
object, then it's a regular attribute going forward.

Curious here is what kind of scheme you have that requires a manual setting of the discriminator.

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,
(Continue reading)

Permalink | Reply | 8 comments |
headers
Eric Ongerth | 3 Jan 2011 05:25
Picon

Re: change of discriminator column apparently not saved by session?

On Jan 2, 7:59 am, Michael Bayer <mike... <at> 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
(Continue reading)

Permalink | Reply | 7 comments |
headers
Michael Bayer | 3 Jan 2011 06:18
Gravatar

Re: Re: change of discriminator column apparently not saved by session?


On Jan 2, 2011, at 11:25 PM, Eric Ongerth wrote:

> On Jan 2, 7:59 am, Michael Bayer <mike... <at> 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.
(Continue reading)

Permalink | Reply | 6 comments |
headers
Eric Ongerth | 3 Jan 2011 06:55
Picon

Re: change of discriminator column apparently not saved by session?

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
(Continue reading)

Permalink | Reply | 5 comments |
headers
Eric Ongerth | 3 Jan 2011 08:02
Picon

Re: change of discriminator column apparently not saved by session?

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... <at> 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
(Continue reading)

Permalink | Reply | 3 comments |
headers
Michael Bayer | 3 Jan 2011 08:07
Gravatar

Re: Re: change of discriminator column apparently not saved by session?


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... <at> zzzcomputing.com> wrote:
>> On Jan 2, 2011, at 11:25 PM, Eric Ongerth wrote:
>> 
>> 
>>
(Continue reading)

Permalink | Reply | 0 comments |
headers
Michael Bayer | 3 Jan 2011 08:09
Gravatar

Re: Re: change of discriminator column apparently not saved by session?


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... <at> gmail.com> wrote:
>> Ah!  I did it again.  You may or may not remember I asked you a
(Continue reading)

Permalink | Reply | 2 comments |
headers
Eric Ongerth | 3 Jan 2011 08:32
Picon

Re: change of discriminator column apparently not saved by session?

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... <at> 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.
>
(Continue reading)

Permalink | Reply | 1 comment |
headers
Eric Ongerth | 3 Jan 2011 09:04
Picon

Re: change of discriminator column apparently not saved by session?

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... <at> 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... <at> 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
(Continue reading)

Permalink | Reply | 0 comments |

Gmane