sps Search for FullText

转载 2007年09月28日 00:36:00

This post is a description of how to use the Microsoft.Office.Server.Search.Query.FullTextSqlQuery class for searching with the MOSS 2007 search engine using the Enterprise Search SQL syntax. I will at the end of this posting explain how to execute queries for optimal results rankings. 

But let me first establish the basics of using the FullTestSqlQuery class. You will only need to employ it for one of the following reasons:

  • You need to support Wildcard searches.
  • You need to search with date ranges.
  • You need to search properties with different operators (CONTAINS, =, >=, <=, <, >, LIKE).
  • You need to search properties for NULL values.
  • You need to use the NEAR operator.
  • You need nested Boolean queries.
  • You like the most flexible (or complicated ;-) ) solution.

Simple keyword and property queries are easier done with the Microsoft.Office.Server.Search.Query.KeywordQuery class. I will, however, not cover this class here.

Ok, let us get started. I have outlined the format of the SQL Syntax accepted by the MOSS 2007 search engine below. After that I will show how to talk to the search engine via the official .NET API.

SQL Syntax
A SQL query is a string that must adhere to the following structure:
SELECT <columns>
FROM <content source>
WHERE <conditions>
ORDER BY <columns>

SQL Syntax Examples

  1. Finds relevant results containing the keyword SharePoint.

    SELECT WorkId,Path,Title,Write,Author,HitHighlightedSummary,
    HitHighlightedProperties,CollapsingStatus
    FROM Scope()
    WHERE FREETEXT(defaultproperties, 'SharePoint')
    ORDER BY Rank Desc
  2. Finds relevant results containing at least one of the keywords SharePoint and Search.
    SELECT WorkId,Path,Title,Write,Author,...
    FROM Scope()
    WHERE FREETEXT(defaultproperties, 'SharePoint Search')
    ORDER BY Rank Desc
  3. Finds relevant results containing both the keywords SharePoint and Search.
    SELECT WorkId,Path,Title,Write,Author,...
    FROM Scope()
    WHERE FREETEXT(defaultproperties, '+SharePoint +Search')
    ORDER BY Rank Desc
  4. Finds relevant results containing the exact phrase SharePoint Search.
    SELECT WorkId,Path,Title,Write,Author,...
    FROM Scope()
    WHERE FREETEXT(defaultproperties, ' "SharePoint Search" ')
    ORDER BY Rank Desc
  5. Finds relevant results containing both the keywords SharePoint and Search but not the keyword WSS
    SELECT WorkId,Path,Title,Write,Author,...
    FROM Scope()
    WHERE FREETEXT(defaultproperties, '+SharePoint +Search -WSS')
    ORDER BY Rank Desc
  6. Finds relevant SharePoint results authored by persons named John.
    SELECT WorkId,Path,Title,Write,Author,...
    FROM Scope()
    WHERE FREETEXT(defaultproperties, 'SharePoint') AND CONTAINS(Author,' "John" ')
    ORDER BY Rank Desc
  7. Finds relevant SharePoint results modified within the last 30 days.
    SELECT WorkId,Path,Title,Write,Author,...
    FROM Scope()
    WHERE FREETEXT(defaultproperties, 'SharePoint') AND Write<=DATEADD(DAY,30,GETGMTDATE())
    ORDER BY Rank Desc

 .NET API
The following code should give you some inspiration how to execute a SQL query against the MOSS 2007 search engine.

// Execute Query
ResultTableCollection results = null;
using (FullTextSqlQuery query = new FullTextSqlQuery(ServerContext.Current))
{
query.StartRow = 0;
query.RowLimit = 10;
query.HighlightedSentenceCount = 3;
query.EnableStemming = true;
query.TrimDuplicates = true;
query.Culture = CultureInfo.CurrentCulture;

query.KeywordInclusion = KeywordInclusion.AnyKeyword;
query.SiteContext = new Uri("http://yourserver/sites/asite");




if (SPSecurity.AuthenticationMode != AuthenticationMode.Windows)

query.AuthenticationType = QueryAuthenticationType.PluggableAuthenticatedQuery;
else

    query.AuthenticationType = QueryAuthenticationType.NtAuthenticatedQuery;
query.QueryText = "SELECT ... FROM Scope() WHERE ... ORDER BY ...";
results = query.Execute();
}

