QGIS API Documentation 3.41.0-Master (3440c17df1d)
Loading...
Searching...
No Matches
qgssqlcomposerdialog.cpp
Go to the documentation of this file.
1/***************************************************************************
2 qgssqlcomposerdialog.cpp
3 Dialog to compose SQL queries
4
5begin : Apr 2016
6copyright : (C) 2016 Even Rouault
7email : even.rouault at spatialys.com
8
9 Adapted/ported from DBManager dlg_query_builder
10 ***************************************************************************/
11
12/***************************************************************************
13 * *
14 * This program is free software; you can redistribute it and/or modify *
15 * it under the terms of the GNU General Public License as published by *
16 * the Free Software Foundation; either version 2 of the License, or *
17 * (at your option) any later version. *
18 * *
19 ***************************************************************************/
20
22#include "moc_qgssqlcomposerdialog.cpp"
23#include "qgssqlstatement.h"
24#include "qgshelp.h"
25#include "qgsvectorlayer.h"
26
27#include <QMessageBox>
28#include <QKeyEvent>
29
30#include <Qsci/qscilexer.h>
31
32QgsSQLComposerDialog::QgsSQLComposerDialog( QWidget *parent, Qt::WindowFlags fl )
33 : QgsSQLComposerDialog( nullptr, parent, fl )
34{}
35
36QgsSQLComposerDialog::QgsSQLComposerDialog( QgsVectorLayer *layer, QWidget *parent, Qt::WindowFlags fl )
37 : QgsSubsetStringEditorInterface( parent, fl )
38 , mLayer( layer )
39{
40 setupUi( this );
41 connect( mTablesCombo, static_cast<void ( QComboBox::* )( int )>( &QComboBox::currentIndexChanged ), this, &QgsSQLComposerDialog::mTablesCombo_currentIndexChanged );
42 connect( mColumnsCombo, static_cast<void ( QComboBox::* )( int )>( &QComboBox::currentIndexChanged ), this, &QgsSQLComposerDialog::mColumnsCombo_currentIndexChanged );
43 connect( mSpatialPredicatesCombo, static_cast<void ( QComboBox::* )( int )>( &QComboBox::currentIndexChanged ), this, &QgsSQLComposerDialog::mSpatialPredicatesCombo_currentIndexChanged );
44 connect( mFunctionsCombo, static_cast<void ( QComboBox::* )( int )>( &QComboBox::currentIndexChanged ), this, &QgsSQLComposerDialog::mFunctionsCombo_currentIndexChanged );
45 connect( mOperatorsCombo, static_cast<void ( QComboBox::* )( int )>( &QComboBox::currentIndexChanged ), this, &QgsSQLComposerDialog::mOperatorsCombo_currentIndexChanged );
46 connect( mAddJoinButton, &QPushButton::clicked, this, &QgsSQLComposerDialog::mAddJoinButton_clicked );
47 connect( mRemoveJoinButton, &QPushButton::clicked, this, &QgsSQLComposerDialog::mRemoveJoinButton_clicked );
48 connect( mTableJoins, &QTableWidget::itemSelectionChanged, this, &QgsSQLComposerDialog::mTableJoins_itemSelectionChanged );
49
50 mQueryEdit->setWrapMode( QsciScintilla::WrapWord );
51 mQueryEdit->installEventFilter( this );
52 mColumnsEditor->installEventFilter( this );
53 mTablesEditor->installEventFilter( this );
54 mTableJoins->installEventFilter( this );
55 mWhereEditor->installEventFilter( this );
56 mOrderEditor->installEventFilter( this );
57 mTablesCombo->view()->installEventFilter( this );
58
59
60 connect( mButtonBox->button( QDialogButtonBox::Reset ), &QAbstractButton::clicked,
61 this, &QgsSQLComposerDialog::reset );
62
63 connect( mQueryEdit, &QsciScintilla::textChanged,
64 this, &QgsSQLComposerDialog::splitSQLIntoFields );
65 connect( mColumnsEditor, &QTextEdit::textChanged,
66 this, &QgsSQLComposerDialog::buildSQLFromFields );
67 connect( mTablesEditor, &QLineEdit::textChanged,
68 this, &QgsSQLComposerDialog::buildSQLFromFields );
69 connect( mWhereEditor, &QTextEdit::textChanged,
70 this, &QgsSQLComposerDialog::buildSQLFromFields );
71 connect( mOrderEditor, &QTextEdit::textChanged,
72 this, &QgsSQLComposerDialog::buildSQLFromFields );
73 connect( mTableJoins, &QTableWidget::cellChanged,
74 this, &QgsSQLComposerDialog::buildSQLFromFields );
75 connect( mButtonBox, &QDialogButtonBox::helpRequested,
76 this, &QgsSQLComposerDialog::showHelp );
77
78 QStringList baseList;
79 baseList << QStringLiteral( "SELECT" );
80 baseList << QStringLiteral( "FROM" );
81 baseList << QStringLiteral( "JOIN" );
82 baseList << QStringLiteral( "ON" );
83 baseList << QStringLiteral( "USING" );
84 baseList << QStringLiteral( "WHERE" );
85 baseList << QStringLiteral( "AND" );
86 baseList << QStringLiteral( "OR" );
87 baseList << QStringLiteral( "NOT" );
88 baseList << QStringLiteral( "IS" );
89 baseList << QStringLiteral( "NULL" );
90 baseList << QStringLiteral( "LIKE" );
91 baseList << QStringLiteral( "ORDER" );
92 baseList << QStringLiteral( "BY" );
93 addApis( baseList );
94
95 QStringList operatorsList;
96 operatorsList << QStringLiteral( "AND" );
97 operatorsList << QStringLiteral( "OR" );
98 operatorsList << QStringLiteral( "NOT" );
99 operatorsList << QStringLiteral( "=" );
100 operatorsList << QStringLiteral( "<" );
101 operatorsList << QStringLiteral( "<=" );
102 operatorsList << QStringLiteral( ">" );
103 operatorsList << QStringLiteral( ">=" );
104 operatorsList << QStringLiteral( "<>" );
105 operatorsList << QStringLiteral( "BETWEEN" );
106 operatorsList << QStringLiteral( "NOT BETWEEN" );
107 operatorsList << QStringLiteral( "IS" );
108 operatorsList << QStringLiteral( "IS NOT" );
109 operatorsList << QStringLiteral( "IN" );
110 operatorsList << QStringLiteral( "LIKE" );
111 addOperators( operatorsList );
112
113 mAggregatesCombo->hide();
114 mFunctionsCombo->hide();
115 mSpatialPredicatesCombo->hide();
116 mStringFunctionsCombo->hide();
117
118 delete mPageColumnsValues;
119 mPageColumnsValues = nullptr;
120
121 mRemoveJoinButton->setEnabled( false );
122
123 mTableJoins->setRowCount( 0 );
124 mTableJoins->setItem( 0, 0, new QTableWidgetItem( QString() ) );
125 mTableJoins->setItem( 0, 1, new QTableWidgetItem( QString() ) );
126}
127
129{
130 // Besides avoid memory leaks, this is useful since QSciAPIs::prepare()
131 // starts a thread. If the dialog was killed before the thread had started,
132 // he could run against a dead widget. This can happen in unit tests.
133 delete mQueryEdit->lexer()->apis();
134 mQueryEdit->lexer()->setAPIs( nullptr );
135}
136
137bool QgsSQLComposerDialog::eventFilter( QObject *obj, QEvent *event )
138{
139 if ( event->type() == QEvent::FocusIn )
140 {
141 if ( obj == mTablesCombo->view() )
142 lastSearchedText.clear();
143 else
144 mFocusedObject = obj;
145 }
146
147 // Custom search in table combobox
148 if ( event->type() == QEvent::KeyPress && obj == mTablesCombo->view() )
149 {
150 QString currentString = ( ( QKeyEvent * )event )->text();
151 if ( !currentString.isEmpty() && ( ( currentString[0] >= 'a' && currentString[0] <= 'z' ) ||
152 ( currentString[0] >= 'A' && currentString[0] <= 'Z' ) ||
153 ( currentString[0] >= '0' && currentString[0] <= '9' ) ||
154 currentString[0] == ':' || currentString[0] == '_' || currentString[0] == ' ' ||
155 currentString[0] == '(' || currentString[0] == ')' ) )
156 {
157 // First attempt is concatenation of existing search text
158 // Second attempt is just the new character
159 const int attemptCount = ( lastSearchedText.isEmpty() ) ? 1 : 2;
160 for ( int attempt = 0; attempt < attemptCount; attempt ++ )
161 {
162 if ( attempt == 0 )
163 lastSearchedText += currentString;
164 else
165 lastSearchedText = currentString;
166
167 // Find the string that contains the searched text, and in case
168 // of several matches, pickup the one where the searched text is the
169 // most at the beginning.
170 int iBestCandidate = 0;
171 int idxInTextOfBestCandidate = 1000;
172 for ( int i = 1; i < mTablesCombo->count(); i++ )
173 {
174 const int idxInText = mTablesCombo->itemText( i ).indexOf( lastSearchedText, Qt::CaseInsensitive );
175 if ( idxInText >= 0 && idxInText < idxInTextOfBestCandidate )
176 {
177 iBestCandidate = i;
178 idxInTextOfBestCandidate = idxInText;
179 }
180 }
181 if ( iBestCandidate > 0 )
182 {
183 mTablesCombo->view()->setCurrentIndex( mTablesCombo->model()->index( 0, 0 ).sibling( iBestCandidate, 0 ) );
184 return true;
185 }
186 }
187 lastSearchedText.clear();
188 }
189 }
190
191 return QDialog::eventFilter( obj, event );
192}
193
195{
196 mTableSelectedCallback = tableSelectedCallback;
197}
198
200{
201 mSQLValidatorCallback = sqlValidatorCallback;
202}
203
204void QgsSQLComposerDialog::setSql( const QString &sql )
205{
206 mResetSql = sql;
207 mQueryEdit->setText( sql );
208}
209
211{
212 return mQueryEdit->text();
213}
214
215void QgsSQLComposerDialog::accept()
216{
217 if ( mSQLValidatorCallback )
218 {
219 QString errorMsg, warningMsg;
220 if ( !mSQLValidatorCallback->isValid( sql(), errorMsg, warningMsg ) )
221 {
222 if ( errorMsg.isEmpty() )
223 errorMsg = tr( "An error occurred during evaluation of the SQL statement." );
224 QMessageBox::critical( this, tr( "SQL Evaluation" ), errorMsg );
225 return;
226 }
227 if ( !warningMsg.isEmpty() )
228 {
229 QMessageBox::warning( this, tr( "SQL Evaluation" ), warningMsg );
230 }
231 }
232 if ( mLayer )
233 {
234 mLayer->setSubsetString( sql() );
235 }
236 QDialog::accept();
237}
238
239void QgsSQLComposerDialog::buildSQLFromFields()
240{
241 if ( mAlreadyModifyingFields )
242 return;
243 mAlreadyModifyingFields = true;
244 QString sql( QStringLiteral( "SELECT " ) );
245 if ( mDistinct )
246 sql += QLatin1String( "DISTINCT " );
247 sql += mColumnsEditor->toPlainText();
248 sql += QLatin1String( " FROM " );
249 sql += mTablesEditor->text();
250
251 const int rows = mTableJoins->rowCount();
252 for ( int i = 0; i < rows; i++ )
253 {
254 QTableWidgetItem *itemTable = mTableJoins->item( i, 0 );
255 QTableWidgetItem *itemOn = mTableJoins->item( i, 1 );
256 if ( itemTable && !itemTable->text().isEmpty() &&
257 itemOn && !itemOn->text().isEmpty() )
258 {
259 sql += QLatin1String( " JOIN " );
260 sql += itemTable->text();
261 sql += QLatin1String( " ON " );
262 sql += itemOn->text();
263 }
264 }
265
266 if ( !mWhereEditor->toPlainText().isEmpty() )
267 {
268 sql += QLatin1String( " WHERE " );
269 sql += mWhereEditor->toPlainText();
270 }
271 if ( !mOrderEditor->toPlainText().isEmpty() )
272 {
273 sql += QLatin1String( " ORDER BY " );
274 sql += mOrderEditor->toPlainText();
275 }
276 mQueryEdit->setText( sql );
277
278 mAlreadyModifyingFields = false;
279}
280
281void QgsSQLComposerDialog::splitSQLIntoFields()
282{
283 if ( mAlreadyModifyingFields )
284 return;
285 const QgsSQLStatement sql( mQueryEdit->text() );
286 if ( sql.hasParserError() )
287 return;
288 const QgsSQLStatement::NodeSelect *nodeSelect = dynamic_cast<const QgsSQLStatement::NodeSelect *>( sql.rootNode() );
289 if ( !nodeSelect )
290 return;
291 mDistinct = nodeSelect->distinct();
292 const QList<QgsSQLStatement::NodeSelectedColumn *> columns = nodeSelect->columns();
293 QString columnText;
294 const auto constColumns = columns;
295 for ( QgsSQLStatement::NodeSelectedColumn *column : constColumns )
296 {
297 if ( !columnText.isEmpty() )
298 columnText += QLatin1String( ", " );
299 columnText += column->dump();
300 }
301
302 const QList<QgsSQLStatement::NodeTableDef *> tables = nodeSelect->tables();
303 QString tablesText;
304 const auto constTables = tables;
305 for ( QgsSQLStatement::NodeTableDef *table : constTables )
306 {
307 if ( !tablesText.isEmpty() )
308 tablesText += QLatin1String( ", " );
309 loadTableColumns( QgsSQLStatement::quotedIdentifierIfNeeded( table->name() ) );
310 tablesText += table->dump();
311 }
312
313 QString whereText;
314 QgsSQLStatement::Node *where = nodeSelect->where();
315 if ( where )
316 whereText = where->dump();
317
318 QString orderText;
319 const QList<QgsSQLStatement::NodeColumnSorted *> orderColumns = nodeSelect->orderBy();
320 const auto constOrderColumns = orderColumns;
321 for ( QgsSQLStatement::NodeColumnSorted *column : constOrderColumns )
322 {
323 if ( !orderText.isEmpty() )
324 orderText += QLatin1String( ", " );
325 orderText += column->dump();
326 }
327
328 const QList<QgsSQLStatement::NodeJoin *> joins = nodeSelect->joins();
329
330 mAlreadyModifyingFields = true;
331 mColumnsEditor->setPlainText( columnText );
332 mTablesEditor->setText( tablesText );
333 mWhereEditor->setPlainText( whereText );
334 mOrderEditor->setPlainText( orderText );
335
336 mTableJoins->setRowCount( joins.size() + 1 );
337 int iRow = 0;
338 const auto constJoins = joins;
339 for ( QgsSQLStatement::NodeJoin *join : constJoins )
340 {
341 loadTableColumns( QgsSQLStatement::quotedIdentifierIfNeeded( join->tableDef()->name() ) );
342 mTableJoins->setItem( iRow, 0, new QTableWidgetItem( join->tableDef()->dump() ) );
343 if ( join->onExpr() )
344 mTableJoins->setItem( iRow, 1, new QTableWidgetItem( join->onExpr()->dump() ) );
345 else
346 mTableJoins->setItem( iRow, 1, new QTableWidgetItem( QString() ) );
347 iRow ++;
348 }
349 mTableJoins->setItem( iRow, 0, new QTableWidgetItem( QString() ) );
350 mTableJoins->setItem( iRow, 1, new QTableWidgetItem( QString() ) );
351
352 mAlreadyModifyingFields = false;
353}
354
355void QgsSQLComposerDialog::addTableNames( const QStringList &list )
356{
357 const auto constList = list;
358 for ( const QString &name : constList )
359 mapTableEntryTextToName[name] = name;
360 mTablesCombo->addItems( list );
361 addApis( list );
362}
363
364void QgsSQLComposerDialog::addTableNames( const QList<PairNameTitle> &listNameTitle )
365{
366 QStringList listCombo;
367 QStringList listApi;
368 const auto constListNameTitle = listNameTitle;
369 for ( const PairNameTitle &pair : constListNameTitle )
370 {
371 listApi << pair.first;
372 QString entryText( pair.first );
373 if ( !pair.second.isEmpty() && pair.second != pair.first )
374 {
375 if ( pair.second.size() < 40 )
376 entryText += " (" + pair.second + ")";
377 else
378 entryText += " (" + pair.second.mid( 0, 20 ) + QChar( 0x2026 ) + pair.second.mid( pair.second.size() - 20 ) + ")";
379 }
380 listCombo << entryText;
381 mapTableEntryTextToName[entryText] = pair.first;
382 }
383 mTablesCombo->addItems( listCombo );
384 addApis( listApi );
385}
386
387void QgsSQLComposerDialog::addColumnNames( const QStringList &list, const QString &tableName )
388{
389 QList<PairNameType> listPair;
390 const auto constList = list;
391 for ( const QString &name : constList )
392 listPair << PairNameType( name, QString() );
393 addColumnNames( listPair, tableName );
394}
395
396static QString sanitizeType( QString type )
397{
398 if ( type.startsWith( QLatin1String( "xs:" ) ) )
399 return type.mid( 3 );
400 if ( type.startsWith( QLatin1String( "xsd:" ) ) )
401 return type.mid( 4 );
402 if ( type == QLatin1String( "gml:AbstractGeometryType" ) )
403 return QStringLiteral( "geometry" );
404 return type;
405}
406
407void QgsSQLComposerDialog::addColumnNames( const QList<PairNameType> &list, const QString &tableName )
408{
409 mAlreadySelectedTables.insert( tableName );
410 if ( mColumnsCombo->count() > 1 )
411 mColumnsCombo->insertSeparator( mColumnsCombo->count() );
412
413 QStringList listCombo;
414 QStringList listApi;
415 const auto constList = list;
416 for ( const PairNameType &pair : constList )
417 {
418 listApi << pair.first;
419 QString entryText( pair.first );
420 if ( !pair.second.isEmpty() )
421 {
422 entryText += " (" + sanitizeType( pair.second ) + ")";
423 }
424 listCombo << entryText;
425 mapColumnEntryTextToName[entryText] = pair.first;
426 }
427 mColumnsCombo->addItems( listCombo );
428
429 addApis( listApi );
430}
431
432void QgsSQLComposerDialog::addOperators( const QStringList &list )
433{
434 mOperatorsCombo->addItems( list );
435 addApis( list );
436}
437
438static QString getFunctionAbbridgedParameters( const QgsSQLComposerDialog::Function &f )
439{
440 if ( f.minArgs >= 0 && f.maxArgs > f.minArgs )
441 {
442 return QObject::tr( "%1 to %n argument(s)", nullptr, f.maxArgs ).arg( f.minArgs );
443 }
444 else if ( f.minArgs == 0 && f.maxArgs == 0 )
445 {
446 }
447 else if ( f.minArgs > 0 && f.maxArgs == f.minArgs )
448 {
449 return QObject::tr( "%n argument(s)", nullptr, f.minArgs );
450 }
451 else if ( f.minArgs >= 0 && f.maxArgs < 0 )
452 {
453 return QObject::tr( "%n argument(s) or more", nullptr, f.minArgs );
454 }
455 return QString();
456}
457
458
459void QgsSQLComposerDialog::getFunctionList( const QList<Function> &list,
460 QStringList &listApi,
461 QStringList &listCombo,
462 QMap<QString, QString> &mapEntryTextToName )
463{
464 const auto constList = list;
465 for ( const Function &f : constList )
466 {
467 listApi << f.name;
468 QString entryText( f.name );
469 entryText += QLatin1Char( '(' );
470 if ( !f.argumentList.isEmpty() )
471 {
472 for ( int i = 0; i < f.argumentList.size(); i++ )
473 {
474 if ( f.minArgs >= 0 && i >= f.minArgs ) entryText += QLatin1Char( '[' );
475 if ( i > 0 ) entryText += QLatin1String( ", " );
476 if ( f.argumentList[i].name == QLatin1String( "number" ) && !f.argumentList[i].type.isEmpty() )
477 {
478 entryText += sanitizeType( f.argumentList[i].type );
479 }
480 else
481 {
482 entryText += f.argumentList[i].name;
483 const QString sanitizedType( sanitizeType( f.argumentList[i].type ) );
484 if ( !f.argumentList[i].type.isEmpty() &&
485 f.argumentList[i].name != sanitizedType )
486 {
487 entryText += QLatin1String( ": " );
488 entryText += sanitizedType;
489 }
490 }
491 if ( f.minArgs >= 0 && i >= f.minArgs ) entryText += QLatin1Char( ']' );
492 }
493 if ( entryText.size() > 60 )
494 {
495 entryText = f.name;
496 entryText += QLatin1Char( '(' );
497 entryText += getFunctionAbbridgedParameters( f );
498 }
499 }
500 else
501 {
502 entryText += getFunctionAbbridgedParameters( f );
503 }
504 entryText += QLatin1Char( ')' );
505 if ( !f.returnType.isEmpty() )
506 entryText += ": " + sanitizeType( f.returnType );
507 listCombo << entryText;
508 mapEntryTextToName[entryText] = f.name + "(";
509 }
510}
511
512void QgsSQLComposerDialog::addSpatialPredicates( const QStringList &list )
513{
514 QList<Function> listFunction;
515 const auto constList = list;
516 for ( const QString &name : constList )
517 {
518 Function f;
519 f.name = name;
520 listFunction << f;
521 }
522 addSpatialPredicates( listFunction );
523}
524
525void QgsSQLComposerDialog::addSpatialPredicates( const QList<Function> &list )
526{
527 QStringList listApi;
528 QStringList listCombo;
529 getFunctionList( list, listApi, listCombo, mapSpatialPredicateEntryTextToName );
530 mSpatialPredicatesCombo->addItems( listCombo );
531 mSpatialPredicatesCombo->show();
532 addApis( listApi );
533}
534
535void QgsSQLComposerDialog::addFunctions( const QStringList &list )
536{
537 QList<Function> listFunction;
538 const auto constList = list;
539 for ( const QString &name : constList )
540 {
541 Function f;
542 f.name = name;
543 listFunction << f;
544 }
545 addFunctions( listFunction );
546}
547
548void QgsSQLComposerDialog::addFunctions( const QList<Function> &list )
549{
550 QStringList listApi;
551 QStringList listCombo;
552 getFunctionList( list, listApi, listCombo, mapFunctionEntryTextToName );
553 mFunctionsCombo->addItems( listCombo );
554 mFunctionsCombo->show();
555 addApis( listApi );
556}
557
558void QgsSQLComposerDialog::loadTableColumns( const QString &table )
559{
560 if ( mTableSelectedCallback )
561 {
562 if ( !mAlreadySelectedTables.contains( table ) )
563 {
564 mTableSelectedCallback->tableSelected( table );
565 mAlreadySelectedTables.insert( table );
566 }
567 }
568}
569
570static void resetCombo( QComboBox *combo )
571{
572 // We do it in a deferred way, otherwise Valgrind complains when using QTest
573 // since basically this call a recursive call to QComboBox::setCurrentIndex()
574 // which cause internal QComboBox logic to operate on a destroyed object
575 // However that isn't reproduce in live session. Anyway this hack is safe
576 // in all modes.
577 QMetaObject::invokeMethod( combo, "setCurrentIndex", Qt::QueuedConnection, Q_ARG( int, 0 ) );
578}
579
580void QgsSQLComposerDialog::mTablesCombo_currentIndexChanged( int )
581{
582 const int index = mTablesCombo->currentIndex();
583 if ( index <= 0 )
584 return;
585 QObject *obj = mFocusedObject;
586 const QString newText = mapTableEntryTextToName[mTablesCombo->currentText()];
587 loadTableColumns( newText );
588 if ( obj == mTablesEditor )
589 {
590 const QString currentText = mTablesEditor->text();
591 if ( currentText.isEmpty() )
592 mTablesEditor->setText( newText );
593 else
594 mTablesEditor->setText( currentText + ", " + newText );
595 }
596 else if ( obj == mTableJoins )
597 {
598 if ( mTableJoins->selectedItems().size() == 1 )
599 {
600 mTableJoins->selectedItems().at( 0 )->setText( newText );
601 }
602 }
603 else if ( obj == mWhereEditor )
604 {
605 mWhereEditor->insertPlainText( newText );
606 }
607 else if ( obj == mOrderEditor )
608 {
609 mOrderEditor->insertPlainText( newText );
610 }
611 else if ( obj == mQueryEdit )
612 {
613 mQueryEdit->insertText( newText );
614 }
615 resetCombo( mTablesCombo );
616}
617
618void QgsSQLComposerDialog::mColumnsCombo_currentIndexChanged( int )
619{
620 const int index = mColumnsCombo->currentIndex();
621 if ( index <= 0 )
622 return;
623 QObject *obj = mFocusedObject;
624 const QString newText = mapColumnEntryTextToName[mColumnsCombo->currentText()];
625 if ( obj == mColumnsEditor )
626 {
627 const QString currentText = mColumnsEditor->toPlainText();
628 if ( currentText.isEmpty() )
629 mColumnsEditor->insertPlainText( newText );
630 else
631 mColumnsEditor->insertPlainText( ",\n" + newText );
632 }
633 else if ( obj == mTableJoins )
634 {
635 if ( mTableJoins->selectedItems().size() == 1 &&
636 mTableJoins->selectedItems().at( 0 )->column() == 1 )
637 {
638 const QString currentText( mTableJoins->selectedItems().at( 0 )->text() );
639 if ( !currentText.isEmpty() && !currentText.contains( QLatin1String( "=" ) ) )
640 mTableJoins->selectedItems().at( 0 )->setText( currentText + " = " + newText );
641 else
642 mTableJoins->selectedItems().at( 0 )->setText( currentText + newText );
643 }
644 }
645 else if ( obj == mWhereEditor )
646 {
647 mWhereEditor->insertPlainText( newText );
648 }
649 else if ( obj == mOrderEditor )
650 {
651 mOrderEditor->insertPlainText( newText );
652 }
653 else if ( obj == mQueryEdit )
654 {
655 mQueryEdit->insertText( newText );
656 }
657 resetCombo( mColumnsCombo );
658}
659
660void QgsSQLComposerDialog::mFunctionsCombo_currentIndexChanged( int )
661{
662 functionCurrentIndexChanged( mFunctionsCombo, mapFunctionEntryTextToName );
663}
664
665void QgsSQLComposerDialog::mSpatialPredicatesCombo_currentIndexChanged( int )
666{
667 functionCurrentIndexChanged( mSpatialPredicatesCombo, mapSpatialPredicateEntryTextToName );
668}
669
670void QgsSQLComposerDialog::functionCurrentIndexChanged( QComboBox *combo,
671 const QMap<QString, QString> &mapEntryTextToName )
672{
673 const int index = combo->currentIndex();
674 if ( index <= 0 )
675 return;
676 QObject *obj = mFocusedObject;
677 const QString newText = mapEntryTextToName[combo->currentText()];
678 if ( obj == mColumnsEditor )
679 {
680 mColumnsEditor->insertPlainText( newText );
681 }
682 else if ( obj == mWhereEditor )
683 {
684 mWhereEditor->insertPlainText( newText );
685 }
686 else if ( obj == mQueryEdit )
687 {
688 mQueryEdit->insertText( newText );
689 }
690 resetCombo( combo );
691}
692
693void QgsSQLComposerDialog::mOperatorsCombo_currentIndexChanged( int )
694{
695 const int index = mOperatorsCombo->currentIndex();
696 if ( index <= 0 )
697 return;
698 QObject *obj = mFocusedObject;
699 const QString newText = mOperatorsCombo->currentText();
700 if ( obj == mColumnsEditor )
701 {
702 mColumnsEditor->insertPlainText( newText );
703 }
704 else if ( obj == mWhereEditor )
705 {
706 mWhereEditor->insertPlainText( newText );
707 }
708 else if ( obj == mTableJoins )
709 {
710 if ( mTableJoins->selectedItems().size() == 1 &&
711 mTableJoins->selectedItems().at( 0 )->column() == 1 )
712 {
713 const QString currentText( mTableJoins->selectedItems().at( 0 )->text() );
714 mTableJoins->selectedItems().at( 0 )->setText( currentText + newText );
715 }
716 }
717 else if ( obj == mQueryEdit )
718 {
719 mQueryEdit->insertText( newText );
720 }
721 resetCombo( mOperatorsCombo );
722}
723
724void QgsSQLComposerDialog::mAddJoinButton_clicked()
725{
726 int insertRow = mTableJoins->currentRow();
727 const int rowCount = mTableJoins->rowCount();
728 if ( insertRow < 0 )
729 insertRow = rowCount;
730 mTableJoins->setRowCount( rowCount + 1 );
731 for ( int row = rowCount ; row > insertRow + 1; row -- )
732 {
733 mTableJoins->setItem( row, 0, mTableJoins->takeItem( row - 1, 0 ) );
734 mTableJoins->setItem( row, 1, mTableJoins->takeItem( row - 1, 1 ) );
735 }
736 mTableJoins->setItem( ( insertRow == rowCount ) ? insertRow : insertRow + 1, 0, new QTableWidgetItem( QString() ) );
737 mTableJoins->setItem( ( insertRow == rowCount ) ? insertRow : insertRow + 1, 1, new QTableWidgetItem( QString() ) );
738}
739
740void QgsSQLComposerDialog::mRemoveJoinButton_clicked()
741{
742 int row = mTableJoins->currentRow();
743 if ( row < 0 )
744 return;
745 const int rowCount = mTableJoins->rowCount();
746 for ( ; row < rowCount - 1; row ++ )
747 {
748 mTableJoins->setItem( row, 0, mTableJoins->takeItem( row + 1, 0 ) );
749 mTableJoins->setItem( row, 1, mTableJoins->takeItem( row + 1, 1 ) );
750 }
751 mTableJoins->setRowCount( rowCount - 1 );
752
753 buildSQLFromFields();
754}
755
756void QgsSQLComposerDialog::reset()
757{
758 mQueryEdit->setText( mResetSql );
759}
760
761void QgsSQLComposerDialog::mTableJoins_itemSelectionChanged()
762{
763 mRemoveJoinButton->setEnabled( mTableJoins->selectedItems().size() == 1 );
764}
765
766void QgsSQLComposerDialog::addApis( const QStringList &list )
767{
768 mApiList += list;
769
770 delete mQueryEdit->lexer()->apis();
771 QsciAPIs *apis = new QsciAPIs( mQueryEdit->lexer() );
772
773 const auto constMApiList = mApiList;
774 for ( const QString &str : constMApiList )
775 {
776 apis->add( str );
777 }
778
779 apis->prepare();
780 mQueryEdit->lexer()->setAPIs( apis );
781}
782
783void QgsSQLComposerDialog::setSupportMultipleTables( bool on, const QString &mainTypename )
784{
785 mJoinsLabels->setVisible( on );
786 mTableJoins->setVisible( on );
787 mAddJoinButton->setVisible( on );
788 mRemoveJoinButton->setVisible( on );
789 mTablesCombo->setVisible( on );
790
791 QString mainTypenameFormatted;
792 if ( !mainTypename.isEmpty() )
793 mainTypenameFormatted = "(" + mainTypename + ")";
794 mQueryEdit->setToolTip( tr( "This is the SQL query editor. The SQL statement can select data from several tables, \n"
795 "but it must compulsory include the main typename %1 in the selected tables, \n"
796 "and only the geometry column of the main typename can be used as the geometry column of the resulting layer." ).arg( mainTypenameFormatted ) );
797}
798
799void QgsSQLComposerDialog::showHelp()
800{
801 QgsHelp::openHelp( QStringLiteral( "working_with_ogc/ogc_client_support.html#ogc-wfs" ) );
802}
static void openHelp(const QString &key)
Opens help topic for the given help key using default system web browser.
Definition qgshelp.cpp:39
Callback to do validation check on dialog validation.
virtual bool isValid(const QString &sql, QString &errorReason, QString &warningMsg)=0
method should return true if the SQL is valid. Otherwise return false and set the errorReason
Callback to do actions on table selection.
virtual void tableSelected(const QString &name)=0
method called when a table is selected
SQL composer dialog.
bool eventFilter(QObject *obj, QEvent *event) override
QgsSQLComposerDialog(QWidget *parent=nullptr, Qt::WindowFlags fl=QgsGuiUtils::ModalDialogFlags)
constructor
void addSpatialPredicates(const QStringList &list)
add a list of spatial predicates
void setSQLValidatorCallback(SQLValidatorCallback *sqlValidatorCallback)
Set a callback that will be called when the OK button is pushed.
void setTableSelectedCallback(TableSelectedCallback *tableSelectedCallback)
Set a callback that will be called when a new table is selected, so that new column names can be adde...
QPair< QString, QString > PairNameType
pair (name, type)
void addOperators(const QStringList &list)
add a list of operators
void addColumnNames(const QStringList &list, const QString &tableName)
add a list of column names
void setSupportMultipleTables(bool bMultipleTables, const QString &mainTypename=QString())
Sets if multiple tables/joins are supported. Default is false.
void addApis(const QStringList &list)
add a list of API for autocompletion
void setSql(const QString &sql)
initialize the SQL statement
void addFunctions(const QStringList &list)
add a list of functions
void addTableNames(const QStringList &list)
add a list of table names
QPair< QString, QString > PairNameTitle
pair (name, title)
QString sql() const
Gets the SQL statement.
QList< QgsSQLStatement::NodeColumnSorted * > orderBy() const
Returns the list of order by columns.
QList< QgsSQLStatement::NodeSelectedColumn * > columns() const
Returns the list of columns.
bool distinct() const
Returns if the SELECT is DISTINCT.
QList< QgsSQLStatement::NodeJoin * > joins() const
Returns the list of joins.
QgsSQLStatement::Node * where() const
Returns the where clause.
QList< QgsSQLStatement::NodeTableDef * > tables() const
Returns the list of tables.
Abstract node class.
virtual QString dump() const =0
Abstract virtual dump method.
Class for parsing SQL statements.
static QString quotedIdentifierIfNeeded(const QString &name)
Returns a quoted column reference (in double quotes) if needed, or otherwise the original string.
Interface for a dialog that can edit subset strings.
Represents a vector layer which manages a vector based data sets.
virtual bool setSubsetString(const QString &subset)
Sets the string (typically sql) used to define a subset of the layer.
#define str(x)
Definition qgis.cpp:39
description of server functions
QString returnType
Returns type, or empty if unknown.
int maxArgs
maximum number of argument (or -1 if unknown)
int minArgs
minimum number of argument (or -1 if unknown)
QList< Argument > argumentList
list of arguments. May be empty despite minArgs > 0