krpors / gimlet

A JDBC-based database drill down utility.

Gimlet

GImlet is an attempt to make a useful query application. Users can create their own predefined (parameterized) queries to run against a database. Following the result set of that query, you can 'drill down' further into other tables by effectively executing a sub-query, based on the resultset of the previous query. In a sense, it's a lazy query evaluator.

Screenshots

Screenshots speak louder when it comes to user interfaces:

👆 Loading database objects from a database. Red rows are the primary keys. Tabs can be given an explicit color as a hint of what database/environment you are connected to.

👆 Plain SQL can be entered in the SQL tab. Selected rows can be copied into the clipboard.

👆 Execute a configured child query, based on results from the previous (parent) query.

👆 This screenshot shows the relationship between named parameters (:ID) and how the query is completed using the result from the second row in the table (13). The actual query which is executed by Gimlet will be

select * from item where invoiceid = 13

But why not just use JOINs?

Joins are useful of course, but fail to display data properly when you have to find specific information when there is more than 1 join. You could get double data and things. Also, when there are N:M relations between tables, a drill-down functionality might come in handy. This tool attempts to make that drill down configurable.

Example

Given two tables:

CUSTOMER
-------------------------
ID    NAME    LASTNAME
1     A       Einstein
2     T       Raadt, de
3     G       Rossum, van
4     P       Fry


INVOICE
-------------------------
ID    CUSTOMER_ID
1     1
2     1
3     1
4     3
5     3
6     4

In Gimlet we can now define the following query tree:

1. Select all customers: select * from CUSTOMER
   1. Select invoices for customer: select * from INVOICE where CUSTOMER_ID = :ID

The :ID is crucial here: this is input from the column ID from the previous query.

Compiling and packaging

This codebase is targeted from Java 11 and onwards, and can be compiled with Maven 3.5+.

After cloning the repository, just run

mvn clean install

to compile the code. To create .tar.gz and .zip distribution:

mvn clean package assembly:single

In the target directory a file will be created, called gimlet-$VERSION.tar.gz. The contents are as follows:

gimlet-$VERSION
	README.md       <-- this file you're reading
	LICENSE         <-- license file
	/bin/
		gimlet.sh   <-- shell script for Linux
		gimlet.bat  <-- batch file for Windows
		gimletw.bat <-- batch file for Windows, without console
	/drivers/
	    (drop any JDBC driver JARs here)
	/lib/
		(all libraries requires to run the application)

Running from Maven

To directly run from within Maven, just run:

mvn exec:java

JDBC driver installation

The only thing required is that the necessary JARs are to be put on the classpath. If you have a driver JAR, for example ojdbc6.jar, just drop it in the ./lib/ or ./drivers/ folder. When Gimlet is run the next time (using the run scripts), all JARs are added to the classpath, thus loading the JDBC driver automatically. In the Alias editor dialog, the combobox should then list all available drivers.

Parameter formatting

There are multiple ways to configure parameters in a formatted query, and can be of influence for user input. Depending on the configuration, a PreparedStatement receives a different type.

Parameter format	Parsing notes	UI element
:ID	Basic parameter input. Result will be given as a java.lang.String	Text field
:ID[STRING]	Same as :ID, but more explicit. Rather redundant.	Text field
:ID[NUMBER]	The given parameter will be given as a java.lang.Number into the statement.	Number text field
:ID[DATE]	A java.sql.Date is used.	Date picker
:ID[DATETIME]	A java.sql.Timestamp is used.	Date/time picker

For example, if a query is configured as follows:

select * from some_table
where
    id         = :someId[NUMBER]
and start_date > :startDate[DATE]
and click_time < :clicketyTime[DATETIME]
and user       = :userName[STRING]

and the query is directly executed, you are prompted with this screen:

TODOs and ideas

A list of things to be done, or some ideas. Not in any particular order.

Milestones for 1.1 release

1. Recent queries: save them in different file.
2. Multiple select, and multiple sub-query execution. For instance, select 4 rows, right click and then select a sub query. This will result in 4 new tabs with results.

Nice to haves

1. Add some kind of list of known JDBC drivers + connection strings. This eases up the initial setup of a JDBC connection.

2. Help file/screen.

3. Query wizard or something: start by a root query, execute sample query, then hit next to configure the next query, until finished, then a query tree is a result.

4. Easier column selector (hide all/show all)

5. Syntax highlighter?

Code cleanups

1. Fix the way the QueryTree and the backed Query object tree work?
2. Moar Javadoc.

Notes

These are some notes about things used to help development of certain features.

Mimic network latency using NetEm (man netem), using the tc utility. This can be used to test connections such as longer roundtrips, connection timeouts, interruptions, etc.

Icons used can be found here.

Mimic network cutoffs by using socat as a TCP proxy between Gimlet and the target database host, for example

socat -x -v tcp4-listen:1521,bind=127.0.0.1,reuseaddr,fork tcp4:targetmachine:1521 2> lol.txt

Then establish a connection to localhost:1521 and interrupt using ^C.

gimlet's Issues

There's no disconnect context menu item

There's no disconnect context menu item on the connections in the Aliases Tab.

Since DBs in embedded mode (like H2) can have only one connection at a time, it's important to know if the connection is open, or not, and maybe disconnect.

Usually in most tools after the users clicks connect, the menu item(or action) is transformed in "Disconnect".

By this operation I don't mean to close the Tab that was opened :), but just the connection, in order to allow other tools to do something on the embedded DB.

On i3, some dialogs don't seem to automatically get a proper size.

On PostgreSQL, column type bytea has improper rendering

On PostgreSQL (maybe also other RDBMS?), the blob type bytea is rendered using the hashcode of the value in the table view. For example, this table definition:

is rendered as follows:

Ideally this would be rendered differently. Probably depends on size of the content (don't want to render 500 megabytes of data, for instance).

Icon for the app?

The app looks very polished and nice, and it even has a splash screen in About, so it's missing just an icon :).

Operations on more than one connection?

Any plans allowing operations on more than one connection?

It seems that quite allot of cases involves work on more than one DB (and sometimes those are not even the same DB type, or support DB Link like Oracle to be able to work with a single connection).

Thank you.

Missing Vertical Split Bar

There's a horizontal Split Bar, but not a vertical one.
Especially when using on some laptops (without external monitor), or when having deep drill downs, it would be useful to be able to resize the vertical split too.

Copy as Plain Text.

The functionality from the context menu on a Table "Copy as Plain Text", looks more like "Copy as a Wiki Table" .

It is very useful, but not what most users would expect.

Maybe Plain text should be just plain text (tab separated, so that it can be pasted into any spreadsheet) ?

Also I'm not sure if the encoding of the copied text is the usual default to UTF-8, since most tools seems to recognized it as UTF-16 on Windows (but maybe this is a tool problem).

'Port' code to Java 11

Java 8 has been "EOL" for quite some time now. There is a jdk11 branch. It requires some merging here and there but the master branch should be targeting JDK 11.

Executing a non-root query with manual input throws exception with PostgreSQL

See title. When connecting to a PostgreSQL database and executing a non-root query (with some manual input), an exception gets thrown.

Logging Tab with Table rendering usage.

The logging tab with a "table" as a rendering looks really cool and polished, and more than most tools have :) , but:

• can't select more than one row
• can't copy the selected row to the clipboard
• can't select all with Ctrl+A (as it's possible in other tables)
• can't sort the table
• can't filter (search) the columns to find something useful
  If the Logging tab were a simple text output (e.g. formatted with colors maybe), all the above would be possible.

Drivers directory!

Please create a /drivers directory where users can copy their drivers, in order not to mix them with the application JARs from /lib