Date type support in DBWriteTable

[ablack3]

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

[ablack3]

This should have been part of issue #319

[krlmlr]

Thanks for raising this. Do we need to tweak dbDataType() ?

[ablack3]

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.

[krlmlr]

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.

[ablack3]

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?

[krlmlr]

Thanks. Can you please branch off of main?

[ablack3]

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.

[krlmlr]

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.

[krlmlr]

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.

[ablack3]

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?

[krlmlr]

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?