[hibernate-dev] Database with UUID support

[Cody Lerum]

I prefer time based (version 1) UUID due the insert performance of random ID's.

Though that sends you down the road of using a third party generator.
FWIW http://johannburkard.de/software/uuid/ is recommended on the
Apache Cassandra wiki.

On Wed, May 26, 2010 at 2:24 PM, Steve Ebersole <steve at hibernate.org> wrote:
> 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
>
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>