Chapter 5. Working with Web SQL

Dead Spec Walking

Before we say anything at all about Web SQL, we regret to inform you that this spec is dead. Or dying. Or at least sentenced to death. Web SQL as a feature was a rather interesting one. It gave you access to miniature databases within the browser. For web developers who did server-side work, this was especially nice because they may have had some familiarity with SQL already. However, for reasons that are not important to this book, the specification has been EOLed (End of Life) and will (possibly) not be available in the future. That means—in theory—you shouldn’t even be reading this chapter.

However...

Web SQL has very good support on mobile browsers, and was available before IndexedDB and much better supported than IndexedDB. It is entirely possible that as a developer, you will run into web apps making use of Web SQL. While we don’t recommend starting new projects with Web SQL, we hope this chapter will give you enough knowledge about the ins and outs of Web SQL so that if you have to help support an existing implementation you’ll know what to do.

As before, this particular client-side data storage technique will be tied to a particular domain. Storage limits are pretty varied and can range from 5 MB to 50 to more. Before we dive in, let’s cover some basic terms.

Basic Database Terms

For those of you with experience working with traditional relational database servers, you can just go ahead and skip this section.

Databases
A database is the top-level container where you would store your data. As with IndexedDB, you can have as many of these as you would like, but typically you’ll probably stick to one database per site.
Tables
This is where data for a particular type of information is stored. Unlike object stores in IndexedDB, tables are very strict about what gets stored. If you have defined a “people” table as having name, age, and gender columns, you can only store values in those columns. Each column also has a particular type of expected data, and you must match that criteria to store your data.
Rows
A row is simply an individual unit of data for a table. Given a table called people, one row would be a person.

Checking for Web SQL Support

The simplest way to check for Web SQL support is by checking for the openDatabase API of the window object:

if("openDatabase" in window) {
}

Which can be nicely turned into a function like so:

function webSQLOK() {
    return "openDatabase" in window;
}

Working with Databases

Much  like IndexedDB, databases in Web SQL have a name and a version. Unlike in IndexedDB, though, the version number will not provide an event for you to perform changes; instead, it acts as a validation. If the user had an earlier version of the database, you can perform an update manually to handle changes. (We’ll show an easier way around that, however.) Next you provide a “friendly name” for the database, which as far as I can tell is never referenced again. You also are required to provide an initial size for the database. This is an estimated size, and frankly, most examples I’ve seen use the same value (5 MB) and don’t bother actually trying to figure out what size they really think they need. Your code will begin by opening the database, which is a synchronous API.

db = window.openDatabase("name","1","nice name",5*1024*1024);

Note that I’ve taken the result of the window.openDatabase call and stored it. This lets you perform operations on the database later on. Let’s consider a simple example that just opens up a database and uses the console to display the object itself (Example 5-1).

Example 5-1. test1.html
<!doctype html>
<html>
<head>
    <script type="text/javascript" src =
    "http://ajax.googleapis.com/ajax/libs/jquery/2.1.0/jquery.min.js"></script>
</head>

<body>

<script>
function websqlOK() {
    return "openDatabase" in window;
}

var db;

$(document).ready(function() {

    //No support? Go in the corner and pout.
    if(!websqlOK()) return;

    db = window.openDatabase("db1", "1", "Database 1", 5*1024*1024);

    console.dir(db);

});

</script>
</body>
</html>

While there isn’t much going on here, this shows the basic “how to get started”-type code for working with Web SQL. Figure 5-1 shows what Chrome displays in the console for the database object.

Chrome’s dump of the database object
Figure 5-1. Chrome’s dump of the database object

Working with Transactions

Once you’ve gotten a database object, you can then begin doing, well, everything—and unlike in IndexedDB, working with data in Web SQL is rather simple (assuming you know SQL, of course). Once again, a transaction will be used, and once again, you get to specify either a read-only or read/write transaction, but after that, the code remains the same no matter what you do. All that changes is the SQL. As a basic example, here is how you open a read-only transaction (this assumes you’ve created a Web SQL variable called db):

db.readTransaction(function to do stuff, error handler, success handler);

In real code, this could look like this:

db.readTransaction(function(tx) {
    tx.executeSql("select * from foo");
}, function(e) {
    console.log("Db error ",e);
}, function() {
    console.log("Done");
});

