LibreOffice Base

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

Prepare ODBC connection

Before going further make sure to prepare an ODBC connector as explained on MariaDB ODBC connector article. That includes:

• Download MariaDB ODBC connector (latest version 3.1.7-ga)
• Copy shared library libmaodbc.so to /usr/lib/[multi-arch]
• Install unixodbc, unixodbc-dev, openssh-client, odbcinst packages
• Create template file for ODBC driver, example for template file “MariaDB_odbc_driver_template.ini” could be:

• Install ODBC driver from template file running:

$ odbcinst -i -d -f MariaDB_odbc_driver_template.ini

Verify successful installation in /etc/odbcinst.ini file.

• Create template file for Data Source Name (DSN), example for template file “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 /.odbc.ini file and also using 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 terminal by running lobase (make sure to install libreoffice-base package if needed). Default option is to create a new database, which is HSQLDB. In order to connect to running MariaDB server, choose “Connect to an existing database” and choose “ODBC” driver as shown in following picture:

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

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

After that we have options to register database. Registry in this sense means the database is viewable by other LibreOffice modules (like LibreOffice Calc, LibreOffice Writer). So this step is optional. In this example will be saved as “fosdem21_mariadb.odb”. See How to use registered database.

It opens the following window:

It consists of three windows/panels:

1. “Database” window with options
   1. "Tables",
   2. "Queries",
   3. "Forms",
   4. "Reports".
2. "Tasks window (it depends what is selected in “Database” window). In case when “Tables” are selected 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 “mysql” database as well as “test” database.

Let’s say we create table using 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 CONNECT storage engine plugin and start with REST_API, see more in this article. The queries we need to run in MariaDB are:

MariaDB [test]> CREATE TABLE webusers ENGINE=CONNECT TABLE_TYPE=JSON HTTP='http://jsonplaceholder.typicode.com/users';

MariaDB [test]> CREATE TABLE webposts ENGINE=CONNECT TABLE_TYPE=JSON HTTP='http://jsonplaceholder.typicode.com/posts';

If checked the result in LibreOffice Base it will be as shown below:

Double click on the table opens the new window with the data stored to inspect:

If one want to create the table, from the “Tasks” window it can be done using the option “Create Table in Design View”, where one can specify specific field names and types as showed:

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

Attachment 'librebase_9' not found

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

How to use registered database

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

Attachment 'librebase_register_1' not found