Notes on Connecting to SQLite from Base

 SQLite has some advantages over MySQL (which I had been using).

• SQLite database files are byte-for-byte identical regardless of operating system
• the "amalgamation file" makes it easy to embed SQLite into another program.  This becomes a big deal when you swap computers.
• it is inherently more distributed, because it creates local files on demand

If you're a single user (like me) then I'd add:  

• it doesn't require a user name or password.  

That's probably an unacceptable risk in an enterprise environment, but I'm just one guy.  SQLite also uses some unique language and for data combination it is less powerful than some of the other big name SQLs.  My prediction is you'll end up using it to simply serve data to other applications, which will in turn manipulate or visualize the results.  Philosophically that's similar to other *nix programs so some people might say it's a good thing.

I connected SQLite to LibreOffice's database application under Ubuntu with an open database connector (ODBC), which calls drivers.  Setting the connection up is done in the opposite order:  drivers, then connector, then Base application.

1.  Get the drivers:

sudo apt-get install libsqlite3-dev sqlite3 sqlite3-doc

2.  Get the connector.  

You might already have it; check for it:

dpkg -l | grep unixodbc 

If it doesn't show up, you can install it:

sudo apt-get install unixodbc

After the install, run it as root to add an SQLite driver.

sudo ODBCConfig

Select the Drivers tab (I've already added the drivers... your tab might be empty):

Click the Add button and fill in the information.  Your result should look something like this:

Next, I quit the connector to re-open it as a normal user.  My theory was I wanted to add my specific database file for normal, non-root use and I'd do that as a normal user.  

At this point an actual SQLite database file is required.  It can be empty, but it has to exist.  If you don't have one, here's how to make a simple one so you can practice filling out the next forms.  Open a terminal and start SQLite:

sqlite3 dummy.db

From the SQLite prompt, create a table:

sqlite> create table dummy (id primary key);

sqlite> .quit

That drops an SQLite database file called "dummy.db" in your current directory.  ODBC and Base will be able to work with it.  My database file was called "OpenItems."

Start the connector again as a normal user (no "sudo"):

ODBCConfig

On the User DSN tab, click the Add button to add your database file.  Again, your window might be blank at this point; I've already added mine:

From the Add, fill out the database information:

If you got this far, you've pretty much conquered the connection!  Congratulations.

3.  Point LibreOffice Base at the data connection  (almost done now!)

Select an ODBC database:

Click Next >> and Browse to find the connection you just created in ODBCConfig:

Hit OK, then Next >> again to get to the connection test.  SQLite doesn't require a user name or password, so just click Test Connection.  If everything worked you'll see some good news:

From there on out, it's as if you had created the database inside LibreOffice.  

Click Next >> and decide on database registration etc, and in a couple of windows you should see a the Base interface using your SQLite database.  But using Base is another story - you're connected, so this story is done.  Congratulations!

- nzvyyx