FTS usage issue "unable to use function MATCH in the requested context"

[avaan]

I have created a table along with FTS table as below,

try db.create(table: "dcontacts") { t in
    // An integer primary key auto-generates unique IDs
    t.column("id", .integer).primaryKey()
    t.column("number", .text).unique(onConflict: .replace)
    t.column("ucontact_id", .integer)
    t.column("display_name", .text)
}

// create Full-Text Search with FTS4
try db.create(virtualTable: "dcontacts_ft", using: FTS4()) { t in
    t.synchronize(withTable: "dcontacts")
    t.content = ""
    t.compress = "zip"
    t.uncompress = "unzip"
    t.tokenizer = .porter
    t.column("display_name")
}

As per above anything inserted into table "dcontacts" will be synced to "dcontacts_ft" table.

when I try to execute below code

let pattern = FTS3Pattern(matchingAllTokensIn: "h")
try! contactDbQueue.inTransaction { db in
    contacts =  DContacts.filter(Column("display_name").match(pattern)).fetchAll(db)
    return .commit
}

I am getting below error "SQLite error 1 with statement SELECT * FROM "DContacts" WHERE ("display_name" MATCH ?) arguments ["h"]: unable to use function MATCH in the requested context"

I don't know what I am doing wrong here. Please help me out.

[groue]

Hello @avaan,

I may have to update the External Content Full-Text Tables documentation chapter so that other users aren't confused.

Basically, the SQLite MATCH full-text operator only works with columns that belong to a full-text virtual table. You get the "unable to use function MATCH in the requested context" error because you match on the dcontacts table, when SQLite can only match on the dcontacts_ft table.

Since you intend to load all columns from dcontacts, you need a query that involves both tables: dcontacts for the full contact info, and dcontacts_ft for the full text matching. SQLite lets you query several tables at the same time with joined SQL queries:

SELECT dcontacts.*
FROM dcontacts
JOIN dcontacts_ft ON
     dcontacts_ft.rowid = dcontacts.rowid AND
     dcontacts_ft.display_name MATCH ?

GRDB's Query Interface does not generate JOIN queries for you. This feature may come one day, but today you'll have to use raw SQL:

let pattern = FTS3Pattern(matchingAllTokensIn: "h")
try! contactDbQueue.inDatabase { db in
    let sql = "SELECT dcontacts.* " +
              "FROM dcontacts " +
              "JOIN dcontacts_ft ON " +
              "dcontacts_ft.rowid = dcontacts.rowid AND " +
              "dcontacts_ft.display_name MATCH ?"
    contacts = DContacts.fetchAll(db, sql, arguments: [pattern])
}

Tell me if this works.

Oh, a final word: remember that SQLite full-text performs on tokens which basically are words: the h pattern will match contacts that have the h word in their full name: "David H. Parker", but not contacts that have the h letter in their full name: "Henry Johnson". I hope that you know that.

[groue]

Another advice. Your table creation has troubles:

try db.create(virtualTable: "dcontacts_ft", using: FTS4()) { t in
    t.synchronize(withTable: "dcontacts")
    t.content = ""
    t.compress = "zip"
    t.uncompress = "unzip"
    t.tokenizer = .porter
    t.column("display_name")
}

The content property documentation says:

When you want the full-text table to be synchronized with the content of an external table, prefer the synchronize(withTable:) method.

Setting this property invalidates any synchronization previously established with the synchronize(withTable:) method.

So don't set content to "" after calling synchronize(withTable:):

try db.create(virtualTable: "dcontacts_ft", using: FTS4()) { t in
    t.synchronize(withTable: "dcontacts")
    t.compress = "zip"
    t.uncompress = "unzip"
    t.tokenizer = .porter
    t.column("display_name")
}

As for the compress and uncompress options, the SQLite documentation says:

The compress and uncompress options allow FTS4 content to be stored in the database in a compressed form. Both options should be set to the name of an SQL scalar function registered using sqlite3_create_function() that accepts a single argument.

I'm not sure that you have registered a zip and unzip functions. So if I were you, I'd simply write:

try db.create(virtualTable: "dcontacts_ft", using: FTS4()) { t in
    t.synchronize(withTable: "dcontacts")
    t.tokenizer = .porter
    t.column("display_name")
}

If your database is too big, you'll maybe have to consider compression. But I advise you to start with a simple setup first.

[avaan]

Thanks for your valuable inputs. I will try this and will let you know the output.

Meanwhile one more question, do I need to create "dcontacts_ft" every time my app launches?
Bcoz I can see no data in my virtual table after application relaunch.

[avaan]

Its working like a charm. Thanks for your valuable suggestions..

[groue]

Meanwhile one more question, do I need to create "dcontacts_ft" every time my app launches? Bcoz I can see no data in my virtual table after application relaunch.

No, a full text table is just like other tables: once created, and populated, it remains. Normally, an application sets up its database only once. GRDB recommends using migrations for that, because migrations can also handle schema changes as your application evolves. See DemoApps/GRDBDemoiOS/Database.swift for a sample code.

Its working like a charm

Great! Happy GRDB!

[dineshflock]

Meanwhile one more question, do I need to create "dcontacts_ft" every time my app launches? Bcoz I can see no data in my virtual table after application relaunch.

No, a full text table is just like other tables: once created, and populated, it remains. Normally, an application sets up its database only once. GRDB recommends using migrations for that, because migrations can also handle schema changes as your application evolves. See DemoApps/GRDBDemoiOS/Database.swift for a sample code.

Its working like a charm

Great! Happy GRDB!

@groue Are you trying to point that FTS tables are migratable/alterable.

[groue]

@dineshflock. More or less, but don't draw too many conclusions from my sentences. A conversation in an issue is not reference documentation. The definitive reference is SQLite itself. For example, as all SQLite virtual tables, full text tables can not be altered, and there's nothing GRDB can do about it.

If you have a more precise question, please ask. But remember that GRDB is just a thin layer over SQLite: most questions about GRDB are actually questions about SQLite, and StackOverflow is likely to be a better place for asking.

[dineshflock]

@groue I got it. Thanks.