Skip to content

Instantly share code, notes, and snippets.

@phlummox

phlummox/libreoffice-base-sqlite-odbc-osx.md

Created July 17, 2019 08:07
Show Gist options
  • Save phlummox/bf27fbd19d185e67faa236c6bd0fceca to your computer and use it in GitHub Desktop.
Save phlummox/bf27fbd19d185e67faa236c6bd0fceca to your computer and use it in GitHub Desktop.
Download ZIP
Use LibreOffice Base as a GUI for an SQLite database in OS X
Raw
libreoffice-base-sqlite-odbc-osx.md
author description keywords lang title date twitter:creator original-url original-markdown original-git-repository links-updated-and-markdown-tidied-by wayback-cache-at
Andrew Heiss
Connect LibreOffice to an SQLite database to take advantage of SQLite's ubiquitousness and LibreOffice's form-based GUI.
osx, sqlite, databases
en
Use LibreOffice Base as a GUI for an SQLite database in OS X
Wednesday, February 10, 2016
\@andrewheiss
\@phlummox, wget and the Wayback Machine

The problem

As I conduct interviews for my dissertation research, I've been trying to figure out an open source database for storing interview notes and keeping track of the people and organizations I'm talking to. My ideal requirements are simple:

  1. The format should be open source. #. The format should be portable and not require an underlying server (sorry MongoDB and MySQL)---this way I can save the file in an encrypted file container for IRB data protection purposes. #. The format should be easy to access with multiple languages (especially R and Python), ideally without external dependencies like Java. #. The format should be compatible with some sort of Microsoft Access-esque form GUI to allow for easy data insertion.

However, finding the right combination of programs and formats has been slightly more difficult. SQLite is the best format, given that it's the most widely deployed and used database engine and is open source and has native support in both R1 and Python. The only thing it lacks is a nice form-based GUI front end.

There are plenty of SQLite viewers, but I haven't found any that let you create Access-like forms. I could use Python to program my own GUI (or even get fancy and learn Swift and make a native Cocoa app), but that seems like an excessive amount of work.

LibreOffice Base has excellent support for database-backed forms, but under the hood, LibreOffice uses the Java-based HSQLDB, which does not have native R and Python support and requires older Java runtime environments.

The solution

Fortunately there's a way to use an SQLite database as the backend for LibreOffice Base using an ODBC driver, giving the best of both worlds: an open, universal, Java-free database behind a customizable form-based GUI.

There are official instructions for doing this on Linux and Windows, but there's nothing about doing it in OS X. So here's that missing tutorial.

  1. SQLite is already installed on OS X. Create a new SQLite database using sqlite3 in Terminal (or even easier, use a GUI program). Add some tables to it, or don't---it doesn't matter. You just some sort of database file. #. Download the SQLite ODBC driver for OS X. The page includes a link to a precompiled version (currently it says "Steve Palm kindly provided a build of version 0.9993 for MacOSX 10.{6,7,8,9,10,11} on Intel as installer package (sqliteodbc-0.9993.dmg)"). Install the driver by opening sqliteodbc-0.9993.pkg. #. Download an ODBC manager app. Prior to OS X 10.5, Apple included one of these, but for whatever reason they stopped with Snow Leopard. There are two that work equally well: ODBC Manager and iODBC Administrator. #. Open the ODBC manager/administrator app. Add a new driver using these settings: [Broken link to image --ed.] #. Add a new User DSN (Data Source Name). Create a new key named "database" and use the full absolute path to the SQLite database file as the value: [Broken link to image --ed.] #. Quit the ODBC manager. The SQLite file is now accessible in any program that uses ODBC. #. Open LibreOffice and create a new Base database. In the wizard, select "Connect to an existing database" and choose "ODBC": [Broken link to image --ed.] #. Click next to select which ODBC database to load. If you click on "Browse...", you should see the name of the SQLite database you set up as a DSN earlier. #. Click on "Finish." LibreOffice will prompt you to save an .odf database. This is fine---it's not actually saving the database, just the accompanying form data.2 #. Create new tables and forms using LibreOffice: [Broken link to image --ed.]

Any time you save, all edits will occur on the SQLite file. Create a table, insert some records, and open the SQLite file in a GUI program to see all the changes. Magic!

Even though there are 10 steps, it's not too difficult. tl;dr version: (1) install an SQLite ODBC driver, (2) install an ODBC manager, (3) use the manager to configure the SQLite ODBC driver and connect to an existing SQLite database, and (4) connect to the SQLite database through ODBC with LibreOffice Base.

Perfect!

ORCID iD: 0000-0002-3948-3914

PGP public • PGP fingerprint:
4AA2 FA83 A8B2 05A4 E30F 610D 1382 6216 9178 36AB

Code for site

Footnotes

  1. Technically RSQLite is a separate package, but it's a dependency of dplyr, which is as important as base R in my book. ↩

  2. I think... I haven't actually checked or tested this. ↩

@phlummox
Copy link
Author

Pandoc-style markdown: https://pandoc.org/MANUAL.html

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment