The localStorage and sessionStorage methods give us an easy way to store simple name/value pairs on the client’s computer, but sometimes we need more than that. The HTML5 specification initially introduced the ability to store data in relational databases. It’s since been spun off into a separate specification called Web SQL Storage.6 If you have even
a basic background in writing SQL statements, you’ll feel right at home in no time. To get you comfortable, we’ll use Web SQL Storage to create, retrieve, update, and destroy notes in a client-side database.
CRUD in Your Browser
The term CRUD, an acronym for “Create, Retrieve, Update, and Delete,” 7 pretty much describes what we can do with our client-side database. The specification and implementations allow us to insert, select, update, and delete records.
AwesomeCo wants to equip their sales team with a simple application to collect notes while they’re on the road. This application will need to let users create new notes, as well as update and delete existing ones. To change existing notes, we’ll need to let users retrieve them from the database.
Here are the SQL statements we’ll need to write in order to make this happen:
The Notes Interface
The Interface for the notes application consists of a left sidebar that will have a list of the notes already taken and a form on the right side with a title field and a larger text area for the note itself. Look at Figure 9.2, on the following page to see what we’re building.
To start, we need to code up the interface.
<!doctype html>
<html>
<head>
<title>AwesomeNotes</title>
<1 ink rel=”stylesheet” href=”style.css”>
< s c r i p t type=”text/javascript”
charset= “utf-8”
src=
“http://aj ax.googleapis.com/ajax/1ibs/jquery/1.4.2/jquery.min.js”>
</script>
< s c r i p t type=”text/javascript”
charset=”utf-8″ src=”javascripts/notes.js”>
</script>
</head><body>
<section id=”sidebar”>
<input type=”button” id=”new_button” value=”New note”>
<ul id=”notes”>
</ul>
</section>
<section id=”main”>
<form>
<ol>
<li>
<input type=”submit” id=”save_button” value=”Save”>
<input type=”submit” id=”delete_button” value=”Delete”>
</li>
<li>
<1abel for=”title”>Title</label>
<input type=”text” id=”title”>
</li>
<li>
<label for=”note”>Note</label>
<textarea id=”note”x/textarea>
</li>
</ol>
</form>
</section>
</body>
</html>
We define the sidebar and main regions using section tags, and we have given IDs to each of the important user interface controls like the Save button. This will make it easier for us to locate elements so that we can attach event listeners.
We’ll also need a style sheet so that we can make this look more like the figure, style.ess looks like this:
# s i d e b a r , #main{
d i s p l a y : b l o c k ;
f l o a t : l e f t ;
# s i d e b a r {
w i d t h : 2 5%;
#mai n{
w i d t h : 75%;
f o rm o l {
l i s t – s t y l e : none;
m a r g i n : 0;
p a d d i n g : 0;
}
f o rm 1 i {
p a d d i n g : 0;
m a r g i n : 0;
}
f o rm l i l a b e l {
d i s p l a y : b l o c k ;
}
# t i t l e , #note{
w i d t h : 100%;
f o n t – s i z e : 20px;
b o r d e r : lpx s o l i d #000;
# t i t l e{
h e i g h t : 20px;
}
# n o t e {
h e i g h t : 40px;
}
This style sheet turns off the bullet points, sizes the text areas, and lays things out in two columns. Now that we have the interface done, we can build the JavaScript we need to make this work.
Connecting to the Database
We need to make a connection and create a database:
/ / Database reference
var db = null ;
/ / Creates a connection to the l o c a l database
connectToDB = functionO
{
db = window.openDatabaseC’awesome_notes’, ‘1.0’ ,
‘AwesomeNotes Database’, 1024*1024*3);
We’re declaring the db variable at the top of our script. Doing this makes it available to the rest of the methods we’ll create.8 We then declare the method to connect to the database by using the window.openDatabase method. This takes the name of the database, a version number, a description, and a size parameter.
Creating the Notes Table
Our notes table needs three columns:
c r e a t e N o t e s T a b l e = functionO
{
d b . t r a n s a c t ! on(function( t x ) {
t x . e x e c u t e S q l (
“CREATE TABLE notes ( i d INTEGER
PRIMARY KEY, t i t l e TEXT, note TEXT)”, [] ,
function(){ a l e r t ( ‘ N o t e s database created successfully!’); },
function( t x , e r r o r ) { a l e r t ( e r r o r . m e s s a g e ) ; } );
} ) ;
We fire the SQL statement inside a transaction, and the transaction has two callback methods: one for a successful execution and one for a failure. This is the pattern we’ll use for each of our actions.
Note that the executeSqK) method also takes an array as its second parameter. This array is for binding placeholders in the SQL to variables. This lets us avoid string concatenation and is similar to prepared statements in other languages. In this case, the array is empty because
we have no placeholders in our query to populate.
Now that we have our first table, we can make this application actually do something.
Loading Notes
When the application loads, we want to connect to the database, create the table if it doesn’t already exist, and then fetch any existing notes from the database.
/ / loads a l l records from the notes t a b l e of the database;
f e t c h N o t e s = function(){
d b . t r a n s a c t i o n ( f u n c t i o n ( t x ) {
t x . e x e c u t e S q l (‘SELECT id, title, note FROM notes ‘ , [] ,
f u n c t i o n C S Q L T r a n s a c t i o n , d a t a ) {
for (var i = 0 ; i < d a t a . r o w s . 1 e n g t h ; ++i) {
var row = d a t a . r o w s . i t e r n ( i ) ;
var i d = row[ ‘id’] ;
var t i t l e = row[ ‘ ti tie ‘ ] ;
addToNotesLi s t ( i d , t i t l e ) ;
}
} ) ;
} ) ;
This method grabs the results from the database. If it’s successful, it loops over the results and calls the addNoteToList method that we define to look like this:
/ / Adds the l i s t i t em t o the l i s t of notes, given an id and a t i t l e .
addToNotesLi s t = function( i d , t i t l e ) {
var notes = $(“#notes”);
var i t em = $ ( ” < l i > ” ) ;
i t e m . a t t r C ” d a t a – i d ” , i d ) ;
i t e m . h t m l ( t i t l e ) ;
n o t e s . a p p e n d ( i t e m ) ;
};
We’re embedding the ID of the record Into a custom data attribute. We’ll use that ID to locate the record to load when the user clicks the list Item. We then add the new list Item we create to the unordered list In our Interface with the ID of notes. Now we need to add code to load that Item Into the form when we select a note from this list.
Fetching a Specific Record
We could add a click event to each list Item, but a more practical approach Is to watch any clicks on the unordered list and then determine which one was clicked. This way, when we add new entries to the list (like when we add a new note), we don’t have to add the click event to the list.
Within our jQuery function, we’ll add this code:
$(“#notes”).cli ck(function(event){
if (SCevent.target),is( ‘li ‘ ) ) {
var element = SCevent.target);
loadNoteCelement.attr(“data-id”)) ;
}
This fires off the loadNoteO method, which looks like this:
loadNote = function(id){
db.transaction(function(tx) {
tx.executeSql (‘SELECT id, title, note FROM notes where id = ?’, [id],
functionCSQLTransaction, data){
var row = data.rows.itern(0);
var title = $(“#tit7e”) ;
var note = $(“#note”);
title.val (row[ “ti t7e”]) ;
ti tl e .attrC “data-id” , row[ “id”]) ;
note.val(row[“note”]);
$C “#delete_button”) . showO ;
} ) ;
} ) ;
This method looks a lot like the previous fetchNotesO method. It fires a SQL statement, and we then handle the success path. This time, the statement contains a question-mark placeholder, and the actual value is in the second parameter as a member of the array.
When we have found a record, we display it in the form. This method also activates the Delete button and embeds the ID of the record into a custom data attribute so that updates can easily be handled. Our Save button will check for the existence of the ID. If one exists, we’ll update the record. If one is missing, we’ll assume it’s a new record. Let’s write that bit of logic next.
Inserting, Updating, and Deleting Records
When a user clicks the Save button, we want to trigger code to either insert a new record or update the existing one. We’ll add a click event handler to the Save button by placing this code inside the j Query function:
$(“#save_button”).cli ck(function(event){
event.preventDefault();
var title = $(“#tit7e”) ;
var note = $(“#note”);
i f ( t i tle.attr(“data-id”)){
updateNote(title, note);
}el se{
insertNoteCtitle, note);
}
This method checks the data-id attribute of the form’s title field. If it has no ID, the form assumes we’re inserting a new record and invokes the insertNote method, which looks like this:
i{n sertNote = functionCtitle, note)
db.transact!on(function(tx){
tx.executeSql (“INSERT INTO notes (title, note) VALUES (?, ?)”,
[title.val () , note .val ()] ,
function(tx, result){
var id = result.insertld ;
al ert( ‘Record ‘ + id+ ‘ saved!’);
title.attr(“data-id”, result.insertld );
addToNotesLi st(id , title.val ()) ;
$ C”#delete_button”).show();
} ,functionO {
a l e r t C ‘ T h e note could not be saved.’);
}
);
} ) ;
The insertNoteO method inserts the record into the database and uses the insertld property of the resultset to get the ID that was just inserted. We then apply this to the “title” form field as a custom data attribute and invoke the addToNotesListO method to add the note to our list on the side of the page.
Next, we need to handle updates. The updateNoteO method looks just like the rest of the methods we’ve added so far:
updateNote = function( t i t l e , note)
{
var id = t i t l e . a t t r C ” d a t a – i d ” ) ;
d b . t r a n s a c t i o n(function( t x ) {
t x . e x e c u t e S q l (“UPDATE notes set title = ?, note = ? where id = ?”,
[ t i t l e . v a l () , note . val 0 , i d ] ,
function( t x , r e s u l t ) {
a l e r t ( ‘Record ‘ + id + ‘ updated!’);
$C”#notes>li[data-id=” + id + “] ” ) . h t m l ( t i t l e .val ()) ;
},
functionO {
a l e r t C ‘ T h e note was not updated!’);
}
);
} ) ;
When the update statement is successful, we update the title of the note in our list of notes by finding the element with the data-id field with the value of the ID we just updated.
As for deleting records, it’s almost the same. We need a handler for the delete event like this:
$ C”#delete_button”).cli ckCfunction(event){
e v e n t . p r e v e n t D e f a u l t ( ) ;
var t i t l e = $ ( ” # t i t 7 e ” ) ;
d e l e t e N o t e ( t i t l e ) ;
} ) ;
Then we need the delete method itself, which not only removes the record from the database but also removes it from the list of notes in the sidebar.
d e l e t e N o t e = f u n c t i o n ( t i t l e )
{
var id = t i t l e . a t t r ( ” d a t a – i d ” ) ;
d b . t r a n s a c t i o n ( f u n c t i o n ( t x ) {
t x . e x e c u t e S q l (“DELETE from notes where id = ?”, [ i d ] ,
function( t x , r e s u l t ) {
a l e r t ( ‘ R e c o r d ‘ + id + ‘ deleted!’);
$(“#notes>li[data-id=” + id + “] “) . remove() ;
} ,
functionO {
a l e r t ( ‘ T h e note was not deleted!’) ;
}
);
} ) ;
Now we just need to clear out the form so we can create a new record without accidentally duplicating an existing one.
Wrapping Up
Our notes application is mostly complete. We just have to activate the New button, which clears the form out when clicked so a user can create a new note after they’ve edited an existing one. We’ll use the same pattern as before—we’ll start with the event handler inside the j Query function for the New button:
$(“#new_button”).cli ck(function( e v e n t ){
e v e n t . p r e v e n t D e f a u l t ( ) ;
newNoteO ;
} ) ;
/ / e n d : n e w b u t t o n
newNoteO ;
Next we’ll clear out the data-id attribute of the “title” field and remove the values from the forms. We’ll also hide the Delete button from the interface.
newNote = function(){
$(“#delete_button”) . h i d e O ;
var t i t l e = $ ( ” # t i ‘ t 7 e ” ) ;
t i t l e . r e m o v e A t t r C”data-id”);
t i t l e . v a l (“”) ;
var note = $ ( ” # n o t e ” ) ;
n o t e . val(“”) ;
}
We should call this newForm method from within our jQuery function when the page loads so that the form Is ready to be used. This way, the Delete button Is hidden too.
That’s all there Is to It. Our application works on IPhones, Android devices, and desktop machines running Chrome, Safari, and Opera. However, there’s little chance this will ever work In Flrefox, and It’s not supported In Internet Explorer either.
Falling Back
Unlike our other solutions, there are no good libraries available that would let us Implement SQL storage ourselves, so we have no way to provide support to Internet Explorer users natively. However, if this type of application is something you think could be useful, you could
convince your users to use Google Chrome, which works on all platforms, for this specific application. That’s not an unheard of practice, especially if using an alternative browser allows you to build an internal application that could be made to work on mobile devices as well.
Another alternative is to use the Google Chrome Frame plug-in.9 Add this to the top of your HTML page right below the head tag:
<meta http-equiv=”X-UA-Compatible” content=”chrome=l”>
This snippet gets read by the Google Chrome Frame plug-in and activates it for this page.
If you want to detect the presence of the plug-in and prompt your users to install it if it doesn’t exist, you can add this snippet right above the closing body tag:
< s c r i p t type=”text/javascript”
src=
“http://aj ax.googleapis.com/aj ax/1i bs/ch rome-frame/l/CFInstall.min.js”>
</script>
<script>
window.attachEventC”on7oad”, f u n c t i o n O {
C F I n s t a l 1 . c h e c k ( {
mode: “inline”, // the d e f a u l t
node: “prompt”
});
});
</script>
This will give the user an option to install the plug-in so they can work with your site.
Google Chrome Frame may not be a viable solution for a web application meant to be used by the general public, but it works well for internal applications like the one we just wrote. There may be corporate IT policies that prohibit something like this, but I’ll leave that up to you
to work out how you can get something like this approved if you’re in that situation. Installing a plug-in is certainly more cost-effective than writing your own SQL database system.
With HTML5’s Offline support,10 we can use HTML and related technologies to build applications that can still function while disconnected from the Internet. This is especially useful for developing applications for mobile devices that may drop connections.
This technique works in Firefox, Chrome, and Safari, as well as on the iOS and Android 2.0 devices, but there’s no fallback solution that will work to provide offline support for Internet Explorer.
AwesomeCo just bought its sales team some iPads, and they’d like to make the notes application we developed in Storing Data in a Client-Side Relational Database, on page 181, work offline. Thanks to the HTML5 manifest file, that will be a simple task.
Defining a Cache with the Manifest
The manifest file contains a list of all the web application’s client-side files that need to exist in the client browser’s cache in order to work offline. Every file that the application will reference needs to be listed here in order for things to work properly. The only exception to this is that the file that includes the manifest doesn’t need to be listed; it is cached implicitly.
Create a file called notes.manifest. Its contents should look like this:
CACHE MANIFEST
# v = 1 . 0 . 0
/ s t y l e . e s s
/ j a v a s c r i p t s / n o t e s . j s
/ j a v a s c r i p t s / j q u e r y . m i n . j s
The version comment in this file gives us something we can change so that browsers will know that they should fetch new versions of our files. When we change our code, we need to modify the manifest.
Also, we’ve been letting Google host jQuery for us, but that won’t work if we want our application to work offline, so we need to download jQuery and modify our script tag to load jQuery from our javascripts folder.
< s c r i p t t y p e = ” t e x t /javascript”
c h a r s e t = “utf-8″
src=”javascripts/jquery.min.js”>
< / s c r i p t >
Next, we need to link the manifest file to our HTML document. We do this by changing the html element to this:
<html manifest=”notes.manifest”>
That’s all we need to do. There’s just one little catch—the manifest file has to be served by a web server, because the manifest must be served using the text/cache-manifest MIME type. If you’re using Apache, you can set the MIME type in an .htaccess like this:
AddType t e x t / c a c h e – m a n i f e s t .manifest
After we request our notes application the first time, the files listed in the manifest get downloaded and cached. We can then disconnect from the network and use this application offline as many times as we want.
Be sure to investigate the specification. The manifest file has more complex options you can use. For example, you can specify that certain things should not be cached and should never be accessed offline, which is useful for ignoring certain dynamic files.
Manifest and Caching
When you’re working with your application in development mode, you are going to want to disable any caching on your web server. By default, many web servers cache files by setting headers that tell browsers not to fetch a new copy of a file for a given time. This can trip you up while you’re adding things to your manifest file.
If you use Apache, you can disable caching by adding this to your .htaccess file.
E x p i r e s A c t i v e On
E x p i r e s D e f a u l t “access”
This disables caching on the entire directory, so It’s not something you want to do In production. But this will ensure that your browser will always request a new version of your manifest file.
If you change a file listed In your manifest, you’ll want to modify the manifest file too, by changing the version number comment we added
The Future
Features like localStorage and Web SQL Databases give developers the ability to build applications In the browser that don’t have to be connected to a web server. Applications like the ones we worked on run on an IPad or Android device as well, and when we combine them with the HTML5 manifest file, we can build offline rich applications using familiar tools Instead of proprietary platforms. As more browsers enable support, developers will be able to leverage them more, creating applications that run on multiple platforms and devices, that store data locally, and that could sync up when connected.
The future of Web SQL Storage Is unknown. Mozllla has no plans to Implement It In Flrefox, and the W3C Is choosing Instead to move forward Implementing the IndexedDB specification. We’ll talk more about that specification In Section 11.5, Indexed Database API, on page 229.
However, Web SQL Storage has been In use on the IOS and Android devices for a while, and It’s likely to stay. This specification could be extremely useful to you if you’re developing applications in that space.