[R-sig-DB] default driver and connection

[Gabor Grothendieck]

On 7/18/07, Seth Falcon <sfalcon using fhcrc.org> wrote:
> "Gabor Grothendieck" <ggrothendieck using gmail.com> writes:
> > Although the best situation would be if I could discover whether
> > the driver had been loaded, I agree that the next best thing
> > is to just check whether the package has been loaded and will
> > do it that way for now.
>
> I'm not understanding why knowing whether or not one of the drivers
> has been initialized via a call to SQLite() or MySQL() is useful.

Consider this:

> f2 <- function(s, DF) {
+    on.exit(dbDisconnect(con))
+    if ("package:RMySQL" %in% search()) {
+       m <- dbDriver("MySQL")
+       con <- dbConnect(m)
+    } else {
+       m <- dbDriver("SQLite")
+       con <- dbConnect(m, dbname = ":memory:")
+    }
+    dbWriteTable(con, deparse(substitute(DF)), DF)
+    dbGetQuery(con, s)
+ }
>
> library(RSQLite)
> f2("select * from iris limit 3", iris)
  row_names Sepal_Length Sepal_Width Petal_Length Petal_Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
3         3          4.7         3.2          1.3         0.2  setosa

or we could tell it to use MySQL like this:

library(RMySQL)
f2("select * from iris limit 3", iris)

provided we had set up a MySQL config file with the necessary username, etc.
The select statement given works with either but in some cases we might
want to use the extra power of MySQL to give it certain select statements
not accepted by SQLite or we might want to use some other database with
other features in the select statements not covered by either MySQL or
SQLite such as the PIVOT keyword.

Now perhaps its enough to check for specific load packages and it is
convenient. On the other hand it would be even more convincing that the
user wanted to use a particular database it they had actually loaded the
driver for it regardless of which library calls had been made -- although
admittedly the user would then have to issue another statement, the
dbDriver statement, so its slightly less convenient than what we have above.

The next level is that we suppose we want to handle any database
including SQLite, MySQL and other databases in the future that have
not even been implemented with R drivers yet so its not possible to
list them all.

>
> I can see how finding open connections is useful, and you can do that
> using dbListConnections.
> >
> > One other thing.  dbListConnections(SQLite()) lists SQLite
> > connections and dbListConnections(MySQL()) lists MySQL
> > connections but it would be nice if one could issue
> > dbListConnections() and a combined list of both.  If one were
> > open to using either then this would, for example, be a quick
> > way to know if there were just one open connection and
> > determine which of the two database systems it was associated
> > with.   Both are DBIConnection objects so one is really just
> > asking for a list of the DBIConnection objects.
>
> dbListAllCons = function() {
>    drivers = list(SQLite(), MySQL())
>    unlist(lapply(drivers, dbListConnections))
> }
>

That would work although there could be other types of databases too
including drivers not yet written or contemplated and the above requires
that we know ahead of time what they all could be.