QGIS API Documentation  3.2.0-Bonn (bc43194)
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( QLatin1String( "" ) ) );
117  mTableJoins->setItem( 0, 1, new QTableWidgetItem( QLatin1String( "" ) ) );
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  Q_FOREACH ( QgsSQLStatement::NodeSelectedColumn *column, columns )
283  {
284  if ( !columnText.isEmpty() )
285  columnText += QLatin1String( ", " );
286  columnText += column->dump();
287  }
288 
289  QList<QgsSQLStatement::NodeTableDef *> tables = nodeSelect->tables();
290  QString tablesText;
291  Q_FOREACH ( QgsSQLStatement::NodeTableDef *table, tables )
292  {
293  if ( !tablesText.isEmpty() )
294  tablesText += QLatin1String( ", " );
295  loadTableColumns( QgsSQLStatement::quotedIdentifierIfNeeded( table->name() ) );
296  tablesText += table->dump();
297  }
298 
299  QString whereText;
300  QgsSQLStatement::Node *where = nodeSelect->where();
301  if ( where )
302  whereText = where->dump();
303 
304  QString orderText;
305  QList<QgsSQLStatement::NodeColumnSorted *> orderColumns = nodeSelect->orderBy();
306  Q_FOREACH ( QgsSQLStatement::NodeColumnSorted *column, orderColumns )
307  {
308  if ( !orderText.isEmpty() )
309  orderText += QLatin1String( ", " );
310  orderText += column->dump();
311  }
312 
313  QList<QgsSQLStatement::NodeJoin *> joins = nodeSelect->joins();
314 
315  mAlreadyModifyingFields = true;
316  mColumnsEditor->setPlainText( columnText );
317  mTablesEditor->setText( tablesText );
318  mWhereEditor->setPlainText( whereText );
319  mOrderEditor->setPlainText( orderText );
320 
321  mTableJoins->setRowCount( joins.size() + 1 );
322  int iRow = 0;
323  Q_FOREACH ( QgsSQLStatement::NodeJoin *join, joins )
324  {
325  loadTableColumns( QgsSQLStatement::quotedIdentifierIfNeeded( join->tableDef()->name() ) );
326  mTableJoins->setItem( iRow, 0, new QTableWidgetItem( join->tableDef()->dump() ) );
327  if ( join->onExpr() )
328  mTableJoins->setItem( iRow, 1, new QTableWidgetItem( join->onExpr()->dump() ) );
329  else
330  mTableJoins->setItem( iRow, 1, new QTableWidgetItem( QLatin1String( "" ) ) );
331  iRow ++;
332  }
333  mTableJoins->setItem( iRow, 0, new QTableWidgetItem( QLatin1String( "" ) ) );
334  mTableJoins->setItem( iRow, 1, new QTableWidgetItem( QLatin1String( "" ) ) );
335 
336  mAlreadyModifyingFields = false;
337 }
338 
339 void QgsSQLComposerDialog::addTableNames( const QStringList &list )
340 {
341  Q_FOREACH ( const QString &name, list )
342  mapTableEntryTextToName[name] = name;
343  mTablesCombo->addItems( list );
344  addApis( list );
345 }
346 
347 void QgsSQLComposerDialog::addTableNames( const QList<PairNameTitle> &listNameTitle )
348 {
349  QStringList listCombo;
350  QStringList listApi;
351  Q_FOREACH ( const PairNameTitle &pair, listNameTitle )
352  {
353  listApi << pair.first;
354  QString entryText( pair.first );
355  if ( !pair.second.isEmpty() && pair.second != pair.first )
356  {
357  if ( pair.second.size() < 40 )
358  entryText += " (" + pair.second + ")";
359  else
360  entryText += " (" + pair.second.mid( 0, 20 ) + QChar( 0x2026 ) + pair.second.mid( pair.second.size() - 20 ) + ")";
361  }
362  listCombo << entryText;
363  mapTableEntryTextToName[entryText] = pair.first;
364  }
365  mTablesCombo->addItems( listCombo );
366  addApis( listApi );
367 }
368 
369 void QgsSQLComposerDialog::addColumnNames( const QStringList &list, const QString &tableName )
370 {
371  QList<PairNameType> listPair;
372  Q_FOREACH ( const QString &name, list )
373  listPair << PairNameType( name, QString() );
374  addColumnNames( listPair, tableName );
375 }
376 
377 static QString sanitizeType( QString type )
378 {
379  if ( type.startsWith( QLatin1String( "xs:" ) ) )
380  return type.mid( 3 );
381  if ( type.startsWith( QLatin1String( "xsd:" ) ) )
382  return type.mid( 4 );
383  if ( type == QLatin1String( "gml:AbstractGeometryType" ) )
384  return QStringLiteral( "geometry" );
385  return type;
386 }
387 
388 void QgsSQLComposerDialog::addColumnNames( const QList<PairNameType> &list, const QString &tableName )
389 {
390  mAlreadySelectedTables.insert( tableName );
391  if ( mColumnsCombo->count() > 1 )
392  mColumnsCombo->insertSeparator( mColumnsCombo->count() );
393 
394  QStringList listCombo;
395  QStringList listApi;
396  Q_FOREACH ( const PairNameType &pair, list )
397  {
398  listApi << pair.first;
399  QString entryText( pair.first );
400  if ( !pair.second.isEmpty() )
401  {
402  entryText += " (" + sanitizeType( pair.second ) + ")";
403  }
404  listCombo << entryText;
405  mapColumnEntryTextToName[entryText] = pair.first;
406  }
407  mColumnsCombo->addItems( listCombo );
408 
409  addApis( listApi );
410 }
411 
412 void QgsSQLComposerDialog::addOperators( const QStringList &list )
413 {
414  mOperatorsCombo->addItems( list );
415  addApis( list );
416 }
417 
418 static QString getFunctionAbbridgedParameters( const QgsSQLComposerDialog::Function &f )
419 {
420  if ( f.minArgs >= 0 && f.maxArgs > f.minArgs )
421  {
422  return QObject::tr( "%1 to %2 arguments" ).arg( f.minArgs ).arg( f.maxArgs );
423  }
424  else if ( f.minArgs == 0 && f.maxArgs == 0 )
425  {
426  }
427  else if ( f.minArgs > 0 && f.maxArgs == f.minArgs )
428  {
429  if ( f.minArgs == 1 )
430  return QObject::tr( "1 argument" );
431  else
432  return QObject::tr( "%1 arguments" ).arg( f.minArgs );
433  }
434  else if ( f.minArgs >= 0 && f.maxArgs < 0 )
435  {
436  if ( f.minArgs > 1 )
437  return QObject::tr( "%1 arguments or more" ).arg( f.minArgs );
438  else if ( f.minArgs == 1 )
439  return QObject::tr( "1 argument or more" );
440  else
441  return QObject::tr( "0 argument or more" );
442  }
443  return QString();
444 }
445 
446 
447 void QgsSQLComposerDialog::getFunctionList( const QList<Function> &list,
448  QStringList &listApi,
449  QStringList &listCombo,
450  QMap<QString, QString> &mapEntryTextToName )
451 {
452  Q_FOREACH ( const Function &f, list )
453  {
454  listApi << f.name;
455  QString entryText( f.name );
456  entryText += QLatin1String( "(" );
457  if ( !f.argumentList.isEmpty() )
458  {
459  for ( int i = 0; i < f.argumentList.size(); i++ )
460  {
461  if ( f.minArgs >= 0 && i >= f.minArgs ) entryText += QLatin1String( "[" );
462  if ( i > 0 ) entryText += QLatin1String( ", " );
463  if ( f.argumentList[i].name == QLatin1String( "number" ) && !f.argumentList[i].type.isEmpty() )
464  {
465  entryText += sanitizeType( f.argumentList[i].type );
466  }
467  else
468  {
469  entryText += f.argumentList[i].name;
470  QString sanitizedType( sanitizeType( f.argumentList[i].type ) );
471  if ( !f.argumentList[i].type.isEmpty() &&
472  f.argumentList[i].name != sanitizedType )
473  {
474  entryText += QLatin1String( ": " );
475  entryText += sanitizedType;
476  }
477  }
478  if ( f.minArgs >= 0 && i >= f.minArgs ) entryText += QLatin1String( "]" );
479  }
480  if ( entryText.size() > 60 )
481  {
482  entryText = f.name;
483  entryText += QLatin1String( "(" );
484  entryText += getFunctionAbbridgedParameters( f );
485  }
486  }
487  else
488  {
489  entryText += getFunctionAbbridgedParameters( f );
490  }
491  entryText += QLatin1String( ")" );
492  if ( !f.returnType.isEmpty() )
493  entryText += ": " + sanitizeType( f.returnType );
494  listCombo << entryText;
495  mapEntryTextToName[entryText] = f.name + "(";
496  }
497 }
498 
499 void QgsSQLComposerDialog::addSpatialPredicates( const QStringList &list )
500 {
501  QList<Function> listFunction;
502  Q_FOREACH ( const QString &name, list )
503  {
504  Function f;
505  f.name = name;
506  listFunction << f;
507  }
508  addSpatialPredicates( listFunction );
509 }
510 
511 void QgsSQLComposerDialog::addSpatialPredicates( const QList<Function> &list )
512 {
513  QStringList listApi;
514  QStringList listCombo;
515  getFunctionList( list, listApi, listCombo, mapSpatialPredicateEntryTextToName );
516  mSpatialPredicatesCombo->addItems( listCombo );
517  mSpatialPredicatesCombo->show();
518  addApis( listApi );
519 }
520 
521 void QgsSQLComposerDialog::addFunctions( const QStringList &list )
522 {
523  QList<Function> listFunction;
524  Q_FOREACH ( const QString &name, list )
525  {
526  Function f;
527  f.name = name;
528  listFunction << f;
529  }
530  addFunctions( listFunction );
531 }
532 
533 void QgsSQLComposerDialog::addFunctions( const QList<Function> &list )
534 {
535  QStringList listApi;
536  QStringList listCombo;
537  getFunctionList( list, listApi, listCombo, mapFunctionEntryTextToName );
538  mFunctionsCombo->addItems( listCombo );
539  mFunctionsCombo->show();
540  addApis( listApi );
541 }
542 
543 void QgsSQLComposerDialog::loadTableColumns( const QString &table )
544 {
545  if ( mTableSelectedCallback )
546  {
547  if ( !mAlreadySelectedTables.contains( table ) )
548  {
549  mTableSelectedCallback->tableSelected( table );
550  mAlreadySelectedTables.insert( table );
551  }
552  }
553 }
554 
555 static void resetCombo( QComboBox *combo )
556 {
557  // We do it in a deferred way, otherwise Valgrind complains when using QTest
558  // since basically this call a recursive call to QComboBox::setCurrentIndex()
559  // which cause internal QComboBox logic to operate on a destroyed object
560  // However that isn't reproduce in live session. Anyway this hack is safe
561  // in all modes.
562  QMetaObject::invokeMethod( combo, "setCurrentIndex", Qt::QueuedConnection, Q_ARG( int, 0 ) );
563 }
564 
565 void QgsSQLComposerDialog::mTablesCombo_currentIndexChanged( int )
566 {
567  int index = mTablesCombo->currentIndex();
568  if ( index <= 0 )
569  return;
570  QObject *obj = mFocusedObject;
571  QString newText = mapTableEntryTextToName[mTablesCombo->currentText()];
572  loadTableColumns( newText );
573  if ( obj == mTablesEditor )
574  {
575  QString currentText = mTablesEditor->text();
576  if ( currentText.isEmpty() )
577  mTablesEditor->setText( newText );
578  else
579  mTablesEditor->setText( currentText + ", " + newText );
580  }
581  else if ( obj == mTableJoins )
582  {
583  if ( mTableJoins->selectedItems().size() == 1 )
584  {
585  mTableJoins->selectedItems().at( 0 )->setText( newText );
586  }
587  }
588  else if ( obj == mWhereEditor )
589  {
590  mWhereEditor->insertPlainText( newText );
591  }
592  else if ( obj == mOrderEditor )
593  {
594  mOrderEditor->insertPlainText( newText );
595  }
596  else if ( obj == mQueryEdit )
597  {
598  mQueryEdit->insertText( newText );
599  }
600  resetCombo( mTablesCombo );
601 }
602 
603 void QgsSQLComposerDialog::mColumnsCombo_currentIndexChanged( int )
604 {
605  int index = mColumnsCombo->currentIndex();
606  if ( index <= 0 )
607  return;
608  QObject *obj = mFocusedObject;
609  QString newText = mapColumnEntryTextToName[mColumnsCombo->currentText()];
610  if ( obj == mColumnsEditor )
611  {
612  QString currentText = mColumnsEditor->toPlainText();
613  if ( currentText.isEmpty() )
614  mColumnsEditor->insertPlainText( newText );
615  else
616  mColumnsEditor->insertPlainText( ",\n" + newText );
617  }
618  else if ( obj == mTableJoins )
619  {
620  if ( mTableJoins->selectedItems().size() == 1 &&
621  mTableJoins->selectedItems().at( 0 )->column() == 1 )
622  {
623  QString currentText( mTableJoins->selectedItems().at( 0 )->text() );
624  if ( !currentText.isEmpty() && !currentText.contains( QLatin1String( "=" ) ) )
625  mTableJoins->selectedItems().at( 0 )->setText( currentText + " = " + newText );
626  else
627  mTableJoins->selectedItems().at( 0 )->setText( currentText + newText );
628  }
629  }
630  else if ( obj == mWhereEditor )
631  {
632  mWhereEditor->insertPlainText( newText );
633  }
634  else if ( obj == mOrderEditor )
635  {
636  mOrderEditor->insertPlainText( newText );
637  }
638  else if ( obj == mQueryEdit )
639  {
640  mQueryEdit->insertText( newText );
641  }
642  resetCombo( mColumnsCombo );
643 }
644 
645 void QgsSQLComposerDialog::mFunctionsCombo_currentIndexChanged( int )
646 {
647  functionCurrentIndexChanged( mFunctionsCombo, mapFunctionEntryTextToName );
648 }
649 
650 void QgsSQLComposerDialog::mSpatialPredicatesCombo_currentIndexChanged( int )
651 {
652  functionCurrentIndexChanged( mSpatialPredicatesCombo, mapSpatialPredicateEntryTextToName );
653 }
654 
655 void QgsSQLComposerDialog::functionCurrentIndexChanged( QComboBox *combo,
656  const QMap<QString, QString> &mapEntryTextToName )
657 {
658  int index = combo->currentIndex();
659  if ( index <= 0 )
660  return;
661  QObject *obj = mFocusedObject;
662  QString newText = mapEntryTextToName[combo->currentText()];
663  if ( obj == mColumnsEditor )
664  {
665  mColumnsEditor->insertPlainText( newText );
666  }
667  else if ( obj == mWhereEditor )
668  {
669  mWhereEditor->insertPlainText( newText );
670  }
671  else if ( obj == mQueryEdit )
672  {
673  mQueryEdit->insertText( newText );
674  }
675  resetCombo( combo );
676 }
677 
678 void QgsSQLComposerDialog::mOperatorsCombo_currentIndexChanged( int )
679 {
680  int index = mOperatorsCombo->currentIndex();
681  if ( index <= 0 )
682  return;
683  QObject *obj = mFocusedObject;
684  QString newText = mOperatorsCombo->currentText();
685  if ( obj == mColumnsEditor )
686  {
687  mColumnsEditor->insertPlainText( newText );
688  }
689  else if ( obj == mWhereEditor )
690  {
691  mWhereEditor->insertPlainText( newText );
692  }
693  else if ( obj == mTableJoins )
694  {
695  if ( mTableJoins->selectedItems().size() == 1 &&
696  mTableJoins->selectedItems().at( 0 )->column() == 1 )
697  {
698  QString currentText( mTableJoins->selectedItems().at( 0 )->text() );
699  mTableJoins->selectedItems().at( 0 )->setText( currentText + newText );
700  }
701  }
702  else if ( obj == mQueryEdit )
703  {
704  mQueryEdit->insertText( newText );
705  }
706  resetCombo( mOperatorsCombo );
707 }
708 
709 void QgsSQLComposerDialog::mAddJoinButton_clicked()
710 {
711  int insertRow = mTableJoins->currentRow();
712  int rowCount = mTableJoins->rowCount();
713  if ( insertRow < 0 )
714  insertRow = rowCount;
715  mTableJoins->setRowCount( rowCount + 1 );
716  for ( int row = rowCount ; row > insertRow + 1; row -- )
717  {
718  mTableJoins->setItem( row, 0, mTableJoins->takeItem( row - 1, 0 ) );
719  mTableJoins->setItem( row, 1, mTableJoins->takeItem( row - 1, 1 ) );
720  }
721  mTableJoins->setItem( ( insertRow == rowCount ) ? insertRow : insertRow + 1, 0, new QTableWidgetItem( QLatin1String( "" ) ) );
722  mTableJoins->setItem( ( insertRow == rowCount ) ? insertRow : insertRow + 1, 1, new QTableWidgetItem( QLatin1String( "" ) ) );
723 }
724 
725 void QgsSQLComposerDialog::mRemoveJoinButton_clicked()
726 {
727  int row = mTableJoins->currentRow();
728  if ( row < 0 )
729  return;
730  int rowCount = mTableJoins->rowCount();
731  for ( ; row < rowCount - 1; row ++ )
732  {
733  mTableJoins->setItem( row, 0, mTableJoins->takeItem( row + 1, 0 ) );
734  mTableJoins->setItem( row, 1, mTableJoins->takeItem( row + 1, 1 ) );
735  }
736  mTableJoins->setRowCount( rowCount - 1 );
737 
738  buildSQLFromFields();
739 }
740 
741 void QgsSQLComposerDialog::reset()
742 {
743  mQueryEdit->setText( mResetSql );
744 }
745 
746 void QgsSQLComposerDialog::mTableJoins_itemSelectionChanged()
747 {
748  mRemoveJoinButton->setEnabled( mTableJoins->selectedItems().size() == 1 );
749 }
750 
751 void QgsSQLComposerDialog::addApis( const QStringList &list )
752 {
753  mApiList += list;
754 
755  delete mQueryEdit->lexer()->apis();
756  QsciAPIs *apis = new QsciAPIs( mQueryEdit->lexer() );
757 
758  Q_FOREACH ( const QString &str, mApiList )
759  {
760  apis->add( str );
761  }
762 
763  apis->prepare();
764  mQueryEdit->lexer()->setAPIs( apis );
765 }
766 
767 void QgsSQLComposerDialog::setSupportMultipleTables( bool on, const QString &mainTypename )
768 {
769  mJoinsLabels->setVisible( on );
770  mTableJoins->setVisible( on );
771  mAddJoinButton->setVisible( on );
772  mRemoveJoinButton->setVisible( on );
773  mTablesCombo->setVisible( on );
774 
775  QString mainTypenameFormatted;
776  if ( !mainTypename.isEmpty() )
777  mainTypenameFormatted = " (" + mainTypename + ")";
778  mQueryEdit->setToolTip( tr( "This is the SQL query editor. The SQL statement can select data from several tables, \n"
779  "but it must compulsory include the main typename%1 in the selected tables, \n"
780  "and only the geometry column of the main typename can be used as the geometry column of the resulting layer." ).arg( mainTypenameFormatted ) );
781 }
782 
783 void QgsSQLComposerDialog::showHelp()
784 {
785  QgsHelp::openHelp( QStringLiteral( "working_with_ogc/ogc_client_support.html#ogc-wfs" ) );
786 }
virtual void tableSelected(const QString &name)=0
method called when a table is selected
void addOperators(const QStringList &list)
add a list of operators
Callback to do actions on table selection.
void addFunctions(const QStringList &list)
add a list of functions
QList< Argument > argumentList
list of arguments. May be empty despite minArgs > 0
QgsSQLStatement::Node * onExpr() const
On expression. Will be nullptr if usingColumns() is not empty.
void setSQLValidatorCallback(SQLValidatorCallback *sqlValidatorCallback)
Set a callback that will be called when the OK button is pushed.
QgsSQLStatement::NodeTableDef * tableDef() const
Table definition.
QgsSQLStatement::Node * where() const
Returns the where clause.
void addApis(const QStringList &list)
add a list of API for autocompletion
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...
int maxArgs
maximum number of argument (or -1 if unknown)
void addColumnNames(const QStringList &list, const QString &tableName)
add a list of column names
QList< QgsSQLStatement::NodeSelectedColumn * > columns() const
Returns the list of columns.
bool distinct() const
Returns if the SELECT is DISTINCT.
Abstract node class.
QPair< QString, QString > PairNameTitle
pair (name, title)
Class for parsing SQL statements.
QString name() const
Table name.
QgsSQLComposerDialog(QWidget *parent=nullptr, Qt::WindowFlags fl=QgsGuiUtils::ModalDialogFlags)
constructor
virtual QString dump() const =0
Abstract virtual dump method.
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 ...
QPair< QString, QString > PairNameType
pair (name, type)
void addSpatialPredicates(const QStringList &list)
add a list of spatial predicates
QList< QgsSQLStatement::NodeTableDef * > tables() const
Returns the list of tables.
description of server functions
Callback to do validation check on dialog validation.
QString dump() const override
Abstract virtual dump method.
QList< QgsSQLStatement::NodeColumnSorted * > orderBy() const
Returns the list of order by columns.
QString returnType
Returns type, or empty if unknown.
QString sql() const
Gets the SQL statement.
void setSupportMultipleTables(bool bMultipleTables, const QString &mainTypename=QString())
Sets if multiple tables/joins are supported. Default is false.
QList< QgsSQLStatement::NodeJoin * > joins() const
Returns the list of joins.
bool eventFilter(QObject *obj, QEvent *event) override
QString dump() const override
Abstract virtual dump method.
static QString quotedIdentifierIfNeeded(const QString &name)
Returns a quoted column reference (in double quotes) if needed, or otherwise the original string...
static void openHelp(const QString &key)
Opens help topic for the given help key using default system web browser.
Definition: qgshelp.cpp:35
QString dump() const override
Abstract virtual dump method.
void addTableNames(const QStringList &list)
add a list of table names
void setSql(const QString &sql)
initialize the SQL statement
int minArgs
minimum number of argument (or -1 if unknown)