1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
---
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.
|
