The IT Ninja

HTML5 Offline and Online Web SQL tutorial.

One of the extremely cool feature of HTML5 is that of “local storage”. Local storage allows data to be saved on the clients computer. In HTML5 there are two options for local storage: “localStorage” and “WebSQL”.

In this tutorial we are going to focus on WebSQL. localStorage is only a simple Key,Value or KV store. This is perfect for some lightweight data needs, however for more complex data needs webSQL is the perfect fit. WebSQL is a full blown sqlite implementation that runs in the browser!

Web applications are excellent for many reasons, platform independence, centralised versioning, zero installation and deployment, etc. However they do suffer from one major problem: Connectivity.

How do you function when you have no internet connectivity? If a web application is designed to work “offline” then it is possible to resolve the issue of connectivity. Please note, when we say offline we are not talking about zero internet connectivity. Instead when we talk about offline we mean intermittent connection failure.

Tutorial Overview:

* Part 1: Offline and Online architecture explained
* Part 2: Front end UI and HTML
* Part 3: Web SQL and Ajax Functions
* Part 4: Local SQL functions
* Part 5: Buffer Logic and Buffer Manager
* Part 6: Network PHP Load and Configuration Function
* Part 7: Network PHP SQL Data access
* Part 8: Network SQL Functions

Part 1: Offline and Online architecture explained



The offline mode architecture design is depicted in the above diagram. The client interacts with the web application using the browser.

All network based actions are taken via AJAX calls to the network. During network failure, the application handles the request by “buffering” it. This is done by storing this data in “Local Storage” using WebSQL.

The results that are then displayed are also stored, this part is crucial to the design. This is known as the “Current State”. The Current State data is always stored locally.

While the user is interacting with the web application, a secondary background action occurs. At a set interval, a background AJAX action is taken to take one item off the buffer and added to the network. If this action is successful then the network returns the buffer ID (the local buffer record). The local buffer record is then deleted. If the action fails then it can be ignored and no local buffer record are deleted.

When a user starts the application, we must first check to see if the local buffer is empty. If it is empty this means that the network is up to date. The current data from the network should be “pulled down”. On the other hand if the buffer is not empty, then it means that the network is not yet up to date. In this case, the “local Current State Data” should be loaded.

In this manner, the application can work seamlessly with or without network connectivity.

Part 2: Front end UI and HTML

The front end UI is fairly straightforward and not the focus of this tutorial, below is a screenshot of the final UI.



It consists of three buttons, and a “debug” window. The debug window allows us to understand the application activity.

The HTML code is attached below:

Part 3: Web SQL and Ajax Functions

For this application we will be relying on two key technologies:

* WebSQL
* AJAX

We can wrap up these two key ingredients into Javascript functions, these will be very important as these will be used throughout the rest of the application.

Web SQL Function:

The Web SQL function takes 4 parameters, these are:

* sql: the sql query to be executed.
* data: the set of parameters associated with the above sql query, passed as an array.
* fn_callback: a function callback when the sql is executed.
* fn_Error: a function callback when the sql fails.



Ajax Function:

The Ajax function takes 4 parameters. these are:

* action: this is a string that represent the function call on the server end.
* data: json data that is associated with the network function call.
* fn_callback: a function callback when the network responds.
* fn_Error: a function callback when the network fails.



A finally some miscellaneous utility functions:

Part 4: Local SQL functions

All of our local database (Web SQL) functions are listed below:

* CreateBufferTable: this is part of the application initialisation, if this table does not exist then creates it.

The buffer table holds data for failed network transactions.

* CreateStateTable: this is part of the application initialisation, if this table does not exist then creates it.

The state table is crucial, it holds the current “state” of the web application, it is triggered when the screen is updated. This data is only “read” when the application is started up and the buffer is NOT empty.

* AddBufferItem: This adds the data to the local Buffer.

* DeleteBufferItem: Self explanatory
* GetBufferItem: Gets the oldest buffer item (to be pushed to the network)
* GetBufferCount: Self explanatory
* UpdateCurrentState: When page render is updated, the current “state” is saved
* GetCurrentState: This reads the current state.
* DeleteAll: Debug purpose, deletes all local data.

Part 5: Buffer Logic and Buffer Manager

Buffer Logic:
The entry point of the javascript code is “InitBuffer()”. This creates both the Buffer and State tables (if they do not exist).

Then the buffer is checked, if the buffer is not empty, the data is loaded from the State table. If the buffer is empty then the data is loaded from the network.

After this the buffer Manager is called, this simply runs in a loop in the background to clear the buffer.

Buffer logic



Buffer Manager



A closer look at the “ButtonClicked()” function:

This function is triggered when one of the buttons are clicked. It simply takes the current state (total number of times this button has been clicked), and increments it.

An Ajax request is made to the network to update this data, on fail it is buffered. the result is then updated on the screen and the “Current State Data” is also updated.

Part 6: Network PHP Load and Configuration Function

We need to define some database configuration for use in our project. The values below will need to be set to whatever your database settings are:



This section is server side, all calls are made to the jxNetwork.php script. The overall theory for this part is simple. All ajax calls made to this file contain two main querystring parameters:

* action: a string value that names a function to execute.
* data: json data that is url-encoded.

The pattern is fairly straightforward, the ajax is always going to call a function and possibly have some data that function should execute. Based on this rather than have a long winded mess of spaghetti code that entails lots of “switch statements” or “if then statements”. We can replace it with a single function, that will check to see if the named function exists, and if so call it and pass any data should it exists.

Part 7: Network PHP SQL Data access

To avoid ugly and messy spaghetti code we can create two simple “data access” functions. These will help remove duplicate code and help us write cleaner code, when calling the database.

There are two main functions:

* runSQL: this takes one parameter “query”. Which is the sql query to be executed. The returned value can be iterated using “mysql_fetch_array” function.

* runSQLParms: the exact same as above, however for queries that have parameters involved, this is a cleaner way of executing them. An array of parameters along with the sql query can be passed. The function escapes the parameters and then creates merges the parameters with the query to create the final sql query which is then executed.

Part 8: Network SQL Functions

This is the final part of this tutorial. The “loader” function simply call these functions and pass any data as a parameter (if it exists).

The functions use the “Data Access” functions as described in part 7. The functions are self explanatory:



That’s it! although this tutorial is targeted for advanced programmers, it hopefully should help others also.

As usual all the material for this project is available in the members section:

Project Files for Download