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