Offline Database On iPhone Using SQLite

I like to take my iTouch with me on my trips to New York City so I can listen to music during the long bus trip. I also copy my compiled HTML notes on New York City to my iTouch so I can consult it when I need to. Neither of those uses requires a wireless Internet connection. I can rarely get a wireless Internet connection while traveling so I don’t rely upon it.

Recently I have discovered an interesting way to increase the usefulness of my iTouch. It is possible to create offline web applications which remain functional even when you can’t find a wireless connection. These offline web applications can also make use of a database which will reside on the device.

Safari and Opera browsers support SQLite, a SQL database engine. Web applications can create databases which will be stored on the user’s computer. The iPhone and iTouch run the mobile version of Safari which includes SQLite so you can potentially store databases on your mobile phone or iPod. This gave me the idea to store a few databases on my iTouch with an offline web application to query the data. This would give me the ability to look up data without a wireless connection. There are a few database applications for the iPhone but this method is free and completely customizable.

To create a database, you just need to write a little JavaScript:

try {
    if (!window.openDatabase) {
        alert('not supported');
    } else {
        var shortName = 'mydatabase';
        var version = '1.0';
        var displayName = 'My Important Database';
        var maxSize = 65536; // in bytes
        var db = openDatabase(shortName, version, displayName, maxSize);

        // You should have a database instance in db.
    }
} catch(e) {
    // Error handling code goes here.
    if (e == 2) {
        // Version number mismatch.
        alert("Invalid database version.");
    } else {
        alert("Unknown error "+e+".");
    }
    return;
}
alert("Database is: "+db);

You can verify that the JavaScript created a database by opening Preferences > Security and clicking the Show Databases button in Safari:

Here you can see that I have a few databases under localhost from my experimentation on my local web server and a Books database from my business web site. A database is associated with a domain name so web sites cannot access each other’s databases. You may be wondering where these database files were created. I found them under C:\Documents and Settings\[username]\Local Settings\Application Data\Apple Computer\Safari\Databases\http_localhost_0/0000000000000001.db

After you create the database, you need to create your tables using some more JavaScript:

createTables(db);

function nullDataHandler(transaction, results) { }
function createTables(db)
{
    db.transaction(
        function (transaction) {
            /* The first query causes the transaction to (intentionally) fail if the table exists. */
            transaction.executeSql('CREATE TABLE people(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL DEFAULT "John Doe", shirt TEXT NOT NULL DEFAULT "Purple");', [], nullDataHandler, errorHandler);
            /* These insertions will be skipped if the table already exists. */
            transaction.executeSql('insert into people (name, shirt) VALUES ("Joe", "Green");', [], nullDataHandler, errorHandler);
            transaction.executeSql('insert into people (name, shirt) VALUES ("Mark", "Blue");', [], nullDataHandler, errorHandler);
            transaction.executeSql('insert into people (name, shirt) VALUES ("Phil", "Orange");', [], nullDataHandler, errorHandler);
            transaction.executeSql('insert into people (name, shirt) VALUES ("jdoe", "Purple");', [], nullDataHandler, errorHandler);
        }
    );
}

You are now ready to import data into your database table. If you have a lot of data you’ll probably want to download the sqlite3.exe tool from http://www.sqlite.org/download.html. This little program is a command line shell for SQLite. It can import data from text files and dump the database as SQL statements.

My Books database is a simple database I maintain to keep track of all the books I’ve read. I like to use it for experimenting with database technology because it is extremely simple. I keep this database in Access. To import the data into SQLite from Access, I exported the table into a comma separated values CSV text file. However, because I have a few commas in the titles of books, I changed the separator character to a semicolon. I also discovered that text values should be surrounded by single quotes, not double quotes. I used the following commands to import the data.

   1: sqlite> .separator ";"
   2: sqlite> .import Books.txt Books

NOTE: To make things easier, I copied the sqlite3.exe tool and my Books.txt file into the folder that Safari uses to store its SQLite databases.

The built-in Safari developer tools make it easy to verify that the database now has some data. Just open the Web Inspector and select the Storage button while the web site with the database is open:

You can also download the SQLite Manager extension for Firefox which gives you a complete tool for managing your SQLite databases. Firefox does not permit web sites to use SQLite but the browser uses it for a few internal databases. You can still use SQLite Manager to work with your Safari databases by browsing to the file.

