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