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.
