MD5 Function

[Ben Hood]

Hi,

I was wondering whether there is a factory method to call an MD5() string function in a query. Natively I was thinking of the following SQL:

select year(some_date), md5(group_concat(some_column))

from some_table

group by year(some_date)

The generic Factory class offers functions to generate year() and group_concat(), but I couldn't see one for md5().

How should I proceed?

TIA,

Cheers,

Ben 

[Lukas Eder]

I'm guessing that this is about MySQL? Have a look at MySQLFactory, then:
http://www.jooq.org/javadoc/latest/org/jooq/util/mysql/MySQLFactory.html
http://www.jooq.org/javadoc/latest/org/jooq/util/mysql/MySQLFactory.html#md5(org.jooq.Field)

Cheers
Lukas

2012/11/21 Ben Hood <0x6e...@gmail.com>:

[Ben Hood]

Actually I was hoping for Oracle, MySQL and HSQL support :-)

So the MySQLFactory looks like a good solution for MySQL, but I guess that I going to have to look at a way of executing the native queries on a dialect by dialect basis. Is this something that could be patched into the default Factory and implemented separately for each DB you want to support? 

[Lukas Eder]

I see, I wasn't aware of other databases shipping with similar
functions. Let's pull this method up to Factory, then:
https://github.com/jOOQ/jOOQ/issues/1972

So, Oracle has some MD5 function:
LOWER(RAWTOHEX(SYS.DBMS_OBFUSCATION_TOOLKIT.MD5(input_string=>'mypass')))

How would you generically go about this for HSQLDB (without creating a
UDF, of course)?

2012/11/21 Ben Hood <0x6e...@gmail.com>:

[Lukas Eder]

Note that Oracle's DBMS_OBFUSCATION_TOOLKIT package seems to offer
other hashing algorithms as well, e.g. DES. I might consider moving
more functions up from MySQLFactory to Factory

2012/11/21 Lukas Eder <lukas...@gmail.com>:

[Ben Hood]

On Wed, Nov 21, 2012 at 2:29 PM, Lukas Eder <lukas...@gmail.com> wrote:

I see, I wasn't aware of other databases shipping with similar
functions. Let's pull this method up to Factory, then:
https://github.com/jOOQ/jOOQ/issues/1972

Cool :-)

 

How would you generically go about this for HSQLDB (without creating a
UDF, of course)?

Very good point - I was just going to resort to creating a UDF to call out to Apache Commons DigestUtils ...... 

[Lukas Eder]

>> How would you generically go about this for HSQLDB (without creating a
>> UDF, of course)?
>
> Very good point - I was just going to resort to creating a UDF to call out
> to Apache Commons DigestUtils ......

Well, I found this answer by Fred Toussi, the HSQLDB developer, here:
http://stackoverflow.com/a/10777312/521799

Of course, such a solution is not viable for jOOQ's core. So if you
want this to work for HSQLDB as well, you'll have to provide your own
abstraction. A good way to do this is by implementing a CustomField:
http://www.jooq.org/doc/2.6/manual/sql-building/queryparts/custom-queryparts/

You then have full control over its SQL rendering and variable
binding, depending on the context's SQLDialect.

[Ben Hood]

Thanks a lot for the heads up about the HSQL library to generate a digest and how to implement a CustomField - I'll check it out.

(And as ever, thanks for responding so quickly :-)

[Lukas Eder]

MD5 function support is now simulated for Oracle/MySQL. This is now
committed to GitHub:
https://github.com/jOOQ/jOOQ/issues/1972

Cheers
Lukas

2012/11/21 Ben Hood <0x6e...@gmail.com>:

[Ben Hood]

Very cool! Thanks a lot for putting the effort into this one.

Would the factory also generate a md5() function for HSQL (assuming that you've tweaked your HSQL with a function called md5 that takes a varchar)?

Also, and only FWIW, I've noticed with Oracle that dbms_obfuscation_toolkit has a 32K varchar input limit, where dbms_crypto can operate on lobs.

[Lukas Eder]

> Would the factory also generate a md5() function for HSQL (assuming that
> you've tweaked your HSQL with a function called md5 that takes a varchar)?

Yes, the default function name rendered by org.jooq.impl.MD5 will be
"md5". It will work, although this might (improbably) change in the
future.

> Also, and only FWIW, I've noticed with Oracle that dbms_obfuscation_toolkit
> has a 32K varchar input limit, where dbms_crypto can operate on lobs.

Nice hint. DBMS_CRYPTO also supports a lot more algorithms. I'll fix that

Cheers
Lukas