RSQLite
RSQLite copied to clipboard
Published
20 hours ago •
r-dbi
r-dbi
Date type support in DBWriteTable
Thank you very much for adding Date support in RSQLite! RSQLite support Date typed columns when tables are created using SQL. However when tables are created from dataframes using dbWriteTable Date types are converted to numbers. My expectation is that when creating a SQLite table from a dataframe with dbWriteTable that Date types would be preserved and not converted to numbers.
library(RSQLite)
#> Warning: package 'RSQLite' was built under R version 4.0.4
conn <- dbConnect(RSQLite::SQLite(), extended_types = TRUE)
myTable <- data.frame(someDate = as.Date(c("2000-01-01", "2000-02-03")),
someNumber = c(1,2))
# dbWriteTable will convert Dates to numeric type
dbWriteTable(conn, "my_table", myTable)
dbReadTable(conn, "my_table")
#> someDate someNumber
#> 1 10957 1
#> 2 10990 2
class(dbReadTable(conn, "my_table")$someDate)
#> [1] "numeric"
# Date types are created when using SQL to create a table
dbExecute(conn, "CREATE TABLE my_table_2 (some_date DATE);")
#> [1] 0
dbExecute(conn, "INSERT INTO my_table_2 values ('2000-01-01'), ('2000-02-03');")
#> [1] 2
dbGetQuery(conn, 'SELECT * from my_table_2')
#> some_date
#> 1 2000-01-01
#> 2 2000-02-03
class(dbGetQuery(conn, 'SELECT * from my_table_2')$some_date)
#> [1] "Date"
# Dates do not get converted when using dplyr::copy_to
dplyr::copy_to(conn, myTable, "my_table_3")
dplyr::tbl(conn, "my_table_3")
#> # Source: table<my_table_3> [?? x 2]
#> # Database: sqlite 3.34.1 []
#> someDate someNumber
#> <dbl> <dbl>
#> 1 10957 1
#> 2 10990 2
dbDisconnect(conn)
Created on 2021-03-16 by the reprex package (v0.3.0)
Session info
devtools::session_info()
#> Error in get(genname, envir = envir) : object 'testthat_print' not found
#> - Session info ---------------------------------------------------------------
#> setting value
#> version R version 4.0.2 (2020-06-22)
#> os Windows 10 x64
#> system x86_64, mingw32
#> ui RTerm
#> language (EN)
#> collate English_United States.1252
#> ctype English_United States.1252
#> tz America/New_York
#> date 2021-03-16
#>
#> - Packages -------------------------------------------------------------------
#> package * version date lib source
#> assertthat 0.2.1 2019-03-21 [1] CRAN (R 4.0.2)
#> backports 1.1.10 2020-09-15 [1] CRAN (R 4.0.3)
#> bit 4.0.4 2020-08-04 [1] CRAN (R 4.0.2)
#> bit64 4.0.5 2020-08-30 [1] CRAN (R 4.0.3)
#> blob 1.2.1 2020-01-20 [1] CRAN (R 4.0.2)
#> cachem 1.0.1 2021-01-21 [1] CRAN (R 4.0.3)
#> callr 3.5.1 2020-10-13 [1] CRAN (R 4.0.3)
#> cli 2.1.0 2020-10-12 [1] CRAN (R 4.0.3)
#> crayon 1.3.4 2017-09-16 [1] CRAN (R 4.0.2)
#> DBI 1.1.0 2019-12-15 [1] CRAN (R 4.0.2)
#> dbplyr 1.4.4 2020-05-27 [1] CRAN (R 4.0.2)
#> desc 1.2.0 2018-05-01 [1] CRAN (R 4.0.2)
#> devtools 2.3.2 2020-09-18 [1] CRAN (R 4.0.3)
#> digest 0.6.27 2020-10-24 [1] CRAN (R 4.0.3)
#> dplyr 1.0.2 2020-08-18 [1] CRAN (R 4.0.2)
#> ellipsis 0.3.1 2020-05-15 [1] CRAN (R 4.0.2)
#> evaluate 0.14 2019-05-28 [1] CRAN (R 4.0.2)
#> fansi 0.4.1 2020-01-08 [1] CRAN (R 4.0.2)
#> fastmap 1.0.1 2019-10-08 [1] CRAN (R 4.0.2)
#> fs 1.5.0 2020-07-31 [1] CRAN (R 4.0.2)
#> generics 0.0.2 2018-11-29 [1] CRAN (R 4.0.2)
#> glue 1.4.2 2020-08-27 [1] CRAN (R 4.0.3)
#> highr 0.8 2019-03-20 [1] CRAN (R 4.0.2)
#> hms 0.5.3 2020-01-08 [1] CRAN (R 4.0.2)
#> htmltools 0.5.0 2020-06-16 [1] CRAN (R 4.0.2)
#> knitr 1.30 2020-09-22 [1] CRAN (R 4.0.3)
#> lifecycle 0.2.0 2020-03-06 [1] CRAN (R 4.0.2)
#> magrittr 1.5 2014-11-22 [1] CRAN (R 4.0.2)
#> memoise 1.1.0 2017-04-21 [1] CRAN (R 4.0.2)
#> pillar 1.5.1 2021-03-05 [1] CRAN (R 4.0.4)
#> pkgbuild 1.1.0 2020-07-13 [1] CRAN (R 4.0.2)
#> pkgconfig 2.0.3 2019-09-22 [1] CRAN (R 4.0.2)
#> pkgload 1.1.0 2020-05-29 [1] CRAN (R 4.0.2)
#> prettyunits 1.1.1 2020-01-24 [1] CRAN (R 4.0.2)
#> processx 3.4.4 2020-09-03 [1] CRAN (R 4.0.3)
#> ps 1.4.0 2020-10-07 [1] CRAN (R 4.0.3)
#> purrr 0.3.4 2020-04-17 [1] CRAN (R 4.0.2)
#> R6 2.5.0 2020-10-28 [1] CRAN (R 4.0.2)
#> Rcpp 1.0.5 2020-07-06 [1] CRAN (R 4.0.2)
#> remotes 2.2.0 2020-07-21 [1] CRAN (R 4.0.2)
#> rlang 0.4.10 2020-12-30 [1] CRAN (R 4.0.3)
#> rmarkdown 2.5 2020-10-21 [1] CRAN (R 4.0.3)
#> rprojroot 1.3-2 2018-01-03 [1] CRAN (R 4.0.2)
#> RSQLite * 2.2.4 2021-03-12 [1] CRAN (R 4.0.4)
#> rstudioapi 0.11 2020-02-07 [1] CRAN (R 4.0.2)
#> sessioninfo 1.1.1 2018-11-05 [1] CRAN (R 4.0.2)
#> stringi 1.5.3 2020-09-09 [1] CRAN (R 4.0.2)
#> stringr 1.4.0 2019-02-10 [1] CRAN (R 4.0.2)
#> testthat 2.3.2 2020-03-02 [1] CRAN (R 4.0.2)
#> tibble 3.1.0 2021-02-25 [1] CRAN (R 4.0.4)
#> tidyselect 1.1.0 2020-05-11 [1] CRAN (R 4.0.2)
#> usethis 1.6.3 2020-09-17 [1] CRAN (R 4.0.3)
#> utf8 1.1.4 2018-05-24 [1] CRAN (R 4.0.2)
#> vctrs 0.3.4 2020-08-29 [1] CRAN (R 4.0.3)
#> withr 2.3.0 2020-09-22 [1] CRAN (R 4.0.3)
#> xfun 0.18 2020-09-29 [1] CRAN (R 4.0.3)
#> yaml 2.2.1 2020-02-01 [1] CRAN (R 4.0.2)
#>
#> [1] C:/Users/Adam Black/Documents/R/win-library/4.0
#> [2] C:/Program Files/R/R-4.0.2/library
Thanks for the hint. I think I have a solution.
Add an extended_types argument to and some if statements to dbDataType().
#' @rdname SQLiteConnection-class
#' @export
setMethod("dbDataType", "SQLiteConnection", function(dbObj, obj, ...) {
dbDataType(SQLite(), obj, extended_types = dbObj@extended_types, ...)
})
#' @rdname SQLiteDriver-class
#' @export
setMethod("dbDataType", "SQLiteDriver", function(dbObj, obj, extended_types, ...) {
if (is.factor(obj)) {
return("TEXT")
}
if (is.data.frame(obj)) {
return(callNextMethod(dbObj, obj))
}
if (is.integer64(obj)) {
return("INTEGER")
}
if (extended_types && methods::is(obj, "Date")) {
return("DATE")
}
if (extended_types && methods::is(obj, "POSIXct")) {
return("TIMESTAMP")
}
if (extended_types && methods::is(obj, "hms")) {
return("TIME")
}
switch(typeof(obj),
integer = "INTEGER",
double = "REAL",
character = "TEXT",
logical = "INTEGER",
list = "BLOB",
raw = "TEXT",
stop("Unsupported type", call. = FALSE)
)
})
Test the change.
library(RSQLite)
library(testthat)
#> Error in get(genname, envir = envir) : object 'testthat_print' not found
test_that("Dates and times work with dbWriteTable", {
conn <- dbConnect(RSQLite::SQLite(), ":memory:", extended_types = TRUE)
dates_and_times <- data.frame(some_date = as.Date(c("2000-01-01", "2000-02-03")),
some_datetime = .POSIXct(1:2, tz = "UTC"),
some_time = hms::hms(1:2))
dbWriteTable(conn, "dates_and_times", dates_and_times, overwrite = T)
to_match <- dbReadTable(conn, "dates_and_times")
expect_identical(lapply(to_match, class),
lapply(dates_and_times, class))
dbDisconnect(conn)
conn <- dbConnect(RSQLite::SQLite(), ":memory:", extended_types = FALSE)
dates_and_times <- data.frame(some_date = as.Date(c("2000-01-01", "2000-02-03")),
some_datetime = .POSIXct(1:2, tz = "UTC"),
some_time = hms::hms(1:2))
dbWriteTable(conn, "dates_and_times", dates_and_times, overwrite = T)
to_match <- dbReadTable(conn, "dates_and_times")
coerced_types <- list(some_date = "numeric", some_datetime = "integer", some_time = "integer")
expect_identical(lapply(to_match, class), coerced_types)
dbDisconnect(conn)
})
Created on 2021-04-01 by the reprex package (v0.3.0)
Let me know what you think.
I like it, thanks! We can borrow code from RPostgres or RMariaDB too if necessary.
We could also run the extended_types check as a second DBItest run, with (hopefully) fewer tests skipped, and different settings for tweaks() . This would give a broader coverage of the feature set.
Great! I'll work on a pulll request. Is there a development branch I should work off of or create a new branch from main?
I opened a PR with the changes and a simple test. I was hoping that adding the DBItest date/time checks would be as simple as
DBItest::make_context(
SQLite(),
list(dbname = tempfile("DBItest", fileext = ".sqlite"),
extended_types = TRUE),
tweaks = DBItest::tweaks(
constructor_relax_args = TRUE,
placeholder_pattern = c("?", "$1", "$name", ":name"),
date_cast = function(x) paste0("'", x, "'"),
time_cast = function(x) paste0("'", x, "'"),
timestamp_cast = function(x) paste0("'", x, "'"),
logical_return = function(x) as.integer(x),
date_typed = T,
time_typed = T,
timestamp_typed = T
),
name = "RSQLite",
default_skip = default_skip
)
but no such luck.
library(DBItest)
#> Warning: package 'DBItest' was built under R version 4.0.4
#> Error in get(genname, envir = envir) : object 'testthat_print' not found
tweaks <- tweaks(
constructor_relax_args = TRUE,
placeholder_pattern = c("?", "$1", "$name", ":name"),
date_cast = function(x) paste0("'", x, "'"),
time_cast = function(x) paste0("'", x, "'"),
timestamp_cast = function(x) paste0("'", x, "'"),
logical_return = function(x) as.integer(x),
date_typed = TRUE,
time_typed = FALSE,
timestamp_typed = FALSE
)
default_skip <- c("roundtrip_timestamp")
drv <- dblog::dblog(RSQLite::SQLite())
#> drv1 <- new("SQLiteDriver")
invisible(make_context(
new(
"DBIConnector",
.drv = drv,
.conn_args = list(dbname = tempfile("DBItest", fileext = ".sqlite"),
extended_types = TRUE)
),
tweaks = tweaks,
default_skip = default_skip
))
testthat::with_reporter(
c("location", "stop"),
DBItest::test_some("data_date_typed")
)
#> Start test: DBItest: Result: data_date_typed
#> spec-result-roundtrip.R#133:1 [success]
#> conn1 <- dbConnect(drv1, dbname = "C:\\Users\\ADAMBL~1\\AppData\\Local\\Temp\\RtmpoRH4cY\\DBItest3e5c41ac415b.sqlite",
#> extended_types = TRUE)
#> dbGetQuery(conn1, "SELECT '2015-01-01' as a, '2015-01-02' as b, '2015-01-03' as c, '2015-01-04' as d, '2015-01-05' as e, '2015-01-06' as f, '2015-01-07' as g, '2015-01-08' as h, '2015-01-09' as i, '2015-01-10' as j, '2015-01-11' as k, '2015-01-12' as l")
#> ## a b c d e f g
#> ## 1 2015-01-01 2015-01-02 2015-01-03 2015-01-04 2015-01-05 2015-01-06 2015-01-07
#> ## h i j k l
#> ## 1 2015-01-08 2015-01-09 2015-01-10 2015-01-11 2015-01-12
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [success]
#> dbGetQuery(conn1, "SELECT NULL as a, NULL as b, NULL as c, NULL as d, NULL as e, NULL as f, NULL as g, NULL as h, NULL as i, NULL as j, NULL as k, NULL as l, 1 as id UNION SELECT '2015-01-01' as a, '2015-01-02' as b, '2015-01-03' as c, '2015-01-04' as d, '2015-01-05' as e, '2015-01-06' as f, '2015-01-07' as g, '2015-01-08' as h, '2015-01-09' as i, '2015-01-10' as j, '2015-01-11' as k, '2015-01-12' as l, 2 as id")
#> ## a b c d e f g
#> ## 1 <NA> <NA> <NA> <NA> <NA> <NA> <NA>
#> ## 2 2015-01-01 2015-01-02 2015-01-03 2015-01-04 2015-01-05 2015-01-06 2015-01-07
#> ## h i j k l id
#> ## 1 <NA> <NA> <NA> <NA> <NA> 1
#> ## 2 2015-01-08 2015-01-09 2015-01-10 2015-01-11 2015-01-12 2
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [success]
#> dbGetQuery(conn1, "SELECT '2015-01-01' as a, '2015-01-02' as b, '2015-01-03' as c, '2015-01-04' as d, '2015-01-05' as e, '2015-01-06' as f, '2015-01-07' as g, '2015-01-08' as h, '2015-01-09' as i, '2015-01-10' as j, '2015-01-11' as k, '2015-01-12' as l, 1 as id UNION SELECT NULL as a, NULL as b, NULL as c, NULL as d, NULL as e, NULL as f, NULL as g, NULL as h, NULL as i, NULL as j, NULL as k, NULL as l, 2 as id")
#> ## a b c d e f g
#> ## 1 <NA> <NA> <NA> <NA> <NA> <NA> <NA>
#> ## 2 2015-01-01 2015-01-02 2015-01-03 2015-01-04 2015-01-05 2015-01-06 2015-01-07
#> ## h i j k l id
#> ## 1 <NA> <NA> <NA> <NA> <NA> 2
#> ## 2 2015-01-08 2015-01-09 2015-01-10 2015-01-11 2015-01-12 1
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [failure]
#> spec-result-roundtrip.R#137:1 [success]
#> spec-result-roundtrip.R#137:1 [success]
#> dbDisconnect(conn1)
#> spec-result-roundtrip.R#137:1 [success]
#> End test: DBItest: Result: data_date_typed
#>
#> Test failed: 'DBItest: Result: data_date_typed'
#> * spec-result-roundtrip.R:137: rows[1L, 1L] not identical to structure(16436, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 2L] not identical to structure(16437, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 3L] not identical to structure(16438, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 4L] not identical to structure(16439, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 5L] not identical to structure(16440, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 6L] not identical to structure(16441, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 7L] not identical to structure(16442, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 8L] not identical to structure(16443, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 9L] not identical to structure(16444, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 10L] not identical to structure(16445, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 11L] not identical to structure(16446, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 12L] not identical to structure(16447, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 1L] not identical to structure(16436, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 2L] not identical to structure(16437, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 3L] not identical to structure(16438, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 4L] not identical to structure(16439, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 5L] not identical to structure(16440, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 6L] not identical to structure(16441, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 7L] not identical to structure(16442, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 8L] not identical to structure(16443, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 9L] not identical to structure(16444, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 10L] not identical to structure(16445, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 11L] not identical to structure(16446, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 12L] not identical to structure(16447, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 1L] not identical to structure(16436, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 2L] not identical to structure(16437, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 3L] not identical to structure(16438, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 4L] not identical to structure(16439, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 5L] not identical to structure(16440, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 6L] not identical to structure(16441, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 7L] not identical to structure(16442, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 8L] not identical to structure(16443, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 9L] not identical to structure(16444, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 10L] not identical to structure(16445, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 11L] not identical to structure(16446, class = "Date").
#> Types not compatible: character is not double
#> * spec-result-roundtrip.R:137: rows[1L, 12L] not identical to structure(16447, class = "Date").
#> Types not compatible: character is not double
Created on 2021-04-02 by the reprex package (v0.3.0)
I'm a little lost about how to debug this even with the guidance here.
Thanks. The DBItest invocation looks good. We can skip the failing tests for now (by adding them to default_skip), and sort them out later.
I agree it's still difficult to interpret these failures. In this case, results are expected to come back as a date but don't; I don't see how to fix this for this particular test, so we may well end up leaving the skip.
I think I've made some progress and learned a little more about the DBItest package. I think that we want to use the appropriate cast functions in tweaks.
date_cast = function(x) paste0("DATE('", x, "')")
The problem is that this SQL does not return a date
library(RSQLite)
con <- dbConnect(SQLite(), extended_types = TRUE)
tibble::tibble(dbGetQuery(con, "SELECT DATE('now') as a_date"))
#> # A tibble: 1 x 1
#> a_date
#> <chr>
#> 1 2021-04-08
Created on 2021-04-08 by the reprex package (v0.3.0)
Clearly DATE('now') is producing a date in SQLite but that is not being converted to a date in R. I looked over the dbSendQuery method in RSQLite but I don't see where R types are being assinged. Maybe this is happening in C. Any ideas?
Sorry, missed that.
The type assignment might happen around here: https://github.com/r-dbi/RSQLite/pull/333/files#diff-1c0fcdd98b173ac09576b9c81e81e7c14985dfeb1f11dd1e99f67b6575b728a5. It looks like we have a branch (if you scroll a bit to the top). In fact, we get "Date" values when querying a table with a corresponding column:
library(RSQLite)
con <- dbConnect(SQLite(), extended_types = TRUE)
dbWriteTable(con, "test", data.frame(date = Sys.Date()))
str(dbReadTable(con, "test"))
#> 'data.frame': 1 obs. of 1 variable:
#> $ date: Date, format: "2021-08-21"
Created on 2021-08-21 by the reprex package (v2.0.1)
On the other hand, the SQL DATE() function returns a string, that's what we see from the query. We might be able to relax the tests in DBItest a bit to account for this. Would you like to share the work you've done so far?