OpenLab V4 博客DB脚本备份

终于写完了,可能后续的修改不会少,先备份一下吧。

更多信息:http://openlab.codeplex.com/SourceControl/list/changesets

 

 blogs

/****** 对象:  Schema [blogs]    脚本日期: 01/07/2010 18:18:37 ******/
CREATE SCHEMA [blogs] AUTHORIZATION [dbo]
GO
/****** 对象:  Table [blogs].[Blogs]    脚本日期: 01/07/2010 18:18:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [blogs].[Blogs](
    
[BlogId] [int] IDENTITY(1,1NOT NULL,
    
[CreatorUserId] [int] NOT NULL,
    
[Slug] [nvarchar](128NOT NULL,
    
[LoweredSlug] [nvarchar](128NOT NULL,
    
[DisplayName] [nvarchar](256NOT NULL,
    
[LoweredDisplayName] [nvarchar](256NOT NULL,
    
[Description] [nvarchar](512NOT NULL,
    
[Theme] [nvarchar](256NOT NULL,
    
[CreatedDateUtc] [datetime] NOT NULL CONSTRAINT [DF_Blogs_CreatedDateUtc]  DEFAULT (getutcdate()),
    
[LastPostDateUtc] [datetime] NOT NULL CONSTRAINT [DF_Blogs_LastPostDateUtc]  DEFAULT (((1900)-(1))-(1)),
    
[LastCommentDateUtc] [datetime] NOT NULL CONSTRAINT [DF_Blogs_LastCommentDateUtc]  DEFAULT (((1900)-(1))-(1)),
    
[TotalPosts] [int] NOT NULL CONSTRAINT [DF_Blogs_TotalPosts]  DEFAULT ((0)),
    
[TotalComments] [int] NOT NULL CONSTRAINT [DF_Blogs_TotalComments]  DEFAULT ((0)),
    
[SortOrder] [int] NOT NULL CONSTRAINT [DF_Blogs_SortOrder]  DEFAULT ((0)),
    
[EnableComments] [bit] NOT NULL CONSTRAINT [DF_Blogs_EnableComments]  DEFAULT ((1)),
    
[IsActive] [bit] NOT NULL CONSTRAINT [DF_Blogs_IsActive]  DEFAULT ((1)),
    
[Property] [xml] NOT NULL,
 
CONSTRAINT [PK_Blog] PRIMARY KEY CLUSTERED 
(
    
[BlogId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Blogs_Name] ON [blogs].[Blogs] 
(
    
[LoweredDisplayName] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Blogs_Slug] ON [blogs].[Blogs] 
(
    
[LoweredSlug] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'博客Id,用来唯一标识一个博客' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'@level2type=N'COLUMN',@level2name=N'BlogId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'创建者的用户Id' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'@level2type=N'COLUMN',@level2name=N'CreatorUserId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'博客站点的英文标识,即友好Url' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'@level2type=N'COLUMN',@level2name=N'Slug'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'小写的博客站点的英文标识' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'@level2type=N'COLUMN',@level2name=N'LoweredSlug'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'博客显示名称' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'@level2type=N'COLUMN',@level2name=N'DisplayName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'小写的博客显示名称' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'@level2type=N'COLUMN',@level2name=N'LoweredDisplayName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'博客描述,即副标题' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'@level2type=N'COLUMN',@level2name=N'Description'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'博客使用的主题名' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'@level2type=N'COLUMN',@level2name=N'Theme'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'博客创建时间' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'@level2type=N'COLUMN',@level2name=N'CreatedDateUtc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'最近发帖时间' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'@level2type=N'COLUMN',@level2name=N'LastPostDateUtc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'最近一次回复的时间' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'@level2type=N'COLUMN',@level2name=N'LastCommentDateUtc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子总数' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'@level2type=N'COLUMN',@level2name=N'TotalPosts'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'评论总数' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'@level2type=N'COLUMN',@level2name=N'TotalComments'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'排序编号' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'@level2type=N'COLUMN',@level2name=N'SortOrder'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'是否允许评论; 1:允许 0:不允许' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'@level2type=N'COLUMN',@level2name=N'EnableComments'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'是否激活
true, 已激活,可正常访问
false, 未激活,不能正常访问
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'@level2type=N'COLUMN',@level2name=N'IsActive'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'序列化XML存储扩展属性,详见Openlab.Modules.Forums.ForumPostProperty类' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'@level2type=N'COLUMN',@level2name=N'Property'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'名称: 博客表
说明:
记录所有博客站点以及其相关属性配置。提供博客Id,作为其他博客相关表的外键。
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Blogs'
GO
/****** 对象:  Table [blogs].[PostBody]    脚本日期: 01/07/2010 18:18:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [blogs].[PostBody](
    
[PostId] [int] NOT NULL,
    
[Body] [nvarchar](maxNOT NULL,
    
[FormattedBody] [nvarchar](maxNOT NULL,
 
CONSTRAINT [PK_PostBody] PRIMARY KEY CLUSTERED 
(
    
[PostId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子Id' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostBody'@level2type=N'COLUMN',@level2name=N'PostId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子原始内容' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostBody'@level2type=N'COLUMN',@level2name=N'Body'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'格式化后的帖子内容' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostBody'@level2type=N'COLUMN',@level2name=N'FormattedBody'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'名称: 帖子内容表
说明:
记录帖子的实际内容和格式化内容,通过[PK, FK]PostId进行查询
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostBody'
GO
/****** 对象:  Table [blogs].[PostComments]    脚本日期: 01/07/2010 18:19:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [blogs].[PostComments](
    
[PostId] [int] NOT NULL,
    
[CommentId] [int] NOT NULL,
    
[BlogId] [int] NOT NULL,
    
[CommentDateUtc] [datetime] NOT NULL,
 
CONSTRAINT [PK_PostComments] PRIMARY KEY NONCLUSTERED 
(
    
[CommentId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_PostComments_CommentDateUtc] ON [blogs].[PostComments] 
(
    
[CommentDateUtc] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PostComments_BlogId] ON [blogs].[PostComments] 
(
    
[BlogId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PostComments_PostId] ON [blogs].[PostComments] 
(
    
[PostId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子编号,标识评论所属的帖子' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostComments'@level2type=N'COLUMN',@level2name=N'PostId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'评论编号,唯一标识一个评论' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostComments'@level2type=N'COLUMN',@level2name=N'CommentId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'博客编号,标识评论所属的博客' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostComments'@level2type=N'COLUMN',@level2name=N'BlogId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'评论创建时间' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostComments'@level2type=N'COLUMN',@level2name=N'CommentDateUtc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'名称: 博客/帖子下评论索引表
说明:
博客/帖子下所有评论索引表,按照评论时间排序。

查询某博客/帖子下所有评论时,使用当前表;
“假”删除评论时,从该表中删除;
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostComments'
GO
/****** 对象:  Table [blogs].[Comments]    脚本日期: 01/07/2010 18:18:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [blogs].[Comments](
    
[CommentId] [int] IDENTITY(1,1NOT NULL,
    
[BlogId] [int] NOT NULL,
    
[PostId] [int] NOT NULL,
    
[UserId] [int] NOT NULL,
    
[ParentId] [int] NOT NULL CONSTRAINT [DF_Comments_ReplyId]  DEFAULT ((0)),
    
[Subject] [nvarchar](256NOT NULL,
    
[PostAuthor] [nvarchar](256NOT NULL CONSTRAINT [DF_Comments_PostAuthor]  DEFAULT (''),
    
[IPAddress] [bigint] NOT NULL CONSTRAINT [DF_Comments_IPAddress]  DEFAULT ((0)),
    
[EmailAddress] [nvarchar](256NOT NULL,
    
[WebUrl] [nvarchar](256NOT NULL,
    
[TotalViews] [int] NOT NULL,
    
[CommentDateUtc] [datetime] NOT NULL CONSTRAINT [DF_Comments_CommentDateUtc]  DEFAULT (getutcdate()),
    
[FormattedBody] [nvarchar](maxNOT NULL,
    
[State] [tinyint] NOT NULL CONSTRAINT [DF_Comments_State]  DEFAULT ((0)),
 
CONSTRAINT [PK_Comment] PRIMARY KEY CLUSTERED 
(
    
[CommentId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'评论Id,唯一标识一个评论' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Comments'@level2type=N'COLUMN',@level2name=N'CommentId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'评论所属的博客Id' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Comments'@level2type=N'COLUMN',@level2name=N'BlogId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'评论所属的帖子Id' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Comments'@level2type=N'COLUMN',@level2name=N'PostId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'评论者的UserId' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Comments'@level2type=N'COLUMN',@level2name=N'UserId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'本条评论所回复的评论Id,默认为0' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Comments'@level2type=N'COLUMN',@level2name=N'ParentId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'评论标题' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Comments'@level2type=N'COLUMN',@level2name=N'Subject'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'评论者昵称' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Comments'@level2type=N'COLUMN',@level2name=N'PostAuthor'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'评论者IP' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Comments'@level2type=N'COLUMN',@level2name=N'IPAddress'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'评论人Email' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Comments'@level2type=N'COLUMN',@level2name=N'EmailAddress'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'评论人网站Url' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Comments'@level2type=N'COLUMN',@level2name=N'WebUrl'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'浏览次数' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Comments'@level2type=N'COLUMN',@level2name=N'TotalViews'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'评论添加时间' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Comments'@level2type=N'COLUMN',@level2name=N'CommentDateUtc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'格式化后的评论内容' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Comments'@level2type=N'COLUMN',@level2name=N'FormattedBody'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'评论状态
0, 未设置
1, 正常
2, 待审核
3, 已删除
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Comments'@level2type=N'COLUMN',@level2name=N'State'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'名称: 博客评论列表
说明:
记录所有博客的评论信息
向其它评论信息相关表提供[FK]CommentId
通过[FK]BlogId指定所属的博客
通过[FK]PostId指定所属的帖子
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Comments'
GO
/****** 对象:  Table [blogs].[BlogPosts]    脚本日期: 01/07/2010 18:18:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [blogs].[BlogPosts](
    
[BlogId] [int] NOT NULL,
    
[PostId] [int] NOT NULL,
    
[UserId] [int] NOT NULL,
    
[PostDateUtc] [datetime] NOT NULL,
 
CONSTRAINT [PK_BlogPosts] PRIMARY KEY NONCLUSTERED 
(
    
[PostId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_BlogPosts_PostDateUtc] ON [blogs].[BlogPosts] 
(
    
[PostDateUtc] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_BlogPosts_BlogId] ON [blogs].[BlogPosts] 
(
    
[BlogId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'博客Id,标识帖子所属的博客' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'BlogPosts'@level2type=N'COLUMN',@level2name=N'BlogId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子Id, 唯一标识一个帖子' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'BlogPosts'@level2type=N'COLUMN',@level2name=N'PostId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'发帖用户的Id' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'BlogPosts'@level2type=N'COLUMN',@level2name=N'UserId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子发布时间,在此表中用于排序' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'BlogPosts'@level2type=N'COLUMN',@level2name=N'PostDateUtc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'名称: 博客/用户下帖子论索引表
说明:
博客/用户下所有帖子索引表,按照评论时间排序。

查询某博客/用户下所有帖子时,使用当前表;
“假”删除帖子时,从该表中删除;
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'BlogPosts'
GO
/****** 对象:  Table [blogs].[PostCategoryRelationship]    脚本日期: 01/07/2010 18:19:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [blogs].[PostCategoryRelationship](
    
[PostCategoryId] [int] NOT NULL,
    
[PostId] [int] NOT NULL,
    
[PostDateUtc] [datetime] NOT NULL,
    
[State] [tinyint] NOT NULL CONSTRAINT [DF_PostCategoryRelationship_State]  DEFAULT ((0)),
 
CONSTRAINT [PK_PostsInCategory] PRIMARY KEY NONCLUSTERED 
(
    
[PostCategoryId] ASC,
    
[PostId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_PostCategoryRelationship_PostDateUtc] ON [blogs].[PostCategoryRelationship] 
(
    
[PostDateUtc] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PostCategoryRelationship_PostId] ON [blogs].[PostCategoryRelationship] 
(
    
[PostId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子分类Id' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostCategoryRelationship'@level2type=N'COLUMN',@level2name=N'PostCategoryId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子Id' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostCategoryRelationship'@level2type=N'COLUMN',@level2name=N'PostId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子发布时间,在此表中用于排序' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostCategoryRelationship'@level2type=N'COLUMN',@level2name=N'PostDateUtc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子的状态
0, 未设置
1, 正常
2, 待审核
3, 已删除
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostCategoryRelationship'@level2type=N'COLUMN',@level2name=N'State'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'名称: Post, Category关系表
说明:
通过[FK]PostId和[FK]CategoryId链接帖子和帖子所属的分类;
分类与帖子为多对多的关系;

查询分类下的帖子时使用:通过CategoryId获取PostIds
查询帖子所属分类时使用:通过PostId获取CategoryIds
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostCategoryRelationship'
GO
/****** 对象:  Table [blogs].[PostTagRelationship]    脚本日期: 01/07/2010 18:19:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [blogs].[PostTagRelationship](
    
[PostId] [int] NOT NULL,
    
[PostTagId] [int] NOT NULL,
    
[PostDateUtc] [datetime] NOT NULL,
    
[State] [tinyint] NOT NULL CONSTRAINT [DF_PostTagRelationship_State]  DEFAULT ((0)),
 
CONSTRAINT [PK_PostTagRelationship] PRIMARY KEY NONCLUSTERED 
(
    
[PostId] ASC,
    
[PostTagId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_PostTagRelationship_PostDateUtc] ON [blogs].[PostTagRelationship] 
(
    
[PostDateUtc] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PostTagRelationship_PostId] ON [blogs].[PostTagRelationship] 
(
    
[PostId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子Id' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostTagRelationship'@level2type=N'COLUMN',@level2name=N'PostId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'标记Id' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostTagRelationship'@level2type=N'COLUMN',@level2name=N'PostTagId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子发布时间,在此表中用于排序' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostTagRelationship'@level2type=N'COLUMN',@level2name=N'PostDateUtc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子的状态
0, 未设置
1, 正常
2, 待审核
3, 已删除
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostTagRelationship'@level2type=N'COLUMN',@level2name=N'State'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'名称: Post,PostTag关系表
说明:
通过[FK]PostId和[FK]PostTagId链接帖子和帖子所属的PostTag;
PostTag与帖子为多对多的关系;

查询PostTag下的帖子时使用:通过PostTagId获取PostIds
查询帖子所属PostTag时使用:通过PostId获取PostTagIds
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostTagRelationship'
GO
/****** 对象:  Table [blogs].[PostSlugs]    脚本日期: 01/07/2010 18:19:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [blogs].[PostSlugs](
    
[BlogId] [int] NOT NULL,
    
[PostId] [int] NOT NULL,
    
[LoweredSlug] [nvarchar](128NOT NULL,
 
CONSTRAINT [PK_PostSlugs] PRIMARY KEY CLUSTERED 
(
    
[LoweredSlug] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'所属的博客Id' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostSlugs'@level2type=N'COLUMN',@level2name=N'BlogId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子Id' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostSlugs'@level2type=N'COLUMN',@level2name=N'PostId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'小写的帖子Url' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'PostSlugs'@level2type=N'COLUMN',@level2name=N'LoweredSlug'
GO
/****** 对象:  Table [blogs].[Links]    脚本日期: 01/07/2010 18:18:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [blogs].[Links](
    
[LinkId] [int] IDENTITY(1,1NOT NULL,
    
[BlogId] [int] NOT NULL,
    
[LinkCategoryId] [int] NOT NULL,
    
[Title] [nvarchar](256NOT NULL,
    
[Description] [nvarchar](512NOT NULL CONSTRAINT [DF_Links_Description]  DEFAULT (''),
    
[Url] [nvarchar](256NOT NULL,
    
[RSS] [nvarchar](256NOT NULL,
    
[CreatedDateUtc] [datetime] NOT NULL CONSTRAINT [DF_Links_CreatedDateUtc]  DEFAULT (getutcdate()),
    
[NewWindow] [bit] NOT NULL,
    
[State] [tinyint] NOT NULL CONSTRAINT [DF_Links_State]  DEFAULT ((0)),
    
[SortOrder] [int] NOT NULL,
 
CONSTRAINT [PK_Link] PRIMARY KEY CLUSTERED 
(
    
[LinkId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Links_CategoryId] ON [blogs].[Links] 
(
    
[BlogId] ASC,
    
[LinkCategoryId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'链接Id,唯一标识一个链接' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Links'@level2type=N'COLUMN',@level2name=N'LinkId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'链接所属的博客Id' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Links'@level2type=N'COLUMN',@level2name=N'BlogId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'链接所属的分类Id' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Links'@level2type=N'COLUMN',@level2name=N'LinkCategoryId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'链接标题' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Links'@level2type=N'COLUMN',@level2name=N'Title'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'链接描述' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Links'@level2type=N'COLUMN',@level2name=N'Description'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'链接地址' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Links'@level2type=N'COLUMN',@level2name=N'Url'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'链接目标的RSS地址' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Links'@level2type=N'COLUMN',@level2name=N'RSS'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'链接创建时间' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Links'@level2type=N'COLUMN',@level2name=N'CreatedDateUtc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'是否在新窗口中打开' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Links'@level2type=N'COLUMN',@level2name=N'NewWindow'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子状态
0, 未设置
1, 正常
2, 待审核
3, 已删除
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Links'@level2type=N'COLUMN',@level2name=N'State'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'排序' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Links'@level2type=N'COLUMN',@level2name=N'SortOrder'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'名称: 友情链接表
说明:
记录博客的友情链接信息
通过[FK]BlogId指定所属的博客
通过[FK]CategoryId指定所属的分类
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Links'
GO
/****** 对象:  Table [blogs].[Posts]    脚本日期: 01/07/2010 18:19:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [blogs].[Posts](
    
[PostId] [int] IDENTITY(1,1NOT NULL,
    
[BlogId] [int] NOT NULL,
    
[UserId] [int] NOT NULL,
    
[PostAuthor] [nvarchar](256NOT NULL CONSTRAINT [DF_Posts_PostAuthor]  DEFAULT (''),
    
[Slug] [nvarchar](128NOT NULL,
    
[LoweredSlug] [nvarchar](128NOT NULL CONSTRAINT [DF_Posts_LoweredSlug]  DEFAULT (''),
    
[Subject] [nvarchar](256NOT NULL CONSTRAINT [DF_Posts_Subject]  DEFAULT (''),
    
[Summary] [nvarchar](512NOT NULL CONSTRAINT [DF_Posts_Summary]  DEFAULT (''),
    
[PostDateUtc] [datetime] NOT NULL CONSTRAINT [DF_Posts_PostDate]  DEFAULT (getutcdate()),
    
[ApprovedDateUtc] [datetime] NOT NULL CONSTRAINT [DF_Posts_ApprovedDate]  DEFAULT (((1900)-(1))-(1)),
    
[TotalViews] [int] NOT NULL CONSTRAINT [DF_Posts_TotalViews]  DEFAULT ((0)),
    
[TotalComments] [int] NOT NULL CONSTRAINT [DF_Posts_TotalComments]  DEFAULT ((0)),
    
[TotalAggViews] [int] NOT NULL CONSTRAINT [DF_Posts_TotalAggViews]  DEFAULT ((0)),
    
[RatingAverage] [float] NOT NULL CONSTRAINT [DF_Posts_RatingAverage]  DEFAULT ((0)),
    
[IPAddress] [bigint] NOT NULL CONSTRAINT [DF_Posts_IPAddress]  DEFAULT ((0)),
    
[EnableComments] [bit] NOT NULL CONSTRAINT [DF_Posts_EnableComments]  DEFAULT ((1)),
    
[EnableRatings] [bit] NOT NULL CONSTRAINT [DF_Posts_EnableRatings]  DEFAULT ((1)),
    
[IsBodyHtml] [bit] NOT NULL CONSTRAINT [DF_Posts_IsBodyHtml]  DEFAULT ((1)),
    
[State] [tinyint] NOT NULL CONSTRAINT [DF_Posts_State]  DEFAULT ((0)),
    
[Property] [xml] NOT NULL,
 
CONSTRAINT [PK_Post] PRIMARY KEY CLUSTERED 
(
    
[PostId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子Id,用来唯一标识一个帖子' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'PostId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子所属的博客Id' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'BlogId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'发帖人UserId' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'UserId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'发帖人显示名' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'PostAuthor'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子友好Url' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'Slug'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'小写的Slug' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'LoweredSlug'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子标题' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'Subject'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子摘要' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'Summary'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'发布时间' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'PostDateUtc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'通过审核时间' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'ApprovedDateUtc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'浏览次数' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'TotalViews'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'评论次数' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'TotalComments'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'TotalAggViews'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'评分平均值' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'RatingAverage'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'发帖人IP' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'IPAddress'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'是否可以评论' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'EnableComments'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'是否可以评分 ' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'EnableRatings'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子内容是否为Html' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'IsBodyHtml'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'帖子状态
0, 未设置
1, 正常
2, 待审核
3, 已删除
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'State'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'对于帖子的其他信息,序列化为XML保存,详见Openlab.Modules.Forums.ForumThreadProperty类' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'@level2type=N'COLUMN',@level2name=N'Property'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'名称: 帖子信息表
说明:
记录帖子信息, 包括帖子各种参数、设置等短属性;
通过[FK]BlogId指定所属的博客
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Posts'
GO
/****** 对象:  Table [blogs].[BlogOwners]    脚本日期: 01/07/2010 18:18:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [blogs].[BlogOwners](
    
[BlogId] [int] NOT NULL,
    
[UserId] [int] NOT NULL,
    
[CreateDateUtc] [datetime] NOT NULL CONSTRAINT [DF_Owner_CreateDateUtc]  DEFAULT (getutcdate()),
 
CONSTRAINT [PK_Owner] PRIMARY KEY CLUSTERED 
(
    
[BlogId] ASC,
    
[UserId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Owners_UserId] ON [blogs].[BlogOwners] 
(
    
[UserId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'博客Id,外键,见Blog表' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'BlogOwners'@level2type=N'COLUMN',@level2name=N'BlogId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'用户Id,外键,见Membership表' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'BlogOwners'@level2type=N'COLUMN',@level2name=N'UserId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'博客添加时间' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'BlogOwners'@level2type=N'COLUMN',@level2name=N'CreateDateUtc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'名称: 博客用户表
说明:
通过[FK]BlogId和[FK]UserId记录所有博客站点及其所有者的映射关系;记录博客开通时间
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'BlogOwners'
GO
/****** 对象:  Table [blogs].[Categories]    脚本日期: 01/07/2010 18:18:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [blogs].[Categories](
    
[CategoryId] [int] IDENTITY(1,1NOT NULL,
    
[BlogId] [int] NOT NULL,
    
[ParentId] [int] NOT NULL CONSTRAINT [DF_Categories_ParentId]  DEFAULT ((0)),
    
[CategoryType] [tinyint] NOT NULL,
    
[CategoryName] [nvarchar](256NOT NULL,
    
[LoweredCategoryName] [nvarchar](256NOT NULL,
    
[Slug] [nvarchar](128NOT NULL,
    
[LoweredSlug] [nvarchar](128NOT NULL,
    
[Description] [nvarchar](512NOT NULL,
    
[CreatedDateUtc] [datetime] NOT NULL,
    
[TotalEntities] [int] NOT NULL,
    
[SortOrder] [int] NOT NULL,
    
[State] [tinyint] NOT NULL CONSTRAINT [DF_Categories_State1]  DEFAULT ((0)),
 
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED 
(
    
[CategoryId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Categories_Name] ON [blogs].[Categories] 
(
    
[BlogId] ASC,
    
[CategoryType] ASC,
    
[LoweredCategoryName] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Categories_Slug] ON [blogs].[Categories] 
(
    
[BlogId] ASC,
    
[CategoryType] ASC,
    
[LoweredSlug] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'分类Id,用来唯一标识一个分类' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Categories'@level2type=N'COLUMN',@level2name=N'CategoryId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'博客Id,用来标识分类所属的博客' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Categories'@level2type=N'COLUMN',@level2name=N'BlogId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'父分类的Id' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Categories'@level2type=N'COLUMN',@level2name=N'ParentId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'分类类别,对应//todo枚举
1. 帖子类别
2. 帖子Tag
3. 链接类别
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Categories'@level2type=N'COLUMN',@level2name=N'CategoryType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'分类名称' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Categories'@level2type=N'COLUMN',@level2name=N'CategoryName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'小写的分类名称' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Categories'@level2type=N'COLUMN',@level2name=N'LoweredCategoryName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'分类友好Url' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Categories'@level2type=N'COLUMN',@level2name=N'Slug'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'小写的分类友好Url' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Categories'@level2type=N'COLUMN',@level2name=N'LoweredSlug'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'分类描述' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Categories'@level2type=N'COLUMN',@level2name=N'Description'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'分类创建时间' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Categories'@level2type=N'COLUMN',@level2name=N'CreatedDateUtc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'分类下的实体数量' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Categories'@level2type=N'COLUMN',@level2name=N'TotalEntities'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'排列顺序号,用于显示' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Categories'@level2type=N'COLUMN',@level2name=N'SortOrder'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'评论状态
0, 未设置
1, 正常
2, 待审核
3, 已删除
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Categories'@level2type=N'COLUMN',@level2name=N'State'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description'@value=N'名称: 实体分类表
说明:
存储所有实体的分类,通过CategoriesType区分为:
1. 帖子类别
2. 帖子Tag
3. 链接类别
通过[FK]BlogId指定所属的博客
' , @level0type=N'SCHEMA',@level0name=N'blogs'@level1type=N'TABLE',@level1name=N'Categories'
GO
/****** 对象:  ForeignKey [FK_Owner_Blog]    脚本日期: 01/07/2010 18:18:38 ******/
ALTER TABLE [blogs].[BlogOwners]  WITH CHECK ADD  CONSTRAINT [FK_Owner_Blog] FOREIGN KEY([BlogId])
REFERENCES [blogs].[Blogs] ([BlogId])
GO
ALTER TABLE [blogs].[BlogOwners] CHECK CONSTRAINT [FK_Owner_Blog]
GO
/****** 对象:  ForeignKey [FK_BlogPosts_Blogs]    脚本日期: 01/07/2010 18:18:40 ******/
ALTER TABLE [blogs].[BlogPosts]  WITH CHECK ADD  CONSTRAINT [FK_BlogPosts_Blogs] FOREIGN KEY([BlogId])
REFERENCES [blogs].[Blogs] ([BlogId])
GO
ALTER TABLE [blogs].[BlogPosts] CHECK CONSTRAINT [FK_BlogPosts_Blogs]
GO
/****** 对象:  ForeignKey [FK_BlogPosts_Posts]    脚本日期: 01/07/2010 18:18:40 ******/
ALTER TABLE [blogs].[BlogPosts]  WITH CHECK ADD  CONSTRAINT [FK_BlogPosts_Posts] FOREIGN KEY([PostId])
REFERENCES [blogs].[Posts] ([PostId])
GO
ALTER TABLE [blogs].[BlogPosts] CHECK CONSTRAINT [FK_BlogPosts_Posts]
GO
/****** 对象:  ForeignKey [FK_Categories_Blogs]    脚本日期: 01/07/2010 18:18:50 ******/
ALTER TABLE [blogs].[Categories]  WITH CHECK ADD  CONSTRAINT [FK_Categories_Blogs] FOREIGN KEY([BlogId])
REFERENCES [blogs].[Blogs] ([BlogId])
GO
ALTER TABLE [blogs].[Categories] CHECK CONSTRAINT [FK_Categories_Blogs]
GO
/****** 对象:  ForeignKey [FK_Comment_Blog]    脚本日期: 01/07/2010 18:18:54 ******/
ALTER TABLE [blogs].[Comments]  WITH CHECK ADD  CONSTRAINT [FK_Comment_Blog] FOREIGN KEY([BlogId])
REFERENCES [blogs].[Blogs] ([BlogId])
GO
ALTER TABLE [blogs].[Comments] CHECK CONSTRAINT [FK_Comment_Blog]
GO
/****** 对象:  ForeignKey [FK_Comment_Post]    脚本日期: 01/07/2010 18:18:54 ******/
ALTER TABLE [blogs].[Comments]  WITH CHECK ADD  CONSTRAINT [FK_Comment_Post] FOREIGN KEY([PostId])
REFERENCES [blogs].[Posts] ([PostId])
GO
ALTER TABLE [blogs].[Comments] CHECK CONSTRAINT [FK_Comment_Post]
GO
/****** 对象:  ForeignKey [FK_Links_Blogs]    脚本日期: 01/07/2010 18:18:58 ******/
ALTER TABLE [blogs].[Links]  WITH CHECK ADD  CONSTRAINT [FK_Links_Blogs] FOREIGN KEY([BlogId])
REFERENCES [blogs].[Blogs] ([BlogId])
GO
ALTER TABLE [blogs].[Links] CHECK CONSTRAINT [FK_Links_Blogs]
GO
/****** 对象:  ForeignKey [FK_Links_Categories]    脚本日期: 01/07/2010 18:18:58 ******/
ALTER TABLE [blogs].[Links]  WITH CHECK ADD  CONSTRAINT [FK_Links_Categories] FOREIGN KEY([LinkCategoryId])
REFERENCES [blogs].[Categories] ([CategoryId])
GO
ALTER TABLE [blogs].[Links] CHECK CONSTRAINT [FK_Links_Categories]
GO
/****** 对象:  ForeignKey [FK_PostBody_Posts]    脚本日期: 01/07/2010 18:18:59 ******/
ALTER TABLE [blogs].[PostBody]  WITH CHECK ADD  CONSTRAINT [FK_PostBody_Posts] FOREIGN KEY([PostId])
REFERENCES [blogs].[Posts] ([PostId])
GO
ALTER TABLE [blogs].[PostBody] CHECK CONSTRAINT [FK_PostBody_Posts]
GO
/****** 对象:  ForeignKey [FK_PostCategoryRelationship_Categories]    脚本日期: 01/07/2010 18:19:01 ******/
ALTER TABLE [blogs].[PostCategoryRelationship]  WITH CHECK ADD  CONSTRAINT [FK_PostCategoryRelationship_Categories] FOREIGN KEY([PostCategoryId])
REFERENCES [blogs].[Categories] ([CategoryId])
GO
ALTER TABLE [blogs].[PostCategoryRelationship] CHECK CONSTRAINT [FK_PostCategoryRelationship_Categories]
GO
/****** 对象:  ForeignKey [FK_PostsInCategory_Post]    脚本日期: 01/07/2010 18:19:01 ******/
ALTER TABLE [blogs].[PostCategoryRelationship]  WITH CHECK ADD  CONSTRAINT [FK_PostsInCategory_Post] FOREIGN KEY([PostId])
REFERENCES [blogs].[Posts] ([PostId])
GO
ALTER TABLE [blogs].[PostCategoryRelationship] CHECK CONSTRAINT [FK_PostsInCategory_Post]
GO
/****** 对象:  ForeignKey [FK_PostComments_Blogs]    脚本日期: 01/07/2010 18:19:03 ******/
ALTER TABLE [blogs].[PostComments]  WITH CHECK ADD  CONSTRAINT [FK_PostComments_Blogs] FOREIGN KEY([BlogId])
REFERENCES [blogs].[Blogs] ([BlogId])
GO
ALTER TABLE [blogs].[PostComments] CHECK CONSTRAINT [FK_PostComments_Blogs]
GO
/****** 对象:  ForeignKey [FK_PostComments_Comments]    脚本日期: 01/07/2010 18:19:03 ******/
ALTER TABLE [blogs].[PostComments]  WITH CHECK ADD  CONSTRAINT [FK_PostComments_Comments] FOREIGN KEY([CommentId])
REFERENCES [blogs].[Comments] ([CommentId])
GO
ALTER TABLE [blogs].[PostComments] CHECK CONSTRAINT [FK_PostComments_Comments]
GO
/****** 对象:  ForeignKey [FK_PostComments_Posts]    脚本日期: 01/07/2010 18:19:03 ******/
ALTER TABLE [blogs].[PostComments]  WITH CHECK ADD  CONSTRAINT [FK_PostComments_Posts] FOREIGN KEY([PostId])
REFERENCES [blogs].[Posts] ([PostId])
GO
ALTER TABLE [blogs].[PostComments] CHECK CONSTRAINT [FK_PostComments_Posts]
GO
/****** 对象:  ForeignKey [FK_PostSlugs_Blogs]    脚本日期: 01/07/2010 18:19:04 ******/
ALTER TABLE [blogs].[PostSlugs]  WITH CHECK ADD  CONSTRAINT [FK_PostSlugs_Blogs] FOREIGN KEY([BlogId])
REFERENCES [blogs].[Blogs] ([BlogId])
GO
ALTER TABLE [blogs].[PostSlugs] CHECK CONSTRAINT [FK_PostSlugs_Blogs]
GO
/****** 对象:  ForeignKey [FK_PostSlugs_Posts]    脚本日期: 01/07/2010 18:19:04 ******/
ALTER TABLE [blogs].[PostSlugs]  WITH CHECK ADD  CONSTRAINT [FK_PostSlugs_Posts] FOREIGN KEY([PostId])
REFERENCES [blogs].[Posts] ([PostId])
GO
ALTER TABLE [blogs].[PostSlugs] CHECK CONSTRAINT [FK_PostSlugs_Posts]
GO
/****** 对象:  ForeignKey [FK_PostTagRelationship_Categories]    脚本日期: 01/07/2010 18:19:06 ******/
ALTER TABLE [blogs].[PostTagRelationship]  WITH CHECK ADD  CONSTRAINT [FK_PostTagRelationship_Categories] FOREIGN KEY([PostTagId])
REFERENCES [blogs].[Categories] ([CategoryId])
GO
ALTER TABLE [blogs].[PostTagRelationship] CHECK CONSTRAINT [FK_PostTagRelationship_Categories]
GO
/****** 对象:  ForeignKey [FK_PostTagRelationship_Post]    脚本日期: 01/07/2010 18:19:06 ******/
ALTER TABLE [blogs].[PostTagRelationship]  WITH CHECK ADD  CONSTRAINT [FK_PostTagRelationship_Post] FOREIGN KEY([PostId])
REFERENCES [blogs].[Posts] ([PostId])
GO
ALTER TABLE [blogs].[PostTagRelationship] CHECK CONSTRAINT [FK_PostTagRelationship_Post]
GO
/****** 对象:  ForeignKey [FK_Post_Blog]    脚本日期: 01/07/2010 18:19:13 ******/
ALTER TABLE [blogs].[Posts]  WITH CHECK ADD  CONSTRAINT [FK_Post_Blog] FOREIGN KEY([BlogId])
REFERENCES [blogs].[Blogs] ([BlogId])
GO
ALTER TABLE [blogs].[Posts] CHECK CONSTRAINT [FK_Post_Blog]
GO





