\documentclass[graphics,times,psfig,dvips,hyper]{article}
% $Id: DBI.tex,v 1.1 2001/10/06 03:51:34 dj Exp dj $
\usepackage{graphics,times,psfig}
\usepackage[%
  bookmarks=true,
  colorlinks=false,
  backref=true,
  hyperfigures=true,
  %pdfpagemode=UseOutlines,
  pdfauthor={R-Databases Special Interest Group},
  pdftitle={A Common Database Interface (DBI) -- Draft},
  pdfkeywords={Databases, Relational DBMS, RDBMS, R, S, Splus, 
               PostgreSQL, MySQL, mSQL, Microsoft SQL Server, 
               Oracle, Inter-system communications, Distributed computing}
]{hyperref}

% R/S related commands
\newcommand{\sfun}[1]{\mbox{\tt #1()}}  % print arg as an R/S fun()
\newcommand{\sobj}[1]{\mbox{\tt #1}}    % print arg as an R/S object
\newcommand{\sexp}[1]{\mbox{\tt #1}}    % print arg as an R/S expression
\newcommand{\sclass}[1]{\mbox{\tt #1}}  % print arg as an R/S class
\newcommand{\smethod}[1]{\mbox{\tt #1}} % print arg as an R/S method
\begin{document}

\title{A Common Database Interface (DBI) -- Draft}
\author{
R-Databases Special Interest Group\\
\href{mailto:r-sig-db@stat.math.ethz.ch}{r-sig-db@stat.math.ethz.ch}
}

\date{5 October 2001}

\maketitle 
\tableofcontents

\begin{abstract}
This draft describes a common interface between the S language (for
both R and S-Plus implementations) and database management systems
(DBMS).  The interface defines a small set of classes and methods
similar in spirit to Perl's DBI, Java's JDBC, Python's DB-API,
and Microsoft's ODBC.  
\end{abstract}

\section{Introduction}\label{sec:intro}

This proposed DBI separates the connection 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.

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

These are the elements of the DBI:
\begin{enumerate}
\item A set of classes and methods (Section~\ref{sec:DBIClasses})
  that define what operations are possible and how they are defined, 
  e.g.:
  \begin{itemize}
  \item connect/disconnect to the DBMS
  \item create and execute statements in the DBMS
  \item extract results/output from statements
  \item error/exception handling
  \item information (meta-data) from database objects
  \item transaction management (optional)
  \end{itemize}

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

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

\item Data type mappings (Section~\ref{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-define conversion function to handle
  more generic objects (e.g., factors, ordered factors, time series,
  arrays, images).

\item Utilities (Section~\ref{sec:utilities}.)
  
  These facilities help with details such as mapping of identifiers
  between S and DBMS (e.g., \texttt{"\_"} is illegal in R/S
  names, and \texttt{"."} is used for constructing compound SQL
  identifiers), etc.

\end{enumerate}

\section{DBI Classes and Methods}\label{sec:DBIClasses}
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).

\emph{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) to
convey precisely the DBI class hierarchy, its methods, and intended
behavior.  The actual implementation need not take advantage of
this object-oriented paradigm.
}

The DBI classes are \sclass{DBIObject}, \sclass{DBIDriver},
\sclass{DBIConnection} and \sclass{DBIResult}. All these are
\emph{virtual} classes.  Drivers define new classes that extend
these, e.g., \sclass{PgSQLDriver}, \sclass{PgSQLConnection}, and
so on.
\begin{figure}
\psfig{figure=figure1.eps,width=\textwidth,silent=}
\caption{Class hierarchy for the DBI. The top two layers
are comprised of virtual classes (see Section~\ref{sec:DBIClasses} for
possible refinements).}\label{fig:DBIClasses}
\end{figure}
\begin{description}
\item[\sclass{DBIObject}:]
  Virtual class\footnote{A virtual class allows us to group
  classes that share some common characteristics, even if their
  implementations are radically different.} that groups all other
  DBI classes.

\item[\sclass{DBIDriver}:]
  Virtual class that groups all DBMS drivers. Each DBMS driver
  extends this class.  Typically generator functions instantiate
  the actual driver objects, e.g., \sfun{PgSQL}, \sfun{HDF5}, 
  \sfun{BerkleyDB}.

\item[\sclass{DBIConnection}:]
  Virtual class that encapsulates connections to DBMS.

\item[\sclass{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 \texttt{delete} from DBMS queries (i.e., those that generate 
  data).]

\end{description} 

The following methods are defined (and implemented) for the virtual
classes: \smethod{format}, \smethod{print}/\smethod{show}, and
\smethod{dbGetInfo}, but individual drivers are free to override them
as they see fit:
\begin{description} 
\item[\smethod{format(x, ...)}:]
   produces a concise string representation (label) for the
   \sclass{DBIObject} \sobj{x}.

\item[\smethod{print(x, ...)}/\smethod{show(x)}:]
  prints a one-line identification of the object \sobj{x}.

\item[\smethod{dbGetInfo(dbObj, ...)}:]
  extracts information (meta-data) relevant for the
  \sclass{DBIObject} \sobj{dbObj}.

\end{description}

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

\subsection{Class \sclass{DBIObject}}\label{sec:DBIObject}
This class simply groups all DBI classes, and thus all extend it.

\subsection{Class \sclass{DBIDriver}}\label{sec:DBIDriver}
This class identifies the database management system. It needs to 
be extended by individual back-ends (Oracle, PostgreSQL, etc.)

The DBI provides the generator \sexp{DBIDriver("driverName")} 
which simply invokes the function \sfun{driverName}, which
in turn instantiates the corresponding driver object.

The \sclass{DBIDriver} class defines the following methods:
\begin{description}

\item[\sfun{driverName}:]\label{meth:driverName}
  initializes the driver code. The name \sobj{driverName} refers to
  the actual generator function for the DBMS, e.g., \sfun{PgSQL},
  \sfun{RODBC}, \sfun{HDF5}.  The driver instance object is used
  with \smethod{dbConnect} (see Page~\pageref{meth:dbConnect})
  for opening one or possibly more connections to one or more DBMS.

\item[\smethod{dbListConnections(drv, ...)}:]
  list of current connections being handled by the \sobj{drv}
  driver.  May be \sobj{NULL} if there are no open connections.
  Drivers that do not support multiple connections may return the
  one open connection.

\item[\smethod{dbGetInfo(drv, ...)}:]
  returns a list of name-value pairs of information about the
  driver.  The list must include \sobj{name} with the driver name
  (e.g., \texttt{"RODBC"}), \sobj{version} with the version of the
  driver, \sobj{"DBI.version"} with the version of the DBI that the
  driver implements, plus any other relevant information about the
  implementation (e.g., the version of the DBMS' API being used.)

\item[\smethod{unloadDriver("driverName")}:]
  frees all resources used by the driver (e.g., memory, closes opened
  files, connections).  Returns a logical to indicate if it succeeded
  or not. (Optional)
 
\end{description}

\subsection{Class \sclass{DBIConnection}}\label{sec:DBIConnection}
This virtual class encapsulates the connection to a DBMS, and
it provides access to dynamic queries, result sets, DBMS session
management (transactions), etc.

\emph{Note:} Individual drivers are free to implement single or
multiple simultaneous connections.

The methods defined by the \sclass{DBIConnection} class include:
\begin{description}
\item[\smethod{dbConnect(drv, ...)}:]\label{meth:dbConnect}
  creates and opens a connection to the database implemented by the
  driver \sobj{drv} (see Section~\ref{sec:DBIDriver}).  Each driver will
  define what other arguments are required, e.g., \sobj{"dbname"}
  for the database name, \sobj{"user"}, and \sobj{"password"}.
  It returns an object that extends \sclass{DBIConnection} in a
  driver-specific manner (e.g., the MySQL implementation could
  create an object of class \sclass{MySQLConnection} that extends 
  \sclass{DBIConnection}).

\item[\smethod{dbDisconnect(conn, ...)}:]
  closes the connection, discards all pending work, and frees
  resources (e.g., memory, sockets).  Returns a logical indicating
  whether it succeeded or not.

\item[\smethod{dbReconnect(conn, ...)}:]
  attempts to re-establish a previous connection.  Returns a
  connection object.

  [This can present some security problems if we insist on being
  able to re-connect across sessions; in this case we may be
  required to store passwords in files that most likely lack the
  proper security.]

\item[\smethod{dbSendQuery(conn, statement, ...)}:]
  submits one statement to the DBMS.  It returns a \sclass{DBIResult}
  object. This object is needed for fetching data in case
  the statement generates output (see \smethod{fetch} on
  Page~\pageref{meth:fetch}), otherwise it describes the
  state of the operation, such as number of rows affected (see
  \smethod{getRowsAffected} on Page~\pageref{meth:res-others}).

\item[\smethod{dbGetQuery(conn, statement, ...)}:]
  submit, execute, and extract output in one operation.
  The resulting object is a \sclass{data.frame}.

\item[\smethod{dbGetException(conn, ...)}:]
  returns the status of the last DBMS statement sent on a given
  connection.

  \emph{Hint:} The ANSI SQL-92 defines both a status code and an
  status message that could be return as members of a list.

\item[\smethod{dbGetInfo(conn, ...)}:]
  returns a list of name-value pairs describing the state of the
  connection.  

  \emph{Hint:} Useful entries could include the name of the database
  in use, its version, host where the database resides, user name,
  thread ID, type of connection, and any other relevant information
  that the underlying DBMS API may provide.

\item[\smethod{dbListResults(conn, ...)}:]
  list of \sobj{DBIResult} objects current in the \sobj{conn}
  connection.  May be \sobj{NULL} if no result object is open
  in \sobj{conn}.  Drivers that implement only one result set
  per connection should return that one object.

\end{description}

\emph{Note: The following are convenience methods that simplify
  the import/export of (mainly) data.frames.  The first five
  methods implement the core methods needed to \sfun{attach}
  remote DBMS to the S search path. (For details, see
  \cite{data-management:1991,database-classes:1999}.)
}

\emph{Hint:} These methods may be easily implemented using the core
  DBI methods \smethod{dbConnect}, \smethod{dbSendQuery}, and
  \smethod{fetch}, and possibly some of the utilities
  (Section~\ref{sec:utilities}).  

\begin{description}
\item[\smethod{dbListTables(conn, ...)}:]
  returns a character vector of object (table) names available on
  the \sclass{DBIConnection} \sobj{conn}.

\item[\smethod{dbReadTable(conn, dbms.obj, ...)}:]
  imports the data stored remotely in the object \texttt{dbms.obj}
  on connection \sobj{conn}. Returns a \sclass{data.frame}.

\item[\smethod{dbWriteTable(conn, dbms.obj, value, ...)}:]
  write the object \sobj{value} (perhaps after coercing it to 
  data.frame) into the remote object \sobj{dbms.obj} in connection
  \sobj{conn}.  Returns a logical indicating whether the operation
  succeeded or not.

\item[\smethod{dbExistsTable(conn, dbms.obj, ...)}:]
  does remote object \texttt{dbms.obj} exists on \sobj{conn}?
  Returns a logical.

\item[\smethod{dbRemoveTable, conn, dbms.obj, ...)}:]
  removes remote object \sobj{dbms.obj} on connection \sobj{conn}.
  Returns a logical indicating whether the operation succeeded or not.

\item[\smethod{dbAppendTable(conn, dbms.obj, value, ...)}:]
  appends the object \sobj{value} (perhaps after coercing it to 
  data.frame) into the remote object \sobj{dbms.obj} in connection
  \sobj{conn}.  Returns a logical indicating whether the operation
  succeeded or not.
  
\item[\smethod{dbColumnInfo(conn, dbms.obj, ...)}:]
   lists the fields in the remote object \sobj{dbms.obj} on connection 
   \sobj{conn} (optional).

\end{description}

\emph{Note: The following methods deal with transactions and 
   stored procedures. All these functions are optional.
}
\begin{description}
\item[\smethod{commit(conn, ...)}:]
  commits pending transaction on the connection and returns 
  \sobj{TRUE} or \sobj{FALSE} depending on whether the operation
  succeeded or not (optional).

\item[\smethod{rollback(conn, ...)}:]
  undoes current transaction on the connection and returns
  \sobj{TRUE} or \sobj{FALSE} depending on whether the operation
  succeeded or not (optional).

\item[\smethod{callProc(conn, storedProc, ...)}:]
  invokes a stored procedure in the DBMS and returns a \sclass{DBIResult} 
  object (optional).

  [Stored procedures are \emph{not} part of the ANSI SQL-92 standard and
  possibly vary substantially from one RDBMS to another.]

\end{description}

\subsection{Class \sclass{DBIResult}}\label{sec:DBIResult}

This virtual class describes the result and state of execution of
a DBMS statement (any statement, query or non-query).  The result
\sobj{res} keeps track of whether the statement produces output
for R/S, how many rows were affected by the operation, how many
rows have been fetched (if statement is a query), whether there
are more rows to fetch, etc.  

\emph{Note: Individual drivers are free to allow single or multiple
instances of results per connection.
}

[Q: Should we distinguish between results that return no data from those
that return data?]

The class \sclass{DBIResult} defines the following methods:
\begin{description}
\item[\smethod{fetch(res, n, ...)}:]\label{meth:fetch}
  fetches the next \sobj{n} elements (rows) from the result set
  \sobj{res} and return them as a data.frame.  A value of \sexp{n=-1}
  is interpreted as ``return all elements/rows''.

\item[\smethod{dbClearResult(res, ...)}:]
  flushes any pending data and frees all resources used by the
  object \sobj{res} on both sides of the connection. Returns a
  logical indicating success or not.

\item[\smethod{dbListFields(res, ...)}:]
  if \sobj{res} produces output, this method extracts a description
  of the extracted fields.  The description includes field names,
  DBMS internal data types, internal length, internal precision
  and scale, null flag (i.e., column allows \sobj{NULL}'s),
  and corresponding S classes (which can be over-ridden with
  user-provided classes).  See \cite{Xopen-sql} for an exact 
  description of these.

\item[\smethod{setDataMappings(flds, ...)}:] 
  defines a conversion between internal DBMS data types
  and R/S classes.  We expect the default mappings (see
  Section~\ref{sec:data-mappings}) to be by far the most
  common ones, but users that need more control may specify
  a class generator for individual fields in the result set.

\end{description}

\emph{Note: The following are convenience methods that extract
information from the result object (they may be simple implemented
by invoking \sfun{dbGetInfo}).  
}

[Q: Should we make these optional?]

\begin{description}\label{meth:res-others}
\item[\smethod{getDBIConnection(res, ...)}:]
   returns the DBI connection object on which the DBMS statement was sent.

\item[\smethod{getStatement(res, ...)}:]
  returns the DBMS statement (as a character string) associated
  with the result \sobj{res}.

\item[\smethod{getRowsAffected(res, ...)}:]
  returns the number of rows affected by the executed statement.

\item[\smethod{hasCompleted(res, ...)}:] 
  returns a logical that indicates whether the operation was
  completed?

\item[\smethod{getRowCount(res, ...)}:]
  returns the number of rows fetched so far.

\item[\smethod{getNullOk(res, ...)}:]  
  returns a logical vector with as many elements as there are
  fields in the result set, each element describing whether the
  corresponding field accepts \sobj{NULL} values.

\end{description}

\section{Data Type Mappings}\label{sec:data-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 \sobj{"character"}.
Fixed-precision (non-IEEE) numbers are mapped into either doubles
(\sobj{"numeric"}) or long (\sobj{"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.

By default dates and date-time objects are mapped to character
using the appropriate \texttt{TO\_CHAR} function in the DBMS
(which should take care of any locale information).  Some RDBMS
support the type \texttt{CURRENCY} or \texttt{MONEY} which should
be mapped to \sobj{"numeric"}.  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.\footnote{Duncan Temple Lang has volunteered
to port the data conversion code found in R-Jave, R-Perl, and 
R-Python packages to the DBI}.

Specifying user-defined conversion functions may be accomplished 
(more or less) as follows:

\begin{enumerate}
\item run the query with \smethod{dbSendQuery} 
\begin{verbatim}
> res <- dbSendQuery(con, "select whatever-You-need")
\end{verbatim}
\item  extract the output field definitions
\begin{verbatim}
> flds <- dbListFields(res)
\end{verbatim}
\item  replace the class generator in the, say 3rd field, by the user 
own generator:
\begin{verbatim}
> flds[3, "Sclass"]            # default mapping
[1] "character"
\end{verbatim}
by
\begin{verbatim}
> flds[3, "Sclass"] <- "myOwnGeneratorFunction"
\end{verbatim}
\item set the new data mapping prior to fetching
\begin{verbatim}
> setDataMappings(resutlSet, flds)
\end{verbatim}
\item fetch the rows and store in a \sclass{data.frame}
\begin{verbatim}
> data <- fetch(res)
\end{verbatim}
\end{enumerate}

\section{Utilities}\label{sec:utilities}
The core DBI implementation should make available to all
drivers some common basic utilities.  For instance:
\begin{description}
\item[\smethod{SQLDataType(dbObj, obj, ...)}:]
  determines the (approximately) appropriate SQL data type for
  the R/S object \sobj{obj}.  The DBI can implement this following
  the ANSI-92 standard, but individual drivers may want to extend
  it to make use of DBMS-specific types.

\item[\smethod{make.SQL.names(s.names, ...)}:]
  maps R/S names (identifiers) and SQL identifiers replacing 
  illegal characters (as \sobj{"."}) by the legal SQL \sobj{"\_"}.

\item[\sobj{.SQLKeywords}:]
  a vector of SQL keywords (reserved words).  It may be overridden
  by individual drivers.

\item[\smethod{isSQLKeyword(x, ...)}:]
  for each element in the character vector \sobj{x} determine 
  whether or not it is an SQL keyword.  Returns a logical vector
  parallel to the input object \sobj{x}.

\end{description}

\section{Open Issues and Limitations}\label{sec:open-issues}
There are a number of issues and limitations that the current
DBI conscientiously does not address on the interest of simplicity.
But we do list here the most important ones.
\begin{description}
\item[Non-SQL:]
  Is it realistic to attempt to encompass non-relational databases,
  like HDF5, Berkeley DB, etc.?

\item[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 \texttt{odbc.ini}) with more stringent 
  permissions.

\item[Exceptions:]
  the exception mechanism is a bit too simple, and it does not
  provide for information when problems stem from the S-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.

\item[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 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)
  and other details.  Also, some DBMS require applications to use
  threads to implement asychronous communication, something that
  neither R nor S-Plus currently provides.

\item[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 \texttt{nextResultSet}).

\item[BLOBS:]
  binary objects and large objects 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.
  
\item[Transactions:]
  transaction management is not fully described.

\item[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

\begin{verbatim}
  /* SQL */
  SELECT * from emp_table where emp_id = :sampleEmployee
\end{verbatim}
  would take the vector \sobj{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.

\end{description}

\section{Resources}\label{sec:resources}
The idea of a common interface to databases has been successfully
implemented in various environments, for instance:

Java's Database Connectivity (JDBC)
(\href{http://www.javasoft.com/products/jdbc/index.html}{www.javasoft.com}).

In C through the Open Database Connectivity (ODBC)
(\href{http://www.genix.net/unixODBC}{www.genix.net/unixODBC}).

Python's Database Application Programming Interface
(\href{http://www.python.org/topics/database}{www.python.org}).

Perl's Database Interface
(\href{http://dbi.perl.org}{dbi.perl.org}).

\nocite{*}
\bibliography{biblio}
\bibliographystyle{plain}
\end{document}

