QGIS API Documentation  2.13.0-Master
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()
QString name() const
Get the display name of the layer.
void test()
Test the constructed sql statement to see if the database likes it.
virtual QVariant data(const QModelIndex &index, int role) const
QString toString(Qt::DateFormat format) const
#define QgsDebugMsg(str)
Definition: qgslogger.h:33
QgsFields fields() const
Returns the list of fields of this layer.
void uniqueValues(int index, QList< QVariant > &uniqueValues, int limit=-1)
Returns unique values for column.
Container of fields for a vector layer.
Definition: qgsfield.h:189
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:196
QString join(const QString &separator) const
void on_btnGetAllValues_clicked()
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)
int size() const
virtual void setData(const QVariant &value, int role)
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.
int count() const
Return number of items.
Definition: qgsfield.cpp:363
void insertRow(int row, const QList< QStandardItem * > &items)
const QgsField & at(int i) const
Get field at particular index (must be in range 0..N-1)
Definition: qgsfield.cpp:383
virtual void accept()
void on_lstFields_clicked(const QModelIndex &index)
void on_btnGreaterEqual_clicked()
QDate toDate() const
QString providerType() const
Return the provider type for this layer.
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
FieldOrigin fieldOrigin(int fieldIdx) const
Get field&#39;s origin (value from an enumeration)
Definition: qgsfield.cpp:409
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
QgsVectorDataProvider * dataProvider()
Returns the data provider.
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)
QString storageType() const
Returns the permanent storage type for this layer as a friendly name.