实现CommunityStartKit的中文搜索(转)
官方版本的CommunityStartKit(简称CSK)的搜索功能只支持英文的词汇搜索,中文只能整个文章或句子作为关键字实现搜索,其实就是中文没有进行词汇分割。下面是CSK中文搜索实现方法:
1. 先在SQL SERVER建立全文检索
2. 在表Community_ContentPages上按contentPage_title,contentPage_description建立全文检索
其中SQLServer全文检索有中文问题:
在\Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config 目录用修改文件noise.chs,内容用字符'@'保存即可。
3.修改存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Community_SearchAddSearchKey]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Community_SearchAddSearchKey]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Community_SearchGetSearchResults]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Community_SearchGetSearchResults]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.Community_SearchAddSearchKey
(
@communityID int,
@sectionID int,
@contentPageID int,
@searchKey nvarchar(100)
)
AS
DELETE
Community_SearchKeys
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.Community_SearchGetSearchResults
(
@communityID int,
@username nvarchar(50),
@sectionID int,
@searchString nvarchar(50)
)
AS
-- Get User ID
DECLARE @UserID Int
SET @UserID = dbo.Community_GetUserID( @communityID, @Username)
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
PageID int
)
-- Create dynamic search string
DECLARE @search nvarchar(4000)
IF @sectionID = -1
BEGIN
SET @search =
'INSERT INTO #PageIndex(PageID)
SELECT TOP 50
contentPage_id
FROM
Community_ContentPages
WHERE
(Contains(contentPage_title,' + @searchString + ') or Contains(contentPage_description,' + @searchString + '))
AND dbo.Community_IsSectionAllowed(' + CAST(@communityID AS NVarChar(10)) + ', contentPage_sectionID, ''' + @username + ''')=1
GROUP BY
contentPage_id
ORDER BY COUNT(*) DESC'
END
ELSE
BEGIN
SET @search =
'INSERT INTO #PageIndex(PageID)
SELECT TOP 50
contentPage_id
FROM
Community_ContentPages
WHERE
(Contains(contentPage_title,' + @searchString + ') or Contains(contentPage_description,' + @searchString + '))
and contentPage_sectionID =' + CAST(@sectionID AS NVarchar(10)) +'
GROUP BY
contentPage_id
ORDER BY COUNT(*) DESC'
END
-- Execute Dynamic query
EXEC (@search)
-- create static date for UDF
DECLARE @currentDate DateTime
SET @currentDate = GetUtcDate()
SELECT
Content.*
FROM
dbo.Community_GetContentItem(@communityID, @userID, @currentDate) Content
INNER JOIN #PageIndex WITH (nolock)
ON ContentPage_ID = #PageIndex.PageID
ORDER BY
#PageIndex.IndexID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
drop procedure [dbo].[Community_SearchAddSearchKey]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Community_SearchGetSearchResults]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Community_SearchGetSearchResults]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.Community_SearchAddSearchKey
(
@communityID int,
@sectionID int,
@contentPageID int,
@searchKey nvarchar(100)
)
AS
DELETE
Community_SearchKeys
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.Community_SearchGetSearchResults
(
@communityID int,
@username nvarchar(50),
@sectionID int,
@searchString nvarchar(50)
)
AS
-- Get User ID
DECLARE @UserID Int
SET @UserID = dbo.Community_GetUserID( @communityID, @Username)
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
PageID int
)
-- Create dynamic search string
DECLARE @search nvarchar(4000)
IF @sectionID = -1
BEGIN
SET @search =
'INSERT INTO #PageIndex(PageID)
SELECT TOP 50
contentPage_id
FROM
Community_ContentPages
WHERE
(Contains(contentPage_title,' + @searchString + ') or Contains(contentPage_description,' + @searchString + '))
AND dbo.Community_IsSectionAllowed(' + CAST(@communityID AS NVarChar(10)) + ', contentPage_sectionID, ''' + @username + ''')=1
GROUP BY
contentPage_id
ORDER BY COUNT(*) DESC'
END
ELSE
BEGIN
SET @search =
'INSERT INTO #PageIndex(PageID)
SELECT TOP 50
contentPage_id
FROM
Community_ContentPages
WHERE
(Contains(contentPage_title,' + @searchString + ') or Contains(contentPage_description,' + @searchString + '))
and contentPage_sectionID =' + CAST(@sectionID AS NVarchar(10)) +'
GROUP BY
contentPage_id
ORDER BY COUNT(*) DESC'
END
-- Execute Dynamic query
EXEC (@search)
-- create static date for UDF
DECLARE @currentDate DateTime
SET @currentDate = GetUtcDate()
SELECT
Content.*
FROM
dbo.Community_GetContentItem(@communityID, @userID, @currentDate) Content
INNER JOIN #PageIndex WITH (nolock)
ON ContentPage_ID = #PageIndex.PageID
ORDER BY
#PageIndex.IndexID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
4.增加函数
CREATE FUNCTION dbo.Community_IsSectionAllowed
(
@communityID INT,
@sectionID INT,
@username NVarchar(50)
)
RETURNS BIT
AS
BEGIN
/* Public content */
IF @sectionID IN
(
SELECT
ss_sectionID
FROM
Community_SectionSecurity
join Community_Sections
on Community_SectionSecurity.ss_sectionID = Community_Sections.section_id
WHERE
ss_communityID = @communityID
AND ss_roleType = 0
AND ss_role='Community-Everyone'
AND Community_Sections.section_isEnabled = 1
)
RETURN 1
If NOT(dbo.Community_GetUserID(@communityID, @Username) IS NULL)
BEGIN
/* Authenticated User allowed content */
If @sectionID IN
(
SELECT
ss_sectionID
FROM
Community_SectionSecurity
join Community_Sections
on Community_SectionSecurity.ss_sectionID = Community_Sections.section_id
WHERE
ss_communityID = @communityID
AND ss_roleType = 0
AND ss_role='Community-Authenticated'
AND Community_Sections.section_isEnabled = 1
)
RETURN 1
/* User allowed content */
If @sectionID IN
(
SELECT
ss_sectionID
FROM
Community_SectionSecurity
join Community_Sections
on Community_SectionSecurity.ss_sectionID = Community_Sections.section_id
join Community_UsersInRoles
on Community_UsersInRoles.UserRoles_Rolename = Community_SectionSecurity.ss_role
WHERE
ss_communityID = @communityID
AND ss_roleType = 0
AND Community_UsersInRoles.UserRoles_Username = @userName
AND Community_Sections.section_isEnabled = 1
)
RETURN 1
END
RETURN 0
END
(
@communityID INT,
@sectionID INT,
@username NVarchar(50)
)
RETURNS BIT
AS
BEGIN
/* Public content */
IF @sectionID IN
(
SELECT
ss_sectionID
FROM
Community_SectionSecurity
join Community_Sections
on Community_SectionSecurity.ss_sectionID = Community_Sections.section_id
WHERE
ss_communityID = @communityID
AND ss_roleType = 0
AND ss_role='Community-Everyone'
AND Community_Sections.section_isEnabled = 1
)
RETURN 1
If NOT(dbo.Community_GetUserID(@communityID, @Username) IS NULL)
BEGIN
/* Authenticated User allowed content */
If @sectionID IN
(
SELECT
ss_sectionID
FROM
Community_SectionSecurity
join Community_Sections
on Community_SectionSecurity.ss_sectionID = Community_Sections.section_id
WHERE
ss_communityID = @communityID
AND ss_roleType = 0
AND ss_role='Community-Authenticated'
AND Community_Sections.section_isEnabled = 1
)
RETURN 1
/* User allowed content */
If @sectionID IN
(
SELECT
ss_sectionID
FROM
Community_SectionSecurity
join Community_Sections
on Community_SectionSecurity.ss_sectionID = Community_Sections.section_id
join Community_UsersInRoles
on Community_UsersInRoles.UserRoles_Rolename = Community_SectionSecurity.ss_role
WHERE
ss_communityID = @communityID
AND ss_roleType = 0
AND Community_UsersInRoles.UserRoles_Username = @userName
AND Community_Sections.section_isEnabled = 1
)
RETURN 1
END
RETURN 0
END