MySQL GUID stored as BINARY(16)

[Tristan Lee]

We're in the process of transitioning our CHAR(36) columns that store a GUID to BINARY(16) in MySQL. Currently, our entities are defined like so:

component persistent="true" table="tmpTest" {
property name="ID" fieldType="id" generator="guid";
property name="Name";
}

With the change to BINARY, I've been reading the Hibernate forums and comparing different "ormtype" values to find out which combinating will correctly convert the string representation of the GUID to binary format when a record is inserted. On Oracle, this seems to be handled internally with the RAW(16) columns we use, but when trying the same with MySQL's BINARY(16) datatype, it's not so smooth.

I've tried a few combinations of attributes for the ID column. Unfortunately, none of them worked, but here's the definition I am expecting:

component persistent="true" table="tmpTest" {
property name="ID" fieldType="id" ormtype="binary" length="16" generator="guid";
property name="Name";
}

When inserting, I get the error "java.lang.String cannot be cast to [B". In MySQL, you typically use HEX() and UNHEX() functions to convert the value from binary to String. Can this be handled internally with Hibernate and CF's implementation?

[Cameron Childress]

On Mon, Feb 23, 2015 at 9:35 AM, Tristan Lee wrote:

We're in the process of transitioning our CHAR(36) columns that store a GUID to BINARY(16) in MySQL. Currently, our entities are defined like so:

Why?

As far as I know, GUID is text data and Hibernate will want to store it as a string. You are better off leaving the database as a char or varchar field of some sort if you want to use Hibernate and ORM without making your life very difficult.

There may be some valid reason to convert it to binary that I am not aware of. If there is, then you are most likely going to have to change your PK to assigned, and then generate the PK yourself using the ORM preInsert() event or some other mechanism. However, this is very likely to make everything you do an order of magnitude more difficult for no good reason.

-Cameron

-- 

Cameron Childress
--
p:   678.637.5072

im: cameroncf

facebook | twitter | google+

[Brian Kotek]

I'm pretty sure Hibernate added a binary uuid generator, but I don't think CF support it yet. And even if it did, a UUID isn't a GUID, so I don't think that would work anyway.

--
You received this message because you are subscribed to the Google Groups "cf-orm-dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cf-orm-dev+...@googlegroups.com.
To post to this group, send email to cf-or...@googlegroups.com.
Visit this group at http://groups.google.com/group/cf-orm-dev.
For more options, visit https://groups.google.com/d/optout.

[Cameron Childress]

On Mon, Feb 23, 2015 at 11:29 AM, Brian Kotek wrote:

I'm pretty sure Hibernate added a binary uuid generator, but I don't think CF support it yet. And even if it did, a UUID isn't a GUID, so I don't think that would work anyway.

Huh. Interesting. Perhaps it could be configured by writing the HBML files manually then. I still think that would be a pretty painful maintenance task to inflict on oneself unless no other option.

-Cameron

 

[Tristan Lee]

The reason being for performance reasons. I am opposed to the idea as CHAR is working just fine for us, and the small amount of records in these tables will not be affected by the additional length of a CHAR, and currently there's no difference in read times. Unfortunately, the change is not my call.

You are correct about the complications this brings. Fortunately, I have facilitated a save() method that all entities inherit and that's how MOST entities are inserted/updated. Like you mentioned, I had set the generator to "assigned" and create my UUIDs manually and do their binary conversion. This works great for inserting. Now even nastier conversions need done just to read data. I'd say likely entityloadByPK() will no longer work.

public struct function save () {
var results = validate();

if (results.isValid) {

// convert the ID if it's MySQL BINARY
if (structKeyExists(getIdentityProperty(), "mysqltype") && getIdentityProperty().mysqltype == "binary") {
var uuid = createObject("java", "java.util.UUID");
var bb = createObject("java", "java.nio.ByteBuffer").allocate(16);
var id = "";
var isNewEntity = isNull(getIdentityValue());

// assign new UUID if it's not set
if (isNull(getIdentityValue())) {
id = uuid.randomUUID();

// convert from existing binary representation
} else if (isBinary(getIdentityValue())) {
id = uuid.nameUUIDFromBytes(getIdentityValue());

// convert from existing string representation
} else {
id = uuid.fromString(getIdentityValue());
}

bb.putLong(id.getMostSignificantBits()).putLong(id.getLeastSignificantBits());
local.methodName = this["set#getIdentityPropertyName()#"];
methodName(bb.array());
}

transaction {
if (isNewEntity) {
ormGetSession().save(this, getIdentityValue());
} else {
entitySave(this);
}
}

if (structKeyExists(getIdentityProperty(), "mysqltype") && getIdentityProperty().mysqltype == "binary" && isBinary(getIdentityValue())) {
local.methodName = this["set#getIdentityPropertyName()#"];
methodName(createObject("java", "java.util.UUID").nameUUIDFromBytes(getIdentityValue()).toString());
}
}

return results;
}

[Tristan Lee]

I did notice they implemented it and one example I saw being used in JPA was 'type="uuid-binary"'. That didn't work for me in Railo. However, digging deeper I see the class for it is: org.hibernate.type.UUIDBinaryType. So far, this seems to work in Railo:

component persistent="true" table="tmpTest" extends="models.BaseEntity" {
property name="ID" fieldType="id" ormtype="org.hibernate.type.UUIDBinaryType" generator="uuid";
property name="Name";
}

Dumping the entity, my ID is a UUID. In the DB, it's binary. EntityLoadByPK() with the UUID as a string pulls back the entity correctly.

I'm anxious to see how this behaves on ACF though...

[Tristan Lee]

Looks like those ormtype="uuid-binary" and ormtype="org.hibernate.type.UUIDBinaryType" value types are only available in Hibernate 3.6, where as CF10 has 3.5.2.