ColdFusion Database Pools and Resource Counting

Posted At : April 30, 2007 12:12 AM | Posted By : Justin Mclean
Related Categories: ColdFusion,Singleton,Database,Custom Tag

With a discussion with Sammy he suggested that my ColdFusion database pool DSN component could be extended to select the current datasource with the least no of connections rather than just randomly picking one.

Here's the modified code.

DSN.cfc:

<cfcomponent displayname="DSN" extends="singleton">
   <cfset variables.DSN = arraynew(1)>
   <cfset variables.DSNs = "">
   <cfset variables.length = 0>
   
   <cffunction name="getDSN" access="public" returntype="string" output="true">
      <cfscript>
         var min = 0;
         var use = 0;
         var i = 0;
         for (i=1; i lte variables.length; i=i+1) {
            if (variables.DSN[i].count lte min) {
               use = i;
               min = variables.DSN[i].count;

            }
         }
         variables.DSN[use].count = variables.DSN[use].count + 1;
         return variables.DSN[use].DSN;
      </cfscript>
   </cffunction>
   
   <cffunction name="freeDSN" access="public" output="false">
      <cfargument name="DSN" type="string" required="yes">
   
      <cfscript>
         var i = 0;
         for (i=1; i lt variables.length; i=i+1) {
            if (variables.DSN[i].DSN is arguments.DSN) {
               variables.DSN[i].count = variables.DSN[i].count - 1;
               break;
            }
         }
         return;
      </cfscript>
   </cffunction>   
   
   <cffunction name="getAllDSNs" access="public" returntype="string" output="false">
      <cfreturn variables.DSNs>
   </cffunction>   
   
   <cffunction name="setDSNs" access="public" output="false">
      <cfargument name="DSNs" type="string" required="yes">
      
      <cfscript>
         var i = 0;
         for (i=1; i lte listlen(DSNs); i=i+1) {
            variables.DSN[i] = structnew();
            variables.DSN[i].DSN = listgetat(DSNs,i);
            variables.DSN[i].count = 0;
         }
         variables.DSNs = DSNs;
         variables.length = listlen(arguments.DSNs);   
      </cfscript>
   </cffunction>
</cfcomponent>

Using the component is exactly the same except you have to call freeDSN after the query is run. So the calling code becomes:

<cfset dsn = getInstance("dsn")>
<cfset currentdsn = dsn.getDSN()>

<cfquery name="myquery1" datasource="#currentdsn#">
select ....
</cfquery>
<cfset dsn.freeDNS(currentdsn)>

Alternatively you could create a custom tag dsn.cfm

<cfif thisTag.executionMode IS "start">
   <cfset request.currentdsn = caller.dsn.getDSN()>
<cfelseif NOT thisTag.hasEndTag>
<cfthrow message="Missing end tag.">
<cfelseif thisTag.executionMode IS "end">
   <cfset caller.dsn.freeDSN(request.currentdsn)>
</cfif>

And call it like so:

<cfset dsn = getInstance("dsn")>
<cf_dsn>
<cfquery name="myquery1" datasource="#request.currentdsn#">
select ....
</cfquery>
</cf_dsn>

Comments (4) | Send | 1698 Views
Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
[Add Comment]
Sammy Larbi's Gravatar Good work, though I can't take credit for the least number of connections bit - I didn't think /that/ far ahead! =)
# Posted By Sammy Larbi | 4/30/07 2:25 AM
Michael Long's Gravatar I'd also figure out a way to specify whether or not you want the DSN for read-only or read-write access. Many systems are setup with one "master" database that replicates data to it's slaves. In that configuration, you always want to write to one of them, but can read from any of them.
# Posted By Michael Long | 4/30/07 4:35 AM
Justin Mclean's Gravatar Using the custom tag method above you could specify as an attribute if you wanted read write access. The loop over all datasources could even be added to the custom tag.

With the master/slave(s) setup I assume you have the data replication at the database level rather than at the application level (like my code). Only down side I see if that if the master db goes off offline you can no longer do any update queries but it would generally be easier to keep the databases in sync.
# Posted By Justin Mclean | 4/30/07 8:03 AM
Justin Mclean's Gravatar Just realized there was a few bugs with the original code (ColdFusion is not Actionscript!) - now fixed (and tested).
# Posted By Justin Mclean | 4/30/07 6:40 PM
[Add Comment]