Test if API and local SQL queries result in same objects

[PietrH]

During #317 I noticed 9 functions return a different result between the API and a local SQL query.

• Add tests all exported functions to see if API and local queries match
• Create custom expectation for comparing api to sql query
• find why there is a mismatch, and fix it!

Some of these differences might be fixed, or at least different after merging #328

API - SQL mismatch

mismatches for:

• get_cpod_projects
• list_animal_project_codes
• list_acoustic_project_codes
• get_animals
• list_receiver_ids
• get_tags
• get_animal_projects
• list_scientific_names
• get_acoustic_projects

---

          Maybe this is a good use case for a [custom expectation](https://testthat.r-lib.org/articles/custom-expectation.html).

For example, you could create a expect_call_agnostic(), with one argument: a function call. The helper could grab the function call (and its arguments), run it locally and over API and expect the result to be the same.

It could then be called as part of a regular test-function.R file:

testthat("get_animals() returns same result locally and via API", {
  expect_call_agnostic(get_animals(animal_id = "5"))
})

_Originally posted by @peterdesmet in https://github.com/inbo/etn/issues/317#issuecomment-2421841943_
            

[PietrH]

lil 🐧 script to check for these functions:

for(function_to_test in getNamespaceExports("etnservice")) {
  test_that(paste(
    function_to_test,
    "returns same result over api as over local db connection"
  ),
  {
    skip_if_offline()
    skip("not all functions work with no arguments")

    api_result <- do.call(function_to_test, list(api = TRUE))
    sql_result <- do.call(function_to_test, list(api = FALSE))
    expect_identical(api_result, sql_result, label = function_to_test)
  })
}

for(function_to_test in getNamespaceExports("etnservice")){
  print(function_to_test)
  api_result <- do.call(function_to_test, list(api = TRUE))
  sql_result <- do.call(function_to_test, list(api = FALSE))
  expect_identical(api_result, sql_result, label = function_to_test)
}

test_result <- purrr::map(
  getNamespaceExports("etnservice"),
  purrr::safely(function(function_to_test){
    api_result <- do.call(function_to_test, list(api = TRUE))
    sql_result <- do.call(function_to_test, list(api = FALSE))
    expect_identical(api_result, sql_result)
  }
)) %>% purrr::set_names(getNamespaceExports("etnservice"))

# only keep those with errors
test_result[purrr::map_lgl(test_result, ~!is.null(purrr::pluck(.x,"error")))]

[PietrH]

skip_if(Sys.info()["nodename"] != "rshiny", "Test is not running on Lifewatch RStudio Server")

Or even better:

skip_if("ETN" %in% odbc::odbcListDataSources()$name, "ETN is not a local database on this machine")

[PietrH]

Blocked by #423 , I can only run these tests when I have local database access.

[PietrH]

Mismatches should no longer occur, as local database queries use etnservice as a dependency. As long as the local etnservice version is identical to the one deployed on OpenCPU, the results should be identical.

Only possible exceptions are:

• get_acoustic_detections() uses internal logic
• write_dwc() download_acoustic_dataset() : client side processing