Re: [IndexedDB] Proposal for async API changes

On 5/17/2010 6:15 PM, Jonas Sicking wrote:
> We've created some examples of what using this proposed API would look like:
>
> http://docs.google.com/document/pub?id=1I__XnwvvSwyjvxi-FAAE0ecnUDhk5DF7L2GI6O31o18
>
> we've also implemented the same examples using the currently drafted API:
>
> http://docs.google.com/document/pub?id=1KKMAg_oHLeBvFUWND5km6FJtKi4jWxwKR0paKfZc8vU
And I've gone ahead and converted these samples to WebDatabase language 
too for comparison purposes (I think, at least.  I haven't used it 
before, and these aren't actual fully runnable demos.  If you spot an 
error, let me know!)

It should be noted that WebDatabase fares much better when it comes to 
joins, but I suspect we all knew that.

////////////////////////////////
// Initialize database

var db = window.openDatabase("CandyDB", "", "My candy store database",
                              1024);
if (db.version != "1") {
   db.changeVersion(db.version, "1", function(tx) {
     // User's first visit.  Initialize database.
     var tables = [
       { name: "kids", columns: ["id INTEGER PRIMARY KEY",
                                 "name TEXT"]},
       { name: "candy", columns: ["id INTEGER PRIMARY KEY",
                                  "name TEXT"]},
       { name: "candySales", columns: ["kidId INTEGER",
                                       "candyId INTEGER",
                                       "date TEXT"]}
     ];

     for (var index = 0; index < tables.length; index++) {
       var table = tables[index];
       tx.executeSql("CREATE TABLE " + table.name + "(" +
                     table.columns.join(", ") + ");",
                     null, tableCreated);
     }
   }, null, function() { loadData(db); });
}
else {
   // User has been here before, no initialization required.
   loadData(db);
}

function loadData(db) {
   // Do stuff!
}

////////////////////////////////
// List kids

var db = window.openDatabase("CandyDB", "1", "My candy store database",
                              1024);
db.readTransaction(function(tx) {
   // Enumerate the entire table.
   tx.executeSql("SELECT * FROM kids", function(tx, results) {
     var rows = results.rows;
     for (var index = 0; index < rows.length; index++) {
       var item = rows.item(index);
       var element = document.createElement("div");
       element.textContent = item.name;
       document.getElementById("kidList").appendChild(element);
     }
   });
});

////////////////////////////////
// Store kids into database

var kids = [
   { name: "Anna" },
   { name: "Betty" },
   { name: "Christine" },
];

var db = window.openDatabase("CandyDB", "1", "My candy store database",
                              1024);
db.transaction(function(tx) {
   for (var index = 0; index < kids.length; index++) {
     var kid = kids[index];
     tx.executeSql("INSERT INTO kids (name) VALUES (:name);", [kid],
                   function(tx, results) {
       document.getElementById("display").textContent =
           "Saved record for " + kid.name + " with id " + results.insertId;
     });
   }
});


////////////////////////////////
// List kids who bought candy, and the number of purchases they made

var db = window.openDatabase("CandyDB", "1", "My candy store database",
                              1024);
db.readTransaction(function(tx) {
   tx.executeSql("SELECT name, count " +
                 "FROM kids " +
                 "INNER JOIN candySales " +
                 "ON kids.id = candySales.kidId;",
                 function(tx, results) {
     var rows = results.rows;
     for (var index = 0; index < rows.length; index++) {
       var item = rows.item(index);
       display.textContent += ", " + item.name + "bought " +
                              item.count + "pieces";
     }
   });
});

////////////////////////////////
// List kids who bought candy, and the number of purchases they made
// (some may have bought 0)

var db = window.openDatabase("CandyDB", "1", "My candy store database",
                              1024);
db.readTransaction(function(tx) {
   tx.executeSql("SELECT name, count " +
                 "FROM kids " +
                 "LEFT JOIN candySales " +
                 "ON kids.id = candySales.kidId;",
                 function(tx, results) {
     var rows = results.rows;
     for (var index = 0; index < rows.length; index++) {
       var item = rows.item(index);
       display.textContent += ", " + item.name + "bought " +
                              item.count + "pieces";
     }
   });
});


Cheers,

Shawn