Implementing a new backend

Getting started

Start by creating a package. It’s up to you what to call the package, but following the existing pattern of RSQLite, RMySQL, RPostgreSQL and ROracle will make it easier for people to find it. For this example, I’ll call my package RKazam.

In your DESCRIPTION, make sure to include:

Depends:
  DBI (>= 0.3.0),
  methods

This is one of the few situations in which using depends (instead of imports) is correct, because you want to make sure the generics from the DBI package (like dbConnect(), dbGetQuery() etc) are available.

Driver

Start by making a driver class which inherits from DBIDriver. This class doesn’t need to do anything, it’s just used to dispatch other generics to the right method. Users don’t need to know about this, so you can remove it from the default help listing with @keywords internal:

#' Driver for Kazam database.
#' 
#' @keywords internal
#' @export
setClass("KazamDriver", contains = "DBIDriver")

The driver class was more important in older versions of DBI, so you should also provide a dummy dbUnloadDriver() method.

#' @export
#' @rdname Kazam-class
setMethod("dbUnloadDriver", "KazamDriver", function(drv, ...) {
  TRUE
})
#> [1] "dbUnloadDriver"

If your package needs global setup or tear down, do this in the .onLoad() and .onUnload() functions.

You might also want to add a show method so the object prints nicely:

setMethod("show", "KazamDriver", function(object) {
  cat("<KazamDriver>\n")
})
#> [1] "show"

Next create Kazam() which instantiates this class.

#' @export
Kazam <- function() {
  new("KazamDriver")
}

Kazam()
#> <KazamDriver>

Connection

Next create a connection class that inherits from DBIConnection. This should store all the information needed to connect to the database. If you’re talking to a C api, this will include a slot that holds an external pointer.

#' Kazam connection class.
#' 
#' @export
#' @keywords internal
setClass("KazamConnection", 
  contains = "DBIConnection", 
  slots = list(
    host = "character", 
    username = "character", 
    # and so on
    ptr = "externalptr"
  )
)

Now you have some of the boilerplate out of the way, you can start work on the connection. The most important method here is dbConnect() which allows you to connect to a specified instance of the database. Note the use of @rdname Kazam. This ensures that Kazam() and the connect method are documented together.

#' @param drv An object created by \code{Kazam()} 
#' @rdname Kazam
#' @export
#' @examples
#' \dontrun{
#' db <- dbConnect(RKazam::Kazam())
#' dbWriteTable(db, "mtcars", mtcars)
#' dbGetQuery(db, "SELECT * FROM mtcars WHERE cyl == 4")
#' }
setMethod("dbConnect", "KazamDriver", function(drv, ...) {
  # ...
  
  new("KazamConnection", host = host, ...)
})
#> [1] "dbConnect"

• Replace ... with the arguments needed to connect to your database. You’ll always need to include ... in the arguments, even if you don’t use it, for compatibility with the generic.

• This is likely to where people first come for help, so the examples so show how to connect to the database, and how to query it. (Obviously these examples won’t work yet.) Ideally, include examples that can be run right away (perhaps relying on a publicly hosted database), but failing that surround in \dontrun{} so people can at least see the code.

Next, implement show() and dbDisconnect() methods.

Results

Finally, you’re ready to implement the meat of the system: fetching results of a query into a data frame. First define a results class:

#' Kazam results class.
#' 
#' @keywords internal
#' @export
setClass("KazamResult", 
  contains = "DBIResult",
  slots = list(ptr = "externalptr")
)

Then write a dbSendQuery() method. This takes a connection and SQL string as arguments, and returns a result object. Again ... is needed for compatibility with the generic, but you can add other arguments if you need them.

#' Send a query to Kazam.
#' 
#' @export
#' @examples 
#' # This is another good place to put examples
setMethod("dbSendQuery", "KazamConnection", function(conn, statement, ...) {
  # some code
  new("KazamResult", ...)
})
#> [1] "dbSendQuery"

The hardest part of every DBI package is writing the dbFetch() method. This needs to take a result set and (optionally) number of records to return, and create a dataframe:

#' Retrieve records from Kazam query
#' @export
setMethod("dbFetch", "KazamResult", function(res, n = -1, ...) {
  ...
})
#> [1] "dbFetch"

Finally implement, dbClearResult():

#' @export
setMethod("dbClearResult", "KazamResult", function(res, ...) { 
  
})
#> [1] "dbClearResult"

Next implement dbHasCompleted() which should return a boolean indicating if there are any rows remaining to be fetched.

#' @export
setMethod("dbHasCompleted", "KazamResult", function(res, ...) { 
  
})
#> [1] "dbHasCompleted"

With these four methods in place, you can now use the default dbGetQuery() to send a query to the database, retrieve results if available and then clean up. Spend some time now making sure this works with an existing database.

SQL methods

You’re now on the home stretch, and can make your wrapper substantially more useful by implementing methods that wrap around variations in SQL across databases:

• dbQuoteString() and dbQuoteIdentifer() are used to safely quote strings and identifiers to avoid SQL injection attacks.

• dbWriteTable() creates a database table given an R dataframe. I’d recommend using the methods in the SQL package to generate the SQL. This package is still a work in progress so please let me know if you have problems.

• dbReadTable(): a simple wrapper around SELECT * FROM table. Use dbQuoteIdentifer() to safely quote the table name and prevent mismatches between the names allowed by R and the database.

• dbBegin(), dbCommit() and dbRollback(): implement these three functions to provide basic transaction support.

• dbListTables() and dbExistsTable() let you determine what tables are available. If not provided by your database’s API, you may need to generate sql that inspects the system tables.

• dbRemoveTable() wraps around DROP TABLE. Start with SQL::sqlTableDrop().

Metadata methods

Finally there are a lot of extra metadata methods that you might want to implement. However, I’ve run out of energy to list them all - instead look at the source code for the DBI package.