sAsync:   SQLAlchemy done Asynchronously

sAsync Usage

sAsync wraps your SQLAlchemy database access code inside asynchronous transactions. At the lowest level, it provides a @transact decorator for your database-access methods that makes them immediately return a Twisted Deferred object.

For example, suppose you want to run a method that selects a list of row objects from a table. Instead of waiting around for your method to return the list, blocking everything else your program is trying to do, you decorate it with @transact and run it. It immediately hands you a Deferred, and you scribble the name of your callback function on it, handing the Deferred back to your decorated method. (You can also keep a copy of it (i.e., a reference to the object) around if you like, for example to add other callbacks.)

Once you've attached your callback function to the Deferred result, you can go on with your business, knowing that SQLAlchemy will be cranking away behind the scenes (in a transaction-specific thread) to obtain a result for you. When the result is finally ready, your transact-decorated method will look at the Deferred, see the note you scribbled on it ("Pls call this function with the result. Thx!"), and give your function a call with the list of rows. It will supply the callback with the list as the function's argument.

You can also do some asynchronous database operations on a higher level. For example, you can maintain a store of Python objects, with each object accessible (with deferred results) via a unique key. If that sounds like what a dictionary does, it should! The sAsync package also provides a dictionary-like object with database-persistent items that you can access in an asynchronous fashion.

And, someday, someone will get the full-text searching capabilities working. Of course, the results of your potentially time-consuming searches will be done in the same asynchronous fashion.

Subclassing AccessBroker for all your Database Access Needs

The foundation for all of the asynchronous magic that sAsync provides, besides of course the Twisted asynchronous framework and SQLAlchemy themselves, is the AccessBroker base class. You import that class from the database module and subclass it.

Instances of your subclass are efficient engine-starting, table-generating, transaction-wrapping machines. For example, the following class is an excerpt of the Transactor class used in the items module:

import sqlalchemy as SA
from database import transact, AccessBroker

class Transactor(AccessBroker):
    """
    I do the hands-on work of (potentially) non-blocking database access for
    the persistence of name:value items within a uniquely-identified group,
    e.g., for a persistent dictionary using L{PersistentDict}.

    My methods return Twisted C{Deferred} instances to the results of their
    database accesses rather than forcing the client code to block while the
    database access is being completed.
    
    """
    def __init__(self, ID, *url, **kw):
        """
        Instantiates me for the items of a particular group uniquely identified
        by the supplied integer I{ID}, optionally using a particular database
        connection to I{url} with any supplied keywords.
        """
        if not isinstance(ID, int):
            raise TypeError("Item IDs must be integers")
        self.groupID = ID
        if url:
            AccessBroker.__init__(self, url[0], **kw)
        else:
            AccessBroker.__init__(self)
    
    def startup(self):
        """
        This method runs automatically as a preliminary part of the first transaction call.
        """
        return self.table(
            'sasync_items',
            SA.Column('group_id', SA.Integer, index="item"),
            SA.Column('name', SA.String(40), index="item"),
            SA.Column('value', SA.PickleType, nullable=False)
            )
    
    @transact
    def load(self, name):
        """
        Item load transaction
        """
        items = self.sasync_items
        if not self.s('load'):
            self.s(
                [items.c.value],
                SA.and_(items.c.group_id == self.groupID,
                        items.c.name == SA.bindparam('name')))
        rows = self.s().execute(name=name).fetchone()
        if not rows:
            return Missing(self.groupID, name)
        else:
            return rows['value']

There are a couple of cool things going on here:

• Via the @transact decorator, the entire load method is encapsulated into a distinct database transaction. More about that in the next section.

• Tables are conveniently defined (and created, if necessary) via a call to the table method. This is usually done in your startup method, which is automatically called (with _no_ arguments) as part of the first call to a transaction-encapsulated method like load.

• Select objects are generated just once and cached for future use with a convenient pattern:

     if not self.s(<select object name>):
         self.s(<select object arguments>)
     self.s().<select object method call>

In the if statement above, your code determines whether it still needs to define a particular, named select object. It makes that determination based on the True or False result of a call to the method s of your AccessBroker instance with the select object's name. In addition to returning that result, the method call also sets the name of the select object for the subsequent calls discussed below.

To define the select object, should that prove necessary, your code calls method s not with a string defining a name but with the arguments that are used in a regular SQLAlchemy select(...) call.

To actually use the select object, whether you just had to define it or whether it was cached, just call the s method with no arguments. The result will be a Select object with an .execute() method you can call as usual.

Encapsulating your Transaction Methods with the @transact Decorator

All database transactions are run in a single worker thread using a class-wide instance of ThreadedQueue() from the separately-distributed asynqueue package. A call to any method decorated with @transact, like the load method in the code excerpt above, will immediately return a Deferred to the method's eventual result.

The code inside the method will be queued up as a task and run when the single-threaded code devoted to the queue gets around to it. A priority queue is used, so higher-priority tasks are run first. Cool as that is, you won't have to pay much attention to it; you'll have your Deferred to play around with while your task waits a while to get run and then takes another while to produce its result.

You can set the priority of the task represented by your transaction method. The @transact decorator exposes several priority-queueing keywords to the API for every method it decorates. These are:

niceness

Scheduling niceness, an integer between -20 and 20, with lower numbers having higher scheduling priority as in UNIX nice and renice.

series

A hashable object uniquely identifying a series for this task. Tasks of multiple different series will be run with somewhat concurrent scheduling between the series even if they are dumped into the queue in big batches, whereas tasks within a single series will always run in sequence (except for niceness adjustments).

doNext

Set True to assign highest possible priority, even higher than with niceness = -20.

doLast

Set True to assign lower possible priority, even lower than with niceness = 20.

Tasks in a series of tasks all having niceness N+10 are dequeued and run at approximately half the rate of tasks in another series with niceness N.

Note that you should use almost always use a niceness value to set priority rather than resorting to the doNext and doNext "nuclear option" keywords, which are used internally to guarantee highest or lowest priority to certain things.

One simple and effective use of the priority queueing is to give database-writing transactions a bit more niceness than the default value of zero. That way, your database-reading transactions will run before your "lazy writes."

Of course, you should make sure that your database reads don't depend on the values of lower-priority database writes, e.g., with appropriate in-memory caching. See the pdict module to see how the built-in PersistentDict object does this with its data and keyCache instance variables.

Persisting name:value Items in an Items() Store

See the self-documented items module and the auto-generated API doc page themselves for details.

Asynchronously-Accessible Persistent Dictionaries and Arrays

See the self-documented pdict and parray modules and the auto-generated API docs themselves for details.

Full-Text Searching with Lazy Indexing and Asynchronous Queries

Not implemented yet.