Today I was inspired to write a Random utility component. The first article that inspired me to write this utility was Pete Freitag's article on selecting a random row from a database table. The second was Ben Forta's article on how ColdFusion is not a DBMS. The main point was to leverage the DBMS whenever possible and let ColdFusion do the job that it needs to. With that in mind I created a Random component that allows you to retrieve a random row, retrieve random rows, and incorporated a few random utilities from cflib. After completing this I realized that putting DBMS specfic methods with utility methods was not the best design but would work for now until I get a chance to separate them. You want to see some code, ok lets take a look.

Random.cfc

view plain print about
1<!---
2License:
3Copyright 2006 Dan Vega (danvega@gmail.com)
4
5Licensed under the Apache License, Version 2.0 (the "License");
6you may not use this file except in compliance with the License.
7You may obtain a copy of the License at
8
9 http://www.apache.org/licenses/LICENSE-2.0
10
11Unless required by applicable law or agreed to in writing, software
12distributed under the License is distributed on an "AS IS" BASIS,
13WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14See the License for the specific language governing permissions and
15limitations under the License.
16
17Copyright: Dan Vega (danvega@gmail.com)
18$Id: Random.cfc
19--->
20<cfcomponent name="Random">
21
22    <cfset variables.dbtype = "">
23    <cfset variables.dsn = "">
24    
25    <cffunction name="init" access="public" output="false" returntype="Random">
26        <cfargument name="dbtype" type="string" required="true">
27        <cfargument name="dsn" type="string" required="true">
28        <cfset var validDbTypes = "mysql,mssql,postgre,oracle">
29        
30        <cfif NOT listFindNoCase(validDbTypes,arguments.dbtype,",")>
31            <cfthrow message="Invalid dbtype. Valid dbtypes are mysql,mssql,postgre and oracle.">
32        <cfelse>
33            <cfset variables.dbtype = arguments.dbtype>
34            <cfset variables.dsn = arguments.dsn>
35        </cfif>
36        <cfreturn this>
37    </cffunction>
38    
39    <cffunction name="getRandomRow" access="public" output="false" returntype="query">
40        <cfargument name="tablename" type="string" required="true">
41        <cfargument name="topN" type="numeric" default="1">
42        <cfset var qRow = "">
43        
44        <cfswitch expression="#variables.dbtype#">
45            <cfcase value="mysql">
46                <cfquery name="qRow" datasource="#variables.dsn#">
47                SELECT *
48                FROM #arguments.tablename#
49                ORDER BY RAND()
50                LIMIT #arguments.topN#
51                </cfquery>
52            </cfcase>
53            
54            <cfcase value="mssql">
55                <cfquery name="qRow" datasource="#variables.dsn#">
56                SELECT TOP #arguments.topN# *
57                FROM #arguments.tablename#
58                ORDER BY NEWID()
59                </cfquery>
60            </cfcase>
61            
62            <cfcase value="postgre">
63                <cfquery name="qRow" datasource="#variables.dsn#">
64                SELECT #arguments.topN#
65                FROM #arguments.tablename#
66                ORDER BY RANDOM()
67                LIMIT 1
68                </cfquery>
69            </cfcase>
70            
71            <cfcase value="oracle">
72                <cfquery name="qRow" datasource="#variables.dsn#">
73                SELECT * FROM
74                SELECTCT * FROM #arguments.tablename#
75                ORDER BY dbms_random.value )
76                WHERE rownum = #arguments.topN#
77                </cfquery>
78            </cfcase>    
79        </cfswitch>
80        
81        <cfif qRow.recordCount EQ 0>
82            <cfthrow message="The table [#arguments.tablename#] returned 0 records.">
83        </cfif>
84        
85        <cfreturn qRow>
86    </cffunction>
87    
88    <cffunction name="getRandomRows" access="public" output="false" returntype="query">
89        <cfargument name="tablename" type="string" required="true">
90        <cfargument name="topN" type="numeric" required="true">
91        <cfset var results = getRandomRow(arguments.tablename,arguments.topN)>
92        <cfreturn results>
93    </cffunction>
94    
95    <cffunction name="getRandomString" access="public" output="false" returntype="string">
96        <cfargument name="type" type="string" required="true">
97        <cfargument name="length" type="numeric" required="true">
98        <cfset var validTypes = "alpha,alphanum,secure,numeric">
99        <cfset var i = 1>
100        <cfset var randStr = "">
101        <cfset var randNum = "">
102        <cfset var alpha = "A,B,C,D,E,F,G,H,I,J,K,L,
103M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z">
104        <cfset var secure = "!,@,$,%,&,*,-,_,=,+,?,~">
105        
106        <cfif NOT listFindNoCase(validTypes,arguments.type,",")>
107            <cfthrow message="Invalid type. Valid types are alpha,alphanum,secure,numeric.">
108        <cfelse>
109            <!--- RandString() http://www.cflib.org/udf.cfm?id=171&enable=1 --->
110            <cfscript>
111            /**
112             * Returns a random string of the specified length of either alpha, numeric or mixed-alpha-numeric characters.
113             * v2, support for lower case
114             * v3 - more streamlined code
115             *
116             * @param Type      Type of random string to create. (Required)
117             * @param Length      Length of random string to create. (Required)
118             * @return Returns a string.
119             * @author Joshua Miller (josh@joshuasmiller.com)
120             * @version 2, November 4, 2003
121             */
122             for(i=1;i LTE arguments.length;i=i+1){
123             if(arguments.type is "alpha"){
124             randNum=RandRange(1,52);
125             useList=alpha;
126             }else if(arguments.type is "alphanum"){
127             randNum=RandRange(1,62);
128             useList="#alpha#,0,1,2,3,4,5,6,7,8,9";
129             }else if(arguments.type is "secure"){
130             randNum=RandRange(1,73);
131             useList="#alpha#,0,1,2,3,4,5,6,7,8,9,#secure#";
132             }else if(arguments.type is "numeric"){
133             randNum=RandRange(1,10);
134             useList="0,1,2,3,4,5,6,7,8,9";
135             }
136            
137             randStr="#randStr##ListGetAt(useList,randNum)#";
138             }
139             return randStr;
140            </cfscript>
141        </cfif>
142        
143    </cffunction>
144    
145    <cffunction name="getRandomColor" access="public" output="false" returntype="string">
146        <cfargument name="escapePoundSign" type="boolean" default="false">
147        <cfset     var redColor = formatBaseN(randRange(0,255),16)>
148        <cfset    var greenColor = formatBaseN(randRange(0,255),16)>
149        <cfset    var blueColor = formatBaseN(randRange(0,255),16)>    
150        
151        <cfscript>
152        /**
153         * Returns a completely random color. Beautiful, isn't it?
154         * Version 2 by Raymond Camden
155         *
156         * @return Returns a string.
157         * @author Nathan Strutz (nathans@dnsfirm.com)
158         * @version 2, May 13, 2003
159         */
160            
161            if(len(redColor) is 1) redColor = "0" & redColor;
162            if(len(greenColor) is 1) greenColor = "0" & greenColor;
163            if(len(blueColor) is 1) blueColor = "0" & blueColor;
164        
165            return "##" & redColor & greenColor & blueColor;
166        </cfscript>
167    </cffunction>
168    
169    <cffunction name="getRandomDirectory" access="public" output="false" returntype="string">
170        <cfargument name="rootDirectory" type="string" required="true">
171        <cfargument name="recurse" type="boolean" default="false">
172        <cfset var directoryName = "">
173        <cfset var qDirectories = "">
174        <cfset var listing = "">
175        
176        <cfif directoryExists(arguments.rootDirectory)>
177            <cfdirectory action="list" directory="#arguments.rootDirectory#" name="qDirectories" recurse="#arguments.recurse#">
178            <cfif qDirectories.recordCount GT 0>
179                <cfloop query="qDirectories">
180                    <cfif type EQ "dir">
181                        <cfset listing = listAppend(listing,"#name#")>
182                    </cfif>
183                </cfloop>
184            <cfelse>
185                <cfthrow message="The were no directories found under the root directory [#arguments.rootDirectory#].">
186            </cfif>
187        <cfelse>    
188            <cfthrow message="The root directory passed to getRandomDirectoryName is not valid">
189        </cfif>
190        
191        <cfreturn listGetAt(listing,randRange(1,listLen(listing)),",")>
192    </cffunction>
193    
194    <cffunction name="getRandomImage" access="public" output="false" returntype="string">
195        <cfargument name="imageDirectory" type="string" required="true" hint="Absolute path to the direcotry.">
196        <cfset var qFiles = "">
197        <cfset var images = "">
198        
199        <cfif directoryExists(arguments.imageDirectory)>
200            <cfdirectory action="list" directory="#arguments.imageDirectory#" name="qFiles">
201            <cfif qFiles.recordCount GT 0>
202                <cfloop query="qFiles">
203                    <cfif type EQ "File" AND (findNoCase("gif",name) OR findNoCase("jpg",name) OR findNoCase("png",name)) >
204                        <cfset images = listAppend(images,"#name#")>
205                    </cfif>
206                </cfloop>                    
207            <cfelse>
208                <cfthrow message="There were no images found in directory [#arguments.imageDirectory#].">
209            </cfif>
210        <cfelse>
211            <cfthrow message="The root directory passed to getRandomDirectoryName is not valid">
212        </cfif>
213        
214        <cfreturn listGetAt(images,randRange(1,listLen(images)),",")>
215    </cffunction>
216    
217    
218</cfcomponent>


index.cfm (used to test)
view plain print about
1<html>
2<head>
3    <title>Radmom CFC Testing</title>
4</head>
5
6<body>
7
8<cfset rand = createObject("component", "org.danvega.Random")>
9<cfset rand.init("mssql","test")>
10
11<h2>Random Row Example</h2>
12<cfset randrow = rand.getRandomRow("client")>
13<cfdump var="#randrow#">
14<br><br>
15
16<h2>Random Row Example - Table with no rows</h2>
17<cftry>
18    <cfset randrowNull = rand.getRandomRow("school")>
19    <cfdump var="#randrowNull#">
20    <cfcatch type="any">
21        <cfdump var="#cfcatch#">
22    </cfcatch>
23</cftry>
24<br><br>
25
26<h2>Random Rows Example - Return n random rows</h2>
27<cfset randrows = rand.getRandomRow("client", 10)>
28<cfdump var="#randrows#">
29<br><br>
30
31<h2>Random String Example - Return a random string (alpha,10)</h2>
32<cfset randstring = rand.getRandomString("alpha", 10)>
33<cfdump var="#randstring#">
34<br><br>
35
36<h2>Random String Example - Return a random string (alphanum,10)</h2>
37<cfset randstring2 = rand.getRandomString("alphanum", 10)>
38<cfdump var="#randstring2#">
39<br><br>
40
41<h2>Random String Example - Return a random string (secure,10)</h2>
42<cfset randstring3 = rand.getRandomString("secure", 10)>
43<cfdump var="#randstring3#">
44<br><br>
45
46<h2>Random String Example - Return a random string (numeric,10)</h2>
47<cfset randstring4 = rand.getRandomString("numeric", 10)>
48<cfdump var="#randstring4#">
49<br><br>
50
51<h2>Random Color Example - Return a random color</h2>
52<cfset randcolor = rand.getRandomColor()>
53<cfoutput>
54    <h1 style="color:#randcolor#">Random Color</h1>
55</cfoutput>
56<br><br>
57
58<h2>Random Direcotry - An exception thrown with a bad directory</h2>
59<cftry>
60    <cfset randDir = rand.getRandomDirectory("c:\danissweet")>
61    <cfdump var="#randDir#">
62    <cfcatch type="any">
63        <cfdump var="#cfcatch#">
64    </cfcatch>
65</cftry>
66<br><br>
67
68<h2>Random Direcotry - List Random Directory under parent directory thats passed.</h2>
69<cftry>
70    <cfset randDir2 = rand.getRandomDirectory("c:\")>
71    <cfdump var="#randDir2#">
72    <cfcatch type="any">
73        <cfdump var="#cfcatch#">
74    </cfcatch>
75</cftry>
76<br><br>
77
78<!---
79this 1 could take a very long time depending on disk size so by default leave commented out
80
81<h2>Random Direcotry - List Random Directory under parent directory thats passed (same as above except this uses recursive lookup).</h2>
82<cftry>
83    <cfset randDir3 = rand.getRandomDirectory("c:\",true)>
84    <cfdump var="#randDir3#">
85    <cfcatch type="org.danvega.random.dirNotFound">
86        <cfdump var="#cfcatch#">
87    </cfcatch>
88</cftry>
89<br><br>
90--->
91
92<h2>Random Image - List A Random image under a direcotry containing images</h2>
93<cfset randImage = rand.getRandomImage("C:\Documents and Settings\dvega\Desktop")>
94<cfdump var="#randImage#">
95<br><br>
96
97
98<cfdump var="#rand#">
99
100</body>
101</html>

You can download the entire zip file using the download link below. So how does it work, lets dive into the code and take a look. The init method of our component sets our dsn and our dbtype. If an incorrect dbtype is passed our component will return an error message. If it is a valid dbtype then we will initialize our component and return and instance back.

view plain print about
1<cffunction name="init" access="public" output="false" returntype="Random">
2        <cfargument name="dbtype" type="string" required="true">
3        <cfargument name="dsn" type="string" required="true">
4        <cfset var validDbTypes = "mysql,mssql,postgre,oracle">
5        
6        <cfif NOT listFindNoCase(validDbTypes,arguments.dbtype,",")>
7            <cfthrow message="Invalid dbtype. Valid dbtypes are mysql,mssql,postgre and oracle.">
8        <cfelse>
9            <cfset variables.dbtype = arguments.dbtype>
10            <cfset variables.dsn = arguments.dsn>
11        </cfif>
12        <cfreturn this>
13    </cffunction>


Next we have our two methods that deal with our db records. If you read the link to Pete Freitag's article than our getRandomRow method will look familiar. Basically it takes the table name you want to retrieve a random row from, and limits it to 1.
view plain print about
1<cffunction name="getRandomRow" access="public" output="false" returntype="query">
2        <cfargument name="tablename" type="string" required="true">
3        <cfargument name="topN" type="numeric" default="1">
4        <cfset var qRow = "">
5        
6        <cfswitch expression="#variables.dbtype#">
7            <cfcase value="mysql">
8                <cfquery name="qRow" datasource="#variables.dsn#">
9                SELECT *
10                FROM #arguments.tablename#
11                ORDER BY RAND()
12                LIMIT #arguments.topN#
13                </cfquery>
14            </cfcase>
15            
16            <cfcase value="mssql">
17                <cfquery name="qRow" datasource="#variables.dsn#">
18                SELECT TOP #arguments.topN# *
19                FROM #arguments.tablename#
20                ORDER BY NEWID()
21                </cfquery>
22            </cfcase>
23            
24            <cfcase value="postgre">
25                <cfquery name="qRow" datasource="#variables.dsn#">
26                SELECT #arguments.topN#
27                FROM #arguments.tablename#
28                ORDER BY RANDOM()
29                LIMIT 1
30                </cfquery>
31            </cfcase>
32            
33            <cfcase value="oracle">
34                <cfquery name="qRow" datasource="#variables.dsn#">
35                SELECT * FROM
36                SELECTCT * FROM #arguments.tablename#
37                ORDER BY dbms_random.value )
38                WHERE rownum = #arguments.topN#
39                </cfquery>
40            </cfcase>    
41        </cfswitch>
42        
43        <cfif qRow.recordCount EQ 0>
44            <cfthrow message="The table [#arguments.tablename#] returned 0 records.">
45        </cfif>
46        
47        <cfreturn qRow>
48    </cffunction>


Finally, the getRandomRow() method accepts a topN argument. This is so our getRandomRows can work and just use this method instead of reproducing code.
view plain print about
1<cffunction name="getRandomRows" access="public" output="false" returntype="query">
2        <cfargument name="tablename" type="string" required="true">
3        <cfargument name="topN" type="numeric" required="true">
4        <cfset var results = getRandomRow(arguments.tablename,arguments.topN)>
5        <cfreturn results>
6    </cffunction>

There are also some utilty functions I found on cflib that I included in there because I needed them. Again you can download the link below and let me know if you have any problems. I have not had a chance to test against every DMBS and will probably split this into multiple components in the near future. I would love your feedback if you get a chance though. I am also interested in your feedback on how this could be separated. The reason you want to separate this is because the utility functions do not need to know about a dsn or dbtype. It is kind of dumb to have to initialize the component if all you need to do is getRandomString().

 

This entry was posted on December 28, 2006 at 5:13 PM and has received 2649 views. Comments 1 | Print this entry. Download attachment.