Show generated sql

Writes the generated sql to a file or command line. The script contains ALTER and UPDATE statements and can be used for documentation purposes.

Usage

dump_sql(x, table, con = NULL, file = stdout(), ...)

Arguments

x

dcmodify::modifier() object with rules to be written

table

either a dplyr::tbl() object or a character with table name

con

optional, when table is a character, a dbi connection.

file

to which the sql will be written.

...

not used

Value

character sql script with all statements.

Details

Note that when this script is run on the database it will change the original table. This differs from the default behavior of dcmodify which works on a (temporary) copy of the table.

Furthermore, it seems wise to wrap the generated SQL in a transaction when apply the SQL code on a database.

See also

Other sql translation: modifier_to_sql()

Examples

# load modification rules and apply:
library(dcmodify)

con <- DBI::dbConnect(RSQLite::SQLite(), dbname=system.file("db/person.db", package="dcmodifydb"))
person <- dplyr::tbl(con, "person")

rules <- modifier(.file = system.file("db/corrections.yml", package="dcmodifydb"))
print(rules)
#> Object of class modifier with 6 elements:
#> M1: nochildlabor
#>   if (age < 16) income = 0
#> 
#> M2: longyear
#>   if (year < 25) year = year + 2000
#> 
#> M3: smoker
#>   if (cigarettes > 0) smokes = "yes"
#> 
#> M4: nosmoke
#>   if (smokes == "no") cigarettes = 0
#> 
#> M5: ageclass
#>   ageclass <- if (age < 18) "child" else "adult"
#> 
#> M6: gender
#>   gender <- switch(toupper(gender), F = "F", V = "F", M = "M", "NB")
#> 

# show sql code generated from the rules.
dump_sql(rules, person)
#> -- -------------------------------------
#> -- Generated with dcmodifydb, do not edit
#> -- dcmodify version: 0.1.9
#> -- dcmodifydb version: 0.3.1
#> -- dplyr version: 1.0.9
#> -- dbplyr version: 2.2.0
#> -- from: '/Users/runner/work/_temp/Library/dcmodifydb/db/corrections.yml'
#> -- date: 2022-06-17
#> -- -------------------------------------
#> 
#> 
#> ALTER TABLE `person`
#> ADD `ageclass` TEXT;
#> 
#> -- M1: nochildlabor
#> -- Children are not allowed to work, so can not have income.
#> -- R expression: if (age < 16) income = 0
#> UPDATE `person`
#> SET `income` = 0.0
#> WHERE `age` < 16.0;
#> 
#> -- M2: longyear
#> -- Convert 2 digits year into 4 digits.
#> -- R expression: if (year < 25) year = year + 2000
#> UPDATE `person`
#> SET `year` = `year` + 2000.0
#> WHERE `year` < 25.0;
#> 
#> -- M3: smoker
#> -- If you smoke cigarettes you are a smoker...
#> -- R expression: if (cigarettes > 0) smokes = "yes"
#> UPDATE `person`
#> SET `smokes` = 'yes'
#> WHERE `cigarettes` > 0.0;
#> 
#> -- M4: nosmoke
#> -- If you dont smoke, the (unknown) number of cigarettes is zero
#> -- R expression: if (smokes == "no") cigarettes = 0
#> UPDATE `person`
#> SET `cigarettes` = 0.0
#> WHERE `smokes` = 'no';
#> 
#> -- M5: ageclass
#> -- Derive ageclass using the age variable
#> -- R expression: ageclass <- if (age < 18) "child" else "adult"
#> UPDATE `person`
#> SET `ageclass` = 'child'
#> WHERE `age` < 18.0;
#> 
#> UPDATE `person`
#> SET `ageclass` = 'adult'
#> WHERE NOT(`age` < 18.0);
#> 
#> -- M6: gender
#> -- Map the labels for gender to M/F/NB
#> -- R expression: gender <- switch(toupper(gender), F = "F", V = "F", M = "M", "NB")
#> UPDATE `person`
#> SET `gender` = CASE UPPER(`gender`) WHEN ('F') THEN ('F') WHEN ('V') THEN ('F') WHEN ('M') THEN ('M') ELSE ('NB') END
#> ;