/****** 对象:  StoredProcedure [blogs].[up_GetAllBlogIds]    脚本日期: 01/07/2010 18:20:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    ids
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-23>
--
 Description: <Get ALL BlogIds>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetAllBlogIds]

AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT
            
[BlogId]
        
FROM
            
[Blog] B (NOLOCK)
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetCategoryIdsBySlug]    脚本日期: 01/07/2010 18:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    ids
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-25>
--
 Description: <Get CategoryIds by its slug and location>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetCategoryIdsBySlug]
(
    
@BlogId                INT,
    
@CategoryType        TINYINT,
    
@Slug                NVARCHAR(128)
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT
            
[CategoryId]
        
FROM
            
[Categories] C (NOLOCK)
        
WHERE
            
[BlogId] = @BlogId 
        
AND [CategoryType] = @CategoryType
        
AND    [LoweredSlug] = LOWER(@Slug)
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetCategoryIds]    脚本日期: 01/07/2010 18:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    ids
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-25>
--
 Description: <Get CategoryIdS by Blog and Type>
--
 Memo: Maybe we should store the relationship in a individual table.
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetCategoryIds]
(
    
@BlogId                INT,
    
@CategoryType        TINYINT
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT
            
[CategoryId]
        
FROM
            
[Categories] C (NOLOCK)
        
WHERE
            
[BlogId] = @BlogId AND [CategoryType] = @CategoryType
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_SavePostViews]    脚本日期: 01/07/2010 18:20:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-06>
--
 Description: <Batch save post views>
--
 =============================================
CREATE PROCEDURE [blogs].[up_SavePostViews]
(
    
@PostViews        XML
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN

        
-- reutrn if no data.
        IF @PostViews IS NULL
            
RETURN
    
        
-- update.
        UPDATE
            
[Posts] WITH (ROWLOCK)
        
SET
            
[Posts].[TotalViews] = [Posts].[TotalViews] + [TVT].[TotalViews]
        
FROM
            
[Posts] JOIN
            (
                
SELECT
                    TV.Vs.value(
'@id','int'AS PostId,
                    TV.Vs.value(
'@v','int'AS TotalViews
                
FROM
                    
@PostViews.nodes('/vs/v') TV(Vs) 
            ) TVT 
        
ON
            
[Posts].[PostId] = [TVT].[PostId]

    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_CreateGetTagIds]    脚本日期: 01/07/2010 18:20:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-06>
--
 Description: <Make sure all the tag EXISTS in DB, and then get their ids.>
--
 =============================================
CREATE PROCEDURE [blogs].[up_CreateGetTagIds]
(
    
@BlogId                INT,
    
@TagNames            XML
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
/******************************* DECLARE VARIABLE *************************************************/

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN

        
BEGIN TRANSACTION;

            
-- Create temp table.
            CREATE TABLE #TagsTable
            (
                TagName        
NVARCHAR(64)
            )   

            
