HSQLDB to Firebird migration

Hello everyone,

Based on https://bugs.documentfoundation.org/show_bug.cgi?id=116944#c42 a
list of TODO items for the migration:

I was told discussing this bug both on QA and DEV ML is okay.

Some TODO items / things I had in mind :)

- 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 related data
in the file anymore.
- Ask to store in new file -> backup

- Automated tests should be created that cover the following aspects. The
current unit test is just a very basic example. The test document should be
a HSQLDB one as explicitly the transition to Firebird should be tested. I
am never taking about the Firebird database, I am only talking about the
migration.

The following things should work / need to be discussed from my point of
view (sorry if this sounds harsh - didn't meant to):

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

- Auto increment of elements needs to be working (needs to be checked after
migration finished)
- 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 in both
database and can they be mapped?) 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 of the types
- 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 should be covered
(migration is something different than just importing data)
- 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. --> Unit tests
- What to do with HSQLDB specific SQL functions?
-Example for incompatible SQL dialects: Firebird 3.0 does not support
concat in select
- Can be rewritten from "concat(A,B)" to "A || B" -> See above. How to
handle custom SQL.

After automated testing of some migrations (migration itself works and
reports / entries can be added) manual QA effort is needed. The timeframe
to the 6.1 release seems to close to tackle everything listed above.
Therefore defaulting to enabling this big feature for 6.1 is not
recommended from my side. As highlighted above incompatible data types /
SQL code has not been discussed as part of the migration to Firebird. I saw
one example of incompatible types, but no resolution of how to continue.

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 [one test document
can be found here::
https://bugs.documentfoundation.org/attachment.cgi?id=141489 Base manual's
DB]

Yours Florian

PS: please answer to both mailing lists to not split up to conversation.
Thank you!
-- 

Mit freundlichen Grüßen, | Yours,
Florian Reisinger