R/create_portfolios_db.R

In databrew/portfoliodash: The IFC Portfolio Dashboard

#' Create an initial portfolios table in the psql database
#'
#' Create an initial portfolio_users data table in the psql database
#' @param portfolios A dataframe with the columns portfolio_id, portfolio_name
#' @param connection_object An open connection to adatabase (as created through \code{credentials_extract} and \code{credentials_connect} or \code{credentials_now}); if \code{NULL}, the function will try to create a \code{connection_object} by retrieving user information from the \code{credentials/credentials.yaml}
#' in or somewhere upwards of the working directory.
#' @return A "portfolios" table added to PSQL database
#' @import DBI
#' @import dplyr
#' @import RPostgreSQL
#' @export

create_portfolios_db <- function(portfolios = NULL,
                                 connection_object = NULL){
  
  # If not connection object, try to find one
  if(is.null(connection_object)){
    message(paste0('No connection_object provided. Will try ',
                   'to find a credentials file.'))
    # Get credentials
    the_credentials <- credentials_extract()
    # Establish the connection
    connection_object <- credentials_connect(the_credentials)
  }
  
  if(is.null(portfolios)){
    as_portfolio <- get_data(query = 'SELECT* FROM portfolio.as_portfolio',
                             connection_object = connection_object)
    portfolios <-
      as_portfolio %>%
      dplyr::select(primary_business_line_code) %>%
      dplyr::rename(portfolio_name = primary_business_line_code) %>%
      dplyr::filter(!duplicated(portfolio_name)) %>%
      mutate(portfolio_id = as.integer(factor(portfolio_name))) %>%
      dplyr::select(portfolio_id, portfolio_name)
  }
  
  # Upload it to the database
  copy_to(connection_object, portfolios,
          dbplyr::in_schema("portfolio", "portfolios"),
          temporary = FALSE,
          overwrite = TRUE)
}