Feature request #17195

Open a database connection from metadata

Added by Paul van Genuchten almost 3 years ago. Updated almost 3 years ago.

Status:Open
Priority:Normal
Assignee:Tom Kralidis
Category:MetaSearch Catalogue Client
Pull Request or Patch supplied:No Resolution:
Easy fix?:No Copied to github as #:25094

Description

If in metadata the connection details of a database connection are provided,
Can metasearch use this information to set up a connection to the database and load the indicated table as a gis-layer?

- Is there any QGIS convention for storing a database connection as part of a resource description in metadata?
- Is there a certain protocol (like listed in https://github.com/OSGeo/Cat-Interop/blob/master/LinkPropertyLookupTable.csv) to indicate an endpoint is of type (postgre)SQL?
- When a database connection is detected can metasearch open the database connection panel to load table data as layer

History

#1 Updated by Tom Kralidis almost 3 years ago

Hi Paul: sounds feasible. notes:

- suggest we use the SQLAlchemy convention (http://docs.sqlalchemy.org/en/latest/core/engines.html#database-urls) (per RFC-1738: http://rfc.net/rfc1738.html)
- suggest we add a new link type to https://github.com/OSGeo/Cat-Interop/blob/master/LinkPropertyLookupTable.csv (i.e.
DBMS:LINK) and then metadata documents this accordingly

- then when MetaSearch detects DBMS:LINK, it detects what kind of DB is in the url then opens a signal to the DB Provider window to add the layer

Thoughts

#2 Updated by paul van genuchten almost 3 years ago

sounds good Tom,
thanks for that pointer to rfc-1738

#3 Updated by Joana Simoes almost 3 years ago

Which QGIS version would you like to target? the current one (2.18.13) or v3?

#4 Updated by Joana Simoes almost 3 years ago

Tom Kralidis wrote:

Hi Paul: sounds feasible. notes:

- suggest we use the SQLAlchemy convention (http://docs.sqlalchemy.org/en/latest/core/engines.html#database-urls) (per RFC-1738: http://rfc.net/rfc1738.html)
- suggest we add a new link type to https://github.com/OSGeo/Cat-Interop/blob/master/LinkPropertyLookupTable.csv (i.e.
DBMS:LINK) and then metadata documents this accordingly

- then when MetaSearch detects DBMS:LINK, it detects what kind of DB is in the url then opens a signal to the DB Provider window to add the layer

Thanks for the advice, Tom. So I guess we should add a new DB:link type here: https://github.com/qgis/QGIS/blob/master/python/plugins/MetaSearch/link_types.py

Then I guess we should detect this link type, when searching for services:

https://github.com/qgis/QGIS/blob/master/python/plugins/MetaSearch/dialogs/maindialog.py#L586

And send a signal to the relevant db provider. I am not sure if to open the db connection dialog, or to open the db directly passing the relevant parameters. What do you think?

#5 Updated by Tom Kralidis almost 3 years ago

Hi Joana: comments:

- let's target master for this given it's a feature
- can you submit a PR to the Cat-Interop lookup?
- as for opening the connection dialog vs connecting right away, would there be cases where no authentication params are part of the DB URL? In this case the connection dialog would be needed for interactive authentication (option a). Then again opening the connection right away takes out that step, which is good (option b).

Option A is more cautious and option B is ideal if applicable/without error. Thoughts?

Also available in: Atom PDF