Tuesday, August 23, 2011

Into the Blender: JavaScript, SQLite, iOS, and PhoneGap

A few months ago I wrote a piece discussing my experiences in attempting to use localStorage in Mobile Safari on the iPad and iPhone. While I was disenchanted with the support for that mechanism via JavaScript, the application was written and worked pretty well. Well, that was until I needed to support 3+ packages that download from the same site and can, at times, exceed the 50 MB cap (per site) on the Apple line of devices.

Storing More Than Files
After doing some research, I found another storage technique that really appealed to me. SQLite-based storage is present in all of the modern browsers (well, Firefox has it technically, but wont let others play in it). It is also present on the major mobile platforms such as iOS and Android. It is a powerful system that stores to DB files on the operating system. In theory, SQLite can store up to the space available to the system. For more information on this storage system, head over to the official SQLite web site at http://www.sqlite.org and look through their documents. You can also check out the Safari-specific implementation information at http://developer.apple.com/library/safari/ipad/#documentation/iPhone/Conceptual/SafariJSDatabaseGuide/UsingtheJavascriptDatabase/UsingtheJavascriptDatabase.html

So, once I learned that I could store very large amounts of data per row, I began concocting a plan to use web services to deliver data URI's of the images I needed to offline-stored web apps that would then store the base64-encoded data to the database for future offline use. For several days I was plagued with how to execute cross-site requests to pull back the images since the web app would be used offline. This was resolved by using JSON-P calls from the jQuery library to SOAP-based web services. If you haven't taken a crack at using this yet, you simply must. It was truly easy to do and several times had me shaking my head thinking, "This can't be this easy," but it was. It finally seemed that the strengths of each component were going to work in concert to perform beautifully. I should have known better, right?

You Should Know Better
After reading so much on the web from people that seemed to be asking my questions and getting, presumably, good answers, I didn't think I had left anything to guess work. What I hadn't anticipated, however, was that there would be so many people out there ready to provide answers that didn't really have the knowledge in practice to be qualified to provide those answers. So, when I read overwhelming feedback that indicated that you could store more than 50 MB of data in Safari and, later, that the secret to beating that limit was to break it up into multiple databases, I was feeling pretty sure of myself.

The Breaks
The first big thing to break was the single database at 50 MB. When I hit the limit, the insert just fails silently. Despite having error callbacks, it just doesn't insert and silently fails the calling routine. This lead to "neverending syncs" - a problem I faced using the localStorage caching mechanism in my last go-round. While I likely could have used a timer such as setInterval() to look for progress to cease and signal a timeout, it wasn't worth it because no amount of error handling was going to give me more than 50 MB of storage in that database on iOS.

Ok, so the next big break happened when I followed statements in multiple boards suggesting that the 50 MB limit was a per-database limit and that a somewhat-elaborate scheme of managing multiple database files could allow you to get around this roadblock. Armed with this, I recreated the data management layer to account for a master database that cataloged the various child databases being created and provided for simplified storage tables in each. It didn't take long, however for this data architecture change to prove just the opposite. At present, no amount of stringing together databases will work because there is a per-site limit in Mobile Safari of 50 MB, not a per-database limit. At this point, I was even more frustrated as I felt that I had made no more progress than back when I was using localStorage caching to handle the dirty work. And I was right.

This whole time I had been using PhoneGap's javascript include file as a sort of shortcut for some things. I knew it wasn't "properly" being used as it is intended to perform Objective-C callbacks from the wrapping/parent application created in Xcode and, since I wasn't using Xcode to produce this as a native application at all, it was really just "hanging around" in my project. Then I started to think that surely PhoneGap had gotten around this problem entirely since it was a native application calling SQLite and not restricted to the 50 MB limit that Mobile Safari was imposing upon me. The answer, it seemed, was to wrap this puppy up as PhoneGap had intended and push this to the Apple app store. I mean, the problem wasn't in Android so I didnt really need to build for it, but I *could* using PhoneGap if I wanted to. Why not create real native apps for all the devices?

So, with much excitement (and no pre-reading, I might add) I downloaded the recent 1.0 release of PhoneGap, Xcode 4.1, and created a new project. I followed the instructions on the PhoneGap web site for getting started and had a base project in no time. I followed the details of doing a first build (knowing it would fail) and then transplanting the www folder into the project. I copied my HTML and JavaScript files into the folder and built. Everything built perfectly and I deployed to the iPad simulator that comes with Xcode.

After some minor bug fixes that are caused by going to real use of PhoneGap's JavaScript files instead of just the lip-servicing I was doing as a web app (such as converting alert() functions to use navigator.notification.alert() syntax) I got past the introduction screen and into the databasing "meat" of the application. I initiated a sync and watched the progress bar edge over. And then it stopped. I was syncing a medium-sized presentation that was under 50 MB and had an empty database. That being said, the sync (which also saves to the database as it progresses) stopped when barely into the process. So, I reset the simulator to a fresh state and tried again with the same result.

