Earlier, we have described an
approach to call Windows Search from SQL Server 2008. But it has turned out that
our problem is more complicated...
All has started from the initial task:
-
to allow free text search in a store of huge xml files;
-
files should be compressed, so these are *.xml.gz;
-
search results should be addressable to a fragment within xml.
Later we shall describe how we have solved this task, and now it's enough to say that
we have implemented a
Protocol Handler for Windows Search named '.xml-gz:'. This way original file stored
say at 'file:///c:/store/data.xml-gz' is seen as a container by the Windows Search:
-
.xml-gz:///file:c:/store/data.xml-gz/id1.xml
-
.xml-gz:///file:c:/store/data.xml-gz/id2.xml
-
...
This way search xml should be like this:
select System.ItemUrl from SystemIndex where scope='.xml-gz:' and contains(...)
Everything has worked during test: we have succeeded to issue Windows Search selects
from SQL Server and join results with other sql queries.
But later on when we considered a runtime environment we have seen that our design
won't work. The reason is simple. Windows Search will work on a computer different
from those where SQL Servers run. So, the search query should look like this:
select System.ItemUrl from Computer.SystemIndex where scope='.xml-gz:' and contains(...)
Here we have realized the limitation of current (Windows Search 4) implementation:
remote search works for shared folders only, thus query may only look like:
select System.ItemUrl from Computer.SystemIndex where scope='file://Computer/share/'
and contains(...)
Notice that search restricts the scope to a file protocol, this way remoter search
will never return our results. The only way to search in our scope is to perform a
local search.
We have considered following approaches to resolve the issue.
The simplest one would be to access Search protocol on remote computer using a connection
string: "Provider=Search.CollatorDSO;Data Source=Computer" and use local
queries. This does not work, as provider simply disregards Data Source parameter.
The other try was to use MS Remote OLEDB provider. We tried hard to configure it but
it always returns obscure error, and more than that it's deprecated (Microsoft claims
to remove it in future).
So, we decided to forward request manually:
-
SQL Server calls a web service (through a CLR function);
-
Web service queries Windows Search locally.
Here we considered WCF
Data Services and a custom web service.
The advantage of WCF Data Services is that it's a technology that has ambitions of
a standard but it's rather complex task to create implementation that will talk with
Windows Search SQL dialect, so we have decided to build a primitive http handler to
get query parameter. That's trivial and also has a virtue of simple implementation
and high streamability.
So, that's our http handler (WindowsSearch.ashx):
<%@ WebHandler Language="C#" Class="WindowsSearch" %>
using System;
using System.Web;
using System.Xml;
using System.Text;
using System.Data.OleDb;
/// <summary>
/// A Windows Search request handler.
/// </summary>
public class WindowsSearch: IHttpHandler
{
/// <summary>
/// Handles the request.
/// </summary>
/// <param name="context">A request context.</param>
public void ProcessRequest(HttpContext context)
{
var request = context.Request;
var query = request.Params["query"];
var response = context.Response;
response.ContentType = "text/xml";
response.ContentEncoding = Encoding.UTF8;
var writer = XmlWriter.Create(response.Output);
writer.WriteStartDocument();
writer.WriteStartElement("resultset");
if (!string.IsNullOrEmpty(query))
{
using(var connection = new OleDbConnection(provider))
using(var command = new OleDbCommand(query, connection))
{
connection.Open();
using(var reader = command.ExecuteReader())
{
string[] names = null;
while(reader.Read())
{
if (names == null)
{
names
= new string[reader.FieldCount];
for
(int i = 0; i < names.Length; ++i)
{
names[i] = XmlConvert.EncodeLocalName(reader.GetName(i));
}
}
writer.WriteStartElement("row");
for(int i = 0;
i < names.Length; ++i)
{
writer.WriteElementString(
names[i],
Convert.ToString(reader[i]));
}
writer.WriteEndElement();
}
}
}
}
writer.WriteEndElement();
writer.WriteEndDocument();
writer.Flush();
}
/// <summary>
/// Indicates that a handler is reusable.
/// </summary>
public bool IsReusable { get { return true; } }
/// <summary>
/// A connection string.
/// </summary>
private const string provider =
"Provider=Search.CollatorDSO;" +
"Extended Properties='Application=Windows';" +
"OLE DB Services=-4";
}
And a SQL CLR function looks like this:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.IO;
using System.Xml;
/// <summary>
/// A user defined function.
/// </summary>
public class UserDefinedFunctions
{
/// <summary>
/// A Windows Search returning result as xml strings.
/// </summary>
/// <param name="url">A search url.</param>
/// <param name="userName">A user name for a web request.</param>
/// <param name="password">A password for a web request.</param>
/// <param name="query">A Windows Search SQL.</param>
/// <returns>A result rows.</returns>
[SqlFunction(
IsDeterministic = false,
Name = "WindowsSearch",
FillRowMethodName = "FillWindowsSearch",
TableDefinition = "value nvarchar(max)")]
public static IEnumerable Search(
string url,
string userName,
string password,
string query)
{
return SearchEnumerator(url, userName, password, query);
}
/// <summary>
/// A filler of WindowsSearch function.
/// </summary>
/// <param name="value">A value returned from the enumerator.</param>
/// <param name="row">An output value.</param>
public static void FillWindowsSearch(object value, out string row)
{
row = (string)value;
}
/// <summary>
/// Gets a search row enumerator.
/// </summary>
/// <param name="url">A search url.</param>
/// <param name="userName">A user name for a web request.</param>
/// <param name="password">A password for a web request.</param>
/// <param name="query">A Windows Search SQL.</param>
/// <returns>A result rows.</returns>
private static IEnumerable<string> SearchEnumerator(
string url,
string userName,
string password,
string query)
{
if (string.IsNullOrEmpty(url))
{
throw new ArgumentException("url");
}
if (string.IsNullOrEmpty(query))
{
throw new ArgumentException("query");
}
var requestUrl = url + "?query=" + Uri.EscapeDataString(query);
var request = WebRequest.Create(requestUrl);
request.Credentials = string.IsNullOrEmpty(userName) ?
CredentialCache.DefaultCredentials :
new NetworkCredential(userName, password);
using(var response = request.GetResponse())
using(var stream = response.GetResponseStream())
using(var reader = XmlReader.Create(stream))
{
bool read = true;
while(!read || reader.Read())
{
if ((reader.Depth == 1) && reader.IsStartElement())
{
// Note that ReadInnerXml()
advances the reader similar to Read().
yield return reader.ReadInnerXml();
read = false;
}
else
{
read = true;
}
}
}
}
}
And, finally, when you call this service from SQL Server you write query like this:
with search as
(
select
cast(value as xml) value
from
dbo.WindowsSearch
(
N'http://machine/WindowsSearchService/WindowsSearch.ashx',
null,
null,
N'
select
"System.ItemUrl"
from
SystemIndex
where
scope=''.xml-gz:'' and contains(''...'')'
)
)
select
value.value('/System.ItemUrl[1]', 'nvarchar(max)')
from
search
Design is not trivial but it works somehow.
After dealing with all these problems some questions remain unanswered:
-
Why SQL Server does not allow to query Windows Search directly?
-
Why Windows Search OLEDB provider does not support "Data Source" parameter?
-
Why Windows Search does not support custom protocols during remote search?
-
Why SQL Server does not support web request/web services natively?
