Previously, we have created a business intelligence web app, PineApple Sales Dashboard. The app connects a back-end SQLite database and a front-end dashboard using RSQLite and Shiny. We summarized how we built this business intelligence dashboard in this post and in these sequenced video tutorials (open to NYU community).

As an extension to the PineApple Sales Dashboard, this Shiny app (shown below) is a prototype of a database management platform. It allows users to view tables, create tables, update tables, insert entries, and, depending on the user role, delete tables. While the business intelligence dashboard focuses on the front-end user interactions, this app addresses the needs in back-end database management.

The app below is embedded on this webpage and you may interact with it directly.

In this post, we share the key features of this app and what we feel most excited about building this app up.

The packages we have used to create the app include DBI, RSQLite, DT, shiny, shinyjs, shinythemes, shinyWidgets, shinydashboard, shinyauthr, and shinyFeedback.

Dashboard layout

The app uses the package shinydashboard to lay out a dashboard structure. As shown below, it consists of a header, a sidebar and a main body.

Figure 1. Structure of the dashboard layout: header, sidebar and body.

Figure 1. Structure of the dashboard layout: header, sidebar and body.

Code below generates the header, sidebar and main body right away. We don’t need to know the technical details of HTML, CSS or JavaScript to achieve this.

library(shiny)
library(shinydashboard)

ui <- dashboardPage(
  dashboardHeader(),
  dashboardSidebar(),
  dashboardBody()
)

server <- function(input, output) { }

shinyApp(ui, server)

Collapsible sidebar layout

We can set the sidebar to be collapsed by default and to be expanded after user authorization.

observe({
  if(credentials()$user_auth) {
    shinyjs::removeClass(selector = "body", class = "sidebar-collapse")
  } else {
    shinyjs::addClass(selector = "body", class = "sidebar-collapse")
  }
})

Using box as container

The fundamental unit of containers in shinydashboard is box. A box can be thought of as a block to define a content area on a webpage.

For example, in a fluid page layout, elements in the tab Update Tables are grouped into two fluidRows. The top fluidRow contains a box to display and hide a “Note”. The bottom fluidRow contains three boxes for users to rename table, rename column and add column respectively.

Figure 3. Using box to create containing blocks in a fluid page layout.

Figure 3. Using box to create containing blocks in a fluid page layout.

The code to lay out the structure above is provided below.

output$tab3UI <- renderUI({
  fluidPage(
    fluidRow(
      box(width = 12, collapsible = TRUE, title = "Note:", "")
    ),
    fluidRow(
      box(title = "Rename Table", width = 4, solidHeader = TRUE, status = "primary",
          selectInput(),
          wellPanel(
            textInput(),
            actionButton())
           ),
      box(title = "Rename Column", width = 4, solidHeader = TRUE, status = "primary",
          selectInput(),
          wellPanel()
          ),
      box(title = "Add Column", width = 4, solidHeader = TRUE, status = "primary",
          selectInput(),
          wellPanel()
          )
    )
  )
})

For another example, in a sidebar layout, all elements in the tab View Tables are contained in a box. Inside this box, the next layer is a sidebarLayout that consists of a sidebarPanel and a mainPanel. In the sidebarPanel, we use another box that is collapsible to display and hide “Database Info”.

Figure 4. Using box to create containing blocks in a sidebar layout.

Figure 4. Using box to create containing blocks in a sidebar layout. 

output$tab1UI <- renderUI({
box(width = NULL, status = "primary",
    sidebarLayout(
      sidebarPanel(
        box(width = 12,
            collapsible = TRUE,
            div(style = "height: 15px; background-color: white;"),
            title = "Database Info:",
            p("")),
        selectInput(),
        textOutput(outputId = "tab_intro"),
        tags$head(tags$style("#tab_intro{font-size: 15px;font-style: italic;}"))
        ),
      mainPanel(
        h4(strong("Table Preview")),
        dataTableOutput(outputId = "sel_table_view")
      )
    )
  )
})

Authentication

The app leverages the package shinyauthr to add a user authentication layer and to build dynamic user interfaces based on user information.

