Confused about using FTS5 with GRDB

[EdIzaguirre]

Hi all,

Had a question regarding the use of FTS5 and GRDB. So I set up my dbQueue and instantiate it within the App Delegate as shown in the examples. Now, I am trying to do a simple queue on my dbQueue to get all Groups within the database, and rank them according to relevancy using the bm25 algorithm. Below is the code in the relevant ViewController where I am trying to do this. Unfortunately, I am getting an error on the "let results" line that says: "No exact matches in call to static method 'matching'." I suppose this is because my Group struct does not extend the proper protocol. I extended the Group using: 'extension Group: FetchableRecord, PersistableRecord {}' Is this not enough? Am I totally on the wrong track here? Also I don't see anything in the documentation regarding the bm25 ranking algorithm, is this supported? Any help would be appreciated. Thank you.

`func searchBar(_ searchBar: UISearchBar, textDidChange searchText: String) {

    let filteredText = String(searchText.unicodeScalars.filter {return CharacterSet.alphanumerics.contains($0)})
    //FIXME: Order by bm25/rank
    do {
        try dbQueue.read { db in
            let pattern = FTS5Pattern(matchingAnyTokenIn: searchText)
            let results = try Group.matching(pattern).fetchAll(db)

            for group in results{
                filteredGroups.append(Group(row: group))
            }
            tableView.reloadData()
        }
    }
    catch{
        print("Error executing full-text search", error.localizedDescription)
    }
}`

[groue]

Hello @EdIzaguirre,

The matching method you are looking for is defined on the TableRecord protocol: https://github.com/groue/GRDB.swift/blob/v5.2.0/GRDB/QueryInterface/FTS5+QueryInterface.swift#L40

PersistableRecord happens to derive from TableRecord, so you should be good to go, and I do not understand why you are getting a compiler error.

About the ranking, you can try:

let pattern = ...
let groups = try Group // fetches [Group]
    .matching(pattern)
    .order(sql: "bm25(group)")
    .fetchAll(db)

Due to holiday season, you may have to wait until somebody can help you more: keep on your investigations, because you're on the right track.

[EdIzaguirre]

Thanks for the support.

I still have a few questions related to GRDB that I am trying to tease out:

1. Just to make sure I understand the general flow of creating and writing to a database, the dbQueue is just a way to access the sqlite database. So when referring to dbQueue, I should just treat it as an sqlite database, correct? Also, it appears that to get access to a table in the database, I need to follow the following format (to write to the table, for example):

try dbQueue.write { db in ... }

Here db stands for the database, I am assuming? But now that I am in the database, how do I access a specific table? For example, after creating the dbQueue connection to the database, I created a 'groupsTable' table. How do I write to this specific table, after it has been created? Would I replace 'db' with 'groups'?

2. In sqlite, I had created a variable for the table specifically, so that I was able to access it easily. Is this possible to do in GRDB? For example, could I instantiate a 'groupsTable' by writing the following: private var groupsTable: PersistableRecord! and then somehow link it to the table in the dbQueue database?

3. It appears that when inserting a new row into a table, I have to use the following syntax: try dbQueue.write { db in try group.insert(db) } where group is an instance of my custom struct Group, and I'm assuming db must be the database? But if I do assume it is the database, then how do I know I am writing to the 'groupsTable' record? With my current code, I now get an "SQLite Error: no such table: group." Which is correct, because I called it 'groupsTable', but I appear to have no method of letting the program know which table I want to write to. Also, if Group is a PersistableRecord, then will this automatically insert the Group into the table, or do I need to write custom encodable/decodable methods to aid with the writing?

Sorry about all of the questions, I think GRDB is a fascinating tool, and would love to be able to understand the bigger picture.

[groue]

1. Just to make sure I understand the general flow of creating and writing to a database, the dbQueue is just a way to access the sqlite database. So when referring to dbQueue, I should just treat it as an sqlite database, correct? Also, it appears that to get access to a table in the database, I need to follow the following format (to write to the table, for example):

try dbQueue.write { db in ... }

Here db stands for the database, I am assuming?

Applications access the database through DatabaseQueue and DatabasePool. Those types provide "database access methods" such as read and write that provide thread-safety, and concurrency guarantees. Inside the database access methods, the db stands for the "actual" SQLite connection, yes.

This db conventional name could have been named connection instead. But GRDB draws inspiration from FMDB, so db it is.

But now that I am in the database, how do I access a specific table? For example, after creating the dbQueue connection to the database, I created a 'groupsTable' table. How do I write to this specific table, after it has been created? Would I replace 'db' with 'groups'?

No. db can access the whole database, and the groups table if you want it to. Maybe you can check again the Fetch Queries, and The Query Interface documentation chapters.

1. In sqlite, I had created a variable for the table specifically, so that I was able to access it easily. Is this possible to do in GRDB? For example, could I instantiate a 'groupsTable' by writing the following: private var groupsTable: PersistableRecord! and then somehow link it to the table in the dbQueue database?
2. It appears that when inserting a new row into a table, I have to use the following syntax: try dbQueue.write { db in try group.insert(db) } where group is an instance of my custom struct Group, and I'm assuming db must be the database? But if I do assume it is the database, then how do I know I am writing to the 'groupsTable' record? With my current code, I now get an "SQLite Error: no such table: group." Which is correct, because I called it 'groupsTable', but I appear to have no method of letting the program know which table I want to write to. Also, if Group is a PersistableRecord, then will this automatically insert the Group into the table, or do I need to write custom encodable/decodable methods to aid with the writing?

GRDB provides raw SQL access, and, for convenience, Swift apis that help defining "record types" that can access the database through the "query interface".

Record types implement the active record pattern, which means that record types have static and instance methods that access the database:

try dbQueue.write { db in
    try group.insert(db)
}
let groups = try dbQueue.read { db in
    try Group.fetchAll(db)
}

One record type such as Group defines its relation to the database through static methods and properties such as Group.databaseTableName, which returns the name of the database table that contains groups. Instance methods, on the other hand, provide particular information for a particular record. For example group.encode(to:) provides the values for the columns of one particular row in the groups table. Those static and instance methods can be defined explicitly, or use the default implementation.

See Records.