LibreOffice Base is an open source RDBMS (relational database management system) front-end tool to create and manage various databases.
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:
[MariaDB ODBC 3.1 Driver] |
Description = MariaDB Connector/ODBC v.3.1 |
Driver = /usr/lib/x86_64-linux-gnu/libmaodbc.so |
- Install the ODBC driver from the template file by running:
$ odbcinst -i -d -f MariaDB_odbc_driver_template.ini
Verify successful installation in /etc/odbcinst.ini file.
- Create a template file for the Data Source Name (DSN). A sample “MariaDB_odbc_data_source_template.ini” could be:
[MariaDB-server] |
Description=MariaDB server |
Driver=MariaDB ODBC 3.1 Driver |
SERVER=MariaDB |
USER=anel |
PASSWORD= |
DATABASE=test |
PORT=3306 |
- Install data source:
odbcinst -i -s -h -f MariaDB_odbc_data_source_template.ini
Verify successful installation in the /.odbc.ini file and 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 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
- "Tables",
- "Queries",
- "Forms",
- "Reports".
- "Create Table in Design View",
- "Use Wizard to Create Table" and
- "Create View".
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:
Attachment 'librebase_9' not found
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).
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".
Attachment 'librebase_register_1' not found