Experiences migrating from HSQL to Firebird in Libreoffice 6.2.5

asked 2019-08-27 23:29:31 +0200

fgotsis gravatar image

This migration was done with Libreoffice using OpenSuse Leap 15.0 and 15.1 as well as Windows 10. The existing documentation is quite short and in my opinion incomplete. The documentation I could find was here: https://wiki.documentfoundation.org/D...

The migration was quite frustrating as there were lots of undocumented changes necessary, so I wrote down most of problems I faced including their solutions which I considered as not specific to my solution here. But beware there might be suggesstions that may only have been a partial solution for my situation that do not apply to other problems.

The option to run the old and the new database side by side on the same computer will not work (2 screen solution to compare the bahavior before and after the migration). As Libreoffice detects the other running instance and will use the macro language and libraries of the database that was opened first. So in my case I was running the HSQLDB-Database via libreoffice on one computer and the firebird on another side by side. Another help is to run a standalone firebird server to test the viability of SQL-Queries or SQL-functions to narrow down the source of the problems caused by migrating. If the SQL query will not work with the standalone version there is no point trying to run it in libreoffice.

List of issues I faced

  1. Field names, table names and query name length limitation to a maximum 31 characters This is documented, but not checked during migration. If you miss out a single field, you end up that the table and its data that contains the field that has a name that was longer than 31 characters is missing in the converted database. If you have lots of tables, this is not easy to spot. The migration assistant should have no problem spotting a problem and reporting it to the user.
  2. Changes in Query Functions that can be used The explanation of DATEDIFF is quite short, examples oder links to the firebird documentation are not there. But if you want to use the function in a query you have to open the Query in SQL-Edit mode and switch on a mode to run thos SQL functions with the menuitem "Run SQL command dicretly" in the EDIT menu.
  3. undocumented changes The previously usable function CASE_WHEN had to be replaced with IIF which is not described as a function that needs to change. So look out for functions in your old code that need replacement and do not believe the error messages. If a function, query or form did work in the database before the migration it was always an incompatibility that was to blame for the error message.
  4. Relating to 2 In case you use SQL-Function in queries and you want to reuse the calculated column as a condition (WHERE clause) you must generate a subquery: Non working code in Firebird SELECT "familyname", "forename", "birthday", CURRENT_DATE, DATEDIFF(year, CURRENT_DATE, "birthday") AS ...
edit retag flag offensive close merge delete



It appears you missed this post -> Migrate from HSQLDB

Also, one of the main reasons to move away from HSQLDB is elimination of JAVA. Firebird is C++.

Ratslinger gravatar imageRatslinger ( 2019-08-28 01:40:58 +0200 )edit

Thank you for telling me, I did not know about that link. On Linux and windows 10 there was no link offered for additional information, contrary to the shown dialog box.

The Bug "Migration bug for subforms and parameter queries" relating to subforms did not come up in my migration project. Alas, another important issue came up: Installing the firebird server on the same linux maschine as the one where you want to use libreoffice results in an error that certain files cannot be written to. That problem did go away after uninstalling firebird server package (on OpenSuse).

Regarding the reasons: I read in a few posts about a problem Java would have, but I have not seen any explanation what kind of problem that might be, that is critical to libreoffice.The only arguments I can think of is speed and possible license problems, but I could not measure ...(more)

fgotsis gravatar imagefgotsis ( 2019-08-28 03:01:56 +0200 )edit

Anyway as the decision was made, there is no point to go back on that. The idea is to make the best out of that decision and give a user experience that encourage the usage of libreoffice base. A good starting point would be to improve the migration documentation and the wizard.

fgotsis gravatar imagefgotsis ( 2019-08-28 03:05:11 +0200 )edit

Frankly have not seen many of the problems you seem to experience. Have had LO Firebird embedded & Firebird server on same systems (Mint 18.3 & Ubuntu 18.04 ) with Mint being for over a year. See no problems in speed regarding SQL direct mode. See much more functionality available with SQL. Did spend time with the Firebird manual regarding SQL. There are still issues but can deal with most.

Overall, after some conversion problems, most notably dealing with numeric & decimal data, I don't have many complaints. My main database remains PostgreSQL.

Ratslinger gravatar imageRatslinger ( 2019-08-28 03:50:12 +0200 )edit