Dec 4, 2023

Use SQLite3 with LibreOffice Base in Fedora 38

LibreOffice Base requires the ODBC SQLite3 driver to be installed, but luckily it is in the repositories on Fedora.

This post is so far only helpful in some of the setup for using Base with SQLite. None of the usual options for creating primary keys are showing for the SQLite databases. Yet tables can be saved to the databases. Not sure if this is a problem with the specific driver, or a user malfunction, etc.


Why ODBC?

(Open Data-b-ase Connectivity)

SQLite3 developers might have programmed compliance specifically for the aging MS Access and/or LibreOffice Base applications. However, they chose general compliance with ODBC.

ODBC is a recognized, industry standard catch-all for compatibility between many relational databases. There is no such thing as a native ODBC relational database, as it is a connectivity API.

LibreOffice Base has compatibility with ODBC, as long as the system has the right ODBC drivers.




Install the packages

 dnf install sqliteodbc 

 dnf install sqlite-devel 

 dnf install unixODBC unixODBC-devel


Note: This will facilitate SQLite3 capabilities in the Base application.




Confirm the system ini file

Note: In Fedora 38, the ODBC driver configuration file should be ready to go after installing the above packages.

In a terminal, you can type:

 cat /etc/odbcinst.ini 

You should get the following:

-------------------------------------------------------
[MySQL]
Description=ODBC for MySQL 8
Driver=/usr/lib/unixODBC/libmyodbc8.so
Driver64=/usr/lib64/unixODBC/libmyodbc8.so
FileUsage=1

[MySQL-5]
Description=ODBC for MySQL 5
Driver=/usr/lib/libmyodbc5.so
Driver64=/usr/lib64/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1

[FreeTDS]
Description=Free Sybase & MS SQL Driver
Driver=/usr/lib/libtdsodbc.so
Setup=/usr/lib/libtdsS.so
Driver64=/usr/lib64/libtdsodbc.so
Setup64=/usr/lib64/libtdsS.so
Port=1433

[MariaDB]
Description=ODBC for MariaDB
Driver=/usr/lib/libmaodbc.so
Driver64=/usr/lib64/libmaodbc.so
FileUsage=1

[SQLITE]
Description=SQLite ODBC 2.X
Driver=/usr/lib64/libsqliteodbc.so
Setup=/usr/lib64/libsqliteodbc.so
Threading=2
FileUsage=1
UsageCount=1

[SQLITE3]
Description=SQLite ODBC 3.X
Driver=/usr/lib64/libsqlite3odbc.so
Setup=/usr/lib64/libsqlite3odbc.so
Threading=2
FileUsage=1
UsageCount=1

-------------------------------------------------------

Note: The spelling of the SQLite3 entry: "SQLITE3". This will be needed in the next steps.



Database Preparation


Unfortunately, it is required to enter every SQLite database file's name and location in a special .ini file in the home directory before Base will be able to acknowledge those files' existence.

Filename: /home/<username>/.odbc.ini

Note: The file is probably empty. 

Note: Like the other .ini file, Fedora 38 should have also created this .ini file, because you already installed the ODBC packages from the repos.



For a new database:

You have to:

  1. Create a blank database file first
  2. Add a section to the .odbc.ini file containing:
    1. A custom tag/title in brackets
    2. The SQLITE3 driver designation from the /etc/odbcinst.ini file
    3. The sqlite3 file's name and location (as per the example below)




Example entry for the file: /home/<username>/.odbc.ini

-------------------------------------------------------
[customtitle]
Description=Something descriptive about the database
Driver=SQLITE3
Database=/home/<username>/Downloads/exampledb.sqlite

[customtitle2]
Description=Something descriptive about the database
Driver=SQLITE3
Database=/home/<username>/Documents/exampledb2.sqlite 
-------------------------------------------------------

 


For a pre-existing SQLite database file:

You repeat step 2 as above, adding an entry in the .odbc.ini file.





Steps in Base:


