> Excellent!
>
> I knew this is what I wanted to do .. just wanted to make sure!
>
> Thanks
>
> Will apply it as you suggest Alan
>
> Adrian
>

Every time I make a release of the app/db, the db version gets parked as the
target db for the next upgrade. Before the next upgrade release, a db
compare (see most admin tools) provides a script to bring the target up to
the reference (current). After several upgrades there will be several
upgrade scripts parked in the application code. If an upgrade is required
from version 1 to version 7, then 6 scripts fire one after the other. If
version 6 to 7 only is required, then only 1 script fires.
Up to you to ensure that this process guards against more than one user
firing up the upgrade app code before it's finished and ensuring that no
users are connected while this upgrade is being carried out.
Alan

>
> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Alan McDonald
> Sent: 18 September 2005 09:19 AM
> To: firebird-support@yahoogroups.com
> Subject: RE: [firebird-support] Question regarding ability to
> branchexecution of script depending on a value in database
>
> >
> > 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.
> >
> > Adrian
> >
>
> All my DBs are versioned (major and minor versions - one record on a
> DBVERSION table).
> On app startup, the app checks the version of the db it is running against
> and compares with the application major and minor version (ie. build
> versioning).
> Minor app upgrades are those which can run against the same major db
> version. Major app upgrades will not run against superceded db versions.
> They halt and ask the user to upgrade. If yes - then the app runs one or
> more scripts which are contained in the app build to bring the db up to a
> compatible version.
> No branching in scripts required, the user must either upgrade to the
> minimum backend version or not, in which case they would not be executing
> the upgraded application.
> If a later DB version is opened by a superceded app version, the app
> terminates after alerting the user to the fact that the db is a leter
> version than the application and cannot run.
> Alan
>
>
> >
> > -----Original Message-----
> > From: firebird-support@yahoogroups.com
> > [mailto:firebird-support@yahoogroups.com] On Behalf Of David Johnson
> > Sent: 18 September 2005 06:05 AM
> > To: firebird-support@yahoogroups.com
> > 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,
> > 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
> > >
> > >
> > >
> >
> >
> >
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > Visit http://firebird.sourceforge.net and click the Resources item
> > on the main (top) menu. Try Knowledgebase and FAQ links !
> >
> > Also search the knowledgebases at http://www.ibphoenix.com
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > 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
> >
> >
> >
> >
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > Visit http://firebird.sourceforge.net and click the Resources item
> > on the main (top) menu. Try Knowledgebase and FAQ links !
> >
> > Also search the knowledgebases at http://www.ibphoenix.com
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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.1/104 - Release Date: 2005/09/16
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>