Mobile offline app with local DB based on Visualforce

Introduction

This was an experimental project which started as a POC for a client. Even if we realised at the end that using this stack for making a mobile app is a nonsense, it was at least technically very interesting and might be even an option for a desktop app.

Requirements

The idea was to develop an offline mobile application based on Visualforce Pages. The application requires to be able to browse a potential list of tens of thousands of "clients" online like offline. Records can be updated and if the application is offline, they need to be synchronised to Salesforce when the application comes back online. Finally, the application should be packaged in a native Android container.

Stack

Ionic Framework

Being since a long time an Angular.js fan, it was obvious to use it again. As for the UI, it's usual to pick up something like Bootstrap or Foundation but 2 months ago, I heard about the awesome team developing the Ionic Framework and this project was the ideal occasion to have a better look at it. Despite being in alpha development (was at that time in version 0.9.27), I got a few issues but most of things were well thoughts and it was easy to have quickly an up and running beautiful app:
Login:alt Edit Contact:alt For those who don't know, Ionic is a UI framework built on top of Angular which allows to build quickly good looking mobile app. It has lots of pre-built components (header, footer, side menu, forms, lists, ... with built-in animations when swiping, pulling, going to another page...), almost everything you may need for building a normal mobile app.

Offline Visualforce

This part was much easier than expected. It was about adding a manifest to the home page describing the different resources to be cached.
Concretely, I had to create a Visualforce Page describing these different resources.
First, an attribute in the main VF page linking to the "cache" VF page:

<html manifest="/demoReal/CourseConsultantCache">

demoReal was the name of the Force.com Site while CourseConsultantCache is the name of the VF Page. And here the CourseConsultantCache page itself:

<apex:page sidebar="false" showHeader="false" standardStylesheets="false" contentType="text/cache-manifest">CACHE MANIFEST
#ver 2
{!URLFOR($Resource.CourseConsultant, 'css/ionic.min.css')}
{!URLFOR($Resource.CourseConsultant, 'js/ionic.bundle.min.js')}
{!URLFOR($Resource.CourseConsultant, 'js/jquery.min.js')}
{!URLFOR($Resource.CourseConsultant, 'js/Linq2IndexedDB.min.js')}
/jslibrary/1383366200000/sfdc/JiffyStubs.js 
/jslibrary/1383366200000/sfdc/VFRemote.js

NETWORK:
*
</apex:page>

Don't add a break line before CACHE MANIFEST otherwise the browser won't parse the file correctly (Salesforce adds otherwise an empty line)! You will also notice the 2 last files JiffiStubs and VFRemote, which belong to Salesforce. They are useless for the offline application but are required for the JavaScript Remoting of the online application. If you load the page when offline and ignore these 2 files, your application won't be able to perform Remote Actions when the applications comes online. This is definitely an issue hardcoding these files since Salesforce may at any time update the path without notice (especially the 1383366200000). An idea was to copy paste the scripts of these 2 files and adding them to the application but then what happens when Salesforce updates the code...
Nonetheless this solution works for now in our POC app and the other resources are cached just fine.

IndexedDB and Linq2IndexedDB

This part was actually really tricky. A few years ago, Google opens the way of browser databases by developing WebSQL which is a kind of local DB on which you can perform SQL queries. The DB was not so fast, but it was working and you could perform real SQL queries. However, this has not been accepted for different reasons by the community (it has only been implemented in WebKit AFAIK) and the open standards have defined IndexedDB as the real standard. IndexedDB has therefore been implemented in most browsers (including now Chrome, Firefox and even IE10+), however Safari is still using WebSQL (is Safari the new IE? :-) ).
Safari was anyway not that important since only Android devices were targeted and Safari is expected to support this year IndexedDB. The native Android Browser supports IndexedDB since Android 4.4.
I first spent lots of time understanding IndexedDB and used the native implementation to load records from Salesforce to IndexedDB (window.indexedDB). After having understood how the indexes are working, it was quite straight forward and rather fast to load 400000 records locally (yes I go crazy and wanted to test the limits). The whole operation didn't took much more than 1 single minute (had to perform Salesforce side X queries of max 50K records each because of SOQL limits). I thought I was almost done with everything when unexpected issues appeared. Indeed, in order to browse a Database of 400K records, you usually have search criteria :-)

It was nothing more than just a few hardcoded criteria (i.e. the Name, the Phone and a birth date range)

When the IndexedDB database is created, indexes which are used for indexing the data may be created (i.e. these indexes are then used for retrieving the data). Problem come when a complex query has to be performed, i.e. a query with "and" / "or" / "like" in the "where" condition. IndexedDB has simply not been done to retrieve data this way (IndexedDB retrieves data well only on 1 index at a time). There are some custom implementations, which try to workaround the limitations (like Linq2IndexedDB or Ydn-db) but none of them are perfect (the idea behind is a complex algorithm which retrieve the data from IndexedDB, each on a single index and then try "to join" the queries in this algorithm). Linq2IndexedDB performs at best since the library creates Web Workers for multiples indexes and joins the result at the end. Using multiple cores of the CPU produced great results on Chrome, while performance on Firefox were rather poor (FF looks to be the issue, more than the library itself). On the other hand, Ydn-db was really complex to use with deprecated documentation while it performed poorly at the end so finally I have sticked with Linq2IndexedDB. And I reduced the numbers of records from 400K to something more realistic like 60K. However the algorithm to insert data is slow, hence I have used the native browser implementation for inserting data while I used Linq2IndexedDB for retrieving them...

Conclusion

If it's about developing a real offline mobile application, it's much smarter going through the usual way, i.e. something like Hybrid HTML5 App + Salesforce Mobile SDK (for smartstore/encryption) + your preferred JS Framework (e.g. Angular, Ember, Backbone, Sencha...). But if your requirement is to develop an offline desktop application, the approach described before may work for you. However I would strongly advice to reduce the scope of browsers to just Chrome ideally (because of IndexedDB). Another idea, would be to package the application into a Chrome app in order to force your users to use Chrome in any case.

If you'd like to download the source code, you may find it here: https://github.com/Krisa/Offline-indexedDB-Visualforce

comments powered by Disqus