database-funcs.R

#' Connect to the data mart
#'
#' You must set three variables in your .Renviron: 'DATAMART_DSN', 'DATAMART_ID', and 'DATAMART_PW'.
#' @export
#' @examples
#'\dontrun{
#'irdb <- connect_to_datamart()
#'}
connect_to_datamart <- function() {
  DBI::dbConnect(odbc::odbc(),
                 dsn = Sys.getenv("DATAMART_DSN"),
                 uid = Sys.getenv("DATAMART_ID"),
                 pwd = Sys.getenv("DATAMART_PW"))
}

#' List data mart tables by schema
#'
#' @param schema either 'IRDMSTG' or 'IRWFRPD'
#'
#' @return a data frame
#' @export
#'
#' @examples
#'\dontrun{
#'list_dm_tables()
#'}
list_dm_tables <- function(schema = "IRDMSTG") {
  con <- rsfsu::connect_to_datamart()
  odbc::dbListTables(con, schema_name = schema)
  }

#' Connect to a table in the data mart
#'
#' @param con an Oracle connection
#' @param table_name a valid table name
#'
#' @export
#'
#' @examples
#' \dontrun{
#'con <- connect_to_datamart()
#'dm_enr <- connect_to_table(con, "DM_ENR")
#'}
connect_to_table <- function(con, table_name) {
  dplyr::tbl(con, dbplyr::in_schema("IRDMSTG", table_name))
}

#' Create a tbl connection to a data mart table
#'
#' @param table_name a table in the data mart
#' @param schema data mart schema, e.g. IRWFRPD, IRDMSTG
#'
#' @return a tbl
#' @export
#'
#' @examples
#'\dontrun{
#' dm_enr <- tbl_IR("DM_ENR")
#'}
tbl_IR <- function(table_name, schema = "IRDMSTG") {
  con <- rsfsu::connect_to_datamart()
  if (table_name %in% list_dm_tables()) {
    return(rsfsu::connect_to_table(con, table_name))
  }
  dplyr::tbl(con, dbplyr::in_schema("IRWFRPD", table_name))
}

#' Check number of records in data mart tables
#'
#' @param tables data mart tables
#'
#' @return nothing
#' @export
#'
#' @examples
#' \dontrun{
#'check_datamart_tables()
#'}
check_datamart <- function(tables = c("DM_RETN_SID",
                                             "DM_RETN",
                                             "DM_DEG",
                                             "DM_ENR",
                                             "DM_APP",
                                             "CRS_TAB",
                                             "DM_CRS_BOT",
                                             "DM_CRS_EOT"
                                             )
                                  ) {
  conx <- rsfsu::connect_to_datamart()
  message("Checking datamart...")
  for (i in seq_along(tables)) {
    sql <- sprintf("SELECT COUNT(*) FROM IRDMSTG.%s", tables[i])
    res <- DBI::dbSendQuery(conx, sql)
    records <- DBI::dbFetch(res)
    records <- as.integer(records)
    record_col <- ifelse(records == 0, function(x) {crayon::red(x)}, function(x) {crayon::green(x)})
    DBI::dbClearResult(res)
    m <- glue::glue("* Table {crayon::bold(tables[i])} returned {record_col(as.integer(records))} records. \n")
    message("\t", m)
  }
  message(crayon::yellow("Done!"))
  DBI::dbDisconnect(conx)
}

#' Put connections to common tables in your environment
#'
#' @param names_to_lower whether the names should be lowercase
#' @param ... additional args passed to kapow
#'
#' @export
#'
#' @examples
#' \dontrun{
#' tables_to_env()
#' }
tables_to_env <- function(names_to_lower = TRUE, ...) {
  tables <- c("DM_RETN_SID",
              "DM_RETN",
              "DM_DEG",
              "DM_ENR",
              "DM_APP",
              "CRS_TAB",
              "DM_CRS_BOT",
              "DM_CRS_EOT",
              "DM_STU_CONTACT"
              )
  if (names_to_lower) table_names <- tolower(tables)
  else table_names <- tables
  tables %>%
    purrr::map(tbl_IR) %>%
    purrr::set_names(table_names) %>%
    kapow::kapow(...)
}

#' Query the datamart
#'
#' @param sql A valid SQL query
#'
#' @return a data frame
#' @export
#'
dm_query <- function(sql) {
  con <- rsfsu::connect_to_datamart()
  on.exit(DBI::dbDisconnect(con))
  out <- DBI::dbGetQuery(con, sql)
  return(out)
}

#' Join a table in R with a table in the data mart
#'
#' @param x a table in your environment, the LHS of the join
#' @param dm_table name of a data mart table, e.g. "DM_DEG", the RHS of the join
#' @param by column (or columns) to join by
#' @param type join type, e.g. "left", "inner", "anti"
#' @param col_select One or more selection expressions, like in
#'   [dplyr::select()]. Use `c()` or `list()` to use more than one expression.
#' @param ... arbitrary filters to apply to RHS
#'
#' @return a table
#' @export
#'
#' @examples
#' \dontrun{
#' dm_join(x = my_table, dm_table = "DM_DEG", by = "SID", type = "left", col_select = everything())
#' }
dm_join <- function(x,
                    dm_table,
                    by,
                    type = c("left", "inner", "right", "semi", "anti"),
                    col_select = NULL,
                    ...) {

  join_type <- match.arg(type)
  col_select <- rlang::enquo(col_select)
  con <- connect_to_datamart()
  # tmp_tbl <- dplyr::copy_to(con, x)
  # xtab <- dplyr::tbl(con, tmp_tbl)
  # Would prefer to use a temp table, but receiving error:
  # Oracle Error: HYC00: [Oracle][ODBC]Optional feature not implemented.
  DBI::dbWriteTable(con, "TEMP_JOIN_TAB", x, overwrite = TRUE)
  on.exit(DBI::dbRemoveTable(con, "TEMP_JOIN_TAB"))
  xtab <- dplyr::tbl(con, "TEMP_JOIN_TAB")
  if (dm_table %in% list_dm_tables()) {
    dm_table <- rsfsu::connect_to_table(con, dm_table)
  } else {
    dm_table <- dplyr::tbl(con, dbplyr::in_schema("IRWFRPD", dm_table))
  }
  FUN <- switch(join_type,
                "left" = dplyr::left_join,
                "inner" = dplyr::inner_join,
                "right" = dplyr::right_join,
                "semi" = dplyr::semi_join,
                "anti" = dplyr::anti_join)
  out <- FUN(
    xtab,
    dm_table %>%
      dplyr::filter(...) %>%
      dplyr::select(!!col_select),
    by = by
    )
  dplyr::collect(out)
}