Working with web sql databases

GFX9.COM share Working with web sql databases, you can download now.



It's an exciting time to be a web developer; so many exciting functionalities and new standards are slowly emerging. In this new Premium tutorial and screencast, we're going to look at one of these new standards: Web SQL Databases.

Scroll down the the bottom to see the full 80-minute screencast portion of this tutorial!


Step 0.1: The Disclaimer

Currently, Web SQL databases are only supported by Safari, Chrome, and Opera 10.50 and higher. Since 3 of the 5 heavy-weights support it, you might be surprised that Firefox doesn’t. According to the HTML5 Doctor, “Firefox are holding off as they feel there's a better implementation than SQLite.” And Internet Explorer: well, it isn’t in version 9; version 10, maybe?


Step 0.2: The Plan

So here’s what we’re going to create: a simple todo-list web app. We’ll be able to create and then delete or complete tasks on two lists: work or home. Of course, this will be anything but full-featured; however, we’ll be able to see the the Web SQL API in action and have a lot of JavaScript fun!

Here’s a screenshot of what we’ll end up with:



Step 1: The HTML

As usual, we’ll start with the HTML frame that you’re oh-so-familiar with:

  
	 
	 
	 
	     
	    Web SQL Task List 
	     
	     
	     
	 
	 
 
	     
	     
	     
	 
	

The important things to notice here are the link and script tags. We’re pulling in three CSS files: the first is Eric Meyer’s reset.css. Secondly, we’ve got the text.css that comes with the 960 grid system. We’ll be building the final stylesheeet (default.css) ourselves in just a couple of minutes;

For scripts, we’re importing jQuery from Google’s CDN. Then, we’ve got two scripts of our own to write. The first is a function that will created our tabbed interface. The second is the tasklist guts.

Now, let’s add the structure and some dummy content to the body:

  
	
Add Task Remove Task Complete Task
  • This is a sample task to assist us in styling.
  • Here's another task
  • And one more

What do we have here? Everything is wrapped up in a div#lists. Inside that, we’ve got two sections: the div.head and div.body. In our div.head, we’ve got an ul and a div#taskTools. The list items in the unordered list will be the tabs for the our task lists. Then, in the toolbar div, we've got three buttons: one to add a new task, one to remove one or more tasks, and one to complete one or more tasks. Within the div.body, we’ll add a div for each list. Inside, we’ll have the list of tasks. Prepended to each list item, we’ll have a span.select; this will provide visual feedback when the user has clicked a task to select it.

Two more things to notice: first, div.head ul and div.body will be empty by default; we’re only putting some dummy content in their now so we can do our styling. The other thing is this: the div that hold the task list has an id that’s the same as the href of the associated tab; also, the active list and tab have a class of “current.”

Now that’s we’ve determined our skeleton, let’s put some skin on it!


Step 2: The CSS

We begin with #lists and its child .head.

  
	#lists { 
	    padding: 10px; 
	} 
	#lists .head ul li { 
	    display:inline-block; 
	    margin-right:5px; 
	} 
	#lists .head ul li a {   
	    background:#c1cbcc; 
	    background: -webkit-gradient(linear, left top, left bottom, from(rgba(255, 255, 255, 0.5)), to(rgba(255, 255, 255, 0))) #aaa; 
	    -webkit-border-top-left-radius:5px; 
	    -webkit-border-top-right-radius:5px; 
	    display:inline-block; 
	    padding:5px 15px; 
	    color:#000; 
	    text-decoration:none; 
	}

We’ve added some padding to #lists; then, we give each of the list-item/tabs some right margins and set display to inline-block. For the anchors within the list items, we’ve give them a background. We’ll first set a regular color, but then we’ll override that with a webkit gradient for some progressive enhancement. We’ll round the corners on the top and, well, the rest is obvious.

Moving on …

  
	#lists .head ul li a.current { 
	    cursor: default; 
	    background: #797979; 
	    background: -webkit-gradient(linear, left top, left bottom, from(#aaa), to(#797979)); 
	} 
	#lists .head ul li a:not(.current):hover { 
	    background: #DDFDFF; 
	    background: -webkit-gradient(linear, left top, left bottom, from(rgba(255, 255, 255, 0)), to(rgba(255, 255, 255, 0.95))) #aaa; 
	}

For the currently selected tab (with the class of current) we’ll set the cursor to the default, so it doesn’t look like a link; Then, we’ll change the background color, again setting both a flat color and a gradient for webkit.

