Server module to present and control a DB table — cdr_manage_db

This function is a server module that gets database tables from a DB, presents that table in shiny server, and syncs user changes between the UI server-memory R tables, and the back-end DB.

Usage

cdr_manage_db_tbls(
  db_tbl_name,
  key_col,
  conn_pool,
  session,
  add_row_permission = FALSE,
  del_row_permission = FALSE,
  cell_edit_permission = FALSE,
  lock_fields = c(),
  multiuser_update_wait = 0,
  chg_log_suffix = "_DELTAS",
  ...
)

Arguments

db_tbl_name: a string specifying the table name for the the primary database table you are managing, e.g. 'iris_tbl'
key_col: name of the unique ID column in the db table (table must have a unique ID column with unique IDs)
conn_pool: db connection from package 'pool' or 'DBI'
session: current shiny session
add_row_permission: T or F: allows user to add a row to the primary table of the module
del_row_permission: T or F: allows user to delete a row on the primary table of the module
cell_edit_permission: T or F: to make editable the primary table from the module (cell_edit_permission = T means the user can change the data) (cell_edit_permission = F means the user can only see the data)
lock_fields: strings: a vector of field names from the database table to lock from user editing
multiuser_update_wait: numeric: minimum time in seconds between checking for and incorporating potential data changes made by other users
chg_log_suffix: optional string to identify a suffix other than '_DELTAS' for your database change-log table
...: additional parameters to specify the primary table location in the database if needed, e.g. schema = 'my_schema', catalog = 'some_catalog'

Value

returns a DT to the shiny ui environment, and as side-effect returns a chg_log_tbl and an editable db_tbl specified by namespace

Examples

if (FALSE) {
con <- pool::dbPool(DBI::dbConnect(RSQLite::SQLite(), 'iris.db'))
cdr_make_db_tbls(con, iris)
server <- function(input, output, session){
             r_tbl <- cdr_manage_db_tbls('iris', 'UID', con, session)
             output$iris <- DT::renderDT(r_tbl()) }
ui <- shiny::fluidPage(DT::DTOutput('iris'))
shiny::shinyApp(ui,server)
}