First let me say that I adore this project. Finding new wanted or
unexpected features in jOOQ has been making my day all week.
Our database team is particularly concerned with logging, and they want to
make sure that every query and procedure call gets logged, with its
parameters, in a readable format. The SQL renderer is probably a pretty
boring piece of software to work on, but quality of logging output is going
to be a crucial part of getting my organization to adopt jOOQ.
Jooq's rendering, for the most part, is an improvement over our
hand-written queries, but I anticipate getting a little pushback on line
length. It can get bad in our system because we tend to have long
identifiers and need to fully qualify tables with the schema name. Here's
an example (identifiers are redacted, but their lengths are accurate):
select
alpha_id,
alpha_date
from (select
alpha_id,
alpha_date
from (select
zzzzzzzz.xxxxxxxxxxxxxxxxxxxx.yyyyyyyyyyyyy alpha_id,
zzzzzzzz.xxxxxxxxxxxxxxxxxxxx.ssssssssss alpha_date,
row_number() over (partition by
zzzzzzzz.xxxxxxxxxxxxxxxxxxxx.pppppp order by
zzzzzzzz.xxxxxxxxxxxxxxxxxxxx.ssssssssss desc) r
from zzzzzzzz.xxxxxxxxxxxxxxxxxxxx)
where R = 1) alpha
left outer join zzzzzzzz.qqqqqqqqqqqqqqqqqqqqqqq on
zzzzzzzz.qqqqqqqqqqqqqqqqqqqqqqq.yyyyyyyyyyyyy = alpha_id
where zzzzzzzz.qqqqqqqqqqqqqqqqqqqqqqq.yyyyyyyyyyyyy is null
The partition and the outer join would both be nice to wrap. It would be
great if I could specify a maximum line length of something like 60
characters. Did I overlook this setting, or is this a new feature request?
Side note: The documentation does not delve deeply enough into logging and
settings for my liking. I didn't even know that pretty-printing queries was
possible until I started digging through the API. It's a great feature, and
you ought to promote it.
If there is any request I have of the documentation, it's even more
examples! Perhaps my use case might be a useful one? My short code is given
below.
class JooqLogging extends DefaultExecuteListener {
private static final Logger log = getLogger(JooqLogging.class);
public void end(ExecuteContext ctx) {
if (log.isDebugEnabled()) {
Factory factory = new Factory(ctx.getDialect(), new
JooqSettings());
if (ctx.query() != null) {
log.debug(factory.renderInlined(ctx.query()));
} else if (ctx.routine() != null) {
log.debug(factory.renderInlined(ctx.routine()));
} else if (!StringUtils.isBlank(ctx.sql())) {
log.debug(ctx.sql());
}
}
}
}
class JooqSettings extends Settings {
{
setExecuteLogging(false);
getExecuteListeners().add(JooqLogging.class.getName());
setRenderNameStyle(RenderNameStyle.LOWER);
renderFormatted = true;
}
}