We’re using the CSS3 selector :not() in the second piece; when all the links except the one with the class of current are hovered over, we’ll again set the background.

  
	#lists #taskTools { 
	    background: #797979; 
	} 
	#lists #taskTools span { 
	    display:inline-block; 
	    margin: 10px; 
	}

There’s not much here for the toolbar; but each of the buttons on the toolbar has the class button, so let’s check that out:

  
	.button { 
	    padding:5px 10px; 
	    cursor:pointer; 
	    border-radius:3px; 
	    -webkit-border-radius:3px; 
	    background: -webkit-gradient(linear, left top, left bottom, from(rgba(255, 255, 255, 0.75)), to(rgba(255, 255, 255, 0))) #aaa; 
	    border:1px solid #aaa; 
	} 
	.button:hover { 
	    background: -webkit-gradient(linear, left top, left bottom, from(rgba(255, 255, 255, 0.95)), to(rgba(255, 255, 255, 0.25))) #aaa; 
	    border:1px solid #f3f3f3; 
	}

I won’t insult your intelligence, you know what this does; by the way, if you’re having some trouble getting your head around the gradients, check out Jeffrey Way’s Quick Tip on CSS3 gradients.

The last piece to style is div.body:

  
	#lists .body { 
	    background:#ccc; 
	} 
 
	#lists .body div { 
	    display:none; 
	} 
 
	#lists .body div.current { 
	    display:block; 
	} 
	#lists .body li {    
	    padding:5px 15px; 
	    background:#ccc; 
	} 
 
	#lists .body li:nth-child(2n) { 
	    background: #fff; 
	}

This covers the the div.body wrapper, the divs within (remember, one for each list), and the list items. Notice that we’re setting all the divs to display:none except for the one with the class of current; this way, we can show only one div at a time. Notice that we’re getting an incredibly easy zebra striping effect by using the :nth-child() selector.

  
	#lists .body li .select { 
	    display:inline-block; 
	    width:10px; 
	    height:10px; 
	    background:#ececec; 
	    border:1px solid #494949; 
	    vertical-align:middle; 
	    margin:0 15px 0 0; 
	    cursor:pointer; 
	} 
	#lists .body li .select.selected { 
	    background:#474747; 
	}

This last piece of styling is for the span.select; We want little squares, 10x10px. Then, when they are clicked, we’ll add the selected class, which just darkens the background color.

So here’s what our little webapp should look like now:


That’s all for the CSS: let’s get coding!


Step 3: The JavaScript

We have two JavaScript functions to make; first, a simple tabbed container; second, our tasks API.

Here come the tabs:


Step 3.1: The Tabs Function

There’s not much that the tabs function has to do; when we click on one of the tabs, we’ll figure our which list is associated with it and show that one while hiding all the others.

  
	var TABS = function (selector) {  
	    var container = $(selector), 
 
	        moveToTab = function (tabId, tab) { 
	            if (typeof tabId === 'string') { 
	                tabId = (tabId.indexOf('#') !== 0) ? '#' + tabId : tabId; 
	                if (!tab) { 
	                    tab = $('[href='+ tabId + ']'); 
	                } 
	            } else { 
	                tab = $(tabId); 
	                tabId = tab.attr('href'); 
	            } 
	            $('.body div', container).removeClass('current').filter(tabId).addClass('current'); 
	            $('.head li a', container).removeClass('current').filter(tab).addClass('current'); 
	        }; 
	    $('.head li a', container).live('click', function (evt) { 
	        var id = $(this).attr('href'); 
	            moveToTab(id); 
	    }); 
 
	    return { 
	        moveToTab : moveToTab 
	    }; 
	};

We’ll pass one selector to the function; that will be the parent of both the tab and list sections; in this case, #lists. We’ll create a jQuery object with that selector. Then, we create an important function: moveToTab. This can take two parameters, the tab id word, and the tab element. If the user just passes in a string, we’ll make sure it starts with a hash (#). Then, if there isn’t a tab element, we’ll find the anchor with the href of the tabId.

However, if the tabId isn’t a string, we’ll assume that it’s the tab element. Then, we’ll set tab equal to tabId, and set tabId equal to the href. Now that we’re sure our parameters are correct, we can do our work: we’ll get all the divs inside .body using container as the context; now the jQuery collection holds our lists. We’ll remove the “current” class from all of them; then, we’ll filter out the one we want (remember, the tabId is the selector for the appropriate list div) and add the class “current” to that. Then, we’ll do the same for all the anchors in .head li a.

