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.
Table of Contents
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
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 Linux1.
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 2
./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
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 -
To define DSN, Edit ~/.odbc.ini (This is specific to your logged in user account) and add the following.
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
Now you can save the document for future use. The document will have .odb extension.
Create a Report
Save the odb file at this point so your report settings are saved.
- 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