SQLiteOOo

The embedded SQLite driver for LibreOffice

Python

Documentation

Ce document en français.

The use of this software subjects you to our Terms Of Use.

version 1.2.3

Introduction:

SQLiteOOo is part of a Suite of LibreOffice ~~and/or OpenOffice~~ extensions allowing to offer you innovative services in these office suites.

This extension allows you to use SQLite JDBC database in embedded mode, making the database portable (a single odb file).
It allows you to take advantage of the ACID properties of the underlying SQLite database.

Being free software I encourage you:

To duplicate its source code.
To make changes, corrections, improvements.
To open issue if needed.

In short, to participate in the development of this extension.
Because it is together that we can make Free Software smarter.

Requirement:

Due to issue #156471 and following PR#154989, the SQLiteOOo extension requires LibreOffice version 24.2.x minimum to work.

The SQLiteOOo extension uses the jdbcDriverOOo extension to work.
It must therefore meet the requirement of the jdbcDriverOOo extension.

If you are using LibreOffice on Linux and LibreOffice was installed with the package manager, Your Python packages may be system-provided and outdated. The extension’s logging will allow you to check if this is the case. It is accessible via the menu: Tools -> Options -> LibreOffice Base -> Embedded SQLite driver -> View log -> System Info and requires restarting LibreOffice after activation.
If outdated packages appear, you can update them with the command:
pip install --upgrade <package-name>

For more information see: What has been done for version 1.1.0.

Installation:

It seems important that the file was not renamed when it was downloaded.
If necessary, rename it before installing it.

Install jdbcDriverOOo.oxt extension

This extension is necessary to use SQLite version 3.42.0.0 with all its features.
Install SQLiteOOo.oxt extension

Restart LibreOffice after installation.
Be careful, restarting LibreOffice may not be enough.

On Windows to ensure that LibreOffice restarts correctly, use Windows Task Manager to verify that no LibreOffice services are visible after LibreOffice shuts down (and kill it if so).
Under Linux or macOS you can also ensure that LibreOffice restarts correctly, by launching it from a terminal with the command soffice and using the key combination Ctrl + C if after stopping LibreOffice, the terminal is not active (no command prompt).

After restarting LibreOffice, you can ensure that the extension and its driver are correctly installed by checking that the io.github.prrvchr.SQLiteOOo.Driver driver is listed in the Connection Pool, accessible via the menu: Tools -> Options -> LibreOffice Base -> Connections. It is not necessary to enable the connection pool.

If the driver is not listed, the reason for the driver failure can be found in the extension’s logging. This log is accessible via the menu: Tools -> Options -> LibreOffice Base -> Embedded SQLite Driver -> Logging Options.
The SQLiteLogger logging must first be enabled and then LibreOffice restarted to get the error message in the log.

Remember to first update the version of the Java JRE or JDK installed on your computer, this extension need the new version of jdbcDriverOOo that requires Java version 17 or later instead of Java 11 previously.

Use:

How to create a new database:

In LibreOffice / OpenOffice go to File -> New -> Database…:

SQLiteOOo screenshot 1

In step: Select database:

select: Create a new database
in: Emdedded database: choose: Embedded SQLite Driver
click on button: Next

SQLiteOOo screenshot 2

In step: Save and proceed:

adjust the parameters according to your needs…
click on button: Finish

SQLiteOOo screenshot 3

Have fun…

How does it work:

SQLiteOOo is an com.sun.star.sdbc.Driver UNO service written in Python.
It is an overlay to the jdbcDriverOOo extension allowing to store the SQLite database in an odb file (which is, in fact, a compressed file).

Its operation is quite basic, namely:

When requesting a connection, several things are done:
- If it does not already exist, a subdirectory with name: . + odb_file_name + .lck is created in the location of the odb file where all SQLite files are extracted from the database directory of the odb file (unzip).
- The jdbcDriverOOo extension is used to get the com.sun.star.sdbc.XConnection interface from the subdirectory path + /sqlite.
- If the connection is successful, a DocumentHandler is added as an com.sun.star.util.XCloseListener and com.sun.star.document.XStorageChangeListener to the odb file.
- If the connection is unsuccessful and the files was extracted in phase 1, the subdirectory will be deleted.
When closing or renaming (Save As) the odb file, if the connection was successful, the DocumentHandler copies all files present in the subdirectory into the (new) database directory of the odb file (zip), then delete the subdirectory.

The main purpose of this mode of operation is to take advantage of the ACID characteristics of the underlying database in the event of an abnormal closure of LibreOffice.
On the other hand, the function: file -> Save has no effect on the underlying database. Only closing the odb file or saving it under a different name (File -> Save As) will save the database in the odb file.

How to build the extension:

Normally, the extension is created with Eclipse for Java and LOEclipse. To work around Eclipse, I modified LOEclipse to allow the extension to be created with Apache Ant.
To create the HyperSQLOOo extension with the help of Apache Ant, you need to:

