Include synonyms in `odbcListObjects()`, `dbListTables()`, and the Connections Pane

[ThomasSoeiro]

The issue discussed in #773 for SQL Server also applies to Oracle. So here is a separate issue as suggested by @simonpcouch.

In Oracle, synonyms are available in {ALL|USER}_SYNONYMS.

Same reprex as in https://github.com/r-dbi/odbc/pull/773#issuecomment-2021287247:

library(DBI)
con <- dbConnect(odbc::odbc(), dsn = "zzz")

dbExecute(con, "create table test (x int)")
# [1] 0

user <- dbGetQuery(con, "select user from dual")$USER

# confirm that we can find the table:
odbc::odbcListObjects(con, schema = user)
#   name  type
# 1 TEST table
dbListTables(con)
# [1] "TEST"

# make a synonym and show that it can't be found:
dbExecute(con, "create synonym test2 for test")
# [1] 0
odbc::odbcListObjects(con, schema = user)
#   name  type
# 1 TEST table
dbListTables(con)
# [1] "TEST"

However:

• Showing synonyms in dbListTables() would be problematic in some cases. E.g. our database has many synonyms (>3500) so showing all synonyms would make dbListTables() useless. Maybe an argument synonyms = TRUE|FALSE could be use?
• Showing synonyms in the connection would worsen the situation if the database contains thousands of objects (see #605). Maybe it should be taken care of when improving the connection pane? (see #447 and to a lesser extent #158)

Database

dbGetInfo(con)
# $dbname
# [1] ""
# 
# $dbms.name
# [1] "Oracle"
# 
# $db.version
# [1] "19.00.0000"
# 
# $username
# [1] ""
# 
# $host
# [1] ""
# 
# $port
# [1] ""
# 
# $sourcename
# [1] "zzz"
# 
# $servername
# [1] "zzz"
# 
# $drivername
# [1] "RStudio Oracle ODBC Driver"
# 
# $odbc.version
# [1] "03.52"
# 
# $driver.version
# [1] "2.0.2.1002"
# 
# $odbcdriver.version
# [1] "03.80"
# 
# $supports.transactions
# [1] TRUE
# 
# $getdata.extensions.any_column
# [1] TRUE
# 
# $getdata.extensions.any_order
# [1] TRUE
# 
# attr(,"class")
# [1] "Oracle"      "driver_info" "list"       

Session Info

sessionInfo()
# R version 4.1.2 (2021-11-01)
# Platform: x86_64-pc-linux-gnu (64-bit)
# Running under: Red Hat Enterprise Linux Server 7.8 (Maipo)
# 
# Matrix products: default
# BLAS/LAPACK: /usr/lib64/libopenblasp-r0.3.3.so
# 
# locale:
#  [1] LC_CTYPE=fr_FR.UTF-8       LC_NUMERIC=C               LC_TIME=fr_FR.UTF-8        LC_COLLATE=fr_FR.UTF-8    
#  [5] LC_MONETARY=fr_FR.UTF-8    LC_MESSAGES=fr_FR.UTF-8    LC_PAPER=fr_FR.UTF-8       LC_NAME=C                 
#  [9] LC_ADDRESS=C               LC_TELEPHONE=C             LC_MEASUREMENT=fr_FR.UTF-8 LC_IDENTIFICATION=C       
# 
# attached base packages:
# [1] stats     graphics  grDevices datasets  utils     methods   base     
# 
# other attached packages:
# [1] DBI_1.0.0
# 
# loaded via a namespace (and not attached):
#  [1] bit_1.1-14      odbc_1.3.5      compiler_4.1.2  ellipsis_0.3.2  hms_1.1.1       tools_4.1.2     Rcpp_1.0.7      bit64_0.9-7    
#  [9] vctrs_0.3.8     blob_1.2.2      lifecycle_1.0.1 pkgconfig_2.0.2 rlang_0.4.12   

[simonpcouch]

Thanks for filing here!

Yup, I hear you that there are performance concerns here. May be worth making the inclusion of synonyms opt-in.