feat: EXPOSED-238 Support EXPLAIN statements

explain() takes any valid SELECT, INSERT, UPDATE, DELETE etc statement in its lambda body. It blocks the execution of this internal statement and uses it to prepare an EXPLAIN query, which returns a result set of query execution path details (very different depending on database). It acts like a standard Query in that it will not be executed until it is iterated over in some way:

val result = explain {
    Countries.select(Countries.id).where { Countries.code like "A%" }
}.toList()

println(result)
// PostgreSQL example
// QUERY PLAN=Seq Scan on countries  (cost=0.00..25.38 rows=6 width=4)
// QUERY PLAN=  Filter: ((country_code)::text ~~ 'A%'::text)

explain() blocks execution of the lambda statement by using 2 new Transaction properties. 1 of these properties makes sure that the lambda statement is captured so it can be passed to the ExplainQuery constructor, since not all statement functions return Statement<*> (namely update() and delete() return numbers).

Next steps:

[ ] Oracle supports this but the returned value is not a ResultSet. Instead, EXPLAIN PLAN expects an output table to exist for holding the result rows. Still need to decide if this is a table Exposed should create (and later drop) or if responsibility should go to the user. More details at EXPOSED-308.
[ ] SQL Server also supports this but the return value is an XML document. It should be possible to retrieve the contents as text.
[ ] ExplainResultRow is introduced to hold the field-value mapping from the result set because ResultRow's logic is too specific to Exposed table columns and expressions. It acts in a very similar way but is currently only set up to output the string results (as above) and should have getters and some API functions added. More details at EXPOSED-309.

Not supported:

DAO statements: It seems counter-intuitive to allow a DAO operation inside explain(), but this view may be wrong. Blocking execution would also mean blocking all the extra entity steps under-the-hood and in the cache. If requested, starting with SELECT would be the easiest to support (and its the most common use for EXPLAIN).
Batch statements: Depending on the underlying database and whether batching is actually supported, this works. But if batching isn't actually implemented by the driver, it attempts to wrap each individual statement with an EXPLAIN and returns the results of the last one. If there is a use case for querying the execution of batch operations over the single operation, this will need to be addressed.

JetBrains / Exposed

feat: EXPOSED-238 Support EXPLAIN statements #2022