QGIS API Documentation  2.99.0-Master (53aba61)
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( buttonBox, &QDialogButtonBox::helpRequested, this, &QgsQueryBuilder::showHelp );
36 
37  QgsSettings settings;
38  restoreGeometry( settings.value( QStringLiteral( "Windows/QueryBuilder/geometry" ) ).toByteArray() );
39 
40  QPushButton *pbn = new QPushButton( tr( "&Test" ) );
41  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
42  connect( pbn, &QAbstractButton::clicked, this, &QgsQueryBuilder::test );
43 
44  pbn = new QPushButton( tr( "&Clear" ) );
45  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
46  connect( pbn, &QAbstractButton::clicked, this, &QgsQueryBuilder::clear );
47 
48  setupGuiViews();
49 
50  mOrigSubsetString = layer->subsetString();
51 
52  mUseUnfilteredLayer->setDisabled( mLayer->subsetString().isEmpty() );
53 
54  lblDataUri->setText( tr( "Set provider filter on %1" ).arg( layer->name() ) );
55  txtSQL->setText( mOrigSubsetString );
56 
57  populateFields();
58 }
59 
61 {
62  QgsSettings settings;
63  settings.setValue( QStringLiteral( "Windows/QueryBuilder/geometry" ), saveGeometry() );
64 }
65 
66 void QgsQueryBuilder::showEvent( QShowEvent *event )
67 {
68  txtSQL->setFocus();
69  QDialog::showEvent( event );
70 }
71 
72 void QgsQueryBuilder::populateFields()
73 {
74  const QgsFields &fields = mLayer->fields();
75  for ( int idx = 0; idx < fields.count(); ++idx )
76  {
77  if ( fields.fieldOrigin( idx ) != QgsFields::OriginProvider )
78  {
79  // only consider native fields
80  continue;
81  }
82  QStandardItem *myItem = new QStandardItem( fields.at( idx ).name() );
83  myItem->setData( idx );
84  myItem->setEditable( false );
85  mModelFields->insertRow( mModelFields->rowCount(), myItem );
86  }
87 
88  // All fields get ... setup
89  setupLstFieldsModel();
90 }
91 
92 void QgsQueryBuilder::setupLstFieldsModel()
93 {
94  lstFields->setModel( mModelFields );
95 }
96 
97 void QgsQueryBuilder::setupGuiViews()
98 {
99  //Initialize the models
100  mModelFields = new QStandardItemModel();
101  mModelValues = new QStandardItemModel();
102  // Modes
103  lstFields->setViewMode( QListView::ListMode );
104  lstValues->setViewMode( QListView::ListMode );
105  lstFields->setSelectionBehavior( QAbstractItemView::SelectRows );
106  lstValues->setSelectionBehavior( QAbstractItemView::SelectRows );
107  // Performance tip since Qt 4.1
108  lstFields->setUniformItemSizes( true );
109  lstValues->setUniformItemSizes( true );
110  // Colored rows
111  lstFields->setAlternatingRowColors( true );
112  lstValues->setAlternatingRowColors( true );
113 }
114 
115 void QgsQueryBuilder::fillValues( int idx, int limit )
116 {
117  // clear the model
118  mModelValues->clear();
119 
120  // determine the field type
121  QSet<QVariant> values = mLayer->uniqueValues( idx, limit );
122 
123  QString nullValue = QgsApplication::nullRepresentation();
124 
125  QgsDebugMsg( QString( "nullValue: %1" ).arg( nullValue ) );
126 
127  Q_FOREACH ( const QVariant &var, values )
128  {
129  QString value;
130  if ( var.isNull() )
131  value = nullValue;
132  else if ( var.type() == QVariant::Date && mLayer->providerType() == QLatin1String( "ogr" ) && mLayer->storageType() == QLatin1String( "ESRI Shapefile" ) )
133  value = var.toDate().toString( QStringLiteral( "yyyy/MM/dd" ) );
134  else
135  value = var.toString();
136 
137  QStandardItem *myItem = new QStandardItem( value );
138  myItem->setEditable( false );
139  myItem->setData( var, Qt::UserRole + 1 );
140  mModelValues->insertRow( mModelValues->rowCount(), myItem );
141  QgsDebugMsg( QString( "Value is null: %1\nvalue: %2" ).arg( var.isNull() ).arg( var.isNull() ? nullValue : var.toString() ) );
142  }
143 }
144 
146 {
147  lstValues->setCursor( Qt::WaitCursor );
148 
149  QString prevSubsetString = mLayer->subsetString();
150  if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
151  {
152  mLayer->setSubsetString( QLatin1String( "" ) );
153  }
154 
155  //delete connection mModelValues and lstValues
156  QStandardItemModel *tmp = new QStandardItemModel();
157  lstValues->setModel( tmp );
158  //Clear and fill the mModelValues
159  fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), 25 );
160  lstValues->setModel( mModelValues );
161  //delete the tmp
162  delete tmp;
163 
164  if ( prevSubsetString != mLayer->subsetString() )
165  {
166  mLayer->setSubsetString( prevSubsetString );
167  }
168 
169  lstValues->setCursor( Qt::ArrowCursor );
170 }
171 
173 {
174  lstValues->setCursor( Qt::WaitCursor );
175 
176  QString prevSubsetString = mLayer->subsetString();
177  if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
178  {
179  mLayer->setSubsetString( QLatin1String( "" ) );
180  }
181 
182  //delete connection mModelValues and lstValues
183  QStandardItemModel *tmp = new QStandardItemModel();
184  lstValues->setModel( tmp );
185  //Clear and fill the mModelValues
186  fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), -1 );
187  lstValues->setModel( mModelValues );
188  //delete the tmp
189  delete tmp;
190 
191  if ( prevSubsetString != mLayer->subsetString() )
192  {
193  mLayer->setSubsetString( prevSubsetString );
194  }
195 
196  lstValues->setCursor( Qt::ArrowCursor );
197 }
198 
200 {
201  // test the sql statement to see if it works
202  // by counting the number of records that would be
203  // returned
204 
205  if ( mLayer->setSubsetString( txtSQL->text() ) )
206  {
207  mUseUnfilteredLayer->setDisabled( mLayer->subsetString().isEmpty() );
208 
209  QMessageBox::information( this,
210  tr( "Query Result" ),
211  tr( "The where clause returned %n row(s).", "returned test rows", mLayer->featureCount() ) );
212  }
213  else if ( mLayer->dataProvider()->hasErrors() )
214  {
215  QMessageBox::warning( this,
216  tr( "Query Failed" ),
217  tr( "An error occurred when executing the query." )
218  + tr( "\nThe data provider said:\n%1" ).arg( mLayer->dataProvider()->errors().join( QStringLiteral( "\n" ) ) ) );
219  mLayer->dataProvider()->clearErrors();
220  }
221  else
222  {
223  QMessageBox::warning( this,
224  tr( "Query Failed" ),
225  tr( "An error occurred when executing the query." ) );
226  }
227 }
228 
230 {
231  if ( !mLayer->setSubsetString( txtSQL->text() ) )
232  {
233  //error in query - show the problem
234  if ( mLayer->dataProvider()->hasErrors() )
235  {
236  QMessageBox::warning( this,
237  tr( "Query Failed" ),
238  tr( "An error occurred when executing the query." )
239  + tr( "\nThe data provider said:\n%1" ).arg( mLayer->dataProvider()->errors().join( QStringLiteral( "\n" ) ) ) );
240  mLayer->dataProvider()->clearErrors();
241  }
242  else
243  {
244  QMessageBox::warning( this, tr( "Error in Query" ), tr( "The subset string could not be set" ) );
245  }
246 
247  return;
248  }
249 
250  QDialog::accept();
251 }
252 
254 {
255  if ( mLayer->subsetString() != mOrigSubsetString )
256  mLayer->setSubsetString( mOrigSubsetString );
257 
258  QDialog::reject();
259 }
260 
262 {
263  txtSQL->insertText( QStringLiteral( " = " ) );
264  txtSQL->setFocus();
265 }
266 
268 {
269  txtSQL->insertText( QStringLiteral( " < " ) );
270  txtSQL->setFocus();
271 }
272 
274 {
275  txtSQL->insertText( QStringLiteral( " > " ) );
276  txtSQL->setFocus();
277 }
278 
280 {
281  txtSQL->insertText( QStringLiteral( "%" ) );
282  txtSQL->setFocus();
283 }
284 
286 {
287  txtSQL->insertText( QStringLiteral( " IN " ) );
288  txtSQL->setFocus();
289 }
290 
292 {
293  txtSQL->insertText( QStringLiteral( " NOT IN " ) );
294  txtSQL->setFocus();
295 }
296 
298 {
299  txtSQL->insertText( QStringLiteral( " LIKE " ) );
300  txtSQL->setFocus();
301 }
302 
304 {
305  return txtSQL->text();
306 }
307 
308 void QgsQueryBuilder::setSql( const QString &sqlStatement )
309 {
310  txtSQL->setText( sqlStatement );
311 }
312 
313 void QgsQueryBuilder::on_lstFields_clicked( const QModelIndex &index )
314 {
315  if ( mPreviousFieldRow != index.row() )
316  {
317  mPreviousFieldRow = index.row();
318 
319  btnSampleValues->setEnabled( true );
320  btnGetAllValues->setEnabled( true );
321 
322  mModelValues->clear();
323  }
324 }
325 
326 void QgsQueryBuilder::on_lstFields_doubleClicked( const QModelIndex &index )
327 {
328  txtSQL->insertText( '\"' + mLayer->fields().at( mModelFields->data( index, Qt::UserRole + 1 ).toInt() ).name() + '\"' );
329  txtSQL->setFocus();
330 }
331 
332 void QgsQueryBuilder::on_lstValues_doubleClicked( const QModelIndex &index )
333 {
334  QVariant value = mModelValues->data( index, Qt::UserRole + 1 );
335  if ( value.isNull() )
336  txtSQL->insertText( QStringLiteral( "NULL" ) );
337  else if ( value.type() == QVariant::Date && mLayer->providerType() == QLatin1String( "ogr" ) && mLayer->storageType() == QLatin1String( "ESRI Shapefile" ) )
338  txtSQL->insertText( '\'' + value.toDate().toString( QStringLiteral( "yyyy/MM/dd" ) ) + '\'' );
339  else if ( value.type() == QVariant::Int || value.type() == QVariant::Double || value.type() == QVariant::LongLong )
340  txtSQL->insertText( value.toString() );
341  else
342  txtSQL->insertText( '\'' + value.toString().replace( '\'', QLatin1String( "''" ) ) + '\'' );
343 
344  txtSQL->setFocus();
345 }
346 
348 {
349  txtSQL->insertText( QStringLiteral( " <= " ) );
350  txtSQL->setFocus();
351 }
352 
354 {
355  txtSQL->insertText( QStringLiteral( " >= " ) );
356  txtSQL->setFocus();
357 }
358 
360 {
361  txtSQL->insertText( QStringLiteral( " != " ) );
362  txtSQL->setFocus();
363 }
364 
366 {
367  txtSQL->insertText( QStringLiteral( " AND " ) );
368  txtSQL->setFocus();
369 }
370 
372 {
373  txtSQL->insertText( QStringLiteral( " NOT " ) );
374  txtSQL->setFocus();
375 }
376 
378 {
379  txtSQL->insertText( QStringLiteral( " OR " ) );
380  txtSQL->setFocus();
381 }
382 
384 {
385  txtSQL->clear();
386  mLayer->setSubsetString( QLatin1String( "" ) );
387  mUseUnfilteredLayer->setDisabled( true );
388 }
389 
391 {
392  txtSQL->insertText( QStringLiteral( " ILIKE " ) );
393  txtSQL->setFocus();
394 }
395 
397 {
398  lblDataUri->setText( uri );
399 }
400 
401 void QgsQueryBuilder::showHelp()
402 {
403  QgsHelp::openHelp( QStringLiteral( "working_with_vector/vector_properties.html#query-builder" ) );
404 }
void accept() override
void on_btnLessEqual_clicked()
FieldOrigin fieldOrigin(int fieldIdx) const
Get field&#39;s origin (value from an enumeration)
Definition: qgsfields.cpp:161
QString name
Definition: qgsfield.h:54
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:54
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:41
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:48
void on_btnGetAllValues_clicked()
Get all distinct values for the field.
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:135
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.
void on_lstFields_doubleClicked(const QModelIndex &index)
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.
void on_lstFields_clicked(const QModelIndex &index)
void on_btnGreaterEqual_clicked()
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
void on_btnSampleValues_clicked()
Get sample distinct values for the selected field.
void on_btnGreaterThan_clicked()
QString name
Definition: qgsmaplayer.h:58
QString providerType() const
Return the provider type for this layer.
Represents a vector layer which manages a vector based data sets.
void on_lstValues_doubleClicked(const QModelIndex &index)
void showEvent(QShowEvent *event) override