// Parse results and create XML output
StringBuilder buffer = new StringBuilder(10240);
ResultTable relevantResults = results[ResultType.RelevantResults];
using (XmlTextWriter writer = new XmlTextWriter(new StringWriter(buffer)))
{
writer.Formatting = Formatting.Indented;
writer.WriteStartElement("Results");
writer.WriteAttributeString("hits", relevantResults.TotalRows.ToString());
while (relevantResults.Read())
{
writer.WriteStartElement("Result");
for (int i=0; i<relevantResults.FieldCount; i++)
{
writer.WriteStartElement(relevantResults.GetName(i));
object val = relevantResults.GetValue(i);
if (val != null)
writer.WriteString(val.ToString());
else
writer.WriteString("null");
writer.WriteEndElement();
}     
writer.WriteEndElement();
}
  writer.WriteEndElement();
  string xml = buffer.ToString();
...






}

The using statement around the usage scope of the FullTextSqlQuery class is very important to remember as you will otherwise get an OutOfMemoryException after running a good number of queries. This is so because the search engine is a COM server that the .NET API maintains a handle to.

A closer look at the FREETEXT predicate
Take a look at following two keyword searches using the FREETEXT predicate (rest of SQL string omitted for simplicity):

FREETEXT(defaultproperties, '+sharepoint +search')
FREETEXT(defaultproperties, 'sharepoint') AND FREETEXT(defaultproperties, 'search')

Both queries are valid and yield the same results buth with different ranking. Which one yields the best results ranking? Answer: The first one! It is recommended that you only use one FREETEXT predicate in a search query. Results ranking will otherwise not be optimal.

The defaultproperties keywords references the default set of properties to include in the ranking algorithm. This is also recommended for optimal ranking of results. You can alternatively use the WITH predicate to define your own set of properties if you are not happy with the default one. This is helpful if you need to promote results containing the keywords on your own custom properties. Note: The standard MOSS 2007 search center simply uses the defaultproperties.

The FREETEXT predicate can also be configured for implicit AND search or implicit OR search. This means that a query like:

FREETEXT(defaultproperties, 'sharepoint search')

can respectively find results containing both the keywords sharepoint and search or find results containing at least one of the keywords. Use the the KeywordInclusion property on the FulltextSqlQuery object to control this behavior. See the code example earlier in this posting.

Conclusion
I have just described the key concepts of using the FulltextSqlQuery class. I have not described all of its features nor have I described the SQL Syntax in full. I refer you to the MOSS 2007 SDK for a complete reference.

Stay stuned for more postings on the inner workings of the MOSS 2007 search engine! 

相关文章推荐

Hello, MYSQL FULLTEXT SEARCH

1. MySQL 4.x版本及以上版本提供了全文检索支持,但是表的存储引擎类型必须为MyISAM,以下是建表SQL,注意其中显式设置了存储引擎类型 CREATE TABLE articles...
  • sudoers
  • sudoers
  • 2012年08月25日 17:23
  • 373

HANA system replication for SPS12

  • 2016年10月14日 09:50
  • 669KB
  • 下载

R-CNN之前的准备:Selective Search for Object Recognition

在阅读这篇博客之前,推荐先阅读关于讲解Efficient Graph-Based Image Segmentation 的这篇关于图像分割的博客,因为这个图像分割算法是selective search...
  • xbcReal
  • xbcReal
  • 2017年07月24日 12:07
  • 323

【LeetCode】34. Search for a Range

题目描述Given an array of integers sorted in ascending order, find the starting and ending position of a...

hibernate-search-5.5.4 api docset for Dash

  • 2017年02月16日 21:26
  • 4.53MB
  • 下载

2016 UESTC Training for Search Algorithm & String A - Xiper的奇妙历险(1) 八皇后问题、dfs

八皇后问题 这里是九个皇后 逐行放置,则皇后肯定不会横向攻击,只需要检查纵向和两个斜向攻击即可 其中用 cur - C[cur] == j - C[j] || cur + C[cur] == j + ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:sps Search for FullText
举报原因:
原因补充:

(最多只允许输入30个字)