Offline SQLiteSVG database applications with Firefox

Klaus Förster

Klaus Förster works and teaches Web-mapping courses at the Department of Geography [geoIbk], University of Innsbruck and has been technical leader of the Tirol Atlas project [taProj]. He is author of the PostGIS asSVG() function [postGis] and GRASS GIS v.out.svg module [grassGis] and maintains Universities World Wide, a free link portal to universities around the world [univCc].


Abstract


This paper presents a case study on how to use Firefox 3 in conjunction with Javascript, SQLite and SVG to deliver offline web-applications. It provides a basic example on how to work with the given setup and examines future chances for client side database applications arising with the upcoming HTML5 standard.


Table of Contents

Introduction
A first example
First example revisited
Advanced usage - Tasmania Energy
Conclusion
A. Links

As of planning Firefox 2 the Mozilla team searched for ways to "provide a unified interface for storing and searching through data for all Mozilla components and extensions" [mozUnified]. This should also eliminate multiple file formats that have been in use for Mozilla profiles and settings and "provide Mozilla core components and extension authors with tools to enable richer interaction with user data" (ibid). The solution they came up with was Mozilla Storage [mozStorage], a database API for C++ and Javascript with SQLite, an "in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine" [sqliteAbout] on the backend side. The following case study will show how to use Firefox in combination with SVG, Javascript and SQLite to create offline database applications and examines possible future enhancements arising with the upcoming HTML5 standard developed by the Web Hypertext Application Technology Working Group (WHATWG) [whatwgHome].

To demonstrate the basics of clientside database usage we'll start with a simple "Hello World" SVG application that reads a text string from an SQLite database and allows to change and update it using DOM scripting for user interaction and the Mozilla Storage API for connecting to the database and executing queries [mozStorage]. Due to security restrictions in Mozilla's Storage implementation, this example must be run locally. Please download hello.tar.gz, unpack it and open file hello.svg with Firefox 3 [mozDownload]


 1 window.onload = function() {
 2     // connect to database within same directory as .svg file
 3     window.CONN = openDatabase(document.URL.replace("hello.svg","hello.db"));
 4
 5     // get reference to text-element and add eventListener
 6     var textNode = document.getElementById('hello.msg');
 7     textNode.onclick = function() {
 8         var msg = prompt("Enter message",textNode.firstChild.nodeValue);    // ask for new text
 9         if (msg && !msg.match(/^ +$/)) {
10             executeSql("UPDATE hello SET msg=?1",[msg]);    // update database
11             queryMessage();   // display message
12         }
13     };
14
15     // query current message from database and set text in SVG
16     queryMessage = function() {
17         executeSql("CREATE TABLE IF NOT EXISTS hello (msg TEXT)");
18         var res = executeSql("SELECT msg FROM hello");
19         if (res.length == 1) {
20             textNode.firstChild.nodeValue = res[0]['msg'];
21         }
22         else {
23             // table hello is empty so populate it
24             executeSql("INSERT INTO hello (msg) VALUES ('Hello SQLite World!')");
25             textNode.firstChild.nodeValue = 'Hello SQLite World!';
26         }
27     };
28
29     // get current message from database onload
30     queryMessage();
31 }
32
33 function openDatabase(dbURI) {
34     try {
35         // grant enhanced abilities to script
36         netscape.security.PrivilegeManager.enablePrivilege("UniversalXPConnect");
37
38         // open local database and return db-handle
39         var ioService = Components.classes["@mozilla.org/network/io-service;1"].
40             getService(Components.interfaces.nsIIOService);
41         var dbFile = ioService.newURI(dbURI,null,null).
42             QueryInterface(Components.interfaces.nsIFileURL).file;
43         var stService = Components.classes["@mozilla.org/storage/service;1"].
44             getService(Components.interfaces.mozIStorageService);
45         return stService.openDatabase(dbFile);
46     }
47     catch(err) {
48         alert('Error: Could not connect to database '+dbURI+'.\nStatus: '+err);
49     }
50 }
51
52 function executeSql(sqlStatement,sqlParams) {
53     // grant enhanced abilities to script
54     netscape.security.PrivilegeManager.enablePrivilege("UniversalXPConnect");
55
56     // create statement, bind parameters, execute it and return result-set as dictionary
57     var res = [];
58     var stmt = window.CONN.createStatement(sqlStatement);
59     if (sqlParams) {
60         for (var i=0; i<sqlParams.length; i++) {
61             stmt.bindUTF8StringParameter(i,sqlParams[i]);
62         }
63     }
64     while (stmt.executeStep()) {
65         var row = {};
66         for (var i=0; i<stmt.numEntries;i++) {
67             var colName = stmt.getColumnName(i).toLowerCase();
68             row[colName] = stmt.getUTF8String(i);
69         }
70         res[res.length] = row;
71     }
72     return res;
73 }