Now that we’ve got that function written, it’s time to write the event handler. Whenever someone clicks one of the tabs, we’ll get the href of the anchor—which is the id of the list we want to move to. Then, we’ll pass it to the moveToTab function.

Finally, we’ll return an object with moveToTab as a method. This way, someone using our little tabs API could switch to a tab in ways other than the tabs themselves.


Step 3.2: The Task Function

Now, for the feature presentation: the tasks API, starring special guest Web SQL Databases.

  
	var LOCALTODO = function (tabs, listWrap) { 
 
	// ========== VARIABLES ===========  
 
	    var db   = null; 
	    tabs     = $(tabs); 
	    listWrap = $(listWrap); 
 
	}

Here’s the most basic shell of our function; we’ll take two parameters: the tabs list selector and the task list wrapper selector. We’ve only got one function-wide variable here, and that’s the database. Why not initialize it right away? Well, we want to gracefully crash if web SQL isn’t supported in the browser the user is using.

To effect this gracious crash, we shall do as follows:

  
	//   ============ INITIALIZATION WORK ========== BEGIN   
 
	try { 
	     if (window.openDatabase) { 
	       db = openDatabase("tasks", "1.0", "Local todo list database", 200000); 
	       if (db) { 
	         db.transaction(function(tx) { 
	           tx.executeSql("CREATE TABLE IF NOT EXISTS tasklist (id INTEGER UNIQUE, task TEXT, list TEXT, completed BOOLEAN)", [], function (tx, res) { 
	                tx.executeSql("SELECT * FROM tasklist", [], function (tx, res) { 
	                    if (res.rows.length < 1) { 
	                        addTask("Click 'Add Task' to create a new Task", "home"); 
	                        addTask("To remove or complete tasks, click on them to select them before clicking 'Remove Task' or 'Complete Task.'", "work");                          
	                    }                        
	                }); 
	            }); 
	         }); 
	       } else { 
	         console.log('error occurred trying to open DB'); 
	       } 
	     } else { 
	       console.log('Web Databases not supported'); 
	     } 
	   } catch (e) { 
	     console.log('error occurred during DB init, Web Database supported?'); 
	   }

To be completely honest, I stole this general "safety net" structure straight from a project by Remy Sharp. We start by checking to see if the browser supports the openDatabase function. If it does, we'll initiate the database. Here's the pertinent line, taken from the snippet above.

  
	db = openDatabase("tasks", "1.0", "Local todo list database", 200000);

openDatabase takes four parameters: the name of the database, the version of the database, a description of the database, and the size of the database (in bytes, I believe). This function will create the the database if it doesn't exist, or just open it if it does exist.

Back to our safety scaffolding, if the database opens properly, we'll use the transaction method to act on it. Any time you want to perform any CRUD operations on your database, you have to use the transaction method. This is actually great, because if anything goes wrong inside this transaction method, everything will be rolled back and no changes will be made to the database.

So what do you pass to the transaction method? You pass a function, specifically, a function that takes a transaction object as a parameter. This transaction object has a method called executeSql. Here's where the action happens. There are currently three parameters that the executeSql method takes: first is the SQL query; since web SQL is an implementation of SQLite, any query you can write for a SQLite database you can use here. The second is an array of values that will take the place of question marks in your query; this allows you to prevent SQL injection. The final parameter is a success callback function (which takes two parameters, the transaction object and a SQL results object).

