QGIS API Documentation  2.99.0-Master (314842d)
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, SIGNAL( clicked() ), this, SLOT( test() ) );
42 
43  pbn = new QPushButton( tr( "&Clear" ) );
44  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
45  connect( pbn, SIGNAL( clicked() ), this, SLOT( 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  QList<QVariant> values;
121  mLayer->uniqueValues( idx, values, limit );
122 
123  QgsSettings settings;
124  QString nullValue = QgsApplication::nullRepresentation();
125 
126  QgsDebugMsg( QString( "nullValue: %1" ).arg( nullValue ) );
127 
128  for ( int i = 0; i < values.size(); i++ )
129  {
130  QString value;
131  if ( values[i].isNull() )
132  value = nullValue;
133  else if ( values[i].type() == QVariant::Date && mLayer->providerType() == QLatin1String( "ogr" ) && mLayer->storageType() == QLatin1String( "ESRI Shapefile" ) )
134  value = values[i].toDate().toString( QStringLiteral( "yyyy/MM/dd" ) );
135  else
136  value = values[i].toString();
137 
138  QStandardItem *myItem = new QStandardItem( value );
139  myItem->setEditable( false );
140  myItem->setData( values[i], Qt::UserRole + 1 );
141  mModelValues->insertRow( mModelValues->rowCount(), myItem );
142  QgsDebugMsg( QString( "Value is null: %1\nvalue: %2" ).arg( values[i].isNull() ).arg( values[i].isNull() ? nullValue : values[i].toString() ) );
143  }
144 }
145 
147 {
148  lstValues->setCursor( Qt::WaitCursor );
149 
150  QString prevSubsetString = mLayer->subsetString();
151  if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
152  {
153  mLayer->setSubsetString( QLatin1String( "" ) );
154  }
155 
156  //delete connection mModelValues and lstValues
157  QStandardItemModel *tmp = new QStandardItemModel();
158  lstValues->setModel( tmp );
159  //Clear and fill the mModelValues
160  fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), 25 );
161  lstValues->setModel( mModelValues );
162  //delete the tmp
163  delete tmp;
164 
165  if ( prevSubsetString != mLayer->subsetString() )
166  {
167  mLayer->setSubsetString( prevSubsetString );
168  }
169 
170  lstValues->setCursor( Qt::ArrowCursor );
171 }
172 
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  //delete connection mModelValues and lstValues
184  QStandardItemModel *tmp = new QStandardItemModel();
185  lstValues->setModel( tmp );
186  //Clear and fill the mModelValues
187  fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), -1 );
188  lstValues->setModel( mModelValues );
189  //delete the tmp
190  delete tmp;
191 
192  if ( prevSubsetString != mLayer->subsetString() )
193  {
194  mLayer->setSubsetString( prevSubsetString );
195  }
196 
197  lstValues->setCursor( Qt::ArrowCursor );
198 }
199 
201 {
202  // test the sql statement to see if it works
203  // by counting the number of records that would be
204  // returned
205 
206  if ( mLayer->setSubsetString( txtSQL->text() ) )
207  {
208  mUseUnfilteredLayer->setDisabled( mLayer->subsetString().isEmpty() );
209 
210  QMessageBox::information( this,
211  tr( "Query Result" ),
212  tr( "The where clause returned %n row(s).", "returned test rows", mLayer->featureCount() ) );
213  }
214  else if ( mLayer->dataProvider()->hasErrors() )
215  {
216  QMessageBox::warning( this,
217  tr( "Query Failed" ),
218  tr( "An error occurred when executing the query." )
219  + tr( "\nThe data provider said:\n%1" ).arg( mLayer->dataProvider()->errors().join( QStringLiteral( "\n" ) ) ) );
220  mLayer->dataProvider()->clearErrors();
221  }
222  else
223  {
224  QMessageBox::warning( this,
225  tr( "Query Failed" ),
226  tr( "An error occurred when executing the query." ) );
227  }
228 }
229 
231 {
232  if ( !mLayer->setSubsetString( txtSQL->text() ) )
233  {
234  //error in query - show the problem
235  if ( mLayer->dataProvider()->hasErrors() )
236  {
237  QMessageBox::warning( this,
238  tr( "Query Failed" ),
239  tr( "An error occurred when executing the query." )
240  + tr( "\nThe data provider said:\n%1" ).arg( mLayer->dataProvider()->errors().join( QStringLiteral( "\n" ) ) ) );
241  mLayer->dataProvider()->clearErrors();
242  }
243  else
244  {
245  QMessageBox::warning( this, tr( "Error in Query" ), tr( "The subset string could not be set" ) );
246  }
247 
248  return;
249  }
250 
251  QDialog::accept();
252 }
253 
255 {
256  if ( mLayer->subsetString() != mOrigSubsetString )
257  mLayer->setSubsetString( mOrigSubsetString );
258 
259  QDialog::reject();
260 }
261 
263 {
264  txtSQL->insertText( QStringLiteral( " = " ) );
265  txtSQL->setFocus();
266 }
267 
269 {
270  txtSQL->insertText( QStringLiteral( " < " ) );
271  txtSQL->setFocus();
272 }
273 
275 {
276  txtSQL->insertText( QStringLiteral( " > " ) );
277  txtSQL->setFocus();
278 }
279 
281 {
282  txtSQL->insertText( QStringLiteral( "%" ) );
283  txtSQL->setFocus();
284 }
285 
287 {
288  txtSQL->insertText( QStringLiteral( " IN " ) );
289  txtSQL->setFocus();
290 }
291 
293 {
294  txtSQL->insertText( QStringLiteral( " NOT IN " ) );
295  txtSQL->setFocus();
296 }
297 
299 {
300  txtSQL->insertText( QStringLiteral( " LIKE " ) );
301  txtSQL->setFocus();
302 }
303 
305 {
306  return txtSQL->text();
307 }
308 
309 void QgsQueryBuilder::setSql( const QString &sqlStatement )
310 {
311  txtSQL->setText( sqlStatement );
312 }
313 
315 {
316  if ( mPreviousFieldRow != index.row() )
317  {
318  mPreviousFieldRow = index.row();
319 
320  btnSampleValues->setEnabled( true );
321  btnGetAllValues->setEnabled( true );
322 
323  mModelValues->clear();
324  }
325 }
326 
328 {
329  txtSQL->insertText( '\"' + mLayer->fields().at( mModelFields->data( index, Qt::UserRole + 1 ).toInt() ).name() + '\"' );
330  txtSQL->setFocus();
331 }
332 
334 {
335  QVariant value = mModelValues->data( index, Qt::UserRole + 1 );
336  if ( value.isNull() )
337  txtSQL->insertText( QStringLiteral( "NULL" ) );
338  else if ( value.type() == QVariant::Date && mLayer->providerType() == QLatin1String( "ogr" ) && mLayer->storageType() == QLatin1String( "ESRI Shapefile" ) )
339  txtSQL->insertText( '\'' + value.toDate().toString( QStringLiteral( "yyyy/MM/dd" ) ) + '\'' );
340  else if ( value.type() == QVariant::Int || value.type() == QVariant::Double || value.type() == QVariant::LongLong )
341  txtSQL->insertText( value.toString() );
342  else
343  txtSQL->insertText( '\'' + value.toString().replace( '\'', QLatin1String( "''" ) ) + '\'' );
344 
345  txtSQL->setFocus();
346 }
347 
349 {
350  txtSQL->insertText( QStringLiteral( " <= " ) );
351  txtSQL->setFocus();
352 }
353 
355 {
356  txtSQL->insertText( QStringLiteral( " >= " ) );
357  txtSQL->setFocus();
358 }
359 
361 {
362  txtSQL->insertText( QStringLiteral( " != " ) );
363  txtSQL->setFocus();
364 }
365 
367 {
368  txtSQL->insertText( QStringLiteral( " AND " ) );
369  txtSQL->setFocus();
370 }
371 
373 {
374  txtSQL->insertText( QStringLiteral( " NOT " ) );
375  txtSQL->setFocus();
376 }
377 
379 {
380  txtSQL->insertText( QStringLiteral( " OR " ) );
381  txtSQL->setFocus();
382 }
383 
385 {
386  txtSQL->clear();
387  mLayer->setSubsetString( QLatin1String( "" ) );
388  mUseUnfilteredLayer->setDisabled( true );
389 }
390 
392 {
393  txtSQL->insertText( QStringLiteral( " ILIKE " ) );
394  txtSQL->setFocus();
395 }
396 
398 {
399  lblDataUri->setText( uri );
400 }
void uniqueValues(int index, QList< QVariant > &uniqueValues, int limit=-1) const
Calculates a list of unique values contained within an attribute in the layer.
void accept() override
static unsigned index
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:53
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:51
QString storageType() const
Returns the permanent storage type for this layer as a friendly name.
#define QgsDebugMsg(str)
Definition: qgslogger.h:36
Container of fields for a vector layer.
Definition: qgsfields.h:39
Field comes from the underlying data provider of the vector layer (originIndex = index in provider&#39;s ...
Definition: qgsfields.h:46
void on_btnGetAllValues_clicked()
int count() const
Return number of items.
Definition: qgsfields.cpp:115
QgsFields fields() const
Returns the list of fields of this layer.
QgsField at(int i) const
Get field at particular index (must be in range 0..N-1)
Definition: qgsfields.cpp:135
void setSql(const QString &sqlStatement)
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.
QgsQueryBuilder(QgsVectorLayer *layer, QWidget *parent=nullptr, Qt::WindowFlags fl=QgisGui::ModalDialogFlags)
This constructor is used when the query builder is called from the vector layer properties dialog...
void on_lstFields_clicked(const QModelIndex &index)
void on_btnGreaterEqual_clicked()
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.
QVariant value(const QString &key, const QVariant &defaultValue=QVariant(), const Section section=Section::NoSection) const
Returns the value for setting key.
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()
void on_btnGreaterThan_clicked()
QString name
Read property of QString layerName.
Definition: qgsmaplayer.h:56
QgsVectorDataProvider * dataProvider()
Returns the data provider.
QString providerType() const
Return the provider type for this layer.
Represents a vector layer which manages a vector based data sets.
void setValue(const QString &key, const QVariant &value, const Section section=Section::NoSection)
Sets the value of setting key to value.
void on_lstValues_doubleClicked(const QModelIndex &index)
bool isNull(const QVariant &v)
void showEvent(QShowEvent *event) override