-- Insert into temp table.
            INSERT INTO #TagsTable 
                ( TagName )
            
SELECT 
                TG.Tags.value(
'@t','nvarchar(64)'AS TagName
            
FROM 
                
@TagNames.nodes('/ts/t') TG(Tags) 

            
-- Create new tags.
            BEGIN
                
INSERT INTO 
                    
[Categories]
               (
                    
[BlogId]
                    ,
[ParentId]
                    ,
[CategoryType]
                    ,
[CategoryName]
                    ,
[LoweredCategoryName]
                    ,
[Slug]
                    ,
[LoweredSlug]
                    ,
[Description]
                    ,
[CreatedDateUtc]
                    ,
[TotalEntities]
                    ,
[SortOrder]
                    ,
[State]
                )
                
SELECT
                    
@BlogId,
                    
0,                                    -- ParentId, 0 as default.
                    2,                                    -- CategoryType, 2 as Post Tag.
                    TT.TagName,
                    
LOWER(TT.TagName),
                    TT.TagName,                            
-- Slug, use CategoryName as default.
                    LOWER(TT.TagName),                    -- LoweredSlug, use LoweredCategoryName as default.
                    '',                                    -- Description, Empty as default.
                    GETUTCDATE(),
                    
0,                                    -- TotalEntities, 0 as default.
                    COALESCE(MAX(C.SortOrder) + 11),    -- SortOrder.
                    1                                    -- State, 1 as Normal.
                FROM
                    #TagsTable TT, 
[Categories] C ( NOLOCK )
                
WHERE
                    
LOWER(TT.TagName) NOT IN
                    (
                        
SELECT 
                            
[LoweredCategoryName] 
                        
FROM 
                            
[Categories] ( UPDLOCK, HOLDLOCK )
                        
WHERE
                            
[BlogId] = @BlogId
                            
AND [CategoryType] = 2        -- Post Tag.    
                    )
                    
AND C.[BlogId] = @BlogId 
                    
AND C.[CategoryType] = 2            -- Post Tag.    

            
END

            
-- Batch Get Tag Ids.
            BEGIN
                
SELECT
                    
[CategoryId]
                
FROM
                    
[Categories] C (NOLOCK)
                
JOIN 
                    #TagsTable TT 
                
ON 
                    
LOWER(TT.TagName) = C.[LoweredCategoryName]
                
WHERE
                    C.
[BlogId] = @BlogId 
                    
AND C.[CategoryType] = 2                -- Post Tag.
                    AND C.[State] = 1                        -- 1 as Normal status.
            END

            
-- clear and drop temp table
            TRUNCATE TABLE 
                #TagsTable
            
DROP TABLE 
                #TagsTable

        
IF@@ERROR <> 0 )
            
BEGIN
                
ROLLBACK TRANSACTION
                
RETURN -1
            
END
        
ELSE
            
BEGIN
                
COMMIT TRANSACTION;
            
END

        
RETURN 1
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_UpdateComment]    脚本日期: 01/07/2010 18:20:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

    public enum CreateUpdateCommentStatus
    {
        UnknownFailure = 0,
        Success = 1,
        InvalidSubject = 2,
        InvalidAuthor = 3,
        CommentNotFound = 4,
        ProviderError = 99,
    }
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-06>
--
 Description: <Update entity of Comment>
--
 =============================================
