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,1) NOT NULL,
[CreatorUserId] [int] NOT NULL,
[Slug] [nvarchar](128) NOT NULL,
[LoweredSlug] [nvarchar](128) NOT NULL,
[DisplayName] [nvarchar](256) NOT NULL,
[LoweredDisplayName] [nvarchar](256) NOT NULL,
[Description] [nvarchar](512) NOT NULL,
[Theme] [nvarchar](256) NOT 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 = ON) ON [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 = ON) ON [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 = ON) 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'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](max) NOT NULL,
[FormattedBody] [nvarchar](max) NOT 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 = ON) ON [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 = ON) ON [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 = ON) ON [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 = ON) ON [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 = ON) ON [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,1) NOT 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](256) NOT NULL,
[PostAuthor] [nvarchar](256) NOT NULL CONSTRAINT [DF_Comments_PostAuthor] DEFAULT (''),
[IPAddress] [bigint] NOT NULL CONSTRAINT [DF_Comments_IPAddress] DEFAULT ((0)),
[EmailAddress] [nvarchar](256) NOT NULL,
[WebUrl] [nvarchar](256) NOT NULL,
[TotalViews] [int] NOT NULL,
[CommentDateUtc] [datetime] NOT NULL CONSTRAINT [DF_Comments_CommentDateUtc] DEFAULT (getutcdate()),
[FormattedBody] [nvarchar](max) NOT 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 = ON) ON [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 = ON) ON [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 = ON) ON [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 = ON) 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'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 = ON) ON [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 = ON) ON [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 = ON) 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'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 = ON) ON [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 = ON) ON [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 = ON) 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'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](128) NOT 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 = ON) ON [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,1) NOT NULL,
[BlogId] [int] NOT NULL,
[LinkCategoryId] [int] NOT NULL,
[Title] [nvarchar](256) NOT NULL,
[Description] [nvarchar](512) NOT NULL CONSTRAINT [DF_Links_Description] DEFAULT (''),
[Url] [nvarchar](256) NOT NULL,
[RSS] [nvarchar](256) NOT 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 = ON) ON [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 = ON) 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'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,1) NOT NULL,
[BlogId] [int] NOT NULL,
[UserId] [int] NOT NULL,
[PostAuthor] [nvarchar](256) NOT NULL CONSTRAINT [DF_Posts_PostAuthor] DEFAULT (''),
[Slug] [nvarchar](128) NOT NULL,
[LoweredSlug] [nvarchar](128) NOT NULL CONSTRAINT [DF_Posts_LoweredSlug] DEFAULT (''),
[Subject] [nvarchar](256) NOT NULL CONSTRAINT [DF_Posts_Subject] DEFAULT (''),
[Summary] [nvarchar](512) NOT 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 = ON) ON [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 = ON) ON [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 = ON) ON [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,1) NOT NULL,
[BlogId] [int] NOT NULL,
[ParentId] [int] NOT NULL CONSTRAINT [DF_Categories_ParentId] DEFAULT ((0)),
[CategoryType] [tinyint] NOT NULL,
[CategoryName] [nvarchar](256) NOT NULL,
[LoweredCategoryName] [nvarchar](256) NOT NULL,
[Slug] [nvarchar](128) NOT NULL,
[LoweredSlug] [nvarchar](128) NOT NULL,
[Description] [nvarchar](512) NOT 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 = ON) ON [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 = ON) ON [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 = ON) 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'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) + 1, 1), -- 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(@Slug) AND @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) + 1, 1) FROM [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) + 1, 1) FROM [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) + 1, 1) FROM [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) + 1, 1) FROM [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) + 1, 1) FROM [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
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,1) NOT NULL,
[CreatorUserId] [int] NOT NULL,
[Slug] [nvarchar](128) NOT NULL,
[LoweredSlug] [nvarchar](128) NOT NULL,
[DisplayName] [nvarchar](256) NOT NULL,
[LoweredDisplayName] [nvarchar](256) NOT NULL,
[Description] [nvarchar](512) NOT NULL,
[Theme] [nvarchar](256) NOT 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 = ON) ON [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 = ON) ON [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 = ON) 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'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](max) NOT NULL,
[FormattedBody] [nvarchar](max) NOT 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 = ON) ON [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 = ON) ON [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 = ON) ON [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 = ON) ON [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 = ON) ON [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,1) NOT 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](256) NOT NULL,
[PostAuthor] [nvarchar](256) NOT NULL CONSTRAINT [DF_Comments_PostAuthor] DEFAULT (''),
[IPAddress] [bigint] NOT NULL CONSTRAINT [DF_Comments_IPAddress] DEFAULT ((0)),
[EmailAddress] [nvarchar](256) NOT NULL,
[WebUrl] [nvarchar](256) NOT NULL,
[TotalViews] [int] NOT NULL,
[CommentDateUtc] [datetime] NOT NULL CONSTRAINT [DF_Comments_CommentDateUtc] DEFAULT (getutcdate()),
[FormattedBody] [nvarchar](max) NOT 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 = ON) ON [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 = ON) ON [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 = ON) ON [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 = ON) 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'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 = ON) ON [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 = ON) ON [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 = ON) 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'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 = ON) ON [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 = ON) ON [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 = ON) 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'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](128) NOT 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 = ON) ON [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,1) NOT NULL,
[BlogId] [int] NOT NULL,
[LinkCategoryId] [int] NOT NULL,
[Title] [nvarchar](256) NOT NULL,
[Description] [nvarchar](512) NOT NULL CONSTRAINT [DF_Links_Description] DEFAULT (''),
[Url] [nvarchar](256) NOT NULL,
[RSS] [nvarchar](256) NOT 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 = ON) ON [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 = ON) 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'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,1) NOT NULL,
[BlogId] [int] NOT NULL,
[UserId] [int] NOT NULL,
[PostAuthor] [nvarchar](256) NOT NULL CONSTRAINT [DF_Posts_PostAuthor] DEFAULT (''),
[Slug] [nvarchar](128) NOT NULL,
[LoweredSlug] [nvarchar](128) NOT NULL CONSTRAINT [DF_Posts_LoweredSlug] DEFAULT (''),
[Subject] [nvarchar](256) NOT NULL CONSTRAINT [DF_Posts_Subject] DEFAULT (''),
[Summary] [nvarchar](512) NOT 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 = ON) ON [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 = ON) ON [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 = ON) ON [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,1) NOT NULL,
[BlogId] [int] NOT NULL,
[ParentId] [int] NOT NULL CONSTRAINT [DF_Categories_ParentId] DEFAULT ((0)),
[CategoryType] [tinyint] NOT NULL,
[CategoryName] [nvarchar](256) NOT NULL,
[LoweredCategoryName] [nvarchar](256) NOT NULL,
[Slug] [nvarchar](128) NOT NULL,
[LoweredSlug] [nvarchar](128) NOT NULL,
[Description] [nvarchar](512) NOT 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 = ON) ON [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 = ON) ON [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 = ON) 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'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) + 1, 1), -- 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(@Slug) AND @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) + 1, 1) FROM [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) + 1, 1) FROM [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) + 1, 1) FROM [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) + 1, 1) FROM [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) + 1, 1) FROM [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
