DataFaucet ORM

Select Statement

The select statement will probably be the statement object you work with most often. This is the object that returns a single query from the database.

The select object provides the ability to filter the result, join additional tables or create a union.

The Select object has these properties:

PropertyDefaultDescription
tablen/aThe table from which data is returned
columns*The columns returned
orderbyn/aThe default order in which data is returned - value is trusted
distinctfalseboolean - indicates if the data returned is distinct
includeandand/or - indicates whether filters are cumulative (and) or mutually exclusive (or)
groupbyn/acolumn names to use in the query's group-by clause
havingn/acolumn names to use in the query's having clause
maxrows-1the maximum number of records to return - defaults to all results
sortn/aa list of columns to sort the query - overrides the orderby clause - value is not trusted (scrubbed)
cacheNamen/aa name to use to cache the results of this query - required to cache the results
cachedAftern/aa date value after which the results of this query should be cached
cachedWithin1a duration of time within which to cache the query results
datePartnunits of time for the cachedWithin option - defaults to minutes
validateCachefalseboolean - when true the SQL syntax of the query is checked when caching and a new query result is created if the syntax has changed
sortLSfalseboolean - when true the query results will be sorted in a non-lexigraphical, locale specific manner by Java
sortLSStrengthTERTIARYPRIMARY|SECONDARY|TERTIARY|IDENTICAL - java collation strength for locale-specific sorting
sortLSDecompFULLNO|CANONICAL|FULL - java collation decomposition for locale-specific sorting

Sorting

Reading the list of properties for the select statement, you might find it a little bit confusing that there is both an orderby property and a sort property. The short answer is that these two properties exist to provide the ability for end-users (not you) to sort query results safely.

The long answer is that the value of the sort property is untrusted and is scrubbed prior to execution of the query, to eliminate any values that don't specifically match names of columns in the referenced tables. This way even if a malicious user did try to enter "&sort=ProductName;delete * from tblproducts" into a URL in your site or application, it would never execute the delete statement because your table won't have a column named "productname;delete". Instead the unmatched strings in the sort URL parameter will be dropped prior to executing the query and in a worst case scenario, the query results will be improperly sorted.

That being said, now it's up to you to ensure that your applications are properly mistrusting user-supplied data. Values from form or URL parameters should never be placed in the orderby property. The orderby property should be reserved for the default sort order you define and code into your application. After you've defined your default sort order, you can allow users to override the default sort using the "sort" property. This also allows you to use functions, formulas or case statements in your orderby clause if necessary, although you may want to use that option sparingly.

Lastly the sort property can be set quickly using the sort() function, i.e.

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // selet products by category 
  stmt = ds.getSelect(table="tblProduct",orderby="productprice desc, productname").filter("categoryid",url.categoryid"); 
  
  // let users sort the results 
  MyQuery = stmt.sort(url.sort).execute(); 
</cfscript>

Query Caching

DataFaucet's select statement provides a method of caching query results similar to the native query caching features in ColdFusion server. Both CachedAfter and CachedWithin are supported as methods of setting the query, however in both cases it is necessary to also set the CacheName property of the select statement object. Any queries sharing the same CacheName value will share the same cache. Also instead of using a CreateTimeSpan() value, the CachedWithin value is a number. By default CachedWithin is measured in minutes. To change the units of measure for a query cached with CachedWithin, set the DatePart property of the select statement object.

Examples:

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // create a select statement 
  stmt = ds.getSelect("*","myTable"); 
  
  // cache within one minute 
  stmt.setValue("cacheName","myCachedQuery"); 
  
  // cache within one hour 
  stmt.setValue("datePart","h"); 
  
  // cache within two hours 
  stmt.setValue("cachedWithin",2); 
  
  // cache starting at midnight of the current day 
  // this overrides the previous cachedWithin settings 
  stmt.setValue("cachedAfter",DateFormat(now())); 
  
  // get the cached query 
  stmt.execute(); 
</cfscript>

Resetting the Cache

You can force the statement object to invalidate its cache (and therefore create a new query the next time it executes), by using the purge() method of the select statement object. The database object also has a similar purge() method which allows you to purge all the cached queries for that database at the same time. This is especially useful in conjunction with listeners on the active record object. Using a listener to reset the cache of a particular query means that only the factory responsible for creating the active record and the cached query statement need to understand that relationship. The rest of your code, the controller in your application can be blissfully unaware that any such cache restting happens or is needed.

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // create a select statement 
  stmt = ds.getSelect("*","myTable"); 
  
  // cache within one minute 
  stmt.setValue("cacheName","myCachedQuery"); 
  
  // execute the query 
  qry = stmt.execute(); 
  
  // get the query from cache 
  qry = stmt.execute(); 
  
  // reset the query cache 
  stmt.purge(); 
  
  // execute the query again 
  qry = stmt.execute(); 
</cfscript>

Validate Cache

Unlike the native query caching features in ColdFusion, the faucet doesn't compare the generated SQL syntax to the cache by default. If you have a cached query for which the syntax may change periodically (for example if the query includes a filter of today's date) then you can enable syntax checking by setting the ValidateCache property of the select statement object.

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // create a select statement 
  stmt = ds.getSelect("*","myTable"); 
  
  // cache after January 1, 1970
  stmt.setValue("cacheName","myCachedQuery"); 
  stmt.setValue("cachedAfter","1/1/1970"); 
  
  // check statement syntax when caching 
  stmt.setValue("validateCache",true); 
  
  // get the cached query 
  stmt.execute(); 
</cfscript>

Swapping the Engine

Caching is handled by an object called a CacheManager. By default this object resides in the DataManager in the server scope so that applications that share databases can also share cached queries from those databases. You can change the algorithm for storing cached queries by setting the default CacheManager via the DataManager's setCacheManager() method. Caching can also be defined more granularly at the level of the Datasource() or at the individual Select statement object using the same method. This allows maximum flexibility by allowing you the opportunity to specify your own caching routines from the trivially simple all the way up to something like memcached which would allow you to share caching across multiple servers.

The default CacheManager object in the latest version of DataFaucet takes advantage of a new open source project called CacheBox, which provides consolidated caching from the application level all the way up to synchronizing a cluster, as well as hot-swapping of eviction policies and engines including memcached, ehCache, disk, etc. CacheBox also provides a convenient management application that makes it much easier to administer the cache across several applications on a server or cluster. Only a small portion of the CachBox project, the CacheBoxAgent (CFC) is included in DataFaucet, providing a very basic level of caching functionality. To get all the advanced features of CacheBox you'll need to download and install the CacheBox project separately. It should be as simple as extracting CacheBox to your webroot.