CREATE PROCEDURE [blogs].[up_UpdateComment]
(
    
@CommentId                INT,
    
@BlogId                    INT,
    
@PostId                    INT,
    
@ParentId                INT,
    
@Subject                NVARCHAR(256),
    
@PostAuthor                NVARCHAR(256),
    
@EmailAddress            NVARCHAR(256),
    
@WebUrl                    NVARCHAR(256),
    
@TotalViews                INT,
    
@FormattedBody            NVARCHAR(MAX),
    
@State                    TINYINT
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    
/******************************* DECLARE VARIABLE *************************************************/
    
DECLARE        @OldState            TINYINT
    
DECLARE        @CommentDateUtc        DATETIME

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- Validations.
        BEGIN

            
-- InvalidSubject = 2,
            IF @Subject = '' OR @Subject IS NULL
                
RETURN 2
            
            
-- InvalidAuthor = 3,
            IF @PostAuthor = '' OR @PostAuthor IS NULL
                
RETURN 3

            
-- CommentNotFound = 4,
            SELECT 
                
@OldState = [State],
                
@CommentDateUtc = [CommentDateUtc]
            
FROM 
                
[Comments] WITH ( UPDLOCK, HOLDLOCK )
            
WHERE [CommentId] = @CommentId

            
IF @OldState IS NULL
                
RETURN 4
        
END

        
-- begin operations.
        BEGIN TRANSACTION;

                
-- update entity.
                UPDATE 
                    
[Comments]
                
SET 
                     
[ParentId]                = @ParentId
                    ,
[Subject]                = @Subject
                    ,
[PostAuthor]            = @PostAuthor
                    ,
[EmailAddress]            = @EmailAddress
                    ,
[WebUrl]                = @WebUrl
                    ,
[TotalViews]            = @TotalViews
                    ,
[FormattedBody]        = @FormattedBody
                    ,
[State]                = @State
                 
WHERE 
                    
[CommentId] = @CommentId


                
-- update count in blog and post.
                IF @OldState <> 1 AND @State = 1        -- Normal status now.
                BEGIN
                    
-- update blog's comments count.
                    UPDATE 
                        
[Blogs] WITH (ROWLOCK)
                    
SET
                        
[TotalComments] = [TotalComments] + 1,
                        
[LastCommentDateUtc] = @CommentDateUtc
                    
WHERE
                        BlogId 
= @BlogId

                    
-- update post's comments count
                    UPDATE 
                        
[Posts] WITH (ROWLOCK)
                    
SET
                        
[TotalComments] = [TotalComments] + 1
                    
WHERE
                        BlogId 
= @BlogId

                    
-- insert into relation table.
                    INSERT INTO 
                        
[PostComments]
                   (
                        
[PostId]
                       ,
[CommentId]
                       ,
[BlogId]
                       ,
[CommentDateUtc]
                    )
                    
VALUES
                    (
                        
@PostId
                       ,
@CommentId
                       ,
@BlogId
                       ,
@CommentDateUtc
                    )
                
END

                
-- update count in blog and post.
                IF @OldState = 1 AND @State <> 1        -- Abnormal status now.
                BEGIN
                    
-- update blog's comments count.
                    UPDATE 
                        
[Blogs] WITH (ROWLOCK)
                    
SET
                        
[TotalComments] = [TotalComments] - 1,
                        
[LastCommentDateUtc] = ISNULL((SELECT TOP 1 [CommentDateUtc] FROM [Comments] WHERE BlogId = @BlogId Order By CommentDateUtc Desc), '1900-1-1')
                    
WHERE
                        BlogId 
= @BlogId

                    
-- update post's comments count
                    UPDATE 
                        
[Posts] WITH (ROWLOCK)
                    
SET
                        
[TotalComments] = [TotalComments] - 1
                    
WHERE
                        BlogId 
= @BlogId

                    
-- delete from relation table.
                    DELETE FROM 
                        
[PostComments]
                    
WHERE
                        
[CommentId] = @CommentId
                
END

        
IF@@ERROR <> 0 )
            
BEGIN
                
ROLLBACK TRANSACTION
                
RETURN 0
            
END
        
ELSE
            
BEGIN
                
COMMIT TRANSACTION;
            
END

        
RETURN 1
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_UpdatePost]    脚本日期: 01/07/2010 18:20:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

    public enum CreateUpdateBlogPostStatus
    {
        UnknownFailure = 0,
        Success = 1,
        InvalidSubject = 2,
        DuplicateSlug = 3,
        InvalidSlug = 4,
        BlogNotFound = 5,
        PostNotFound = 6,
        ProviderError = 99,
    }
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-07>
--
 Description: 1.Update entity.
--
                2.Update PostBody.
--
                3.Update PostSlug table if need.
--
                4.Update blog's Posts count.
--
                5.Update relation.
--
                6.Update category status.
--
                7.Update tag status.
--
 =============================================
CREATE PROCEDURE [blogs].[up_UpdatePost]
(
    
@PostId                    INT,
    
@BlogId                    INT,
    
@UserId                    INT,
    
@PostAuthor                NVARCHAR(256),
    
@Slug                    NVARCHAR(128),
    
@Subject                NVARCHAR(256),
    
@Summary                NVARCHAR(512),
    
@TotalViews                INT,
    
@TotalComments            INT,
    
@TotalAggViews            INT,
    
@RatingAverage            FLOAT,
    
@EnableComments            BIT,
    
@EnableRatings            BIT,
    
@IsBodyHtml                BIT,
    
@Body                    NVARCHAR(MAX),
    
@FormattedBody            NVARCHAR(MAX),
    
@ApprovedDateUtc        DATETIME,
    
@State                    TINYINT,
    
@Property                XML
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    
/******************************* DECLARE VARIABLE *************************************************/
    
DECLARE        @OldState            TINYINT
    
DECLARE        @OldLoweredSlug        NVARCHAR(128)
    
DECLARE        @PostDateUtc        DATETIME

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- Validations.
        BEGIN

            
-- InvalidSubject = 2,
            IF @Subject = '' OR @Subject IS NULL
                
RETURN 2
            
            
-- InvalidSlug = 4,
            IF @Slug = '' OR @Slug IS NULL
                
RETURN 4

            
-- DuplicateSlug = 3,
            IF (EXISTS (SELECT 1
                        
FROM [Posts] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [LoweredSlug] = LOWER(@Slug)))
            
BEGIN
                
RETURN 3
            
END        

            
-- BlogNotFound = 5,
            IF (NOT EXISTS (SELECT 1
                        
FROM [Blogs] WITH ( NOLOCK )
                        
WHERE [BlogId] = @BlogId))
            
BEGIN
                
RETURN 5
            
END        

            
-- PostNotFound = 6,
            SELECT 
                
@OldState = [State],
                
@PostDateUtc = [PostDateUtc],
                
@OldLoweredSlug = [LoweredSlug]
            
FROM 
                
[Posts] WITH ( UPDLOCK, HOLDLOCK )
            
WHERE [PostId] = @PostId

            
IF @OldState IS NULL
                
RETURN 6
        
END

        
-- begin operations.
        BEGIN TRANSACTION;

                
-- 1.Update entity.
                UPDATE 
                    
[Posts] WITH(ROWLOCK)
                
SET 
                     
[Slug]                    = @Slug
                    ,
[LoweredSlug]            = LOWER(@Slug)
                    ,
[Subject]                = @Subject
                    ,
[PostAuthor]            = @PostAuthor
                    ,
[Summary]                = @Summary
                    ,
[ApprovedDateUtc]        = @ApprovedDateUtc
                    ,
[TotalViews]            = @TotalViews
                    ,
[TotalComments]        = @TotalComments
                    ,
[TotalAggViews]        = @TotalAggViews
                    ,
[RatingAverage]        = @RatingAverage
                    ,
[EnableComments]        = @EnableComments
                    ,
[IsBodyHtml]            = @IsBodyHtml
                    ,
[EnableRatings]        = @EnableRatings
                    ,
[State]                = @State
                    ,
[Property]                = @Property
                 
WHERE 
                    
[PostId] = @PostId

                
-- 2.Update PostBody.
                UPDATE
                    
[PostBody] WITH (ROWLOCK)
                
SET
                    
[Body] = @Body
                    
[FormattedBody] = @FormattedBody
                
WHERE
                    
[PostId] = @PostId

                
-- 3.Update PostSlug table if need 
                -- 3.1 update slug table when state always 1, slug changed.
                IF @OldLoweredSlug <> LOWER(@SlugAND @State = @OldState AND @State = 1
                
BEGIN
                    
UPDATE
                        
[PostSlug] WITH (ROWLOCK)
                    
SET
                        
[LoweredSlug] = LOWER(@Slug)
                    
WHERE
                        
[PostId] = @PostId
                
END

                
-- update count in blog.
                IF @OldState <> 1 AND @State = 1        -- Normal status now.
                BEGIN
                    
-- 3.Update PostSlug table if need
                    -- 3.2 insert into slug table.
                    INSERT INTO 
                        
[PostSlugs]
                   (
                        
[BlogId]
                       ,
[PostId]
                       ,
[LoweredSlug]
                    )
                    
VALUES
                    (
                        
@BlogId
                       ,
@PostId
                       ,
LOWER(@Slug)
                    )

                    
-- 4.Update blog's Posts count.
                    UPDATE 
                        
[Blogs] WITH (ROWLOCK)
                    
SET
                        
[TotalPosts] = [TotalPosts] + 1,
                        
[LastPostDateUtc] = @PostDateUtc
                    
WHERE
                        BlogId 
= @BlogId

                    
-- 5.Update relation.
                    -- Insert into relation table if need.
                    INSERT INTO 
                        
[PostSlugs]
                   (
                        
[BlogId]
                       ,
[PostId]
                       ,
[LoweredSlug]
                    )
                    
VALUES
                    (
                        
@BlogId
                       ,
@PostId
                       ,
LOWER(@Slug)
                    )

                    
-- 6.Update category status.
                    INSERT INTO 
                        
[BlogPosts]
                   (
                        
[BlogId]
                       ,
[PostId]
                       ,
[UserId]
                       ,
[PostDateUtc]
                    )
                    
VALUES
                    (
                        
@BlogId
                       ,
@PostId
                       ,
@UserId
                       ,
@PostDateUtc
                    )
                
END

                
-- update count in blog and post.
                IF @OldState = 1 AND @State <> 1        -- Abnormal status now.
                BEGIN

                    
-- 3.Update PostSlug table if need
                    -- 3.2 insert into slug table.
                    DELETE FROM 
                        
[PostSlugs]
                    
WHERE
                        
[PostId] = @PostId

                    
-- 4.Update blog's Posts count.
                    UPDATE 
                        
[Blogs] WITH (ROWLOCK)
                    
SET
                        
[TotalPosts] = [TotalPosts] - 1,
                        
[LastPostDateUtc] = ISNULL((SELECT TOP 1 [PostDateUtc] FROM [Posts] WHERE BlogId = @BlogId Order By PostDateUtc Desc), '1900-1-1')
                    
WHERE
                        BlogId 
= @BlogId

                    
-- 5.Update relation.
                    -- delete from relation table.
                    DELETE FROM 
                        
[BlogPosts]
                    
WHERE
                        
[PostId] = @PostId
                
END

                
-- 6.Update category status.
                UPDATE
                    
[PostCategoryRelationship] WITH (ROWLOCK)
                
SET 
                    
[State] = @State
                
WHERE
                    
[PostId] = @PostId

                
-- 7.Update tag status.
                UPDATE
                    
[PostTagRelationship] WITH (ROWLOCK)
                
SET 
                    
[State] = @State
                
WHERE
                    
[PostId] = @PostId

        
IF@@ERROR <> 0 )
            
BEGIN
                
ROLLBACK TRANSACTION
                
RETURN 0
            
END
        
ELSE
            
BEGIN
                
COMMIT TRANSACTION;
            
END

        
RETURN 1
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_CreatePost]    脚本日期: 01/07/2010 18:20:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

    public enum CreateUpdateBlogPostStatus
    {
        UnknownFailure = 0,
        Success = 1,
        InvalidSubject = 2,
        DuplicateSlug = 3,
        InvalidSlug = 4,
        BlogNotFound = 5,
        PostNotFound = 6,
        ProviderError = 99,
    }
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-07>
--
 Description:  1.Create entity of Post;
--
                 2.Insert PostBody;
--
                 3.Insert into PostSlug table IF STATE = 1;
--
                 4.Update blog's Posts count;
--
                 5.Insert into relation table.
--
 =============================================
CREATE PROCEDURE [blogs].[up_CreatePost]
(
    
@PostId                    INT OUT,
    
@BlogId                    INT,
    
@UserId                    INT,
    
@PostAuthor                NVARCHAR(256),
    
@Slug                    NVARCHAR(128),
    
@Subject                NVARCHAR(256),
    
@Summary                NVARCHAR(512),
    
@IPAddress                BIGINT,
    
@EnableComments            BIT,
    
@EnableRatings            BIT,
    
@IsBodyHtml                BIT,
    
@Body                    NVARCHAR(MAX),
    
@FormattedBody            NVARCHAR(MAX),
    
@PostDateUtc            DATETIME,
    
@ApprovedDateUtc        DATETIME,
    
@State                    TINYINT,
    
@Property                XML
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF
    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- Validations.
        BEGIN

            
-- InvalidSubject = 2,
            IF @Subject = '' OR @Subject IS NULL
                
RETURN 2
            
            
-- InvalidSlug = 4,
            IF @Slug = '' OR @Slug IS NULL
                
RETURN 4

            
-- DuplicateSlug = 3,
            IF (EXISTS (SELECT 1
                        
FROM [Posts] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [LoweredSlug] = LOWER(@Slug)))
            
BEGIN
                
RETURN 3
            
END        

            
-- BlogNotFound = 5,
            IF (NOT EXISTS (SELECT 1
                        
FROM [Blogs] WITH ( NOLOCK )
                        
WHERE [BlogId] = @BlogId))
            
BEGIN
                
RETURN 5
            
END        
        
        
END

        
-- begin operations.
        BEGIN TRANSACTION;

                
-- insert entity.
                INSERT INTO
                    
[Posts]
                (
                    
[BlogId]
                   ,
[UserId]
                   ,
[PostAuthor]
                   ,
[Slug]
                   ,
[LoweredSlug]
                   ,
[Subject]
                   ,
[Summary]
                   ,
[PostDateUtc]
                   ,
[ApprovedDateUtc]
                   ,
[TotalViews]
                   ,
[TotalComments]
                   ,
[TotalAggViews]
                   ,
[RatingAverage]
                   ,
[IPAddress]
                   ,
[EnableComments]
                   ,
[EnableRatings]
                   ,
[IsBodyHtml]
                   ,
[State]
                   ,
[Property]
                )
                
VALUES
                (
                    
@BlogId
                   ,
@UserId
                   ,
@PostAuthor
                   ,
@Slug
                   ,
LOWER(@Slug)
                   ,
@Subject
                   ,
@Summary
                   ,
@PostDateUtc
                   ,
@ApprovedDateUtc
                   ,
0                            -- @TotalViews, 0 as default
                   ,0                            -- @TotalComments, 0 as default
                   ,0                            -- @TotalAggViews, 0 as default
                   ,0                            -- @RatingAverage, 0 as default
                   ,@IPAddress
                   ,
@EnableComments
                   ,
@EnableRatings
                   ,
@IsBodyHtml
                   ,
@State
                   ,
@Property
                )

                
-- get PostId.
                SELECT @PostId = SCOPE_IDENTITY()

                
-- insert PostBody
                INSERT INTO 
                    
[PostBody]
                (
                    
[PostId]
                   ,
[Body]
                   ,
[FormattedBody]
                )
                
VALUES
                  (
                    
@PostId
                   ,
@Body
                   ,
@FormattedBody
                )

                
IF @State = 1                    -- Normal status.
                BEGIN

                    
-- insert into slug table.
                    INSERT INTO 
                        
[PostSlugs]
                   (
                        
[BlogId]
                       ,
[PostId]
                       ,
[LoweredSlug]
                    )
                    
VALUES
                    (
                        
@BlogId
                       ,
@PostId
                       ,
LOWER(@Slug)
                    )

                    
-- update blog's Posts count.
                    UPDATE 
                        
[Blogs] WITH (ROWLOCK)
                    
SET
                        
[TotalPosts] = [TotalPosts] + 1,
                        
[LastPostDateUtc] = @PostDateUtc
                    
WHERE
                        BlogId 
= @BlogId

                    
-- insert into relation table.
                    INSERT INTO 
                        
[BlogPosts]
                   (
                        
[BlogId]
                       ,
[PostId]
                       ,
[UserId]
                       ,
[PostDateUtc]
                    )
                    
VALUES
                    (
                        
@BlogId
                       ,
@PostId
                       ,
@UserId
                       ,
@PostDateUtc
                    )

                
END

        
IF@@ERROR <> 0 )
            
