sqlhelper.Rmd

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

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


```{r setup}

```

## Connections

Before you can execute any SQL you will need connections to the databases you
want to use. Either define them yourself (e.g. using `DBI::dbConnect()`), or
place their specifications in a yaml file and use `connect()`:

```{r}
library(sqlhelper)

conf_fn <- "examples/sqlhelper_db_conf.yml"

readLines(conf_fn) |>
  writeLines()

connect(conf_fn)
```

`vignette("connections")` and `connect()` describe in detail how to specify and manage
connections. In particular, note that `sqlhelper` defines a configuration search
path. This means that if you tend to connect to the same databases in every
session, you can avoid supplying file names by placing yaml files in the search
path. Similarly, if you are a database admin you can supply connections to your
analysts by placing such a file in the site-wide directory of the search path.

## Executing files

`sqlhelper`'s principal function is `run_files()`; use it to execute files or
list of files. If you have used `connect()` to set up your connections you can
simply pass file names to `run_files()`.

```{r}

# Write iris to sqlhelper's default connection
DBI::dbWriteTable(default_conn(), name = "IRIS", value = iris)

# write some queries in a .sql file
file_to_run <- "examples/example.sql"

readLines(file_to_run) |> writeLines()

#Define a parameter
petal_length <- 1.3

# Run the queries and save the results
results <- run_files(file_to_run)

# Inspect the results. By default, run_files() returns a list of the results of 
# each query in the files you provided. Results of a specific query can be accessed by the
# the name of the query. See the article 'Executing SQL' for more on named queries.
results

results$short_petal_setosa
```

This example illustrates two key aspects of `run_files()`. Queries can be
parameterized using the familiar `glue::glue_sql()` syntax, and queries can be
named using interpreted comments for easier access to the results. Other aspects
of execution can also be controlled using interpreted comments; `run_files()`
and `vignette("execution")` describe this in detail.

## Executing queries

For exploratory work you might just want to run some short queries interactively. Use the function `run_queries()` for this.

```{r}
# write some queries
my_queries <- c( 
  showtabs = "SELECT name FROM sqlite_schema WHERE type ='table' AND name NOT LIKE 'sqlite_%'",
  how_many_irises = "select count(*) from iris"
)

# Run the queries and save the results
results <- run_queries(my_queries)

# Inspect the results. runqueries() returns a list with one element per query.
# You can access them using the names of the queries:
results$showtabs

results$how_many_irises
```

`run_queries()` and `vignette("execution")` describe in detail the options for running individual queries.

## Preparing queries

Prior to execution, `run_files()` reads and prepares SQL files using the
functions `read_sql()` and `prepare_sql()` respectively. These functions can be
also be used in isolation to read and/or prepare SQL for execution without
actually executing it. This can be useful if you need paramaterize your SQL in a
differently (for example by using `DBI::dbBind()`), or if you need to debug your
parameterized SQL.