[WebSQL] Any future plans, or has IndexedDB replaced WebSQL?

Hi.

I've been watching discussions on IndexedDB for a while now, and wondered if
anyone would mind spending a few moments to explain how IndexedDB is related
(or not) to WebSQL. Is IndexedDB seen as replacing the functionality
originally offered by WebSQL? If not, are there any plans to make a
cross-platform variant of Web SQL?

If (?) most web developers know SQL, is there a case to be made for
abstracting SQL into JSON/JavaScript rather than moving to IndexedDB
document storage? Reasons for asking this:

   - Many of the posts appearing to come from "the dev community" rather
   than W3C seem to expect more SQL-esque functionality from IndexedDB. If the
   enthusiasts who get involved enough to post to the board are expecting
   SQL/query type experience, maybe there is a driver for a native database API
   supporting this.
   - Several people have noted that third-party frameworks could implement
   this functionality. This might be a daft question, but isn't it easier to
   implement an "IndexedDB"-like framework on top of "WebSQL", than a
   "WebSQL"-like framework on top of "IndexedDB" (overuse of quotes to indicate
   the general concept).

I had a ponder on how I'd like to see such a framework implemented (in both
Access & SQLite :p ), and came up with a stack of pseudo-code below in my
lunch break. Might make an interesting discussion point. It's not really
IndexedDB, it's WebSQL v2. Or maybe WebJSQL or something. I'd be really
interested to understand what advantages IndexedDB has over an
implementation like the one below though.

// DATABASE
// First, open a database with the specified name. The number at the
// end denotes the version of the specification that the application
// plans to use. This allows forward-compatibility with vNext.
var db = window.openDatabase("shoppinglist", 1.0);

// MIGRATIONS
// Next, create some migrations. These are predefined structures which
// are validated by the browser database engine. A migration consists
// of two actions: one "up", one "down". Each action specifies some
// operations and parameters. It's up to the browser database to read
// these and perform the appropriate action, as defined in the spec.

// Other actions may include a "batch add" for "static" data. It could
// also be valid to have key and index creation and removal as separate
// actions.

// SHOPPING TRIP
var createTripTableAction = {
    action: "create-table",
    params: {
        name: "trip",
        columns: [
          { name: "id", type: "whole-number", primaryKey: true },
          { name: "name", type: "string", length: 32, regex: "[A-Z]{1,32}" }
// regex: wouldn't that be nice...
        ],
        indexes: [
          {
            columns: [
              { name: "name",   type: "full-text" }
            ]
          } // More indexes here if required
        ]
      }
  };

var removeTripTableAction = {
    action: "remove-table",
    params: {
        name: "shopping",
        cleardata: true
      }
  };


// SHOPPING
var createShoppingTableAction = {
    action: "create-table",
    params: {
        name: "shopping",
        columns: [
          { name: "id", type: "whole-number", primaryKey: true },
          { name: "tripid", type: "whole-number" },
          { name: "name", type: "string", length: 128, nillable: false },
          { name: "cost", type: "decimal-number" }
        ],
        keys: [
          { local: "tripid", foreign: { table: "trips", column: "id" },
cascade-delete: true } }
        ],
        indexes: [
          {
            columns: [
              { name: "tripid", type: "asc" },
              { name: "name",   type: "full-text" }
            ]
          }
        ]
      }
  };

var removeShoppingTableAction = {
    action: "remove-table",
    params: {
        name: "shopping",
        cleardata: true
      }
  };

var migTrip = { up: createTripTableAction, down: removeTripTableAction };
var migShopping = { up: createShoppingTableAction, down:
removeShoppingTableAction };

// Example calls which might be appropriate here, where the word
// "callback" is placeholder for async operations:

db.getVersion();                        // Returns 0
db.migrations.add(migTrip);             // Persists the up/down actions as a
list somewhere
db.migrations.add(migShopping);         // Persists the up/down actions as a
list somewhere
db.migrateUp(2, callback);              // Runs the two migrations
db.getVersion();                        // Returns 2
db.migrateDown(0, callback);            // Runs all "down" migrations to
schema version 0.
db.getVersion();                        // Returns 0
db.destroy(true);                       // Get rid of the database,
optionally running "down" migrations

// * Note that during migration operations the database should be locked: no
other threads should
// * be able to interact/access the database while this is taking place.

// QUERIES
// Invent a JSON structure for creating queries. There could be simple
// versions to support simple queries similar to the one below. I personally
// think that the key here is readability:

var query = { from: "shopping", select: ["name", "cost"], where: [ { column:
"name", operator: "equals", value: "cookies"} ] };

// And provide the capability to evaluate more complex query structures if
needed:

var queryJoin = {
    select: [ { table: "shopping", column: "name" }, { table: "shopping",
column: "cost" } ],
    join: [
      { from: { table: "trips", column: "id" }, to: { table: "trips",
column: "id" } }
    ],
    where: [
      { operator: "and",
        clause: [
          { table: "trips", column: "name", operator: "equals", value:
"Costco" },
          { table: "shopping", column: "name", operator: "like", value:
"*cookies" }  // Can only use "like" on full-text indexed columns
        ]
      }
    ]
  };

// Querying returns results to callback methods
db.query(query, callback);


// CREATE/UPDATE
// Treat everything as "put" operations. Optionally specifying the ID
// (Primary Key) of the object being added to the database should update
// an existing item.

var toy = { tripid: 1, name: "Flag", cost: 12.99 };
var toys = [
    { tripid: 1, name: "Water pistol", cost: 27.50 }
    { tripid: 1, name: "Football", cost: 4.99 }
    { tripid: 1, name: "Rocket", cost: 8.99 }
  ];

db.put("shopping", toy, callback);
db.put("shopping", toys, callback);

var replacementToy = { id: 3, tripid: 1, name: "England Flag", cost: 12.99
};

db.put("shopping", replacementToy);

// There also needs to be functionality to batch update:

var epicSale = { cost: 0.99 };
var putQuery = {
    where: [
      { column: "tripid", operator: "equals", value: 1 }
    ]
  };

// This effectively does: "UPDATE shopping SET cost=0.99 WHERE tripid=1;"
db.put("shopping", putQuery, epicSale);

// DELETE
// Finally, provide the same kind of mechanisms for deleting data.
db.remove("shopping", 1);

var queryRemove = {
    remove: { table: "shopping", column: "name" },
    join: [
      { from: { table: "trips", column: "id" }, to: { table: "trips",
column: "id" } }
    ],
    where: [
      { operator: "and",
        clause: [
          { table: "trips", column: "name", operator: "equals", value:
"Costco" },
          { table: "shopping", column: "name", operator: "like", value:
"*cookies" }
        ]
      }
    ]
  };

db.remove("shopping", queryRemove);