Wednesday, 14 May 2008

Decoding a Query back to a string

On the App Engine Google group, Thomas Kuczek asked:

I have a query object representing a query. How can I print the resulting Gql to log it with the logger framework?

I thought this sounded like an interesting and possibly useful thing to do, so I wrote a small module which can convert either a db.Query or a db.GqlQuery object to a meaningful string.

Warning

This code depends on details of the query implementation (a lot of the required fields are private), so I can be fairly safe in saying that not only may it break in the next release of the App Engine environment, but it almost certainly will break.

In the code samples which follow the test class is:

class Person(db.Model):
"""Dummy class for testing"""
first_name = db.StringProperty()
last_name = db.StringProperty()
city = db.StringProperty()
birth_year = db.IntegerProperty()
height = db.IntegerProperty()


First some setup code for the tests:

>>> from google.appengine.ext import db
>>> from Person import Person
>>> from showquery import showQuery, showGqlQuery


Now try printing some queries:

>>> q = Person.all()
>>> print showQuery(q)
Person.all()
>>> print showQuery(q.filter("last_name =", "Smith"))
Person.all().filter('last_name =', 'Smith')


If we have more than one filter, showQuery will always output them in sorted order rather than the order in which they were input. This is simply to make doctests easier:

>>> print showQuery(q.filter('height <', 72))
Person.all().filter('height <', 72).filter('last_name =', 'Smith')
>>> print showQuery(q.order("-height"))
Person.all().filter('height <', 72).filter('last_name =', 'Smith').order('-height')


We can even handle an ancestor on the query although the key may be a bit of a mouthful:

>>> p = Person(first_name='Duncan', last_name='Booth', city='Oxford', height=183)
>>> key = p.put()
>>> print showQuery(q.ancestor(p))
Person.all().ancestor(datastore_types.Key.from_path('Person', 1, _app=u'test_app')).filter('height <', 72).filter('last_name =', 'Smith').order('-height')


There is also a showGqlQuery function to convert GQL back to the equivalent query:

>>> q = db.GqlQuery("SELECT * FROM Person WHERE last_name = :1 AND height < :2", "Smith", 72)
>>> print showGqlQuery(q)
SELECT * FROM Person WHERE last_name = :1 AND height < :2


Notice that once again the output may have the clauses in a different order than they were originally input.

>>> print showGqlQuery(db.GqlQuery("SELECT * FROM Person WHERE last_name = :name AND height < :height"))
SELECT * FROM Person WHERE height < :height AND last_name = :name


We can also handle literal values in queries:

>>> print showGqlQuery(db.GqlQuery("SELECT * FROM Person WHERE last_name = 'Smith'"))
SELECT * FROM Person WHERE last_name = 'Smith'


Sorting is also handled. The ORDER BY clause does preserve the original order:

>>> print showGqlQuery(db.GqlQuery("SELECT * FROM Person WHERE height < :1 ORDER BY last_name ASC"))
SELECT * FROM Person WHERE height < :1 ORDER BY last_name ASC
>>> print showGqlQuery(db.GqlQuery("SELECT * FROM Person WHERE height<:1 ORDER BY last_name DESC, height ASC"))
SELECT * FROM Person WHERE height < :1 ORDER BY last_name DESC, height ASC


Ancestor, limit, and offset classes also all work. If you specify limit and offset separately then they are output together:

>>> print showGqlQuery(db.GqlQuery("SELECT * FROM Person WHERE ANCESTOR IS :1 AND height < 72"))
SELECT * FROM Person WHERE ANCESTOR IS :1 AND height < 72
>>> print showGqlQuery(db.GqlQuery("SELECT * FROM Person WHERE ANCESTOR IS :1 LIMIT 10,5"))
SELECT * FROM Person WHERE ANCESTOR IS :1 LIMIT 10,5
>>> print showGqlQuery(db.GqlQuery("SELECT * FROM Person WHERE ANCESTOR IS :1 OFFSET 3"))
SELECT * FROM Person WHERE ANCESTOR IS :1 OFFSET 3
>>> print showGqlQuery(db.GqlQuery("SELECT * FROM Person WHERE ANCESTOR IS 'xxx' LIMIT 3 OFFSET 5"))
SELECT * FROM Person WHERE ANCESTOR IS 'xxx' LIMIT 5,3


The source code (showquery.py) looks like this:

from google.appengine.ext import db
from google.appengine.api import datastore

def showQuery(query):
"""Represent a query as a string"""
kind = query._model_class.kind()
ancestor = query._Query__ancestor
filters = query._Query__query_set
orderings = query._Query__orderings
hint = None
limit = None
offset = None

res = ["%s.all()" % kind]
if ancestor is not None:
res.append("ancestor(%r)" % ancestor)
for k in sorted(filters):
res.append("filter(%r, %r)" % (k, filters[k]))
for p, o in orderings:
if o==datastore.Query.DESCENDING:
p = '-'+p
res.append("order(%r)" % p)

return '.'.join(res)

def showGqlQuery(query):
"""Represent a GQL query as a string"""
proto = query._proto_query
kind = query._model_class.kind()
filters = proto.filters()
boundfilters = proto._GQL__bound_filters
orderings = proto.orderings()
hint = proto.hint()
limit = proto.limit()
offset = proto._GQL__offset

select = "SELECT * FROM %s" % kind
where = []
order = []

for k in sorted(filters):
for clause in filters[k]:
name, op = clause
if name==-1: name = 'ANCESTOR'
where.append("%s %s :%s" % (name, op.upper(), k))

for k in sorted(boundfilters):
where.append("%s %r" % (k, boundfilters[k]))

for p, o in orderings:
order.append("%s %s" % (p, 'DESC' if o==datastore.Query.DESCENDING else 'ASC'))

gql = select
if where:
gql += ' WHERE '+' AND '.join(where)
if order:
gql += ' ORDER BY ' + ', '.join(order)
if limit != -1:
if offset != -1:
gql += ' LIMIT %s,%s' % (offset,limit)
else:
gql += ' LIMIT %s' % limit
elif offset != -1:
gql += ' OFFSET %s' % offset
return gql

2 comments:

Duncan said...

I've moved the code for this blog post (and the previous and probably also the next) into a public svn repository. Get it with:
svn checkout http://kupuguy.googlecode.com/svn/trunk/appengine-doctests

Duncan said...

As predicted the code to decode a query broke in SDK 1.1.0. Fortunately the fix was trivial: see the SVN repository for the latest version.