At 01:38 9/10/2008, you wrote:

>The classic way of changing schemas seems to be unidirectional and to
>assume that all sequential upgrades will be stable and OK.

Correct. So don't put your production database into your test system.

>If they turn out not to be OK, the previous schema and data must be
>restored from a backup, thus losing all data changes since the backup.

You can always retrieve the schema of the original database (assuming you are using "schema" to refer to the metadata). You can also retrieve the metadata of the upgraded database. If this isn't what you mean, then more explanation is necessary.

There is a trick that (as it happens) you can do to "downgrade" a database to the version it was upgraded from. It's not that way by design so you should certainly not plan future upgrades to depend on it.

1. WORK ON A COPY OF THE CURRENT DATABASE in a location away from where the current database is.

2. In the higher server, rename gbak.exe.

3. In its place, copy gbak.exe from the installation the database was upgraded FROM.

4. Use that gbak to back up the database. The backup should now restore in the old server.

>This seems very unsatisfactory because the schema error might not be
>detected for some time and substantial data loss might result.

???????

>In Rails there is a standard methodology for downgrading schemas as
>well as upgrading. It applies a "reverse upgrade" to an existing live
>database by reversing the schema changes applied in the last upgrade.
>
>1. Although this is also undesirable, is it not a better way of
>handling this, as it potentially avoids losing the new data?

Such a methodology wouldn't be applicable to DBMS's where the database engine manages the disposition of data on the disk and metadata is stored within the structure. It might be applicable to some file-based systems such as Access or MySQL-free. I can't see how Rails (or any generic tool) would know how to revert the on-disk structure of any DBMS that lays down data by file-system-independent rules.

>2. Does anyone do this with Firebird? If so, are there any tools to
>help with this?

As above. It's also possible that you might find a third-party tool that someone has developed to simplify it. You could ask on firebird-tools...

Understand that, when you upgrade a database from one major version to another, the on-disk structure changes. This makes the new on-disk structure incompatible with the old. If there was a requirement to make database structures backward-compatible, one could never implement changes or improvements.

Fb 1.5.x ODS 10.1 can be opened by Fb 1.5.x, Fb 2.0.x, 2.1.x and 2.5 engines
Fb 2.0.x ODS 11.0 can be opened by Fb 2.0.x, 2.1.x and 2.5 engines
Fb 2.1.x ODS 11.1 can be opened by Fb 2.1.x and 2.5 engines

In theory, an ODS 11.1 database should be accessible by the Fb 2.0.x engine versions. In practice, that is not the case for some databases, due to the stepwise approach taken to implementing Unicode support in metadata across 2.0 and 2.1. It was a bullet that needed to be bitten at some point...instructions are in the release notes...though we might live to rue the day the decision was made not to do a full-step ODS increment for 2.1...

./heLen