Final steps of LibreOffice database migration

It is my third post about implementing HSQL migration inside odb documents in connection with a TDF tender. The previous posts were about creating the schema import, and the import of binary data.

Today I will write about the final steps which has to be made in order to
toggle off experimental feature from Firebird and use HSQLDB migration by default. This process also involves the creation of some unit tests, and
modifying some others.

First, I’d like to mention that there were already some unit tests, which tested the HSQLDB driver. These tests can be easily reused, since the test cases use the sdbc interface which makes the underlying driver transparent. It means that these tests – after enabling database migration – will be useful not only for testing the sdbc driver, but also to test the migration.

Besides that, I added two CppUnitTest for testing the migration indirectly. One of them tests the sql parser which should get rid of HSQLDB specific parts of the statement. These test cases get SQL strings as an input, and assures that the output of the parser makes sense in the actual sdbc driver’s context.

There were some special cases to cover up. For example HSQLDB’s VARCHAR doesn’t necessarily need a parameter during the creation of the table. This would cause an error with Firebird, so in that case a default size parameter has to be added.

The other test suite aims the binary import by using an example odb file containing a HSQLDB database. The test asserts that the values in each cell of the example database are the same after the migration.

At this point, I had some trouble with the DATE, TIME and DATETIME values.
HSQLDB interprets an incoming input of one of these types as local time. That means if you insert 06.04.2018 00:00 into the database – either with direct sql or in the graphical table view in LibreOffice – it will check your timezone, and convert the time to GMT (in case of CACHED tables, as it is in case of an embedded HSQLDB document). If I created the database from Budapest with time zone GMT+2, the actual value stored will be the elapsed milliseconds since 1970 until 05.04.2018 22:00. This issue is tracked in Bugzilla here.

There is no problem with that when you read out your date with the same time zone. But If I want to send my odb file to a friend in China, all the values will be changed. An easier way to reproduce this is to run LO as follows:

DATE=$(TZ=Asia/Taipei date +”%F %H:%M”) libreoffice –base

This anomaly comes up when migrating the database too. Firebird (by default) doesn’t care about time zones, it is the user’s responsibility to interpret the values the correct way. It seems to me that this would be the correct behaviour in case of an embedded database, since these files can be moved easily.

In the current state, after migrating the database the time related columns will be slightly different. But this is intentional, since the migrated values are represented in UTC.

Next step was to flip migration to default. After that a bunch of unit tests
failed related to HSQLDB. After adding the firebird_sdbc component, there were still some failing tests, because they contained HSQLDB specific statements. They has to be simply replaced with Firebird specific stuff. For example there is no such thing as “drop table myTable if exists” in Firebird. You can use PSQL to check the existence of myTable instead.

The current state of flipping to default can be found on gerrit:

https://gerrit.libreoffice.org/52008/