官方版本的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