Create functions to instantiate test tables for development and unit testing

[pbchase]

Because redcapcustodian works with database tables for both input and output data, the development and testing process will require that we can repeatably instantiate tables from code and populate them with test data. To that end, we will need functions and recipes that simplify that task. These tools should be as reduce the code required to instantiate a novel testing environment and be quick to run so tests can be run quickly and often.

Here is a non-modular approach for creating an in-memory data source using DBI, SQLite, a schema file, a data file, tidyverse. The function create_test_table() reads the schema file to create the table then reads the data file to populate it.

# Create an ephemeral in-memory RSQLite database
library(DBI)
schema <- "
CREATE TABLE `cases` (
  `record_id` double NOT NULL,
  `case_name_first` varchar(255) DEFAULT NULL,
  `case_name_last` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`record_id`)
);
"
cases <- tribble(
  ~record_id,
  1,2,3,4,5,6,7,8
)
con <- dbConnect(RSQLite::SQLite(), dbname = ":memory:")
create_test_table <- function(table) {
  # Create empty table
  schema_file_name <- paste0(table, "_schema.sql"))
  schema <- read_file(file=system.file(schema_file_name, package = "redcapcustodian"))
  dbSendQuery(con, schema)
  # Populate table
  table_file_name <- paste0(table, ".csv"))
  table_data <- dplyr:::read_csv(file=system.file(table_file_name, package = "redcapcustodian"))
  dbAppendTable(conn=con,
                name=table,
                value=table_data
                )
}
create_test_database <- function() {
  tables <- c(
    "redcap_user_information"
  )
  #tables %>% map(redcap_custodian::create_test_table())
}

This issue should create these deliverables:

1. Codify create_test_table() as a function in the library
2. SQL and CSV files for two REDCap tables. Do this for redcap_user_information and redcap_projects. A few records of data in each is fine. Exporting the test users built by redcap_custodian might well be perfect for redcap_user_information.
3. Create a test that creates one of those tables with create_test_table() to verify its functionality
4. Create an example script that uses these files to instantiate the two tables based on a vector of tables names.

[pbchase]

Addressed by PR #18