Log In:Register.
Generic DAO Object
HI all,

I'm back for a small article on a new object I threw together called genericDao. I created this object for a quick project I was working on for my brother's DnD group, with the hopes of being able to access multiple tables with the same object with minimal setup. I got the basic idea from talking with a friend (Alan Szlosek). He told me how he was working with a new class type that allowed him to access database rows as an object. This object would retrieve, cache (when necessary), insert and update 1 row of a database table with little or no set up needed. Now since he used PhP to do it, and php classes have some functionality that I am lacking with CF, my implementation is not as clean as his probably is. Please note, that I understand my friend did not invent this type of database access, its around for quite a while if I"m not mistaken. he is just the one who told me about it. Ok, now that I've gotten the disclaimer out of the way, I can move on...

First, Let me also say that this object is not meant to access only 1 row of data. Though that idea intrigues me, this object is intended to be a generic DAO to access a whole table using CRUD (i've also added L for listing the table).

Ok, so onto the object itself. Here is how you initialize it in CF:

<cfset dao = createObject('component','components.genericDao').init(createObject('component','components.datasource').init('dsn'),'tableName')/>


You'll probably notice the inclusion of my datasource object. I've started doing this. Basically this object just includes 2 functions, init(dsn) and getDatasourceName(). Fairly simple. You'll also notice that I have to send the table name in. In my friends implementation in PhP, he can call the name of the class and merely name the object the same as the table. I love this idea, and would love to use it, but as far as I know, CF doesn't allow this.

Ok, so time for the Init() function:

init()
 <cffunction name="init" access="public" output="false">
    <cfargument name="datasource"/>
        <cfargument name="tableName"/>
        <cfset variables.datasource = arguments.datasource/>
        <cfset variables.tableName = arguments.tableName/>
        <cfset variables.cache = structNew()/>
        <cfquery name="variables.tableDef" datasource="#variables.datasource.getDatasourceName()#">
         describe #variables.tableName#
        </cfquery>
        <cfloop query="variables.tableDef">
         <cfif key eq 'pri'>
             <cfset variables.primaryKey = field/>
                <cfbreak>
            </cfif>
        </cfloop>
        <cfreturn this/>
    </cffunction>

Ok, so what i'm doing here is simple. Store the arguments in the CFC's variables scope, and then query the table definition from the database. You'll also notice that I loop through the table definition and find the table's primary key. This is used for caching, reading, and updating rows. I know there could be a more generic way of doing this, but for my quick implementation, it fit the bill quite nicely.

For the rest of the object,, the functions are quite simple. The major exceptions are the Update() and Create() functions that have to decide if you sent in the correct data. Basically I just loop through the table definition, if you have a key in the data structure sent into the object that matches a column name in the table, it adds that row and it's value to the query. Also, in update I have to verify the existence of the primary key in the data structure since it is required to update the row. In my friend's single row object, this wouldn't be as necessary as the object holds all the rows values and the id in the object itself. Anywho, let's check out the object as a whole:
genericDAO
<cfcomponent>

 <cffunction name="init" access="public" output="false">
     <cfargument name="datasource"/>
        <cfargument name="tableName"/>
        <cfset variables.datasource = arguments.datasource/>
        <cfset variables.tableName = arguments.tableName/>
        <cfset variables.cache = structNew()/>
        <cfquery name="variables.tableDef" datasource="#variables.datasource.getDatasourceName()#">
         describe #variables.tableName#
        </cfquery>
        <cfloop query="variables.tableDef">
         <cfif key eq 'pri'>
             <cfset variables.primaryKey = field/>
                <cfbreak>
            </cfif>
        </cfloop>
        <cfreturn this/>
    </cffunction>
    
    <cffunction name="create" access="public" output="false">
     <cfargument name="formData"/>
        <cfset var newIdQry = '/>
        <cfset var newRowQry = '/>
        <cfquery datasource="#variables.datasource.getDatasourceName()#">
         insert
            into   #tableName#
                   (<cfloop query="variables.tableDef"><cfif key neq 'pri' and not extra contains 'auto_increment' and structkeyExists(arguments.formData,field)>#field#<cfif currentrow neq reccordcount or (variables.tableDef.key[currentRow+1] neq 'pri' and not variables.tableDef.extra[currentrow+1] contains 'auto_increment')>,</cfif></cfif></cfloop>)
            values (<cfloop query="variables.tableDef"><cfif key neq 'pri' and not extra contains 'auto_increment' and structkeyExists(arguments.formData,field)>#arugments.formData[field]#<cfif currentrow neq reccordcount or (variables.tableDef.key[currentRow+1] neq 'pri' and not variables.tableDef.extra[currentrow+1] contains 'auto_increment')>,</cfif></cfif></cfloop>)
        </cfquery>
        <cfquery name="hewIdQry" datasource="#variables.datasource.getDatasourceName()#">
         select last_insert_id() as _id
            from   #variables.tableName#
        </cfquery>
        <cfset read(newIdQry._id)/>
        <cfreturn newIdQry._id/>
    </cffunction>
   
    <cffunction name="read" access="public" output="false">
     <cfargument name="id" type="numeric"/>
        <cfset var retQry = '/>
        <cfif checkCache(arugments.id)>
         <cfset retQry = retrieveFromCache(arguments.id)/>
        <cfelse>
            <cfquery name="retQry" datasource="#variables.datasoruce.getDatasourceName()#">
                select *
                from   #variables.tableName#
             where  #variables.primaryKey# = '#arguments.id#'
            </cfquery>
            <cfset cacheData(retQry)/>
        </cfif>
        <cfreturn retQry/>
    </cffunction>
   
    <cffunction name="list" access="public" output="true">
     <cfargument name="cache" required="no" default="false"/>
     <cfset var retQry = '/>
        <cfset var tmpqry = '/>
        <cfquery name="retQry" datasource="#variables.datasource.getDatasourceName()#">
         select *
            from   #variables.tableName#
            <cfif structKeyExists(arguments,'where')>
            where  #arguments.where#
            </cfif>
            <cfif structKeyExists(arguments,'order_by')>
            order by #arguments.order_by#
            </cfif>
            <cfif structKeyExists(arguments,'limit')>
            limit  #arguments.limit#
            </cfif>
        </cfquery>
        <cfif arguments.cache>
            <cfloop query="retQry">
                <cfif not checkCache(retQry[variables.primaryKey][currentRow])>
                    <cfquery name="tmpQry" dbtype="query">
                        select *
                        from   retQry
                        where  #variables.primaryKey# = #retQry[variables.primarykey][currentRow]#
                    </cfquery>
                    <cfset cacheData(tmpQry)/>
                </cfif>
            </cfloop>
        </cfif>
        <cfreturn retQry/>
    </cffunction>
   
    <cffunction name="update" access="public" output="false">
     <cfargument name="data"/>
        <cfif not structKeyExists(variables.data,variables.primaryKey)>
         <cfthrow detail="In order to update you must send in a value for '#variables.primaryKey#' (the primary key for table '#vavriables.tableName#')."/>
        </cfif>
        <cfquery datasource="#variables.datasource.getDatasourceName()#">
         update #variables.tableName#
               set <cfloop query="variables.tableDef"><cfif key neq 'pri' and not extra contains 'auto_increment' and structkeyExists(arguments.formData,field)>#field# = '#argments.data[field]#'<cfif currentrow neq reccordcount or (variables.tableDef.key[currentRow+1] neq 'pri' and not variables.tableDef.extra[currentrow+1] contains 'auto_increment')>,</cfif></cfif></cfloop>
            where #variables.primaryKey# = '#arguments.data[variables.primaryKey]#'
        </cfquery>
        <cfreturn arguments.data[variables.primaryKey]/>
    </cffunction>
   
    <cffunction name="delete" access="public" output="false">
     <cfargument name="id"/>
        <cfquery datasource="#variables.datasource.getDatasourceName()#">
         delete
            from   #vavriables.tableName#
            where  #variables.primaryKey# = '#arguments.id#'
        </cfquery>
    </cffunction>
   
    <cffunction name="cacheData" access="private" output="true">
     <cfargument name="data"/>
        <cfset var tmp = arguments.data[variables.primaryKey]/>
        <cfset variables.cache[tmp] = arguments.data/>
    </cffunction>
   
    <cffunction name="checkCache" access="private" output="false">
     <cfargument name="id">
        <cfreturn structKeyExists(variables.cache,arguments.id)/>
    </cffunction>
   
    <cffunction name="retrieveFromCache" access="private" output="false">
     <cfargument name="id"/>
        <cfreturn variales.cache[arguments.id]/>
    </cffunction>
   
    <cffunction name="retrieveCache" access="public" output="false">
     <cfreturn variables.cache/>
    </cffunction>

</cfcomponent>

Some will notice my lack of sql injection protection. I do plan on adding this, I just have yet to do it. Quick and dirty, remember ;).

Ok, so what did I do with this? Well, Wizards of the Coast (the current publishers of ADnD), released some of their core data in the form of an SRD a couple of years ago, and there were some very nice people that put this information into tables. So, what I am currently doing (with the help of some of my homebrew ajax code), is create a page that allows the DnD group to search the tables with relative ease based on the name of the data.  Here is my code for the page that actually retrieves the data:
retriever.cfm
<cfset dao = createObject('component','components.genericDao').init(createObject('component','components.datasource').init('gas'),'dndsrd_' & form.table)/>
<cfset tmp = dao.list(where='#form.searchField# = "#where#"')/>
<cfif tmp.recordcount eq 0>
<cfset tmp = dao.list(where='#form.searchField# like "%#where#%"')/>
</cfif>
<cfcontent reset="yes"/>
<cfif tmp.recordcount eq 1>
 <cfoutput>#tmp.full_text#</cfoutput>
<cfelseif tmp.recordCount gt 1>
 <ol>
 <cfoutput query="tmp">
        <li><a href="javascript:populateFormField('where','#form.table#','#name#');submitForm(document['#form.table#'    ],'#form.table#Contents')">#name#</a></li>
    </cfoutput>
    </ol>
<cfelse>
 Nothing found!
</cfif>

At its core its pretty basic, I've added some support if your search criteria isn't found exactly and then finds rows with data that may be what you were looking for and sends back a list of those items. It's not really relavant, but you can see that the actually code to retrieve the data is quite simple. I just send the table name, the column I want to search, and the criteria i'm searching for and thats it.

Here is the actual page:


Ok, there you go. play with it. don't know what to search for. Just leave the search field blank and click 'Search' and it will return a list of all objects in that table. Also note that if you check 'Remember Search' it will save a cookie with that search so every time you return to this page it will automatically search for that criteria and load the results. The [-] is also cookie enabled. If you leave a box closed up, it will remember it even if it has to load data. There are a couple of other features, but this is off topic from this writing.

I hope you enjoyed reading this. I didn't get to deep into the object itself, but it is pretty self explanatory. I do plan on playing with it some more to see what I can do with it and seeing how useful it really can be. Basically right now, I can use it to throw up quick pages of data, or in instances where I don't really need the functions of a more specialize DAO.
The technique my friend was referring to is called ORM I believe, and seems to be popuplar in the Ruby world
(1)Comments | < Back
Comments
Tuesday, April 3, 2007 by StreetMonk
It's your DnD group too.... :-P
-StreetMonk
streetmonk@gas-productions.com
Leave a Comment
Please log in to leave a comment.