gmane.comp.python.sqlalchemy.user

Sam Magister | 4 Sep 02:46

Multiple Inheritance


I was wondering if it is possible to set up joined table inheritance
so that a subclass inherits from more than one base table. To extend
the example given in the documentation, we would have a base class
'Employee' and a base class 'Citizen' such that an 'Engineer' would
inherit from both Employee and Citizen classes and have independent
'citizen_id' and 'employee_id'. One could imagine other classes that
only inherit from either employee or citizen.

employees = Table('employees', metadata,
   Column('employee_id', Integer, primary_key=True),
   Column('employee_type', String(30), nullable=False)
)

citizens = Table('citizens', metadata,
   Column('citizen_id', Integer, primary_key=True),
   Column('citizen_type', String(30), nullable=False)
)

An engineer who is both an employee and a citizen would have am
employee_id and a citizen_id:

engineers = Table('engineers', metadata,
   Column('id', Integer, primary_key=True)
   Column('employee_id', Integer,
ForeignKey('employees.employee_id')),
   Column('citizen_id', Integer, ForeignKey('citizens.citizen_id')),
   Column('engineer_info', String(50)),
)

(Continue reading)

headers

Michael Bayer | 4 Sep 05:25

Re: Multiple Inheritance


On Sep 3, 2008, at 8:47 PM, Sam Magister wrote:

>
> I was wondering if it is possible to set up joined table inheritance
> so that a subclass inherits from more than one base table. To extend
> the example given in the documentation, we would have a base class
> 'Employee' and a base class 'Citizen' such that an 'Engineer' would
> inherit from both Employee and Citizen classes and have independent
> 'citizen_id' and 'employee_id'. One could imagine other classes that
> only inherit from either employee or citizen.
>
> employees = Table('employees', metadata,
>   Column('employee_id', Integer, primary_key=True),
>   Column('employee_type', String(30), nullable=False)
> )
>
> citizens = Table('citizens', metadata,
>   Column('citizen_id', Integer, primary_key=True),
>   Column('citizen_type', String(30), nullable=False)
> )
>
> An engineer who is both an employee and a citizen would have am
> employee_id and a citizen_id:
>
> engineers = Table('engineers', metadata,
>   Column('id', Integer, primary_key=True)
>   Column('employee_id', Integer,
> ForeignKey('employees.employee_id')),
>   Column('citizen_id', Integer, ForeignKey('citizens.citizen_id')),

(Continue reading)

headers

az | 4 Sep 08:51

Re: Multiple Inheritance


i went for polymorphic asociation on my multiple inheritances / 
multiple aspects. it gives even more freedom than what strict 
inheritance needs.

the examples around ruby-on-rails are for one2many/many2one: 
http://wiki.rubyonrails.org/rails/pages/UnderstandingPolymorphicAssociations
sqlalchemy/examples/poly_assoc/

i have a many2many version, 
http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/usage/polymassoc.py

the idea: the assoc.table points to the value and to all the possible 
owners (via foreign keys), with only one of the owners set-up for 
each record. this can probably be extended to point to multiple types 
of values too.

ciao
svil

On Thursday 04 September 2008 06:25:53 Michael Bayer wrote:
> On Sep 3, 2008, at 8:47 PM, Sam Magister wrote:
> > I was wondering if it is possible to set up joined table
> > inheritance so that a subclass inherits from more than one base
> > table. To extend the example given in the documentation, we would
> > have a base class 'Employee' and a base class 'Citizen' such that
> > an 'Engineer' would inherit from both Employee and Citizen
> > classes and have independent 'citizen_id' and 'employee_id'. One
> > could imagine other classes that only inherit from either
> > employee or citizen.

(Continue reading)

headers

Michael Bayer | 4 Sep 14:30

Re: Multiple Inheritance

On Sep 3, 2008, at 11:25 PM, Michael Bayer wrote:

>
> This pattern doesnt entirely make sense - the "citizen_type" and
> "employee_type" columns seem superfluous and redundant against each
> other, since we really can't load Engineer rows without querying all
> three tables.  In that sense it takes on all the limitations of
> concrete table inheritance, which doesnt use a "type" column at the
> table level.
>

after a night's sleep, let me backtrack a bit.    having  
discriminiator columns in all superclass tables would probably still  
be effective in the way we use discriminiator columns right now.   the  
mapper would basically have to use one or the other in the case where  
more than one is available (like, querying subclasses of Engineer).

You would want to share the primary key column across all three tables  
though (i.e. foreign key in the subclass table, like the link I  
mentioned) so that the primary key takes on the same form no matter  
what class you're looking at - that helps inheritance a great deal  
since its one of the assumptions SQLA makes.

The change to SA's internals would still be pretty heavy and its hard  
to say what kinds of roadblocks would appear when developing such a  
feature.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

(Continue reading)

headers

Sam Magister | 4 Sep 20:01

Re: Multiple Inheritance


Michael,

Thanks for the thoughtful replies. I'm going to explore the options
you raised here. I'll post back with any insights I come to.

Best,

Sam
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---

headers

Michael Bayer | 4 Sep 20:12

Re: Multiple Inheritance


sorry for the rant.  my second response is closer to the mark.

On Sep 4, 2008, at 2:01 PM, Sam Magister wrote:

>
> Michael,
>
> Thanks for the thoughtful replies. I'm going to explore the options
> you raised here. I'll post back with any insights I come to.
>
> Best,
>
> Sam
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---

headers

Sam Magister | 4 Sep 20:12

Re: Multiple Inheritance


On Sep 3, 8:25 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:

> You can certainly map to any of the hierarchies indicated in that  
> article, but you wouldn't be able to take advantage of SQLA's  
> "polymorphic" capabilities, which are designed to only handle single  
> inheritance.   You'd really want to make your Engineer(Employee,  
> Citizen) class and just map it to  
> engineers.join(citizens).join(employees).   That would get your schema  
> going, just without SQLA having any awareness of the "inheritance"  
> portion of it, and is extremely similar to a plain concrete setup,  
> which is pretty much all you'd get anyway without the ability to load  
> polymorphically.
>

