pmuellr is Patrick Mueller

other pmuellr thangs: home page, twitter, flickr, github

Thursday, January 12, 2012

making Web SQL easier to use

One HTML5-ish area I've not yet played much with, but am starting to now, is Web SQL. Web SQL is the W3 standard for "SQL in the browser", which has been orphaned by the W3C. Here are some warnings from the Web SQL Database spec at

  • "Beware. This specification is no longer in active maintenance and the Web Applications Working Group does not intend to maintain it further."

  • "This document was on the W3C Recommendation track but specification work has stopped. The specification reached an impasse: all interested implementors have used the same SQL backend (Sqlite), but we need multiple independent implementations to proceed along a standardisation path."

Despite the fact that the W3C no longer endorses Web SQL and will never 'standardize' it, implementations of Web SQL are shipping in browsers, including some mobile platforms. It's not dead yet.

what's the api like?

Here's an example right out of the spec, to count the number of rows in a table:

A function showDocCount()that calls a function db.readTransaction() that takes two anonymous functions as callbacks. One of those callbacks calls t.executeSql() passing it yet another callback, which finally, mercifully, gets the row count.

It's executing a single SQL statement.

But wait, it gets better.

If you need to run multiple SQL statements within a single transaction, you'll have to chain those executeSql() invocations within increasingly nested callbacks.

It's basically a horror show. At least as far as I'm concerned. Nested callbacks are things I hate to have to write, and things I really hate seeing other people have to write. The result is almost always unintelligible.

can we make this easier to understand?

For weinre, I implemented a stepper module used by the inspector's database interface. Not very clear what's going on there though, sorry.

The basic idea is that you provide an array of functions - the steps - that the stepper will arrange to run in order, by managing the callbacks itself. Double bonus good - you get to linearize your database transaction steps and you don't have to deal with callbacks!

I found another instance of this kind of processing in Caolan McMahon's async library - specifically the waterfall() function. Not really appropriate for Web SQL, but at least it seems to validate the approach.

I decided to implement a clean implementation of the stepper, trying to make it easy to use and understand. The result is available as WebSQLStepper at GitHub. As a use case, I took the Todos sample from Backbone, and modified it to use this library to persist the data to a Web SQL database. The complete sample is included in the GitHub repo, and here are the steps used to read all the rows of a database:


wr - an auto-build command for the command-line

For much of my programming career, I've been a user of "IDE"s, including BASIC, Turbo Pascal, various Smalltalks, IBM's VisualAge products and Eclipse.

Lately, I've been spending most of my time working without any IDEs. My tools are text editors, terminal windows, a boat load of unix commands built-in to my OS, some additional command-line tools from various open source locales, and web browsers. I think of this loosely organized/integrated set of tools as my IDE, they're just not packaged up in a nice single app.

But there's one thing I dearly love and really depend on in most IDE's that's missing from this command-line-based environment: an "auto-build" capability. Specifically, when I save a file I'm editing, I want a "build" to happen automagically. Without an "auto-build" capability, I'm left in a world where I save a change in my text editor, jump over to the terminal, run make, then jump to my browser to test the build. The jump to the terminal is not desired. Using some existing editor's capability of running shell commands doesn't work either, because I use a wide variety of tools to "edit" things - all the tools would need this capability.

(Quick aside about "builds". A lot of people see "build" and think "30 minutes of waiting for the compiles to be done". Yes, those builds are long and painful. I'm looking at you WebKit. But in all the IDEs I used to use, builds were insanely fast. Sub-second fast. If you're living the "big C++ project" lifestyle, I feel your pain, and "auto-build" is probably not applicable for you.

So, I'm talking about quick builds here. As an example, once you've done at least one build of weinre, subsequent builds take on the order of 6 seconds or so - I consider that slow, but it's doing a lot of work, and I could probably cut the time in half if I needed to - I just haven't felt the need to.)

a command to run something when a file changes

A few years ago, I wasn't aware of a tool that could watch for arbitrary file changes and run a command for me, so I wrote one - run-when-changed. This script served me well for a long time, but there were always little thingees I wanted to add to it. And since run-when-changed polled the file system to see when files changed, I was always interested in finding a better story than polling and the inevitable waiting for the polling cycle to hit - even waiting 3 seconds for run-when-changed to realize a file changed was bothersome.

A port to node.js seemed like it might be in order, especially since it has a non-polling file watch function

Earlier this week, I spent a few hours and now I have a replacement for run-when-changed, called wr, available at GitHub, written using node.js. The name wr comes from "watch and run".

Some features:

  • installable via npm

  • colorized stdout and stderr

  • reversed video status messages

  • when run with no arguments, will look for a .wr file in the current directory to use as arguments

Here's what it looks like, using it with one of my current projects:

Click the image for a slightly larger version.


  • the reverse video blue lines are status messages
  • the reverse video green lines are command success messages
  • the reverse video red lines are command failure messages
  • the blue text is stdout
  • the red text is stderr

As another example, I'm generating this blog post by running the following command:

    wr --exec "markdown > wr.html"

Breaking this command down, we have:

  • wr - the command
  • --exec - a command-line option (see below in gotchas)
  • "markdown > wr.html" - the command to run
  • - the file to watch for changes.

The arguments to wr are options, the command to run, and the names of files or directories to watch. When the wr command is run, it waits for one of the specified files to change, runs the specified command, reports the results of running the command, and then starts waiting for a file to change again. Exit the program by pressing ^C.

The ability to store a .wr file in a project directory makes life even easier. I used to create a target in my make files to invoke run-which-changed ; now I just put the wr invocation for a project in the .wr file, and run wr with no arguments.

My work-flow for using wr goes something like this:

  • open a terminal window
  • cd into my project's home directory
  • launch an editor, returning control to the command-line
  • run wr
  • move the terminal window where I will always be able to see the last few lines of the window, to see the live status
  • switch between text editor and browser, for my edit-save-reload-test cycle, all day long, keeping an eye out for red stuff in the wr output
  • when the day is done, ^C out of wr, and then commit work to my SCM

This can all be yours, with a simple command-line incantation:

    sudo npm -g install wr

current gotchas

wr has two ways of invoking the command to run, determined by the command-line option --exec; see the README, in the ENVIRONMENT section, for more information on the differences.

The big gotcha though is the non-polling file watching. This is an awesome feature, as it means as soon as you save a file, wr will wake up and run a command. The problem, on the Mac anyway, is that there is a relatively small limit on the number of files that can be watched this way. Like, 200 or so. If you go over the limit, you'll see some error messages from wr, and you'll have to resort to using the --poll option, which uses a polling file watcher.