Triggers and Functions in Groovy, or Javascript, or Java, or any BSF language...

[Kerry Sainsbury]

Hi Folks,

I've done the unthinkable and implemented non-Java based aliases and
triggers. With my patch you can now use Java, or any BSF-supported
language, or if you use Groovy you get what is (to me) a much more
user-friendly syntax -- one like many other database engines use.

I've been worrying about this patch for ages, because I think Thomas
is going to hate it, but I've done it now, and I've managed to support
the traditional Java-oriented style (to keep him happy :-), as well as
a more traditional Postgres/Oracle/etc style.

eg: Here's my Groovy trigger. Take note of the NEW and OLD variables,
as well as CURRENT_USER and the SQL object (which is actually an
instance of the Groovy Sql class). I think it's quite readable:

create module MyModule lang groovy as
$$
def fire() {
SQL.execute(
"INSERT INTO TEST_AUDIT(SALARY_ID, OLD_SALARY, NEW_SALARY) " +
"VALUES ($NEW.id, $OLD.salary, $NEW.salary)")

NEW.updated_by = CURRENT_USER
}
$$;

BSF-based languages, such as Javascript in this example, aren't as
nice as Groovy, but still (IMHO) look better than the Java equivalent:

create module MyModule lang js as
$$
function fire(conn) {
prep = conn.prepareStatement(
"INSERT INTO TEST_AUDIT(SALARY_ID, OLD_SALARY,
NEW_SALARY) VALUES (?, ?, ?));
prep.setInt(1, NEW.get('ID'));
prep.setInt(2, OLD.get('SALARY'));
prep.setInt(3, NEW.get('SALARY'));
prep.execute();
prep.close();

NEW.put('UPDATED_BY', CURRENT_USER);
}
$$

Here's the Java equivalent, with its magic numbers to identify
columns, and extra methods that are (IMHO) not particularly
worthwhile.

create module MyModule as
$$
import java.sql.*;

public class MyTrigger implements org.h2.api.Trigger {
public void init(Connection conn, String schemaName,
String triggerName, String tableName,
boolean before, int type) throws SQLException {
}
public void fire(Connection conn, Object[] oldRow,
Object[] newRow) throws SQLException {
PreparedStatement prep = conn.prepareStatement(
"INSERT INTO TEST_AUDIT(SALARY_ID, OLD_SALARY, NEW_SALARY) "+
"VALUES (?, ?, ?)");
prep.setInt(1, (Integer)newRow[0]);
prep.setInt(2, (Integer)oldRow[1]);
prep.setInt(3, (Integer)newRow[1]);
prep.execute();
prep.close();

newRow[3] = "Bob"; // Can't actually access 'current user'
}
public void close() {}
public void remove() {}
}
$$

... and they all need this:

create trigger TRIG_INS before update,insert on test for each row
call "MyModule";

If you want BSF support, you need to be running JDK 1.6 (and you'll
get Javascript support out-of-the-box), or you need to include the BSF
and your language of choice in the classpath.

For Groovy support you need the groovy-all-1.7.5.jar jar in your classpath.

Technically what I did was, approximately:

- Added org.h2.language package for LanguageRunner interface, and
implementations
JavaLanguageRunner, BsfLanguageRunner and GroovyLanguageRunner
- Added UserMethod interface to represent 'methods' that can be called.
- Move "class" oriented methods from org.h2.util.Utils to ClassUtils
- New tests: TestUserModuleBsf, TestUserModuleGroovy, TestUserModule
- New error messages 90141 and 90142

There are some issues that I haven't resolved, because I think Thomas
may well explode and not want this patch. I'll fix these issues if he
is comfortable with what I've done so far :-)

- How to handle upgrade so existing code is identified as 'JAVA'
- GroovyLanguageRunner could be prettier!
- JavaLanguageRunner could have nicer exception handling
- BsfUserMethod and GroovyUserMethod need some work

