Querying the Context

An important part of any ORM is being able to easily query the objects contained in the database. Not every ORM handles this particularly gracefully.

Probably the most graceful I’ve seen is the Pythonic query in Dejavu where you literally write Python code which gets disassembled and turned into queries under the covers.

One of the least graceful is the SQLBuilder module which is part of SQLObject. While I recognise the vast amount of really clever code that goes into making SQLBuilder work, I just find it icky. Note: This is just my personal opinion.

High expectations

One of the goals for the querying facility within my ORM library is the ability to cleanly perform multiple queries. Given the following model:

class Page(orm.Entity):
    name= orm.StringCol( length=200 )
    entries= orm.ToMany( dest="Entry", inverse="page" )

class Entry(orm.Entity):
    data= orm.StringCol()
    lastModified= orm.DateTimeCol( defaults= datetime.now )

I want to be able to perform the following query: Find the Page that was most recently modified. Currently this is pretty tough. I’ve not spent enough time with Dejavu to know how I would perform this query with that library, but I’m pretty certain this would be a really nasty rat’s-nest of SQLBuilder code.

First steps

Last night I took the very first steps on the road to providing a clean query facility. I haven’t yet built a parser for queries, so they must be built by combining objects (like SQLBuilder).

My tests used the following model:

class Address(orm.Entity):
    street= orm.StringCol()
    city= orm.StringCol()
    state= orm.StringCol(length=2)
    zip= orm.StringCol()

class User(orm.Entity):
    _tablename="users"

    name= orm.StringCol( alternateId=True )
    address= orm.ToOne( dest="orm.test.model.Address" )

I then created some Users with Addresses:

u1= User( name="Jeff", address=Address( street="3 Market Sq Apt #3",
                                        city="Newburyport", state="MA",
                                        zip="01950" ) )
u2= User( name="Anna", address=Address( street="3 Market Sq Apt #3",
                                        city="Newburyport", state="MA",
                                        zip="01950" ) )
u3= User( name="Bean", address=Address( street="300 Brickstone Sq",
                                        city="Andover", state="MA",
                                        zip="01810" ) )

At the moment, I have to build the query by hand:

a= orm.AttributeExpression( "address.city" )
e= orm.ComparisonPredicate( a, orm.ConstExpression("Newburyport"),
                            orm.ComparisonPredicate.EQ )
f=orm.FetchRequest( User, e )

But eventually I want to have the syntax something like:

context.fetch( User, "address.city='Newburyport'" )

A fetched collection

One of the driving reasons behind creating this query code (beside the obvious ability to retrieve objects from the database) is a Query column for Entities. These Query columns would contain a predefined database query that can access properties of the host object. So if I were building a smart playlist like in iTunes, I might have:

class SmartPlaylist(orm.Entity):
    genres= orm.ToMany( dest="Genre" )
    songs= orm.Query( dest="Song", query="genre in ${genres}" )

I think this would be really powerful.

Parser generator

Were I writing a C++ or Java library, I’d know where to go for a good parser generator. But what’s the tool of choice for Python?

Possibly Related

Comments

Bob Ippolito November 21st, 2005 @ 9:48 am

If you want to use a subset of Python to do it then take a look at what PJE does in RuleDispatch.

Robert Brewer November 21st, 2005 @ 1:09 pm

There a a couple of ways to go in Dejavu, but here’s what I’d probably start with:

class Page(Unit):
    name = UnitProperty()

    def last_mod(cls, sandbox=None):
        if sandbox is None:
            sandbox = arena.new_sandbox()

        e = sandbox.view(Entry, ('lastModified', 'page'))
        if e:
            e.sort()
            lastmod, lastpage = e[-1]
            return sandbox.unit(cls, ID=page)
        else:
            return None
    last_mod = classmethod(last_mod)

class Entry(Unit):
    data = UnitProperty()
    page = UnitProperty(int)
    lastModified = UnitProperty(datetime.datetime)

Page.one_to_many("ID", Entry, 'page')

One of these days, we’ll add support for aggregators like MAX and MIN.. :/

Shalabh November 21st, 2005 @ 10:20 pm

Interestingly, for this special case, the following solution works in QLime (assuming you have two tables with one PK-FK relation):

for page in Page.search(sort_on=(Reversed(Page.entries.lastModified),),
                        limit=1):
    latest_page = page
    break

This fires exactly one SQL query and loads the page and all associated entries. Here’s another solution - first find the latest entry and then the associated page. This would work even if entries was a ‘lazy’ attribute:

for entry in Entry.search(sort_on=(Reversed(Entry.lastModified),), limit=1):
    latest_entry = entry
    break

latest_page = Page.find_one(entries=latest_entry)

I’m wondering why something like above would not be possible in SQLObject, or is it that an Entry may exist for classes other than Page?

Runar Petursson November 23rd, 2005 @ 3:03 am

Currently using SQLString, my single minded library to build SQL Strings, the syntax for the would be:

run(rm.user + rm.address.where(rm.address.city == "NewBuryport"))

Without any previous definition. This returns a Query result, wrapped in a nice iterable object.

In my yet-to-be-written ORM I’d like the classes to be tied to relations (views), not relvars (tables). This allows for a more natural mapping between Objects (which often span multiple tables) and the Relational Model. The syntax might be:

class UserRecord(relation):
    """
    % user join address
    """

m = UserRecord.where("address1 = 'NewBuryport')

or (page case)

class NewestPage(relation):
"""
This is a class whose instances always contain the newest page.
% page join entry where lastModified = (entry over max(lastModified))
"""

The relation is expressed in the doc string. Of course my dream ORm has a built in Tutorial-D parser, so I can write elegant expressions and have them work. There’s no reason this couldn’t create an object that was then updateable to the original tables in question. There are a ton of other doors that open up too, like taking two random runtime instances and joining them, or inserting one “set” into another one of the same type. The idea is mostly based on Date’s work and the third manifesto.

There are a lot of issues to work out, but I think it’s an elegant way to solve the recurring query issue of ORM’s.

Alexander Kozlovsky November 25th, 2005 @ 5:13 pm

Were I writing a C++ or Java library,
I’d know where to go for a good parser generator.
But what’s the tool of choice for Python?

I think ToyParserGenerator is the most Pythonic solution.
http://christophe.delord.free.fr/en/tpg/