Skip to contents

Check validation rules on the database

Source: R/check_rules.R
check_rules.Rd

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

Value

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

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.

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

Examples

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

check_rules(person, rules, key = "id")
#> 
#> Testing rule: 'V1: age - 18 >= -1e-08'...
#>  - Did you mean '18L' instead of '18'?
#> ...works!
#> 
#> 
#> ***************************************************
#> ** This method returns a data.frame with the sql code.
#> ** Please assign the return value to inspect it.
#> *****************************************************

# use the result of check_rules to find out more on the translation
res <- check_rules(person, rules, key = "id")
#> 
#> Testing rule: 'V1: age - 18 >= -1e-08'...
#>  - Did you mean '18L' instead of '18'?
#> ...works!
#> 
#> 
#> ***************************************************
#> ** This method returns a data.frame with the sql code.
#> ** Please assign the return value to inspect it.
#> *****************************************************

print(res[-4])
#>   name               rule working
#> 1   V1 age - 18 >= -1e-08    TRUE
writeLines(res$sql)
#> SELECT `id`, 'V1' AS `rule`, 1 AS `fail`
#> FROM (SELECT `id`, `age`
#> FROM (SELECT *
#> FROM `dbplyr_004`
#> LIMIT 6))
#> WHERE (`age` - 18.0 < -1e-08)
#> UNION ALL
#> SELECT `id`, 'V1' AS `rule`, NULL AS `fail`
#> FROM (SELECT `id`, `age`
#> FROM (SELECT *
#> FROM `dbplyr_004`
#> LIMIT 6))
#> WHERE (((`age`) IS NULL))