MySQL compatibility: Date function implementations for H2
jasonb
I'm porting an application to H2 that was initially written for MySQL
(the queries contain some MySQL-specific syntax). Probably the most
challenging part of getting the queries running on H2 has been the use
of the MySQL date functions like DATE(), UNIX_TIMESTAMP(), and
FROM_UNIXTIME(). The good news is: because H2 supports creating
aliases for these, I was able to write a very small Java class that
implements these MySQL functions for H2, and once I aliased them into
place, H2 runs just about all of our queries without modification.
Suggestion: The doc page about CREATE ALIAS should really also state
that the Java method you name *must* be a static method. If it isn't
a static method, H2 says that it isn't found, which is difficult to
debug.
Would it be possible to include my implementation of the MySQL
functions class either in h2.jar or on the web site so that others can
use it? It's currently 38 lines of Java code, including some blank
lines. It makes H2 easier to get working with an application that was
initially written for MySQL like the one I'm porting, which I believe
to be pretty common.
Thanks.
--
Jason Brittain
Co-Author, Tomcat: The Definitive Guide, 2nd Edition (O'Reilly)
jasonb
Thomas Mueller
> Suggestion: The doc page about CREATE ALIAS should really also state
> that the Java method you name *must* be a static method. If it isn't
> a static method, H2 says that it isn't found, which is difficult to
> debug.
You are right, I will add "The class and the method must both be
public, and the method must be static." I will also create a new error
message for this case: "The public static Java method was not found\:
{0}"
> Would it be possible to include my implementation of the MySQL
> functions class either in h2.jar or on the web site so that others can
> use it? It's currently 38 lines of Java code, including some blank
> lines. It makes H2 easier to get working with an application that was
> initially written for MySQL like the one I'm porting, which I believe
> to be pretty common.
Why don't you post it? There are two options: one is to include it in
the H2 core, the other is to create some kind of 'plugin library'
section so that people can add the features when required. This plugin
library could still be included in the H2 download, but wouldn't be
included in the h2.jar file by default. What do you think?
Regards,
Thomas
jasonb
On Aug 14, 9:41 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
>
> > Suggestion: The doc page about CREATE ALIAS should really also state
> > that the Java method you name *must* be a static method. If it isn't
> > a static method, H2 says that it isn't found, which is difficult to
> > debug.
>
> You are right, I will add "The class and the method must both be
> public, and the method must be static." I will also create a new error
> message for this case: "The public static Java method was not found\:
> {0}"
> > Would it be possible to include my implementation of the MySQL
> > functions class either in h2.jar or on the web site so that others can
> > use it? It's currently 38 lines of Java code, including some blank
> > lines. It makes H2 easier to get working with an application that was
> > initially written for MySQL like the one I'm porting, which I believe
> > to be pretty common.
>
> Why don't you post it? There are two options: one is to include it in
> the H2 core, the other is to create some kind of 'plugin library'
> section so that people can add the features when required. This plugin
> library could still be included in the H2 download, but wouldn't be
> included in the h2.jar file by default. What do you think?
the behavior of the plugin would need to depend on the compatibility
mode being used at the time, if any. The way I see the implementation
for the function UNIX_TIMESTAMP() is that lots of MySQL-specific
queries probably use it, and whenever H2 is using the MySQL
compatibility mode, it should be available (by default, without the H2
administrator needing to do anything). Some other MySQL-specific
functions may be less used, so it may be less critical to have them in
the j2.jar, but how compatible is H2 trying to be? :) Same goes with
all of the other compatibility modes. Really, it would be great if it
was easy for outsiders to contribute code for the compatibility modes,
which is probably the feature that draws me into H2 the most.
I'll try to post the code here today so that it may be included. What
license & copyright should I put on it? This is such a small amount
of code, and I'd like to contribute to H2, so it's whatever you
choose.
Thanks!
--
Jason
jasonb
I uploaded my class to the files area here. I probably should have
named it "MySQLFunctions". Anyway, I added Javadocs to it as well.
The DATE() function is not correctly implemented, but the
implementations as they are in the class right now allow our MySQL-
developed webapp to run against H2. Let me know what you think.
By the way, the docs for CREATE ALIAS says that it is possible to
create multiple aliases for the same function name, calling different
Java methods. I wasn't able to get that to work. I could only create
one alias at a time for the same function name. On the second CREATE
ALIAS command, going to my second fromUnixTime2() method like this:
CREATE ALIAS FROM_UNIXTIME FOR
"org.h2.util.H2MySQLFunctions.fromUnixTime2(int, java.lang.String)";
I got this error:
Function alias FROM_UNIXTIME already exists [90076-76] 90076/90076
--
Jason
Thomas Mueller
> I'm not sure if the behavior of the plugin would need to depend on the compatibility mode being used at the time, if any.
Not necessarily, but the mode could be used to load classes (if they
are in the classpath).
> by default, without the H2 administrator needing to do anything
Some people are concerned a lot about jar file size. H2 needs to be
more modular. The build tool will support 'custom' builds using flags
/ settings. I think the name of those custom jar files should contain
the settings used. Example: 'my' for MySQL, 'pg' for PostgreSQL, 'deb'
for debug, 'c' for H2 Console. So the jar file h2-s-my-pg-c-deb.jar
would mean: H2 with MySQL and PostgreSQL compatibility, with H2
Console, with debug info. There would be a set of abbreviations for
common settings.
> how compatible is H2 trying to be? :)
As compatible as people want it to be.
> it would be great if it was easy for outsiders to contribute code for the compatibility modes.
Yes. Currently, H2 is extensible, but extending is not easy enough.
This needs to change, I just don't know how yet. What do you suggest?
> What license & copyright should I put on it
I think the regular H2 license makes sense (for now).
> CREATE ALIAS says that it is possible to
> create multiple aliases for the same function name, calling different
> Java methods. I wasn't able to get that to work.
You only need to call CREATE ALIAS once, and all methods with this
name are registered. I will improve the docs:
"If the class contains multiple methods with the given name but
different parameter count, all methods are mapped."
I have improved (I hope) your code a bit. Also, I have added some test
cases in TestCompatibility.testMySQL. This is already in the source
code repository and will be included in the next release. They are not
in the h2.jar file currently, but in
src/tools/org/h2/mode/FunctionsMySQL.java. To install, add this class
to the classpath and call FunctionsMySQL.register(conn) in the Java
code. See:
http://code.google.com/p/h2database/source/browse/trunk/h2/src/tools/org/h2/mode/FunctionsMySQL.java
http://code.google.com/p/h2database/source/browse/trunk/h2/src/test/org/h2/test/db/TestCompatibility.java
Regards,
Thomas
jasonb
On Aug 19, 7:32 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
[snip]
> > by default, without the H2 administrator needing to do anything
>
> Some people are concerned a lot about jar file size. H2 needs to be
> more modular. The build tool will support 'custom' builds using flags
> / settings. I think the name of those custom jar files should contain
> the settings used. Example: 'my' for MySQL, 'pg' for PostgreSQL, 'deb'
> for debug, 'c' for H2 Console. So the jar file h2-s-my-pg-c-deb.jar
> would mean: H2 with MySQL and PostgreSQL compatibility, with H2
> Console, with debug info. There would be a set of abbreviations for
> common settings.
to build it from source to get a smaller jar, though. If all of the
features were simply whole classes to be included or not included, you
could release a single jar containing all of the classes, and an
executable jar command could be used to output a new jar that contains
a subset of the classes, selected by feature options, and named like
you described above.. that way nobody would need to even have a source
tree to get a smaller, more customized H2 jar. Just an idea. :)
> > how compatible is H2 trying to be? :)
>
> As compatible as people want it to be.
seem.
> > it would be great if it was easy for outsiders to contribute code for the compatibility modes.
>
> Yes. Currently, H2 is extensible, but extending is not easy enough.
> This needs to change, I just don't know how yet. What do you suggest?
H2 web site that is focused on encouraging code contributions to make
H2 compatibility modes more complete. It's pretty easy to contribute
something small like the functions I contributed, so a page showing
how that works and how to submit it for inclusion would prompt more
developers to contribute some code. Deeper changes would probably be
more rare.. and making that easier is the hard part. But, it seems
that there are numerous db impl-specific functions that are
individually easy to implement, and each improve compatibility a
little.
[snip]
> > CREATE ALIAS says that it is possible to
> > create multiple aliases for the same function name, calling different
> > Java methods. I wasn't able to get that to work.
>
> You only need to call CREATE ALIAS once, and all methods with this
> name are registered
> I will improve the docs:
> "If the class contains multiple methods with the given name but
> different parameter count, all methods are mapped."
(taking types into account)?
If it's the latter, how about "If the class contains more than one
method with the given name but with different arguments, all methods
are mapped with a single CREATE ALIAS statement."
> I have improved (I hope) your code a bit.
> Also, I have added some test cases in TestCompatibility.testMySQL.
Why doesn't testMySQL call stat.execute("SET MODE MySQL"); near the
top? Shouldn't these functions only be available in MySQL mode?
Like, if the class is available, these functions should get activated
when the mode is set to MySQL, but only then. Right?
> This is already in the source
> code repository and will be included in the next release. They are not
> in the h2.jar file currently, but in
> src/tools/org/h2/mode/FunctionsMySQL.java. To install, add this class
> to the classpath and call FunctionsMySQL.register(conn) in the Java
Okay. I'm not sure how other functions end up being used when they're
part of the h2.jar, so when these new classes are in the jar, then how
do they get registered? My db client could issue CREATE ALIAS
commands to register them if that's required, but I'm hoping to just
use the ";mode=mysql" JDBC URL parameter.
Thanks for all of your work on this!
--
Jason
Thomas Mueller
> how much people will accept needing
> to build it from source to get a smaller jar, though.
Good question. The most important jar files should be available for
download. For most people jar file size is not such a big problem (at
least if the jar file is only 1 MB). A few want a smaller jar file,
and for those few, every KB counts. I doubt they all want the same
features however. At the moment I can only guess what features need to
be included in this small jar file.
> If all of the
> features were simply whole classes to be included or not included, you
> could release a single jar containing all of the classes, and an
> executable jar command could be used to output a new jar that contains
> a subset of the classes, selected by feature options, and named like
> you described above.. that way nobody would need to even have a source
> tree to get a smaller, more customized H2 jar. Just an idea. :)
Good idea. There is one option that can't be supported in this way:
disabling debug info.
> have a section of the
> H2 web site that is focused on encouraging code contributions to make
> H2 compatibility modes more complete. It's pretty easy to contribute
> something small like the functions I contributed, so a page showing
> how that works and how to submit it for inclusion would prompt more
> developers to contribute some code.
The is no page yet on 'how to contribute' but there is
http://www.h2database.com/html/build.html#providing_patches
> Deeper changes would probably be
> more rare.. and making that easier is the hard part.
Actually there are quite a few people who provide bigger patches. So
far I am the only commiter because nobody wanted to commit himself,
and because I am quite picky about the quality - or what I think is
quality :-) I'm not sure yet if I really want to make it easier to
commit changes.
> Is it just the number of parameters? Or, a different signature
> (taking types into account)?
No, only number of parameters at the moment. However the next version
will support var args (when using Java 1.5 or newer).
> Why doesn't testMySQL call stat.execute("SET MODE MySQL"); near the
> top? Shouldn't these functions only be available in MySQL mode?
> Like, if the class is available, these functions should get activated
> when the mode is set to MySQL, but only then. Right?
Currently the functions don't get registered automatically, you need
to call org.h2.mode.FunctionsMySQL.register(conn). This could be done
automatically (using reflection) in the future. I like to wait
implementing this however until more people request this feature. It
sounds like it's the right solution but I'm not sure yet.
Change log entry for the next release: "Jason Brittain has contributed
MySQL date functions. Thanks a lot! They are not in the h2.jar file
currently, but in src/tools/org/h2/mode/FunctionsMySQL.java. To
install, add this class to the classpath and call
FunctionsMySQL.register(conn) in the Java code."
Regards,
Thomas