HTML5 Web SQL Database Concerns

Question

My company is currently transitioning to a new architecture, as well as a model that provides services to mobile devices. Our application was traditionally web based (HTML5/CSS3/JS/PHP/MYSQL), so we want to be able to port it to mobile platforms without having to reinvent the wheel (Cordova), as well as to desktops in a standalone fashion (AppJS) so that we don't have to worry about browser-dependent bugs. We are also transitioning away from PHP to NodeJS to make this more feasible.

The problem is that our users NEED the ability to use our application offline, with no real limit on how long or how much data they can store before "syncing" it to our servers.

With AppJS, this isn't an issue because they can store as much data as needed within a sqlite database. However, I have discovered the issue regarding a 5MB quota for WebSQL data and cordova/phonegap. This presents obvious technical challenges regarding our business needs.

I am aware that there are a couple of plugins that enable you to use sqlite data. The original sqlite plugin (https://github.com/davibe/Phonegap-SQLitePlugin) lacks documentation for android, and the other (https://github.com/chbrody/Cordova-SQLitePlugin) requires me to specify a data limit which obviously isn't possible. I am therefore left with very few options, one being to split the databases into multiple 5MB segments which are controlled by a JS wrapper.

Can you feasibly split larger datastructures into multiple 5MB websql databases with no issues across iOS and Android?

Is there a limit on the total number of websql databases on iOS and android?

Are there any other options that you would recommend we look at? We need to be able to use our existing HTML5 and CSS for the frontend!

EDIT

Our original architecture never really worked with tablet devices. This is something we are looking to remedy with cordova, as well as building a more stable solution. Originally, our offline product ran on a Server2Go stack that had all sorts of platforming issues because of the various applications running on the clients machine

Abbasid

How did you solve this in the original browser application? – Elayneelazaro

On the original browser application, we were using a Server2Go stack to actually "host" the application on their machines (server2go-web.de). This caused all sorts of issues (mainly with running mysql locally. WAMP wasn't designed for production, nor are Apache/MySQL,PHP designed to run on a platform that constantly changes its configuration since we have no control over the software that the client chooses to install that may conflict with this stack), and is one of the reasons we are switching our architecture. – Abbasid

How about IndexedDB. It does not have limit. – Stab

Answer 1

For the android platform, you could create a native app, and then use a webview to display the front end (if its really that important to keep it HTML), then use a javascript bridge to allow the content in the WebView to interact with your native app.

From what you have described about the application though, I think you are better off actually creating a native application. As you say in your comment, one of the reasons you are switching your platform is because of lack of control. Why then immediately re-open this can of worms by introducing all sorts of hybrid app framework into your stack.

Long story short, you will have full control (and quite likely, better product) if you just go with the native approach.

Elayneelazaro

This is something that I am currently looking at. The stakeholder wants to be able to reuse the existing HTML/CSS wherever possible, and all of our developers are familiar with Javascript. Our entire new platform runs javascript (AngularJS for the frontend, NodeJS for the backend) so it would be great if we could find a solution that uses JavaScript rather than Objective-C or Java. – Abbasid

I hear you, this is a very very common scenario in mobile development. Business sponsors don't want to throw resource at porting already existing code, but then end up spending almost as much resource creating a unholy storm-p type machine to make it work with "what we have"(+god knows how many pieces of duck tape :( Good luck with the project though, hope it wont be too painful. – Elayneelazaro

Really, the only issue here is Apple. Their stringent QA stuff creates all sorts of (what seems like) unnecessary technical challenges. Android is easy, but because Apple owns such a large majority of the table market and our clients obviously will want to use tablets, we have to conform to their (ridiculous?) specifications. – Abbasid

but a hybrid app would most likely go though the exact same QA process? #12068692 – Elayneelazaro

Yes, I know. That's the problem, isn't it? I would love to simply be able to port NodeJS straight to objective c and build a wrapper. That could another product in of itself – Abbasid

Ok, I lost track of what you want you are actually looking for mate (i thought you where exploring phonegap sqlite plugins as potential solutions, these are hybrid apps) :D And i got a meeting in 5minutes. Good luck finding a solution that suits you and your team :) – Elayneelazaro

Answer 2

Generally speaking, I recommend using lawnchair so that you can be implementation agnostic. http://brian.io/lawnchair/adapters/

Can you feasibly split larger datastructures into multiple 5MB websql databases with no issues across iOS and Android?

Before I knew about lawnchair, I implemented this using WebSQL, which I kinda regret. WebSQL databases can grow beyond 5MBs. I'm not sure what the true max is, but I've set my Phonegap app to 100 * 1024 * 1024 and haven't had any issues on Honeycomb Android.

For iOS you could use SQLite database on PhoneGap / iOS - More than 5mb possible.

Midden

For 3rd wrapper were used, I will go with dev.yathit.com/api-reference/ydn-db/storage.html it has better performance and support IndexedDB, websql. – Stab

@KyawTun Dude you're linking to your own site, no wonder you'll go with it. – Midden

Answer 3

To get a truly cross-platform, cross-browser relational solution that will work offline and have the necessary storage capacity, I would suggest going with: SequelSphere

Since it is a 100% JavaScript solution, it should be usable in each of your configurations without requiring a different codebase for each. Furthermore, it uses IndexedDB (if available, and then LocalStorage) to store its data in relational tables. I believe most browsers do not have a size limit on the storage amount of IndexedDB. I think Firefox may have an initial 50MB limit per the following: Max size in IndexedDB.

Also, there is a new feature called "Change Trackers" that significantly help in the data synchronization process by tracking changes (inserts/updates/deletes) to tables, and then reporting them to you when you desire.

As an extra added bonus, unlike WebSQL, SequelSphere is VERY JSON friendly. Creating tables couldn't be easier:

db.catalog.createTable({
    tableName: "EMPL",
    columns: [ "EMPL_ID", "NAME", "AGE", "DEPT_ID" ],
    primaryKey: [ "EMPL_ID" ],
    data: [
        [0,"Bob",32,0],
        [1,"John",37,2],
        [2,"Fred",28,1]
    ]
});

Querying the data is easy:

var res = db.query("SELECT name, age, dept_id FROM empl WHERE Dept_ID = 2");

Inserting / Updating / Deleteing data is easy:

db.insertRow("empl", [3, "Sue", 26, 2]);
db.updateRow("empl", [3, "Suzy", 26, 2]);
db.deleteRow("empl", [3, "Suzy", 26, 2]);

One thing to be aware of: Since this will be used in an offline application, make sure you set the "Persistence Scope" of either the entire Catalog, or each Table to be "SCOPE_LOCAL":

db.catalog.setPersistenceScope(db.SCOPE_LOCAL);
//  -or-
db.catalog.getTable("empl").setPersistenceScope(db.SCOPE_LOCAL);

• Documentation on the basics: SequelSphere Basics
• Documentation on "Local Storage" support: Local Data Storage and Persistence
• Here is the API: SequelSphere API

If you have any questions, just email the support for SequelSphere.

For complete transparency, I am part of SequelSphere, and it does seem to answer your question very well... ;)

Macroclimate

Looks nice, but I can't find a cost anywhere on the website. Just a form to request a price. Not ideal... – Midden