[R-sig-DB] RBI and front-ends to RODBC and RPgSQL

[David James]

Hi,

A few weeks back, I suggested that we could implement the database
interface (DBI) that Tim initially wrote in Rdbi using the new style
classes and methods.  Also, I thought that having a DBI front-end
to some of the existing packages would make it easier for the R-SIG-DB
to try the proposed DBI with real data.  Thus, Kurt has agreed to put
the 3 packages below in the 1.4.0/Other/Devel area.

1. DBI -- The API for the database interface.  This package
   consists of virtual class definitions and their generic functions,
   and it requires the "methods" package.  The API is fully described
   in the file "DBI.pdf", found in the package directory (i.e., once
   DBI is installed).   That document identifies a number of open
   issues that we should close before we make the DBI "official",
   and it would be very useful if you could comment on (at least)
   those issues.

2. DBI.RODBC -- this is a DBI front-end to the current RODBC (0.8-3).
   This package requires the DBI package above, and it implements the
   DBI on top of existing RODBC (but see the caveats below).

3. DBI.RPgSQL -- similarly, this is a DBI front-end to the existing
   RPgSQL (1.0-0), and it also requires the DBI package.

(I should have implemented front-ends for Torsten's mSQL, and MySQL, 
etc., but there's only so much time...)

What the R-SIG-DB needs to do is to experiment with these packages
and make suggestions and/or modifications to ensure we're not
leaving out important functionality in the API.  It's probably time
to freeze the API soon, so that we can move to implement other things
on top of it (e.g., the attach() that Duncan has already working).

Sample R Session 
----------------

##
## attach the appropriate library
##

library(DBI.RPgSQL)   ## this attaches methods, RPgSQL (if needed)
library(DBI.RODBC)    ## this attaches methods, RODBC (if needed)

##
## instantiate one or both driver
##

pg <- RPgSQL()   ## equivalent to pg <- dbDriver("RPgSQL")  
od <- RODBC()    ## equivalent to od <- dbDriver("RODBC")

##
## print/show/summary methods to print a one-line object identification
## and produce a brief description (meta-data) of an object
##

> print(pg)
<PgSQLDriver:(27642)> 

> summary(pg)
PgSQLDriver 
Id = (27642) 
driver.name = RPgSQL 
driver.version = 1.0-0 
DBI.version = 0.1-2 
client.version = NA 
max.connections = 1 
 
> show(od)
<ODBCDriver:(27642)> 

> summary(od)
ODBCDriver 
Id = (27642) 
driver.name = RODBC 
driver.version = 0.8-3 
DBI.version = 0.1-2 
client.version = NA 
max.connections = 16 

##
## connect to a database 
##

> dbConnect(pg, "user", "password", "dbname")
<PgSQLConnection:(27642,0)> 
> pg
<PgSQLDriver:(27642)> 

## Oops!  I forgot to assign the connection --
pcon <- dbListConnections(pg)     ## (RPgSQL returns one connection obj)

> summary(pcon)
PgSQLConnection 
dbname = rsdbi 
user = rsdbi 
status = 0 
open = TRUE 
server.version = NA 

> ocon <- dbConnect(od, "dsn", "uid", "pwd")
> summary(ocon)
ODBCConnection 
server.version = NA 
results = NA 

##
## convenience methods on connection objects:
##
## dbLisTables(), dbExistsTable(), dbReadTable(), dbWriteTable(), and
## dbRemoveTable()

> dbListTables(pcon)[10:14]
[1] "USArrests"     "USJudgeRatings"     "USPersonalExpenditure"
[4] "VADeaths"      "airmiles"             

> dbListTables(ocon)[1:5]
[1] "Formaldehyde"  "HairEyeColor"   "InsectSprays"  "LifeCycleSavings"
[5] "OrchardSprays"   

> dbExistsTable(ocon, "quakes")
[1] TRUE

> dbExistsTable(pcon, "quakes")
[1] FALSE

> dbWriteTable(pcon, "quakes", dbReadTable(ocon, "quakes"))

> dbRemoveTable(ocon, "quakes")
[1] TRUE

##
## Queries, result sets.
##

> prs <- dbSendQuery(pcon, "select * from quakes")  ## leave output in dbms
> prs
<PgSQLResult:(27642,0,0)> 

> summary(prs)
PgSQLResult 
rows = 1000 
cols = 5 
field.names = long, lat, depth, mag, stations 
hasCompleted = NA 

> dbColumnInfo(prs)    ## column info of the result set
  field.name field.type  R.type Nullable
1       long        701 numeric       NA
2        lat        701 numeric       NA
3      depth         23 integer       NA
4        mag        701 numeric       NA
5   stations         23 integer       NA


> x <- fetch(ors, n = 0)      ## bring entire output in

## combine dbSendQuery()/fetch() in one operation
y <- dbGetQuery(ocon, "select * from quakes")  

##
## free up resources
##

> dbClearResult(prs) 
[1] TRUE

> dbDisconnect(pcon)  
[1] TRUE
> dbDisconnect(ocon)
[1] TRUE

> dbUnloadDriver(pg)
[1] TRUE
> dbUnloadDriver(od)
[1] TRUE
#################################################################

Caveats:
--------

  I ran into some problems with the RODBC package where I could
  not get sqlSave() to work when invoked from within a function
  (it may have something to do with calls to substitute()) thus
  dbWriteTable() fails with an ODBC connection.  I'd appreciate
  help on the correct usage of RODBC's sqlSave().  Notice that I
  ran into similar problems with sqlDrop(), but I was able to get
  it working by using do.call() to invoke sqlDrop().

-- 
David A. James
Statistics Research, Room 2C-253            Phone:  (908) 582-3082       
Bell Labs, Lucent Technologies              Fax:    (908) 582-3340
Murray Hill, NJ 09794-0636