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

Have a look at my RelationalDB API

https://github.com/keean/RelationalDB

In particular examples/candy.html

A lot of work went into the underlying concepts - Its work originally
published by myself and others at the 2004 Haskell Workshop, and follows on
from HaskellDB which was the original inspiration behind C#s Linq
functionality).

It implements the relational-algebra operators as methods that operate on
relation objects.

Let me know what you think.


Cheers,
Keean.


On 31 March 2011 15:19, Nathan Kitchen <w3c@nathankitchen.com> wrote:

> 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);
>