32 mQueryResultsTableView->hide();
33 mQueryResultsTableView->setItemDelegate(
new QgsQueryResultItemDelegate( mQueryResultsTableView ) );
37 connect( mClearButton, &QPushButton::pressed,
this, [ = ]
39 mSqlEditor->setText( QString() );
41 connect( mLoadLayerPushButton, &QPushButton::pressed,
this, [ = ]
45 emit
createSqlVectorLayer( mConnection->providerKey(), mConnection->uri(), sqlVectorLayerOptions() );
49 connect( mSqlEditor, &QgsCodeEditorSQL::textChanged,
this, &QgsQueryResultWidget::updateButtons );
50 connect( mFilterToolButton, &QToolButton::pressed,
this, [ = ]
56 std::unique_ptr<QgsVectorLayer> vlayer { mConnection->createSqlVectorLayer( sqlVectorLayerOptions() ) };
58 if ( builder.exec() == QDialog::Accepted )
60 mFilterLineEdit->setText( builder.sql() );
65 mMessageBar->pushCritical( tr(
"Error opening filter dialog" ), tr(
"There was an error while preparing SQL filter dialog: %1." ).arg( ex.
what() ) );
72 mSqlErrorText->hide();
74 mLoadAsNewLayerGroupBox->setCollapsed(
true );
82 mPkColumnsCheckBox->setVisible( showPkConfig );
83 mPkColumnsComboBox->setVisible( showPkConfig );
86 mGeometryColumnCheckBox->setVisible( showGeometryColumnConfig );
87 mGeometryColumnComboBox->setVisible( showGeometryColumnConfig );
90 mFilterLabel->setVisible( showFilterConfig );
91 mFilterToolButton->setVisible( showFilterConfig );
92 mFilterLineEdit->setVisible( showFilterConfig );
95 mAvoidSelectingAsFeatureIdCheckBox->setVisible( showDisableSelectAtId );
106 cancelRunningQuery();
111 mSqlVectorLayerOptions = options;
112 if ( ! options.
sql.isEmpty() )
118 mPkColumnsComboBox->setCheckedItems( {} );
123 mGeometryColumnCheckBox->setChecked( ! options.
geometryColumn.isEmpty() );
124 mGeometryColumnComboBox->clear();
127 mGeometryColumnComboBox->setCurrentText( options.
geometryColumn );
129 mFilterLineEdit->setText( options.
filter );
130 mLayerNameLineEdit->setText( options.
layerName );
135 mQueryWidgetMode = widgetMode;
136 switch ( widgetMode )
139 mLoadAsNewLayerGroupBox->setTitle( tr(
"Load as New Layer" ) );
140 mLoadLayerPushButton->setText( tr(
"Load Layer" ) );
141 mLoadAsNewLayerGroupBox->setCollapsed(
true );
144 mLoadAsNewLayerGroupBox->setTitle( tr(
"Update Query Layer" ) );
145 mLoadLayerPushButton->setText( tr(
"Update Layer" ) );
146 mLoadAsNewLayerGroupBox->setCollapsed(
false );
153 mQueryResultsTableView->hide();
154 mSqlErrorText->hide();
155 mFirstRowFetched =
false;
157 cancelRunningQuery();
161 const QString sql { mSqlEditor->text( ) };
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();
172 connect( mStopButton, &QPushButton::pressed, mFeedback.get(), [ = ]
174 mStatusLabel->setText( tr(
"Stopped" ) );
176 mProgressBar->hide();
181 connect( &mQueryResultWatcher, &QFutureWatcher<QgsAbstractDatabaseProviderConnection::QueryResult>::finished,
this, &QgsQueryResultWidget::startFetching, Qt::ConnectionType::UniqueConnection );
187 return mConnection->execSql( sql, mFeedback.get() );
191 mSqlErrorMessage = ex.
what();
195 mQueryResultWatcher.setFuture( future );
199 showError( tr(
"Connection error" ), tr(
"Cannot execute query: connection to the database is not available." ) );
203 void QgsQueryResultWidget::updateButtons()
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() &&
215 void QgsQueryResultWidget::updateSqlLayerColumns( )
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 )
230 const bool pkCheckedState = hasPkInformation ? mSqlVectorLayerOptions.
primaryKeyColumns.contains(
c ) :
c.contains( QStringLiteral(
"id" ), Qt::CaseSensitivity::CaseInsensitive );
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 ) )
236 mGeometryColumnComboBox->setCurrentText(
c );
239 mPkColumnsCheckBox->setChecked( hasPkInformation );
240 mGeometryColumnCheckBox->setChecked( hasGeomColInformation );
241 if ( hasGeomColInformation )
243 mGeometryColumnComboBox->setCurrentText( mSqlVectorLayerOptions.
geometryColumn );
247 void QgsQueryResultWidget::cancelRunningQuery()
256 if ( mQueryResultWatcher.isRunning() )
258 mQueryResultWatcher.waitForFinished();
262 void QgsQueryResultWidget::cancelApiFetcher()
266 mApiFetcher->stopFetching();
267 mApiFetcherWorkerThread.quit();
268 mApiFetcherWorkerThread.wait();
272 void QgsQueryResultWidget::startFetching()
274 if ( ! mWasCanceled )
276 if ( ! mSqlErrorMessage.isEmpty() )
278 showError( tr(
"SQL error" ), mSqlErrorMessage,
true );
284 mStatusLabel->setText( QStringLiteral(
"Query executed successfully (%1 rows, %2 ms)" )
285 .arg( QLocale().toString( mQueryResultWatcher.result().rowCount() ),
286 QLocale().toString( mQueryResultWatcher.result().queryExecutionTime() ) ) );
290 mStatusLabel->setText( QStringLiteral(
"Query executed successfully (%1 s)" ).arg( QLocale().toString( mQueryResultWatcher.result().queryExecutionTime() ) ) );
292 mProgressBar->hide();
293 mModel = std::make_unique<QgsQueryResultModel>( mQueryResultWatcher.result() );
302 mFetchedRowsBatchCount = 0;
303 mProgressBar->setRange( 0, maxRows );
304 mProgressBar->show();
307 connect( mModel.get(), &QgsQueryResultModel::rowsInserted,
this, [ = ](
const QModelIndex &,
int first,
int last )
309 if ( ! mFirstRowFetched )
311 emit firstResultBatchFetched();
312 mFirstRowFetched = true;
313 mQueryResultsTableView->show();
315 updateSqlLayerColumns( );
316 mActualRowCount = mModel->queryResult().rowCount();
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 );
327 mQueryResultsTableView->setModel( mModel.get() );
328 mQueryResultsTableView->show();
332 mProgressBar->hide();
333 mStopButton->setEnabled( false );
339 mStatusLabel->setText( tr(
"SQL command aborted" ) );
340 mProgressBar->hide();
346 mStatusLabel->show();
347 mStatusLabel->setText( tr(
"An error occurred while executing the query" ) );
348 mProgressBar->hide();
349 mQueryResultsTableView->hide();
352 mSqlErrorText->show();
353 mSqlErrorText->setText( message );
357 mMessageBar->pushCritical( title, message );
363 mSqlEditor->setExtraKeywords( mSqlEditor->extraKeywords() + tokens );
364 mSqlErrorText->setExtraKeywords( mSqlErrorText->extraKeywords() + tokens );
370 mSqlVectorLayerOptions.
sql = mSqlEditor->text();
371 mSqlVectorLayerOptions.
filter = mFilterLineEdit->text();
373 mSqlVectorLayerOptions.
geometryColumn = mGeometryColumnComboBox->currentText();
374 mSqlVectorLayerOptions.
layerName = mLayerNameLineEdit->text();
375 mSqlVectorLayerOptions.
disableSelectAtId = mAvoidSelectingAsFeatureIdCheckBox->isChecked();
378 if ( ! mPkColumnsCheckBox->isChecked() )
382 if ( ! mGeometryColumnCheckBox->isChecked() )
384 options.geometryColumn.clear();
391 mConnection.reset( connection );
399 const QMultiMap<Qgis::SqlKeywordCategory, QStringList> keywordsDict { connection->
sqlDictionary() };
400 QStringList keywords;
401 for (
auto it = keywordsDict.constBegin(); it != keywordsDict.constEnd(); it++ )
403 keywords.append( it.value() );
407 mSqlEditor->setExtraKeywords( keywords );
408 mSqlErrorText->setExtraKeywords( keywords );
411 mApiFetcher = std::make_unique<QgsConnectionsApiFetcher>( connection );
412 mApiFetcher->moveToThread( &mApiFetcherWorkerThread );
413 connect( &mApiFetcherWorkerThread, &QThread::started, mApiFetcher.get(), &QgsConnectionsApiFetcher::fetchTokens );
415 connect( mApiFetcher.get(), &QgsConnectionsApiFetcher::fetchingFinished, &mApiFetcherWorkerThread, [ = ]
417 mApiFetcherWorkerThread.quit();
418 mApiFetcherWorkerThread.wait();
420 mApiFetcherWorkerThread.start();
429 mSqlEditor->setText( sql );
434 mMessageBar->pushMessage( title, text, level );
440 void QgsConnectionsApiFetcher::fetchTokens()
442 if ( ! mStopFetching && mConnection )
445 if ( mConnection->capabilities().testFlag( QgsAbstractDatabaseProviderConnection::Capability::Schemas ) )
449 schemas = mConnection->schemas();
450 emit tokensReady( schemas );
459 schemas.push_back( QString() );
462 for (
const auto &schema : std::as_const( schemas ) )
470 QStringList tableNames;
473 const QList<QgsAbstractDatabaseProviderConnection::TableProperty> tables = mConnection->tables( schema );
476 if ( mStopFetching ) {
return; }
477 tableNames.push_back( table.tableName() );
479 emit tokensReady( tableNames );
487 for (
const auto &table : std::as_const( tableNames ) )
495 QStringList fieldNames;
498 const QgsFields fields( mConnection->fields( schema, table ) );
499 if ( mStopFetching ) {
return; }
500 for (
const auto &
field : std::as_const( fields ) )
503 if ( mStopFetching ) {
return; }
505 emit tokensReady( fieldNames );
509 QgsMessageLog::logMessage( tr(
"Error retrieving fields for table %1: %2" ).arg( table, ex.
what() ), QStringLiteral(
"QGIS" ), Qgis::MessageLevel::Warning );
514 emit fetchingFinished();
517 void QgsConnectionsApiFetcher::stopFetching()
523 QgsQueryResultItemDelegate::QgsQueryResultItemDelegate( QObject *parent )
524 : QStyledItemDelegate( parent )
528 QString QgsQueryResultItemDelegate::displayText(
const QVariant &value,
const QLocale &locale )
const
531 QString result { QgsExpressionUtils::toLocalizedString( value ) };
533 if ( result.length() > 255 )
535 result.truncate( 255 );
536 result.append( QStringLiteral(
"…" ) );
MessageLevel
Level for messages This will be used both for message log and message bar in application.
@ 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.
void canceled()
Internal routines can connect to this signal if they use event loop.
Container of fields for a vector layer.
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.
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() ...
@ 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
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.
QStringList primaryKeyColumns
List of primary key column names.
QString filter
Additional subset string (provider-side filter), not all data providers support this feature: check s...
QString layerName
Optional name for the new layer.
bool disableSelectAtId
If SelectAtId is disabled (default is false), not all data providers support this feature: check supp...
QString geometryColumn
Name of the geometry column.
The TableProperty class represents a database table or view.