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')),
> Column('engineer_info', String(50)),
> )
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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
