Tutorial 2: Supporting both IndexedDB and WebSQL on a cross platform web app

by Matt Andrews,

This is part 2 of a tutorial series on how to make an FT style offline web app. If you haven’t already, read part 1 first.

We left off last time with a severely limited, but working, offline HTML5 web app. This time we will aim to address some of the concerns that were raised at the end of the last tutorial.

We will keep the core functionality of the app the same – the same simple RSS reader – and instead focus on making it faster and making it work for everyone, including users of Firefox, IE10 (including web apps formerly known as Metro style), and even old pre-HTML5 browsers, such as IE6.

This demo app will continue to be based on the same principals of maximum ubiquity and brevity and so will continue to use jQuery and PHP and will require no special server configuration. As before this tutorial intentionally avoids going into detail on particular technologies and instead attempts to give a high level overview on how, with the fewest lines of code and in the shortest amount of time, various technologies can be brought together to achieve the goals we have set out to solve. This is purely about making the best of what we have available in real browsers today and delivering great experiences on the web platform.

As always, the full code is up on GitHub.

Getting Started

Start by cloning (or downloading) the GitHub repository from Part 1.

git clone https://github.com/matthew-andrews/basic-offline-html5-web-app.git

New requirements

  • A minimised delay between a user clicking a button and a page being displayed.
  • Fully support Firefox, Internet Explorer 10, achieve better performance on Chrome as well as continue to support all the platforms supported by the first tutorial (which included Android, Blackberry Playbook, iPads and iPhones).
  • Support every other browser as well as we can – falling back to being a normal website on older browsers.
  • Users of the app we made in the previous tutorial should be able to seamlessly start using the new app.

Housekeeping

To overcome the tap-delay we’ll use an FT Labs open source project: FT FastClick. We’ll put it in the folder /libraries/client, because soon we will need server libraries as well as client libraries, and keeping them separate will make our app easier to maintain. Add the following files:

/libraries/client/fastclick.js Download
/source/datasources/network.js create blank
/source/datasources/indexeddb.js create blank
/source/datasources/websql.js create blank

And delete this one:

/source/database.js This file will be replaced by the three Javascript wrappers around the three data source technologies available to browsers (IndexedDB, WebSQL and live-loading from the network – traditional AJAX) which one will be chosen based on the capabilities of the browser being used.

Faster clicks with FT FastClick

At the end of the last tutorial I described the 300ms delay between the user tapping a link and the browser responding. (It’s waiting to see if you are going to double tap – if you do, it knows you want to zoom in instead of clicking). Since we don’t want the user to zoom right now, we can use FT Fastclick to remove that delay.

