QGIS API Documentation  2.18.21-Las Palmas (9fba24a)
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 <QListView>
18 #include <QMessageBox>
19 #include <QRegExp>
20 #include <QPushButton>
21 #include <QSettings>
22 #include "qgsvectorlayer.h"
23 #include "qgsvectordataprovider.h"
24 
25 // constructor used when the query builder must make its own
26 // connection to the database
28  QWidget *parent, const Qt::WindowFlags& fl )
29  : QDialog( parent, fl )
30  , mPreviousFieldRow( -1 )
31  , mLayer( layer )
32 {
33  setupUi( this );
34 
35  QSettings settings;
36  restoreGeometry( settings.value( "/Windows/QueryBuilder/geometry" ).toByteArray() );
37 
38  QPushButton *pbn = new QPushButton( tr( "&Test" ) );
39  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
40  connect( pbn, SIGNAL( clicked() ), this, SLOT( test() ) );
41 
42  pbn = new QPushButton( tr( "&Clear" ) );
43  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
44  connect( pbn, SIGNAL( clicked() ), this, SLOT( clear() ) );
45 
46  setupGuiViews();
47 
48  mOrigSubsetString = layer->subsetString();
49 
50  mUseUnfilteredLayer->setDisabled( mLayer->subsetString().isEmpty() );
51 
52  lblDataUri->setText( tr( "Set provider filter on %1" ).arg( layer->name() ) );
53  txtSQL->setText( mOrigSubsetString );
54 
55  populateFields();
56 }
57 
59 {
60  QSettings settings;
61  settings.setValue( "/Windows/QueryBuilder/geometry", saveGeometry() );
62 }
63 
65 {
66  txtSQL->setFocus();
67  QDialog::showEvent( event );
68 }
69 
70 void QgsQueryBuilder::populateFields()
71 {
72  const QgsFields& fields = mLayer->fields();
73  for ( int idx = 0; idx < fields.count(); ++idx )
74  {
75  if ( fields.fieldOrigin( idx ) != QgsFields::OriginProvider )
76  {
77  // only consider native fields
78  continue;
79  }
80  QStandardItem *myItem = new QStandardItem( fields[idx].name() );
81  myItem->setData( idx );
82  myItem->setEditable( false );
83  mModelFields->insertRow( mModelFields->rowCount(), myItem );
84  }
85 
86  // All fields get ... setup
87  setupLstFieldsModel();
88 }
89 
90 void QgsQueryBuilder::setupLstFieldsModel()
91 {
92  lstFields->setModel( mModelFields );
93 }
94 
95 void QgsQueryBuilder::setupGuiViews()
96 {
97  //Initialize the models
98  mModelFields = new QStandardItemModel();
99  mModelValues = new QStandardItemModel();
100  // Modes
101  lstFields->setViewMode( QListView::ListMode );
102  lstValues->setViewMode( QListView::ListMode );
103  lstFields->setSelectionBehavior( QAbstractItemView::SelectRows );
104  lstValues->setSelectionBehavior( QAbstractItemView::SelectRows );
105  // Performance tip since Qt 4.1
106  lstFields->setUniformItemSizes( true );
107  lstValues->setUniformItemSizes( true );
108  // Colored rows
109  lstFields->setAlternatingRowColors( true );
110  lstValues->setAlternatingRowColors( true );
111 }
112 
113 void QgsQueryBuilder::fillValues( int idx, int limit )
114 {
115  // clear the model
116  mModelValues->clear();
117 
118  // determine the field type
119  QList<QVariant> values;
120  mLayer->uniqueValues( idx, values, limit );
121 
122  QSettings settings;
123  QString nullValue = settings.value( "qgis/nullValue", "NULL" ).toString();
124 
125  QgsDebugMsg( QString( "nullValue: %1" ).arg( nullValue ) );
126 
127  for ( int i = 0; i < values.size(); i++ )
128  {
129  QString value;
130  if ( values[i].isNull() )
131  value = nullValue;
132  else if ( values[i].type() == QVariant::Date && mLayer->providerType() == "ogr" && mLayer->storageType() == "ESRI Shapefile" )
133  value = values[i].toDate().toString( "yyyy/MM/dd" );
134  else
135  value = values[i].toString();
136 
137  QStandardItem *myItem = new QStandardItem( value );
138  myItem->setEditable( false );
139  myItem->setData( values[i], Qt::UserRole + 1 );
140  mModelValues->insertRow( mModelValues->rowCount(), myItem );
141  QgsDebugMsg( QString( "Value is null: %1\nvalue: %2" ).arg( values[i].isNull() ).arg( values[i].isNull() ? nullValue : values[i].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( "" );
153  }
154 
155  //delete connection mModelValues and lstValues
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( "" );
180  }
181 
182  //delete connection mModelValues and lstValues
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 
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( "\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( "\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( " = " );
264  txtSQL->setFocus();
265 }
266 
268 {
269  txtSQL->insertText( " < " );
270  txtSQL->setFocus();
271 }
272 
274 {
275  txtSQL->insertText( " > " );
276  txtSQL->setFocus();
277 }
278 
280 {
281  txtSQL->insertText( "%" );
282  txtSQL->setFocus();
283 }
284 
286 {
287  txtSQL->insertText( " IN " );
288  txtSQL->setFocus();
289 }
290 
292 {
293  txtSQL->insertText( " NOT IN " );
294  txtSQL->setFocus();
295 }
296 
298 {
299  txtSQL->insertText( " 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 
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 
327 {
328  txtSQL->insertText( '\"' + mLayer->fields().at( mModelFields->data( index, Qt::UserRole + 1 ).toInt() ).name() + '\"' );
329  txtSQL->setFocus();
330 }
331 
333 {
334  QVariant value = mModelValues->data( index, Qt::UserRole + 1 );
335  if ( value.isNull() )
336  txtSQL->insertText( "NULL" );
337  else if ( value.type() == QVariant::Date && mLayer->providerType() == "ogr" && mLayer->storageType() == "ESRI Shapefile" )
338  txtSQL->insertText( '\'' + value.toDate().toString( "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( '\'', "''" ) + '\'' );
343 
344  txtSQL->setFocus();
345 }
346 
348 {
349  txtSQL->insertText( " <= " );
350  txtSQL->setFocus();
351 }
352 
354 {
355  txtSQL->insertText( " >= " );
356  txtSQL->setFocus();
357 }
358 
360 {
361  txtSQL->insertText( " != " );
362  txtSQL->setFocus();
363 }
364 
366 {
367  txtSQL->insertText( " AND " );
368  txtSQL->setFocus();
369 }
370 
372 {
373  txtSQL->insertText( " NOT " );
374  txtSQL->setFocus();
375 }
376 
378 {
379  txtSQL->insertText( " OR " );
380  txtSQL->setFocus();
381 }
382 
384 {
385  txtSQL->clear();
386  mLayer->setSubsetString( "" );
387  mUseUnfilteredLayer->setDisabled( true );
388 }
389 
391 {
392  txtSQL->insertText( " ILIKE " );
393  txtSQL->setFocus();
394 }
395 
397 {
398  lblDataUri->setText( uri );
399 }
virtual QString subsetString()
Get the string (typically sql) used to define a subset of the layer.
void accept() override
QByteArray toByteArray() const
static unsigned index
virtual bool event(QEvent *e)
void setupUi(QWidget *widget)
void on_btnLessEqual_clicked()
virtual void reject()
FieldOrigin fieldOrigin(int fieldIdx) const
Get field&#39;s origin (value from an enumeration)
Definition: qgsfield.cpp:448
void test()
Test the constructed sql statement to see if the vector layer data provider likes it...
virtual QVariant data(const QModelIndex &index, int role) const
QString toString(Qt::DateFormat format) const
QString storageType() const
Returns the permanent storage type for this layer as a friendly name.
#define QgsDebugMsg(str)
Definition: qgslogger.h:33
void uniqueValues(int index, QList< QVariant > &uniqueValues, int limit=-1)
Calculates a list of unique values contained within an attribute in the layer.
Container of fields for a vector layer.
Definition: qgsfield.h:252
QStringList errors()
Get recorded errors.
field comes from the underlying data provider of the vector layer (originIndex = index in provider&#39;s ...
Definition: qgsfield.h:259
QString join(const QString &separator) const
void on_btnGetAllValues_clicked()
int count() const
Return number of items.
Definition: qgsfield.cpp:402
QString tr(const char *sourceText, const char *disambiguation, int n)
StandardButton information(QWidget *parent, const QString &title, const QString &text, QFlags< QMessageBox::StandardButton > buttons, StandardButton defaultButton)
const QgsField & at(int i) const
Get field at particular index (must be in range 0..N-1)
Definition: qgsfield.cpp:422
int size() const
virtual void setData(const QVariant &value, int role)
QgsFields fields() const
Returns the list of fields of this layer.
long featureCount(QgsSymbolV2 *symbol)
Number of features rendered with specified symbol.
void setValue(const QString &key, const QVariant &value)
const char * name() const
void setSql(const QString &sqlStatement)
int toInt(bool *ok) const
bool isNull() const
bool restoreGeometry(const QByteArray &geometry)
QgsQueryBuilder(QgsVectorLayer *layer, QWidget *parent=nullptr, const Qt::WindowFlags &fl=QgisGui::ModalDialogFlags)
This constructor is used when the query builder is called from the vector layer properties dialog...
bool hasErrors()
Provider has errors to report.
void on_lstFields_doubleClicked(const QModelIndex &index)
bool isEmpty() const
int row() const
void clearErrors()
Clear recorded errors.
void insertRow(int row, const QList< QStandardItem * > &items)
virtual void accept()
void on_lstFields_clicked(const QModelIndex &index)
void on_btnGreaterEqual_clicked()
QDate toDate() const
QString & replace(int position, int n, QChar after)
QVariant value(const QString &key, const QVariant &defaultValue) const
QByteArray saveGeometry() const
void setDatasourceDescription(const QString &uri)
virtual int rowCount(const QModelIndex &parent) const
virtual bool setSubsetString(const QString &subset)
Set the string (typically sql) used to define a subset of the layer.
virtual void showEvent(QShowEvent *event)
void reject() override
void on_btnSampleValues_clicked()
void on_btnGreaterThan_clicked()
StandardButton warning(QWidget *parent, const QString &title, const QString &text, QFlags< QMessageBox::StandardButton > buttons, StandardButton defaultButton)
typedef WindowFlags
QString name
Read property of QString layerName.
Definition: qgsmaplayer.h:53
QgsVectorDataProvider * dataProvider()
Returns the data provider.
QString providerType() const
Return the provider type for this layer.
Type type() const
bool connect(const QObject *sender, const char *signal, const QObject *receiver, const char *method, Qt::ConnectionType type)
Represents a vector layer which manages a vector based data sets.
QString toString() const
void on_lstValues_doubleClicked(const QModelIndex &index)
bool isNull(const QVariant &v)
void showEvent(QShowEvent *event) override
void setEditable(bool editable)