[hibernate-dev] Database with UUID support

[Steve Ebersole]

In regards to
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3579 I am
trying to put together the best all around support for java.util.UUID we
can as a type.  

Obviously most databases "support uuid" via char/varchar data type.
Postgresql being the lone exception of which I am aware with their
special UUID datatype, even though they choose to map it to
java.sql.Types.OTHER (don't get me started).  So for sure we can support
UUID in every database.

The next logical thought was supporting UUID as an identifier value.  If
we were to add that what I would really prefer is a way to obtain the
UUID ahead of time (pre-insert) which would mean knowing what databases
supported a UUID-generation function and, for those that did, what that
function is.  For those that do not, is using
java.util.UUID.randomUUID() a viable alternative?

A list of major databases and their support/non-support for generating
UUID values that I know of are as follows:

1) postgresql - database itself does not support generation, instead
relying on an optional module called uuid-ossp
2) mysql - supports generation via the UUID() function
3) h2 - supports generation via the RANDOM_UUID()
4) hsqldb - does not support generation afaict
5) derby - does not support generation afaict
6) db2 - does have a GENERATE_UNIQUE() function; was not able to tell if
that is compatible with java.util.UUID
7) firebird - supports generation via GEN_UUID()
8) informix - does not support generation afaict
9) ingres - supports via UUID_CREATE()
10) oracle - supports via SYS_GUID()
11) maxdb (sapdb) - does not support generation afaict
12) mssql (sql server) - does have a NEWID() function, though it
generates a string with hyphons in it; not sure how/if this value
relates to java.util.UUID
13) sybase - like mssql also has a NEWID() function which here can
additionally be given a parameter to say whether to include hyphons etc;
again, not sure how/if this value relates to java.util.UUID
14) terradata - does not support generation afaict

DISCLAIMER : When I say "does not support" I mean in the base server
install.  As I pointed out with postgresql I understand some might have
additional optional or third-party modules that add such capabilities


So by my count, that means that we can have pretty complete support for
UUID on the following databases without any extra requirements:
a) mysql
b) h2
c) firebird
d) ingres
e) oracle

Databases with potential support:
a) postgresql - is it reasonable to require this uuid-ossp module?
b) db2 - what is the compatibility of its GENERATE_UNIQUE() function
with java.util.UUID?
c) mssql/sybase - what is the compatibility of NEWID() function with
with java.util.UUID?

For databases which do not support UUID generation, the question becomes
how do we support that in hibernate for identifiers?  Do we rely on
java.util.UUID.randomUUID()?  Do you use a special generator like
http://jug.safehaus.org/ ?  Other options?

Also, if anyone notices issues in my db support investigations please
let me know.

-- 
Steve Ebersole <steve at hibernate.org>
http://hibernate.org