Robin Munn wrote:
> I believe SQLAlchemy supports composite primary keys [...] In
> http://www.sqlalchemy.org/docs/metadata.myt, the second example shows
> a composite primary key, and another table that references it with a
> composite foreign key.
Thanks for clarifying that, it may provide a solution. However, the SA
identity template code in the current TG head (and branch, as far as I
can tell) produces model.py identity statements which are
uncontaminated by composite key declarations. For example ...
group_permission = Table("group_permission", metadata,
Column("group_id", Integer,
ForeignKey("tg_group.group_id"),
primary_key=True),
Column("permission_id", Integer,
ForeignKey("permission.permission_id"),
primary_key=True))
[ ... ]
class Group(ActiveMapper):
"""
An ultra-simple group definition.
"""
class mapping:
__table__="tg_group"
group_id = column(Integer, primary_key=True)
[ ... ]
permissions = many_to_many("Permission", group_permission,
backref="groups")
[...]
class Permission(ActiveMapper):
class mapping:
__table__="permission"
permission_id = column(Integer, primary_key=True)
[ ... ]
groups = many_to_many("Group", group_permission,
backref="permmissions")
(That "permmissions" is clearly a typo, backrefs have to be a column
name, this template won't actually work out of the box.)
ISTM that this is straight many_to_many usage and, unless there's some
sqlalchemical magic of which I am unaware (quite feasible), it provides
the following PostgreSQL statements which specify simple primary_key
attributes for both the group_id and permission_id columns of the
group_permission table:
CREATE TABLE group_permission (
group_id integer NOT NULL,
permission_id integer NOT NULL
);
CREATE TABLE permission (
[ ... ]
permission_id serial NOT NULL,
permission_name character varying(16)
);
CREATE TABLE tg_group (
[ ... ]
group_id serial NOT NULL,
group_name character varying(16)
);
ALTER TABLE ONLY group_permission
ADD CONSTRAINT group_permission_pkey PRIMARY KEY (group_id,
permission_id);
ALTER TABLE ONLY group_permission
ADD CONSTRAINT group_permission_group_id_fkey FOREIGN KEY
(group_id) REFERENCES tg_group(group_id);
ALTER TABLE ONLY group_permission
ADD CONSTRAINT group_permission_permission_id_fkey FOREIGN KEY
(permission_id) REFERENCES permission(permission_id);
When using this schema, attempts to add multiple permissions to a group
cause PostgreSQL to complain of a violation of primary_key uniqueness.
So, it would appear that the composite primary key declarations (to
which you refer) need to be added to the SA identity template in order
to produce correct PostgreSQL statements.
Is this worth posting on trac or is it just too soon to be trying to
use the sa identity provision? (Or have I got hold of the wrong end of
the stick?)
Cheers,
Graham Higgins
============
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears" 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/turbogears
-~----------~----~----~----~------~----~------~--~---
