SQLite trace for logging

Question

SQLite trace for logging

I am trying to debug some SQLite queries in my code using a traceback to just log into the console whatever happens, but there seems to be almost no information about it - Google search for "sqlite3_trace_v2 swift" only returns two pages of results, none of which was not helpful other than the link above. Using the following code, I was able to bring it to the point where it at least triggers a trace callback:

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)
}

but I can't figure out what to do with the function exit - currently it just prints out a string of unreadable characters, and my previous attempts didn't even work it out. I suspect that at least part of the problem is that I don't really know how to work with UnsafeMutableRawPointer

in Swift (something else that seems to be missing from the information available).

tl; dr: How do I log SQLite trace results?

+3

sqlite swift

John Montgomery Apr 24 17 at 16:43

source to share

1 answer

Martin R · Accepted Answer · 2017-04-24T18:38:08+0000

The main mistake in your code is that you are eliminating the raw pointers being passed to the callback instead of being re-interpreted (cast). Also, the meaning of these pointers is different for different events.

Here's an example of how to track various events and how to convert the original pointers to the "correct" types using literal closure as a callback. The comments explaining the meaning p

and x

argument are taken from the 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)

Of course, you can restrict the trace mode to events you are interested in, for example

let traceMask = SQLITE_TRACE_STMT

to track only trained operators.

SQLite trace for logging

More articles: