R/client.R

In Cloud9Smart/postgresUtils: Functions for Postgres

.convert <- function(dat_obj) {

  # remove null and NA values
  topass <- dat_obj %>%
    purrr::map(discard, is.na) %>%
    purrr::compact()

  values <- purrr::map_if(topass, is.character, ~paste0("'",gsub("'", "''", .,), "'"))

  # dates get special formatting
  values <- purrr::map_if(values, ~inherits(., "Date"), ~paste0("'", ., "'"))

  paste(values, collapse = ", ")

}


.convupdate <- function(dat_obj) {

  # remove null values
  topass <-  dat_obj %>%
    purrr::map(purrr::discard, is.na) %>%
    purrr::compact()

  # Character values get quotes
  values <- purrr::map_if(topass, is.character, ~paste0("'", gsub("'", "''", .), "'"))
  # dates get special formatting
  values <- purrr::map_if(values, ~inherits(., "Date"), ~paste0("'", ., "'"))

  values

}



#' Insert things to Postgres
#'
#' @export
pg_insert <- function(path, dat_obj) {

  dat_obj <- dat_obj %>%
    purrr::map(purrr::discard, is.na) %>%
    purrr::compact()

  ins_name <- paste(names(dat_obj), collapse = ", ")
  values <- .convert(dat_obj = dat_obj)
  paste0("Insert INTO ", path, " (", ins_name, ") VALUES (", values, ")")
}




#' Update helper (PATCH)
#'
#' For updating informaiton by supplying the path to the table, and the
#' value to update.
#'
#' This is geared towards mostly updating
#'
#' //TODO update the primary key and the column name as separate things
#'
#' @param path to the object to update
#' @param dat_obj data to pass to quick books
#' @param primary_key data 'primary key = dat_object$fullName'
#' @param value field to define as the primary key
#'
#' @export
pg_update_stmt <- function(table, dat_obj, primary_key = NULL, value = NULL) {


  # these are not variable that should ever be updated
  dat_obj$name <- NULL
  dat_obj$ParentRefFullName <- NULL

  update_obj <- purrr::compact(dat_obj)

  # returns just the formatted values
  updates <- .convupdate(update_obj)

  # paste the name of the variables "=" to the updated values
  updates_topass <- paste(names(update_obj), updates, sep = " = ", collapse = ", ")

  paste0("Update ", table, " set ", updates_topass,
         " WHERE ", primary_key, " = '", gsub("'", "''", value), "'")
}