Think about column conversion

[krlmlr]

By @hadley:

dbFetch() and dbGetQuery() need to have optional coercion object. Should be named list, and provide common converters (e.g. times to number of seconds, dates to days, factors to strings, binary serialisation (default for list, raw should stay as is) etc, bool<->int for sqllite).

Defaults only need to work with atomic vectors.

Original issue: r-dbi/RSQLite#74

[hadley]

We should discuss before you implement — I'm now thinking readr/readxl/haven/... will use a tibble for this. The tibble will have one row per column, and colums name, type, ... Every read_xxx function will be paired with spec_xxx function that creates that tibble for you.

I think this approach is flexible enough to work in a similar way across all these package.

[krlmlr]

readr 1.1.0 still seems to use a col_spec class, and not a tibble. Looks like tidyverse/readr#237 is the relevant issue, still open.

CC @jimhester @jennybc.

[jennybc]

readxl has a notional goal of using this yet-to-be-created colspec approach and package, next time column typing gets revisited:

tidyverse/readxl#198

This also feels related to uniform reporting of problems, again with readr's approach being the likely basis (?).

[krlmlr]

Related: Choice of default representation of numeric data, r-dbi/RMariaDB@bcd4216#commitcomment-24322761, r-dbi/DBItest#133.

[krlmlr]

Do we have a plan for a portable column type specification that can be used in DBI too?

[smithjd]

I'm not sure exactly where this belongs (glad to move or repost if that would be helpful). I DBI returns different values depending on which backend is processing the query.

library(tidyverse)
library(RSQLite)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars)
tbl(con, "mtcars") %>% group_by(cyl) %>% 
  summarize(tot_wt = sum(wt), n = n()) %>% 
  collect() %>% 
  str()

Produces this output:

Classes ‘tbl_df’, ‘tbl’ and 'data.frame':	3 obs. of  3 variables:
 $ cyl   : num  4 6 8
 $ tot_wt: num  25.1 21.8 56
 $ n     : int  11 7 14

When the backend is postgreSQL, the output of the same code is different:

 Classes ‘tbl_df’, ‘tbl’ and 'data.frame':	3 obs. of  3 variables:
  $ cyl   : num  8 4 6
  $ tot_wt: num  56 25.1 21.8
  $ n     :integer64 14 11 7

That creates a problem for several bread and butter packages that don't like integer64 (e.g., ggplot).

[krlmlr]

Can you please try dbConnect(..., bigint = "integer") or any other option? https://dbi.r-dbi.org/reference/dbconnect#specification

[smithjd]

If I understand your question, this is what I get when I add the bigint options to the connection function:

> con <- RPostgres::dbConnect("PgDriver",
+   host = "localhost",
+   port = 5432,
+   user = "postgres",
+   password = "postgres",
+   dbname = "adventureworks",
+   seconds_to_test = sleep_default, connection_tab = TRUE, bigint = "integer"
+ )
Error in (function (classes, fdef, mtable)  : 
  unable to find an inherited method for function ‘dbConnect’ for signature ‘"character"’

Here is the context.

[krlmlr]

Do you need dbConnect(RPostgres::Postgres(), host = ......) ?

[smithjd]

That does it!

con <- connection_open(
  RPostgres::Postgres(),
  bigint = "integer",
  host = "localhost", 
  ...)

Thanks!

[krlmlr]

Perhaps more useful with ALTREP. Closing for now, bookmarking this issue.