dittodb::start_db_capturing() + dbplyr::in_schema() error

[KoderKow]

Brief description of the problem

I have an issue when I use dbplyr::in_schema(), I have confirmed this only happens after running dittodb::start_db_capturing().

The kind of database backend you are trying to test

I am using SQL Server, for simplicity, my reprex below is postgres.

# Connect to a PostgreSQL database
con <- DBI::dbConnect(RPostgres::Postgres(), dbname = "mydbname", host = "host", user = "user", password = "password")

# Create a new schema
DBI::dbExecute(con, "CREATE SCHEMA test_schema1")
# Create a table within each schema
DBI::dbExecute(con, "CREATE TABLE test_schema1.iris (id SERIAL PRIMARY KEY, species TEXT, petal_length REAL, petal_width REAL)")

# My test data
sql_statement <- "INSERT INTO [test_schema1].[iris] (sepal_length, sepal_width, petal_length, petal_width, species) VALUES (1, 2, 3, 4, 'A')"

DBI::dbExecute(con, sql_statement)

# Start DB capturing
dittodb::start_db_capturing()

# Use dplyr to query the table
tbl1 <- dplyr::tbl(con, dbplyr::in_schema("test_schema1", "iris"))

I get the following error

Here is another screen shot of showing the capture, hitting the error, stopping the capture, then having the same command run as normal.

If there is some idea of how to fix this let me know, I wouldn't mind hoping into the codebase to help fix it! :D

[jonkeane]

Thanks for reporting the issue in such great detail. It makes figuring out what's going on much easier! I've got some good news and some less good news (well, really, something that we should fix in dittodb!)

The good news is that there's not actually an issue as far as I can tell using dbplyr::in_schema(), in your example you call dittodb::start_db_capturing() after connecting to the database. But you actually need to do that in the other order (call dittodb::start_db_capturing() and then con <- dbConnect(...)). When you connect to the database dittodb sets up a few bits of information that is necessary for it to work.

So, after you've created your test data, try:

# Connect to a PostgreSQL database
# Start DB capturing
dittodb::start_db_capturing()

con <- DBI::dbConnect(RPostgres::Postgres(), dbname = "mydbname", host = "host", user = "user", password = "password")

# Use dplyr to query the table
tbl1 <- dplyr::tbl(con, dbplyr::in_schema("test_schema1", "iris"))

And that should work. I've tried it with the test data + postgres setup locally and I could reproduce the same error if I called dbConnect before starting to capture, but it is resolved if it's called after.

But this is a bit confusing because of the error messages that we get it's not clear what's actually going on. In dittodb, we could be better about detecting this and erroring with a clearer message here.

Specifically, what is missing is that the value in .dittodb_env$curr_file_path (which is used to tell where the mock directory is) gets initialized with the correct path when one calls dbConnect after starting capturing. But in the other order it's empty. This is the line that ends up empty and errors inside of dput:

dittodb/R/capture-requests.R

Line 183 in 2ba39c7

.dittodb_env$curr_file_path,

Would you be interested in trying to add a check before that line that detects if .dittodb_env$curr_file_path is empty and then errors with a clearer message? If you are interested, I should also let you know that I'm seeing som failures for RPostgreSQL on main (https://github.com/ropensci/dittodb/actions/runs/8031430709/job/21939746540?pr=184). I'm looking into those, so don't worry if you find them — I'll try to get those resolved, but they should all be unrelated to what you're dealing with here.