目录

SQLite3应用

lilac

2024-06-04 2024-06-04 约 678 字预计阅读 3 分钟

目录

记录日志 SQLite数据库应用在嵌入式设备上，默认不记录操作日志。可以使用sqlite3_trace_v2手动开启。 1）SQL Trace Hook

** ^A trace callback is invoked with four arguments: callback(T,C,P,X).
** ^The T argument is one of the [SQLITE_TRACE]
** constants to indicate why the callback was invoked.
** ^The C argument is a copy of the context pointer.
** The P and X arguments are pointers whose meanings depend on T.
SQLITE_API int sqlite3_trace_v2(
  sqlite3*,
  unsigned uMask,
  int(*xCallback)(unsigned,void*,void*,void*),
  void *pCtx
);

2）Hook Detail 方法注释中提到了callback(T,C,P,X)各个参数的含义，其中T为调试事件宏对应值，C为上述方法pCtx的入参，P为sqlite3_stmt*，X可以强转为sql语句。

** [[SQLITE_TRACE_STMT]] <dt>SQLITE_TRACE_STMT</dt>
** <dd>^An SQLITE_TRACE_STMT callback is invoked when a prepared statement
** first begins running and possibly at other times during the
** execution of the prepared statement, such as at the start of each
** trigger subprogram. ^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 of the prepared statement or an SQL comment
** that indicates the invocation of a trigger.  ^The callback can compute
** the same text that would have been returned by the legacy [sqlite3_trace()]
** interface by using the X argument when X begins with "--" and invoking
** [sqlite3_expanded_sql(P)] otherwise.

3）应用举例

static int trace_callback(unsigned t, void *c, void *p, void *x)
{
    auto dstFile = static_cast<std::ofstream*>(c);
    char* pSql = sqlite3_expanded_sql((sqlite3_stmt*)p);
    if (pSql) {
        dstFile << "psql statement: " << pSql << std::endl;
    }

    const char* sql = (const char *) x;
    if (sql) {
        dstFile << "sql statement: " << sql << std::endl;
    }
    return 0;
}

int main(int argc, char *argv[])
{
    sqlite3 *db;
    sqlite3_open(":memory:", &db);

    std::ofstream dstFile("./sqlite3.log", std::ios::trunc);
    // 注册调试回调
    sqlite3_trace_v2(db, SQLITE_TRACE_STMT, trace_callback, &dstFile);

    sqlite3_exec(db, "CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)", NULL, NULL, NULL);

    dstFile.close();
    sqlite3_close(db);
}

上述打印了pSql和sql两个变量，注意他们的区别。如果使用预编译的SQL语句，使用函数sqlite3_expanded_sql展开后是可以打印其变量值的。 2. 预编译预编译语句使用时会使用到?占位符，他能减少硬解析，节约CPU资源，有效提升数据库执行效率；防止SQL注入，从而提高执行安全性等优点。

int main(int argc, char *argv[])
{
    sqlite3 *db;
    sqlite3_open(":memory:", &db);

    sqlite3_stmt *stmt;
    const char *sql = "SELECT * FROM test WHERE id = ?;"; // 使用参数占位符
    sqlite3_prepare_v2(db, sql, -1, &stmt, NULL); // 编译预处理语句
    sqlite3_bind_int(stmt, 1, 1); // 绑定参数值

    int32_t rc;
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        int id = sqlite3_column_int(stmt, 0);
        const uint8_t *val = sqlite3_column_text(stmt, 1);
        printf("id: %d, value: %s\n", id, value);
    }
    sqlite3_finalize(stmt);

    sqlite3_close(db);
}