Install the Java SDK version 8 or higher.
Install Apache Ant version 1.9.1 or higher.
Install LibreOffice and its SDK version 7.x or higher.
Clone the SQLiteOOo repository on GitHub into a folder.
From this folder, move to the directory: source/SQLiteOOo/
In this directory, edit the file: build.properties so that the office.install.dir and sdk.dir properties point to the folders where LibreOffice and its SDK were installed, respectively.
Start the archive creation process using the command: ant
You will find the generated archive in the subfolder: dist/

Has been tested with:

LibreOffice 24.2.1.2 - Lubuntu 22.04
LibreOffice 24.8.0.3 (x86_64) - Windows 10(x64) - Python version 3.9.19 (under Lubuntu 22.04 / VirtualBox 6.1.38)

I encourage you in case of problem 😕
to create an issue
I will try to solve it 😄

Historical:

What has been done for version 1.0.0:

Integration of SQLite JDBC version 3.42.0.0. I especially want to thank gotson for the many improvements to the SQLite JDBC driver that made it possible to use SQLite in LibreOffice/OpenOffice.

What has been done for version 1.0.1:

Fixed bug 156511 occurring when using the com.sun.star.embed.XStorage interface. The workaround is to use the copyElementTo() method instead of moveElementTo(). Versions of LibreOffice 7.6.x and higher become usable.

What has been done for version 1.0.2:

The absence or obsolescence of jdbcDriverOOo extension necessary for the proper functioning of SQLiteOOo now displays an error message.
Many other things…

What has been done for version 1.1.0:

All Python packages necessary for the extension are now recorded in a requirements.txt file following PEP 508.
Now if you are not on Windows then the Python packages necessary for the extension can be easily installed with the command:
pip install requirements.txt
Modification of the Requirement section.

What has been done for version 1.1.1:

Support for new features in jdbcDriverOOo 1.1.2.

What has been done for version 1.1.2:

SQLite driver updated to latest version SQLite-jdbc-3.45.1.3-SNAPSHOT.jar. This new driver has been implemented to support part of the JDBC 4.1 specifications and more particularly the java.sql.Statement.getGeneratedKeys() interface and allows the use of the com.sun.star.sdbc.XGeneratedResultSet interface.

What has been done for version 1.1.3:

Support for the latest version of jdbcDriverOOo 1.1.4 and the SQLite-jdbc-3.45.1.6-SNAPSHOT.jar.
Now for proper functioning in Base under: Edit -> Database -> Advanced Settings… -> Query of generated values must be left blank. If you want to use an odb file created with a previous version of SQLiteOOo you must change this setting manually.
Normally the next versions of SQLiteOOo should be able to be updated in the list of extensions installed under LibreOffice: Tools -> Extension manager… -> Check for Updates.

What has been done for version 1.1.4:

Support for the latest version of jdbcDriverOOo 1.3.1.
When saving under a different name, the database if open will be closed correctly.

What has been done for version 1.1.5:

Use of the new data format implemented in version 1.1.4. As a result, if you need to open odb files created with a version lower than 1.1.4 you must first open them with version 1.1.4, otherwise an error will be thrown.

What has been done for version 1.2.0:

This version is based on fix #154989 available since LibreOffice 24.2.x. It can therefore work with other extensions offering integrated database services.
Now SQLiteOOo requires LibreOffice 24.2.x minimum and will load for the url: sdbc:embedded:sqlite.

What has been done for version 1.2.1:

Updated the Python packaging package to version 24.1.
Updated the Python setuptools package to version 72.1.0.
The extension will ask you to install the jdbcDriverOOo extension in versions 1.4.2 minimum.

What has been done for version 1.2.2:

Fixed issue #2 which appears to be a regression related to the release of JaybirdOOo. Thanks to madalienist for reporting it.
Updated the Python setuptools package to version 73.0.1.
The extension options are now accessible via: Tools -> Options -> LibreOffice Base -> Embedded SQLite Driver
Logging accessible in extension options now displays correctly on Windows.
Changes to extension options that require a restart of LibreOffice will result in a message being displayed.
In the extension options it is possible to define the options: View system tables, Use bookmarks and Force SQL mode which will be specific to this driver.
Requires the latest version of jdbcDriverOOo 1.4.4.
Support for LibreOffice version 24.8.x.

What has been done for version 1.2.3:

The extension will ask you to install jdbcDriverOOo extension in versions 1.4.6 minimum.
Modification of the extension options accessible via: Tools -> Options -> LibreOffice Base -> Embedded SQLite Driver in order to comply with the new graphic charter.

What has been done for version 1.3.0:

Passive registration deployment that allows for much faster installation of extensions and differentiation of registered UNO services from those provided by a Java or Python implementation. This passive registration is provided by the LOEclipse extension via PR#152 and PR#157.
It is now possible to build the oxt file of the HyperSQLOOo extension only with the help of Apache Ant and a copy of the GitHub repository. The How to build the extension section has been added to the documentation.
Any errors occurring while loading the driver will be logged in the extension’s log if logging has been previously enabled. This makes it easier to identify installation problems on Windows.
Requires the jdbcDriverOOo extension at least version 1.5.0.

What remains to be done for version 1.3.0:

Add new language for internationalization…
Anything welcome…