My DAO/Gateway hybrid
June 19, 2008
As a spin off of my post An ORM sceptic a few people have asked me how I handle my database interaction. ColdFusion is great at returning data from a database in a nice query object. ORMs convert this into an object so that you need call a method to access the data in a field. I can see the benefits of this, but it also looses one of the nice features of ColdFusion. Also most people use an RDBMS not an ODBMS, so you are in effect converting the data from the database to ColdFusion and ColdFusion to the database. (Please bare in mind I'm no expert, this is just how I understand it.)
Before I continue, readers should check out Transfer, it is a truly impressive bit of code and is perfect for OO development. If I didn't already have my own code generator I'd have adopted it long ago; I may yet!
So anyway, this is basically what I do....
Each table in the database has a CFC, which has the CRUD (Create, Read, Update and Delete) methods in it. I've removed some validation and error trapping to make the example a bit clearer, but this is pretty much what my code generator produces (the basics of it are here) when it introspects the database (MSSQL):
BlogSubscribers.cfc
<cfcomponent extends="Factory"
displayname="BlogSubscribers"
hint="I act as an interface to the BlogSubscribers database table. One instance handles all the rows in the table">
<cfset this.lockname = CreateUUID() />
<!---
---------------------------------------------------------------
Public Methods
---------------------------------------------------------------
--->
<cffunction name="init"
access="package"
returntype="BlogSubscribers"
output="false"
hint="I am a pseudo constructor method. I instantiate and return this object.">
<cfargument name="datasource"
type="string"
required="true"
hint="I am the datasource to access the database" />
<cfargument name="username"
type="string"
required="false"
default=""
hint="The username to access the datasource" />
<cfargument name="password"
type="string"
required="false"
default=""
hint="The password to access the datasource" />
<cfargument name="populate"
type="boolean"
required="false"
default="false"
hint="I determine if the data should be retrieved from the database when initialised" />
<cfset setDatasource(arguments.datasource) />
<cfset setUsername(arguments.username) />
<cfset setPassword(arguments.password) />
<cfif arguments.populate>
<cfset GetAll() />
</cfif>
<cfreturn this />
</cffunction>
<cffunction name="Create"
access="public"
returntype="numeric"
output="false"
hint="I insert a record into the BlogSubscribers table">
<cfargument name="Insert"
required="true"
type="struct"
hint="At least one param must be passed for safety reasons!" />
<cfset var qryInsert = "" />
<cfif StructCount(Arguments.Insert) gt 0>
<cfquery name="qryInsert" datasource="#getDatasource()#" username="#getUsername()#" password="#getPassword()#">
Insert into [BlogSubscribers] (
[subscriber_email]
, [subscriber_post_id]
, [subscriber_created]
, [subscriber_updated]
)
values (
<cfqueryparam value="#Arguments.Insert.subscriber_email#" cfsqltype="CF_SQL_VARCHAR" />
, <cfqueryparam value="#Arguments.Insert.subscriber_post_id#" cfsqltype="CF_SQL_INTEGER" />
, <cfqueryparam value="#Now()#" cfsqltype="CF_SQL_TIMESTAMP" />
, <cfqueryparam value="#Now()#" cfsqltype="CF_SQL_TIMESTAMP" />
)
select IdentityInsert=@@IDENTITY
</cfquery>
<cfset GetAll( clearCache=True ) />
</cfif>
<cfreturn Val(qryInsert.IdentityInsert) />
</cffunction>
<cffunction name="Read"
access="public"
returntype="query"
output="false"
hint="I return records from the BlogSubscribers table">
<cfargument name="Filter"
required="false"
type="struct"
default="#StructNew()#"
hint="I construct the WHERE clause" />
<cfargument name="Order"
required="false"
type="string"
default=""
hint="I construct the ORDER BY clause" />
<cfset var qrySelectBlogSubscribers = "" />
<cfset var qryGetAll = GetAll() />
<cfquery name="qrySelectBlogSubscribers" dbtype="query">
SELECT
[subscriber_uuid]
, [subscriber_email]
, [subscriber_post_id]
FROM qryGetAll
WHERE 1 = 1
<cfif StructKeyExists(Arguments.Filter, "subscriber_uuid")>
AND [subscriber_uuid] = <cfqueryparam value="#Arguments.Filter.subscriber_uuid#" cfsqltype="CF_SQL_IDSTAMP" />
</cfif>
<cfif StructKeyExists(Arguments.Filter, "subscriber_email")>
AND [subscriber_email] = <cfqueryparam value="#Arguments.Filter.subscriber_email#" cfsqltype="CF_SQL_VARCHAR" />
</cfif>
<cfif StructKeyExists(Arguments.Filter, "subscriber_post_id")>
AND [subscriber_post_id] = <cfqueryparam value="#Arguments.Filter.subscriber_post_id#" cfsqltype="CF_SQL_INTEGER" />
</cfif>
<cfif Trim(Arguments.Order) neq "">
ORDER BY #Arguments.Order#
</cfif>
</cfquery>
<cfreturn qrySelectBlogSubscribers />
</cffunction>
<cffunction name="Update"
access="public"
returntype="numeric"
output="false"
hint="I update records in the specified table based on a filter">
<cfargument name="Update"
required="true"
type="struct"
hint="At least one param must be passed" />
<cfargument name="Filter"
required="true"
type="struct"
hint="I construct the WHERE clause" />
<cfset var qryUpdateBlogSubscribers = "" />
<cfset var AffectedRows = -1 />
<cfif StructCount(Arguments.Update) gt 0>
<cfquery name="qryUpdateBlogSubscribers" datasource="#getDatasource()#" username="#getUsername()#" password="#getPassword()#">
UPDATE [BlogSubscribers] SET
<cfif StructKeyExists(Arguments.Update, "subscriber_email")>
[subscriber_email] = <cfqueryparam value="#Arguments.Update.subscriber_email#" cfsqltype="CF_SQL_VARCHAR" />,
</cfif>
<cfif StructKeyExists(Arguments.Update, "subscriber_post_id")>
[subscriber_post_id] = <cfqueryparam value="#Arguments.Update.subscriber_post_id#" cfsqltype="CF_SQL_INTEGER" />,
</cfif>
[subscriber_updated] = <cfqueryparam value="#Now()#" cfsqltype="CF_SQL_TIMESTAMP" />,
WHERE 1 = 1
<cfif StructKeyExists(Arguments.Filter, "subscriber_uuid")>
AND [subscriber_uuid] = <cfqueryparam value="#Arguments.Filter.subscriber_uuid#" cfsqltype="CF_SQL_IDSTAMP" />
</cfif>
<cfif StructKeyExists(Arguments.Filter, "subscriber_email")>
AND [subscriber_email] = <cfqueryparam value="#Arguments.Filter.subscriber_email#" cfsqltype="CF_SQL_VARCHAR" />
</cfif>
<cfif StructKeyExists(Arguments.Filter, "subscriber_post_id")>
AND [subscriber_post_id] IN ( <cfqueryparam value="#Arguments.Filter.subscriber_post_id#" cfsqltype="CF_SQL_INTEGER" list="true" /> )
</cfif>
SELECT AffectedRows=@@ROWCOUNT
</cfquery>
<cfset AffectedRows = Val(qryUpdateBlogSubscribers.AffectedRows) />
<cfset GetAll( clearCache=True ) />
</cfif>
<cfreturn AffectedRows />
</cffunction>
<cffunction name="Delete"
access="public"
returntype="numeric"
output="false"
hint="I delete records from the BlogSubscribers table">
<cfargument name="Filter"
required="true"
type="struct"
hint="At least one param must be passed for safety reasons!" />
<cfset var qryDeleteBlogSubscribers = "" />
<cfset var AffectedRows = -1 />
<cfif StructCount(Arguments.Filter) gt 0>
<cfquery name="qryDeleteBlogSubscribers" datasource="#getDatasource()#" username="#getUsername()#" password="#getPassword()#">
DELETE FROM [BlogSubscribers]
WHERE 1 = 1
<cfif StructKeyExists(Arguments.Filter, "subscriber_uuid")>
AND [subscriber_uuid] = <cfqueryparam value="#Arguments.Filter.subscriber_uuid#" cfsqltype="CF_SQL_IDSTAMP" />
</cfif>
<cfif StructKeyExists(Arguments.Filter, "subscriber_email")>
AND [subscriber_email] = <cfqueryparam value="#Arguments.Filter.subscriber_email#" cfsqltype="CF_SQL_VARCHAR" />
</cfif>
<cfif StructKeyExists(Arguments.Filter, "subscriber_post_id")>
AND [subscriber_post_id] = <cfqueryparam value="#Arguments.Filter.subscriber_post_id#" cfsqltype="CF_SQL_INTEGER" />
</cfif>
SELECT AffectedRows=@@ROWCOUNT
</cfquery>
<cfset AffectedRows = Val(qryDeleteBlogSubscribers.AffectedRows) />
<cfset GetAll( clearCache=True ) />
</cfif>
<cfreturn AffectedRows />
</cffunction>
<!---
---------------------------------------------------------------
Private Methods
---------------------------------------------------------------
--->
<cffunction name="GetAll"
access="private"
returntype="query"
output="false"
hint="I return all records from the BlogSubscribers table">
<cfargument name="clearCache"
default="false"
type="boolean" />
<cfset var qrySelectAllBlogSubscribers = "" />
<cfset var CacheTimeSpan = "" />
<cfif arguments.clearCache>
<cfset CacheTimeSpan = CreateTimeSpan(0,0,0,0) />
<cfelse>
<cfset CacheTimeSpan = CreateTimeSpan(0,0,15,0) />
</cfif>
<cfquery name="qrySelectAllBlogSubscribers" datasource="#getDatasource()#" username="#getUsername()#" password="#getPassword()#" cachedwithin="#CacheTimeSpan#">
SELECT [subscriber_uuid]
, [subscriber_email]
, [subscriber_post_id]
FROM [BlogSubscribers]
INNER JOIN [BlogPosts]
ON ( [BlogPosts].[post_id] = [BlogSubscribers].[subscriber_post_id] )
</cfquery>
<cfreturn qrySelectAllBlogSubscribers />
</cffunction>
</cfcomponent>
Each of these CFCs go into a folder with a CFC which I use to manage them (I call it a factory but I'm not sure that is the correct term!).
Factory.cfc
<cfcomponent
displayname="DataFactory"
hint="I initiate and act as an base class for the database cfcs.">
<cfset this.lockname = CreateUUID() />
<cfset variables.instance = StructNew() />
<!---
---------------------------------------------------------------
Public Methods
---------------------------------------------------------------
--->
<cffunction name="init"
access="public"
output="false"
returntype="Factory"
hint="I am a pseudo constructor method. I instantiate and return this object.">
<cfargument name="datasource"
type="string"
required="true"
hint="I am the datasource to access the database" />
<cfargument name="username"
type="string"
required="false"
default=""
hint="The username to access the datasource" />
<cfargument name="password"
type="string"
required="false"
default=""
hint="The password to access the datasource" />
<cfargument name="populate"
type="boolean"
required="false"
default="false"
hint="I determine if the data should be retrieved from the database when initialised" />
<cfset var qryFileList = "" />
<!---
load and initiate classes for each table
--->
<cfdirectory
directory="#GetDirectoryFromPath(GetCurrentTemplatePath())#"
action="list"
name="qryFileList"
filter="*.cfc" />
<cfloop query="qryFileList">
<cfif qryFileList.Name neq GetFileFromPath(GetCurrentTemplatePath())>
<cfset This[ListFirst(qryFileList.Name, ".")] = CreateObject( "component", ListFirst(qryFileList.name, "." ) ).init( datasource=arguments.datasource , username=arguments.username , password=arguments.password , populate=arguments.populate ) />
</cfif>
</cfloop>
<cfreturn this />
</cffunction>
<!---
---------------------------------------------------------------
Private Getters
---------------------------------------------------------------
--->
<cffunction name="getDatasource"
access="private"
returntype="string"
output="false"
hint="I return the datasource name.">
<cfreturn variables.instance.datasource />
</cffunction>
<cffunction name="getUsername"
access="private"
returntype="string"
output="false"
hint="I return the username used to connect to the database.">
<cfreturn variables.instance.username />
</cffunction>
<cffunction name="getPassword"
access="private"
returntype="string"
output="false"
hint="I return the password used to connect to the database.">
<cfreturn variables.instance.password />
</cffunction>
<!---
---------------------------------------------------------------
Private Setters
---------------------------------------------------------------
--->
<cffunction name="setDatasource"
access="private"
returntype="void"
output="false"
hint="I set the datasource name.">
<cfargument name="datasource"
type="string"
required="true" />
<cfset variables.instance.datasource = arguments.datasource />
</cffunction>
<cffunction name="setUsername"
access="private"
returntype="void"
output="false"
hint="I set the username to connect to the database.">
<cfargument name="username"
type="string"
required="true" />
<cfset variables.instance.username = arguments.username />
</cffunction>
<cffunction name="setPassword"
access="private"
returntype="void"
output="false"
hint="I set the password to connect to the database.">
<cfargument name="password"
type="string"
required="true" />
<cfset variables.instance.password = arguments.password />
</cffunction>
</cfcomponent>
In onApplicationStart of Application.cfc:
<cfset Application.Data = CreateObject( "component", "pathtocfc.factory" ).init( datasource=application.datasource ) />
The Factory.cfc goes off and dynamically loads each cfc (so you can just drop new ones in and it will find them). You can now use the objects.
To get data:
<cfset filter = StructNew() /> <cfset filter.subscriber_post_id = 1 /> <cfset qryObject = Application.Data.BlogSubscribers.Read(filter) /
As you can see I can use any field in the database table to filter by.
To delete:
<cfset filter = StructNew() /> <cfset filter.subscriber_post_id = '12,40' /> <cfset affectedrows = Application.Data.BlogSubscribers.Delete(filter) />
This will delete all rows that have a post id of 12 or 40 (by doing an SQL IN (12,40))
To update:
<cfset filter = StructNew() /> <cfset filter.subscriber_email = 'old@email.com' /> <cfset filter.subscriber_post_id = '12,40' /> <cfset data = StructNew() /> <cfset data.subscriber_email = 'new@email.com' /> <cfset affectedrows = Application.Data.BlogSubscribers.Update(data, filter) />
This will update the email address field value to the value 'new@email.com' where the email address was 'old@email.com' and the post id was in 12 or 40.
The benefits of this approach is that I don't need to use the primary key to update or select records, any field can be used. An additional benefit is that the updated field in each table is populated automatically when the table is updated.
It's not OO but it works well for me! :)
- Posted in:
- SQL
- ColdFusion
2 comments
Leave a comment
If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)
Have you looked at DataMgr? Just like your solution, it takes structures and returns query recordsets.
It doesn't require you to have SQL in your code. It doesn't provide for built-in methods to update or delete multiple records with one command, but it does work on several databases and plenty of other nifty features.
I think it mixes well with cfquery when you have the need to write complex queries.
Free and open source, of course.
Comment by Steve Bryant – June 20, 2008
Yes I watched your DataMgr presentation on UGTV a while back, the "active schema" part of it is very cool and something I have thought about trying to replicate. It's a very impressive bit of code!
Using structs seem logical to me - I just don't like having a DAO and gateway (why have both!?). I frequently have to do delete or update records without using the primary key, something like "delete expired accounts".
I quite often end up writing complex queries (subqueries, case statements, tables linking to themselves etc), which is why I like having my basic CRUD methods in a CFC because I can then add my own methods or tweak the existing ones (such as changing the cache time). I don't like having SQL queries in different places so tend to use one solution for all database interaction.
Because I wrote the generator I can also add in a generated search / save method to meet by project requirements. Maybe I'm just a control freak :)
For anyone else reading; Steve's DataMgr can be found at:
datamgr.riaforge.org/
Comment by John Whish – June 20, 2008