run_files: Read, prepare and execute .SQL files

Description

Accepts a character vector of SQL file names and attempts to execute the queries in each one.

Usage

run_files(filenames, ..., include_params = FALSE)
runfiles(filenames, ..., include_params = FALSE)

Value

A list of results of sql queries found in files

Arguments

filenames: name, or vector of names, of file(s) to be executed
...: Arguments to be passed to run_queries(), prepare_sql(), or read_sql()
include_params: TRUE or FALSE. Should the parameters be included in the output?

Details

If no default connection is supplied via default.conn and no connections have been configured using connect(), an attempt will be made to configure connections via connect() using the configuration search path. If no database connections are available after this attempt, an error will be raised. See vignette("connections") for details about the configuration search path.

run_files() calls read_sql() on each file, and prepare_sql() on the queries read from those files. Prepared queries are executed with run_queries(). The behaviour of those functions can be controlled by passing the relevant parameters to run_files() as the ... argument.

run_files() also enables control of the arguments accepted by run_queries() on a per-query basis, by interpreting comments in SQL files as described for read_sql(). Interpreted comments precede the sql query to which they refer. Each interpretable comment must be on a line by itself and take the form:

-- keyword = value

Keywords and possible values for interpretable comments are:

qname: A name for this query
quotesql: "yes" or "no" - should interpolated characters be quoted?
interpolate: "yes" or "no" - should sql be interpolated?
execmethod: One of "get", "execute", "sendq", "sends" or "spatial" - which method should be used to execute the query? "get" means DBI::dbGetQuery(); "execute" means DBI::dbExecute(); "sendq" means DBI::dbSendQuery; "sends" means DBI::dbSendStatement(); "spatial" means sf::st_read().
geometry: The name of a spatial column. Ignored if execmethod is not 'spatial'
conn_name: The name of a connection to execute this query against

All interpreted comments except qname are cascaded within their file, meaning that if you want to use the same values throughout, you need only set them for the first query. See read_sql() for details.

Examples

Run this code

library(sqlhelper)

config_filename <- system.file("examples/sqlhelper_db_conf.yml",
                package="sqlhelper")

readLines( config_filename ) |> writeLines()

connect(
    config_filename,
    exclusive=TRUE)

DBI::dbWriteTable( default_conn(), "iris", iris)

sf::st_write(spData::congruent, default_conn(), "congruent")
sf::st_write(spData::incongruent, live_connection("pool_sqlite"), "incongruent")

run_files_ex1 <- system.file("examples/run_files_ex1.sql", package="sqlhelper")
readLines( run_files_ex1 ) |> writeLines()

run_files_ex2 <- system.file("examples/run_files_ex2.sql", package="sqlhelper")
readLines( run_files_ex2 ) |> writeLines()

n_longest_petals <- 5
results <- run_files( c( run_files_ex1, run_files_ex2 ) )

names(results)

results$how_many_irises

results$n_longest_setosa_petal_lengths

plot(results$get_congruent, border = "orange")
plot(results$get_incongruent, border = "blue", add=TRUE)