Adding SQLite as a datasource to SQLeo

An audio version of this post is available on Hacker Public Radio.

I have been looking for a tool that will graphically and programmatically track identifiers as they pass through systems. I could have done this in Inkscape after following the excellent tutorials on http://screencasters.heathenx.org/, however I also wanted to be able to describe the relationships programmatically.

This got me to thinking about graphical query builders for databases. The idea is to show each system as a table block and then draw lines between them to show how “Field_X” in “System_A” will map to “Field_Y” in “System_B”. Many of the proprietary and some free database solutions allow this type of view. However I also want to easily package the entire thing up, so that someone else could access it without needing to pay for or install any specialized software. That limited the choice of database to SQLite, which is small, supported on many platforms and is released into the Public Domain.

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is the most widely deployed database in the world with more applications than we can count, including several high-profile projects.

Please follow the instructions on the SQLite site for information on how you can install it on your system. For me on Fedora it’s simple to install via dnf/yum. You might also want to install some GUI managers if that’s your thing.

dnf install sqlite sqlitebrowser sqliteman

I created a small database for demonstration purposes, consisting of two tables and one field in each.

Next step is to download SQLeo Visual Query Builder which has support for a graphical query builder.

A powerful SQL tool to transform or reverse complex queries (generated by OBIEE, Microstrategy, Cognos, Hyperion, Pentaho …) into diagrams to ease visualization and analysis. A graphical query builder that permits to create complex SQL queries easily. The GUI with multi-connections supports virtually all JDBC drivers, including ODBC bridge, Oracle, MySQL, PostgreSQL, Firebird, HSQLDB, H2, CsvJdbc, SQLite. And top of that, everything is open-source!

SQLeo is a Java Tool and there is a limited version available on the web site which is limited to 3 tables per graph and 100 rows. Now as the program is released under the GPLv2.0, you could download the code and remove the restrictions. You can also support the project to the tune of €10 and you will get the full version ready to rock.

Unzip the file and enter the newly created directory, and run the program as follows:

java -Dfile.encoding=UTF-8 -jar SQLeoVQB.jar

One slightly confusing thing, and the reason for this post, is that I could not find support for SQLite listed in the list of databases to connect to. A quick search on the support forum and I found the question “Connection to SQLite DB“. I found the answer a bit cryptic until I read the manual related to JDBC Drivers, which told me how to add the sqlite library.

SQLeo uses a standard Java sqlite library that is released under the Apache Software License, Version 2.0. You can download it from the SQLite JDBC MVNRepository and save it into the same directory as SQLeo.

Right Click in the Metadata explorer window and select new driver.

Click “add library”

Enter the following information
Name: SQLite JDBC
Driver: org.sqlite.JDBC
Example: jdbc:sqlite:~/yourdb.db

Next right click on the newly created driver and select “new datasource”

The name can be anything you like, but the url needs to start with jdbc:sqlite: and then the path to the sqlite database you created earlier. I selected auto-connect and pressed connect as well.

Now you can press the Query Designer button and drag the tables into the view. Once there you can then join up the fields.

That covers the graphical representation, and we can tackle the programmatic implementation by pressing the save button. Which gives the structure as defined by SQL.


SELECT
FROM
System_B
INNER JOIN System_A
ON System_B.Field_Y = System_A.Field_X


So now I can check the SQL into git and have a nice image for adding to documentation any time I like.