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 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, MySQL, and PostgreSQL.
For interactive exploitation, attach dplyr and DBI. If you’re creating a package, you’ll need to import dplyr and DBI.
Check that you can create a tbl from a connection, like:
con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
DBI::dbWriteTable(con, "mtcars", mtcars)
tbl(con, "mtcars")
#> # Source: table<`mtcars`> [?? x 11]
#> # Database: sqlite 3.46.0 []
#> mpg cyl disp hp drat wt qsec vs am gear carb
#> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 21 6 160 110 3.9 2.62 16.5 0 1 4 4
#> 2 21 6 160 110 3.9 2.88 17.0 0 1 4 4
#> 3 22.8 4 108 93 3.85 2.32 18.6 1 1 4 1
#> 4 21.4 6 258 110 3.08 3.22 19.4 1 0 3 1
#> # ℹ more rows
If you can’t, this likely indicates some problem with the DBI methods. Use DBItest to narrow down the problem.
The first method of your dbplyr backend should always be for the
dbplyr_edition()
generic:
#' @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.
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()
.
Make sure you’ve read vignette("translation-verb")
so
you have the lay of the land.
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()
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.