Tag: SQL

Yes, I know that CommunityServer 2.0 has been released months ago, but considering the effort in upgrading (and my lack of time), I’m still using CommunityServer 1.x on CSFBL. It does a fine job for what I need at this time.

However, one thing perturbs me — the fact that new forums in a forum group aren’t added in the correct place when you want alphabetical sorting. There’s a good reason for this: CommuntiyServer manages sort order by using a SortOrder value in the database, allowing you to choose any sort order. However, this works against you when you want to use plain old alphabetical sorting.

The solution is to run a SQL script to update the SortOrder value of each forum such that they will be displayed alphabetically. The following SQL script will show you the forums, current sort order, and new sort order for all forums in a given group.

select sectionid, name, sortorder,
( select count(*) from cs_sections
where groupid = cs.groupid and name < cs.name ) as new_sortorder from cs_sections cs where groupid = 8 order by sortorder asc [/source] Pretty simple. You can get the GroupID by looking at the same table, or by looking at the ForumGroupID in the URLs on your web site. To apply these changes: [source='sql'] update cs_sections set sortorder = ( select count(*) from cs_sections where groupid = cs.groupid and name < cs.name ) from cs_sections cs where groupid = 8 [/source] Simple and relatively harmless -- and a heck of a lot easier than trying to use the (buggy) reordering of forums in the admin console!

I’ve used Telligent‘s CommunityServer product for about a year and a half now, and it’s proven to be a good (but not great) forums product. (Note: Version 2.0, recently released, seems to correct that issue.)

While doing some maintenance on the forums as installed for my baseball game, CSFBL, I deleted one forum that was still needed. To correct the problem, I restored a copy of a recent backup as a separate database and ran the following SQL scripts to copy the database from the backup to the production copy.

In the following source code, productionforum is the production database name, and productionforum_old is the old (backup) database name. Note that I haven’t moved every column over (such a read flags, attachment links, edit notes, etc.), but the process for those would be pretty much the same.

On to the SQL…

declare @sectionID int
set @sectionID = 0 --replace with the sectionID to move

--1. Move section
set identity_insert productionforum..cs_sections on

INSERT INTO productionforum..cs_Sections(SectionID, SettingsID, IsActive, ParentID, GroupID, Name, NewsgroupName, Description, DateCreated, Url, IsModerated, DaysToView, SortOrder, TotalPosts, TotalThreads, DisplayMask, EnablePostStatistics, EnableAutoDelete, EnableAnonymousPosting, AutoDeleteThreshold, MostRecentPostID, MostRecentThreadID, MostRecentThreadReplies, MostRecentPostSubject, MostRecentPostAuthor, MostRecentPostAuthorID, MostRecentPostDate, PostsToModerate, ForumType, IsSearchable, ApplicationType, ApplicationKey, PropertyNames, PropertyValues, Path)
select *
from productionforum_old..cs_sections
where sectionid = @sectionID

set identity_insert productionforum..cs_sections off

--2. Move permissions
INSERT INTO productionforum..cs_SectionPermissions(SettingsID, SectionID, RoleID, AllowMask, DenyMask)
select *
from productionforum_old..cs_sectionpermissions
where sectionid = @sectionID

--3. Move moderators
INSERT INTO productionforum..cs_Moderators(UserID, SectionID, DateCreated, EmailNotification, PostsModerated, SettingsID)
select *
from productionforum_old..cs_Moderators
where sectionid = @sectionID

--4. Move threads
set identity_insert productionforum..cs_threads on

INSERT INTO productionforum..cs_Threads(threadid, SectionID, UserID, PostAuthor, PostDate, ThreadDate, LastViewedDate, StickyDate, TotalViews, TotalReplies, MostRecentPostAuthorID, MostRecentPostAuthor, MostRecentPostID, IsLocked, IsSticky, IsApproved, RatingSum, TotalRatings, ThreadEmoticonID, ThreadStatus, SettingsID)
select threadid, SectionID, UserID, PostAuthor, PostDate, ThreadDate, LastViewedDate, StickyDate, TotalViews, TotalReplies, MostRecentPostAuthorID, MostRecentPostAuthor, MostRecentPostID, IsLocked, IsSticky, IsApproved, RatingSum, TotalRatings, ThreadEmoticonID, ThreadStatus, SettingsID
from productionforum_old..cs_threads
where sectionid = @sectionID

set identity_insert productionforum..cs_threads off

--5. Move posts
set identity_insert productionforum..cs_Posts on

INSERT INTO productionforum..cs_Posts(postid, ThreadID, ParentID, PostAuthor, UserID, SectionID, PostLevel, SortOrder, Subject, PostDate, IsApproved, IsLocked, IsIndexed, TotalViews, Body, FormattedBody, IPAddress, PostType, EmoticonID, PropertyNames, PropertyValues, SettingsID, AggViews, PostConfiguration)
select postid, ThreadID, ParentID, PostAuthor, UserID, SectionID, PostLevel, SortOrder, Subject, PostDate, IsApproved, IsLocked, IsIndexed, TotalViews, Body, FormattedBody, IPAddress, PostType, EmoticonID, PropertyNames, PropertyValues, SettingsID, AggViews, PostConfiguration
from productionforum_old..cs_Posts
where sectionid = @sectionID

set identity_insert productionforum..cs_Posts off