DBMS migration in LibreOffice: Firebird and HSQLDB schema import

TDF has published tenders to improve LibreOffice in some areas, including its database management program, Base. The goal here is to implement the import of HSQLDB databases without Java Runtime Environment, in order to facilitate the change-over to
Firebird. In this post I would like to explain what steps are made at Collabora so far to achieve this goal.

Before dealing with the compatibility of the old HSQLDB-based odf files, an improvement had to be made on the Firebird driver. Within this context I implemented those types which could be created with the HSQLDB driver, but not with Firebird. Those Hyper SQL types are:

  • LONGVARCHAR, implemented as a BLOB holding text in Firebird context;
  • BINARY (fix) and VARBINARY. These types can be created as a CHAR/VARCHAR
    field with a special character set called OCTETS. This character set indicates
    that it stores binary format, and it is needed to indicate that no character
    set or collation makes sense here.
  • LONGVARBINARY. This type is responsible for holding images. As images can be
    really big, I implemented this as a subtype of BLOB.

Besides that, to prevent the changing of the DBMS back end being a setback, there were some bugs with high priority to fix. For example the impossibility to copy integer values from Calc to Base (tdf#70425) is fixed by allowing the driver to set numerical values even with the setString method, when it is called on a column with numerical type (int, smallint, etc.).

After that, in order to prepare the removal of the legacy java-based DBMS, it is needed to create a way to import the database schema and the binary data stored in older odf files without Java. Technically it means a new library in module “dbaccess”, which does all the import, and which is used in the dba (core) library when creating connection.

Splitting it to subtasks, I decided to implement the schema import first. The database schema is zipped into the odf file in a text file called “script”. This file consists of DDL statements in a strict format. The new library reads these SQL statements line-by-line, transforms it a little, and then executes them using the sdbc driver. A modification is needed in statements which contain column definitions (since the types typically differ in different systems), and there are some HSQLDB specific keywords too (e.g. CACHED tables).

The migration can be enabled using DBACCESS_HSQL_MIGRATION=1 when starting
LibreOffice. Currently, as the binary import is not ready yet, the result of the migration is some empty tables mostly.

Next step will be to implement the binary import and create unit tests for all.
the changes I have made.

4 thoughts on “DBMS migration in LibreOffice: Firebird and HSQLDB schema import”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: