Mobile native apps storage using HTML5

2012-08-22T06:25:00.002-07:00

In this article I will speak about the database storage capabilities in HTML5 and how to enhance the native mobile app creation using them.

As you maybe already know, we have quite a pack of frameworks hanging around that allow us to generate a native application using HTML5, CSS3 and Javascript. The apps created using those frameworks can be uploaded to their respectives app markets like any other native application.

Here you have some of them:
Phonegap, appcelerator, redfoundry, rhomobile, appmobi, anscamobile

When we talk about the HTML5 and CSS3 functionalities one question comes often: Is it crossbrowser ? With the html5 to native frameworks you are using the inner browser of the mobile OS to encapsulate the html5 application so it runs like any other native app.

The problem of crossbrowsing is nearly solved in this context for 86% of the market as iOS and Android are leading and they use webkit browsers !

Here you can check the statistics regarding mobile phones usage:


22 august 2012 - http://www.netmarketshare.com/mobile-market-share

Despite there is still a 14% that uses other kind of browsers we can be sure of the compatibility as we release each version in a platform specific compiled package.

In most cases we aim the two leading platforms iOS and Android, both using a webkit based browser.

Client side storage:

Cookies:

There are different types of storage systems on browsers.
The most well known is the cookie storage. It has been used for a long time but it is quite restrictive as it only allows to store pairs of key-value with a 4ko size limit.

Another disadvantage is that cookies are send using http headers and that slows down the website. Cookies are generally used to storage an identifier string and the data is in fact stored in a distant server.

But other storage systems are appearing that allow us to go farther: Web storage, Index Database, Web SQL database and File storage.

Web SQL database:

The web SQL database system is implemented by all webkit browsers ( Android and iOS default browsers ). This dabase system uses SQLite, a well known relational database ( almost the same as MySQL ), that will allow you to manage information using joints, and other interesting functions of the SQL.

This SQL db uses transactions. A transaction allow to execute one or more sql sentences and if there are any error during the execution, to rollback to the state before the transaction.

As you allready should know, lll the SQL management is done inside a <script> tag, using javascript.

To create a database or to connect to an allready created database we have to call the openDatabase method.

openDatabase([database Name],[Version],[Description],[Size], [creationCallback] );

The openDatabase method is used to open a database if it already exists or to create it if it doesn't exist.

Above method takes five parameters:

Database name
Version number
Text description
Size of database
Creation callback

The creation callback is only executed if a database has been created, and it's optional.

example:

var db = window.openDatabase("Database", "1.0", "PhoneGap Demo", 200000);

To execute SQL statements you should call a transaction. The transaction has a single function argument that executes one or many SQL statements using executeSql([SQL statement]);

example:

db.transaction(function (tx) {
       tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
       tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "foobar")');
       tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "logmsg")');});

If for any reason in the third operation there an error happens, the transaction won't be executed at all, rolling back the database to the state where it was before this trasaction. Great isn't ? This is one of some benefits from SQL databases.

Now let's get things more interesting, we know how to create a database and how to create tables, insert and update data, but what about getting data from it ?

The "SELECT" SQL statement is used as we have seen before, as a parameter of the executeSql function, but we need to add to more parameter to receive the rows from the database.

executeSql([SQL statement],[],[Select return function]);

full exemple:

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024); db.transaction(function (tx) {

          tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "foobar")');
          tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "logmsg")');
});

db.transaction(function (tx) {
          tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
                var len = results.rows.length, i;
                msg = "<p>Found rows: " + len + "</p>";
                document.querySelector('#status').innerHTML += msg;
                for (i = 0; i < len; i++){
                     alert(results.rows.item(i).log );
                }
          }, null);
});

This function will show the content of each column of logs table inside the #status tag. This cover all the basic database functionalities you can do with html5 SQL database.

Notice that this SQL system is to be used for relative complex database structure, other simpler structures can use the "web storage" ( with a better compatibility ) or a NoSQL database like the Indexed db API (still on it first steps, but really promissing) , or even the file storage system ( only avalaible on chrome, Android ) that allows the storage of big data structures ( files and folders ).

With this information and examples you should be able to start using SQL databases inside your native mobile apps so what are you waiting ?

Useful links, some used to write this post:

Here you have the Phonegap ( aka Apache Cordova ) framework and their SQL database tutorial

Another tutorial with more specific explanations of the SQL functions: http://www.tutorialspoint.com/html5/html5_web_sql.htm

You can also find interesting the article from the W3C ( that announce they have stopped to maintain the specification) http://dev.w3.org/html5/webdatabase/

Other interesting resources: http://www.html5rocks.com/en/tutorials/offline/whats-offline/

Logicdesign Developer

Mobile native apps storage using HTML5

Client side storage:

Cookies:

Web SQL database:

openDatabase([database Name],[Version],[Description],[Size], [creationCallback] );