{% extends "tutorial.html" %}

{% block html5badge %}
<img src="/static/images/identity/html5-badge-h-storage.png" width="133" height="64" alt="This article is powered by HTML5 Offline &amp; Storage" title="This article is powered by HTML5 Offline &amp; Storage" />
{% endblock %}

{% block iscompatible %}
  return typeof window.openDatabase == "function" ? true : false;
{% endblock %}
{% block head %}
{% if is_mobile %}
  <script>  
    var html5rocks = {};
    html5rocks.webdb = {};
    html5rocks.webdb.db = null;

    html5rocks.webdb.open = function() {
      var dbSize = 5 * 1024 * 1024; // 5MB
      html5rocks.webdb.db = openDatabase("Todo", "1.0", "Todo manager", dbSize);
    }

    html5rocks.webdb.createTable = function() {
      var db = html5rocks.webdb.db;
      db.transaction(function(tx) {
        tx.executeSql("CREATE TABLE IF NOT EXISTS todo(ID INTEGER PRIMARY KEY ASC, todo TEXT, added_on DATETIME)", []);
      });
    }

    html5rocks.webdb.addTodo = function(todoText) {
      var db = html5rocks.webdb.db;
      db.transaction(function(tx){
        var addedOn = new Date();
        tx.executeSql("INSERT INTO todo(todo, added_on) VALUES (?,?)",
            [todoText, addedOn],
            html5rocks.webdb.onSuccess,
            html5rocks.webdb.onError);
       });
    }

    html5rocks.webdb.onError = function(tx, e) {
      alert("There has been an error: " + e.message);
    }

    html5rocks.webdb.onSuccess = function(tx, r) {
      // re-render the data.
      html5rocks.webdb.getAllTodoItems(loadTodoItems);
    }

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

    html5rocks.webdb.deleteTodo = function(id) {
      var db = html5rocks.webdb.db;
      db.transaction(function(tx){
        tx.executeSql("DELETE FROM todo WHERE ID=?", [id],
            html5rocks.webdb.onSuccess,
            html5rocks.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='html5rocks.webdb.deleteTodo(" + row.ID +");'>Delete</a>]</li>";
    }

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

    function addTodo() {
      var todo = document.getElementById("todo");
      html5rocks.webdb.addTodo(todo.value);
      todo.value = "";
    }
  </script>
{% endif %}
{% endblock %}

{% block onload %}{% if is_mobile %}init();{% endif %}{% endblock %}

{% block content %}
  <h2 id="toc-intro">Introduction</h2>
  <p>
    <a href="http://dev.w3.org/html5/webdatabase">Web Databases</a> are new
    in HTML5. Web Databases are hosted and persisted inside a user's browser.
    By allowing developers to create applications with rich query abilities 
    it is envisioned that a new breed of web applications will emerge that
    have the ability to work online and off-line.
  </p>
  <p>
    On November 18, 2010, the <a href="http://www.w3.org/TR/webdatabase/">W3C
    announced</a> that Web SQL database is a deprecated specification. This is a
    recommendation for web developers to no longer use the technology as
    effectively the spec will receive no new updates and browser vendors aren't
    encouraged to support this technology. Many major browsers including Chrome,
    Safari, Opera and nearly all Webkit based mobile devices support WebSQL and
    will likely maintain support for the foreseeable future.
  </p>
  <p class="notice" style="text-align:center;">
    This tutorial is also <a href="/tutorials/indexeddb/todo/">
    available written using "IndexedDB"</a>, the replacement offline storage 
    technology.
  </p>
  <p>
    The example code in this article demonstrates how to create a very simple
    todo list manager.  It is a very high level tour of some of the features
    available in HTML5.
  </p>
  <h2 id="toc-prereqs">Pre-requisites</h2>
  <p>
    This sample uses a namespace to encapsulate the database logic.
  </p>
  <pre class="prettyprint">var html5rocks = {};
html5rocks.webdb = {};</pre>
  <h2 id="toc-transactions">Asynchronous and Transactional</h2>
  <p>
    In the majority of cases where you are using Web Database 
    support you will be using the <a
      href="http://dev.w3.org/html5/webdatabase/#asynchronous-database-api">Asynchronous API</a>.  The Asynchronous API
    is a non-blocking system and as such will not get data
    through return values, but rather will get data delivered to a defined
    callback function.
  </p>
  <p>
    The Web Database support through HTML is transactional.  It is not
     possible to execute SQL statements outside of a transaction.
    There are two types of transactions: read/write transactions
    (<em>transaction()</em>) and read
    only transactions (<em>readTransaction()</em>). Please note, read/write will lock the entire database.
  </p>
  <h2 id="toc-step1">Step 1. Opening the database</h2>
  <p>The database needs to be opened before it can be accessed.  
    You need to define the name, version, description and the size of the database.
  </p>
  <pre class="prettyprint">html5rocks.webdb.db = null;

html5rocks.webdb.open = function() {
  var dbSize = 5 * 1024 * 1024; // 5MB
  html5rocks.webdb.db = openDatabase("Todo", "1.0", "Todo manager", dbSize);
}

html5rocks.webdb.onError = function(tx, e) {
  alert("There has been an error: " + e.message);
}

html5rocks.webdb.onSuccess = function(tx, r) {
  // re-render the data.
  // loadTodoItems is defined in Step 4a
  html5rocks.webdb.getAllTodoItems(loadTodoItems);
}</pre>

  <h2 id="toc-step2">Step 2. Creating a table</h2>
  <p>
    You can only create a table by executing a CREATE TABLE SQL statement
     inside a <a href="#toc-transactions">transaction</a>.
  </p>
  <p>
    We have defined a function that will create a table in the body onload
     event. If the table doesn't already exist, a table will be created.  
     The table is called todo and has 3 columns.
    <ul>
      <li>ID - a incrementing sequential ID column</li>
      <li>todo - a text column that is the body of the item</li>
      <li>added_on - the time that the todo item was created</li>
    </ul>
  </p>
  <pre class="prettyprint">html5rocks.webdb.createTable = function() {
  var db = html5rocks.webdb.db;
  db.transaction(function(tx) {
    tx.executeSql("CREATE TABLE IF NOT EXISTS " +
                  "todo(ID INTEGER PRIMARY KEY ASC, todo TEXT, added_on DATETIME)", []);
  });
}</pre>

  <h2 id="toc-step3">Step 3. Adding data to a table</h2>
  <p>
    We are building a todo list manager so it is pretty important that
    we are able to add todo items in to the database.
  </p>
  <p>
    A transaction is created, inside the transaction an INSERT into the todo
    table is performed.
  </p>
  <p>
    executeSql takes several parameters, the SQL to execute and the parameters
    values to bind the query.
  </p>
  <pre class="prettyprint">html5rocks.webdb.addTodo = function(todoText) {
  var db = html5rocks.webdb.db;
  db.transaction(function(tx){
    var addedOn = new Date();
    tx.executeSql("INSERT INTO todo(todo, added_on) VALUES (?,?)",
        [todoText, addedOn],
        html5rocks.webdb.onSuccess,
        html5rocks.webdb.onError);
   });
}</pre>

  <h2 id="toc-step4">Step 4. Selecting data from a table</h2>
  <p>
    Now that the data is in the database, you need a function that gets 
    the data back out.  In Chrome, Webdatabase's use standard SQLite SELECT
     queries.
  </p>
  <pre class="prettyprint">html5rocks.webdb.getAllTodoItems = function(renderFunc) {
  var db = html5rocks.webdb.db;
  db.transaction(function(tx) {
    tx.executeSql("SELECT * FROM todo", [], renderFunc,
        html5rocks.webdb.onError);
  });
}</pre>
  <p>
    Note that all of these commands used in this sample
    are asynchronous and as such the data is not returned from the transaction
    or the executeSql call.  The results are passed through to the success
    callback. 
  </p>
  <h2>Step 4a. Rendering data from a table</h2>
  <p>
    Once the data has been fetched from the table, the loadTodoItems method
  will be called.
  </p>
  <p>
    The onSuccess callback takes two parameters.  The first being the 
    transaction of the query and the second being the result set.  It is 
    fairly simple to iterate across the data:
  </p>
  <pre class="prettyprint">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 "&lt;li&gt;" + row.todo + 
         " [&lt;a href='javascript:void(0);' onclick='html5rocks.webdb.deleteTodo(" + 
         row.ID +");'&gt;Delete&lt;/a&gt;]&lt;/li&gt;";
}</pre>
  <p>
    The effect of this method call is that the todo list is rendered into
    a DOM Element called "todoItems".
  </p>
  <h2 id="toc-step5">Step 5. Deleting data from a table</h2>
  <pre class="prettyprint">html5rocks.webdb.deleteTodo = function(id) {
  var db = html5rocks.webdb.db;
  db.transaction(function(tx){
    tx.executeSql("DELETE FROM todo WHERE ID=?", [id],
        html5rocks.webdb.onSuccess,
        html5rocks.webdb.onError);
    });
}</pre>
  <h2 id="toc-step6">Step 6. Hooking it all up</h2>
  <p>
    When the page loads, open the database and create the table (if
     needed) and render any todo items that might already be in the database.
  </p>
  <pre class="prettyprint">....
function init() {
  html5rocks.webdb.open();
  html5rocks.webdb.createTable();
  html5rocks.webdb.getAllTodoItems(loadTodoItems);
}
&lt;/script&gt;

&lt;body onload="init();"&gt;</pre>
  <p>
    A function that takes the data out of the DOM is needed so,
    call the html5rocks.webdb.addTodo method
  </p>
  <pre class="prettyprint">function addTodo() {
  var todo = document.getElementById("todo");
  html5rocks.webdb.addTodo(todo.value);
  todo.value = "";
}</pre>
  <h2 id="toc-final">The final product</h2>
{% if is_mobile %}
  <ul id="todoItems">
  
  </ul>
  <form type="post" onsubmit="addTodo(); return false;">
    <input type="text" id="todo" name="todo" 
      placeholder="What do you need to do?" style="width: 200px;" />
    <input type="submit" value="Add Todo Item"/>
  </form>
{% else %}
<iframe src="http://playground.html5rocks.com/?mode=frame&hu=210&hl=150#a_simple_todo_list_using_web_sql_database" style="border: none; width: 100%; height: 480px;"></iframe>
{% endif %}
{% endblock %}