Feature suggestion: jooq Factory listeners and filters

[FractalizeR]

I think it would be nice to be able to plug your own query listeners into Factory. Particulary, onExecute() listener will be very useful.

For example, I have many queries in my software already. And some time ago I wanted to verify if my DB indexes are ok for performance. For this it would be nice to inspect all where() clauses of all executed queries for some particular software setup. But get a list of plain text queries (from source or from slf4j jooq listener) and then parse it is tedious. It would be nice to be able to plug a listener to jooq, that can inspect all queries in their object-oriented jooq representation and generate what I want in the format I want.

Also, there are some products currently, like ShardQuery. As I understand, what it does is just parse queries and rewrite/add their WHERE parts so that data will be extracted from the shard you need. It would be nice to be able to plug a filter to jooq Factory, that allows to restructure queries before execution to do this or not-so-this functionality.

[Lukas Eder]

I strongly advise against "automatic" query restructuring. This is a
very complex and theoretical field, which a third party library such
as jOOQ is unlikely to implement satisfactorily. Instead, I recommend
you use your SQL tuning and logging components in MySQL directly, and
analyse those.

I'm not sure about MySQL, but with Oracle, you can do miracles inside
of grid control, trying to find bottlenecks and inefficiencies. But
that's database business, not data layer abstraction business...

I suggest for now, you stay with parsing log files, or you find actual
SQL analysers for MySQL. Or you create a delegate jdbc Connection and
PreparedStatement, that intercepts the execution of queries and does
the analysis for you

2011/5/17 FractalizeR <Fract...@yandex.ru>:

[FractalizeR]

Well, I never asked you for query reconstruction abilities for shard-making with jooq ;) I asked for listeners and filters. The examples of possible use I provided are picked up randomly. There are plenty more.

I also share your concern about automatic query reconstruction, but shard-query seems to be a more or less mature project, which was tested by Percona:

http://www.mysqlperformanceblog.com/2011/05/06/scale-out-mysql/
http://www.mysqlperformanceblog.com/2011/05/14/distributed-set-processing-with-shard-query/

And I really respect those guys and I don't think they would waste time to test rubbish.

[Lukas Eder]

> Well, I never asked you for query reconstruction abilities for shard-making
> with jooq ;) I asked for listeners and filters. The examples of possible use
> I provided are picked up randomly. There are plenty more.

But what I don't understand: Why do you need a listener of the type
onExecute(), that will be executed, well, pretty much after you call
execute() anyway? Can you please list a couple of interesting use
cases, which you mentioned?

> I also share your concern about automatic query reconstruction, but
> shard-query seems to be a more or less mature project, which was tested by
> Percona:
>
> http://www.mysqlperformanceblog.com/2011/05/06/scale-out-mysql/
> http://www.mysqlperformanceblog.com/2011/05/14/distributed-set-processing-with-shard-query/
>
> And I really respect those guys and I don't think they would waste time to
> test rubbish.

I don't doubt that. However, I'm a bit spoiled with my Oracle 10g/11g
cost based optimiser that does these things automatically for me. As
soon as I partition a table, Oracle starts parallelising queries
automatically (by transforming filters into unions, etc). I think
that's the way it should be done.

[Lukas Eder]

On the other hand, I might be able to just add another hook similar to
the currently being developed deserialisation magic, as discussed in
this thread:
http://groups.google.com/group/jooq-user/browse_thread/thread/d33e9a902707d111

Once a query has been properly attached, it can indeed communicate
execution to other listeners... I'll sleep over it

[Lukas Eder]

Hi Vladislav,

I've given this some more thought, and I finally came to the
conclusion that I could make this a feature along with my plans to
generalise logging:
http://sourceforge.net/apps/trac/jooq/ticket/139

My original plans were to have a generic logging interface with a
default implementation (binding to SLF4J). Users can override that
implementation and hook into jOOQ in order to intercept all logging
events. With your suggestions, the logging events could contain
QueryParts and Stores instead of plain strings, such that client code
has more information about what's going on.

In the end, I think you are right. There are quite a few interesting
use cases not directly related to jOOQ itself, but to SQL profiling
and tracing. Let's see if this can make it in 1.6.x

Cheers
Lukas

2011/5/17 Lukas Eder <lukas...@gmail.com>:

[FractalizeR]

I'm sorry for not replying for a long time. Been busy a little with my current project.

Can you please list a couple of interesting use cases, which you mentioned?

Well, while jooq utilizes SLF4J for do some benchmarking, using listeners it would be possible do that even better. For example, you shipped some release of your software to a customer. And this customer pretends, that your application sometimes works really slow. But only sometimes. Well, you can just enable your own profiler listener in jooq via configuration file and it will magically log all queries, that execute longer than X seconds and all related queries to those (how speciffically related depends on your profiler logic. It could be queries, that immediately precede long ones on the same layer or other). If this listener has access to jooq object model, it can also be set up so that it would log long query structure without data so not to overload log file with queries. I know, that some kinds of the job can be done using special tools for query profiling, but I would prefer to spare myself a nightmare of  explaining how to install and operate them to the customer.

For example, I would like to implement soft delete functionality into software. Jooq filter can be used to add WHERE predicate on "deleted" field to all queries for this. It can do this correctly,only if it has access to jooq query model.

Another example can be a plugin "number of thread edits" to a forum software. It can automatically add edited=edited+1 to all update queries on a particular table. Of course, you need to plan the architecture of your software so that it would be possible to add all those using another way, but you can't have too many ways, can you? :)

An important part here I think is for listeners and filters to have access to jooq object model, not only string values of generated SQL queries.

Well, if you need some more, I can think of plenty ;)

Oracle starts parallelising queries automatically (by transforming filters into unions, etc). I think that's the way it should be done.

Well, I'm MySQL guy and I'm scared of even thinking of administering such a monster as Oracle ;) Let alone it's huge price which leaves real sharding available only to Rockfeller-like fellows. There are a plenty other databases each of it has it's own way of development. And I doubt Oracle should set standards of how dabatase should work. Look at Facebook or some other large things.

[Lukas Eder]

Hi,

You should've read my subsequente mails... ;-)

> [...] sometimes works really slow. But only sometimes. Well, you can

> just enable your own profiler listener in jooq via configuration file and it

> will magically log all queries, that execute longer than X seconds [...]

I've seen this use case, too.

> [...] but I would prefer to spare

> myself a nightmare of  explaining how to install and operate them to the
> customer.

That's a good point.

> For example, I would like to implement soft delete functionality into
> software. Jooq filter can be used to add WHERE predicate on "deleted" field
> to all queries for this. It can do this correctly,only if it has access to
> jooq query model.

That, I don't understand. Can you give an example?

> Another example can be a plugin "number of thread edits" to a forum
> software. It can automatically add edited=edited+1 to all update queries on
> a particular table.

Why not use a trigger?

> Of course, you need to plan the architecture of your
> software so that it would be possible to add all those using another way,
> but you can't have too many ways, can you? :)

I think it might work along with the further logging abstraction
plans, I had previously mentioned.

> Well, if you need some more, I can think of plenty ;)

Sure. The more use cases, the better and more general the API.

> Well, I'm MySQL guy and I'm scared of even thinking of administering such a
> monster as Oracle ;)

Me too.

> Let alone it's huge price which leaves real sharding
> available only to Rockfeller-like fellows. There are a plenty other
> databases each of it has it's own way of development. And I doubt Oracle
> should set standards of how dabatase should work. Look at Facebook or some
> other large things.

On the other hand, many of them have copied from Oracle... But let's
omit this discussion ;-)