inst/doc/dittodb.R

In dittodb: A Test Environment for Database Requests

## ----include = FALSE----------------------------------------------------------
library(testthat)
library(dittodb)

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

db_mock_paths("dittodb")

## ----setup db, include = FALSE, eval = FALSE----------------------------------
#  # setup the DB used in the rest of the vignette
#  
#  con <- dbConnect(
#    RMariaDB::MariaDB(),
#    dbname = "nycflights",
#    host = "127.0.0.1",
#    username = "travis",
#    password = ""
#  )
#  
#  nycflights13_create_sql(con, schema = "nycflights13")
#  
#  # record interactions
#  
#  start_db_capturing()
#  out <- mean_delays("day")
#  out <- mean_delays("month")
#  stop_db_capturing()

## ----mean_delays--------------------------------------------------------------
library(DBI)

mean_delays <- function(group_col) {
  con <- dbConnect(
    RMariaDB::MariaDB(),
    dbname = "nycflights"
  )
  on.exit(dbDisconnect(con))

  query <- glue::glue(
    "SELECT {group_col}, AVG(arr_delay) as mean_delay from nycflights13.flights ",
    "WHERE arr_delay > 0 GROUP BY {group_col}"
  )

  return(dbGetQuery(con, query))
}

## ----mean_delays_rpostgres, eval = FALSE--------------------------------------
#  library(DBI)
#  
#  mean_delays <- function(group_col) {
#    con <- dbConnect(
#      RPostgres::Postgres(),
#      dbname = "nycflights"
#    )
#    on.exit(dbDisconnect(con))
#  
#    query <- glue::glue(
#      "SELECT {group_col}, AVG(arr_delay) as mean_delay from nycflights13.flights ",
#      "WHERE arr_delay > 0 GROUP BY {group_col}"
#    )
#  
#    return(dbGetQuery(con, query))
#  }

## ----mean_delays_rsqlite, eval = FALSE----------------------------------------
#  library(DBI)
#  
#  mean_delays <- function(group_col) {
#    con <- dbConnect(
#      RSQLite::SQLite(),
#      dbname = "nycflights"
#    )
#    on.exit(dbDisconnect(con))
#  
#    query <- glue::glue(
#      "SELECT {group_col}, AVG(arr_delay) as mean_delay from nycflights13.flights ",
#      "WHERE arr_delay > 0 GROUP BY {group_col}"
#    )
#  
#    return(dbGetQuery(con, query))
#  }

## ----month, eval = FALSE------------------------------------------------------
#  mean_delays("month")

## ----cooking_show, echo = FALSE-----------------------------------------------
with_mock_db(mean_delays("month"))

## ----tests_1, eval = FALSE----------------------------------------------------
#  library(testthat)
#  
#  test_that("mean_delays()", {
#    out <- mean_delays("month")
#    expect_named(out, c("month", "mean_delay"))
#    expect_equal(dim(out), c(12, 2))
#  })

## ----recording, eval = FALSE--------------------------------------------------
#  start_db_capturing()
#  out <- mean_delays("month")
#  stop_db_capturing()

## ----with_mock_1--------------------------------------------------------------
with_mock_db(
  mean_delays("month")
)

## ----tests_2------------------------------------------------------------------
library(testthat)
library(dittodb)

with_mock_db(
  test_that("mean_delays()", {
    out <- mean_delays("month")
    expect_named(out, c("month", "mean_delay"))
    expect_equal(dim(out), c(12, 2))
  })
)

## ----recording_days, eval = FALSE---------------------------------------------
#  start_db_capturing()
#  out <- mean_delays("day")
#  stop_db_capturing()

## ----tests_day_2--------------------------------------------------------------
with_mock_db(
  test_that("mean_delays()", {
    out <- mean_delays("day")
    expect_named(out, c("day", "mean_delay"))
    expect_equal(dim(out), c(31, 2))
  })
)

## ----editing, eval = FALSE----------------------------------------------------
#  # read in the recorded fixture
#  df_fixt <- source("nycflights/SELECT-16d120.R", keep.source = FALSE)$value
#  
#  # filter out anything after february and all days after the 9th of the month
#  df_fixt <- dplyr::filter(df_fixt, month <= 2 & day < 10)
#  
#  # save the fixture for use in tests
#  dput(df_fixt, file = "nycflights/SELECT-16d120.R", control = c("all", "hexNumeric"))