Errors with PostgreSQL 8.3 UUID data type and Transfer 1.1

[Jamie Krug]

Sorry in advance for a lengthy one...

Using the UUID data type in PostgreSQL 8.3 (http://www.postgresql.org/
docs/8.3/interactive/datatype-uuid.html) is problematic with
cfqueryparam. There is no explicit cfsqltype, and using cf_sql_varchar
will cause the following error when attempting a query like this:

select * from my_table
where my_uuid=<cfqueryparam value="#my_uuid_value"
cfsqltype="cf_sql_varchar" />

org.postgresql.util.PSQLException: ERROR: operator does not exist:
uuid = character varying

I stumbled upon this post, which fixes a similar issue regarding the
interval data type:
http://www.ghidinelli.com/2007/06/14/postgresql-interval-data-type-with-cfqueryparam
(Here's the important part:)
UPDATE table
SET someInterval = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#interalValue#">::interval

So I tried sticking a "::uuid" on the end of my cfqueryparam and it
works! (But don't stop reading now, I found another solution and then
another problem...)

By the way, I should note that I'm using property type "GUID" in my
Transfer configuration file, not UUID, even though that is the
PostgreSQL data type name. The standard output format used by
PostgreSQL for UUID data type matches what is considered GUID format
in a Transfer property type.

I couldn't find any mention of this issue on this list or noted in the
Transfer issue tracker, but please let me know if I've missed
something.

So, I thought I had a quick fix... I believe I've identified all
relevant cfqueryparam instances in the Transfer 1.1 code (just 4 of
them, in 3 files). I saved the diff/patch for the fix if anyone cares.
It's just a conditional at the end of the cfqueryparam tags used for
Transfer property types that currently result in use of
cfsqltype="cf_sql_varchar". This made things work for simple object
definitions (no onetomany/manytoone/manytomany relationships).

A less quirky solution to the cfqueryparam/uuid issue is to simply
download the latest PostgreSQL JDBC drivers, replace your current .jar
file and restart CF (got this idea from
http://jochem.vandieten.net/2008/02/06/postgresql-uuids-and-coldfusion-1/).
I downloaded postgresql-8.3-603.jdbc4.jar from http://jdbc.postgresql.org/download.html,
placed it in the proper directory, renamed the existing
postgresql-8.1-407.jdbc4.jar to postgresql-8.1-407.jdbc4.jar.BAK and
restarted CF. (To find your existing postgre driver path click on the
"I" icon for System Information in CF Admin and find "postgre" on the
page -- my default install location for CF8.01 multiple server
configuration on Linux is /opt/jrun4/servers/cfusion/cfusion-ear/
cfusion-war/WEB-INF/cfusion/lib/). I can now use cfqueryparam with
cfsqltype="cf_sql_other" for PostgreSQL UUID data type columns. I also
tried this fix in the Transfer code, but still ran into another
issue...

Unfortunately, the above solutions only worked with simple Transfer
object definitions. As soon as I added a onetomany to a basic object
definition, I received a new error, which also appears to be a type/
cast inconsistency issue:

ERROR: UNION types text and integer cannot be matched

It looks like a CAST(text as NULL) in one part of a UNION is matched
up with an actual column value of data type UUID, which throws the
above error. I haven't dug nearly deep enough into the Transfer code
to know if this is Transfer-related, or just a compatibility issue
between PostgreSQL 8.3 and ColdFusion 8.01 (or more likely, the driver
included w/CF). I've pasted details of my environment, relevant
Transfer config snippets and more details from the error output at the
bottom of this message.

My config in this case is very much like the basic idea of a Post
object with a onetomany to Comment, as in the tBlog sample
application, only I'm using PostgreSQL 8.3 UUID data types for primary
keys instead of numeric.

My immediate goal is to get this relatively small project moving
quickly, so I'm trying to discern whether I should try to work around
the use of UUID data types, not use UUID, not use Transfer (this is my
first real dive in and was excited until this) until I figure the UUID
stuff out, etc. To keep things moving here, I may have to leave out
Transfer, temporarily, for this app.

Has anyone else tackled this problem? Others using PostgreSQL with
Transfer, maybe an older version? Is PostgreSQL 8.3 support in the
Transfer road map yet? Any feedback would be much appreciated.

Thanks,
Jamie

*Dev environment:*

Transfer 1.1 (final)
ColdFusion 8.01, multiple server (JRun) configuration
PostgreSQL 8.3.3
Ubuntu 8.04
Apache 2.2


*Relevant snippets from my transfer.xml:*

<package name="page">
<object name="Page" table="page">
<id name="idpage" type="GUID" />
<property name="isActive" type="boolean"
column="is_active" />
<property name="href" type="string" column="href" />
<property name="anchorText" type="string"
column="anchor_text" />
<property name="anchorTitle" type="string"
column="anchor_title" />
<onetomany name="ContentPage">
<link to="content.ContentPage" column="idpage" />
<collection type="array">
<order property="IDContent_Page" order="asc" /
>
</collection>
</onetomany>
</object>
</package>
<package name="content">
<object name="Content" table="content">
<id name="idcontent" type="GUID" />
<property name="content" type="string"
column="content" />
</object>
<object name="ContentPage" table="content_page">
<id name="idcontent_page" type="GUID" />
<property name="title" type="string" column="title" /
>
<property name="description" type="string"
column="description" />
<property name="keywords" type="string"
column="keywords" />
<manytoone name="Content">
<link to="content.Content" column="idcontent" />
</manytoone>
</object>
<object name="ContentSnippet" table="content_snippet">
<id name="idcontent_snippet" type="GUID" />
<property name="friendlyID" type="string"
column="friendly_id" />
<manytoone name="Content">
<link to="content.Content" column="idcontent"/>
</manytoone>
</object>
</package>


*Test code:*

<cfscript>
transferConfig = structNew();
transferConfig["datasourcePath"] = "/temp/config/transfer/
datasource.xml";
transferConfig["configPath"] = "/temp/config/transfer/
transfer.xml";
transferConfig["definitionPath"] = "/temp/model/data/transfer";
transferFactory =
createObject("component","transfer.TransferFactory").init(argumentCollection=transferConfig);
transfer = transferFactory.getTransfer();
page = transfer.get('page.Page', '7a85e8c7-ebb7-
f962-0fe4-6043ce7e6721');
</cfscript>
<cfdump var="#page#" />


*Error output:*

Error Executing Database Query.
ERROR: UNION types text and uuid cannot be matched

The error occurred in /home/jkrug/www/common/transfer/com/sql/
QueryExecution.cfc: line 82
Called from /home/jkrug/www/common/transfer/com/sql/
TransferSelecter.cfc: line 53
Called from /home/jkrug/www/common/transfer/com/sql/SQLManager.cfc:
line 87
Called from /home/jkrug/www/common/transfer/com/dynamic/
TransferPopulator.cfc: line 50
Called from /home/jkrug/www/common/transfer/com/dynamic/
DynamicManager.cfc: line 72
Called from /home/jkrug/www/common/transfer/com/Transfer.cfc: line 120
Called from /home/jkrug/www/temp/scribble.cfm: line 10
Called from /home/jkrug/www/common/transfer/com/sql/
QueryExecution.cfc: line 82
Called from /home/jkrug/www/common/transfer/com/sql/
TransferSelecter.cfc: line 53
Called from /home/jkrug/www/common/transfer/com/sql/SQLManager.cfc:
line 87
Called from /home/jkrug/www/common/transfer/com/dynamic/
TransferPopulator.cfc: line 50
Called from /home/jkrug/www/common/transfer/com/dynamic/
DynamicManager.cfc: line 72
Called from /home/jkrug/www/common/transfer/com/Transfer.cfc: line 120
Called from /home/jkrug/www/temp/scribble.cfm: line 10

80 : <cfquery name="queryValue"
datasource="#getDataSource().getName()#"
username="#getDataSource().getUsername()#"
password="#getDataSource().getPassword()#">
81 : <cfset eLen = ArrayLen(evaluation) />
82 : <cfloop from="1" to="#eLen#" index="eCounter">
83 : <cfset block = evaluation[eCounter] />
84 : #PreserveSingleQuotes(block.preSQL)#

SQLSTATE 42804
SQL SELECT page_1.href, page_1.idpage, Cast(NULL as text) as
idcontent_page, Cast(NULL as text) as content, Cast(NULL as text) as
description, Cast(NULL as text) as idcontent, Cast(NULL as text) as
title, page_1.is_active, Cast(NULL as text) as keywords,
page_1.anchor_title, page_1.anchor_text, Cast(NULL as text) as
transfer_parentKey, 1 as transfer_orderIndex,'page.Page' as
transfer_className,'' as transfer_parentClassName,'' as
transfer_parentParentClassName,'' as
transfer_parentCompositeName,'false' as transfer_isArray,'' as
transfer_compositeName, 'false' as transfer_isProxied FROM page page_1
WHERE page_1.idpage = (param 1) AND page_1.idpage IS NOT NULL UNION
ALL SELECT Cast(NULL as text) as href, content_page_2.idpage,
content_page_2.idcontent_page, Cast(NULL as text) as content,
content_page_2.description, Cast(NULL as text) as idcontent,
content_page_2.title, Cast(NULL as boolean) as is_active,
content_page_2.keywords, Cast(NULL as text) as anchor_title, Cast(NULL
as text) as anchor_text, CAST(page_1.idpage as varchar(1000)) as
transfer_parentKey, 2 as transfer_orderIndex,'content.ContentPage' as
transfer_className,'page.Page' as transfer_parentClassName,'' as
transfer_parentParentClassName,'' as
transfer_parentCompositeName,'true' as transfer_isArray,'ContentPage'
as transfer_compositeName, 'false' as transfer_isProxied FROM page
page_1 INNER JOIN content_page content_page_2 ON page_1.idpage =
content_page_2.idpage WHERE page_1.idpage = (param 2) AND
content_page_2.idcontent_page IS NOT NULL UNION ALL SELECT Cast(NULL
as text) as href, Cast(NULL as text) as idpage, Cast(NULL as text) as
idcontent_page, content_3.content, Cast(NULL as text) as description,
content_3.idcontent, Cast(NULL as text) as title, Cast(NULL as
boolean) as is_active, Cast(NULL as text) as keywords, Cast(NULL as
text) as anchor_title, Cast(NULL as text) as anchor_text,
CAST(content_page_2.idcontent_page as varchar(1000)) as
transfer_parentKey, 3 as transfer_orderIndex,'content.Content' as
transfer_className,'content.ContentPage' as
transfer_parentClassName,'page.Page' as
transfer_parentParentClassName,'ContentPage' as
transfer_parentCompositeName,'false' as transfer_isArray,'Content' as
transfer_compositeName, 'false' as transfer_isProxied FROM page page_1
INNER JOIN content_page content_page_2 ON page_1.idpage =
content_page_2.idpage INNER JOIN content content_3 ON
content_page_2.idcontent = content_3.idcontent WHERE page_1.idpage =
(param 3) AND content_3.idcontent IS NOT NULL ORDER BY
transfer_orderIndex ASC, idcontent_page asc

[Mark Mandel]

There is an enhancement in the tracker to allow you to extend property
definitions to use your own custom query params, with prefixes,
suffixes, etc

http://tracker.transfer-orm.com/issue.cfm?p=89977683-A728-9CD3-ABD9545A91734422&i=0DCDC600-91FC-A244-C3114EB37C2EDD1C

It has yet to be implemented.

Mark

--
E: mark....@gmail.com
W: www.compoundtheory.com

[Jamie Krug]

Okay, thanks, Mark. I'll hold off for now, but if I have some time, I
may try to hack just a little further to get things working w/
PostgreSQL 8.3. I'm just concerned that after I address the current
error, I'll hit another, and then another... Not sure how long of a
thread I'd be pulling! Can you give me your best guess as to how much
digging I may have to do to ensure the PostgreSQL UUID data type
doesn't make my Transfer implementation error out?

Thanks!
Jamie

On Nov 11, 4:38 pm, "Mark Mandel" <mark.man...@gmail.com> wrote:
> There is an enhancement in the tracker to allow you to extend property
> definitions to use your own custom query params, with prefixes,
> suffixes, etc
>

> http://tracker.transfer-orm.com/issue.cfm?p=89977683-A728-9CD3-ABD954...

>
> It has yet to be implemented.
>
> Mark
>

> On Wed, Nov 12, 2008 at 1:29 AM, Jamie Krug <jamiek...@gmail.com> wrote:
>
> > Sorry in advance for a lengthy one...
>
> > Using the UUID data type in PostgreSQL 8.3 (http://www.postgresql.org/
> > docs/8.3/interactive/datatype-uuid.html) is problematic with
> > cfqueryparam. There is no explicit cfsqltype, and using cf_sql_varchar
> > will cause the following error when attempting a query like this:
>
> > select * from my_table
> > where my_uuid=<cfqueryparam value="#my_uuid_value"
> > cfsqltype="cf_sql_varchar" />
>
> > org.postgresql.util.PSQLException: ERROR: operator does not exist:
> > uuid = character varying
>
> > I stumbled upon this post, which fixes a similar issue regarding the
> > interval data type:

> >http://www.ghidinelli.com/2007/06/14/postgresql-interval-data-type-wi...

> >http://jochem.vandieten.net/2008/02/06/postgresql-uuids-and-coldfusio...).
> > I downloaded postgresql-8.3-603.jdbc4.jar fromhttp://jdbc.postgresql.org/download.html,

> ...
>
> read more »

[Mark Mandel]

How long is a piece of String? ;)

Mark

[Jared Rypka-Hauer]

The smallest piece of String you can get will cost you 8 bits. Take a
byte outta that, eh?

J

[Jamie Krug]

Thanks, guys :) I probably will dig around and see what it looks like,
but not for a week or two. I'll share if I find anything interesting/
useful.

Jamie

On Nov 11, 5:58 pm, Jared Rypka-Hauer <armchairde...@gmail.com> wrote:
> The smallest piece of String you can get will cost you 8 bits. Take a  
> byte outta that, eh?
>
> J
>
> On Nov 11, 2008, at 4:27 PM, Mark Mandel wrote:
>
>
>
> > How long is a piece of String? ;)
>
> > Mark
>

> > On Wed, Nov 12, 2008 at 8:45 AM, Jamie Krug <jamiek...@gmail.com>  

[Brian G]

On Nov 11, 6:29 am, Jamie Krug <jamiek...@gmail.com> wrote:
> Using the UUID data type in PostgreSQL 8.3 (http://www.postgresql.org/
> docs/8.3/interactive/datatype-uuid.html) is problematic with
> cfqueryparam. There is no explicit cfsqltype, and using cf_sql_varchar
> will cause the following error when attempting a query like this:
>
>

> I stumbled upon this post, which fixes a similar issue regarding the

> interval data type:http://www.ghidinelli.com/2007/06/14/postgresql-interval-data-type-wi...

> (Here's the important part:)
> UPDATE table
> SET someInterval = <cfqueryparam cfsqltype="cf_sql_varchar"
> value="#interalValue#">::interval
>
> So I tried sticking a "::uuid" on the end of my cfqueryparam and it
> works! (But don't stop reading now, I found another solution and then
> another problem...)

Jamie, how have you gotten on with this issue? I'm contemplating
switching to native UUID support in Postgres for the speed and size
improvements but I need to deal with Transfer as well.

I mentioned this on my site but I don't know if I've shared it with
anyone here: since I use hungarian notation in my database and all
interval columns are prefixed with "inv" like invSomething, I modified
transfer/com/sql/postgresql/TransferSelecter.cfc in writeNull to do
the following:

default:
if (left(arguments.column, 3) EQ "inv")
{
null = null & "interval";
}
else
{
null = null & "text";
}

You might be able to get away with something similar with castValue.
I name my primary key UUID columns uidSomething so if I go down this
route and Mark's enhancement is not yet done, I will be looking to
write more hooks like this.

Mark - what does it take to prioritize this enhancement? I'm sure $$$
would help - how much?

Let us know if you have gotten it working in the past month or so...


Brian

[Brian G]

On Nov 11, 1:45 pm, Jamie Krug <jamiek...@gmail.com> wrote:
> Okay, thanks, Mark. I'll hold off for now, but if I have some time, I
> may try to hack just a little further to get things working w/
> PostgreSQL 8.3. I'm just concerned that after I address the current

FWIW, the dev branch of Postgres 8.4 will accept UUIDs in basically
any format so even the built in Adobe format will work:

http://developer.postgresql.org/pgdocs/postgres/datatype-uuid.html
(note the part about any hyphen can be missing)

And they will also natively generate UUIDs if you like:

http://developer.postgresql.org/pgdocs/postgres/uuid-ossp.html

8.4's roadmap say something about a release in March but I don't know
if they are on track.


Brian