Re: Relational Data Model Example

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