[FR] Allow database name in in_schema

[thothal]

When connecting to an MSSQL Server, we can have several databases from which we can pull data. To get the quoting straight, one has to add an additional sql call to in_schema to be able to get the data in a particular database, which is not that clear:

library(dbplyr)

(s1 <- in_schema("mydatabase.myschema", "mytable"))
## <SCHEMA> `mydatabase.myschema`.`mytable`
(s2 <- in_schema(sql("mydatabase.myschema"), "mytable"))
## <SCHEMA> mydatabase.myschema.`mytable`
# library(dplyr)
# con <-  dbConnect(odbc::odbc(), "mydatabase_server")
# tbl(con, s1) ## will not work
# tbl(con, s2) ## will work

Admittedly, my knowledge of standard SQL is mediocre at best, so there may be very good reasons for not adding a catalog/database parameter to in_schema, but at least the proper way of addressing a table in a schema in a catalog / database should be documented somewhere. w/o SO I would yet not have any idea of how to pull data with the fully qualified name.

Thus, either we add that to the docs or even add a new parameter to in_schema.

[krlmlr]

I agree this would be useful. Should we add a new in_database() function to keep a logical argument order? Internally, it could be mapped to the same class.

[hadley]

I think adding in_database() would be the easisest approach.

[krlmlr]

I can propose something very soon, can we sneak this into 2.2.0?

[thothal]

Thanks to see some progress on that. If you are creating a new function anyways it may make sense to think about linked servers as well? That is a query like

SELECT foo
  FROM linked_server.database.schema.table_name

is valid and while in_schema and soon in_database take care about properly escaping database and schema respectively there is no mechanism (yet) to mask linked_server. Addmitedly, this may be en an edge case, but if you are anyways creating a new in_database function, you may want to add an optional parameter for the linked_server as well.

You could argue that one could create another connection to linked_server, but for instance in our setting here that would not be possible, b/c while I can connect to server A (which provides a linked server handle to server B) I cannot directly connect to server B. That is the only way to join data from server A and server B is via the linked server handle.