Re: [sqlite] Multi-master replication with updated Versioning extension

[Alexey Pechnikov]

Hello!

On Friday 31 July 2009 18:34:17 Ken wrote:
> I've looked at your code and discussions on this list about the versioning. I 
> have a few questions.
> 
> 1. How are you moving the data around from one master to another?
> 2. How are you applying the changes once moved to the master?

By the tcl scripts. Databases only store data and versions information.

I'm planning to publish some of the tcl scripts on the next week.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication with updated Versioning extension

[Ken]

Alex,

I've looked at your code and discussions on this list about the versioning. I 
have a few questions.

1. How are you moving the data around from one master to another?
2. How are you applying the changes once moved to the master?



--- On Fri, 7/31/09, Alexey Pechnikov <pechni...@mobigroup.ru> wrote:

> From: Alexey Pechnikov <pechni...@mobigroup.ru>
> Subject: Re: [sqlite] Multi-master replication with updated Versioning 
> extension
> To: sqlite-users@sqlite.org
> Cc: "D. Richard Hipp" <d...@hwaci.com>
> Date: Friday, July 31, 2009, 8:42 AM
> Hello!
> 
> I made some changes:
>     hash field in actions table has always
> name "checksum" (so versioning and replication logic doesn't
> influence of hash algorithm)
>     versioning() function without second
> argument now start "local" mode
>     history and actions tables are renamed
> 
> Updated files is here
> http://mobigroup.ru/files/sqlite-ext/versioning/
> 
> Now there are two problems in the realization:
>     the "replace" conflict resolution algorithm
> for SOURCE table may produce errors - tickets 3964, 3982
>     versioning_drop() function doesn't work -
> ticket 4001
> 
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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

Re: [sqlite] Multi-master replication with updated Versioning extension

[Alexey Pechnikov]

Hello!

