Psychic Origami

For those that aren’t aware, SQLObject is an Object-Relational Mapping (ORM) library for Python. I use it in chrss (my chess by rss web app) as part of Turbogears. Ian and Kyran also use it as part of the ShowMeDo site.

Chrss and ShowMeDo have quite different levels of traffic. ShowMeDo has a lot more traffic than chrss, so performance might seem like more of an issue for ShowMeDo. However as chrss is a game that requires more interaction from the user this is not necessarily the case. If moving a piece takes even a second the site would seem sluggish. Whereas for a content rich site such as ShowMeDo user expectation can be a bit more forgiving.

Until recently Ian and Kyran have not needed to worry about performance and (rightly so) got on with the things that mattered (e.g. creating more screen-casts and building their community).

However the other day Ian asked me to help him out speed the site up. They were having some issues with a page taking too long to render. When creating chrss I’d spent a bit of extra time worrying about the performance of SQLObject, so I already knew what to look out for in their code. Luckily it mostly only required a few small tweaks and things ran a good deal quicker.

So what can you do to speed up SQLObject?

Enable Query Logging

Obviously don’t do this for your production server (it’ll only slow things down), but by adding ?debug=1 to your database connection URI, you can enable debug query logging. This will simply make SQLObject print out the details of every SQL statement that is ran against the database.

When developing this can give you a good idea of when you aren’t using SQLObject in an appropriate fashion. If you see pages of SQL statements flying past in your console window you should probably have a look to see why!

Enabling query logging is only going to help if you actually understand the SQL that you are looking at. Make sure you do some research if you aren’t familiar with SQL. SQLObject makes dealing with a relational database easier, but you still need to understand what it is actually doing to make the most of it.

SQLRelatedJoin/SQLMultipleJoin vs. RelatedJoin/MultipleJoin

Your mileage may vary, but generally speaking I’d recommend not using RelatedJoin (or MultipleJoin) to define many-to-many (or one-to-many) relationships with SQLObject. Instead use the SQL* related versions (SQLRelatedJoin and SQLMultipleJoin).

Why though?

Well RelatedJoin (and MultipleJoing) loads data lazily. Meaning that it first loads the id’s for each object, then uses a new query to load each object on demand. SQLRelatedJoin on the other hand works like select() and loads up all the data in one query. I’m simplifying a bit, but you can probably see that they behave differently.

Now sometimes lazy loading is what you want. Each object may contain a lot of data and you know you don’t need all of it.

However for the “normal” case you probably just want to get your object loaded into memory, with as few queries as possible. SQLRelatedJoin is what you want.

An example

I quick-started a project with tg-admin and created two model classes using RelatedJoin to link them:

class Entry(SQLObject):
    title=StringCol(length=255)
    body=StringCol()
    tags=RelatedJoin('Tag')

class Tag(SQLObject):
    name=StringCol(length=255,
                   alternateID=True,
                   alternateMethodName="by_tag_name")
    entries=RelatedJoin('Entry')

Pretty simple stuff. We can define an Entry and add Tag objects to it.

Then I ran tg-admin sql create to populate the (SQLite) database.

Next I ran tg-admin shell so I could create some objects in the database:

entry=Entry(title='a title',body='entry body')
test_tag=Tag(name='test_tag')
tag2=Tag(name='tag2')
entry.addTag(test_tag)
entry.addTag(tag2)

I then added ?debug=1 to the database URI:

sqlobject.dburi="sqlite://%(current_dir_uri)s/devdata.sqlite?debug=1"

Then I restarted tg-admin shell (with the IPython shell) and ran the following:

In [1]: entry=Entry.get(1)
 1/QueryOne:  SELECT title, body FROM entry WHERE id = (1)
 1/QueryR  :  SELECT title, body FROM entry WHERE id = (1)

In [2]: for tag in entry.tags:
   …:     print “tag.name=%s” % tag.name
   …:
 1/QueryAll:  SELECT tag_id FROM entry_tag WHERE entry_id = (1)
 1/QueryR  :  SELECT tag_id FROM entry_tag WHERE entry_id = (1)
 1/QueryOne:  SELECT name FROM tag WHERE id = (1)
 1/QueryR  :  SELECT name FROM tag WHERE id = (1)
 1/QueryOne:  SELECT name FROM tag WHERE id = (2)
 1/QueryR  :  SELECT name FROM tag WHERE id = (2)
tag.name=test_tag
tag.name=tag2

As you can see with a RelatedJoin printing the two tags on the Entry requires the following three queries:

SELECT tag_id FROM entry_tag WHERE entry_id = (1)
SELECT name FROM tag WHERE id = (1)
SELECT name FROM tag WHERE id = (2)

(note how only the name field is queried for as this is all we use)
The RelatedJoin performs lazy-loading and ends up having to perform one query per tag! For two tags this might not be a problem, but it soon adds up if you aren’t careful.

A minor change

Simply changing RelatedJoin to SQLRelatedJoin in the models and running that same code yields:

In [1]: entry=Entry.get(1)
 1/QueryOne:  SELECT title, body FROM entry WHERE id = (1)
 1/QueryR  :  SELECT title, body FROM entry WHERE id = (1)

In [2]: for tag in entry.tags:
   …:     print “tag.name=%s” % tag.name
   …:
 1/Select  :  SELECT tag.id, tag.name FROM entry, tag, entry_tag WHERE ((tag.id = entry_tag.tag_id) AND ((entry_tag.entry_id = entry.id) AND (entry.id = 1)))
 1/QueryR  :  SELECT tag.id, tag.name FROM entry, tag, entry_tag WHERE ((tag.id = entry_tag.tag_id) AND ((entry_tag.entry_id = entry.id) AND (entry.id = 1)))