BEGIN
                
ROLLBACK TRANSACTION
                
RETURN 0
            
END
        
ELSE
            
BEGIN
                
COMMIT TRANSACTION;
            
END

        
RETURN 1
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_UpdateBlogOwners]    脚本日期: 01/07/2010 18:20:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

    public enum CreateUpdateSectionStatus
    {
        UnknownFailure = 0,
        Success = 1,
        DuplicateName = 2,
        DuplicateSlug = 3,
        InvalidName = 4,
        InvalidSlug = 5,
        SectionNotFound = 6,
        ParentSectionNotFound = 7,
        ProviderError = 99,
    }
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-21>
--
 Description: <Update owners of Blog>
--
 =============================================
CREATE PROCEDURE [blogs].[up_UpdateBlogOwners]
(
    
@BlogId                    INT,
    
@UserIds                XML
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- Validations.
        BEGIN

            
-- SectionNotFound = 6,
            IF (NOT EXISTS (SELECT 1
                        
FROM [Blogs] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [BlogId] = @BlogId))
            
BEGIN
                
RETURN 6
            
END

        
END

        
-- do transaction.    
        BEGIN TRANSACTION;
    
            
-- delete old relations.
            DELETE FROM [BlogOwners] WHERE [BlogId] = @BlogId

            
-- insert new relations.
            INSERT INTO [BlogOwners]
                   (
[BlogId]
                   ,
[UserId])
            
SELECT @BlogId, UI.Ids.value('@i','int'as UserId
            
FROM @UserIds.nodes('/us/u') UI(Ids) 

        
IF@@ERROR <> 0 )
            
BEGIN
                
ROLLBACK TRANSACTION
            
END
        
ELSE
            
BEGIN
                
COMMIT TRANSACTION;
            
END

        
RETURN 1
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetBlogIdsByUser]    脚本日期: 01/07/2010 18:20:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    ids
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-21>
--
 Description: <Get BlogIds by owners' UserId>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetBlogIdsByUser]
(
    
@UserId        INT
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT
            
[BlogId]
        
FROM
            
[BlogOwners] BO (NOLOCK)
        
WHERE
            
[UserId] = @UserId
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_DeleteBlog]    脚本日期: 01/07/2010 18:20:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

    public enum DeleteBlogStatus
    {
        /// <summary>
        /// 未知原因导致失败
        /// </summary>
        UnknownFailure = 0,
        /// <summary>
        /// 成功
        /// </summary>
        Success = 1,
        /// <summary>
        /// 未找到
        /// </summary>
        BlogNotFound = 2
    }
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-23>
--
 Description: <Delete entity of Blogs>
--
 =============================================
CREATE PROCEDURE [blogs].[up_DeleteBlog]
(
    
@BlogId                    INT OUT
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- Validations.
        BEGIN

            
-- BlogNotFound = 2
            IF (NOT EXISTS (SELECT 1
                        
FROM [Blogs] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [BlogId] = @BlogId))
            
BEGIN
                
RETURN 2
            
END

        
END

        
BEGIN TRY

            
BEGIN TRANSACTION;

            
-- delete ownerships.
            DELETE FROM [BlogOwners]
            
WHERE [BlogId] = @BlogId

            
-- delete entities.
            DELETE FROM [Blogs]
            
WHERE [BlogId] = @BlogId

            
COMMIT TRANSACTION;

            
RETURN 1

        
END TRY
        
BEGIN CATCH
            
IF XACT_STATE() <> 0
            
BEGIN
                
ROLLBACK TRANSACTION;
                
RETURN 0
            
END
        
END CATCH
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_DeleteComment]    脚本日期: 01/07/2010 18:20:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

    public enum DeleteCommentStatus
    {
        /// <summary>
        /// 未知原因导致失败
        /// </summary>
        UnknownFailure = 0,
        /// <summary>
        /// 成功
        /// </summary>
        Success = 1,
        /// <summary>
        /// 未找到
        /// </summary>
        CommentNotFound = 2
    }
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-06>
--
 Description: <Delete entity of Comment>
--
 =============================================
CREATE PROCEDURE [blogs].[up_DeleteComment]
(
    
@CommentId                    INT OUT
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    
/******************************* DECLARE VARIABLE *************************************************/
    
DECLARE        @BlogId        INT
    
DECLARE        @PostId        INT

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- Validations.
        BEGIN

            
-- CommentNotFound = 2
            SELECT 
                
@BlogId = [BlogId],
                
@PostId = [PostId]
            
FROM 
                
[Comments] WITH ( UPDLOCK, HOLDLOCK )
            
WHERE [CommentId] = @CommentId

            
IF @BlogId IS NULL
                
RETURN 2

        
END

        
BEGIN TRY

            
BEGIN TRANSACTION;

            
-- delete ownerships.
            DELETE FROM [PostComments]
            
WHERE [CommentId] = @CommentId
    
            
-- delete entities.
            DELETE FROM [Comments]
            
WHERE [CommentId] = @CommentId

            
-- update blog's comments count.
            UPDATE 
                
[Blogs] WITH (ROWLOCK)
            
SET
                
[TotalComments] = [TotalComments] - 1,
                
[LastCommentDateUtc] = ISNULL((SELECT TOP 1 [CommentDateUtc] FROM [Comments] WHERE BlogId = @BlogId Order By CommentDateUtc Desc), '1900-1-1')
            
WHERE
                BlogId 
= @BlogId

            
-- update post's comments count
            UPDATE 
                
[Posts] WITH (ROWLOCK)
            
SET
                
[TotalComments] = [TotalComments] - 1
            
WHERE
                BlogId 
= @BlogId

            
-- update children comments.
            UPDATE
                
[Comments]
            
SET
                
[ParentId] = 0
            
WHERE
                
[ParentId] = @CommentId

            
COMMIT TRANSACTION;

            
RETURN 1

        
END TRY
        
BEGIN CATCH
            
IF XACT_STATE() <> 0
            
BEGIN
                
ROLLBACK TRANSACTION;
                
RETURN 0
            
END
        
END CATCH
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_CreateComment]    脚本日期: 01/07/2010 18:20:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

    public enum CreateUpdateCommentStatus
    {
        UnknownFailure = 0,
        Success = 1,
        InvalidSubject = 2,
        InvalidAuthor = 3,
        CommentNotFound = 4,
        ProviderError = 99,
    }
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-06>
--
 Description: <Create entity of Comment>
--
 =============================================
CREATE PROCEDURE [blogs].[up_CreateComment]
(
    
@CommentId                INT OUT,
    
@BlogId                    INT,
    
@PostId                    INT,
    
@UserId                    INT,
    
@ParentId                INT,
    
@Subject                NVARCHAR(256),
    
@PostAuthor                NVARCHAR(256),
    
@IPAddress                BIGINT,
    
@EmailAddress            NVARCHAR(256),
    
@WebUrl                    NVARCHAR(256),
    
@FormattedBody            NVARCHAR(MAX),
    
@State                    TINYINT
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- Validations.
        BEGIN

            
-- InvalidSubject = 2,
            IF @Subject = '' OR @Subject IS NULL
                
RETURN 2
            
            
-- InvalidAuthor = 3,
            IF @PostAuthor = '' OR @PostAuthor IS NULL
                
RETURN 3
        
END

        
-- begin operations.
        BEGIN TRANSACTION;

                
-- insert entity.
                INSERT INTO
                    
[Comments]
                (
                    
[BlogId]
                   ,
[PostId]
                   ,
[UserId]
                   ,
[ParentId]
                   ,
[Subject]
                   ,
[PostAuthor]
                   ,
[IPAddress]
                   ,
[EmailAddress]
                   ,
[WebUrl]
                   ,
[TotalViews]
                   ,
[CommentDateUtc]
                   ,
[FormattedBody]
                   ,
[State]
                )
                
VALUES
                (
                    
@BlogId
                    ,
@PostId
                    ,
@UserId
                    ,
@ParentId
                    ,
@Subject
                    ,
@PostAuthor
                    ,
@IPAddress
                    ,
@EmailAddress
                    ,
@WebUrl
                    ,
0                    -- TotalViews, 0 as default.
                    ,GETUTCDATE()
                    ,
@FormattedBody
                    ,
@State
                )

                
-- get CommentId.
                SELECT @CommentId = SCOPE_IDENTITY()

                
IF @State = 1            -- Normal status.
                BEGIN
                    
-- update blog's comments count.
                    UPDATE 
                        
[Blogs] WITH (ROWLOCK)
                    
SET
                        
[TotalComments] = [TotalComments] + 1,
                        
[LastCommentDateUtc] = GetUtcDate()
                    
WHERE
                        BlogId 
= @BlogId

                    
-- update post's comments count
                    UPDATE 
                        
[Posts] WITH (ROWLOCK)
                    
SET
                        
[TotalComments] = [TotalComments] + 1
                    
WHERE
                        BlogId 
= @BlogId

                    
-- insert into relation table.
                    INSERT INTO 
                        
[PostComments]
                   (
                        
[PostId]
                       ,
[CommentId]
                       ,
[BlogId]
                       ,
[CommentDateUtc]
                    )
                    
VALUES
                    (
                        
@PostId
                       ,
@CommentId
                       ,
@BlogId
                       ,GETUTCDATE()
                    )
                
END

        
IF@@ERROR <> 0 )
            
BEGIN
                
ROLLBACK TRANSACTION
                
RETURN 0
            
END
        
ELSE
            
BEGIN
                
COMMIT TRANSACTION;
            
END

        
RETURN 1
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetComments]    脚本日期: 01/07/2010 18:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    Category entities
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-05>
--
 Description: <Get Comments entities by CommentIds>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetComments]
(
    
@Ids        XML
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT
            C.
[CommentId]
            , 
[BlogId]
            , 
[PostId]
            , 
[UserId]
            , 
[ParentId]
            , 
[Subject]
            , 
[PostAuthor]
            , 
[IPAddress]
            , 
[EmailAddress]
            , 
[WebUrl]
            , 
[TotalViews]
            , 
[CommentDateUtc]
            , 
[FormattedBody]
            , 
[State]
        
FROM
            
[Comments] C (NOLOCK)
            
JOIN
            (
                
SELECT CI.Ids.value('@i','int'as CommentId
                
FROM @Ids.nodes('/es/e') CI(Ids) 
            ) xCI 
ON C.CommentId = xCI.CommentId
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_CreateBlog]    脚本日期: 01/07/2010 18:20:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

    public enum CreateUpdateSectionStatus
    {
        UnknownFailure = 0,
        Success = 1,
        DuplicateName = 2,
        DuplicateSlug = 3,
        InvalidName = 4,
        InvalidSlug = 5,
        SectionNotFound = 6,
        ParentSectionNotFound = 7,
        ProviderError = 99,
    }
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-21>
--
 Description: <Create entity of Blogs>
--
 =============================================
CREATE PROCEDURE [blogs].[up_CreateBlog]
(
    
@BlogId                    INT OUT,
    
@CreatorUserId            INT,
    
@Slug                    NVARCHAR(256),
    
@DisplayName            NVARCHAR(512),
    
@Description            NVARCHAR(1024),
    
@Theme                    NVARCHAR(512),
    
@SortOrder                INT,
    
@EnableComments            BIT,
    
@IsActive                BIT,
    
@Property                XML
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- Validations.
        BEGIN
            
-- InvalidName = 4,
            IF @DisplayName = '' OR @DisplayName IS NULL
                
RETURN 4
            
            
-- InvalidSlug = 5,
            IF @Slug = '' OR @Slug IS NULL
                
RETURN 5

            
-- DuplicateName = 2,
            IF (EXISTS (SELECT *
                        
FROM [Blogs] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [LoweredDisplayName] = LOWER(@DisplayName)))
            
BEGIN
                
RETURN 2
            
END

            
-- DuplicateSlug = 3,
            IF (EXISTS (SELECT *
                        
FROM [Blogs] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [LoweredSlug] = LOWER(@Slug)))
            
BEGIN
                
RETURN 3
            
END        
        
END

        
-- init @SortOrder
        IF@SortOrder = 0 OR @SortOrder IS NULL ) 
            
SELECT @SortOrder = COALESCE(MAX(SortOrder) + 11FROM [Blogs] WITH ( NOLOCK )

        
BEGIN TRANSACTION;

            
INSERT INTO
                    
[Blogs]
                (
                    
[CreatorUserId]
                    ,
[Slug]
                    ,
[LoweredSlug]
                    ,
[DisplayName]
                    ,
[LoweredDisplayName]
                    ,
[Description]
                    ,
[Theme]
                    ,
[CreatedDateUtc]
                    ,
[LastPostDateUtc]
                    ,
[LastCommentDateUtc]
                    ,
[TotalPosts]
                    ,
[TotalComments]
                    ,
[SortOrder]
                    ,
[EnableComments]
                    ,
[IsActive]
                    ,
[Property]
                )
                
VALUES
                (
                    
@CreatorUserId
                    ,
@Slug
                    ,
LOWER(@Slug)
                    ,
@DisplayName
                    ,
LOWER(@DisplayName)
                    ,
@Description
                    ,
@Theme
                    ,GETUTCDATE()
                    ,
'1900-1-1'
                    ,
'1900-1-1'
                    ,
0
                    ,
0
                    ,
@SortOrder
                    ,
@EnableComments
                    ,
@IsActive
                    ,
@Property
                )

        
IF@@ERROR <> 0 )
            
BEGIN
                
ROLLBACK TRANSACTION
                
RETURN 0
            
END
        
ELSE
            
BEGIN
                
COMMIT TRANSACTION;
                
Select @BlogId = SCOPE_IDENTITY()
            
END

        
RETURN 1
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_UpdateBlog]    脚本日期: 01/07/2010 18:20:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

    public enum CreateUpdateSectionStatus
    {
        UnknownFailure = 0,
        Success = 1,
        DuplicateName = 2,
        DuplicateSlug = 3,
        InvalidName = 4,
        InvalidSlug = 5,
        SectionNotFound = 6,
        ParentSectionNotFound = 7,
        ProviderError = 99,
    }
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-21>
--
 Description: <Update entity of Blogs>
