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:
<- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:") con ::dbWriteTable(con, "mtcars", mtcars) DBI tbl(con, "mtcars") #> # Source: table<mtcars> [?? x 11] #> # Database: sqlite 3.34.1  #> 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 #> # … with 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
#' @importFrom dbplyr dbplyr_edition #' @export <- function(con) 2Ldbplyr_edition.myConnectionClass
This declares that your package uses version 2 of the API, which is the version that this vignette documents.
Next, check that
copy_to() fails, you probably need a method for
copy_to() fails during creation of the tbl, you may need a method for
collapse() fails, your database has a non-standard way of constructing subqueries. Add a method for
compute() fails, your database has a non-standard way of saving queries in temporary tables. Add a method for
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:
filter()etc: powered by
inner_join(): powered by
anti_join(): powered by
setdiff(): powered by
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.