The app provides two types of access roles that need to be authenticated in the login stage: admin and manager. Both roles are able to view tables, create tables, update tables, and insert entries. Beyond those, the admin role is able to delete tables, while the manager role is not.

Login UI

Users may log in as one of the two roles.

Figure 5. Login.

Figure 5. Login.

The Login form and button is created with shinyauthr::loginUI().

loginUI(id = "login", 
        title = "Please log in", 
        user_title = "User Name",
        pass_title = "Password", 
        login_title = "Log in",
        error_message = "Invalid username or password!",
        additional_ui = NULL)

Logout UI

Once users have logged in, they will see the logout button in the header.

Figure 6. Logout.

Figure 6. Logout.

The Logout button is created with shinyauthr::logoutUI().

logoutUI(id = "logout", 
         label = "Log out", 
         icon = NULL, 
         class = "btn-danger",
         style = "color: white;")

Login credentials

In order to make logging in and logging out function, we followed and adapted the example provided by the author of the package shinyauthr.

user data

First of all, we created a data frame user_base to store user data.

The line of code password_hash = sapply(c("pass1", "pass2"), sodium::password_store) means that user passwords are encrypted with a hashing algorithm with the sodium package to protect stored passwords from brute-force attacks. Later, passwords will be decrypted when login is requested.

user_base <- data.frame(
  username = c("user1", "user2"),
  password = c("pass1", "pass2"), 
  password_hash = sapply(c("pass1", "pass2"), sodium::password_store), 
  permissions = c("manager", "admin")
)

logging in and out

We then put the login and logout UI as well as the user data table in the ui function.

Note that we must initiate the package shinyjs with shinyjs::useShinyjs() in our UI code for things to work.

library(shiny)
library(shinydashboard)
library(shinyjs)
library(shinyauthr)

ui <- dashboardPage(
  dashboardHeader(
    title = "",
    tags$li(),
    tags$li(shinyauthr::logoutUI("logout"))
    ),
  
  dashboardSidebar(),  
  
  dashboardBody(
    shinyjs::useShinyjs(),
    tags$head(tags$style(".table{margin: 0 auto;}"),
              tags$script(src="https://cdnjs.cloudflare.com/ajax/libs/iframe-resizer/3.5.16/iframeResizer.contentWindow.min.js",type="text/javascript"),
              includeScript("returnClick.js")),
    shinyauthr::loginUI("login"),
    uiOutput("user_table")
    )
)

The script returnClick.js ensures successful login when a user clicks the Enter key.

reactive output

In the server function, we created login and logout modules that would react to user action and user information.

callModule(login) calls the login module. callModule(logout) calls the logout module. Here, callModule() invokes a Shiny module; login() and logout() are Shiny module server functions.

server <- function(input, output, session){
  logout_init <- callModule(shinyauthr::logout, 
                            id = "logout", 
                            reactive(credentials()$user_auth))

  credentials <- callModule(shinyauthr::login, 
                            id = "login", 
                            data = user_base,
                            user_col = username,
                            pwd_col = password_hash,
                            sodium_hashed = TRUE,
                            log_out = reactive(logout_init()))
  
  output$user_table <- renderUI({
    if(credentials()$user_auth) return(NULL)
    fluidRow(column(4,
                    p("Please use the usernames and passwords ...", 
                      class = "text-center", style = "font-size: 15px;"),
                    br(),
                    renderTable({user_base[, -3]}), offset = 4
    )
    )
  })
}

When we call the login module, it will return a reactive list containing two elements: user_auth and info. user_auth is a boolean indicating whether there has been a successful login or not. info is the user data provided to the function, filtered to the row, to match the logged in username. The initial values of user_auth and info are FALSE and NULL respectively. When user_auth is FALSE, info is NULL.

When the user gives the correct username and password, user_auth will become TRUE. info will then become the row of data associated with that user. (Later, in the section below, we will see how user info controls the content displayed in the sidebar menu and the body of the dashboard, based on user permission and other variables.)

From the logout, reactive(credentials()$user_auth) hides or shows the logout button. When user_auth is TRUE, the logout button will be visible. Clicking the logout button will reset user_auth back to FALSE, which will hide the button and show the login panel again.

