Bug 104734 - FIREBIRD: Add those field types that are not available with FB while they are available with Hsqldb
Summary: FIREBIRD: Add those field types that are not available with FB while they are...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.4.0.0.alpha0+
Hardware: All All
: medium enhancement
Assignee: Tamas Bunth
URL:
Whiteboard: target:5.4.0 target:5.3.1 target:6.1.0
Keywords:
Depends on:
Blocks: Database-Firebird-Default Base-Images
  Show dependency treegraph
 
Reported: 2016-12-17 14:33 UTC by m.a.riosv
Modified: 2017-12-30 21:51 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description m.a.riosv 2016-12-17 14:33:51 UTC
Description:
Recently with #103074 fixed, BOOLEAN type was added to Firebird, but several field types are not available with Firebird while they are with Hsqldb.

Name                Hsqldb              Firebird            
Tiny Integer        TINYINT             (1) SMALLINT        
BigInt              BIGINT              BIGINT              
Image               LONGVARBINARY       (1) BLOB            
Binary              VARBINARY           (1) BLOB            
Binary (fix)        BINARY              (1) BLOB            
Memo                LONGVARCHAR         (1) BLOB            
Text (fix)          CHAR                CHAR                
Number              NUMERIC             NUMERIC             
Decimal             DECIMAL             DECIMAL             
Integer             INTEGER             INTEGER             
Small Integer       SMALLINT            SMALLINT            
Float               FLOAT               FLOAT               
Real                REAL                (1) DOUBLE PRECISION
Double              DOUBLE              DOUBLE PRECISION    
Text                VARCHAR             VARCHAR             
Text                VARCHAR_IGNORECASE  VARCHAR (2)         
Yes/No              BOOLEAN             BOOLEAN             
Date                DATE                DATE                
Time                TIME                TIME                
Date/Time           TIMESTAMP           TIMESTAMP           
OTHER               OTHER               (1) BLOB            
BLOB                --                  BLOB                
                                                       
(1) Not defined for FB in base, substitute types to use with FB
(2) Looks it can be achivied by defining a specific Collation for the column.
http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-datatypes-chartypes.html#fblangref25-datatypes-chartypes-collation"


Actual Results:  
Not all field's tupe defined for FB.

Expected Results:
Make easier to transpose a database with the embedded Hsqldb to Firebird dragging the tables, without need to reenter the column definition.


Reproducible: Always

User Profile Reset: No

Additional Info:


User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:50.0) Gecko/20100101 Firefox/50.0
Comment 1 Alex Thurgood 2016-12-19 08:26:17 UTC
Some of this is already being considered in other bug reports, for example, the various CLOB sub-type implementations. Not sure of the bug report number, but it is already in the system.
Comment 2 Commit Notification 2017-01-14 12:14:27 UTC
Tamás Bunth committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=882db8709f4ebc9f170cf743f39434d791b34b14

tdf#70433 tdf#104734 implement sdbc XClob

It will be available in 5.4.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 3 Drew Jensen 2017-01-23 04:54:51 UTC
Using 5.4 Alpha0 downloaded yesterday under Linux

With embedded Firebird, using the GUI table designer the following dataypes are _not_ available:
 Image, Binary, Binary fix, Memo, OTHER

A new datatype is available:
 BLOB 



I wasn't sure if those were expected in this build, I kind of thought not reading a couple of comments but thought I'd mention it.

For the other data types I was able to for create a table using all of them, with default scaling information where applicable.

In a few cases the display of column information in the table editor was not what I expected when the table design was opened for editing.
i.e.

FLOAT creating the column UI defaults to Length 7, Decimal places 1; open for editing design displays Length 0, Decimal places 1, which seems to be a display issue, not a change to the column in the database.

DOUBLE creating the column UI defaults to Length 10, Decimal places 1; open for editing design displays Length 0, Decimal places 1, which seems to be a display issue, not a change to the column in the database.

DECIMAL creating the column UI defaults to Length 15, Decimal places 1; but is displayed later as Length 8, Decimal places 1. Here the column in the table was created as scale (8,1) apparently.
Comment 4 Commit Notification 2017-01-24 06:14:00 UTC
Tamás Bunth committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c6d9e749b72c18cb71f7ffc25bb3474ee5c355b4&h=libreoffice-5-3

tdf#70433 tdf#104734 implement sdbc XClob

