shadow_backup.R

message(Sys.time(), "\n\n")

x <- na.omit(stringr::str_match(commandArgs(), "--file=(.+)")[,2])
if (length(x)) setwd(dirname(x))

decolonize <- function(s) {
  s <- stringr::str_replace_all(s, "[[:punct:]]", "_") 
  stringr::str_replace_all(s, "[[:space:]]", "__")
}

suppressPackageStartupMessages(
  library(loggit)
)

set_logfile(
  glue::glue(
    "{getwd()}/log/shadow_backup_{decolonize(Sys.time())}.log"
  )
)
set_timestamp_format("%Y-%m-%dT%H:%M:%OS4%z")

message("Execution start")

real_time_rq <- httr::RETRY("GET", "example.com")
real_time <- httr::parse_http_date(httr::headers(real_time_rq)$date)

off <- Sys.time() - real_time
loggit(
  "INFO",
  glue::glue("Offset of this server and real time is {format(off)}"),
  offset_s = as.double(off, units = "secs")
)

source("secret.R")
source("utils/initializers.R")
source("utils/sql_constructors.R")

suppressPackageStartupMessages({
  library(dplyr)
  library(DBI)
})

repos <- "../shadow-splits"

loggit(
  "INFO",
  "Repo status",
  output = capture.output(git2r::status(repos)) %>% 
    sanitize_ndjson()
  )

git2r::config(
  repo = git2r::repository(repos),
  user.name = "shadow_db_backup_bot",
  user.email = "brianwdavis@gmail.com"
)

dir_create_conditional <- function(path) {
  if (!dir.exists(path)) {
    dir.create(path)
  }
}

etl_dump <- function(db_name, tbl_name) {
  out <- glue::glue("../shadow-splits/{db_name}_{tbl_name}.sql")
  
  system2(
    "sqlite3",
    args = c(
      glue::glue("./db/{db_name}.db"), 
      glue::glue("'.dump {tbl_name}'")
      ),
    stdout = out
  )

  info <- file.info(out)
  
  # age <- as.double(
  #   Sys.time() - info$mtime, 
  #   units = "secs"
  #   )
  # 
  # if (age > 10 || is.na(age) || is.null(age)) {
  #   warning("File `", basename(out), "` was not written during this call.")
  #   }
  
  #tibble(table = tbl_name, size = info$size, last_stored = info$mtime)
  loggit(
    "INFO", glue::glue("Dump"), 
    db = db_name, file = tbl_name, size = info$size, last_stored = info$mtime
    )
}


etl_split_dumps <- function(db_name, tbl_name) {
  dir_create_conditional(
    glue::glue("../shadow-splits/{db_name}/{tbl_name}_sql")
  )
  
  dump_src <- glue::glue("../shadow-splits/{db_name}_{tbl_name}.sql")
  dump_trg <- glue::glue("../shadow-splits/{db_name}/{tbl_name}_sql/sql_")
  
  system2("split", args = c("-l 1000", "-a 3", dump_src, dump_trg))
  
  num_splits <- length(list.files(dirname(dump_trg)))
  
  loggit(
    "INFO", "Split",
    db = db_name, file = tbl_name, number_of_files = num_splits
  )
  
}

con_sh_f <- etl_connect_shadow("forms")


DBI::dbListTables(con_sh_f) %>% 
  purrr::walk(~etl_dump("forms", .x))

message("Forms dump completed")

DBI::dbListTables(con_sh_f) %>% 
  purrr::walk(~etl_split_dumps("forms", .x))

message("Forms dumps split")


dbDisconnect(con_sh_f)




con_sh_s <- etl_connect_shadow("sensors")

DBI::dbListTables(con_sh_s) %>% 
  purrr::walk(~etl_dump("sensors", .x)) 

message("Sensors dump completed")

DBI::dbListTables(con_sh_s) %>% 
  purrr::walk(~etl_split_dumps("sensors", .x)) 

message("Sensors dumps split")


dbDisconnect(con_sh_s)



loggit(
  "INFO",
  "Repo status",
  output = capture.output(git2r::status(repos)) %>% 
    sanitize_ndjson()
)

git2r::add(
  repo = repos,
  path = "*"
)
message("`git add`ed all files")


s <- git2r::status(repos)
s

loggit(
  "INFO",
  "Repo status",
  output = capture.output(s) %>% 
    sanitize_ndjson()
)

l <- s[c("staged", "unstaged")] %>% 
  purrr::map_int(length)

sess <- sessionInfo()
sys <- Sys.info()

if (sum(l)) {
  cmt <- capture.output(
    git2r::commit(
      repo = repos,
      message = glue::glue(
        "Automated backup at {Sys.time()}\n\n",
        R.version.string,
        "\n{sess$platform}\n{sess$running}",
        "\n{sys['nodename']}\n{sys['user']}"
      ),
      all = TRUE
    )
  ) %>% sanitize_ndjson()
  
  loggit(
    "INFO",
    "Repo commit",
    output = cmt
  )
} else {
  message("Nothing to commit!")
}

# git2r::remote_add(
#   repo = repos,
#   name = "shadow-splits-remote",
#   url = "https://github.com/precision-sustainable-ag/shadow-splits.git"
# )

if (sum(l)) {
  git2r::push(
    object = repos,
    name = "shadow-splits-remote",
    refspec = "refs/heads/master",
    credentials = git2r::cred_user_pass(
      username = "brianwdavis",
      password = gh_token
    )
  )
  message("Pushed to GH remote")
} else {
  message("Nothing to push!")
}

message("Execution end")

set_logfile(logfile = NULL, confirm = TRUE)


# Generate README for shadow-backup repo ----
#
# writeLines(
#   c(
#     "# Structure",
#     "Each file is a dump generated by calling: ", "",
#     " - `sqlite3 .dump {table_name} > {db_name}_{table_name}.sql`",
#     " - `split -l 1000 -a 3 {db_name}_{table_name}.sql {db_name}/{table_name}_sql/sql_`,
#     "",
#     "To reconstruct, use:", "",
#     " - `cat {db_name}/{table_name}_sql/sql_* > {db_name}_{table_name}.sql`,
#     " - `sqlite3 new_db.db ".read {db_name}_{table_name}.sql"`,
#     "",
#     "# Origin",
#     "These files are automatically generated by the script at:", "",
#     "`<link to ETL repo coming...>`"
#   ),
#   file.path(repos, "README.md")
# )
# git2r::add(
#   repos,
#   "README.md"
# )