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.

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. Each tester function allows tweaking and/or skipping tests via a simple key-value interface (named list provided by the caller). These 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() and testthat::test_that() as appropriate. Support for RUnit can be added later if necessary.

The driver and the connection arguments 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 each and every time, and simplifies creating the tests and interactive use.

Examples:

make_context <- function(drv, connect_args, set_as_default = TRUE) { ... }
set_default_context <- function(ctx) { ... }
get_default_context <- function() { ... }
test_general <- function(options = NULL, ctx = get_default_context()) { ... }
test_data_translation <- function(options = NULL, ctx = get_default_context()) { ... }

Testing packages written from scratch will be possible. For this, the test functions are organized in a sensible manner, similar to the order given in the vignette on creating a DBI backend.

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: General tests, and which aspects are tested for the methods of each class. The first few tests below correspond to sections in the backend vignette.

Getting started

• Test package dependencies

Driver

• 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 Driver!dbDisconnect
  • Repeated load, connect, disconnect, and unload works
• show method

Results

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

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

  • Test an invalid query
• dbFetch, dbHasCompleted, dbClearResult
  • Test queries that supply constants
  • Fetch single rows
  • Fetch multiple rows
  • Fetch more rows than available
• dbGetQuery
  • Single values
  • Single columns
  • Single rows
  • Multicolumn + multirow
• show method

SQL methods

General

• Interface compliance: as in DBI::dbiCheckCompliance
• Read-only vs. read-write: In read-only mode, all write requests should result in an error.
• dbGetFeature
  • Data type is correct (or NA/NULL)

Data translation DB -> R

Create data in database using the DB's SQL dialect, and compare results in R.

• Character encoding: Non-ASCII characters are preserved
• Time as UTC
• NA <-> NULL
• 64-bit integers

Connection

• dbIsValid: When testing dbListConnections
• dbGetInfo
  • Are necessary elements present?
• dbQuoteString, dbQuoteIdentifier
  • Quoting rules
  • Quote quoted string
  • Check result of SELECT <dbQuoteString(...)>, especially for corner cases
• dbWriteTable, dbReadTable, dbExistsTable, dbListTables, dbListFields, dbRemoveTable
  • Work as expected
  • Duplicate tables
  • Consistency: Data in = data out
  • SQL keywords as column names
  • Use quotes in column names and data
  • Character encoding: Non-ASCII characters are preserved
  • Time (as UTC, with or without timezone)
  • NA <-> NULL
  • 64-bit integers
• dbBegin, dbCommit, dbRollback
  • ACID properties
• dbGetException
  • Is available after triggering an error
  • Changes when triggering another error
• dbListResults
  • Changes if sending query and clearing result
• dbSendQuery
  • Implicitly, see tests for Result

Result

• dbClearResult, dbIsValid
  • Becomes invalid after clearing
• dbFetch, dbColumnInfo, dbGetRowsAffected, dbGetRowCount, dbHasCompleted, dbGetStatement
  • Data in = data out
  • Closing result set when fetching only part of the data
  • Queries that don't return results
• dbBind
  • Create parametrized query
  • Test with different inputs

Not tested

• dbUnloadDriver: Deprecated
• dbListConnections: Will be deprecated

Inversion of control

This section describes an alternative definition for the package's interface using inversion of control.

Functions

The entire test suite can be run with one single function call. Example file (in tests/testthat):

DBItest::test_all(Driver(), connect_args = list(...))

Testing is tightly coupled with testthat -- the test_all function will call testthat::context() and testthat::test_that() as appropriate. Support for RUnit can be added later if necessary.

Running only parts of the test suite is supported, via helper functions (DBItest::test_xxx(drv, connect_args)) and a function that takes an argument (DBItest::test_some(drv, what, connect_args)). In test_some, what can be a vector. This makes it easy to run the tests from a console when working on a backend.

If connect_args is NULL, the connection parameters are queried from a global option.

Opt-out

The DBI backends state which parts of DBI they support to what extent (and therefore can be tested). With this approach, the test logic is not coded explicitly into each backend, but queried by DBItest as necessary. OLE DB and ODBC have similar facilities. For querying the information, a new function should be part of the DBI interface: dbGetFeature, signature: DBIDriver, character and, if necessary, DBIConnection, character. This function accepts a property name and returns a value, which can theoretically depend on the connection. Unknown properties should return a default value (NULL or NA) to allow further extensions. The list of valid properties, their type, and the default value, is part of the DBI specification. Obvious candidates:

• Transaction support (nested?)
• Supported data types
• SQL names for data types
• Listing connections (see RSQLite)
• UTF-8 support
• Prepared statements (dbBind)

Further properties are added as necessary.

Testing packages written from scratch will be possible. Special "work in progress" properties in the DBI backend indicate which parts of DBI are implemented (or not); the tests will skip those parts that are marked as broken.

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.

Open questions

• test_all() and test_some(), or test() with optional argument?
• Licensing? Copyright holder?