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

## -----------------------------------------------------------------------------
library(qryflow)

## ----example------------------------------------------------------------------
# Connection to In-Memory DB with table populated from mtcars
con <- example_db_connect(mtcars)

sql <- "
-- @exec: drop_cyl_6
DROP TABLE IF EXISTS cyl_6;

-- @exec: prep_cyl_6
CREATE TABLE cyl_6 AS
SELECT *
FROM mtcars
WHERE cyl = 6;

-- @query: df_cyl_6
SELECT *
FROM cyl_6;
"

# Pass tagged SQL to `qryflow`
results <- qryflow(con, sql, verbose = TRUE)

# Access the results from the chunk named `df_cyl_6`
head(results$df_cyl_6)

## ----error=TRUE---------------------------------------------------------------
try({
# on_error = "stop" (default): halts on first failure
bad_sql <- "
-- @exec: prep_cyl_6
CREATE TABLE cyl_6 AS SELECT * FROM mtcars WHERE cyl = 6;

-- @query: df_missing
SELECT * FROM nonexistent_table;

-- @query: df_mtcars
SELECT * FROM mtcars;
"

qryflow(con, bad_sql, on_error = "stop")
})

## -----------------------------------------------------------------------------
# Warn collects errors and signals a warning
qryflow(con, bad_sql, on_error = "warn")

## ----error=TRUE---------------------------------------------------------------
try({
# on_error = "collect": runs everything, then reports all failures together
qryflow(con, bad_sql, verbose = TRUE, on_error = "collect")
})

## -----------------------------------------------------------------------------
sql1 <- "
-- @query: df_mtcars
SELECT *
FROM mtcars;
"

sql2 <- "
-- @query: df_mtcars
SELECT *
FROM mtcars;

-- @query: df_mtcars_cyl6
SELECT *
FROM mtcars
WHERE cyl = 6;
"

# Pass tagged SQL to `qryflow`
res1 <- qryflow(con, sql1, simplify = TRUE)
res2 <- qryflow(con, sql2, simplify = TRUE)
res3 <- qryflow(con, sql1, simplify = FALSE)

class(res1) # simplifies the result to the single data.frame() because only one chunk
class(res2) # returns named list
class(res3) # returns named list, because simplify = FALSE

## -----------------------------------------------------------------------------
obj <- qryflow_run(con, sql)

# A qryflow object
class(obj)

# Chunk names are top-level list names
names(obj)

obj # Print Method

## -----------------------------------------------------------------------------
class(obj$df_cyl_6)

# Print the chunk
obj$df_cyl_6

## -----------------------------------------------------------------------------
results <- qryflow_results(obj)
class(results$df_cyl_6)
head(results$df_cyl_6)

## -----------------------------------------------------------------------------
# Step 1: Parse the SQL into structured chunks
filepath <- example_sql_path()
workflow <- qryflow_parse(filepath)

class(workflow)
length(workflow)
names(workflow)

# Inspect a chunk before execution
workflow$df_mtcars

## -----------------------------------------------------------------------------
# Step 2: Execute the parsed workflow
executed <- qryflow_execute(con, workflow)

class(executed)
names(executed)
executed

## -----------------------------------------------------------------------------
qryflow_meta(executed) # The whole workflow

## -----------------------------------------------------------------------------
qryflow_meta(executed[[1]]) # The whole chunk

## ----echo=FALSE, include=FALSE------------------------------------------------
DBI::dbDisconnect(con)