About a year or so ago, I blogged about problems I was having with ColdFusion 9 ORM and using its entityDelete() function to delete items that ultimately could not be deleted because of foreign key constraints within the database. The problem I was having, in a nutshell, was I couldn't trap the error. Despite all kinds of error trapping, a nasty Hibernate exception kept bubbling up.

I was stuck. I tried posting the problem to cf-talk, but didn't find much help.

Some time later, I discovered the cf-orm-dev Google Group and again posted my quandary. Thankfully, I got several replies. One of those was from Mark Mandel (of Transfer and ColdSpring fame), someone in the CF community that I respect very much. He essentially said using try/catch in the manner I was attempting was bad coding practice. I had never considered that -- it was one of those things I always did. Another person suggested that I use ORM events, namely preDelete(). ORM events were new to me, so I investigated.

ColdFusion ORM includes a bunch of events which get called at various states of a database request's lifecycle. Think of it like Application.cfc for database calls. Among these include preLoad, postLoad, preInsert, postInsert, preUpdate, postUpdate, preDelete, and postDelete. Dan Vega has a really great breakdown of all these on his website. The thing I discovered early on is these events aren't enabled by default. You have to switch on eventhandling in ormsettings, like so:

this.ormsettings.eventhandling = true;

I decided that the preDelete() event would allow me to check for constrains, and if I found anything, eaisly back out of the delete. So, in order to delete something we have to load up a record, then run it through entityDelete():

objSong = ("Song",rc.songID,true);
entityDelete(local.objSong);

At this point, preDelete() gets called in Song.cfc, where I can check for any constraints:

<cffunction name="preDelete" access="public" output="false" returntype="boolean">
    <cfquery name="local.qryChildRecords">
        SELECT ServiceItemID AS ID FROM ServiceItems WHERE SongID = <cfqueryparam value="#this.getSongID()#" cfsqltype="cf_sql_integer" />
        UNION
        SELECT EventItemID AS ID FROM EventItems WHERE SongID = <cfqueryparam value="#this.getSongID()#" cfsqltype="cf_sql_integer" />
        UNION
        SELECT SongFileID AS ID FROM SongFiles WHERE SongID = <cfqueryparam value="#this.getSongID()#" cfsqltype="cf_sql_integer" />
    </cfquery>
    <cfif local.qryChildRecords.RecordCount>
        <!--- Child records exists, abort the delete --->
        <cfset this.setSongID(javaCast("null","")) />
        <cfreturn true />
    </cfif>
    <cfreturn false />
</cffunction>

The way preDelete() works is if it returns a true value, the delete is aborted. While that's all fine and good, it doesn't do anything to inform the user nothing was deleted. So in order to detect that, I set the primary key of the object (in this case SongID) to NULL. Then, after entityDelete() is called I know if the delete happened or not. There may be a better way, but that seems to work for the vast majority of what I've done.

If anyone has any ideas or suggestions on how to improve on this, I'd love to hear them!

Most ColdFusion functions, like cfdirectory, return file size information in bytes. While that is probably a more technically accurate number, it usually doesn't mean a whole heck of a lot to end users, who are probably more used to "denominations" such as the kilo or megabyte. I remedy this, I whipped up the following helper function.

<cffunction name="fileSizeFormat" access="public" returntype="string" output="false">
    <cfargument name="size" type="numeric" required="true" />
    <cfif arguments.size lt 1024>
        <cfreturn "#arguments.size# bytes" />
    <cfelseif arguments.size lt 1024^2>
        <cfreturn "#round(arguments.size/1024)# KB" />
    <cfelseif arguments.size lt 1024^3>
        <cfreturn "#decimalFormat(arguments.size/1024^2)# MB" />
    <cfelse>
        <cfreturn "#decimalFormat(arguments.size/1024^3)# GB" />
    </cfif>
</cffunction>

Basic usage: fileSizeFormat(size)

If you need to show file sizes larger that GB, you could easily add 1024^4 and 1024^5 to the above if statement. Either that or seriously start looking into compression.

You've probably seen those relative or "fuzzy" date displays on various sites across the internet, especially on sites like Twitter and Facebook where they'll say "Just Now" or "13 minutes ago" rather than a traditional date-formatted string. It makes sense, after all that's how we speak to each other. For example, if you're meeting someone, if you're asked how long you'd been there, you don't say "I got here at 11:05:46 am". Instead, you say "I got here about 5 minutes ago."

Wanting similar functionality in a project I was working on, I looked around the web and sites like CFLib to see if I could find something similar. I couldn't, so I decided to write my own. The code for the function is below.  Although it could be a lot smarter and more international-friendly, it gets the basic job done.

