On 8/6/06, Graham Higgins <[EMAIL PROTECTED]> wrote:
>
> 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))
That looks right to me; when SQLAlchemy sees multiple columns with the
primary_key attribute set to True, it automagically collects them into
a single, composite primary key.
I just checked this by running the following code:
from sqlalchemy import *
db = create_engine('postgres://[EMAIL PROTECTED]/rmunn', echo=True)
metadata = BoundMetaData(db)
user = Table('tg_user', metadata,
Column('user_id', Integer, primary_key=True),
Column('user_name', Unicode(16), unique=True),
)
group = Table('tg_group', metadata,
Column('group_id', Integer, primary_key=True),
Column('group_name', Unicode(16), unique=True),
)
user_group = Table('user_group', metadata,
Column('user_id', Integer, ForeignKey("tg_user.user_id"), primary_key=True),
Column('group_id', Integer, ForeignKey("tg_group.group_id"),
primary_key=True),
)
metadata.create_all()
The result:
[2006-08-06 11:58:48,568] [engine]: select relname from pg_class where
lower(relname) = %(name)s
[2006-08-06 11:58:48,568] [engine]: {'name': 'tg_user'}
[2006-08-06 11:58:48,573] [engine]:
CREATE TABLE tg_user (
user_id SERIAL NOT NULL,
user_name VARCHAR(16),
PRIMARY KEY (user_id)
)
[2006-08-06 11:58:48,574] [engine]: None
[2006-08-06 11:58:48,614] [engine]: COMMIT
[2006-08-06 11:58:48,620] [engine]: CREATE UNIQUE INDEX
ux_tg_user_user_name ON tg_user (user_name)
[2006-08-06 11:58:48,620] [engine]: None
[2006-08-06 11:58:48,627] [engine]: COMMIT
[2006-08-06 11:58:48,629] [engine]: select relname from pg_class where
lower(relname) = %(name)s
[2006-08-06 11:58:48,629] [engine]: {'name': 'tg_group'}
[2006-08-06 11:58:48,631] [engine]:
CREATE TABLE tg_group (
group_id SERIAL NOT NULL,
group_name VARCHAR(16),
PRIMARY KEY (group_id)
)
[2006-08-06 11:58:48,631] [engine]: None
[2006-08-06 11:58:48,674] [engine]: COMMIT
[2006-08-06 11:58:48,677] [engine]: CREATE UNIQUE INDEX
ux_tg_group_group_name ON tg_group (group_name)
[2006-08-06 11:58:48,677] [engine]: None
[2006-08-06 11:58:48,683] [engine]: COMMIT
[2006-08-06 11:58:48,684] [engine]: select relname from pg_class where
lower(relname) = %(name)s
[2006-08-06 11:58:48,685] [engine]: {'name': 'user_group'}
[2006-08-06 11:58:48,687] [engine]:
CREATE TABLE user_group (
user_id INTEGER NOT NULL,
group_id INTEGER NOT NULL,
PRIMARY KEY (user_id, group_id),
FOREIGN KEY(user_id) REFERENCES tg_user (user_id),
FOREIGN KEY(group_id) REFERENCES tg_group (group_id)
)
[2006-08-06 11:58:48,687] [engine]: None
[2006-08-06 11:58:48,704] [engine]: COMMIT
As you can see, the SQL code being generated is not specifying simple
primary_key constraints on two different columns (user_id and
group_id), but rather specifying a single, composite primary_key
constraint across both columns.
In other words, the code in the quickstart-generated model.py seems to
be correct as far as I can tell. (Apart from the "permmissions" typo,
of course, which needs to be fixed).
--
Robin Munn
[EMAIL PROTECTED]
GPG key 0xD6497014
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
