Source Javascript for blog post http://benpoole.com/weblog/201106302211

winkling.js

/*
  winkling.js, v1.0.0
  @author Ben Poole, http://benpoole.com
  Example Javascript for playing with the local Web SQL database
  @see http://benpoole.com/weblog/201106302211
*/


// Open / initialise the db - this will fail in browsers like Firefox & IE
var db = openDatabase("winkles", "1.0", "Winkles Of The World", 32678);


// Create winkles table (if required)
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);");
});


// This is the SAVE function, called on submission of form
var saveWinkle = function(values, successCallback){
  if(values.id=="")
  {
    console.log("Saving a new winkle");
    db.transaction(function(transaction){
      transaction.executeSql(("INSERT INTO winkles (winklename, location) VALUES (?, ?);"), 
      [values.winklename, values.location], 
      function(transaction, results){successCallback(results);}, errCallback);
    });
  } else {
    console.log("Saving an existing winkle - id " + values.id);
    db.transaction(function(transaction){
      transaction.executeSql(("UPDATE winkles SET winklename = ?, location = ? WHERE id = ?;"), 
      [values.winklename, values.location, values.id], 
      function(transaction, results){successCallback(results);}, errCallback);
    });
  }
};


/*  This is a LOAD function, which can pull all winkles for a given 
    location (or just all winkles if location is blank)
*/
var loadWinkles = function(location, successCallback){
  
  if(location==""){
    console.log("No location. Loading all");
    db.transaction(function(transaction){
      transaction.executeSql(("SELECT * FROM winkles"), [],
        function(transaction, results){successCallback(results);}, errCallback);
    });
  } else {
    console.log("Loading winkles for " + location);
    db.transaction(function(transaction){
      transaction.executeSql(("SELECT * FROM winkles WHERE location=?"), [location],
        function(transaction, results){successCallback(results);}, errCallback);
    });
  }
};



// This is a LOAD function for a single record (@see loadMe())
var loadRecord = function(id, successCallback){
  if(id==""){
    console.log("No id passed in. Stopping");
    return;
  } else {
    console.log("Loading winkle id " + id);
    db.transaction(function(transaction){
      transaction.executeSql(("SELECT * FROM winkles WHERE id=?"), [id],
        function(transaction, results){successCallback(results);}, errCallback);
    });
  }
};




// This is a success CALLBACK to load data into a page (@see showWinkles())
var updatePage = function(results){
  var list = $("#winkle-list");
  list.empty();
  console.dir(results);
  if (results.rows.length==0){
    alert("Alas, there be no winkles here.");
  } else {
    $.each(results.rows, function(rowIndex){
      var row = results.rows.item(rowIndex);
      list.append( "<li>" + row.winklename + ", " + row.location + "</li>");
    });
  }
};


/*  This is a success CALLBACK to load data into a page with links
    (@see loadWinkles() call)
*/
var updatePageWithLinks = function(results){
  var list = $("#winkle-list");
  list.empty();
  console.dir(results);
  if (results.rows.length==0){
    list.append("<li>Alas, there be no winkles saved yet</li>");
  } else {
    $.each(results.rows, function(rowIndex){
      var row = results.rows.item(rowIndex);
      list.append( "<li><a href='#' onclick='loadMe(" + row.id + ");'>" + 
      row.winklename + ", " + row.location + "</a></li>");
    });
  }
};


/*  Inject a given winkle's data back into the form. Calls to this 
    function are written to page by updatePageWithLinks()
*/
function loadMe(id){

  // Callback once record is found via loadRecord()
  var loadFormWithData = function(results){
    if(results.rows.length > 0){
      var row = results.rows.item(0);
      $("#winklename").val(row.winklename);
      $("#location").val(row.location);
      $("#id").val(row.id);

      // You can do stuff like this if you have lots of fields
      /*  $.each(row, function(key, val){
            $('[name="'+key+'"]').val(val);
          }
      */
    }else{
      console.log("Doh, can't find record");
    }
  }


  console.log("Loading winkle id " + id);
  // Invoke SQL load which has loadFormWithData() as a callback
  loadRecord(id, loadFormWithData);
}


// Generic error callback for db transactions
var errCallback = function(){
  alert("Oh noes! There haz bin a datamabase error!");
}


// Helper function. Uses jQuery to serialize a form's data into JSON
$.fn.serializeJSON=function() {
  var json = {};
  jQuery.map($(this).serializeArray(), function(n, i){json[n['name']] = n['value'];});
  return json;
};

/*  Helper function which simplt resets the main form. Very 
    specific for our app but I don't care, so ner.
*/
function resetWinkle(){
  $('#winklename').val('');
  $('#location').val('');
  $('#id').val('');
}