Version

This document describes version 0.1-6 of the database interface API (application programming interface).

Introduction

The database interface (DBI) separates the connectivity to the DBMS into a “front-end” and a “back-end”. Applications use only the exposed “front-end” API. The facilities that communicate with specific DBMS (Oracle, PostgreSQL, etc.) are provided by “device drivers” that get invoked automatically by the S language evaluator. The following example illustrates some of the DBI capabilities:

## Choose the proper DBMS driver and connect to the server

drv <- dbDriver("ODBC")
con <- dbConnect(drv, "dsn", "usr", "pwd")

## The interface can work at a higher level importing tables 
## as data.frames and exporting data.frames as DBMS tables.

dbListTables(con)
dbListFields(con, "quakes")
if(dbExistsTable(con, "new_results"))
   dbRemoveTable(con, "new_results")
dbWriteTable(con, "new_results", new.output)

## The interface allows lower-level interface to the DBMS
res <- dbSendQuery(con, paste(
            "SELECT g.id, g.mirror, g.diam, e.voltage",
            "FROM geom_table as g, elec_measures as e",
            "WHERE g.id = e.id and g.mirrortype = 'inside'",
            "ORDER BY g.diam"))
out <- NULL
while(!dbHasCompleted(res)){
   chunk <- fetch(res, n = 10000)
   out <- c(out, doit(chunk))
}

## Free up resources
dbClearResult(res)
dbDisconnect(con)
dbUnloadDriver(drv)

(only the first 2 expressions are DBMS-specific – all others are independent of the database engine itself).

Individual DBI drivers need not implement all the features we list below (we indicate those that are optional). Furthermore, drivers may extend the core DBI facilities, but we suggest to have these extensions clearly indicated and documented.

The following are the elements of the DBI:

1. A set of classes and methods (Section [sec:DBIClasses]) that defines what operations are possible and how they are defined, e.g.:

   • connect/disconnect to the DBMS

   • create and execute statements in the DBMS

   • extract results/output from statements

   • error/exception handling

   • information (meta-data) from database objects

   • transaction management (optional)

   Some things are left explicitly unspecified, e.g., authentication and even the query language, although it is hard to avoid references to SQL and relational database management systems (RDBMS).

2. Drivers

   Drivers are collection of functions that implement the functionality defined above in the context of specific DBMS, e.g., mSQL, Informix.

3. Data type mappings (Section [sec:data-mappings].)

   Mappings and conversions between DBMS data types and R/S objects. All drivers should implement the “basic” primitives (see below), but may chose to add user-defined conversion function to handle more generic objects (e.g., factors, ordered factors, time series, arrays, images).

4. Utilities (Section [sec:utilities].)

   These facilities help with details such as mapping of identifiers between S and DBMS (e.g., _ is illegal in R/S names, and . is used for constructing compound SQL identifiers), etc.

DBI Classes and Methods

The following are the main DBI classes. They need to be extended by individual database back-ends (Sybase, Oracle, etc.) Individual drivers need to provide methods for the generic functions listed here (those methods that are optional are so indicated).

Note: Although R releases prior to 1.4 do not have a formal concept of classes, we will use the syntax of the S Version 4 classes and methods (available in R releases 1.4 and later as library methods) to convey precisely the DBI class hierarchy, its methods, and intended behavior.

The DBI classes are DBIObject, DBIDriver, DBIConnection and DBIResult. All these are virtual classes. Drivers define new classes that extend these, e.g., PgSQLDriver, PgSQLConnection, and so on.

Class hierarchy for the DBI. The top two layers are comprised of virtual classes and each lower layer represents a set of driver-specific implementation classes that provide the functionality defined by the virtual classes above.

DBIObject:

Virtual class¹ that groups all other DBI classes.

DBIDriver:

Virtual class that groups all DBMS drivers. Each DBMS driver extends this class. Typically generator functions instantiate the actual driver objects, e.g., PgSQL, HDF5, BerkeleyDB.

DBIConnection:

Virtual class that encapsulates connections to DBMS.

DBIResult:

Virtual class that describes the result of a DBMS query or statement.

[Q: Should we distinguish between a simple result of DBMS statements e.g., as delete from DBMS queries (i.e., those that generate data).]

The methods format, print, show, dbGetInfo, and summary are defined (and implemented in the DBI package) for the DBIObject base class, thus available to all implementations; individual drivers, however, are free to override them as they see fit.

format(x, ...):

produces a concise character representation (label) for the DBIObject x.

print(x, ...)/show(x):

prints a one-line identification of the object x.

summary(object, ...):

produces a concise description of the object. The default method for DBIObject simply invokes dbGetInfo(dbObj) and prints the name-value pairs one per line. Individual implementations may tailor this appropriately.

dbGetInfo(dbObj, ...):

