实现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  

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