SQLite in LibreOffice / OpenOffice Base

[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:

• jdbcDriverOOo

• SQLiteOOo

enjoy...

[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...

[psilocybe]

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

[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

[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.

[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.

[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

[Mountaineer]

... 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.

[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.

[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.

[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.
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...

[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

[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.

[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.

[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.

[psilocybe]

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.

I know, but at the beginning jdbcDriverOOo was supposed to work on LibreOffice and OpenOffice.
But trying to get Python 2 to work now has become impossible.
I wouldn't change the name and we can say that this is for historical reasons...

In addition, this resurrects Sun, because it is thanks to them that we have Java and LibreOffice.
Besides, in version 1.2.2 of jdbcDriverOOo, I think that I am using features in Base which were written by Sun quite a while ago and which have never been used until now...
So thank you Sun Microsystems...

[Villeroy]

Trying to update from 1.1.5 to 1.2.2 on
Version: 24.2.0.3 (X86_64) / LibreOffice Community
Build ID: da48488a73ddd66ea24cf16bbc4f7b9c08e9bea1
CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: x11
Locale: de-DE (de_DE.UTF-8); UI: en-US
Calc: threaded

Error while installing extension jdbcDriverOOo. The error message is: Cannot detect media-type: file:///tmp/C0KLtY_/vO5d73_/40307323-a6d6-401d-b980-67614a469d4c at /home/buildslave/source/libo-core/desktop/source/deployment/registry/dp_registry.cxx:479
The extension will not be installed.

[psilocybe]

Yes I know but I can't do much about it. It comes from LibreOffice see issue #159775.
You have to install by hand...
Thank you for the report.

[Villeroy]

You have to install by hand...

Can I simply replace the old jars with the niew ones?
Why does the old version install cleanly but not the new one?

[psilocybe]

Can I simply replace the old jars with the new ones?

The extension is not just the jar files it contains.
I advise you to manually download the new version and use the LibreOffice extension manager to install it.
I even recommend uninstalling before reinstalling, restarting LibreOffice between each.

Why does the old version install cleanly but not the new one?

Have you ever managed to do an automatic update?
Because I've never seen it work.

[Villeroy]

Since several versions, my Thunderbird address book (SQLite) fails with the following query:

Code: Select all

SELECT "value" / 86400.00000 + 25569.00000  AS "LastModifiedDate"  
FROM "properties" WHERE "name" = "LastModifiedDate"

which used to convert the epoch time into office time (days since 1899-12-30).
"value" is a numeric text, but with version 1.0 of your extension, the implicit conversion worked just fine. On https://sqlite.org/lang_corefunc.html I can not find any explicit conversion.

[psilocybe]

Since several versions, my Thunderbird address book (SQLite) fails with the following query:

Code: Select all

SELECT "value" / 86400.00000 + 25569.00000  AS "LastModifiedDate"  
FROM "properties" WHERE "name" = "LastModifiedDate"

which used to convert the epoch time into office time (days since 1899-12-30).
"value" is a numeric text, but with version 1.0 of your extension, the implicit conversion worked just fine. On https://sqlite.org/lang_corefunc.html I can not find any explicit conversion.

Sorry I just saw your message. I'll check and come back...

[Villeroy]

Sorry I just saw your message. I'll check and come back...

Take your time. I don't really need that database. Just wonder why CAST("value" AS INTEGER) fails.

[RogoWarrior69]

Weird that the CAST function isn't working as expected. Is it possible that the data type of the 'value' column has changed in the newer versions of the extension?

[psilocybe]

Hi,

I just updated SQLiteOOo to fix a regression and to support LibreOffice 24.8.x which comes with Python 3.9 in its Window version.

However, I have trouble explaining why this query worked with version 1.0.0 of SQLite and not with the following versions. Normally the driver does not influence the queries, it only reads them to extract:
- The type of query (ie: SELECT, INSERT or OTHER).
- And only for SELECT and INSERT queries the name of the table involved.

For me, but this remains to be verified, only the SQLite Java driver can be responsible for such a problem.
I see that SQLiteOOo 1.0.0 was shipped with SQLite version 3.42.0.0 and I will try this driver with the latest version of SQLiteOOo.

However, for ease of use, do you know how to easily reproduce this problem?

[Villeroy]

However, for ease of use, do you know how to easily reproduce this problem?

Install the Thunderbird mail client with at least one address entry in the native address book.
Download my database sample, call Edit>Database>Properties and adjust the URL path to <thunderbird-profile>/your_profile-folder/abook.sqlite
Add this query which is a little section from my main query:

Code: Select all

SELECT "value" / 86400.00000 + 25569.00000  AS "LastModifiedDate"  
FROM "properties" WHERE "name" = "LastModifiedDate"

It converts the epoch date (seconds since 1970-1-1) into a day number since 1899-12-30 as used with date controls, formatted controls, spreadsheet cells, date fields etc.
of course, you can use any other column with some string of digits.