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(a)hibernate.org&gt; 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(a)hibernate.org&gt; > http://hibernate.org > > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev >

From my understanding the issue is that using the character

Anyway, the discussion here wrt this UUID project is all about generating the UUID value in Java. The first point is to determine whether there is any benefit to relying on the database to generate these for us in the cases when they can. Secondly there is the question of if we are going to do it in Java then what is the correct approach. Both libraries mentioned here (UUID and JUG) use MAC address resolution in building the uuid value. JUG is updating to use java.util.UUID, but relies on native code for mac-address-resoltion; UUID use its own UUID class but does not need native code for the mac-address-resolution (it uses OS-sniffing and system calls). Heck maybe we offer this as a strategy configurable with the Configuration/SessionFactory. interface UUIDGenerationStrategy extends Serializable { public int getGenerationVersion(); // informational public UUID generateUUID(SessionImplementor session); } But first things first... Do we use database uuid generation if supported? Is that itself perhaps just a strategy? On Thu, 2010-05-27 at 06:12 -0500, Steve Ebersole wrote: > The site is not just about the UUID generator project. He wrote posts > on ohter subjects as well. > > My point there is more to the maven repo. How do we reference this? > Are we ending up hosting another artifact? How do we even contact > him to find out? > > > > > > -- Sent from my Palm Pre > > steve(a)hibernate.org > http://hibernate.org > ______________________________________________________________________ > On May 27, 2010 1:28 AM, Emmanuel Bernard <emmanuel(a)hibernate.org&gt; > wrote: > > To his credit a UUID impl is not a 10 year project plan ;) I would not > necessarily consider it abandonware rather than done. > > On 27 mai 2010, at 03:13, Steve Ebersole wrote: > > > and the latest posts on that website are > > from 2007. > > > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev

While the character representation is less efficient, it is much more readable for humans using SQL consoles or other non Hibernate layers. On 28 mai 2010, at 21:23, Steve Ebersole wrote: > I am pretty much done with the plumbing for this UUID support. > > However... > > Another difficulty is the standard database type to which to map UUIDs. > BINARY(16) or CHAR(36) are the most common datatypes used that I have > seen. I have also seen strategies using NUMERIC and two INTEGER values > (splitting least/most significant bits); the later is not a viable > option though for the built-in support. > > I have never used UUIDs as PK values so I am far from an expert here. >> From my understanding the issue is that using the character > representation not only wastes storage but also makes indexing and > joining much slower. And do all databases support NUMERIC types large > enough? > > Personally, I say we use BINARY(16) for the standard support. WDYT? > > > On Thu, 2010-05-27 at 07:01 -0500, Steve Ebersole wrote: >> Anyway, the discussion here wrt this UUID project is all about >> generating the UUID value in Java. The first point is to determine >> whether there is any benefit to relying on the database to generate >> these for us in the cases when they can. >> >> Secondly there is the question of if we are going to do it in Java then >> what is the correct approach. Both libraries mentioned here (UUID and >> JUG) use MAC address resolution in building the uuid value. JUG is >> updating to use java.util.UUID, but relies on native code for >> mac-address-resoltion; UUID use its own UUID class but does not need >> native code for the mac-address-resolution (it uses OS-sniffing and >> system calls). Heck maybe we offer this as a strategy configurable with >> the Configuration/SessionFactory. >> >> interface UUIDGenerationStrategy extends Serializable { >> public int getGenerationVersion(); // informational >> public UUID generateUUID(SessionImplementor session); >> } >> >> But first things first... >> >> Do we use database uuid generation if supported? Is that itself perhaps >> just a strategy? >> >> >> On Thu, 2010-05-27 at 06:12 -0500, Steve Ebersole wrote: >>> The site is not just about the UUID generator project. He wrote posts >>> on ohter subjects as well. >>> >>> My point there is more to the maven repo. How do we reference this? >>> Are we ending up hosting another artifact? How do we even contact >>> him to find out? >>> >>> >>> >>> >>> >>> -- Sent from my Palm Pre >>> >>> steve(a)hibernate.org >>> http://hibernate.org >>> ______________________________________________________________________ >>> On May 27, 2010 1:28 AM, Emmanuel Bernard <emmanuel(a)hibernate.org&gt; >>> wrote: >>> >>> To his credit a UUID impl is not a 10 year project plan ;) I would not >>> necessarily consider it abandonware rather than done. >>> >>> On 27 mai 2010, at 03:13, Steve Ebersole wrote: >>> >>>> and the latest posts on that website are >>>> from 2007. >>> >>> >>> _______________________________________________ >>> hibernate-dev mailing list >>> hibernate-dev(a)lists.jboss.org >>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >> >> > > > -- > Steve Ebersole <steve(a)hibernate.org&gt; > http://hibernate.org > > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev