tests/testthat/test-zzz-dplyr.R

In CDMConnector: Connect to an OMOP Common Data Model

# copy_to with overwrite=T does not work on Oracle. Global temp tables need to be truncated before being dropped.

# test_that("Oracle dplyr works", {
#   skip_if_not("OracleODBC-19" %in% odbc::odbcListDataSources()$name)
#   skip_on_ci() # need development version of dbplyr
#
#   con <- DBI::dbConnect(odbc::odbc(), "OracleODBC-19")
#
#   cdm <- CDMConnector::cdm_from_con(
#     con = con,
#     cdm_schema = "CDMV5"
#   )
#
#   df <- cdm$observation_period %>%
#     dplyr::mutate(new_date = !!asDate(observation_period_start_date)) %>% #as.Date translation is incorrect
#     head() %>%
#     dplyr::collect()
#
#   expect_s3_class(df, "data.frame")
#
#   df <- cdm$person %>%
#     # dplyr::left_join(cdm$observation_period, by = "person_id") %>% # this fails
#     dplyr::left_join(cdm$observation_period, by = "person_id", x_as = "x", y_as = "y") %>%
#     head() %>%
#     dplyr::collect()
#
#   expect_s3_class(df, "data.frame")
#
#
#   df <- cdm$person %>%
#     dplyr::slice_sample(n = 100) %>%
#     dplyr::collect()
#
#   expect_s3_class(df, "data.frame")
#
#   cdm$person %>%
#     dplyr::select(year_of_birth, month_of_birth, day_of_birth) %>%
#     dplyr::mutate(dob = as.Date(paste0(
#       .data$year_of_birth, "/",
#       .data$month_of_birth, "/",
#       .data$day_of_birth
#     ))) %>%
#     dbplyr::sql_render()
#
#   DBI::dbDisconnect(con)
# })
#
#
# # Test dbplyr quantile translation -----
#
#
# test_that("quantile translation works on postgres", {
#   skip_if(Sys.getenv("CDM5_POSTGRESQL_USER") == "")
#   skip("manual test")
#
#   con <- DBI::dbConnect(RPostgres::Postgres(),
#                         dbname = Sys.getenv("CDM5_POSTGRESQL_DBNAME"),
#                         host = Sys.getenv("CDM5_POSTGRESQL_HOST"),
#                         user = Sys.getenv("CDM5_POSTGRESQL_USER"),
#                         password = Sys.getenv("CDM5_POSTGRESQL_PASSWORD"))
#
#
#
#   cdm <- cdm_from_con(con, cdm_schema = Sys.getenv("CDM5_POSTGRESQL_CDM_SCHEMA"))
#
#   # fails
#   # df <- cdm$drug_exposure %>%
#   #   dplyr::select(drug_concept_id, days_supply) %>%
#   #   dplyr::group_by(drug_concept_id) %>%
#   #   dplyr::mutate(q05_days_supply = quantile(.data$days_supply, 0.05, na.rm = T)) %>%
#   #   dplyr::distinct(drug_concept_id, q05_days_supply) %>%
#   #   dplyr::collect()
#   #
#   # expect_s3_class(df, "data.frame")
#
#   df <- cdm$drug_exposure %>%
#     dplyr::select(drug_concept_id, days_supply) %>%
#     dplyr::group_by(drug_concept_id) %>%
#     dplyr::summarise(q05_days_supply = quantile(.data$days_supply, 0.05, na.rm = T)) %>%
#     dplyr::collect()
#
#   expect_s3_class(df, "data.frame")
#
#   DBI::dbDisconnect(con)
# })
#
# test_that("quantile translation works on sql server", {
#   skip_if(Sys.getenv("CDM5_SQL_SERVER_USER") == "")
#   skip("manual test")
#
#   con <- DBI::dbConnect(odbc::odbc(),
#                         Driver   = Sys.getenv("SQL_SERVER_DRIVER"),
#                         Server   = Sys.getenv("CDM5_SQL_SERVER_SERVER"),
#                         Database = Sys.getenv("CDM5_SQL_SERVER_CDM_DATABASE"),
#                         UID      = Sys.getenv("CDM5_SQL_SERVER_USER"),
#                         PWD      = Sys.getenv("CDM5_SQL_SERVER_PASSWORD"),
#                         TrustServerCertificate = "yes",
#                         Port     = 1433)
#
#   cdm <- cdm_from_con(con, cdm_schema = c("CDMV5", "dbo"))
#
#   df <- cdm$drug_exposure %>%
#     dplyr::select(drug_concept_id, days_supply) %>%
#     dplyr::group_by(drug_concept_id) %>%
#     dplyr::mutate(q05_days_supply = quantile(.data$days_supply, 0.05, na.rm = T)) %>%
#     dplyr::distinct(drug_concept_id, q05_days_supply) %>%
#     dplyr::collect()
#
#   expect_s3_class(df, "data.frame")
#
#   # fails
#   # df <- cdm$drug_exposure %>%
#   #   dplyr::select(drug_concept_id, days_supply) %>%
#   #   dplyr::group_by(drug_concept_id) %>%
#   #   dplyr::summarise(q05_days_supply = quantile(.data$days_supply, 0.05, na.rm = T)) %>%
#   #   dplyr::collect()
#
#   # expect_s3_class(df, "data.frame")
#
#   DBI::dbDisconnect(con)
# })
#
# test_that("quantile translation works on redshift", {
#   skip_if(Sys.getenv("CDM5_REDSHIFT_USER") == "")
#   skip("manual test")
#
#   con <- DBI::dbConnect(RPostgres::Redshift(),
#                         dbname   = Sys.getenv("CDM5_REDSHIFT_DBNAME"),
#                         host     = Sys.getenv("CDM5_REDSHIFT_HOST"),
#                         port     = Sys.getenv("CDM5_REDSHIFT_PORT"),
#                         user     = Sys.getenv("CDM5_REDSHIFT_USER"),
#                         password = Sys.getenv("CDM5_REDSHIFT_PASSWORD"))
#
#   cdm <- cdm_from_con(con, cdm_schema = Sys.getenv("CDM5_REDSHIFT_CDM_SCHEMA"))
#
#   # df <- cdm$drug_exposure %>%
#   #   dplyr::select(drug_concept_id, days_supply) %>%
#   #   dplyr::group_by(drug_concept_id) %>%
#   #   dplyr::mutate(q05_days_supply = quantile(.data$days_supply, 0.05, na.rm = T)) %>%
#   #   dplyr::distinct(drug_concept_id, q05_days_supply) %>%
#   #   dplyr::collect()
#   #
#   # expect_s3_class(df, "data.frame")
#
#   df <- cdm$drug_exposure %>%
#     dplyr::select(drug_concept_id, days_supply) %>%
#     dplyr::group_by(drug_concept_id) %>%
#     dplyr::summarise(q05_days_supply = quantile(.data$days_supply, 0.05, na.rm = T)) %>%
#     dplyr::collect()
#
#   expect_s3_class(df, "data.frame")
#
#   DBI::dbDisconnect(con)
# })
#
# test_that("quantile translation works on Oracle", {
#   skip_on_ci()
#   skip_on_cran()
#   skip_if_not("OracleODBC-19" %in% odbc::odbcListDataSources()$name)
#   skip("manual test")
#
#   cdm_schema <- Sys.getenv("CDM5_ORACLE_CDM_SCHEMA")
#   con <- DBI::dbConnect(odbc::odbc(), "OracleODBC-19")
#
#   cdm <- cdm_from_con(con, cdm_schema = cdm_schema)
#
#   df <- cdm$drug_exposure %>%
#     dplyr::select(drug_concept_id, days_supply) %>%
#     dplyr::group_by(drug_concept_id) %>%
#     dplyr::mutate(q05_days_supply = quantile(.data$days_supply, 0.05, na.rm = T)) %>%
#     dplyr::distinct(drug_concept_id, q05_days_supply) %>%
#     dplyr::collect()
#
#   expect_s3_class(df, "data.frame")
#
#   df <- cdm$drug_exposure %>%
#     dplyr::select(drug_concept_id, days_supply) %>%
#     dplyr::group_by(drug_concept_id) %>%
#     dplyr::summarise(q05_days_supply = quantile(.data$days_supply, 0.05, na.rm = T)) %>%
#     dplyr::collect()
#
#   expect_s3_class(df, "data.frame")
#
#   DBI::dbDisconnect(con)
# })
#
# test_that("quantile translation works on Spark", {
#   skip_if_not("Databricks" %in% odbc::odbcListDataSources()$name)
#   skip("manual test")
#
#   con <- DBI::dbConnect(odbc::odbc(), dsn = "Databricks", bigint = "numeric")
#
#   cdm <- cdm_from_con(con, cdm_schema = "omop531")
#
#   # df <- cdm$drug_exposure %>%
#   #   dplyr::select(drug_concept_id, days_supply) %>%
#   #   dplyr::group_by(drug_concept_id) %>%
#   #   dplyr::mutate(q05_days_supply = quantile(.data$days_supply, 0.05, na.rm = T)) %>%
#   #   dplyr::distinct(drug_concept_id, q05_days_supply) %>%
#   #   dplyr::collect()
#   #
#   # expect_s3_class(df, "data.frame")
#
#   df <- cdm$drug_exposure %>%
#     dplyr::select(drug_concept_id, days_supply) %>%
#     dplyr::group_by(drug_concept_id) %>%
#     dplyr::summarise(q05_days_supply = quantile(.data$days_supply, 0.05, na.rm = T)) %>%
#     dplyr::collect()
#
#   expect_s3_class(df, "data.frame")
#
#   DBI::dbDisconnect(con)
# })
#
# test_that("quantile translation works on duckdb", {
#   skip_if_not(rlang::is_installed("duckdb"))
#   skip_if_not(eunomiaIsAvailable())
#   skip("manual test")
#
#   con <- DBI::dbConnect(duckdb::duckdb(), dbdir = eunomiaDir())
#
#   cdm <- cdm_from_con(con, cdm_schema = "main")
#
#   # df <- cdm$drug_exposure %>%
#   #   dplyr::select(drug_concept_id, days_supply) %>%
#   #   dplyr::group_by(drug_concept_id) %>%
#   #   dplyr::mutate(q05_days_supply = quantile(.data$days_supply, 0.05, na.rm = T)) %>%
#   #   dplyr::distinct(drug_concept_id, q05_days_supply) %>%
#   #   dplyr::collect()
#
#   # expect_s3_class(df, "data.frame")
#
#   df <- cdm$drug_exposure %>%
#     dplyr::select(drug_concept_id, days_supply) %>%
#     dplyr::group_by(drug_concept_id) %>%
#     dplyr::summarise(q05_days_supply = quantile(.data$days_supply, 0.05, na.rm = T)) %>%
#     dplyr::collect()
#
#   expect_s3_class(df, "data.frame")
#
#   DBI::dbDisconnect(con, shutdown = TRUE)
# })
#
#
# test_that("Oracle inSchema works", {
#   skip_if_not("OracleODBC-19" %in% odbc::odbcListDrivers())
#
#   con <- DBI::dbConnect(odbc::odbc(), "OracleODBC-19")
#   cdm_schema <- Sys.getenv("CDM5_ORACLE_CDM_SCHEMA")
#
#   person <- dplyr::tbl(con, inSchema(cdm_schema, "PERSON", dbms(con))) %>%
#     dplyr::rename_all(tolower)
#
#   observation_period <- dplyr::tbl(con, inSchema(cdm_schema, "OBSERVATION_PERIOD", dbms(con))) %>%
#     dplyr::rename_all(tolower)
#
#   df <- dplyr::inner_join(person, observation_period, by = "person_id") %>%
#     head(2) %>%
#     dplyr::collect()
#
#   expect_s3_class(df, "data.frame")
#
#   DBI::dbDisconnect(con)
# })
#
#


# TODO not operator on a column does not work on sqlserver
# dplyr::tbl(attr(cdm, "dbcon"), inSchema(attr(cdm, "write_schema"),
#                                         tempName,
#                                         dbms = dbms(con))) %>%
#   dplyr::filter(!.data$is_excluded)