Select Page

Wouldn’t it be nice to be able to keep your visual data up to date with a single line of code, like this?

I’m not reading all that! Just give me the code.

Preface

I’ll be entirely honest – this article was originally titled ‘Realtime Synchronization of a UITableView with SQLite’. In attempting to achieve that, however, I discovered some critical shortcomings in the behavior of UITableView. In digging deeper I found these were inherent to it’s design – with no solutions offered by Apple. It certainly appears that Apple is pushing developers towards the use of the UICollectionView instead – and given the minimal learning curve afforded by their similar API – I can’t say I’m surprised.

You can read more about my troubles on Stack Overflow.

How to animate UITableView rows when IndexPathsForVisibleRows[0] is moved or removed
How to prevent UITableView from scrolling when moving a row

The UITableView fails to correctly animate (see left) when any row, from the top-most visible upwards, experiences a modification (insert/delete/reload).

This results is a jumpy visual experience.

Any other row behaves as expected (see right).

Introduction

It’s easy enough to implement a UICollectionViewDataSource to populate data from a SQLite database. SQLite is even quick enough to pull records on-demand (though you really should batch them to maximise efficiency). But how does one keep the view up to date when there are changes in that underlying data?
Consumers of CoreData are spoiled, with access to NSFetchedResultsController – which handles the synchronization of view and data. If you’re using Xamarin and SQLite-Net, however, you’re likely going to have to roll your own. Most solutions I have seen involve broadcasting change notifications via some kind of message-bus. Interested views then subscribe and manipulate any lists accordingly. Another technique, albeit a crude one, is to simply reload all collections whenever the view appears.
This article investigates how to synchronize a UICollectionView to a highly dynamic data source of thousands of records, avoiding any complex, application-specific messaging – while supporting request pagination, animations, and persistent cell-selection.

High Level Goals

A single function call will visually present all the changes in the underlying data, optionally allowing modification of the sort and filter criteria. Specifically:

  1. Remove rows that have disappeared from the result-set.
    This may be the result of being either removed from the database, or simply no longer matching the query criteria.
  2. Add rows that have appeared in the result-set.
    This may be the result of being either added to the database, and/or now matching the query criteria where they previously did not.
  3. Move the positions of rows that now fall in a different location in the result-set.
    This may be the result of either a change in the data, or a change in our criteria.
  4. Reload rows that have changed in the database since they were last displayed.
  5. Retain selection as rows move about.
  6. Avoid a ‘janky’ experience!

Wow! That sounds like a lot… and it is. The code is going to have to requery the database with new WHERE and/or ORDER BY criteria (utilising correct pagination to maintain acceptable performance), calculate what to add/remove/update from the UICollectionView – and issue the appropriate UICollectionView commands. If we do all of this correctly, then the view will operate as normal, animating in-place, while issuing the requisite database requests to pull row information as necessary.

An API such as the following would be nice and easy to use…

Breaking down the work

What I’m trying to achieve here is a fairly significant, so probably best broken up into a couple of smaller steps.

  1. Batching and Caching
    The UICollectionView pulls rows from the underlying data-source a single record at a time. This would perform exceedingly badly if we were to issue an independent SQL request for each cell. We need to pull records in batches, and cache the results to fulfill subsequent demands for rows from the same batch.
  2. Tracking row positions and versions
    We need to know how a row moved between one query criteria, and another, in addition to whether the content of that row has changed (typically achieved through a database field dedicated to maintaing a version number).

Milestone 1 – Batching and Caching

UICollectionViewSource is the delegate which supplies row data to a UICollectionView. Within the WillDisplayCell function, we are going to kick-off an asynchronous Task to issue a SQL query, then populate the view once the Task returns.
Our UICollectionViewSource will look like this:

This is a pretty concise API – so let’s go ahead and implement it.

And the simple class to encapsulate a page of data and the task responsible for populating it:

Batching and Caching: The Result

Here’s what the result looks like in our proof of concept application. After downloading the our data-points from the web and inserting them into a table, the data is pulled into the UICollectionView for display. I’ve added little green ‘flash’ to the status bar every time the database is hit so you can see that requests truly are batched and cached.
Once we’ve scrolled towards the bottom of the list, we can return to the top without hitting the database again – demonstrating the caching of the preloaded data.

When fleshing this out for production code, one would probably extend this area with logic to begin releasing the cached data to alleviate memory demands.

Notice how because queries are issued by an asynchronous Task, the list does not stutter, nor does it take long for the view to initially load, regardless of the fact there are over 1000 rows in this data-set.

Milestone 2 – Tracking Row Position and Version

Given any 2 sequential calls to the database, a specific row may move from one position to another. This may be the result of either a prior database write changing where that row resides, or a change in the query criteria.
Because we are relying on the UICollectionView to pull rows as needed, let’s simplify our problem space by considering a result-set to be just the rows within the visible region. That gives us these scenarios:

  1. A row disappears
  2. A row appears
  3. A row moves from one visible position to another
  4. Content changes on a visible row

We need to disambiguate the underlying cause of the first 2 scenarios, such that we can issue the correct commands to the UICollectionView.

Possible causes for scenario #1

  • The row moved out of the visible region (from inside).
  • The row no longer matches the unpaginated query criteria.

Possible causes for scenario #2

  • The row moved into the visible region (from outside).
  • The row now matches the unpaginated query criteria, whereas previously it did not.

We can actually issue a slightly modified query to capture enough data to perform this disambiguation.
If, whenever we request a page of data from the database, we query for the row positions both the current result-set, and the previously visible result-set – then we will know where the previously visible rows went.
Their position will either stay the same, move position, or disappear. Those that remain in the result-set may also have changed their content, so we’ll need some kind of a version column in our database as well.

Our query to pull a page of data will look something like this:

SELECT * FROM Table WHERE $CRITERIA$ ORDER BY Name LIMIT X OFFSET Y

A query to capture version and position information could simply crafted from the same criteria, and a sub-select to count all records whose order-by value is less than the resultant row:

SELECT Id, Version, (SELECT COUNT(*) FROM Table WHERE $CRITERIA$ AND Name < t.Name) as Position FROM Table t WHERE $CRITERIA$ ORDER BY t.Name

Ok, so we’re going to need to track the positions of visible rows by primary key. Our data-source implementation must then provide methods to query the database for:

  1. Result count.
  2. A batch of results given an offset and size.
  3. A batch of primary keys given an offset and size     we could just pull the primary key field from #2, but this would be more efficient
  4. Row positions for a set of primary keys

Here’s how we do it for our use-case: