Managed Hosting



DataFaucet ORM
Project Home Blog Forums Wiki Known Issues External Project Link Contact Project

DataFaucet ORM Issue: Filters = URL creates incorrect filter sql

Name: Filters = URL creates incorrect filter sql
ID: 11
Project: DataFaucet ORM
Type: Bug
Area: Code
Severity: Normal
Status: Open
Related URL: http://www.datafaucet.com/hellodata.cfm
Creator: Darren Cook
Created: 05/26/10 7:33 PM
Updated: 05/05/12 9:13 PM
Description: Your Hello Data example outlines how to send a scope in the filters attribute of the select() method:
cfdump var="#ds.select(table='hello_world',filters=URL)#"

However this does not seem to always generate a correct filter statement.

<cffunction name="test">
<cfargument name="id">
<cfdump var="#ds.select(table="someTable",filters=ARGUMENTS)#">
Assuming that someTable has numeric columns, the result is a sql statement which has a comparison of id as a range with every other numeric column in someTable. eg:
SELECT [testdb].[dbo].[someTable].*
FROM [testdb].[dbo].[someTable]
WHERE ( [testdb].[dbo].[someTable].[fkID] >= ?
AND [testdb].[dbo].[someTable].[fkID] <= ? )
AND [testdb].[dbo].[someTable].[ID] IN ( ? )
AND ( [testdb].[dbo].[someTable].[fk2ID] >= ?
AND [testdb].[dbo].[someTable].[fk2ID] <= ? )
AND ( [testdb].[dbo].[someTable].[fk3ID] >= ?
AND [testdb].[dbo].[someTable].[fk3ID] <= ? )

It seems related to the cfif statement in system.sql.collectionfilter.getORMCollection(), which appears to allow this leak through? That is, a column which has no corresponding key in ARGUMENTS.collection is always evaluated for a Contains sql statement, against all keys in the collection, and can be output if it is of one of the listed types. This is perhaps for a reason, but the following refactoring did fix the issue outlined above:
<cfif structKeyExists(collection,cname)>
<cfif isSimpleValue(collection[cname]) >
<cfif (matchnull xor len(trim(collection[cname])))>
<cfset clone[cname] = collection[cname]>
<cfelseif comparison is "contains">
<cfif listfindnocase("date,datetime,timestamp,time",df_datatype)>
<cfset setRangeFilter(clone,cname,getFilterDates(cname,collection)) />
<cfelseif listfindnocase("numeric,number,float,double,int,integer,tinyint,smallint,bigint,decimal,money,real",df_datatype)>
<cfset setRangeFilter(clone,cname,getFilterNumbers(cname,collection)) />
History: Created by darrentcook (Darren Cook) : 05/26/10 7:33 PM

Comment by ike (Isaac Dealey) : 05/05/12 9:13 PM
Thanks for the deep look into the code, Darren! Few people dig that far. :) I'll have to have a closer look at this later when I have more time.

To add a comment to this bug, please login using the link above.