如何实现RSQLite对nanotime（integer64包装类型）的无缝支持？

我正在研究如何在RSQLite查询中支持nanotime对象，它本质是integer64的包装类型。

以下是示例代码：

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:", bigint = "integer64")
ts <- nanotime::as.nanotime(Sys.time())

str(ts) # integer64 2023-06-04 17:30:21.669581000 

DBI::dbExecute(con, 'CREATE TABLE test (ts INTEGER);')
DBI::dbExecute(con, 'INSERT INTO test (ts) VALUES (:ts);', list('ts' = ts))

DBI::dbGetQuery(con, 'SELECT ts FROM test;') # 返回5.757609e-196
# 结果不正确：预期返回值应为1685899821669581000，类型为integer64

核心需求：如何让RSQLite识别nanotime为integer64类型并按该类型存储？我期望最后一条查询返回integer64对象而非double类型。

我了解过DBI::dbDataType()，但不确定如何使用。目前的解决方法是通过函数将nanotime转换为integer64后再传入RSQLite，但如果能在RSQLite包内部或通过配置实现这种无缝转换会更理想。

注：使用的版本为RSQLite_2.3.1和nanotime_0.3.7。

解决方案1：注册DBI类型映射方法 #

通过为nanotime类扩展dbDataType方法，告诉RSQLite将其识别为SQLite的INTEGER类型（对应R的integer64）：

# 为nanotime类型注册dbDataType方法
setMethod("dbDataType", signature(dbObj = "SQLiteConnection", obj = "nanotime"),
          function(dbObj, obj, ...) {
            return("INTEGER")
          })

解决方案2：自动处理参数绑定转换 #

为了让RSQLite在绑定参数时自动提取nanotime内部的integer64值，扩展dbBind方法：

setMethod("dbBind", signature(conn = "SQLiteConnection", statement = "SQLiteResult", params = "list"),
          function(conn, statement, params, ...) {
            params <- lapply(params, function(x) {
              if (inherits(x, "nanotime")) as.integer64(x) else x
            })
            callNextMethod(conn, statement, params, ...)
          })

解决方案3：查询结果自动转回nanotime（可选） #

如果希望查询结果直接返回nanotime对象而非integer64，可以扩展dbFetch方法：

setMethod("dbFetch", signature(res = "SQLiteResult"),
          function(res, n = -1, ...) {
            df <- callNextMethod(res, n, ...)
            df <- lapply(df, function(col) {
              if (inherits(col, "integer64")) nanotime::as.nanotime(col) else col
            })
            as.data.frame(df)
          })

测试修改后的代码 #

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:", bigint = "integer64")
# 先运行上述方法注册代码
ts <- nanotime::as.nanotime(Sys.time())

DBI::dbExecute(con, 'CREATE TABLE test (ts INTEGER);')
DBI::dbExecute(con, 'INSERT INTO test (ts) VALUES (:ts);', list('ts' = ts))

result <- DBI::dbGetQuery(con, 'SELECT ts FROM test;')
str(result) # 若使用方案1/2，返回integer64类型；若使用方案3，直接返回nanotime类型

替代简化方案（无需扩展方法） #

如果不想修改DBI方法，可保持手动转换流程，这也是你当前的解决方法，可靠且无需额外配置：

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:", bigint = "integer64")
ts <- nanotime::as.nanotime(Sys.time())

DBI::dbExecute(con, 'CREATE TABLE test (ts INTEGER);')
# 插入前转换为integer64
DBI::dbExecute(con, 'INSERT INTO test (ts) VALUES (:ts);', list('ts' = as.integer64(ts)))

# 查询后转换回nanotime
result <- nanotime::as.nanotime(DBI::dbGetQuery(con, 'SELECT ts FROM test;')$ts)
str(result)

内容的提问来源于stack exchange，提问作者Davor Josipovic