Open elpaso opened 3 years ago
I understand the need to move away from the db manager python plugin, but regarding the SQL window, I still have a few GUI\UX concerns.
Is this the time to discuss how the GUI implementation/integration with the main window will work? Scripts saving? Etc...?
@SrNetoChan this proposal is only about the foundation: a widget with the underlying implementation to run a query and display the results.
For this proposal I'm only planning what is in the attached screen recording plus the view creation (which is not shown).
All other functionality can be easily added on top of it when the widget is embedded into a dialog. At that point we will be able to decide whether we want to reimplement exactly the same functionality that we have in DB manager or if have better ideas.
I imagine that at some point we will have a complete replacement of DB-manager with a C++ implementation, but there are still a few missing steps.
May I add my feature request about this result widget ? :)
Having a map canvas for queries with a geom column. This would be so convenient, as a preview to check if the result is correct. Either side by side with the table, or as tab with the table. Like PgAdmin4 https://www.compose.com/articles/geofile-pgadmin-4-and-the-geometry-viewer/
This is way quicker than adding the layer manually to the main canvas. And I need to remove these layers manually, because there are just draft until I get the correct query.
@Gustry I like it, but it does not need to be part of this widget. You can add a viewer in the dialog by creating a query layer over the SQL in a separate tab.
I really want to keep this PR focused on the core functionality of running the query efficiently and smoothly and display the results in a table view, all the rest of the functionality can be easily added with the pieces the we already have.
I don't see in the screencast, but the Create View
is very useful in the DB Manager. Is this planned?
I don't see in the screencast, but the
Create View
is very useful in the DB Manager. Is this planned?
Yes, as I already mentioned in https://github.com/qgis/QGIS-Enhancement-Proposals/issues/205#issuecomment-789002932
@elpaso very nice!!
Working with pretty huge tables (millions), I'm wondering if there should be two 'retrieval' options:
one to get the actual data in case you maybe want to retrieve all (but now talking about it, you probably also use a spatial extent to retrieve them now? or not?)
one the get the result of the query to show in the 'table' in you widget
DBeaver and friends often do some sort of 'lazy loading': if you do a 'select * from table', it (silently?) only retrieves like 1000 or so... and only if you start scrolling through the table it adds more... Not sure if this is easy to implement though, but it would help ignorant users to freeze ui?
Off course users can/should also always add 'limit' in there clause... So maybe this is too much...
@elpaso very nice!!
Working with pretty huge tables (millions), I'm wondering if there should be two 'retrieval' options:
* one to get the actual data in case you maybe want to retrieve all (but now talking about it, you probably also use a spatial extent to retrieve them now? or not?) * one the get the result of the query to show in the 'table' in you widget
DBeaver and friends often do some sort of 'lazy loading': if you do a 'select * from table', it (silently?) only retrieves like 1000 or so... and only if you start scrolling through the table it adds more... Not sure if this is easy to implement though, but it would help ignorant users to freeze ui?
The proposed implementation does not freeze the UI because it uses a separate thread for fetching data, this is the complexity of this work.
I focused on making sure that the query can be interrupted, this is implemented in different ways depending on the data provider and the type of query so YMMV but my tests so far are looking good.
I tested the draft implementation with 10M postgis database and it works fast and smooth.
Off course users can/should also always add 'limit' in there clause... So maybe this is too much...
Yeah, on demand fetching is definitely possible and I like the idea but I'm not sure we need it: as long as the fetching is easily interruptible. I'm undecided for now, but as I said, it should not be difficult to implement it because I designed the fetching in a way that it already happens in batches (the fetching thread sends the fetched results to the main thread in batches).
QGIS Enhancement: Port DB Manager Table Management Functionalities to Browser: SQL execution (part 3)
Date 2021/01/16
Author Alessandro Pasotti (@elpaso)
Contact elpaso dot itopen dot it
maintainer @elpaso
Version QGIS 3.20.0
Summary
Many functionalities of DB manager are now available in the QGIS browser panel, we still miss some of them, most notably the SQL execution dialog.
With this implementation we will provided an independent widget where users can enter an SQL statement, execute it and display the results.
The widget will be exposed to Python and it will be suitable for standalone usage and for embedding into other dialogs.
Motivation
This work is being prompted by:
The desire to provide an alternative to the regressions frequently encountered in the db manager plugin (due mostly to the nature of the Python language and the inherent difficulties in creating stable, regression-free larger projects in Python).
Providing a more unified QGIS experience with database and layer management. Currently we have two completely distinct interfaces for managing databases and tables (db manager and browser). One of the long-term goals for the QGIS project is to remedy this situation by moving the functionality from db manager into the browser panel, so that QGIS offers a single place for users to performance these tasks.
Removal of duplicate code. The db manager plugin uses a lot of code which duplicates code already present in the QGIS c++ libraries. While the db manager code only has a limited test coverage, the c++ versions of this code are extremely well tested and have proved to be regression-free over recent releases. Duplicate code also adds "technical debt" and management burden to the QGIS project.
Performance Implications
Being implemented in C++ with rows fetching and auto-completion tokens fetching both happening in separate threads the performances of the new implementation are expected to be better than the current Python implementation for an overall better user experience.
Prototype example
Further Considerations/Improvements
The widget will use the newly added implementation of
QgsQueryResultModel
andQgsAbstractDatabaseProviderConnection::QueryResult
Proposed interface
Example usage in Python
Other affected classes
The connections API will need some more methods and flags:
Backwards Compatibility
None
Issue Tracking ID(s)
None
Votes
(required)