--
 =============================================
CREATE PROCEDURE [blogs].[up_UpdateBlog]
(
    
@BlogId                    INT,
    
@CreatorUserId            INT,
    
@Slug                    NVARCHAR(256),
    
@DisplayName            NVARCHAR(512),
    
@Description            NVARCHAR(1024),
    
@Theme                    NVARCHAR(512),
    
@CreatedDateUtc            DATETIME,
    
@LastPostDateUtc        DATETIME,
    
@LastCommentDateUtc        DATETIME,
    
@TotalPosts                INT,
    
@TotalComments            INT,
    
@SortOrder                INT,
    
@EnableComments            BIT,
    
@IsActive                BIT,
    
@Property                XML
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- Validations.
        BEGIN
            
-- InvalidName = 4,
            IF @DisplayName = '' OR @DisplayName IS NULL
                
RETURN 4
            
            
-- InvalidSlug = 5,
            IF @Slug = '' OR @Slug IS NULL
                
RETURN 5

            
-- DuplicateName = 2,
            IF (EXISTS (SELECT *
                        
FROM [Blogs] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [LoweredDisplayName] = LOWER(@DisplayName)))
            
BEGIN
                
RETURN 2
            
END

            
-- DuplicateSlug = 3,
            IF (EXISTS (SELECT *
                        
FROM [Blogs] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [LoweredSlug] = LOWER(@Slug)))
            
BEGIN
                
RETURN 3
            
END        

            
-- SectionNotFound = 6,
            IF (NOT EXISTS (SELECT *
                        
FROM [Blogs] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [BlogId] = @BlogId))
            
BEGIN
                
RETURN 6
            
END        
        
END

        
-- init @SortOrder.
        IF@SortOrder = 0 OR @SortOrder IS NULL ) 
            
SELECT @SortOrder = COALESCE(MAX(SortOrder) + 11FROM [Blogs] WITH ( NOLOCK )

        
-- do transaction.    
        UPDATE
            
[Blogs]
        
SET
            
[CreatorUserId] = @CreatorUserId
            ,
[Slug] = @Slug
            ,
[LoweredSlug] = LOWER(@Slug)
            ,
[DisplayName] = @DisplayName
            ,
[LoweredDisplayName] = LOWER(@DisplayName)
            ,
[Description] = @Description
            ,
[Theme] = @Theme
            ,
[CreatedDateUtc] = @CreatedDateUtc
            ,
[LastPostDateUtc] = @LastPostDateUtc
            ,
[LastCommentDateUtc] = @LastCommentDateUtc
            ,
[TotalPosts] = @TotalPosts
            ,
[TotalComments] = @TotalComments
            ,
[SortOrder] = @SortOrder
            ,
[EnableComments] = @EnableComments
            ,
[IsActive] = @IsActive
            ,
[Property] = @Property
        
WHERE
            
[BlogId] = @BlogId

        
RETURN 1
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetBlogIdsBySlug]    脚本日期: 01/07/2010 18:20:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    ids
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-21>
--
 Description: <Get BlogIds by owners UserId>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetBlogIdsBySlug]
(
    
@Slug            NVARCHAR(128)
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT
            
[BlogId]
        
FROM
            
[Blogs] B (NOLOCK)
        
WHERE
            
[LoweredSlug] = LOWER(@Slug)
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetBlogs]    脚本日期: 01/07/2010 18:20:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    blog entities
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-21>
--
 Description: <Get Blog entities by BlogIds>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetBlogs]
(
    
@Ids        XML
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT
            B.
[BlogId]    
              ,
[CreatorUserId]
              ,
[Slug]
              ,
[LoweredSlug]
              ,
[DisplayName]
              ,
[LoweredDisplayName]
              ,
[Description]
              ,
[Theme]
              ,
[CreatedDateUtc]
              ,
[LastPostDateUtc]
              ,
[LastCommentDateUtc]
              ,
[TotalPosts]
              ,
[TotalComments]
              ,
[SortOrder]
              ,
[EnableComments]
              ,
[IsActive]
              ,
[Property]
        
FROM
            
[Blogs] B (NOLOCK)
            
JOIN
            (
                
SELECT BI.Ids.value('@i','int'as BlogId
                
FROM @Ids.nodes('/es/e') BI(Ids) 
            ) xBI 
ON B.BlogId = xBI.BlogId
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_CreateUpdateLink]    脚本日期: 01/07/2010 18:20:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

    public enum CreateUpdateLinkStatus
    {
        UnknownFailure = 0,
        Success = 1,
        InvalidTitle = 2,
        BlogNotFound = 3,
        CategoryNotFound = 4,
        LinkNotFound = 5,
        ProviderError = 99,
    }
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-24>
--
 Description: <Create or update entity of Link, update owner's category's entities count in creation case.>
--
 =============================================
CREATE PROCEDURE [blogs].[up_CreateUpdateLink]
(
    
@LinkId                INT    OUT,
    
@BlogId                INT,
    
@LinkCategoryId        INT,
    
@Title                NVARCHAR(512),
    
@Description        NVARCHAR(1024),
    
@Url                NVARCHAR(512),
    
@RSS                NVARCHAR(512),
    
@NewWindow            BIT,
    
@State                TINYINT,
    
@SortOrder            INT
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- Validations.
        BEGIN

            
-- InvalidTitle = 2,
            IF @Title = '' OR @Title IS NULL
                
RETURN 2

            
-- BlogNotFound = 3,
            IF (NOT EXISTS (SELECT *
                        
FROM [Blogs] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [BlogId] = @BlogId))
            
BEGIN
                
RETURN 3
            
END

            
-- CategoryNotFound = 4,
            IF (NOT EXISTS (SELECT *
                        
FROM [Categories] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [CategoryId] = @LinkCategoryId))
            
BEGIN
                
RETURN 4
            
END        
        
END

        
-- init @SortOrder.
        IF@SortOrder = 0 OR @SortOrder IS NULL ) 
            
SELECT @SortOrder = COALESCE(MAX(SortOrder) + 11FROM [Links] WITH ( NOLOCK )
            
WHERE [BlogId] = @BlogId AND [LinkCategoryId] = @LinkCategoryId

        
-- begin operations.

        
IF @LinkId > 0
            
-- update.
            BEGIN
                    
-- LinkNotFound = 5,
                    IF (NOT EXISTS (SELECT *
                                
FROM [Links] WITH ( UPDLOCK, HOLDLOCK )
                                
WHERE [LinkId] = @LinkId))
                    
BEGIN
                        
RETURN 5
                    
END
                    
                    
UPDATE
                        
[Links]
                    
SET
                         
[BlogId] = @BlogId
                        ,
[LinkCategoryId] = @LinkCategoryId
                        ,
[Title] = @Title
                        ,
[Description] = @Description
                        ,
[Url] = @Url
                        ,
[RSS] = @RSS
                        ,
[CreatedDateUtc] = GETUTCDATE()
                        ,
[NewWindow] = @NewWindow
                        ,
[State] = @State
                        ,
[SortOrder] = @SortOrder
                    
WHERE
                        
[LinkId] = @LinkId
            
END
        
ELSE
        
BEGIN
            
-- create.
            BEGIN TRANSACTION;

                
-- create entity.
                INSERT INTO
                    
[Links]
                (
                    
[BlogId]
                    ,
[LinkCategoryId]
                    ,
[Title]
                    ,
[Description]
                    ,
[Url]
                    ,
[RSS]
                    ,
[CreatedDateUtc]
                    ,
[NewWindow]
                    ,
[State]
                    ,
[SortOrder]
                )
                
VALUES
                (
                    
@BlogId
                    ,
@LinkCategoryId
                    ,
@Title
                    ,
@Description
                    ,
@Url
                    ,
@RSS
                    ,GETUTCDATE()
                    ,
@NewWindow
                    ,
@State
                    ,
@SortOrder
                )

                
-- update count in owner category
                UPDATE
                    
[Categories]
                
SET
                    
[TotalEntities] = [TotalEntities] + 1
                
WHERE
                    
[CategoryId] = @LinkCategoryId

            
IF@@ERROR <> 0 )
                
BEGIN
                    
ROLLBACK TRANSACTION
                    
RETURN 0
                
END
            
ELSE
                
BEGIN
                    
COMMIT TRANSACTION;
                    
Select @LinkId = SCOPE_IDENTITY()
                
END
        
END

        
RETURN 1
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetPostsBody]    脚本日期: 01/07/2010 18:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    post body record set.
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-06>
--
 Description: <Get PostsBody by PostIds>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetPostsBody]
(
    
@PostIds        XML
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN

        
IF @PostIds IS NULL
                
RETURN

        
-- query.
        SELECT
            PB.
[PostId]    
              ,
[Body]
        
FROM
            
[PostBody] PB (NOLOCK)
            
JOIN
            (
                
SELECT xPI.Ids.value('@i','int'as [PostId]
                
FROM @PostIds.nodes('/es/e') xPI(Ids) 
            ) xPB 
ON PB.[PostId] = xPB.[PostId]
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetPostsFormattedBody]    脚本日期: 01/07/2010 18:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    post body record set.
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-06>
--
 Description: <Get PostsFormattedBody by PostIds>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetPostsFormattedBody]
(
    
@PostIds        XML
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN

        
IF @PostIds IS NULL
                
RETURN

        
-- query.
        SELECT
            PB.
[PostId]    
              ,
[FormattedBody]
        
FROM
            
[PostBody] PB (NOLOCK)
            
JOIN
            (
                
SELECT xPI.Ids.value('@i','int'as [PostId]
                
FROM @PostIds.nodes('/es/e') xPI(Ids) 
            ) xPB 
ON PB.[PostId] = xPB.[PostId]
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetCommentIdsByPostId]    脚本日期: 01/07/2010 18:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    ids
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-05>
--
 Description: <Get CommentIds by PostId>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetCommentIdsByPostId]
(
    
@PostId                INT,
    
@RowsToReturn        INT = 10
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
IF @RowsToReturn > 0
        
SET ROWCOUNT @RowsToReturn

    
/******************************* DECLARE VARIABLE *************************************************/

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT
            
[CommentId]
        
FROM
            
[PostComments] (NOLOCK)
        
WHERE
            
[PostId] = @PostId
        
ORDER BY 
            
[CommentDateUtc] ASC
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetRecentCommentIds]    脚本日期: 01/07/2010 18:20:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    ids
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-06>
--
 Description: <Get recent CommentIds>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetRecentCommentIds]
(
    
@BlogId                INT,
    
@RowsToReturn        INT = 10
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
IF @RowsToReturn > 0
        
SET ROWCOUNT @RowsToReturn

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT
            
[CommentId]
        
FROM
            
[PostComments] (NOLOCK)
        
WHERE
            
[BlogId] = @BlogId
        
ORDER BY 
            
[CommentDateUtc] DESC
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_DeleteLink]    脚本日期: 01/07/2010 18:20:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

    public enum DeleteLinkStatus
    {
        UnknownFailure = 0,
        Success = 1,
        LinkNotFound = 2,
        ProviderError = 99,
    }
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-24>
--
 Description: <Create or update entity of Link, update owner's category's entities count in creation case.>
--
 =============================================
CREATE PROCEDURE [blogs].[up_DeleteLink]
(
    
@LinkId                INT
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    
/******************************* DECLARE VARIABLE *************************************************/
    
DECLARE        @CategoryId        INT

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- Validations.
        BEGIN

            
-- LinkNotFound = 2,
            IF (NOT EXISTS (SELECT *
                        
FROM [Links] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [LinkId] = @LinkId))
            
BEGIN
                
RETURN 2
            
END
        
END

        
-- init @CategoryId
        SELECT @CategoryId = [LinkCategoryId] FROM [Links] (NOLOCK) WHERE [LinkId] = @LinkId

        
-- begin operations.
        BEGIN TRANSACTION;

            
-- delete entity.
            DELETE FROM [Links]
            
WHERE [LinkId] = @LinkId

            
-- update count in owner category
            UPDATE
                
[Categories]
            
SET
                
[TotalEntities] = [TotalEntities] - 1
            
WHERE
                
[CategoryId] = @CategoryId

        
IF@@ERROR <> 0 )
            
