Import #4 : from DB connection

[pvictor]

Goal : use a DBI connection to import a table

Features:

• Use existing DBI connection from Global Env ?
• Interface to connect to a DB with DBI::dbConnect ?
• Get schemas and tables in DB and let the user choose

[struckma]

+1 -- Maybe, I can help here and also with my own fequest #42

[pvictor]

Your thoughts on what features this module should include are welcomed.

My first two concerns:

• How to connect ? Using an already existing connection object or with a GUI asking for host, port, etc ? How to select the driver ?
• How to list schemas if there are schemas, or how to allow users to find tables easily ?

[struckma]

Dear @pvictor, after a short discussion with my database colleague, we agreed that providing a GUI for connecting to arbitrary databases would clearly be out of scope for your widget, what do you think? Therefore, we could offer a drop-down with existing opened connections from the global environment or this RStudio-Connection panel. Maybe, I find time for searching for GUI packages helping with creating such connections, so that we could optionally use such a package for adding custom connections. Maybe, I consider such important, that I would start writing such a widget, not sure about the latter yet.

Regarding the selection of a schema, the SQL standard lacks a default way of retrieving these. However, according to https://en.wikipedia.org/wiki/Information_schema , the Information_schema-table is provided by some of the most prominent DBMS. However, maybe, we need to also address some of the other databases too (Oracle, DB2) -- not sure about that. However, having something working for PostgreSQL, MySQL/MariaDB and MSSQL is better than not having anything, at least in my opinion. However, DBI should be responsible for this, and it has dbListTables and dbListObjects, so we maybe should just use these functions for a first implementation?

[struckma]

Digged deeper a bit for abstract solutions (not for the first time). I would come to the conclusion, that this issue needs specific widgets for each DBI driver, because they differ too much from each other. Also, this way, the driver-selection drop-down could be filled based on manually defined rules like "PostgreSQL is available if RPostgreSQL or RPostgres are installed" . So, I would start with a specific widget for PostgreSQL, maybe later other DBI packages could be amended.

NB: Although, I found a way to find all installed DBI drivers, still, the connection parameters cannot be easily found out. DBI in R really would profit from some metadata standards to define specific arguments needed for a connection using a given driver package, and given the complexity of my driver-finder code, also the information, which DBIDrivers a package implements, if any, should be somewhere in a driver's package's metadata, maybe, similar to RStudio-Addons, in a specific additional file in the inst-directory, or as a Config/... entry in DESCRIPTION (I have seen the latter lately e.g. at https://r-pkgs.org/description.html, has the CRAN-policy about DESCRIPTION been extended?).