wrapper function in odbc/dbplyr, doesn't produce connections pane ("connections contract")

beau · March 5, 2019, 11:34pm

Hi all, I am developing an R package (lets call it dbr) at work so users can easily connect via ODBC to a database without having to remember any boilerplate. The workflow would be to assign a variable to dbr::connect_database.

The Issue:

While all it does is wrap/abstract away a standard odbc call, it does not pass the "Connections Contract" that produces the Connections pane information in RStudio. Given that odbc already has this code, I'm guessing I am missing some quirk of R that is preventing this from being passed.

What I Found:

I did some research on this and found information about the Connections Contract. Given that odbc already has this built in, and I am simply wrapping the function, it seems that I shouldn't have to rewrite this from scratch.

https://db.rstudio.com/advanced/contract/

github.com

r-dbi/odbc/blob/5acc55350e0664002d6281f4659c089911fbd7c3/R/Viewer.R

#' Return the object hierarchy supported by a connection.
#'
#' Lists the object types and metadata known by the connection, and how those
#' object types relate to each other.
#'
#' The returned hierarchy takes the form of a nested list, in which each object
#' type supported by the connection is a named list with the following
#' attributes:
#'
#' \describe{
#'   \item{contains}{A list of other object types contained by the object, or
#'       "data" if the object contains data}
#'   \item{icon}{An optional path to an icon representing the type}
#' }
#
#' For instance, a connection in which the top-level object is a schema that
#' contains tables and views, the function will return a list like the
#' following:
#'
#' \preformatted{list(schema = list(contains = list(

This file has been truncated. show original

My Code

This code is reproducible if you have set up an ODBC connection. When connecting to DB via the wrapper function, everything works except the Connections Pane. When calling the exact code outside of the function, the Connections Pane reappears.

connect_database <- function(dsn = "database") {

  DBI::dbConnect(odbc::odbc(),
                 dsn = dsn,
                 username = keyring::key_list("database")[1,2],
                 password = keyring::key_get("database"))

}

Has anybody dealt with this? Why is the odbc Connections Contract not passed when wrapped inside of a function call? I am having trouble googling around for it. My idea was that the outcome of calling the code should work exactly the same whether or not its called within a function. Assigning a variable such as con to both types of executions (inside a function and outside a function) produce a global environment variable with identical attributes.

Thanks!

cderv · March 6, 2019, 9:04pm

I think this is not working as you expect because of this code

github.com

r-dbi/odbc/blob/7b35549f9df935e1d132f6221860f87a6eb64ef6/R/Driver.R#L112-L128


# perform the connection notification at the top level, to ensure that it's had
# a chance to get its external pointer connected, and so we can capture the
# expression that created it
if (!is.null(getOption("connectionObserver"))) { # nocov start
  addTaskCallback(function(expr, ...) {
    tryCatch({
      if (is.call(expr) &&
          as.character(expr[[1]]) %in% c("<-", "=") &&
          "dbConnect" %in% as.character(expr[[3]][[1]])) {


        # notify if this is an assignment we can replay
        on_connection_opened(eval(expr[[2]]), paste(
          c("library(DBI)", deparse(expr)), collapse = "\n"))
      }
    }, error = function(e) {
      warning("Could not notify connection observer. ", e$message, call. = FALSE)
    })

The notification to the connection pane is done only if dbConnect() is called at the top level on an odbc object. When you encapsulate in a function it is not more top level.

You can follow the documentation and obdc example to try implement connection pane in your own package
https://rstudio.github.io/rstudio-extensions/connections-contract.html

or you can try to create a new method for dbConnect in your package following DBI standard I guess...

Hope it helps.

beau · March 7, 2019, 6:12pm

Thank you @cderv

Is there any resource to understand the behind-the-scenes difference when calling something in a function vs top-level?

I understand that I can implement my own "Connections Contract", (albeit it is probably easier to tell analysts to remember the DBI::dbConnect call), but given that odbc and dbConnect already have it implemented, and I am using a vanilla version of both, is there not a way I can bring that execution back to the top-level?

Again, thanks! This is a small topic to google about, and I appreciate the time.

grrrck · March 7, 2019, 6:32pm

Here's a somewhat outside-of-the-box suggestion. Your package could provide a small RStudio addin that generates the correct DBI::dbConnect() code using a small shiny app with options relevant to your users' situation. The code can then be written directly into the open source document or run in the console. This idea was completely inspired by Hao Zhu's rstudio::conf talk https://resources.rstudio.com/rstudio-conf-2019/empowering-a-data-team-with-rstudio-addins

cderv · March 7, 2019, 10:03pm

Not that I know of. I stumble upon this code once, so it is how I know.

Also, we included connection contract into our own function at my company in a .

system · March 28, 2019, 10:03pm

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.