Proposal

This proposal describes an R package that provides testing infrastructure for DBI backends like RSQLite, RPostgres and RMySQL. DBI backends add this package to their Suggests list, and call its functions as part of their automated tests.

The design goals are:

• Simplicity: Easy to use for authors of DBI backends
• Completeness: This package should test the entire feature set of DBI
• Opt-out: There should be a way to opt out of certain tests (e.g., if a part of the DBI is not implemented)

A brief interface specification and a list of features tested are presented below. The description of an alternative interface that uses inversion of control has been moved to a separate page.

Interface

This section describes the contract that DBI backends must follow to use the DBItest package.

Functions

The package exports tester functions which test a certain aspect of the DBI interface, as described below. In turn, each tester function defines named sub-tests. These tester functions are intended to be called by files living in tests/testthat. Testing is tightly coupled with testthat -- the tester functions will call testthat::context() once and testthat::test_that() for each sub-test. Support for RUnit can be added later if necessary.

Selected sub-tests can be skipped by name. The driver, the connection arguments, and (if necessary) options that tweak the tests are stored in a context. There is one active context that is used by default when no context is given explicitly. This avoids specifying the same information with each call, and simplifies creating the tests and interactive use.

The suggested design is simple but does not allow discovering the names of the sub-tests and has some unnecessary redundancy. Perhaps a design that uses R6 classes is a neater option here.

Examples:

make_context <- function(drv, connect_args, options = NULL, set_as_default = TRUE) { ... }
set_default_context <- function(ctx) { ... }
get_default_context <- function() { ... }
test_getting_started <- function(skip = NULL, ctx = get_default_context()) {
  tests <- list(
    package_dependencies = function() {
      # should be able to access ctx here
      ...
    }
  )
  run_tests(tests, skip)
}
test_driver <- function(skip = NULL, ctx = get_default_context()) {
  tests <- list(
    inherits_from_driver = function() { ... },
    get_info = function() { ... },
    data_type = function() { ... },
    ...
  )
  run_tests(tests, skip)
}
test_connection <- function(skip = NULL, ctx = get_default_context()) { ... }

The tests run on an initially empty database and create/destroy everything they need for testing. This is not possible with read-only databases, therefore testing read-only databases is not supported.

What is tested

This section describes a list of features tested by the DBItest package. The subsections below correspond to sections in the backend vignette (permalink). Each section or bullet point corresponds to to a tester function. This allows testing packages written from scratch incrementally using a test-first approach without having too many failing tests. Completing a (part of a) section in the vignette corresponds to a stable state of the driver with a well-defined feature set and (ideally) no failing tests. Conversely, the backend vignette should be updated to show how to implement testing right from the start.

Getting started

• Test package dependencies
  • Depends on DBI
  • Imports methods

Driver

• Inherits from the DBIDriver class

• dbGetInfo

  • Are necessary elements present?

• dbDataType

  • Is there an equivalent for each R data type (logical, integer, numeric, date, character, ...)

• Repeated load and unload works

• Constructor exists and is named like the package

• show method

Connection

• Driver!dbConnect and dbDisconnect
  • Inherits from the DBIConnection class
  • Repeated load, connect, disconnect, and unload works
• dbGetInfo
  • Are necessary elements present?
• show method

Results

• Connection!dbSendQuery

  • Inherits from the DBIResult class

  • Test a query that does not return a result set, e.g.:

    CREATE TABLE test (a integer);
    DROP TABLE test;
    

  • Test an invalid query

• dbGetInfo

  • Are necessary elements present?

  • Test return value for queries that supply constants, e.g.:

    SELECT 1 as a UNION SELECT 2;
    SELECT 1 as a, 2 as b;
    

• dbFetch, dbHasCompleted, dbClearResult

  • Fetch single rows
  • Fetch multiple rows
  • Fetch more rows than available
  • Closing result set when fetching only part of the data
  • Queries that don't return results

• Connection!dbGetQuery

  • Single values
  • Single columns
  • Single rows
  • Multicolumn + multirow

• show method

• Data translation DB -> R: Create data in database using the DB's SQL dialect, and compare results in R.

  • Character encoding: Non-ASCII characters (e.g., Latin-1, cyrillic, and chinese) are preserved
  • Time as UTC
  • NA <-> NULL
  • 64-bit integers

SQL methods

• dbQuoteString, dbQuoteIdentifier

  • Quoting rules
  • Quote quoted string
  • Check result of SELECT <dbQuoteString(...)>, especially for corner cases

• dbWriteTable and dbReadTable

  • Work as expected
  • Duplicate tables
  • Consistency: Data in = data out

• dbListTables, dbExistsTable, dbRemoveTable

  • Work as expected
  • New table appears in dbListTables

• Data translation R -> DB -> R via dbWriteTable and dbReadTable

  • SQL keywords as column names
  • Use quotes in column names and data
  • Character encoding: Non-ASCII characters (e.g., Latin-1, cyrillic, and chinese) are preserved
  • Time (as UTC, with or without timezone)
  • NA <-> NULL
  • 64-bit integers

Metadata methods

Connection

• dbIsValid

  • Only an open connection is valid

• dbGetException

  • Is available after triggering an error
  • Changes when triggering another error

• dbListResults

  • Changes if sending query and clearing result

Result

• dbIsValid

  • Only an open result set is valid

• dbColumnInfo, dbGetRowsAffected, dbGetRowCount, dbHasCompleted, dbGetStatement

  • Data in = data out

• dbBind

  • Create parametrized query
  • Test with different inputs

Full compliance

• Interface compliance: as in DBI::dbiCheckCompliance
• Read-only vs. read-write: In read-only mode, all write requests should result in an error.

Not tested

• dbUnloadDriver: Deprecated
• dbListConnections: Will be deprecated
• dbBegin, dbCommit, dbRollback
  • Transaction testing is potentially out of scope of the DBItest package

Open questions

• Arguments for appending and overwriting are defined only for RSQLite. Do we need to extend the DBI specification for dbWriteTable?
• I don't undersand "Need specific tests around dbFetch only returning some results".
• Agree on expanding the backend vignette? Authorship?
• Licensing? Copyright holder?