QGIS API Documentation 3.30.0-'s-Hertogenbosch (f186b8efe0)
qgsqueryresultwidget.cpp
Go to the documentation of this file.
1/***************************************************************************
2 qgsqueryresultwidget.cpp - QgsQueryResultWidget
3
4 ---------------------
5 begin : 14.1.2021
6 copyright : (C) 2021 by Alessandro Pasotti
7 email : elpaso at itopen dot it
8 ***************************************************************************
9 * *
10 * This program is free software; you can redistribute it and/or modify *
11 * it under the terms of the GNU General Public License as published by *
12 * the Free Software Foundation; either version 2 of the License, or *
13 * (at your option) any later version. *
14 * *
15 ***************************************************************************/
18#include "qgsexpressionutils.h"
19#include "qgscodeeditorsql.h"
20#include "qgsmessagelog.h"
21#include "qgsquerybuilder.h"
22#include "qgsvectorlayer.h"
23
25 : QWidget( parent )
26{
27 setupUi( this );
28
29 // Unsure :/
30 // mSqlEditor->setLineNumbersVisible( true );
31
32 mQueryResultsTableView->hide();
33 mQueryResultsTableView->setItemDelegate( new QgsQueryResultItemDelegate( mQueryResultsTableView ) );
34 mProgressBar->hide();
35
36 connect( mExecuteButton, &QPushButton::pressed, this, &QgsQueryResultWidget::executeQuery );
37 connect( mClearButton, &QPushButton::pressed, this, [ = ]
38 {
39 mSqlEditor->setText( QString() );
40 } );
41 connect( mLoadLayerPushButton, &QPushButton::pressed, this, [ = ]
42 {
43 if ( mConnection )
44 {
45 emit createSqlVectorLayer( mConnection->providerKey(), mConnection->uri(), sqlVectorLayerOptions() );
46 }
47 }
48 );
49 connect( mSqlEditor, &QgsCodeEditorSQL::textChanged, this, &QgsQueryResultWidget::updateButtons );
50 connect( mFilterToolButton, &QToolButton::pressed, this, [ = ]
51 {
52 if ( mConnection )
53 {
54 try
55 {
56 std::unique_ptr<QgsVectorLayer> vlayer { mConnection->createSqlVectorLayer( sqlVectorLayerOptions() ) };
57 QgsQueryBuilder builder{ vlayer.get() };
58 if ( builder.exec() == QDialog::Accepted )
59 {
60 mFilterLineEdit->setText( builder.sql() );
61 }
62 }
63 catch ( const QgsProviderConnectionException &ex )
64 {
65 mMessageBar->pushCritical( tr( "Error opening filter dialog" ), tr( "There was an error while preparing SQL filter dialog: %1." ).arg( ex.what() ) );
66 }
67 }
68 } );
69
70
71 mStatusLabel->hide();
72 mSqlErrorText->hide();
73
74 mLoadAsNewLayerGroupBox->setCollapsed( true );
75
76 connect( mLoadAsNewLayerGroupBox, &QgsCollapsibleGroupBox::collapsedStateChanged, this, [ = ]( bool collapsed )
77 {
78 if ( ! collapsed )
79 {
80 // Configure the load layer interface
81 const bool showPkConfig { connection &&connection->sqlLayerDefinitionCapabilities().testFlag( Qgis::SqlLayerDefinitionCapability::PrimaryKeys )};
82 mPkColumnsCheckBox->setVisible( showPkConfig );
83 mPkColumnsComboBox->setVisible( showPkConfig );
84
85 const bool showGeometryColumnConfig {connection &&connection->sqlLayerDefinitionCapabilities().testFlag( Qgis::SqlLayerDefinitionCapability::GeometryColumn )};
86 mGeometryColumnCheckBox->setVisible( showGeometryColumnConfig );
87 mGeometryColumnComboBox->setVisible( showGeometryColumnConfig );
88
89 const bool showFilterConfig { connection &&connection->sqlLayerDefinitionCapabilities().testFlag( Qgis::SqlLayerDefinitionCapability::SubsetStringFilter ) };
90 mFilterLabel->setVisible( showFilterConfig );
91 mFilterToolButton->setVisible( showFilterConfig );
92 mFilterLineEdit->setVisible( showFilterConfig );
93
94 const bool showDisableSelectAtId{ connection &&connection->sqlLayerDefinitionCapabilities().testFlag( Qgis::SqlLayerDefinitionCapability::UnstableFeatureIds ) };
95 mAvoidSelectingAsFeatureIdCheckBox->setVisible( showDisableSelectAtId );
96
97 }
98 } );
99
100 setConnection( connection );
101}
102
104{
105 cancelApiFetcher();
106 cancelRunningQuery();
107}
108
110{
111 mSqlVectorLayerOptions = options;
112 if ( ! options.sql.isEmpty() )
113 {
114 setQuery( options.sql );
115 }
116 mAvoidSelectingAsFeatureIdCheckBox->setChecked( options.disableSelectAtId );
117 mPkColumnsCheckBox->setChecked( ! options.primaryKeyColumns.isEmpty() );
118 mPkColumnsComboBox->setCheckedItems( {} );
119 if ( ! options.primaryKeyColumns.isEmpty() )
120 {
121 mPkColumnsComboBox->setCheckedItems( options.primaryKeyColumns );
122 }
123 mGeometryColumnCheckBox->setChecked( ! options.geometryColumn.isEmpty() );
124 mGeometryColumnComboBox->clear();
125 if ( ! options.geometryColumn.isEmpty() )
126 {
127 mGeometryColumnComboBox->setCurrentText( options.geometryColumn );
128 }
129 mFilterLineEdit->setText( options.filter );
130 mLayerNameLineEdit->setText( options.layerName );
131}
132
134{
135 mQueryWidgetMode = widgetMode;
136 switch ( widgetMode )
137 {
139 mLoadAsNewLayerGroupBox->setTitle( tr( "Load as New Layer" ) );
140 mLoadLayerPushButton->setText( tr( "Load Layer" ) );
141 mLoadAsNewLayerGroupBox->setCollapsed( true );
142 break;
144 mLoadAsNewLayerGroupBox->setTitle( tr( "Update Query Layer" ) );
145 mLoadLayerPushButton->setText( tr( "Update Layer" ) );
146 mLoadAsNewLayerGroupBox->setCollapsed( false );
147 break;
148 }
149}
150
152{
153 mQueryResultsTableView->hide();
154 mSqlErrorText->hide();
155 mFirstRowFetched = false;
156
157 cancelRunningQuery();
158
159 if ( mConnection )
160 {
161 const QString sql { mSqlEditor->text( ) };
162
163 mWasCanceled = false;
164 mFeedback = std::make_unique<QgsFeedback>();
165 mStopButton->setEnabled( true );
166 mStatusLabel->show();
167 mStatusLabel->setText( tr( "Executing query…" ) );
168 mProgressBar->show();
169 mProgressBar->setRange( 0, 0 );
170 mSqlErrorMessage.clear();
171
172 connect( mStopButton, &QPushButton::pressed, mFeedback.get(), [ = ]
173 {
174 mStatusLabel->setText( tr( "Stopped" ) );
175 mFeedback->cancel();
176 mProgressBar->hide();
177 mWasCanceled = true;
178 } );
179
180 // Create model when result is ready
181 connect( &mQueryResultWatcher, &QFutureWatcher<QgsAbstractDatabaseProviderConnection::QueryResult>::finished, this, &QgsQueryResultWidget::startFetching, Qt::ConnectionType::UniqueConnection );
182
183 QFuture<QgsAbstractDatabaseProviderConnection::QueryResult> future = QtConcurrent::run( [ = ]() -> QgsAbstractDatabaseProviderConnection::QueryResult
184 {
185 try
186 {
187 return mConnection->execSql( sql, mFeedback.get() );
188 }
190 {
191 mSqlErrorMessage = ex.what();
193 }
194 } );
195 mQueryResultWatcher.setFuture( future );
196 }
197 else
198 {
199 showError( tr( "Connection error" ), tr( "Cannot execute query: connection to the database is not available." ) );
200 }
201}
202
203void QgsQueryResultWidget::updateButtons()
204{
205 mFilterLineEdit->setEnabled( mFirstRowFetched );
206 mFilterToolButton->setEnabled( mFirstRowFetched );
207 mExecuteButton->setEnabled( ! mSqlEditor->text().isEmpty() );
208 mLoadAsNewLayerGroupBox->setVisible( mConnection && mConnection->capabilities().testFlag( QgsAbstractDatabaseProviderConnection::Capability::SqlLayers ) );
209 mLoadAsNewLayerGroupBox->setEnabled(
210 mSqlErrorMessage.isEmpty() &&
211 mFirstRowFetched
212 );
213}
214
215void QgsQueryResultWidget::updateSqlLayerColumns( )
216{
217 // Precondition
218 Q_ASSERT( mModel );
219
220 mFilterToolButton->setEnabled( true );
221 mFilterLineEdit->setEnabled( true );
222 mPkColumnsComboBox->clear();
223 mGeometryColumnComboBox->clear();
224 const bool hasPkInformation { ! mSqlVectorLayerOptions.primaryKeyColumns.isEmpty() };
225 const bool hasGeomColInformation { ! mSqlVectorLayerOptions.geometryColumn.isEmpty() };
226 static const QStringList geomColCandidates { QStringLiteral( "geom" ), QStringLiteral( "geometry" ), QStringLiteral( "the_geom" ) };
227 const QStringList constCols { mModel->columns() };
228 for ( const QString &c : constCols )
229 {
230 const bool pkCheckedState = hasPkInformation ? mSqlVectorLayerOptions.primaryKeyColumns.contains( c ) : c.contains( QStringLiteral( "id" ), Qt::CaseSensitivity::CaseInsensitive );
231 // Only check first match
232 mPkColumnsComboBox->addItemWithCheckState( c, pkCheckedState && mPkColumnsComboBox->checkedItems().isEmpty() ? Qt::CheckState::Checked : Qt::CheckState::Unchecked );
233 mGeometryColumnComboBox->addItem( c );
234 if ( ! hasGeomColInformation && geomColCandidates.contains( c, Qt::CaseSensitivity::CaseInsensitive ) )
235 {
236 mGeometryColumnComboBox->setCurrentText( c );
237 }
238 }
239 mPkColumnsCheckBox->setChecked( hasPkInformation );
240 mGeometryColumnCheckBox->setChecked( hasGeomColInformation );
241 if ( hasGeomColInformation )
242 {
243 mGeometryColumnComboBox->setCurrentText( mSqlVectorLayerOptions.geometryColumn );
244 }
245}
246
247void QgsQueryResultWidget::cancelRunningQuery()
248{
249 // Cancel other threads
250 if ( mFeedback )
251 {
252 mFeedback->cancel();
253 }
254
255 // ... and wait
256 if ( mQueryResultWatcher.isRunning() )
257 {
258 mQueryResultWatcher.waitForFinished();
259 }
260}
261
262void QgsQueryResultWidget::cancelApiFetcher()
263{
264 if ( mApiFetcher )
265 {
266 mApiFetcher->stopFetching();
267 mApiFetcherWorkerThread.quit();
268 mApiFetcherWorkerThread.wait();
269 }
270}
271
272void QgsQueryResultWidget::startFetching()
273{
274 if ( ! mWasCanceled )
275 {
276 if ( ! mSqlErrorMessage.isEmpty() )
277 {
278 showError( tr( "SQL error" ), mSqlErrorMessage, true );
279 }
280 else
281 {
282 if ( mQueryResultWatcher.result().rowCount() != static_cast<long long>( Qgis::FeatureCountState::UnknownCount ) )
283 {
284 mStatusLabel->setText( QStringLiteral( "Query executed successfully (%1 rows, %2 ms)" )
285 .arg( QLocale().toString( mQueryResultWatcher.result().rowCount() ),
286 QLocale().toString( mQueryResultWatcher.result().queryExecutionTime() ) ) );
287 }
288 else
289 {
290 mStatusLabel->setText( QStringLiteral( "Query executed successfully (%1 s)" ).arg( QLocale().toString( mQueryResultWatcher.result().queryExecutionTime() ) ) );
291 }
292 mProgressBar->hide();
293 mModel = std::make_unique<QgsQueryResultModel>( mQueryResultWatcher.result() );
294 connect( mFeedback.get(), &QgsFeedback::canceled, mModel.get(), [ = ]
295 {
296 mModel->cancel();
297 mWasCanceled = true;
298 } );
299
300 connect( mModel.get(), &QgsQueryResultModel::fetchMoreRows, this, [ = ]( long long maxRows )
301 {
302 mFetchedRowsBatchCount = 0;
303 mProgressBar->setRange( 0, maxRows );
304 mProgressBar->show();
305 } );
306
307 connect( mModel.get(), &QgsQueryResultModel::rowsInserted, this, [ = ]( const QModelIndex &, int first, int last )
308 {
309 if ( ! mFirstRowFetched )
310 {
311 emit firstResultBatchFetched();
312 mFirstRowFetched = true;
313 mQueryResultsTableView->show();
314 updateButtons();
315 updateSqlLayerColumns( );
316 mActualRowCount = mModel->queryResult().rowCount();
317 }
318 mStatusLabel->setText( tr( "Fetched rows: %1/%2 %3 %4 ms" )
319 .arg( QLocale().toString( mModel->rowCount( mModel->index( -1, -1 ) ) ),
320 mActualRowCount != -1 ? QLocale().toString( mActualRowCount ) : tr( "unknown" ),
321 mWasCanceled ? tr( "(stopped)" ) : QString(),
322 QLocale().toString( mQueryResultWatcher.result().queryExecutionTime() ) ) );
323 mFetchedRowsBatchCount += last - first + 1;
324 mProgressBar->setValue( mFetchedRowsBatchCount );
325 } );
326
327 mQueryResultsTableView->setModel( mModel.get() );
328 mQueryResultsTableView->show();
329
330 connect( mModel.get(), &QgsQueryResultModel::fetchingComplete, mStopButton, [ = ]
331 {
332 mProgressBar->hide();
333 mStopButton->setEnabled( false );
334 } );
335 }
336 }
337 else
338 {
339 mStatusLabel->setText( tr( "SQL command aborted" ) );
340 mProgressBar->hide();
341 }
342}
343
344void QgsQueryResultWidget::showError( const QString &title, const QString &message, bool isSqlError )
345{
346 mStatusLabel->show();
347 mStatusLabel->setText( tr( "An error occurred while executing the query" ) );
348 mProgressBar->hide();
349 mQueryResultsTableView->hide();
350 if ( isSqlError )
351 {
352 mSqlErrorText->show();
353 mSqlErrorText->setText( message );
354 }
355 else
356 {
357 mMessageBar->pushCritical( title, message );
358 }
359}
360
361void QgsQueryResultWidget::tokensReady( const QStringList &tokens )
362{
363 mSqlEditor->setExtraKeywords( mSqlEditor->extraKeywords() + tokens );
364 mSqlErrorText->setExtraKeywords( mSqlErrorText->extraKeywords() + tokens );
365}
366
367
368QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions QgsQueryResultWidget::sqlVectorLayerOptions() const
369{
370 mSqlVectorLayerOptions.sql = mSqlEditor->text();
371 mSqlVectorLayerOptions.filter = mFilterLineEdit->text();
372 mSqlVectorLayerOptions.primaryKeyColumns = mPkColumnsComboBox->checkedItems();
373 mSqlVectorLayerOptions.geometryColumn = mGeometryColumnComboBox->currentText();
374 mSqlVectorLayerOptions.layerName = mLayerNameLineEdit->text();
375 mSqlVectorLayerOptions.disableSelectAtId = mAvoidSelectingAsFeatureIdCheckBox->isChecked();
376 QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions options { mSqlVectorLayerOptions };
377 // Override if not used
378 if ( ! mPkColumnsCheckBox->isChecked() )
379 {
380 options.primaryKeyColumns.clear();
381 }
382 if ( ! mGeometryColumnCheckBox->isChecked() )
383 {
384 options.geometryColumn.clear();
385 }
386 return options;
387}
388
390{
391 mConnection.reset( connection );
392
393 cancelApiFetcher();
394
395 if ( connection )
396 {
397
398 // Add provider specific APIs
399 const QMultiMap<Qgis::SqlKeywordCategory, QStringList> keywordsDict { connection->sqlDictionary() };
400 QStringList keywords;
401 for ( auto it = keywordsDict.constBegin(); it != keywordsDict.constEnd(); it++ )
402 {
403 keywords.append( it.value() );
404 }
405
406 // Add static keywords from provider
407 mSqlEditor->setExtraKeywords( keywords );
408 mSqlErrorText->setExtraKeywords( keywords );
409
410 // Add dynamic keywords in a separate thread
411 mApiFetcher = std::make_unique<QgsConnectionsApiFetcher>( connection );
412 mApiFetcher->moveToThread( &mApiFetcherWorkerThread );
413 connect( &mApiFetcherWorkerThread, &QThread::started, mApiFetcher.get(), &QgsConnectionsApiFetcher::fetchTokens );
414 connect( mApiFetcher.get(), &QgsConnectionsApiFetcher::tokensReady, this, &QgsQueryResultWidget::tokensReady );
415 connect( mApiFetcher.get(), &QgsConnectionsApiFetcher::fetchingFinished, &mApiFetcherWorkerThread, [ = ]
416 {
417 mApiFetcherWorkerThread.quit();
418 mApiFetcherWorkerThread.wait();
419 } );
420 mApiFetcherWorkerThread.start();
421 }
422
423 updateButtons();
424
425}
426
427void QgsQueryResultWidget::setQuery( const QString &sql )
428{
429 mSqlEditor->setText( sql );
430}
431
432void QgsQueryResultWidget::notify( const QString &title, const QString &text, Qgis::MessageLevel level )
433{
434 mMessageBar->pushMessage( title, text, level );
435}
436
437
439
440void QgsConnectionsApiFetcher::fetchTokens()
441{
442 if ( ! mStopFetching && mConnection )
443 {
444 QStringList schemas;
445 if ( mConnection->capabilities().testFlag( QgsAbstractDatabaseProviderConnection::Capability::Schemas ) )
446 {
447 try
448 {
449 schemas = mConnection->schemas();
450 emit tokensReady( schemas );
451 }
453 {
454 QgsMessageLog::logMessage( tr( "Error retrieving schemas: %1" ).arg( ex.what() ), QStringLiteral( "QGIS" ), Qgis::MessageLevel::Warning );
455 }
456 }
457 else
458 {
459 schemas.push_back( QString() ); // Fake empty schema for DBs not supporting it
460 }
461
462 for ( const auto &schema : std::as_const( schemas ) )
463 {
464
465 if ( mStopFetching )
466 {
467 return;
468 }
469
470 QStringList tableNames;
471 try
472 {
473 const QList<QgsAbstractDatabaseProviderConnection::TableProperty> tables = mConnection->tables( schema );
474 for ( const QgsAbstractDatabaseProviderConnection::TableProperty &table : std::as_const( tables ) )
475 {
476 if ( mStopFetching ) { return; }
477 tableNames.push_back( table.tableName() );
478 }
479 emit tokensReady( tableNames );
480 }
482 {
483 QgsMessageLog::logMessage( tr( "Error retrieving tables: %1" ).arg( ex.what() ), QStringLiteral( "QGIS" ), Qgis::MessageLevel::Warning );
484 }
485
486 // Get fields
487 for ( const auto &table : std::as_const( tableNames ) )
488 {
489
490 if ( mStopFetching )
491 {
492 return;
493 }
494
495 QStringList fieldNames;
496 try
497 {
498 const QgsFields fields( mConnection->fields( schema, table ) );
499 if ( mStopFetching ) { return; }
500 for ( const auto &field : std::as_const( fields ) )
501 {
502 fieldNames.push_back( field.name() );
503 if ( mStopFetching ) { return; }
504 }
505 emit tokensReady( fieldNames );
506 }
508 {
509 QgsMessageLog::logMessage( tr( "Error retrieving fields for table %1: %2" ).arg( table, ex.what() ), QStringLiteral( "QGIS" ), Qgis::MessageLevel::Warning );
510 }
511 }
512 }
513 }
514 emit fetchingFinished();
515}
516
517void QgsConnectionsApiFetcher::stopFetching()
518{
519 mStopFetching = 1;
520}
521
522
523QgsQueryResultItemDelegate::QgsQueryResultItemDelegate( QObject *parent )
524 : QStyledItemDelegate( parent )
525{
526}
527
528QString QgsQueryResultItemDelegate::displayText( const QVariant &value, const QLocale &locale ) const
529{
530 Q_UNUSED( locale )
531 QString result { QgsExpressionUtils::toLocalizedString( value ) };
532 // Show no more than 255 characters
533 if ( result.length() > 255 )
534 {
535 result.truncate( 255 );
536 result.append( QStringLiteral( "…" ) );
537 }
538 return result;
539}
540
MessageLevel
Level for messages This will be used both for message log and message bar in application.
Definition: qgis.h:100
@ UnstableFeatureIds
SQL layer definition supports disabling select at id.
@ SubsetStringFilter
SQL layer definition supports subset string filter.
@ PrimaryKeys
SQL layer definition supports primary keys.
@ GeometryColumn
SQL layer definition supports geometry column.
The QgsAbstractDatabaseProviderConnection class provides common functionality for DB based connection...
virtual Qgis::SqlLayerDefinitionCapabilities sqlLayerDefinitionCapabilities()
Returns SQL layer definition capabilities (Filters, GeometryColumn, PrimaryKeys).
virtual QMultiMap< Qgis::SqlKeywordCategory, QStringList > sqlDictionary()
Returns a dictionary of SQL keywords supported by the provider.
void collapsedStateChanged(bool collapsed)
Signal emitted when groupbox collapsed/expanded state is changed, and when first shown.
QString what() const
Definition: qgsexception.h:48
void canceled()
Internal routines can connect to this signal if they use event loop.
QString name
Definition: qgsfield.h:61
Container of fields for a vector layer.
Definition: qgsfields.h:45
static void logMessage(const QString &message, const QString &tag=QString(), Qgis::MessageLevel level=Qgis::MessageLevel::Warning, bool notifyUser=true)
Adds a message to the log instance (and creates it if necessary).
Custom exception class for provider connection related exceptions.
Definition: qgsexception.h:101
Query Builder for layers.
void fetchMoreRows(qlonglong maxRows)
Emitted when more rows are requested.
void fetchingComplete()
Emitted when rows have been fetched (all of them or a batch if maxRows was passed to fetchMoreRows() ...
void tokensReady(const QStringList &tokens)
Triggered when the threaded API fetcher has new tokens to add.
void notify(const QString &title, const QString &text, Qgis::MessageLevel level=Qgis::MessageLevel::Info)
Displays a message with text title and level in the widget's message bar.
void setQuery(const QString &sql)
Convenience method to set the SQL editor text to sql.
void setSqlVectorLayerOptions(const QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions &options)
Initializes the widget from options.
QgsQueryResultWidget(QWidget *parent=nullptr, QgsAbstractDatabaseProviderConnection *connection=nullptr)
Creates a QgsQueryResultWidget with the given connection, ownership is transferred to the widget.
void setConnection(QgsAbstractDatabaseProviderConnection *connection)
Sets the connection to connection, ownership is transferred to the widget.
void showError(const QString &title, const QString &message, bool isSqlError=false)
Hides the result table and shows the error title and message in the message bar or in the SQL error p...
void executeQuery()
Starts executing the query.
QueryWidgetMode
The QueryWidgetMode enum represents various modes for the widget appearance.
@ QueryLayerUpdateMode
SQL query layer update mode: the create SQL layer button is renamed to 'Update' and the SQL layer cre...
@ SqlQueryMode
Defaults widget mode for SQL execution and SQL query layer creation.
void createSqlVectorLayer(const QString &providerKey, const QString &connectionUri, const QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions &options)
Emitted when a new vector SQL (query) layer must be created.
void setWidgetMode(QueryWidgetMode widgetMode)
Sets the widget mode to widgetMode, default is SqlQueryMode.
@ UnknownCount
Provider returned an unknown feature count.
As part of the API refactoring and improvements which landed in the Processing API was substantially reworked from the x version This was done in order to allow much of the underlying Processing framework to be ported into c
const QgsField & field
Definition: qgsfield.h:501
The QueryResult class represents the result of a query executed by execSql()
The SqlVectorLayerOptions stores all information required to create a SQL (query) layer.
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 s...
bool disableSelectAtId
If SelectAtId is disabled (default is false), not all data providers support this feature: check supp...
The TableProperty class represents a database table or view.