Add a streamlined code example for scraping credentials

[pbchase]

The 1 Florida ADRC project produced a snippet of code and some docs that might be useful to include in REDCap Custodian. @pbchase wrote this for that project in the https://github.com/ctsit/adrc_reporting repo:

library(redcapcustodian)
library(DBI)
library(tidyverse)
library(dotenv)

load_dot_env(".env")

# Create an empty credentials DB
# Make a connection
file_conn <- DBI::dbConnect(RSQLite::SQLite(), Sys.getenv("CREDENTIALS_DB"))

# SQLite friendly schema
credentials_sql <- "CREATE TABLE IF NOT EXISTS `credentials` (
  `redcap_uri` TEXT NOT NULL,
  `server_short_name` varchar(128) NOT NULL,
  `username` varchar(191) NOT NULL,
  `project_id` int(10) NOT NULL,
  `project_display_name` TEXT NOT NULL,
  `project_short_name` varchar(128) DEFAULT NULL,
  `token` varchar(64) NOT NULL,
  `comment` varchar(256) DEFAULT NULL
);
"
# Make the credentials DB
dbExecute(file_conn, credentials_sql)

# Get REDCap credentials
# copy example.redcap_prod.env to redcap_prod.env
load_dot_env("redcap_prod.env")
source_conn <- connect_to_redcap_db()
source_credentials <- scrape_user_api_tokens(source_conn, Sys.getenv("USER"))

# Alter credentials to match local schema
source_credentials_upload <- source_credentials %>%
  mutate(
    redcap_uri = Sys.getenv("URI"),
    server_short_name = tolower(Sys.getenv("INSTANCE"))
  ) %>%
  # Remove duplicates
  anti_join(
    tbl(file_conn, "credentials") %>%
      collect()
  )

# Write the new records to the Credentials DB
dbAppendTable(file_conn, "credentials", source_credentials_upload)

# Read the Credentials DB back to check your work
tbl(file_conn, "credentials") %>%
  collect()

dbDisconnect(source_conn)

It was described in the README for that project like this:

## Configuration and Credentials

These scripts require use environment files and a SQLite DB to manage local settings and secrets. You will need to create those files from the examples and customize them with your configuration and secrets before you can use any of these scripts.

To begin copy these two example files to their production names:

\```bash
cp example.env .env
cp example.redcap_prod.env 
\```

Edit `.env` to update these values:

\```bash
TOKEN=Your Token to the ADRC REDCap Project
EMAIL_TO=[email protected]_prod.env
EMAIL_CC=[email protected]
\```

You might also want to update the value of `CREDENTIALS_DB` in that file as well. While older scripts will read `TOKEN` from `.env`, newer scripts use this credentials database and the tools provided by REDCap Custodian to manage REDCap API credentials. We've provided the script [`create_and_populate_credentials_db.R`](./create_and_populate_credentials_db.R) to set the values in that credentials DB for you.

The script will need the database password for REDCap Prod. Write that into the `REDCAP_DB_PASSWORD` value in `redcap_prod.env`, then run [`create_and_populate_credentials_db.R`](./etl/create_and_populate_credentials_db.R) once. You can erase the REDCap Prod password from `redcap_prod.env` immediately after running the script.

These might form the foundation for a vignette, or possibly a function.