Monday, April 12, 2010

SQL Cursor and Google App Engine

Google App Engine SDK 1.3.1 released a new important feature Datastore Query Cursors.
With this new feature we can bypass requests hard limits (1MB response size and 30 sec timeout), we can run a large query and split it into multiple request. This is how it can be done with JDO (it is also possible with low level API)
  1. Create your query
  2. Limit execution for a specific amount of rows
  3. Execute the query
  4. Process the results set
  5. Keep the cursor in memcache
Here is a simple code that implements all steps from my java Servlet:

        final int MAX_REC = 2000;
        final String cacheKeyName = "ExportMyTable" + req.getRemoteAddr();

        PrintWriter prt = resp.getWriter();

        PersistenceManager pm = PMF.get().getPersistenceManager(); // PMF is my representation to get the PersistenceManager instance
        Query query = pm.newQuery(myTable.class);
        List results = null;
        String cursorString = null;
        Cursor cursor = null;
        Map extensionMap = new HashMap();
        Cache cache = null;

        try { // check if cursor already saved in the cache, if so, use it
            cache = CacheManager.getInstance().getCacheFactory().createCache(Collections.emptyMap());
            cursorString = (String) cache.get(cacheKeyName);
            if (cursorString != null && !cursorString.isEmpty()){
                // Cursor were found in the cache, set the query to start from that cursor
                cursor = Cursor.fromWebSafeString(cursorString);
                extensionMap.put(JDOCursorHelper.CURSOR_EXTENSION, cursor);
        } catch (CacheException e) {
        query.setRange(0, MAX_REC);

        //execute the query
        results = (List) query.execute();

        // Process the results
        for (myTable t : results){   

        // Get the cursor
        cursor = JDOCursorHelper.getCursor(results);

        // get the cursor as string
        cursorString = cursor.toWebSafeString();

        // remove the cursor from the cache if got less records then requested (the query ended)
        if (results.size()
        } else {

            // keep current cursor location
            cache.put(cacheKeyName, cursorString);


No comments: