R/callisto_tbl.R

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

#' Create a tbl pointer to Callisto
#'
#' If a name is not provided, will print a list of the available tables and views in HI_DB.DMS_CALLISTO.
#'
#' @param conn a DBI-compliant connection object. recommend using the 'connect_cdw()` function
#' @param name A character string specifying a table in the HI_DB.DMS_CALLISTO schema
#'
#' @return a tbl or a display of tables and views in the DMS_CAlLISTO schema
#'
#' @importFrom dplyr tbl
#' @importFrom dbplyr in_schema sql
#' @importFrom DBI dbListTables Id
#'
#' @export
#'
#' @examples
#'
#' \dontrun{
#' conn <- connect_cdw()
#'
#' callisto_tbl(conn)
#'
#' callisto_tbl(conn, "ACCOUNT_LEVEL_SUMMARY")
#'
#' disconnect_cdw(conn)
#' }


callisto_tbl <- function(conn = conn, name = NA) {

    # ToDo: check that there's a valid connection
    if(is.na(name)) {
        paste0("Table/View not specified.\nHere's a list of available Callisto tables:\n\n")

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

        return(results)

    }

    # get the tbl
    dplyr::tbl(conn, dbplyr::in_schema(sql("HI_DB.DMS_CALLISTO"), name))
    #dplyr::tbl(conn, DBI::Id(database = "HI_DB", schema = "DMS_CALLISTO", table = name))

}