Living with SQLObject's Cache

Recently I’ve had a reason to look around again at Python ORM land again. I use/have used ORM – Object Relational Membrane (thanks Diedrich!), a well-hidden but enjoyable to use tool from Titus Brown called Cucumber (Postgres only, makes use of Postgresql inheritance); my own (yuk) and SQLObject and as well have fired up quite a few of older and newer entries into this area.

SQLObject takes the prize for overall speed and quick convenience when doing something quick and dirty. Its especially nice to have it create “objects” on the fly from the database or create your database from an object definition. Perfect for quick projects.

I was struggling with one issue however – SQLObject caches (like most of these tools) but it really caches entries. Updates in another process will not be reflected in an already running process unless you force SQLObject to skip caching – which very oddly really kills its performance – far more so than simply restarting a process!

Listing showing performance against ORM and SQLObject


--------------------
SQLObject no expire Elapsed 00:00:00.67 00:00:00.67 1258 original object
ORM Elapsed 00:00:00.62 00:00:00.61 1258 original object
--------------------
SQLObject no expire Elapsed 00:00:00.46 00:00:00.46 1258 original object
ORM Elapsed 00:00:00.62 00:00:00.61 1258 original object
--------------------
SQLObject no expire Elapsed 00:00:00.48 00:00:00.48 1258 original object
ORM Elapsed 00:00:00.73 00:00:00.72 1258 original object
--------------------
SQLObject no expire Elapsed 00:00:00.47 00:00:00.47 1258 original object
ORM Elapsed 00:00:00.62 00:00:00.62 1258 now changed
--------------------
SQLObject no expire Elapsed 00:00:00.46 00:00:00.46 1258 original object
ORM Elapsed 00:00:00.62 00:00:00.61 1258 now changed
--------------------
SQLObject no expire Elapsed 00:00:00.46 00:00:00.46 1258 original object
ORM Elapsed 00:00:00.64 00:00:00.63 1258 now changed
--------------------
SQLObject no expire Elapsed 00:00:00.47 00:00:00.46 1258 original object
ORM Elapsed 00:00:00.76 00:00:00.75 1258 now changed


And with expire_all()
--------------------
SQLObject w/expire Elapsed 00:00:00.68 00:00:00.68 1258 original object
ORM Elapsed 00:00:00.63 00:00:00.62 1258 original object
--------------------
SQLObject w/expire Elapsed 00:00:00.62 00:00:00.62 1258 original object
ORM Elapsed 00:00:00.62 00:00:00.62 1258 original object
--------------------
SQLObject w/expire Elapsed 00:00:00.66 00:00:00.66 1258 original object
ORM Elapsed 00:00:00.62 00:00:00.61 1258 original object
--------------------
SQLObject w/expire Elapsed 00:00:00.68 00:00:00.68 1258 now changed
ORM Elapsed 00:00:00.62 00:00:00.61 1258 now changed
--------------------
SQLObject w/expire Elapsed 00:00:00.67 00:00:00.67 1258 now changed
ORM Elapsed 00:00:00.63 00:00:00.62 1258 now changed
--------------------
SQLObject w/expire Elapsed 00:00:00.67 00:00:00.67 1258 now changed
ORM Elapsed 00:00:00.62 00:00:00.61 1258 now changed
--------------------
SQLObject w/expire Elapsed 00:00:00.67 00:00:00.67 1258 now changed
ORM Elapsed 00:00:00.62 00:00:00.61 1258 now changed

And a hack to try to deal with the situation:

__connection__ = None


def get_connection():
    global __connection__ 
    if __connection__ is None:
        __connection__ = _get_connection(dsn=config.db_dsn, debug=config.db_debug_sql)
    return __connection__


get_connection()



import gc
def expire_all():
    c = get_connection()
    for k in c.cache.caches.keys():
        c.cache.caches[k].expireAll()
    gc.collect()

So, when needed, expire_all() plus YourObject.sync() and other strategies will allow you to exploit SQLObject cache, most of the time.

The SQLObject code has a bug in expireAll – ref(obj) instead of ref(value) – and the method isn’t called from anywhere in the code either, at least not in the svn checkout I am looking at.