如何使用IN条件访问SQLite.swift中的FTS表 [英] How to access an FTS table in SQLite.swift using the IN condition

问题描述

我试图用SQLite.swift查询FTS表。 以前我在Android上完成了它。我想要做的事的精髓是这个：

<$
FROM t2
WHERE id IN（SELECT docid
FROM fts_table
WHERE col_text MATCH'something'）
SELECT * / code>

从 SQLite.swift文档我看到 IN 条件可以像这个：

  users.filter（[1 ，2,3,4,5] .contains（id））
 // SELECT * FROMusersWHERE（idIN（1，2，3，4，5））
  

虚拟表可以像这个：

  let wonderfulEmails = emails.match（wonder *）
 // SELECT * FROMemailsWHEREemailsMATCH'wonder *'
 
 let reply = emails .filter（subject.match（Re：*））
 // SELECT * FROMemailsWHEREsubjectMATCH'Re：*'
  

然而，我无法弄清楚如何结合这些。我真的不想要执行任意SQL （虽然现在可以使用这个答案）帮助。

更新

这是我最近的一次尝试， b
$ b

  let tableText = Table（t2）
 let id = Expression< Int64>（id）
 let someColumn =表达式< String>（someColumn）
 
 let tableFts = VirtualTable（fts_table）
 let columnDocId =表达式< Int64>（docId）
 
 
 let ftsQuery = tableFts 
 .select（columnDocId）
 .filter（tableFts.match（something））
 let tableQuery = tableText 
 .filter（（尝试db.prepare（ftsQuery））
 .contains（id））//<  -  error 
 
 for try in db.prepare（tableQ uery）{
 print（row [someColumn]）
} 
  

包含（id）引发错误：

无法转换类型的值'Expression'to expected argument type'@noescape（Row）throws - > Bool'

解决方案

我最终只是运行原始的SQL命令。这并不完全回答我在这个问题中提出的问题（因为它不使用纯SQLite.swift API），但对于那些只需要得到某些工作的人来说......它可以工作。

这是我从一些源代码中提取的一个修改后的示例。

  static func findLinesContaining（_ searchTerms：String，forSearchScope scope：SearchScope）throws  - > [TextLines] {
 
 guard let db = SQLiteDataStore.sharedInstance.TextDB else {
 throw DataAccessError.datastore_Connection_Error 
} 
 
 // TODO：convert to SQLite.swift语法而不是使用SQL字符串
 var statement：Statement？ = nil 
 switch scope {
 case .wholeCollection：
 statement = try db.run（SELECT bookId，chapterId，lineId，lineText+ 
FROM lines+ 
WHERE rowid IN（SELECT docid FROM fts_table+ 
WHERE fts_table MATCH？）;，searchTerms）
 
 case .singleBook（let chosenBookId）：
 statement =尝试db.run（SELECT bookId，chapterId，lineId，lineText+ 
FROM lines+ 
WHERE rowid IN（SELECT docid FROM fts_table+ 
where fts_table MATCH？） AND bookId =？;，[searchTerms，chosenBookId]）
} 
 
 
 var returnList：[TextLine] = [] 
 for statement in row！ {
 
 let line = TextLine（）
如果让bookId = row [0]为？ Int64，
让chapterId = row [1]为？ Int64，
让lineId = row [2]为？ Int64，
让text = row [3]为？字符串{
 
 line.bookId = Int（bookId）
 line.chapterId = Int（chapterId）
 line.lineId = Int（lineId）
 line.lineText =文本
} 
 
 returnList.append（行）
} 
 
返回returnList 
} 
  

上面的代码在重命名用于SO的变量和类之后未经测试。如果您发现错误，您有权限修改代码。

I'm trying to query an FTS table using SQLite.swift. Previously I have done it in Android. The essence of what I am trying to do is this:

SELECT *
FROM t2
WHERE id IN (SELECT docid
             FROM fts_table
             WHERE col_text MATCH 'something')

From the SQLite.swift documentation I see the IN condition can be written like this:

users.filter([1, 2, 3, 4, 5].contains(id))
// SELECT * FROM "users" WHERE ("id" IN (1, 2, 3, 4, 5))

And virtual tables can be queried like this:

let wonderfulEmails = emails.match("wonder*")
// SELECT * FROM "emails" WHERE "emails" MATCH 'wonder*'

let replies = emails.filter(subject.match("Re:*"))
// SELECT * FROM "emails" WHERE "subject" MATCH 'Re:*'

However, I can't figure out how to combine these. I don't really want to have to execute arbitrary SQL (although this is now working thanks to help from this answer).

Update

Here is my most recent try that is not working:

let tableText = Table("t2")
let id = Expression<Int64>("id")
let someColumn = Expression<String>("someColumn")

let tableFts = VirtualTable("fts_table")
let columnDocId = Expression<Int64>("docId")


let ftsQuery = tableFts
    .select(columnDocId)
    .filter(tableFts.match("something"))
let tableQuery = tableText
    .filter((try db.prepare(ftsQuery))
        .contains(id))                       // <-- error

for row in try db.prepare(tableQuery) {
    print(row[someColumn])
}

where the line with contains(id) throws the error:

Cannot convert value of type 'Expression' to expected argument type '@noescape (Row) throws -> Bool'

解决方案

I ended up just running the raw SQL command. This doesn't exactly answer what I was asking in the question (since it doesn't use the pure SQLite.swift API) but for those who just need to get something working, ... it works.

Here is a modified example that I pulled from some of my source code.

static func findLinesContaining(_ searchTerms: String, forSearchScope scope: SearchScope) throws -> [TextLines] {

    guard let db = SQLiteDataStore.sharedInstance.TextDB else {
        throw DataAccessError.datastore_Connection_Error
    }

    // TODO: convert to SQLite.swift syntax rather than using a SQL string
    var statement: Statement? = nil
    switch scope {
    case .wholeCollection:
        statement = try db.run("SELECT bookId, chapterId, lineId, lineText" +
            " FROM lines" +
            " WHERE rowid IN (SELECT docid FROM fts_table" +
            " WHERE fts_table MATCH ?);", searchTerms)

    case .singleBook(let chosenBookId):
        statement = try db.run("SELECT bookId, chapterId, lineId, lineText" +
            " FROM lines" +
            " WHERE rowid IN (SELECT docid FROM fts_table" +
            " WHERE fts_table MATCH ?) AND bookId = ?;", [searchTerms, chosenBookId])
    }


    var returnList: [TextLine] = []
    for row in statement! {

        let line = TextLine()
        if let bookId = row[0] as? Int64,
            let chapterId = row[1] as? Int64,
            let lineId = row[2] as? Int64,
            let text = row[3] as? String {

            line.bookId = Int(bookId)
            line.chapterId = Int(chapterId)
            line.lineId = Int(lineId)
            line.lineText = text
        }

        returnList.append(line)
    }

    return returnList
}

The above code is untested after renaming variables and classes for use on SO. You have my permission to edit the code if you find a bug.

这篇关于如何使用IN条件访问SQLite.swift中的FTS表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！