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 ***************************************************************************/
16 #include "qgsqueryresultwidget.h"
17 #include "qgsabstractdatabaseproviderconnection.h"
18 #include "qgsexpressionutils.h"
19 #include "qgscodeeditorsql.h"
20 #include "qgsmessagelog.h"
21 #include "qgsquerybuilder.h"
22 #include "qgsvectorlayer.h"
23
QgsQueryResultWidget(QWidget * parent,QgsAbstractDatabaseProviderConnection * connection)24 QgsQueryResultWidget::QgsQueryResultWidget( QWidget *parent, QgsAbstractDatabaseProviderConnection *connection )
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
~QgsQueryResultWidget()103 QgsQueryResultWidget::~QgsQueryResultWidget()
104 {
105 cancelApiFetcher();
106 cancelRunningQuery();
107 }
108
setSqlVectorLayerOptions(const QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions & options)109 void QgsQueryResultWidget::setSqlVectorLayerOptions( const QgsAbstractDatabaseProviderConnection::SqlVectorLayerOptions &options )
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
setWidgetMode(QueryWidgetMode widgetMode)133 void QgsQueryResultWidget::setWidgetMode( QueryWidgetMode widgetMode )
134 {
135 mQueryWidgetMode = widgetMode;
136 switch ( widgetMode )
137 {
138 case QueryWidgetMode::SqlQueryMode:
139 mLoadAsNewLayerGroupBox->setTitle( tr( "Load as New Layer" ) );
140 mLoadLayerPushButton->setText( tr( "Load Layer" ) );
141 mLoadAsNewLayerGroupBox->setCollapsed( true );
142 break;
143 case QueryWidgetMode::QueryLayerUpdateMode:
144 mLoadAsNewLayerGroupBox->setTitle( tr( "Update Query Layer" ) );
145 mLoadLayerPushButton->setText( tr( "Update Layer" ) );
146 mLoadAsNewLayerGroupBox->setCollapsed( false );
147 break;
148 }
149 }
150
executeQuery()151 void QgsQueryResultWidget::executeQuery()
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 }
189 catch ( QgsProviderConnectionException &ex )
190 {
191 mSqlErrorMessage = ex.what();
192 return QgsAbstractDatabaseProviderConnection::QueryResult();
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
updateButtons()203 void 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
updateSqlLayerColumns()215 void 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
cancelRunningQuery()247 void 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
cancelApiFetcher()262 void QgsQueryResultWidget::cancelApiFetcher()
263 {
264 if ( mApiFetcher )
265 {
266 mApiFetcher->stopFetching();
267 mApiFetcherWorkerThread.quit();
268 mApiFetcherWorkerThread.wait();
269 }
270 }
271
startFetching()272 void 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
showError(const QString & title,const QString & message,bool isSqlError)344 void 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
tokensReady(const QStringList & tokens)361 void QgsQueryResultWidget::tokensReady( const QStringList &tokens )
362 {
363 mSqlEditor->setExtraKeywords( mSqlEditor->extraKeywords() + tokens );
364 mSqlErrorText->setExtraKeywords( mSqlErrorText->extraKeywords() + tokens );
365 }
366
367
sqlVectorLayerOptions() const368 QgsAbstractDatabaseProviderConnection::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
setConnection(QgsAbstractDatabaseProviderConnection * connection)389 void QgsQueryResultWidget::setConnection( QgsAbstractDatabaseProviderConnection *connection )
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
setQuery(const QString & sql)427 void QgsQueryResultWidget::setQuery( const QString &sql )
428 {
429 mSqlEditor->setText( sql );
430 }
431
notify(const QString & title,const QString & text,Qgis::MessageLevel level)432 void QgsQueryResultWidget::notify( const QString &title, const QString &text, Qgis::MessageLevel level )
433 {
434 mMessageBar->pushMessage( title, text, level );
435 }
436
437
438 ///@cond private
439
fetchTokens()440 void 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 }
452 catch ( QgsProviderConnectionException &ex )
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 }
481 catch ( QgsProviderConnectionException &ex )
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 }
507 catch ( QgsProviderConnectionException &ex )
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
stopFetching()517 void QgsConnectionsApiFetcher::stopFetching()
518 {
519 mStopFetching = 1;
520 }
521
522
QgsQueryResultItemDelegate(QObject * parent)523 QgsQueryResultItemDelegate::QgsQueryResultItemDelegate( QObject *parent )
524 : QStyledItemDelegate( parent )
525 {
526 }
527
displayText(const QVariant & value,const QLocale & locale) const528 QString 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
541 ///@endcond private
542