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
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。