extracts information (meta-data) relevant for the DBIObject dbObj. It may return a list of key/value pairs, individual meta-data if supplied in the call, or NULL if the requested meta-data is not available.

Hint: Driver implementations may choose to allow an argument what to specify individual meta-data, e.g., dbGetInfo(drv, what = max.connections).

In the next few sub-sections we describe in detail each of these classes and their methods.

Data Type Mappings

The data types supported by databases are different than the data types in R and S, but the mapping between the “primitive” types is straightforward: Any of the many fixed and varying length character types are mapped to R/S character. Fixed-precision (non-IEEE) numbers are mapped into either doubles (numeric) or long (integer). Notice that many DBMS do not follow the so-called IEEE arithmetic, so there are potential problems with under/overflows and loss of precision, but given the R/S primitive types we cannot do too much but identify these situations and warn the application (how?).

By default dates and date-time objects are mapped to character using the appropriate TO_CHAR function in the DBMS (which should take care of any locale information). Some RDBMS support the type CURRENCY or MONEY which should be mapped to numeric (again with potential round off errors). Large objects (character, binary, file, etc.) also need to be mapped. User-defined functions may be specified to do the actual conversion (as has been done in other inter-systems packages²).

Specifying user-defined conversion functions still needs to be defined.

Utilities

The core DBI implementation should make available to all drivers some common basic utilities. For instance:

dbGetDBIVersion:

returns the version of the currently attached DBI as a string.

dbDataType(dbObj, obj, ...):

returns a string with the (approximately) appropriate data type for the R/S object obj. The DBI can implement this following the ANSI-92 standard, but individual drivers may want/need to extend it to make use of DBMS-specific types.

make.db.names(dbObj, snames, ...):

maps R/S names (identifiers) to SQL identifiers replacing illegal characters (as .) by the legal SQL _.

SQLKeywords(dbObj, ...):

returns a character vector of SQL keywords (reserved words). The default method returns the list of .SQL92Keywords, but drivers should update this vector with the DBMS-specific additional reserved words.

isSQLKeyword(dbObj, name, ...):

for each element in the character vector name determine whether or not it is an SQL keyword, as reported by the generic function SQLKeywords. Returns a logical vector parallel to the input object name.

Open Issues and Limitations

There are a number of issues and limitations that the current DBI conscientiously does not address on the interest of simplicity. We do list here the most important ones.

Non-SQL:

Is it realistic to attempt to encompass non-relational databases, like HDF5, Berkeley DB, etc.?

Security:

allowing users to specify their passwords on R/S scripts may be unacceptable for some applications. We need to consider alternatives where users could store authentication on files (perhaps similar to ODBC’s odbc.ini) with more stringent permissions.

Exceptions:

the exception mechanism is a bit too simple, and it does not provide for information when problems stem from the DBMS interface itself. For instance, under/overflow or loss of precision as we move numeric data from DBMS to the more limited primitives in R/S.

Asynchronous communication:

most DBMS support both synchronous and asynchronous communications, allowing applications to submit a query and proceed while the database server process the query. The application is then notified (or it may need to poll the server) when the query has completed. For large computations, this could be very useful, but the DBI would need to specify how to interrupt the server (if necessary) plus other details. Also, some DBMS require applications to use threads to implement asynchronous communication, something that neither R nor S-Plus currently addresses.

SQL scripts:

the DBI only defines how to execute one SQL statement at a time, forcing users to split SQL scripts into individual statements. We need a mechanism by which users can submit SQL scripts that could possibly generate multiple result sets; in this case we may need to introduce new methods to loop over multiple results (similar to Python’s nextResultSet).

BLOBS/CLOBS:

large objects (both character and binary) present some challenges both to R and S-Plus. It is becoming more common to store images, sounds, and other data types as binary objects in DBMS, some of which can be in principle quite large. The SQL-92 ANSI standard allows up to 2 gigabytes for some of these objects. We need to carefully plan how to deal with binary objects.

Transactions:

transaction management is not fully described.

Additional methods:

Do we need any additional methods? (e.g., dbListDatabases(conn), dbListTableIndices(conn, name), how do we list all available drivers?)

Bind variables:

the interface is heavily biased towards queries, as opposed to general purpose database development. In particular we made no attempt to define “bind variables”; this is a mechanism by which the contents of R/S objects are implicitly moved to the database during SQL execution. For instance, the following embedded SQL statement

  /* SQL */
  SELECT * from emp_table where emp_id = :sampleEmployee

would take the vector sampleEmployee and iterate over each of its elements to get the result. Perhaps the DBI could at some point in the future implement this feature.

Resources

The idea of a common interface to databases has been successfully implemented in various environments, for instance:

Java’s Database Connectivity (JDBC) (www.javasoft.com).

In C through the Open Database Connectivity (ODBC) (www.unixodbc.org).

Python’s Database Application Programming Interface (www.python.org).

Perl’s Database Interface (dbi.perl.org).