Skip to contents

Replace documents and their rows in several tables, in one transaction

Source: R/write.R
replace_documents.Rd

Writes a batch of documents together with their rows in related tables (authors, texts, files, classification codes...). For every document of the batch, the rows of every given table are deleted, then the new rows inserted, all in one transaction. A document collected again is thus replaced as a whole: an author removed at the source disappears from the author table instead of lingering.

Usage

replace_documents(
  con,
  tables,
  id_table = names(tables)[1],
  id_col = "doc_id",
  log_table = "ingestion_log",
  script = NA_character_,
  source = NA_character_,
  input = NA_character_
)

Arguments

con

A DBI connection with write access.

tables

Named list of data frames, named after the tables. Each must have the identifier column.

id_table

Character. The table that defines the batch of documents (its identifiers must be unique). Defaults to the first element of tables.

id_col

Character. Identifier column shared by all tables.

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 documents written, invisibly.

Details

Rows of a related table for a document that is not in id_table are not deleted: the batch is defined by id_table only.

See also

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

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 document (doc_id VARCHAR, title VARCHAR)")
#> [1] 0
DBI::dbExecute(con, "CREATE TABLE author (doc_id VARCHAR, name VARCHAR)")
#> [1] 0
replace_documents(con, list(
  document = data.frame(doc_id = "ecb:1", title = "A speech"),
  author = data.frame(doc_id = "ecb:1", name = c("X", "Y"))
), source = "ecb")
#> ✔ 1 document written (document: 1, author: 2).

# Collected again, the speech has one author left.
replace_documents(con, list(
  document = data.frame(doc_id = "ecb:1", title = "A speech"),
  author = data.frame(doc_id = "ecb:1", name = "X")
), source = "ecb")
#> ✔ 1 document written (document: 1, author: 1).
DBI::dbGetQuery(con, "SELECT * FROM author")
#>   doc_id name
#> 1  ecb:1    X
db_disconnect(con)