cs_forums_Post
ALTER PROCEDURE dbo.cs_forums_Post
(
@PostID int,
@UserID int,
@TrackViews bit,
@SettingsID int,
@MarkRead bit = 0
)
AS
/**//*
Procedure for getting basic information on a single post.
获取单个帖子的基本信息
*/
SET Transaction Isolation Level Read UNCOMMITTED --改变事务的隔离级
/**//* Read UNCOMMITTED 执行脏读或 0 级隔离锁定,这表示不发出共享锁,也不接受排它锁。
当设置该选项时,可以对数据执行未提交读或脏读;在事务结束前可以更改数据内的数值,
行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有语句中的所有表上
设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。*/
DECLARE @NextThreadID int
DECLARE @PrevThreadID int
DECLARE @ThreadID int
DECLARE @SectionID int
DECLARE @SortOrder int
DECLARE @IsApproved bit
DECLARE @IsAnonymousUser bit
SELECT
@ThreadID = ThreadID,
@SectionID = SectionID,
@SortOrder=SortOrder,
@IsApproved = IsApproved
FROM
cs_Posts (nolock)
/**//*不要发出共享锁,并且不要提供排它锁。当此选项生效时,
可能会读取未提交的事务或一组在读取中间回滚的页面。有可能发生脏读。
仅应用于 SELECT 语句*/
WHERE
PostID = @PostID and SettingsID = @SettingsID
/**//*查询此回帖的主题ID,版块ID,排序类型,是否核准*/
-- Do we have an anonymous user?
--判断是否是匿名用户,设置@IsAnonymousUser
SET @IsAnonymousUser = (SELECT IsAnonymous FROM cs_vw_Users_FullUser WHERE cs_UserID = @UserID and SettingsID = @SettingsID)
/**//*系统有几个视图保持着挺多信息的,最好也看下,不过我还没看*/
-- Is the Forum 0 (If so this is a private message and we need to verify the user can view it
--(如果是条个人消息我们要验证此用户是否有权查看)
IF @SectionID = 0
BEGIN
IF NOT EXISTS (SELECT UserID FROM cs_PrivateMessages WHERE UserID = @UserID AND ThreadID = @ThreadID)
/**//*如果不存在此消息,返回*/
RETURN
END
DECLARE @TrackingThread bit
IF @TrackViews = 1
BEGIN --如果跟踪查看为1,更新帖子被浏览的次数
-- Update the counter for the number of times this post is viewed
UPDATE cs_Posts SET TotalViews = (TotalViews + 1) WHERE PostID = @PostID and SettingsID = @SettingsID
/**//*更新帖子表*/
UPDATE cs_Threads SET TotalViews = (TotalViews + 1) WHERE ThreadID = @ThreadID and SettingsID = @SettingsID
/**//*更新主题表*/
END
-- Mark the post as read if this user is not anonymous
--如果用户不是匿名设置帖子可读
IF @MarkRead = 1 AND @IsAnonymousUser = 0 AND @IsApproved = 1
BEGIN
IF NOT EXISTS (SELECT ThreadID FROM cs_ThreadsRead WHERE ThreadID = @ThreadID AND UserID = @UserID and SettingsID = @SettingsID and SectionID = @SectionID)
/**//*如果在读主题表中不存在此记录则插入新记录*/
INSERT INTO cs_ThreadsRead (UserID, ThreadID, SectionID, SettingsID) VALUES (@UserID, @ThreadID, @SectionID, @SettingsID)
END
/**//* 这边被注释了,好象是我们的头注释的
-- get the anonymous user id for this site
if( @UserID = 0 )
BEGIN
exec cs_GetAnonymousUserID @SettingsID, @UserID output
END
IF EXISTS(SELECT ThreadID FROM cs_TrackedThreads (nolock) WHERE ThreadID = @ThreadID AND UserID=@UserID)
SELECT @TrackingThread = 1
ELSE
SELECT @TrackingThread = 0
*/
-- Get tracking thread information
--获取跟踪主题信息
IF (@IsAnonymousUser = 0) AND EXISTS(SELECT ThreadID FROM cs_TrackedThreads WHERE ThreadID = @ThreadID AND UserID=@UserID)
/**//*如果不是匿名用户且存在此主题记录*/
SET @TrackingThread = 1
ELSE
SET @TrackingThread = 0
SELECT
P.PostID, P.ThreadID, P.ParentID, P.PostAuthor, P.UserID, P.SectionID, P.PostLevel, P.SortOrder, P.Subject, P.PostDate, P.IsApproved,
P.IsLocked, P.IsIndexed, P.TotalViews, P.Body, P.FormattedBody, P.IPAddress, P.PostType, P.EmoticonID, P.SettingsID, P.AggViews,
P.PostPropertyNames as PostPropertyNames, P.PostPropertyValues as PostPropertyValues,
P.PostConfiguration,P.UserTime, P.ApplicationPostType, P.PostName,
P.Points as PostPoints, P.RatingSum as PostRatingSum, P.TotalRatings as PostTotalRatings,
U.*, P.PostAuthor as [Username],
T.ThreadDate,
T.StickyDate,
T.IsLocked,
T.IsSticky,
T.RatingSum,
T.TotalRatings,
HasRead = 0,
EditNotes = (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),
IndexInThread = (SELECT Count(PostID) FROM cs_Posts P1 WHERE IsApproved = 1 AND ThreadID = @ThreadID AND SortOrder <= (SELECT SortOrder FROM cs_Posts where PostID = @PostID)),
AttachmentFilename,ContentType, IsRemote, FriendlyFileName, ContentSize, [FileName],p.Created, Height, Width,
-- AttachmentFilename = ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
IsModerator = (SELECT Count(*) FROM cs_Moderators WHERE UserID = U.cs_UserID),
Replies = (SELECT COUNT(*) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
PrevThreadID = 0,
NextThreadID = 0,
UserIsTrackingThread = @TrackingThread
FROM
cs_vw_PostsWithAttachmentDetails P,
--cs_Posts P,
cs_Threads T,
cs_vw_Users_FullUser U
WHERE
P.PostID = @PostID AND
P.ThreadID = T.ThreadID AND
P.UserID = U.cs_UserID and P.SettingsID = @SettingsID and U.SettingsID = @SettingsID
/**//*又是从视图里找,而且找好多字段,因为视图保存了好象字段,非看不可了*/
ALTER PROCEDURE dbo.cs_forums_Post
(
@PostID int,
@UserID int,
@TrackViews bit,
@SettingsID int,
@MarkRead bit = 0
)
AS
/**//*
Procedure for getting basic information on a single post.
获取单个帖子的基本信息
*/
SET Transaction Isolation Level Read UNCOMMITTED --改变事务的隔离级
/**//* Read UNCOMMITTED 执行脏读或 0 级隔离锁定,这表示不发出共享锁,也不接受排它锁。
当设置该选项时,可以对数据执行未提交读或脏读;在事务结束前可以更改数据内的数值,
行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有语句中的所有表上
设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。*/
DECLARE @NextThreadID int
DECLARE @PrevThreadID int
DECLARE @ThreadID int
DECLARE @SectionID int
DECLARE @SortOrder int
DECLARE @IsApproved bit
DECLARE @IsAnonymousUser bit
SELECT
@ThreadID = ThreadID,
@SectionID = SectionID,
@SortOrder=SortOrder,
@IsApproved = IsApproved
FROM
cs_Posts (nolock)
/**//*不要发出共享锁,并且不要提供排它锁。当此选项生效时,
可能会读取未提交的事务或一组在读取中间回滚的页面。有可能发生脏读。
仅应用于 SELECT 语句*/
WHERE
PostID = @PostID and SettingsID = @SettingsID
/**//*查询此回帖的主题ID,版块ID,排序类型,是否核准*/
-- Do we have an anonymous user?
--判断是否是匿名用户,设置@IsAnonymousUser
SET @IsAnonymousUser = (SELECT IsAnonymous FROM cs_vw_Users_FullUser WHERE cs_UserID = @UserID and SettingsID = @SettingsID)
/**//*系统有几个视图保持着挺多信息的,最好也看下,不过我还没看*/
-- Is the Forum 0 (If so this is a private message and we need to verify the user can view it
--(如果是条个人消息我们要验证此用户是否有权查看)
IF @SectionID = 0
BEGIN
IF NOT EXISTS (SELECT UserID FROM cs_PrivateMessages WHERE UserID = @UserID AND ThreadID = @ThreadID)
/**//*如果不存在此消息,返回*/
RETURN
END
DECLARE @TrackingThread bit
IF @TrackViews = 1
BEGIN --如果跟踪查看为1,更新帖子被浏览的次数
-- Update the counter for the number of times this post is viewed
UPDATE cs_Posts SET TotalViews = (TotalViews + 1) WHERE PostID = @PostID and SettingsID = @SettingsID
/**//*更新帖子表*/
UPDATE cs_Threads SET TotalViews = (TotalViews + 1) WHERE ThreadID = @ThreadID and SettingsID = @SettingsID
/**//*更新主题表*/
END
-- Mark the post as read if this user is not anonymous
--如果用户不是匿名设置帖子可读
IF @MarkRead = 1 AND @IsAnonymousUser = 0 AND @IsApproved = 1
BEGIN
IF NOT EXISTS (SELECT ThreadID FROM cs_ThreadsRead WHERE ThreadID = @ThreadID AND UserID = @UserID and SettingsID = @SettingsID and SectionID = @SectionID)
/**//*如果在读主题表中不存在此记录则插入新记录*/
INSERT INTO cs_ThreadsRead (UserID, ThreadID, SectionID, SettingsID) VALUES (@UserID, @ThreadID, @SectionID, @SettingsID)
END
/**//* 这边被注释了,好象是我们的头注释的
-- get the anonymous user id for this site
if( @UserID = 0 )
BEGIN
exec cs_GetAnonymousUserID @SettingsID, @UserID output
END
IF EXISTS(SELECT ThreadID FROM cs_TrackedThreads (nolock) WHERE ThreadID = @ThreadID AND UserID=@UserID)
SELECT @TrackingThread = 1
ELSE
SELECT @TrackingThread = 0
*/
-- Get tracking thread information
--获取跟踪主题信息
IF (@IsAnonymousUser = 0) AND EXISTS(SELECT ThreadID FROM cs_TrackedThreads WHERE ThreadID = @ThreadID AND UserID=@UserID)
/**//*如果不是匿名用户且存在此主题记录*/
SET @TrackingThread = 1
ELSE
SET @TrackingThread = 0
SELECT
P.PostID, P.ThreadID, P.ParentID, P.PostAuthor, P.UserID, P.SectionID, P.PostLevel, P.SortOrder, P.Subject, P.PostDate, P.IsApproved,
P.IsLocked, P.IsIndexed, P.TotalViews, P.Body, P.FormattedBody, P.IPAddress, P.PostType, P.EmoticonID, P.SettingsID, P.AggViews,
P.PostPropertyNames as PostPropertyNames, P.PostPropertyValues as PostPropertyValues,
P.PostConfiguration,P.UserTime, P.ApplicationPostType, P.PostName,
P.Points as PostPoints, P.RatingSum as PostRatingSum, P.TotalRatings as PostTotalRatings,
U.*, P.PostAuthor as [Username],
T.ThreadDate,
T.StickyDate,
T.IsLocked,
T.IsSticky,
T.RatingSum,
T.TotalRatings,
HasRead = 0,
EditNotes = (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),
IndexInThread = (SELECT Count(PostID) FROM cs_Posts P1 WHERE IsApproved = 1 AND ThreadID = @ThreadID AND SortOrder <= (SELECT SortOrder FROM cs_Posts where PostID = @PostID)),
AttachmentFilename,ContentType, IsRemote, FriendlyFileName, ContentSize, [FileName],p.Created, Height, Width,
-- AttachmentFilename = ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
IsModerator = (SELECT Count(*) FROM cs_Moderators WHERE UserID = U.cs_UserID),
Replies = (SELECT COUNT(*) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
PrevThreadID = 0,
NextThreadID = 0,
UserIsTrackingThread = @TrackingThread
FROM
cs_vw_PostsWithAttachmentDetails P,
--cs_Posts P,
cs_Threads T,
cs_vw_Users_FullUser U
WHERE
P.PostID = @PostID AND
P.ThreadID = T.ThreadID AND
P.UserID = U.cs_UserID and P.SettingsID = @SettingsID and U.SettingsID = @SettingsID
/**//*又是从视图里找,而且找好多字段,因为视图保存了好象字段,非看不可了*/