## ----setup, include = FALSE---------------------------------------------------
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

## -----------------------------------------------------------------------------
library(DBI)
library(RSQLite)
library(featdelta)

cars <- mtcars
cars$id <- seq_len(nrow(cars))

# Name of the unique row identifier used throughout the pipeline.
key <- "id"

day_one <- 1:20
day_two <- 21:30

## -----------------------------------------------------------------------------
con <- dbConnect(SQLite(), ":memory:")
dbWriteTable(con, "raw_cars", cars[day_one, ], overwrite = TRUE)

dbGetQuery(con, "SELECT id, mpg, cyl, hp, wt, am FROM raw_cars ORDER BY id")

## -----------------------------------------------------------------------------
source_sql <- "SELECT * FROM raw_cars WHERE id > 15 ORDER BY id"

dbGetQuery(con, source_sql)

## -----------------------------------------------------------------------------
defs <- fd_define(
  transmission = ifelse(am == 1, "automatic", "manual"),
  hp_per_cyl = hp / cyl,
  wt_per_hp = wt / hp
)

defs

## -----------------------------------------------------------------------------
raw_preview <- dbGetQuery(con, source_sql)

fd_compute(
  data = raw_preview,
  defs = defs,
  key = key
)

## -----------------------------------------------------------------------------
run_day_one <- fd_run(
  con = con,
  sql = source_sql,
  defs = defs,
  key = key,
  feat_table_name = "car_features",
  verbose = FALSE
)

dbGetQuery(con, "SELECT * FROM car_features ORDER BY id")

## -----------------------------------------------------------------------------
dbAppendTable(con, "raw_cars", cars[day_two, ])

dbGetQuery(
  con,
  "SELECT id, mpg, cyl, hp, wt, am FROM raw_cars WHERE id > 15 ORDER BY id DESC"
)

## -----------------------------------------------------------------------------
dbGetQuery(
  con,
  "SELECT * FROM car_features ORDER BY id DESC"
)

## -----------------------------------------------------------------------------
run_day_two <- fd_run(
  con = con,
  sql = source_sql,
  defs = defs,
  key = key,
  feat_table_name = "car_features",
  verbose = FALSE
)

dbGetQuery(con, "SELECT * FROM car_features ORDER BY id DESC")

## -----------------------------------------------------------------------------
defs_v2 <- fd_define(
  transmission = ifelse(am == 1, "automatic", "manual"),
  hp_per_cyl = hp / cyl,
  wt_per_hp = wt / hp,
  power_to_weight = hp / wt
)

run_refresh <- fd_run(
  con = con,
  sql = source_sql,
  defs = defs_v2,
  key = key,
  feat_table_name = "car_features",
  fetch_mode = "all",
  verbose = FALSE
)

dbGetQuery(con, "SELECT * FROM car_features ORDER BY id DESC")

## ----eval = FALSE-------------------------------------------------------------
# defs <- fd_define(
#   feature_a = some_r_expression,
#   feature_b = another_r_expression
# )
# 
# fd_run(
#   con = con,
#   sql = "SELECT * FROM raw_table",
#   defs = defs,
#   key = "id",
#   feat_table_name = "feature_table"
# )

## -----------------------------------------------------------------------------
run_report <- fd_run(
  con = con,
  sql = source_sql,
  defs = defs_v2,
  key = key,
  feat_table_name = "car_features",
  fetch_mode = "all",
  return_data = "features",
  preview_n = 3,
  verbose = FALSE
)

run_report

## ----cleanup, include = FALSE-------------------------------------------------
dbDisconnect(con)