tag.name=test_tag
tag.name=tag2

Printing out the tag names for the entry now only requires one query:

SELECT tag.id, tag.name FROM entry, tag, entry_tag WHERE ((tag.id = entry_tag.tag_id) AND ((entry_tag.entry_id = entry.id) AND (entry.id = 1)))

This is a big improvement - the number of queries we will run now no longer depends on the number of objects being returned.

Some caveats and notes

It’s not always this simple, so here are some issues you may encounter:

• RelatedJoin returns a list, whereas SQLRelatedJoin returns a SelectResults object (the same kind of object returned when calling select())
• Large columns (text/binary blobs) won’t get lazily loaded with SQLRelatedJoin
• Fewer database queries doesn’t always mean your code will run faster - understand what each query is doing
• Make sure you properly index your database
• You need to understand the SQL that SQLObject generates to get the most out of SQLObject
• SQLObject may not be as slow as you think - you might not be using it right

I’m in the process of to writing a simple mobile version of chrss (chess by rss). This is spurred by the arrival of my new Nokia 6300 (which comes with Opera Mini). I’m aiming to end up with a mini-site running at http://chrss.co.uk/mob/ or similar.

The plan is to keep it _very_ simple. Main page lets you login, then lists your active games (showing which ones it’s your turn to move etc.). From there you’ll be able to get to a game, view the board and make a move. That’ll be it. Dead simple.

Turbogears works quite well for this in some respects - I can encapsulate the mini-site as a CherryPy controller and keep it fairly self contained. Though there is one minor drawback. That being that the identity framework is set up for the main site and will attempt to use the regular login page.

So how do we re-use the whole identity framework, but force access to the mobile mini-site to go via a different login page?

After a little poking around the TG source…

The skeleton controller for logging in:

class Mobile(controllers.Controller):
    @expose(template="cheetah:chrss.templates.mobile.index")
    @mob_login
    def index(self):
        return dict()

    @expose(template="cheetah:chrss.templates.mobile.login")
    def login(self,user=None,pwd=None):
        if identity.current.anonymous:
            msg=None
            visit_key = turbogears.visit.current().key
            ident=identity.current_provider.validate_identity(user,pwd,visit_key)
            if ident is None:
                msg="login failed"
                return dict(user=user,msg=msg)
        raise redirect('/mob/')

The login method looks up the current visit_key (effectively the value of the cookie TG uses to track visits) then uses identity.current_provider. validate_identity to try and log the user in.

NB: I’m using user and pwd as variables, instead of user_name and password. The identity framework intercepts parameters with those names and uses them to authenticate behind the scenes. Obviously I need those values to get through to my controller method - hence why I’m using different names.

After the call to identity.current_provider. validate_identity I check to see if I have an object returned and either report that the login failed or redirect back to /mob/. Easy.

The next bit is the decorator @mob_login shown on the index controller method. This works a bit like the @identity.require decorator, but isn’t as flexible. It simply forces the user to a mobile login page if they aren’t logged in:

def mob_login(fn):
    def decorated(*arg,**kw):
        if identity.current.anonymous:
            return dict(tg_template="cheetah:chrss.templates.mobile.login",user='',msg=None)
        return fn(*arg,**kw)
    return decorated

Obviously you’ll need a template for the login page. I’m using cheetah and my login page looks like this (there’s a master template not shown here):

#extends chrss.templates.mobile.master
#def body
#if $msg
    <div id="msg">${msg}</div>
#end if
#filter WebSafe
    <h1>chrss: chess by rss</h1>
    <form action="$tg.url('/mob/login')" method="POST">
         <label for="user">user name:</label>
         <input type="text" id="user" name="user" value="$user"/>
         <label for="pwd">password:</label>
         <input type="password" id="pwd" name="pwd"/>
         <input type="submit" value="login"/>
    </form>
#end filter
#end def

And that is more or less all I had to do to get a basic mobile/alternative login page working. Hope you found it informative. I’ll report back later on how well actually running it on a mobile device goes…

• Bug fix for PGN (Portable Game Notation) output (as noted by Calibius)
• Faster PGN generation (evaluating only the legal moves I need to consider to avoid ambiguity, rather than _all_ legal moves)
• URLs in comments should now automatically appear as click-able links
• Ability to “rotate” the board, to view it from the other direction (handy when you’re playing black)

• simple bread crumbs added to aid navigation:
  

• highlighting games that it’s your turn to move in:
  

• PGN (portable game notation) output for games:
  

• improved test coverage

Just been reading the free copy of October’s Python Magazine (direct link to pdf).

Doug Hellmann’s “And Now For Something Completely Different” column, was particularly good. He covered a few of the nice higher level modules for parallel processes. The processing module looks like quite a good sweet-spot for ease of use vs. power.

Whilst reading this thread on the Turbogear’s Google Group someone (asm on the list) posted a link to adding breadcrumbs to Turbogears apps.

After a little more reading I found a page on the Turbogears site itself about this subject too: http://docs.turbogears.org/1.0/BreadCrumb

The really interesting morsel though was the discovery of the cherrypy._cputil.get_object_trail() function that’s part of CherryPy (itself a part of Turbogears). This basically gives you access to the path of objects CherryPy followed to invoke the current handler. Which basically means you can easily figure out the hierarchy of the url and generate a nice location based bread crumb trail.

So I’ve now added simple bread crumb navigation to chrss (my chess by rss site). That should go live in the next few days. I’ll need to tweak it a bit though, as I’m actually subverting CherryPy in a few places, but it’s a nice addition as it stands.