Example 2. "Hello SQLite World!" code listing - hello.js


Prior to using Mozilla's Storage API and FileIO interface in hello.js you have to grant enhanced abilities to the script as you're going to read and write files on the local disk. Thus you have to enable the "UniversalXPConnect" privilege using netscape.security.PrivilegeManager.enablePrivilege("UniversalXPConnect") in line 36. On script execution you are then asked for approval (see figure 1). Make sure to check "remember decision" as enhanced abilities are needed for each SQL execution. In case you change your mind and want to revoke these privileges, close Firefox, open prefs.js within your Mozilla profile directory and delete entries containing "user_pref("capability.principal.codebase.p,...").


The code for creating and opening the SQLite database is quite forward and shown in function openDatabase(dbURI), lines 38 to 45. First you define an nsIFile object that represents the desired database location using IOService, then you initialize the StorageService and call it's openDatabase method passing the nsIFile as argument. This will create a fresh database if it didn't exist before, open it and return a mozIStorageConnection ready to be used later on. Due to security restrictions you can not pass a storage connection between functions so we will store it in a global variable named window.CONN.

Communication with the database is handled in function executeSQL(sqlStatment,sqlParams), lines 52 to 73 that requires a SQL statement and optional SQL parameters to be passed as arguments. Once you've called storage service's createStatment method with your SQL-statement you can bind parameters, execute the query, step through the results (if any) and build up a result array with rows consisting of key-value pairs for column names and their values. The process of binding parameters with placeholders (?1, ?2, ...) and corresponding entries in an argument array is very important as it prevents SQL-injection attacks since a bound parameter can never be executed as SQL [mozBinding].

With these two functions at hand it is now easy to add desired behaviour once the SVG document has been loaded (lines 1 to 31). Connect to the SQLite database hello.db which resides in the same directory as the SVG file, get a reference to the SVG text-element with id 'hello.msg' , allow changing of this text onclick and write the altered string back to the database. Function queryMessage (lines 15 to 27) handles basic database work by ensuring that table hello with column msg exists, an initial value is assigned to column msg if table hello is empty, the current value of column msg is queried and last but not least that the SVG text-element is updated as well.

Although this first example works fine and could be easily extended to fulfill tasks beyond displaying and changing trivial "Hello world" strings, there are notable disadvantages in doing so: you are using (abusing) a vendor specific, not yet frozen API, raise serious security issues, limit yourself to local usage via the file:// protocol and lock your application to one browser - Firefox. Fortunately the Web Hypertext Application Technology Working Group (WHATWG) in cooperation with the W3C HTML working group have started to define an API for structured data storage in the upcoming HTML5 specification [whatwgSpec] called "Database storage" [whatwgSql]. If we rewrite the first example to use the proposed syntax it would look as follows:


At the time of writing this paper, the WebKit web browser engine and apparently Safari 3.1 as well as iPhone 2.0 which both use it, have implemented parts of the proposed Database storage API [webkitSql]. This means that you can open the above example in WebKit Launcher and run it without additional libraries needed. As far as Firefox is concerned there's no implementation yet, nevertheless one can expect that it will be available as soon as the WHATWG specification evolves. To be able to use Firefox in this case study, the author has written a wrapper that mimics client-side database Storage behaviour using the Mozilla Storage API (Html5Db.js) and the FileIO interface (Io.js). This allows to use HTML5 conform Javascript syntax as proposed by the WHATWG specification. To run the above example in Firefox please download hello.tar.gz, unpack it and open file hello_html5.svg, if you have WebKit or Safari installed just browse to the Online version.

Let's have a closer look at the code in hello_html5.js. The openDatabase method now requires four arguments to be passed: database name, database version, display name and an estimated size in bytes for the data that will be stored. This method "must use and create databases from the origin of the active document" [whatwgOpenDb] giving each domain identified by protocol, host and port a separate space with an initial quota of five megabytes (ibid). Webkit's implementation reveals how this could be mapped to a directory-structure and if you adopt it for Firefox you will get the structure shown in example 4.


