41 mQueryResultsTableView->hide();
42 mQueryResultsTableView->setItemDelegate(
new QgsQueryResultItemDelegate( mQueryResultsTableView ) );
43 mQueryResultsTableView->setContextMenuPolicy( Qt::CustomContextMenu );
44 connect( mQueryResultsTableView, &QTableView::customContextMenuRequested,
this, &QgsQueryResultWidget::showCellContextMenu );
49 connect( mClearButton, &QPushButton::pressed,
this, [ = ]
51 mSqlEditor->setText( QString() );
53 connect( mLoadLayerPushButton, &QPushButton::pressed,
this, [ = ]
57 emit
createSqlVectorLayer( mConnection->providerKey(), mConnection->uri(), sqlVectorLayerOptions() );
61 connect( mSqlEditor, &QgsCodeEditorSQL::textChanged,
this, &QgsQueryResultWidget::updateButtons );
62 connect( mFilterToolButton, &QToolButton::pressed,
this, [ = ]
68 std::unique_ptr<QgsVectorLayer> vlayer { mConnection->createSqlVectorLayer( sqlVectorLayerOptions() ) };
70 if ( builder.exec() == QDialog::Accepted )
72 mFilterLineEdit->setText( builder.sql() );
77 mMessageBar->pushCritical( tr(
"Error opening filter dialog" ), tr(
"There was an error while preparing SQL filter dialog: %1." ).arg( ex.
what() ) );
84 mSqlErrorText->hide();
86 mLoadAsNewLayerGroupBox->setCollapsed(
true );
94 mPkColumnsCheckBox->setVisible( showPkConfig );
95 mPkColumnsComboBox->setVisible( showPkConfig );
98 mGeometryColumnCheckBox->setVisible( showGeometryColumnConfig );
99 mGeometryColumnComboBox->setVisible( showGeometryColumnConfig );
102 mFilterLabel->setVisible( showFilterConfig );
103 mFilterToolButton->setVisible( showFilterConfig );
104 mFilterLineEdit->setVisible( showFilterConfig );
107 mAvoidSelectingAsFeatureIdCheckBox->setVisible( showDisableSelectAtId );
112 QShortcut *copySelection =
new QShortcut( QKeySequence::Copy, mQueryResultsTableView );
113 connect( copySelection, &QShortcut::activated,
this, &QgsQueryResultWidget::copySelection );
121 cancelRunningQuery();
126 mSqlVectorLayerOptions = options;
127 if ( ! options.
sql.isEmpty() )
133 mPkColumnsComboBox->setCheckedItems( {} );
138 mGeometryColumnCheckBox->setChecked( ! options.
geometryColumn.isEmpty() );
139 mGeometryColumnComboBox->clear();
142 mGeometryColumnComboBox->setCurrentText( options.
geometryColumn );
144 mFilterLineEdit->setText( options.
filter );
145 mLayerNameLineEdit->setText( options.
layerName );
150 mQueryWidgetMode = widgetMode;
151 switch ( widgetMode )
154 mLoadAsNewLayerGroupBox->setTitle( tr(
"Load as New Layer" ) );
155 mLoadLayerPushButton->setText( tr(
"Load Layer" ) );
156 mLoadAsNewLayerGroupBox->setCollapsed(
true );
159 mLoadAsNewLayerGroupBox->setTitle( tr(
"Update Query Layer" ) );
160 mLoadLayerPushButton->setText( tr(
"Update Layer" ) );
161 mLoadAsNewLayerGroupBox->setCollapsed(
false );
168 mQueryResultsTableView->hide();
169 mSqlErrorText->hide();
170 mFirstRowFetched =
false;
172 cancelRunningQuery();
175 const QString sql { mSqlEditor->text( ) };
181 { QStringLiteral(
"query" ), sql },
182 { QStringLiteral(
"provider" ), mConnection->providerKey() },
183 { QStringLiteral(
"connection" ), mConnection->uri() },
187 mWasCanceled =
false;
188 mFeedback = std::make_unique<QgsFeedback>();
189 mStopButton->setEnabled(
true );
190 mStatusLabel->show();
191 mStatusLabel->setText( tr(
"Executing query…" ) );
192 mProgressBar->show();
193 mProgressBar->setRange( 0, 0 );
194 mSqlErrorMessage.clear();
196 connect( mStopButton, &QPushButton::pressed, mFeedback.get(), [ = ]
198 mStatusLabel->setText( tr(
"Stopped" ) );
200 mProgressBar->hide();
205 connect( &mQueryResultWatcher, &QFutureWatcher<QgsAbstractDatabaseProviderConnection::QueryResult>::finished,
this, &QgsQueryResultWidget::startFetching, Qt::ConnectionType::UniqueConnection );
211 return mConnection->execSql( sql, mFeedback.get() );
215 mSqlErrorMessage = ex.
what();
219 mQueryResultWatcher.setFuture( future );
223 showError( tr(
"Connection error" ), tr(
"Cannot execute query: connection to the database is not available." ) );
227void QgsQueryResultWidget::updateButtons()
229 mFilterLineEdit->setEnabled( mFirstRowFetched );
230 mFilterToolButton->setEnabled( mFirstRowFetched );
231 mExecuteButton->setEnabled( ! mSqlEditor->text().isEmpty() );
232 mLoadAsNewLayerGroupBox->setVisible( mConnection && mConnection->capabilities().testFlag( QgsAbstractDatabaseProviderConnection::Capability::SqlLayers ) );
233 mLoadAsNewLayerGroupBox->setEnabled(
234 mSqlErrorMessage.isEmpty() &&
239void QgsQueryResultWidget::showCellContextMenu( QPoint point )
241 const QModelIndex modelIndex = mQueryResultsTableView->indexAt( point );
242 if ( modelIndex.isValid() )
244 QMenu *menu =
new QMenu();
245 menu->setAttribute( Qt::WA_DeleteOnClose );
250 }, QKeySequence::Copy );
252 menu->exec( mQueryResultsTableView->viewport()->mapToGlobal( point ) );
256void QgsQueryResultWidget::copySelection()
258 const QModelIndexList selection = mQueryResultsTableView->selectionModel()->selectedIndexes();
259 if ( selection.empty() )
266 for (
const QModelIndex &index : selection )
268 if ( minRow == -1 || index.row() < minRow )
269 minRow = index.row();
270 if ( maxRow == -1 || index.row() > maxRow )
271 maxRow = index.row();
272 if ( minCol == -1 || index.column() < minCol )
273 minCol = index.column();
274 if ( maxCol == -1 || index.column() > maxCol )
275 maxCol = index.column();
278 if ( minRow == maxRow && minCol == maxCol )
281 const QString text = mModel->data( selection.at( 0 ), Qt::DisplayRole ).toString();
282 QApplication::clipboard()->setText( text );
290void QgsQueryResultWidget::updateSqlLayerColumns( )
295 mFilterToolButton->setEnabled(
true );
296 mFilterLineEdit->setEnabled(
true );
297 mPkColumnsComboBox->clear();
298 mGeometryColumnComboBox->clear();
299 const bool hasPkInformation { ! mSqlVectorLayerOptions.
primaryKeyColumns.isEmpty() };
300 const bool hasGeomColInformation { ! mSqlVectorLayerOptions.
geometryColumn.isEmpty() };
301 static const QStringList geomColCandidates { QStringLiteral(
"geom" ), QStringLiteral(
"geometry" ), QStringLiteral(
"the_geom" ) };
302 const QStringList constCols { mModel->columns() };
303 for (
const QString &
c : constCols )
305 const bool pkCheckedState = hasPkInformation ? mSqlVectorLayerOptions.
primaryKeyColumns.contains(
c ) :
c.contains( QStringLiteral(
"id" ), Qt::CaseSensitivity::CaseInsensitive );
307 mPkColumnsComboBox->addItemWithCheckState(
c, pkCheckedState && mPkColumnsComboBox->checkedItems().isEmpty() ? Qt::CheckState::Checked : Qt::CheckState::Unchecked );
308 mGeometryColumnComboBox->addItem(
c );
309 if ( ! hasGeomColInformation && geomColCandidates.contains(
c, Qt::CaseSensitivity::CaseInsensitive ) )
311 mGeometryColumnComboBox->setCurrentText(
c );
314 mPkColumnsCheckBox->setChecked( hasPkInformation );
315 mGeometryColumnCheckBox->setChecked( hasGeomColInformation );
316 if ( hasGeomColInformation )
318 mGeometryColumnComboBox->setCurrentText( mSqlVectorLayerOptions.
geometryColumn );
322void QgsQueryResultWidget::cancelRunningQuery()
331 if ( mQueryResultWatcher.isRunning() )
333 mQueryResultWatcher.waitForFinished();
337void QgsQueryResultWidget::cancelApiFetcher()
341 mApiFetcher->stopFetching();
346void QgsQueryResultWidget::startFetching()
348 if ( ! mWasCanceled )
350 if ( ! mSqlErrorMessage.isEmpty() )
352 showError( tr(
"SQL error" ), mSqlErrorMessage,
true );
358 mStatusLabel->setText( QStringLiteral(
"Query executed successfully (%1 rows, %2 ms)" )
359 .arg( QLocale().toString( mQueryResultWatcher.result().rowCount() ),
360 QLocale().toString( mQueryResultWatcher.result().queryExecutionTime() ) ) );
364 mStatusLabel->setText( QStringLiteral(
"Query executed successfully (%1 s)" ).arg( QLocale().toString( mQueryResultWatcher.result().queryExecutionTime() ) ) );
366 mProgressBar->hide();
367 mModel = std::make_unique<QgsQueryResultModel>( mQueryResultWatcher.result() );
376 mFetchedRowsBatchCount = 0;
377 mProgressBar->setRange( 0, maxRows );
378 mProgressBar->show();
381 connect( mModel.get(), &QgsQueryResultModel::rowsInserted,
this, [ = ](
const QModelIndex &,
int first,
int last )
383 if ( ! mFirstRowFetched )
385 emit firstResultBatchFetched();
386 mFirstRowFetched = true;
387 mQueryResultsTableView->show();
389 updateSqlLayerColumns( );
390 mActualRowCount = mModel->queryResult().rowCount();
392 mStatusLabel->setText( tr(
"Fetched rows: %1/%2 %3 %4 ms" )
393 .arg( QLocale().toString( mModel->rowCount( mModel->index( -1, -1 ) ) ),
394 mActualRowCount != -1 ? QLocale().toString( mActualRowCount ) : tr(
"unknown" ),
395 mWasCanceled ? tr(
"(stopped)" ) : QString(),
396 QLocale().toString( mQueryResultWatcher.result().queryExecutionTime() ) ) );
397 mFetchedRowsBatchCount += last - first + 1;
398 mProgressBar->setValue( mFetchedRowsBatchCount );
401 mQueryResultsTableView->setModel( mModel.get() );
402 mQueryResultsTableView->show();
407 const QgsHistoryEntry currentHistoryEntry = QgsGui::historyProviderRegistry()->entry( mCurrentHistoryEntryId, ok );
408 QVariantMap entryDetails = currentHistoryEntry.entry;
409 entryDetails.insert( QStringLiteral(
"rows" ), mActualRowCount );
410 entryDetails.insert( QStringLiteral(
"time" ), mQueryResultWatcher.result().queryExecutionTime() );
412 QgsGui::historyProviderRegistry()->updateEntry( mCurrentHistoryEntryId,
414 mProgressBar->hide();
415 mStopButton->setEnabled( false );
421 mStatusLabel->setText( tr(
"SQL command aborted" ) );
422 mProgressBar->hide();
428 mStatusLabel->show();
429 mStatusLabel->setText( tr(
"An error occurred while executing the query" ) );
430 mProgressBar->hide();
431 mQueryResultsTableView->hide();
434 mSqlErrorText->show();
435 mSqlErrorText->setText( message );
439 mMessageBar->pushCritical( title, message );
445 mSqlEditor->setExtraKeywords( mSqlEditor->extraKeywords() + tokens );
446 mSqlErrorText->setExtraKeywords( mSqlErrorText->extraKeywords() + tokens );
451 const int rowCount = mModel->rowCount( QModelIndex() );
452 const int columnCount = mModel->columnCount( QModelIndex() );
453 copyResults( 0, rowCount - 1, 0, columnCount - 1 );
458 QStringList rowStrings;
459 QStringList columnStrings;
461 const int rowCount = mModel->rowCount( QModelIndex() );
462 const int columnCount = mModel->columnCount( QModelIndex() );
464 toRow = std::min( toRow, rowCount - 1 );
465 toColumn = std::min( toColumn, columnCount - 1 );
467 rowStrings.reserve( toRow - fromRow );
470 for (
int col = fromColumn; col <= toColumn; col++ )
472 columnStrings += mModel->headerData( col, Qt::Horizontal, Qt::DisplayRole ).toString();
474 rowStrings += columnStrings.join( QLatin1Char(
'\t' ) );
475 columnStrings.clear();
477 for (
int row = fromRow; row <= toRow; row++ )
479 for (
int col = fromColumn; col <= toColumn; col++ )
481 columnStrings += mModel->data( mModel->index( row, col ), Qt::DisplayRole ).toString();
483 rowStrings += columnStrings.join( QLatin1Char(
'\t' ) );
484 columnStrings.clear();
487 if ( !rowStrings.isEmpty() )
489 const QString text = rowStrings.join( QLatin1Char(
'\n' ) );
490 QString html = QStringLiteral(
"<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 Transitional//EN\"><html><head><meta http-equiv=\"content-type\" content=\"text/html; charset=utf-8\"/></head><body><table border=\"1\"><tr><td>%1</td></tr></table></body></html>" ).arg( text );
491 html.replace( QLatin1String(
"\t" ), QLatin1String(
"</td><td>" ) ).replace( QLatin1String(
"\n" ), QLatin1String(
"</td></tr><tr><td>" ) );
493 QMimeData *mdata =
new QMimeData();
494 mdata->setData( QStringLiteral(
"text/html" ), html.toUtf8() );
495 if ( !text.isEmpty() )
497 mdata->setText( text );
501 QApplication::clipboard()->setMimeData( mdata, QClipboard::Selection );
503 QApplication::clipboard()->setMimeData( mdata, QClipboard::Clipboard );
509 mSqlVectorLayerOptions.
sql = mSqlEditor->text();
510 mSqlVectorLayerOptions.
filter = mFilterLineEdit->text();
512 mSqlVectorLayerOptions.
geometryColumn = mGeometryColumnComboBox->currentText();
513 mSqlVectorLayerOptions.
layerName = mLayerNameLineEdit->text();
514 mSqlVectorLayerOptions.
disableSelectAtId = mAvoidSelectingAsFeatureIdCheckBox->isChecked();
517 if ( ! mPkColumnsCheckBox->isChecked() )
521 if ( ! mGeometryColumnCheckBox->isChecked() )
523 options.geometryColumn.clear();
530 mConnection.reset( connection );
538 const QMultiMap<Qgis::SqlKeywordCategory, QStringList> keywordsDict { connection->
sqlDictionary() };
539 QStringList keywords;
540 for (
auto it = keywordsDict.constBegin(); it != keywordsDict.constEnd(); it++ )
542 keywords.append( it.value() );
546 mSqlEditor->setExtraKeywords( keywords );
547 mSqlErrorText->setExtraKeywords( keywords );
550 QThread *apiFetcherWorkerThread =
new QThread();
551 QgsConnectionsApiFetcher *apiFetcher =
new QgsConnectionsApiFetcher( mConnection->uri(), mConnection->providerKey() );
552 apiFetcher->moveToThread( apiFetcherWorkerThread );
553 connect( apiFetcherWorkerThread, &QThread::started, apiFetcher, &QgsConnectionsApiFetcher::fetchTokens );
555 connect( apiFetcher, &QgsConnectionsApiFetcher::fetchingFinished, apiFetcherWorkerThread, [apiFetcher, apiFetcherWorkerThread]
557 apiFetcherWorkerThread->quit();
558 apiFetcherWorkerThread->wait();
559 apiFetcherWorkerThread->deleteLater();
560 apiFetcher->deleteLater();
563 mApiFetcher = apiFetcher;
564 apiFetcherWorkerThread->start();
573 mSqlEditor->setText( sql );
578 mMessageBar->pushMessage( title, text, level );
584void QgsConnectionsApiFetcher::fetchTokens()
588 emit fetchingFinished();
596 emit fetchingFinished();
600 if ( ! mStopFetching && connection )
602 mFeedback = std::make_unique< QgsFeedback >();
604 if ( connection->capabilities().testFlag( QgsAbstractDatabaseProviderConnection::Capability::Schemas ) )
608 schemas = connection->schemas();
609 emit tokensReady( schemas );
618 schemas.push_back( QString() );
621 for (
const auto &schema : std::as_const( schemas ) )
627 emit fetchingFinished();
631 QStringList tableNames;
640 emit fetchingFinished();
643 tableNames.push_back( table.tableName() );
645 emit tokensReady( tableNames );
653 for (
const auto &table : std::as_const( tableNames ) )
659 emit fetchingFinished();
663 QStringList fieldNames;
666 const QgsFields fields( connection->fields( schema, table, mFeedback.get() ) );
670 emit fetchingFinished();
674 for (
const auto &field : std::as_const( fields ) )
676 fieldNames.push_back( field.name() );
680 emit fetchingFinished();
684 emit tokensReady( fieldNames );
688 QgsMessageLog::logMessage( tr(
"Error retrieving fields for table %1: %2" ).arg( table, ex.
what() ), QStringLiteral(
"QGIS" ), Qgis::MessageLevel::Warning );
695 emit fetchingFinished();
698void QgsConnectionsApiFetcher::stopFetching()
705QgsQueryResultItemDelegate::QgsQueryResultItemDelegate( QObject *parent )
706 : QStyledItemDelegate( parent )
710QString QgsQueryResultItemDelegate::displayText(
const QVariant &value,
const QLocale &locale )
const
713 QString result { QgsExpressionUtils::toLocalizedString( value ) };
715 if ( result.length() > 255 )
717 result.truncate( 255 );
718 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...
QFlags< TableFlag > TableFlags
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.
static QIcon getThemeIcon(const QString &name, const QColor &fillColor=QColor(), const QColor &strokeColor=QColor())
Helper to get a theme icon.
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 QgsHistoryProviderRegistry * historyProviderRegistry()
Returns the global history provider registry, used for tracking history providers.
long long addEntry(const QString &providerId, const QVariantMap &entry, bool &ok, QgsHistoryProviderRegistry::HistoryEntryOptions options=QgsHistoryProviderRegistry::HistoryEntryOptions())
Adds an entry to the history logs.
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.
static QgsProviderRegistry * instance(const QString &pluginPath=QString())
Means of accessing canonical single instance.
QgsProviderMetadata * providerMetadata(const QString &providerKey) const
Returns metadata of the provider or nullptr if not found.
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.