From the login, reactive(logout_init()) triggers a user logout using the returned reactive from logout.

The line of code if(credentials()$user_auth) return(NULL) in renderUI means that the login information user_base will only be rendered in a table before a user is logged in; it will be invisible after a user is logged in.

Dynamic UI reacting to user inputs

Several UI outputs of this app are reactive to user inputs. For instance, the welcome message in the sidebar will either say “Welcome manager!” or “Welcome admin!” based on the authenticated user role. X number of forms will be rendered on the fly as a user sets a number in the numeric input. When a user selects a table to view from the drop-down menu, a short message describing that table will be displayed. Values of several drop-down menus will be updated dynamically in response to user actions (e.g. choices of tables will be added to the list of menu items when a user creates new tables).

There are several more cases like these in this app, which we discuss below. To make these things work, we will be frequently using the function renderUI() in server.R in conjunction with the function uiOutput() in ui.R. renderUI() generates calls to UI functions and makes the results appear in a predetermined place in the UI. We will also be using the function paste() often to concatenate what is known and what is dynamically generated.

Embedding {user role} in the welcome message

In the sidebar, after user authentication, a welcome message will be displayed. Based on the user role, it will either say “Welcome manager!” or “Welcome admin!”, as shown below.

Figure 7. Dynamically displaying the user role in the welcome message.Figure 7. Dynamically displaying the user role in the welcome message.

Figure 7. Dynamically displaying the user role in the welcome message.

How does it work? As defined in the server function, after user authentication req(credentials()$user_auth), user role {user_info()$permissions} will be embedded in the welcome message.

user_info <- reactive({credentials()$info})
  
output$welcome <- renderText({ 
  req(credentials()$user_auth)
  paste("Welcome ","<font color=\"#f3b404\"><b>", {user_info()$permissions}, "</b></font>","!") 
})

Displaying {text output} based on user selection

In the tab View Tables, in the sidebar panel below the drop-down menu, a short message describing the selected table will be displayed. The description is dynamic, responsive to which table a user selects from the database.

Figure 8. Dynamically displaying table descriptions based on user selection.

To make it work, we first create a table_intro list that stores the descriptions of two kinds of tables. These include the original tables in the database, and tables that will be created by any user, named other.

table_intro <- list(custs = "Customer information, including customer id ...", 
                    prods_i = "Product information, including product id ...",
                    stores = "Store information, including store id ...",
                    orders = "Orders information, including order id ...",
                    order_items = "Order items information ...",
                    other = "This is a table created by you or other users.")

We then use renderUI() to render reactive HTML in a textOutput(). In the textOutput (output id “tab_intro”), if a selected table (input id “sel_table_1”) is one of the existing tables c("custs","order_items","orders","prods_i","stores"), its description will be rendered accordingly. Otherwise, the description will be “This is a table created by you or other users.”

