QGIS API Documentation  2.0.1-Dufour
 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::WFlags 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 
302 void QgsQueryBuilder::on_lstFields_clicked( const QModelIndex &index )
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 
315 void QgsQueryBuilder::on_lstFields_doubleClicked( const QModelIndex &index )
316 {
317  txtSQL->insertPlainText( "\"" + mLayer->pendingFields()[ mModelFields->data( index, Qt::UserRole+1 ).toInt()].name() + "\"" );
318  txtSQL->setFocus();
319 }
320 
321 void QgsQueryBuilder::on_lstValues_doubleClicked( const QModelIndex &index )
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 }