SQLite in LibreOffice / OpenOffice Base

Discussions about using 3rd party extension with OpenOffice.org
Post Reply
psilocybe
Posts: 96
Joined: Thu Jun 15, 2017 5:33 am

SQLite in LibreOffice / OpenOffice Base

Post by psilocybe »

Hi all,

It is now possible to use SQLite in Base.
Autoincrements and view edits are supported.

You need to install the two extensions: enjoy...
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
Mountaineer
Posts: 303
Joined: Sun Sep 06, 2020 8:27 am

Re: SQLite in LibreOffice / OpenOffice Base

Post by Mountaineer »

Thank you.

While I'm not missing much using SQLite via ODBC, it is nice to have a possibility to use newer SQLite 3.42 without needing a compiler...
OpenOffice 3.1 on Windows Vista
psilocybe
Posts: 96
Joined: Thu Jun 15, 2017 5:33 am

Re: SQLite in LibreOffice / OpenOffice Base

Post by psilocybe »

Mountaineer wrote: Wed Jul 26, 2023 7:24 am it is nice to have a possibility to use newer SQLite 3.42 without needing a compiler...
The only requirement is to install Java, LibreOffice or OpenOffice and the extension, whatever the platform...

It would be interesting to make benchmark: SQLite ODBC vs SQLite JDBC
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
User avatar
Villeroy
Volunteer
Posts: 31259
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQLite in LibreOffice / OpenOffice Base

Post by Villeroy »

None of your drivers has ever worked for me on Ubuntu Linux with LO7.5 (community version)
File>New>Database
Connect to existing
Type: SQLite
Connection string: sqlite:/home/villeroy/Documents/LibreOffice/sqlite/chinook.db

Save database
Click "Tables" -> nothing happens
Same connection string works with http://www.java2s.com/Code/Jar/s/Downlo ... 372jar.htm
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31259
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQLite in LibreOffice / OpenOffice Base

Post by Villeroy »

And this is what goes wrong:
The document's DataSource.URL is xdbc:sqlite:sqlite:/home/villeroy/Dokuments/LibreOffice/sqlite/chinook.db
The connection works after removing the superfluous sqlite:
So all you need to specify is the path to your SQLite database file. The extension adds prefix xdbc:sqlite: automatically.
This needs to be documented for each and every type of URL. I can not test any Windows boxes right now.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
psilocybe
Posts: 96
Joined: Thu Jun 15, 2017 5:33 am

Re: SQLite in LibreOffice / OpenOffice Base

Post by psilocybe »

The url can be:
  • /home/user/sqlite.db
  • file:///home/user/sqlite.db
It's the responsibility of the new database Wizard to add subprotocol (ie: xdbc:sqlite:)

the user directory must exist.
if sqlite.db doesn't exist it will be created.
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
psilocybe
Posts: 96
Joined: Thu Jun 15, 2017 5:33 am

Re: SQLite in LibreOffice

Post by psilocybe »

Hi all,

Just to let you know that the latest version of SQLiteOOo is available.

This new version allows:

- To create composite primary keys (on several columns) however you should not declare the column as autoincrement (in any case SQLite manages primary keys as autoincrement).
- To create an SQLite PRIMARY KEY AUTOINCREMENT key which is an improved autoincrement (not really necessary since under SQLite the primary keys are already autoincrementable).
- Edit views in SQL mode.

All this in a single odb file which supports LibreOffice abnormal shutdowns.
Also, I haven't seen a database this fast...
so enjoy
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
Mountaineer
Posts: 303
Joined: Sun Sep 06, 2020 8:27 am

Re: SQLite in LibreOffice

Post by Mountaineer »

psilocybe wrote: Fri Feb 09, 2024 7:09 am ... create composite primary keys (on several columns) ...
One warning (not with your driver, but from my tests with Sqlite3/ODBC): In Base my tables with composite keys were read-only. However I could write to the same database/table via sqlite-Studio, so I assumed this was no restriction of Sqlite but of Base (or maybe the connection via ODBC-driver).

As I avoided this design afterwards by adding an additional autoincrement-id, I can't tell more.
OpenOffice 3.1 on Windows Vista
psilocybe
Posts: 96
Joined: Thu Jun 15, 2017 5:33 am

