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 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

Opt-out is implemented using inversion of control. 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?)
• Data types
• Listing connections (see RSQLite)
• UTF-8 support
• Prepared statements (dbBind)

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.

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.

General

• Interface compliance: as in DBI::dbiCheckCompliance
• Read-only vs. read-write: In read-only mode, all write requests should result in an error.
• dbGetQuery
  • Single values
  • Single columns
  • Single rows
  • Multicolumn + multirow
• 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

Driver

• dbConnect: Implicitly
• dbGetInfo
  • Are necessary elements present?
• dbDataType
  • Is there an equivalent for each R data type (logical, integer, numeric, date, character, ...)

Connection

• dbDisconnect, 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
  • 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
• dbBind
  • Create parametrized query
  • Test with different inputs

Not tested

• dbUnloadDriver: Deprecated
• dbListConnections: Will be deprecated

Open questions

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