output$tab1UI <- renderUI({
  req(credentials()$user_auth)
  box(width = NULL, status = "primary",
      sidebarLayout(
        sidebarPanel(
          box(),
          selectInput(
            inputId = "sel_table_1",
            label = "Tables in Database",
            choices = dbListTables(db),
            selected = "custs"
          ),
          textOutput(outputId = "tab_intro"),
          tags$head(tags$style("#tab_intro{
                               font-size: 15px;
                               font-style: italic;
                               }"))
          ),
        mainPanel()
      )
  )
})
  
output$sel_table_view <- renderDataTable()
  
output$tab_intro <- renderText(
  if (input$sel_table_1 %in% c("custs","order_items","orders","prods_i","stores"))
  {table_intro[[input$sel_table_1]]}
  else {table_intro$other}
)

Rendering {X} number of forms

In the tab Create Tables, users have the option to set how many columns that a new table will have in the numeric input Number of columns. Once a user fills in a number X, X forms will be rendered dynamically and immediately. In these forms, a user can further set the column name in the text input Column name and set the column type in the select input Column type.

For instance, if we fill in “4” in the numeric input Number of columns, 4 forms will be rendered where we can define each column’s name and type. For the title of each form, the index of the form will also be attached, which will look like “Column i”. In this case, they are “Column 1”, “Column 2”, “Column 3”, and “Column 4”. See the screenshot below.

Figure 9. Dynamically rendering forms.

Figure 9. Dynamically rendering forms.

Now we will describe how to generate these dynamic forms.

There are two groups of UI outputs here. The first group of UI outputs take care of user inputs. We have a text input Table name, a numeric input Number of columns for users to input the number of dynamic forms to be rendered below, and an action button Create table to submit the form. We use box() to house these elements.

output$tab4UI <- renderUI({
  req(credentials()$user_auth)
  box(width = NULL, status = "primary",
      textInput(inputId = "table_name", label = "Table name"),
      numericInput(inputId = "ncols", label = "Number of columns", 1, min = 1),
      uiOutput(outputId = "cols"),
      actionButton(inputId = "create_table", label = "Create table", class = "btn-info", style = "")
  )
})

In the second group of UI outputs, the dynamic forms will be rendered with uiOutput() and renderUI(). uiOutput() creates an HTML output element; renderUI() renders reactive HTML.

For each of these forms, we have a text input Column name and a select input Column type.

output$cols <- renderUI({
  req(input$ncols >= 1)
  cols <- vector("list", input$ncols)
  for (i in seq_len(input$ncols)) {
    cols[[i]] <- box(
      title = paste("Column", i), width = 6, solidHeader = TRUE, status = "primary",
      textInput(inputId = paste0("colName", i), label = "Column name"),
      selectInput(inputId = paste0("colType", i), label = "Column type", 
                  choices = c("NUMERIC", "VARCHAR(255)","BOOLEAN","DATE")
      )
    )
  }
  cols
})

In the code chunk above, the number of forms to be rendered is defined by the user in the numeric input (id = “ncols”). The program then runs through each form cols[[i]] from the first to the last element in the user input ncols.

for (i in seq_len(input$ncols)) {
  cols[[i]] <- box()
}

Each form cols[[i]] is rendered dynamically. The title of each form is also dynamically generated; the index i of each form is embedded in the title with paste("Column", i).

Additionally, the input ids associated with each output are dynamic. The input id of the column name is generated by paste0("colName", i); the input id of the column type is generated by paste0("colType", i). For both inputs, the form index is built into the ids so that each output is mapped to each input.

Updating {values} of select inputs

The choices in a drop-down menu, or values of a select input, can be dynamically updated in response to user actions. For instance, after we create a table and add it to the database, as shown in Figure 9, the choices in several drop-down menus will also need to be updated. Therefore, users will always have the up-to-date list of table names and column names to choose from when they view tables, update tables and columns, insert entries, and delete tables.

For example, in View Tables, the name of the newly created table needs to be added to the drop-down menu Tables in Database, where users select a table to view.

Figure 10. Dynamically updating values of a select input to view tables.

Figure 10. Dynamically updating values of a select input to view tables.

In Update Tables, the name of the new table and its fields need to be added to the drop-down menus in the forms Rename Table, Rename Column and Add Column so that the choices are up to date.

Figure 11. Dynamically updating values of select inputs to rename a table, rename a column and add a column.

Figure 11. Dynamically updating values of select inputs to rename a table, rename a column and add a column.

Likewise, in Insert Entries and Delete Tables, the new table is added to the lists.

Figure 12. Dynamically updating values of a select input to choose a table to insert entries.

Figure 12. Dynamically updating values of a select input to choose a table to insert entries.

Figure 13. Dynamically updating values of a select input to delete a table.

Figure 13. Dynamically updating values of a select input to delete a table.

To update the values of the select inputs as described above, we use the function updateSelectInput(session, inputId, choices).

For instance, after we create a new table in the database db, we have an updated list of table names dbListTables(db), including the name of the new table. Therefore, all select inputs whose values are tables names will need to be updated.

For instance, the code block below updates the values of the select input for viewing tables (input id = “sel_table_1”).

updateSelectInput(session, "sel_table_1", choices = dbListTables(db))

There are several other select inputs whose values are tables names. These are select inputs for deleting table, renaming column, adding column, renaming table, and inserting values. But in these select inputs, we only allow for newly created tables to be modified. Therefore, in the code block below, we use setdiff() to remove the names of the original tables in the database from the choices in those select inputs.

for (sel_input in c("sel_table_2","sel_table_3","sel_table_3_i","sel_table_3_ii","sel_table_5")){
  updateSelectInput(session, sel_input, choices = setdiff(dbListTables(db),
                                      c("custs","order_items","orders","prods_i","stores")))
}

When we need to update select inputs of column names, the choices are colnames() instead of dbListTables(). The example below updates column names in the resulting data extracted from the database, stored in d.

updateSelectInput(session, "sel_col_3", choices = colnames(d))

Rendering {input} types matched with predefined data types

In Insert Entries, after we select a table, the column name predefined by user will appear in the title of the form together with its predefined column type. Both have been set by users in Create Tables.

Figure 14. Rendering input types matched with predefined data types.

Figure 14. Rendering input types matched with predefined data types.

There are four preset data types in Create Tables: NUMERIC, VARCHAR(255), BOOLEAN and DATE. In Insert Entries, users may further insert records for each predefined column, as shown above. If a predefined column type is NUMERIC, users will see a numeric input to insert numbers. If a predefined column type is BOOLEAN, users will see radio buttons with values “True” or “False”. If a predefined column type is string, users will see a text input to type characters. Finally, if a predefined column type is DATE, users will see a date input to pick a date.

Code chunk below generates the input type for users to insert entries that is matched with the predefined data type of a column, and embeds the column type in the title of the form.

output$values <- renderUI({
# UI outputs rendered in the place that has an uiOutput id "values"
  
  req(isTruthy(input$sel_table_5))

  values <- list()
  d <- dbGetQuery(
    conn = db,
    statement = paste0('SELECT * from ',input$sel_table_5)
  )
  typ <- dbGetQuery(
    conn = db, statement = paste0('PRAGMA table_info(',input$sel_table_5,')')
  )
  for (col in colnames(d)) {
    typ_i = typ$type[typ$name==col]
    values[[col]] <- box(
      title = paste0(as.character(col),' (',typ_i,')'), 
      width = 6, solidHeader = TRUE, status = "primary",
      
      if (typ_i == "BOOLEAN") {radioButtons(inputId = paste0("value_", col), label = "Value",
                                            c("TRUE","FALSE") )}
      else if (typ_i == "NUMERIC" | typ_i == "FLOAT" |
               typ_i == "INTEGER" | typ_i == "NUM" ) 
      {numericInput(inputId = paste0("value_", col), label = "Value", value = 0)} 
      else if (typ_i == "DATE") {dateInput(inputId = paste0("value_", col),
                                           label = "Value",
                                           value = "2020-12-01") }
      else {tagList(useShinyFeedback(),
                    textInput(inputId = paste0("value_", col), label = "Value"))}
    )
  }
  values
})

PRAGMA table_info statement

As shown in the code above, first we make sure there are user inputs in the select input Select Table (id = “sel_table_5”) with req(isTruthy(input$sel_table_5)). We then select data from our database and store the returned data in a result table d.

Next, we query information about the table we selected, stored in typ, using SQLite statement PRAGMA [database.]table_info( table_name );.

The result from the PRAGMA table_info statement contains one row for each column in the table we are asking about. Columns in the returned data include the column name, data type, whether or not the column can be NULL, and the default value for the column. The “pk” column is zero for columns that are not part of the primary key, and is the index of the column in the primary key for columns that are part of the primary key.

cid name type notnull dflt_value pk
0 id NUMERIC 0 NA 0
1 name VARCHAR(255) 0 NA 0
2 pass BOOLEAN 0 NA 0
3 date DATE 0 NA 0

embedding column type in the form title

For each column in the returned data d, we retrieve its data type and add it to the form title.

for (col in colnames(d)) {
  typ_i = typ$type[typ$name==col]
  values[[col]] <- box(
    title = paste0(as.character(col),' (',typ_i,')'), 
    ...
  )
}

rendering the correct input type

Finally, the correct input type is rendered, which is matched with the predefined data type, where users may insert entries for the column.

Making sure the SQL parts work in Shiny

There are numerous ways to break our app, especially when we are not careful enough with the SQL parts.

Figure 15. Exploits of a Mom. Source: https://xkcd.com/327/

Figure 15. Exploits of a Mom. Source: https://xkcd.com/327/

Invalid table name

In our app, there are several scenarios where the app may fail to function properly. For instance, when creating a new table or rename an existing table, have we got the table name right? In our app, a modal box with an alert message will pop up if 1) the table already exists in the database, 2) the table name is blank, or if 3) it is an invalid table name.