These tools will not help you to get data into a database on the iPhone. To accomplish that I dumped the database to a file of SQL statements and then created a web page containing the JavaScript to run those SQL statements. This is not a very eloquent approach but it was the simplest way to get data into the database. I used the following commands in the sqlite3.exe tool to dump my database as SQL statements which I copied raw into JavaScript with a little editing.

sqlite> .output dump.sql
sqlite> .dump Books
sqlite> .output stdout

I then created a very simple web page to query the database and return the results in a table. Here is the JavaScript to query the database:

try {
    if (!window.openDatabase) {
        alert('not supported');
    } else {
        var shortName = 'books';
        var version = '1.0';
        var displayName = 'Books';
        var maxSize = 65536; // in bytes
        var db = openDatabase(shortName, version, displayName, maxSize);

        // You should have a database instance in db.      
    }
} catch (e) {
    // Error handling code goes here.
    if (e == 2) {
        // Version number mismatch.
        alert("Invalid database version.");
    } else {
        alert("Unknown error " + e + ".");
    }
}
//alert("Database is: " + db);
//createTables(db);

function searchDatabase() {
    var criteria = $("#criteria").val();
    var column = $("input[@name='column']:checked").val();
    db.transaction(
        function (transaction) {
            transaction.executeSql("SELECT * FROM Books WHERE " + column + " LIKE '%" + criteria + "%';",
                [], // array of values for the ? placeholders
                dataHandler, errorHandler);
        }
    );
}

function createTables(db) {
    db.transaction(
        function (transaction) {
            /* The first query causes the transaction to (intentionally) fail if the table exists. */
            transaction.executeSql('CREATE TABLE Books(BookNum INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Title TEXT NOT NULL, Author TEXT NOT NULL);', [], nullDataHandler, errorHandler);
        }
    );
}
function nullDataHandler(transaction, results) { }
function errorHandler(transaction, error) {
    // error.message is a human-readable string.
    // error.code is a numeric error code
    alert('Oops.  Error was ' + error.message + ' (Code ' + error.code + ')');

    // Handle errors here
    var we_think_this_error_is_fatal = true;
    if (we_think_this_error_is_fatal) return true;
    return false;
}
function dataHandler(transaction, results) {
    // Handle the results
    $("#results").empty();
    $("#results").append("<table id=\"resultsTable\" class=\"tableData\">");
    $("#resultsTable").append("<tr><th>BookNum</th><th>Title</th><th>Author</th></tr>");
    //var string = "The Books table contains the following records:<br /><br />\n\n";
    for (var i = 0; i < results.rows.length; i++) {
        // Each row is a standard JavaScript array indexed by column names.
        var row = results.rows.item(i);
        $("#resultsTable > tbody:last").append("<tr>");
        $("#resultsTable > tbody:last").append("<td>" + row['BookNum'] + "</td>");
        $("#resultsTable > tbody:last").append("<td>" + row['Title'] + "</td>");
        $("#resultsTable > tbody:last").append("<td>" + row['Author'] + "</td>");
        $("#resultsTable > tbody:last").append("</tr>");
        //string = string + row['BookNum'] + ": " + row['Title'] + ", " + row['Author'] + "<br />\n";
    }
}

The final task in this project was to get the web application to function while offline. This proved to be difficult because the manifest file must be received by the web browser with the content type text/cache-manifest. Unfortunately IIS already has the .manifest file extension registered for Silverlight. My business web site is hosted by CrystalTech and I am unable to add mime types to their web server. It really does not matter what web server you use because the web application is going to function offline anyways. So I eventually used my Apache web server on localhost. Once I’d loaded the page from there and bookmarked the site, it remained available even without access to my local network. Just make sure the manifest file for the offline web application has the content type text/cache-manifest as revealed in Firebug:

Here is what my offline web application looks like while running on my iTouch. I can still query my database even when I don’t have a wireless connection. Now I can look up the author and title of any book I’ve read while I’m on the go!

S	M	T	W	T	F	S
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

3 Responses to Offline Database On iPhone Using SQLite

Leave a Reply

Now Reading

Planned books:

Current books:

Recent books: