Skip to content

default driver and connection

10 messages · Gabor Grothendieck, Seth Falcon

Original
Gabor Grothendieck
# 
Is there a way of finding out if the driver is loaded and if there
are any open connections in RSQLite or other database and what
they are?  Even better would be if the code below could work regardless
of whether an SQLite or MySQL connection was open.  I have written pseudocode
in the places where I don't know how to access the functionality.
If all this exists is there an example?  Thanks.

# returns first three rows of iris
# loading any driver and opening any connection if need be
f <- function(m, con, dbname = ":memory:") {
       if (missing(m) {
               m <- if (no.data.base.drivers.loaded)
                       dbDriver("SQLite")
               else loaded.driver
       }
       if (missing(con)) {
               con <- if (no.connections.open)
                       dbConnect(m, dbname)
               active.connection
       }
       dbWriteTable(con, "iris", iris)
       dbGetQuery(con, "select * from iris limit 3")
}

# test
f()
Seth Falcon
# 
Hi,

[The following comments hold for RSQLite.  I'm pretty sure they hold
for all DBI, but haven't gone and looked to verify the code.]

"Gabor Grothendieck" <ggrothendieck at gmail.com> writes:
The driver is implemented as a singleton so there is no need to worry
about multiple calls to dbDriver("SQLite") or more simply SQLite().
The driver gets loaded on first invocation of SQLite() and subsequent
calls simply return the existing instance.
dbListConnections(SQLite()) will tell you about open connections to
the SQLite driver.

+ seth
Gabor Grothendieck
# 
Thanks.  Is there any way to discover whether SQLite or MySQL
was loaded so that I can do something like this:
On 7/18/07, Seth Falcon <sfalcon at fhcrc.org> wrote:
Thanks.  Is there also a way to discover whether SQLite or MySQL or
neither has been loaded.  Note that they could be loaded but not have
an open connection.

Thus, I would like to do this or similar processing:

If only one of MySQL or SQLite is loaded then use that one.
If neither is loaded use SQLite.

Thanks.
Seth Falcon
# 
"Gabor Grothendieck" <ggrothendieck at gmail.com> writes:
What if both are loaded?

I don't think there is a way currently to determine if SQLite() has
yet been called.  However, if RSQLite is not loaded, it certainly has
not been called ;-)  So perhaps you can get by with checking whether
the package is loaded?

Such a feature could be added, but I'm not convinced it would actually
be useful for anything.

+ seth
Gabor Grothendieck
#
On 7/18/07, Seth Falcon <sfalcon at fhcrc.org> wrote:
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.

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.
Seth Falcon
# 
"Gabor Grothendieck" <ggrothendieck at gmail.com> writes:
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.

I can see how finding open connections is useful, and you can do that
using dbListConnections.
dbListAllCons = function() {
    drivers = list(SQLite(), MySQL())
    unlist(lapply(drivers, dbListConnections))
}
Gabor Grothendieck
#
On 7/18/07, Seth Falcon <sfalcon at fhcrc.org> wrote:
Consider this:
+    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)
+ }
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.
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.
Gabor Grothendieck
#
On 7/18/07, Gabor Grothendieck <ggrothendieck at gmail.com> wrote:
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.
Seth Falcon
# 
"Gabor Grothendieck" <ggrothendieck at gmail.com> writes:
How about a function in your package that sets the default db driver:

   setDefaultDb("MySQL")

This function could set a value in an environment object in your
package's namespace.  Users could load your package and call this
function in their .Rprofile for added convenience.  IMO relying on
packages being loaded (or not) is fragile.
setDefaultDb seems to solve this.
I'm with you as far as desiring well factored code, but honestly it
isn't like new DB drivers are popping up all over the place (when was
the last time a new driver was introduced?).  So listing them out
isn't going to be so bad in practice.

+ seth
Gabor Grothendieck
#
On 7/19/07, Seth Falcon <sfalcon at fhcrc.org> wrote:
I agree that querying packages for being loaded is fragile. That is
why I originally had the question of how to query whether the database
driver was loaded.  I thought that would be more reliable than relying
on package loading since one is not likely to load a database
driver they don't intend to use.

The problem with setDefaultDb is that it adds duplicate infrastructure.
Its just something else to learn, document, use, etc. whereas the
dbDriver infrastructure already exists and I think it would be better to
be able to leverage off that.

I guess one possibility would be to have a dbDriver option and my
program could use getOptions to retrieve it, defaulting to
SQLite if the option were missing; however, even that
adds a new option that was not there before and is, in principle,
unnecessary if one could discover what drivers were loaded.