Michael, what would the mapper function look like if it were to map
Engineer(Employee, Citizen) to
engineers.join(citizens).join(employees). What argument of the mapper
would that join condition be in? I think concrete inheritance might be
the way to go about things, at the cost of the nice polymorphic
loading features.

Thanks,

Sam
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en

(Continue reading)

headers

Michael Bayer | 4 Sep 20:15

Re: Multiple Inheritance

On Sep 4, 2008, at 2:12 PM, Sam Magister wrote:

> Michael, what would the mapper function look like if it were to map
> Engineer(Employee, Citizen) to
> engineers.join(citizens).join(employees). What argument of the mapper
> would that join condition be in? I think concrete inheritance might be
> the way to go about things, at the cost of the nice polymorphic
> loading features.

that would just be the ordinary "table" argument.   The join  
conditions are within the join() calls themselves.  mapper(Engineer,  
engineers.join(citizens,...).join(employees, ...)) .   I dont think  
you can even say "concrete=True" here unless there were an "inherits"  
argument, in which case you'd have to just pick a superclass out of  
the two.... it would be better to not use the inherits argument at all  
though (pretty sure SQLA won't complain).

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---

headers

Sam Magister | 5 Sep 20:56

Re: Multiple Inheritance


I've come up with an implementation based on concrete table
inheritance, without touching any SQLA internals like Michael
suggested. I've also added a discriminator to both base classes,
'citizen_type' and 'employee_type'. The discriminator is set via the
before_insert() method of a MapperExtension which extends the Engineer
mapper.

I realize that going this way forfeits the advantages of the
polymorphic querying, but that's ok for my application. I can still
get the same resuts, it just takes a few subqueries after I query one
of the base classes.

I've run a few tests with this setup and it seems to work - the crux
is in the engineers.join(employees).join(citizens) table definition in
the Engineer mapper and the ForeignKey constraints.

I just wanted to put this out there to see if anyone notices any
issues I am not anticipating or if this model might be helpful to
someone in the future who wants to inherit from two independent base
classes. I realize that the Engineers inheriting from Employees and
Citizens example is a bit forced, sorry about that. This is not my
actual application, I just wanted to give a simple example. Perhaps a
better application would be having 'Dragon' inheriting from both
'Bird' and 'Lizard', as given in the Agile Data essay linked to
earlier.

Here's the setup:

employees = Table('employees', metadata,

(Continue reading)

headers

az | 5 Sep 23:14

Re: Multiple Inheritance

just a theoretical comment... so instead of relying on SA mapper logic 
to automaticaly put the discriminator/s, u are doing it on a level 
above, in a way - as SA does not know about the (multiple) 
inheritance that those imply. can be useful as an approach for 
other 'beyond SA' things... 
will it work for multilevel inheritance? 
btw i would not call it concrete-table inh, as Engineer has only 
foreignkeys to other tables (like joined-table) and not their data 
columns. but it isnt joined-table either. inbetween...

On Friday 05 September 2008 21:56:28 Sam Magister wrote:
> I've come up with an implementation based on concrete table
> inheritance, without touching any SQLA internals like Michael
> suggested. I've also added a discriminator to both base classes,
> 'citizen_type' and 'employee_type'. The discriminator is set via
> the before_insert() method of a MapperExtension which extends the
> Engineer mapper.
>
> I realize that going this way forfeits the advantages of the
> polymorphic querying, but that's ok for my application. I can still
> get the same resuts, it just takes a few subqueries after I query
> one of the base classes.
>
> I've run a few tests with this setup and it seems to work - the
> crux is in the engineers.join(employees).join(citizens) table
> definition in the Engineer mapper and the ForeignKey constraints.
>
> I just wanted to put this out there to see if anyone notices any
> issues I am not anticipating or if this model might be helpful to

(Continue reading)

headers

Sam Magister | 6 Sep 01:49

Re: Multiple Inheritance


On Sep 5, 2:14 pm, a...@svilendobrev.com wrote:
> just a theoretical comment... so instead of relying on SA mapper logic
> to automaticaly put the discriminator/s, u are doing it on a level
> above, in a way - as SA does not know about the (multiple)
> inheritance that those imply. can be useful as an approach for
> other 'beyond SA' things...
That's right, SQLA is not handling the inheritance directly. In some
sense this is nice because there is nothing going on 'under the
hood' (at least nothing related to the inheritance structure).

> will it work for multilevel inheritance?
I don't think so. This issue did come up but we don't use it in our
application so that's ok. I'm not sure how the table joins would work
and how the discriminator setting would work.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---

headers

Michael Bayer | 5 Sep 23:27

Re: Multiple Inheritance


this looks pretty impressive !  I was poking around today to see how  
hard it would be to make "inherits" a list.     It seems like some  
things would be straightforward, others not.   Its interesting and I'm  
still thinking about it.   But its good you could make it work without  
getting into all of that.

On Sep 5, 2008, at 2:56 PM, Sam Magister wrote:

>
> I've come up with an implementation based on concrete table
> inheritance, without touching any SQLA internals like Michael
> suggested. I've also added a discriminator to both base classes,
> 'citizen_type' and 'employee_type'. The discriminator is set via the
> before_insert() method of a MapperExtension which extends the Engineer
> mapper.
>
> I realize that going this way forfeits the advantages of the
> polymorphic querying, but that's ok for my application. I can still
> get the same resuts, it just takes a few subqueries after I query one
> of the base classes.
>
> I've run a few tests with this setup and it seems to work - the crux
> is in the engineers.join(employees).join(citizens) table definition in
> the Engineer mapper and the ForeignKey constraints.
>
> I just wanted to put this out there to see if anyone notices any
> issues I am not anticipating or if this model might be helpful to
> someone in the future who wants to inherit from two independent base
> classes. I realize that the Engineers inheriting from Employees and

(Continue reading)

headers

Sam Magister | 6 Sep 01:51

Re: Multiple Inheritance


On Sep 5, 2:27 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> this looks pretty impressive !  I was poking around today to see how  
> hard it would be to make "inherits" a list.     It seems like some  
> things would be straightforward, others not.   Its interesting and I'm  
> still thinking about it.   But its good you could make it work without  
> getting into all of that.

Thanks Michael, and thanks for your help. It was your suggestion to do
chained joins that was the key.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---