Adding SQL comments to DSL

[adam...@snaphop.com]

We use codehale metrics to instrument our JDBC and have a special JDBC wrapper driver that looks to see if the SQL has a comment.

This comment is used for the metric name.

For example we might have SQL that looks like:

/* com.snaphop.data.campaign.SELECT */ select * from campaign where ....

Now through our magical JDBC spy wrapper we get a metric named "com.snaphop.data.campaign.SELECT" with the execution time and various other metrics like how many times per second etc..

This has worked amazingly well for us since Hibernate will auto prefix SQL with comments as an option and our developers can also create their own metrics even using plain JDBC.

The problem is that it seems the jOOQ DSL does not allow you to enter adhoc SQL comments.

Any thoughts on how we can do this?

[Lukas Eder]

jOOQ supports Oracle-style hints, which are comments. Granted, those hints are not placed at the beginning of a SELECT, they would be here:

select /* com.snaphop.data.campaign.SELECT */ * from campaign where ....

You can do the above as such:

DSL.using(...)

   .select()

   .hint("/* com.snaphop.data.campaign.SELECT */")

   .from(CAMPAIGN)

If you need the comments to be placed at the beginning, an ExecuteListener might be a better choice and the most idiomatic one for jOOQ in general:

http://www.jooq.org/doc/latest/manual/sql-execution/execute-listeners/

It would probably look like this:

public class AddMetricsListener extends DefaultExecuteListener {

    @Override
    public void renderEnd(ExecuteContext ctx) {
        if (somePredicate) {
            ctx.sql("/* com.snaphop.data.campaign.SELECT */ " + ctx.sql());
        }
    }
}

Another option would be to embed your SELECT statement in plain SQL, although that might not be as elegant, as you would be losing type safety:

DSL.using(...)

   .fetch("/* com.snaphop.data.campaign.SELECT */ {0}", theOriginalSelect);

Of course, if you're running all your queries via jOOQ, then why not just implement your magical JDBC spy wrapper as a jOOQ ExecuteListener? In that case, you wouldn't even need the comment trick, you could simply add some custom elements to your ExecuteContext's data() object...

Hope this helps,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[adam...@snaphop.com]

I'm not sure how I see the ExecuteContext + ExecuteListener helping as the issue is not global instrumenting but rather tagging specific SQL calls.

The only way I see this working ostensibly (given that DSL has no way to pass down adhoc data) regardless of whether or not I use an ExecuteListener is to use nasty threadlocals.

That is store the metric name I want with a static utility method that sets the metric in a threadlocal before I call jOOQ and then have my spy/ExecuteListener use the threadlocal and clean it up.

While I can do this I still find it unusual that a tool that can generate SQL programmatically not allow me to generate SQL comments.

Perhaps a comment method should be added to the DSL as a feature? For all SQL you could provide 

public CHAIN __(String comment) that does "-- comments" and then a dialect specific one for providing "/* */" as I believe the C style is not in the SQL standard.

However given the nature of the DSL fluent return value chaining this is probably a rather massive change as it would be needed on every SQL step.

That being said adding SQL comments has been even useful for non metric reasons as the comment provides reference when looking into database logs.

For now I will threadlocal and ask for forgiveness later :)

[Lukas Eder]

2014-12-02 19:16 GMT+01:00 <adam...@snaphop.com>:

I'm not sure how I see the ExecuteContext + ExecuteListener helping as the issue is not global instrumenting but rather tagging specific SQL calls.

You can derive new Configurations from your existing one, which contain the ExecuteListener. Or you can derive new Configurations that contain elements in the data() Map:

http://www.jooq.org/javadoc/latest/org/jooq/Configuration.html#data--

You can then use such a derived Configuration on a per-query basis. jOOQ doesn't make any assumptions about the lifecycle of your Configuration...

 

The only way I see this working ostensibly (given that DSL has no way to pass down adhoc data) regardless of whether or not I use an ExecuteListener is to use nasty threadlocals.

That's another option, of course. If you're using Java 8, you could implement this quite nicely as such:

Comments.comment("My Comment", () -> {

    // jOOQ query here

    // jOOQ query here

});

The comment() method would set and cleanup the ThreadLocal before executing the Runnable lambda, and an ExecuteListener would patch all generated SQL for the time being.

That is store the metric name I want with a static utility method that sets the metric in a threadlocal before I call jOOQ and then have my spy/ExecuteListener use the threadlocal and clean it up.

While I can do this I still find it unusual that a tool that can generate SQL programmatically not allow me to generate SQL comments.

It has been mentioned only once on this user group so far... Besides, you *can* generate SQL comments. In various ways :)

 

Perhaps a comment method should be added to the DSL as a feature? For all SQL you could provide 

public CHAIN __(String comment) that does "-- comments" and then a dialect specific one for providing "/* */" as I believe the C style is not in the SQL standard.

However given the nature of the DSL fluent return value chaining this is probably a rather massive change as it would be needed on every SQL step.

That discussion I mentioned asked for comments at arbitrary positions (https://github.com/jOOQ/jOOQ/issues/2543). I'm still not 100% convinced that we should make this heavy change for the little gain that we get...

That being said adding SQL comments has been even useful for non metric reasons as the comment provides reference when looking into database logs.

For now I will threadlocal and ask for forgiveness later :)

You're forgiven :-)