check_rules: Check validation rules on the database

In data-cleaning/validatedb: Validate Data in a Database using 'validate'

Check validation rules on the database

Description

Checks whether validation rules are working on the database, and gives hints on non working rules.

Usage

check_rules(tbl, x, key = NULL)

Arguments

tbl	dbplyr::tbl_dbi() table in a database, retrieved with tbl()
x	validate::validator() object with validation rules.
key	character with key column name, must be specified

Details

validatedb translates validation rules using dbplyr on a database. Every database engine is different, so it may happen that some validation rules will not work. This function helps in finding out why rules are not working.

In some (easy to fix) cases, this may be due to:

• using variables that are not present in the table

• using a different value type than the column in the database, e.g.using an integer value, while the database column is of type "varchar".

• To debug your rules, a useful thing to do is first to test the rules on a small sub set of the table

• e.g.

tbl |> 
  head() |>          # debugging on db
  as.data.frame() |> # debugging "rules", do they work on a data.frame
  confront(rules, key = "id") |> 
  summary()

But it can also be that some R functions are not available on the database, in which case you have to reformulate the rule.

Value

data.frame with name, rule, working, sql for each rule.

Examples

person <- dbplyr::memdb_frame(id = letters[1:2], age = c(12, 20))
rules <- validator(age >= 18)

check_rules(person, rules, key = "id")

# use the result of check_rules to find out more on the translation
res <- check_rules(person, rules, key = "id")

print(res[-4])
writeLines(res$sql)

data-cleaning/validatedb documentation built on June 11, 2022, 4:33 p.m.