The new MySQL driver in LibreOffice

Base – the database editing program of LibreOffice – offers several features when it comes to external database connection. One interesting feature is that Base lets you connect to an external database not only with ODBC and JDBC but with native connectors too. Using the native connector instead of a standard like JDBC sometimes has a positive impact on performance.

Considering that MySQL is one of the most used database management systems worldwide it is clear that the support of connecting to a MySQL database is an unavoidable part of Base. Currently there are three ways supported to connect to a MySQL database: with ODBC, JDBC and using a native connector. However, the last option was only available as an extension, and it is the part of the core project only since November 2018.

Why was it only an extension at the first place? The native connection was implemented by using the C++ connector of MySQL, which is licensed under GPL. Because of that it could not be put into the core project. So what is the solution then?

Because of the above mentioned problem we, at Collabora decided to use the connector of MariaDB instead. MariaDB is a fork of MySQL which has the same database structure and indexes as MySQL. It has a C API which can be used to connect to MySQL and MariaDB databases too. What is more, the connector is LGPL licensed, which means that it can be used in the core project too. So the next task was to use this API to implement the sdbc driver.

Some parts of the implementation is pretty straightforward. It is pretty easy to create a connection and pass options through the function “mysql_options”. It was also clear to me how to pass things like user and password to the DBMS.

There were a few things though which were a bit more challenging. The XDatabaseMetadata interface for example – which is responsible for providing information about the current state and capabilities of the database – could not be implemented with only a few function calls. In order to implement them, I had to construct SQL queries for most of the methods. Database information are available in the schema called “INFORAMTION_SCHEMA”. That task took some time since the interface has more than a hundred methods which had to be implemented.

Another challenge was a bug about parallel execution of result sets. The C API does not support the usage of two result sets simultaneously, but the sdbc standard supports it. The solution was to store the result of a query and free the mysql resources afterwards. That way a new result set can be fetched with the C API while the previously fetched data is stored in memory.

Having the native MySQL connector in the core project has several advantages. First, it is easier for the user to install it. It is bundled with the core project, there is nothing to do there. Also, it improves maintainability, because it does not have to be maintained separately as an extension. For example the automatically triggered clang plugins do not run on extensions. It can easily happen With an extension that after a few releases it is not usable anymore, because of the lack of maintenance.

Besides that, in order to improve quality and maintainability I created a test suite to test the implementation of the sdbc driver of a MySQL database. This test suite does not run automatically with each build though. In order to test it, a running MySQL or MariaDB server is needed, so only manual execution of the test suite is supported. The test suite can be triggered with the same method as the other tests, but you have to declare an environment variable as well (CONNECTIVITY_TEST_MYSQL_DRIVER). The variable should store the URL of an available MySQL or MariaDB server. It should contain a user, a password, and a schema, which can be used to test the functionality of the driver.

Although a lot of things work now, there might be some bugs hidden. Feel free to test the driver if you like, file a bug and please CC me in Bugzilla if you found something interesting.

You can also take a look at my talk on FOSDEM about the MySQL connector and other improvements of Base.

Advertisements

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 )

Google photo

You are commenting using your Google 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