In this post I will talk about a tool for quickly finding rows in a database and navigating through them. If you work in support and you have to make a lot of database queries, if you are tired of writing SELECTs, please, under cat.
Motivation
Some time ago, I helped maintain a large accounting system. In the course of work, it was required to search for information on the database. Typical scenario: a user with a problem calls on request N1. For diagnostics, you need to view some data on this application in the database. We fulfill the request:
SQL SELECT * FROM ORDER WHERE ID = 'N1'
An aggregate is associated with the application, therefore, we perform the following request to obtain information on the aggregate:
SQL SELECT * FROM DEVICE WHERE ORDER_ID = 'N1'
Then we look for all applications related to the unit:
SQL SELECT * FROM ORDERS WHERE DEVICEID = '92375'
And so on. After performing N queries, sooner or later we will find a problem in the data and take measures. The disadvantages of this approach are obvious:
- Manually writing queries is slow and inconvenient. Especially if the database structure is complex and there are many tables. So it is possible to modify the tunnel syndrome.
- When you need to find a related line by Unique constraint or Foreign Key, you have to write a new query.
- Typically, database tools display data in tabular form. When there are many columns in the table, you have to scroll the table horizontally, or select columns in the query. Again, manual work is required.
Idea
First you need to simplify the search. This action should be performed with a minimum of clicks. Just enter the desired line in the text box, and press Enter. Primary keys are usually indexed, so you can search for a value immediately in all columns that are included in Primary Keys or Unique Constraints.
Then you need to solve the navigation problem. How to quickly jump to a related entry on a Foreign Key? You can think of a database as a file system: imagine a database row is a directory, a related line by Foreign Key is a symlink, and a field that is not a Foreign Key is a simple file. I'm not going to write a file system driver, it's just an analogy. So database rows can be represented as a hierarchical structure, which can be displayed using the TreeTable component.
You can also add a column to the TreeTable component, in which some meaningful value for a given row will be displayed. This value can be obtained by concatenating the values of the database row fields. For example, for an order line you can make an expression:
ORDER_NAME + ', ' + ORDER_STATUS + ', ' + ORDER_CUSTOMER
Closest analogy: toString () method in java.
Implementation
Programming took many months. At first I tried to use C ++ and Qt, but it turned out to be difficult: in the C ++ world there is nothing similar to jdbc drivers, and the language itself is much more complicated. Therefore, the application is written in Java.
In the screenshot, we see the search field, the combo box for switching the current connection, and the TreeTable component, which displays hierarchical data.
Search
You can enter a string in the text box and press Enter. Search now works only on columns of string and numeric types: VARCHAR, NUMBER, etc. Date and time types are not yet supported. By default, the tool searches for values in the columns that are included in the Primary Key. In the settings, you can tick off the other fields that will be used in the search.
Key navigation
The nodes labeled [F] are the Foreign Key. In the Table column, we see the name of the table this key refers to. Having opened the node, we move on to the related line. Compound Foreign Keys are also supported.
The nodes labeled [U] are Unique Constraint or Primary Key. Having expanded the node, you can go to the related lines. Take a look at the screenshot:
We entered the value 10248 in the search bar and found a line in the ORDERS table. We opened the [U] ORDER_ID node and found 3 rows in the ORDER_DETAILS table. Then you can expand each node and go to the rows of the ORDER_DETAILS table.
Column String
Primary key values are often uninformative. In the previous screenshot, we see the values ORDER_ID = 10248, PRODUCT_ID = 11. These numbers do not tell us anything. To somehow humanize them, you can make an expression:
'Product: ' + PRODUCT_ID.PRODUCT_NAME + ', Price: ' + UNIT_PRICE
and enter it in the cell of the String column:
Press Enter and see more meaningful values:
Technical details
The application is written in Java, an interface in JavaFX. You may notice that the TreeTable uses the strings "[U]" and "[F]" instead of icons, this was done due to this annoying bug: JDK-8190331 . Database passwords are stored in a secure repository using the java-keyring library. OpenJDK 13 and early-access build jpackage are used to build installers . Build commands can be found here .
Oracle, MariaDB and PostgreSQL databases are now supported.
References
Project page on github: db-tree-fx
If you find an error, or you need to add something, feel free to start the issue or write directly to the mail: db.tree.app@gmail.com .
Installation Packages
Rpm for GNU / Linux: db-tree-0.0.2-1.x86_64.rpm
Deb for GNU / Linux: db-tree_0.0.2-1_amd64.deb
Signed dmg for macOS: db-tree-0.0.2.dmg
Signed msi for Windows: db-tree-0.0.2.msi
Latest release can be found on github