DBMS migration in LibreOffice: HSQLDB binary import

Last time I wrote a blog post about the challenges of some Firebird bugs in LibreOffice, and the main concept of implementing the schema import of HyperSQL databases inside a Base document.

Since lately, I continued working on the tender published by TDF with Collabora. Next step was to create a way of importing table rows from a HSQL database. Base creates tables as CACHED tables – meaning in HSQL terminology that data is stored on disk, and pulled up to memory in demand – which implies that the data is stored in a binary file near the schema description.

To interpret the binary file, we get some help from the “script” file, which contains metadata for the database. For each table there is a statement which pairs a set of numbers to them (SET TABLE … INDEX … ). These numbers are file pointers to the root element of AVL trees. Each node of the tree stores a row.

In order to read a row from the table, it is necessary to know the ordered set of its column types, because the row does not contain this information. Fortunately, the column types are known from the schema import.

The next step is to read the data of each column in a row. The data is stored continuously in the file and each type has its own format. For example, the VARCHAR type is stored as follows: The first 4 bytes can be interpreted as an (unsigned) number, which tells us the size – number of unicode characters – of the string. The next array of bytes contains the string itself.

Finally, we have to adopt the data with the new DBMS. For that I use the sdbc layer’s PreparedStatement. By calling the appropriate setXXX method, and executing the statement, the data is finally migrated to the new database.

For those, who were enthusiastic enough to read this post so far, this is the related commit on gerrit:
https://gerrit.libreoffice.org/#/c/50536/

The job is not done yet. There are some type interpretations which are not implemented yet (e.g. Date/Time, Numeric/Decimal). Also I intend to write unit tests for the whole library, and I expect some upcoming problems while testing it with different databases.