To start using FT FastClick in this demo app:

  1. Ensure the latest version of FT FastClick is saved at /libraries/client/fastclick.js.
  2. Add it to /api/resources/index.php. The top of my file now looks like this:-
    <?php
    // Concatenate the files in the /source/ directory
    // This would be a sensible point to compress your Javascript.
    $js = '';
    $js = $js . file_get_contents('../../libraries/client/fastclick.js');
    $js = $js . 'window.APP={}; (function (APP) {';
    $js = $js . file_get_contents('../../source/application/applicationcontroller.js');
    $js = $js . file_get_contents('../../source/articles/articlescontroller.js');
    $js = $js . file_get_contents('../../source/articles/article.js');
    ... etc ...
  3. Inside /source/application/applicationcontroller.js, make two changes. First, declare the variable that FT FastClick will use at the top of the file, so we can maintain a reference to it and disable it later if required. The top of applicationcontroller.js is now:
    APP.applicationController = (function () {
      'use strict';
    
      var fastClick;
    
      function offlineWarning() {
        alert("This feature is only available online.");
      }
    
      function pageNotFound() {
        alert("That page you were looking for cannot be found.");
      }
    
    ... etc ...
  4. Second, add the single line that sets up FT FastClick for the project (fastClick = new FastClick(document.body);) inside the start function. The start function is now:
    function start(resources, storeResources) {
      APP.database.open(function () {
    
        // Listen to the hash tag changing
        $(window).bind("hashchange", route);
    
        // Set up FT FastClick
        fastClick = new FastClick(document.body);
    
        // Inject CSS Into the DOM
        $("head").append("<style>" + resources.css + "</style>");
    
        // Create app elements
        $("body").html(APP.templates.application());
    
        // Remove our loading splash screen
        $("#loading").remove();
    
        route();
      });
    
      if (storeResources) {
        localStorage.resources = JSON.stringify(resources);
      }
    }

IndexedDB: Firefox and Internet Explorer 10

We’re firm believers in progressive enhancement but implementing a feature so that it can work with two different client side database technologies is tricky. We could use a polyfill to make WebSQL behave like IndexedDB but this has some significant performance implications for WebSQL (still required for iPhone, iPad, Playbook, amongst others) and it won’t help old browsers such as IE 6-9 or old non-HTML5 mobile web browsers that don’t support WebSQL either. So, for maximum compatibility, we will design a new, simple common API that can use any of IndexedDB, WebSQL or the network (via ajax) as a data source.

For this common API we will implement the following methods on each store:

Method name (Parameters) Notes
start(successCallback) Perform any required data source set up. We need have a success callback because initialising some data sources (indexedDB) may require asynchronous calls.
selectOne(model, id, successCallback, failureCallback) Fetch a single data object from the data store for a type of data. In our demo app the model will always be an article.
selectAll(model, successCallback, failureCallback) Fetch all data objects of a specified type from the data store.
insertInto(model, data, successCallback) Insert data into a data store.
deleteAllFrom(model, successCallback) Clear all the data of one type from a data store.

Here’s how we can implement those methods for the network store:

/source/datasources/network.js

APP.network = (function () {
  'use strict';

  function start(successCallback) {
    if (successCallback) {
      successCallback();
    }
  }

  function insertInto(model, data, successCallback) {
    if (successCallback) {
      successCallback();
    }
  }

  function deleteAllFrom(model, successCallback) {
    if (successCallback) {
      successCallback();
    }
  }

  function selectAll(model, successCallback, failureCallback) {
    $.ajax({
      dataType: 'json',
      url: 'api/' + model,
      success: successCallback || function () {},
      type: 'GET',
      error: failureCallback || function () {}
    });
  }

  function selectOne(model, id, successCallback, failureCallback) {
    $.ajax({
      dataType: 'json',
      url: 'api/' + model + '/?id=' + id,
      success: successCallback || function () {},
      type: 'GET',
      error: failureCallback || function () {}
    });
  }

  return {
    start: start,
    insertInto: insertInto,
    deleteAllFrom: deleteAllFrom,
    selectAll: selectAll,
    selectOne: selectOne
  };

}());

This is just a very thin wrapper around the jQuery.ajax method. As far as our app is concerned insertInto and deleteAllFrom don’t make sense if we don’t have a local database because those functions are concerned with caching data for offline use – but we have to implement them to ensure network and Websql/IndexedDB all share the same API, so here calling either of those methods just calls their successCallback straight away.

Because we need to be able to select individual articles (the selectOne method) we need to update the /api/articles/index.php file.

<?php
$rss = new SimpleXMLElement(file_get_contents('http://feeds2.feedburner.com/ft/tech-blog'));

$articleId = intval(isset($_GET['id']) ? $_GET['id'] : 0);
if ($articleId) {
  $xpath = '/rss/channel/item['. $articleId .']';
} else {
  $xpath = '/rss/channel/item';
}
$items = $rss->xpath($xpath);

if ($items) {
  $output = array();
  foreach ($items as $id => $item) {

    // This will be encoded as an object, not an array, by json_encode
    $output[] = array(
      'id' => $id + 1,
      'headline' => strval($item->title),
      'date' => strval($item->pubDate),
      'body' => strval(strip_tags($item->description,'<p><br>')),
      'author' => strval($item->children('http://purl.org/dc/elements/1.1/')->creator)
    );
  }

  if ($articleId > 0) {
    echo json_encode($output[0]);
  } else {
    echo json_encode($output);
  }
}

Moving on to the WebSQL data store:

/source/datasources/websql.js

APP.webSQL = (function () {
    'use strict';

    var smallDatabase;

    function runQuery(query, data, returnFirst, successCallback) {
        var i, l, remaining;


        if (!(data[0] instanceof Array)) {
            data = [data];
        }

        remaining = data.length;

        function innerSuccessCallback(tx, rs) {
            var i, l, output = [];
            remaining = remaining - 1;
            if (!remaining) {

                // HACK Convert row object to an array to make our lives easier
                for (i = 0, l = rs.rows.length; i < l; i = i + 1) {
                    output.push(rs.rows.item(i));
                }
                if (successCallback) {
                    successCallback(returnFirst ? output[0] : output);
                }
            }
        }

        function errorCallback(tx, e) {
            if (console) {
          console.log("WebSQL error: ", tx, e);
        }
        }

        smallDatabase.transaction(function (tx) {
            for (i = 0, l = data.length; i < l; i = i + 1) {
                tx.executeSql(query, data[i], innerSuccessCallback, errorCallback);
            }
        });
    }

    function insertInto(model, data, successCallback) {
        var remaining = data.length, i, l, insertData = [];

        if (remaining === 0) {
            successCallback();
        }

        // Convert article array of objects to array of arrays
        for (i = 0, l = data.length; i < l; i = i + 1) {
            insertData[i] = [data[i].id, data[i].date, data[i].headline, data[i].author, data[i].body];
        }

        if (model === 'articles') {
            runQuery("INSERT INTO articles (id, date, headline, author, body) VALUES (?, ?, ?, ?, ?);", insertData, false, successCallback);
        }
    }

    function deleteAllFrom(model, successCallback) {
        runQuery("DELETE FROM " + model, [], false, successCallback);
    }

    function selectAll(model, successCallback) {
        if (model === "articles") {
            runQuery("SELECT id, headline, date, author FROM articles", [], false, successCallback);
        }
    }

    function selectOne(model, id, successCallback) {
        if (model === "articles") {
            runQuery("SELECT id, headline, date, author, body FROM articles WHERE id = ?", [id], true, successCallback);
        }
    }

    function start(successCallback, failureCallback) {
        try {
            smallDatabase = openDatabase("APP", "1.0", "Not The FT Web App", (5 * 1024 * 1024));
            runQuery("CREATE TABLE IF NOT EXISTS articles(id INTEGER PRIMARY KEY ASC, date TIMESTAMP, author TEXT, headline TEXT, body TEXT)", [], false, successCallback);
        } catch (e) {
            if (failureCallback){
                failureCallback();
            }
        }
    }

    return {
        start: start,
        insertInto: insertInto,
        deleteAllFrom: deleteAllFrom,
        selectAll: selectAll,
        selectOne: selectOne
    };
}());

This is based on the same code the we used in part 1 of the tutorial.

Finally, here’s the indexedDB one:

/source/datasources/indexeddb.js

APP.indexedDB = (function () {
	'use strict';

	var db, indexedDB, IDBTransaction, IDBKeyRange;

	function indexedDBError(event) {
		if (typeof console !== "undefined") {
			console.error("An error occurred", event);
		}
	}

	function insertInto(model, data, successCallback) {
		var transaction = db.transaction([model], IDBTransaction.READ_WRITE || 'readwrite'), store, i, request, total = data.length;

		function successCallbackInner() {
			total = total - 1;
			if (total === 0) {
				successCallback();
			}
		}

		transaction.onerror = indexedDBError;
		store = transaction.objectStore(model);
		for (i in data) {
			if (data.hasOwnProperty(i)) {
				request = store.add(data[i]);
				request.onsuccess = successCallbackInner;
				request.onerror = indexedDBError;
			}
		}
	}

	function deleteAllFrom(model, successCallback) {
		var transaction = db.transaction([model], IDBTransaction.READ_WRITE || 'readwrite'), store, request;
		transaction.onerror = indexedDBError;
		store = transaction.objectStore(model);
		request = store.clear();
		request.onerror = indexedDBError;
		request.onsuccess = successCallback;
	}

	function selectAll(model, successCallback) {
		var transaction = db.transaction([model], IDBTransaction.READ_ONLY || 'readonly'), store, request, results = [];
		transaction.onerror = indexedDBError;
		store = transaction.objectStore(model);
		request = store.openCursor();

		request.onerror = indexedDBError;
		request.onsuccess = function (event) {
			var result = event.target.result;

			// When result is null the end is reached
			if (!result) {
				successCallback(results);
				return;
			}
			results.push(result.value);

			// Weird to hack jslint
			result['continue']();
		};
	}

	function selectOne(model, id, successCallback) {
		var transaction = db.transaction([model], IDBTransaction.READ_WRITE || 'readwrite'), store, request;
		transaction.onerror = indexedDBError;
		store = transaction.objectStore(model);
		request = store.get(id);
		request.onerror = indexedDBError;
		request.onsuccess = function (event) {
			var result = event.target.result;
			successCallback(result);
		};
	}

	function start(successCallback, failureCallback) {

		// Protect ourselves inside old browsers
		try {
			indexedDB = window.indexedDB || window.webkitIndexedDB || window.mozIndexedDB || window.msIndexedDB;
			IDBTransaction = window.hasOwnProperty('webkitIndexedDB') ? window.webkitIDBTransaction : window.IDBTransaction;
			IDBKeyRange = window.hasOwnProperty('webkitIndexedDB') ? window.webkitIDBKeyRange : window.IDBKeyRange;

		} catch (e) {
			failureCallback();
		}
		if (!indexedDB) {
			failureCallback();
			return;
		}

		var version = 6,
		request = indexedDB.open("APPDATA", version);

		function installModels() {
			if (db.objectStoreNames.contains("articles")) {
				db.deleteObjectStore("articles");
			}

			// TODO This is strictly model logic, and ought not live inside the indexedDB library, should move.
			db.createObjectStore("articles", { keyPath: "id" });
		}

		request.onsuccess = function (event) {
			var setVersionRequest;

			db = event.target.result;
			version = String(version);
			if (db.setVersion && version !== db.version) {
				setVersionRequest = db.setVersion(version);
				setVersionRequest.onfailure = indexedDBError;
				setVersionRequest.onsuccess = function (event) {
					installModels();
					setVersionRequest.result.oncomplete = function () {
						if (successCallback) {
							successCallback();
						}
					};
				};

			} else {
				successCallback();
			}
		};
		request.onupgradeneeded = function (event) {
			db = event.target.result;
			installModels();
		};
		request.onerror = function (event) {
			alert("You have chosen not to use offline storage");
			failureCallback();
		};
	}

	return {
		start: start,
		insertInto: insertInto,
		deleteAllFrom: deleteAllFrom,
		selectAll: selectAll,
		selectOne: selectOne
	};
}());

A good resource for understanding indexedDB is on the Mozilla Development Network so I won’t go into details on how this works for this tutorial.

One thing to note though is that on the Firefox the browser asks the user for permission to open an IndexedDB database and if the user declines or cancels the prompt, the app should still be able to continue running. This is handled by request.onerror (notice this calls the failureCallback function, which we’ll implement inside /source/application/applicationcontroller.js). I’ve added a simple alert so that the user knows they have opted out of offline functionality. In a production app we might like to handle this more gracefully and perhaps even provide instructions on how to re-enable offline storage.

Now we need to update the resources api PHP file so that it is aware of the files we just created. (the old database.js file can also be deleted at the same time)

/api/resources/index.php

<?php
// Concatenate the files in the /source/ directory
// This would be a sensible point to compress your Javascript.
$js = '';
$js = $js . file_get_contents('../../libraries/client/fastclick.js');
$js = $js . 'window.APP={}; (function (APP) {';
$js = $js . file_get_contents('../../source/application/applicationcontroller.js');
$js = $js . file_get_contents('../../source/articles/articlescontroller.js');
$js = $js . file_get_contents('../../source/articles/article.js');
$js = $js . file_get_contents('../../source/datasources/network.js');
$js = $js . file_get_contents('../../source/datasources/indexeddb.js');
$js = $js . file_get_contents('../../source/datasources/websql.js');
$js = $js . file_get_contents('../../source/templates.js');
$js = $js . '}(APP));';
$output['js'] = $js;

// Concatenate the files in the /css/ directory
// This would be a sensible point to compress your css
$css = '';
$css = $css . file_get_contents('../../css/global.css');
$output['css'] = $css;

// Encode with JSON (PHP 5.2.0+) & output the resources
echo json_encode($output);

Now we can add code that makes the decision about which data store to use.

/source/application/applicationcontroller.js

APP.applicationController = (function () {
    'use strict';

    var fastClick;

    function offlineWarning() {
        alert("This feature is only available online.");
    }

    function pageNotFound() {
        alert("That page you were looking for cannot be found.");
    }

    function showHome() {
        $("#body").html(APP.templates.home());

        // Load up the last cached copy of the news
        APP.articlesController.showArticleList();

        $('#refreshButton').click(function () {

            // If the user is offline, don't bother trying to synchronize
            if (navigator && navigator.onLine === false) {
                offlineWarning();
            } else {
                APP.articlesController.synchronizeWithServer(function failureCallback() {
                    alert("This feature is not available offline");
                });
            }
        });
    }

    function showArticle(id) {
        $("#body").html(APP.templates.articleLoading());
        APP.articlesController.showArticle(id);
    }

    function route() {
        var page = window.location.hash;
        if (page) {
            page = page.substring(1);
        }
        if (page.length > 0) {
            if (parseInt(page, 10) > 0) {
                showArticle(parseInt(page, 10));
            } else {
                pageNotFound();
            }
        } else {
            showHome();
        }
    }

    function initialize(resources) {

        // Listen to the hash tag changing
        if ("onhashchange" in window) {
            $(window).bind("hashchange", route);
            
        // Support for old IE (which didn't have hash change)
        } else {
            (function () {
                var lastHash = window.location.hash;
                window.setInterval(function () {
                    if (window.location.hash !== lastHash) {
                        lastHash = window.location.hash;
                        route();
                    }
                }, 100);
            }());
        }

        // Set up FastClick
        fastClick = new FastClick(document.body);

        // Inject CSS Into the DOM
        $("head").append("<style>" + resources.css + "</style>");

        // Create app elements
        $("body").html(APP.templates.application());

        // Remove our loading splash screen
        $("#loading").remove();

        route();
    }

    // This is to our webapp what main() is to C, $(document).ready is to jQuery, etc
    function start(resources, start) {

        // When indexedDB available, use it!
        APP.indexedDB.start(function indexedDBSuccess() {
            APP.database = APP.indexedDB;
            initialize(resources);

            // When indexedDB is not available, fallback to trying websql
        }, function indexedDBFailure() {
            APP.webSQL.start(function webSQLSuccess() {
                APP.database = APP.webSQL;
                initialize(resources);

            // When webSQL not available, fall back to using the network
            }, function webSQLFailure() {
                APP.network.start(function networkSuccess() {
                    APP.database = APP.network;
                    initialize(resources);
                });
            });
        });

        if (storeResources && window['localStorage']) {
            localStorage.resources = JSON.stringify(resources);
        }
    }

    return {
        start: start
    };
}());

The code to decide which data store to use is inside start. We first attempt to set up an indexedDB database, then if that fails attempt to set up a WebSQL database then finally fall back to using the network. This enables us to get the best out of those browsers that support IndexedDB whilst continuing to be able to support all browsers that do not support these features.

Other changes:

  • I’ve refactored the code slightly so that much of the functionality that used to live inside start is moved to the new private function initialize – which will get run after whichever data source technology the app chooses to use is fully loaded;
  • and made a small change to the route function to force the value it passes to showArticle to be an integer.
  • To be able to run the app on IE6, which does not support the hashchange event, I’ve had to implement a short piece of code that runs every 100ms to see if the hash tag has changed. Also I’ve needed to change the check to see localStorage is available on the device to window['localStorage'].

Now all the groundwork is done, we can update the article model to use the new database API, like this:

/source/articles/article.js

APP.article = (function () {
    'use strict';

    function deleteArticles(successCallback) {
        APP.database.deleteAllFrom('articles', successCallback);
    }

    function insertArticles(articles, successCallback) {
        APP.database.insertInto('articles', articles, successCallback);
    }

    function selectBasicArticles(successCallback) {
        APP.database.selectAll('articles', successCallback);
    }

    function selectFullArticle(id, successCallback) {
        APP.database.selectOne('articles', id, successCallback);
    }

    return {
        insertArticles: insertArticles,
        selectBasicArticles: selectBasicArticles,
        selectFullArticle: selectFullArticle,
        deleteArticles: deleteArticles
    };
}());

The format in which a single article is returned has changed from an array containing a single article to an object. So one final update is needed to the article method inside /source/templates.js. Replace the article function with the code below:-

/source/templates.js

function article(articleData) {

  // If the data is not in the right form, redirect to an error
  if (!articleData) {
    window.location = '#error';
    return;
  }
  return '<a href="#">Go back home</a><h2>' + articleData.headline + '</h2><h3>By ' + articleData.author + ' on ' + articleData.date + '</h3>' + articleData.body;
}

Wrapping Up

The app is now responsive on touch enabled devices thanks to FT FastClick. It is also now able to work fully in Firefox and IE10, will perform faster on Chrome and Chrome for Android, and should work – albeit not offline – on older browsers such as IE6 (tested and working!). But we’re not done yet – it’s still not a website in a traditional sense: it doesn’t work if Javascript is switched off and it can’t be indexed by search engines. In next month’s article we’ll get a step closer to achieving this.

Finally, if you think you’d like to work on this sort of thing and live (or would like to live) in London, we’re hiring!

By Matt Andrews – @andrewsmatt on Twitter & Weibo.

Continue to part 3 – ‘Fixing’ the application cache with an iframe