Using SQLite FTS3 Full Text Search capabilities

Hello,

I had an app made using Lightspeed, which connects to an SQLite database. Everything is working just fine. However, I now need to use FTS3 full text search capabilities which are built-in, but I cannot find a way to use them. I'm supposed to use the MATCH keyword instead of LIKE, as described in the following link: http://dotnetperls.com/sqlite-fts3

I'm able to make queries that use FTS3 using an SQLite manager, but not using Lightspeed. I must be overlooking something, so any help would be great!

Thank in advance.

paulo

The only FTS solution we support out of the box is Lucene.NET, but there are two approaches you can take to use a custom solution:

1. Implement your own search broker.  See http://www.mindscape.co.nz/blog/index.php/2009/02/25/lightspeed-writing-a-custom-search-engine/ for information about how to do this.  I don't know anything about FTS3, so I don't know if this will be feasible for you.

2. Bypass the LightSpeed query builder and write custom SQL to use the MATCH keyword, then use IUnitOfWork.FindBySql to execute that custom SQL and bring back entities.  (You'll need 3.0 or a recent 2.x nightly build to use FindBySql.)  Obviously, if you do this you're kinda on your own in terms of getting the SQL query exactly right, parameterising to prevent SQL injection, etc., and it won't be portable -- but it does provide maximum flexibility and native integration.

Hope this helps -- let us know if you need any further info!

Hi Ivan!

I will try the second approach. I wasn't aware of FindBySql. It might be just what I need!

Thanks for your help.

paulo

I donwloaded the latest nightly build and tried this:

IUnitOfWork uow = dbContext.CreateUnitOfWork();
IDbCommand cmd = uow.Context.DataProviderObjectFactory.CreateCommand();
cmd.CommandText = "select * from ContentsFts where Description MATCH 'article'";
cmd.CommandType = CommandType.Text;
var results = uow.FindBySql<ContentsFt>(cmd);

It returns 2749 rows, but they're fields are all null!

Doing this:

SQLiteConnection conn = new SQLiteConnection("articles.lfdb");
conn.Open();
SQLiteDataAdapter sda = new SQLiteDataAdapter("select * from ContentsFts where Description MATCH 'article'", conn);
DataSet ds = new DataSet();
sda.Fill(ds);

It returns the same 2749 rows, but with their fields correctly populated.

Is there anything else I should specify when using FindBySql ?

Thanks.

paulo

Hmm, that looks okay but it's possible this could be a column mapping issue.  I'd suggest you do a Find<ContentsFt>() and capture the generated SQL using a logger (LightSpeedContext.Logger = new TraceLogger()).  Look at the specific columns that LightSpeed is querying for e.g.

SELECT
  ContentsFt.Id,
  ContentsFt.Name,
  ContentsFt.Description
FROM ...

and replace the * in your FTS3 query by those columns (preferably in that order, though this *shouldn't* matter).  I know * should be equivalent but I'm just trying to eliminate differences between your test case and ours.

If that doesn't help, could you post the ContentsFt class definition and the ContentsFts database schema (CREATE TABLE script) and we'll see if we can figure out the problem.  Thanks!

The database schema for the table is:

CREATE VIRTUAL TABLE ContentsFTS USING FTS3(PKContents INTEGER NOT NULL PRIMARY KEY, Description TEXT, Text TEXT)

It's a virtual table, because that's what's used for FTS3.

I've tried Find<ContentsFt>(), but it gives the following error: "no such column: ContentsFTS.Id". I then tried to edit the model, because I noticed that it didn't import the Primary Key correctly. However, as soon as I change that table, it immediately messes up the associated .cs file. So, I tried to edit only the .cs file, defining the Id column and removing it from the Fields definition.

I then ran Find<ContentsFt>() and it worked, but only the Id column was returned populated. The other two were null.

This might have something to do with the fact that creating ContentsFTS as a virtual table using FTS3, creates an additional 3 tables, which contain the data for the queries. This should be transparent for the queries, because FTS3 is built-in, but for some reason, it isn't working with Lightspeed.

I think I'll have to use this out of Lightspeed as in my previous post. Anyway, if you know of anything I could try it would be great.

Thanks for your help.

Okay, this does look like a column mapping error.  The problem is that by default LightSpeed expects the identity column to be called "Id", and you need to tell it that actually, the identity column is called "PKContents" (so that it can map this column to the LightSpeed Entity<T>.Id property).

What you need to do is:

* Delete the PKContents property from the designer ContentFt entity.

* Select the entity, go to the Properties window and set Identity Column Name to PKContents.

Let us know if this improves matters.

You are of course correct that LightSpeed should have done this when you dragged the table into the designer.  I'm guessing that it may have been confused by the virtual table and been unable to locate the information about the primary key.

Hi Ivan, and thanks for your reply.

I'm sorry I did not state it clearly in my previous message, but I'd already tried that. It destroys the .cs file associated with the Lightspeed model and the project doesn't compile anymore. The .cs gets like this:

[System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
  public partial class ClientDBUnitOfWork : Mindscape.LightSpeed.UnitOfWork
  {
    /// <summary>
    /// Initializes a new instance of the ClientDBUnitOfWork class.
    /// </summary>
    [System.ComponentModel.EditorBrowsable(System.ComponentModel.EditorBrowsableState.Never)]
    public ClientDBUnitOfWork()
    {
    }

    public System.Linq.IQueryable<Content> Contents
    {
      get { return this.Query<Content>(); }
    }
   
    public System.Linq.IQueryable<ContentsFt> ContentsFts
    {
      get { return this.Query<ContentsFt>(); }
    }

(...)

Everything else is lost (fields, relationships, properties, etc.).

To overcome this, I reverted the files back to its original state and made the same modifications, this time on the .cs file directly. The Id field (PkContents) now comes populated, but the other two are null.

This might have something to do with the fact that an FTS3 virtual table works differently, and uses 3 other tables, but it should be transparent. At least, when querying the database.

Is there anything else I should try?

If applying Identity Column Name in the designer destroys the .cs file, that sounds like a bug and it would be great if you could post the .lsmodel file which causes the error (i.e. including the Identity Column Name that caused the problem) so we can investigate.  You can attach a file via the Options tab.  Thanks!

In the meantime, to change the identity column name in code, don't edit the .cs file directly (it gets overwritten whenever you change the model): instead, create a partial class for the ContentsFt class, and apply the attribute [Table(IdColumnName="PKContents")] to the partial class declaration.

Sure Ivan. I attached the file to this reply.

Please let me know if you fix it.

Thanks.

I forgot to add: Any change I try to do to the ContentsFT class using the designer, breaks the .cs file. I also noticed that by using FTS3, because it generates 4 tables for each full-text indexed table, I exceeded the 8 classes limit for the free version of Lightspeed which I'm using. Could this be the problem?

Thanks.

The file you've sent me doesn't seem to include the "broken" behaviour -- i.e. with the PkContents column removed and the Identity Column Name set.  Would you be able to supply me with the "broken" files please?  Thanks!

The 8-entity limitation could the issue, but in that case you should see errors, and wouldn't get anything generated even in the first place.  (Also, LightSpeed 2.2 didn't enforce the 8-class limitation in the designer, only at runtime.)

That said, you shouldn't need to include the XxxContent, XxxSegdir and XxxSegment entities in your model, unless you need to talk to those tables explicitly -- but I'm assuming the FTS virtual table handles them for you and your app code doesn't need them.  So you should be safe to remove them from your model.

By the way, you'll need to update the data types which we're currently marking as Object (looks like the virtual table isn't providing type metadata).  These will need to be String.

Sorry Ivan. I thought you wanted the model before it went broken. I've attached the broken one now.

I noticed the Object data type thing. Problem is, changing that was breaking the .cs file too.

Trying to solve the problem, I deleted the XxxContent, XxxSegdir and XxxSegment, and the .cs file goes crazy again.

Then I created another model from scratch, added the tables from Server Explorer, opened the .cs file and it's all messed up.

So, I'm getting to the conclusion that this must be some bug with the nightly build I downloaded yesterday, because my original model was made with the stable version I had in my system. I only downloaded the nightly build because of FindBySql. Could this really be the cause?

Aaaaaaaaahhhhhh... No, I don't think it's a bug with the nightly build, but I do suspect it's an upgrade issue.  When upgrading from RTM to a nightly build, you *MUST* uninstall the RTM version first, *but will not be prompted to do so*.  (We do put a warning on the download page, but we realise it's easy to miss.)  If you don't uninstall, the upgrade will appear to succeed but the designer will go crazy in random ways.

Could you please:

* Go to Control Panel > Add/Remove Programs (aka Programs and Features)
* Look for Mindscape LightSpeed 2.2.  You may find more than one entry.  Uninstall it (both entries if you find two).
* Check the Program Files > Mindscape > LightSpeed 2.2 directory to make sure everything has gone, and ensure that no Mindscape.* files remain in the GAC.
* Reinstall the nightly build.

Then reload your project, make a change to the model and see if it stops going crazy.

Subsequent upgrades from nightly to nightly or from nightly to 3.0 should NOT run into this problem, i.e. you should just be able to run the installer without having to manually uninstall first.

If this still doesn't help, let me know and I'll start investigating the specifics of your model.

Errrrr... sorry Ivan. My mistake! I didn't look at the notice on the nightly build download page.

I did just like you said, and the designer is now working normally.

I could now change the data types and the Id column on ContentsFT, and FindBySql is working as expected.

Happy ending! Thank you so much for your help.

paulo