How does fossil synchronize databases?

[Adam Jensen]

How does fossil synchronize sharable changes between a local repository
and a remote repository?

The architecture drh describes in this talk[1] that has a well-managed
central PostgreSQL DBMS that communicates with SQLite databases that are
in the rough and tumble environment of the leaf nodes might be a
generally useful design pattern.

[1]: "SQLite: The Database at the Edge of the Network with Dr. Richard
Hipp" <https://youtu.be/Jib2AmRb_rk>


It might be incredibly useful to be able to set up and operate a database
synchronization scheme from Tcl. So there is tdbc::sqlite3,
tdbc::postgres, and PL/Tcl[2] and my current thinking is that maybe fossil
[3] could be used as a model or example of what is involved synchronizing
the sharable data.

[2]: https://www.postgresql.org/docs/10/static/pltcl.html
[3]: https://fossil-scm.org

If fossil is a good model for a more general Tcl-based system, what's a
good way to figure out how it synchronizes data between local and remote
databases? I am not a C language programmer; if examining the detailed
implementation is the way to go, are there any useful tools to help map
out the data structures, functions and their references? I suppose, in a
way this process might be a bit like reverse engineering the design from
the implementation.

What do you think?

[mango]

On Friday, October 5, 2018 at 11:55:20 AM UTC-7, Adam Jensen wrote:
> How does fossil synchronize sharable changes between a local repository
> and a remote repository?

[snip]
> What do you think?

Have you read:

http://fossil-scm.org/index.html/doc/trunk/www/sync.wiki

Does that answer your question?

[Adam Jensen]

