Adding a new DBI backend

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

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.

First steps

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

library(dplyr)
library(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.19.3 []
#> mpg cyl disp hp drat wt qsec vs am gear carb
#> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 21.0 6.00 160 110 3.90 2.62 16.5 0 1.00 4.00 4.00
#> 2 21.0 6.00 160 110 3.90 2.88 17.0 0 1.00 4.00 4.00
#> 3 22.8 4.00 108 93.0 3.85 2.32 18.6 1.00 1.00 4.00 1.00
#> 4 21.4 6.00 258 110 3.08 3.22 19.4 1.00 0 3.00 1.00
#> # ... 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:

#' @export
db_desc.PostgreSQLConnection <- function(x) {
info <- dbGetInfo(x)
host <- if (info$host == "") "localhost" else info$host
paste0("postgres ", info$serverVersion, " [", info$user, "@",
host, ":", info$port, "/", info$dbname, "]")
}

Copying, computing, collecting and collapsing

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

SQL translation

Make sure you’ve read vignette("sql-translation") so you have the lay of the land.

Verbs

Check that SQL translation for the key verbs work:

Vectors

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