QGIS API Documentation  2.9.0-Master
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Modules Pages
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, 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 
64 void QgsQueryBuilder::showEvent( QShowEvent *event )
65 {
66  txtSQL->setFocus();
67  QDialog::showEvent( event );
68 }
69 
70 void QgsQueryBuilder::populateFields()
71 {
72  const QgsFields& fields = mLayer->pendingFields();
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
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( "" );
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( "\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( 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->pendingFields()[ 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
static unsigned index
void on_btnLessEqual_clicked()
void setSql(QString sqlStatement)
#define QgsDebugMsg(str)
Definition: qgslogger.h:33
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:172
QStringList errors()
Get recorded errors.
void on_btnGetAllValues_clicked()
const QString & name() const
Get the display name of the layer.
field comes from the underlying data provider of the vector layer (originIndex = index in provider's ...
Definition: qgsfield.h:179
QgsQueryBuilder(QgsVectorLayer *layer, QWidget *parent=0, Qt::WindowFlags fl=QgisGui::ModalDialogFlags)
bool hasErrors()
Provider has errors to report.
void on_lstFields_doubleClicked(const QModelIndex &index)
void clearErrors()
Clear recorded errors.
int count() const
Return number of items.
Definition: qgsfield.h:214
void on_lstFields_clicked(const QModelIndex &index)
void on_btnGreaterEqual_clicked()
QString providerType() const
Return the provider type for this layer.
virtual long featureCount() const
Number of features in the layer.
virtual bool setSubsetString(QString subset)
Set the string (typically sql) used to define a subset of the layer.
void setDatasourceDescription(QString uri)
FieldOrigin fieldOrigin(int fieldIdx) const
Get field's origin (value from an enumeration)
Definition: qgsfield.cpp:218
void reject() override
void on_btnSampleValues_clicked()
void on_btnGreaterThan_clicked()
const QgsFields & pendingFields() const
returns field list in the to-be-committed state
QgsVectorDataProvider * dataProvider()
Returns the data provider.
Represents a vector layer which manages a vector based data sets.
void on_lstValues_doubleClicked(const QModelIndex &index)
bool isNull(const QVariant &v)
void showEvent(QShowEvent *event) override
QString storageType() const
Returns the permanent storage type for this layer as a friendly name.
#define tr(sourceText)