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