Apply corrections/derivations to a db table

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 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

Value

tbl_sql() object, referencing the modified table object.

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.

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)
#> # Source:   SQL [6 x 2]
#> # Database: sqlite 3.38.5 []
#>     cyl  gear
#>   <dbl> <dbl>
#> 1     6     4
#> 2     6     4
#> 3     4     4
#> 4     6     3
#> 5     8     3
#> 6     6     3

# 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)
#> # Source:   SQL [6 x 2]
#> # Database: sqlite 3.38.5 []
#>     cyl  gear
#>   <dbl> <dbl>
#> 1     6    10
#> 2     6    10
#> 3     4     0
#> 4     6    10
#> 5     2     3
#> 6     6    10

# 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)