backup_tbl

backup_tbl.R

library("dplyr")
library("readr")

# Download a tbl from a db connection and write to a csv
backup_tbl <- function(tbl_name, src, output_dir) {
  # Try to download the tbl, defaulting to an empty data-frame
  try_tbl <- failwith(data_frame(), tbl)
  df <- collect(try_tbl(src, tbl_name))

  output_file <- file.path(output_dir, paste0(tbl_name, ".csv"))
  message("Writing ", output_file)
  write_csv(df, output_file)
  df
}

#' @export
l2t_backup <- function(l2t_con, backup_dir) {
  # Create a folder for today's date
  stamp <- format(Sys.time(), "%Y_%m_%d")
  this_backup_dir <- file.path(backup_dir, stamp)
  dir.create(this_backup_dir, showWarnings = FALSE, recursive = TRUE)

  # Backup each tbl in the database connection
  tbls <- src_tbls(l2t_con)
  dfs <- lapply(tbls, backup_tbl, src = l2t_con, output_dir = this_backup_dir)
  names(dfs) <- tbls
  dfs
}

connect.R

library("assertthat")
library("dplyr")

#' Connect to the L2T database using a MySQL config file
#' @param cnf_file a MySQL config file
#' @return a dplyr database connection
#' @details
#' http://svitsrv25.epfl.ch/R-doc/library/RMySQL/html/RMySQL-package.html
#' @export
l2t_connect <- function(cnf_file) {
  assert_that(file.exists(cnf_file))

  src_mysql(
    user = NULL,
    password = NULL,
    dbname = "l2t",
    default.file = cnf_file)
}