LibreOffice Base

LibreOffice Base is an open source RDBMS (relational database management system) front-end tool to create and manage various databases.

Preparing the ODBC Connection

First, make sure to prepare MariaDB Connector/ODBC as explained in MariaDB Connector/ODBC.

That includes:

• Download the latest MariaDB Connector/ODBC
• Copy the shared library libmaodbc.so to /usr/lib/[multi-arch]
• Install the unixodbc, unixodbc-dev, openssh-client, odbcinst packages
• Create a template file for the ODBC driver. A sample “MariaDB_odbc_driver_template.ini” could be:

• Install the ODBC driver from the template file by running:

$ sudo odbcinst -i -d -f MariaDB_odbc_driver_template.ini
odbcinst: Driver installed. Usage count increased to 1. 
    Target directory is /etc

Verify successful installation in /etc/odbcinst.ini file (this path is obtained by the config info /-j/ option, where drivers are installed in that predefined location).

$ odbcinst -j
unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/anel/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

$ cat /etc/odbcinst.ini 
[MariaDB ODBC 3.1 Driver]
Description=MariaDB Connector/ODBC v.3.1
Driver=/usr/lib/x86_64-linux-gnu/libmaodbc.so
UsageCount=1

• Create a template file for the Data Source Name (DSN). A sample “MariaDB_odbc_data_source_template.ini” could be:

• Install data source:

odbcinst -i -s -h -f MariaDB_odbc_data_source_template.ini

• Verify successful installation in the /.odbc.ini file

$ cat ~/.odbc.ini 
[MariaDB-server]
Description=MariaDB server
Driver=MariaDB ODBC 3.1 Driver
SERVER=MariaDB
USER=anel
PASSWORD=
DATABASE=test
PORT=3306

• Verify successful installation also using the isql utility, for example:

$ isql MariaDB-server
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> show tables;
+--------------------------------------------------------------------------+
| Tables_in_test                                                           |
+--------------------------------------------------------------------------+
| Authors                                                                  |
| tbl_names                                                                |
| webposts                                                                 |
| webusers                                                                 |
+--------------------------------------------------------------------------+
SQLRowCount returns 4
4 rows fetched

Start with LibreOffice Base

Start Libreoffice Base from the terminal by running lobase (make sure to install the libreoffice-base package if needed). The default option is to create a new database, which is HSQLDB. In order to connect to a running MariaDB server, choose “Connect to an existing database” and choose “ODBC” driver as shown below:

After that, choose DSN (the one that we created in the previous step) and click “Next”:

Provide a user name (and password if needed) and again check the connection (with the “Test Connection” button) and click “Next”:

After that, we have options to register the database. Registration in this sense means that the database is viewable by other LibreOffice modules (like LibreOffice Calc and LibreOffice Writer). So this step is optional. In this example, we will save as “fosdem21_mariadb.odb”. See Using a Registered Database.

It opens the following window:

It consists of three windows/panels:

1. “Database” window with the options
   1. "Tables",
   2. "Queries",
   3. "Forms",
   4. "Reports".
2. "Tasks window (dependent on what is selected in the “Database” window). When “Tables” is selected, the options are:
   1. "Create Table in Design View",
   2. "Use Wizard to Create Table" and
   3. "Create View".
3. "Tables" window - shows list of tables that are created.

As we can see, there are system tables in the “mysql” database as well as “test” database.

Let’s say we create a table using the REST API from JSON data from http://jsonplaceholder.typicode.com/posts, and another table using the same mechanism from http://jsonplaceholder.typicode.com/users, and let’s call them webposts and webusers. In order to do so, we have to enable the CONNECT storage engine plugin and start with REST_API. See more in the CONNECT - Files Retrieved Using Rest Queries article.

The queries we need to run in MariaDB are:

CREATE TABLE webusers ENGINE=CONNECT TABLE_TYPE=JSON
  HTTP='http://jsonplaceholder.typicode.com/users';

CREATE TABLE webposts ENGINE=CONNECT TABLE_TYPE=JSON
  HTTP='http://jsonplaceholder.typicode.com/posts';

The result in LibreOffice Base will be as shown below:

Double clicking on the table opens a new window with the data displayed to inspect:

To create the table from the “Tasks” window, use the option “Create Table in Design View”, where one can specify specific field names and types as shown:

From the “Tasks” window one can create a table using the option “Use Wizard to Create Table” to create some sample tables.

One can fill the data in the existing table, or create and define the new table from the LibreOffice Calc module with simple copy-paste (in the "Tasks" window).

Using a Registered Database

Other modules can use the registered database, for example, open "LibreOffice Calc" and go to "Tools", "Options" and you will see the "odb" file we registered when starting "LibreOffice Base".