[sqlalchemy] Postgres INHERITS and concrete table inheritance
Hi,
I'm a little unclear about the relationship between SQLAlchemy
inheritance (in particular with the declarative extension) and the
INHERITS keyword in Postgres.
In Postgres, we have this (simplified for the purposes of his mail):
CREATE TABLE owner (
id integer NOT NULL,
name character varying
);
CREATE TABLE vehicle (
id integer NOT NULL,
owner_id integer,
price integer
);
CREATE TABLE vehicle_car (
id integer DEFAULT NULL,
owner_id integer,
price integer,
fuel_type integer,
)
INHERITS (vehicle);
CREATE TABLE vehicle_bus (
id integer DEFAULT NULL,
owner_id integer,
price integer,
passengers integer,
)
INHERITS (vehicle);
CREATE SEQUENCE vehicle_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE vehicle ALTER COLUMN id SET DEFAULT
nextval('vehicle_id_seq'::regclass);
Here, owner_id is meant to be a foreign key to owner.id, as well
Now, in Postgres, the INHERITS keyword means that:
- I can insert into vehicle directly, in which case the row will
appear in that table only
- I can insert into one of the sub-tables, vehicle_car or vehicle_bus,
in which case the row will appear both vehicle and the sub-table I
inserted into (unless I use FROM ONLY in the query)
I also *think* that repeating id, owner_id and price in the sub-tables
is unnecessary.
I'd like to map this to SQLAlchemy using inheritance, and I think the
correct thing to use is concrete inheritance. Here's what we've tried:
class Owner(Base):
__tablename__ = 'owner'
id = schema.Column(types.Integer(),
primary_key=True,
autoincrement=True)
name = schema.Column(types.String(64), nullable=False)
class Vehicle(Base):
__tablename__ = 'vehicle'
id = schema.Column(types.Integer(),
primary_key=True,
autoincrement=True)
owner_id = schema.Column(types.Integer(),
schema.ForeignKey('owner.id'),
nullable=False)
owner = orm.relation(Owner,
primaryjoin=Owner.id==owner_id, backref=vehicles)
price = schema.Column(types.Integer(), nullable=False)
class VehicleCar(Vehicle):
__tablename__ = 'vehicle_car'
__mapper_args__ = {'concrete':True }
id = schema.Column(types.Integer(),
primary_key=True,
autoincrement=True)
owner_id = schema.Column(types.Integer(),
schema.ForeignKey('owner.id'),
nullable=False)
owner = orm.relation(Owner,
primaryjoin=Owner.id==owner_id, backref=vehicles)
price = schema.Column(types.Integer(), nullable=False)
fuel_type = schema.Column(types.Integer())
class VehicleBus(Vehicle):
__tablename__ = 'vehicle_bus'
__mapper_args__ = {'concrete':True }
id = schema.Column(types.Integer(),
primary_key=True,
autoincrement=True)
owner_id = schema.Column(types.Integer(),
schema.ForeignKey('owner.id'),
nullable=False)
owner = orm.relation(Owner,
primaryjoin=Owner.id==owner_id, backref=vehicles)
price = schema.Column(types.Integer(), nullable=False)
fuel_type = schema.Column(types.Integer())
This kind of works, but there are a few problems:
- The 'owners' variable on the Game type only contains Vehicle
objects. I'd like it to contain the correct sub-class if possible.
- I've had to repeat all the fields from the base class in the
sub-classes. Otherwise, I'd get errors using those attributes, even
though VehicleCar and VehicleBus both inherits form Vehicle.
- Setting a 'backref' on the relation() on VehicleCar and VehicleBus
results in an error (the Owner object already has an 'owners' field)
I feel like I may've missed something here, though. Any suggestions on
how to do this better?
Cheers,
Martin
--
Author of `Professional Plone Development`, a book for developers who
want to work with Plone. See http://martinaspeli.net/plone-book
--
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.
Re: [sqlalchemy] Postgres INHERITS and concrete table inheritance
Martin Aspeli wrote: Hi, This kind of works, but there are a few problems: - The 'owners' variable on the Game type only contains Vehicle objects. I'd like it to contain the correct sub-class if possible. When a row is received SQLAlchemy would need to know what type that row is, in order to dispatch to the correct class. SQLA currently uses a discriminator column for that purpose, so you'd have to find some way to have a column in the result set (or an expression) which can be used in this way. - I've had to repeat all the fields from the base class in the sub-classes. Otherwise, I'd get errors using those attributes, even though VehicleCar and VehicleBus both inherits form Vehicle. Well SQLA doesn't have any direct support for PG INHERITS, and the fact is that concrete inherits means that each Table repeats each common column specifically - one reason why concrete inheritance is widely considered to be the most cumbersome form of relational inheritance. There was a trac ticket requesting that the columns inherit the way they do with a simpler single- or joined- table setup, but at the end of the day that request was asking for some very complex magic to occur. Your database expresses distinct columns at the public DDL level, even though INHERITS means theyre the same, so SQLA keeps it simple and would like you to express them in the same way as what it will see when talking to the DB. - Setting a 'backref' on the relation() on VehicleCar and VehicleBus results in an error (the Owner object already has an 'owners' field) there is documentation on how to address concrete backrefs, using the back_populates keyword: http://www.sqlalchemy.org/docs/05/mappers.html#using-relations-with-inheritance I feel like I may've missed something here, though. Any suggestions on how to do this better? unfortunately we haven't attempted to smoothly integrate with PG's INHERITS. It may or may not require additional complexity and would provide a feature that would not work on any of the other half dozen databases we support. My understanding is that INHERITS is usually used in practice to provide transparent sharding of table data and not necessarily to express class hierarchies, but this is strictly anecdotal knowledge.I'm actually encouraged that you've gotten it to work somewhat reasonably. -- 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.
Re: [sqlalchemy] Postgres INHERITS and concrete table inheritance
On Dec 15, 2009, at 17:59 , Michael Bayer wrote: My understanding is that INHERITS is usually used in practice to provide transparent sharding of table data and not necessarily to express class hierarchies, but this is strictly anecdotal knowledge. Agreed. The PostgreSQL documentation[1] mentions many caveats with INHERITS, most notably: A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint. For those reasons it is also actively *discouraged* for expressing class hierarchies. To put that into context, consider the following: CREATE TABLE vehicle ( id integer NOT NULL, owner_id integer, price integer, primary key (id) -- I added this ); CREATE TABLE vehicle_bus ( passengers integer ) INHERITS (vehicle); CREATE TABLE vehicle_whatever( id integer primary key references vehicle(id), whatever text ); INSERT INTO vehicle_bus VALUES (1, 1, 42, 123); SELECT * FROM vehicle; id | owner_id | price +--+--- 1 |1 |42 (1 row) SELECT * FROM vehicle_bus; id | owner_id | price | passengers +--+---+ 1 |1 |42 |123 (1 row) INSERT INTO vehicle_whatever VALUES (1, 'but clearly this is in vehicle, no?'); ERROR: insert or update on table vehicle_whatever violates foreign key constraint vehicle_whatever_id_fkey DETAIL: Key (id)=(1) is not present in table vehicle. ~ [1] http://www.postgresql.org/docs/8.4/static/ddl-inherit.html -- Alex Brasetvik -- 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.
