Following my 4 part series on using two different forms of SQLite across Windows Phone and Windows RT I thought I’d take a couple of hours to see how hard it would be to build a generic synchronisation framework that would allow me to synchronise data between SQLite and a Windows Azure Mobile Service (WAMS). Of course as I want it to work across both Windows Phone and Windows RT I want to make sure all my logic is contained within a Portable Class Library (PCL) – this is initially made possible by the awesome support the WAMS has for PCL. If you haven’t already read my previous posts on SQLite, here a summary list:
Windows (RT and Phone) and Sqlite (Part 1)
Windows (RT and Phone) and Sqlite (Part 2)
Windows (RT and Phone) and Sqlite (Part 3)
Windows (RT and Phone) and Sqlite (Part 4)
Now I apologise in advance, synchronisation is not simple, which may make this post long and hard to follow. I’m also certain that since I wrote this logic in the space of a couple of hours, I’m sure there are bugs I haven’t polished out – the idea here is to give you a heads up on how easily you can write this logic yourself.
We’ll start with some basics:
INotifyPropertyChanged
Since I want my entities to be bindable, and be able to update the UI, all my entities will need to implement INotifiyPropertyChanged. Rather than code this into each entity I, like many others, have a base class that implements this interface and exposes a helper method OnPropertyChanged which can be called when a property is changed.
public class NotifyBase : INotifyPropertyChanged
{
public event PropertyChangedEventHandler PropertyChanged;
protected virtual void OnPropertyChanged
([CallerMemberName] string propertyName = null)
{
PropertyChangedEventHandler handler = PropertyChanged;
if (handler != null)
handler(this, new PropertyChangedEventArgs(propertyName));
}
}
ISyncableEntity
Let’s start with what this interface looks like:
public interface ISyncableEntity
{
int Id { get; set; }
bool IsDeleted { get; set; }
DateTime LastUpdated { get; set; }
DateTime LocalLastUpdated { get; set; }
}
All our entities are going to need to implement this interface because it exposes the minimal set of properties we need in order to do bi-directional synchronisation. By this I mean be able to push all local changes (insert, update, delete) up to WAMS, as well as receive updates back from WAMS (insert, update and delete).
WAMS uses an int as the primary key for all entities. This makes change tracking a little hard as you need to use negative ids to track new entities.
If you’re doing bi-directional synchronisation across any number of clients you need to do logical, not real deletes. This means that all entities will need an IsDeleted flag so that you can a) track local deletes and push them to WAMS before actually removing the local entity and b) so that you can pull a list of deleted entities from WAMS so that they can be removed locally.
The LastUpdated field is used to track when the entity was last updated server-side. Golden rule of synchronisation is never trust the client apps. The LocalLastUpdated field is used to track local updates (ie which entities have been modified) and is not synchronised to WAMS
BaseEntity
Rather than having to implement all those properties on each entity I added a BaseEntity which has these properties.
[DataContract]
public class BaseEntity : NotifyBase, ISyncableEntity
{
[PrimaryKey,DataMember]
public int Id { get; set; }
[DataMember]
public bool IsDeleted { get; set; }
[DataMember]
public DateTime LastUpdated { get; set; }
[IgnoreDataMember]
public DateTime LocalLastUpdated { get; set; }
}
You’ll notice that we’ve added the PrimaryKey attribute to the Id field and have marked the LocalLastUpdated field with the Ignore attribute. The Ignore attribute will prevent that field from being sent to WAMS.
Person
This is going to be the entity that I’m going to be synchronising. As you can image it’s going to inherit from BaseEntity. It also has the DataContract and DataMember attributes to aid with synchronisation.
[DataContract]
public class Person : BaseEntity
{
private string name;
[DataMember]
public string Name
{
get { return name; }
set
{
if (Name == value) return;
name = value;
OnPropertyChanged();
}
}
private int age;
[DataMember]
public int Age
{
get { return age; }
set
{
if (Age == value) return;
age = value;
OnPropertyChanged();
}
}
}
At this point it’s worth creating the corresponding table in WAMS. When you create it, you’ll see it appear with a single int Id column – don’t worry, the other columns will get created automatically as you start to synchronise data. Of course, when you go to production you’ll want to disable the dynamic schema capability of WAMS but for the timebeing it makes life easier.
MainViewModel
For my single page application I have MainPage.xaml with a corresponding view model, MainViewModel (as per code below). This also inherits from NotifyBase so that it can raise property change events to update the UI when values change. MainViewModel also exposes a StateChanged event which is raised when the view model wants the page to change visual state – I’m not a big fan of having string literals lying around so I use an enumeration to determine which state to go between. Essentially there are two states representing the list of people (Normal) and editing a person (EditPerson).
The MainViewModel has two properties that are exposed for data binding: People, an ObservableCollection of our Person entity, and CurrentPerson. CurrentPerson is used by the EditPerson state to allow the user to add or edit a person record.
The bulk of the heavy lifting has been abstracted away into the DataService class, which we’ll talk about in a minute. The DataService class needs to be initiated with an instance of the ISQLiteConnectionFactory, a path to where the database will be stored, and the endpoint and application key for the mobile service. It also requires a list of entity types that it needs to track and sync. With this information, it’s able to expose very simple methods for loading, saving, deleting and synchronising entities.
public class MainViewModel:NotifyBase
{
#region ---------------------- Visual States ----------------------
private enum States
{
Base,
EditPerson,
Normal
}
public event EventHandler<StateChangedEventArgs> StateChanged;
#endregion ---------------------- ----------------------
#region ---------------------- Data binding properties ----------------------
private Person currentPerson;
public Person CurrentPerson
{
get { return currentPerson; }
set
{
if (CurrentPerson == value) return;
currentPerson = value;
OnPropertyChanged();
}
}
private readonly ObservableCollection<Person> people = new ObservableCollection<Person>();
public ObservableCollection<Person> People
{
get { return people; }
}
#endregion ---------------------- ----------------------
#region ---------------------- Configure Data Service ----------------------
private readonly DataService data = new DataService();
public DataService Data
{
get
{
return data;
}
}
public void Initialise(string databasePath, ISQLiteConnectionFactory sqLiteConnectionFactory)
{
Data.ConfigureDatabase(sqLiteConnectionFactory, databasePath, typeof(Person));
Data.ConfigureMobileService("https://xxxx.azure-mobile.net/", "--not a real key--");
}
#endregion ---------------------- ----------------------
#region ---------------------- Local Operations ----------------------
/// <summary>
/// Load data into the people collection (which
/// will automatically populate the UI)
/// </summary>
/// <returns></returns>
public async Task Load()
{
var list = await Data.Load<Person>();
People.Clear();
foreach (var person in list)
{
People.Add(person);
}
}
/// <summary>
/// Save a new or existing person
/// </summary>
public async void SavePerson()
{
if (CurrentPerson == null) return;
await Data.Save(CurrentPerson);
if (!People.Contains(CurrentPerson))
{
People.Add(CurrentPerson);
}
CurrentPerson = null;
StateChanged(this, States.Normal.ToString());
}
/// <summary>
/// Delete the current person
/// </summary>
public async void DeletePerson()
{
await Data.Delete(CurrentPerson);
if (People.Contains(CurrentPerson))
{
People.Remove(CurrentPerson);
}
CurrentPerson = null;
StateChanged(this, States.Normal.ToString());
}
#endregion ---------------------- ----------------------
#region ---------------------- Synchronisation (and reload) ----------------------
/// <summary>
/// Synchronise local data with WAMS
/// </summary>
public async void Sync()
{
await Data.Sync<Person>();
await Load();
}
#endregion ---------------------- ----------------------
/// <summary>
/// Enter edit mode for a new person
/// </summary>
public void BeginAddPerson()
{
CurrentPerson = new Person();
StateChanged(this, States.EditPerson.ToString());
}
/// <summary>
/// Enter edit mode for an existing person
/// </summary>
/// <param name="p"></param>
public void BeginEditPerson(Person p)
{
CurrentPerson = p;
StateChanged(this, States.EditPerson.ToString());
}
/// <summary>
/// Cancel the current edit
/// </summary>
public void CancelPersonEdit()
{
CurrentPerson = null;
StateChanged(this, States.Normal.ToString());
}
}
DataService
Now onto the good stuff… In this post I’m not going to go through the details of this class but it’s all here (and in the attached sample solution) for you to browse:
public class DataService
{
private class SyncMarker
{
[PrimaryKey]
public string TypeName { get; set; }
public DateTime LastSynced { get; set; }
}
private MobileServiceClient MobileService { get; set; }
private ISQLiteConnectionFactory Factory { get; set; }
private string DatabasePath { get; set; }
private string MobileServiceEndpoint { get; set; }
private string MobileServiceApplicationKey { get; set; }
private Type[] Tables { get; set; }
public void ConfigureDatabase
(ISQLiteConnectionFactory factory, string path, params Type[] tables)
{
Factory = factory;
DatabasePath = path;
tables = tables ?? new Type[] {};
Tables = tables.Union(new[] {typeof (SyncMarker)}).ToArray();
}
public void ConfigureMobileService(string endpoint, string applicationKey)
{
MobileServiceEndpoint = endpoint;
MobileServiceApplicationKey = applicationKey;
MobileService = new MobileServiceClient(MobileServiceEndpoint,
MobileServiceApplicationKey);
}
private ISQLiteAsyncConnection connection;
private async Task<ISQLiteAsyncConnection> Connect()
{
if (connection == null)
{
connection = Factory.CreateAsync(DatabasePath, true);
await connection.CreateTablesAsync(Tables);
}
return connection;
}
public async Task<List<TEntity>> Load<TEntity>()
where TEntity : class, ISyncableEntity, new()
{
var con = await Connect();
var list = await (from p in con.Table<TEntity>()
where p.IsDeleted!=true
select p).ToListAsync();
return list;
}
public async Task Save<TEntity>(TEntity entity)
where TEntity : class, ISyncableEntity, new()
{
if (entity == null) return ;
var con = await Connect();
entity.LocalLastUpdated = DateTime.MaxValue;
if (entity.Id != 0)
{
await con.UpdateAsync(entity);
}
else
{
var minentityId = await (from p in con.Table<TEntity>()
where p.Id < 0
orderby p.Id
select p).FirstOrDefaultAsync();
var minId = (minentityId != null ? minentityId.Id : 0) - 1;
entity.Id = minId;
entity.LastUpdated = DateTime.MinValue;
await con.InsertAsync(entity);
}
}
public async Task Delete<TEntity>(TEntity entity)
where TEntity : class, ISyncableEntity, new()
{
if (entity == null) return;
var con = await Connect();
entity.LocalLastUpdated = DateTime.MaxValue;
entity.IsDeleted = true;
if (entity.Id == 0)
{
// entity hasn't been saved locally, so simply return
// nothing more to do (it's like cancel!)
}
else if (entity.Id > 0)
{
await con.UpdateAsync(entity);
}
else
{
// Id<0 indicates new entity which hasn't been sync'd
// so can simply remove out of local db
await con.DeleteAsync(entity);
}
}
public async Task<DateTime?> LastSynced<TEntity>()
{
var con = await Connect();
var typeName = typeof(TEntity).Name;
var lastSynced = await (from marker in con.Table<SyncMarker>()
where marker.TypeName == typeName
select marker).FirstOrDefaultAsync();
return lastSynced != null ? lastSynced.LastSynced : default(DateTime?);
}
public async Task UpdateLastSynced<TEntity>(DateTime lastUpdated)
{
var con = await Connect();
var typeName = typeof(TEntity).Name;
var lastSynced = await (from marker in con.Table<SyncMarker>()
where marker.TypeName == typeName
select marker).FirstOrDefaultAsync();
if (lastSynced == null)
{
lastSynced = new SyncMarker
{ TypeName = typeName, LastSynced = lastUpdated };
await con.InsertAsync(lastSynced);
}
else
{
lastSynced.LastSynced = lastUpdated;
await con.UpdateAsync(lastSynced);
}
}
public async Task Sync<TEntity>()
where TEntity : class, ISyncableEntity, new()
{
var con = await Connect();
var remoteTable = MobileService.GetTable<TEntity>();
// Need to download existing entities
var marker = await LastSynced<TEntity>();
var dateTimeStamp = marker ?? DateTime.MinValue;
var newTimeStamp = dateTimeStamp;
var remoteentities = await(from p in remoteTable
where p.LastUpdated > dateTimeStamp
select p).ToListAsync();
foreach (var entity in remoteentities)
{
var local = await (from p in con.Table<TEntity>()
where p.Id == entity.Id
select p).FirstOrDefaultAsync();
entity.LocalLastUpdated = entity.LastUpdated;
if (local != null)
{
if (!entity.IsDeleted)
{
await con.UpdateAsync(entity);
}
else
{
await con.DeleteAsync(local);
}
}
else
{
if (!entity.IsDeleted)
{
await con.InsertAsync(entity);
}
}
newTimeStamp = newTimeStamp < entity.LastUpdated ?
entity.LastUpdated : newTimeStamp;
}
// Upload changed entities
var updatedentities = await (from p in con.Table<TEntity>()
where p.Id > 0 &&
p.LocalLastUpdated > newTimeStamp
select p).ToListAsync();
foreach (var entity in updatedentities)
{
await remoteTable.UpdateAsync(entity);
entity.LocalLastUpdated = entity.LastUpdated;
if (!entity.IsDeleted)
{
await con.UpdateAsync(entity);
}
else
{
await con.DeleteAsync(entity);
}
newTimeStamp = newTimeStamp < entity.LastUpdated ?
entity.LastUpdated : newTimeStamp;
}
// Upload new entities
var newentities = await (from p in con.Table<TEntity>()
where p.Id < 0
select p).ToListAsync();
foreach (var entity in newentities)
{
await con.DeleteAsync(entity);
entity.Id = 0;
await remoteTable.InsertAsync(entity);
entity.LocalLastUpdated = entity.LastUpdated;
await con.InsertAsync(entity);
newTimeStamp = newTimeStamp < entity.LastUpdated ?
entity.LastUpdated : newTimeStamp;
}
await UpdateLastSynced<TEntity>(newTimeStamp);
}
}
Hopefully this gives you a foundation and you can work through the basics of doing data sync to the cloud…. Well, almost…. There is one thing that you may be wondering after trawling through this code. How does the LastUpdated property every get updated? As I mentioned before, with synchronisation you never trust the client. As such it’s the responsibility of the WAMS to update the LastUpdated property as part of both insert and update:
function insert(item, user, request) {
item.LastUpdated= new Date();
request.execute();
}
function update(item, user, request) {
item.LastUpdated=new Date();
request.execute();
}
You’ll need to amend the scripts for all entities you wish to sync.
Now, the final thing we haven’t looked at is what you do for conflicts. We’ve been overly optimistic and have assumed that last in wins. Not great if you have multiple users all writing data to the same records. We’ll leave this for a future post.