Figure 16. Alert for invalid table name.

Figure 16. Alert for invalid table name. 

if (tolower(input$table_name) %in% tolower(dbListTables(db)) |
    !isTruthy(input$table_name) |
    grepl("^[a-zA-Z_][a-zA-Z0-9_]*$",input$table_name) == FALSE) {
  showModal(modalDialog(
    title = "Invalid table name",
    "You get this message possibly because:
    1) the table already exists;
    2) the table name is blank;
    or 3) this is an invalid table name.",
    footer = modalButton("OK"), easyClose = TRUE ) )
  return()
}

Additionally, there might be no value in the input to indicate number of columns, or the input value of column number is smaller than one. We have to make sure none of these happens once a user clicks the action button (id = “create_table”) to create the table.

# make sure there is value in the input 
  if (!isTruthy(input$ncols)) {
    showModal(modalDialog(
      title = "Invalid table name",
      "Please type in the right column number.",
      footer = modalButton("OK"), easyClose = TRUE ) )
  }
  
# make sure the input value of column number is larger than one  
  if (input$ncols < 1) {
    showModal(modalDialog(
      title = "No columns",
      "Each table must have one or more columns.",
      footer = modalButton("OK"), easyClose = TRUE
    )) 
  }

Invalid column name

When creating a new table, renaming a column, or adding a coulmn, an alert message will pop up if the column name is invalid. It is triggered when 1) one or more fields are blank, 2) one or more fields contain invalid SQLite column name(s), 3) there are duplicate column names, or when 4) one or more fields conflict with a SQLite keyword.

Figure 17. Alert for invalid column name.

Figure 17. Alert for invalid column name. 

# gather all the colnames into a list
  col_names_list = list()
  for (i in seq_len(input$ncols)) {
    col_names_list <- c(col_names_list,input[[paste0("colName", i)]])
  }
  
# make sure the column name is valid  
  if ( any(col_names_list == "") | 
       sum(duplicated(col_names_list)) > 0 |
       any(grepl("^[a-zA-Z_][a-zA-Z0-9_]*$",col_names_list) == FALSE) |
       any(tolower(col_names_list) %in% sqlite_kw_lo) ) {
    showModal(modalDialog(
      title = "Invalid column name",
      "You get this message possibly because: 
      1) the column name already exists;
      2) the field is blank;
      3) this is an invalid SQLite column name;
      or 4) the field name conflicts with a SQLite keyword.",
      footer = modalButton("OK"), easyClose = TRUE
    ))
    return()
  }

Compiling SQLite queries

Finally, we have to make sure the SQLite statements are compiled correctly.

observeEvent(input$create_table, {
  # make sure table name is not the same as an existing table in the database, blank, or invalid
  if () {}
  # make sure there is value in the input 
  if () {}
  # make sure the input value of column number is larger than one  
  else if () {}  
  else {
    # gather all the column names into a list
    # make sure the column name is valid  
    if () {}
      
    # compile query
    query <- paste0('CREATE TABLE ',input$table_name,' (')
    for (i in seq_len(input$ncols)) { 
      query <- paste0(query,input[[paste0("colName", i)]],' ',input[[paste0("colType", i)]],',')
    }
    query <- paste0(str_sub(query,1,-2),')')
    dbGetQuery(
      conn = db,
      statement = query )
    # if successful, update inputs
    updateNumericInput(session, "ncols", value = "1")
    updateTextInput(session, "table_name", value = "")
    for (sel_input in c("sel_table_2","sel_table_3","sel_table_3_i","sel_table_3_ii","sel_table_5")) {
      updateSelectInput(session, sel_input, 
                        choices = setdiff(dbListTables(db),
                                          c("custs","order_items","orders",
                                            "prods_i","stores")))
    }
    updateSelectInput(session, "sel_table_1", choices = dbListTables(db))
    showModal(modalDialog(
      title = "Success",
      "The table has been successfully created.",
      footer = modalButton("OK"), easyClose = TRUE ) )
  }
})

Web development in Shiny

This is a “Praise Shiny” list. As you may have heard many times, you don’t need to know HTML, CSS and JavaScript to build a web app with Shiny. Indeed, the powerful Shiny functions and add-on packages have made creating a Shiny web app flexible and intuitive.

Layout and style

By default, Shiny uses Bootstrap, the most popular HTML/CSS framework for developing responsive, mobile first projects on the web.

layout features

Shiny provides a number of layout features to arrange the components of an application, made available via Bootstrap, many of which we have seen earlier. These include functions such as fluidPage(), fluidRow(), sidebarLayout(), sidebarPanel(), mainPanel(), and wellPanel(), and the package shinydashboard that we rely on to structure the UI of this app.

HTML tags

We used the HTML tags in our Shiny UI for custom HTML and CSS. With the help of the tags functions, we were able to write HTML using R functions.

dashboardHeader(
  title = span("Database Management Platform", style = "font-size: 20px"),
  titleWidth = 300,
  tags$li(class = "dropdown",tags$a("PineApple Database",style = "color: white")),
  tags$li(class = "dropdown",
          tags$a(img(src = "pineapple_logo.png", height = 20),
                 href = "https://yundai.shinyapps.io/2020-business-intelligence/",
                 title = "Check out the Pineapple Sales Dashboard!")),
  tags$li(class = "dropdown", style = "padding: 8px;",
          shinyauthr::logoutUI("logout"))
)

We were also able to add custom CSS class with the tags functions.

The code chunck below appeared where we discussed displaying dynamic text outputs based on user selection.

textOutput(outputId = "tab_intro"),
tags$head(tags$style("#tab_intro{
                   font-size: 15px;
                   font-style: italic;
                   }")

Shiny provides many HTML builder functions. The tags environment contains convenience functions for all valid HTML5 tags.

Font Awesome

In Shiny, we can create icons with the function icon(). Icons are drawn from the Font Awesome Free and Glyphicons libraries.

UI Inputs

We allowed user inputs with several UI input functions, including selectInput(), textInput(), numericInput(), and dateInput() to create drop-down menus, text inputs, numeric inputs and date inputs respectively.

The values of these inputs can be updated with functions updateSelectInput(), updateTextInput(), updateNumericInput(), and updateDateInput() respectively.

We also used the function actionButton() to create action buttons.

Writing JavaScript

In Shiny, we get to enjoy the beauty of JavaScript without needing to learn its technical details.

shinyjs

We used the package shinyjs to help us perform common useful JavaScript operations in our app.

observe({
  if(credentials()$user_auth) {
    shinyjs::removeClass(selector = "body", class = "sidebar-collapse")
  } else {
    shinyjs::addClass(selector = "body", class = "sidebar-collapse")
  }
})

req(), isTruthy()

We have used the functions req() and isTruthy() to check for required values many times.

isTruthy() evaluates whether a value is missing or available, the user input is available, or whether the button has been clicked.

req() can be called like a statement before attempting operations using the required values, or can be used to wrap an expression that must be truthy.

For instance, we used req(credentials()$user_auth) to set the code to only run after a successful login. In one example, we used req(credentials()$user_auth) inside the function renderTable() to ensure that the table showing the returned user information is only rendered when user_auth is TRUE.

We used req(input$ncols >= 1) to set the code to only run when the user input of the number of columns is larger than or equal to one.

We used req(isTruthy(input$sel_table_5)) to set the code to only run when there is user input in the select input Select Table in the tab Insert Values.