I made some changes:
hash field in actions table has always name "checksum" (so versioning 
and replication logic doesn't influence of hash algorithm)
versioning() function without second argument now start "local" mode
history and actions tables are renamed

Updated files is here
http://mobigroup.ru/files/sqlite-ext/versioning/

Now there are two problems in the realization:
the "replace" conflict resolution algorithm for SOURCE table may produce 
errors - tickets 3964, 3982
versioning_drop() function doesn't work - ticket 4001

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication with updated Versioning?extension

[Simon Slavin]

On 30 Jul 2009, at 6:19pm, Jay A. Kreibich wrote:

> You're not really supposed to
>  dump and restore a multi-master system.

We're back to talking about synchronising different copies of the  
database again, aren't we ?

Dumping and restoring a multi-master system means restoring the entire  
context of all the copies of the database.  You have to not only  
restore the contents of one copy of the database, but also restore the  
state of all other copies.  And if you're using a journaling method to  
manage your synchrony, restore the state of all the journals too.

The only exception to this is if the dump was done when all sites  
agreed that they all had completely synchronised up-to-date copies of  
the database.  And many multi-master databases are never in that  
state.  So the question is 'what was dumped, and what state was it in' ?

So the next question is how your journaling system was designed.  You  
can design a journaling and synchronising system so that any site(s)  
can restore from a backup at any time, and the next time everyone  
synchronises they'll get their best data.  But often this is not  
considered when the system is designed and the system loses data or  
worse still corrupts everyone else's.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication with updated Versioning?extension

[Jay A. Kreibich]

On Thu, Jul 30, 2009 at 08:33:10PM +0400, Alexey Pechnikov scratched on the 
wall:
> Hello!
> 
> On Thursday 30 July 2009 19:47:39 Jay A. Kreibich wrote:
> >   Then again, given that ROWID values are signed 64 bit values, you
> >   could just start each master at some offset (like +0x00FF)
> >   and not worry about it.  It would still be a good idea to force all
> >   the tables into an AUTOINCREMENT mode somehow.
> 

> Before start replication databases may be copied from prototype 
> database as files and so they are binary equal.

  That still works (outside of adjustments to the sqlite_sequences
  table).
  
  Any replication function is going to manually insert a whole record,
  including the ROWID.  The AUTOINCREMENT values would only be used if
  a master has to insert a brand-new row that otherwise doesn't exist
  anywhere in the cluster of masters.  In that case it has to be
  assigned a globally unique (i.e. UUID) ROWID value.  Off-setting the
  pool of ROWID values each master uses effectively does this.

> UUID is really unique key for multi-master replication.

  And that's more or less what this does.  Any brand new record that is
  created will be assigned a globally unique ROWID value, basically
  making that ROWID a UUID.

> This is bad because ROWID without explicit field is not persistant 
> and may change after dump/restore. Please see illustration of the problem:

  Yes, this is true of almost every database system out there,
  including ones that generate UUIDs.  You're not really supposed to
  dump and restore a multi-master system.  If you trash a database you
  "restore" it by creating an empty master an syncing from a working
  master (or, in the case of SQLite, just copy of the file).  If all
  your masters are trashed and you're building a new cluster, the
  alignment of ROWID values doesn't matter; even if they're freshly
  generated they're still unique across the new one-master cluster.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication with updated Versioning extension

[Jay A. Kreibich]

On Thu, Jul 30, 2009 at 06:11:42PM +0400, Alexey Pechnikov scratched on the 
wall:
> Hello!
> 
> On Thursday 30 July 2009 17:25:15 P Kishor wrote:
> > > I haven't looked at your work in depth, but I am interested in this. I
> > > have implemented a very simple versioning system with TRIGGERs whereby
> > > every change (INSERT, UPDATE, DELETE) in a column in a table is stored
> > > in a versions table along with its primary key, allowing me to go back
> > > and examine any version and roll back to it, if desired.
> 
> Yes, the primary key field is good enough for master-slave replication
> but not for multi-master because each master has self sequence counter.

  You're saying primary key when I think you more specifically mean
  ROWID.  Assuming you could override and force AUTOINCREMENT behavior
  on all tables (that might make an interesting PRAGMA), this could
  help with that:

  http://www.sqlite.org/cvstrac/tktview?tn=3563

  As long as the "BY" is greater than the number of masters and each
  master has a "FROM" that is sequenced, they'll leap-frog over each
  other.

  Then again, given that ROWID values are signed 64 bit values, you
  could just start each master at some offset (like +0x00FF)
  and not worry about it.  It would still be a good idea to force all
  the tables into an AUTOINCREMENT mode somehow.

  PRAGMA request: http://www.sqlite.org/cvstrac/tktview?tn=4002

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication with updated Versioning extension

[Jim Showalter]

MD5 hashes can still collide. How does this implementation deal with 
hash collisions?

- Original Message - 
From: "Alexey Pechnikov" <pechni...@mobigroup.ru>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Thursday, July 30, 2009 7:11 AM
Subject: Re: [sqlite] Multi-master replication with updated Versioning 
extension


> Hello!
>
> On Thursday 30 July 2009 17:25:15 P Kishor wrote:
>> > I haven't looked at your work in depth, but I am interested in 
>> > this. I
>> > have implemented a very simple versioning system with TRIGGERs 
>> > whereby
>> > every change (INSERT, UPDATE, DELETE) in a column in a table is 
>> > stored
>> > in a versions table along with its primary key, allowing me to go 
>> > back
>> > and examine any version and roll back to it, if desired.
>
> Yes, the primary key field is good enough for master-slave 
> replication but not for
> multi-master because each master has self sequence counter. And full 
> record
> hash may be used for master-slave replication on tables without 
> primary keys.
>
> As table-independant way extension use ROWID field and md5 hash of 
> all fields.
> So multi-master is possible with some restrictions such as 
> non-unique records
> is denied. But may be ROWID + hash of record can help for this 
> situation too.
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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

Re: [sqlite] Multi-master replication with updated Versioning extension

[Alexey Pechnikov]

Hello!

On Thursday 30 July 2009 17:25:15 P Kishor wrote:
> > I haven't looked at your work in depth, but I am interested in this. I
> > have implemented a very simple versioning system with TRIGGERs whereby
> > every change (INSERT, UPDATE, DELETE) in a column in a table is stored
> > in a versions table along with its primary key, allowing me to go back
> > and examine any version and roll back to it, if desired.

Yes, the primary key field is good enough for master-slave replication but not 
for 
multi-master because each master has self sequence counter. And full record 
hash may be used for master-slave replication on tables without primary keys.

As table-independant way extension use ROWID field and md5 hash of all fields.
So multi-master is possible with some restrictions such as non-unique records
is denied. But may be ROWID + hash of record can help for this situation too.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication with updated Versioning extension

[P Kishor]

On Thu, Jul 30, 2009 at 7:42 AM, P Kishor wrote:
> Alexey,
>
>
> On Thu, Jul 30, 2009 at 4:42 AM, Alexey Pechnikov 
> wrote:
>> Hello!
>>
>> Please see
>> http://mobigroup.ru/files/sqlite-ext/versioning/
>
> I haven't looked at your work in depth, but I am interested in this. I
> have implemented a very simple versioning system with TRIGGERs whereby
> every change (INSERT, UPDATE, DELETE) in a column in a table is stored
> in a versions table along with its primary key, allowing me to go back
> and examine any version and roll back to it, if desired.
>
> Again, without looking at your code first, what is it that you are
> doing with the C libs that can't be done with just TRIGGERs and a
> versions table?

Ok, I get it. Your code actually creates the versioning tables and
triggers automatically. Great. Will give you feedback once I try it
out.

By the way, DRH has create fossilscm, which is obviously using some
kind of versioning. It might be worthwhile studying that and
incorporating that, if relevant.


>
>>
>> Master-slave may use ROWIDs and multy-master may use md5 hash of full record.
>>
>> This is test version and I'm glad to get any comments and ideas.
>>
>> P.S. md5 extension sources is here
>> http://mobigroup.ru/files/sqlite-ext/md5/
>>
>> Best regards, Alexey Pechnikov.
>> http://pechnikov.tel/
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> Sent from Madison, WI, United States
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication with updated Versioning extension

[P Kishor]

Alexey,


On Thu, Jul 30, 2009 at 4:42 AM, Alexey Pechnikov wrote:
> Hello!
>
> Please see
> http://mobigroup.ru/files/sqlite-ext/versioning/

I haven't looked at your work in depth, but I am interested in this. I
have implemented a very simple versioning system with TRIGGERs whereby
every change (INSERT, UPDATE, DELETE) in a column in a table is stored
in a versions table along with its primary key, allowing me to go back
and examine any version and roll back to it, if desired.

Again, without looking at your code first, what is it that you are
doing with the C libs that can't be done with just TRIGGERs and a
versions table?

>
> Master-slave may use ROWIDs and multy-master may use md5 hash of full record.
>
> This is test version and I'm glad to get any comments and ideas.
>
> P.S. md5 extension sources is here
> http://mobigroup.ru/files/sqlite-ext/md5/
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multi-master replication with updated Versioning extension

[Alexey Pechnikov]

Hello!

Please see
http://mobigroup.ru/files/sqlite-ext/versioning/

Master-slave may use ROWIDs and multy-master may use md5 hash of full record.

This is test version and I'm glad to get any comments and ideas.

P.S. md5 extension sources is here
http://mobigroup.ru/files/sqlite-ext/md5/

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users