Fully fletched table editor
A relational database consists out of many normalized tables. This is
a perfect model for storing data, since it avoids duplicate information.
However, it often leads to rather incomprehensible tables with a lot of
‘id’ columns. The goal of this editor is therefore to give people the
opportunity to edit a table in its ‘flat’ form. Meaning you join all
tables with additional information based on these ‘id’ keys. See also
this function of dm:
## # Source: SQL [?? x 4]
## # Database: sqlite 3.45.0 [/tmp/RtmpOGknNx/file24f4bc579781bd.sqlite]
## AlbumId Title ArtistId Name
## <int> <chr> <int> <chr>
## 1 1 For Those About To Rock We Salute You 1 AC/DC
## 2 2 Balls to the Wall 2 Accept
## 3 3 Restless and Wild 2 Accept
## 4 4 Let There Be Rock 1 AC/DC
## 5 5 Big Ones 3 Aerosmith
## 6 6 Jagged Little Pill 4 Alanis Morissette
## 7 7 Facelift 5 Alice In Chains
## 8 8 Warner 25 Anos 6 Antônio Carlos Jobim
## 9 9 Plays Metallica By Four Cellos 7 Apocalyptica
## 10 10 Audioslave 8 Audioslave
## # ℹ more rowsAs you can see, providing the ArtistName to a user is
way more convenient than just the ArtistId.
editbl::eDT can handle similar joins by its
foreignTbls argument. Let us define a function that
extracts the needed information from a dm object.
getForeignTbls <- function(dm, table){
dm_fks <- dm::dm_get_all_fks(dm)
dm_fks <- dm_fks[dm_fks$child_table == table,]
tbl_list <- dm::dm_get_tables(dm)
foreignTbls <- lapply(seq_len(nrow(dm_fks)), function(i){
r <- dm_fks[i,]
x <- tbl_list[r$child_table][[1]]
y <- dm::dm_flatten_to_tbl(dm, !!(r$parent_table), .recursive = TRUE)
child_fks <- unlist(r$child_fk_cols)
parent_fks <- unlist(r$parent_key_cols)
# Renaming of parent colums to avoid naming conflicts
# Done a bit heuristically here for convenience.
lookup <- parent_fks
names(lookup) <- child_fks
other_parent_cols <- setdiff(colnames(y), parent_fks)
names(other_parent_cols) <- paste(r$parent_table, other_parent_cols, sep = '.')
lookup <- c(lookup, other_parent_cols)
y <- y %>% dplyr::rename(all_of(lookup))
editbl::foreignTbl(
x = x,
y = y,
by = child_fks,
naturalKey = colnames(y)
)
})
foreignTbls
}Next, let’s use this to build a shiny app.
dbUI <- function(id) {
ns <- NS(id)
fluidPage(
uiOutput(outputId = ns('selectUI')),
eDTOutput(id = ns('DT'))
)
}
dbServer <- function(id, dm) {
moduleServer(
id,
function(input, output, session) {
ns <- session$ns
tables <- dm::dm_get_tables(dm)
output$selectUI <- renderUI({
selectInput(ns('table'), label = 'table', choices = names(tables))
})
data <- reactive({
req(input$table)
tables[input$table][[1]]
})
foreignTbls <- reactive({
req(input$table)
getForeignTbls(dm, input$table)
})
eDT(
id = "DT",
data = data,
foreignTbls = foreignTbls,
in_place = TRUE
)
invisible()
}
)
}
As you click the ‘edit’ button, you will notice you can now select rows from the referenced tables. This makes it easier to navigate compared to just having id’s to work with.