Re: SQLite in LibreOffice / OpenOffice Base

Post by psilocybe »

Hi Mountaineer ,

According to my tests even with a composite key the table remains editable.
And if the compound key includes an integer auto-increment column it is possible or not to fill this column during entry.
I had to port the SQLite JDBC driver to JDBC version 4.1 to make this possible...
But I think it was worth it. :D
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
User avatar
Villeroy
Volunteer
Posts: 31259
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQLite in LibreOffice / OpenOffice Base

Post by Villeroy »

Great job. This driver allows me to access the contacts database of the Thunderbird mail client without too many issues. Can you tell me if it is possible to establish a read-only connection? If I load the database document after the Thunderbird client, the database is locked (does not show any tables). I think, an explicit read-only connection would help.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
psilocybe
Posts: 96
Joined: Thu Jun 15, 2017 5:33 am

Re: SQLite in LibreOffice / OpenOffice Base

Post by psilocybe »

Hi Villeroy,
This driver allows me to access the contacts database of the Thunderbird mail client without too many issues
I'm glad this can help. :D
I suppose you open the SQLite file corresponding to Thunderbird's address book?
If this is the case, the jdbcDriverOOo / SQLiteOOo extensions can't do much about it, I guess SQLite files are not shareable.

Maybe you can use the BACKUP SQLite commands to create a copy of the SQLite file or try to use ?mode=ro&nolock=1 connection options?

I confirm that these two extensions require JRE (Java) version 11 minimum...
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
psilocybe
Posts: 96
Joined: Thu Jun 15, 2017 5:33 am

Re: SQLite in LibreOffice / OpenOffice Base

Post by psilocybe »

@Villeroy it seems possible to share the abook.sqlite file (Thunderbird address book):
  • You have to open it with the jdbcDriverOOo extension by: File -> New -> Database -> Connect to an existing database -> SQLite Driver. The url must be of the form: file:///path_to_address_book/abook.sqlite
  • In Base go to: Tools -> SQL and execute the command:: PRAGMA journal_mode=WAL;
  • Close then reopen Base (the abook.sqlite file)
Now if we open the abook.sqlite file with Base first, it is possible to edit the address book in Thunderbird...

More information: Write-Ahead Logging
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
User avatar
Villeroy
Volunteer
Posts: 31259
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQLite in LibreOffice / OpenOffice Base

Post by Villeroy »

Somehow, the Thunderbird application grabs all access rights, no matter if it is started before or after LO Base. The Base frontend gets into some "frozen" state when TB is running. My problem is, that I can not open the database in read-only mode, so it does not interfere with TB in any way.
PRAGMA journal_mode=WAL; makes no difference, as far as I can see.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
psilocybe
Posts: 96
Joined: Thu Jun 15, 2017 5:33 am

Re: SQLite in LibreOffice / OpenOffice Base

Post by psilocybe »

Hi Villeroy,
no matter if it is started before or after LO Base.
Curious because for me it is imperative that I open the connection in Base first otherwise I cannot access the tables in Base.
PRAGMA journal_mode=WAL; makes no difference, as far as I can see
Yeah, I'm not sure it's of any use...

I think you are interested in the properties table which for me is read-only. Only the lists and list_cards tables seem editable in Base.

On the other hand, if I open Base first and open the properties table in edition then I open Thunderbird and edit a contact in the address book then a simple refresh of the table properties in Base allows me to see the changes.

Well I'm under Lubuntu maybe that changes a lot of things regarding this problem.
LibreOffice 5.3.3.2 - Lubuntu 16.10 - LxQt 0.11.0.3
User avatar
Villeroy
Volunteer
Posts: 31259
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SQLite in LibreOffice / OpenOffice Base

Post by Villeroy »

By the way, "OOo" in the name jdbcDriverOOo is misleading for two reasons. "OOo" refers to the product "OpenOffice.org" which was named after the website hosted by Sun Microsystems.
- Since 2011 the product name is Apache OpenOffice (AOO).
- Your package is incompatible with AOO because something in your Python code is not Python2 compatible.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply