R/access_postgresql.R

In JayAchar/hisreportr: hisreportr: Automating MSF OCA EMR reporting

#' Access and import PostgreSQL data
#' 
#' This function should be used to access the remote PostgreSQL data base 
#'   and import data into the current R session. The function will attempt
#'   to open a connection, execute the defined functions and close the
#'   database connection. 
#' 
#' @param host string defining PostgreSQL database host name
#' @param username string to identify database username
#' @param password string to provide access password
#' @param db_name string to identify the name of the database being accessed - e.g. "analytics"
#' @param report_name string defining which report-specific data to extract from 
#' PostgreSQL data base
#' @author Jay Achar
#' @seealso \code{\link{hisreportr}}
#' 
#' @importFrom assertthat assert_that
#' @importFrom RPostgres Postgres
#' @importFrom purrr map set_names
#' @importFrom DBI dbGetQuery dbDisconnect dbConnect
#' @importFrom dplyr %>% 
#'
#' @return A list of R objects will be returned. The content will be dependent
#'   upone the functions defined in `import_fun`.
#'
#' @examples
#' \dontrun{
#' access_postgresql(host = "test-postgre-oca.org",
#'                   username = "user1",
#'                   password = PG_PWD,
#'                   report_name = "tb_month")
#' }
#'


access_postgresql <- function(host, 
                              username, 
                              password,
                              db_name,
                              report_name = c("tb_month", 
                                              "hcv_month", 
                                              "hiv_month")) {
  
  # check args
  assertthat::assert_that(is.character(host),
                          is.character(username),
                          is.character(password),
                          is.character(report_name),
                          is.character(db_name))
  
  report_name <- match.arg(report_name)
  
  # creates a connection to the postgres database
  # note that "con" will be used later in each connection to the database
  con <- DBI::dbConnect(RPostgres::Postgres(), 
                        dbname = db_name,
                        host = host, 
                        port = 5432,
                        user = username, 
                        password = password)
  
  # close connections on exit
  on.exit(DBI::dbDisconnect(con))
  # on.exit(dbUnloadDriver(drv), add = TRUE)
  
  # check con exists
  assertthat::assert_that(exists("con"),
                          "PqConnection" %in% class(con))
  
  # define which PostgreSQL queries to use for report
  if (grepl("^tb", report_name)) {
    sql <- tb_commands
  }

  if (grepl("^hiv", report_name)) {
    sql <- hiv_commands
  }
  
  if (grepl("^hcv", report_name)) {
    sql <- hcv_commands
  }
    
  # TODO run map2 safely 
  # GET data from db according to report-specific commands
  purrr::map(sql$command, 
                  .f = ~ DBI::dbGetQuery(con, .x)) %>% 
    purrr::set_names(sql$name)
  
  # TODO when map2 converted to safely, check output for errors

}