In the process of developing the contextual guidance API, I tried to push as much of the work as I could to the database. I knew that I would have to do multiple queries to check and see if a user had an existing record, when their last visit was to the application, and more. I had read some time ago about the concept of the upsert, and thought this was a good time to put it in to practice.
In many applications, you need to check to see if a record exists in a table, and do an UPDATE if a record does exist, or an INSERT if one does not exist. This is a common pattern in application development. Many developers do something like this:
Query the table to see if a record exists which matches the passed ID
IF match found {
Run an UPDATE query
} else {
Run an INSERT query
}
That's fine, but ultimately requires two trips to the database, or more, depending on what other conditional processing you need to do. The upsert combines all of this in to a single SQL statement. In addition, I needed to run some other database-related logic that would affect the SQL statement. If a user hadn't visited the application in more than 180 days (6 months), I wanted to treat them as a new user and delete their previous record in the log table. (You could set an "isDeleted" flag or delete the record, that's up to you.)
My solution is as follows, and combines some conditional logic in ColdFusion with conditional logic in the SQL as well. This is for MS SQL Server, but the concept applies to pretty much any database.
<!--- First check and see if a record for this user has been updated in the last 30 minutes. If so, don't do anything as that's not really a new session. --->
DECLARE @lastVisitDate datetime
set @lastVisitDate =
(
SELECT lastVisit
FROM logTable
WHERE userID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.userID#" />
AND appName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.applicationName#" />
<cfif Len(arguments.applicationSection)>
AND sectionName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.applicationSection#" />
<cfelse>
AND sectionName IS NULL
</cfif>
AND DateDiff(n, lastVisit, getDate()) > 30
)
<!--- If the @lastVisitDate value is not null, a record was found matching the criteria, so we can do an update or insert, as needed. --->
IF @lastVisitDate IS NOT NULL
BEGIN
<!--- If the user hasn't visited the app in more than 180 days, treat them as a brand-new user by deleting their record and starting fresh. --->
IF DateDiff(d, @lastVisitDate, getDate()) > 180
BEGIN
DELETE FROM logTable
WHERE userID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.userID#" />
AND appName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.applicationName#" />
<cfif Len(arguments.applicationSection)>
AND sectionName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.applicationSection#" />
<cfelse>
AND sectionName IS NULL
</cfif>
END
<!--- Try the update first. If there's nothing to update, we need to do an insert instead. --->
UPDATE contextualGuidanceLog
SET totalVisits = totalVisits + 1,
lastVisit = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#CreateODBCDateTime(Now())#" />,
previousLastVisit = (
SELECT lastVisit FROM contextualGuidanceLog
WHERE userID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.userID#" />
AND appName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.applicationName#" />
<cfif Len(arguments.applicationSection)>
AND sectionName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.applicationSection#" />
<cfelse>
AND sectionName IS NULL
</cfif>
)
WHERE userID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.userID#" />
AND appName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.applicationName#" />
<cfif Len(arguments.applicationSection)>
AND sectionName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.applicationSection#" />
<cfelse>
AND sectionName IS NULL
</cfif>
<!--- If nothing was updated, then the resulting @@rowcount value will be zero. We need to do an insert in this case. --->
IF @@rowcount = 0
BEGIN
INSERT INTO contextualGuidanceLog (
userID,
appName
<cfif Len(arguments.applicationSection)>, sectionName</cfif>
)
<!--- totalVisits, lastVisit and previousLastVisit have defaults created in the table by the db --->
VALUES (
<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.userID#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.applicationName#" />
<cfif Len(arguments.applicationSection)>
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.applicationSection#" />
</cfif>
)
END
END <!--- End the "IF @lastVisitDate IS NOT NULL" IF --->
This may look a bit complex, but the key thing here is the UPDATE followed by the IF @@rowcount = 0 statement. If no records were updated (meaning there was no record to match the passed values), then we do an insert. This is just like our original logic, except we're doing everything in one query, rather than two. Because I also needed to check the last time a record for this person was updated and make sure we're not updating it more than once every 30 minutes, I combined 3 potential <cfquery> calls in to a single one.
There's one issue, though, that I'm not too happy with:
I realize that I'm repeating a bunch of code to generate the WHERE clause in these SELECT statements. I can't generate this text dynamically as a string and then <cfoutput> the WHERE clause because I'm using <cfqueryparam>, as I damn well better be. I guess I could build a dynamic string and then wrap it in the Evaluate() function, but that seems pretty ugly to me (and may not work because of how CF's engine parses the query text and does the binding to the values in a <cfqueryparam> tag). Though I guess using Evaluate() isn't any uglier than the repeated code for the WHERE statement.
I like the upsert approach. I'll be using it from here on out in these situations. If anyone has any suggestions about the repeated WHERE clause code, I'd love to hear them!
