R/sql_append_table.R

In elo2zero/oddsandsods: Miscellanous functions

#' @export
sqlAppendTable <- function(df_new, table_name, by="id"){
  # df_new <- read.table("~/debug_file_speedway_dashboard")
  df_new[df_new==""] <- NA
  dbSendQuery(con, "DROP TABLE IF EXISTS tempTable")
  df_new %>% oddsandsods:::sqlCreateTable(table_name = "tempTable")
  df_new %>% sqlInsert("tempTable")
  #
  if("id" %in% colnames(df_new)){
    fields_to_insert <- setdiff(colnames(df_new),"id")
    fields_to_insert <- paste0("new.",fields_to_insert) %>%
      paste(collapse=",\n")
    fields           <- setdiff(colnames(df_new),"id") %>% paste(collapse=",")
  } else {
    fields_to_insert <- paste0("new.",colnames(df_new)) %>% paste(collapse=",")
    fields           <- colnames(df_new) %>% paste(collapse=",")
  }
  fields_to_match    <- paste0(paste0("old.",by),"=",paste0("new.",by)) %>%
    paste(collapse=" and ")

  fields_to_update   <- setdiff(colnames(df_new), by)
  fields_to_update   <- paste0(
    paste0("old.",fields_to_update),"=",
    paste0("new.",fields_to_update)
  ) %>%
    paste(collapse=",\n")

  #
  update_cmd <- sprintf({"
    UPDATE %1$s old
    INNER JOIN tempTable new
    ON %2$s
    SET
      %3$s,
      old.updated_at = CURRENT_TIMESTAMP;"}, table_name, fields_to_match, fields_to_update)
  insert_cmd <- sprintf({"
      INSERT INTO %1$s (%2$s, updated_at)
      SELECT %3$s, CURRENT_TIMESTAMP
      FROM tempTable new
      LEFT JOIN %1$s old ON %4$s
      WHERE old.id IS NULL"}, table_name, fields, fields_to_insert, fields_to_match)

  dbSendQuery(con, update_cmd); dbClearResult(dbListResults(con)[[1]])
  dbSendQuery(con, insert_cmd); dbClearResult(dbListResults(con)[[1]])
}