Once the database object is created with the openDatabase method "we can use the transaction method on it. The transaction method takes one to three arguments: a transaction callback, an error callback, and a success callback. The transaction callback gets passed a SQL transaction object on which you can use the executeSql method. This method takes from one to four arguments: a SQL statement, arguments, a SQL statement callback, and a SQL statement error callback. The SQL statement callback gets passed the transaction object and a SQL statement result object which gives access to the rows, last inserted ID, et cetera" [w3cOfflineWebapps].

Although you've got to get used to the concept and proposed syntax of the WHATWG API, it's quite intuitive to work with. Our last example will show how to put the pieces together and create an offline database driven application. Please download tasmania.tar.gz, unpack it and open file tasmania_init.html with Firefox 3. This will initialize the local database schema within your Firefox profile directory and load geometry with attributes and data needed for the "Tasmania Energy" example.

Basic data has been downloaded from Geoscience Australia [gaGov] and consists of a couple of ESRI shapefiles with boundaries, lakes, rivers, roads, railroads, places and power stations. Conversion to SQLite is accomplished by the OGR Simple Feature Library, "a C++ open source library (and command line tools) providing read (and sometimes write) access to a variety of vector file formats including ESRI Shapefiles, S-57, SDTS, PostGIS, Oracle Spatial, and Mapinfo mid/mif and TAB formats" [ogrLib]. Example 5 shows how you can use the command line tool ogr2ogr [ogr2ogr] to import a shapefile and data and how to fine-tune your database with the sqlite3 executable to create additional tables, columns and views.


Once the data is loaded, a Python script translates the content of tasmania.sqlite to HTML5 database storage API calls within tasmania_init.html - see source code and lines with tx.executeSql. This extra step of storing will be unnecessary once HTML5 database storage is implemented in Firefox and thus available via the http:// protocol as well. WebKit already supports this, so it's possible to run the python script directly from the server and initialize the local database remotely. The link "continue to Demo" will then take you to the Tasmania Energy example which is created by Javascript code executed onload in tasmania.js.


The basemap as shown in the screenshot above is built by converting geometry in WKT format to SVG path, circle or text elements using
WKTParserLib.js. WKT ("Well Know Text") "is a text markup language for representing vector geometry" [wktWikipedia] and is regulated by the Open Geospatial Consortium [ogcHome]. It allows to define POINT, LINESTRING, POLYGON and their MULTI-variations in a simple way - e.g LINESTRING(3 4,10 50,20 25) . Ogr2ogr uses WKT as format during conversion of shapefile geometry to SQLite and stores it in column "wkt_geometry". The Javascript library that converts to SVG notation adapts the parsing algorithm found in WKT.js [openLayersFormat] from the Openlayers project [openLayers].

Along with translation from WKT to SVG, namespaced attributes (tas:id, tas:name, tas:anchor etc.) are added for each geometry or label and a simple tooltip is provided using the title attribute. Circle sizes for power stations are set according to capacity (KW) and styling is managed with CSS style-rules in tasmania.css representing columns "feature_code " or "class" in the specific layer. Text anchors are set according to column "anchor" within table places and allow to choose among nine anchor positions starting with 1 for top left and ending with 9 for bottom right. Horizontal positioning is set with text-anchor start, middle or end and vertical position through baseline-shift 0, -0.35em and -0.7em. Unfortunately this attribute is not implemented by Firefox 3, so setting vertical positioning will have no (visible) effect.

As soon as all layers are present, interactivity is added. Onclick event handlers allow to display detailed data for each power station by querying the local database and enable to redefine and store text anchor positions for each label with a simple graphical tool that helps to set the anchor. The text block with explanations and links on the right hand side of the interface is generated as foreignObject element with regular HTML code inside.

Client side database storage is a fascinating new development that "addresses the challenge of building Web applications that work while offline" [w3cOfflineWebappsIntro] and will probably have strong influence on SVG application programming in the future. It's not clear though where this development will lead to as there's a lot of work in progress. The specification has not been finished yet and browser implementations won't be really usable until the specification is done. Nevertheless, it's possible to experiment with the given setup so let us start exploring these new possibilities!

A. Links