TQL Transfer lexicon for fusebox

This information assumes you have setup your Fusebox App for Transfer as described in the Transfer Lexicon post.

Since March Transfer has supported TQL, which is transfers own scripting language:

There is also a scripting language that allows you to perform database queries based
on the information and naming scheme that you set up in your transfer configuration
file called Transfer Query Language (TQL). TQL is very similar to SQL, however since
Transfer already knows about the relationships in your system, you don't have to write
as much code to perform complicated queries against your database.

This is a fantastic addition as it allows you to quickly manipulate your data. One thing that I thought could make easy to use via fusebox was to create a lexicon, which is done here:

<cfscript>
// author: Nick Tong - http://succor.co.uk | http://talkwebsolutions.co.uk // usage: // <transfer:tql
// tql=" from post.Post as Post join system.Category join user.User order by Post.dateTime desc "
// queryName="myQueryName">
// <transfer:parameter name="id" value="#attributes.id#" />
// </transfer:tql> if (fb_.verbInfo.executionMode is "start") {
// validate attributes // object - string if (not structKeyExists(fb_.verbInfo.attributes,"tql")) {
fb_throw("fusebox.badGrammar.requiredAttributeMissing",
"Required attribute is missing",
"The attribute 'tql' is required, for a 'tql' verb in fuseaction #fb_.verbInfo.circuit#.#fb_.verbInfo.fuseaction#.");
}
// queryName - string if (not structKeyExists(fb_.verbInfo.attributes,"queryName")) {
fb_throw("fusebox.badGrammar.requiredAttributeMissing",
"Required attribute is missing",
"The attribute 'queryName' is required, for a 'tql' verb in fuseaction #fb_.verbInfo.circuit#.#fb_.verbInfo.fuseaction#.");
}
// generate code: fb_appendLine('<cfset #fb_.verbInfo.attributes.queryName# = ' &
'myFusebox.getApplication().getApplicationData().transferFactory.getTransfer().createQuery("#fb_.verbInfo.attributes.tql#") />');

// prepare for any parameters: fb_.verbInfo.parameters = structNew();
} else {

for (fb_.p in fb_.verbInfo.parameters) {
fb_appendLine('<cfset #fb_.verbInfo.attributes.queryName#.setParam("#fb_.p#",#fb_.verbInfo.parameters[fb_.p]#) />');
}

fb_appendLine('<cfset #fb_.verbInfo.attributes.queryName# = ' &
'myFusebox.getApplication().getApplicationData().transferFactory.getTransfer().listByQuery(#fb_.verbInfo.attributes.queryName#) />');

fb_appendLine('<cfset myFusebox.trace("Transfer","Created TQL Query") />');
}
</cfscript>

Save the above code in your lexicons folder as TQL.cfm you can then use it like this in your circuit.xml.cfm file as follows:

<tr:tql tql=" from event.event as event where event.startDate = :startDate order by event.eventName " queryName="myQueryName">
<tr:parameter name="startDate" value="lsDateFormat(now())" />
</tr:tql>

What this does is passes through the TQL parameters and a queryName (what you would like it to be called). It will then generate the output code like:

<cfset myQueryName = myFusebox.getApplication().getApplicationData().transferFactory.getTransfer().createQuery(" from event.event as event where event.startDate = :startDate order by event.eventName ") />
<cfset myQueryName.setParam("startDate",lsDateFormat(now())) />
<cfset myQueryName = myFusebox.getApplication().getApplicationData().transferFactory.getTransfer().listByQuery(myQueryName) />
<cfset myFusebox.trace("Transfer","Listed TQL Records") />

In the example above you can see that i pass though

from event.event as event where

event.startDate = :startDate order by event.eventName

as my TQL, this is telling transfer to look at my Tranfer config file and get the event object from my event package. I then want to limit the returned values to todays date

where event.startDate = :startDate

:startDate is the name of the paramater that we are passing in.

As mentioned above we use the Transfer parameter lexicon, because of this you will need to edit your parameter.cfm file so it can be called via the TQL lexicon. Update line 12 to:

listFind("delete,read,save,tql",fb_.verbInfo.parent.lexiconVerb) neq 0) {

External Links