App Engine and SQLite

Wow. I'd like to thank everyone who filled out the form and left me feedback on what to write about next. They are very good, and I am glad to say that I now have a little backlog of things to talk about :-) Keep the ideas coming, and thank you so much!

Many suggestions fell into one of the following categories:

• Beginner's advice: what's a recommended setup? What are "best practices"? What's the best way to transition if you know Rails or insert-your-favorite-framework-here?


• What cool applications out there exist? How do people put app engine to use?


• Can you shine light on parts of the framework that are not so well documented?
  

After some consideration, I have decided (for now) to focus on the third aspect. I do not feel qualified yet to give good advice on IDEs or best practices (I still use jedit and a commandline, although I'd really love to get my Eclipse setup running). As far as cool apps is concerned, I am too lazy to hunt them down (although I won't hesitate to point them out if I ever run into them, see the yuil-article earlier this month). Thus, framework archeology (cue Indiana Jones theme here ;-) it is.

My task for the next couple of weeks is going to be to connect a local (aka running on the devserver) App Engine application to a relational database. For simplicity (I really don't want to set up Oracle or MySQL on my puny little laptop), I have chosen SQLite. My mission is going to be:

• Find out how the current, file-based connector for the dev appserver ticks.


• Replace each of its features (saving data, retrieving data, queries, transactions...) with the SQL-based analogon.


• Take a regular App Engine app and run it against the database.

Now, the reader of this blog might ask him- or herself, why would I do this? After all, even if I get this to run in the dev-appserver, there is no way SQLite could work for a deployed application. While that is certainly true, I would like to point out that there are still a couple of reasons that it's worth the effort. First of all, trying to do this should create some valuable insights on the way the lower-level persistence API happens to work. This knowledge is transferrable to other applications, like decorators around the existing datastore to add additional features, or a more complete integration for other python-based web frameworks. Furthermore, groundwork like this will hopefully show that people do not need to be concerned about lock-in when developing for App Engine. Maybe someday something like this could be integrated with other containers like AppDrop -- who knows... Last but not least, I expect it to be a lot of fun!

So, where do I start? My first seam is an old forum post on how to make an in-memory datastore instance for unit tests. Guess what? Not only happens that class to be Apache open sourced (which my final solution will also be, should I manage to pull this off!), it also ships as part of the standard SDK. I took the liberty of opening the file (datastore_file_stub.py) in a text editor and gut it to build the skeleton for my new implementation:

class DatastoreSqliteStub(object):
  """ Datastore stub implementation that uses a sqlite instance."""

  def __init__(self, database_name):
    """Constructor.

    Initializes and loads the datastore from the backing files, if they exist.

    Args:
      database_name: the name of the sqlite instance
    """
    #TODO: initialize sqlite instance
    pass
    
    
  def MakeSyncCall(self, service, call, request, response):
    """ Taken pretty much verbatim from the original datastore_file_stub."""
    assert service == 'datastore_v3'
    explanation = []
    assert request.IsInitialized(explanation), explanation
    (getattr(self, "_Dynamic_" + call))(request, response)
    assert response.IsInitialized(explanation), explanation
    

  def _Dynamic_Put(self, put_request, put_response):
    #TODO: find out what this is good for
    pass


  def _Dynamic_Get(self, get_request, get_response):
    #TODO: find out what this is good for
    pass


  def _Dynamic_Delete(self, delete_request, delete_response):
    #TODO: find out what this is good for
    pass


  def _Dynamic_RunQuery(self, query, query_result):
    #TODO: find out what this is good for
    pass


  def _Dynamic_Next(self, next_request, query_result):
    #TODO: find out what this is good for
    pass


  def _Dynamic_Count(self, query, integer64proto):
    #TODO: find out what this is good for
    pass


  def _Dynamic_BeginTransaction(self, request, transaction):
    #TODO: find out what this is good for
    pass


  def _Dynamic_Commit(self, transaction, transaction_response):
    #TODO: find out what this is good for
    pass


  def _Dynamic_Rollback(self, transaction, transaction_response):
    #TODO: find out what this is good for
    pass


  def _Dynamic_GetSchema(self, app_str, schema):
    #TODO: find out what this is good for
    pass


  def _Dynamic_CreateIndex(self, index, id_response):
    #TODO: find out what this is good for
    pass


  def _Dynamic_GetIndices(self, app_str, composite_indices):
    #TODO: find out what this is good for
    pass


  def _Dynamic_UpdateIndex(self, index, void):
    #TODO: find out what this is good for
    pass


  def _Dynamic_DeleteIndex(self, index, void):
    #TODO: find out what this is good for
    pass

So what does this code actually mean? It turns out that there are three layers of datastore APIs in our SDK:

• the high-level api from google.appengine.ext.db is what we are all used to by now. It has Model classes with data validation, GQL-queries and a lot of other goodness.
• beneath the high-level api, there is the module google.appengine.api.datastore. This module is what all the high-level stuff gets translated to. Datastore entities are mostly sets of values, and queries are hierarchies of objects rather than an easily human-readable language. Some of the early samples published when App Engine went live (see the wiki example mentioned in this old post) use the datastore. Personally, I think the Models are much nicer :-)
• the lowest-level is the API shown in the skeleton. All requests to the datastore are compiled into a tuple of objects: a "request" object containing all the input and a "response" object with all the output. Together with details on what method to call, these objects are shoved into a method called MakeSyncCall. Depending on a verb (call) that is passed along with the request, this method will then call one of the "dynamic"-method stubs shown in this source.

Over the next couple of weeks, I will do my best to analyze these methods one by one and translate them into the SQL-equivalents. At the end of this series of articles, I hope to have the ability to run a local app engine app against a database that can also be accessed with other db/reporting tools, populated through sql scripts, easily backed up and manipulated. Keep your fingers crossed...