--- title: "Editing database tables" output: rmarkdown::html_vignette: self_contained: false vignette: > %\VignetteIndexEntry{Editing database tables} %\VignetteEngine{knitr::rmarkdown} %\VignetteEncoding{UTF-8} --- ```{r setup, include = FALSE} has_db <- requireNamespace("RSQLite", quietly = TRUE) && requireNamespace("dbplyr", quietly = TRUE) && requireNamespace("dplyr", quietly = TRUE) knitr::opts_chunk$set( collapse = TRUE, comment = "#>", eval = has_db ) screenshot <- function(file) { path <- file.path("images", file) if (!file.exists(path)) { path <- paste0( "https://raw.githubusercontent.com/DillonHammill/DataEditR/master/", "vignettes/images/", file ) } knitr::include_graphics(path) } ``` **DataEditR** can edit tables stored in a database as easily as data in your R session. You connect with [DBI](https://dbi.r-dbi.org/), pass a [dbplyr](https://dbplyr.tidyverse.org/) lazy table to `data_edit()`, and the editor loads the rows, lets you edit them with all the usual tools, and writes your changes back to the database in a single transaction when you are done. ## 1. Connect to a database Any database with a `DBI` backend can be used. A few common examples are shown below; see the documentation of each backend for the connection details it needs. ```{r, eval = FALSE} # SQLite - a database in a single file con <- DBI::dbConnect(RSQLite::SQLite(), "cars.sqlite") # DuckDB con <- DBI::dbConnect(duckdb::duckdb(), "cars.duckdb") # PostgreSQL con <- DBI::dbConnect( RPostgres::Postgres(), dbname = "garage", host = "db.example.com", user = "me", password = Sys.getenv("PGPASSWORD") ) # MySQL or MariaDB con <- DBI::dbConnect(RMariaDB::MariaDB(), dbname = "garage", host = "db.example.com", user = "me", password = Sys.getenv("MYSQL_PASSWORD")) # SQL Server and other databases through ODBC con <- DBI::dbConnect(odbc::odbc(), dsn = "garage") ``` In this article we will use a small SQLite database created from `mtcars`: ```{r, message = FALSE} library(DataEditR) library(dplyr) con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") DBI::dbExecute(con, " CREATE TABLE cars ( id INTEGER PRIMARY KEY, model TEXT, mpg REAL, cyl INTEGER, hp INTEGER ) ") DBI::dbAppendTable(con, "cars", data.frame( model = rownames(mtcars), mpg = mtcars$mpg, cyl = as.integer(mtcars$cyl), hp = as.integer(mtcars$hp) )) ``` ## 2. Edit a table Pass a lazy table created with `dplyr::tbl()` to `data_edit()`: ```{r, eval = FALSE} data_edit(tbl(con, "cars")) ``` Use `filter()`, `select()` and `arrange()` to choose the rows and columns to edit; only the matching rows are loaded into the editor, so you can work on a small part of a very large table. Columns calculated with `mutate()` are shown for reference but are read-only (they show a lock next to their name), because they do not exist in the table. ```{r, eval = FALSE} cars <- tbl(con, "cars") %>% filter(cyl >= 6) %>% mutate(kpl = round(mpg * 0.425, 1)) data_edit(cars) ``` ```{r, echo = FALSE, fig.align = "center", out.width = "100%", eval = TRUE} screenshot("db-editor.png") ``` Everything else works as it does for data frames: edit cells, paste ranges, fill down, add or delete rows, undo and redo, filter rows under the column headers, review changes and revert them. Adding, removing and renaming columns is disabled, because that would change the structure of the table. ## 3. How rows are matched Rows are matched to the database through the table's **primary key**. The key is detected automatically for SQLite, DuckDB and databases that describe their keys in `information_schema` (such as PostgreSQL). For other databases, or tables without a primary key, use `db_table()` to say which column(s) uniquely identify each row: ```{r} cars_db <- db_table(tbl(con, "cars"), key = "id") cars_db ``` The key columns must be part of the data you edit (don't drop them with `select()`), and their values must be unique in the loaded rows. `db_table()` also accepts a connection and a table name, which is useful for tables in another schema: ```{r, eval = FALSE} # a table in another schema data_edit(db_table(con, table = DBI::Id(schema = "sales", table = "orders"), key = "order_id")) ``` Queries that combine tables, such as joins or unions, raw SQL queries and tables without a primary key cannot be written back automatically, so they are opened **read-only** (you can still filter, sort and look through them). Use `db_table()` to say which table the edits belong to; columns from other tables are then read-only: ```{r, eval = FALSE} owners <- tbl(con, "owners") cars_and_owners <- left_join(tbl(con, "cars"), owners, by = c("id" = "car_id")) data_edit(db_table(cars_and_owners, table = "cars", key = "id")) ``` ## 4. Tables too big to load The table does not have to fit in memory. `data_edit()` loads at most 100,000 rows at a time (set `limit` in `db_table()`, or `options(DataEditR.db_rows = )`), and the header and the `Rows` button in the toolbar show how many rows are loaded out of how many there are: ```{r, eval = FALSE} flights <- tbl(con, "flights") # 30 million rows data_edit(flights) # loads 100,000 of them # or choose the rows before opening the editor data_edit(filter(flights, carrier == "UA", dep_delay > 60)) ``` Click `Rows` to load other rows: the rows matching a filter, written like `dplyr::filter()` (e.g. `carrier == "UA" & dep_delay > 60`), sorted by a column, at most a number of rows. The database does the filtering and sorting (the filter is translated to SQL by `dbplyr`), and the dialog shows how many rows match before you load them. If you have changed rows that are not saved yet, you can save them to the database first or discard them. ```{r, echo = FALSE, fig.align = "center", out.width = "100%"} screenshot("db-rows.png") ``` Filters can use column names, values and the functions that formulas may use (see the user guide), so users of a shiny app cannot run other code or SQL. Column summaries, the filters under the column headers, the `Filters` panel and *Find* work on the loaded rows. Files that are too big for memory can be explored the same way through DuckDB, which reads Parquet and CSV files without loading them; such queries are read-only: ```{r, eval = FALSE} con <- DBI::dbConnect(duckdb::duckdb()) data_edit(tbl(con, sql("SELECT * FROM read_parquet('trips/*.parquet')"))) ``` ## 5. Committing changes Nothing is written to the database while you edit. When you press `Done`, the editor shows a summary of the rows that will be updated, inserted and deleted and asks you to confirm. Choose `Keep editing` to go back to the editor or `Commit` to write the changes. The `Code` panel shows the `rows_update()`, `rows_append()` and `rows_delete()` calls that make the same changes. ```{r, echo = FALSE, fig.align = "center", out.width = "100%", eval = TRUE} screenshot("db-commit.gif") ``` ```{r, echo = FALSE, fig.align = "center", out.width = "100%", eval = TRUE} screenshot("db-commit.png") ``` All changes are written in a **single transaction**, so either every change is saved or none are: * edited rows are updated with `UPDATE ... WHERE = ...`, only setting the columns you changed; * removed rows are deleted by their key; * new rows are inserted. If you leave the key of a new row empty the database generates it (e.g. an auto-incrementing `id`). After a successful commit `data_edit()` returns the edited rows as a data frame. Press `Cancel` to close the editor without writing anything. ```{r, eval = FALSE} cars_edited <- data_edit(tbl(con, "cars")) ``` To keep a record of who changed which rows, and why, pass a function to `log` that writes the change log to a table in the same database. Rows are named by their key in the log, and the notes added in the `History` tab are included: ```{r, eval = FALSE} data_edit(tbl(con, "cars"), log = function(log) DBI::dbWriteTable(con, "cars_log", log, append = TRUE)) ``` ## 6. When someone else changes the data Databases are often shared, so the rows you are editing may be changed by someone else before you press `Done`. To make sure you never overwrite their changes without knowing, every update checks that the values you changed are still the values that were loaded, and every delete checks that the whole row is unchanged (optimistic concurrency). If any row was changed or removed in the meantime, the whole commit is rolled back, nothing is written and you are told how many rows conflicted. Close the editor, load the data again to see the latest values and redo your edits. If you would rather overwrite rows regardless of changes made by others, turn the check off with `check = FALSE`: ```{r, eval = FALSE} data_edit(db_table(tbl(con, "cars"), check = FALSE)) ``` ## 7. Code to reproduce the changes Set `code = TRUE` (or supply the name of an R script) to get the `dplyr` code that makes the same changes to the database with `rows_delete()`, `rows_update()` and `rows_append()`. The `Code` tab in the editor shows the same code as you edit. ```{r, eval = FALSE} cars <- tbl(con, "cars") data_edit(cars, code = TRUE) ``` ```{r, eval = FALSE} library(dplyr) cars <- tbl(con, "cars") rows_delete(cars, tibble::tibble(id = c(4)), by = "id", unmatched = "ignore", copy = TRUE, in_place = TRUE) rows_update(cars, tibble::tibble(id = c(1), mpg = c(21.5)), by = "id", unmatched = "ignore", copy = TRUE, in_place = TRUE) rows_update(cars, tibble::tibble(id = c(2), model = c("Mazda RX4 Wagon")), by = "id", unmatched = "ignore", copy = TRUE, in_place = TRUE) rows_append(cars, tibble::tibble(model = c("Ford Escort"), mpg = c(30.1), cyl = c(4)), copy = TRUE, in_place = TRUE) ``` Tables in other schemas are referred to with `dbplyr::in_schema()` in the generated code. ## 8. Databases in shiny apps The `dataEdit` module edits database tables in your own shiny applications too. Pass the lazy table (or a `db_table()`) to `dataEditServer()` and call `dataEditCommit()` when the user wants to save; it writes the changes in a single transaction, raises an error of class `"editr_conflict"` if rows were changed by someone else, and reads the rows back from the database afterwards. `dataEditChanges()` summarises what will be written, e.g. to enable a *Save* button or ask for confirmation. ```{r, eval = FALSE} server <- function(input, output, session) { cars <- dataEditServer("cars", data = tbl(con, "cars")) observeEvent(input$save, { res <- tryCatch(dataEditCommit(cars), editr_conflict = function(e) e) if (inherits(res, "editr_conflict")) { showNotification(conditionMessage(res), type = "error") } else { # who changed what, next to the data DBI::dbWriteTable(con, "cars_log", res$log, append = TRUE) showNotification(sprintf("%d updated, %d added, %d removed", res$updated, res$inserted, res$deleted)) } }) } ``` `dataEditChanges()` counts the rows that will be updated, inserted and deleted, which makes it easy to enable a *Save* button only when there is something to save, or to ask the user to confirm: ```{r, echo = FALSE, fig.align = "center", out.width = "100%", eval = TRUE} screenshot("shiny-save-confirm.png") ``` See `vignette("shiny-modules")` for the complete app. ```{r, include = FALSE} DBI::dbDisconnect(con) ```