Multiple Inheritance

[Sam Magister]

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)),
)

And the classes would look like

class Employee(object):
pass

class Citizen(object):
pass

class Engineer(Employe, Citizen):
pass

And the mappers for the base classes would be something like:
mapper(Employee, employees, polymorphic_on=employees.c.employee_type,
polymorphic_identity='employee')
mapper(Citizen, citizens, polymorphic_on=citizens.c.citizen_type,
polymorphic_identity='citizen')

While the mapper for the engineer has argument 'inherits_multi' and
looks like:
mapper(Engineer, engineers, inherits_multi=[Employee, Citizen],
polymorphic_identity='engineer')

I realize that the mapper does not have an 'inherits_muti' argument
and that multiple inheritance is not supported. I also saw this thread
from back in 2006:
http://www.mail-archive.com/sqlalche...@lists.sourceforge.net/msg03303.html

For my application, this pattern is important (the above example is
only an example of the pattern, I'm not really modeling employees and
citizens) and I was wondering if anyone had any suggestions as to how
to go about implementing this functionality, which I'm planning on
doing.

Also, thanks to the folks on this list who have responded to my
previous questions. I'm hoping that someone else would find this
functionality useful and I can share my (yet to be had) solution with
the community.

Sam

[Michael Bayer]

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.

Also, a key aspect of SQLA's polymorphic loading capability is that a
mapper is aware of all of its possible subtypes. If multiple
inheritance is part of that, the geometry of "what are all my
subtypes?" becomes a more chaotic. We'd have to join to every table
in the whole hierarchy to identify the type. To be fair I think this
is a behavior that Hibernate supports but they only support it for
single inheritance (and they also boast of how difficult it was to
implement). SQLA's usual notion of "primary key" with respect to
joined table inheritance wouldn't work here either (engineer's PK is
either (x, y) or (x, y, z), employee and citizen are just (x)),
suggesting again a more "concrete" notion - you need to select from
the subclass table in order to locate the object, and the primary key
itself does not provide enough information to select the appropriate
subclass table.

The standard patterns for "multiple" inheritance in SQL are listed at http://www.agiledata.org/essays/mappingObjects.html#MappingMultipleInheritance
. There you'll find examples of concrete, single, and joined table
"multiple" inheritance.

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.

> For my application, this pattern is important (the above example is
> only an example of the pattern, I'm not really modeling employees and
> citizens) and I was wondering if anyone had any suggestions as to how
> to go about implementing this functionality, which I'm planning on
> doing.

if you mean "implementing" within SQLAlchemy itself such that its core
notion of inheritance is modified to support multiple base classes
spread across multiple tables, this would be an enormously difficult
feature to implement. For polymorphic loading, at the very least
SQLA would need to lose its dependency on "discriminator" columns and
learn to just look for row presence in a table as evidence of
belonging to a certain type (that alone is not necessarily a bad
thing, as Hibernate does this too).

It would also need to learn to create joins to other tables
corresponding to horizontal and vertical relationships, and be able to
guess the type of a row based on a complicated equation of row
presence. All of the ambigousness introduced by multiple inheritance,
like diamond patterns and such would also have to be dealt with. So
I'm not really sure that even with the best of efforts, multiple
inheritance could ever be nearly as transparent as single
inheritance. Beyond the effort level to implement, I'd be very
concerned about the complexity it would introduce to SQLA's
internals. The use case itself seems exceedingly rare. While a
recipe that "gets the job done" is entirely fine in this case, I'm
fairly skeptical of functionality like this as a core feature.

[a...@svilendobrev.com]

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

[Michael Bayer]

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.

[Sam Magister]

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

[Sam Magister]

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

[Michael Bayer]

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

[Michael Bayer]

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

[Sam Magister]

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,
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 an

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)),
)

And the classes look like:

class Employee(object):
pass

class Citizen(object):
pass

class Engineer(Employe, Citizen):
pass

This is the mapper extension for setting the discriminators:

class EngineerExtension(MapperExtension):

def __init__(self):
MapperExtension.__init__(self)

def before_insert(self, mapper, connection, instance):
instance.employee_type = 'engineer'
instance.citizen_type = 'engineer'

And the mappers:

mapper(Engineer, engineers.join(employees).join(citizens),
extension=EngineerExtension())
mapper(Employee, employees)
mapper(Citizen, citizens)

Any comments on this setup are welcome!

Sam

[a...@svilendobrev.com]

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

[Michael Bayer]

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.

[Sam Magister]

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.

[Sam Magister]

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.