Bug 104942 - Conversion HSQLDB to Firebird, Table design issues
Summary: Conversion HSQLDB to Firebird, Table design issues
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.3.0.0.beta2
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:6.1.0 target:6.0.0.2
Keywords:
Depends on:
Blocks: Database-Firebird
  Show dependency treegraph
 
Reported: 2016-12-27 16:24 UTC by Gerhard Schaber
Modified: 2018-01-13 18:12 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample HSQLDB file (36.58 KB, application/vnd.sun.xml.base)
2016-12-31 18:58 UTC, Gerhard Schaber
Details
tested.odb manually "converted" to Firebird by drag-and-dropping the tables (8.48 KB, application/vnd.oasis.opendocument.database)
2018-01-02 12:24 UTC, Tor Lillqvist
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gerhard Schaber 2016-12-27 16:24:39 UTC
The Firebird support is much better now, but there are still some table design related issues.

I am trying to move a HSQLDB based file to a Firebird based file, because I consider Firebird better and faster. I just drag tables from one base document to the other, but have a couple of issues to get the table design over to the Firebird based file.

1. The definition is copied, but not the table data, nor the indexes.
Error code: 1
firebird_sdbc error:
*Attempt to reclose a closed cursor
caused by
'isc_dsql_free_statement: close cursor'

2. All auto increment fields of the source document become normal fields in the Firebird base file.

3. When I then change the auto increment from No to Yes, I get the error:
SQL Status: IM001
Changing autoincrement property of existing column is not supported

