I announced Sueetie Friends and Favorites yesterday, demonstrating how members will be able to follow blog post authors and commenters as well as tag posts and comments as favorites. Thinking ahead to displaying and managing BlogEngine.NET blog favorite data in the Sueetie User Profile, it seemed a good time to extend BlogEngine.NET data availability to Sueetie.Core.
As a quick review, Sueetie supports an unlimited number of BlogEngine.NET blogs both on the site and in Sueetie Groups. Thanks to the flexibility of BlogEngine.NET, this is done very efficiently by storing blog data as XML and membership data in SQL Server. This isolates blog data while requiring only 3 SQL tables to handle all site blog user data. This works great, but looking ahead and outside of the application for things like generating favorite lists, global searching, analytic reporting and so forth, it was time for an improved BlogEngine.NET-Sueetie data co-existence model.
I should mention that one approach to supporting multiple BlogEngine.NET blogs on a site is by using a separate SQL prefix per blog instance (the default is "be_"), but I'm not a fan of this kind of needless-redundancy, or dealing with 10 tables per blog. This model also does little to integrate data among, say, 100 blogs. What a mess.
So here is how BlogEngine.NET data will better co-exist with Sueetie.Core. The current design of blog data in XML and user data in SQL works great, and that won't change. However, what we'll be doing now is copying all blog post and comment data into two Sueetie_ tables using BlogEngine.NET's global event architecture. The two tables, Sueetie_bePosts and Sueetie_beComments, will suffice to house all site blog posts and comments as well as integrate them with other Sueetie data. The Sueetie_bePosts table will have additional SueetieUserID(int) and SueetieBlogID(int) values to support Sueetie framework services, including the generation of User Favorite Posts and Comments lists.
One additional table is required to uniquely identify the blogs on a Sueetie site, Sueetie_Blogs.
Here's a sample record. It should be noted that the BlogKey, here as "blog" matches the blog's directory name, with GroupKey matching the group's directory name. The GroupKey for the Zune group blog at Sueetie.org, for instance, would be "zune." This is used primarily for dynamic URL building. NULL as a GroupKey denotes the blog is not in a group.
Let's tie things together by looking at the Sueetie_bePosts table, where all posts of all blogs will be centrally located. SueetiePostID serves as the Identity Primary Key simply because I like working with Integer IDs rather than Guids. SueetieUserID creates a relationship to Sueetie_Users.UserID and to all Sueetie User data and profiling info. SueetieBlogID keys on Sueetie_Blogs.BlogID. The Sueetie_beComments table is the same structure as its be_PostComment counterpart with the addition of an Int primary key and a SueetieUserID key.
Now back to BlogEngine.NET and how we'll be copying blog post and comment data into SQL Server. BlogEngine.NET's built-in Extensions Architecture makes this a snap. Two simple Sueetie Extensions will create the SQL post and comment records for us, triggered by BlogEngine.NET's Post.Saved and Post.CommentAdded global events.
Here is what the Save Sueetie Post Extension looks like in BlogEngine.NET.
The core of the post and comment record creation logic is ridiculously simple. Our SueetieBlogPost and SueetieBlogComment containers are available in Sueetie.Core and populated in the Extension, then handed off to Sueetie's SQL Data Provider. Below is the SaveSueetiePost Extension method that saves a new or updated blog post to SQL. We're going to use the BlogEngine.NET API to quickly create a post description (excerpt) if it doesn't exist, then populate SueetieBlogPost. The properties in bold are the only original Sueetie items we need to obtain.
private static void SaveToSql(Post post)
string description = post.Description;
description = Utils.StripHtml(post.Content);
if (description.Length > BlogSettings.Instance.DescriptionCharacters)
description = description.Substring(0,
BlogSettings.Instance.DescriptionCharacters) + "...";
SueetieBlogPost sueetieBlogPost = new SueetieBlogPost
SueetieUserID = SueetieContext.Current.User.UserID,
SueetieBlogID = int.Parse(_settings.GetSingleValue("BlogID")),
PostID = post.Id,
Title = post.Title,
Description = description,
PostContent = post.Content,
DateCreated = post.DateCreated,
DateModified = post.DateModified,
Author = post.Author,
IsPublished = post.IsPublished,
IsCommentEnabled = post.IsCommentsEnabled,
Raters = post.Raters,
Rating = post.Rating,
Slug = post.Slug
As a sidebar, nearly all of the code above, ALL Sueetie data provider code and ALL SQL Stored Procedure scripting, was generated using my DRIVE CodeSmith template library. I’ve been rewriting DRIVE from scratch for Sueetie. I've got a ways to go before the library is as robust for Sueetie as it is for Community Server coding, but I'm creating the scripts as I need them for the task and it won't be long where DRIVE for Sueetie will rule the day…and DRIVE for the other will go away. Here's a bonus snapshot of DRIVE for Sueetie for April 13, 2009.
With the weekend's code update complete in laying the groundwork for tighter integration of multiple BlogEngine.NET blog data and the Sueetie framework, I can get back to finishing the Friends and Favorites WCF Services for Sueetie blogs, then move onto Friends and Favorites for YetAnotherForum.NET. See you there!
Please note that this information is also found on "BlogEngine.NET and Sueetie Data Integration" in the Sueetie Wiki, where it will be maintained and updated to include additional data integration steps which may occur in the future.