An Example of Writing Your Own Blog Engine (mini-version)

Preface: Obviously blog engines can be much more comprehensive than what I will be describing in this article. This is just something to show a few techniques to get you started.

This is a three part series, please check back soon for the next two parts (business layer and presentation layer).

The Database (Barebones)

In this blog engine, we’re only going to have two tables; blog posts and blog comments. The tables look as follows:

Blog Posts Table

BlogPosts    
BlogID  (PK, int, not null) Internal ID of blog post
Title (varchar(100), not null) The title of the blog post
Summary (varchar(200), null) A brief description of blog post (displayed in rss lists)
Description (varchar(8000), not null) The actual content of the blog post
DateCreated (datetime, not null) Date blog post was created
DateUpdated (datetime, not null) Date blog post was last updated

Note: We're creating this as though it can only have one blog.  This model
will not support multiple blogs without adding a higher level table to track
individual blogs, but that's a pretty simple enhancement you can make.

Blog Comments Table

BlogComments    
BlogCommentID (PK, int, not null) Internal ID of blog post comment
BlogID (FK, int, not null) ID of blog post the comment responds to
Username (varchar(100), null) Username of user commenting (can be anonymous)
Email (varchar(100), null) Email of user commenting (can be anonymous)
URL (varchar(250), null) URL of trackback/additional content
Comment (varchar(2000), not null) The actual content of the blog post comment
DateCreated (datetime, not null) Date comment was created
DateUpdated (datetime, not null) Date comment was last updated (usually never changes)

Note: An additional enhancement you may want to make is to create an approval process of comments. There are several ways to do this, one way could be to simply add a new column in the BlogComments table which toggles status of comment. An administrator could then manually change the status of comments to be approved or not.

Great, database tables are done – not very hard, right? Next, lets go ahead and write a few stored procedures to handle data manipulation.

SPROC’s

We’re going to create four simple stored procedures to handle creating a new blog post, updating an existing blog post, deleting a blog post, and finally, create a blog comment.

CreateBlogPost

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	Nicholas Barger
-- Create date: 04/01/2008
-- Description:	Creates a new blog post
-- =============================================
CREATE PROCEDURE [dbo].[spCreateBlogPost] 
	@Title varchar(100),
	@Summary varchar(200),
	@Description varchar(8000),
	@BlogID int output
AS 
BEGIN
	SET NOCOUNT ON;

	INSERT INTO dbo.BlogPosts (Title, Summary, Description) VALUES (@Title, @Summary, @Description);

	SELECT @BlogID = SCOPE_IDENTITY();
END
GO

DeleteBlogPost

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	Nicholas Barger
-- Create date: 04/01/2008
-- Description:	Deletes an existing blog post
-- =============================================
CREATE PROCEDURE [dbo].[spDeleteBlogPost] 
	@BlogID int
AS 
BEGIN
	SET NOCOUNT ON;

	DELETE FROM BlogPosts WHERE BlogID = @BlogID;
END
GO

UpdateBlogPost

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	Nicholas Barger
-- Create date: 04/01/2008
-- Description:	Updates an existing blog post
-- =============================================
CREATE PROCEDURE [dbo].[spUpdateBlogPost] 
	@BlogID int, 
	@Title varchar(100),
	@Description varchar(8000),
	@Summary varchar(200) = null
AS 
BEGIN
	SET NOCOUNT ON;

	UPDATE dbo.BlogPosts SET DateUpdated = getDate(), Title = @Title, Summary = @Summary, Description = @Description WHERE BlogID = @BlogID;
END
GO

CreateBlogComment

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	Nicholas Barger
-- Create date: 04/01/2008
-- Description:	Creates a new blog post comment
-- =============================================
CREATE PROCEDURE [dbo].[spCreateBlogComment] 
	@BlogID int, 
	@Username varchar(100) = null,
	@Email varchar(100) = null,
	@URL varchar(250) = null,
	@Comment varchar(2000),
	@BlogCommentID int output
AS 
BEGIN
	SET NOCOUNT ON;

	INSERT INTO dbo.BlogComments (BlogID, Username, Email, URL, Comment) VALUES (@BlogID, @Username, @Email, @URL, @Comment);
	
	SELECT @BlogCommentID = SCOPE_IDENTITY();
END
GO

Ok, we’re going to stop there for this part of the series. Please check back later when I demonstrate how we start using the database we setup!

Remember, this is not only good practice for writing a simple application, but it’s also really beneficial to develop the basis of a blog engine that you can now build any custom features you can’t get with out of the box blog services or engines. So, at the end of this series, be sure to take this code and make it your own by adding to it. Feel free to
write me back with additional features you’ve come up with in the comments below!

One thought on “An Example of Writing Your Own Blog Engine (mini-version)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s