“it could be bunnies”

In part 1 I showed how to adapt ColdFusion to work with PostgreSQLs native UUID datatype. In this part, I will show how to adapt PostgreSQL to accept ColdFusions native UUID datatype.

UUIDs: equal, but not the same

As ColdFusion developers are very aware thestring representation of a UUID in ColdFusionis a 35 character string. However, the rest of the world has decided on a 36 character string representation. Both in standards (RFC 4122, ITU-T X.667 and The Open Group) as in implementations (MS SQL Servers newID(), Javas java.util.UUID etc.). PostgreSQL is no exception and uses the 36 character string representation. As a result, the output of the ColdFusion CreateUUID() function will not be accepted into a PostgreSQL UUID field and the output of a PostgreSQL UUID field will not be recognized by ColdFusion as a UUID. I actually believe this is a bug in ColdFusions type detection and conversion system. Since CFML is a typeless language, it should just convert both the 35 and the 36 character string representation. ColdFusion will on the fly convert both a m/d/yy string and a yyyy-mm-dd string to a date object, so why not convert both UUID string representations to their native integer representation?

Patching PostgreSQL

Unfortunately ColdFusion is a closed source product and I can not adapt it to work the way I want it to work. However, PostgreSQL is Open Source and I can adapt it. A little digging in the source code reveals that the input and output formats for the UUID datatype are defined in uuid.c and it is trivial to adapt them to use the ColdFusion format. Just apply the patch for PostgreSQL 8.3 to accept a 35-character string representation as UUID and recompile PostgreSQL. Once you have done that, the following code will work:

CREATE TABLE test (testID UUID);

<cfquery>
INSERT INTO test (testID)
VALUES (<cfqueryparam cfsqltype="cf_sql_other" value="#CreateUUID()#">)
</cfquery>

Warnings:

• c is not a language I am comfortable in so the code is probably suboptimal;
• I do not intend to submit this as a patch to PostgreSQL since they follow all the standards and ColdFusion does not;
• a permanent solution to the problem of ColdFusion UUIDs clashing with the rest of the world should come from Adobe.

So to go from ColdFusion to PostgreSQL we now can use the 36-character representation as an inline variable, with the updated JDBC driver as a bind variable, and with this patch also using a 35-character string. What is left to do is patching the JDBC driver to convert the 16-byte representation it receives from PostgreSQL to a 35-character string ColdFusion understand. Looks like that requires writing a new type. Unfortunately I will be busy for the rest of the month with a new three week Flex/LiveCycle class we will be teaching so I won’t be able to start on that for a while.