R/phc_tbl.R

In RollieParrish/ccdm: R functions for the Core Clinical Data Model (CCDM)

#' Create a tbl pointer to a specified schema and table in the PHC_DB_DEV database
#'
#' __Note:__ requires a connection using a Snowflake role such as PHC_ANALYTICS
#' If a schema name is not provided, will print a list of the available schemas in PHC_DB_DEV.
#' If a table name is not provided, will print a list of the available tables in the specified schema.
#'
#' @param conn a DBI-compliant connection object. recommend using the 'connect_cdw()` function
#' @param schema A character string specifying a table
#' @param name A character string specifying a table or view
#'
#' @return a tbl or a display of tables and views available
#
#' @importFrom dplyr tbl select mutate bind_rows
#' @importFrom dbplyr in_schema sql
#' @importFrom DBI dbListObjects
#'
#' @export
#'
#' @examples
#'
#' \dontrun{
#' # must use a role that has access (ie. PHC_ANALYTICS)
#' conn <- connect_cdw(role = "PHC_ANALTYICS")
#'
#' phc_tbl(conn)
#'
#' phc_tbl(conn, schema = "REF")
#'
#' phc_tbl(conn, "WHS_SEPSIS_DATAMART", "SEPSIS")
#'
#' disconnect_cdw(conn)
#'}


phc_tbl <- function(conn, schema = NA, name = NA) {

    # Todo: check that there's a valid connection

    if(is.na(schema)) {
        cat("Schema not specified.\nHere's a list of available schemas in PHC_DB_DEV:\n\n")

        results <- DBI::dbGetQuery(conn, "SHOW SCHEMAS IN DATABASE PHC_DB_DEV;") %>%
            dplyr::filter(options == "MANAGED ACCESS") %>%
            dplyr::select(schema = name,  comment) %>%
            tidyr::as_tibble()

        return(results)
    }

    if(is.na(name)) {
        paste0("Table/View not specified.\nHere's a list of available tables & views in ", schema, ":\n\n")

        results <-
            DBI::dbGetQuery(conn, "SELECT * FROM PHC_DB_DEV.INFORMATION_SCHEMA.TABLES;") %>%
            dplyr::filter(TABLE_SCHEMA == schema) %>%
            dplyr::mutate(SIZE = prettyunits::pretty_bytes(BYTES)) %>%
            dplyr::mutate(COMMENT = stringr::str_trunc(COMMENT, 80)) %>%
            dplyr::filter(!is.na(TABLE_OWNER)) %>%
            dplyr::select(TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE,
                          ROW_COUNT, SIZE, LAST_ALTERED, COMMENT)

        return(results)

        }

    # get the tbl
    dplyr::tbl(conn, dbplyr::in_schema(sql(glue::glue("PHC_DB_DEV.{schema}")), name))
    #dplyr::tbl(conn, DBI::Id(database = "PHC_DB_DEV", schema = schema, table = name))

}