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:

sqlproxy1


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:

sqluuid

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();
});

27 thoughts on “Revisiting the Sencha Touch 2.2 Sql Proxy

  1. Pingback: Using the Sencha Touch 2.1 SQL Proxy | Druck-I.T.

    1. Jose

      Greg,
      It would much appreciated if you could post your findings. After wasting a lot time with original SQL proxy – I’m a little gun shy to go back and test it.
      Thanks

      Reply
  2. Pingback: Sencha Touch 2 SQL proxy for SQLiteVadim Popa's Blog

  3. Flava

    Nice work! I just two questions: Can I store “pdf”-Files with this? Like a blob or something…

    And, if I clean the “cache”, are the files locally still avaiblabe after that?

    Reply
  4. Alex Graham

    Thanks for your posts on the sql proxy. which are a real help at the moment. I’ve used localstorage to sync locally stored data with remote data http://lalexgraham.wordpress.com/2012/09/12/sencha-touch-2-example-of-syncing-localstorage-store-with-remote-jsonp-proxy-store/

    but I want to develop a two way sync of data with multiple users performing CRUD operations on the same records. My plan is to do all the CRUD on locally stored data and amend the remote store accordingly and I thought localstorage, mainly because of the basic nature of its data storage(key/value) won’t cut it. Just a couple of questions if you don’t mind..

    Will I be safe in assuming that ST2.2 can now support what I would like to do?

    You mention use of UUID for ID column. I don’t know much about them but are you saying they are unique so that one person adding records on a device in a ST2 app would always create ids unique to their session that could not be replicated on another session ?

    Thanks

    Reply
  5. dreamtimeline

    Extending on Alex’s question, what is in your opinion the best way of syncing offline and online data, where the offline data is stored in the WebSQL database, and online is a superset housed in relational database.

    The user would not see only records that they are allowed to.

    Is there a neat efficient way to do this two way synchronisation (and conflict resolution) using checksums on records or something? Ideally not looping through every possible record too.

    Also is there an optimal chunk size / approach for design that minimises the risk of conflicts, as well as sending too much data back and forth?

    Reply
    1. sdrucker Post author

      Typically, what I do is store the lastsync date/time on the client and then pass it to the server on subsequent requests to check to see if there are any new records.

      Unfortunately, there’s no magic sync() method.

      Anything pertaining to security (“The user would only see records that they are allowed to”) has to be evaluated at the server. Evaluating security rules on the client is inherently insecure.

      If you have a lot of records that need to be transmitted, you might be able to offload some of the processing into an HTML5 web worker. I believe that there’s an extension on market.sencha.com that deals with the creation and handling of web workers.

      If you expect that the mobile user will be modifying data on the device and you want to sync those changes back to the server, you’ll need to implement a “sentinel” approach whereby each record has an autoincrementing integer that is incremented on each update. When you transmit the data from the client to the server, you check to see if the sentinel value for your record matches with the value for the record on the server. If they match, then you’re clear to overwrite the server’s record. If they don’t match, then it means that you have a conflict (e.g. someone updated the record while you were working on it) and you’ll have to decide how you want to resolve the differences.

      Reply
  6. Pingback: Links for May 21st through May 26th

  7. Andrew Wilkin

    Another question or two, perhaps because thinking still from the relational database world.

    Can one have a model that relates to a join across multiple tables? Or is it always one model, proxied to one table?

    If it is not possible, how does one get data neatly into the Xtemplate framework in a DataView. My data naturally has relationships, and I could store this as a hierarchical document but this will lead to more data synchronisation and possibility for clashes.

    Additionally being able to filter and leverage the query power would be useful. Thoughts?

    Reply
    1. sdrucker Post author

      Right now, the proxy only operates against a single table. I started working on a new proxy class that would enable multi-table joins (via passed-in SQL), but never finished it.

      Reply
      1. Andrew Wilkin

        Glad I’m not going mad, was thinking the same today. Added to which by creating own proxy would be able to add under the covers checksums and other bits to aid data synchronisation. Which I will now park on the to consider later pile…

  8. Ewerton Vicentin

    “…It defaults to 5MB, but you can override this setting to request more space from the user.”
    Steve, how can I set sql proxy to create a database grater than 5 MB?

    Reply
  9. DaB Apps

    Thanks for a great article. If you wanted to use SQLCipher as the underlying database, would you have to customize any of the Sencha code, or do you just swap in SQLCipher for the built-in Web DB in the native code only? This question, for me, pertains to Android.

    Reply
  10. rjsystematica

    It seems I am now able to create new model records with the standard field declaration (i.e. string value of ”) and the record is committing to the table.

    This issue hadn’t come up until I packaged the codebase for an android emu… so perhaps SenchaCmd corrupted something.

    I did come across another issue though – async race condition detailed here:
    http://www.sencha.com/forum/showthread.php?268579-Async-proxy-race-condition

    I don’t want to turn autoSync off on my store, and found not calling store.sync() after add() helpful.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s