Using SQLite with Libreoffice Base on Linux

I love SQLIte for its simplicity, portability and stability. Most of my personal projects use SQLite as the database. For most I suggest using DB Browser for SQLite (DB4S) project which has a great UI, is open source and cross-platform. But I still know some who like to use LibreOffice Base. LibreOffice is a powerful, Free and Open Source office suite. Base is part of this suite. Base is a full-featured desktop database front end designed for non technical users. It makes interacting with the database (local or remote) easy. It allows the users to create reports, forms etc.

Base is a full-featured desktop database front end, designed to meet the needs of a broad array of users. Base caters to power users and enterprise requirements, providing native-support drivers for some of the most widely employed multi-user database engines: MySQL/MariaDB, Adabas D, MS Access and PostgreSQL. In addition, the built-in support for JDBC- and ODBC-standard drivers allows you to connect to almost any other existing database engine as well.

So I thought it would be great to have SQLite work with Base. It would have been great if base allowed opening Sqlite file directly. But it doesn't. But base does allow the user to connect to any database using ODBC. In this how-to we will do the same. We will also create a report as a test case.

System

Even though the steps I have here should be some what reproducible on any Linux system, I use Ubuntu on daily basis. Hence the commands to install are mostly for Ubuntu. You might want to run the specific commands for your distro, specially sudo apt-get install part.

Install SQLite3 and Libreoffice Base

Let's start with installing SQLite3. We are choosing the latest SQLite available. We also need to install the SQLite3 development files. This can be done by installing libsqlite3-dev

sudo apt-get install sqlite3 libsqlite3-dev

My Ubuntu LibreOffice installation didn't have base installed by default. So I had to install the Base manually

sudo apt-get install libreoffice-base

Install UnixODBC

Open Database Connectivity (ODBC) is a standard set of APIs for accessing database management systems (DBMS). Idea was to make the Application-DBMS interaction independent of platform or databases. UnixODBC is a standard ODBC implementation for *nix systems like Linux¹.

You can install the unixodbc bin files and def files by running the

sudo apt-get install unixodbc-dev unixodbc-bin unixodbc

Install SQLIte ODBC Driver

ODBC driver is a translation layer between the your application and database. When it receives a specific query from the application it translates into a way that is required by the specific database. It is very specific to database and can vary for platforms. In our case we need to install SQLite ODBC Driver for Linux. Go to the project website and download the tar file. When I was writing this file it was sqliteodbc-0.9996.tar.gz. Extract it and then use make to compile and install ²

./configure && make
make install

Configure ODBC - Driver

Now that we are done with the installations we need to configure the ODBC Driver. Start with editing the file /etc/odbcinst.ini

nano /etc/odbcinst.ini:

and add the following definitions to ini file. It defines the ODBC Drive setup

[SQLite]
Description=SQLite ODBC Driver
Driver=/usr/local/lib/libsqlite3odbc-0.9996.so
Setup=/usr/local/lib/libsqlite3odbc-0.9996.so
Threading=2

Configure databases to be used by ODBC - Data Source Name (DSN)

To start create a dummy database called test.sqlite in your home folder. In my case it will be at /home/thej/test.sqlite. Also create a table called accounts with id and name as columns. We want to connect to this test.sqlite from Base and using the SQLite ODBC Driver To do this we need to define Data Source Name (DSN). We can have as many as we want depending on number of databases we want to connect. Make sure names are unique - thejtest

To define DSN, Edit ~/.odbc.ini (This is specific to your logged in user account) and add the following.

nano ~/.odbc.ini

It basically defines the name of this DSN, a description, Which ODBC driver to use and a database URI (in this case path).

[thejtest]
Description=My SQLite test database
Driver=SQLite
Database=/home/thej/test.sqlite
# optional lock timeout in milliseconds
Timeout=2000

Now we are ready to connect to our database from Base.

Use your SQLite in Libreoffice Base

Setup

Start Libreoffice Base and Select the connection type as ODBC.

Select the connection (DSN) name that you have already setup.

Test the connection. SQLite usually doesn't have any passwords.
Test connection should work if everything is setup properly.

Save this registration.

Now you can save the document for future use. The document will have .odb extension.

Create a Report

Once you open it should show you the list of tables in the database. You can see accounts here.

Go to reports tab at left and then select Wizard to create a report.

Now the base open writer and a wizard showing tables.

Once you select the fields, grouping and sorting, Select the layout.

Give a name to the report and select the option dynamic report. So you will always get the report based on latest data.

Save the odb file at this point so your report settings are saved.

Once its saved, it can be run fun from the home page any time.
You can do a lot of customization like colors, logos etc. I have not put that here.

Remember

• In Base, all editable table needs primary keys.
• The Base documents will have .odb extension.
• You can send/share the .odb documents by email or using any other options. The receiver also needs to have corresponding SQLite database file. He also needs to configure ODBC on his machine
• SQLite database can be updated independent of Base

Footnotes