Skip to contents

Replace the rows of a table, in one transaction

Source: R/write.R
replace_rows.Rd

Deletes the rows of a table that the new data replace, then inserts the new data, in one transaction: if anything fails, the table is left as it was. Rerunning an import or a collection on the same records is therefore idempotent: it does not create duplicates.

The rows to delete are selected either by key (keys: every row whose key appears in data) or by a SQL condition (where, e.g. "source = 'ecb'" to replace everything a collector wrote). Replacing the whole table must be asked explicitly with where = "TRUE".

Usage

replace_rows(
  con,
  table,
  data,
  keys = NULL,
  where = NULL,
  log_table = "ingestion_log",
  script = NA_character_,
  source = NA_character_,
  input = NA_character_
)

Arguments

con

A DBI connection with write access.

table

Character. Name of an existing table.

data

A data frame with the columns of the table. Missing columns are added as NA; columns that the table lacks are an error, so that a schema change is noticed rather than silently dropped.

keys

Character or NULL. Key columns: rows of the table whose key is in data are deleted.

where

Character or NULL. SQL condition selecting the rows to delete.

log_table

Character or NULL. Table where one row per write is logged (created if needed): time, script, source, input file, table, rows written and rows deleted. NULL disables the log.

script, source, input

Character. Recorded in the log: the script that writes, the source of the records, the input file.

Value

The number of rows written, invisibly.

Details

The log answers the question "where does this row come from, and when did it change?", which a database built over months by several scripts otherwise loses. The number of deleted rows is logged too: a collector that deletes many more rows than it writes has a problem.

See also

Other writing: append_raw_records(), check_unique(), replace_documents()

Examples

con <- db_connect(tempfile(fileext = ".duckdb"))
#> duckdb is storing downloaded extensions and secrets under ~/.duckdb:
#> ℹ /home/agoutsmedt/.duckdb
#> This persists across sessions and is shared with the DuckDB CLI and other clients.
#> ℹ Run duckdb(shared_home = FALSE) to use a temporary directory instead.
#> ℹ See ?duckdb_storage for details and alternatives.
DBI::dbExecute(con, "CREATE TABLE author (doc_id VARCHAR, name VARCHAR)")
#> [1] 0
authors <- data.frame(doc_id = c("a:1", "a:1", "a:2"), name = c("X", "Y", "Z"))
replace_rows(con, "author", authors, keys = "doc_id", script = "example")
#> ✔ author: 3 rows written, 0 replaced.

# A correction of document a:1 replaces its two rows by one.
replace_rows(con, "author", data.frame(doc_id = "a:1", name = "X"), keys = "doc_id")
#> ✔ author: 1 row written, 2 replaced.
DBI::dbGetQuery(con, "SELECT * FROM author")
#>   doc_id name
#> 1    a:2    Z
#> 2    a:1    X
DBI::dbGetQuery(con, "SELECT table_name, n_rows, n_deleted FROM ingestion_log")
#>   table_name n_rows n_deleted
#> 1     author      3         0
#> 2     author      1         2
db_disconnect(con)