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.

view plain print about
1<!--- Valid database types --->
2<cfset validDBTypes = "MSACCESS,MYSQL,MSSQL,ORACLE,PGSQL">

Other changes are to cfif statements based on db type

view plain print about
1<cfif listFindNoCase("MSSQL,MSACCESS,PGSQL", instance.blogDBType)> ..</cfif>

Or the negative of the db type.

view plain print about
1<cfif ListFindNoCase("MSSQL,MSACCESS",instance.blogDBType)>....</cfif>

The getActiveDays() method

view plain print about
1<cffunction name="getActiveDays" returnType="string" output="false" hint="Returns a list of days with Entries.">
2    <cfargument name="year" type="numeric" required="true">
3    <cfargument name="month" type="numeric" required="true">
4    <cfset var dtMonth = createDateTime(arguments.year,arguments.month,1,0,0,0)>
5    <cfset var dtEndOfMonth = createDateTime(arguments.year,arguments.month,daysInMonth(dtMonth),23,59,59)>
6    <cfset var days = "">
7    <cfset var posted = "">        
8    <cfif instance.blogDBType is "MSSQL">
9        <cfset posted = "dateAdd(hh, #instance.offset#, tblblogentries.posted)">
10    <cfelseif instance.blogDBType is "MSACCESS">
11        <cfset posted = "dateAdd('h', #instance.offset#, tblblogentries.posted)">
12    <cfelseif instance.blogDBType is "MYSQL">
13        <cfset posted = "date_add(posted, interval #instance.offset# hour)">
14    <cfelseif instance.blogDBType is "ORACLE">
15        <cfset posted = "tblblogentries.posted + (#instance.offset#/24)">
16        <!--- HERE --->
17    <cfelseif instance.blogDBType is "PGSQL">
18        <cfset posted = "posted + interval '#instance.offset# hours'">
19    </cfif>                
20    <cfquery datasource="#instance.dsn#" name="days" username="#instance.username#" password="#instance.password#">
21        select distinct
22            <cfif trim(instance.offset) NEQ "">
23                <cfif instance.blogDBType is "MSSQL">
24                    datepart(dd, #preserveSingleQuotes(posted)#)
25                <cfelseif instance.blogDBType is "MYSQL">
26                    extract(day from #preserveSingleQuotes(posted)#)
27                <cfelseif instance.blogDBType is "MSACCESS">
28                    datepart('d', #preserveSingleQuotes(posted)#)
29                <cfelseif instance.blogDBType is "ORACLE">
30                    to_char(#preserveSingleQuotes(posted)#, 'dd')
31                    <!--- HERE --->    
32                <cfelseif instance.blogDBType is "PGSQL">
33                    date_part('day',#preserveSingleQuotes(posted)#)
34                </cfif> as posted_day
35            <cfelse>
36                posted as posted_day
37            </cfif>
38        from tblblogentries
39        where
40            #preserveSingleQuotes(posted)# >= <cfqueryparam value="#dtMonth#" cfsqltype="CF_SQL_TIMESTAMP">
41            and
42            #preserveSingleQuotes(posted)# <= <cfqueryparam value="#dtEndOfMonth#" cfsqltype="CF_SQL_TIMESTAMP">
43            and blog = <cfqueryparam value="#instance.name#" cfsqltype="CF_SQL_VARCHAR" maxlength="50">
44            and    #preserveSingleQuotes(posted)# < <cfqueryparam cfsqltype="cf_sql_timestamp" value="#blogNow()#">
45            and    released = 1
46    </cfquery>
47    <cfreturn valueList(days.posted_day)>
48</cffunction>

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

view plain print about
1<cfquery name="getIt" datasource="#instance.dsn#" username="#instance.username#" password="#instance.password#">
2    select        tblblogentries.id, tblblogentries.title,
3        <!--- Handle offset --->
4        <cfif trim(instance.offset) NEQ "">
5            <cfif instance.blogDBType is "MSACCESS">
6            dateAdd('h', #instance.offset#, tblblogentries.posted) as posted,
7            <cfelseif instance.blogDBType is "MSSQL">
8            dateAdd(hh, #instance.offset#, tblblogentries.posted) as posted,
9            <cfelseif instance.blogDBType is "ORACLE">
10            tblblogentries.posted + (#instance.offset#/24) as posted,
11            <!--- HERE --->
12            <cfelseif instance.blogDBType is "PGSQL">
13            tblblogentries.posted + interval '#instance.offset# hours' as posted,
14            <cfelse>
15            date_add(posted, interval #instance.offset# hour) as posted,
16            </cfif>
17        <cfelse>
18            posted,
19        </cfif>
20        tblblogentries.body,
21        tblblogentries.morebody, tblblogentries.alias, tblusers.name, tblblogentries.allowcomments,
22        tblblogentries.enclosure, tblblogentries.filesize, tblblogentries.mimetype, tblblogentries.released, tblblogentries.mailed
23    from    tblblogentries, tblusers
24    where    tblblogentries.id = <cfqueryparam value="#arguments.id#" cfsqltype="CF_SQL_VARCHAR" maxlength="35">
25    and tblblogentries.blog = <cfqueryparam value="#instance.name#" cfsqltype="CF_SQL_VARCHAR" maxlength="50">
26    and tblblogentries.username = tblusers.username
27</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.

This entry was posted on November 28, 2008 at 2:51 PM and has received 1740 views. There are currently 0 comments. Print this entry. Download attachment.