It will be available in 5.3.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 5 Xisco Faulí 2017-07-14 13:20:34 UTC
Polite ping: is this bug fixed? if so, please close it as RESOLVED FIXED
Comment 6 Lionel Elie Mamane 2017-07-14 13:31:11 UTC
(In reply to Xisco Faulí from comment #5)
> Polite ping: is this bug fixed? if so, please close it as RESOLVED FIXED

Only partially
Comment 7 Commit Notification 2017-12-28 09:40:20 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=bf662904c4b60e93c6b86e06288d41996eed12a2

tdf#104734 Firebird: Add Binary (fix) type

It will be available in 6.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 8 Commit Notification 2017-12-28 09:41:37 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e7a80ddb91c44711296aa996bf00242edbfba32d

tdf#104734 Firebird: Add VARBINARY type to driver

It will be available in 6.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 9 Tamas Bunth 2017-12-28 10:26:46 UTC
According to the openoffice wiki[1] and a HSQLDB documentation[2] the OTHER type is for "serialized Java objects".

I'm not sure if it makes sense to allow the creation of this type when using Firebird as a backend.

It can simply be mapped to BLOB when it comes to migrating from a HSQLDB database though.

[1]https://wiki.openoffice.org/wiki/Base/Data_Types
[2]http://hsqldb.org/doc/guide/sqlgeneral-chapt.html
Comment 10 Commit Notification 2017-12-28 10:29:08 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f80b51ae441e3483a2e9b77a30b932d4e8fba192

tdf#104734 Firebird improve XClob implementation

It will be available in 6.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 11 Lionel Elie Mamane 2017-12-28 14:12:36 UTC
(In reply to Tamas Bunth from comment #9)
> According to the openoffice wiki[1] and a HSQLDB documentation[2] the OTHER
> type is for "serialized Java objects".

Well, that's what it is with a Java database / JDBC driver.

There is some hope it could be more a bit more general than that in SDBC, but:

 * indeed there doesn't seem to be a decent way to migrate the data
   from a Java database to a non-Java database in all generality
   (but read below).

 * I'm not sure any driver has a meaningful / working implementation of the
   OTHER datatype in LibreOffice; even the JDBC/HSQLDB driver, I'm not sure
   how it would handle arbitrary Java objects and deliver to the C++ SDBC layer.

Reading the implementation of getObject() in connectivity/source/drivers/jdbc/ResultSet.cxx it seems to me that the only Java objects (classes) that can actually be fetched (from a JDBC/HSQLDB database to LibreOffice/SDBC) are: String, Boolean, Date, Time, Timestamp. Any other Java class will _not_ be returned (fetched).

If one would really want to (or if one would have a library that does it), one could reimplement a deserializer for the Java serialization (it only has to handle the above classes) and be sure to be able to migrate even OTHER columns. Not sure it is worth the effort.

https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sdbc_1_1XRow.html#a90cbbeb5df3abc5521f2465cd97be4f7
https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sdbc_1_1DataType.html#a3d8b0e16a3f79539f5d4acd28cdc59c9

> It can simply be mapped to BLOB when it comes to migrating from a HSQLDB
> database though.

If you want to offer a seamless migration, and 100% feature-for-feature, then you need to have "some column type" in Firebird where you can stick any of String, Boolean, Date, Time, Timestamp and getObject() must deliver an Any with the corresponding String, Bool, Date, Time or Timestamp inside. That's then your "OTHER" datatype.

(I'm not sure anybody is actually using the OTHER datatype in LibreOffice, but <shrug> one is always surprised.)
Comment 12 Commit Notification 2017-12-28 15:20:56 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0217031a98508731f15df9d361a6e5b584db5716

tdf#104734 Firebird: Add LONGVARBINARY/Image type

It will be available in 6.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Tamas Bunth 2017-12-28 17:14:24 UTC
Besides OTHER, the TINYINT type is also missing.

There is no such type defined by Firebird, but it might be supported in the future[1].

As a workaround, it could be mapped to SMALLINT when migrating to Firebird. Of course it's not a 100% solution (SMALLINT can store TINYINT data and more)

Considering that only these two types are missing, I would close this bug (and maybe create new trackers for these missing types, if there is someone who misses them)

[1] http://tracker.firebirdsql.org/browse/CORE-5635
Comment 14 m.a.riosv 2017-12-28 21:06:49 UTC
Really thanks for the work @Tamas. IMO a needed step for the Firebird integration.

OTH I think should be resolved as FIXED instead worksforme, the usual when there are patches to resolve.