<cffunction name="fuzzyDateDiff" access="public" output="false" returntype="string">
	<cfargument name="date1" type="date" required="true" />
	<cfargument name="date2" type="date" required="true" />
	<cfset var intDiffUnits = 0 />
	<!--- Seconds --->
	<cfif dateDiff("s",arguments.date1,arguments.date2) lt 60>
		<cfreturn "Just now" />
	</cfif>
	<!--- Minutes --->
	<cfset intDiffUnits = dateDiff("n",arguments.date1,arguments.date2) />
	<cfif intDiffUnits lt 60>
		<cfreturn "#intDiffUnits# minute#iif(intDiffUnits gt 1,de("s"),"")# ago" />
	</cfif>
	<!--- Hours --->
	<cfset intDiffUnits = dateDiff("h",arguments.date1,arguments.date2) />
	<cfif intDiffUnits lt 24>
		<cfreturn "#intDiffUnits# hour#iif(intDiffUnits gt 1,de("s"),"")# ago" />
	</cfif>
	<!--- Days --->
	<cfset intDiffUnits = dateDiff("d",arguments.date1,arguments.date2) />
	<cfif intDiffUnits lt 7>
		<cfreturn dateFormat(arguments.date1,"dddd") />
	</cfif>
	<!--- Weeks --->
	<cfset intDiffUnits = dateDiff("ww",arguments.date1,arguments.date2) />
	<cfif intDiffUnits is 1>
		<cfreturn "Last week" />
	<cfelseif intDiffUnits lt 4>
		<cfreturn "#intDiffUnits# weeks ago" />
	</cfif>
	<!--- Months/Years --->
	<cfset intDiffUnits = dateDiff("yyyy",arguments.date1,arguments.date2) />
	<cfif intDiffUnits lt 1>
		<cfreturn dateFormat(arguments.date1,"mmmm d") />
	<cfelse>
		<cfreturn dateFormat(arguments.date1,"mmmm d, yyyy") />
	</cfif>
</cffunction>

Basic usage fuzzyDateDiff (date1, date2)

If you want the result to be realitive to the current date and time, simply use now() for date2. Happy coding!

In a project I've been working on, I wanted a way to remotely backup a mySQL database on to Amazon S3. Because the site was hosted on a shared server, I also didn't have access to the command line.

There are probably dozens of different ways to go about doing this, but the solution I arrived at is incredibly simple and easily lends itself to a quick restore script.

First, we need to get a list of tables in the database:

<!--- Get data  --->
<cfquery name="qryTables" result="stcTables">
    SHOW TABLES
</cfquery>

We will then get all the data from each of those tables and create a WDDX packet out of each (which is a XML representation of ColdFusion query object). Each of these packets is written in to a text file.

<!--- Pull down data and export each table's data into WDDX file--->
<cfset variables.strTempDirectory = "#expandPath("./")#temp\" />
<cfloop query="qryTables">
    <cfset variables.strTableName = qryTables[listFirst(stcTables.ColumnList)] />
    <cfquery name="qryData">
        SELECT * FROM #variables.strTableName#
    </cfquery>
    <cfwddx
        action="cfml2wddx"
        input="#qryData#"
        output="variables.strData" />
    <cffile
        action="write"
        file="#variables.strTempDirectory##variables.strTableName#.txt"
        output="#variables.strData#"
        charset="utf-8" />
</cfloop>

Finally, we'll take all those text files and zip them into a single file, which will be sent to Amazon S3 (thanks to the CF 9.01 enhancement).

<!--- Package WDDX files into a ZIP file --->
<cfset variables.strBackupZip = "database-backup-#dateFormat(now(),"yyyy-mm-dd")#.zip" />
<cfzip
    action="zip"
    file="#variables.strTempDirectory##variables.strBackupZip#"
    source="#variables.strTempDirectory#"
    filter="*.txt" />

<!--- Send to Amazon S3 --->
<cffile
    action="copy"
    source="#variables.strTempDirectory##variables.strBackupZip#"
    destination="s3://your-bucket-name/database/#variables.strBackupZip#"/>

I've been using Hostek for a couple of years now, and recently inquired about using the latest version of CF for one of my sites. This was the response I got from their support team:

"We currently have all of our new servers running ColdFusion 9.0.1 if you would like we can move the domain to one of the servers running CF 9.0.1, copy the files/DSNs and the have you test it before pointing domain to new server IP... Please let us know the domain name that needs to be moved and if you would like us to proceed. There will be no downtime during control panel migration or site move."

You hear a lot of hosting horror stories out there, but don't always hear when companies get it right.