In our recent project with Adobe AIR and Flash CS3, We are working on making offline mechanism to perform best sync in online and offline modes. Normally for Rich Internet Application development, We are using MySQL as database system.

It’s really fun to work with two database systems, one for online transactions (MySQL) and another for offline transactions (SQLite). Analysis part of data, what should store in online database and what should store in local AIR database. How to maintain sync states? And how to optimize the sync? And when to store data locally and when to fetch data from online database! Yeah, all these questions drives you through all excitement, challenges and fun.

Specially, in applications which are using user based authentication and if user is offline – That’s a tough case. I am not sure, what we are using is the most efficient way to do that or not; but we are storing encrypted password in local database which successfully authenticated last time. If user is online, it will authenticate credentials via online transaction or with local transaction, if not connected.

Once user is successfully authenticated via online or offline transaction, In offline case; It’s easy to fetch data from local database and show required data. But in online case, You need to (at least that’s what we are using) show data from local database, for fastest possible data representation. After that we are comparing sync status in background with showing little status update at some corner of the application. If there is no update, no user interaction is required but, if there is any update on server side – We ask user to update the data representation or to stay wherever he is. Based on user’s decision either we discard the data and let him work on local data or we update the local database and refresh the data representation.

In a special case of, user modifies data while is not connected and someone else is also modified the data between the time when user made last sync and this sync. This case really creates the excitement. This requires timestamp based verification of various data in different tables. Also, this data comparison is not just record to record comparison, but it should be set of data action based on what task user is performing with application. Even this part is easy compare to the situation where you need to handle the merging the data. It’s like you are walking on double edge sword. Any mistake will mess up all the data. You need to take care of all little cases even with 0.001 probability! While working with this application, I was thinking about SVN and Git. Just thinking that how they are managing different versions of files, directories and with many users. And that’s programming all about!

We are still working on it and I will share all the possible peace of information I can share.