HTML5 Web SQL

Learning By Doing - Here we try to provide some sample codes and links to external resources. There is a lot of HTML5 materials.

HTML5 SQL Database

Database Webapp works online and offline

This is HTML5, javascript sample to implement DoTo list. The javascript portion created a database by openDatabase() and then execute the query by executeSql(). After you add items, they will store locally. The data will be available for offline and reopen the page.

Live Demo

Download the source files - todolist.html

<!DOCTYPE html> 
<html manifest="sqldb.manifest"> 
<head> 
<meta http-equiv="content-type" content="text/html; charset=UTF-8"/> 
<title>SQL Database Sample</title> 
<script>
var webdb = {};
webdb.db = null;
    
webdb.open = function() {   
  var dbSize = 5 * 1024 * 1024; // 5MB
  webdb.db = openDatabase("Todo", "1.0", "Todo manager", dbSize);
}
  
webdb.createTable = function() {
  var db = webdb.db;
  db.transaction(function(tx) {
    tx.executeSql("CREATE TABLE IF NOT EXISTS todo(ID INTEGER PRIMARY KEY ASC, todo TEXT, added_on DATETIME)", []);
  });
}
  
webdb.addTodo = function(todoText) {
  var db = webdb.db;
  db.transaction(function(tx){
    var addedOn = new Date();
    tx.executeSql("INSERT INTO todo(todo, added_on) VALUES (?,?)",
      [todoText, addedOn],
      webdb.onSuccess,
      webdb.onError);
    });
}
  
webdb.onError = function(tx, e) {
  alert("There has been an error: " + e.message);
}
  
webdb.onSuccess = function(tx, r) {
  // re-render the data.
  webdb.getAllTodoItems(loadTodoItems);
}


webdb.getAllTodoItems = function(renderFunc) {
  var db = webdb.db;
  db.transaction(function(tx) {
    tx.executeSql("SELECT * FROM todo", [], renderFunc,
    webdb.onError);
  });
}

webdb.deleteTodo = function(id) {
  var db = webdb.db;
  db.transaction(function(tx){
    tx.executeSql("DELETE FROM todo WHERE ID=?", [id],
      webdb.onSuccess,
      webdb.onError);
    });
}
  
function loadTodoItems(tx, rs) {
  var rowOutput = "";
  var todoItems = document.getElementById("todoItems");
  for (var i=0; i < rs.rows.length; i++) {
    rowOutput += renderTodo(rs.rows.item(i));
  }

  todoItems.innerHTML = rowOutput;
}

function renderTodo(row) {
  return "<li>" + row.todo  + " [<a href='javascript:void(0);'  onclick='webdb.deleteTodo(" + row.ID +");'>Delete</a>]</li>";
}

function init() {
  webdb.open();
  webdb.createTable();
  webdb.getAllTodoItems(loadTodoItems);
}

function addTodo() {
  var todo = document.getElementById("todo");
  webdb.addTodo(todo.value);
  todo.value = "";
}
</script>
</head>
  <body onload="init();">
    <h1>Zetakey: Offline SQL-based database.</h1>
    <ul id="todoItems">
    </ul>
    <form method="post" onsubmit="addTodo(); return false;">
      <input type="text" id="todo" name="todo" placeholder="Add Todo Item Here!" style="width: 200px;" />
      <input type="submit" value="Add Todo Item"/>
    </form>
  </body>
</html>​

Create the manifest file "sqldb.manifest"

CACHE MANIFEST
index.html

Add the manifest type to .htaccess

AddType text/cache-manifest .manifest