357 Lotus blogs updated hourly. Who will post next? Home | Downloads | Events | Pods | Blogs | Search | myPL | About 
 
Latest 7 Posts
On Kate Bush
Sat, Aug 30th 2014 158
On blogs
Thu, Aug 28th 2014 158
Nation Apparently Believed in Science at Some Point
Tue, Jul 22nd 2014 226
CSC “mini” event
Mon, Jun 2nd 2014 228
Atom
Tue, May 6th 2014 311
Agile, agility?
Mon, Mar 24th 2014 362
Phew!
Sun, Mar 23rd 2014 388
Top 10
Phew!
Sun, Mar 23rd 2014 388
Off-line web apps: local Web SQL & Javascript
Wed, Jun 22nd 2011 378
Agile, agility?
Mon, Mar 24th 2014 362
The JVM in 9.0.1
Mon, Nov 18th 2013 320
Atom
Tue, May 6th 2014 311
Oh you’ve got to come to this...
Thu, Feb 20th 2014 307
ThoughtWorks release their technology radar
Wed, Jan 29th 2014 300
Your language is not dead
Tue, Feb 25th 2014 287
</2013><2014>
Tue, Dec 31st 2013 275
Netgear ReadyNAS & OS X: fixing printing
Mon, Sep 12th 2011 252


Off-line web apps: local Web SQL & Javascript
Ben Poole    

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:

Screenshot of Safari Web Inspector showing our winkles database

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):

$('#show-me').click(function(){showWinkles('Ayrshire', updatePage);});

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”:

gist - Javascript & Web SQL example

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!



---------------------
http://benpoole.com/weblog/201106222227
Jun 22, 2011
379 hits



Recent Blog Posts
158


On Kate Bush
Sat, Aug 30th 2014 5:01a   Ben Poole
I do not have the body of work in my past that she has—but to see a mature woman create something so idiosyncratic, risk it all in the public view and then triumph on her own terms is a joy. Read more&hellip; Hear, hear. Off to see Kate Bush in less than two weeks, and cannot wait! [read] Keywords:
158


On blogs
Thu, Aug 28th 2014 2:41a   Ben Poole
My blog’s older than Twitter and Facebook, and it will outlive them. It has seen Flickr explode and then fade. It’s seen Google Wave and Google Reader come and go, and it’ll still be here as Google Plus fades. When Medium and Tumblr are gone, my blog will be here. Google Plus is still a thing? ;-) Read more&hellip; [read] Keywords: facebook google twitter
226


Nation Apparently Believed in Science at Some Point
Tue, Jul 22nd 2014 12:43a   Ben Poole
Historians studying archival photographs from four decades ago have come to the conclusion that the U.S. must have believed in science at some point. Read more&hellip; (beautiful typography too). [read] Keywords:
228


CSC “mini” event
Mon, Jun 2nd 2014 2:54p   Ben Poole
As you may have seen, we at LDC Towers, together with esteemed colleagues at The Turtle Partnership are looking to run a second Collaboration Stack Community event, this time a “mini” session, taking an evening slot on the 16th July. As before, there will be two tracks or themes to the event, and your participation is encouraged, nay mandatory! The details: CSCEvent Evening event this July. Do take a look at the post, and register if you are interested. As ever, it’s free! [read] Keywords: collaboration community
311


Atom
Tue, May 6th 2014 2:14p   Ben Poole
I am writing this post in Github’s new “hackable” programming editor, Atom. It’s pretty nifty, and I’ve been using it for a few weeks now. For general mark-up and the like it has supplanted the old main-stay, Coda 2, which has been having some issues for a little while now (frankly, it tries to be too many things). Whether I shall ditch Sublime Text as well (which Atom is very similar to, in terms of looks and core functionality), remains to be seen. Time will tell [read] Keywords:
362


Agile, agility?
Mon, Mar 24th 2014 12:34p   Ben Poole
One of the architects of the agile manifesto, Dave Turner, has written an eloquent piece on how the term “agile” has been horribly misappropriated: The word “agile” has been subverted to the point where it is effectively meaningless, and what passes for an agile community seems to be largely an arena for consultants and vendors to hawk services and products. Sadly, this mirrors my experience, and probably that of many other IT practitioners out there. The term has been [read] Keywords: collaboration community




388


Phew!
Sun, Mar 23rd 2014 2:50p   Ben Poole
Well that was a fair few weeks of effort on all fronts. As some of you will know, we recently co-hosted the inaugural Collaborative Stack Community event and good fun it was too. The venue was fab (the Soho Hotel, picked out by good chum and colleague Abigail Roberts at Ravendesk), the attendees were in good voice, our speakers were great, and as ever Gab and Mike organised things wonderfully. Me? I just turned up and looked pretty. Many thanks to everyone who took part, and keep your eye on t [read] Keywords: community twitter
287


Your language is not dead
Tue, Feb 25th 2014 3:34a   Ben Poole
This: Meanwhile, I suspect 80% of programmers are still working on problems where their development velocity is a much bigger problem than how many hits their server can take before falling over. I dunno, maybe my view of the industry is skewed. I just don’t think there are really that many developers, statistically speaking, who can cite system capacity as their current problem #1. Or #2, or #3. Excellent exposition of those interminable “language blah is dead” memes that just w [read] Keywords: lotusscript development server
307


Oh you’ve got to come to this...
Thu, Feb 20th 2014 2:08p   Ben Poole
If you read the sites of my good friends and colleagues the Turtle Partnership, Matt White and the Wookiee (and why, pray, would you not?), you will be aware of a new, free event taking place in a month. On Friday, March 21st we are holding an informal techie(ish) get-together for those interested in collaborative technologies. This will take place in Soho, London, and will not be a sales-fest in any way—nor will it be vendor / platform-specific: come one, come all! CSC Event will most d [read] Keywords:
300


ThoughtWorks release their technology radar
Wed, Jan 29th 2014 1:43a   Ben Poole
The ThoughtWorks technology Radar is out, and it makes for interesting reading. Some of the “Adopt” languages and frameworks are new to me, and one in particular warrants further investigation: Dropwizard is an opinionated combination of several lightweight Java tools and frameworks, many of which would merit mention in their own right. The package embodies many of our favorite techniques, including an embedded HTTP server, support for RESTful endpoints, built-in operational metrics and hea [read] Keywords: java server




Created and Maintained by Yancy Lent - About - Blog Submission - Suggestions - Change Log - Blog Widget - Advertising - Mobile Edition