The first argument to the readTransaction call is a function that is provided a transaction object. On that object, you can run executeSql, which, as you might guess, is where you perform SQL queries. The second argument is the error handler and the last is the success handler.

So far, so good. This is where things get a tiny bit confusing. First, here is how the API works in general:

tx.executeSql("sql statement", "array of values", "success handler", 
"error hander");

Disregard the second argument for now; we’ll come back to it. The thing you’ll want to note most is that the order of handlers (success and then error) is the opposite of the transaction call. This is very easy to mess up, so be careful when working with these handlers.

Let’s enhance the initial demo to do some setup work. Before you can store data in a database, you’ll need a table. Luckily, it isn’t difficult to create tables in SQL. Example 5-2 shows how.

Example 5-2. test2.html
<!doctype html>
<html>
<head>
    <script type="text/javascript" src =
    "http://ajax.googleapis.com/ajax/libs/jquery/2.1.0/jquery.min.js"></script>
</head>

<body>

<script>
function websqlOK() {
    return "openDatabase" in window;
}

var db;

$(document).ready(function() {

    //No support? Go in the corner and pout.
    if(!websqlOK()) return;

    db = window.openDatabase("db1", "1", "Database 1", 5*1024*1024);

    db.transaction(function(tx) {
        tx.executeSql("create table if not exists notes(id INTEGER PRIMARY "+
        "KEY AUTOINCREMENT, title TEXT, body TEXT, updated DATE)");
    },dbError,function(tx) {
        ready();
    });

});

function dbError(e) {
    console.log("Error", e);
}

function ready() {
    console.log("Ready to do stuff!");
}
</script>
</body>
</html>

In this version, after the database is opened we create a read/write transaction (using db.transaction). We then use SQL to create a table. The cool thing about this SQL is that it gracefully handles not doing anything if the table already exists. As we stated earlier, Web SQL does include the concept of versioning and does offer a way to perform tasks when versions change, but this style of setup is far simpler and most likely sufficient for your needs. You could execute multiple different SQL statements there to set up as many tables as you need.

Obviously all of this is simple if you know SQL. If you don’t, there are various books and tutorials that can help you. The SQL used in Example 5-2 creates a table called notes. It has an id column that will be the primary key for data, a title and body column that contain text, and an updated column storing a date value.

Now let’s kick it up a notch with a real, but simple, demo in Example 5-3.

Example 5-3. test3.html
<!doctype html>
<html>
<head>
    <script type="text/javascript" src =
    "http://ajax.googleapis.com/ajax/libs/jquery/2.1.0/jquery.min.js"></script>
</head>

<body>

<h2>Add a Note</h2>
<form>
Title: <input type="text" id="title"><br/>
Body:<br/>
<textarea id="body"></textarea><br/>
<button id="addNote">Add Note</button>
</form>

<p/>

<table id="notes" border="1"><tbody></tbody></table>

<script>
function websqlOK() {
    return "openDatabase" in window;
}

var db;

$(document).ready(function() {

    //No support? Go in the corner and pout.
    if(!websqlOK()) return;

    db = window.openDatabase("db1", "1", "Database 1", 5*1024*1024);

    db.transaction(function(tx) {
        tx.executeSql("create table if not exists notes(id INTEGER PRIMARY "+
        "KEY AUTOINCREMENT, title TEXT, body TEXT, updated DATE)");
    },dbError,function(tx) {
        ready();
    });

});

function dbError(e) {
    console.log("Error", e);
}

var $title, $body, $notesTable;

function ready() {
    $("#addNote").on("click", addNote);
    $title = $("#title");
    $body = $("#body");
    $notesTable = $("#notes tbody");
    renderNotes();
}

function addNote(e) {
    e.preventDefault();
    //no validation
    var title = $title.val();
    var body = $body.val();

    db.transaction(function(tx) {
        tx.executeSql("insert into notes(title,body,updated) values(" +
        "'" + title + "','" + body + "'," + (new Date().getTime()) +")");
    },dbError,function(tx) {
        $title.val("");
        $body.val("");
        renderNotes();
    });

}

