## -----------------------------------------------------------------------------
knitr::opts_chunk$set(collapse = TRUE, comment = "#>")
options(tibble.print_min = 6L, tibble.print_max = 6L, digits = 3)

## ----eval = FALSE-------------------------------------------------------------
# install.packages("dbplyr")

## ----setup--------------------------------------------------------------------
library(dbplyr)
library(dplyr, warn.conflicts = FALSE)
con <- DBI::dbConnect(RSQLite::SQLite(), dbname = ":memory:")

## ----eval = FALSE-------------------------------------------------------------
# con <- DBI::dbConnect(RMariaDB::MariaDB(),
#   host = "database.rstudio.com",
#   user = "hadley",
#   password = rstudioapi::askForPassword("Database password")
# )

## -----------------------------------------------------------------------------
copy_to(con, nycflights13::flights, "flights", temporary = FALSE)

## -----------------------------------------------------------------------------
flights_db <- tbl(con, "flights")

## -----------------------------------------------------------------------------
flights_db 

## -----------------------------------------------------------------------------
flights_db |> 
  select(year:day, dep_delay, arr_delay)

flights_db |> 
  filter(dep_delay > 240)

flights_db |> 
  group_by(dest) |>
  summarise(delay = mean(dep_delay, na.rm = TRUE))

## -----------------------------------------------------------------------------
tailnum_delay_db <- flights_db |> 
  group_by(tailnum) |>
  summarise(
    delay = mean(arr_delay),
    n = n()
  ) |> 
  arrange(desc(delay)) |>
  filter(n > 100)

## -----------------------------------------------------------------------------
tailnum_delay_db

## -----------------------------------------------------------------------------
tailnum_delay_db |> show_query()

## -----------------------------------------------------------------------------
tailnum_delay <- tailnum_delay_db |> collect()
tailnum_delay

## ----error = TRUE-------------------------------------------------------------
try({
nrow(tailnum_delay_db)

tail(tailnum_delay_db)
})

## -----------------------------------------------------------------------------
db <- lazy_frame(x = 1, y = 2)

db |> 
  mutate(
    a = y * x,
    b = a ^ 2,
  )

## -----------------------------------------------------------------------------
db |>
  mutate(z = foofify(x, y))

db |>
  filter(x %LIKE% "%foo%")

## -----------------------------------------------------------------------------
db |>
  mutate(z = .sql$foofify(x, y))

## -----------------------------------------------------------------------------
db |>
  transmute(factorial = sql("x!"))

db |>
  transmute(factorial = sql("CAST(x AS FLOAT)"))