ColdFusion Database Pools and Resource Counting

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

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:

view plain print about
1<cfcomponent displayname="DSN" extends="singleton">
2    <cfset variables.DSN = arraynew(1)>
3    <cfset variables.DSNs = "">
4    <cfset variables.length = 0>
5    
6    <cffunction name="getDSN" access="public" returntype="string" output="true">
7        <cfscript>
8            var min = 0;
9            var use = 0;
10            var i = 0;
11            for (i=1; i lte variables.length; i=i+1) {
12                if (variables.DSN[i].count lte min) {
13                    use = i;
14                    min = variables.DSN[i].count;
15
16                }
17            }
18            variables.DSN[use].count = variables.DSN[use].count + 1;
19            return variables.DSN[use].DSN;
20        </cfscript>
21    </cffunction>
22    
23    <cffunction name="freeDSN" access="public" output="false">
24        <cfargument name="DSN" type="string" required="yes">
25    
26        <cfscript>
27            var i = 0;
28            for (i=1; i lt variables.length; i=i+1) {
29                if (variables.DSN[i].DSN is arguments.DSN) {
30                    variables.DSN[i].count = variables.DSN[i].count - 1;
31                    break;
32                }
33            }
34            return;
35        </cfscript>
36    </cffunction>    
37    
38    <cffunction name="getAllDSNs" access="public" returntype="string" output="false">
39        <cfreturn variables.DSNs>
40    </cffunction>    
41    
42    <cffunction name="setDSNs" access="public" output="false">
43        <cfargument name="DSNs" type="string" required="yes">
44        
45        <cfscript>
46            var i = 0;
47            for (i=1; i lte listlen(DSNs); i=i+1) {
48                variables.DSN[i] = structnew();
49                variables.DSN[i].DSN = listgetat(DSNs,i);
50                variables.DSN[i].count = 0;
51            }
52            variables.DSNs = DSNs;
53            variables.length = listlen(arguments.DSNs);    
54        </cfscript>
55    </cffunction>
56</cfcomponent>

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

view plain print about
1<cfset dsn = getInstance("dsn")>
2<cfset currentdsn = dsn.getDSN()>
3
4<cfquery name="myquery1" datasource="#currentdsn#">
5select ....
6</cfquery>
7<cfset dsn.freeDNS(currentdsn)>

Alternatively you could create a custom tag dsn.cfm

view plain print about
1<cfif thisTag.executionMode IS "start">
2    <cfset request.currentdsn = caller.dsn.getDSN()>
3<cfelseif NOT thisTag.hasEndTag>
4 <cfthrow message="Missing end tag.">
5<cfelseif thisTag.executionMode IS "end">
6    <cfset caller.dsn.freeDSN(request.currentdsn)>
7</cfif>

And call it like so:

view plain print about
1<cfset dsn = getInstance("dsn")>
2<cf_dsn>
3<cfquery name="myquery1" datasource="#request.currentdsn#">
4select ....
5</cfquery>
6</cf_dsn>

Comments Comments (4) | Send Send | 5562 Views | Bookmark and Share
TweetBacks
Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
[Add Comment] [Subscribe to Comments]
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
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
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
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]