modify-ANY-modifier-method: Apply corrections/derivations to a db table

In dcmodifydb: Modifying Rules on a DataBase

Apply corrections/derivations to a db table

Description

Change records in a database table using modification rules specified in a modifier() object. This is the main function of package dcmodifydb. For more information see the vignettes.

Usage

## S4 method for signature 'ANY,modifier'
modify(
  dat,
  x,
  copy = NULL,
  transaction = !isTRUE(copy),
  ignore_nw = FALSE,
  ...
)

Arguments

dat	tbl_sql() object, table in a SQL database
x	dcmodify::modifier() object.
copy	if TRUE (default), modify copy of table
transaction	if TRUE use one transaction for all modifications.
ignore_nw	if TRUE non-working rules are ignored
...	unused

Details

The modification rules are translated into SQL update statements and executed on the table.

Note that

• by default the updates are executed on a copy of the table.

• the default for transaction is FALSE when copy=TRUE and TRUE when copy=FALSE

• when transaction = TRUE and a modification fails, all modifications are rolled back.

Value

tbl_sql() object, referencing the modified table object.

Examples

library(DBI)
library(dcmodify)
library(dcmodifydb)

# silly modification rules
m <- modifier( if (cyl == 6)  gear <- 10
             , gear[cyl == 4] <- 0  # this R syntax works too :-)
             , if (gear == 3) cyl <- 2
             )

# setting up a table in the database
con <- dbConnect(RSQLite::SQLite())
dbWriteTable(con, "mtcars", mtcars[,c("cyl", "gear")])
tbl_mtcars <- dplyr::tbl(con, "mtcars")

# "Houston, we have a table"
head(tbl_mtcars)

# lets modify on a temporary copy of the table..
# this copy is only visible to the current connection
tbl_m <- modify(tbl_mtcars, m, copy=TRUE)


# and gear has changed...
head(tbl_m)

# If one certain about the changes, then you can overwrite the table with the changes
tbl_m <- modify(tbl_mtcars, m, copy=FALSE)

dbDisconnect(con)

dcmodifydb documentation built on June 17, 2022, 5:05 p.m.