Subject RE: [firebird-support] Question regarding ability to branchexecution of script depending on a value in database
Author Adrian Wreyford
Thanks David

The software is distributed to farmers. The software relies on the database
for its functionality, and storage of individual farmer's data. They are not
linked to a central database.

As the software evolves, new features are added, which inevitably require
either restructuring of the database, or adding new tables.

Not everybody upgrades to newer versions, as newer features may at the time
not interest them, but eventually do require upgrades as they are interested
in newer features.

This is when the scripts become complicated, if there is no way to know what
version of database the client has, as they obviously don't want to loose
data already entered.

So my question still stands.

Do I programmatically branch, and run different scripts, depending on the
value of version field, or can I branch from within the scripts (ie test for
value version within a script, and then branch accordingly)?

Regards, and thanks for your very informative and thorough answer.


-----Original Message-----
[] On Behalf Of David Johnson
Sent: 18 September 2005 06:05 AM
Subject: Re: [firebird-support] Question regarding ability to
branchexecution of script depending on a value in database

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,
> 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
> 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
> 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
> 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
> to date irrespective of the version.
> Well I hope I have properly explained what I wish to achieve.
> Thanks in advance
> Adrian


Visit and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at


Yahoo! Groups Links

No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date: 2005/09/15

No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date: 2005/09/15