Search code examples
sqlitexamarinfts4

Using CreateTable<T>(CreateFlags.FullTextSearch4) doesn't auto increment PK

I'm using Xamarin on Visual Studio 2015 with this SQLite Plugin: https://github.com/praeclarum/sqlite-net

Here's my algorithm and explanation of the problem:

public class MyModel
{
     [PrimaryKey, AutoIncrement]
     public int _id { get; set; }
     public string bla { get; set; }
     public string ble { get; set; }

    public MyModel()
    {

    }
}

Then:

conn.CreateTable<MyModel>(CreateFlags.FullTextSearch4);

var item = new MyModel();
var insertCount = conn.Insert(item);

The code above returns 1 for the insertCount and item._id is always updated as it should. But when I later query the table, the _id is always 0 for all rows. For instance:

var tokens = mySearchBar.Text;
string query = "SELECT * FROM MyModel WHERE MyModel MATCH '" + tokens + "'";
List<MyModel> result = conn.Query<MyModel>(query);

This seems to work but feels not right, it doesn't seem to be returning all possible result. But the main problem is that it's returning 0 for every _id. And I can check it out with:

var bli = conn.Query<MyModel>("SELECT * FROM MyModel");

All rows _id are 0. Any idea why and how can I fix it?

I have no problems if I create the table without the fts4 flag. But then I can't do the MATCH search.

Thanks

Solution

  • Full Text Search Table, mainly known as Virtual Table, are extension modules that allows users to create special tables with a built-in full-text index.

    It ignores everything but the column names (e.g. auto increment, primary key, unique, and even Integer). Everything to an FTS table is just text. This table has a privaterow called rowid. It behaves in the same way as the rowid column of an ordinary SQLite table, except that the values stored in the rowid column of an FTS table remain unchanged.

    The only way to get and id from a selected row is by doing:

    select rowid,* from thetable

    If you try to do this in a common SQLite-net query, you will not get the rowid.

    SQLite table scheme:

    enter image description here

    Possible solutions:

    1 - In your case I don't know if you really need the Id. But you could remove it it, if it doesn't matter for the query (yes i know you posted in the question).

    2 - You just can add a new column and place there some information that identifies the row, when inserting it.

    3 - SQLite-net don't allow you to do a query and parse the result using a IDataReader. So you could use another library (android and ios) that allow to parse the query result to get the rowid.

    4 - Don't use FTS table. Use a normal table and query something like this:

    var query = conn.Table<MyModel>().Where(v => v.bla.Contains("mystring"));