"You have to be really careful" Absolutely. Even if you know the order of updates (which I do). If site A updates an off line record in a cached copy after site B deletes it other sites can receive the change records in order and have the record re-appear (via insert or replace).
One can also get a mess if Mr. Red and Mr Black both get new customers, and enter them and they both get the same ID because the auto-generated int happens to be the same. Both copies get updated with the other guy's data, they then get annoyed and enter the stuff again and it happens over again, but now there are N entries of the other guy's customer in the database depending on how many times they do it. On Wed, Oct 7, 2009 at 3:18 PM, Simon Slavin <slav...@hearsay.demon.co.uk>wrote: > > On 7 Oct 2009, at 7:20pm, Adam DeVita wrote: > > > regarding this > > " The fault is that > > almost nobody does it right: they neglect to keep an 'unaltered > > central copy' and think they can cross-apply journals each time two > > databases talk to one-another. That does not work for various > > reasons." > > > > Would a central repository of journals that can be applied to local > > repositories be sufficient? I suppose I assume that running the same > > program on N workstations with the same set of journals should > > produce N > > identical results. > > You need a copy of the database which is not changed by any site. All > the sites send in their journals. The journals are merged into a > superjournal in time order. The superjournal is then applied to the > central copy of the database. Then the updated database is sent back > out to all sites. > > The problem comes when you apply multiple journals in a different > order. Start with each site with identical copies of a TABLE with > three clients: one managed by Mr. Green, one by Mr. Red, and one by > Mr. Black. 'G R B'. Then, in this order ... > > Mr. Green goes on holiday ... > Site A says that all Mr. Green's customers will be handled by Mr. Red. > UPDATE clients SET contact = 'red' WHERE contact = 'green' > > Mr. Red goes on holiday ... > Site B says that all Mr. Red's customers will be handled by Mr. Black. > > Then Mr. Green comes back from holiday, and Mr. Black goes on holiday > so ... > Site C says that all Mr. Black's customers will be handled by Mr. Green. > > Then they all synchronise databases. See if you can make them all end > up with the same data if they synch against each-other rather than a > central unaltered copy of the databases. Doesn't work: one site might > have 'B B B', another 'R B R'. You can do it only by luck ... by > happening to know in which order people went on holiday. However, if > you always synch against a central unaltered copy of the database you > can synch in any order. Once everyone has synchronised you distribute > a copy of the central database to everyone and they all have identical > data once more. That's the simplest setup. You can get more > complicated by having each site remember which journals they've played > back. > > The problem does not occur if any record can only ever be modified by > one site. But if you have the normal 'anyone can do anything' setup, > you have to be really really careful. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
