How to implement this .NET client and PHP server synchronization scenario?

Question

I have a PHP webserver and a .NET mobile application. The .NET application needs data from a database, which is provided (for now) by the php webserver. I'm fairly new to this kind of scenario so I'm not sure what the best practices are. I ran into a couple of problems and I am not certain how to overcome them.

For now, I have the following setup. I run a PHP SOAP server which has a couple of operations which simply retrieve data from the database. For this web service I have created a WSDL file. In Visual Studio I added a web reference to my project using the WSDL file and it generated some classes for it. I simply call something like "MyWebService.GetItems();" and I get an array of items in my .NET application, which come straight from the database. Next I also serialize all these retrieved objects to local (permanent) storage.

I face a couple of challenges which I don't know how to resolve properly.

• The idea is for the mobile client to synchronize the data once (at the start of the day), before working, and then use the local storage throughout the day, and synchronize it back at the end of the day.

• At the moment all data is downloaded through SOAP, and not a subset (only what is needed). How would I know which new information should be sent to the client? Only the server knows what is new, but only the client knows for sure which data it already has.

• I seem to be doing double work now. The data which is transferred with SOAP basically already are serialized objects. But at the moment I first retrieve all objects through SOAP and the .NET framework automatically deserializes it. Then I serialize all data again myself. It would be more efficient to simply download it to storage, and then deserialize it.

• The mobile device does not have a lot of memory. In my test data this is not really a problem, but with potentially tenths of thousands of records I can imagine this will become a problem. Currently when I call the SOAP method, it loads all data into memory (as an array) but as described above perhaps it would be better to have it stored into storage directly, and only retrieve from storage the objects that are needed. But how would I store this? An array would serialize to one big XML (or binary) file from which I cannot choose which objects to load. Would I make (possible tenths of thousands) separate files? Also at the end of the day when I want to send the changes back to the server, how would I know which objects to send... since they're not in memory?

I hope my troubles are clear and I hope you are able to help me figure out how to implement this the best way. :)

Some things are already fixed (like using .NET on the mobile device, using PHP and having a MySQL database server) but other things can most certainly be changed (like using SOAP).

Answer 1

I've never implemented something like this, but form the description, I think the local storage of mobile device acts as a cache. Because it may not be practical if the local storage is of the same size as the server side database. So I would first figure out the suitable size of client side storage, and then try to find a suitable cache replacement stategy. This depends on the nature of your application. The principle is that you keep the data that is most likely to be used in cache. When your application requires scaning the whole data set, i.e. select * from mytable, do this kind of query on the server side. Also connect the server if local cache is miss. To do the cache replacement, you may need to partation the server side data into groups, so that for most groups, if you have the group in your cache, you know you have all of its members in your cache. For example, you can partition a datatable by the day it was last modified, or by (row number mod group size). The point is, only cache the information you need during the day. If its impossible to decide which information will be needed, you can just download the newer groups that is not in local storage. Such as all records for yestaday (since you already have all records from the day before yesterday). I think its critical to find a suitable way to partition the records.

Answer 2

Determining what to sync could probably be solved by using timestamps. Ie. for every record in your DB (this applies to server as well as client side), you store a timestamp whenever the data is modified. Syncing the data could then be as easy as storing the (start)timestamp of the last successful synchronization and fetching all data that has been changed between that timestamp and the current time (which should be stored after sync completes succesfully). Just make sure your clocks are synced, otherwise this approach will not work :p.

You could also set a flag when something is changed and only fetch flagged records when syncing (and reset the flag after sync was succesful of course).

As for the syncing itself, besides SOAP you could also do it the old-school way and simply transfer files that will be processed on the client/server after they have been copied. That way, memory usage should not pose any problems as you can process the file bit by bit as needed.

The big challenge (though it will depend on your actual implementation) will probably be solving potential conflicts, where data regarding the same item was changed on both client and server.