What next? If Thomas is ok with this concept, then I can do the final
cleanup and documentation. If he isn't, well that's fine too because
I've enjoyed building it, and I'm happy enough to just throw it all
away!

Feedback appreciated.

Cheers
Kerry

[Sergi Vladykin]

Hi,

This looks like a very inetersting feature for increasing developer
productivity but did you any preformance comparisons of Java vs Groovy
triggers and functions?
It would be very interesting to see if it will be significant or not
too.

regards,
Sergi

[Rami Ojares]

This proves once again that an open source project should have an
extension mechanisms so that
inessential contributions, like this, could be kept separate from the
product itself but could still be
easily available for those who happen to find it useful.

Personally I don't see how it is useful to be able to write the same
thing in yet another syntax.
But that's just me and my situation.

- Rami

[Jesse Long]

On 05/11/2010 13:42, Rami Ojares wrote:
> This proves once again that an open source project should have an
> extension mechanisms so that
> inessential contributions, like this, could be kept separate from the
> product itself but could still be
> easily available for those who happen to find it useful.

Agreed, I vote for the diff/patch framework.

Cheers,
Jesse

[Kerry Sainsbury]

I'm quite sure it must be slower, but I haven't measured anything.
I'll put something together and see.

> --
> 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.
>
>

[Kerry Sainsbury]

Agreed. I've tried to make the code plug-and-playable, so it should
fit into any such framework easily.

Of course as it stands now you can run H2 standalone, without the
Groovy jar (or anything else), and it'll still behave as it always
has.

[Dario Fassi]

Hi Kerry,
I take a little time to see your patch/extensions and seems there are interesting things.

About MODULE implementation, many has been said and I still think that is a needed feature to let proper entity to user code as a database object with multiple possible purposes that deserve been added to the core.
New FunctionAlias class with LanguageRunner* look cleaner more extensible and elegant but IMHO may be need careful testing in terms of performance and possible side effects before added to the core.

BSF languages support, look very nice and I'm sure that many people can found very desirable, but I agree with others proposal to be a "contrib" like optional extension.
About triggers in particular I wish to have a more complete and feature rich java implementation before BSF but that's only my wish.
Some performance comparison will be very informative too.

Good job and thanks for share your work.
regards,
Dario

[Kerry Sainsbury]

I did a quick test, and my Groovy trigger implementation is a bit more
than twice as slow as the Java implementation.

On my machine 10000 executions of the trigger in Groovy took approx
1546ms, and 704ms in Java. Of course the Groovy code is actually doing
more, because there's a cost to be paid if you want the less brittle
syntax it offers.

Cheers
Kerry

[Thomas Mueller]

Hi,

I think it's generally a good feature to have. The patch is a bit
large however, so it's not so easy for me to apply it. To review it,
it will take quite a long time. The general approach looks good (but I
didn't apply the patch on my side yet). It's also very good to have
test cases.

If it runs out the h2.jar file gets too large because of this change,
I think we would need "conditional compilation" (as already used for a
few features, using "//#"), plus maybe filtering classes when creating
the jar file.

There is a bit "magic" in the form of source code templates
(automatically adding a header and a footer to the source code), that
already exists for Java functions. Maybe using resources instead of
hardcoding the templates would simplify things. Similar to the .jsp
files for the H2 Console.

There are multiple ways to continue now. One is: I apply the patch and
go through all the changes. This will take quite a long time I guess.
Another approach is: split the patch into multiple smaller patches.
One patch contains just the refactorings (Utils.java to
ClassUtils.java). Another patch possibly adding support for Groovy. It
doesn't matter that much how to split the patch, only that each
individual patch should result in a complete and testable project.

Regards,
Thomas

[Kerry Sainsbury]

Hi Thomas,

I'll go ahead and tidy it up a bit, and see if I can break it down
into smaller patches, although that might be tricky. Mostly the change
is about rearranging the code to make it "language-pluggable" --
"Groovy" support is wrapped up in a couple of independent files.

Cheers
Kerry

I had already intended to use resource files :-)