---
title: "Edit tables in a relational database - dm package"
date: "`r Sys.Date()`"
output:
  rmarkdown::html_vignette
vignette: >
  %\VignetteEncoding{UTF-8}
  %\VignetteIndexEntry{How to: dm package integration for database editor.}
  %\VignetteEngine{knitr::rmarkdown}
---


If it is your first time using
`editbl`, make sure to first read the introductory vignette
on how to work with relational databases (`vignette("howto_relational_db")`).

This document describes how make use of the
[{dm} package](https://CRAN.R-project.org/package=dm). This package is
useful to extract relational data models out of a database into R. Which can
help in setting up a correct configuration for `editbl`.

```{r echo = TRUE, results = 'hide'}
library(dplyr)
library(shiny)
library(editbl)
library(dm)
```

## Creating a database connection

The first thing you need is a database connection. Here we connect to an
[sqlite](https://en.wikipedia.org/wiki/SQLite) file, which is a portable
database format.

```{r echo = TRUE, results = 'hide'}
tmpFile <- tempfile(fileext = ".sqlite")
file.copy(system.file("extdata", "chinook.sqlite", package = 'editbl'), tmpFile) 
conn <-   DBI::dbConnect(
    dbname = tmpFile,
    drv = RSQLite::SQLite()
)
```

## Setting up the data model

```{r echo = TRUE, results = 'hide'}
dm <- dm::dm_from_con(conn, learn_keys = FALSE)
```

For some databases this is all you need to do.
Currently `dm` can not (yet) learn the keys from SQLite.
Thus we still manually have to specify the primary and foreign keys.
```{r echo = TRUE, results = 'hide'}
dm <- dm %>%
    dm_add_pk(Artist, ArtistId) %>%
    dm_add_pk(Album, AlbumId) %>%
    dm_add_pk(Customer, CustomerId) %>%
    dm_add_pk(Employee, EmployeeId) %>%
    dm_add_pk(Genre, GenreId) %>%
    dm_add_pk(Invoice, InvoiceId) %>%
    dm_add_pk(InvoiceLine, InvoiceLineId) %>%
    dm_add_pk(MediaType, MediaTypeId) %>%
    dm_add_pk(Playlist, PlaylistId) %>%
    dm_add_pk(PlaylistTrack, c(PlaylistId, TrackId)) %>%
    dm_add_pk(Track, TrackId)
    
```

```{r echo = TRUE, results = 'hide'}
dm <- dm %>%
    dm_add_fk(
      table = Album,
      columns = ArtistId,
      ref_table = Artist) %>%
    dm_add_fk(
      table = Invoice,
      columns = CustomerId,
      ref_table = Customer) %>%
    dm_add_fk(
      table = InvoiceLine,
      columns = InvoiceId,
      ref_table = Invoice) %>%
    dm_add_fk(
      table = InvoiceLine,
      columns = TrackId,
      ref_table = Track) %>%
    dm_add_fk(
      table = PlaylistTrack,
      columns = TrackId,
      ref_table = Track) %>%
    dm_add_fk(
      table = PlaylistTrack,
      columns = PlaylistId,
      ref_table = Playlist) %>%
    dm_add_fk(
      table = Track,
      columns = AlbumId,
      ref_table = Album) %>%
    dm_add_fk(
      table = Track,
      columns = MediaTypeId,
      ref_table = MediaType) %>%
    dm_add_fk(
      table = Track,
      columns = GenreId,
      ref_table = Genre)
```

## 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`:

```{r}
dm::dm_flatten_to_tbl(dm, "Album", .recursive = TRUE)
```
As 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.

```{r}
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.

```{r}
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()
      }
  )
}

```


```{r, screenshot.opts = list(vwidth = 700, vheight = 500, delay = 1), screenshot.alt = 'screenshots/howto_relational_db_dm_1.png'}
shiny::shinyApp( 
    ui = dbUI('id'),
    server =  function(input, output,session){
      dbServer('id', dm)
    })
```

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.