ClearCode Standards in practice

I've just watched Ben Nadel's thought provoking presentation on Code Standards. I've always been a fan of standards, Ben takes this further and talks about how the eye and brain process the code on the screen. As a result I thought I'd try comparing the way I would write code with Ben's.

SQL Statements

The conventions I have used when writing SQL statement are:

• All columns from a table should go on one line in the SELECT statement
• SELECT, FROM, WHERE, GROUP BY and ORDER BY should all be indented at the same level
• Table names should be inside square brackets (i.e. [Articles])
• Joined tables should be nested to reflect the one to many relationship between tables
• All columns refereneces should be prefixed with the table name
• The value of the cfsqltype should be upper case as it is a constant (left over from my VB days!)

Here is an example:

<cfquery name="qryArticles" datasource="#Application.Datasource#">
    SELECT 
        [Articles].article_id, [Articles].article_title, [Articles].article_published, 
        [Categories].category_id, [Category].category_title,
        [Topics].topic_id, [Topics].topic_title
    FROM [Articles]
        INNER JOIN [Categories] on ( [Articles].article_category_id = [Categories].category_id )
            INNER JOIN [Topics] on ( [Categories].category_topic_id = [Topics].topic_id )
    WHERE 1 = 1
    <cfif StructKeyExists(Filter, "article_id")>
        AND [Articles].article_id = <cfqueryparam value="#Val(Filter.article_id)#" cfsqltype="CF_SQL_INTEGER" />
    </cfif>
    <cfif StructKeyExists(Filter, "category_id")>
        AND [Categories].category_id = <cfqueryparam value="#Val(Filter.category_id)#" cfsqltype="CF_SQL_INTEGER" />
    </cfif>
    ORDER BY
        [Articles].article_published,
        [Category].category_title
</cfquery>

The same statement using Ben's standards (I apologise Ben if I've got this wrong!).

<cfquery name="qryArticles" datasource="#Application.Datasource#">
    SELECT 
        a.article_id, 
        a.article_title, 
        a.article_published, 
        c.category_id, 
        c.category_title,
        t.topic_id, 
        t.topic_title
    FROM 
        Articles a
    INNER JOIN 
        Categories c 
    ON 
        ( 
            a.article_category_id = c.category_id 
        )
    INNER JOIN 
        Topics t 
    ON
        ( 
            c.category_topic_id = t.topic_id 
        )
    WHERE 
        1 = 1
    <cfif StructKeyExists(Filter, "article_id")>
        AND 
            a.article_id = <cfqueryparam value="#Val(Filter.article_id)#" cfsqltype="cf_sql_integer" />
    </cfif>
    <cfif StructKeyExists(Filter, "category_id")>
        AND 
            c.category_id = <cfqueryparam value="#Val(Filter.category_id)#" cfsqltype="cf_sql_integer" />
    </cfif>
    ORDER BY
        a.article_published,
        c.category_title,
        t.topic_title
</cfquery>

Functions

 My normal style:

<cffunction name="CodeFormat" access="public" hint="I am the pseduo constructor." output="false">
    <cfargument name="content" type="string" required="true" hint="I am the string that will be format and tags escaped" />
    <cfreturn Replace(Replace(HtmlEditFormat(Arguments.content), Chr(10), "<br />", "all"), Chr(09), "&nbsp;&nbsp;", "all") />
</cffunction>

 Ben's way:

<cffunction 
    name="CodeFormat" 
    access="public" 
    hint="I am the format code." 
    output="false">
    
    <cfargument 
        name="content" 
        type="string" 
        required="true" 
        hint="I am the string that will be format and tags escaped" />

    <cfreturn 
        Replace(
            Replace(
                HtmlEditFormat(Arguments.content), 
                Chr(10), 
                "<br />", 
                "all"
            ), 
            Chr(09), 
            "&nbsp;&nbsp;", 
            "all"
        ) />
</cffunction>

Interesting isn't it, which do you find easier to read? You can find out more about Ben's ClearCode Standards Project on his blog:
www.bennadel.com/index.cfm?dax=blog:1211.view

• Posted in:
• SQL
• ColdFusion

2 comments

1. Hi John,
   
   I do a mix, Ben's SQL-style and your CFC-style. best of both worlds ;-)
   
   Comment by Sebastiaan – May 19, 2008
2. Hi Sebastiaan,
   I'm not sure that my style is the best of any world but it works for me :) I have adopted Ben's SQL style but I tend to put the commas in front of the column name (It's a great way of avoiding missing out the comma!) and also I find my style of indenting linked tables easy to see how they are relate to each other. Something this:
   
   SELECT
     a.article_id
     , a.article_title
     , a.article_published
     , c.category_id
     , c.category_title
     , t.topic_id
     , t.topic_title
   FROM [Articles]
     INNER JOIN [Categories] on ( [Articles].article_category_id = [Categories].category_id )
       INNER JOIN [Topics] on ( [Categories].category_topic_id = [Topics].topic_id ) WHERE
     1 = 1
   ORDER BY
     a.article_published
     , c.category_title
     , t.topic_title
   
   Comment by John Whish – May 21, 2008