---
title: "Adding a new DBI backend"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Adding a new DBI backend}
  %\VignetteEngine{knitr::rmarkdown}
  \usepackage[utf8]{inputenc}
---

```{r, echo = FALSE, message = FALSE}
knitr::opts_chunk$set(collapse = T, comment = "#>")
options(tibble.print_min = 4L, tibble.print_max = 4L)
```

This document describes how to add a new SQL backend to dbplyr. To begin:

* Ensure that you have a DBI compliant database backend. If not, you'll need
  to first create it by following the instructions in 
  `vignette("backend", package = "DBI")`.
  
* You'll need a working knowledge of S3. Make sure that you're 
  [familiar with the basics](https://adv-r.hadley.nz/s3.html) 
  before you start.
 
This document is still a work in progress, but it will hopefully get you started. I'd also strongly recommend reading the bundled source code for [SQLite](https://github.com/tidyverse/dbplyr/blob/master/R/backend-sqlite.R), [MySQL](https://github.com/tidyverse/dbplyr/blob/master/R/backend-mysql.R), and [PostgreSQL](https://github.com/tidyverse/dbplyr/blob/master/R/backend-postgres.R).

## First steps

For interactive exploitation, attach dplyr and DBI. If you're creating a package, you'll need to import dplyr and DBI.

```{r setup, message = FALSE}
library(dplyr)
library(DBI)
```

Check that you can create a tbl from a connection, like:

```{r}
con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
DBI::dbWriteTable(con, "mtcars", mtcars)

tbl(con, "mtcars")
```

If you can't, this likely indicates some problem with the DBI methods. Use [DBItest](https://github.com/r-dbi/DBItest) to narrow down the problem.

## Write your first method

The first method of your dbplyr backend should always be for the `dbplyr_edition()` generic:

```{r}
#' @importFrom dbplyr dbplyr_edition
#' @export
dbplyr_edition.myConnectionClass <- function(con) 2L
```

This declares that your package uses version 2 of the API, which is the version that this vignette documents.

## Copying, computing, collecting and collapsing

Next, check that `copy_to()`, `collapse()`, `compute()`, and `collect()` work:

*   If `copy_to()` fails, you probably need a method for `sql_table_analyze()` 
    or `sql_table_index()`. If `copy_to()` fails during creation of the tbl, 
    you may need a method for `sql_query_fields()`.

*   If `collapse()` fails, your database has a non-standard way of constructing 
    subqueries. Add a method for `sql_subquery()`.
  
*   If `compute()` fails, your database has a non-standard way of saving queries
    in temporary tables. Add a method for `db_save_query()`.

## SQL translation

Make sure you've read `vignette("translation-verb")` so you have the lay of the land. 

### Verbs

Check that SQL translation for the key verbs work:

* `summarise()`, `mutate()`, `filter()` etc: powered by `sql_query_select()`
* `left_join()`, `inner_join()`: powered by `sql_query_join()`
* `semi_join()`, `anti_join()`: powered by `sql_query_semi_join()`
* `union()`, `intersect()`, `setdiff()`: powered by `sql_query_set_op()`

### Vectors

Finally, you may have to provide custom R -> SQL translation at the vector level by providing a method for `sql_translate_env()`. This function should return an object created by `sql_variant()`. See existing methods for examples.