spyne many-to-many version of server_sqlalchemy

I modified the server_sqlalchemy example to enable multi table as follows permissions = Array(Permission).store_as(table(multi=True)) and added methods get_permission, put_permission and get_all_permission similar to get_user, put_user and get_all_user.

On the client side I am using suds client as follows:

from suds.client import Client
c = Client('http://localhost:8000/?wsdl')

#create two permissions
p = c.factory.create('Permission')
p.application = 'usermgr'
p.operation = 'modify'
p.id = c.service.put_permission(p)

q = c.factory.create('Permission')
q.application = 'accountmgr'
q.operation = 'read'
q.id = c.service.put_permission(q)

#create two users
u = c.factory.create('User')
u.user_name = 'abcd'
u.full_name = 'abcd xyz'
u.email = 'abcd@xyz.com'
u.permissions = c.factory.create('PermissionArray')
u.permissions.Permission = [p,q]
u.id = c.service.put_user(u)

v = c.factory.create('User')
v.user_name = 'dcba'
v.full_name = 'dcba zyx'
v.email = 'dcba@zyx.com'
v.permissions = c.factory.create('PermissionArray')
v.permissions.Permission = [p,q] #note the same p,q used in u
v.id = c.service.put_user(v)

The put_user(v) fails due to

sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (sqlite3.IntegrityError) UNIQUE constraint failed: permission.id [SQL: 'INSERT INTO permission (id, operation, application) VALUES (?, ?, ?)'] [parameters: ((3, 'modify', 'usermgr'), (4, 'read', 'accountmgr'))]

Clearly the code is trying to insert p,q gain into permission table and fails. Shouldn't multi table only insert into user_permissions table? If not, how to I achieve the desired behavior as shown in the client?

Thanks

Solution

TL;DR: Use the first suggested workaround at the bottom of this post.

OK, I knew this'd be an issue.

Here's what's happening here:

You insert two permission objects successfully, get their server-generated ids.
You create a user object and put your newly inserted permissions inside the permissions array of the User instance.
As the put_user comes in as a separate request that uses a fresh session, sqlalchemy does not remember that these two permission objects are already in the database and tries to insert them again.
As the primary keys for these permissions already exist, you get that error.

So it's got nothing to do with having multi=True in the permissions array.

Relations in SQLAlchemy are "owner" relations by default. Here you need a "user" relation.

i.e. In this case, the User instances do not "own" permissions, they just "use" them. For example none of the permissions need to be deleted when a user is deleted.

I don't know of any way of defining a "user" relationship with SQLAlchemy. You need to bring the "used" objects into the session, and the only way of doing that is selecting them -- which is inefficient. Here's an answer by the author of SQLAlchemy about this subject: https://stackoverflow.com/a/9674552/1520211

As of 2.12, Spyne does not support marking relations as "owner/user" relations yet -- I just did not have time to study SQLAlchemy internals enough to come up with an elegant implementation.

So I got two workarounds for you:

user.permissions = [session.query(Permission).filter_by(id=perm.id).one() for perm in user.permissions]

VERY inefficient, but a single-line. Runs a select for every single incoming permission object. Because it uses .one() it will make sure clients can only add already-existing permissions. Note that Spyne will handle the NotFoundException gracefully and return a client error.
The efficient version that inserts the relationship rows manually.
```
permissions = user.permissions
user.permissions = []
session.add(user)
session.flush()
for perm in permissions:
    session.connection().execute("insert into user_permission "
                     "(user_id, permission_id) values (%s, %s)",
                                               perm.id, user.id)
```
Note that

i. This can be further optimized to use issue a single insert with multiple rows.

ii. If the permission id's are not valid, this will throw a foreign key error that Spyne knows nothing about. This means the client will receive a server error and think there's an issue on your side whereas the error is actually his fault. So if you care about retuning correct exceptions, you need to catch the database exception, make sure it's a constraint validation for the particular foreign key that points to the permissions table and raise a client error that says something along the lines of "Unknown permission id".

You can join http://lists.spyne.io/listinfo/people for further discussion if you prefer.