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.
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
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.
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.
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!