In eauleaf/crudr: Simplifies CRUD tasks including tracking changes of a db table

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

library(crudr)

Overview

This package is a (create-read-update-delete) CRUD app built on DT in R. It's a Shiny interface where users directly edit cells within the presented datatable, and the syncing between your user computer, the server, and the database happen immediately and automatically in the background as you exit the cell. The idea behind crudr is to make it easy for you to centrally maintain a single source of centralized data while at the same time maintaining a log of all changes and allowing controlled but distributed editing of a dataset to keep it continuously updated.

A Common Problem

Companies often have different departments with different spreadsheets that track the same information. For instance, let's say you have a bunch of store locations in your company. Ideally, someone in your company has all the updated information about those locations, e.g. the store's mailing address, phone number, email, manager, manager's email, etc. In most companies, that information actually resides in a lot of different places and the datasets almost always have errors because managers move around, store leases expire, etc. Further, Sally might be in charge of the Locations, but Jose knows the Manager info, etc. You need that data to be correct, joined, posted, and available for everyone, at any time time, but maintaining quality data is hard to do. This package helps to solve the distributed-data-maintenance and distributed-data-availability problem.

Goals for crudr

The goals with crudr are to:

1. Present data from a relational database through a DataTable in a Shiny app for viewing and management

2. Separate data viewers from data managers / administrators

3. Make it easy to control which admin users can update columns in a DB table

4. Keep a record of all changes made to the database table in a corresponding change log table

5. Allow more than one simultaneous editor without crashes

6. Be database agnostic

Other Notes

• Crudr works by synchronizing tables between the User's Computer, the Host Server, and your Database
• The code is built with Shiny modules so that you can manage and present several tables in the same app
• SQLite, Postgres, and Snowflake are the databases that I have tested
• The database checks periodically whether other admins have made changes to the data; if so, the UI and server refresh
• I would love if someone at Posit would improve the code or just take the code and make it their own. It'd be great if people could use this app broadly since this problem exists so often, but I don't know how to make it really robust since I'm a hobbyist.

Example App

Below is an example of a minimal app. This one is connected to a Postgres database for testing.

{width="100%"}

Getting Started

To install crudr, run:

remotes::install_github('eauleaf/crudr')

The Example is built from these packages, so you might want to install the below packages. Note: the package 'pool' must be the most updated version on CRAN.

install.packages(c(
  'shiny',
  'shinydashboard',
  'tidyverse',
  'lubridate',
  'DT',
  'pool',
  'here'
))

4 crudr functions

There are really only 4 crudr functions you need to know: cdr_make_db_tbls(), cdr_manage_db_tbls(), cdr_deliver_admin_tbl(), & cdr_deliver_chg_log_tbl().

1) cdr_make_db_tbls()

This function, cdr_make_db_tbls(), writes two tables into your database. You hand this function a tibble and a pool connection, and it'll write that table, as well as a corresponding change tracking table, into your database. If you already have the main table in your database, no problem. Run the same function with the same inputs and cdr_make_db_tbls() will just create the change tracking table. If you already have both tables, the function doesn't do anything except write some notes out to your R console saying that it's not going to mess with tables that already exist.

Note: For crudr to work, each row of the table you write must have a Unique ID that's recorded as a character. If you don't have a Unique ID key field in your table when , cdr_make_db_tbls() initially writes the table, then the function will automatically make one for you named "UID".

Example for cdr_make_db_tbls():

If you want to create a database on your machine called 'test.db', and you want to put into your database a table of flower data called 'iris', you'd write:

con <- pool::dbPool(DBI::dbConnect(RSQLite::SQLite(), 'test.db'))

Then you'd call the function with the above connection and a table to write. You can find this database by looking in your default directory with here::here().

cdr_make_db_tbls(con, iris)

This code will join the new column, 'UID', onto your iris data and push the table to your test database.

If you already created a Unique ID key field in your table, just tell the function the name of that field with cdr_make_db_tbls(con, iris, key_field = 'name_of_your_unique_id_field').

Okay, the function should have written to your database a new table called 'iris' and a second table called 'iris_DELTAS'. The table names are case sensitive. You can see the tables that the function wrote by running the following:

paste(pool::dbListTables(con), collapse = ', ')

You should see the printout: "iris" and "iris_DELTAS". These two tables are your original table, but with a 'UID' field, and a secondary table named similar to your first, but with '*_DELTAS'. The 'iris_DELTAS' deltas table is a change log. Any change you make to the primary table through the admin user interface, which we'll create below, gets recorded in the deltas table.