function renderNotes() {
    db.readTransaction(function(tx) {
        tx.executeSql("select * from notes order by updated desc",[],
        function(tx, results) {
            var rowStr = "";
            for(var i=0;i<results.rows.length;i++) {
                var row = results.rows.item(i);
                //use row.col
                rowStr += "<tr><td>" + row.title + "</td>";
                rowStr += "<td>" + row.body + "</td>";
                var d = new Date();
                d.setTime(row.updated);
                rowStr += "<td>" + d.toDateString() + " " + d.toTimeString();
                rowStr += "</td></tr>";
            };
            $notesTable.empty();
            $notesTable.append(rowStr);
        });
    },dbError);

}
</script>
</body>
</html>

This new version includes a form and an empty table. The form will be used to let the user enter a note (a title and the body), while the table will be used to display existing data. That’s it for the user interface—now let’s dig into the code.

The ready function will be run after the initial table creation SQL is executed. Remember, this SQL safely runs multiple times, as it won’t recreate the table after the first time. We add a simple click handler for the form, store some jQuery variables from the DOM, and immediately run the renderNotes function.

The addNote click handler simply fetches the form values and then creates a SQL statement to handle the insert. This SQL statement is rather brittle. We’ll fix that in the next update. Once the SQL statement is executed, renderNotes is run again to update the display.

Within renderNotes, we again have a transaction, but note the change to a read-only transaction. You can see that we select all rows and order by the updated column so we always get the latest data first. Ignore that empty array argument for now. Once the SQL is executed, we can work with the results. The success handler for executeSql is passed the transaction object itself and the results. This results object is an instance of a SQLResultSet. It has a rows property, which has a length allowing us to loop over it. To get an individual row, the item method is called with the corresponding row number. That row object is just a set of key/value pairs representing the columns in the row. A string is used to construct a table (yes, yes, I know, tables are passé), which is then rendered out to the DOM. Figure 5-2 demonstrates how this looks. (And yes, it could be much better designed.)

The Note form
Figure 5-2. The Note form

So, now that you have a basic idea of how Web SQL works, let’s focus on the insert statement from Example 5-3:

tx.executeSql("insert into notes(title,body,updated) values(" +
"'" + title + "','" + body + "'," + (new Date().getTime()) +")");

When executed, this generates a SQL statement that could look like so:

insert into notes(title, body, updated)
values('some title', 'some body', 1)

The issue with the code is that if the form values themselves included a single quote character, the SQL would break. Typically, allowing user input to drive dynamic SQL leads to something called a SQL injection attack. It’s nasty, but luckily easily fixed. Remember that second argument that was an empty array? Instead of creating a dynamic SQL string with concatenation, you can use “tokens” within the SQL that represent variables. You can then use the array argument to supply those values. Example 5-4 demonstrates how simple this is to put into effect.

Example 5-4. Partial code from test4.html
function addNote(e) {
    e.preventDefault();
    //no validation
    var title = $title.val();
    var body = $body.val();

    db.transaction(function(tx) {
        tx.executeSql("insert into notes(title,body,updated) "+
        "values(?,?,?)", [title, body, new Date().getTime()]);
    },dbError,function(tx) {
        $title.val("");
        $body.val("");
        renderNotes();
    });

}

Notice how the SQL now is a simple string—no embedded variables. Where the variables were, there are now question marks. They will be replaced in the same order as the values included in the array in the next argument.

Inspecting Web SQL with Dev Tools

You can find pretty good support for Web SQL in Chrome’s Dev Tools. Under the Resources tab, you’ll see a section just for Web SQL along with any defined databases. Selecting one and expanding it then lets you select a table to view all the data (see Figure 5-3).

Chrome’s Web SQL view
Figure 5-3. Chrome’s Web SQL view

The empty text field at the bottom of the data display lets you enter the name of a column. Doing so will filter the view to just the primary key and that column. What isn’t terribly obvious is that if you click on the database itself, you’ll see a console where you can enter arbitrary SQL statements (see Figure 5-4).

Running SQL commands in Dev Tools
Figure 5-4. Running SQL commands in Dev Tools

As we mentioned in the beginning of the chapter, you’ll probably not be starting new projects with Web SQL, but if you have to debug an existing one, Chrome’s Dev Tools support can be very useful.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.118.12.157