Port DB Manager Table Management Functionalities to Browser: SQL execution (part 3)

[elpaso]

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:

1. 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).

2. 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.

3. 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 and QgsAbstractDatabaseProviderConnection::QueryResult

Proposed interface

/**
 * The QgsQueryResultWidget class allow users to enter and run an SQL query on a
 * DB connection (an instance of QgsAbstractDatabaseProviderConnection).
 *
 * Query results are displayed in a table view.
 * Query execution and result
 * fetching can be interrupted by pressing the "Stop" push button.
 *
 * \note the ownership of the connection is transferred to the widget.
 *
 * \since QGIS 3.20
 */
class GUI_EXPORT QgsQueryResultWidget: public QWidget, private Ui::QgsQueryResultWidgetBase
{
    Q_OBJECT

  public:

    /**
     * Creates a QgsQueryResultWidget with the given \a connection, ownership is transferred to the widget.
     */
    QgsQueryResultWidget( QWidget *parent = nullptr, QgsAbstractDatabaseProviderConnection *connection SIP_TRANSFER = nullptr );

    virtual ~QgsQueryResultWidget();

    /**
     * Set the connection to \a connection, ownership is transferred to the widget.
     */
    void setConnection( QgsAbstractDatabaseProviderConnection *connection );

    /**
     * Convenience method to set the SQL editor test to \a sql.
     */
    void setQuery( const QString &sql );

  public slots:

    /**
     * Executes the query
     */
    void executeQuery();

    /**
     * Shows the error \a title and \a message in the message bar.
     */
    void showError( const QString &title, const QString &message );

};

Example usage in Python

md = QgsProviderRegistry.instance().providerMetadata('postgres')
conn = md.createConnection(self.uri, {})
widget = QgsQueryResultWidget(None, conn)
widget.setQuery("SELECT * FROM qgis_test.random_big_data")
d = QDialog()
l = QVBoxLayout(d)
d.setLayout(l)
l.addWidget(widget)
d.exec_()

Other affected classes

The connections API will need some more methods and flags:

class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProviderConnection
{

[...]

    /**
     * \brief The SqlVectorLayerOptions stores all information required to create a SQL (query) layer.
     * \see createSqlVectorLayer()
     *
     * \since QGIS 3.20
     */
    struct CORE_EXPORT SqlVectorLayerOptions {
        QString sql; //! The SQL expression that defines the SQL (query) layer
        QString filter; //! Additional subset string (provider-side filter), not all data providers support this feature: check support with SqlLayerFilters capability
        QString layerName; //! Optional name for the new layer
        QStringList primaryKeyColumns; //! List of primary key column names
        QString geometryColumn; //! Name of the geometry column
    };

[...]
    /**
     * \brief The Capability enum represents the operations supported by the connection
     */
    enum Capability
    {
     [...]     
      SqlLayerFilters = 1 << 22,    //!< SQL Layers support filters (subset strings), implies SqlLayers capability
    };

[...]

    /**
     * Creates and returns a vector layer based on the SQL vector layer \a options.
     * Raises a QgsProviderConnectionException if any errors are encountered or if SQL layer creation is not supported.
     * \throws QgsProviderConnectionException
     * \since QGIS 3.20
     */
    virtual QgsVectorLayer* createSqlVectorLayer(const SqlVectorLayerOptions &options ) const SIP_THROW( QgsProviderConnectionException ) SIP_FACTORY;

Backwards Compatibility

None

Issue Tracking ID(s)

None

Votes

(required)

[SrNetoChan]

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...?

[elpaso]

@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.

[Gustry]

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.

[elpaso]

@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.

[ghtmtt]

I don't see in the screencast, but the Create View is very useful in the DB Manager. Is this planned?

[elpaso]

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 #205 (comment)

[rduivenvoorde]

@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]

@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).