Trunkful.com <cf_gems>: BlogCFC, PostgreSQL and Bluedragon

Sun	Mon	Tue	Wed	Thu	Fri	Sat
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

BlogCFC, PostgreSQL and Bluedragon

Posted At : November 28, 2008 2:51 PM | Posted By : Wil Genovese
Related Categories: BlueDragon,ColdFusion,PostgreSQL

In my description of this blog I wrote that I converted BlogCFC to work with PostgresSQL. It did not take much to make the conversion and the code is still fully compatible with the other versions of databases that are officially supported. There is absolutely no reason why PostgreSQL should not be supported.

So here are some examples of the changes I made. Most of the changes had to due with date functions and in most cases can be spotted in the code by looking for db type "PGSQL".

In the file blog.cfc version 5.9.002

First add PostgreSQL as a valid db type.


<cfset validDBTypes = "MSACCESS,MYSQL,MSSQL,ORACLE,PGSQL">

Other changes are to cfif statements based on db type

Or the negative of the db type.

The getActiveDays() method

<cffunction name="getActiveDays" returnType="string" output="false" hint="Returns a list of days with Entries.">
   <cfargument name="year" type="numeric" required="true">
   <cfargument name="month" type="numeric" required="true">
   <cfset var dtMonth = createDateTime(arguments.year,arguments.month,1,0,0,0)>
   <cfset var dtEndOfMonth = createDateTime(arguments.year,arguments.month,daysInMonth(dtMonth),23,59,59)>
   <cfset var days = "">
   <cfset var posted = "">
   <cfif instance.blogDBType is "MSSQL">
      <cfset posted = "dateAdd(hh, #instance.offset#, tblblogentries.posted)">
   <cfelseif instance.blogDBType is "MSACCESS">
      <cfset posted = "dateAdd('h', #instance.offset#, tblblogentries.posted)">
   <cfelseif instance.blogDBType is "MYSQL">
      <cfset posted = "date_add(posted, interval #instance.offset# hour)">
   <cfelseif instance.blogDBType is "ORACLE">
      <cfset posted = "tblblogentries.posted + (#instance.offset#/24)">
      
   <cfelseif instance.blogDBType is "PGSQL">
      <cfset posted = "posted + interval '#instance.offset# hours'">
   </cfif>
   <cfquery datasource="#instance.dsn#" name="days" username="#instance.username#" password="#instance.password#">
      select distinct
         <cfif trim(instance.offset) NEQ "">
            <cfif instance.blogDBType is "MSSQL">
               datepart(dd, #preserveSingleQuotes(posted)#)
            <cfelseif instance.blogDBType is "MYSQL">
               extract(day from #preserveSingleQuotes(posted)#)
            <cfelseif instance.blogDBType is "MSACCESS">
               datepart('d', #preserveSingleQuotes(posted)#)
            <cfelseif instance.blogDBType is "ORACLE">
               to_char(#preserveSingleQuotes(posted)#, 'dd')
               
            <cfelseif instance.blogDBType is "PGSQL">
               date_part('day',#preserveSingleQuotes(posted)#)
            </cfif> as posted_day
         <cfelse>
            posted as posted_day
         </cfif>
      from tblblogentries
      where
         #preserveSingleQuotes(posted)# >= <cfqueryparam value="#dtMonth#" cfsqltype="CF_SQL_TIMESTAMP">
         and
         #preserveSingleQuotes(posted)# <= <cfqueryparam value="#dtEndOfMonth#" cfsqltype="CF_SQL_TIMESTAMP">
         and blog = <cfqueryparam value="#instance.name#" cfsqltype="CF_SQL_VARCHAR" maxlength="50">
         and   #preserveSingleQuotes(posted)# < <cfqueryparam cfsqltype="cf_sql_timestamp" value="#blogNow()#">
         and   released = 1
   </cfquery>
   <cfreturn valueList(days.posted_day)>
</cffunction>

Lines 1113 - 1138 the getIt query in the getEntry() method

<cfquery name="getIt" datasource="#instance.dsn#" username="#instance.username#" password="#instance.password#">
   select      tblblogentries.id, tblblogentries.title,
      
      <cfif trim(instance.offset) NEQ "">
         <cfif instance.blogDBType is "MSACCESS">
         dateAdd('h', #instance.offset#, tblblogentries.posted) as posted,
         <cfelseif instance.blogDBType is "MSSQL">
         dateAdd(hh, #instance.offset#, tblblogentries.posted) as posted,
         <cfelseif instance.blogDBType is "ORACLE">
         tblblogentries.posted + (#instance.offset#/24) as posted,
         
         <cfelseif instance.blogDBType is "PGSQL">
         tblblogentries.posted + interval '#instance.offset# hours' as posted,
         <cfelse>
         date_add(posted, interval #instance.offset# hour) as posted,
         </cfif>
      <cfelse>
         posted,
      </cfif>
      tblblogentries.body,
      tblblogentries.morebody, tblblogentries.alias, tblusers.name, tblblogentries.allowcomments,
      tblblogentries.enclosure, tblblogentries.filesize, tblblogentries.mimetype, tblblogentries.released, tblblogentries.mailed
   from   tblblogentries, tblusers
   where   tblblogentries.id = <cfqueryparam value="#arguments.id#" cfsqltype="CF_SQL_VARCHAR" maxlength="35">
   and tblblogentries.blog = <cfqueryparam value="#instance.name#" cfsqltype="CF_SQL_VARCHAR" maxlength="50">
   and tblblogentries.username = tblusers.username
</cfquery>

I made many changes throughout blog.cfc including recording the ip addresses of those that leave comments. At the end of this post you can click the download icon and get the modified version of the file blog.cfc that this blog is using as of the date of this post. Included in the file is the sql script to create the BlogCFC data structure in PostgreSQL. This is compatible with PostgreSQL 8.1 and up. Also note that this version does run on BlueDragon 7.x.