QGIS API Documentation  2.99.0-Master (6c64c5a)
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 "qgssettings.h"
18 #include "qgsvectorlayer.h"
19 #include "qgsvectordataprovider.h"
20 
21 #include <QListView>
22 #include <QMessageBox>
23 #include <QRegExp>
24 #include <QPushButton>
25 
26 // constructor used when the query builder must make its own
27 // connection to the database
29  QWidget *parent, Qt::WindowFlags fl )
30  : QDialog( parent, fl )
31  , mPreviousFieldRow( -1 )
32  , mLayer( layer )
33 {
34  setupUi( this );
35  connect( btnEqual, &QPushButton::clicked, this, &QgsQueryBuilder::btnEqual_clicked );
36  connect( btnLessThan, &QPushButton::clicked, this, &QgsQueryBuilder::btnLessThan_clicked );
37  connect( btnGreaterThan, &QPushButton::clicked, this, &QgsQueryBuilder::btnGreaterThan_clicked );
38  connect( btnPct, &QPushButton::clicked, this, &QgsQueryBuilder::btnPct_clicked );
39  connect( btnIn, &QPushButton::clicked, this, &QgsQueryBuilder::btnIn_clicked );
40  connect( btnNotIn, &QPushButton::clicked, this, &QgsQueryBuilder::btnNotIn_clicked );
41  connect( btnLike, &QPushButton::clicked, this, &QgsQueryBuilder::btnLike_clicked );
42  connect( btnILike, &QPushButton::clicked, this, &QgsQueryBuilder::btnILike_clicked );
43  connect( lstFields, &QListView::clicked, this, &QgsQueryBuilder::lstFields_clicked );
44  connect( lstFields, &QListView::doubleClicked, this, &QgsQueryBuilder::lstFields_doubleClicked );
45  connect( lstValues, &QListView::doubleClicked, this, &QgsQueryBuilder::lstValues_doubleClicked );
46  connect( btnLessEqual, &QPushButton::clicked, this, &QgsQueryBuilder::btnLessEqual_clicked );
47  connect( btnGreaterEqual, &QPushButton::clicked, this, &QgsQueryBuilder::btnGreaterEqual_clicked );
48  connect( btnNotEqual, &QPushButton::clicked, this, &QgsQueryBuilder::btnNotEqual_clicked );
49  connect( btnAnd, &QPushButton::clicked, this, &QgsQueryBuilder::btnAnd_clicked );
50  connect( btnNot, &QPushButton::clicked, this, &QgsQueryBuilder::btnNot_clicked );
51  connect( btnOr, &QPushButton::clicked, this, &QgsQueryBuilder::btnOr_clicked );
52  connect( btnGetAllValues, &QPushButton::clicked, this, &QgsQueryBuilder::btnGetAllValues_clicked );
53  connect( btnSampleValues, &QPushButton::clicked, this, &QgsQueryBuilder::btnSampleValues_clicked );
54  connect( buttonBox, &QDialogButtonBox::helpRequested, this, &QgsQueryBuilder::showHelp );
55 
56  QgsSettings settings;
57  restoreGeometry( settings.value( QStringLiteral( "Windows/QueryBuilder/geometry" ) ).toByteArray() );
58 
59  QPushButton *pbn = new QPushButton( tr( "&Test" ) );
60  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
61  connect( pbn, &QAbstractButton::clicked, this, &QgsQueryBuilder::test );
62 
63  pbn = new QPushButton( tr( "&Clear" ) );
64  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
65  connect( pbn, &QAbstractButton::clicked, this, &QgsQueryBuilder::clear );
66 
67  setupGuiViews();
68 
69  mOrigSubsetString = layer->subsetString();
70 
71  mUseUnfilteredLayer->setDisabled( mLayer->subsetString().isEmpty() );
72 
73  lblDataUri->setText( tr( "Set provider filter on %1" ).arg( layer->name() ) );
74  txtSQL->setText( mOrigSubsetString );
75 
76  populateFields();
77 }
78 
80 {
81  QgsSettings settings;
82  settings.setValue( QStringLiteral( "Windows/QueryBuilder/geometry" ), saveGeometry() );
83 }
84 
85 void QgsQueryBuilder::showEvent( QShowEvent *event )
86 {
87  txtSQL->setFocus();
88  QDialog::showEvent( event );
89 }
90 
91 void QgsQueryBuilder::populateFields()
92 {
93  const QgsFields &fields = mLayer->fields();
94  for ( int idx = 0; idx < fields.count(); ++idx )
95  {
96  if ( fields.fieldOrigin( idx ) != QgsFields::OriginProvider )
97  {
98  // only consider native fields
99  continue;
100  }
101  QStandardItem *myItem = new QStandardItem( fields.at( idx ).name() );
102  myItem->setData( idx );
103  myItem->setEditable( false );
104  mModelFields->insertRow( mModelFields->rowCount(), myItem );
105  }
106 
107  // All fields get ... setup
108  setupLstFieldsModel();
109 }
110 
111 void QgsQueryBuilder::setupLstFieldsModel()
112 {
113  lstFields->setModel( mModelFields );
114 }
115 
116 void QgsQueryBuilder::setupGuiViews()
117 {
118  //Initialize the models
119  mModelFields = new QStandardItemModel();
120  mModelValues = new QStandardItemModel();
121  // Modes
122  lstFields->setViewMode( QListView::ListMode );
123  lstValues->setViewMode( QListView::ListMode );
124  lstFields->setSelectionBehavior( QAbstractItemView::SelectRows );
125  lstValues->setSelectionBehavior( QAbstractItemView::SelectRows );
126  // Performance tip since Qt 4.1
127  lstFields->setUniformItemSizes( true );
128  lstValues->setUniformItemSizes( true );
129  // Colored rows
130  lstFields->setAlternatingRowColors( true );
131  lstValues->setAlternatingRowColors( true );
132 }
133 
134 void QgsQueryBuilder::fillValues( int idx, int limit )
135 {
136  // clear the model
137  mModelValues->clear();
138 
139  // determine the field type
140  QList<QVariant> values = mLayer->uniqueValues( idx, limit ).toList();
141  std::sort( values.begin(), values.end() );
142 
143  QString nullValue = QgsApplication::nullRepresentation();
144 
145  QgsDebugMsg( QString( "nullValue: %1" ).arg( nullValue ) );
146 
147  Q_FOREACH ( const QVariant &var, values )
148  {
149  QString value;
150  if ( var.isNull() )
151  value = nullValue;
152  else if ( var.type() == QVariant::Date && mLayer->providerType() == QLatin1String( "ogr" ) && mLayer->storageType() == QLatin1String( "ESRI Shapefile" ) )
153  value = var.toDate().toString( QStringLiteral( "yyyy/MM/dd" ) );
154  else
155  value = var.toString();
156 
157  QStandardItem *myItem = new QStandardItem( value );
158  myItem->setEditable( false );
159  myItem->setData( var, Qt::UserRole + 1 );
160  mModelValues->insertRow( mModelValues->rowCount(), myItem );
161  QgsDebugMsg( QString( "Value is null: %1\nvalue: %2" ).arg( var.isNull() ).arg( var.isNull() ? nullValue : var.toString() ) );
162  }
163 }
164 
165 void QgsQueryBuilder::btnSampleValues_clicked()
166 {
167  lstValues->setCursor( Qt::WaitCursor );
168 
169  QString prevSubsetString = mLayer->subsetString();
170  if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
171  {
172  mLayer->setSubsetString( QLatin1String( "" ) );
173  }
174 
175  //delete connection mModelValues and lstValues
176  QStandardItemModel *tmp = new QStandardItemModel();
177  lstValues->setModel( tmp );
178  //Clear and fill the mModelValues
179  fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), 25 );
180  lstValues->setModel( mModelValues );
181  //delete the tmp
182  delete tmp;
183 
184  if ( prevSubsetString != mLayer->subsetString() )
185  {
186  mLayer->setSubsetString( prevSubsetString );
187  }
188 
189  lstValues->setCursor( Qt::ArrowCursor );
190 }
191 
192 void QgsQueryBuilder::btnGetAllValues_clicked()
193 {
194  lstValues->setCursor( Qt::WaitCursor );
195 
196  QString prevSubsetString = mLayer->subsetString();
197  if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
198  {
199  mLayer->setSubsetString( QLatin1String( "" ) );
200  }
201 
202  //delete connection mModelValues and lstValues
203  QStandardItemModel *tmp = new QStandardItemModel();
204  lstValues->setModel( tmp );
205  //Clear and fill the mModelValues
206  fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), -1 );
207  lstValues->setModel( mModelValues );
208  //delete the tmp
209  delete tmp;
210 
211  if ( prevSubsetString != mLayer->subsetString() )
212  {
213  mLayer->setSubsetString( prevSubsetString );
214  }
215 
216  lstValues->setCursor( Qt::ArrowCursor );
217 }
218 
220 {
221  // test the sql statement to see if it works
222  // by counting the number of records that would be
223  // returned
224 
225  if ( mLayer->setSubsetString( txtSQL->text() ) )
226  {
227  mUseUnfilteredLayer->setDisabled( mLayer->subsetString().isEmpty() );
228 
229  QMessageBox::information( this,
230  tr( "Query Result" ),
231  tr( "The where clause returned %n row(s).", "returned test rows", mLayer->featureCount() ) );
232  }
233  else if ( mLayer->dataProvider()->hasErrors() )
234  {
235  QMessageBox::warning( this,
236  tr( "Query Failed" ),
237  tr( "An error occurred when executing the query." )
238  + tr( "\nThe data provider said:\n%1" ).arg( mLayer->dataProvider()->errors().join( QStringLiteral( "\n" ) ) ) );
239  mLayer->dataProvider()->clearErrors();
240  }
241  else
242  {
243  QMessageBox::warning( this,
244  tr( "Query Failed" ),
245  tr( "An error occurred when executing the query." ) );
246  }
247 }
248 
250 {
251  if ( !mLayer->setSubsetString( txtSQL->text() ) )
252  {
253  //error in query - show the problem
254  if ( mLayer->dataProvider()->hasErrors() )
255  {
256  QMessageBox::warning( this,
257  tr( "Query Failed" ),
258  tr( "An error occurred when executing the query." )
259  + tr( "\nThe data provider said:\n%1" ).arg( mLayer->dataProvider()->errors().join( QStringLiteral( "\n" ) ) ) );
260  mLayer->dataProvider()->clearErrors();
261  }
262  else
263  {
264  QMessageBox::warning( this, tr( "Error in Query" ), tr( "The subset string could not be set" ) );
265  }
266 
267  return;
268  }
269 
270  QDialog::accept();
271 }
272 
274 {
275  if ( mLayer->subsetString() != mOrigSubsetString )
276  mLayer->setSubsetString( mOrigSubsetString );
277 
278  QDialog::reject();
279 }
280 
281 void QgsQueryBuilder::btnEqual_clicked()
282 {
283  txtSQL->insertText( QStringLiteral( " = " ) );
284  txtSQL->setFocus();
285 }
286 
287 void QgsQueryBuilder::btnLessThan_clicked()
288 {
289  txtSQL->insertText( QStringLiteral( " < " ) );
290  txtSQL->setFocus();
291 }
292 
293 void QgsQueryBuilder::btnGreaterThan_clicked()
294 {
295  txtSQL->insertText( QStringLiteral( " > " ) );
296  txtSQL->setFocus();
297 }
298 
299 void QgsQueryBuilder::btnPct_clicked()
300 {
301  txtSQL->insertText( QStringLiteral( "%" ) );
302  txtSQL->setFocus();
303 }
304 
305 void QgsQueryBuilder::btnIn_clicked()
306 {
307  txtSQL->insertText( QStringLiteral( " IN " ) );
308  txtSQL->setFocus();
309 }
310 
311 void QgsQueryBuilder::btnNotIn_clicked()
312 {
313  txtSQL->insertText( QStringLiteral( " NOT IN " ) );
314  txtSQL->setFocus();
315 }
316 
317 void QgsQueryBuilder::btnLike_clicked()
318 {
319  txtSQL->insertText( QStringLiteral( " LIKE " ) );
320  txtSQL->setFocus();
321 }
322 
324 {
325  return txtSQL->text();
326 }
327 
328 void QgsQueryBuilder::setSql( const QString &sqlStatement )
329 {
330  txtSQL->setText( sqlStatement );
331 }
332 
333 void QgsQueryBuilder::lstFields_clicked( const QModelIndex &index )
334 {
335  if ( mPreviousFieldRow != index.row() )
336  {
337  mPreviousFieldRow = index.row();
338 
339  btnSampleValues->setEnabled( true );
340  btnGetAllValues->setEnabled( true );
341 
342  mModelValues->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 = mModelValues->data( index, Qt::UserRole + 1 );
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 
404 {
405  txtSQL->clear();
406  mLayer->setSubsetString( QLatin1String( "" ) );
407  mUseUnfilteredLayer->setDisabled( true );
408 }
409 
410 void QgsQueryBuilder::btnILike_clicked()
411 {
412  txtSQL->insertText( QStringLiteral( " ILIKE " ) );
413  txtSQL->setFocus();
414 }
415 
417 {
418  lblDataUri->setText( uri );
419 }
420 
421 void QgsQueryBuilder::showHelp()
422 {
423  QgsHelp::openHelp( QStringLiteral( "working_with_vector/vector_properties.html#query-builder" ) );
424 }
void accept() override
FieldOrigin fieldOrigin(int fieldIdx) const
Get 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:57
QString storageType() const
Returns the permanent storage type for this layer as a friendly name.
#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
Return number of items.
Definition: qgsfields.cpp:115
~QgsQueryBuilder() override
QgsField at(int i) const
Get 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 setValue(const QString &key, const QVariant &value, const QgsSettings::Section section=QgsSettings::NoSection)
Sets the value of setting key to value.
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
Get recorded errors.
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...
QVariant value(const QString &key, const QVariant &defaultValue=QVariant(), const Section section=NoSection) const
Returns the value for setting key.
void setDatasourceDescription(const QString &uri)
bool hasErrors() const
Provider has errors to report.
virtual QString subsetString() const
Get the string (typically sql) used to define a subset of the layer.
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:60
QString providerType() const
Return the provider type for this layer.
Represents a vector layer which manages a vector based data sets.
void showEvent(QShowEvent *event) override