Patch to extend JMX support to send notifications

[Steve McLeod]

Hi Thomas and others,

I've patched the current H2 source to extend the usage of JMX. If JMX
is enabled for a database, then it sends a notification whenever a
query starts and whenever a query ends.

Is this something you'd consider adding to the H2 trunk? If so, I'll
finalise my patch and submit it.

Currently it is a proof of concept but I envisage adding notifications
for all DML and DDL commands.

My use case is tracking long running schema manipulation queries such
as "ALTER TABLE ADD COLUMN", which actually performs several commands,
including "CREATE TABLE", "DROP TABLE", "CREATE INDEX". Currently
these can't be tracked in their entirety to give users helpful
feedback. I also envisage this being a way to eventually track long-
running SELECT queries, by sending a notification after every x rows
are handled.

Alternatively I could modify the existing DatabaseEventListener to do
this, but as that would require changing an existing API, I think this
would not be a good approach.

Regards,

Steve

[Thomas Mueller]

Hi,

It sounds like JMX would be used as a "event mechanism". Until today I didn't know that this was even possible...

I understand extending the DatabaseEventListener might be a backward compatibility issue. But what about adding new states in the DatabaseEventListener? For example:

DatabaseEventListener.STATE_STATEMENT_START

DatabaseEventListener.STATE_STATEMENT_END

Existing listeners should still work, and wouldn't have to be changed. Maybe they throw an exception for a (so far) unknown state, but such exceptions could be ignored. Therefore, it wouldn't be a interface compatibility problem, just a semantical compatibility problem. Because throwing exceptions is slow (the fillInStackTrace part), that would mean reduced performance, but I think that's OK.

What do you think?

Regards,

Thomas

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>

[Steve McLeod]

Hi Thomas,

The JMX approach is architecturally elegant and has certain advantages. But using the existing DatabaseEventListener is lightweight and requires less code to be added to H2. It is nicer to use the existing approach (DatabaseEventListener) rather than add a duplicate effort (JMX).

So if you are happy with me adding new states to DatabaseEventListener, I'll do so. That was my first trial approach actually!

The DatabaseEventListener notifications run on the database server thread. A consequence is that an API user might inadvertently write code that causes delays to the query. So I propose also making the calls to DatabaseEventListener.setProgress run using a dedicated Executor. For backwards compatibility maybe this should be optional though. What do you think? Shall I make the notifications asynchronous?

Regards

Steve

> To unsubscribe from this group, send email to h2-database+unsubscribe@googlegroups.com.

[Thomas Mueller]

Hi,

> The DatabaseEventListener notifications run on the database server thread.

Yes, I think that's fine.

> A consequence is that an API user might inadvertently write code that causes
> delays to the query.

That's his problem. Maybe he actually wants to delay the query.

> So I propose also making the calls
> to DatabaseEventListener.setProgress run using a dedicated Executor.

No, I think that's not necessary.

> Shall I make the notifications asynchronous?

No, please not. If such a feature is required, the
DatabaseEventListener implementation could process this
asynchronously. Possibly DatabaseEventListener could be stacked. If
that ever is a problem.

Regards,
Thomas

[Rami Ojares]

There already seems to exist a reference to asynchronous execution of
triggers in the documentation of CREATE TRIGGER.
Namely QUEUE.

The thing the unnerves me with the database listener is that there is no
way to manage those listeners
with SQL whereas there is such capability with triggers.

And making JMX the only way to do some operations with H2 bewilders me...
Why must we make the life of users so difficult?

Let's make only one way to write hooks into H2 which is implementing a
java interface
and managing the triggering of that interface with SQL (using jdbc as
the communications API).

So basically we would need statements like CREATE DATABASE_LISTENER ... etc.
This is not urgent but the point is that if we implement things in some
consistent way
then the database is easier for users to use.

Additional interfaces can be supported of course to increase
compatibility but only after the basic way
to do things has been implemented.

- Rami

[Steve McLeod]

Okay, I'll keep things happening on the Database Server thread, as they currently are.

I plan to make time this week to finish my work on adding some new "states" for the Database Event Listener.

Regards,

Steve

[Steve McLeod]

Here is my patch to use the existing DatabaseEventListener to notify of statement start and statement end.

It notifies for every statement, including those executed internally by H2.

I wrote the code, it's mine, and I'm contributing it to H2 for distribution multiple-licensed under the H2 License, version 1.0, and under the Eclipse Public License, version 1.0 

Regards,

Steve

[Noel Grandin]

Sorry this took so long Steve. Patch applied.