Re: [sqlite] Features of SQLite question

[Brent Shifley]

Wow!  I feel I just tried to eat an elephant.  ;)

What I am thinking of doing is creating a specialized app for handling contacts 
related to a business that I own.  I am formerly a software tester that was 
exposed to a lot of different things, including a little sql.  Now, back to the 
problem...

So, if I understand things correctly, if I created said app, with associated 
database, if I had identical databases on both iPads, and tracked all 
transactions and used them to modify a third copy of the database, that this 
might work?

Uh, right.

Since I am beginner programmer / beginner sql dude (can you tell I like a 
challenge / pain) how would I store said transactions so that I can readily 
post them against the third db?

Sent from my iPad

On Jun 11, 2012, at 5:31 PM, Simon Slavin  wrote:

> 
> On 11 Jun 2012, at 11:11pm, "Black, Michael (IS)"  
> wrote:
> 
>> Isn't it true that semi-accurate (sub-second) time-tagged transactions will 
>> generally keep 2 databases in sync?
>> 
>> I've done that before with considerable success.  You don't sync the 
>> fields...you sync the transactions. The presumption is that later 
>> transactions win regardless of intervening updates.
> 
> Your idea, if expressed in terms of SQL, is that you do not keep copies of 
> the records in the databases, instead you keep copies of all the INSERT, 
> UPDATE and DELETE commands executed.  To synchronise two databases you just 
> make sure all the commands are executed on all copies of the database.
> 
> It's a great idea.  Except it doesn't work unless the commands are executed 
> in the same order and there's no way to do that starting with one or other 
> copy which has been changed since the last sync.  So you need a third copy of 
> the databases: the database as it was the last time two copies were 
> synchronised.  And this needs to be stored either centrally or with each copy 
> of the database, which means there's no magic simple synchronisation 
> algorithm.
> 
> And then you get into intent.  Suppose you have this row of data:
> 
> {name: Mike Smith, town: London}
> 
> on your database.  Since the last synchronisation one user of the database 
> executes the command
> 
> UPDATE contacts SET town='Hendon' WHERE town='London'
> 
> And an hour later the user of the other copy executes the command
> 
> DELETE FROM contacts WHERE town='Hendon'
> 
> Before the two were synchonised, the row for Mike Smith still exists in both 
> copies.  Both users were happy with this, even though they didn't know they 
> had different towns for Mike.  But after the two are synchonised and all 
> transactions are played back in log order, Mike Smith has somehow disappeared 
> from both copies.  That's not synchronisation, that's deleting data we wanted 
> to keep !  That's disastrous !
> 
>> Only problem is when times are too close (beyond your time-sync resolution) 
>> which requires human intervention...though in a user-driven system that 
>> should be nigh on to impossible to create.
> 
> For the sake of discussion you can pretend that your log stores times to Unix 
> epoch precision.  Even with that, as you can see, there are still problems.
> 
>> I do realize the complexity of keeping two database in syncbut 
>> transaction systems have been around for decades.  Depends on your 
>> application.
> 
> Yet it's still an unsolved problem.  I can (maybe should) write some text 
> layout out this problem and some solutions which work in some situations but 
> there's still no general solution that I know of.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Features of SQLite question

[Brent Shifley]

I was wondering.  If you had a SQLite database on two different iPads, then had 
different changes applied to each database, is it possible to sync the changes 
to each database?


Sent from my iPad
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users