---
title: "Extending qryflow Functionality"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Extending qryflow Functionality}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)
```

```{r}
library(qryflow)
```

# Overview

`qryflow` is designed to be easily extended, allowing users to define custom chunk types. This vignette provides relevant background knowledge on how `qryflow` works under the hood, then walks through how to create and register custom chunk types.

This vignette assumes the knowledge found in the "Getting Started" (`vignette("getting-started", package = "qryflow")`) vignette.

# Big Picture: How `qryflow` Works

When you run a SQL script using `qryflow`, the process follows these steps:

1. Split the SQL script into chunks using tagged comments (e.g., `-- @query: name`)

2. Parse each chunk, capturing type, name, and other tags

4. Execute each chunk using a **type-specific handler**

To support a new chunk type, you'll need to:

- **Create a handler** — which defines how to execute the chunk and return results.

- **Register** your new type with `qryflow` so the package knows how to process it.

# Creating Handlers

Each chunk type needs to have an associated handler. Handlers accept both a `qryflow_chunk` object and a database connection object (e.g., `DBI::dbConnect`). They should execute the SQL as appropriate and then return the result:

This is the handler for the "exec" type:

```r
qryflow_exec_handler <- function(con, chunk, ...) {
  
  # Pass the SQL of the chunk to desired execution strategy
  result <- DBI::dbExecute(con, chunk$sql, ...)

  # Return the result
  result
}
```

After a custom handler has been created, it needs to be validated and registered.

## Validate the Handler

`qryflow` provides `validate_qryflow_handler()` to test whether the handler function meets specifications. An error will occur if:

- The object is not a function

- The formal arguments are not included

- The formal arguments are not in the right order

```r
validate_qryflow_handler(qryflow_exec_handler)
```

Note: This does not test that the code within your function is correct nor does it verify the correct output type.

# How the Registry Works

`qryflow` maintains an internal environment called `.qryflow_handlers` to store registered chunk handlers.

When the package is loaded, default types like "`query`" and "`exec`" are automatically registered. You can register additional types using:

```r
register_qryflow_type("custom", my_custom_handler_func, overwrite = TRUE)
```

This will validate the handler before registering in the internal environment.

We can access what types are registered:

```r
ls_qryflow_types()
```

Custom types must be re-registered each session. To make them persistent, add registration calls to your `.Rprofile`, or create a small package with an `.onLoad()` hook.

# Toy Example: Create `query-send` Chunk Type

This example shows how to implement a new chunk type that's similar to `exec` and `query`. We will create a new type, called `query-send` that works like `query` except calls `DBI::dbSendQuery` instead of `DBI::dbGetQuery`.

First, create the handler:

```{r}
query_send_handler <- function(con, chunk, ...) {
  res <- DBI::dbSendQuery(con, chunk$sql, ...)

  results <- DBI::dbFetch(res)

  DBI::dbClearResult(res)

  results
}
```

Validate it by hand, if you'd like:

```{r}
validate_qryflow_handler(query_send_handler)
```

Then, register it:

```{r}
register_qryflow_type(
  "query-send",
  handler = query_send_handler,
  overwrite = TRUE
)
```

Check that it registered properly:

```{r}
ls_qryflow_types()
```

And test it out on some SQL:

```{r}
# Creates an in-memory sqlite database and populates it with an mtcars table, named "mtcars"
con <- example_db_connect(mtcars)

# Create
sql <- "
-- @query-send: df_mtcars
SELECT *
FROM mtcars;
"

results <- qryflow(con, sql)

head(results)
```