BEGIN
                
ROLLBACK TRANSACTION
                
RETURN 0
            
END
        
ELSE
            
BEGIN
                
COMMIT TRANSACTION;
            
END

        
RETURN 1
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetLinks]    脚本日期: 01/07/2010 18:20:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    Link entities
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-25>
--
 Description: <Get Link entities by LinkIds>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetLinks]
(
    
@Ids        XML
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT
            L.
[LinkId]
            , 
[BlogId]
            , 
[LinkCategoryId]
            , 
[Title]
            , 
[Description]
            , 
[Url]
            , 
[RSS]
            , 
[CreatedDateUtc]
            , 
[NewWindow]
            , 
[State]
            , 
[SortOrder]
        
FROM
            
[Links] L (NOLOCK)
            
JOIN
            (
                
SELECT LI.Ids.value('@i','int'as LinkId
                
FROM @Ids.nodes('/es/e') LI(Ids) 
            ) xLI 
ON L.LinkId = xLI.LinkId

    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetLinkIdsByBlogId]    脚本日期: 01/07/2010 18:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    ids
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-25>
--
 Description: <Get LinkIdS by Blog>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetLinkIdsByBlogId]
(
    
@BlogId                INT
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT
            
[LinkId]
        
FROM
            
[Links] L (NOLOCK)
        
WHERE
            
[BlogId] = @BlogId 
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetLinkIdsByCategoryId]    脚本日期: 01/07/2010 18:20:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    ids
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-25>
--
 Description: <Get LinkIdS by CategoryId>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetLinkIdsByCategoryId]
(
    
@BlogId                INT,
    
@CategoryId            INT
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT
            
[LinkId]
        
FROM
            
[Links] L (NOLOCK)
        
WHERE
            
[BlogId] = @BlogId 
        
AND [LinkCategoryId] = @CategoryId
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_DeleteCategory]    脚本日期: 01/07/2010 18:20:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

    public enum DeleteCategoryStatus
    {
        /// <summary>
        /// 未知原因导致失败
        /// </summary>
        UnknownFailure = 0,
        /// <summary>
        /// 成功
        /// </summary>
        Success = 1,
        /// <summary>
        /// 未找到
        /// </summary>
        CategoryNotFound = 2
    }
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-23>
--
 Description: <Delete entity of Category, and its relationships>
--
 =============================================
CREATE PROCEDURE [blogs].[up_DeleteCategory]
(
    
@CategoryId            INT            OUT,
    
@CategoryType        TINYINT
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- Validations.
        BEGIN

            
-- CategoryNotFound = 2
            IF (NOT EXISTS (SELECT *
                        
FROM [Categories] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [CategoryId] = @CategoryId))
            
BEGIN
                
RETURN 2
            
END        

        
END

        
BEGIN TRY

            
BEGIN TRANSACTION;

                    
-- post category
                    IF @CategoryType = 1
                    
BEGIN
                        
DELETE FROM 
                            
[PostCategoryRelationship]
                        
WHERE
                            
[PostCategoryId] = @CategoryId
                    
END

                    
-- post tag
                    IF @CategoryType = 2
                    
BEGIN
                        
DELETE FROM 
                            
[PostTagRelationship]
                        
WHERE
                            
[PostTagId] = @CategoryId
                    
END

                    
-- link    category
                    IF @CategoryType = 3
                    
BEGIN
                        
DELETE FROM 
                            
[Links]
                        
WHERE
                            
[LinkCategoryId] = @CategoryId
                    
END


                    
-- delete entities.
                    DELETE FROM 
                        
[Categories]
                    
WHERE 
                        
[CategoryId] = @CategoryId

            
COMMIT TRANSACTION;

            
RETURN 1

        
END TRY
        
BEGIN CATCH
            
IF XACT_STATE() <> 0
            
BEGIN
                
ROLLBACK TRANSACTION;
                
RETURN 0
            
END
        
END CATCH
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetCategoriesWithBlogLink]    脚本日期: 01/07/2010 18:20:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    ids
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-04>
--
 Description: <Get Link Categories and owned links>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetCategoriesWithBlogLink]
(
    
@BlogId                INT,
    
@WithLinks            BIT
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    
/******************************* DECLARE VARIABLE *************************************************/

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT [CategoryId]
              ,
[BlogId]
              ,
[ParentId]
              ,
[CategoryType]
              ,
[CategoryName]
              ,
[LoweredCategoryName]
              ,
[Slug]
              ,
[LoweredSlug]
              ,
[Description]
              ,
[CreatedDateUtc]
              ,
[TotalEntities]
              ,
[SortOrder]
              ,
[State]
        
FROM
            
[Categories] C (NOLOCK)
        
WHERE
            
[BlogId] = @BlogId 
        
AND [CategoryType] = 3 -- Link Categorys

        
IF @WithLinks = 1
        
BEGIN
            
SELECT [LinkId]
                  ,
[BlogId]
                  ,
[LinkCategoryId]
                  ,
[Title]
                  ,
[Description]
                  ,
[Url]
                  ,
[RSS]
                  ,
[CreatedDateUtc]
                  ,
[NewWindow]
                  ,
[State]
                  ,
[SortOrder]
            
FROM [Links] L (NOLOCK)
            
WHERE [BlogId] = @BlogId 
        
END
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetPostIds_Day]    脚本日期: 01/07/2010 18:20:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    ids
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-05>
--
 Description: <Get blogIds by Post Year and Month and Day>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetPostIds_Day]
(
    
@BlogId                    INT,
    
@Year                    INT,
    
@Month                    INT,
    
@Day                    INT,
    
@RowsToReturn            INT = -1
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
IF @RowsToReturn > 0
        
SET ROWCOUNT @RowsToReturn

    
/******************************* DECLARE VARIABLE *************************************************/

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT 
                
[PostId]
        
FROM 
                
[BlogPosts] (NOLOCK)
        
WHERE 
                
[BlogId] = @BlogId
                
AND Year([PostDateUtc]= @Year
                
AND Month([PostDateUtc]= @Month
                
AND Day([PostDateUtc]= @Day
        
ORDER BY
                
[PostDateUtc] DESC
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetPostIds_Month]    脚本日期: 01/07/2010 18:20:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    ids
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-05>
--
 Description: <Get blogIds by Post Year and Month>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetPostIds_Month]
(
    
@BlogId                    INT,
    
@Year                    INT,
    
@Month                    INT,
    
@RowsToReturn            INT = -1
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
IF @RowsToReturn > 0
        
SET ROWCOUNT @RowsToReturn

    
/******************************* DECLARE VARIABLE *************************************************/

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT 
                
[PostId]
        
FROM 
                
[BlogPosts] (NOLOCK)
        
WHERE 
                
[BlogId] = @BlogId
                
AND Year([PostDateUtc]= @Year
                
AND Month([PostDateUtc]= @Month
        
ORDER BY
                
[PostDateUtc] DESC
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetArchiveDataItemsByMonth]    脚本日期: 01/07/2010 18:20:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    ids
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-04>
--
 Description: <Get ArchiveDataItems By Month and blog>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetArchiveDataItemsByMonth]
(
    
@BlogId                INT,
    
@Year                INT,
    
@Month                 INT
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    
/******************************* DECLARE VARIABLE *************************************************/

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT 
            
@Month                AS [Month]
            
@Year                AS [Year]
            
DAY(BP.PostDateUtc)    AS [Day]
            
COUNT(1)            AS [Count] 
        
FROM 
            
[BlogPosts] BP ( NOLOCK )
        
WHERE 
            BP.BlogId 
= @BlogId 
            
AND MONTH(BP.PostDateUtc) = @Month 
            
AND YEAR(BP.PostDateUtc) = @Year 
            
AND BP.PostDateUtc <= GETUTCDATE()
        
GROUP BY 
            
DAY(BP.PostDateUtc) 
        
ORDER BY  
            
[Day]
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetArchiveDataItemsByMonthList]    脚本日期: 01/07/2010 18:20:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    record set.
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-06>
--
 Description: <Get ArchiveDataItems (with miniposts if need) By Month and blog>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetArchiveDataItemsByMonthList]
(
    
@BlogId                INT,
    
@WithPosts            BIT = 0
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    
/******************************* DECLARE VARIABLE *************************************************/

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT 
            
MONTH(BP.PostDateUtc)    AS [Month]
            
YEAR(BP.PostDateUtc)    AS [Year]
            
1                        AS [Day]
            
COUNT(1)                AS [Count] 
        
FROM 
            
[BlogPosts] BP ( NOLOCK )
        
WHERE 
            BP.BlogId 
= @BlogId 
            
AND BP.PostDateUtc <= GETUTCDATE()
        
GROUP BY 
            
YEAR(BP.PostDateUtc), 
            
MONTH(BP.PostDateUtc) 
        
ORDER BY  
            
[Year]    DESC
            
[Month] DESC

        
-- Get MiniPosts.
        IF @WithPosts = 1
        
BEGIN

            
SELECT
                
@BlogId AS [BlogId],
                BP.
[PostId],
                P.
[Subject],
                BP.
[PostDateUtc]
            
FROM
                
[BlogPosts] BP
            
JOIN
                
[Posts] P
            
ON
                BP.
[PostId] = P.[PostId]
            
WHERE
                BP.
[BlogId] = @BlogId
            
ORDER BY
                BP.
[PostDateUtc] DESC

        
END
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetPostIds_Random]    脚本日期: 01/07/2010 18:20:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    ids
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-05>
--
 Description: <Get blogIds by Random>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetPostIds_Random]
(
    
@BlogId                    INT,
    
@RowsToReturn            INT = -1
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
IF @RowsToReturn > 0
        
SET ROWCOUNT @RowsToReturn

    
/******************************* DECLARE VARIABLE *************************************************/

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT 
                
[PostId]
        
FROM 
                
[BlogPosts] (NOLOCK)
        
WHERE 
                
[BlogId] = @BlogId
        
ORDER BY
                
NEWID()
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetPostIds_Year]    脚本日期: 01/07/2010 18:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    ids
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-05>
--
 Description: <Get blogIds by Post Year and Month>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetPostIds_Year]
