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
On Saturday, 10 March 2012 18:23:35 UTC+1, Steve McLeod wrote:
>
> 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
>
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/h0Rr-IF7exAJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Index: src/test/org/h2/test/jdbc/TestDatabaseEventListener.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>MacRoman
===================================================================
--- src/test/org/h2/test/jdbc/TestDatabaseEventListener.java (revision 4181)
+++ src/test/org/h2/test/jdbc/TestDatabaseEventListener.java (revision )
@@ -23,6 +23,7 @@
public class TestDatabaseEventListener extends TestBase implements DatabaseEventListener {
private static boolean calledOpened, calledClosingDatabase, calledScan, calledCreateIndex;
+ private static boolean calledStatementStart, calledStatementEnd;
/**
* Run just this test.
@@ -40,6 +41,7 @@
testCalled();
testCloseLog0(false);
testCloseLog0(true);
+ testCalledForStatement();
deleteDb("databaseEventListener");
}
@@ -90,7 +92,7 @@
}
deleteDb("databaseEventListener");
String url = getURL("databaseEventListener", true);
- url += ";DATABASE_EVENT_LISTENER='"+ Init.class.getName() + "'";
+ url += ";DATABASE_EVENT_LISTENER='" + Init.class.getName() + "'";
Connection conn = DriverManager.getConnection(url, "sa", "sa");
Statement stat = conn.createStatement();
stat.execute("select * from test");
@@ -210,6 +212,24 @@
assertTrue(calledClosingDatabase);
}
+ private void testCalledForStatement() throws SQLException {
+ Properties p = new Properties();
+ p.setProperty("user", "sa");
+ p.setProperty("password", "sa");
+ calledStatementStart = false;
+ calledStatementEnd = false;
+ p.put("DATABASE_EVENT_LISTENER", getClass().getName());
+ org.h2.Driver.load();
+ String url = "jdbc:h2:mem:databaseEventListener";
+ Connection conn = org.h2.Driver.load().connect(url, p);
+ Statement stat = conn.createStatement();
+ stat.execute("create table test(id int primary key, name varchar)");
+ stat.execute("select * from test");
+ conn.close();
+ assertTrue(calledStatementStart);
+ assertTrue(calledStatementEnd);
+ }
+
public void closingDatabase() {
calledClosingDatabase = true;
}
@@ -233,6 +253,17 @@
if (state == DatabaseEventListener.STATE_CREATE_INDEX) {
if (!name.startsWith("SYS:")) {
calledCreateIndex = true;
+ }
+ }
+ if (state == STATE_STATEMENT_START) {
+ System.out.println("name = " + name);
+ if (name.equals("select * from test")) {
+ calledStatementStart = true;
+ }
+ }
+ if (state == STATE_STATEMENT_END) {
+ if (name.equals("select * from test")) {
+ calledStatementEnd = true;
}
}
}
Index: src/main/org/h2/command/Command.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>MacRoman
===================================================================
--- src/main/org/h2/command/Command.java (revision 4181)
+++ src/main/org/h2/command/Command.java (revision )
@@ -8,6 +8,8 @@
import java.sql.SQLException;
import java.util.ArrayList;
+
+import org.h2.api.DatabaseEventListener;
import org.h2.constant.ErrorCode;
import org.h2.engine.Constants;
import org.h2.engine.Database;
@@ -121,6 +123,14 @@
if (trace.isInfoEnabled()) {
startTime = System.currentTimeMillis();
}
+ }
+
+ void publishStart() {
+ session.getDatabase().publishEvent(DatabaseEventListener.STATE_STATEMENT_START, sql);
+ }
+
+ void publishEnd() {
+ session.getDatabase().publishEvent(DatabaseEventListener.STATE_STATEMENT_END, sql);
}
/**
Index: src/docsrc/html/changelog.html
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- src/docsrc/html/changelog.html (revision 4181)
+++ src/docsrc/html/changelog.html (revision )
@@ -23,6 +23,7 @@
</li><li>ConvertTraceFile: the time in the trace file is now parsed as a long.
</li><li>Invalid connection settings are now detected.
</li><li>Issue 387: WHERE condition getting pushed into sub-query with LIMIT.
+</li><li>DatabaseEventListener now calls setProgress whenever a statement starts and ends.
</li></ul>
<h2>Version 1.3.165 (2012-03-18)</h2>
Index: src/main/org/h2/command/CommandContainer.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>MacRoman
===================================================================
--- src/main/org/h2/command/CommandContainer.java (revision 4181)
+++ src/main/org/h2/command/CommandContainer.java (revision )
@@ -67,20 +67,24 @@
public int update() {
recompileIfRequired();
+ publishStart();
start();
session.setLastIdentity(ValueNull.INSTANCE);
prepared.checkParameters();
int updateCount = prepared.update();
prepared.trace(startTime, updateCount);
+ publishEnd();
return updateCount;
}
public ResultInterface query(int maxrows) {
recompileIfRequired();
+ publishStart();
start();
prepared.checkParameters();
ResultInterface result = prepared.query(maxrows);
prepared.trace(startTime, result.getRowCount());
+ publishEnd();
return result;
}
Index: src/main/org/h2/engine/Database.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>MacRoman
===================================================================
--- src/main/org/h2/engine/Database.java (revision 4181)
+++ src/main/org/h2/engine/Database.java (revision )
@@ -2406,4 +2406,9 @@
throw DbException.throwInternalError();
}
+ public void publishEvent(int state, String sql) {
+ if (eventListener!= null) {
+ eventListener.setProgress(state, sql, 0, -1);
+ }
+ }
}
Index: src/main/org/h2/api/DatabaseEventListener.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>MacRoman
===================================================================
--- src/main/org/h2/api/DatabaseEventListener.java (revision 4181)
+++ src/main/org/h2/api/DatabaseEventListener.java (revision )
@@ -45,6 +45,16 @@
int STATE_RECONNECTED = 4;
/**
+ * This state is used when a query starts.
+ */
+ int STATE_STATEMENT_START = 5;
+
+ /**
+ * This state is used when a query ends.
+ */
+ int STATE_STATEMENT_END = 6;
+
+ /**
* This method is called just after creating the object.
* This is done when opening the database if the listener is specified
* in the database URL, but may be later if the listener is set at
@@ -79,7 +89,7 @@
* @param state the state
* @param name the object name
* @param x the current position
- * @param max the highest value
+ * @param max the highest value, -1 if the max value is unknown
*/
void setProgress(int state, String name, int x, int max);
