Clean redcap_log_view

[pbchase]

The redcap_log_view table can get very large while storing access data that is not always very useful. This is especially true of the logged activity of scheduled downloads via the API. The UF STP project effectively managed the growth of this table in the script groom_redcap_log_view.R. The code from that private repo is reproduced here for reference:

library(DBI)
library(RMariaDB)
library(dotenv)
library(lubridate)
library(sendmailR)
library(stp)
library(tidyverse)

# set script run time
set_script_name("groom_redcap_log_view")
script_run_time <- set_script_run_time()

# enable logging
if (!interactive()) {
  logging <- log_messages_to_file()
}

# open connection
con <- stp::connect_to_redcap_db()

# set window start and end time
window_start_time <- script_run_time - lubridate::ddays(16)
window_end_time <- script_run_time - lubridate::ddays(14)

sql <- paste0("delete from redcap_log_view",
              " where session_id is null",
              " and ts > '", window_start_time, "'",
              " and ts < '", window_end_time, "'",
              " and user in ('alexandrammiller','inyoungjun','kyle.chesney','lawjames1','mbentz','pbc','roywilliams')")

message <- ""
results <- DBI::dbExecute(con, sql)

if (results > 0) {
  message <- paste0(
    message, "\n", "Records removed from redcap_log_view: ",
    results
  )
}

job_duration <- sprintf("%03.2f", get_current_run_time())

email_subject <- paste(
  "Groomed redcap_log_view", "|",
  Sys.getenv("INSTANCE"), "|", script_run_time, "|", job_duration, "s")
attachment_object <- mime_part(logging$file_fullpath, logging$file_name)
email_message <- list(message, attachment_object)
send_upload_email(email_message, email_subject)

dbDisconnect(con)

The referenced functions from the private stp package are not provided, but their meaning can probably be inferred from their names.