If you don't want to mess around with database constraints manually as
Patrick suggested, it is possible to do through SQLObject alone, but
it's not too pretty. You should know what you're doing if you're going
to use this. Read the notes after the code unless you want things to
go horribly wrong.
The trick is creating the intermediate table yourself:
class UserRoles(SQLObject):
user = ForeignKey('User')
role = ForeignKey('Role')
assignedIndex = DatabaseIndex('user', 'role', unique=True) # Here's
the unique constraint
You'll also need to change the RelatedJoin calls in your User and Role
classes. Note that the UserRoles class must be defined BEFORE your User
and Role classes for this variation of the code to work.
class User(SQLObject):
username = StringCol(alternateID=True)
roles = RelatedJoin('Role',
joinColumn=UserRoles.q.userID.fieldName,
otherColumn=UserRoles.q.roleID.fieldName,
intermediateTable=UserRoles.sqlmeta.table)
class Role(SQLObject):
name = StringCol(alternateID=True)
users = RelatedJoin('User',
joinColumn=UserRoles.q.roleID.fieldName,
otherColumn=UserRoles.q.userID.fieldName,
intermediateTable=UserRoles.sqlmeta.table)
Now trying to add the same user to a role more than once will raise:
pysqlite2.dbapi2.OperationalError: columns user_id, role_id are not
unique
...which you can catch if such a thing is attempted.
*** BIG DISCLAIMER ***
When creating and dropping tables, you now have to tell the respective
classes NOT to drop/create their join tables. This is done like so:
Creating:
UserRoles.createTable()
User.createTable(createJoinTables=False)
User.createJoinTables(ifNotExists=True)
Role.createTable(createJoinTables=False)
Role.createJoinTables(ifNotExists=True)
Dropping:
UserRoles.dropTable()
User.dropTable(dropJoinTables=False)
User.dropJoinTables(ifExists=True)
Role.dropTable(dropJoinTables=False)
Role.dropJoinTables(ifExists=True)
This will NOT work with TurboGear's tg-admin sql utility since that
doesn't let you mess with the create/drop arguments as far as I know --
ask a TurboGears developer how to do this.
You've been warned. ;)
