Pre-packaging database with HTML5 Mobile Application

I was discussing with a couple of colleagues of mine yesterday about a HTML5 mobile application that we are developing, and one of the requirements was to pre-package database with the application. A few months back I had created an application that did just that. I thought the solution might be of interest to some of the readers of this blog.

Mobile (or for that matter non-mobile) browsers can create databases for your HTML5 applications. HTML5 provides APIs to create and access the database. But how do you pre-package the database?

The solution is not really to pre-package the database, but package data that you want a database to initialize with. I don’t think you can really package a SQLite database and ask browser to use it for your HTML5 application. You need to create database and tables when the application is run the first time and then load data packaged with the application. Let’s say we want to create a table called ‘person’ and want to populate this table when the application is run the first time.
To keep the table simple, we will create only three fields in the table – id, name and picture_file. We could create a blob field to hold pictures in the table, but that will unnecessarily duplicate the data, one that we pre-package and another we insert in the table. So we will just save the file name in the database. We will use JSON to specify initial data, because it would be easy to convert that data to JS objects.

Let’s say we want to initialize the table with two records. So we will create an array with two objects in json_data.js

[
	{
		id:1,
		name:"Person1",
		picture_file:"person1.jpeg"
	},
	{
		id:2,
		name:"Person2",
		picture_file:"person2.jpeg"
	}
]

In the app.js we will write JS code to create and access database –

//app.js
db = null; //Reference to open database
tableName = "persons"; //Table name
_onDBInitializedCallback = null; //Callback function after DB is initialized

function openDB()
{
	db = window.openDatabase("persons", "1.0", "Persons DB", 1000000);
	//We will use a flag in localStorage to check if database is already
	//populated with pre-packaged data.
	if (localStorage.tablesCreated == undefined || localStorage.tablesCreated == "false")
	{
		//Database is not initilized. Create tables and inserts initial records
		//specified in _data/json_data.js
		createTable();
	}
	else if (_onDBInitializedCallback != null)
	{
		//Person table is already created and initilized. 
		//Call callback function.
		_onDBInitializedCallback();
	}
}

//Reads data from json_data.js and return aray of Person objects
function getPackagedData()
{
	//Get data from json_data.js using XHR
	var httpReq = new XMLHttpRequest();
	httpReq.open("GET","_data/json_data.js",false);
	httpReq.send();
	//Convert string to JavaScript objects using eval
	return eval(httpReq.responseText);
}

//Create Person table
function createTable()
{
	db.transaction (function (tx){
		var sql = "create table if not exists " + tableName +
			" (id integer, name text, picture_file text)";
		tx.executeSql(sql);
	},
	onError,
	initializeTable);
}

//Inserts records in Person table after reading 
//initial data from json_data.js
function initializeTable()
{
	var persons = getPackagedData();
	insertPerson(persons,0);
	localStorage.tablesCreated = "true";
}

//Inserts person at given index from persons array
function insertPerson (persons, index)
{
	//Checkif we have inserted all records
	if (index >= persons.length)
	{
		//Database is initialized. Call callback function
		if (_onDBInitializedCallback != null)
		{
			_onDBInitializedCallback();
			return;
		}
	}

	var person = persons[index];

	db.transaction(function (tx){
		//Got transaction
		var sql = "insert into " + tableName + "(id,name,picture_file) values (?,?,?)";
		tx.executeSql(sql,[person.id, person.name, person.picture_file]);
	},
	onError,
	function () {
		//insert next person in persons array
		insertPerson(persons,++index);
	});
}

//DB error handler
function onError(err)
{
	if (typeof err.message != 'undefined')
		err = err.message;
	alert(err);
}

//Get all persons from Person table. 
//Calls callbackFunc with array of person object as argument
function getPersons(callbackFunc)
{
	db.transaction(function (tx){
		var sql = "select * from " + tableName + " order by name";
		tx.executeSql(sql,null, function(tx, result) {
			var persons = new Array();
			var rows = result.rows;

			for (var i = 0; i < rows.length; i++)
			{
				var person = new Object();
				person.id = rows.item(i).id;
				person.name = rows.item(i).name;
				person.picture_file = "_data/images/" + rows.item(i).picture_file;
				persons.push(person);
			}

			if (callbackFunc)
				callbackFunc(persons);
		})
	},
	onError,
	null);
}

To test DB functions we have written in app.js, we will create index.html, in which will open the database, wait till it is initialized and then call getPersons function to fetch records from the database and then display images and names of persons in a table –

<!DOCTYPE html>
<html>
	<head>
		<title>Example - Pre-packaged HTML5 DB Demo</title>
		<script src="app.js"></script>

		<script>
			//Specify function to call after database is initialized
			//_onDBInitializedCallback is declared in app.js
			_onDBInitializedCallback = function onDBInitialized() {

				getPersons(function(persons){
					var buf = "";
					var tableElm = document.getElementById("personsTable");
					for (var i = 0; i < persons.length; i++)
					{
						var row = "<tr><td>" +
							"<img src='" + persons[i].picture_file + "'></img></td>" +
							"<td>" + persons[i].name + "</td></tr>";
						tableElm.innerHTML += row;
					}
				});
			}

			//Open the database
			openDB();
		</script>
	</head>
	<body>
		<table id="personsTable">
		</table>
	</body>
</html>