HSQL to Firebird auto migration firebird data types

[Tamas Bunth]

Hi,

On Mon, May 07, 2018 at 03:24:49AM +0000, Drew Jensen wrote:
> Looking at the open issues for the HSQL to Firebird auto migration
> functionality particular the issues regarding data types and other test
> files I put together a possible map of what I think makes sense in
> translating hsql to firebird types.  [Read only view of it here
> https://nextcloud.documentfoundation.org/s/TTBT4fJ2xTdbMJQ ]
> 
> I'm sharing it here for two purposes, first it would be a big help to have
> this type of thing with whatever the actual mapping is - so - if that is
> already set I could really use for someone to let me know what would need
> to change there.

- Image type has a special subtype, so it can be distinguished from a normal
  BLOB later: BLOB SUB_TYPE -9546

- Binary [VARBINARY] is mapped to VARCHAR with a special character set
  "OCTETS".

- Binary(fix) is mapped to CHAR with character set "OCTETS".

> The are three suggested changes;
> first is to treat OTHER[OTHER] as a custom subt_type (-1) of blob instead
> of a CLOB as this would give the same behavior in Base with firebird as
> with hsql

IIRC I didn't implement any mapping from type OTHER. It just skips the column
currently. I skipped it because I couldn't find any test files in Bugzilla for
OTHER column types, so I assumed nobody uses it. :)

It would make sense to map it to BLOB or to VARCHAR with character set octets
though.

> second is to treat HSQL Binary(fix)[BINARY] as Binary[BINARY]  with
> sub_type of 0 or 1 based on a scan of some number of the records checking
> for text data

I think if the user wanted to store textual data then he would have created a
CHAR / VARCHAR column at the first place.

Besides that, the user can change the character set from OCTETS to UTF8 later
(with a direct sql). After that the column would act like a normal CHAR/VARCHAR
in the database.

Thanks,
Tamás