Hi Myles,

> > On 02-Aug-2004 06:50:03, Alan McDonald wrote:
> > My application and DBs share a version number. (app gets an additional
> build
> > and minor revision, db gets a major revision. When a schema change is
> > required by the app, both app and db get a major revision increment.
> > At this stage a DB comare is run between the dev copy (source) and the
> prod
> > copy (target). An SQL script is the end product here which is manually
> > checked and inserted into a script component of the app. Each time the

app

> > runs it checks the major app revision with the db app revision. If

db>app,

> > the app terminates with appropriate message to upgrade the app. If

db<app,

> > then the app runs the script, disconnects and reconnects.
> > One weak spot (if you like) is during an upgrade install. This has to be
> > done on one machine with noone else connected. (I stipulate this). This
> app
> > upgrade is then run, it does it's work, and can remain running while all
> > other desktops are upgraded. Once this first one is done, the other
> desktops
> > won't run until they are upgraded. This makes for good protection.
>
> This looks pretty much like what we were expecting to develop. A couple

of

> questions from your answers:
>
> a. Where do you store the version number of the DB? Is this in data in
> the database or somewhere in meta data?

You can do this easily with a small table...

We used to store such a number in our CONFIGURATION table -
it was structured something like:

CONF_NAME varchar(50)
CONF_VALUE_STRING varchar(1000);
CONF_VALUE_INT integer
etc

Next, we would have one row saying:
VERSION_NUMBER, 2.5

and a row saying:
VERSION_UPGRADE, F

When VERSION_UPGRADE was "F" (False), the normal application(s)
could do their login. If this turned to "T", then the connect would be
rejected. Of course, there _was_ an actual connection but hey, this
is the least you can do. There was an automated system in the application
to throw users/applications from the database in order to ensure no
connections left. This was done from the application itself...

> b. Is there a way that you know, where an application can detect if
> other users are logged into the database, to be sure that they can run any
> utility scripts without logged in users getting in the way?
> c. You mentioned that there is no need to unload the data from the
> database when the schema is altered. Am I to assume, that statements like
> 'ALTER TABLE', etc. will not affect data stored in the tables in Firebird?

That depends, an "ALTER TABLE mytable DROP columname" does :-)

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com