Blog 4 umbraco 2.0.5 – Comments

Today we'll make an improvement to blog 4 umbraco that shouldn't have any impact on the frontend of the blog but will make it much easier to handle comment spam and moderate comments. We'll move away from storing the comments as documents and store them in a database table instead. We'll fetch them with some xslt extensions (haven't heard of xslt extensions, be sure to check out the umbraco.tv chapter /documentation/videos/for-developers/xslt-extensions/introduction-to-xslt-extensions)

First

The first step is to create this new table, we'll need to store the post id, the actual comment (name, email, website, comment), the timestamp and we'll also add a spam field.

CREATE TABLE [Comment](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [blogid] [int] NOT NULL,
    [postid] [int] NOT NULL,
    [name] [nvarchar](250) NULL,
    [email] [nvarchar](250) NULL,
    [website] [nvarchar](250) NULL,
    [comment] [text] NULL,
    [spam] [bit] NULL,
    [created] [datetime] NULL,
CONSTRAINT [PK_Comment] PRIMARY KEY CLUSTERED
(
    [id] ASC
)

(this is the sql server create script, we'll also include a vistadb and mysql create script in the installer)

Storing comments

Instead of creating a comment document with the umbraco api we'll update the method to insert a record in the new table using the umbraco datalayer (to make sure this can be executed on sql server, vistadb and mysql)

ISqlHelper SqlHelper = DataLayerHelper.CreateSqlHelper(umbraco.GlobalSettings.DbDSN);
            SqlHelper.ExecuteNonQuery(
                @"insert into Comment(blogid,postid,name,email,website,comment,spam,created)
                    values(@blogid,@postid,@name,@email,@website,@comment,@spam,@created)",
                 SqlHelper.CreateParameter("@blogid", blogid),
                SqlHelper.CreateParameter("@postid", blogpost.Id),
                SqlHelper.CreateParameter("@name", name),
                SqlHelper.CreateParameter("@email", email),
                SqlHelper.CreateParameter("@website", website),
                SqlHelper.CreateParameter("@comment", comment),
                SqlHelper.CreateParameter("@spam", false),
                SqlHelper.CreateParameter("@created", DateTime.Now));

Showing comments

For showing the comments we'll add some new methods to our blog xslt extensions. Basicly I want to select all comments or select the comment for a specific post. So I'll need a GetComments() method and a GetCommentsForBlog(int id) method. Both will return some xml (XPathNodeIterator) that will look like this:

<comments>

<comment id="2" postid="1206" created="7/12/2009 16:22:28">

<name>Tim Geyssens</name>

<email>myemail@domain.com</email>

<website>http://www.umbraco.org</website>

<message>This should be stored in the database</message>

</comment>

</comments>

We'll also use the umbraco datalayer to execute these queries and we'll add a method that creates the comment xml from a RecordsReader.

public static XPathNodeIterator GetCommentsForPost(int id)
      {

          ISqlHelper SqlHelper = DataLayerHelper.CreateSqlHelper(umbraco.GlobalSettings.DbDSN);
          IRecordsReader rr = SqlHelper.ExecuteReader(
              "select * from comment where postid = @postid and spam != 1",
               SqlHelper.CreateParameter("@postid", id));

          return CommentsToXml(rr);
      }

 

private static XPathNodeIterator CommentsToXml(IRecordsReader rr)
        {
            XmlDocument xd = new XmlDocument();

            XmlNode x = umbraco.xmlHelper.addTextNode(xd, "comments", "");

            while (rr.Read())
            {
                XmlNode c = xd.CreateElement("comment");

                c.Attributes.Append(umbraco.xmlHelper.addAttribute(xd, "id", rr.GetInt("id").ToString()));
                c.Attributes.Append(umbraco.xmlHelper.addAttribute(xd, "postid", rr.GetInt("postid").ToString()));
                c.Attributes.Append(umbraco.xmlHelper.addAttribute(xd, "created", rr.GetDateTime("created").ToString()));

                c.AppendChild(umbraco.xmlHelper.addCDataNode(xd, "name", rr.GetString("name")));
                c.AppendChild(umbraco.xmlHelper.addCDataNode(xd, "email", rr.GetString("email")));
                c.AppendChild(umbraco.xmlHelper.addCDataNode(xd, "website", rr.GetString("website")));
                c.AppendChild(umbraco.xmlHelper.addCDataNode(xd, "message", rr.GetString("comment")));

                x.AppendChild(c);
            }

            xd.AppendChild(x);

            return xd.CreateNavigator().Select(".");
        }

 

With the new xslt extensions in place we'll just need to update our xslt to fetch the comments from these.

So this is how the list comments xslt will look after the update:

<h3 id="comments">
<xsl:value-of select="count(BlogLibrary:GetCommentsForPost($currentPage/@id)//comment)"/> comment(s)  for  &#8220;<xsl:value-of select="$currentPage/@nodeName"/>&#8221;</h3>

<ol class="commentlist">
<xsl:for-each select="BlogLibrary:GetCommentsForPost($currentPage/@id)//comment">

<li class="alt" id="comment-{@id}">
<div class="commentcontent">
    <div class="commentgravatar" style="float:right;margin-right: 5px;">
        <img width="40px" height="40px" src="{BlogLibrary:getGravatar(./email, 40, '')}" alt="Gravatar of {./name}"/>
    </div>
    <cite>  <xsl:value-of select="./name"/> </cite> Says: <br />
    <small class="commentmetadata"><xsl:value-of select="umbraco.library:LongDate(@created)"/> </small>
    <xsl:value-of select="umbraco.library:ReplaceLineBreaks(./message)" disable-output-escaping="yes"/>
</div>

</li>
</xsl:for-each>
</ol>

That's it, the blog comments are now stored/fetched from a table instead of using documents.