Whew! Having covered all that, it should be obvious what we're doing above. If the table "tasklist" does not exist, we'll create it. It will have four fields: id (the primary key), the task, the list it's on, and the completed status. When that executes successfully, we'll run the callback. The callback runs another statement on the transaction object: it gets all the rows in the database. Then, the callback on that checks to see if the table is empty. If it is, it adds two tasks to get the user started (and we'll be writing the addTask function in a minute).

All along the way of this entire process, we've got if statements to catch us if we fall; and it's all wrapped in a try/catch, for failsafe-ness.

Let's move on!

  
	refresh(); 
 
	//  =============== Event Handlers 
 
	$("#addTask").click(function () { 
	    var list = $('.body .current'); 
 
	    $('
  • Add
  • ’) .find(‘span’) .click(function () { var self = $(this); addTask(self.prev().val(), list.attr(‘id’)); self.parent().remove(); }) .end() .prependTo(list.find(‘ul’)); }); $(‘#remTask’).click(function () { var ids = (‘.body .current span.select.selected’).map(function (i, el) { return $(el).parent().attr(‘id’); }).get(); removeTask(ids); }); $(‘#comTask’).click(function () { var ids = $(‘span.select.selected’).map(function (i, el) { return $(el).parent().attr(‘id’); }).get(); completeTask(ids); }); // =========== INITIALIZATION WORK =========== END

    Right at the top here, we’re calling a refresh function. We’re write this next, but it simply sets up the tabs and list user interface. Then, we set up event handlers for the toolbar buttons. First we’ve got the #addTask button. When the button is clicked, we’ll start by getting the current list. Then, we’ll create a list item with a text input and a button. When that button is clicked, we’ll call the addTask function, passing in the value of the text input and the id of the list. Then we’ll remove the list item. This whole structure will be prepended to the front of the list.

    For the #remTask button, we’ll collect all the spans with the classes “select” and “selected” (remember, when the user clicks on a list item, the span at the front will be given the class “selected”). Then, we’ll pass a function to jQuery’s map function, which gives you two paramters: the index of the element and the element itself. This function will return the id attribute of the span’s parent, which is the list item; we haven’t seen this yet, but the id of the list item will be the unique id from its database entry. Once we have all those ids, we call the get method, which strips away all the jQuery goodness and leaves us with a JavaScript array of the ids of the tasks we want to remove. Then, we just pass that array to the removeTask function.

    For the #comTask button, we do exactly the same as we do with the #remTask button, except that instead, we pass the array of ids to the completeTask function.

    That’s the end of our initialization. Let’s now implement the functions we’ve been talking about:

      
    	function refresh() { 
    	    db.transaction(function (tx) { 
    	        tx.executeSql("SELECT * FROM tasklist WHERE completed = ?", [false], function (tx, results) {}); 
    	    }); 
    	}

    This is our start; we select all the rows in the table where the completed field is set to false. Notice that this takes advantage of that second parameter to pass in the values for the SQL statement.

    So what do we want to do with those rows? Put this in the success callback:

      
    	var lists = [], i, len = results.rows.length, 
    	    tasks = {}, 
    	    currList = "home";   
     
    	if (len > 0) { 
    	    tabs.empty(); 
    	    if (listWrap.children(".current").length > 0) { 
    	        currList = listWrap.children('.current').attr('id'); 
    	    } 
    	    listWrap.empty(); 
    	}

    We start by declaring a bunch of variables. Then if len (the number of rows returned from our SQL query) is greater than zero, we clear out the tabs holder. If there’s a list with the class “current”, we’ll catch its id. We do this because we use this function in two cased: when the user loads the page, and when the user changes the database. If the user is looking at a list, we want to make sure they're still looking at it after the list refreshes. Finally, we’ll empty the list wrapper.

    Here’s the next part of our success callback:

      
    	    for ( i = 0; i < len; i++ ) { 
    	    var item     = results.rows.item(i), 
    	        listName = item.list; 
     
    	    if (lists.indexOf(listName) < 0) { 
    	        lists.push(listName); 
    	        tasks[listName] = []; 
    	    } 
    	    tasks[listName].push(item); 
    	}

    Now, we'll loop over each row we got back from the database. We'll store the row in the item variable. We retrieve the row by passing its index to the item method in the rows property on the results object. Now our item variable is an object, with each of the fields in the row as a property. We'll store the list name in a variable, too. Then, if the lists array we made at the beginning of the function doesn't have an entry for that list, we'll push it in. Then, we'll add that same listName as a property of the tasks object we created; it will be an array. Then, we'll push the task into that array. This for loop gives us two things: an array of the lists, and an object with an array of each list's tasks as a property.

    Here's the last section of our success callback, where the UI materializes:

      
    	$.each(lists, function (i, val) { 
    	    var listUl, j, li, 
    	        len = tasks[val].length 
    	        currClass = (val === currList) ? "current" : ""; 
     
    	    $('
  • ’).find(‘a’).attr(‘href’, ‘#’ + val).addClass(currClass).text(val).end().prependTo(tabs); listUl = $(‘
      ’); for ( j = 0; j < len; j++ ) { li = $(“
    • ”).attr(‘id’, tasks[val][j].id); li.append(tasks[val][j].task); li.find(‘span’).addClass(‘select’).end().click(function () { $(‘span’, this).toggleClass(‘selected’); }).appendTo(listUl); } listUl.wrap(‘
      ’).parent(‘div’).attr(‘id’, val).addClass(currClass).appendTo(listWrap); });

    This times, we’re using jQuery’s each to loop over that array of list names. The function we pass in takes two parameters: the index of the object and the value from the array. As usual, we start with the variables. the important ones here are len (the number of tasks in the respective list), and currClass (if the name of the list we’re processing is the same as the name of currList, it will be set to “current”).

    Then, we create a list item with an anchor inside it. That anchor will have a href pointing to the corresponding list. We’ll give it a class of currClass (which may or may not be “current”), set its text equal to the name of the list, and append the whole list item to tabs.

    Next, we’ll create an unordered list. We then loop over each item in the list’s corresponding array in tasks. We’re create a list item for it, making sure to set the id of the list item to the id of the task. We then append the text of the task to the list item. Next, we’ll find the span and give it the “select” class. We’ll also give the whole list item a click event handler, which will toggle the “selected” class on that span. Finally, we’ll append the list item to our list.

    After it’s looped through all that, we’ll wrap the list item in a div. We’ll have that div the id of the list name, and give it the currClass class (which may or may not be “current”). Finally, we’ll append the whole thing to the list wrapper.

    That’s the end of our refresh function; like I said, we call this when the page loads and whenever the user makes a change. We could probably write something more optimized from user interactions, but this lazy way will work.

    Now what about those functions for adding, removing, and completing tasks?

      
    	function addTask (task, list) {      
    	    db.transaction(function (tx) { 
    	            tx.executeSql('INSERT INTO tasklist (id, task, list, completed) VALUES (?, ?, ?, ?)', [+new Date(), task, list, false], function (tx, res) { 
    	                refresh(); 
    	            }); 
    	    }); 
    	}

    Here’s our addTask function; it takes a task and a list. Just as you’ve seen already, we call the transaction method on the database, passing it a function. That function calls the the executeSql method on the transaction object. This time, we’re inserting a record into the database. We’re using question marks in the values section, and passing those values in as the second parameter. The ID is the the date; using the unary plus prefix converts an object to a number; in this case, it will be a Unix timestamp. Finally, if the insert is successful, we’ll call refresh.

      
    	function removeTask (taskId) { 
    	    var query = [], i; 
     
    	    if (typeof taskId === 'string') { 
    	        taskId = [taskId]; 
    	    } 
     
    	    for (var i = 0; i < taskId.length; i++ ) { 
    	        query.push(" id = " + taskId[i]); 
    	    } 
    	    query = "DELETE FROM tasklist WHERE" + query.join(" or"); 
    	    db.transaction(function (tx) { 
    	        tx.executeSql(query, [], function (tx, res) { 
    	            refresh(); 
    	        });  
    	    }); 
    	}

    The removeTask method is a little more complicated. We take a task id, but this could also be an array of task ids. If we get a single string, put it in an array. Then, we loop over each item in that array and concatenate it with ” id = ” , pushing the resulting string into a query array we created at the top of the function. We’ll create the final query by joining the array elements with ” or” and tacking “DELETE FROM tasklist WHERE” on the beginning. Then, execute the query and refresh the UI.

      
    	function completeTask (taskId) { 
    	    var query = [], i; 
     
    	    if (typeof taskId === 'string') { 
    	        taskId = [taskId]; 
    	    } 
     
    	    for (var i = 0; i < taskId.length; i++ ) { 
    	        query.push(" id = " + taskId[i]); 
    	    } 
    	    query = "UPDATE tasklist SET completed = 'true' WHERE" + query.join(" or"); 
    	    db.transaction(function (tx) { 
    	        tx.executeSql(query, [], function (tx, res) { 
    	            refresh();           
    	        });  
    	    }); 
    	}

    The completeTask method is very similar to the removeTask method, except that we “UPDATE tasklist SET completed = ‘true’” for each of the ids received. And of course, refresh the UI.

    Believe it or not, that’s the end of our todo list API; it’s not hard to implement all our work. Just add a script tag to index.html and throw this in.

      
    	LOCALTODO('#lists .head ul', '#lists .body'); 
     
    	TABS('#lists');

    Now, everything should be working properly. Here’s what we end up with:



    That’s All!

    Web SQL Databases is one of the steps toward standard offline web support. It’s fun to see where these new standards are going, so give this one a try and let me know what you think in the comments!






    Similar content