QGIS API Documentation  2.99.0-Master (b058df7)
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  QSet<QVariant> values = mLayer->uniqueValues( idx, limit );
141 
142  QString nullValue = QgsApplication::nullRepresentation();
143 
144  QgsDebugMsg( QString( "nullValue: %1" ).arg( nullValue ) );
145 
146  Q_FOREACH ( const QVariant &var, values )
147  {
148  QString value;
149  if ( var.isNull() )
150  value = nullValue;
151  else if ( var.type() == QVariant::Date && mLayer->providerType() == QLatin1String( "ogr" ) && mLayer->storageType() == QLatin1String( "ESRI Shapefile" ) )
152  value = var.toDate().toString( QStringLiteral( "yyyy/MM/dd" ) );
153  else
154  value = var.toString();
155 
156  QStandardItem *myItem = new QStandardItem( value );
157  myItem->setEditable( false );
158  myItem->setData( var, Qt::UserRole + 1 );
159  mModelValues->insertRow( mModelValues->rowCount(), myItem );
160  QgsDebugMsg( QString( "Value is null: %1\nvalue: %2" ).arg( var.isNull() ).arg( var.isNull() ? nullValue : var.toString() ) );
161  }
162 }
163 
164 void QgsQueryBuilder::btnSampleValues_clicked()
165 {
166  lstValues->setCursor( Qt::WaitCursor );
167 
168  QString prevSubsetString = mLayer->subsetString();
169  if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
170  {
171  mLayer->setSubsetString( QLatin1String( "" ) );
172  }
173 
174  //delete connection mModelValues and lstValues
175  QStandardItemModel *tmp = new QStandardItemModel();
176  lstValues->setModel( tmp );
177  //Clear and fill the mModelValues
178  fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), 25 );
179  lstValues->setModel( mModelValues );
180  //delete the tmp
181  delete tmp;
182 
183  if ( prevSubsetString != mLayer->subsetString() )
184  {
185  mLayer->setSubsetString( prevSubsetString );
186  }
187 
188  lstValues->setCursor( Qt::ArrowCursor );
189 }
190 
191 void QgsQueryBuilder::btnGetAllValues_clicked()
192 {
193  lstValues->setCursor( Qt::WaitCursor );
194 
195  QString prevSubsetString = mLayer->subsetString();
196  if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
197  {
198  mLayer->setSubsetString( QLatin1String( "" ) );
199  }
200 
201  //delete connection mModelValues and lstValues
202  QStandardItemModel *tmp = new QStandardItemModel();
203  lstValues->setModel( tmp );
204  //Clear and fill the mModelValues
205  fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), -1 );
206  lstValues->setModel( mModelValues );
207  //delete the tmp
208  delete tmp;
209 
210  if ( prevSubsetString != mLayer->subsetString() )
211  {
212  mLayer->setSubsetString( prevSubsetString );
213  }
214 
215  lstValues->setCursor( Qt::ArrowCursor );
216 }
217 
219 {
220  // test the sql statement to see if it works
221  // by counting the number of records that would be
222  // returned
223 
224  if ( mLayer->setSubsetString( txtSQL->text() ) )
225  {
226  mUseUnfilteredLayer->setDisabled( mLayer->subsetString().isEmpty() );
227 
228  QMessageBox::information( this,
229  tr( "Query Result" ),
230  tr( "The where clause returned %n row(s).", "returned test rows", mLayer->featureCount() ) );
231  }
232  else if ( mLayer->dataProvider()->hasErrors() )
233  {
234  QMessageBox::warning( this,
235  tr( "Query Failed" ),
236  tr( "An error occurred when executing the query." )
237  + tr( "\nThe data provider said:\n%1" ).arg( mLayer->dataProvider()->errors().join( QStringLiteral( "\n" ) ) ) );
238  mLayer->dataProvider()->clearErrors();
239  }
240  else
241  {
242  QMessageBox::warning( this,
243  tr( "Query Failed" ),
244  tr( "An error occurred when executing the query." ) );
245  }
246 }
247 
249 {
250  if ( !mLayer->setSubsetString( txtSQL->text() ) )
251  {
252  //error in query - show the problem
253  if ( mLayer->dataProvider()->hasErrors() )
254  {
255  QMessageBox::warning( this,
256  tr( "Query Failed" ),
257  tr( "An error occurred when executing the query." )
258  + tr( "\nThe data provider said:\n%1" ).arg( mLayer->dataProvider()->errors().join( QStringLiteral( "\n" ) ) ) );
259  mLayer->dataProvider()->clearErrors();
260  }
261  else
262  {
263  QMessageBox::warning( this, tr( "Error in Query" ), tr( "The subset string could not be set" ) );
264  }
265 
266  return;
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  }
343 }
344 
345 void QgsQueryBuilder::lstFields_doubleClicked( const QModelIndex &index )
346 {
347  txtSQL->insertText( '\"' + mLayer->fields().at( mModelFields->data( index, Qt::UserRole + 1 ).toInt() ).name() + '\"' );
348  txtSQL->setFocus();
349 }
350 
351 void QgsQueryBuilder::lstValues_doubleClicked( const QModelIndex &index )
352 {
353  QVariant value = mModelValues->data( index, Qt::UserRole + 1 );
354  if ( value.isNull() )
355  txtSQL->insertText( QStringLiteral( "NULL" ) );
356  else if ( value.type() == QVariant::Date && mLayer->providerType() == QLatin1String( "ogr" ) && mLayer->storageType() == QLatin1String( "ESRI Shapefile" ) )
357  txtSQL->insertText( '\'' + value.toDate().toString( QStringLiteral( "yyyy/MM/dd" ) ) + '\'' );
358  else if ( value.type() == QVariant::Int || value.type() == QVariant::Double || value.type() == QVariant::LongLong )
359  txtSQL->insertText( value.toString() );
360  else
361  txtSQL->insertText( '\'' + value.toString().replace( '\'', QLatin1String( "''" ) ) + '\'' );
362 
363  txtSQL->setFocus();
364 }
365 
366 void QgsQueryBuilder::btnLessEqual_clicked()
367 {
368  txtSQL->insertText( QStringLiteral( " <= " ) );
369  txtSQL->setFocus();
370 }
371 
372 void QgsQueryBuilder::btnGreaterEqual_clicked()
373 {
374  txtSQL->insertText( QStringLiteral( " >= " ) );
375  txtSQL->setFocus();
376 }
377 
378 void QgsQueryBuilder::btnNotEqual_clicked()
379 {
380  txtSQL->insertText( QStringLiteral( " != " ) );
381  txtSQL->setFocus();
382 }
383 
384 void QgsQueryBuilder::btnAnd_clicked()
385 {
386  txtSQL->insertText( QStringLiteral( " AND " ) );
387  txtSQL->setFocus();
388 }
389 
390 void QgsQueryBuilder::btnNot_clicked()
391 {
392  txtSQL->insertText( QStringLiteral( " NOT " ) );
393  txtSQL->setFocus();
394 }
395 
396 void QgsQueryBuilder::btnOr_clicked()
397 {
398  txtSQL->insertText( QStringLiteral( " OR " ) );
399  txtSQL->setFocus();
400 }
401 
403 {
404  txtSQL->clear();
405  mLayer->setSubsetString( QLatin1String( "" ) );
406  mUseUnfilteredLayer->setDisabled( true );
407 }
408 
409 void QgsQueryBuilder::btnILike_clicked()
410 {
411  txtSQL->insertText( QStringLiteral( " ILIKE " ) );
412  txtSQL->setFocus();
413 }
414 
416 {
417  lblDataUri->setText( uri );
418 }
419 
420 void QgsQueryBuilder::showHelp()
421 {
422  QgsHelp::openHelp( QStringLiteral( "working_with_vector/vector_properties.html#query-builder" ) );
423 }
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:56
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:55
QString storageType() const
Returns the permanent storage type for this layer as a friendly name.
#define QgsDebugMsg(str)
Definition: qgslogger.h:37
QgsQueryBuilder(QgsVectorLayer *layer, QWidget *parent=0, Qt::WindowFlags fl=QgsGuiUtils::ModalDialogFlags)
This constructor is used when the query builder is called from the vector layer properties dialog...
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
QgsField at(int i) const
Get field at particular index (must be in range 0..N-1)
Definition: qgsfields.cpp:145
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.
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:34
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