QGIS API Documentation  2.3.0-Master
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Properties Friends Macros Groups 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( layer->name() );
53  txtSQL->setText( mOrigSubsetString );
54 
56 }
57 
59 {
60  QSettings settings;
61  settings.setValue( "/Windows/QueryBuilder/geometry", saveGeometry() );
62 }
63 
65 {
66  const QgsFields& fields = mLayer->pendingFields();
67  for ( int idx = 0; idx < fields.count(); ++idx )
68  {
69  QStandardItem *myItem = new QStandardItem( fields[idx].name() );
70  myItem->setData( idx );
71  myItem->setEditable( false );
72  mModelFields->insertRow( mModelFields->rowCount(), myItem );
73  }
74 
75  // All fields get ... setup
77 }
78 
80 {
81  lstFields->setModel( mModelFields );
82 }
83 
85 {
86  //Initialize the models
87  mModelFields = new QStandardItemModel();
88  mModelValues = new QStandardItemModel();
89  // Modes
90  lstFields->setViewMode( QListView::ListMode );
91  lstValues->setViewMode( QListView::ListMode );
92  lstFields->setSelectionBehavior( QAbstractItemView::SelectRows );
93  lstValues->setSelectionBehavior( QAbstractItemView::SelectRows );
94  // Performance tip since Qt 4.1
95  lstFields->setUniformItemSizes( true );
96  lstValues->setUniformItemSizes( true );
97  // Colored rows
98  lstFields->setAlternatingRowColors( true );
99  lstValues->setAlternatingRowColors( true );
100 }
101 
102 void QgsQueryBuilder::fillValues( int idx, int limit )
103 {
104  // clear the model
105  mModelValues->clear();
106 
107  // determine the field type
108  QList<QVariant> values;
109  mLayer->uniqueValues( idx, values, limit );
110 
111  QSettings settings;
112  QString nullValue = settings.value( "qgis/nullValue", "NULL" ).toString();
113 
114  QgsDebugMsg( QString( "nullValue: %1" ).arg( nullValue ) );
115 
116  for ( int i = 0; i < values.size(); i++ )
117  {
118  QString value;
119  if ( values[i].isNull() )
120  value = nullValue;
121  else if ( values[i].type() == QVariant::Date && mLayer->providerType() == "ogr" && mLayer->storageType() == "ESRI Shapefile" )
122  value = values[i].toDate().toString( "yyyy/MM/dd" );
123  else
124  value = values[i].toString();
125 
126  QStandardItem *myItem = new QStandardItem( value );
127  myItem->setEditable( false );
128  myItem->setData( values[i], Qt::UserRole + 1 );
129  mModelValues->insertRow( mModelValues->rowCount(), myItem );
130  QgsDebugMsg( QString( "Value is null: %1\nvalue: %2" ).arg( values[i].isNull() ).arg( values[i].isNull() ? nullValue : values[i].toString() ) );
131  }
132 }
133 
135 {
136  lstValues->setCursor( Qt::WaitCursor );
137 
138  QString prevSubsetString = mLayer->subsetString();
139  if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
140  {
141  mLayer->setSubsetString( "" );
142  }
143 
144  //delete connection mModelValues and lstValues
145  QStandardItemModel *tmp = new QStandardItemModel();
146  lstValues->setModel( tmp );
147  //Clear and fill the mModelValues
148  fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), 25 );
149  lstValues->setModel( mModelValues );
150  //delete the tmp
151  delete tmp;
152 
153  if ( prevSubsetString != mLayer->subsetString() )
154  {
155  mLayer->setSubsetString( prevSubsetString );
156  }
157 
158  lstValues->setCursor( Qt::ArrowCursor );
159 }
160 
162 {
163  lstValues->setCursor( Qt::WaitCursor );
164 
165  QString prevSubsetString = mLayer->subsetString();
166  if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
167  {
168  mLayer->setSubsetString( "" );
169  }
170 
171  //delete connection mModelValues and lstValues
172  QStandardItemModel *tmp = new QStandardItemModel();
173  lstValues->setModel( tmp );
174  //Clear and fill the mModelValues
175  fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), -1 );
176  lstValues->setModel( mModelValues );
177  //delete the tmp
178  delete tmp;
179 
180  if ( prevSubsetString != mLayer->subsetString() )
181  {
182  mLayer->setSubsetString( prevSubsetString );
183  }
184 
185  lstValues->setCursor( Qt::ArrowCursor );
186 }
187 
189 {
190  // test the sql statement to see if it works
191  // by counting the number of records that would be
192  // returned
193 
194  if ( mLayer->setSubsetString( txtSQL->toPlainText() ) )
195  {
196  mUseUnfilteredLayer->setDisabled( mLayer->subsetString().isEmpty() );
197 
198  QMessageBox::information( this,
199  tr( "Query Result" ),
200  tr( "The where clause returned %n row(s).", "returned test rows", mLayer->featureCount() ) );
201  }
202  else if ( mLayer->dataProvider()->hasErrors() )
203  {
204  QMessageBox::warning( this,
205  tr( "Query Failed" ),
206  tr( "An error occurred when executing the query." )
207  + tr( "\nThe data provider said:\n%1" ).arg( mLayer->dataProvider()->errors().join( "\n" ) ) );
209  }
210  else
211  {
212  QMessageBox::warning( this,
213  tr( "Query Failed" ),
214  tr( "An error occurred when executing the query." ) );
215  }
216 }
217 
219 {
220  if ( !mLayer->setSubsetString( txtSQL->toPlainText() ) )
221  {
222  //error in query - show the problem
223  if ( mLayer->dataProvider()->hasErrors() )
224  {
225  QMessageBox::warning( this,
226  tr( "Query Failed" ),
227  tr( "An error occurred when executing the query." )
228  + tr( "\nThe data provider said:\n%1" ).arg( mLayer->dataProvider()->errors().join( "\n" ) ) );
230  }
231  else
232  {
233  QMessageBox::warning( this, tr( "Error in Query" ), tr( "The subset string could not be set" ) );
234  }
235 
236  return;
237  }
238 
239  QDialog::accept();
240 }
241 
243 {
246 
247  QDialog::reject();
248 }
249 
251 {
252  txtSQL->insertPlainText( " = " );
253  txtSQL->setFocus();
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( " IN " );
277  txtSQL->setFocus();
278 }
279 
281 {
282  txtSQL->insertPlainText( " NOT IN " );
283  txtSQL->setFocus();
284 }
285 
287 {
288  txtSQL->insertPlainText( " LIKE " );
289  txtSQL->setFocus();
290 }
291 
293 {
294  return txtSQL->toPlainText();
295 }
296 
297 void QgsQueryBuilder::setSql( QString sqlStatement )
298 {
299  txtSQL->setText( sqlStatement );
300 }
301 
303 {
304  if ( mPreviousFieldRow != index.row() )
305  {
306  mPreviousFieldRow = index.row();
307 
308  btnSampleValues->setEnabled( true );
309  btnGetAllValues->setEnabled( true );
310 
311  mModelValues->clear();
312  }
313 }
314 
316 {
317  txtSQL->insertPlainText( "\"" + mLayer->pendingFields()[ mModelFields->data( index, Qt::UserRole+1 ).toInt()].name() + "\"" );
318  txtSQL->setFocus();
319 }
320 
322 {
323  QVariant value = mModelValues->data( index, Qt::UserRole + 1 );
324  if ( value.isNull() )
325  txtSQL->insertPlainText( "NULL" );
326  else if ( value.type() == QVariant::Date && mLayer->providerType() == "ogr" && mLayer->storageType() == "ESRI Shapefile" )
327  txtSQL->insertPlainText( "'" + value.toDate().toString( "yyyy/MM/dd" ) + "'" );
328  else if ( value.type() == QVariant::Int || value.type() == QVariant::Double || value.type() == QVariant::LongLong )
329  txtSQL->insertPlainText( value.toString() );
330  else
331  txtSQL->insertPlainText( "'" + value.toString().replace( "'", "''" ) + "'" );
332 
333  txtSQL->setFocus();
334 }
335 
337 {
338  txtSQL->insertPlainText( " <= " );
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( " AND " );
357  txtSQL->setFocus();
358 }
359 
361 {
362  txtSQL->insertPlainText( " NOT " );
363  txtSQL->setFocus();
364 }
365 
367 {
368  txtSQL->insertPlainText( " OR " );
369  txtSQL->setFocus();
370 }
371 
373 {
374  txtSQL->clear();
375  mLayer->setSubsetString( "" );
376  mUseUnfilteredLayer->setDisabled( true );
377 }
378 
380 {
381  txtSQL->insertPlainText( " ILIKE " );
382  txtSQL->setFocus();
383 }
384 
386 {
387  lblDataUri->setText( uri );
388 }
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:164
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:198
QString mOrigSubsetString
original subset string
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)
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)