Psychic Origami

Well it’s finally finished.  I’d best get on with delivery to the venue…

You’ll be able to see this painting as part of MASS which will be running on the 3rd, 10th, 17th and 24th of May.

I’ve mentioned this on the £5app blog already, but I’d just like to point out here that there is now wiki page to discuss the future of the £5app meet. We want to get feedback and suggestions on where we should take the £5app idea and this is one way to do it. We’ll also get a google group or similar setup. Hopefully this will also be a good way to get people interested in getting involved and doing talks.

For more details about the wiki itself you can read about it on the main page.

I’ve posted details of the next £5app meet to upcoming. It will be (as tradition dictates) the 2nd Tuesday of the Month (April 8th) at Brunswick square. Ian has a few more details. I’ll be posting more details on the £5app site later.

This will most likely be the last “story-led” £5app meet. We’ve had some pretty good tales told to us over the last year or so, but we feel it’s time to change things. So we’re aiming to try and get people to come along and actually work on stuff at the meets. Not sure of the exact plan yet (we’ll be discussing it at the next meet), but it’ll be a sort of mini-barcamp/workshop/hackday sort of an affair. Basically we want to help people get their projects off the ground. Exact details will follow as we figure them out.

After feeling somewhat intimidated by the blank canvas I made a start on my portrait for MASS on Sunday. Going fairly well so far:

The sketch you can see if my very rough idea of what I’m aiming for, but is very much a guideline and the final version will turn out very different. I’ve also resorted to painting with the canvas on the floor. For some reason I find it easier to work this way - I guess it’s what I’m used to from working with smaller pieces normally. Think I’ll revert to the easel later on as I add more detail and finish things off.

I’ve got a Flickr set of my progress with the painting for those who want see see how things have progressed/are progressing.

Free Porn

Finally started my piece for MASS today:

Picked up the canvas yesterday and realised quite how much work there is to do. Glad I’ve only agreed to paint one portrait, though if things go well I might just have time to do another. Think this is basically the largest piece of artwork I’ve ever attempted to do, so I think I’ll just have to chip away at it. First job will be applying primer to build up a nice even surface for me to work with.

does cialis soft tabs work with alcohol generic viagra authorized online pharmacy cialis soft tabs herbal alternative to viagra soft tabs female levitra use viagra purchase uk whats a female viagra viagra soft tabs or levitra viagra without prescription cialis online pharmacy levitra cheap buy cheap cialis online uk female levitra uk

Tomorrow Ian and I are holding our first Python Meet in Brighton. It’s all a bit low-key and we’ll actually be sharing the space with The Farm, but hopefully it’ll be a nice way to start kick starting a bit more of an active Python community down here in Brighton.

Ian will have a copy of Learning Python and I’ll bring the Django book. Plus we’ll have laptops for looking at code etc.

Anyway, if you are a Pythonista (or want to learn more about Python) then sign-up at upcoming and hopefully we’ll see you tomorrow at the Hampton Arms!

Carrying on from Girl with an Altair 8800 here’s another vaguely Diamond Age inspired piece. I’m quite enjoying doing paintings with these themes so there might well be a few more to come - probably tracking the evolution of the modern computer.

NB. That’s a “mouse army” at her feet.

This is sort of a continuation of my little SQLObject performance guide. So it might be worth reading that too, if you are after hints about speeding up SQLObject. Anyway, on with the show…

It’s possible to create raw (database agnostic) sql queries with SQLObject. This can be really handy for those spots where you really need to speed things up. It’s a bit like switching from Python to C for some performance intensive part of an application.

However when using raw SQL, we lose some of the nice-ness of SQLObject. Results arrive as tuples and we may then have to do more work to make use of them. So I’m going to discuss an example of using raw SQL in SQLObject, but still keeping the objects around.

The Model Code

In my example there are two model objects:

class Entry(SQLObject):
    title=StringCol(length=255)
    body=StringCol()
    views=SQLMultipleJoin('EntryView')

class EntryView(SQLObject):
    entry=ForeignKey('Entry')

Entry being a blog entry and EntryView being an object to keep track of the Entry being viewed. I’ve kept both objects free of details for this example, but obviously they could have all sorts of extra fields.

N+1 Queries

Now I want to get a list of all of the entries and how many views each entry has (sorted by number of views). So using regular SQLObject this looks like:

    # class method on the Entry class
    @classmethod
    def get_entry_views(cls):
        entries=cls.select()

        # get the count for each entry
        entry_counts=[]
        for entry in entries:
            entry_counts.append((entry, entry.views.count()))

        # now sort the list into descending order
        entry_counts.sort(key=lambda item:item[1])
        entry_counts.reverse()
        return entry_counts

Which is pretty straight forward really and gives the follow results (for some sample data):

[(<Entry 3 title='entry 3' body='body text 3'>, 5),
 (<Entry 1 title='hfdskhfks' body='fsdfsd'>, 2),
 (<Entry 2 title='hel' body='jjj'>, 0)]

(tuple of Entry objects followed by view count).

However this causes the following SQL to be executed:

SELECT entry.id, entry.title, entry.body FROM entry WHERE 1 = 1
SELECT COUNT(*) FROM entry_view WHERE ((entry_view.entry_id) = (1))
SELECT COUNT(*) FROM entry_view WHERE ((entry_view.entry_id) = (2))
SELECT COUNT(*) FROM entry_view WHERE ((entry_view.entry_id) = (3))

