Overwrite in copy_to with in_schema

[gregleleu]

Overwrite doesn't work in copy_to with a schema because db_has_table which is used to check if the table exists here...

dbplyr/R/verb-copy-to.R

Line 114 in 141554c

if (overwrite && !is_false(db_has_table(con, table))) {

... wraps dbExistsTable for DBIConnection here...

dbplyr/R/backend-.R

Line 457 in f84d7d5

db_has_table.DBIConnection <- function(con, table) dbExistsTable(con, table)

which uses DBI::Id to handle schemas. But the rest of copy_to needs to be used with dbplyr::in_schema. Should/could some type of conversion happen between the two? Am I missing a simpler version? I have seen the comments saying to just use dbWriteTable instead but I think it would be great if copy_to could handle schemas all the way.

I'm putting a reprex with RSQLite because it's easier but I use RPostgres.

In a sense similar to #239 I believe, but I don't get where that went.

library(tidyverse)
library(magrittr)
library(DBI)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

con %>% dbExecute("ATTACH ':memory:' AS foo")

mtcars_rm <- con %>% copy_to(df = mtcars, name = dbplyr::in_schema("foo", "mtcars"), temporary = FALSE)
mtcars_rm <- con %>% copy_to(df = mtcars, name = dbplyr::in_schema("foo", "mtcars"), temporary = FALSE, overwrite = TRUE) #fails

con %>% db_drop_table("mtcars")
mtcars_rm <- con %>% copy_to(df = mtcars, name =  Id(schema = "foo", table = "mtcars"), temporary = FALSE) # fails


db_has_table(con, dbplyr::in_schema("foo", "mtcars")) # used in practice to check for existence
db_has_table(con, Id(schema = "foo", table = "mtcars")) # right way to check