# NOT RUN {
if (requireNamespace("DBI", quietly = TRUE) && requireNamespace("RSQLite", quietly = TRUE)) {
# Example: clear one of a or b in any row where both are set.
# Land random selections early to avoid SQLite bug.
my_db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
d <- rq_copy_to(
my_db,
'd',
data.frame(i = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10),
a = c(0, 0, 1, 1, 1, 1, 1, 1, 1, 1),
b = c(0, 1, 0, 1, 1, 1, 1, 1, 1, 1),
r = runif(10),
edited = 0),
temporary=TRUE, overwrite=TRUE)
program <- if_else_block(
testexpr = qe((a+b)>1),
thenexprs = c(
if_else_block(
testexpr = qe(r >= 0.5),
thenexprs = qae(a %:=% 0),
elseexprs = qae(b %:=% 0)),
qae(edited %:=% 1)))
print(program)
optree <- extend_se(d, program)
cat(format(optree))
sql <- to_sql(optree, my_db)
cat(sql)
print(DBI::dbGetQuery(my_db, sql))
# Why we need to land the random selection early
# for SQLIte:
q <- "SELECT r AS r1, r AS r2 FROM (
SELECT random() AS r FROM (
SELECT * from ( VALUES(1),(2) )
) a
) b"
print(DBI::dbGetQuery(my_db, q))
DBI::dbDisconnect(my_db)
}
# }
Run the code above in your browser using DataCamp Workspace