Skip to content

Commit a1ab76d

Browse files
author
timlinux
committedJan 28, 2008
Committed patch from #901 - fixes for pg query dialog
git-svn-id: http://svn.osgeo.org/qgis/trunk/qgis@8061 c8812cc2-4d05-0410-92ff-de0c093fc19c
1 parent 3f2b9d2 commit a1ab76d

File tree

3 files changed

+145
-74
lines changed

3 files changed

+145
-74
lines changed
 

‎src/app/qgspgquerybuilder.cpp

Lines changed: 92 additions & 71 deletions
Original file line numberDiff line numberDiff line change
@@ -24,7 +24,7 @@ QgsPgQueryBuilder::QgsPgQueryBuilder(QWidget *parent, Qt::WFlags fl)
2424
: QDialog(parent, fl)
2525
{
2626
setupUi(this);
27-
setupListViews();
27+
setupGuiViews();
2828
}
2929
// constructor used when the query builder must make its own
3030
// connection to the database
@@ -33,7 +33,7 @@ QgsPgQueryBuilder::QgsPgQueryBuilder(QgsDataSourceURI *uri,
3333
: QDialog(parent, fl), mUri(uri)
3434
{
3535
setupUi(this);
36-
setupListViews();
36+
setupGuiViews();
3737
// The query builder must make its own connection to the database when
3838
// using this constructor
3939
QString connInfo = mUri->connInfo();
@@ -71,7 +71,7 @@ QgsPgQueryBuilder::QgsPgQueryBuilder(QString tableName, PGconn *con,
7171
: QDialog(parent, fl), mPgConnection(con)
7272
{
7373
setupUi(this);
74-
setupListViews();
74+
setupGuiViews();
7575
mOwnConnection = false; // we don't own this connection since it was passed to us
7676
mUri = new QgsDataSourceURI( "table=" + tableName);
7777
QString datasource = QString(tr("Table <b>%1</b> in database <b>%2</b> on host <b>%3</b>, user <b>%4</b>"))
@@ -138,8 +138,10 @@ void QgsPgQueryBuilder::populateFields()
138138
mFieldMap[fieldName] = QgsField(fieldName, type, fieldType);
139139
QStandardItem *myItem = new QStandardItem(fieldName);
140140
myItem->setEditable(false);
141-
mModelFields->insertRow(mModelFields->rowCount(),myItem);
141+
mModelFields->insertRow(mModelFields->rowCount(),myItem);
142142
}
143+
// All fields get ... setup
144+
setupLstFieldsModel();
143145
}
144146
else
145147
{
@@ -148,13 +150,16 @@ void QgsPgQueryBuilder::populateFields()
148150
PQclear(result);
149151
}
150152

151-
void QgsPgQueryBuilder::setupListViews()
153+
void QgsPgQueryBuilder::setupLstFieldsModel()
152154
{
153-
//Models
155+
lstFields->setModel(mModelFields);
156+
}
157+
158+
void QgsPgQueryBuilder::setupGuiViews()
159+
{
160+
//Initialize the models
154161
mModelFields = new QStandardItemModel();
155162
mModelValues = new QStandardItemModel();
156-
lstFields->setModel(mModelFields);
157-
lstValues->setModel(mModelValues);
158163
// Modes
159164
lstFields->setViewMode(QListView::ListMode);
160165
lstValues->setViewMode(QListView::ListMode);
@@ -163,99 +168,88 @@ void QgsPgQueryBuilder::setupListViews()
163168
// Performance tip since Qt 4.1
164169
lstFields->setUniformItemSizes(true);
165170
lstValues->setUniformItemSizes(true);
171+
// Colored rows
172+
lstFields->setAlternatingRowColors(true);
173+
lstValues->setAlternatingRowColors(true);
166174
}
167175

168-
void QgsPgQueryBuilder::on_btnSampleValues_clicked()
176+
void QgsPgQueryBuilder::fillValues(QString theSQL)
169177
{
170-
QString myFieldName = mModelFields->data(lstFields->currentIndex()).toString();
171-
if (myFieldName.isEmpty())
172-
return;
173-
174-
QString sql = "SELECT DISTINCT \"" + myFieldName + "\" " +
175-
"FROM (SELECT \"" + myFieldName + "\" " +
176-
"FROM " + mUri->quotedTablename() + " " +
177-
"LIMIT 5000) AS foo " +
178-
"ORDER BY \"" + myFieldName + "\" "+
179-
"LIMIT 25";
180-
// clear the values list
178+
// clear the model
181179
mModelValues->clear();
180+
182181
// determine the field type
183-
QgsField field = mFieldMap[myFieldName];
184-
bool isCharField = field.typeName().find("char") > -1;
185-
PGresult *result = PQexec(mPgConnection, (const char *) (sql.utf8()));
182+
QgsField field = mFieldMap[mModelFields->data(lstFields->currentIndex()).toString()];
183+
bool mActualFieldIsChar = field.typeName().find("char") > -1;
184+
185+
PGresult *result = PQexec(mPgConnection, (const char *) (theSQL.utf8()));
186186

187187
if (PQresultStatus(result) == PGRES_TUPLES_OK)
188188
{
189189
int rowCount = PQntuples(result);
190190
for(int i=0; i < rowCount; i++)
191191
{
192192
QString value = QString::fromUtf8(PQgetvalue(result, i, 0));
193-
if(isCharField)
194-
{
195-
value = "'" + value + "'";
196-
}
197193
QStandardItem *myItem = new QStandardItem(value);
198194
myItem->setEditable(false);
199195
mModelValues->insertRow(mModelValues->rowCount(),myItem);
200196
}
201197
}else
202198
{
203-
QMessageBox::warning(this, tr("Database error"), tr("<p>Failed to get sample of field values using SQL:</p><p>") + sql + "</p><p>Error message was: "+ QString(PQerrorMessage(mPgConnection)) + "</p>");
199+
QMessageBox::warning(this, tr("Database error"), tr("<p>Failed to get sample of field values using SQL:</p><p>") + theSQL + "</p><p>Error message was: "+ QString(PQerrorMessage(mPgConnection)) + "</p>");
204200
}
205201
// free the result set
206202
PQclear(result);
207203
}
208204

205+
void QgsPgQueryBuilder::on_btnSampleValues_clicked()
206+
{
207+
lstValues->setCursor(Qt::WaitCursor);
208+
QString myFieldName = mModelFields->data(lstFields->currentIndex()).toString();
209+
if (myFieldName.isEmpty())
210+
return;
211+
212+
QgsField field = mFieldMap[mModelFields->data(lstFields->currentIndex()).toString()];
213+
bool mActualFieldIsChar = field.typeName().find("char") > -1;
214+
215+
QString sql = "SELECT DISTINCT \"" + myFieldName + "\" " +
216+
"FROM (SELECT \"" + myFieldName + "\" " +
217+
"FROM " + mUri->quotedTablename() + " " +
218+
"LIMIT 5000) AS foo " +
219+
"ORDER BY \"" + myFieldName + "\" "+
220+
"LIMIT 25";
221+
222+
//delete connection mModelValues and lstValues
223+
QStandardItemModel *tmp = new QStandardItemModel();
224+
lstValues->setModel(tmp);
225+
//Clear and fill the mModelValues
226+
fillValues(sql);
227+
lstValues->setModel(mModelValues);
228+
lstValues->setCursor(Qt::ArrowCursor);
229+
//delete the tmp
230+
delete tmp;
231+
232+
}
233+
209234
void QgsPgQueryBuilder::on_btnGetAllValues_clicked()
210235
{
236+
lstValues->setCursor(Qt::WaitCursor);
211237
QString myFieldName = mModelFields->data(lstFields->currentIndex()).toString();
212238
if (myFieldName.isEmpty())
213239
return;
214240

215241
QString sql = "select distinct \"" + myFieldName
216242
+ "\" from " + mUri->quotedTablename() + " order by \"" + myFieldName + "\"";
217-
// clear the values list
218-
mModelValues->clear();
219-
// determine the field type
220-
QgsField field = mFieldMap[myFieldName];
221-
bool isCharField = field.typeName().find("char") > -1;
222-
223-
PGresult *result = PQexec(mPgConnection, (const char *) (sql.utf8()));
224-
225-
if (PQresultStatus(result) == PGRES_TUPLES_OK)
226-
{
227-
int rowCount = PQntuples(result);
228-
229-
lstValues->setCursor(Qt::WaitCursor);
230-
// Block for better performance
231-
mModelValues->blockSignals(true);
232-
lstValues->setUpdatesEnabled(false);
233-
234-
for(int i=0; i < rowCount; i++)
235-
{
236-
QString value = QString::fromUtf8(PQgetvalue(result, i, 0));
237-
if(isCharField)
238-
{
239-
value = "'" + value + "'";
240-
}
241-
QStandardItem *myItem = new QStandardItem(value);
242-
myItem->setEditable(false);
243-
mModelValues->insertRow(mModelValues->rowCount(),myItem);
244-
}
245-
246-
// Unblock for normal use
247-
mModelValues->blockSignals(false);
248-
lstValues->setUpdatesEnabled(true);
249-
// TODO: already sorted, signal emit to refresh model
250-
mModelValues->sort(0);
251-
lstValues->setCursor(Qt::ArrowCursor);
252-
253-
}else
254-
{
255-
QMessageBox::warning(this, tr("Database error"), tr("Failed to get sample of field values") + QString(PQerrorMessage(mPgConnection)) );
256-
}
257-
// free the result set
258-
PQclear(result);
243+
244+
//delete connection mModelValues and lstValues
245+
QStandardItemModel *tmp = new QStandardItemModel();
246+
lstValues->setModel(tmp);
247+
//Clear and fill the mModelValues
248+
fillValues(sql);
249+
lstValues->setModel(mModelValues);
250+
lstValues->setCursor(Qt::ArrowCursor);
251+
//delete the tmp
252+
delete tmp;
259253
}
260254

261255
void QgsPgQueryBuilder::on_btnTest_clicked()
@@ -398,14 +392,41 @@ void QgsPgQueryBuilder::setSql( QString sqlStatement)
398392
txtSQL->setText(sqlStatement);
399393
}
400394

395+
void QgsPgQueryBuilder::on_lstFields_clicked( const QModelIndex &index )
396+
{
397+
if (mPreviousFieldRow != index.row())
398+
{
399+
mPreviousFieldRow = index.row();
400+
401+
// If type is gemetry .. normal users don't want to get values?
402+
QgsField field = mFieldMap[mModelFields->data(lstFields->currentIndex()).toString()];
403+
if (field.typeName().find("geometry") > -1)
404+
{
405+
btnSampleValues->setEnabled(false);
406+
btnGetAllValues->setEnabled(false);
407+
}else
408+
{
409+
btnSampleValues->setEnabled(true);
410+
btnGetAllValues->setEnabled(true);
411+
}
412+
mModelValues->clear();
413+
}
414+
}
415+
401416
void QgsPgQueryBuilder::on_lstFields_doubleClicked( const QModelIndex &index )
402417
{
403418
txtSQL->insert("\"" + mModelFields->data(index).toString() + "\"");
404419
}
405420

406421
void QgsPgQueryBuilder::on_lstValues_doubleClicked( const QModelIndex &index )
407422
{
408-
txtSQL->insert(mModelValues->data(index).toString());
423+
if (mActualFieldIsChar)
424+
{
425+
txtSQL->insert("'" + mModelValues->data(index).toString() + "'");
426+
}else
427+
{
428+
txtSQL->insert(mModelValues->data(index).toString());
429+
}
409430
}
410431

411432
void QgsPgQueryBuilder::on_btnLessEqual_clicked()

‎src/app/qgspgquerybuilder.h

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -87,6 +87,7 @@ class QgsPgQueryBuilder : public QDialog, private Ui::QgsPgQueryBuilderBase {
8787
void on_btnILike_clicked();
8888
QString sql();
8989
void setSql( QString sqlStatement);
90+
void on_lstFields_clicked( const QModelIndex &index );
9091
void on_lstFields_doubleClicked( const QModelIndex &index );
9192
void on_lstValues_doubleClicked( const QModelIndex &index );
9293
void on_btnLessEqual_clicked();
@@ -123,7 +124,9 @@ class QgsPgQueryBuilder : public QDialog, private Ui::QgsPgQueryBuilderBase {
123124
/*!
124125
* Setup models for listviews
125126
*/
126-
void setupListViews();
127+
void setupGuiViews();
128+
void setupLstFieldsModel();
129+
void fillValues(QString theSQL);
127130

128131
/*! Get the number of records that would be returned by the current SQL
129132
* @return Number of records or -1 if an error was encountered
@@ -149,5 +152,9 @@ class QgsPgQueryBuilder : public QDialog, private Ui::QgsPgQueryBuilderBase {
149152
QStandardItemModel *mModelFields;
150153
//! Model for values ListView
151154
QStandardItemModel *mModelValues;
155+
//! Actual field char?
156+
bool mActualFieldIsChar;
157+
//! Previous field row to delete model
158+
int mPreviousFieldRow;
152159
};
153160
#endif //QGSPGQUERYBUILDER_H

‎src/ui/qgspgquerybuilderbase.ui

Lines changed: 45 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -249,26 +249,62 @@
249249
</property>
250250
<item row="2" column="1" >
251251
<widget class="QPushButton" name="btnGetAllValues" >
252+
<property name="toolTip" >
253+
<string>&lt;html>&lt;head>&lt;meta name="qrichtext" content="1" />&lt;style type="text/css">
254+
p, li { white-space: pre-wrap; }
255+
&lt;/style>&lt;/head>&lt;body style=" font-family:'Sans Serif'; font-size:9pt; font-weight:400; font-style:normal;">
256+
&lt;p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;">Retrieve &lt;span style=" font-weight:600;">all&lt;/span> the record in the vector file (&lt;span style=" font-style:italic;">if the table is big, the operation can consume some time&lt;/span>)&lt;/p>&lt;/body>&lt;/html></string>
257+
</property>
252258
<property name="text" >
253259
<string>All</string>
254260
</property>
255261
</widget>
256262
</item>
257263
<item row="2" column="0" >
258264
<widget class="QPushButton" name="btnSampleValues" >
265+
<property name="toolTip" >
266+
<string>&lt;html>&lt;head>&lt;meta name="qrichtext" content="1" />&lt;style type="text/css">
267+
p, li { white-space: pre-wrap; }
268+
&lt;/style>&lt;/head>&lt;body style=" font-family:'Sans Serif'; font-size:9pt; font-weight:400; font-style:normal;">
269+
&lt;p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;">Take a &lt;span style=" font-weight:600;">sample&lt;/span> of records in the vector file&lt;/p>&lt;/body>&lt;/html></string>
270+
</property>
259271
<property name="text" >
260272
<string>Sample</string>
261273
</property>
262274
</widget>
263275
</item>
264276
<item rowspan="2" row="0" column="0" colspan="2" >
265-
<widget class="QListView" name="lstValues" />
277+
<widget class="QListView" name="lstValues" >
278+
<property name="whatsThis" >
279+
<string>&lt;html>&lt;head>&lt;meta name="qrichtext" content="1" />&lt;style type="text/css">
280+
p, li { white-space: pre-wrap; }
281+
&lt;/style>&lt;/head>&lt;body style=" font-family:'Sans Serif'; font-size:9pt; font-weight:400; font-style:normal;">
282+
&lt;p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;">List of values for the current field.&lt;/p>&lt;/body>&lt;/html></string>
283+
</property>
284+
<property name="autoFillBackground" >
285+
<bool>true</bool>
286+
</property>
287+
<property name="selectionBehavior" >
288+
<enum>QAbstractItemView::SelectRows</enum>
289+
</property>
290+
<property name="uniformItemSizes" >
291+
<bool>true</bool>
292+
</property>
293+
</widget>
266294
</item>
267295
</layout>
268296
</widget>
269297
</item>
270298
<item row="1" column="0" >
271299
<widget class="QGroupBox" name="groupBox1" >
300+
<property name="sizePolicy" >
301+
<sizepolicy>
302+
<hsizetype>0</hsizetype>
303+
<vsizetype>0</vsizetype>
304+
<horstretch>0</horstretch>
305+
<verstretch>0</verstretch>
306+
</sizepolicy>
307+
</property>
272308
<property name="title" >
273309
<string>Fields</string>
274310
</property>
@@ -280,7 +316,14 @@
280316
<number>6</number>
281317
</property>
282318
<item row="0" column="0" >
283-
<widget class="QListView" name="lstFields" />
319+
<widget class="QListView" name="lstFields" >
320+
<property name="whatsThis" >
321+
<string>&lt;html>&lt;head>&lt;meta name="qrichtext" content="1" />&lt;style type="text/css">
322+
p, li { white-space: pre-wrap; }
323+
&lt;/style>&lt;/head>&lt;body style=" font-family:'Sans Serif'; font-size:9pt; font-weight:400; font-style:normal;">
324+
&lt;p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;">List of fields in this vector file&lt;/p>&lt;/body>&lt;/html></string>
325+
</property>
326+
</widget>
284327
</item>
285328
</layout>
286329
</widget>

0 commit comments

Comments
 (0)