To reduce this to the shortest form possible:

You are up against the synchronization issue with the concept of the
Distributed Relational Database Management System.

The first thing to do is take a step back and ask "Is it necessary that
all of the databases are ever completely synchronized?" If the answer
is "yes", then ask the next question, which is "how often must they be
absolutely identical?".

In living businesses, you rarely need all database snapshots to be
identical. You generally need "In agreement up to xxx timestamp as of
yyy timestamp". For business purposes, xxx and yyy may be separated by
hours to days (or sometimes even weeks). It is a given that, at best,
half of your systems will require two connect cycles to bring everything
into synchronization.

If you truly need identity, then you need to be looking at a more
centralized data model. It may involve store and forward for passing
data to the central repository with sub-second phase delay, or the local
datastore may be removed entirely from the system so all operations run
against the central store.

I am well aware that there are reasons for distributed systems, and I am
myself a proponent of them. Firebird is a good fit for a distributed
system, but it only natively supports the back end data store layer.
There is a layer above that which your application programmers will need
to handle - the distributed part - which you are handling with scripts.
The distributed layer is complex and tricky because it is a requirement
that it must be allowed to be imperfect, or it will fail.

Is there are reason that you cannot centralize your repository, or use a
closer-to-real-time store and forward mechanism so your phase delay is
on the order of seconds?

On Sat, 2005-09-17 at 20:57 +0200, Adrian Wreyford wrote:
> Let me explain what I’m trying to do:
>
> Test.fdb, created 20050505, with amongst others a table called version, with
> two fields, version and date.
> Work in progress on the test.fdb
>
> On the 20050606 I compare the test.fdb with the original one created
> 20050505, and generate a script to bring the old test.fdb up to date with
> the new test.fdb.
> This works well.
>
> Now work in progress continues.
>
> On the 20050706 I compare test.fdb with the updated test.fdb updated on the
> 20050606, and generate a script to bring the old 20050606 test.fdb inline
> with the new structure.
> I run the script this works well.
>
> Now in real life my customers don’t always update at the same time.
>
> So when the last script is available, I still have someone with a version
> 20050505 database, trying to apply the 20050706 script which obviously does
> not have the desired effect.
>
> This is what prompted me to add the Version table with Version and Date
> fields.
>
> Now I want to know?
>
> Is it possible to test the version of the table in a script, and then branch
> the script as one would do programmatically say for example:
>
> Connect …
> Then
> If Version.Date = '20050505' then
> begin
> Run script for update 20050505to20050606
> Run script for update 20050606to20050706
> end
> Else
> If Vesion.Date = 20050706 then
> Run script for 20050606to20050706
>
>
> This way I have one script that can bring the databases out in the field up
> to date irrespective of the version.
>
> Well I hope I have properly explained what I wish to achieve.
>
> Thanks in advance
>
> Adrian
>
>
>