I have developed over the past 5 years a very solid (though perhaps I am
kidding myself...but I think it's spiffy) content management system that is
targeted towards small to mid-size businesses and organizations.
Originally, I wrote it specifically for Microsoft SQL Server, but as I
rolled out a couple more sites based upon this software, I realized that the
smaller non-profits that I am helping might be better served by the lower
end hosting plans that usually only offer MySQL or Access databases. As
such, a SQL Server-specific app wouldn't be as flexible as I needed. Also,
I'm an amazingly lazy...er...I mean, efficient developer, and the last thing
I wanted to do was have to write and support two different codebases.
So, here's the approach I took.
First, I pulled all of my queries into a CFC that acted as a database
abstraction layer (DAL). This had the added benefits of (1) forcing me to
analyze all of my queries so that I could use a single CFC call where I used
to have the same SQL in 4 or 5 different places, and (2) it acts as a "black
box" that takes data in and puts data out...if I want to convert my queries
to stored procedures, all I need to do is modify the DAL. At the same time,
I ensured that I was validating all input data and using cfqueryparam
consistently. I also tried to keep my SQL as simple and straightforward as
possible, and avoided RDBMS-specific functions and keywords wherever
possible (which wasn't always).
Second, I created a CFC that detects if the database is MS SQL, MySQL, or MS
Access:
<cffunction name="getDBType" returntype="query" hint="Pass in a DSN, returns
a query with the database type and version number. Currently supports MS
SQL Server and MySQL.">
<cfargument name="dsn" default="#request.app.dsname#" required="no"
type="string">
<cftry>
<!--- SQL Server --->
<cfquery name="getDBInfo" datasource="#arguments.dsn#">
SELECT 'MSSQL' AS dbtype,
SERVERPROPERTY('productversion') as dbversion
</cfquery>
<cfcatch>
<!--- MySQL --->
<cftry>
<cfquery name="getDBInfo" datasource="#arguments.dsn#">
SELECT 'MYSQL' AS dbtype,
VERSION() as dbversion
</cfquery>
<cfcatch>
<cfset dsStructure = getDSN("#arguments.dsn#")>
<cfif dsStructure.driver EQ "MSSQLServer">
<!--- SQL Server --->
<cfset getDBInfo = queryNew("dbtype, dbversion")>
<cfset newrow = queryAddRow(getDBInfo, 1)>
<cfset newcell = querySetCell(getDBInfo, "dbtype", "MSSQL")>
<cfset newcell = querySetCell(getDBInfo, "dbverison", "")>
<cfelseif dsStructure.driver EQ "MSAccess">
<!--- Access --->
<cfset getDBInfo = queryNew("dbtype, dbversion")>
<cfset newrow = queryAddRow(getDBInfo, 1)>
<cfset newcell = querySetCell(getDBInfo, "dbtype", "MSAccess")>
<cfset newcell = querySetCell(getDBInfo, "dbverison", "")>
<cfelseif dsStructure.driver EQ "MSAccessJet">
<!--- Access with Unicode Support --->
<cfset getDBInfo = queryNew("dbtype, dbversion")>
<cfset newrow = queryAddRow(getDBInfo, 1)>
<cfset newcell = querySetCell(getDBInfo, "dbtype",
"MSAccessUnicode")>
<cfset newcell = querySetCell(getDBInfo, "dbverison", "")>
<cfelse>
</cfif>
</cfcatch>
</cftry>
</cfcatch>
</cftry>
<cfreturn getDBInfo>
</cffunction>
Using try/catch blocks, this allows me to create a structure that contains
the db info; the application.cfc's onApplicationStart method calls this and
then sets an application scoped dbtype variable.
Finally, in my DAL CFC, for those queries that did have RDBMS-specific SQL,
I used that application-scope dbtype variable to run specific SQL; SQL
server is the default case:
<cffunction name="getNavChildElements" access="public" description="Returns
nav child elements." output="No" returntype="query">
<cfargument name="contentid" required="Yes" type="numeric" default="0">
<cfquery name="getNavChildElements" datasource="#request.app.dsname#">
<cfswitch expression="#application.dbtype#">
<cfcase value="mysql">
SELECT C.ID, C.title, C.redirect, C.parentid
FROM tblcontent C
WHERE (C.parentid = (SELECT C.parentid
FROM tblcontent C
WHERE C.parentID = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="#arguments.contentid#">
LIMIT 1) OR
C.parentid = (SELECT C.parentid
FROM tblcontent C
WHERE C.ID = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="#arguments.contentid#">
LIMIT 1)) AND
C.active = 1
ORDER BY C.displayorder
</cfcase>
<cfdefaultcase>
SELECT C.ID, C.title, C.redirect, C.parentid
FROM tblcontent C
WHERE (C.parentid = (SELECT TOP 1 C.parentid
FROM tblcontent C
WHERE C.parentID = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="#arguments.contentid#">) OR
C.parentid = (SELECT TOP 1 C.parentid
FROM tblcontent C
WHERE C.ID = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="#arguments.contentid#">)) AND
C.active = 1
ORDER BY C.displayorder
</cfdefaultcase>
</cfswitch>
</cfquery>
<cfreturn getNavChildElements>
</cffunction>
Note that I only did this for those queries that had RDBMS-specific SQL; for
the simple SELECT blah FROM table sorts of queries, there was no switch/case
statement. In either case, again, it was the same arguments being passed in
to the CFC's, and the same data being returned, so it meant there were no
RDBMS-specific changes needed for the actual front end of the site.
I realize this isn't perfect, but it works well, gives me much simpler
front-end code, and doesn't complicate the DAL CFC all that much (though my
DAL CFC at this point is almost 4,000 lines of code...), and I hope that it
helps out at least one person who is wrestling with a similar problem.
Pete
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311159
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