(
    
@BlogId                    INT,
    
@Year                    INT,
    
@RowsToReturn            INT = -1
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
IF @RowsToReturn > 0
        
SET ROWCOUNT @RowsToReturn

    
/******************************* DECLARE VARIABLE *************************************************/

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT 
                
[PostId]
        
FROM 
                
[BlogPosts] (NOLOCK)
        
WHERE 
                
[BlogId] = @BlogId
                
AND Year([PostDateUtc]= @Year
        
ORDER BY
                
[PostDateUtc] DESC
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_UpdatePostInCategories]    脚本日期: 01/07/2010 18:20:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-06>
--
 Description: <Update Post category relationship.>
--
 =============================================
CREATE PROCEDURE [blogs].[up_UpdatePostInCategories]
(
    
@PostId                INT,
    
@CategoryIds        XML
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
/******************************* DECLARE VARIABLE *************************************************/

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN

        
BEGIN TRANSACTION;

            
-- Update total post count of original related categories.
            BEGIN
                
UPDATE 
                    
[Categories] WITH (ROWLOCK)
                
SET
                    
[TotalEntities] = [TotalEntities] - 1
                
WHERE
                    
[CategoryType] = 1 -- post category.
                    AND [CategoryId] IN 
                    (
                        
SELECT
                            
[CategoryId]
                        
FROM
                            
[PostCategoryRelationship]
                        
WHERE
                            
[PostId] = @PostId
                    )
            
END
    
            
-- Delete old Relationship record.
            BEGIN
                
DELETE FROM   
                    
[PostCategoryRelationship]
                
WHERE
                    
[PostId] = @PostId
            
END

            
-- Insert new relations.
            BEGIN
                
INSERT INTO [PostCategoryRelationship]
                       (
[PostId]
                       ,
[PostCategoryId])
                
SELECT
                    
@PostId,
                    UI.Ids.value(
'@c','int'AS PostCategoryId
                
FROM @CategoryIds.nodes('/cs/c') UI(Ids) 
            
END

            
-- Update total post count of new related categories.
            BEGIN
                
UPDATE 
                    
[Categories] WITH (ROWLOCK)
                
SET
                    
[TotalEntities] = [TotalEntities] + 1
                
WHERE
                    
[CategoryType] = 1 -- post category.
                    AND [CategoryId] IN 
                    (
                        
SELECT
                            
[CategoryId]
                        
FROM
                            
[PostCategoryRelationship]
                        
WHERE
                            
[PostId] = @PostId
                    )
            
END

        
IF@@ERROR <> 0 )
            
BEGIN
                
ROLLBACK TRANSACTION
                
RETURN -1
            
END
        
ELSE
            
BEGIN
                
COMMIT TRANSACTION;
            
END

        
RETURN 1
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_UpdatePostInTags]    脚本日期: 01/07/2010 18:20:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-06>
--
 Description: <Update Post Tag relationship.>
--
 =============================================
CREATE PROCEDURE [blogs].[up_UpdatePostInTags]
(
    
@PostId                INT,
    
@TagIds                XML
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
/******************************* DECLARE VARIABLE *************************************************/

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN

        
BEGIN TRANSACTION;

            
-- Update total post count of original related categories.
            BEGIN
                
UPDATE 
                    
[Categories] WITH (ROWLOCK)
                
SET
                    
[TotalEntities] = [TotalEntities] - 1
                
WHERE
                    
[CategoryType] = 2 -- post tag.
                    AND [CategoryId] IN 
                    (
                        
SELECT
                            
[CategoryId]
                        
FROM
                            
[PostTagRelationship]
                        
WHERE
                            
[PostId] = @PostId
                    )
            
END

            
-- Delete old Relationship record.
            BEGIN
                
DELETE FROM   
                    
[PostCategoryRelationship]
                
WHERE
                    
[PostId] = @PostId
            
END

            
-- Insert new tag relations.
            BEGIN
                
INSERT INTO [PostTagRelationship]
                       (
[PostId]
                       ,
[PostTagId])
                
SELECT
                    
@PostId,
                    UI.Ids.value(
'@t','int'AS PostTagId
                
FROM @TagIds.nodes('/ts/t') UI(Ids) 
            
END

            
-- Update total post count of new related categories.
            BEGIN
                
UPDATE 
                    
[Categories] WITH (ROWLOCK)
                
SET
                    
[TotalEntities] = [TotalEntities] + 1
                
WHERE
                    
[CategoryType] = 2 -- post tag.
                    AND [CategoryId] IN 
                    (
                        
SELECT
                            
[CategoryId]
                        
FROM
                            
[PostCategoryRelationship]
                        
WHERE
                            
[PostId] = @PostId
                    )
            
END

        
IF@@ERROR <> 0 )
            
BEGIN
                
ROLLBACK TRANSACTION
                
RETURN -1
            
END
        
ELSE
            
BEGIN
                
COMMIT TRANSACTION;
            
END

        
RETURN 1
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_CreateCategory]    脚本日期: 01/07/2010 18:20:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

    public enum CreateUpdateCategoryStatus
    {
        UnknownFailure = 0,
        Success = 1,
        DuplicateName = 2,
        DuplicateSlug = 3,
        InvalidName = 4,
        InvalidSlug = 5,
        CategoryNotFound = 6,
        InvalidCategoryType = 7,
        ProviderError = 99,
    }
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-24>
--
 Description: <Create entity of Category>
--
 =============================================
CREATE PROCEDURE [blogs].[up_CreateCategory]
(
    
@CategoryId                INT OUT,
    
@BlogId                    INT,
    
@ParentId                INT,
    
@CategoryType            TINYINT,
    
@CategoryName            NVARCHAR(512),
    
@Slug                    NVARCHAR(256),
    
@Description            NVARCHAR(1024),
    
@SortOrder                INT,
    
@State                    TINYINT
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- Validations.
        BEGIN

            
-- InvalidCategoryType = 7,
            IF (( @CategoryType < 1 ) OR ( @CategoryType > 3))
            
BEGIN
                
RETURN 7
            
END

            
-- InvalidName = 4,
            IF @CategoryName = '' OR @CategoryName IS NULL
                
RETURN 4
            
            
-- InvalidSlug = 5,
            IF @Slug = '' OR @Slug IS NULL
                
RETURN 5

            
-- DuplicateName = 2,
            IF (EXISTS (SELECT *
                        
FROM [Categories] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [LoweredCategoryName] = LOWER(@CategoryName)
                            
AND [BlogId] = @BlogId
                            
AND [CategoryType] = @CategoryType))
            
BEGIN
                
RETURN 2
            
END

            
-- DuplicateSlug = 3,
            IF (EXISTS (SELECT *
                        
FROM [Categories] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [LoweredSlug] = LOWER(@Slug)
                            
AND [BlogId] = @BlogId
                            
AND [CategoryType] = @CategoryType))
            
BEGIN
                
RETURN 3
            
END        
        
END

        
-- init @SortOrder.
        IF@SortOrder = 0 OR @SortOrder IS NULL ) 
            
SELECT @SortOrder = COALESCE(MAX(SortOrder) + 11FROM [Categories] WITH ( NOLOCK )
            
WHERE [BlogId] = @BlogId AND [CategoryType] = @CategoryType

        
-- begin operations.
        BEGIN TRANSACTION;

                
INSERT INTO
                    
[Categories]
                (
                    
[BlogId]
                    ,
[ParentId]
                    ,
[CategoryType]
                    ,
[CategoryName]
                    ,
[LoweredCategoryName]
                    ,
[Slug]
                    ,
[LoweredSlug]
                    ,
[Description]
                    ,
[CreatedDateUtc]
                    ,
[TotalEntities]
                    ,
[SortOrder]
                    ,
[State]
                )
                
VALUES
                (
                    
@BlogId
                    ,
@ParentId
                    ,
@CategoryType
                    ,
@CategoryName
                    ,
LOWER(@CategoryName)
                    ,
@Slug
                    ,
LOWER(@Slug)
                    ,
@Description
                    ,GETUTCDATE()
                    ,
0
                    ,
@SortOrder
                    ,
@State
                )

        
IF@@ERROR <> 0 )
            
BEGIN
                
ROLLBACK TRANSACTION
                
RETURN 0
            
END
        
ELSE
            
BEGIN
                
COMMIT TRANSACTION;
                
Select @CategoryId = SCOPE_IDENTITY()
            
END

        
RETURN 1
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_UpdateCategory]    脚本日期: 01/07/2010 18:20:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:

    public enum CreateUpdateCategoryStatus
    {
        UnknownFailure = 0,
        Success = 1,
        DuplicateName = 2,
        DuplicateSlug = 3,
        InvalidName = 4,
        InvalidSlug = 5,
        SectionNotFound = 6,
        InvalidCategoryType = 7,
        CategoryNotFound = 8,
        ProviderError = 99,
    }
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-25>
--
 Description: <Update entity of Category, and update category-entities relations table if need.>
--
 =============================================
CREATE PROCEDURE [blogs].[up_UpdateCategory]
(
    
@CategoryId                INT OUT,
    
@BlogId                    INT,
    
@CategoryType            TINYINT,
    
@CategoryName            NVARCHAR(512),
    
@Slug                    NVARCHAR(256),
    
@Description            NVARCHAR(1024),
    
@SortOrder                INT,
    
@State                    TINYINT
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    
/******************************* DECLARE VARIABLE *************************************************/
    
DECLARE        @OldState    TINYINT

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- Validations.
        BEGIN

            
-- InvalidCategoryType = 7,
            IF (( @CategoryType < 1 ) OR ( @CategoryType > 3))
            
BEGIN
                
RETURN 7
            
END

            
-- CategoryNotFound = 8,
            IF (NOT EXISTS (SELECT *
                        
FROM [Categories] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [CategoryId] = @CategoryId))
            
BEGIN
                
RETURN 8
            
END        

            
-- InvalidName = 4,
            IF @CategoryName = '' OR @CategoryName IS NULL
                
RETURN 4
            
            
-- InvalidSlug = 5,
            IF @Slug = '' OR @Slug IS NULL
                
RETURN 5

            
-- DuplicateName = 2,
            IF (EXISTS (SELECT *
                        
FROM [Categories] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [LoweredCategoryName] = LOWER(@CategoryName)
                            
AND [BlogId] = @BlogId
                            
AND [CategoryType] = @CategoryType))
            
BEGIN
                
RETURN 2
            
END

            
-- DuplicateSlug = 3,
            IF (EXISTS (SELECT *
                        
FROM [Categories] WITH ( UPDLOCK, HOLDLOCK )
                        
WHERE [LoweredSlug] = LOWER(@Slug)
                            
AND [BlogId] = @BlogId
                            
AND [CategoryType] = @CategoryType))
            
BEGIN
                
RETURN 3
            
END        
        
END

        
-- init @SortOrder.
        IF@SortOrder = 0 OR @SortOrder IS NULL ) 
            
SELECT @SortOrder = COALESCE(MAX(SortOrder) + 11FROM [Categories] WITH ( NOLOCK )
            
WHERE [BlogId] = @BlogId AND [CategoryType] = @CategoryType

        
-- init @OldState
        SELECT @OldState = [State] FROM [Categories] WITH ( UPDLOCK, HOLDLOCK )
                            
WHERE [CategoryId] = @CategoryId

        
-- begin operations.
        BEGIN TRANSACTION;

                
-- update category entity.
                UPDATE
                    
[Categories] WITH (ROWLOCK)
                
SET
                    
[BlogId] = @BlogId
                    ,
[CategoryName] = @CategoryName
                    ,
[LoweredCategoryName] = LOWER(@CategoryName)
                    ,
[Slug] = @Slug
                    ,
[LoweredSlug] = LOWER(@Slug)
                    ,
[Description] = @Description
                    ,
[SortOrder] = @SortOrder
                    ,
[State] = @State
                
WHERE
                    
[CategoryId] = @CategoryId

                
-- update relations table if state changes.
                IF ( @OldState <> @State ) 
                
BEGIN
                    
                    
-- post category
                    IF @CategoryType = 1
                    
BEGIN
                        
DELETE FROM 
                            
[PostCategoryRelationship] 
                        
WHERE
                            
[PostCategoryId] = @CategoryId
                    
END

                    
-- post tag
                    IF @CategoryType = 2
                    
BEGIN
                        
DELETE FROM 
                            
[PostTagRelationship] 
                        
WHERE
                            
[PostTagId] = @CategoryId
                    
END

                
END

        
IF@@ERROR <> 0 )
            
BEGIN
                
ROLLBACK TRANSACTION
                
RETURN 0
            
END
        
ELSE
            
BEGIN
                
COMMIT TRANSACTION;
                
Select @CategoryId = SCOPE_IDENTITY()
            
END

        
RETURN 1
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetCategories]    脚本日期: 01/07/2010 18:20:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    Category entities
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2009-12-25>
--
 Description: <Get Category entities by CategoryIds>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetCategories]
(
    
@Ids        XML
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
/******************************* DECLARE VARIABLE *************************************************/


    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT
            C.
[CategoryId]
            , 
[BlogId]
            , 
[ParentId]
            , 
[CategoryType]
            , 
[CategoryName]
            , 
[LoweredCategoryName]
            , 
[Slug]
            , 
[LoweredSlug]
            , 
[Description]
            , 
[CreatedDateUtc]
            , 
[TotalEntities]
            , 
[SortOrder]
            , 
[State]
        
FROM
            
[Categories] C (NOLOCK)
            
JOIN
            (
                
SELECT CI.Ids.value('@i','int'AS CategoryId
                
FROM @Ids.nodes('/es/e') CI(Ids) 
            ) xCI 
ON C.CategoryId = xCI.CategoryId
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetPostIds_Category]    脚本日期: 01/07/2010 18:20:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    ids
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-05>
--
 Description: <Get blogIds by Post Category>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetPostIds_Category]
(
    
@BlogId                    INT,
    
@PostCategoryId            INT,
    
@RowsToReturn            INT = -1
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
IF @RowsToReturn > 0
        
SET ROWCOUNT @RowsToReturn

    
/******************************* DECLARE VARIABLE *************************************************/

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT 
                
[PostId]
        
FROM 
                
[PostCategoryRelationship] (NOLOCK)
        
WHERE 
                
[PostCategoryId] = @PostCategoryId
                
AND [State] = 1 -- Normal status.
        ORDER BY
                
[PostDateUtc] DESC
    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetPosts]    脚本日期: 01/07/2010 18:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    Category entities
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-07>
--
 Description: <Get Posts entities by PostIds,
--
                 Get Post categories ids;
--
                 Get Post TagIds.>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetPosts]
(
    
@PostIds        XML
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
/******************************* DECLARE VARIABLE *************************************************/
    
DECLARE    @PostIdsTable TABLE
    (
        PostId    
INT
    )

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN

        
IF @PostIds IS NULL
            
RETURN

        
-- save ids to table variable.
        INSERT INTO @PostIdsTable 
            ( PostId )
        
SELECT 
            CI.Ids.value(
'@i','int'as PostId
        
FROM 
            
@PostIds.nodes('/ps/p') CI(Ids) 

        
-- query entities.
        SELECT 
            P.
[PostId]
            ,
[BlogId]
            ,
[UserId]
            ,
[PostAuthor]
            ,
[Slug]
            ,
[LoweredSlug]
            ,
[Subject]
            ,
[PostDateUtc]
            ,
[ApprovedDateUtc]
            ,
[TotalViews]
            ,
[TotalComments]
            ,
[TotalAggViews]
            ,
[RatingAverage]
            ,
[IPAddress]
            ,
[EnableComments]
            ,
[EnableRatings]
            ,
[IsBodyHtml]
            ,
[State]
            ,
[Property]
        
FROM 
            
[Posts] P ( NOLOCK )
        
JOIN
            
@PostIdsTable PIT
        
ON
            P.
[PostId] = PIT.[PostId]


        
-- get post categories.
        SELECT 
            PCR.
[PostCategoryId],
            PCR.
[PostId]
        
FROM 
            
[PostCategoryRelationship] PCR ( NOLOCK )
        
JOIN
            
@PostIdsTable PIT
        
ON
            PCR.
[PostId] = PIT.[PostId]
        
WHERE
            PCR.
[State] = 1 -- Normal state.


        
-- get post tags
        SELECT 
            PTR.
[PostTagId],
            PTR.
[PostId]
        
FROM 
            
[PostTagRelationship] PTR ( NOLOCK )
        
JOIN
            
@PostIdsTable PIT
        
ON
            PTR.
[PostId] = PIT.[PostId]
        
WHERE
            PTR.
[State] = 1 -- Normal state.

    
END
END
GO
/****** 对象:  StoredProcedure [blogs].[up_GetPostIds_Tag]    脚本日期: 01/07/2010 18:20:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
    ids
*/
-- =============================================
--
 Author:        <Lance Zhang>
--
 Create date: <2010-01-05>
--
 Description: <Get blogIds by Post Tag>
--
 =============================================
CREATE PROCEDURE [blogs].[up_GetPostIds_Tag]
(
    
@BlogId                    INT,
    
@PostTagId            INT,
    
@RowsToReturn            INT = -1
)
AS
BEGIN
    
/******************************* SET CONFIG *************************************************/
    
SET NOCOUNT ON
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
SET NUMERIC_ROUNDABORT OFF

    
IF @RowsToReturn > 0
        
SET ROWCOUNT @RowsToReturn

    
/******************************* DECLARE VARIABLE *************************************************/

    
/********************************BEGIN TRANSATION**********************************************/

    
BEGIN
        
-- query.
        SELECT 
                
[PostId]
        
FROM 
                
[PostTagRelationship] (NOLOCK)
        
WHERE 
                
[PostTagId] = @PostTagId
                
AND [State] = 1 -- Normal status.
        ORDER BY
                
[PostDateUtc] DESC
    
END
END
GO

 

 

posted @ 2010-01-07 18:30  LanceZhang  阅读(667)  评论(7编辑  收藏  举报