Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Experiences migrating from HSQL to Firebird in Libreoffice 6.2.5

This migration was done with Libreoffice 6.2.5.2 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/Documentation/FirebirdMigration

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 "AgeInYears" FROM MyTable WHERE "AgeInYears" > 50 will not work without giving a meaningful error message (although it worked perfectly fine in HSQLDB) Working code in Firebird SELECT * (From SELECT "familyname", "forename", "birthday", CURRENT_DATE, DATEDIFF(year, CURRENT_DATE, "birthday") AS "AgeInYears" FROM MyTable) WHERE "AgeInYears" > 50 That does work in firebird 3.x
  5. Forms - Filter will not work with predefined queries With HSQLDB there was an easy way to select specific records of tables by applying filters. Example (form definition): content-type: Query Analyse SQL command: yes filter: WHERE (member_number = "1001") With firebird that works only if you do switch on "Analyse SQL command" in the forms setting, which at the same time causes some of the SQL-Functions from firebird to stop working. The only way I know to manage both running internal SQL-Functions of firebird and using filters in forms, is to access the data by SQL-Statements directly, by specifying as "Content Type" "SQL command".
  6. Weird Problems Running SQL-Commands directly via the "Tools" menu of libreoffice leads to very ugly response times (2-5 minutes) but again only if the firebird database is used. The same query in the SQL-edit dialog by adding a query from the GUI executes swiftly and without a problem.

    Another funny problem is that the migration assistant cannot be started from the menu, you have one chance after starting and that is it. It would also be nice to mention the database release (version) and which kind of internal database is used somewhere in the menus or otherwise easily accessible.

In the end after 2 days of work I managed to migrate. I have yet to see any advantage of the new database, but that might change when I am getting a bit more familiar with firebird. At the moment the most annoying problem is the requirement to use short names which makes it hard to use self explaining field names. I cannot confirm that there is any gain in speed or usability, but it does run and I hope it is going to be as stable as HSQL.

I also took a look at the initial discussion why there was a change to firebird (starting here https://ask.libreoffice.org/en/question/56766/base-hsqldb-to-firebird-migration-for-existing-databases/). There is also a nice comparison between HSQL and Firebird (https://db-engines.com/en/system/Firebird%3BHyperSQL).

To sum it up: I have not found an official reasoning why the change was necessary and lot of speculation about possible reasons. As it is done already and so much work was invested to manage the transition I would as well argue against changing back.

I would still improve the migration assistant before phasing out the HSQL-support as well as improve the migration documentation at https://wiki.documentfoundation.org/Documentation/FirebirdMigration which is too short and incomplete (in my opinion).

kind regards Franz
from Munich, Germany