Which seems a bit bad. In fact this is a classic example of the N+1 problem, where we run one initial query and then one query for each row in that result.

2 queries

So now let’s try making that a bit better, with this alternative method:

    # need to import everything from sqlobject.sqlbuilder
    @classmethod
    def get_entry_views2(cls):
        conn=cls._connection
        fields = [Entry.q.id,SQLConstant('COUNT(*)')]
        select = Select(
                        fields,
                        join=INNERJOINOn(Entry,EntryView,Entry.q.id==EntryView.q.entryID),
                        groupBy=Entry.q.id)
        sql=conn.sqlrepr(select)

        # get the counts via the raw
        # sql query
        counts={}
        for entry_id,count in conn.queryAll(sql):
            counts[entry_id]=count

        # now read in all of the entries
        # and match them with the counts
        entries=cls.select()
        entry_counts=[]
        for entry in entries:
            entry_counts.append((entry,counts.get(entry.id,0)))

        # now sort the list into descending order
        entry_counts.sort(key=lambda item:item[1])
        entry_counts.reverse()
        return entry_counts

This time I’m using a raw sql query to get all of the (non-zero) view counts in one query and then using another query to get all of the Entry objects. Then using a bit of Python I stitch the results back together and sort it.

This generates the following SQL:

SELECT entry.id, COUNT(*) FROM  entry INNER JOIN entry_view ON ((entry.id) = (entry_view.entry_id)) GROUP BY entry.id
SELECT entry.id, entry.title, entry.body FROM entry WHERE 1 = 1

That’s not as bad as before, but if we were using regular SQL we’d be doing this in a single query that also sorted the results by the count at the same time!

1 query

At the moment we basically need the 2nd query to get the actual objects. If we could use one raw sql query to do the work for us and somehow use the results of the query to populate the relevant objects for us we’d be golden. After a bit of digging around in the SQLObject source code I looked at the get class method definition:

# in main.py
class SQLObject(object):
    ...
    def get(cls, id, connection=None, selectResults=None):

Further examination showed that if I passed in selectResults (a list of field values) in the right order I could get an object instance either based on the results I passed in, or else the version of the object with the matching id in the cache. Excellent. So now we can have a method that works thus:

    @classmethod
    def get_entry_views3(cls):
        return select_with_count(cls,EntryView,Entry.q.id==EntryView.q.entryID,orderByDesc=True)

Where the juicy bit is here (to make it more reusable elsewhere):

def select_with_count(selectClass,joinClass,join_on,orderByDesc=False):
    conn=selectClass._connection
    fields = [selectClass.q.id]
    for col in selectClass.sqlmeta.columnList:
        fields.append(getattr(selectClass.q, col.name))

    # name we’ll assign to the count
    # so we can sort on it
    count_field=(”%s_count”%joinClass.__name__).lower()
    fields.append(SQLConstant(’COUNT(%s) %s’%(joinClass.q.id, count_field)))

    orderBy=SQLConstant(count_field)
    if orderByDesc:
        orderBy=DESC(orderBy)

    select=Select(
            fields,
            join=LEFTJOINOn(selectClass,joinClass,join_on),
            groupBy=selectClass.q.id,
            orderBy=orderBy)
    sql=conn.sqlrepr(select)
    return read_from_results(conn.queryAll(sql),selectClass)

def read_from_results(results,selectClass):
    num_columns=len(selectClass.sqlmeta.columnList)
    items=[]
    for result in results:
        id,selectResults,extra=result[0],result[1:num_columns],result[num_columns:]
        entry=selectClass.get(id,selectResults=selectResults)
        items.append((entry,)+extra)
    return items

Which returns results in the same format as the original method and only generate one SQL query:

SELECT entry.id, entry.title, entry.body, COUNT(entry_view.id) entryview_count FROM  entry LEFT JOIN entry_view ON ((entry.id) = (entry_view.entry_id)) GROUP BY entry.id ORDER BY entryview_count DESC

There are a few of fiddly bits going on here that I’ll explain.

Firstly I perform a LEFT JOIN and use COUNT(entry_view.id) so we can results for entries that have no views.

Next, the order of the object fields has to match what SQLObject is expecting. That order being defined by the class’s sqlmeta.columnList.

Finally to be able to sort by the view count I have to provide a name for the count ( entryview_count), which I create based on the EntryView class name.

In conclusion

The example I gave was quite specific, but does show it’s possible to slightly better integrate raw SQL queries with SQLObject. This means that it’s possible to retain more of the easy to use nature of SQLObject when needing to speed up a few critical queries.

I suspect that with a bit of work it would be possible to create a quite nice library for performing generalised queries with SQLObject and getting nice objects back. For example it may be possible to use such techniques to eagerly load objects in joins (much as you can do in SQLAlchemy or the Java Persitence API).

Another update for chrss. No major changes to the chess part itself, but I’ve just added a blog so I’ve got a dedicated place to discuss chrss.

This marks the start of me adding some actual “content” to chrss. Up until now chrss (as a site) has basically consisted of a front page and a bunch of chess games! It’s been extremely functional, but I felt it was time for chrss to grow up a bit. It might also help me attract a few more visitors, as currently there’s not exactly much for the search engines to search on!