Yes, indeed. Thank you, very much! I am a little surprised that this
protocol continues to use the revision control on files metaphor. Since
drh is the SQLite guy, I sort of expected something closer to the domain
of database replication. Regardless, it could be that something like this
protocol could be used in a general setup if instead of files and deltas,
the primary artifact is the row of a table or a coherent set of several
rows from several tables. There would probably be some necessary
constraints on the database schema (like the use of UUID's for keys) and
it would probably need to conform to some basic design patterns.

Would anyone here like to speculate about the database schema
requirements and design patterns that might work with this kind of
synchronization protocol?

[Eric]

On 2018-10-05, Adam Jensen <han...@riseup.net> wrote:
> On Fri, 05 Oct 2018 12:35:57 -0700, mango wrote:
>
>> On Friday, October 5, 2018 at 11:55:20 AM UTC-7, Adam Jensen wrote:
>>> How does fossil synchronize sharable changes between a local repository
>>> and a remote repository?
>> [snip]
>>> What do you think?
>>
>> Have you read:
>>
>> http://fossil-scm.org/index.html/doc/trunk/www/sync.wiki
>>
>> Does that answer your question?
>
> Yes, indeed. Thank you, very much! I am a little surprised that this
> protocol continues to use the revision control on files metaphor.

It's not a metaphor, that's what Fossil is about. It would be possible
to create a Fossil clone (including that protocol) using some storage
mechanism other than SQLite, even something that is not a database. No
point in actually doing that of course.

Eric
--
ms fnd in a lbry

[Donal K. Fellows]

On 05/10/2018 22:39, Adam Jensen wrote:
> I am a little surprised that this
> protocol continues to use the revision control on files metaphor.

The whole purpose of SQLite is to do revision control on files. That's
literally the main thing it does; everything else is either to support
that or built on top of that.

Donal.
--
Donal Fellows — Tcl user, Tcl maintainer, TIP editor.

[Adam Jensen]

The computer filesystem seems to have a metaphor-like correspondence with
a typical office filing cabinet - a box possibly with drawers which
contains paper folders which contain paper files. Conceptually, this
filesystem metaphor is somewhat different from the database relational
model - a different approach to data organization in storage system. I
was a little surprised to see that fossil, being a database centric
system that comes from the SQLite guy, didn't decompose and translate the
filesystem metaphor into a relational model at a more fine-grained level
and that the underlying operations, like sync, aren't more closely
associated with database notions of structure rather than filesystem
notions of structure. It was a fairly casual observation but relevant
because the system I have in mind needs to operate on database atoms and
compositions (e.g., rows and tables) rather than filesystem atoms and
compositions (e.g., deltas and files).

[Adam Jensen]

On Sat, 06 Oct 2018 16:24:48 +0100, Donal K. Fellows wrote:

> On 05/10/2018 22:39, Adam Jensen wrote:
>> I am a little surprised that this protocol continues to use the
>> revision control on files metaphor.
>
> The whole purpose of SQLite is to do revision control on files. That's
> literally the main thing it does; everything else is either to support
> that or built on top of that.
>
> Donal.

Yep, I get that Fossil's fundamental purpose is to facilitate the
management of simple text documents and their revisions. No problem.

Since Fossil is built upon a database system, SQLite, I thought the core
functions might be more closely related to database structures and
methods. That would have been very convenient for my purposes. I was just
a little surprised that that's not the case and somewhat disappointed
because now my development costs will probably be significantly higher
than if I could have extracted a complete sub-system from Fossil.

It's curious how that minor blurb would be a point of interest to anyone.
What do you suppose is going on?

[Gerald Lester]

Richard (Dr. Hipp) is available for consulting.

--
+----------------------------------------------------------------------+
| Gerald W. Lester, President, KNG Consulting LLC |
| Email: Gerald...@kng-consulting.net |
+----------------------------------------------------------------------+

[Adam Jensen]

On Sat, 06 Oct 2018 16:55:50 -0500, Gerald Lester wrote:

> On 10/06/2018 01:16 PM, Adam Jensen wrote:
>> On Sat, 06 Oct 2018 16:24:48 +0100, Donal K. Fellows wrote:
>>
>>> On 05/10/2018 22:39, Adam Jensen wrote:
>>>> I am a little surprised that this protocol continues to use the
>>>> revision control on files metaphor.
>>>
>>> The whole purpose of SQLite is to do revision control on files. That's
>>> literally the main thing it does; everything else is either to support
>>> that or built on top of that.
>>>
>>> Donal.
>>
>> Yep, I get that Fossil's fundamental purpose is to facilitate the
>> management of simple text documents and their revisions. No problem.
>>
>> Since Fossil is built upon a database system, SQLite, I thought the
>> core functions might be more closely related to database structures and
>> methods. That would have been very convenient for my purposes. I was
>> just a little surprised that that's not the case and somewhat
>> disappointed because now my development costs will probably be
>> significantly higher than if I could have extracted a complete
>> sub-system from Fossil.
>>
>> It's curious how that minor blurb would be a point of interest to
>> anyone.
>> What do you suppose is going on?
>>
>>
> Richard (Dr. Hipp) is available for consulting.

That's probably overkill and I am not currently burdened with control of
excessive financial resources. At this point, it's probably best to make
do with what I kind find and cobble together a demonstration of concept
system either entirely myself [most likely] or maybe in some kind of
grassroots/potluck fashion.

Actually, this need for a database synchronization scheme is an aspect of
a larger system that I have in mind which is still very much in the hobby
project phase. There are two primary components of the larger system: the
Media Annotation, Analysis and Playback Synthesis System (MAAPSS) that
would most likely be used on desktop and laptop computers, and a server
component - [The Metadata Library] (www.metadatalibrary.org).

I keep extensive notes but there aren't any public descriptions or
explanations of this project other than a few minor mentions in a few
posts in this forum (and the very brief web page mentioned above). Most
of my notes are in the form of audio recordings - that's just part of the
way I work. It's almost amusing that a system like MAAPSS is needed to
support the annotation, searching and refinement of those notes into
something that is more readily accessible. So I guess there's something
of a bootstrapping problem if there are any developers interested in
evaluating the potential of the project...

[Eric]

On 2018-10-06, Adam Jensen <han...@riseup.net> wrote:
> On Sat, 06 Oct 2018 12:16:08 +0200, Eric wrote:
>

8>< --------

>>
>> It's not a metaphor, that's what Fossil is about. It would be possible
>> to create a Fossil clone (including that protocol) using some storage
>> mechanism other than SQLite, even something that is not a database. No
>> point in actually doing that of course.
>

> The computer filesystem seems to have a metaphor-like correspondence with
> a typical office filing cabinet - a box possibly with drawers which
> contains paper folders which contain paper files.

and also folders which can contain files and folders which can contain ...

> Conceptually, this filesystem metaphor is somewhat different from the
> database relational model - a different approach to data organization
> in storage system.

For the third time, it's not a metaphor, but the real-world organisation
of the real-world data that Fossil deals with. The Fossil database
design doesn't actually care much about the filesystem - the place in the
filesystem where a file belongs is stored only as the name of the file
(full path name). The filesystem is a hierarchy, and a hierarchy can
be stored in a relational database, but that's not what Fossil does.

> I was a little surprised to see that fossil, being a database centric
> system that comes from the SQLite guy, didn't decompose and translate
> the filesystem metaphor into a relational model at a more fine-grained
> level and that the underlying operations, like sync, aren't more closely
> associated with database notions of structure rather than filesystem
> notions of structure.

Firstly, my impression (possibly wrong) is that DRH wanted something
like Fossil, so he planned it and wrote it, using the tool he knew best
as storage mechanism.

Secondly, from the beginning of the sync protocol documentation:

The global state of a fossil repository consists of an unordered
collection of artifacts. Each artifact is identified by a
cryptographic hash of its content, expressed as a lower-case
hexadecimal string. Synchronization is the process of sharing
artifacts between servers so that all servers have copies of all
artifacts.

This is NOT database replication, it is a limited, application-specific
data exchange.

And this is all way off-topic for this group :-)

[Roderick]

On Sun, 7 Oct 2018, Eric wrote:

> Firstly, my impression (possibly wrong) is that DRH wanted something
> like Fossil, so he planned it and wrote it, using the tool he knew best
> as storage mechanism.

And fossil proves (or at least proved to me) that sqlite3 is very reliable.

> And this is all way off-topic for this group :-)

It speaks also for tcl, since sqlite3 and tcl make a very good
combination.

Indeed, I am using both for a small, perhaps trivial, proyect and
I am very happy.

Rodrigo

[Adam Jensen]

Hi, Eric.

I think there might have been a communication problem in this thread. To
provide context to inform the development of plausible interpretations of
your perspectives and goals and motivations, I skimmed through and read
some of your other posts in this forum. It's quite a history. Anyway,
pacifying a tantrum isn't really my thing. Maybe you should recruit a
lackey or look for a mother figure ;)

Joking aside, Dude, get some help.

[heinrichmartin]

On Sunday, October 7, 2018 at 8:15:48 PM UTC+2, someone wrote:
> pacifying a tantrum isn't really my thing.

But you should not be teasing either. I hope stepping in here is a good idea - I will not take sides.

Both of you, please reconsider your next message before sending. It could destroy an otherwise interesting conversation.