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. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" 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/sqlalchemy?hl=en.
