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.30.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.
Now is a good time to implement a method for
db_desc(). This should briefly describe the connection, typically formatting the information returned from
dbGetInfo(). This is what dbplyr does for Postgres connections:
Next, check that
copy_to() fails, it’s likely you 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.