As a side note, if you want to remove the tables later, you can do that with:

pool::dbRemoveTable(con,'iris')
pool::dbRemoveTable(con,'iris_DELTAS')

Or, just delete the database 'test.db'.

2) cdr_manage_db_tbls()

This function, cdr_manage_db_tbls(), is the workhorse of the package. If you remember one function, this is the one. The function has only a handful of arguments. At a minimum, you have to tell this function:

1. What the name of your table is. In our example case it's, db_tbl_name = 'iris'.
2. The field with your Unique ID. In our example case it's, key_col = 'UID'.
3. What the database connection is. In our example case it's, conn_pool = con.

That's about it. There are other arguments you can change if you want to control who can update things in the administrator table, but you can just read the function details later for that info.

So, cdr_manage_db_tbls() returns 1 table directly and 2 tables invisibly.

The direct output table in the example app below goes into: iris_r_tbl, as in iris_r_tbl <- cdr_manage_db_tbls(). Because the output is a reactive table, you use the variable by acting like it's a function, i.e. by putting parentheses, '()', after the variable name. So, if you wanted to select some columns or filter some rows, you'd write, iris_r_tbl() %>% select(some_columns) %>% filter(some_rows), with the parentheses after 'iris_r_tbl', but otherwise it works just like with any other dataframe object.

The output table from cdr_manage_db_tbls(), in this case named, iris_r_tbl() is yours; you can do whatever you want with it. You can send it out in any format you like, change the column names, filter it, join it to something else, send it into a 'leaflet' map...whatever. It's an output to present to the masses. In fact, you don't even have to capture this output from cdr_manage_db_tbls() if you don't want to.

The 2 Invisible Tables

However, the 2 tables that cdr_manage_db_tbls() returns invisibly you have to pick up with the functions cdr_deliver_admin_tbl() & cdr_deliver_chg_log_tbl(). The tables these functions return are not like the direct output table. Rather, these tables are direct representations of what's in your database. Let's look at these 2 functions.

3) cdr_deliver_admin_tbl()

This function picks up your invisible administrator table already rendered in a DT object. The table is the primary database table that you wrote into your test database when you ran cdr_make_db_tbls(), so in our test case, cdr_deliver_admin_tbl('iris') will deliver 'iris' data for a data curator. That is, whoever maintains your primary dataset should have access to this admin table, and that person can make changes to the data held in the database via this output in Shiny. You call cdr_deliver_admin_tbl('iris') in the UI portion of Shiny. A full example is below in the section named CRUDR Example Code.

4) cdr_deliver_chg_log_tbl()

This function picks up your invisible history-of-changes table already rendered in a DT object. This table is the '*_DELTAS' database table that you wrote into your test database when you ran cdr_make_db_tbls(), so in our test case, cdr_deliver_chg_log_tbl('iris') will deliver a change history about changes that your data curators made to the table 'iris'. That is, crudr records every change that someone makes to your primary dataset within this change-log table. You don't need to do much with this table. It's just there to record which administrator did what to the database table, and when they made the change. You also call this function in the UI portion of Shiny. A full example is below in the section named CRUDR Example Code. This table should be empty right now, but let's change that.

A Tiny Shiny CRUD App

To manage the database tables, let's launch a bare-bones shiny app. We'll only need the single 'crudr' functions described above, cdr_manage_db_tbls. The other code all comes from packages shiny, shinydashboard, and DT. Copy the code below into your RStudio console, run it, and then play with your data.

CRUDR Example Code

con <- pool::dbPool(DBI::dbConnect(RSQLite::SQLite(), 'test.db'))

crudr::cdr_make_db_tbls(con, iris)

header <- shinydashboard::dashboardHeader(title = 'Tiny CRUDR Example')

sidebar <- shinydashboard::dashboardSidebar(
  shinydashboard::sidebarMenu(
    id = 'tabs',
    shinydashboard::menuItem(
      text = "Iris Data",
      startExpanded = TRUE,
      shinydashboard::menuSubItem("Administrator Table", tabName = "datr_editable", icon = shiny::icon('edit')),
      shinydashboard::menuSubItem("Change Log", tabName = "datr_change_log"),
      shinydashboard::menuSubItem("Iris End User View", tabName = "datr_end_usr")
    )))

