关于更新blog

1.数据库中运行

a.加两个表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[blog_MailNotify]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[blog_MailNotify]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[blog_Profile]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[blog_Profile]
GO

CREATE TABLE [dbo].[blog_MailNotify] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [EntryID] [int] NOT NULL ,
 [BlogID] [int] NOT NULL ,
 [SendToBlogID] [int] NOT NULL ,
 [EMail] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[blog_Profile] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [BlogID] [int] NOT NULL ,
 [City] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

b.修改表blog_SkinControl 加入一个 defaultview（bit）字段

c.存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[blog_GetAggregatedBloggerList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[blog_GetAggregatedBloggerList]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE blog_GetAggregatedBloggerList AS

Select BlogID From blog_config
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

2.增加
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[blog_MailNotify]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[blog_MailNotify]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[blog_MailNotify_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[blog_MailNotify_Delete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[blog_MailNotify_GetMailList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[blog_MailNotify_GetMailList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[blog_MailNotify_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[blog_MailNotify_Insert]
GO




CREATE TABLE [dbo].[blog_MailNotify] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [EntryID] [int] NOT NULL ,
 [BlogID] [int] NOT NULL ,
 [SendToBlogID] [int] NOT NULL ,
 [EMail] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]


ALTER TABLE [dbo].[blog_MailNotify] WITH NOCHECK ADD
 CONSTRAINT [PK_blog_MailNotify] PRIMARY KEY  CLUSTERED
 (
  [ID]
 )  ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[blog_DeletePost]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[blog_DeletePost]
GO

CREATE Proc blog_DeletePost
(
 @ID int,
 @BlogID int
)
as

Declare @ParentID int, @PostType int

Insert blog_Content_Audit ([ID], [Title], [DateAdded], [SourceUrl], [PostType], [Author], [Email], [SourceName], [BlogID], [Description], [DateUpdated], [TitleUrl], [Text], [ParentID], [FeedBackCount], [PostConfig], [EntryName], [IsOriginal])
Select [ID], [Title], [DateAdded], [SourceUrl], [PostType], [Author], [Email], [SourceName], [BlogID], [Description], [DateUpdated], [TitleUrl], [Text], [ParentID], [FeedBackCount], [PostConfig], [EntryName], [IsOriginal] FROM blog_Content
Where [ID] = @ID

Select @ParentID = ParentID, @PostType = PostType From blog_Content where [ID] = @ID

if(@PostType = 8 or @PostType = 4)
Begin
 Update blog_Content
 Set FeedBackCount = FeedBackCount - 1
 where [ID] = @ParentID
 
 Delete From blog_Comment_Audit where EntryID = @ID

End
Else
Begin

 Delete from blog_MailNotify where EntryID=@ID
 Delete From blog_Comment_Audit where EntryID in (Select [ID] From blog_Content Where ParentID = @ID)
 Delete From blog_Content Where ParentID = @ID
 Delete From blog_Links where PostID = @ID
 Delete From blog_EntryViewCount where EntryID = @ID
 Delete From blog_Referrals where EntryID = @ID
End

Delete From blog_Content Where blog_Content.[ID] = @ID and blog_Content.BlogID = @BlogID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[blog_GenericGetEntryIDs_10]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[blog_GenericGetEntryIDs_10]
GO

CREATE PROC blog_GenericGetEntryIDs_10
(
 @ItemCount int,
 @PostType int,
 @PostConfig int,
 @BlogID int,
 @CategoryID int = null,
 @CategoryName nvarchar(100) = null,
 @StartDate datetime = null,
 @StopDate datetime = null,
 @CategoryType int = null,
 @BlogGroupID int=null,
 @Author nvarchar(100) = null
)
as

/*
 Generic Entry Collection Proc With Categories

 All possible combinations will be filter by PostTye, PostConfig, and BlogID

 # of records will be controlled rowcount

 Order of precidence:
  CategoryID
  CategoryName
  StartDate
  Default
*/
SET NOCOUNT ON
set rowcount @ItemCount
--按作者名查询
if(@Author is not null)
begin
 SELECT
   bc.[ID]
  FROM
   blog_Content bc with(nolock)
  WHERE
   bc.PostType | @PostType = @PostType
   and bc.Author = @Author
  ORDER BY
   bc.[dateadded] desc
 return
end
--用户组
if(@BlogGroupID is not null)
Begin
if(@BlogGroupID = 1000)
Begin
 SELECT
  bc.[ID]
 FROM
  blog_Content bc with(nolock)
 INNER Join blog_Comment_Audit bca on bc.ID=bca.EntryID
 WHERE
  bc.PostConfig & @PostConfig = @PostConfig
  and bc.PostType | @PostType = @PostType
  and bca.OwnerBlogID=@BlogID
 ORDER BY
  bc.[dateadded] desc
 return
End
else if(@BlogGroupID = 2000)--查询订阅的文章
Begin
 SELECT
  bc.[ID]
 FROM
  blog_Content bc
 INNER Join blog_MailNotify bmn on bc.ID=bmn.EntryID and bc.BlogID=bmn.BlogID
 WHERE
  bc.PostConfig & @PostConfig = @PostConfig
  and bc.PostType | @PostType = @PostType
  and bmn.SendToBlogID=@BlogID
 ORDER BY
  bc.[dateadded] desc
 return
End
else
Begin
 if(@StartDate is not null and @StopDate is null)
  begin
  Set @StopDate = DateAdd(day,1,@StartDate)
  
  SELECT
   bc.[ID]
  FROM
   blog_Content bc with(nolock)
   INNER JOIN blog_Links bl with(nolock) on bc.ID = bl.PostID
  WHERE
   bc.PostType | @PostType = @PostType
   and bc.BlogID in (Select BlogID from blog_UsersInGroups where GroupID=@BlogGroupID)
   and bc.DateAdded >= @StartDate and bc.DateAdded <= @StopDate
   and bl.CategoryID =808
  ORDER BY
   bc.[dateadded] desc
  end
 else
  begin
 SELECT
   bc.[ID]
  FROM
   blog_Content bc with(nolock)
   INNER JOIN blog_Links bl with(nolock) on bc.ID = bl.PostID
  WHERE
   bc.PostConfig & @PostConfig = @PostConfig
   and bc.PostType | @PostType = @PostType
   and bc.BlogID in (Select BlogID from blog_UsersInGroups where GroupID=@BlogGroupID)
   and bl.CategoryID =808
  ORDER BY
   bc.[dateadded] desc
  end
  return
End
End
--精华区
if(@CategoryType is not null)
Begin
 if(@StartDate is not null and @StopDate is null)
  begin
  Set @StopDate = DateAdd(day,1,@StartDate)
  
  SELECT
   bc.[ID]
  FROM
   blog_Content bc with(nolock)
   INNER JOIN blog_Links bl with(nolock) on bc.ID = bl.PostID
   INNER JOIN blog_LinkCategories bcat with(nolock) on bl.CategoryID = bcat.CategoryID
  WHERE
   bc.PostType | @PostType = @PostType
   and bl.CategoryID in(Select CategoryID from blog_LinkCategories where CategoryType=@CategoryType)
   and bc.DateAdded >= @StartDate and bc.DateAdded <= @StopDate
  ORDER BY
   bc.[dateadded] desc
  end
 else
  begin
 SELECT
   bc.[ID]
  FROM
   blog_Content bc with(nolock)
   INNER JOIN blog_Links bl with(nolock) on bc.ID = bl.PostID
  WHERE
   bc.PostType | @PostType = @PostType
   and bl.CategoryID in(Select CategoryID from blog_LinkCategories where CategoryType=@CategoryType)
  ORDER BY
   bc.[dateadded] desc
  end
  
  return
End

if(@BlogID is not null)
Begin
--Do we have a CategoryID?
if(@CategoryID is not null)
Begin
 --we will filter by categoryID. Should we also filter by date?
 if(@StartDate is null)
 Begin
  -- No Date Filter
  SELECT
   bc.[ID]
  FROM
   blog_Content bc with(nolock)
   INNER JOIN blog_Links bl with(nolock) on bc.ID = bl.PostID
  WHERE
   bc.BlogID = @BlogID
   and bc.PostConfig & @PostConfig = @PostConfig
   and bc.PostType | @PostType = @PostType
   and bl.CategoryID = @CategoryID
  ORDER BY
   bc.[dateadded] desc
 End
 Else
 Begin
  --Filter by CategoryID and Date.

  --If we only have a start date and no stop date, add 24 hours to to stopdate
  if(@StartDate is not null and @StopDate is null)
  Set @StopDate = DateAdd(day,1,@StartDate)
  
  -- No Date Filter
  SELECT
   bc.[ID]
  FROM
   blog_Content bc with(nolock)
   INNER JOIN blog_Links bl with(nolock) on bc.ID = bl.PostID
  WHERE
   bc.BlogID = @BlogID
   and bc.PostConfig & @PostConfig = @PostConfig
   and bc.PostType | @PostType = @PostType
   and bl.CategoryID = @CategoryID
   and bc.DateAdded >= @StartDate and bc.DateAdded <= @StopDate
  ORDER BY
   bc.[dateadded] desc
 End
End
-- Do we have a CategoryName? (CategoryID will override this value)
else if(@CategoryName is not null)
Begin
 --We will filter by categryName (Title)
 --Should we also filter by Date?
 if(@StartDate is null)
 Begin
  -- Filter by CategoryName and not Date
  SELECT
   bc.[ID]
  FROM
   blog_Content bc with(nolock)
   INNER JOIN blog_Links bl with(nolock) on bc.ID = bl.PostID
   INNER JOIN blog_LinkCategories bcat with(nolock) on bl.CategoryID = bcat.CategoryID

  WHERE
   bc.BlogID = @BlogID
   and bc.PostConfig & @PostConfig = @PostConfig
   and bc.PostType | @PostType = @PostType
   and bcat.Title = @CategoryName
  ORDER BY
   bc.[dateadded] desc
 End
 Else
 Begin
  --Filter by CategoryName (Title) and Date

  --If we only have a start date and no stop date, add 24 hours to to stopdate
  if(@StartDate is not null and @StopDate is null)
  Set @StopDate = DateAdd(day,1,@StartDate)
  
  SELECT
   bc.[ID]
  FROM
   blog_Content bc with(nolock)
   INNER JOIN blog_Links bl with(nolock) on bc.ID = bl.PostID
   INNER JOIN blog_LinkCategories bcat with(nolock) on bl.CategoryID = bcat.CategoryID
  WHERE
   bc.BlogID = @BlogID
   and bc.PostConfig & @PostConfig = @PostConfig
   and bc.PostType | @PostType = @PostType
   and bcat.Title = @CategoryName
   and bc.DateAdded >= @StartDate and bc.DateAdded <= @StopDate
  ORDER BY
   bc.[dateadded] desc
 End
End
else if(@StartDate is not null)
Begin
 --No categoryID or Category was found. We will ONLY filter by dates

 --If we only have a start date and no stop date, add 24 hours to to stopdate
 if(@StartDate is not null and @StopDate is null)
 Set @StopDate = DateAdd(day,1,@StartDate)

 SELECT
  bc.[ID]
 FROM
  blog_Content bc with(nolock)
 WHERE
  bc.BlogID = @BlogID
  and bc.PostConfig & @PostConfig = @PostConfig
  and bc.PostType | @PostType = @PostType
  and bc.DateAdded >= @StartDate and bc.DateAdded <= @StopDate
 ORDER BY
  bc.[dateadded] desc

End
Else
Begin
 --All else has failed :)
 --We will just select the last x number of items
 SELECT
  bc.[ID]
 FROM
  blog_Content bc with(nolock)
 WHERE
  bc.BlogID = @BlogID
  and bc.PostConfig & @PostConfig = @PostConfig
  and bc.PostType | @PostType = @PostType
 ORDER BY
  bc.[dateadded] desc
End
End
Else
--BlogID is Null
Begin
--Do we have a CategoryID?
if(@CategoryID is not null)
Begin
 --we will filter by categoryID. Should we also filter by date?
 if(@StartDate is null)
 Begin
  -- No Date Filter
  SELECT
   bc.[ID]
  FROM
   blog_Content bc with(nolock)
   INNER JOIN blog_Links bl with(nolock) on bc.ID = bl.PostID
   INNER JOIN blog_Config bcc with(nolock) on bc.BlogID = bcc.BlogID and bcc.IsAggregated = 1
  WHERE
   bc.PostConfig & @PostConfig = @PostConfig
   and bc.PostType | @PostType = @PostType
   and bl.CategoryID = @CategoryID
  ORDER BY
   bc.[dateadded] desc
 End
 Else
 Begin
  --Filter by CategoryID and Date.

  --If we only have a start date and no stop date, add 24 hours to to stopdate
  if(@StartDate is not null and @StopDate is null)
  Set @StopDate = DateAdd(day,1,@StartDate)
  
  -- No Date Filter
  SELECT
   bc.[ID]
  FROM
   blog_Content bc with(nolock)
   INNER JOIN blog_Links bl with(nolock) on bc.ID = bl.PostID
   INNER JOIN blog_Config bcc with(nolock) on bc.BlogID = bcc.BlogID and bcc.IsAggregated = 1
  WHERE
   bc.PostConfig & @PostConfig = @PostConfig
   and bc.PostType | @PostType = @PostType
   and bl.CategoryID = @CategoryID
   and bc.DateAdded >= @StartDate and bc.DateAdded <= @StopDate
  ORDER BY
   bc.[dateadded] desc
 End
End
-- Do we have a CategoryName? (CategoryID will override this value)
else if(@CategoryName is not null)
Begin
 --We will filter by categryName (Title)
 --Should we also filter by Date?
 if(@StartDate is null)
 Begin
  -- Filter by CategoryName and not Date
  SELECT
   bc.[ID]
  FROM
   blog_Content bc with(nolock)
   INNER JOIN blog_Links bl with(nolock) on bc.ID = bl.PostID
   INNER JOIN blog_LinkCategories bcat with(nolock) on bl.CategoryID = bcat.CategoryID
   INNER JOIN blog_Config bcc with(nolock) on bc.BlogID = bcc.BlogID and bcc.IsAggregated = 1
  WHERE
   bc.PostConfig & @PostConfig = @PostConfig
   and bc.PostType | @PostType = @PostType
   and bcat.Title = @CategoryName
  ORDER BY
   bc.[dateadded] desc
 End
 Else
 Begin
  --Filter by CategoryName (Title) and Date

  --If we only have a start date and no stop date, add 24 hours to to stopdate
  if(@StartDate is not null and @StopDate is null)
  Set @StopDate = DateAdd(day,1,@StartDate)
  
  SELECT
   bc.[ID]
  FROM
   blog_Content bc with(nolock)
   INNER JOIN blog_Links bl with(nolock) on bc.ID = bl.PostID
   INNER JOIN blog_LinkCategories bcat with(nolock) on bl.CategoryID = bcat.CategoryID
   INNER JOIN blog_Config bcc with(nolock) on bc.BlogID = bcc.BlogID and bcc.IsAggregated = 1
  WHERE
   bc.PostConfig & @PostConfig = @PostConfig
   and bc.PostType | @PostType = @PostType
   and bcat.Title = @CategoryName
   and bc.DateAdded >= @StartDate and bc.DateAdded <= @StopDate
  ORDER BY
   bc.[dateadded] desc
 End
End
else if(@StartDate is not null)
Begin
 --No categoryID or Category was found. We will ONLY filter by dates

 --If we only have a start date and no stop date, add 24 hours to to stopdate
 if(@StartDate is not null and @StopDate is null)
 Set @StopDate = DateAdd(day,1,@StartDate)

 SELECT
  bc.[ID]
 FROM
  blog_Content bc with(nolock)
  INNER JOIN blog_Config bcc with(nolock) on bc.BlogID = bcc.BlogID and bcc.IsAggregated = 1
 WHERE
  bc.PostConfig & @PostConfig = @PostConfig
  and bc.PostType | @PostType = @PostType
  and bc.DateAdded >= @StartDate and bc.DateAdded <= @StopDate
  
 ORDER BY
  bc.[dateadded] desc

End
Else
Begin
 --All else has failed :)
 --We will just select the last x number of items
 IF (@PostType = 8)
 Begin
 SELECT
  bc.[ID]
 FROM
  blog_Content bc with(nolock)
  INNER JOIN blog_Config bcc with(nolock) on bc.BlogID = bcc.BlogID and bcc.IsAggregated = 1
 WHERE
  bc.PostConfig & @PostConfig = @PostConfig
  and bc.PostType | @PostType = @PostType
  and bc.ParentID in (Select blog_Content.ID from blog_Content where blog_Content.ID=bc.ParentID and blog_Content.PostConfig=93)
  --and blog_Content.[ID] not in(select blog_Links.PostID from blog_Links  where blog_Links.PostID=bc.ParentID and blog_Links.CategoryID=807))
 ORDER BY
  bc.[dateadded] desc
 End
 Else
 Begin
 SELECT
  bc.[ID]
 FROM
  blog_Content bc with(nolock)
  INNER JOIN blog_Config bcc with(nolock) on bc.BlogID = bcc.BlogID and bcc.IsAggregated = 1
 WHERE
  bc.PostConfig & @PostConfig = @PostConfig
  and bc.PostType | @PostType = @PostType
 ORDER BY
  bc.[dateadded] desc
 End
End
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.blog_MailNotify_Delete
 (
  @EntryID int,
  @SendToBlogID int
 )

AS
 delete from blog_MailNotify where EntryID=@EntryID and SendToBlogID=@SendToBlogID
 
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.blog_MailNotify_GetMailList
 (
  @EntryID int
 )

AS
 select EMail from blog_MailNotify where EntryID = @EntryID
 RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.blog_MailNotify_Insert
(
  @EntryID int,
  @BlogID int,
  @SendToBlogID int,
  @EMail nvarchar(150)
)

AS
 IF NOT EXISTS (SELECT EntryID FROM blog_MailNotify  WHERE EntryID = @EntryID AND SendToBlogID = @SendToBlogID )
 insert into blog_MailNotify(EntryID,BlogID,SendToBlogID,EMail)values(@EntryID,@BlogID,@SendToBlogID,@EMail)
 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO