Bug Hunting Session
Bug 117106 - HSQLDB -> Firebird migration
Summary: HSQLDB -> Firebird migration
Status: RESOLVED WONTFIX
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.0.0.alpha0+
Hardware: All All
: medium critical
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Firebird-Migration
  Show dependency treegraph
 
Reported: 2018-04-19 13:28 UTC by Florian Reisinger
Modified: 2018-04-20 08:36 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
Standard-database of Base-Handbook. Should be migrated completely. (971.09 KB, application/vnd.oasis.opendocument.database)
2018-04-19 14:47 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Florian Reisinger 2018-04-19 13:28:24 UTC
Based on https://bugs.documentfoundation.org/show_bug.cgi?id=116944#c42 a list of TODO items for the transition:

As asked on the above listed bug discussion on the bug itself should be kept to a minimum. (Where to discuss)

- Converting HSQLDB databases from HSQLDB to Firebird should not be enabled by default in 6.1 -> Should be an experimental option.
  - Expert configuration entry should be added to force / ask / delay upgrade
   - In "RELEASE CONFIGURATION" builds default should be "delay", in others "ask".

- After converting the ODB file there must not be the HSQLDB data in the file anymore.
  - Ask to store in new file

- Automated tests should be created that cover the following aspects. The test document should be a HSQLDB one as explicitly the transition to Firebird should be tested:

- Relationships are working between tables:
 - 1:1
 - 1:n
 - n:m

- Auto increment of elements working (needs to be checked after converting)
- Other constraints on elements must work as well
  - UNIQUE / PRIMARY KEY / FOREIGN KEY / NON NULL on single field
  - UNIQUE / PRIMARY / FOREIGN KEY on group of fields
- All different index types (TODO: what index types are supported) need to be mapped to corresponding INDEXES in Firebird
- All different types need to be checked whether a corresponding type is available
 - Same range / order behavior
 - Incompatibilities need to be documented BEFORE deprecating HSQLDB backend to allow people to prepare for the transition. 
- We need to discuss to what extend manually entered SQL needs to be covered
  - However, views created by the designer must work
  - Best effort approach for manually created views
- As long as the HSQLDB driver is integrated we can easily check if the result of reports (or anything else relevant) is exactly the same compared to the HSQLDB version.
- What to do with HSQLDB specific functions?
 - Firebird 3.0 does not support concat in select
   - Can be rewritten from "concat(A,B)" to "A || B" -> See above. How to handle SQL.

After automated testing of some (conversion works and reports lead results / entries can eb added) manual QA effort is needed. The timeframe to the 6.1 seems to close to tackle everything listed above. Therefore defaulting to enabling this big feature for 6.1 is not recommended. As highlighted above incompatible data types / SQL code has not been discussed as part of the transition to Firebird.

TODO from QA side: Prepare 1-3+ test documents, which should be used as data for automated tests. Ideally this should not only be new documents, but also documents created by older LibreOffice versions
Comment 1 Robert Großkopf 2018-04-19 14:29:46 UTC
It's a little bit difficult: What is the bug? This is a summary about all, what has to be done before internal HSQLDB is set to deprecated. I will set this one to "New".
Comment 2 V Stuart Foote 2018-04-19 14:32:25 UTC
This laundry list looks a dupe to existing META bug 116968 for the migration capability and belongs there.

@Xisco, this fails all our QA steps, please adjudicate.
Comment 3 Robert Großkopf 2018-04-19 14:47:19 UTC
Created attachment 141489 [details]
Standard-database of Base-Handbook. Should be migrated completely.

I have added this database for testing the migration after all the reported bugs for Firebird-migration have been set to "Resolved". The database is the main example of the Base-Handbook. It's the example without macros. Could be macros need other kind of migration by the user ...
Comment 4 Xisco Faulí 2018-04-19 15:12:36 UTC
Hi Florian,
Thanks for reporting the bug, but IMHO, it would be much easier if each of the points in the list were reported in different bugs, remember,  divide and conquer.

On the one hand, we have some of the migration problem already reported in meta bug 116968, On the other hand, the already known firebird problems are reported in the meta bug 51780.

 
> - Converting HSQLDB databases from HSQLDB to Firebird should not be enabled
> by default in 6.1 -> Should be an experimental option.
>   - Expert configuration entry should be added to force / ask / delay upgrade
>    - In "RELEASE CONFIGURATION" builds default should be "delay", in others
> "ask".

