DataFaucet ORM

SQL Library

Likely the majority of the work you'll do with DataFaucet will involve using the SQL library, and likely most of that time will be spent creating search utilities. If that sounds like a lot of work, don't worry - the amount of time you'll save by using the library will more than make up for the fact that you're not writing insert and update queries very often. ;) In practice, when I ported Ray Camden's Galleon Forums to use this SQL library, I was able to eliminate about 600 lines of code, plus add features and with no noticeable change in performance.

The Basics

Any given query starts with a "statement" object. There are four basic statements, select, insert, update and delete. These four statement objects can be created and instantiated manually however, because it is less coupled and more importantly easier, it is recommended that you use the datasource object to create new statements.

Example:

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // get a select statement 
  stmt = ds.getSelect("*","MyTable"); 
  // or this way 
  stmt = ds.getStatement("select").init("MyTable","*"); 
</cfscript>

There are ten functions in the datasource object that create statement objects. Four of them execute those objects immediately and return the results. The other six return the statement object.

query = select ([columns,] table [,filters, orderby, maxrows, distinct])Executes a select statement immediately
result = insert (table, insertData)Executes an insert statement immediately
result = update (table, insertData [,column])Executes an update statement immediately - column indicates which key(s) in the insertData are used to filter the update and defaults to the table's primary key(s)
result = delete (table, id [,column])Executes a delete statement immediately - column defaults to the table's primary key
stmt = getSelect ([columns,] table [,filters, orderby, maxrows, distinct])Returns a select statement object like select() for further filtering
stmt = getInsert (table, insertData)Returns an insert statement object like insert() for using multiple times
stmt = getUpdate (table, insertData [,column])Returns an update statement object like update() for further filtering or multiple updates
stmt = getDelete (table, id[,column])Returns a delete statement object like delete() for further filtering
stmt = getStatement (className)Returns a statement object of the specified class - this may be useful for further extending the library and gives you more control over creating the statement
proc = getProcedure (procedure)Returns a stored procedure object

If you need more control over how the query is filtered, you can manually add new filters to the statement object.

Execute() and getSyntax()

Each of the four statement objects includes two methods for handling the generated SQL statement. These two methods are getSyntax() and Execute(). Neither method has any arguments. The getSyntax() method parses the generated SQL statement and returns a string with the complete syntax and all variables substituted so the returned value could be easily copied and pasted into other database tools like Microsoft Enterprise Manager with no modification. The Execute() method executes the SQL statement against the database and returns a query from select statements or a structure containing result information from any other statement type.

Ad-Hoc Datasource Methods

If building queries this way still seems strange and unusual to you, there is an alternative method of creating some simple queries. The datasource object uses an onMissingMethod function (ColdFusion 8) to convert method names into SQL statements using its SQL object library. This allows you to write statements that are visually much closer to the SQL syntax you're familiar with. Because it uses the name of the method to generate the query, it requires the use of underscores instead of spaces (and most punctuation), and ColdFusion style operators (GT, LT, GTE, LTE, IS, EQ, NEQ and NOT).

Example:

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // get products under $50.00 
  qry = ds.query_from_tblProducts_where_price_lte_50(); 
  
  // get discontinued products 
  qry = ds.query_from_tblProducts_where_cancelDate_not_null(); 
  
  // get available products 
  qry = ds.query_from_tblProducts_where_status_is_available(); 
  
  // get messages beginning with "re" (i.e. "RE: the memo") 
  qry = ds.query_from_tblMessages_where_title_beginsWith_re(); 
</cfscript>

To return a query, begin the method name with "query", i.e. ds.query_from_tblProcucts. You can also return a select statement object for further filtering or modification by beginning the same query name with the word "select", i.e. ds.select_from_tblProducts.

The Duck!

All the statement objects ultimately extend a Duck.cfc in the SQL package. While this may sound complicated it's there to help you out. The duck.cfc has two functions for it's "accessors" and "mutators" (otherwise known as getters and setters) called getValue("property") and setValue("property",value). You can use these original methods or if you're more comfortable, the duck will also automatically convert calls to more traditional method names. So for example select.setValue("distinct",true) is the same as select.setDistinct(true) and select.getValue("distinct") is the same as select.getDistinct().

There are also two additional methods for setting or retrieving multiple properties of a statement object at the same time. These are getProperties() and setProperties(struct or query) respectively.

the Table Property

All statement objects have a "table" property indicating the database table affected by the statement. The stored-procedure object is an exception to this rule, having instead a "procedure" property indicating the procedure to execute. Tables in queries can be aliased by adding a space and the alias name in the table property.

Example:

<cfset stmt = ds.getSelect("*","tblVeryLongTableName voltron") />

This code will then use the label "voltron" to refer to that table throughout the query.

CFQUERYPARAM

You might notice that in all the query-building samples in the documentation for DataFaucet there aren't any CFQUERYPARAM tags. Don't worry! DataFaucet applies the CFQUERYPARAM tag for you automatically! This is also good news because it means your application is less coupled. Changing the type of a column during development can be done purely in the database - no need to search and replace in your application code to find references to the cf_sql_type of the updated column.