42 mQueryResultsTableView->hide();
43 mQueryResultsTableView->setItemDelegate(
new QgsQueryResultItemDelegate( mQueryResultsTableView ) );
44 mQueryResultsTableView->setContextMenuPolicy( Qt::CustomContextMenu );
45 connect( mQueryResultsTableView, &QTableView::customContextMenuRequested,
this, &QgsQueryResultWidget::showCellContextMenu );
51 QVBoxLayout *vl =
new QVBoxLayout();
52 vl->setContentsMargins( 0, 0, 0, 0 );
53 vl->addWidget( mCodeEditorWidget );
54 mSqlEditorContainer->setLayout( vl );
56 connect( mExecuteButton, &QPushButton::pressed,
this, &QgsQueryResultWidget::executeQuery );
57 connect( mClearButton, &QPushButton::pressed,
this, [ = ]
59 mSqlEditor->setText( QString() );
61 connect( mLoadLayerPushButton, &QPushButton::pressed,
this, [ = ]
65 emit createSqlVectorLayer( mConnection->providerKey(), mConnection->uri(), sqlVectorLayerOptions() );
69 connect( mSqlEditor, &QgsCodeEditorSQL::textChanged,
this, &QgsQueryResultWidget::updateButtons );
70 connect( mSqlEditor, &QgsCodeEditorSQL::selectionChanged,
this, [ = ]
72 mExecuteButton->setText( mSqlEditor->selectedText().isEmpty() ? tr(
"Execute" ) : tr(
"Execute Selection" ) );
74 connect( mFilterToolButton, &QToolButton::pressed,
this, [ = ]
80 std::unique_ptr<QgsVectorLayer> vlayer { mConnection->createSqlVectorLayer( sqlVectorLayerOptions() ) };
82 if ( builder.exec() == QDialog::Accepted )
84 mFilterLineEdit->setText( builder.sql() );
89 mMessageBar->pushCritical( tr(
"Error opening filter dialog" ), tr(
"There was an error while preparing SQL filter dialog: %1." ).arg( ex.
what() ) );
96 mSqlErrorText->hide();
98 mLoadAsNewLayerGroupBox->setCollapsed(
true );
106 mPkColumnsCheckBox->setVisible( showPkConfig );
107 mPkColumnsComboBox->setVisible( showPkConfig );
110 mGeometryColumnCheckBox->setVisible( showGeometryColumnConfig );
111 mGeometryColumnComboBox->setVisible( showGeometryColumnConfig );
114 mFilterLabel->setVisible( showFilterConfig );
115 mFilterToolButton->setVisible( showFilterConfig );
116 mFilterLineEdit->setVisible( showFilterConfig );
119 mAvoidSelectingAsFeatureIdCheckBox->setVisible( showDisableSelectAtId );
124 QShortcut *copySelection =
new QShortcut( QKeySequence::Copy, mQueryResultsTableView );
125 connect( copySelection, &QShortcut::activated,
this, &QgsQueryResultWidget::copySelection );
127 setConnection( connection );
130QgsQueryResultWidget::~QgsQueryResultWidget()
133 cancelRunningQuery();
138 mSqlVectorLayerOptions = options;
139 if ( ! options.
sql.isEmpty() )
141 setQuery( options.
sql );
145 mPkColumnsComboBox->setCheckedItems( {} );
150 mGeometryColumnCheckBox->setChecked( ! options.
geometryColumn.isEmpty() );
151 mGeometryColumnComboBox->clear();
154 mGeometryColumnComboBox->setCurrentText( options.
geometryColumn );
156 mFilterLineEdit->setText( options.
filter );
157 mLayerNameLineEdit->setText( options.
layerName );
160void QgsQueryResultWidget::setWidgetMode( QueryWidgetMode widgetMode )
162 mQueryWidgetMode = widgetMode;
163 switch ( widgetMode )
165 case QueryWidgetMode::SqlQueryMode:
166 mLoadAsNewLayerGroupBox->setTitle( tr(
"Load as New Layer" ) );
167 mLoadLayerPushButton->setText( tr(
"Load Layer" ) );
168 mLoadAsNewLayerGroupBox->setCollapsed(
true );
170 case QueryWidgetMode::QueryLayerUpdateMode:
171 mLoadAsNewLayerGroupBox->setTitle( tr(
"Update Query Layer" ) );
172 mLoadLayerPushButton->setText( tr(
"Update Layer" ) );
173 mLoadAsNewLayerGroupBox->setCollapsed(
false );
178void QgsQueryResultWidget::executeQuery()
180 mQueryResultsTableView->hide();
181 mSqlErrorText->hide();
182 mFirstRowFetched =
false;
184 cancelRunningQuery();
187 const QString sql { mSqlEditor->selectedText().isEmpty() ? mSqlEditor->text() : mSqlEditor->selectedText() };
193 { QStringLiteral(
"query" ), sql },
194 { QStringLiteral(
"provider" ), mConnection->providerKey() },
195 { QStringLiteral(
"connection" ), mConnection->uri() },
199 mWasCanceled =
false;
200 mFeedback = std::make_unique<QgsFeedback>();
201 mStopButton->setEnabled(
true );
202 mStatusLabel->show();
203 mStatusLabel->setText( tr(
"Executing query…" ) );
204 mProgressBar->show();
205 mProgressBar->setRange( 0, 0 );
206 mSqlErrorMessage.clear();
208 connect( mStopButton, &QPushButton::pressed, mFeedback.get(), [ = ]
210 mStatusLabel->setText( tr(
"Stopped" ) );
212 mProgressBar->hide();
217 connect( &mQueryResultWatcher, &QFutureWatcher<QgsAbstractDatabaseProviderConnection::QueryResult>::finished,
this, &QgsQueryResultWidget::startFetching, Qt::ConnectionType::UniqueConnection );
223 return mConnection->execSql( sql, mFeedback.get() );
227 mSqlErrorMessage = ex.
what();
231 mQueryResultWatcher.setFuture( future );
235 showError( tr(
"Connection error" ), tr(
"Cannot execute query: connection to the database is not available." ) );
239void QgsQueryResultWidget::updateButtons()
241 mFilterLineEdit->setEnabled( mFirstRowFetched );
242 mFilterToolButton->setEnabled( mFirstRowFetched );
243 const bool isEmpty = mSqlEditor->text().isEmpty();
244 mExecuteButton->setEnabled( !isEmpty );
245 mClearButton->setEnabled( !isEmpty );
247 mLoadAsNewLayerGroupBox->setEnabled(
248 mSqlErrorMessage.isEmpty() &&
253void QgsQueryResultWidget::showCellContextMenu( QPoint point )
255 const QModelIndex modelIndex = mQueryResultsTableView->indexAt( point );
256 if ( modelIndex.isValid() )
258 QMenu *menu =
new QMenu();
259 menu->setAttribute( Qt::WA_DeleteOnClose );
264 }, QKeySequence::Copy );
266 menu->exec( mQueryResultsTableView->viewport()->mapToGlobal( point ) );
270void QgsQueryResultWidget::copySelection()
272 const QModelIndexList selection = mQueryResultsTableView->selectionModel()->selectedIndexes();
273 if ( selection.empty() )
280 for (
const QModelIndex &index : selection )
282 if ( minRow == -1 || index.row() < minRow )
283 minRow = index.row();
284 if ( maxRow == -1 || index.row() > maxRow )
285 maxRow = index.row();
286 if ( minCol == -1 || index.column() < minCol )
287 minCol = index.column();
288 if ( maxCol == -1 || index.column() > maxCol )
289 maxCol = index.column();
292 if ( minRow == maxRow && minCol == maxCol )
295 const QString text = mModel->data( selection.at( 0 ), Qt::DisplayRole ).toString();
296 QApplication::clipboard()->setText( text );
300 copyResults( minRow, maxRow, minCol, maxCol );
304void QgsQueryResultWidget::updateSqlLayerColumns( )
309 mFilterToolButton->setEnabled(
true );
310 mFilterLineEdit->setEnabled(
true );
311 mPkColumnsComboBox->clear();
312 mGeometryColumnComboBox->clear();
313 const bool hasPkInformation { ! mSqlVectorLayerOptions.primaryKeyColumns.isEmpty() };
314 const bool hasGeomColInformation { ! mSqlVectorLayerOptions.geometryColumn.isEmpty() };
315 static const QStringList geomColCandidates { QStringLiteral(
"geom" ), QStringLiteral(
"geometry" ), QStringLiteral(
"the_geom" ) };
316 const QStringList constCols { mModel->columns() };
317 for (
const QString &
c : constCols )
319 const bool pkCheckedState = hasPkInformation ? mSqlVectorLayerOptions.primaryKeyColumns.contains(
c ) :
c.contains( QStringLiteral(
"id" ), Qt::CaseSensitivity::CaseInsensitive );
321 mPkColumnsComboBox->addItemWithCheckState(
c, pkCheckedState && mPkColumnsComboBox->checkedItems().isEmpty() ? Qt::CheckState::Checked : Qt::CheckState::Unchecked );
322 mGeometryColumnComboBox->addItem(
c );
323 if ( ! hasGeomColInformation && geomColCandidates.contains(
c, Qt::CaseSensitivity::CaseInsensitive ) )
325 mGeometryColumnComboBox->setCurrentText(
c );
328 mPkColumnsCheckBox->setChecked( hasPkInformation );
329 mGeometryColumnCheckBox->setChecked( hasGeomColInformation );
330 if ( hasGeomColInformation )
332 mGeometryColumnComboBox->setCurrentText( mSqlVectorLayerOptions.geometryColumn );
336void QgsQueryResultWidget::cancelRunningQuery()
345 if ( mQueryResultWatcher.isRunning() )
347 mQueryResultWatcher.waitForFinished();
351void QgsQueryResultWidget::cancelApiFetcher()
355 mApiFetcher->stopFetching();
360void QgsQueryResultWidget::startFetching()
362 if ( ! mWasCanceled )
364 if ( ! mSqlErrorMessage.isEmpty() )
366 showError( tr(
"SQL error" ), mSqlErrorMessage,
true );
372 mStatusLabel->setText( QStringLiteral(
"Query executed successfully (%1 rows, %2 ms)" )
373 .arg( QLocale().toString( mQueryResultWatcher.result().rowCount() ),
374 QLocale().toString( mQueryResultWatcher.result().queryExecutionTime() ) ) );
378 mStatusLabel->setText( QStringLiteral(
"Query executed successfully (%1 s)" ).arg( QLocale().toString( mQueryResultWatcher.result().queryExecutionTime() ) ) );
380 mProgressBar->hide();
381 mModel = std::make_unique<QgsQueryResultModel>( mQueryResultWatcher.result() );
388 connect( mModel.get(), &QgsQueryResultModel::fetchMoreRows,
this, [ = ](
long long maxRows )
390 mFetchedRowsBatchCount = 0;
391 mProgressBar->setRange( 0, maxRows );
392 mProgressBar->show();
395 connect( mModel.get(), &QgsQueryResultModel::rowsInserted,
this, [ = ](
const QModelIndex &,
int first,
int last )
397 if ( ! mFirstRowFetched )
399 emit firstResultBatchFetched();
400 mFirstRowFetched = true;
401 mQueryResultsTableView->show();
403 updateSqlLayerColumns( );
404 mActualRowCount = mModel->queryResult().rowCount();
406 mStatusLabel->setText( tr(
"Fetched rows: %1/%2 %3 %4 ms" )
407 .arg( QLocale().toString( mModel->rowCount( mModel->index( -1, -1 ) ) ),
408 mActualRowCount != -1 ? QLocale().toString( mActualRowCount ) : tr(
"unknown" ),
409 mWasCanceled ? tr(
"(stopped)" ) : QString(),
410 QLocale().toString( mQueryResultWatcher.result().queryExecutionTime() ) ) );
411 mFetchedRowsBatchCount += last - first + 1;
412 mProgressBar->setValue( mFetchedRowsBatchCount );
415 mQueryResultsTableView->setModel( mModel.get() );
416 mQueryResultsTableView->show();
418 connect( mModel.get(), &QgsQueryResultModel::fetchingComplete, mStopButton, [ = ]
421 const QgsHistoryEntry currentHistoryEntry = QgsGui::historyProviderRegistry()->entry( mCurrentHistoryEntryId, ok );
422 QVariantMap entryDetails = currentHistoryEntry.entry;
423 entryDetails.insert( QStringLiteral(
"rows" ), mActualRowCount );
424 entryDetails.insert( QStringLiteral(
"time" ), mQueryResultWatcher.result().queryExecutionTime() );
426 QgsGui::historyProviderRegistry()->updateEntry( mCurrentHistoryEntryId,
428 mProgressBar->hide();
429 mStopButton->setEnabled( false );
435 mStatusLabel->setText( tr(
"SQL command aborted" ) );
436 mProgressBar->hide();
440void QgsQueryResultWidget::showError(
const QString &title,
const QString &message,
bool isSqlError )
442 mStatusLabel->show();
443 mStatusLabel->setText( tr(
"An error occurred while executing the query" ) );
444 mProgressBar->hide();
445 mQueryResultsTableView->hide();
448 mSqlErrorText->show();
449 mSqlErrorText->setText( message );
453 mMessageBar->pushCritical( title, message );
457void QgsQueryResultWidget::tokensReady(
const QStringList &tokens )
459 mSqlEditor->setExtraKeywords( mSqlEditor->extraKeywords() + tokens );
460 mSqlErrorText->setExtraKeywords( mSqlErrorText->extraKeywords() + tokens );
463void QgsQueryResultWidget::copyResults()
465 const int rowCount = mModel->rowCount( QModelIndex() );
466 const int columnCount = mModel->columnCount( QModelIndex() );
467 copyResults( 0, rowCount - 1, 0, columnCount - 1 );
470void QgsQueryResultWidget::copyResults(
int fromRow,
int toRow,
int fromColumn,
int toColumn )
472 QStringList rowStrings;
473 QStringList columnStrings;
475 const int rowCount = mModel->rowCount( QModelIndex() );
476 const int columnCount = mModel->columnCount( QModelIndex() );
478 toRow = std::min( toRow, rowCount - 1 );
479 toColumn = std::min( toColumn, columnCount - 1 );
481 rowStrings.reserve( toRow - fromRow );
484 for (
int col = fromColumn; col <= toColumn; col++ )
486 columnStrings += mModel->headerData( col, Qt::Horizontal, Qt::DisplayRole ).toString();
488 rowStrings += columnStrings.join( QLatin1Char(
'\t' ) );
489 columnStrings.clear();
491 for (
int row = fromRow; row <= toRow; row++ )
493 for (
int col = fromColumn; col <= toColumn; col++ )
495 columnStrings += mModel->data( mModel->index( row, col ), Qt::DisplayRole ).toString();
497 rowStrings += columnStrings.join( QLatin1Char(
'\t' ) );
498 columnStrings.clear();
501 if ( !rowStrings.isEmpty() )
503 const QString text = rowStrings.join( QLatin1Char(
'\n' ) );
504 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 );
505 html.replace( QLatin1String(
"\t" ), QLatin1String(
"</td><td>" ) ).replace( QLatin1String(
"\n" ), QLatin1String(
"</td></tr><tr><td>" ) );
507 QMimeData *mdata =
new QMimeData();
508 mdata->setData( QStringLiteral(
"text/html" ), html.toUtf8() );
509 if ( !text.isEmpty() )
511 mdata->setText( text );
515 QApplication::clipboard()->setMimeData( mdata, QClipboard::Selection );
517 QApplication::clipboard()->setMimeData( mdata, QClipboard::Clipboard );
523 mSqlVectorLayerOptions.
sql = mSqlEditor->text();
524 mSqlVectorLayerOptions.filter = mFilterLineEdit->text();
525 mSqlVectorLayerOptions.primaryKeyColumns = mPkColumnsComboBox->checkedItems();
526 mSqlVectorLayerOptions.geometryColumn = mGeometryColumnComboBox->currentText();
527 mSqlVectorLayerOptions.layerName = mLayerNameLineEdit->text();
528 mSqlVectorLayerOptions.disableSelectAtId = mAvoidSelectingAsFeatureIdCheckBox->isChecked();
531 if ( ! mPkColumnsCheckBox->isChecked() )
535 if ( ! mGeometryColumnCheckBox->isChecked() )
544 mConnection.reset( connection );
552 const QMultiMap<Qgis::SqlKeywordCategory, QStringList> keywordsDict { connection->
sqlDictionary() };
553 QStringList keywords;
554 for (
auto it = keywordsDict.constBegin(); it != keywordsDict.constEnd(); it++ )
556 keywords.append( it.value() );
560 mSqlEditor->setExtraKeywords( keywords );
561 mSqlErrorText->setExtraKeywords( keywords );
564 QThread *apiFetcherWorkerThread =
new QThread();
565 QgsConnectionsApiFetcher *apiFetcher =
new QgsConnectionsApiFetcher( mConnection->uri(), mConnection->providerKey() );
566 apiFetcher->moveToThread( apiFetcherWorkerThread );
567 connect( apiFetcherWorkerThread, &QThread::started, apiFetcher, &QgsConnectionsApiFetcher::fetchTokens );
568 connect( apiFetcher, &QgsConnectionsApiFetcher::tokensReady,
this, &QgsQueryResultWidget::tokensReady );
569 connect( apiFetcher, &QgsConnectionsApiFetcher::fetchingFinished, apiFetcherWorkerThread, [apiFetcher, apiFetcherWorkerThread]
571 apiFetcherWorkerThread->quit();
572 apiFetcherWorkerThread->wait();
573 apiFetcherWorkerThread->deleteLater();
574 apiFetcher->deleteLater();
577 mApiFetcher = apiFetcher;
578 apiFetcherWorkerThread->start();
585void QgsQueryResultWidget::setQuery(
const QString &sql )
587 mSqlEditor->setText( sql );
590void QgsQueryResultWidget::notify(
const QString &title,
const QString &text,
Qgis::MessageLevel level )
592 mMessageBar->pushMessage( title, text, level );
598void QgsConnectionsApiFetcher::fetchTokens()
602 emit fetchingFinished();
610 emit fetchingFinished();
614 if ( ! mStopFetching && connection )
616 mFeedback = std::make_unique< QgsFeedback >();
622 schemas = connection->
schemas();
623 emit tokensReady( schemas );
632 schemas.push_back( QString() );
635 for (
const auto &schema : std::as_const( schemas ) )
641 emit fetchingFinished();
645 QStringList tableNames;
654 emit fetchingFinished();
657 tableNames.push_back( table.tableName() );
659 emit tokensReady( tableNames );
667 for (
const auto &table : std::as_const( tableNames ) )
673 emit fetchingFinished();
677 QStringList fieldNames;
680 const QgsFields fields( connection->
fields( schema, table, mFeedback.get() ) );
684 emit fetchingFinished();
688 for (
const auto &field : std::as_const( fields ) )
690 fieldNames.push_back( field.name() );
694 emit fetchingFinished();
698 emit tokensReady( fieldNames );
709 emit fetchingFinished();
712void QgsConnectionsApiFetcher::stopFetching()
719QgsQueryResultItemDelegate::QgsQueryResultItemDelegate( QObject *parent )
720 : QStyledItemDelegate( parent )
724QString QgsQueryResultItemDelegate::displayText(
const QVariant &value,
const QLocale &locale )
const
727 QString result { QgsExpressionUtils::toLocalizedString( value ) };
729 if ( result.length() > 255 )
731 result.truncate( 255 );
732 result.append( QStringLiteral(
"…" ) );
MessageLevel
Level for messages This will be used both for message log and message bar in application.
@ Warning
Warning message.
@ 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 QList< QgsAbstractDatabaseProviderConnection::TableProperty > tables(const QString &schema=QString(), const QgsAbstractDatabaseProviderConnection::TableFlags &flags=QgsAbstractDatabaseProviderConnection::TableFlags(), QgsFeedback *feedback=nullptr) const
Returns information on the tables in the given schema.
QFlags< TableFlag > TableFlags
@ SqlLayers
Can create vector layers from SQL SELECT queries.
@ Schemas
Can list schemas (if not set, the connection does not support schemas)
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.
virtual QStringList schemas() const
Returns information about the existing schemas.
Capabilities capabilities() const
Returns connection capabilities.
virtual QgsFields fields(const QString &schema, const QString &table, QgsFeedback *feedback=nullptr) const
Returns the fields of a table and schema.
static QIcon getThemeIcon(const QString &name, const QColor &fillColor=QColor(), const QColor &strokeColor=QColor())
Helper to get a theme icon.
A SQL editor based on QScintilla2.
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.
@ 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.