Extending qryflow Functionality

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:

Split the SQL script into chunks using tagged comments (e.g., -- @query: name)
Parse each chunk, capturing type, name, and other tags
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:

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

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:

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:

ls_qryflow_types()

Custom types must be re-registered each session. To make them persistent, add registration calls to your .Rprofile (see: Managing R Startup), or create a small package with an .onLoad() hook (see: R Packages (2e)).

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:

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:

validate_qryflow_handler(query_send_handler)

Then, register it:

register_qryflow_type(
  "query-send",
  handler = query_send_handler,
  overwrite = TRUE
)
#> [1] TRUE

Check that it registered properly:

ls_qryflow_types()
#> [1] "exec"       "query"      "query-send"

And test it out on some SQL:

# 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)
#>    mpg cyl disp  hp drat    wt  qsec vs am gear carb
#> 1 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
#> 2 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
#> 3 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
#> 4 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
#> 5 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
#> 6 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1