LinuxQuestions.org
View the Most Wanted LQ Wiki articles.
Go Back   LinuxQuestions.org > Blogs > Yury_T
User Name
Password

Notices

Rate this Entry

How to set up SQLite database as a (read-only) bibliography database in the LibreOffice/OpenOffice

Posted 10-24-2013 at 02:49 AM by Yury_T

Folks have not-so-positive experience with the LibO/OOO default bibliography database, what with it being effectively either of HSQLDB or CSV text type, for one. One would want to have more choice as to the database type, and to have it storaged more effectively, and in single file, preferrably. Also, there are many popular tools for other database engines, and HSQLDB seems somewhat isolated in this aspect, restricted to all-Java projects, too.

E.g., you might want to have two sets of biblio sources, storaged with their respective projects. You might not want to make use of in-document biblio storage if you make use of 100s of sources and want to keep track of those, etc.

One of the attractive choices for database type is SQLite (http://sqlite.org). However, LibO/OOO doesn't carry DB drivers for it «in the box». The «third party» access tools I tried were: (1) Xerial SQLite JDBC driver/connector, (2) Christian Werner's javasqlite JDBC driver/connector, (3) Christian Werner's ODBC SQLite connector — and this is what worked for me (thanks, Christian Werner!) and is described here. The experience with JDBC tools is put into the appendix.

And yes, I am aware of solutions like Zotero, bibus, jabref.

***

I've managed to do this set-up on Linux. Wouldn't know if this could be reproduced exactly on Windows. Also, I didn't study ODBC exhaustively and so might miss some clues as to how ODBC is properly configured. In effect, this set-up gives you the read-only bibliography database, not modifiable from within LibO/OOO. I'd appreciate thoughts and advice on that.

1) Have Unix ODBC installed. Version 2.3.0/AMD64 here, taken from http://slacky.eu (thanks, guys!), or compile from source (http://unixodbc.org), if you'd like to.

2) Have SQLite ODBC Driver installed. Version 0.995 here, compiled from source (http://www.ch-werner.de/sqliteodbc/).

3) Configure system ODBC. Put an entry into system config file (<b>odbcinst.ini</b> in /etc or in /usr/local/etc or whatever's your system config dir), looking like:

[SQLite]
Driver = /usr/local/lib/libsqlite3odbc.so
FileUsage = 1

4) Configure private data source name (DSN). Put an entry into personal config file (<b>.odbc.ini</b> in your home dir — notice the leading dot in the name), looking like:

[biblio1]
Driver=SQLite
Database=/home/username/bib/bibs.db
Tracefile=/tmp/biblio1.log

Notice how «Driver» parameter value in ~/.odbc.ini is text-case-equal with section name in /etc/odbcinst.ini, although I'm not sure whether case-equality really matters.

BIG CAUTION: do not put leading spaces into .INI files lines!! ODBC (at least in Unix version 2.3.0) fails to process such lines correctly.

5) Create the SQLite database, intended for LibO/OOO bibliography use. The SQL biblio DB schema creation script, which can be found inside the LibO/OOO somewhere, has to be text-edited. Remove keyword AS from it (text-search for ' AS ', replace with '').

6) In LibO/OOO create new ODB database, referencing the SQLite database. Select 'Connect to existing database', select ODBC from list, click 'Next', browse DSN list and select 'biblio1' (like described here). Click 'Next', click 'Test connection' (should produce okay message), click 'Next', uncheck 'Open database for editing', click 'Finish'.

Save this ODB database, e.g., to /home/username/bib/biblio.odb

7) You won't be able to configure LibO/OOO to regard this new ODB database as a 'bibliography database', but you can trick LibO/OOO, at least on Linux, into accepting it like follows:

7.1) Rename ~/.config/libreoffice/3/user/database/biblio.odb to, say, biblio_box.odb:

mv ~/.config/libreoffice/3/user/database/biblio.odb ~/.config/libreoffice/3/user/database/biblio_box.odb

7.2) Put a symbolic link named biblio.odb here, pointing to your ODB database:

ln -s /home/username/bib/biblio.odb ~/.config/libreoffice/3/user/database/biblio.odb

***

If everything worked as expected, you should now have LibO/OOO accessing the SQLite database (via the ODB database→ODBC connector→SQLite database query path), and would be able to insert biblio entries into documents in standard way etc. That's good.

What's bad is, like I said before, that the biblio DB accessed that way is read-only (yet?) from within LibO/OOO and has to be filled by external tools. If LibO/OOO menu 'Tools'→'Bibliography database' is clicked, the table 'biblio' is opened. If you close it without even doing anything, ODBC produces HY010 error.

Also, there is no clearly visible LibO/OOO functionality to switch 'principal bibliography database', at least on Linux. Yes, you can 'Exchange data sources', on table-for-table basis, but that is not it.

The obvious next step would be making sort of LibO/OOO extension (or external script, even), capable of dumping all in-document biblio entries to SQLite database.

***

<b>Appendix.</b> The experience with JDBC SQLite connectors

1) Xerial SQLite JDBC driver/connector (https://bitbucket.org/xerial/sqlite-jdbc). Tried (fresh) version 3.7.15. LibO/OOO's ODB sets up and connects but table access wouldn't work, as the driver advertises FORWARD_CURSOR_TYPE capability only, and LibO/OOO refuses to even open a table via such connector/driver.

2) Christian Werner's javasqlite driver/connector (http://www.ch-werner.de/javasqlite/). Tried version 20130214. LibO/OOO's ODB sets up and connects but table access crashes LibreOffice (version 3.6.7.2/AMD64 here).
Posted in Uncategorized
Views 773 Comments 0
« Prev     Main     Next »
Total Comments 0

Comments

 

  



All times are GMT -5. The time now is 07:13 PM.

Main Menu
Advertisement

My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration