HTML5 Database Transactions

Question

i am wondering abt the example W3C Offline Web Apps the example

function renderNotes() {
  db.transaction(function(tx) {
    tx.executeSql('CREATE TABLE IF NOT EXISTS Notes(title TEXT, body TEXT)', 
      []);
    tx.executeSql(‘SELECT * FROM Notes’, [], function(tx, rs) {
      for(var i = 0; i < rs.rows.length; i++) {
        renderNote(rs.rows[i]);
      }
    });
  });
}

has the create table before the 'main' executeSql(). will it be better if i do something like

$(function() {
    // create table 1st
    db.transaction(function(tx) {
        tx.executeSql('CREATE TABLE IF NOT EXISTS Notes(title TEXT, body TEXT)', 
          []);
    });

    // when i execute say to select/modify data, i just do the actual action
    db.transaction(function(tx) {
        tx.executeSql(‘SELECT * FROM Notes’, [], function(tx, rs) {
            ...
        }
    });

    db.transaction(function(tx) {
        tx.executeSql(‘INSERT ...’, [], function(tx, rs) {
            ...
        }
    });
})

i was thinking i don't need to keep repeating the CREATE IF NOT EXISTS right?

Update

function initDatabase() {
    notes = openDatabase("todolist", "1.0", "To-Do List", 1*1024*1024, function (notes) {
        notes.changeVersion("", "1.0", function(tx) {
            tx.executeSql("CREATE TABLE todolist (id INTEGER, task TEXT)", [], function(tx, rs) {
                alert("Table created");
            });
        }); 
    })
}

Answer 1

You address this by using changeVersion. The API supports database versioning so you can apply schema changes during upgrades, or in your case.. installation.

There are some examples in the documentation:

function prepareDatabase(ready, error) {
  return openDatabase('documents', '1.0', 'Offline document storage', 5*1024*1024, function (db) {
    db.changeVersion('', '1.0', function (t) {
      t.executeSql('CREATE TABLE docids (id, name)');
    }, error);
  });
}

In that example, they call prepareDatabase, which opens a connection to the database, but if the version is < 1.0 or non-existent, it calls that closure which executes the CREATE TABLE statement.

When the page is revisited, assuming the user didn't clear out the database.. it will be at version 1.0 already, so changeVersion will simply do nothing instead of running the CREATE TABLE again.