HTML5 database storage (SQL lite) - few questions

Question

Hy there,

I can't find enough beginner resources on the web about HTML5 database storage usage examples (CRUD)

I'm opening(creating) my DB like this:

var db;

$(document).ready(function() 
{

    try
    {
      if (!window.openDatabase) {
            alert('Not Supported -> Please try with a WebKit Browser');
      } else {
          var shortName = 'PFHjames';
          var version = '1.0';
          var displayName = 'User Settings Database';
          var maxSize = 3072*1024; //  = 3MB            in bytes 65536
          db = openDatabase(shortName, version, displayName, maxSize);      
          }
    } 
    catch(e) 
    {
      if (e == 2) {

          alert("Invalid database version.");
      } else {
          alert("Unknown error "+e+".");
      }return;
    }
});

QUESTION 1: How many databases can i create and use on one domain? QUESTION 2. How to delete (drop) a database. -> i have not figured this out yet.

To create sql queries you use transaction:

function nullDataHandler(transaction, results) { }
function createTables(db)
{
  db.transaction(function (transaction)
  {
    //first query causes the transaction to (intentionally) fail if the table exists.
    transaction.executeSql('CREATE TABLE people(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL DEFAULT "John Doe", shirt TEXT NOT NULL DEFAULT "Purple");', [], nullDataHandler, errorHandler);
  });
}

QUESTION 3: How so is the above transaciton failed if a table exists? Is the nullDataHandler involved to do this? Where on the web is there documentation explaining the executeSql API? Arguments?

thx

Answer 1

The spec you're looking for is Web SQL Database. A quick reading suggests:

1. There is no limit, although once your databases increase beyond a certain size (5MB seems to be the default), the browser will prompt the user to allow for more space.
2. There is no way, in the current spec, to delete databases.
3. The executeSql() function takes an optional error callback argument.

HTML5 Doctor also has a good introduction.

Going forward, though, I'd recommend looking at Indexed DB. Web SQL has essentially been abandoned since there is no standard for SQL / SQLite. Even Microsoft has endorsed Indexed DB. See Consensus emerges for key Web app standard.

Answer 2

Hey I have written out this basic user guide which has a few worked out examples.... Hope they can be of some help.... http://mnesia.wikispaces.com/HTML5+and+webstorage As of now I think no one is really paying a lot of attention to WEB SQL based storage.... And that is understandably so... The key-pair based local-storage system works very well for most web apps.... Reminds me of how redis and memcache have taken off as compared to RDBMS... Especially for apps that dont really need that kind of structure in there data...

Answer 3

I'm using the SQL local database function paired with a google app on the server. I love Safari's exposure of the local databases, but since not every browser seems to have that capability, I'm dropping my tables programmatically.