Enabling FTS5

[gennaios]

What did you do?

Hello. Thank you for this wonderful framework. I'm in the process of trying to migrate a macOS app from Realm to GRDB. As I have learned of SQLite and its FTS5 capabilities, I have started to use FTS5 in another app w/o issues. Now trying to integrate GRDB into project, I have some questions and an issue.

What did you expect to happen?

I am unsure of the enabling FTS5 instructions. My Podfile is as such :

target 'Biblia' do
  platform :osx, '10.14'
  use_frameworks!
  pod 'GRDB.swift', :git => 'https://github.com/groue/GRDB.swift.git', :branch => 'GRDB-4.0'

  post_install do |installer|
    installer.pods_project.targets.select { |target| target.name == "GRDB.swift" }.each do |target|
      target.build_configurations.each do |config|
        config.build_settings['OTHER_SWIFT_FLAGS'] = "$(inherited) -D SQLITE_ENABLE_FTS5"
      end
    end
  end
end

The instructions don't specify use_frameworks! but if I leave that out, I get the warning [!] Using Swift static libraries with custom module maps is currently not supported. Please build GRDB.swift as a framework or remove the custom module map. Otherwise I get this error from pod install :

[!] The `Biblia [Debug]` target overrides the `OTHER_LDFLAGS` build setting defined in `Pods/Target Support Files/Pods-Biblia/Pods-Biblia.debug.xcconfig'. This can lead to problems with the CocoaPods installation
    - Use the `$(inherited)` flag, or
    - Remove the build settings from the target.

[!] The `Biblia [Release]` target overrides the `OTHER_LDFLAGS` build setting defined in `Pods/Target Support Files/Pods-Biblia/Pods-Biblia.release.xcconfig'. This can lead to problems with the CocoaPods installation
    - Use the `$(inherited)` flag, or
    - Remove the build settings from the target.

I am not very experienced with macOS development and perhaps it is from some other setting due to using other frameworks with Carthage. I have tried to look for OTHER_LDFLAGS w/o success. Perhaps that's an unrelated issue.

What happened instead?

Perhaps the integration is not correct. Upon trying to run a FTS5 query, I get the following error :

Fatal error: 'try!' expression unexpectedly raised an error: SQLite error 1 with statement SELECT * FROM files JOIN filesindex ON filesindex.rowid = files.id WHERE files.file_extension = 'epub' AND filesindex MATCH 'test' ORDER BY files.date_created DESC: error in tokenizer constructor: file …, line 361

I have tried using sql arguments with and without specifying single quotes after MATCH.

My code looks like the following. Using an NSTableView, results are refetched upon change in a text field :

if searchString != "" {
    var terms = [String]()
    for item in searchString.components(separatedBy: " ") {
        terms.append(item + "*")
    }
    let queryString = terms.joined(separator: " ") // make all terms wildcard

    try! filesController.setRequest(sql: "SELECT * FROM files JOIN filesindex ON filesindex.rowid = files.id WHERE files.file_extension = 'epub' AND filesindex MATCH 'test' ORDER BY files.date_created DESC")
} else {
    try! filesController.setRequest(File.filter(Column("file_extension") == "epub").order(Column("date_created").desc))
}

import GRDB

struct File {
    var id: Int64?
    var path: String
    var file_name: String
    var file_extension: String
    var file_size: Int64
    var file_title: String?
    var file_authors: String?
    var file_subjects: String?
    var file_publisher: String?
    var date_created: Date
    var date_modified: Date?
    var date_accessed: Date?
    var rating: Int64
    var hash: String
}

extension File: Codable, FetchableRecord, TableRecord, MutablePersistableRecord {
    static let databaseTableName = "files"

    private enum CodingKeys: String, CodingKey, ColumnExpression {
        case id, path, file_name, file_extension, file_size, file_title, file_authors, file_subjects, file_publisher, date_created, date_modified, date_accessed, rating, hash
    }

    enum Columns: String, ColumnExpression {
        case id, path, file_name, file_extension, file_size, file_title, file_authors, file_subjects, file_publisher, date_created, date_modified, date_accessed, rating, hash
    }

    mutating func didInsert(with rowID: Int64, for column: String?) {
        id = rowID
    }

    static func orderedByTitle() -> QueryInterfaceRequest<File> {
        return File.order(CodingKeys.file_title)
    }

    static func orderedByDateCreated() -> QueryInterfaceRequest<File> {
        return File.order(CodingKeys.date_created.desc, CodingKeys.date_created)
    }
}

As I am just getting started, there remains some confusion of using Codable, FetchableRecord, TableRecord, and MutablePersistableRecord. Perhaps TableRecord isn't needed though I saw it as the only place to specify a SQLite table name (files rather than file). I am also unsure when to use enum CodingKeys or enum Columns – I have been able to get a NSTableView to load all data, though as I add other queries I'll figure it out –, and as such I have so far specified both. Perhaps that is part of the problem.

The documentation is terrific although I think there remains a bit of ambiguity, as I have hinted at above. Help would be much appreciated. Mille mercis.

Environment

GRDB flavor(s): GRDB
GRDB version: GRDB-4.0 branch
Installation method: CocoaPods
Xcode version: 10.2
Swift version: 4.2
Platform(s) running GRDB: macOS
macOS version running Xcode: 10.14

[groue]

Hello @gennaios!

I have some questions and an issue.

All right, let's check this out.

My Podfile is as such

Your Podfile looks correct. I'm sorry you stumbled on use_frameworks!. But embedding the full CocoaPods documentation is a non-goal, and using GRDB as a static library is not discouraged at all.

