Subject Re: [firebird-support] Database versions
Author Paul Vinkenoog
Hello Jesus,

>> First, I don't know which InterBase or Firebird version your database is.
> It is Firebird 1 dialect 1

> I have run gfix -sql_dialect 3, then extrated metadata and i have created
> the database from metadata without errors and warnings. Do that mean that i
> can change the database dialect without pump the data??

Maybe, but you should do things in the right order:

*First* extract the metadata from your Dialect 1 database.
*Then* start isql, give the command "set sql dialect 2", and run the metadata script.

This will give you a list of points that need attention.

After that, you can estimate if it's possible to change the dialect in-place
(that is: on the populated database itself, without data pumping).

Based on what you have told, this will probably be the case:
- (old) DATE fields will smoothly become TIMESTAMPs (if they aren't already);
- large NUMERICs and DECIMALs can only be tackled after the conversion anyway;
- and you don't have double-quoted string constants.

At this point, you take a (backup-restored) COPY of the database and run
gfix -sql_dialect 3 on it. Test the result with gfix -validate.

If it's OK, you can upgrade the original database (but make another backup
first, just in case).

Lastly (after the upgrade), you can change some TIMESTAMP fields to DATE
or TIME, and some large NUMERIC/DECIMAL fields to DOUBLE PRECISION. If you
want to transform them to INT64, you have to recreate them as new columns
and move the data internally.

>> TIMESTAMP (old DATE) is an 8-byte field and (new) DATE and TIME are both
>> 4-byte fields. I don't know the internal representation (once did, but
>> forgot) but that's not important for the migration.
> Are you sure are stored as 4 byte?. I have one domain for PK of type
> integer, and if i try to change the domain from integer to bigint, the
> engine raises an error, but if i try to change one domain of type timestamp
> to date or time, no error is raised.

BIGINT isn't available in Firebird 1, nor is it in Dialect 1.

The underlying INT64 type is available in Firebird 1 Dialect 3, but you can't
use it directly; you have to use NUMERIC/DECIMAL with precision > 9. From
Firebird 1.5 onward you can use BIGINT (but only in Dialect 3).

About the 4-byte storage of DATE and TIME: This is what the (Borland) docs
say. I didn't check it. But it doesn't matter much: the conversion works.

Paul Vinkenoog