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
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 <svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink"> 2 <title>Hello SQLite World! example</title> 3 <desc>Use Mozilla Storage API with Javascript, SQLite and SVG</desc> 4 <script type="text/javascript" xlink:href="hello.js" /> 5 <text id="hello.msg" x="50" y="50"> </text> 6 </svg>
Example 1. "Hello SQLite World!" code listing - hello.svg
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:
1 window.onload = function() {
2 // connect to database in domain-subdiretory within profile database-directory
3 var db = openDatabase("Hello", "1.0", "Hello SQLite World!", 512000);
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 db.transaction(function(tx) {
11 tx.executeSql('UPDATE hello SET msg=?',[msg]); // update database
12 });
13 queryMessage(); // display message
14 }
15 };
16
17 // query current message from database and set text in SVG
18 queryMessage = function() {
19 db.transaction(function(tx) {
20 tx.executeSql("CREATE TABLE IF NOT EXISTS hello(msg TEXT)");
21 tx.executeSql("SELECT msg FROM hello", [], function(tx, rs) {
22 if (rs.rows.length == 1) {
23 textNode.firstChild.nodeValue = rs.rows.item(0).msg;
24 }
25 });
26 if (textNode.firstChild.nodeValue == ' ') {
27 // table hello is empty so populate it
28 tx.executeSql("INSERT INTO hello (msg) VALUES ('Hello SQLite World!')");
29 textNode.firstChild.nodeValue = 'Hello SQLite World!';
30 }
31 });
32 }
33
34 // get current message from database onload
35 queryMessage();
36 }
Example 3. "Hello SQLite World!" HTML5 database storage code listing - hello_html5.js
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.
~/.mozilla/ProfD/
|
+-- databases/ (subdirectory for client-side databases)
|
+-- Databases.db (index table with database names, paths, quotas, etc)
|
+-- http_svg.cc_0/ (subdirectory for origin http://svg.cc/ - max. 5Mb)
| |
| +-- 00001.db (user database "hello")
| +-- 00003.db (user database "tasmania")
|
+-- file__0/ (subdirectory for origin file://localhost/- max. 5Mb)
|
+-- 00002.db (user database "hello")
Example 4. HTML5 database storage client-side directory structure
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.
$ echo "loading geometry fron SHAPE file ..."
$ ogr2ogr -f SQLITE -spat 143.7 -43.8 148.7 -39.4 -nln territory \
-select FEAT_CODE,NAME,UFI tasmania.sqlite ./aus5fgd_r.shp # territory
$ echo "loading data from CSV file ..."
$ ogr2ogr -update -f SQLITE -nln data_energy tasmania.sqlite ./energy.csv # power station data
$ echo "adding text-position column to table places"
$ sqlite3 tasmania.sqlite "ALTER TABLE places ADD COLUMN 'anchor' TEXT DEFAULT 7"
$ echo "creating __version__ table and setting it to 1.0"
$ sqlite3 tasmania.sqlite "CREATE TABLE __version__ (revision TEXT)"
$ sqlite3 tasmania.sqlite "INSERT INTO __version__ VALUES ('1.0')"
$ echo "creating view to simplify queries ..."
$ sqlite3 tasmania.sqlite "CREATE VIEW view_energy_tasmania AS
SELECT eno AS id,name,state,owned,fuel_type,technology,no_turbines,capacity_kw,
commission,comments,longitude,latitude,wkt_geometry,status,
CASE
WHEN fuel_type LIKE 'Biomass%' THEN 'type_biomass'
ELSE 'type_'||lower(fuel_type) END AS class,
CASE
WHEN CAST(capacity_kw AS INTEGER) >= 200000 THEN 0.3
WHEN CAST(capacity_kw AS INTEGER) >= 100000 THEN 0.2
WHEN CAST(capacity_kw AS INTEGER) >= 50000 THEN 0.1
WHEN CAST(capacity_kw AS INTEGER) >= 10000 THEN 0.075
WHEN CAST(capacity_kw AS INTEGER) >= 1000 THEN 0.05
ELSE 0.035 END AS radius
FROM data_energy WHERE state='Tasmania'"
Example 5. ogr2ogr and sqlite3 commands for loading geometry and data
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
-
http://www.uibk.ac.at/geographie/ (geoIbkj)
-
http://tirolatlas.uibk.ac.at/ (taProj)
-
http://postgis.refractions.net/ (postGis)
-
http://grass.itc.it/ (grassGis)
-
http://univ.cc/ (univCc)
-
http://wiki.mozilla.org/Mozilla2:Unified_Storage (mozUnified)
-
http://developer.mozilla.org/en/docs/Storage (mozStorage)
-
http://www.sqlite.org/about.html (sqliteAbout)
-
http://www.whatwg.org/ (whatwgHome)
-
http://www.mozilla.com/en-US/firefox/ (mozDownload)
-
http://www.whatwg.org/specs/web-apps/current-work/ (whatwgSpec)
-
http://www.w3.org/TR/offline-webapps/ (w3cOfflineWebapps)
-
http://www.gdal.org/ogr/index.html (ogrLib)
-
http://www.gdal.org/ogr/ogr2ogr.html (ogr2ogr)
-
http://en.wikipedia.org/wiki/Well-known_text (wktWikipedia)
-
http://www.opengeospatial.org/ (ogcHome)
-
http://openlayers.org/ (openLayers)
-
http://svn.openlayers.org/trunk/openlayers/lib/OpenLayers/Format/ (openLayersFormat)
-
http://www.w3.org/TR/offline-webapps/#introduction (w3cOfflineWebappsIntro)