tests/testthat/test-import-strategies.R

In duckspatial: R Interface to 'DuckDB' Database with Spatial Extension

testthat::skip_on_cran()

test_that("Strategy 1: SQL Recreation (View-to-View)", {
  skip_if_not_installed("sf")
  conn1 <- ddbs_create_conn()
  conn2 <- ddbs_create_conn()
  on.exit({
    ddbs_stop_conn(conn1)
    ddbs_stop_conn(conn2)
  })

  # Setup: Create a view in conn1 explicitly via SQL
  # Strategy 1 ONLY works for SQL views (registered in duckdb_views),
  # NOT for arrow/replacement scans created by register()
  DBI::dbExecute(conn1, "CREATE VIEW view1 AS SELECT 1 as id, 2 as val")
  
  # Check if strategy 1 is used
  res <- duckspatial:::import_view_to_connection(conn2, conn1, dplyr::tbl(conn1, "view1"))
  
  expect_equal(res$method, "sql_recreation")
  
  # Verify data in conn2
  n <- DBI::dbGetQuery(conn2, glue::glue("SELECT count(*) as n FROM {res$name}"))$n
  expect_equal(n, 1)
})

test_that("Strategy 2: SQL Render (Query-to-View)", {
  conn1 <- ddbs_create_conn()
  conn2 <- ddbs_create_conn()
  on.exit({
    ddbs_stop_conn(conn1)
    ddbs_stop_conn(conn2)
  })
  
  # Ensure clean slate
  # Strategy 2 relies on the query referencing tables that exist in the target
  
  # 1. Create a physical table in BOTH connections
  # We use dbWriteTable to ensure it's a real table, not a view
  df <- data.frame(id = 1:5)
  DBI::dbWriteTable(conn1, "t1", df)
  DBI::dbWriteTable(conn2, "t1", df)

  # 2. Create lazy query in conn1
  q <- dplyr::tbl(conn1, "t1") |> dplyr::filter(id > 2)
  
  # 3. Import to conn2
  # Should use SQL render because "t1" exists in conn2 (same name)
  res <- duckspatial:::import_view_to_connection(conn2, conn1, q)
  
  expect_equal(res$method, "sql_render")
  
  # Verify
  n <- DBI::dbGetQuery(conn2, glue::glue("SELECT count(*)::int as n FROM {res$name}"))$n
  expect_equal(n, 3)
})

test_that("Strategy 3: ATTACH (File-based DB)", {
  conn_target <- ddbs_create_conn()
  
  # Create a file-based source DB
  db_file <- tempfile(fileext = ".duckdb")
  # Open read-write to populate
  conn_init <- DBI::dbConnect(duckdb::duckdb(), dbdir = db_file, read_only = FALSE)
  
  # Create PERMANENT table
  df <- data.frame(id = 1:10)
  DBI::dbWriteTable(conn_init, "persistent_table", df)
  
  # Close init connection to release write lock
  DBI::dbDisconnect(conn_init, shutdown = TRUE)
  
  # Now open source connection as READ_WRITE (simulating user having it open)
  # NOTE: ATTACH requires that we can access the file.
  # If we open it here as READ_ONLY, DBI::dbGetInfo(conn)$dbdir should return the path.
  # Let's verify what happens if we open it normally.
  conn_source <- DBI::dbConnect(duckdb::duckdb(), dbdir = db_file, read_only = TRUE)
  
  on.exit({
    ddbs_stop_conn(conn_target)
    DBI::dbDisconnect(conn_source, shutdown = TRUE)
    if (file.exists(db_file)) unlink(db_file)
  }, add = FALSE)

  # Verify connection info explicitly
  info <- DBI::dbGetInfo(conn_source)
  # Debug: Check what's happening
  if (is.na(info$dbname) || info$dbname == ":memory:") {
      warning("Connection is memory based! path: ", info$dbname)
  }
  
  # Import
  # Strategy 3 should work because conn_source points to a file that exists
  res <- duckspatial:::import_view_to_connection(conn_target, conn_source, dplyr::tbl(conn_source, "persistent_table"))
  
  expect_equal(res$method, "attach")
  
  # Verify
  n <- DBI::dbGetQuery(conn_target, glue::glue("SELECT count(*) as n FROM {res$name}"))$n
  expect_equal(n, 10)
  
  # Cleanup
  res$cleanup()
})

test_that("Strategy 4: Nanoarrow Streaming (Force fallback via mocks or specific conditions)", {
  skip_if_not_installed("nanoarrow")
  conn1 <- ddbs_create_conn()
  conn2 <- ddbs_create_conn()
  on.exit({
    ddbs_stop_conn(conn1)
    ddbs_stop_conn(conn2)
  })
  
  # Strategy 1, 2, 3 must fail for 4 to trigger
  # 1 fails: not a view
  # 2 fails: table doesn't exist in target
  # 3 fails: in-memory DB
  
  df <- data.frame(a = 1:10)
  duckdb::duckdb_register(conn1, "local_table", df)
  
  q <- dplyr::tbl(conn1, "local_table") |> dplyr::filter(a < 5)
  
  # This should skip 1 (not view), skip 2 (local_table not in conn2), skip 3 (memory)
  # And hit Strategy 4 (Nanoarrow)
  
  # NOTE: Depending on nanoarrow/duckdb versions, this might fail and hit Strategy 5
  # We check if we got EITHER 4 or 5, but aim for 4 if environment supports it
  
  # Suppress the "Imported via..." info messages and warnings
  suppressWarnings({
     res <- duckspatial:::import_view_to_connection(conn2, conn1, q)
  })
 
  # If nanoarrow works, method is "nanoarrow". If not, it falls back to "duckdb_register" (Strategy 5 for DF)
  expect_true(res$method %in% c("nanoarrow", "duckdb_register", "collect_and_write"))
})

test_that("Strategy 5: Collect Fallback works", {
  conn1 <- ddbs_create_conn()
  conn2 <- ddbs_create_conn()
  on.exit({
    ddbs_stop_conn(conn1)
    ddbs_stop_conn(conn2)
  })
  
  # Create a query that fails Nanoarrow (e.g., complex type not supported or mock error)
  # Easier: Just verify that if we pass a raw dataframe (not a remote source), it goes to strategy 5 logic
  # But import_view_to_connection takes a remote source.
  
  # Verification: Strategy 5 is valid if it produces correct results
  df <- data.frame(id = 1:3)
  duckdb::duckdb_register(conn1, "t5", df)
  
  suppressWarnings({
    res <- duckspatial:::import_view_to_connection(conn2, conn1, dplyr::tbl(conn1, "t5"))
  })
  n <- DBI::dbGetQuery(conn2, glue::glue("SELECT count(*) as n FROM {res$name}"))$n
  expect_equal(n, 3)
})