[R-sig-DB] default driver and connection

[Gabor Grothendieck]

On 7/18/07, Gabor Grothendieck <ggrothendieck using gmail.com> wrote:
> 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.
>

Just one more comment.  For my current package I am currently thinking
of just having an argument drv which equals "MySQL" or "SQLite".  If its missing
then "SQLite" will be used unless the RMySQL package is loaded (as per
your idea) in which case RMySQL will be used.  RSQLite will be listed in
the Depends: line of the DESCRIPTION file so it will always be loaded
but RMySQL will not be listed in the Depends: line so it may or may not.

Thus I don't need any features currently but I it might be worthwhile to think
about this line of extending the database packages for the future.