Incremental Data Fetching in Data Abstract for OS X

Bugs 7, the new bug-tracking application i have been working on over the past month for internal use here at RemObjects, employs several different data access paradigms (all based on Data Abstract for OS X), to accommodate the different nature of the data in individual tables.

The most interesting one is the main Issues table that contains all the bugs and tasks that are logged in the system. That is due to the fact that (a) it is a huge table (with over 25,000 records as of now, sized at about 11 MB when transferred over the wire, compressed) and that (b) by the very nature of a bug tracking system, this table changes frequently, and needs to be updated on the clients, often.

Rather then downloading the entire table anew every time, we optioned for a solution that allowed us to incrementally fetch only those records that have changed, and integrate them with the local dataset. This way, only minimal traffic is occurred for the regular refresh (which our client, by default, does every two minutes).

In the following post, i want to give you a quick glimpse at how this was accomplished, and how you can leverage the same technology in your own Data Abstract applications.

The Server

A couple things happen on the middle tier server (written using Data Abstract for .NET), to support the incremental refresh. Like all tables in the Bugs database, our Issues table has an UpdatedDate field, which gets automatically adjusted by the business logic code on the server. Every time a new issue is created, or an existing issue is updated, the server puts the current UTC time into the UpdatedDate field, clearly marking the order in which issues have been touched.

This is handled by a simple BeforeProcessChange event handler on the server, which simply adjusts the received delta, as such:

method BugsDataService.bp_Issues_BeforeProcessChange(aSender: BusinessProcessor; ea: DeltaChangeEventArgs);begin ea.DeltaChange.NewValues['UpdatedDate']:= DateTime.Now; ea.DeltaChange.NewValues['UpdatedByID']:= Session['UserID'];end;

(Of course the actual code in our server performs a lot more checks and changes, to enforce business logic for our database – but that’s beyond the scope of this post.)

Also, our Issues table does not permit deleting of records (only closing of issues, which sets their status accordingly, but does not remove the rows from the database). This alleviates the problem of worrying about rows disappearing from the table altogether.

The Client

On the client, a bit more custom logic is necessary, to perform the incremental updating.

When the client application (“Bugs 7”) is first started, it checks whether a briefcase file with a local copy of the data is found from a previous run, or not.

If not, the client will start a request to download the complete set of data from the server. This is a one-time process, and will download the entire table with it’s (currently) 11MB across the wire. Once downloaded, it is stored in a briefcase file, so on next application start, the data can be loaded locally. After the download is finished, the application also takes note of the latest UpdatedDate value it can find in the table, for future reference. This is made easy by Cocoa’s KVC and Key Paths:

-(void)downloadIssueData {// Initial download can take long, server side, to gather data. // allow for a longer timeout.[(ROHTTPClientChannel *)[[rda dataService] channel] setTimeout:360]; // fetch full table from server issues =[[rda getDataTable:@"Issues"] retain]; maxDate =[[[issues rows] valueForKeyPath:@"@max.UpdatedDate"] retain]; // save table to briefcase[self saveIssues]; }

If instead a briefcase was found, the *Issues* table simply gets loaded from that file, alongside the stored * maxDate*:

-(void)loadIssueDataFromBriefcase { DABriefcase *briefcase =[DABriefcase briefcaseWithFolder:briefcasePath]; issues =[[briefcase tableNamed:@"Issues"] retain]; maxDate =[[briefcase.properties valueForKey:@"BugsMaxUpdateDate"] retain]; }

Whichever path was taken, the application now holds a local copy of the *Issues* table it can work with. The next step it to schedule the regular refreshes, and for that an *NSTimer* is configured, to fire at regular intervals, on a background thread.

This NSTimer will trigger our beginRefreshBugs method, which uses asynchronous requests to start checking for new issues. It uses the previously stored maxDate and a feature of Data Abstract called DA SQL, to fetch only those issues that have newly changed:

-(void)beginRefreshBugs {// prevent two refreshs happening at the same time, if the NSTimer// triggers again before the previous refresh has finished.if(refreshingBugs)return; refreshingBugs =YES; // build the DA SQL queryNSString*sql =[NSString stringWithFormat:"SELECT * FROM Issues WHERE UpdatedDate >= '%d'", (int)[maxDate timeIntervalSince1970]]; //and start an Async Request to the server DAAsyncRequest *ar =[rda beginGetDataTable:@"Issues" withSQL:sql start:NO]; [ar setDelegate:self]; [ar setContext:@"RefreshIssues"]; [ar start]; }

The DAAsyncRequest, once *start*ed, will communicate with the server in a background thread, without blocking the caller. *beginRefreshBugs* will return right away, and not wait for the request to complete (or fail).

Once the request did complete, it will call back to a delegate method (in this case we assigned self as the delegate, above), called asyncRequest:didReceiveTable:. It is here that we handle integrating the received data back with our big issues table by sending it the mergeTable:withPrimaryKey: message. This will replace the data in any rows that have changed, as well as add any new rows to the table:

-(void)asyncRequest:(DAAsyncRequest *)request didReceiveTable:(DADataTable *)table {// our class may handle access to more tables. check the context// to make sure we handle the right requests.if([[request context] compare:@"RefreshBugs"]== NSOrderedSame){// nothing received? nothing to do!if([table rowCount] > 1){@try{[issues mergeTable:table withPrimaryKey:@"ID"]; maxdate =[[[table rows] valueForKeyPath:@"@max.UpdatedDate"] retain]; [self saveBugs]; }@finally{ refreshingBugs =NO; }}}}

Of course a refresh might also fail (for example due to a broken internet connection, for this case, we implement a second delegate method, called *asyncRequest: didFailWithException:*, as follows:

-(void)asyncRequest:(ROAsyncRequest *)request didFailWithException:(NSException*)exception {if([[request context] compare:@"RefreshBugs"]== NSOrderedSame){ refreshingBugs =NO; }

The View

The last step after receiving new issues is to update any affected views. This happens more or less automatically, as every view that shows one one more issues (whether it’s the regular grid view of issues, a chart visualizing issue data, or an individual issue’s detail view) will be have registered itself to observe DA_NOTIFICATION_TABLE_CHANGED notifications on issues. And like any other change to a data table, mergeTable:withPrimaryKey: will send such a notification if changes happened, allowing all views to update themselves.

In Bugs, all of this happens in the background, so over time the view(s) presented to the user just seamlessly adjust themselves, as changes happen – new issues come into views; issues resolved by other users disappear on their own, etc.

![](http://blogs.remobjects.com/wp-content/uploads/2010/01/Bugs7.png "Bugs7")

This topic just touches on a very small aspect on Bugs 7, which itself is part of a mch larger project, comprised of four different client applications (Mac and iPhone, based on DA/OS X, for Windows, based on DA/.NET and Gtk# and the Web) as well as a middle tier server. We will blog more about different aspects of this project over the next few months, ands we’re also working on a bigger case study, to appear at [bugsapp.com](http://www.bugsapp.com), soon. Stay tuned to this space, for more.