Re: HSQL to Firebird auto migration firebird data types
On Mon, May 7, 2018 at 4:25 AM Tamas Bunthwrote: > Hi, > > On Mon, May 07, 2018 at 03:24:49AM +, 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 > ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice
Re: HSQL to Firebird auto migration firebird data types
Hi, On Mon, May 07, 2018 at 03:24:49AM +, 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 ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice
HSQL to Firebird auto migration firebird data types
Hi, 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. However; Secondarily and since it doesn't seem to be settled in the code at the moment I wand to propose this mapping. 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 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 And the third would be to display two additional labels in table editor (Memo[Blob] and OTHER[BLOB]) for consistency with the current UI using hsql. Either way it's good see the work going on and I'm excited to help as I can. Best wishes, Drew ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice