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