SQLite追踪用于日志记录

Question

我正在尝试使用跟踪来调试我的代码中的一些SQLite查询，以便将发生的所有事情记录到控制台中，但似乎几乎没有关于它的信息 - 在谷歌搜索“sqlite3_trace_v2 swift”只返回两页结果，除了上面的链接外，都没有帮助。使用以下代码，我至少能够运行跟踪回调函数：

func traceSQL (database: OpaquePointer?) {
    var pointer: OpaquePointer?
    func traceCallback (mask: UInt32, pointer: UnsafeMutableRawPointer?, query: UnsafeMutableRawPointer?, result: UnsafeMutableRawPointer?) -> Int32 {
        print("SQLite Trace:")

        if let query = query?.load(as: UnsafePointer<Int8>.self) {
            print(String(cString: query))
        } else {
            print("Could not load query.")
        }

        if let result = result?.load(as: UnsafePointer<Int8>.self) {
            print(String(cString: result))
        } else {
            print("Could not load result.")
        }

        return 0
    }
    sqlite3_trace_v2(database, 15, traceCallback as @convention(c) (UInt32, UnsafeMutableRawPointer?, UnsafeMutableRawPointer?, UnsafeMutableRawPointer?) -> Int32, &pointer)
}

但我不知道该如何处理函数的输出 - 目前，它仅打印出一串无法读取的字符，而我的先前尝试甚至都未能成功。我怀疑问题的一部分是因为我不知道如何在Swift中使用UnsafeMutableRawPointer（还有其他缺乏相关信息的内容）。

简而言之：我该如何记录SQLite的跟踪结果？

- John Montgomery

Answer 1

你代码的主要错误是在回调函数中对传递的原始指针进行了取消引用，而不是重新解释（转换）它们。此外，这些指针的含义对于不同的事件是不同的。

下面是一个示例，演示如何跟踪各种事件以及如何使用文字闭包将原始指针转换为“正确”的类型。解释参数p和x的含义的注释来自SQL Trace Event Codes。

let traceMask = SQLITE_TRACE_STMT|SQLITE_TRACE_PROFILE|SQLITE_TRACE_ROW|SQLITE_TRACE_CLOSE

sqlite3_trace_v2(database, UInt32(traceMask), { (reason, context, p, x) -> Int32 in
    switch Int32(reason) {
    case SQLITE_TRACE_STMT:
        // The P argument is a pointer to the prepared statement.
        // The X argument is a pointer to a string which is the unexpanded SQL text 
        guard
            let pStmt = OpaquePointer(p),
            let cSql = x?.assumingMemoryBound(to: CChar.self)
        else {
            return 0
        }

        let sql = String(cString: cSql) // The unexpanded SQL text
        let expandedSql = String(cString: sqlite3_expanded_sql(pStmt)) // The expanded SQL text
        print("SQLITE_TRACE_STMT:", expandedSql)

    case SQLITE_TRACE_PROFILE:
        // The P argument is a pointer to the prepared statement and the X argument points
        // to a 64-bit integer which is the estimated of the number of nanosecond that the
        // prepared statement took to run.
        guard
            let pStmt = OpaquePointer(p),
            let duration = x?.load(as: UInt64.self)
        else {
            return 0
        }

        let milliSeconds = Double(duration)/Double(NSEC_PER_MSEC)
        let sql = String(cString: sqlite3_sql(pStmt)) // The unexpanded SQL text
        print("SQLITE_TRACE_PROFILE:", milliSeconds, "ms for statement:", sql)

    case SQLITE_TRACE_ROW:
        // The P argument is a pointer to the prepared statement and the X argument is unused.
        guard
            let pStmt = OpaquePointer(p)
        else {
            return 0
        }

        print("SQLITE_TRACE_ROW")

    case SQLITE_TRACE_CLOSE:
        // The P argument is a pointer to the database connection object and the X argument is unused.
        guard
            let database = OpaquePointer(p)
        else {
            return 0
        }

        print("SQLITE_TRACE_CLOSE")

    default:
        break
    }
    return 0
}, nil)

当然，您可以将跟踪模式限制为您感兴趣的事件，例如：

let traceMask = SQLITE_TRACE_STMT

仅跟踪准备好的语句。

- Martin R