QGIS API Documentation  2.5.0-Master
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Properties 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 
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 
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 
71 {
72  const QgsFields& fields = mLayer->pendingFields();
73  for ( int idx = 0; idx < fields.count(); ++idx )
74  {
75  QStandardItem *myItem = new QStandardItem( fields[idx].name() );
76  myItem->setData( idx );
77  myItem->setEditable( false );
78  mModelFields->insertRow( mModelFields->rowCount(), myItem );
79  }
80 
81  // All fields get ... setup
83 }
84 
86 {
87  lstFields->setModel( mModelFields );
88 }
89 
91 {
92  //Initialize the models
93  mModelFields = new QStandardItemModel();
94  mModelValues = new QStandardItemModel();
95  // Modes
96  lstFields->setViewMode( QListView::ListMode );
97  lstValues->setViewMode( QListView::ListMode );
98  lstFields->setSelectionBehavior( QAbstractItemView::SelectRows );
99  lstValues->setSelectionBehavior( QAbstractItemView::SelectRows );
100  // Performance tip since Qt 4.1
101  lstFields->setUniformItemSizes( true );
102  lstValues->setUniformItemSizes( true );
103  // Colored rows
104  lstFields->setAlternatingRowColors( true );
105  lstValues->setAlternatingRowColors( true );
106 }
107 
108 void QgsQueryBuilder::fillValues( int idx, int limit )
109 {
110  // clear the model
111  mModelValues->clear();
112 
113  // determine the field type
114  QList<QVariant> values;
115  mLayer->uniqueValues( idx, values, limit );
116 
117  QSettings settings;
118  QString nullValue = settings.value( "qgis/nullValue", "NULL" ).toString();
119 
120  QgsDebugMsg( QString( "nullValue: %1" ).arg( nullValue ) );
121 
122  for ( int i = 0; i < values.size(); i++ )
123  {
124  QString value;
125  if ( values[i].isNull() )
126  value = nullValue;
127  else if ( values[i].type() == QVariant::Date && mLayer->providerType() == "ogr" && mLayer->storageType() == "ESRI Shapefile" )
128  value = values[i].toDate().toString( "yyyy/MM/dd" );
129  else
130  value = values[i].toString();
131 
132  QStandardItem *myItem = new QStandardItem( value );
133  myItem->setEditable( false );
134  myItem->setData( values[i], Qt::UserRole + 1 );
135  mModelValues->insertRow( mModelValues->rowCount(), myItem );
136  QgsDebugMsg( QString( "Value is null: %1\nvalue: %2" ).arg( values[i].isNull() ).arg( values[i].isNull() ? nullValue : values[i].toString() ) );
137  }
138 }
139 
141 {
142  lstValues->setCursor( Qt::WaitCursor );
143 
144  QString prevSubsetString = mLayer->subsetString();
145  if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
146  {
147  mLayer->setSubsetString( "" );
148  }
149 
150  //delete connection mModelValues and lstValues
151  QStandardItemModel *tmp = new QStandardItemModel();
152  lstValues->setModel( tmp );
153  //Clear and fill the mModelValues
154  fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), 25 );
155  lstValues->setModel( mModelValues );
156  //delete the tmp
157  delete tmp;
158 
159  if ( prevSubsetString != mLayer->subsetString() )
160  {
161  mLayer->setSubsetString( prevSubsetString );
162  }
163 
164  lstValues->setCursor( Qt::ArrowCursor );
165 }
166 
168 {
169  lstValues->setCursor( Qt::WaitCursor );
170 
171  QString prevSubsetString = mLayer->subsetString();
172  if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
173  {
174  mLayer->setSubsetString( "" );
175  }
176 
177  //delete connection mModelValues and lstValues
178  QStandardItemModel *tmp = new QStandardItemModel();
179  lstValues->setModel( tmp );
180  //Clear and fill the mModelValues
181  fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), -1 );
182  lstValues->setModel( mModelValues );
183  //delete the tmp
184  delete tmp;
185 
186  if ( prevSubsetString != mLayer->subsetString() )
187  {
188  mLayer->setSubsetString( prevSubsetString );
189  }
190 
191  lstValues->setCursor( Qt::ArrowCursor );
192 }
193 
195 {
196  // test the sql statement to see if it works
197  // by counting the number of records that would be
198  // returned
199 
200  if ( mLayer->setSubsetString( txtSQL->toPlainText() ) )
201  {
202  mUseUnfilteredLayer->setDisabled( mLayer->subsetString().isEmpty() );
203 
204  QMessageBox::information( this,
205  tr( "Query Result" ),
206  tr( "The where clause returned %n row(s).", "returned test rows", mLayer->featureCount() ) );
207  }
208  else if ( mLayer->dataProvider()->hasErrors() )
209  {
210  QMessageBox::warning( this,
211  tr( "Query Failed" ),
212  tr( "An error occurred when executing the query." )
213  + tr( "\nThe data provider said:\n%1" ).arg( mLayer->dataProvider()->errors().join( "\n" ) ) );
215  }
216  else
217  {
218  QMessageBox::warning( this,
219  tr( "Query Failed" ),
220  tr( "An error occurred when executing the query." ) );
221  }
222 }
223 
225 {
226  if ( !mLayer->setSubsetString( txtSQL->toPlainText() ) )
227  {
228  //error in query - show the problem
229  if ( mLayer->dataProvider()->hasErrors() )
230  {
231  QMessageBox::warning( this,
232  tr( "Query Failed" ),
233  tr( "An error occurred when executing the query." )
234  + tr( "\nThe data provider said:\n%1" ).arg( mLayer->dataProvider()->errors().join( "\n" ) ) );
236  }
237  else
238  {
239  QMessageBox::warning( this, tr( "Error in Query" ), tr( "The subset string could not be set" ) );
240  }
241 
242  return;
243  }
244 
245  QDialog::accept();
246 }
247 
249 {
252 
253  QDialog::reject();
254 }
255 
257 {
258  txtSQL->insertPlainText( " = " );
259  txtSQL->setFocus();
260 }
261 
263 {
264  txtSQL->insertPlainText( " < " );
265  txtSQL->setFocus();
266 }
267 
269 {
270  txtSQL->insertPlainText( " > " );
271  txtSQL->setFocus();
272 }
273 
275 {
276  txtSQL->insertPlainText( "%" );
277  txtSQL->setFocus();
278 }
279 
281 {
282  txtSQL->insertPlainText( " IN " );
283  txtSQL->setFocus();
284 }
285 
287 {
288  txtSQL->insertPlainText( " NOT IN " );
289  txtSQL->setFocus();
290 }
291 
293 {
294  txtSQL->insertPlainText( " LIKE " );
295  txtSQL->setFocus();
296 }
297 
299 {
300  return txtSQL->toPlainText();
301 }
302 
303 void QgsQueryBuilder::setSql( QString sqlStatement )
304 {
305  txtSQL->setText( sqlStatement );
306 }
307 
309 {
310  if ( mPreviousFieldRow != index.row() )
311  {
312  mPreviousFieldRow = index.row();
313 
314  btnSampleValues->setEnabled( true );
315  btnGetAllValues->setEnabled( true );
316 
317  mModelValues->clear();
318  }
319 }
320 
322 {
323  txtSQL->insertPlainText( "\"" + mLayer->pendingFields()[ mModelFields->data( index, Qt::UserRole+1 ).toInt()].name() + "\"" );
324  txtSQL->setFocus();
325 }
326 
328 {
329  QVariant value = mModelValues->data( index, Qt::UserRole + 1 );
330  if ( value.isNull() )
331  txtSQL->insertPlainText( "NULL" );
332  else if ( value.type() == QVariant::Date && mLayer->providerType() == "ogr" && mLayer->storageType() == "ESRI Shapefile" )
333  txtSQL->insertPlainText( "'" + value.toDate().toString( "yyyy/MM/dd" ) + "'" );
334  else if ( value.type() == QVariant::Int || value.type() == QVariant::Double || value.type() == QVariant::LongLong )
335  txtSQL->insertPlainText( value.toString() );
336  else
337  txtSQL->insertPlainText( "'" + value.toString().replace( "'", "''" ) + "'" );
338 
339  txtSQL->setFocus();
340 }
341 
343 {
344  txtSQL->insertPlainText( " <= " );
345  txtSQL->setFocus();
346 }
347 
349 {
350  txtSQL->insertPlainText( " >= " );
351  txtSQL->setFocus();
352 }
353 
355 {
356  txtSQL->insertPlainText( " != " );
357  txtSQL->setFocus();
358 }
359 
361 {
362  txtSQL->insertPlainText( " AND " );
363  txtSQL->setFocus();
364 }
365 
367 {
368  txtSQL->insertPlainText( " NOT " );
369  txtSQL->setFocus();
370 }
371 
373 {
374  txtSQL->insertPlainText( " OR " );
375  txtSQL->setFocus();
376 }
377 
379 {
380  txtSQL->clear();
381  mLayer->setSubsetString( "" );
382  mUseUnfilteredLayer->setDisabled( true );
383 }
384 
386 {
387  txtSQL->insertPlainText( " ILIKE " );
388  txtSQL->setFocus();
389 }
390 
392 {
393  lblDataUri->setText( uri );
394 }
virtual QString subsetString()
Get the string (typically sql) used to define a subset of the layer.
static unsigned index
void on_btnLessEqual_clicked()
void setSql(QString sqlStatement)
QStandardItemModel * mModelValues
Model for values ListView.
#define QgsDebugMsg(str)
Definition: qgslogger.h:36
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:163
QStringList errors()
Get recorded errors.
void on_btnGetAllValues_clicked()
const QString & name() const
Get the display name of the layer.
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:200
QString mOrigSubsetString
original subset string
void on_lstFields_clicked(const QModelIndex &index)
void showEvent(QShowEvent *event)
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)
void fillValues(int idx, int limit)
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)
QStandardItemModel * mModelFields
Model for fields ListView.
bool isNull(const QVariant &v)
QgsVectorLayer * mLayer
vector layer
int mPreviousFieldRow
Previous field row to delete model.
QString storageType() const
Returns the permanent storage type for this layer as a friendly name.
#define tr(sourceText)