confront_tbl_sparse: Create a sparse confrontation query
In validatedb: Validate Data in a Database using 'validate'

Description Usage Arguments Details Value See Also Examples

Create a sparse confrontation query. Only errors and missing are stored. This can be useful alternative to confront_tbl() which stores all results of a tbl validation in a table with length(rules) columns and nrow(tbl) rows. Note that the result of this function is a (lazy) query object that still needs to be executed in the database, e.g. with dplyr::collect(), dplyr::collapse() or dplyr::compute().

1	confront_tbl_sparse(tbl, x, key = NULL, union_all = TRUE, check_rules = TRUE)

`tbl`	`dbplyr::tbl_dbi()` table in a database, retrieved with `tbl()`
`x`	`validate::validator()` object with validation rules.
`key`	`character` with key column name.
`union_all`	if `FALSE` each rule is a separate query.
`check_rules`	if `TRUE` it is checked which rules 'work' on the db.

The return value of the function is a list with:

$query: A dbplyr::tbl_dbi() object that refers to the confrontation query.
$errors: The validation rules that are not working on the database
$working: A logical with which expression are working on the database.
$exprs: All validation expressions.

A object with the necessary information: see details

Other validation: tbl_validation-class, values,tbl_validation-method

# create a table in a database
income <- data.frame(id = letters[1:2], age=c(12,35), salary = c(1000,NA))
con <- dbplyr::src_memdb()
tbl_income <- dplyr::copy_to(con, income, overwrite=TRUE)
print(tbl_income)

# Let's define a rule set and confront the table with it:
rules <- validator( is_adult   = age >= 18
                  , has_income = salary > 0
                  , mean_age   = mean(age,na.rm=TRUE) > 20
                  )

# and confront!
cf <- confront(tbl_income, rules)
print(cf)
summary(cf)

# Values (i.e. validations on the table) can be retrieved like in `validate` 
# with`type="matrix"` (simplify = TRUE)
values(cf, type = "matrix")

# But often this seems more handy:
values(cf, type = "tbl")

# We can see the sql code by using `show_query`:
show_query(cf)

# identical
show_query(values(cf, type = "tbl"))

# adding a key often is handy in a database
cf <- confront(tbl_income, rules, key = "id")
print(cf)
values(cf, type="tbl")

# sparse results in db
cf_sparse <- confront(tbl_income, rules, sparse=TRUE)
values(cf_sparse, type="tbl")