官方版本的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
![None.gif](/Images/OutliningIndicators/None.gif)
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
![None.gif](/Images/OutliningIndicators/None.gif)
SET
QUOTED_IDENTIFIER
OFF
GO
SET
ANSI_NULLS
ON
GO
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
dbo.Community_SearchAddSearchKey
(
@communityID
int
,
@sectionID
int
,
@contentPageID
int
,
@searchKey
nvarchar
(
100
)
)
AS
DELETE
Community_SearchKeys
![None.gif](/Images/OutliningIndicators/None.gif)
GO
SET
QUOTED_IDENTIFIER
OFF
GO
SET
ANSI_NULLS
ON
GO
![None.gif](/Images/OutliningIndicators/None.gif)
SET
QUOTED_IDENTIFIER
ON
GO
SET
ANSI_NULLS
ON
GO
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
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
![None.gif](/Images/OutliningIndicators/None.gif)
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
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/* 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
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/* 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
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/* 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
![None.gif](/Images/OutliningIndicators/None.gif)
END
![None.gif](/Images/OutliningIndicators/None.gif)
RETURN
0
END