[R-sig-DB] DBI preferred syntax

[Hadley Wickham]

Hi Paul,

Here are my thoughts:

* dbDriver("SQLite") - never do this. dbDriver initialisation should
only ever be performed by the package (that's the whole point of
RSQLite::SQLite).

* dbConnect("SQLite", "test") - this used to work, but I think it's a
bad idea in general, because it interfaces poorly with namesspaces

* dbConnect(SQLite(), "test") - this will work but only if RSQLite is
attached (i.e. on the search path)

* dbConnect(RSQLite::SQLite(), "test") - this is the best way in my
opinion. It doesn't require that RSQLite be attached, just DBI, which
I think is the right approach. A DBI backend (in general) should only
ever provide methods, not new generics, so it shouldn't need to be
attached. In other words, you should _never_ need require("RSQLite").

* dbConnect(getExportedValue("RSQLite", "SQLite")()) - equivalent to
above, but use strings.


Hadley



On Sat, Oct 11, 2014 at 12:18 PM, Paul Gilbert <pgilbert902 using gmail.com> wrote:
> I am trying to understand what is the new preferred way to establish a db
> connection. (And there seems to be an error in SQLite.)
>
> There also seems to be, from a user's perspective, an inconsistency in the
> call dbConnect() relative to dbDriver(), in that the first works with, for
> example, RSQLite::SQLite() and "SQLite", whereas the latter wants only the
> character string.
>
> I am using
>
> R version 3.1.1 (2014-07-10) -- "Sock it to Me"
> on Linux Mint (3.11.0-12-generic #19-Ubuntu SMP  x86_64 x86_64 x86_64
> GNU/Linux)
>
> installed.packages()[c("DBI","RSQLite", "RMySQL",
> "RPostgreSQL"),c("Package","Version")]
>             Package       Version
> DBI         "DBI"         "0.3.1"
> RSQLite     "RSQLite"     "0.11.4"
> RMySQL      "RMySQL"      "0.9-3"
> RPostgreSQL "RPostgreSQL" "0.4"
>
> For easy comparison the commands are shown here. The R session was restarted
> between testing the different packages. The sessions with error messages are
> further below.
>
> ##########
> require("RSQLite")
> z <-dbConnect("SQLite", dbname="test")          #works but see below
> z <-dbConnect(SQLite, dbname="test")            #fails
> z <-dbConnect(RSQLite::SQLite(), dbname="test") #works
> z <-dbConnect(RSQLite::SQLite, dbname="test")   #fails
>
> m <- dbDriver("SQLite")
> z <-dbConnect(m, dbname="test")                 #works
>
> m <- dbDriver(RMySQL::MySQL())                  #fails
>
> z <-dbConnect("SQLite", dbname="test")     # worked above but now fails
>
> ##########
> require("RMySQL")
> z <-dbConnect("MySQL", dbname="test")           #works
> z <-dbConnect(MySQL, dbname="test")             #fails
> z <-dbConnect(RMySQL::MySQL(), dbname="test")   #works
> z <-dbConnect(RMySQL::MySQL, dbname="test")     #fails
>
> m <- dbDriver("MySQL")
> z <-dbConnect(m, dbname="test")                 #works
>
> m <- dbDriver(RMySQL::MySQL())                  #fails
>
> ##########
> require("RPostgreSQL")
> z <-dbConnect("PostgreSQL", dbname="test")                #works
> z <-dbConnect(PostgreSQL, dbname="test")                  #fails
> z <-dbConnect(RPostgreSQL::PostgreSQL(), dbname="test")   #works
> z <-dbConnect(RPostgreSQL::PostgreSQL, dbname="test")     #fails
>
> m <- dbDriver("PostgreSQL")
> z <-dbConnect(m, dbname="test")                           #works
>
> m <- dbDriver(RPostgreSQL::PostgreSQL())                  #fails
>
>
> ##############################
>  Sessions showing errors. R was restarted for each package test.
> ##############################
>> require("RSQLite")
> Loading required package: RSQLite
> Loading required package: DBI
>> z <-dbConnect("SQLite", dbname="test")          #works but see below
>> z <-dbConnect(SQLite, dbname="test")            #fails
> Error in (function (classes, fdef, mtable)  :
>   unable to find an inherited method for function \u2018dbConnect\u2019 for
> signature \u2018"function"\u2019
>> z <-dbConnect(RSQLite::SQLite(), dbname="test") #works
>> z <-dbConnect(RSQLite::SQLite, dbname="test")   #fails
> Error in (function (classes, fdef, mtable)  :
>   unable to find an inherited method for function \u2018dbConnect\u2019 for
> signature \u2018"function"\u2019
>>
>> m <- dbDriver("SQLite")
>> z <-dbConnect(m, dbname="test")                 #works
>>
>> m <- dbDriver(RMySQL::MySQL())                  #fails
> Error in (function (classes, fdef, mtable)  :
>   unable to find an inherited method for function \u2018dbDriver\u2019 for
> signature \u2018"MySQLDriver"\u2019
>>
>> z <-dbConnect("SQLite", dbname="test")          # now fails
> Error in as.integer(from) :
>   cannot coerce type 'externalptr' to vector of type 'integer'
>
>
>
>> require("RMySQL")
> Loading required package: RMySQL
> Loading required package: DBI
>> z <-dbConnect("MySQL", dbname="test")           #works
>> z <-dbConnect(MySQL, dbname="test")             #fails
> Error in (function (classes, fdef, mtable)  :
>   unable to find an inherited method for function \u2018dbConnect\u2019 for
> signature \u2018"function"\u2019
>> z <-dbConnect(RMySQL::MySQL(), dbname="test")   #works
>> z <-dbConnect(RMySQL::MySQL, dbname="test")     #fails
> Error in (function (classes, fdef, mtable)  :
>   unable to find an inherited method for function \u2018dbConnect\u2019 for
> signature \u2018"function"\u2019
>>
>> m <- dbDriver("MySQL")
>> z <-dbConnect(m, dbname="test")                 #works
>>
>> m <- dbDriver(RMySQL::MySQL())                  #fails
> Error in (function (classes, fdef, mtable)  :
>   unable to find an inherited method for function \u2018dbDriver\u2019 for
> signature \u2018"MySQLDriver"\u2019
>
>
>
>> require("RPostgreSQL")
> Loading required package: RPostgreSQL
> Loading required package: DBI
>> z <-dbConnect("PostgreSQL", dbname="test")                #works
>> z <-dbConnect(PostgreSQL, dbname="test")                  #fails
> Error in (function (classes, fdef, mtable)  :
>   unable to find an inherited method for function \u2018dbConnect\u2019 for
> signature \u2018"function"\u2019
>> z <-dbConnect(RPostgreSQL::PostgreSQL(), dbname="test")   #works
>> z <-dbConnect(RPostgreSQL::PostgreSQL, dbname="test")     #fails
> Error in (function (classes, fdef, mtable)  :
>   unable to find an inherited method for function \u2018dbConnect\u2019 for
> signature \u2018"function"\u2019
>>
>> m <- dbDriver("PostgreSQL")
>> z <-dbConnect(m, dbname="test")                           #works
>>
>> m <- dbDriver(RPostgreSQL::PostgreSQL())                  #fails
> Error in (function (classes, fdef, mtable)  :
>   unable to find an inherited method for function \u2018dbDriver\u2019 for
> signature \u2018"PostgreSQLDriver"\u2019
>
>
> In addition to the above, I am sometimes faced with getting from a string to
> a driver.  Is there any preference for one of these rather than the other?
>
> z <-dbConnect(do.call("SQLite", list()), dbname="test")   #works
>
> z <-dbConnect(get("SQLite")(), dbname="test")   #works
>
> z <-dbConnect("SQLite", dbname="test")          #when it works
>
> or is there a better way to do this?
>
> Thanks,
> Paul



-- 
http://had.co.nz/