After adding some debugging messages and console write-outs, I determined that about 5 MB of data was being read and written to the database when it starts to fail. With a heaping scoop of skepticism that this was more than a coincidence, I went head-first into Google and looked for answers. Sure enough, there was plenty of material to support what I was seeing: PhoneGap native applications have a 5 MB limit on the SQLite databases you create. After several iterations of, "How could this be?" I felt the wind being completely let out of my sails. I had worked a long day and it was now close to 11 PM and I had not taken a break for dinner yet. So, I hit the nearest Steak n Shake hoping that a Frisco Melt might at least eliminate one of my immediate woes.

Davide Saves The Day
Back to work and more Google searching in hopes of saving what sanity was left. I poured through a lot of links (some of which were blocked as "hacking sites" by our enterprise web filtering system) and was losing hope in 15 minute increments. That is when I came across a simple GitHub entry (PhoneGap-SQLitePlugin) for a PhoneGap plugin specifically targeting SQLite quota limits and my spirits were immediately raised. Italian IT engineer Davide Bertola created this plugin for the exact same reason I was searching for it. While the plugin isn't considered "complete" by his own admission, it was certainly feature-complete enough to provide the needed boost to my project.

The plugin took a little help to get compiled into the project as many of the "# include" statements follow a different syntax in Xcode 4.x than in the 3.x version and, at that time, there was very little instruction on how to implement the plugin successfully. In my hour of need, however, I contacted Davide via GitHub and was happy to have received a response about an hour later. His assistance mixed with some research into postings regarding PhoneGap plugins in the new Xcode led me to straighten things out the next morning.

Using the new login, I created some JavaScript code that used the built-in patching function of the plugin to patch the database created using normal openDatabase() calls. This essentially sets the quota to a ridiculous (and impossibly large) size and keeps it from failing when I stuff more and more data into it. The only downside right now is that when a person first installs the application, it goes through the patching routine and the application must be restarted to realize the new and monstrous quota from the patching process. I hope to keep working with the author of the plugin to see if there are other things we can do to make things even easier.

You may wonder how Android did throughout these trials with iOS and it is worth noting that Android does not presently seem to be bothered by the limit though, at the time of this writing, I have not probed for an actual limit and am assuming the theoretical limit of "space available" to be an absolute maximum only. I also find myself mentioning Mobile Safari a lot when I am talking about these limits. It may escape a reader how specific this is. The full version of Safari on a Mac does not possess the same limits as Mobile Safari. While I recognize this may cause some readers to exclaim, "Duh!" I also think this is important to underscore. So, if you are writing specifically to target Chrome or Safari on a full desktop or laptop then you are good to go. But, honestly, who can afford to do that?

Final Roundup
  • As of iOS 4.3, Mobile Safari has a 50 MB SQLite limit that is per-site, not per-database
  • PhoneGap (default) will support only 5 MB of SQLite storage
  • PhoneGap plus the SQLite Plugin compiled into a native iOS app is a quick-win for some cases to allow mega-storage while reusing your web app
  • You can have a native app on iOS and use the same base web app code for other platforms where quotas are seemingly unenforced or not present
  • While a lot of developer help on the web is great (thanks again to Davide Bertola), you (read: I) can be foolishly trusting people that have not put theory into practice


andy said...

Nice post! Thanks for sharing.

What would have been helpful would be a description of the changes to be made to get the plugin work in Xcode 4.x (or even a sample project).

Matt Ray said...


That's fair. Only a couple of things stand out as changes I had to make from the normal process:

1. I had to drag the plugin files into the project. This is covered in PhoneGap's documentation on using a plugin.

2. I had to do a build and then look for headers that the compiler was complaining about and change them to reference the <> style headers instead of the existing style of "#include 'xxx/yyy.h'".

3. The plugin has to be registered with PhoneGap's PLIST file (this information is in the README file with the plugin).

4. Linked libsqlite3.dylib by using some of the instructions located at:

5. Keep reading the errors generated for clues. This obviously isn't an exhaustive list, but I am happy to help track down bugs and repost findings here if asked.


UOW - FYP - Group SS09/4B said...

Was your app approved by Apple after using this plugin?

UOW - FYP - Group SS09/4B said...

Thanks for the post.

I just wonder if your app was approved by Apple after using this plugin?

I just want to make sure before implementing it.


Matt Ray said...

Great follow-up question and you will be happy that the answer is YES. I have had 2 revisions approved as well and am using this in 2 new applications I have been contracted to write. If anything else comes up, ask away!

karthik said...

I tried the plugin . But it is not working . I followed all the steps. I tried with given javascript sample ,but it is not working . I haven't used Lawnchair Adapter...

Matt Ray said...

karthik - You shouldn't have to use LawnChair, but I am aware of its existence. When you say it isn't working, can you provide some more detail? I am happy to help you out if you can provide some specifics.

Matt Ray said...

Just a quick update: the original repo for the adapter has been semi-abandoned, but a combined effort by the original author and some others is available for Cordova/PhoneGap at:

Voice Comments