Synchronized Online/Offline data applications, part 2: Syncing large tables and tables with foreign relations

26 June 2013

[This document is in the process of creation]

 

This post is a part of a series, find more here:

 

I have recently created a sample app that synchronizes tables from a Northwind database (published with WCF Data Services) to a local webSql or IndexedDB cache. Creating a nearly identical local copy of the remote data potentially with foreign keys and large number of rows faces some challenges.

 

First of all, we need a scalable way that is able to copy any number of rows over the net – in chunks. Getting all the items at once would definitely do no good to us, this approach is really everything but feasible beyond of couple of hundred lines . Secondly we have to deal with dependencies – in our case we are to sync three tables: Products, Orders and OrderDetails. The OrderDetail table has a composed primary key that consist of two foreign keys referencing Orders and Products.

 

Example Project

You can download the VS2012 solution from here. Index.html and Scripts/localdb.js contains all the relevant client code.

 

Architecture

The online source of data will be an OData endpoint exposing a partial of the Northwind database. On the client we will use JayData and its OData and local providers to connect to the two data sources and copy data from one to the other. Since we are to store only a subset of the fields locally (the ones for example that take part in creating a fast auto complete solution for a product name) we will need to create local type definitions for the Product, Order and OrderDetails entities. The same thing will be automatic for the OData endpoint. 

 

Efficient copy of entities

For an efficient paging based copy procedure we will use a continuation algorithm that repeats until the last page is copied – only taking a number of items at a time. 

 

    var localDB = new NWLite({ name: 'local', databaseName: 'db' });

    $data.service("/Northwind.svc", function (f, t) {
        remoteDB = f();
        $.when(remoteDB.onReady(), localDB.onReady())
            .then(function() {
                syncSet(remoteDB.Products, localDB.Product); 
            })
    }

The trick is in the syncSet method.

 

function syncSet(fromSet, toSet) {
    var pageSize = 200;
    return 

fromSet .take(pageSize) .toArray()

            .then(function (items) {
                function recurseCopy(items) {
                    toSet.addMany(items);
                    return toSet.saveChanges().then(function (countSaved) {
                        return items.next().then(function (items) {
                            if (items.length > 0) {
                                return recurseCopy(items);
                            } 
                        });
                    });
                }
                return recurseCopy(items).then(function () { console.log("finished!"); });
            });
}

Share this

categories: Online-offline sync

Leave a comment

comments powered by Disqus