W3C home > Mailing lists > Public > public-webapps@w3.org > October to December 2010

Re: Relational Data Model Example

From: Jonas Sicking <jonas@sicking.cc>
Date: Thu, 11 Nov 2010 09:35:39 -0800
Message-ID: <AANLkTikp+OsJrVVooCeqa=b7i1LNzXjGcHJoVgRv15qk@mail.gmail.com>
To: Keean Schupke <keean@fry-it.com>
Cc: WebApps WG <public-webapps@w3.org>
Hi Keean,

This is awesome stuff! Very excited to see libraries that can run both
on top of IndexedDB and on top of WebSQL.

Would love to hear more about your experience working against the IndexedDB API.

/ Jonas

On Thu, Nov 11, 2010 at 5:42 AM, Keean Schupke <keean@fry-it.com> wrote:
> Hi,
> Here are the Mozilla IndexedDB examples converted to us the relational data
> model. Points to note:
> - The database is validated (that is the schema in the JavaScript is either
> used to create the database if it does not exit, or to make sure that the
> database conforms to the schema if it does exist. Currently we require an
> exact match for validation to succeed, however the final version will use
> nullable and default values to allow attributes to be added to existing
> relations, or attributes ignored providing the required pre-conditions are
> met).
> - The 'true' at the end of the validate function tells it to drop the
> existing relations, so we always start with an empty database.
> - We add more data than the original insert example so there are some
> results from the join query.
> - There is no single-value-per-group test yet for project. But effectively
> when grouping by a unique attribute (like id) any attribute in the same
> (pre-join) relation is acceptable, as well as the attribute joined to in the
> other relation, but no other attribute if the joined to column is not unique
> (the case in the example).
>
>         var rdm = new RelationalDataModel;
>         var rdb = new rdm.WebSQLiteDataAdapter;
>         var kids = rdm.relation('kids', {
>             id: rdm.attribute('id', rdm.integer, {auto_increment: true}),
>             name: rdm.attribute('name', rdm.string)
>         });
>         var candy =  rdm.relation('candy', {
>             id: rdm.attribute('id', rdm.integer, {auto_increment: true}),
>             name: rdm.attribute('name', rdm.string)
>         });
>         var candySales = rdm.relation('candySales', {
>             kid: rdm.attribute('kid', rdm.integer),
>             candy: rdm.attribute('candy', rdm.integer),
>             date: rdm.attribute('date', rdm.string)
>         });
>         var v = rdb.validate('CandyDB', 1.0, [kids, candy, candySales],
> true).onsuccess = function(db) {
>             // new database has been created, or existing database has been
> _validated_
>
>             var i = db.transaction(function(tx) {
>                 [
>                     {id: 1, name: 'Anna'},
>                     {id: 2, name: 'Betty'},
>                     {id: 3, name: 'Christine'}
>                 ].forEach(function(k) {
>                     tx.insert(kids, k).onsuccess = function(t, id) {
>                         document.getElementById('display').textContent +=
>                             '\tSaved record for ' + k.name + ' with id ' +
> id + '\n';
>                     };
>                 });
>                 [
>                     {id: 1, name: 'toffee-apple'},
>                     {id: 2, name: 'bonbon'}
>                 ].forEach(function(c) {
>                     tx.insert(candy, c).onsuccess = function(t, id) {
>                         document.getElementById('display').textContent +=
>                             '\tSaved record for ' + c.name + ' with id ' +
> id + '\n';
>                     };
>                 });
>                 [
>                     {kid: 1, candy: 1, date: '1/1/2010'},
>                     {kid: 1, candy: 2, date: '2/1/2010'},
>                     {kid: 2, candy: 2, date: '2/1/2010'},
>                     {kid: 3, candy: 1, date: '1/1/2010'},
>                     {kid: 3, candy: 1, date: '2/1/2010'},
>                     {kid: 3, candy: 1, date: '3/1/2010'}
>                 ].forEach(function(s) {
>                     tx.insert(candySales, s).onsuccess = function(t, id) {
>                         document.getElementById('display').textContent +=
>                             '\tSaved record for ' + s.kid + '/' + s.candy +
> ' with id ' + id + '\n';
>                     };
>                 });
>             });
>             i.onsuccess = function() {
>                 var q1 = db.transaction(function(tx) {
>                     tx.query(kids.project(kids.attributes.name)).onsuccess =
> function(t, names) {
>                         names.forEach(function(name) {
>                             document.getElementById('kidList').textContent
> += '\t' + name + '\n';
>                         });
>                     };
>                 });
>                 q1.onsuccess = function() {
>                     var q2 = db.transaction(function(tx) {
>                         tx.query(
>                             kids.join(candySales,
> kids.attributes.id.eq(candySales.attributes.kid))
>                             .group(candySales.attributes.kid)
>                             .project({name:kids.attributes.name,
> count:kids.attributes.name.count()})
>                         ).onsuccess = function(t, results) {
>                             var display =
> document.getElementById('purchaseList');
>                             results.forEach(function(item) {
>                                 display.textContent += '\t' + item.name + '
> bought ' + item.count + ' pieces\n';
>                             });
>                         };
>                     });
>                 };
>             };
>         }
>
> Cheers,
> Keean.
>
> On 9 November 2010 17:13, Keean Schupke <keean@fry-it.com> wrote:
>>
>> Hi,
>> I have completed the first stage of the Relational Data Model prototype.
>> Error checking is not complete (for example aggregate functions can be
>> nested currently, and this should not be allowed). So it should work for
>> correct examples, but may not generate an error (or the correct error) for
>> incorrect examples.
>> The library (available at http://keean.fry-it.com/relational.js) only
>> implements the WebSQL backend at the moment, as this was the quickest to get
>> up and running. I plan to implement a JavaScript Object backend (IE
>> relational operations in memory) and the IndexedDB backend.
>> There is a simple first example (available at
>> http://keean.fry-it.com/cuboid.html) that shows calculating the average
>> volume of a collection of cuboids the relational way.
>> Attached at the end is the JavaScript source for the cuboid example.
>> Comments appreciated.
>>
>> Cheers,
>> Keean.
>>
>>     try {
>>         var rdm = new RelationalDataModel;
>>         var rdb = new rdm.WebSQLiteDataAdapter;
>>         var cuboid_id = rdm.domain('id', rdm.integer, {not_null: true});
>>         var dimension = rdm.domain('dimension', rdm.number, {not_null:
>> true});
>>         var cuboids = rdm.relation('cuboids', {
>>             id: rdm.attribute('id', cuboid_id, {auto_increment: true}),
>>             length: rdm.attribute('length', dimension),
>>             width: rdm.attribute('width', dimension),
>>             height: rdm.attribute('height', dimension)
>>         });
>>         var v = rdb.validate('cubeoid_db', 1.0, [cuboids]);
>>         v.onerror = function(error) {
>>             alert('ValidateError: ' + error.message);
>>         };
>>         v.onsuccess = function(db) {
>>             var insert = db.transaction(function(tx) {
>>                 tx.insert(cuboids, {width:10.0, length:10.0,
>> height:10.0});
>>                 tx.insert(cuboids, {width:13.5, length:17.2,
>> height:10.1});
>>                 tx.insert(cuboids, {width:23.1, length:7.9, height:9.5});
>>             });
>>             insert.onerror = function(error) {
>>                 alert('InsertTransactionError: ' + error.message);
>>             };
>>             insert.onsuccess = function() {
>>                 var query = db.transaction(function(tx) {
>>                     var average_volume = cuboids.attributes.length
>>                         .mul(cuboids.attributes.width)
>>                         .mul(cuboids.attributes.height)
>>                         .avg();
>>                     var q = tx.query(cuboids.project({avg_vol:
>> average_volume}));
>>                     q.onsuccess = function(t, results) {
>>                         var s = "";
>>                         results.forEach(function(r) {
>>                             s += r.avg_vol + '\n';
>>                         });
>>                         alert(s);
>>                     };
>>                 });
>>                 query.onerror = function(error) {
>>                     alert('QueryTransactionError: ' + error.message);
>>                 };
>>             };
>>         };
>>     } catch (e) {
>>         alert (e.stack);
>>     }
>
>
Received on Thursday, 11 November 2010 17:36:36 GMT

This archive was generated by hypermail 2.3.1 : Tuesday, 26 March 2013 18:49:41 GMT