## ----setup, include=FALSE----------------------------------------------------- library(knitr) opts_chunk$set( echo = TRUE, error = Sys.getenv("IN_PKGDOWN") != "true" || (getRversion() < "3.5"), # FIXME: Bring back when relational.fit clone is up eval = FALSE ) knit_print.data.frame <- function(x, ...) { print(head(x, 3)) if (nrow(x) > 3) { cat("Showing 3 out of", nrow(x), "rows.\n") } invisible(x) } registerS3method("knit_print", "data.frame", "knit_print.data.frame") ## ----------------------------------------------------------------------------- # library(DBI) # # con <- dbConnect( # RMariaDB::MariaDB(), # host = "relational.fit.cvut.cz", # port = 3306, # username = "guest", # password = "relational", # dbname = "sakila" # ) # # res <- dbSendQuery(con, "SELECT * FROM film WHERE rating = 'G'") # df <- dbFetch(res, n = 3) # dbClearResult(res) # # head(df, 3) ## ----------------------------------------------------------------------------- # res <- dbSendQuery(con, "SELECT * FROM film") # while (!dbHasCompleted(res)) { # chunk <- dbFetch(res, n = 300) # print(nrow(chunk)) # } # dbClearResult(res) ## ----quote-------------------------------------------------------------------- # # safe_id <- dbQuoteIdentifier(con, "rating") # safe_param <- dbQuoteLiteral(con, "G") # # query <- paste0("SELECT title, ", safe_id, " FROM film WHERE ", safe_id, " = ", safe_param) # query # # res <- dbSendQuery(con, query) # dbFetch(res) # dbClearResult(res) ## ----------------------------------------------------------------------------- # id <- "rating" # param <- "G" # query <- glue::glue_sql("SELECT title, {`id`} FROM film WHERE {`id`} = {param}", .con = con) # # df <- dbGetQuery(con, query) # head(df, 3) ## ----params------------------------------------------------------------------- # params <- list("G") # safe_id <- dbQuoteIdentifier(con, "rating") # # query <- paste0("SELECT * FROM film WHERE ", safe_id, " = ?") # query # # res <- dbSendQuery(con, query, params = params) # dbFetch(res, n = 3) # dbClearResult(res) ## ----multi-param-------------------------------------------------------------- # q_params <- list("G", 90) # query <- "SELECT title, rating, length FROM film WHERE rating = ? AND length >= ?" # # res <- dbSendQuery(con, query, params = q_params) # dbFetch(res, n = 3) # dbClearResult(res) ## ----dbbind------------------------------------------------------------------- # res <- dbSendQuery(con, "SELECT * FROM film WHERE rating = ?") # dbBind(res, list("G")) # dbFetch(res, n = 3) # dbBind(res, list("PG")) # dbFetch(res, n = 3) # dbClearResult(res) ## ----bind_quotestring--------------------------------------------------------- # res <- dbSendQuery(con, "SELECT * FROM film WHERE rating = ?") # dbBind(res, list(c("G", "PG"))) # dbFetch(res, n = 3) # dbClearResult(res) ## ----bind-multi-param--------------------------------------------------------- # q_params <- list(c("G", "PG"), c(90, 120)) # query <- "SELECT title, rating, length FROM film WHERE rating = ? AND length >= ?" # # res <- dbSendQuery(con, query, params = q_params) # dbFetch(res, n = 3) # dbClearResult(res) ## ----disconnect--------------------------------------------------------------- # dbDisconnect(con) ## ----------------------------------------------------------------------------- # library(DBI) # con <- dbConnect(RSQLite::SQLite(), ":memory:") # # dbWriteTable(con, "cars", head(cars, 3)) # # dbExecute( # con, # "INSERT INTO cars (speed, dist) VALUES (1, 1), (2, 2), (3, 3)" # ) # # rs <- dbSendStatement( # con, # "INSERT INTO cars (speed, dist) VALUES (4, 4), (5, 5), (6, 6)" # ) # dbGetRowsAffected(rs) # dbClearResult(rs) # # dbReadTable(con, "cars") ## ----------------------------------------------------------------------------- # dbDisconnect(con) ## ----------------------------------------------------------------------------- # con <- dbConnect(RSQLite::SQLite(), ":memory:") # # dbWriteTable(con, "cash", data.frame(amount = 100)) # dbWriteTable(con, "account", data.frame(amount = 2000)) # # withdraw <- function(amount) { # # All operations must be carried out as logical unit: # dbExecute(con, "UPDATE cash SET amount = amount + ?", list(amount)) # dbExecute(con, "UPDATE account SET amount = amount - ?", list(amount)) # } # # withdraw_transacted <- function(amount) { # # Ensure atomicity # dbBegin(con) # # # Perform operation # withdraw(amount) # # # Persist results # dbCommit(con) # } # # withdraw_transacted(300) ## ----------------------------------------------------------------------------- # dbReadTable(con, "cash") # dbReadTable(con, "account") ## ----------------------------------------------------------------------------- # withdraw_if_funds <- function(amount) { # dbBegin(con) # withdraw(amount) # # Rolling back after detecting negative value on account: # if (dbReadTable(con, "account")$amount >= 0) { # dbCommit(con) # TRUE # } else { # message("Insufficient funds") # dbRollback(con) # FALSE # } # } # # withdraw_if_funds(5000) # dbReadTable(con, "cash") # dbReadTable(con, "account") ## ----error = TRUE------------------------------------------------------------- # withdraw_safely <- function(amount) { # dbWithTransaction(con, { # withdraw(amount) # if (dbReadTable(con, "account")$amount < 0) { # stop("Error: insufficient funds", call. = FALSE) # } # }) # } # # withdraw_safely(5000) # dbReadTable(con, "cash") # dbReadTable(con, "account") ## ----------------------------------------------------------------------------- # dbDisconnect(con)