Visualising Your Query

Java programmers have a hierarchical view of the world. Which is fine, even good according to the kings and presidents of the world, but it does clash with SQL's egalitarian attitude.

Java is inherently hierarchical, from the need for a Main method to the IsA/HasA paradigm. Relational databases, on the other hand, deliberately have no hierarchy and you can randomly access data from any table. Even when you try to impose a hierarchy using a query the database will happily invert your hierarchy if it prefers it that way.

DBvolution adds an extra layer to that by automatically arranging the tables to avoid some obvious traps.

So basically queries don't work the way you think and every layer between you and the data is going to re-arrange everything anyway.

That leaves you, and me, in a vacuum of knowledge of the actual query that will be executed. There is access to the SQL via DBQuery.getSQLForQuery() but that is obfuscated, by necessity, and quite annoying to interpret. Besides if you wanted to stare at SQL all the time you wouldn't be using DBV.

What would be nice, particularly when queries aren't behaving, would be a nice display of the query graph showing all the tables and the connections between them. The network/graph for this already exists inside DBV ,it just needs a display technology.

I've been playing with this and 0.9.12 includes a first draft of it in DBQuery.displayQueryGraph(). The aim is to generate an easy to read diagram which will highlight the probable problems with your query. In particular it currently shows loops in the queries and, with some squinting, multiple links between 2 tables. These are both likely to cause issues for your queries and are easily solved using DBRow.ignoreForeignKey or by removing an incorrect ad-hoc realtionship.

It's not finished, but it is there and I'm very impressed with it already. It's a terrible API for an application but for debugging it's brilliant.