Questions about writing a FTS engine

I am looking at writing a FTS search engine using the SQL Server FTS.  I have been through the blog post and have a few questions.

In IList Search(string query, params string[] scopes);

1) scopes is the type names.  How do I get from type names to table names?

2) I need to run queries like:

select *
from FreeTextTable(tablename, *, query)
order by RANK desc

e.g.

select *
from FreeTextTable(AccData, *, 'Sean Cross Prevshort')
order by RANK desc

returning

KEY    RANK
175671    27
175673    23
175646    15
175657    7

I am considering grabbing the connection string from the context and using ado.net to run the query.  Is there any better way?

Other than that, it doesn't look too hard.

Sean

1. There's no 100% reliable way for external code to get from type to table names at the moment.  If you know that you are NOT using pluralised names or INamingStrategy, then you can do this by adapting Marko's code -- basically using TableAttribute if present and the type name if not.  If you want to support pluralisation, the algorithm we use is Andrew Peters' Inflector.net.  If you want to support INamingStrategy, you can get the strategy instance from the LightSpeedContext.

If you want to hook into the LightSpeed engine so that your code will be guaranteed to remain consistent even if we change our mapping (or just because this is starting to sound a bit complicated!), let us know: I think we'd need to open up a bit of the internal API to handle this case, which we've been reluctant to do so far, but we're open to discussion.

2. Yes, you will need to use ADO.NET.  You can use the LightSpeedContext.DataProviderObjectFactory.CreateCommand to create a command, and UnitOfWork.PrepareCommand to piggyback your custom query onto an existing UOW's connection (and transaction, if relevant).  Otherwise, if you want to use a separate connection, you will need to get the connection string from the LightSpeedContext as you say (note DataProviderObjectFactory.CreateConnection does *not* pre-populate the connection string).

1. For my purposes, I can just use the type name as I (currently) only need to search one table.  However if anyone else wants the code, something more robust would be useful.

How about adding a translation method to the UOW?

2 Thanks

Sean

Almost there.  I am now (probably) running into issues creating the SearchResult.

Given the following:

Entity being searched: "CmsModels.Entities.AccClaim"

  [Table("AccData", IdColumnName="ClaimID")]
  public partial class AccClaim : Entity<int>

I am getting back the correct data

eg       

        srKey    "175646"    string
        rank    23    int

However I don't think I am getting the right values into the search results

I am doing

var sr = new SearchResult(srKey, "CmsModels.Entities.AccClaim", "ClaimID", rank);

However this gives the error

TestCase 'CmsTests.Integration.Model.AccRepostitoryIntegration.SearchClaims'
failed: System.Collections.Generic.KeyNotFoundException : The given key was not present in the dictionary.
    at System.ThrowHelper.ThrowKeyNotFoundException()
    at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
    at(IList , Boolean )
    at (Query , IList )
    at Mindscape.LightSpeed.UnitOfWork.Find(Query query, IList results

Any ideas where I am going wrong?

The full code is (test only):

        public IList<SearchResult> Search(string query, params string[] scopes)
        {
            IList<SearchResult> result = new List<SearchResult>();

            string tableName = "";
            string fieldName = "Id";
            Type t = Type.GetType(scopes[0]);
            object[] tableAttrib = t.GetCustomAttributes(typeof(TableAttribute), false);
            if (tableAttrib.Length > 0)
            {
                tableName = ((TableAttribute)tableAttrib[0]).Name;
                fieldName = ((TableAttribute)tableAttrib[0]).IdColumnName;
            }
            else
                tableName = t.Name.ToUpper();


            IDbCommand command = _Context.DataProviderObjectFactory.CreateCommand();
            _Context.CreateUnitOfWork().PrepareCommand(command);

            command.CommandText = string.Format("select * from FreeTextTable([{0}], *, '{1}') order by RANK desc", tableName, query);
            IDataReader reader = command.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    string srKey = reader[0].ToString();
                    int rank = reader.GetInt32(1);
                    var sr = new SearchResult(srKey, scopes[0], srKey, rank);
                    result.Add(sr);
                }
            }
            finally
            {
                reader.Close();
            }

            return result;
        }

Hi Sean,

Thank you for your post with all the details listed - it has helped a lot.

I'm guessing that you aren't using the Add() methods on the SearchEngine you have written because SQL Server is managing the index for you - correct?

