So, you know how to take our site off-line: events are triggered, caches are swapped, and even though you still have that (apparent) server-based URL going on in your address bar, you are off-line! Yes! Stick your device in airplane mode and revel in how your website is still doing its thing. Clever.
However, your world is incomplete. You have some pages going on, some links between them, stylesheets—maybe some whacked-out Javascript doing wondrous things—but something is still missing. And that something is persistence, a proper local data store that goes beyond the capabilities of the humble, fragile wee cookie.
Until the newer browser technology started to become mainstream (arguably aided by the early efforts of things like Google Gears), this was a big sticking point for off-line sites and apps, because there was nowhere to store one’s data. Now though? Sea-change, oh yes. Depending on your browser of choice, you have two options: 1) an embedded local relational database (the generic “Web SQL” standard, that in practice actually means SQLite) or Web Storage, which is a simple key-value store.
IndexedDB is one for another day. For now, we’re thinking about a local instance of SQLite with a Javascript framework to access it, which can be found in all up-to-date WebKit-based browsers, and also in Opera. “Web SQL” works well, and is surprisingly well-featured, but we hit the main hiccup with its uptake when we consider the supporting web clients: Chrome and Safari (including mobile variants thereof) are golden of course. Opera is popular, and includes a mobile instance too—again, good—but Firefox and Internet Explorer are out in the cold, with no Web SQL support (in fairness to Mozilla, Firefox instead supports IndexedDB which appears to be the preferred local data store going forward anyway—see “Further reading” section). Only you can decide whether this is an issue, but seeing as I’m been coming from the mobile web dev. angle, I'm OK with it :-)
On to the code
The Javascript API that overlays all this SQLite goodness is pretty simple to use. It is callback-mungous though, so if you’re not used to coding your JS that way, you soon will be. Here is a snippet of Javascript which opens a database for logging periwinkles. The code creates the main table (if it doesn’t already exist), which is a simple three column affair: a generated id, plus two text values for a given winkle’s name and location:
/* Open & initialise our database. Note the required parameters,
which comprise a short name, a version, a display name
& expected maximum size (in bytes)
*/
var db = openDatabase("winkles", "1.0", "Winkles Of The World", 32678);
/* Create the winkles table (if it doesn't already exist), with
an automatic id key, plus two columns for name & location
*/
db.transaction(function(transaction){
transaction.executeSql("CREATE TABLE IF NOT EXISTS winkles (" +
"id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
"winklename TEXT NOT NULL, location TEXT NOT NULL);");
});
So the database and table exist (and will still be there when you close your browser session of course), and now we want to do stuff with the table. Assuming we’ve magically generated some winkle data, let’s say I now want to see the names of all winkles found in Ayrshire. Here’s how it's done:
var showWinkles = function(location, successCallback){
db.transaction(function(transaction){
transaction.executeSql(("SELECT * FROM winkles WHERE location=?"), [location],
function(transaction, results){successCallback(results);}, errCallback);
});
};
A few things to note here: the SQL statement is wrapped in a transaction instance, which is asynchronous. As such, the call to showWinkles is expected to pass over two things. One is the location we want data for (“Ayrshire”), and the other is a callback (successCallback). This should be a function that the SQL results get passed to for onward processing. Finally, you can see reference to some random thing called errCallback. This is optional, but recommended, and in this instance it’s a reference to an error state callback function. You can define this in your main Javascript code, something along these lines perhaps:
// Generic error callback
var errCallback = function(){
alert("Oh noes! There haz bin a datamabase error!");
}
Well you get the idea.
Is this thing on?
A quick aside: let’s fire up the Web Inspector found in all WebKit browsers, and see just what we’ve got there:
Not bad eh? There’s our database, our winkles table, and some data, right there. In fact, the Web Inspector window is quite powerful: highlight the databases icon, and the pane to the right becomes a console into which you can type SQL directly—perform updates, queries, drops, anything you want (you even get some auto-complete on your SQL).
Success
Back to the code. Let’s look at a typical “success” callback in more depth. How might one usefully call showWinkles within a web page? How about a button (with the id show-me), that when clicked, retrieves all the winkle data for a given area and presents it in a list? Anyone familiar with the “subversive injections” presentation I did with the Wookiee at UKLUG 2009 will know I like to inject code into web pages willy-nilly (dirty devil). So, when your document’s ready, bind some code to our button like so (note: all the following snippets use jQuery for brevity, selectors, and a couple of other things):
Now, let’s write the routine mentioned above as the callback we pass to showWinkles—we’re calling it updatePage. This function needs to get a handle on our HTML list (to which I have granted the imaginative epithet winkle-list), iterate our SQLResultSet, and write out a list item for each row in said results (or not, if there’s no data). Again, I’m using jQuery here, both for selectors and for $.each (which I like very much):
var updatePage = function(results){
var winkles = $("#winkle-list");
winkles.empty(); // Clear down before re-creating
if (results.rows.length==0){
// Nothing in the table
alert("Alas, there be no winkles here.");
} else {
// Iterate the SQLResultSet
$.each(results.rows, function(i){
var row = results.rows.item(i);
// Write out a list item for each row
winkles.append("<li>" + row.winklename + " who resides in " +
row.location + "</li>");
});
}
};
All done! So there, in a nutshell, is how to query data in a SQLite database running in your WebKit browser. Now, the more astute amongst you will have noted that this isn’t the whole story—I’ve not listed any code to save my winkles for a start, nor is the mark-up I’m tinkering with detailed. Do not fear, the complete source code for this post (a single HTML page with all the required Javascript) is available on GitHub, in a thing called a “gist”:
Now, this code is a simple introduction to Javascript and SQLite in a local web page. It is not production-ready for a number of reasons, not least that it’s all just straight Javascript function calls and wot-not. Any developer worth his or her salt would rip this apart and create a nice DAO which can then be used as a “service class” of sorts, tucking away all those nasty brackets and SQL statements (in fact, that’s just what I did for a client project recently ;-)), so there’s some homework for you right there!
Back to black
Sat, Feb 23rd 2013 7:28a Ben Poole Mr. Mooney recently posted about his old Apple MacBook, which he received in recognition for all his hard work with ILUG in 2007. Seeing his post reminded me of the “mb4bp” initiative that Volker kicked off:
In December 2006, through the wonderful kindness of the wider IBM Lotus community, I was able to buy my first professional grade (and new to boot) machine. This started something big. Within eight months of receiving that splendid little laptop I had formed my own company and resigned f [read] Keywords: ibm
ilug
lotus
apple
community
laptop
macbook
283
The music post
Tue, Feb 19th 2013 3:28p Ben Poole An astounding year for music so far, and we’re only in February! First up, I was delighted to find that a relatively new discovery of mine released a new album in January. These fellows hail from Manchester and are called Dutch Uncles. Prog-pop is the description they’re most tagged-with, and I suppose it suits. Think a joyful amalgam of 80s King Crimson, Talking Heads and Sparks. Some fantastic odd guitar figures, shimmering keys and a unique vocal style from leader Duncan Wallis. Excellent [read] Keywords:
439
IBM Connect 2013: the last post
Thu, Feb 14th 2013 3:56p Ben Poole And so to the last day of Connect, Thursday (this post is a bit of a ramble, I warn you now). I started off poorly by missing Mark and Julian’s show ’n’ tell, “Buried Treasure: Finding the Hidden Gold in IBM Notes Data”. Damn! Instead, a little work and then off to the various Q&A sessions, kicking off with Gurupalooza, hosted by the wonderful Susan Bulloch. This is always a fun session, and the “re-education bat” added a certain je ne sais quoi.
Gurupalooza was followed by [read] Keywords: connections
domino
ibm
inotes
notes
xpages apple
blogger
development
enterprise
java
javascript
mobile
server
twitter
wiki
358
IBM Connect 2013: the third post
Tue, Feb 5th 2013 3:43p Ben Poole Tuesday was a superb day in Orlando, really enjoyed it. And I’ll tell you for why… (said in best Uncle Bryn voice):
First up, a load of work, and chat in the rotunda. Always good!
And then, just before lunch we had a gem of a session: Spark Ideas, put together by the Nerdgirls. It was just wonderful. I nearly ended up blubbing like a baby (thanks Graham :-)) and also laughed like a drain. Nothing technical, nothing about IBM. Just a group people I respect (more than ever) laying a little o [read] Keywords: domino
ibm
xpages apple
application
applications
eclipse
java
server
wiki
410
IBM Connect 2013: the second post
Mon, Feb 4th 2013 3:20p Ben Poole And so to Sunday. I suppose one would class this as the first “proper” day of IBM Connect as Jumpstart sessions get going, and there’s business development stuff going on. In the Woodward / Myers / Poole suite pit things were somewhat muted and slow to start. We all had things to do, so the day was spent gently faffing. Mark and I even got a decent brunch in (one of the few proper meals enjoyed during the week!)
Predictably, the evening started early at the pool-side reception, and finishe [read] Keywords: ibm
application
development
interface
359
IBM Connect 2013: the first post
Sun, Feb 3rd 2013 3:30p Ben Poole A taxi, a plane, a train, a walk, another train, and another walk. Left the Dolphin just after 5pm on Thursday, and walked through my front door in Blighty exactly fourteen hours later.
And what a trip! As long-time readers will know (!) I don’t do the Lotusphere thing very often. I went courtesy of my old employer in 2006 and then thanks to team Elguji got to go again in 2010 as the indy coder I am now. Despite this paucity in my attendance record, Lotusphere always feels welcoming, it just [read] Keywords: ibm
lotusphere
google
wiki
466
Quote of the day
Mon, Jan 7th 2013 4:30a Ben Poole Just catching up on Robert Fripp’s diaries. He was discussing the various interactions he’d had with people on-line over the years, and this line stood out:
It is not possible to discuss the weather with a person whose head is placed where sunshine never falls, and yet who feels themselves to be a climate expert.
Read more… [read] Keywords:
543
Headline of the year
Mon, Dec 31st 2012 7:57p Ben Poole … possibly. ‘Oompa Loompas’ sought by police over Norwich assault.
Happy 2013 everybody. Sorry for the radio silence for so long. It has been one hell of a mad year! I hope to see many people during the course of the next. Sorry, this. Yes. [read] Keywords:
419
Wise words
Sun, Nov 25th 2012 1:47p Ben Poole Something I just read whilst catching up on Robert Fripp’s diary—a piece of advice to a struggling musician colleague, but I think there’s a little nugget in there for all of us:
My advice: re-establish the morning sitting. Life without the morning sitting is like trying to walk without legs. This is the beginning, the foundation, of strengthening personal presence. If we are absent, then life is all stuff. Nothing real happens; so when things go wrong, it’s not possible [read] Keywords:
