357 Lotus blogs updated hourly. Who will post next? Home | Downloads | Events | Pods | Blogs | Search | myPL | About 
 
Latest 7 Posts
Nation Apparently Believed in Science at Some Point
Tue, Jul 22nd 2014 112
CSC “mini” event
Mon, Jun 2nd 2014 180
Atom
Tue, May 6th 2014 251
Agile, agility?
Mon, Mar 24th 2014 297
Phew!
Sun, Mar 23rd 2014 319
Your language is not dead
Tue, Feb 25th 2014 404
Oh you’ve got to come to this...
Thu, Feb 20th 2014 427
Top 10
Oh you’ve got to come to this...
Thu, Feb 20th 2014 427
Your language is not dead
Tue, Feb 25th 2014 404
Off-line web apps: local Web SQL & Javascript
Wed, Jun 22nd 2011 400
ThoughtWorks release their technology radar
Wed, Jan 29th 2014 376
The JVM in 9.0.1
Mon, Nov 18th 2013 326
Phew!
Sun, Mar 23rd 2014 319
The Macintosh is 30
Tue, Jan 28th 2014 313
Agile, agility?
Mon, Mar 24th 2014 297
Netgear ReadyNAS & OS X: fixing printing
Mon, Sep 12th 2011 273
</2013><2014>
Tue, Dec 31st 2013 272


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
401 hits



Recent Blog Posts
112


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


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
251


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


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
319


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
404


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




427


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


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
313


The Macintosh is 30
Tue, Jan 28th 2014 1:45a   Ben Poole
The Mac turned thirty this weekend, but you could be forgiven for missing that. Very little in the way of hoo-hah. Here are a couple of the more worthwhile links out there: Stephen Fry has turned in a wonderful piece of writing about the Macintosh, and his experience buying just the second one available in Europe, back in 1984: From the very first the Apple Macintosh team included archeologists, classicists, and, famously, Steve’s passion for the print art of fonts was built into the very fi [read] Keywords: apple development mac macintosh
272


</2013><2014>
Tue, Dec 31st 2013 4:47a   Ben Poole
Where does it go? Where does it go?? So, time to bid farewell to another year. And another year of blog neglect. Tsk. No promises from me. Lots to write about, not enough hours. I’m hoping for a little more time in 2014 as I ease back on the work a little, but who can say what will happen? I started 2013 with a lovely jaunt to Connect / Lotusphere, my first since 2010. That was tremendous fun, and I have posts about that on the site already. I can’t post about Lotusphere-that-was without [read] Keywords: domino lotusphere bug java




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