It was discussed in last week's ESC meeting to enable it and ask the users whether to migrate or not. I guess the place to discuss such a rollback is the ESC meeting.

> 
> - After converting the ODB file there must not be the HSQLDB data in the
> file anymore.

I guess this can be discuss in bug 117072

>   - Ask to store in new file
> 
> - Automated tests should be created that cover the following aspects. The
> test document should be a HSQLDB one as explicitly the transition to
> Firebird should be tested:
> 
> - Relationships are working between tables:
>  - 1:1
>  - 1:n
>  - n:m
> 
> - Auto increment of elements working (needs to be checked after converting)
> - Other constraints on elements must work as well
>   - UNIQUE / PRIMARY KEY / FOREIGN KEY / NON NULL on single field
>   - UNIQUE / PRIMARY / FOREIGN KEY on group of fields
> - All different index types (TODO: what index types are supported) need to
> be mapped to corresponding INDEXES in Firebird
> - All different types need to be checked whether a corresponding type is
> available
>  - Same range / order behavior
>  - Incompatibilities need to be documented BEFORE deprecating HSQLDB backend
> to allow people to prepare for the transition. 
> - We need to discuss to what extend manually entered SQL needs to be covered
>   - However, views created by the designer must work
>   - Best effort approach for manually created views
> - As long as the HSQLDB driver is integrated we can easily check if the
> result of reports (or anything else relevant) is exactly the same compared
> to the HSQLDB version.
> - What to do with HSQLDB specific functions?
>  - Firebird 3.0 does not support concat in select
>    - Can be rewritten from "concat(A,B)" to "A || B" -> See above. How to
> handle SQL.

There're already some automated tests in https://cgit.freedesktop.org/libreoffice/core/tree/dbaccess/qa/unit
Anyway, more are welcomed, of course.

> 
> After automated testing of some (conversion works and reports lead results /
> entries can eb added) manual QA effort is needed. The timeframe to the 6.1
> seems to close to tackle everything listed above. Therefore defaulting to
> enabling this big feature for 6.1 is not recommended. As highlighted above
> incompatible data types / SQL code has not been discussed as part of the
> transition to Firebird.

 
> TODO from QA side: Prepare 1-3+ test documents, which should be used as data
> for automated tests. Ideally this should not only be new documents, but also
> documents created by older LibreOffice versions

For automated tests, better to create unittests than 1-3+ documents.
For manual tests, we have hundreds of databases in Bugzilla -> https://bugs.documentfoundation.org/buglist.cgi?f1=attachments.mimetype&list_id=795474&o1=casesubstring&query_format=advanced&v1=application%2Fvnd.oasis.opendocument.database

As said before, if there's anything not reported yet, please create one report for each problem.
Closing as RESOLVED WONTFIX
Comment 5 Lionel Elie Mamane 2018-04-19 17:01:24 UTC
(In reply to Xisco Faulí from comment #4)

>> - Converting HSQLDB databases from HSQLDB to Firebird should not be enabled
>>   by default in 6.1 -> Should be an experimental option.
>> - Expert configuration entry should be added to force / ask / delay upgrade
>> - In "RELEASE CONFIGURATION" builds default should be "delay", in others "ask".

> It was discussed in last week's ESC meeting to enable it and ask the users
> whether to migrate or not. I guess the place to discuss such a rollback is
> the ESC meeting.

I don't think the ESC decision was so rigid as to require nagging the user
at each opening of an embedded HSQLDB file! The scheme proposed by Florian
is reasonable. Other reasonable schemes would be:

- Have a "don't nag again for this file" option in the dialog (which sets some
  property/setting/mark in the file)

- Instead of / additionally to initiating the migration through a pop-up, do
  it through anexplicit menu entry? If technically necessary, that menu entry
  could not act on the currently open file, but on another file that on
  chooses from the filepicker.

Depending on how much development/QA work we get squeezed in before 6.1,
we could start in 6.1 with a menu entry the user has to explicitly look for,
and switch to a pop-up when development/QA is more advanced. That is a decision
to be taken near feature freeze for 6.1.
Comment 6 Xisco Faulí 2018-04-20 08:36:14 UTC
> I don't think the ESC decision was so rigid as to require nagging the user
> at each opening of an embedded HSQLDB file! The scheme proposed by Florian
> is reasonable. Other reasonable schemes would be:
> 
> - Have a "don't nag again for this file" option in the dialog (which sets
> some
>   property/setting/mark in the file)

Reported in bug 117120