Long story short, the key you are trying to use does not match what LightSpeed expects the key to be. The key is a uniquely generated identifier that includes the type name and the identity of the entity.

To help you understand what needs to be passed into the new SearchResult() it would be handy to take note of what the Add() method is passing in regardless as this will help improve your understanding of what LightSpeed is expecting. If you log what is going through the Add() method, you will see that we provide an IndexKey type that has three properties:

• Key
• Scope
• EntityId

These values are needed to be passed in when you create a SearchResult item as well (they are the first three parameters of the SearchResult constructor). 

It has been a few years since I have really explored SQL Server FTS so I'm not ideal for providing advice on how to ensure that you store these details AND get them back when you perform the search so that you can construct the search results. Let me know if you have the answer to this as it would be helpful for others as well :-)

The exception itself is occuring when LightSpeed tries to sort the results based on a search. It creates a dictionary that contains the Key value as the (you guessed it) key for the dictionary. Because the key you are providing does not match any of the entities returned it states that it is not in the dictionary. Internally LightSpeed manage's ID's by holding the type name and the actual PK value so we are comparing against a concatenation of those in a format that you can see by watching what we pass to the Add() method of your search engine.

That's all quite nitty gritty, please let me know if you have any questions or if I can clarify any points here!

John-Daniel Trask

Got it!  I might do a blog post coz it's a bit fiddly.  Also you could update your documentation a bit :).

Given my entity: "CmsModels.Entities.AccClaim" and a result:

KEY          RANK
175671    27

The correct values for SearchResult construtor are:

key = "[AccClaim]175671"
scope= "CmsModels.Entities.AccClaim"
entityId="175671"
score=27

Excellent, thank you for the update :-)

I will review the documentation as this has helped better understand where the pain points of building your own search provider lie! I think a couple of helper methods might save others from similar challenges - I'll add those to our backlog.

If you write a blog post please drop a link here as it might be useful for me to also put a blog post covering what you have created and I would be happy to link to your post with that as well.

Glad you got it sorted Sean,

John-Daniel

This is a "works on my machine" bit of code as it doesn't handle plurallisation etc.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

using Mindscape.LightSpeed.Search;
using Mindscape.LightSpeed;
using System.Data.SqlClient;

namespace CmsModels.Lightspeed
{
    public class SqlServerSearchEngine : ISearchEngine
    {
        #region ISearchEngine Members

        private LightSpeedContext _Context;
        public Mindscape.LightSpeed.LightSpeedContext Context
        {
            get
            {
                return _Context;
            }
            set
            {
                _Context = value;
            }
        }

        private void SearchScope(IList<SearchResult> result, string query, string scope)
        {
            string tableName = "";
            Type t = Type.GetType(scope);
            object[] tableAttrib = t.GetCustomAttributes(typeof(TableAttribute), false);
            if (tableAttrib.Length > 0)
            {
                tableName = ((TableAttribute)tableAttrib[0]).Name;
            }
            else
                tableName = t.Name.ToUpper();


            IDbCommand command = _Context.DataProviderObjectFactory.CreateCommand();
            _Context.CreateUnitOfWork().PrepareCommand(command);

            command.CommandText = string.Format("select * from FreeTextTable([{0}], *, @query) order by RANK desc", tableName);
            ((SqlCommand)command).Parameters.AddWithValue("@query", query);

            IDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    string entityId = reader[0].ToString();
                    int rank = reader.GetInt32(1);
                    string srKey = string.Format("[{0}]{1}", t.Name, entityId);
                    var sr = new SearchResult(srKey, scope, entityId, rank);
                    result.Add(sr);
                }
            }
            finally
            {
                reader.Close();
            }
        }

        public IList<SearchResult> Search(string query, params string[] scopes)
        {           
            IList<SearchResult> result = new List<SearchResult>();

            foreach (string scope in scopes)
            {
                SearchScope(result, query, scope);
            }

            return result;
        }

        // not used for Sql Server
        public void Add(IndexKey indexKey, string data) { }
        public void BeginBulkAdd(){ }
        public void Clear(){ }
        public void EndBulkAdd(){ }
        public void Optimize(){ }
        public void Remove(IndexKey indexKey){ }
        public void Update(IndexKey indexKey, string data){ }

        #endregion
    }
}

Thank you for posting that Sean, much appreciated :-) 

Cheers,

John-Daniel Trask

Blog post is at http://sourceitsoftware.blogspot.com/2010/01/net-using-sql-server-full-text.html