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. 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_driver <- function(options = NULL, ctx = get_default_context()) { ... }
test_connection <- function(options = 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 incremental testing packages written from scratch 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 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

• 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
• Inherits from the DBIDriver class

Connection

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

Results

• Connection!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
• 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
• Inherits from the DBIResult class

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
  • Create new table just for testing that it appears in dbListTables

Data translation R -> DB -> R

• dbWriteTable, dbReadTable, dbExistsTable, dbListTables, dbListFields, dbRemoveTable
  • 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

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