Get column types by example values as a data.frame.

Example values not necessarily all from same row. Taking values from different rows is to try to work around NA not carrying type/class info in many cases.

rq_coltypes(
  db,
  table_name,
  ...,
  qualifiers = NULL,
  prefer_not_NA = FALSE,
  force_check = FALSE
)

Arguments

db	Connection handle.
table_name	character table name referring to a non-empty table.
...	force later arguments to bind by name.
qualifiers	optional named ordered vector of strings carrying additional db hierarchy terms, such as schema.
prefer_not_NA	logical, if TRUE try to find an non-NA example for all columns (FALSE just for logical columns).
force_check	logical, if TRUE perform checks regardless of check_logical_column_types option setting.

Value

single row data.frame with example values, not all values necessarily from same database row.

Examples

if(requireNamespace("DBI", quietly = TRUE) && requireNamespace("RSQLite", quietly = TRUE)) {
  db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

  # getDBOption(db, "check_logical_column_types", FALSE)
  # options(rq_connection_tests(db))
  # getDBOption(db, "check_logical_column_types", FALSE)

  d <- data.frame(w= c(NA, 1L),
                  x= c(NA, 2.0),
                  y= factor(c(NA, "x")),
                  z= c(NA, "y"),
                  want = c(1, 0),
                  stringsAsFactors=FALSE)
  d <- rq_copy_to(db, "d", d,
                   overwrite = TRUE,
                   temporary = TRUE)
  res <- d %.>%
    extend(.,
               wc %:=% ifelse(w>1, "x", "y"),
               wn %:=% ifelse(w>1, 1, 2),
               xc %:=% ifelse(x>1, "x", "y"),
               xn %:=% ifelse(x>1, 1, 2),
               yc %:=% ifelse(y=="a", "x", "y"),
               yn %:=% ifelse(y=="a", "x", "y")) %.>%
    materialize(db, .)
  resn <- DBI::dbQuoteIdentifier(db, res$table_name)
  print("full table types")
  print(str(DBI::dbGetQuery(db, paste("SELECT * FROM", resn))))
  print("single row mis-reported types")
  print(str(DBI::dbGetQuery(db, paste("SELECT * FROM", resn, "WHERE want=1"))))
  print("rq_coltypes correct synthetic example row types")
  print(str(rq_coltypes(db, res$table_name, force_check = TRUE)))
  DBI::dbDisconnect(db)
}
#> [1] "full table types"
#> 'data.frame':	2 obs. of  11 variables:
#>  $ w   : int  NA 1
#>  $ x   : num  NA 2
#>  $ y   : chr  NA "x"
#>  $ z   : chr  NA "y"
#>  $ want: num  1 0
#>  $ wc  : chr  NA "y"
#>  $ wn  : int  NA 2
#>  $ xc  : chr  NA "x"
#>  $ xn  : int  NA 1
#>  $ yc  : chr  NA "y"
#>  $ yn  : chr  NA "y"
#> NULL
#> [1] "single row mis-reported types"
#> 'data.frame':	1 obs. of  11 variables:
#>  $ w   : int NA
#>  $ x   : num NA
#>  $ y   : chr NA
#>  $ z   : chr NA
#>  $ want: num 1
#>  $ wc  : logi NA
#>  $ wn  : logi NA
#>  $ xc  : logi NA
#>  $ xn  : logi NA
#>  $ yc  : logi NA
#>  $ yn  : logi NA
#> NULL
#> [1] "rq_coltypes correct synthetic example row types"
#> 'data.frame':	1 obs. of  11 variables:
#>  $ w   : int NA
#>  $ x   : num NA
#>  $ y   : chr NA
#>  $ z   : chr NA
#>  $ want: num 1
#>  $ wc  : chr "y"
#>  $ wn  : int 2
#>  $ xc  : chr "x"
#>  $ xn  : int 1
#>  $ yc  : chr "y"
#>  $ yn  : chr "y"
#> NULL