R/02_helpers.R

In moodymudskipper/mmdb:

db_exists <- function(
  tbl_chr){
  # to avoid CMD check notes:
  con <- get("con")

  DBI::dbExistsTable(con, tbl_chr)
}

db_remove <- function(
  tbl_chr,
  remove_in_R = TRUE){
  # to avoid CMD check notes:
  con <- get("con")
  db <- get("db")

  if(db_exists(tbl_chr)){
    DBI::dbRemoveTable(con, tbl_chr)
    if(remove_in_R) rm(list = tbl_chr, envir = db)
  }
  invisible(NULL)
}

# a wrapper around sprintf and dbSendQuery
db_query <- function(
  fmt,
  ...){
  # to avoid CMD check notes:
  con <- get("con")

  query <- sprintf(fmt, ...)
  rs <- DBI::dbSendQuery(con, query)
  DBI::dbClearResult(rs)
}

# rename, through a temporary name if relevant, and overwriting without warning
db_rename <- function(
  from_chr,
  to_chr,
  remove_in_R = TRUE){
  # to avoid CMD check notes:
  con <- get("con")
  db <- get("db")

  if(db_exists(to_chr)){
    # first rename to temp so we don't delete to for nothing if copy of the
    # 'from" table doesn't work
    db_remove("temporary0", remove_in_R = FALSE) # remove if exists (shouldn't !)
    db_query("ALTER TABLE %s RENAME TO %s", from_chr, "temporary0")
    # then remove 'to' table and rename temp table to its name
    db_remove(to_chr)
    db_query("ALTER TABLE %s RENAME TO %s", "temporary0", to_chr)
  } else {
    db_query("ALTER TABLE %s RENAME TO %s", from_chr, to_chr)
  }

  if(remove_in_R) rm(list = from_chr, envir = db)

  res <- dplyr::tbl(con, to_chr)
  assign(to_chr, `class<-`(to_chr, "db_table_name"), envir = db)

  invisible(NULL)
}

db_upload <- function(
  from_chr,
  to_chr){
  # to avoid CMD check notes:
  con <- get("con")
  db <- get("db")

  DBI::dbWriteTable(con, to_chr, from_chr, overwrite = TRUE, append = FALSE)

  assign(to_chr, `class<-`(to_chr, "db_table_name"), envir = db)
  invisible(NULL)
}

db_create <- function(
  from_tbl,
  to_chr){
  # to avoid CMD check notes:
  db <- get("db")

  if(db_exists(to_chr)){
    db_remove("temporary0", remove_in_R = FALSE)
    db_query("CREATE TABLE temporary0 AS %s",
             dbplyr::sql_render(from_tbl))
    db_remove(to_chr)
    db_rename(from_chr = "temporary0", to_chr = to_chr, remove_in_R = FALSE)
  } else {
    db_query("CREATE TABLE %s AS %s", to_chr,
             gsub("\n"," ", dbplyr::sql_render(from_tbl)))
  }
  assign(to_chr, `class<-`(to_chr, "db_table_name"), envir = db)
  invisible(NULL)
}