ColdFusion

Hi Ade,

There are some similarities here with my own code. I think what might be more useful than deleting on the basis of names would be deleting a list of primary keys and/or deleting on the basis of a foreign key (which you probably can do, given what you've posted and it's just not shown in the example). Though I would probably set it up so that the foreign key deletes are also "IN" lists, i.e.

WHERE colname IN (<cfqueryparam value="#foreignkeylist#" cfsqltype="..." list="true" />)

That makes it easier to build an interface where a user might select a series of items to update or delete using checkboxes, a multiple select or a dhtml "swap box" element.

I would probably also change the way your arguments are being passed into the update and delete statements... For example, right now you're probably using <cfset dao.update(argumentcollection=form) /> -- it's very little code, gets the crud done really fast. :) However, I would probably instead of using argumentcollection use the first argument of the update function to hold the collection of data to be inserted or updated in your create / update statements, so that subsequent arguments could be used for other things (right now they would have to mingle, which isn't the end of the world, but occasionally causes some frustration). This would also allow you to do what you were probably originally thinking with regard to updates where you can update on any column (see the previously mentioned frustration).

<cffunction name="update" ...>
<cfargument name="insertdata" type="struct" required="true" />
<cfargument name="filters" type="struct" required="true" />

Then all your where clause info would come out of the filters struct and your update values would come from the insertdata struct. The insertdata struct is probably frequently going to be "form" (or possibly "attributes" if you're using fusebox) and with ColdFusion 8 you can use implicit structure syntax to build the filter structure, i.e.

<cfset myfilters = {id=form.id} />
<cfset dao.update(form,myfilters) />

And -- last thing I'll mention, because it's not really a functional criticism. I personally find the use of the separator variable somewhat cumbersome (yeah, I know, it's generated), so I would probably instead use a list and just remove the items from the list that you don't want to update, i.e.

<cfset clist = listToArray("x,y,z") />
<cfloop index="i" from="#arraylen(clist)#" to="1" step="-1">
  <cfif not structKeyExists(insertdata,clist[i])>
    <cfset ArrayDeleteAt(clist,i) />
  </cfif>
</cfloop>

Then you can just loop over the clist array. I would probably even put the above in a separate method called "getInsertColumns" or the like so that it could be called from both the insert and update methods.

In the update method that I actually use, I used something like this for individual columns:

#colname# = <cfif structkeyexists(insertdata,colname)>
  <cfqueryparam value="#insertdata.colname#" .../>
<cfelse>
  #colname#
</cfif>

So in my case it's actually updating every column, regardless of whether or not it's getting a new value, and setting those that are being "ignored" to their current value. Which could be an issue if you used a lot of triggers in your database, but most people don't. Though mine is even a little more involved because it also includes a comma-delimited list of specific columns to set null -- which of course you could do in your update method as well. Again, I would use an argument for the insertdata and then follow it with a filter argument and the "setnull" argument to indicate the null columns.

Hey Ike, glad to see you're back on CF.

I like the list idea. Right now I call each method multiple times when updating and deleting. I'm gonna work at putting that functionality in.

Regarding the way the arguments are passed in, I did start out passing in a structure as you suggested but I went with the column-to-argument way as it seemed cleaner. I hadn't thought that it would make updating any column based on any where clause easier. I think I'll go with the column structure way because of this.

The new ways to create arrays and structures in CF8 will certainly help with shorting code. If I know my code will be on CF8 I always use it :)

I wasn't happy with the separator in the create and update methods either. If it wasn't auto generated I would have gone with another method.

It would be interesting to know if there's much overhead in updating columns in the SQL statement whether they need it or not. I rarely use triggers but good call on the trouble that may cause.

I hadn't thought about setting NULLs. I'll give that a go as well.

Thanks, for the comment. It's always good to get others' opinions.

Welcome. Null is one of the areas where CF can be frustrating at times -- and I've seen other people handle it in different ways like I remember seeing one guy who had a function that checked to see if the value being passed in was the string "null" and if so then he would set the column to null... which I didn't much like because even though it's not very likely someone will want to put "null" as the content in a form field, it would be problematic if they did. The comma delimited list as an argument was actually my 2nd take on it, which is a lot better than my first take, which was to have a boolean argument to indicate if columns not found in the insertdata structure should be set to null. It worked, but it didn't give me a lot of flexibility for multi-record update statements. :)

Being able to use lists for foreign key columns should make those update / delete statements more scalable. :)

I notice you cleaned up my code samples. :)

I use too many emoticons in my comments. :)

lol @ :) :) :)

I agree with Ike about passing in a structure for data and a structure for a filter for the update function. But yeh I like it.

Dom

Hey Ade, I just installed reactor last night and the DAOs it creates do pretty much what you are doing (the interface is the same anyway, under the hood is a whole different matter).

Love the site btw, classy.