1. Open Base after performing the above steps

2. Select the radial button: "Connect to an existing database"

3. Select the option: "ODBC"



4. Click the button: "Next"

5. Click the button: "Browse"



6. Select the option for your custom database entry, and then click the button: "OK"



7. Click the button: "Next"

8. Base will prompt you to name and save yet another file. This file contains any of the LibreOffice Base forms and views, etc. that correspond to the database. However this file does not contain the actual database itself.




Still Doesn't Work !!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  • Does not offer options to create primary keys, like in native Base db format.
  • This is probably some kind of user malfunction, or LibreOffice does not like SQLite
  • Either way, I have not been able to get the right-click menu on a table's new fields in order to get to create a primary key (or anything else in the menu). And thus I have not been able to fill any new tables with data either. (Just a mild hiccup for using a database.)
  • However, the tables themselves do save into the database when created in Base. And they show up in the sqlite3 cli program afterwards.

Jun 13, 2023

Stuff About Open Source Fonts and CSS/Base64

This is tentative information that I found useful for my preferences and use cases. It is not about advocating general practices, but it might be helpful to some people.

Nov 28, 2022

Manually Install Godot 4 on Fedora 37

This post is about installing/compiling the Godot Game Engine 4.x versions on Fedora. 

I maintain this post despite Godot having updated their instructions to apply to 4.x versions. You might prefer those instructions here.

Nov 14, 2022

Multimedia and Flatpaks in Fedora 37

Make Fedora have the cool stuff.
 

Dec 25, 2021

Tiddly Desktop on Fedora 35, 36

Install the official TiddlyWiki application on Fedora.
  • View wikis separately from the browser
  • Create wikis from personal templates
  • Wikis open in individual windows
  • External web links work to any browser

Aug 30, 2021

Fedora 35-37 Vulkan driver for some AMD GPUs

For AMD graphics cards between around 2012 and 2015, Fedora does not always correctly implement the amdgpu driver with Vulkan support for these cards.
 

Apr 16, 2021

Ubuntu i3 XFCE Final Hurrah

A way to get up and running with an i3 desktop with descent usability and visual appeal. Some people still prefer the other methods on the blog, which you might want to take a look at.

Mar 11, 2021

Lenovo Thinkstation C30 Linux EFI

The key to setting up the TS C30 for Linux is in changing the default efi entry to have the name "Windows Boot Manager".

This is done through the Linux program efibootmgr.

Dec 11, 2020

Ubuntu 20.10 i3 Workspaces Plugin Installation From Github

This guide is closer to a set of commands to build an installation script for i3ipc-glib and xfce4-i3-workspaces-plugin. It relates to this post here.

Nov 30, 2020

Python Install of Mnemosyne on Fedora 36/37


Menmosyne is an open source flashcard management application available on Linux. It is not included in the repos, probably because it asks for some form of data collection.
 

Nov 7, 2020

DeepSpeech and Python on Fedora 33

This is about performing an interesting demo of open source speech recognition on the command line. Deepspeech was being actively developed by Mozilla for a few years.

Nov 5, 2020

Python Environments on Fedora 33

How do you use an earlier version of Python for just one project?

Virtual Environments

Dec 3, 2019

Edit Connections in Fedora

Some network settings once found in the standard network connection editor are not present in the Gnome settings interface.

Sep 23, 2019

Tiddlywiki Sortable Static Tables

This is a cool hack for TiddlyWiki that allows all tables to be sorted with a browser page reload. However it does not lend any data capabilities to the tables.

Aug 19, 2016

Walkthrough for Lubuntu with i3 Tiling

The idea of this setup is to achieve the best of both worlds from convenient desktop features and essential i3 features.  This post uses LXQt, but this blog includes posts about XFCE and Sway instead of i3.

Nov 21, 2015

Xubuntu with i3 Tiling Windows Walkthrough

The idea of this setup is to achieve the best of both worlds from convenient desktop features and essential i3 features. See the Lubuntu version of this here.