QGIS API Documentation  3.2.0-Bonn (bc43194)
qgsquerybuilder.cpp
Go to the documentation of this file.
1 /***************************************************************************
2  qgsquerybuilder.cpp - Query Builder
3  --------------------------------------
4  Date : 2004-11-19
5  Copyright : (C) 2004 by Gary E.Sherman
6  Email : sherman at mrcc.com
7  ***************************************************************************
8  * *
9  * This program is free software; you can redistribute it and/or modify *
10  * it under the terms of the GNU General Public License as published by *
11  * the Free Software Foundation; either version 2 of the License, or *
12  * (at your option) any later version. *
13  * *
14  ***************************************************************************/
15 #include "qgsquerybuilder.h"
16 #include "qgslogger.h"
17 #include "qgsproject.h"
18 #include "qgssettings.h"
19 #include "qgsvectorlayer.h"
20 #include "qgsvectordataprovider.h"
21 
22 #include <QListView>
23 #include <QMessageBox>
24 #include <QRegExp>
25 #include <QPushButton>
26 
27 // constructor used when the query builder must make its own
28 // connection to the database
30  QWidget *parent, Qt::WindowFlags fl )
31  : QDialog( parent, fl )
32  , mPreviousFieldRow( -1 )
33  , mLayer( layer )
34 {
35  setupUi( this );
36  connect( btnEqual, &QPushButton::clicked, this, &QgsQueryBuilder::btnEqual_clicked );
37  connect( btnLessThan, &QPushButton::clicked, this, &QgsQueryBuilder::btnLessThan_clicked );
38  connect( btnGreaterThan, &QPushButton::clicked, this, &QgsQueryBuilder::btnGreaterThan_clicked );
39  connect( btnPct, &QPushButton::clicked, this, &QgsQueryBuilder::btnPct_clicked );
40  connect( btnIn, &QPushButton::clicked, this, &QgsQueryBuilder::btnIn_clicked );
41  connect( btnNotIn, &QPushButton::clicked, this, &QgsQueryBuilder::btnNotIn_clicked );
42  connect( btnLike, &QPushButton::clicked, this, &QgsQueryBuilder::btnLike_clicked );
43  connect( btnILike, &QPushButton::clicked, this, &QgsQueryBuilder::btnILike_clicked );
44  connect( lstFields, &QListView::clicked, this, &QgsQueryBuilder::lstFields_clicked );
45  connect( lstFields, &QListView::doubleClicked, this, &QgsQueryBuilder::lstFields_doubleClicked );
46  connect( lstValues, &QListView::doubleClicked, this, &QgsQueryBuilder::lstValues_doubleClicked );
47  connect( btnLessEqual, &QPushButton::clicked, this, &QgsQueryBuilder::btnLessEqual_clicked );
48  connect( btnGreaterEqual, &QPushButton::clicked, this, &QgsQueryBuilder::btnGreaterEqual_clicked );
49  connect( btnNotEqual, &QPushButton::clicked, this, &QgsQueryBuilder::btnNotEqual_clicked );
50  connect( btnAnd, &QPushButton::clicked, this, &QgsQueryBuilder::btnAnd_clicked );
51  connect( btnNot, &QPushButton::clicked, this, &QgsQueryBuilder::btnNot_clicked );
52  connect( btnOr, &QPushButton::clicked, this, &QgsQueryBuilder::btnOr_clicked );
53  connect( btnGetAllValues, &QPushButton::clicked, this, &QgsQueryBuilder::btnGetAllValues_clicked );
54  connect( btnSampleValues, &QPushButton::clicked, this, &QgsQueryBuilder::btnSampleValues_clicked );
55  connect( buttonBox, &QDialogButtonBox::helpRequested, this, &QgsQueryBuilder::showHelp );
56 
57  QgsSettings settings;
58  restoreGeometry( settings.value( QStringLiteral( "Windows/QueryBuilder/geometry" ) ).toByteArray() );
59 
60  QPushButton *pbn = new QPushButton( tr( "&Test" ) );
61  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
62  connect( pbn, &QAbstractButton::clicked, this, &QgsQueryBuilder::test );
63 
64  pbn = new QPushButton( tr( "&Clear" ) );
65  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
66  connect( pbn, &QAbstractButton::clicked, this, &QgsQueryBuilder::clear );
67 
68  setupGuiViews();
69 
70  mOrigSubsetString = layer->subsetString();
71 
72  mUseUnfilteredLayer->setDisabled( mLayer->subsetString().isEmpty() );
73 
74  lblDataUri->setText( tr( "Set provider filter on %1" ).arg( layer->name() ) );
75  txtSQL->setText( mOrigSubsetString );
76 
77  mFilterLineEdit->setShowSearchIcon( true );
78  mFilterLineEdit->setPlaceholderText( tr( "Search" ) );
79  connect( mFilterLineEdit, &QgsFilterLineEdit::textChanged, this, &QgsQueryBuilder::onTextChanged );
80 
81  populateFields();
82 }
83 
85 {
86  QgsSettings settings;
87  settings.setValue( QStringLiteral( "Windows/QueryBuilder/geometry" ), saveGeometry() );
88 }
89 
90 void QgsQueryBuilder::showEvent( QShowEvent *event )
91 {
92  txtSQL->setFocus();
93  QDialog::showEvent( event );
94 }
95 
96 void QgsQueryBuilder::populateFields()
97 {
98  const QgsFields &fields = mLayer->fields();
99  for ( int idx = 0; idx < fields.count(); ++idx )
100  {
101  if ( fields.fieldOrigin( idx ) != QgsFields::OriginProvider )
102  {
103  // only consider native fields
104  continue;
105  }
106  QStandardItem *myItem = new QStandardItem( fields.at( idx ).name() );
107  myItem->setData( idx );
108  myItem->setEditable( false );
109  mModelFields->insertRow( mModelFields->rowCount(), myItem );
110  }
111 
112  // All fields get ... setup
113  setupLstFieldsModel();
114 }
115 
116 void QgsQueryBuilder::setupLstFieldsModel()
117 {
118  lstFields->setModel( mModelFields );
119 }
120 
121 void QgsQueryBuilder::setupGuiViews()
122 {
123  //Initialize the models
124  mModelFields = new QStandardItemModel();
125  mModelValues = new QStandardItemModel();
126  mProxyValues = new QSortFilterProxyModel();
127  mProxyValues->setSourceModel( mModelValues );
128  // Modes
129  lstFields->setViewMode( QListView::ListMode );
130  lstValues->setViewMode( QListView::ListMode );
131  lstFields->setSelectionBehavior( QAbstractItemView::SelectRows );
132  lstValues->setSelectionBehavior( QAbstractItemView::SelectRows );
133  // Performance tip since Qt 4.1
134  lstFields->setUniformItemSizes( true );
135  lstValues->setUniformItemSizes( true );
136  // Colored rows
137  lstFields->setAlternatingRowColors( true );
138  lstValues->setAlternatingRowColors( true );
139  lstValues->setModel( mProxyValues );
140 }
141 
142 void QgsQueryBuilder::fillValues( int idx, int limit )
143 {
144  // clear the model
145  mModelValues->clear();
146 
147  // determine the field type
148  QList<QVariant> values = mLayer->uniqueValues( idx, limit ).toList();
149  std::sort( values.begin(), values.end() );
150 
151  QString nullValue = QgsApplication::nullRepresentation();
152 
153  QgsDebugMsg( QString( "nullValue: %1" ).arg( nullValue ) );
154 
155  Q_FOREACH ( const QVariant &var, values )
156  {
157  QString value;
158  if ( var.isNull() )
159  value = nullValue;
160  else if ( var.type() == QVariant::Date && mLayer->providerType() == QLatin1String( "ogr" ) && mLayer->storageType() == QLatin1String( "ESRI Shapefile" ) )
161  value = var.toDate().toString( QStringLiteral( "yyyy/MM/dd" ) );
162  else
163  value = var.toString();
164 
165  QStandardItem *myItem = new QStandardItem( value );
166  myItem->setEditable( false );
167  myItem->setData( var, Qt::UserRole + 1 );
168  mModelValues->insertRow( mModelValues->rowCount(), myItem );
169  QgsDebugMsg( QString( "Value is null: %1\nvalue: %2" ).arg( var.isNull() ).arg( var.isNull() ? nullValue : var.toString() ) );
170  }
171 }
172 
173 void QgsQueryBuilder::btnSampleValues_clicked()
174 {
175  lstValues->setCursor( Qt::WaitCursor );
176 
177  QString prevSubsetString = mLayer->subsetString();
178  if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
179  {
180  mLayer->setSubsetString( QLatin1String( "" ) );
181  }
182 
183  //Clear and fill the mModelValues
184  fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), 25 );
185 
186  if ( prevSubsetString != mLayer->subsetString() )
187  {
188  mLayer->setSubsetString( prevSubsetString );
189  }
190 
191  lstValues->setCursor( Qt::ArrowCursor );
192 }
193 
194 void QgsQueryBuilder::btnGetAllValues_clicked()
195 {
196  lstValues->setCursor( Qt::WaitCursor );
197 
198  QString prevSubsetString = mLayer->subsetString();
199  if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
200  {
201  mLayer->setSubsetString( QLatin1String( "" ) );
202  }
203 
204  //Clear and fill the mModelValues
205  fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), -1 );
206 
207  if ( prevSubsetString != mLayer->subsetString() )
208  {
209  mLayer->setSubsetString( prevSubsetString );
210  }
211 
212  lstValues->setCursor( Qt::ArrowCursor );
213 }
214 
216 {
217  // test the sql statement to see if it works
218  // by counting the number of records that would be
219  // returned
220 
221  if ( mLayer->setSubsetString( txtSQL->text() ) )
222  {
223  mUseUnfilteredLayer->setDisabled( mLayer->subsetString().isEmpty() );
224 
225  QMessageBox::information( this,
226  tr( "Query Result" ),
227  tr( "The where clause returned %n row(s).", "returned test rows", mLayer->featureCount() ) );
228  }
229  else if ( mLayer->dataProvider()->hasErrors() )
230  {
231  QMessageBox::warning( this,
232  tr( "Query Result" ),
233  tr( "An error occurred when executing the query." )
234  + tr( "\nThe data provider said:\n%1" ).arg( mLayer->dataProvider()->errors().join( QStringLiteral( "\n" ) ) ) );
235  mLayer->dataProvider()->clearErrors();
236  }
237  else
238  {
239  QMessageBox::warning( this,
240  tr( "Query Result" ),
241  tr( "An error occurred when executing the query." ) );
242  }
243 }
244 
246 {
247  if ( txtSQL->text() != mOrigSubsetString )
248  {
249  if ( !mLayer->setSubsetString( txtSQL->text() ) )
250  {
251  //error in query - show the problem
252  if ( mLayer->dataProvider()->hasErrors() )
253  {
254  QMessageBox::warning( this,
255  tr( "Query Result" ),
256  tr( "An error occurred when executing the query." )
257  + tr( "\nThe data provider said:\n%1" ).arg( mLayer->dataProvider()->errors().join( QStringLiteral( "\n" ) ) ) );
258  mLayer->dataProvider()->clearErrors();
259  }
260  else
261  {
262  QMessageBox::warning( this, tr( "Query Result" ), tr( "Error in query. The subset string could not be set." ) );
263  }
264 
265  return;
266  }
267  }
268 
269  QDialog::accept();
270 }
271 
273 {
274  if ( mLayer->subsetString() != mOrigSubsetString )
275  mLayer->setSubsetString( mOrigSubsetString );
276 
277  QDialog::reject();
278 }
279 
280 void QgsQueryBuilder::btnEqual_clicked()
281 {
282  txtSQL->insertText( QStringLiteral( " = " ) );
283  txtSQL->setFocus();
284 }
285 
286 void QgsQueryBuilder::btnLessThan_clicked()
287 {
288  txtSQL->insertText( QStringLiteral( " < " ) );
289  txtSQL->setFocus();
290 }
291 
292 void QgsQueryBuilder::btnGreaterThan_clicked()
293 {
294  txtSQL->insertText( QStringLiteral( " > " ) );
295  txtSQL->setFocus();
296 }
297 
298 void QgsQueryBuilder::btnPct_clicked()
299 {
300  txtSQL->insertText( QStringLiteral( "%" ) );
301  txtSQL->setFocus();
302 }
303 
304 void QgsQueryBuilder::btnIn_clicked()
305 {
306  txtSQL->insertText( QStringLiteral( " IN " ) );
307  txtSQL->setFocus();
308 }
309 
310 void QgsQueryBuilder::btnNotIn_clicked()
311 {
312  txtSQL->insertText( QStringLiteral( " NOT IN " ) );
313  txtSQL->setFocus();
314 }
315 
316 void QgsQueryBuilder::btnLike_clicked()
317 {
318  txtSQL->insertText( QStringLiteral( " LIKE " ) );
319  txtSQL->setFocus();
320 }
321 
323 {
324  return txtSQL->text();
325 }
326 
327 void QgsQueryBuilder::setSql( const QString &sqlStatement )
328 {
329  txtSQL->setText( sqlStatement );
330 }
331 
332 void QgsQueryBuilder::lstFields_clicked( const QModelIndex &index )
333 {
334  if ( mPreviousFieldRow != index.row() )
335  {
336  mPreviousFieldRow = index.row();
337 
338  btnSampleValues->setEnabled( true );
339  btnGetAllValues->setEnabled( true );
340 
341  mModelValues->clear();
342  mFilterLineEdit->clear();
343  }
344 }
345 
346 void QgsQueryBuilder::lstFields_doubleClicked( const QModelIndex &index )
347 {
348  txtSQL->insertText( '\"' + mLayer->fields().at( mModelFields->data( index, Qt::UserRole + 1 ).toInt() ).name() + '\"' );
349  txtSQL->setFocus();
350 }
351 
352 void QgsQueryBuilder::lstValues_doubleClicked( const QModelIndex &index )
353 {
354  QVariant value = index.data( Qt::DisplayRole );
355  if ( value.isNull() )
356  txtSQL->insertText( QStringLiteral( "NULL" ) );
357  else if ( value.type() == QVariant::Date && mLayer->providerType() == QLatin1String( "ogr" ) && mLayer->storageType() == QLatin1String( "ESRI Shapefile" ) )
358  txtSQL->insertText( '\'' + value.toDate().toString( QStringLiteral( "yyyy/MM/dd" ) ) + '\'' );
359  else if ( value.type() == QVariant::Int || value.type() == QVariant::Double || value.type() == QVariant::LongLong )
360  txtSQL->insertText( value.toString() );
361  else
362  txtSQL->insertText( '\'' + value.toString().replace( '\'', QLatin1String( "''" ) ) + '\'' );
363 
364  txtSQL->setFocus();
365 }
366 
367 void QgsQueryBuilder::btnLessEqual_clicked()
368 {
369  txtSQL->insertText( QStringLiteral( " <= " ) );
370  txtSQL->setFocus();
371 }
372 
373 void QgsQueryBuilder::btnGreaterEqual_clicked()
374 {
375  txtSQL->insertText( QStringLiteral( " >= " ) );
376  txtSQL->setFocus();
377 }
378 
379 void QgsQueryBuilder::btnNotEqual_clicked()
380 {
381  txtSQL->insertText( QStringLiteral( " != " ) );
382  txtSQL->setFocus();
383 }
384 
385 void QgsQueryBuilder::btnAnd_clicked()
386 {
387  txtSQL->insertText( QStringLiteral( " AND " ) );
388  txtSQL->setFocus();
389 }
390 
391 void QgsQueryBuilder::btnNot_clicked()
392 {
393  txtSQL->insertText( QStringLiteral( " NOT " ) );
394  txtSQL->setFocus();
395 }
396 
397 void QgsQueryBuilder::btnOr_clicked()
398 {
399  txtSQL->insertText( QStringLiteral( " OR " ) );
400  txtSQL->setFocus();
401 }
402 
403 void QgsQueryBuilder::onTextChanged( const QString &text )
404 {
405  mProxyValues->setFilterCaseSensitivity( Qt::CaseInsensitive );
406  mProxyValues->setFilterWildcard( text );
407 }
408 
410 {
411  txtSQL->clear();
412  mLayer->setSubsetString( QLatin1String( "" ) );
413  mUseUnfilteredLayer->setDisabled( true );
414 }
415 
416 void QgsQueryBuilder::btnILike_clicked()
417 {
418  txtSQL->insertText( QStringLiteral( " ILIKE " ) );
419  txtSQL->setFocus();
420 }
421 
423 {
424  lblDataUri->setText( uri );
425 }
426 
427 void QgsQueryBuilder::showHelp()
428 {
429  QgsHelp::openHelp( QStringLiteral( "working_with_vector/vector_properties.html#query-builder" ) );
430 }
void accept() override
FieldOrigin fieldOrigin(int fieldIdx) const
Gets field&#39;s origin (value from an enumeration)
Definition: qgsfields.cpp:171
QString name
Definition: qgsfield.h:57
void test()
Test the constructed sql statement to see if the vector layer data provider likes it...
This class is a composition of two QSettings instances:
Definition: qgssettings.h:58
QString storageType() const
Returns the permanent storage type for this layer as a friendly name.
QVariant value(const QString &key, const QVariant &defaultValue=QVariant(), Section section=NoSection) const
Returns the value for setting key.
#define QgsDebugMsg(str)
Definition: qgslogger.h:38
Container of fields for a vector layer.
Definition: qgsfields.h:42
QSet< QVariant > uniqueValues(int fieldIndex, int limit=-1) const override
Calculates a list of unique values contained within an attribute in the layer.
Field comes from the underlying data provider of the vector layer (originIndex = index in provider&#39;s ...
Definition: qgsfields.h:49
int count() const
Returns number of items.
Definition: qgsfields.cpp:115
~QgsQueryBuilder() override
QgsField at(int i) const
Gets field at particular index (must be in range 0..N-1)
Definition: qgsfields.cpp:145
void saveGeometry(QWidget *widget, const QString &keyName)
Save the wigget geometry into settings.
bool restoreGeometry(QWidget *widget, const QString &keyName)
Restore the wigget geometry from settings.
void setSql(const QString &sqlStatement)
QgsFields fields() const override
Returns the list of fields of this layer.
long featureCount(const QString &legendKey) const
Number of features rendered with specified legend key.
QStringList errors() const
Gets recorded errors.
QString subsetString
static QString nullRepresentation()
This string is used to represent the value NULL throughout QGIS.
void clearErrors()
Clear recorded errors.
QgsQueryBuilder(QgsVectorLayer *layer, QWidget *parent=nullptr, Qt::WindowFlags fl=QgsGuiUtils::ModalDialogFlags)
This constructor is used when the query builder is called from the vector layer properties dialog...
void setValue(const QString &key, const QVariant &value, QgsSettings::Section section=QgsSettings::NoSection)
Sets the value of setting key to value.
void setDatasourceDescription(const QString &uri)
bool hasErrors() const
Provider has errors to report.
QgsVectorDataProvider * dataProvider() override
Returns the layer&#39;s data provider.
static void openHelp(const QString &key)
Opens help topic for the given help key using default system web browser.
Definition: qgshelp.cpp:35
virtual bool setSubsetString(const QString &subset)
Set the string (typically sql) used to define a subset of the layer.
void reject() override
QString name
Definition: qgsmaplayer.h:65
QString providerType() const
Returns the provider type for this layer.
Represents a vector layer which manages a vector based data sets.
void showEvent(QShowEvent *event) override