In beerda/crudtable: A Shiny toolkit for Create-Read-Update-Delete Operations on Data

knitr::opts_chunk$set(echo = FALSE)


readExample <- function(filename, from=NULL, to=NULL) {
    cont <- readLines(paste0('inst/examples/', filename))

    if (!is.null(from)) {
        from <- grep(paste0('^# ', from, '\\. '), cont)
        from <- from[1]
        from <- from + 2
        while (trimws(cont[from]) == '' && from < length(cont)) {
            from <- from + 1
        }
    } else {
        from <- 1
    }

    if (!is.null(to)) {
        to <- grep(paste0('^# ', to, '\\. '), cont)
        to <- to[1]
        to <- to - 2
        while (trimws(cont[to]) == '' && to > 1) {
            to <- to - 1
        }
    } else {
        to <- length(cont)
    }

    cont <- cont[seq.int(from, to)]

    cat('```\n')
    cat(cont, sep='\n')
    cat('```\n')
}

crudtable

crudtable is an R package that makes it easy to develop an editable data table in Shiny web applications. With crudtable, the following operations may be easily achieved:

• CRUD - Create, Rread, Update and Delete of data records in DT DataTable and a modal edit dialog window;
• validation - ensuring the correct format of the user input;
• database access - storing the data into a database via the standardized DBI package for R or to a file.

Live Demo

See the live demo of the crudtable package.

Getting Started

To install the latest development version from GitHub:

install.packages("remotes")
remotes::install_github("beerda/crudtable")

A Minimal Working Example

A minimal Shiny app that uses crudtable:

readExample('minimal/app.R')

First, a Data Access Object (DAO) is created with dataFrameDao. DAO is a list structure that provides data access functions to the crudTable user interface. In this example, a simple DAO is created that works with an in-memory data frame CO2. Alternatively, an SQL database may be connected with crudtable's sqlDao DAO.

The UI part consists of crudTableUI that uses DT's DataTable to view the dataset. The crudtable UI also provides the New record, Edit record and Delete record buttons.

The server part consists of the call of the crudTable module that connects the crudTableUI with the DAO.

An Advanced Example

All the aspects and capabilities of the crudtable package will be shown in this advanced example, which covers:

• access to an SQLite data table;
• custom input form user interface;
• validation of the user input;
• how to store values that are, rather than directly entered by the user, obtained programmatically.

First of all, let us import all the needed packages:

readExample('advanced/app.R', NULL, 1)

We need DBI and RSQLite for database access, and shinyjs for JavaScript support.

Next, we initialize the in-memory SQLite database engine and register the connection cleanup hook on stop of Shiny. We also create an empty data frame df with columns: date, service, amount, discount, total and paid. This data frame is saved into SQLite as table 'invoice'. We also create a Data Access Object (DAO) dao by calling the sqlDao() function:

readExample('advanced/app.R', 1, 2)

Note also the typecast argument of the sqlDao() call: it causes the internally numeric attribute date to be type casted into Date. Such workaround is needed because the DBI interface does not support such complex data types as Date.

For our convenience, we also create a constant list of service prices that will be used to populate the select box with values:

readExample('advanced/app.R', 2, 3)

We also want a custom edit dialog window with some pre-defined values and well defined ranges for numeric inputs. We also add two read only input lines that will present some computed values to the user. For that, we use the disabled() function of the shinyjs package. Note also the namespacing of the input IDs by the ns() function, which is mandatory:

readExample('advanced/app.R', 3, 4)

After the edit form UI is defined, we need to create the server part of the form handler. Since we want to perform a lot of custom functionality, we code the server part in two steps. First, a default form server handler is initialized by calling the formServerFactory() function:

readExample('advanced/app.R', 4, 5)

formServerFactory() requires dao and a definition of validators. Validator is a mechanism for restricting the input to certain criteria. If the user insert invalid input, an error message is shown and the edit form dialog can not be submitted. In the piece of code above, we define two types of validators: a custom validator bound to the amount data input, which tests the oddness of the value. The second validator is filledValidator that ensures the data inputs are filled. filledValidator is bound to all data inputs - we call names(dao$getAttributes()) instead of enumerating names of all data columns.

Now we can define our server-side handler of the edit form. First, defaultFormServer handler must be called, which returns a list of useful reactive values and triggers. After that, we can provide an observer that computes the read only inputs of the form. Note that we need to observe the res$loadTrigger() here, which triggers everytime the data get loaded into the edit form. This ensures that the computed values are initialized properly too. Note also that the server-side handler must return the res, which is the result of defaultFormServer:

readExample('advanced/app.R', 5, 6)

And that's nearly all. The last step is the initialization of the Shiny app. We use crudTableUI on the client side and we call the crudTableServer function on the server side. The latter gets dao, myFormUI and myFormServer as arguments. Note also that the crudTableServer function returns a reactive value that changes everytime the CRUD table widget changes the data. That reactive value can be used to trigger update of output widgets that rely on the data, as can be seen below.

readExample('advanced/app.R', 6, NULL)

Note that it is not needed to call the useShinyjs() function in the UI of the Shiny application since the crudtable package does it internally by itself.

The complete advanced example is as follows:

readExample('advanced/app.R')

Enjoy.

beerda/crudtable documentation built on July 13, 2020, 2:16 p.m.