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