I've had a little more time to test the fusebox lexicon and in testing the insert and update verbs I realized they were a little less intuitive / more verbose than I prefer. So I've enhanced them a bit... This is the first time I've created a lexicon for Fusebox and although I don't like all of the decisions about how they work, I will say that I really like the fact that the lexicons can be placed in a mapped directory so you don't have to copy them anywhere. This way if you have multiple applications on your server they can all share the same lexicon in the DataFaucet installation if you prefer or you can install the lexicon into a separate directory if you want to tweak it.
I created a singular model circuit and put all my testing code in that circuit. I configured the faucet by calling the circuit's configureFaucet fuseaction in the appinit in fusebox.xml and then placed a call to the openFaucet fuseaction in the controller's prefuseaction so that the faucet is opened at the beginning of each request.
<circuit access="public" xmlns:df="/datafaucet/fusebox/lexicon">
<fuseaction name="configureFaucet">
<df:open server="mssql"
datasource="datafaucet"
catalog="galleon"
schema="dbo" />
</fuseaction>
<fuseaction name="openFaucet">
<df:open />
</fuseaction>
<fuseaction name="getMessages">
<df:select name="qMessage" table="galleon_messages">
<!-- filter messages by the event's threadid value -->
<df:filter column="threadid" />
</df:select>
</fuseaction>
<fuseaction name="searchMessages">
<!-- search messages with and/or keywords -->
<df:select name="qMessage" table="galleon_messages">
<df:filter column="title,body"
content="searchphrase"
andorkeywords="true" />
</df:select>
</fuseaction>
<fuseaction name="saveMessage">
<!--- insert or update a message record --->
<!--- assume event.getAllValues() as the data to save --->
<df:update table="galleon_messages">
<!-- update on the id column -->
<df:filter column="id" />
</df:update>
</fuseaction>
<fuseaction name="deleteMessage">
<df:delete table="galleon_messages">
<df:filter column="id" />
</df:delete>
</fuseaction>
<!-- or use ActiveRecord --->
<fuseaction name="getMessageObject">
<df:record object="MyMessage"
class="my.activerecord.class"
action="read" id="id" />
</fuseaction>
<fuseaction name="saveMessageObject">
<!--- assume event.getAllValues() as the data to save --->
<df:record object="MyMessage" action="save" />
</fuseaction>
</circuit>
Also, although these samples show the assumptions, you're not tied in to them - you can specify data="myStruct" for a df:update or a df:record action="save" verb. You can also specify df:data verbs as children of the df:update verb for mor granular control. df:filter can accept a "content" attribute if you want to use something other than an event value matching the column name, or if the situation calls for it, you can nest a df:select inside your df:filter to filter on a subquery, i.e. "delete from myTable where id in (select id from othertable where othercolumn like '%something%')".