4. When I try to create a view from a query (right-click, select Create as View), then I get the following error.
Error code: 1
firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 397
*(
caused by
'isc_dsql_prepare'

This issue is slightlyrelated to bug #103715.
Comment 1 m.a.riosv 2016-12-27 22:37:53 UTC
Please could you attach the HSQLDB file as sample to test?
Comment 2 Gerhard Schaber 2016-12-31 18:58:30 UTC
Created attachment 130063 [details]
Sample HSQLDB file
Comment 3 m.a.riosv 2017-01-01 20:15:40 UTC
Reproducible.
Version: 5.3.0.1 (x64)
Build ID: 3b800451b1d0c48045de03b5b3c7bbbac87f20d9
CPU Threads: 4; OS Version: Windows 6.19; UI Render: default; Layout Engine: new; 
Locale: es-ES (es_ES); Calc: group

Many field types are not avaible in FB, in some table data are copyed, by example with "Lehrgangsgruppen" table.
Comment 4 Gerhard Schaber 2017-01-02 09:16:16 UTC
A related question. Is there a way to access the SQL commands for a Firebird DB, just like the "database\script" file in an HSQLDB ODB file? That would be useful for the future, if one has to migrate a database again.

What I will try as workaround is to change the data types of the HSQLDB not supported by Firebird to supported ones, and then run the commands from the "database\script" file of the ODB file. This way the indexes would also be created.

I noticed that indexes are not even copied when I drag a table from one HSQLDB file to another. So this seems to be a general limitation.
Comment 5 Gerhard Schaber 2017-01-02 11:53:44 UTC
The most annoying two issues is number 1. This happens with every single row that is being copied, and you need to confirm a dialog for every single one.

Even, if I create the table manually in the Firebird file, when I try to copy only the data from the HSQLDB file for the Geschlecht (Gender) table, I get the following error:
SQL Status: IM001
The driver does not support the function 'XParameters::setByte'.

The reason seems to be TINYINT. Well, since I only use that in 3 places, I can change that easily in the source file, but then still there is the issue number 1, which makes is nearly impossible to get data over from an HSQLDB file.
Comment 6 m.a.riosv 2017-01-02 23:36:33 UTC
Few days ago I have reported about to have all field types set up for FB.
https://bugs.documentfoundation.org/show_bug.cgi?id=104734
Comment 7 Gerhard Schaber 2017-01-10 17:47:02 UTC
See also #105240 for an issue with inserting rows into a table with auto generated values.
Comment 8 Gerhard Schaber 2017-02-06 11:49:48 UTC
Same issue with libo-master-2017-02-06_00.00.55_LibreOfficeDev_5.4.0.0.alpha0_Win_x86.msi
Comment 10 Commit Notification 2018-01-01 20:05:11 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

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

Related tdf#104942: implement setByte on firebird

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 Tor Lillqvist 2018-01-02 12:23:46 UTC
As far as I could see, copying the tables from the HSQLDB .odb database in Comment #2 into a freshly created empty Firebird .odb database works now in master. Resolve as fixed? I will attach the resulting .odb.

I do se warnings like:
warn:connectivity.firebird:1905:1905:connectivity/source/drivers/firebird/PreparedStatement.cxx:269: firebird_sdbc error:
*Attempt to reclose a closed cursor
caused by
'isc_dsql_free_statement: close cursor'

but they are indeed "just" warnings. Should one still be concerned?
Comment 12 Tor Lillqvist 2018-01-02 12:24:38 UTC
Created attachment 138798 [details]
tested.odb manually "converted" to Firebird by drag-and-dropping the tables
Comment 13 Lionel Elie Mamane 2018-01-02 12:26:21 UTC
(In reply to Tor Lillqvist from comment #11)
> As far as I could see, copying the tables from the HSQLDB .odb database in
> Comment #2 into a freshly created empty Firebird .odb database works now in
> master. Resolve as fixed? I will attach the resulting .odb.
> 
> I do se warnings like:
> warn:connectivity.firebird:1905:1905:connectivity/source/drivers/firebird/
> PreparedStatement.cxx:269: firebird_sdbc error:
> *Attempt to reclose a closed cursor
> caused by
> 'isc_dsql_free_statement: close cursor'
> 
> but they are indeed "just" warnings. Should one still be concerned?

They seem to be a sign of incorrect resource management by the Firebird SDBC driver, so I wouldn't be unhappy to have them understood and fixed. If a cursor is closed "too early", then it might be closed when still needed.
Comment 14 Commit Notification 2018-01-02 15:34:53 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d4469196a2072d2d3fa2e433d02552facaa269f2&h=libreoffice-6-0

Related tdf#104942: implement setByte on firebird

It will be available in 6.0.0.2.

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 15 Tor Lillqvist 2018-01-03 09:17:43 UTC
I think it would be a good idea to split this bug up into separate individual easily verifiable bugs, to the extent there are issues left. How many of the four items in the initial comment are still present?
Comment 16 Gerhard Schaber 2018-01-03 09:50:24 UTC
Hello, I can test as soon as there is a new nightly build where these and related fixes are included. I am having other Firebird unrelated troubles with with 6.1.
Comment 17 Gerhard Schaber 2018-01-08 23:56:28 UTC
1. and 2. work.
3. still does not work. It tries to create an additional primary key. But explicitly setting the previous primary key as primary key again does also not help.
4. Does not work either. But it says "No table format could be found", now.

Tested with:
Version: 6.1.0.0.alpha0+
Build ID: dbf83d315acc454b576355f2e5bd8412586827ac
CPU threads: 8; OS: Windows 6.1; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-01-07_23:57:11
Locale: de-AT (de_AT); Calc: group threaded

I am glad that the major two issues have been resolved. Thanks.
Comment 18 Julien Nabet 2018-01-13 17:10:20 UTC
(In reply to Gerhard Schaber from comment #17)
>...
> 4. Does not work either. But it says "No table format could be found", now.
On pc Debian x86-64 with master sources updated today, I indeed reproduce the error message but if you select Refresh tables, you'll see that the view has been created.
Comment 19 Julien Nabet 2018-01-13 18:12:58 UTC
I forgot to say that if I create a view with "Create View" wizard, the view doesn't appear automatically too.

In comparison, with hsqldb embedded, the view appears automatically (so with using "Refresh Tables") in both cases, so no error message of course with using "Create as View" on a query.