Re: HSQL to Firebird auto migration firebird data types

On Mon, May 7, 2018 at 4:25 AM Tamas Bunth <tamas.bunth@collabora.co.uk>
wrote:

> 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".


> ok - updated my map and will do the same for wiki page


> > 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.

Well, I ran a test file through with a field of type OTHER and what I got
was a column of the same name and type of CLOB. I did NOT run any actual
data through, just the column however.

All the single data type tests I ran are found on the tdf nextcloud also. A
link for that is https://nextcloud.documentfoundation.org/s/W92TGtQpprximdB
(The are in the directory SingleFieldType)

The idea is to end up with a collection of test files that pass through the
migration function without error and with expected results. As the simpler
tests pass I start adding a bit more (so first it was just column
definitions then I added data) and as the project moves from stage to stage
(Alpha, Beta, RC, ... Release) I will go back and run the whole batch to
ensure no regressions.


> > 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