On 10/6/09 15:47 , "Simon Slavin" <slav...@hearsay.demon.co.uk> wrote:
> > On 5 Oct 2009, at 2:59pm, Reusche, Andrew wrote: > There are very many problems associated with synchronising two copies > of the same database. I'm serious: entire books are written about > just this one subject. I am currently facing a similar problem, and I was interested by this. Unfortunately a cursory search of Amazon or Google did not uncover such a book. Would you maybe suggest a reference? The problem I am currently tackling is the following: 1- I have p=1 (will always stays 1) central server running under mySQL v5.x. The use of mySQL is currently a given, but the server might in the future migrate to something else. I have no visibility or control over that, so let's keep mySQL in this context. 2- I have n (currently n=1, but it is likely to grow soon so assume n>1) "core clients" applications using a SQLite database to store their data. 3- I have p (currently p=0, it may or may not grow) "secondary clients" that might modify the data on the central server directly. The foreseen use case is a web application using PHP or some such. The central server is supposed to be the master reference. All clients are responsible for keeping up to date with the master server. I control: the core client application and I can impose any set of rules to any future secondary client. I also control the data models both on the core clients and on the server to the extent that I can add tables and columns to the client's domain data. I also may be able to impose constraints on the client's domain data if there are good reasons. So far my solution (currently about 80% done, but I expect the devil to show up in the last few %) basically does: - add two columns to all domain tables in the client. One, "central_id" is storing a central ID that is set by the server. Any record where central_id=0 is thus only local and needs to be sent to the server. The second, "stamp" is a modification stamp maintained by the server. Given two instances of the same record (where central_id is equal), the current one is the one with the higher stamp. Stamp can be implemented with a revision count or a time stamp or whatever. - replicate the domain tables to the central server with the same columns. An additional boolean column is added on the server only to be used as a "deleted" marker. - upon insert of a new record locally in one client my code sends it to the central database where both the remote_id and the stamp are set and returned to the local client, who stores them locally also. This is conceptually an "on insert" trigger. - upon modification of an existing record locally, the modifications are pushed to the central database where the stamp is "incremented" and returned to the client where is modified too. This is conceptually an "on update" trigger. - upon deletion of an existing record locally, the record is actually deleted from the client data, but it is only marked as deleted in the server. Its central_id is not made available. The data stays there but becomes invisible to the normal client behaviour since all queries in effect have an "and not deleted" condition. This is conceptually an "on delete" trigger. Management of central_id and stamp on the server is done purely on the server using mySQL autoincrement and triggers. Their management on the client is done by my code. - on a regular basis by polling AND before any action started by the user which might use any existing data (basically everything except a pure creation from scratch), bring the local database up to date with in essence a query similar to: SELECT * FROM aTable WHERE stamp > last_sync_stamp. Of course, last_sync_stamp is maintained locally per table. That's it and it's all very naïve. I'd appreciate some feedback here or pointers to litterature. Many thanks, Jean-Denis _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
