Revisiting the Sencha Touch 2.2 Sql Proxy

As I mentioned in the comments of my previous blog post, the Sencha Touch Sql proxy introduced in Touch 2.1 had a number of debilitating bugs that made it largely unusable for production work. However, I’m pleased to report that all of the bugs that I had previously reported now seem to be fixed in the Touch 2.2 RC1 beta!

So, let’s do a little deep-dive into the proxy, shall we?

The Basics

Sencha Touch’s SQL proxy outputs model data into an HTML5 local database (SQLite).

There are two key advantages to using the SQL proxy over over the LocalStorage proxy:

1. Unlike LocalStorage which is fixed at ~5 MB, the size of the WebSQL database can be set by the developer. It defaults to 5MB, but you can override this setting to request more space from the user.
2. As illustrated by the figure below, the SQL proxy creates a table in the WebSQL database. Using native JavaScript methods you can write SQL to perform table joins.

Using the Proxy is a rather straightforward affair as illustrated by the following code snippet:

Ext.application({
 name: 'SqlProxyTest',

 // note that the Sql proxy has been renamed from Ext.data.proxy.SQL
 // in Touch 2.1 to Ext.data.proxy.Sql in Touch 2.2

 requires: [
  'Ext.MessageBox',
  'Ext.data.proxy.Sql',
  'Ext.data.Store'
 ],

 launch: function() {

    // define the model and proxy
    Ext.define("SqlProxyTest.model.User", {
        extend: "Ext.data.Model",
        config: {
          fields: [
             "firstName",
             "lastName",
             {
                name: "dateAdded",
                type: "date",
                defaultValue: new Date()
             }
          ],
          proxy: {
            type: "sql"
          }
        }
     });

     // define the Store
     Ext.create("Ext.data.Store", {
        model: "SqlProxyTest.model.User",
        storeId: 'Users'
     });

     // add a Record
     Ext.getStore('Users').add([{
        firstName: 'Steve',
        lastName: 'Drucker'
     }]);

     // write the record to disk
     Ext.getStore('Users').sync();
    }
});

You can load data back into the Store from LocalStorage by using the Ext.data.Store.load() method. Running local sql queries is an asynchronous process, so you may need to specify a callback function, depending on your specific use-case.

Note: Loading data back into the store was buggy in Touch 2.1.

Ext.getStore('Users').load();

Configuring the Proxy

As illustrated by the previous example, the Sql Proxy will automatically create a 5MB client-side database named “Sencha” and use the name of your model (“User”) as the name of the corresponding database table where records will be written. In theory, you can easily override the names of the table and the database as illustrated by the following snippet:

Ext.define("SqlProxyTest.model.User", {
 extend: "Ext.data.Model",
 config: {
  fields: [
   "firstName",
   "lastName",
   {
    name: "dateAdded",
    type: "date",
    defaultValue: new Date()
   }
  ],
  proxy: {
   type: "sql",
   database: "SqlProxyTest",
   table: "SenchaDevs2"
  }
 }
});

Unfortunately, setting the “table” config property causes an exception in Touch 2.2 RC1 – a rather minor bug, but let’s hope it gets resolved soon!

Also, note that in the prior example, the record’s id field is, by default, an autonumber. While this might work in certain use cases, it would be problematic if you were trying to syndicate and synchronize data. You can have Sencha Touch automatically substitute universally unique identifiers (UUID’s) for the default autonumber field by specifying the identifier config property as illustrated below:

Ext.define("SqlProxyTest.model.User", {
 extend: "Ext.data.Model",
 requires: ['Ext.data.identifier.Uuid'],
 config: {
  identifier: 'uuid',
  fields: [ 
   "firstName",
   "lastName", 
   {
    name: "dateAdded",
    type: "date",
    defaultValue: new Date()
   }
  ],
  proxy: {
   type: "sql",
   database: "SqlProxyTest"
  }
 }
});

Handling Updates to Table Schemas

Currently there are no built-in methods for updating your table’s schema if the structure of your Model has changed. You’ll need to develop an algorithm to handle this yourself. As illustrated by the following code snippet, my suggestion is to load the existing data into a Store, programmatically drop the table, and then write all the records back out to the db using the new schema:

Ext.getStore('Users').load(function(records, operation, success) {
       
 // destroy the table
 this.getModel().getProxy().dropTable();
 this.getModel().getProxy().setTableExists(false);
        
 var numRecs = this.getCount();
 
 // set the double-secret "phantom" bit to mark
 // records for re-insertion
     
 for (var i=0; i<numRecs; i++) {
   this.getAt(i).phantom = true; 
 }

 // create the db table and write all records
 this.sync();
});