Otherwise I get this error from pod install ... I am not very experienced with macOS development and perhaps it is from some other setting due to using other frameworks with Carthage.

I can't afford providing Carthage support on my free time. You may have to look for other Carthage experts.

Upon trying to run a FTS5 query, I get the following error :

Fatal error: 'try!' expression unexpectedly raised an error: SQLite error 1 with statement SELECT * FROM files JOIN filesindex ON filesindex.rowid = files.id WHERE files.file_extension = 'epub' AND filesindex MATCH 'test' ORDER BY files.date_created DESC: error in tokenizer constructor: file …, line 361

As far as a Google search for "FTS5 error in tokenizer constructor" can tell, it looks like FTS5 was well enabled, but there is a tokenizer problem. I don't know how your filesindex FTS5 table is created, but it looks like its tokenizer is not available at the time your request is executed.

Do you happen to use a custom tokenizer? If so, make sure it is added every time you open your database connection (and not only when the table is created).

As I am just getting started, there remains some confusion of using Codable, FetchableRecord, TableRecord, and MutablePersistableRecord. Perhaps TableRecord isn't needed though I saw it as the only place to specify a SQLite table name (files rather than file). I am also unsure when to use enum CodingKeys or enum Columns.

I admit the documentation has several layers, which reflect several years of continuous development and evolving practices 😅. I'll try to provide some guidance in another comment.

[gennaios]

Hello @groue,

thank you so very much for the assistance. It's only in the past few months that I have become somewhat familiar with SQLite and having switched over one database to it, trying to convert my macOS app that uses the same db – an app that I haven't looked at in years –, it's been a bit as one could say chaotic trying to refamiliarize myself with it as well as learning GRDB, how to use CocoaPods, etc.

Your Podfile looks correct. I'm sorry you stumbled on use_frameworks!. But embedding the full CocoaPods documentation is a non-goal, and using GRDB as a static library is not discouraged at all.

Terrific. As I was before less familiar with CocoaPods, I wasn't sure if the integration was correct and had experimented with putting the post_install outside or inside of target without being sure it was properly integrated.

Otherwise I get this error from pod install ... I am not very experienced with macOS development and perhaps it is from some other setting due to using other frameworks with Carthage.

When using use_frameworks!, the message about OTHER_LDFLAGS perhaps comes from GRDB. The only usage I can find is the Pod itself – now that I know where to look –, OTHER_LDFLAGS = $(inherited) -l"sqlite3" -framework "Foundation" -framework "GRDB" I am not sure if that is because I am also using Carthage for other libraries. Certainly understandable that is not an issue I ask you to look into to ; use_frameworks! works for the moment and as I start work again on my app, I will very possibly later switch to CocoaPods for all libraries and figure out that error later so I can use static libraries.

Upon trying to run a FTS5 query, I get the following error :

As far as a Google search for "FTS5 error in tokenizer constructor" can tell, it looks like FTS5 was well enabled, but there is a tokenizer problem. I don't know how your filesindex FTS5 table is created, but it looks like its tokenizer is not available at the time your request is executed.

Your question helped me very much immediately find the answer. :) As over the last week I have been looking over the GRDB docs, figuring out various aspects, trying to refamiliarize myself with my app, etc., I was a bit lost and did not think carefully if the tokenizer error might be in fact the FTS5 tokenizer. Indeed I was using tokenize='porter unicode61 remove_diacritics 2', a feature introduced in 3.27.0. As I don't need it for this particular table after changing it to 1, it works. Very happy.

I admit the documentation has several layers, which reflect several years of continuous development and evolving practices 😅. I'll try to provide some guidance in another comment.

A bit of guidance would be much appreciated. I think for what is in my app so far – before using Realm –, any INSERT, UPDATE, or DELETE, I am comfortable with using raw SQL. The only thing I am unsure about is use of FetchedRecordsController and QueryInterfaceRequest. My SQLite table currently is at around 20,000 records and I am wondering about performance. It seems ok so far. From the demo app that uses QueryInterfaceRequest, it seems that changing results order reloads data from SQLite? My question is mainly about how to query either all results or a filtered subset while being ordered by whatever is the current NSSortDescriptor of my NSTableView. I just need to try it and get it working. Perhaps raw SQL is still needed as the query fetches columns from files, has a where condition for files, joins the FTS table filesindex, does a match against that, and then sorts in either asc or desc according to a NSTableView column. Unsure if I will need to dynamically create the SQL query string because of the sort order or if through some combination of GRDB's query syntax, it can be be done ; and if merely changing the sort column requires a new query. Now that I have gotten past doubt of proper integration of FTS5 through CocoaPods and the other error, I am more comfortable overall and will experiment soon. A bit of a recommended general direction would be very welcome if you have the time. Thank you.

[gennaios]

Hello,

I think I've been able to get all that I want working. It was merely the first step of being unsure of proper enabling of FTS5 that made me not able to think of the why of my intial problems. Loading of 20,000 records is very fast, as well as filtering and sorting. Eventually I'll look at better performance such as testing your example of RestrictedPlayer that doesn't load all table columns. Unsure if in that case FetchedRecordsController will still be notified of changes to Player, or File in my case. I just need to try and then I'll know. It seems FetchedRecordsController requires loading all columns but with some more experimentation, I'll figure it out. Very much enjoying this so nicely designed library. Thank you. You can close this issue.

[groue]

Hello @gennaios,

OK, I'll close this issue. It looks like you are discovering the benefits of Rubber duck debugging, where one moves towards a solution by putting words on the problem to solve.

Many words are needed, sometimes 😉 Who cares, as long as it works?

Happy GRDB!