Can I access session.identity_map? Or is there a better way to do this?

[Kai Jia]

Hi, 

The situation is that I have tow tables (User and UserGrp) and two association tables (one for many-to-many between User and UserGrp, one for many-to-many between UserGrp and user group permissions). Each user can belong to multiple user groups, and each user group can have multiple permissions. The permissions that a user has is the union of the permissions of the groups that it belongs to.

I cached the permission of each user in the User table (see the User._perms_cache attribute). So it is necessary to invalidate the cache when the relationship between users and user groups gets changed. To invalidate the cache, I do an UPDATE on all the affected users, and I also have to expire the _perms_cache attribute of all persistent User instances. However, I searched the docs and couldn't find an appropriate API (Session.expire_all does not work, which is shown later; Session.expire requires an instance).

I looked into SQLAlchemy's source, and finally found that I can iterate over all the persistent objects via Session.identity_map. But this is not documented, and do I really have to do this?

The simplified model is attached (sorry...it's still that long....)

And I also wonder why the following does not work? It raises an IntegrityError.

python test.py 

>>> g0.perms.add(5)

>>> ses.expire_all()

>>> ses.commit()

Finally, thanks very much for your patient reading!

[Michael Bayer]

On Feb 11, 2012, at 7:04 AM, Kai Jia wrote:

Hi, 

The situation is that I have tow tables (User and UserGrp) and two association tables (one for many-to-many between User and UserGrp, one for many-to-many between UserGrp and user group permissions). Each user can belong to multiple user groups, and each user group can have multiple permissions. The permissions that a user has is the union of the permissions of the groups that it belongs to.

I cached the permission of each user in the User table (see the User._perms_cache attribute). So it is necessary to invalidate the cache when the relationship between users and user groups gets changed. To invalidate the cache, I do an UPDATE on all the affected users, and I also have to expire the _perms_cache attribute of all persistent User instances. However, I searched the docs and couldn't find an appropriate API (Session.expire_all does not work, which is shown later; Session.expire requires an instance).

I looked into SQLAlchemy's source, and finally found that I can iterate over all the persistent objects via Session.identity_map. But this is not documented,

this blew me away that it didn't make it into the docs, fixed:

http://docs.sqlalchemy.org/en/latest/orm/session.html#session-attributes

http://docs.sqlalchemy.org/en/latest/orm/session.html#sqlalchemy.orm.session.Session.identity_map

and do I really have to do this?

there's a lot of variables to what you're doing, caching is a pretty open ended thing.    Interesting here that you're caching in two different ways at the same time, both as a textual column and an in-memory map.  I'd probably convert _perms_cache to use a Json column and just skip the extra in-memory dictionary, I'm not sure what you're gaining by caching twice like that.

But, for the general issue of a session-scoped, in-memory cache, this is common.    You might want to consider that the problem you're trying to solve is a per-Session cache of permissions.    But when you deal with each User object, you're storing a cache locally on each User.   Why not just stick the dictionary on the Session itself ?

class User(Base):

    # ....

    _perms_cache = Column('permscache', Text())

    @property

    def perms(self):

        sess = object_session(self)

        if not hasattr(sess, '_perms_cache'):

            sess._perms_cache = {}

        if self in sess._perms_cache:

            return sess._perms_cache[self]

        else:

            sess._perms_cache[self] = result = self._get_perms()

            return result

   # ...

def invalidate_user_perm_cache(session, gid):

    try:

        del session._perms_cache

    except AttributeError:

        pass

    sub = session.query(MapUserAndUserGrp.uid) \

            .filter(MapUserAndUserGrp.gid == gid)

    session.query(User).filter(User.id.in_(sub)) \

            .update({User._perms_cache: None}, synchronize_session = False)

The simplified model is attached (sorry...it's still that long....)

And I also wonder why the following does not work? It raises an IntegrityError.

python test.py 

>>> g0.perms.add(5)

>>> ses.expire_all()

>>> ses.commit()

this model took me a long time to get my head around, guess its early since it's not doing anything weird...guess it's the names.

when you expire_all(), the pending changes on UsrGrp, which include that a new MapUserGrpAndPerm has been associated with it, is removed.   But the actual MapUserGrpAndPerm remains pending in Session.new.   The unit of work flushes it, the "gid" column fails to get populated since UsrGrp's changes are gone, then you get a NULL integrity constraint.

[Kai Jia]

The reason why I used a in-database cache is that the group permissions are not often changed, so when a user logs in, I can know its permissions without querying the other three tables. And I used a in-memory cache because it's quite frequent to check user permissions, so I do not have to json decode the string each time.

But your session-based cache solution is really cool! :)

Thanks very much for your kind reply!