body <- shinydashboard::dashboardBody(
  shinydashboard::tabItems(
    shinydashboard::tabItem(tabName = "datr_editable", crudr::cdr_deliver_admin_tbl('iris')),
    shinydashboard::tabItem(tabName = "datr_change_log", crudr::cdr_deliver_chg_log_tbl('iris')),
    shinydashboard::tabItem(tabName = "datr_end_usr", DT::DTOutput('iris_db_data'))
  ))

ui <- shinydashboard::dashboardPage(header, sidebar, body)


server <- function(input, output, session){

  iris_r_tbl <- crudr::cdr_manage_db_tbls(
   db_tbl_name = 'iris',
    key_col = 'UID',
    conn_pool = con,
    session = session,
    add_row_permission = T,
    del_row_permission = T,
    cell_edit_permission = T,
    lock_fields = c()
  )

  output$iris_db_data <- DT::renderDT(
    DT::formatDate(table   = DT::datatable(iris_r_tbl()),
                   columns = c('WHEN_EDITED_LAST'),
                   method  = 'toLocaleString')
  )

}


shiny::shinyApp(ui, server)

Make Some Changes

In the app above, you're the admin. So, go ahead and make some changes in this administrator table. Double-click on a cell or press 'F2' to enter edit mode, and then change the data. Your app will write your change to the database, update the change log table, write the change-log data to the database, and then await your next change. The 'Iris End User View' corresponds to the iris_r_tbl() direct output from the server. Make your direct output awesome, like add some buttons to it by dropping in some DT code:

DT::datatable(iris_r_tbl(), 
              extensions = 'Buttons', 
              options = list(dom = 'tB', buttons = c('copy', 'csv', 'excel','pdf')))

That's pretty much it. Close down the app. Then reopen it by calling the same app code again. The app should load the data from the database and all the changes that you made to the data should be there.

If you want to see how crudr acts with multiple admin users, open a second app and then go back and forth making some data changes in each app. When you make a change, crudr checks if anyone else made a change and refreshes if so.

User Control Changes

If you want to set admin permissions for specific people, you can do so in your shiny server with some code like this:

user <- ifelse(is.null(session$user), Sys.info()[['user']], session$user)

if (user == 'your.name'){
  okay_to_change = T
} else {
  okay_to_change = F
}

Then pass your 'okay_to_change' variable into the cdr_manage_db_tbls() permission argument(s) like 'cell_edit_permission = okay_to_change' or 'add_row_permission = okay_to_change'. A FALSE in these parameters makes the admin table just another table when a typical user opens it, but whoever signs is as 'your.name' can change the data in the database that everyone else sees. Alternatively, you could just write your code to not present to certain users the tables from cdr_deliver_admin_tbl() or cdr_deliver_chg_log_tbl() at all in the UI, it's up to you.

Other Useful Info

For a Postgres database connection, you can only use a pool connection with RPostgreSQL::PostgreSQL(), but RPostgres::Postgres() also works fine--just don't wrap it in pool::dbPool().

Example Postgres connection strings below.

RPostgreSQL::PostgreSQL():

con = pool::dbPool(
           DBI::dbConnect( 
             drv = RPostgreSQL::PostgreSQL(),
             dbname = "test",
             host = "localhost",
             port = "5432",
             user = your_dsn_uid,
             password = your_dsn_pwd
           )
)

RPostgres::Postgres():

con <- DBI::dbConnect(
  drv = RPostgres::Postgres(),
  dbname = "test",
  host = "localhost",
  user = your_dsn_uid,
  password = your_dsn_pwd
  )

To close your database connection, run: pool::poolClose(con) or DBI::dbDisconnect(con)

Possible Future Improvements

There are quite a few things I should to do to make crudr better. Maybe I'll get to these sometime.

1. Support for more databases, like sqlserver, oracle, etc.

2. When 2 or more admin users are making concurrent changes, update just the specific elements that the other person changed rather than refreshing the whole table.

3. Create a function "cdr_reconstruct_past_tbl(as_of = 'a prior Sys.time()')", where you hand a datetime to the function and the function returns a tibble of the database admin table as it was as of that particular point in time.

4. Figure out how to push data to the database asynchronously.

5. Write a bunch of tests for the functions.

6. Maybe add row lockout controls.

eauleaf/crudr documentation built on Jan. 29, 2024, 4:39 p.m.