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 has these properties:
|table||n/a||The table from which data is returned|
|columns||*||The columns returned|
|orderby||n/a||The default order in which data is returned - value is trusted|
|distinct||false||boolean - indicates if the data returned is distinct|
|include||and||and/or - indicates whether filters are cumulative (and) or mutually exclusive (or)|
|groupby||n/a||column names to use in the query's group-by clause|
|having||n/a||column names to use in the query's having clause|
|maxrows||-1||the maximum number of records to return - defaults to all results|
|sort||n/a||a list of columns to sort the query - overrides the orderby clause - value is not trusted (scrubbed)|
|cacheName||n/a||a name to use to cache the results of this query - required to cache the results|
|cachedAfter||n/a||a date value after which the results of this query should be cached|
|cachedWithin||1||a duration of time within which to cache the query results|
|datePart||n||units of time for the cachedWithin option - defaults to minutes|
|validateCache||false||boolean - when true the SQL syntax of the query is checked when caching and a new query result is created if the syntax has changed|
|sortLS||false||boolean - when true the query results will be sorted in a non-lexigraphical, locale specific manner by Java|
|sortLSStrength||TERTIARY||PRIMARY|SECONDARY|TERTIARY|IDENTICAL - java collation strength for locale-specific sorting|
|sortLSDecomp||FULL||NO|CANONICAL|FULL - java collation decomposition for locale-specific 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>
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.
<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>
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.