Skip to content

Commit f86a0af

Browse files
author
jef
committedMar 13, 2008
- Support NULL database values (fixes #987 and #988)
 - check for NULL values on load - show "NULL" in attribute table and identify results - support entry of "NULL" in attribute table or dialog - free result of PQexec in postgres provider (fixes memory leaks) git-svn-id: http://svn.osgeo.org/qgis/trunk@8217 c8812cc2-4d05-0410-92ff-de0c093fc19c
·
ltr-3_40Before-merge-new_vector_api
1 parent 9e7a287 commit f86a0af

File tree

5 files changed

+131
-63
lines changed

5 files changed

+131
-63
lines changed
 

‎src/app/qgsattributedialog.cpp

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -67,7 +67,11 @@ QgsAttributeDialog::~QgsAttributeDialog()
6767

6868
QString QgsAttributeDialog::value(int row)
6969
{
70-
return mTable->item(row,1)->text();
70+
QString val = mTable->item(row,1)->text();
71+
if(val=="NULL")
72+
return QString::null;
73+
else
74+
return mTable->item(row,1)->text();
7175
}
7276

7377
bool QgsAttributeDialog::isDirty(int row)

‎src/app/qgsattributetable.cpp

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -461,7 +461,10 @@ bool QgsAttributeTable::commitChanges(QgsVectorLayer* layer)
461461
fieldIndex = provider->indexFromFieldName(record_it.key());
462462
if(fieldIndex != -1)
463463
{
464-
newAttMap.insert(fieldIndex, record_it.value());
464+
if( record_it.value()=="NULL" )
465+
newAttMap.insert(fieldIndex, QVariant(QString::null) );
466+
else
467+
newAttMap.insert(fieldIndex, record_it.value());
465468
}
466469
else
467470
{
@@ -572,7 +575,7 @@ void QgsAttributeTable::putFeatureInTable(int row, QgsFeature& fet)
572575
for (it = attr.begin(); it != attr.end(); ++it)
573576
{
574577
// get the field values
575-
setText(row, h++, it->toString());
578+
setText(row, h++, it->isNull() ? "NULL" : it->toString());
576579
}
577580
}
578581

‎src/app/qgsmaptoolidentify.cpp

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -303,7 +303,7 @@ void QgsMapToolIdentify::identifyVectorLayer(QgsVectorLayer* layer, const QgsPoi
303303
{
304304
featureNode->setText(1, it->toString());
305305
}
306-
mResults->addAttribute(featureNode, fields[it.key()].name(), it->toString());
306+
mResults->addAttribute(featureNode, fields[it.key()].name(), it->isNull() ? "NULL" : it->toString());
307307
}
308308

309309
// Calculate derived attributes and insert:

‎src/providers/postgres/qgspostgresprovider.cpp

Lines changed: 117 additions & 59 deletions
Original file line numberDiff line numberDiff line change
@@ -207,7 +207,7 @@ QgsPostgresProvider::QgsPostgresProvider(QString const & uri)
207207
// Set the postgresql message level so that we don't get the
208208
// 'there is no transaction in progress' warning.
209209
#ifndef QGISDEBUG
210-
PQexec(connection, QString("set client_min_messages to error").toUtf8());
210+
PQexecNR(connection, QString("set client_min_messages to error").toUtf8());
211211
#endif
212212

213213
// Kick off the long running threads
@@ -369,7 +369,7 @@ bool QgsPostgresProvider::getNextFeature(QgsFeature& feature)
369369

370370
PQclear(queryResult);
371371
if (ready)
372-
PQexec(connection, QString("end work").toUtf8());
372+
PQexecNR(connection, QString("end work").toUtf8());
373373
ready = false;
374374
return false;
375375
}
@@ -393,17 +393,29 @@ bool QgsPostgresProvider::getNextFeature(QgsFeature& feature)
393393
for(; name_it != mFetchAttributeNames.end(); ++name_it, ++index_it)
394394
{
395395
QString val;
396+
396397
if( (*name_it) == primaryKey)
397398
{
398399
val = QString::number(oid);
399400
}
400401
else
401402
{
402-
val = QString::fromUtf8(PQgetvalue(queryResult, row, PQfnumber(queryResult,quotedIdentifier(*name_it).toUtf8())));
403+
int fn = PQfnumber(queryResult,quotedIdentifier(*name_it).toUtf8());
404+
405+
if( !PQgetisnull(queryResult, row, fn) )
406+
val = QString::fromUtf8(PQgetvalue(queryResult, row, fn));
407+
else
408+
val = QString::null;
403409
}
404410

405-
switch (attributeFields[*index_it].type())
411+
if( val.isNull() )
412+
{
413+
mFeatureQueue.back().addAttribute(*index_it, val);
414+
}
415+
else
406416
{
417+
switch (attributeFields[*index_it].type())
418+
{
407419
case QVariant::LongLong:
408420
mFeatureQueue.back().addAttribute(*index_it, val.toLongLong());
409421
break;
@@ -418,6 +430,7 @@ bool QgsPostgresProvider::getNextFeature(QgsFeature& feature)
418430
break;
419431
default:
420432
assert(0 && "unsupported field type");
433+
}
421434
}
422435
}
423436

@@ -559,11 +572,11 @@ void QgsPostgresProvider::select(QgsAttributeList fetchAttributes,
559572

560573
// set up the cursor
561574
if(ready){
562-
PQexec(connection, QString("end work").toUtf8());
575+
PQexecNR(connection, QString("end work").toUtf8());
563576
}
564-
PQexec(connection,QString("begin work").toUtf8());
577+
PQexecNR(connection,QString("begin work").toUtf8());
565578
ready = true;
566-
PQexec(connection, declare.toUtf8());
579+
PQexecNR(connection, declare.toUtf8());
567580

568581
while(!mFeatureQueue.empty())
569582
{
@@ -614,18 +627,18 @@ bool QgsPostgresProvider::getFeatureAtId(int featureId,
614627

615628
QgsDebugMsg("Selecting feature using: " + sql);
616629

617-
PQexec(connection,QString("begin work").toUtf8());
630+
PQexecNR(connection,QString("begin work").toUtf8());
618631

619632
// execute query
620-
PQexec(connection, sql.toUtf8());
633+
PQexecNR(connection, sql.toUtf8());
621634

622635
PGresult *res = PQexec(connection, QString("fetch forward 1 from qgisfid").toUtf8());
623636

624637
int rows = PQntuples(res);
625638
if (rows == 0)
626639
{
627640
PQclear(res);
628-
PQexec(connection, QString("end work").toUtf8());
641+
PQexecNR(connection, QString("end work").toUtf8());
629642
QgsDebugMsg("feature " + QString::number(featureId) + " not found");
630643
return FALSE;
631644
}
@@ -642,17 +655,29 @@ bool QgsPostgresProvider::getFeatureAtId(int featureId,
642655
for(namesIt = attributeNames.begin(); namesIt != attributeNames.end(); ++namesIt, ++it)
643656
{
644657
QString val;
658+
645659
if( (*namesIt) == primaryKey)
646660
{
647661
val = QString::number(oid);
648662
}
649663
else
650664
{
651-
val = QString::fromUtf8(PQgetvalue(res, 0, PQfnumber(res,quotedIdentifier(*namesIt).toUtf8())));
665+
int fn = PQfnumber(res,quotedIdentifier(*namesIt).toUtf8());
666+
667+
if( PQgetisnull(res, 0, fn) )
668+
val = QString::fromUtf8(PQgetvalue(res, 0, fn));
669+
else
670+
val = QString::null;
652671
}
653672

654-
switch (attributeFields[*it].type())
673+
if( val.isNull() )
674+
{
675+
feature.addAttribute(*it, val);
676+
}
677+
else
655678
{
679+
switch (attributeFields[*it].type())
680+
{
656681
case QVariant::LongLong:
657682
feature.addAttribute(*it, val.toLongLong());
658683
break;
@@ -667,6 +692,7 @@ bool QgsPostgresProvider::getFeatureAtId(int featureId,
667692
break;
668693
default:
669694
assert(0 && "unsupported field type");
695+
}
670696
}
671697
}
672698

@@ -684,7 +710,7 @@ bool QgsPostgresProvider::getFeatureAtId(int featureId,
684710
}
685711

686712
PQclear(res);
687-
PQexec(connection, QString("end work").toUtf8());
713+
PQexecNR(connection, QString("end work").toUtf8());
688714

689715
return TRUE;
690716
}
@@ -745,7 +771,7 @@ QString QgsPostgresProvider::dataComment() const
745771
void QgsPostgresProvider::reset()
746772
{
747773
QString move = "move 0 in qgisf"; //move cursor to first record
748-
PQexec(connection, move.toUtf8());
774+
PQexecNR(connection, move.toUtf8());
749775
mFeatureQueue.empty();
750776
loadFields();
751777
}
@@ -1660,20 +1686,22 @@ QVariant QgsPostgresProvider::getDefaultValue(int fieldId)
16601686

16611687
QString fieldName = attributeFields[fieldId].name();
16621688

1663-
QString sql("SELECT column_default FROM "
1664-
"information_schema.columns WHERE "
1665-
"column_default IS NOT NULL AND "
1666-
"table_schema = '" + mSchemaName + "' AND "
1667-
"table_name = '" + mTableName + "' AND "
1668-
"column_name = '" + fieldName + "'");
1689+
QString sql("SELECT column_default FROM"
1690+
" information_schema.columns WHERE"
1691+
" column_default IS NOT NULL"
1692+
" AND table_schema = " + quotedValue(mSchemaName) +
1693+
" AND table_name = " + quotedValue(mTableName) +
1694+
" AND column_name = " + quotedValue(fieldName) );
16691695

1670-
QString defaultValue("");
1696+
QVariant defaultValue = QString::null;
16711697

16721698
PGresult* result = PQexec(connection, sql.toUtf8());
16731699

1674-
if (PQntuples(result) == 1)
1700+
if (PQntuples(result)==1 && !PQgetisnull(result, 0, 0) )
16751701
defaultValue = QString::fromUtf8(PQgetvalue(result, 0, 0));
16761702

1703+
QgsDebugMsg( QString("defaultValue for %1 is NULL: %2").arg(fieldId).arg( defaultValue.isNull() ) );
1704+
16771705
PQclear(result);
16781706

16791707
return defaultValue;
@@ -1735,14 +1763,20 @@ QString QgsPostgresProvider::postgisVersion(PGconn *connection)
17351763

17361764
QByteArray QgsPostgresProvider::paramValue(QString fieldValue, const QString &defaultValue) const
17371765
{
1738-
if( fieldValue=="NULL" )
1739-
return 0;
1766+
if( fieldValue.isNull() )
1767+
return QByteArray(0); // QByteArray(0).isNull() is true
17401768

1741-
if( fieldValue==defaultValue && !defaultValue.isEmpty() )
1769+
if( fieldValue==defaultValue && !defaultValue.isNull() )
17421770
{
17431771
PGresult *result = PQexec( connection, QString("select %1").arg(defaultValue).toUtf8() );
1744-
fieldValue = QString::fromUtf8(PQgetvalue(result,0,0));
1745-
PQclear(result);
1772+
if( PQgetisnull(result, 0, 0) ) {
1773+
PQclear(result);
1774+
return QByteArray(0); // QByteArray(0).isNull() is true
1775+
} else {
1776+
QString val = QString::fromUtf8(PQgetvalue(result,0,0));
1777+
PQclear(result);
1778+
return val.toUtf8();
1779+
}
17461780
}
17471781

17481782
return fieldValue.toUtf8();
@@ -1756,7 +1790,7 @@ bool QgsPostgresProvider::addFeatures(QgsFeatureList & flist)
17561790
bool returnvalue=true;
17571791

17581792
try {
1759-
PQexec(connection,QString("BEGIN").toUtf8());
1793+
PQexecNR(connection,QString("BEGIN").toUtf8());
17601794

17611795
// Prepare the INSERT statement
17621796
QString insert = QString("INSERT INTO %1(%2,%3")
@@ -1784,11 +1818,9 @@ bool QgsPostgresProvider::addFeatures(QgsFeatureList & flist)
17841818

17851819
QgsDebugMsg("Checking field against: " + fieldname);
17861820

1787-
if( fieldname.isEmpty() || fieldname==geometryColumn || fieldname==primaryKey || it->isNull() )
1821+
if( fieldname.isEmpty() || fieldname==geometryColumn || fieldname==primaryKey )
17881822
continue;
17891823

1790-
QString fieldvalue = it->toString();
1791-
17921824
int i;
17931825
for(i=1; i<flist.size(); i++)
17941826
{
@@ -1798,7 +1830,7 @@ bool QgsPostgresProvider::addFeatures(QgsFeatureList & flist)
17981830
if( thisit == attributevec.end() )
17991831
break;
18001832

1801-
if( thisit->toString() != fieldvalue )
1833+
if( *thisit!=*it )
18021834
break;
18031835
}
18041836

@@ -1808,13 +1840,13 @@ bool QgsPostgresProvider::addFeatures(QgsFeatureList & flist)
18081840

18091841
if( i==flist.size() )
18101842
{
1811-
if( fieldvalue=="NULL" || (fieldvalue==defVal && !defVal.isEmpty()) )
1843+
if( !defVal.isNull() && *it==defVal) {
18121844
{
1813-
values += "," + fieldvalue;
1845+
values += "," + defVal;
18141846
}
18151847
else
18161848
{
1817-
values += "," + quotedValue(fieldvalue);
1849+
values += "," + quotedValue( it->toString() );
18181850
}
18191851
}
18201852
else
@@ -1832,6 +1864,8 @@ bool QgsPostgresProvider::addFeatures(QgsFeatureList & flist)
18321864
if(stmt==0 || PQresultStatus(stmt)==PGRES_FATAL_ERROR)
18331865
throw PGException(stmt);
18341866

1867+
PQclear(stmt);
1868+
18351869
int primaryKeyHighWater = maxPrimaryKeyValue();
18361870
const char **param = new const char *[ fieldId.size()+2 ];
18371871

@@ -1853,27 +1887,30 @@ bool QgsPostgresProvider::addFeatures(QgsFeatureList & flist)
18531887
for(int i=0; i<fieldId.size(); i++)
18541888
{
18551889
qparam.append( paramValue( attributevec[ fieldId[i] ].toString(), defaultValue[i] ) );
1856-
param[i+2] = qparam[i+2];
1890+
if( qparam[i+2].isNull() )
1891+
param[i+2] = 0;
1892+
else
1893+
param[i+2] = qparam[i+2];
18571894
}
18581895

18591896
PGresult *result = PQexecPrepared(connection, "addfeatures", fieldId.size()+2, param, NULL, NULL, 0);
18601897
if( result==0 || PQresultStatus(result)==PGRES_FATAL_ERROR )
18611898
{
1862-
PQclear(stmt);
18631899
delete param;
18641900
throw PGException(result);
18651901
}
18661902

18671903
PQclear(result);
18681904
}
18691905

1870-
PQclear(stmt);
1906+
PQexecNR(connection,QString("COMMIT").toUtf8());
1907+
PQexecNR(connection,QString("DEALLOCATE addfeatures").toUtf8());
18711908
delete param;
1872-
1873-
PQexec(connection,QString("COMMIT").toUtf8());
18741909
} catch(PGException &e) {
18751910
e.showErrorMessage( tr("Error while adding features") );
1876-
PQexec(connection,QString("ROLLBACK").toUtf8());
1911+
PQexecNR(connection,QString("ROLLBACK").toUtf8());
1912+
1913+
PQexecNR(connection,QString("DEALLOCATE addfeatures").toUtf8());
18771914
returnvalue = false;
18781915
}
18791916

@@ -1886,7 +1923,7 @@ bool QgsPostgresProvider::deleteFeatures(const QgsFeatureIds & id)
18861923
bool returnvalue=true;
18871924

18881925
try {
1889-
PQexec(connection,QString("BEGIN").toUtf8());
1926+
PQexecNR(connection,QString("BEGIN").toUtf8());
18901927

18911928
for(QgsFeatureIds::const_iterator it=id.begin();it!=id.end();++it) {
18921929
QString sql("DELETE FROM "+mSchemaTableName+" WHERE "+quotedIdentifier(primaryKey)+"="+QString::number(*it));
@@ -1900,10 +1937,10 @@ bool QgsPostgresProvider::deleteFeatures(const QgsFeatureIds & id)
19001937
PQclear(result);
19011938
}
19021939

1903-
PQexec(connection,QString("COMMIT").toUtf8());
1940+
PQexecNR(connection,QString("COMMIT").toUtf8());
19041941
} catch(PGException &e) {
19051942
e.showErrorMessage( tr("Error while deleting features") );
1906-
PQexec(connection,QString("ROLLBACK").toUtf8());
1943+
PQexecNR(connection,QString("ROLLBACK").toUtf8());
19071944
returnvalue = false;
19081945
}
19091946
reset();
@@ -1915,7 +1952,7 @@ bool QgsPostgresProvider::addAttributes(const QgsNewAttributesMap & name)
19151952
bool returnvalue=true;
19161953

19171954
try {
1918-
PQexec(connection,QString("BEGIN").toUtf8());
1955+
PQexecNR(connection,QString("BEGIN").toUtf8());
19191956

19201957
for(QgsNewAttributesMap::const_iterator iter=name.begin();iter!=name.end();++iter)
19211958
{
@@ -1930,10 +1967,10 @@ bool QgsPostgresProvider::addAttributes(const QgsNewAttributesMap & name)
19301967
PQclear(result);
19311968
}
19321969

1933-
PQexec(connection,QString("COMMIT").toUtf8());
1970+
PQexecNR(connection,QString("COMMIT").toUtf8());
19341971
} catch(PGException &e) {
19351972
e.showErrorMessage( tr("Error while adding attributes") );
1936-
PQexec(connection,QString("ROLLBACK").toUtf8());
1973+
PQexecNR(connection,QString("ROLLBACK").toUtf8());
19371974
returnvalue = false;
19381975
}
19391976

@@ -1946,7 +1983,7 @@ bool QgsPostgresProvider::deleteAttributes(const QgsAttributeIds& ids)
19461983
bool returnvalue=true;
19471984

19481985
try {
1949-
PQexec(connection,QString("BEGIN").toUtf8());
1986+
PQexecNR(connection,QString("BEGIN").toUtf8());
19501987

19511988
for(QgsAttributeIds::const_iterator iter=ids.begin();iter != ids.end();++iter)
19521989
{
@@ -1967,10 +2004,10 @@ bool QgsPostgresProvider::deleteAttributes(const QgsAttributeIds& ids)
19672004
attributeFields.remove(*iter);
19682005
}
19692006

1970-
PQexec(connection,QString("COMMIT").toUtf8());
2007+
PQexecNR(connection,QString("COMMIT").toUtf8());
19712008
} catch(PGException &e) {
19722009
e.showErrorMessage( tr("Error while deleting attributes") );
1973-
PQexec(connection,QString("ROLLBACK").toUtf8());
2010+
PQexecNR(connection,QString("ROLLBACK").toUtf8());
19742011
returnvalue = false;
19752012
}
19762013

@@ -1983,7 +2020,7 @@ bool QgsPostgresProvider::changeAttributeValues(const QgsChangedAttributesMap &
19832020
bool returnvalue=true;
19842021

19852022
try {
1986-
PQexec(connection,QString("BEGIN").toUtf8());
2023+
PQexecNR(connection,QString("BEGIN").toUtf8());
19872024

19882025
// cycle through the features
19892026
for(QgsChangedAttributesMap::const_iterator iter=attr_map.begin();iter!=attr_map.end();++iter)
@@ -2016,10 +2053,10 @@ bool QgsPostgresProvider::changeAttributeValues(const QgsChangedAttributesMap &
20162053
}
20172054
}
20182055

2019-
PQexec(connection,QString("COMMIT").toUtf8());
2056+
PQexecNR(connection,QString("COMMIT").toUtf8());
20202057
} catch(PGException &e) {
20212058
e.showErrorMessage( tr("Error while changing attributes") );
2022-
PQexec(connection,QString("ROLLBACK").toUtf8());
2059+
PQexecNR(connection,QString("ROLLBACK").toUtf8());
20232060
returnvalue = false;
20242061
}
20252062

@@ -2048,7 +2085,7 @@ bool QgsPostgresProvider::changeGeometryValues(QgsGeometryMap & geometry_map)
20482085

20492086
try {
20502087
// Start the PostGIS transaction
2051-
PQexec(connection,QString("BEGIN").toUtf8());
2088+
PQexecNR(connection,QString("BEGIN").toUtf8());
20522089

20532090
for(QgsGeometryMap::iterator iter = geometry_map.begin();
20542091
iter != geometry_map.end();
@@ -2080,10 +2117,10 @@ bool QgsPostgresProvider::changeGeometryValues(QgsGeometryMap & geometry_map)
20802117
} // if (*iter)
20812118
} // for each feature
20822119

2083-
PQexec(connection,QString("COMMIT").toUtf8());
2120+
PQexecNR(connection,QString("COMMIT").toUtf8());
20842121
} catch(PGException &e) {
20852122
e.showErrorMessage( tr("Error while changing attributes") );
2086-
PQexec(connection,QString("ROLLBACK").toUtf8());
2123+
PQexecNR(connection,QString("ROLLBACK").toUtf8());
20872124
returnvalue = false;
20882125
}
20892126

@@ -2364,16 +2401,16 @@ bool QgsPostgresProvider::deduceEndian()
23642401

23652402
// get the same value using a binary cursor
23662403

2367-
PQexec(connection,QString("begin work").toUtf8());
2404+
PQexecNR(connection,QString("begin work").toUtf8());
23682405
QString oidDeclare = "declare oidcursor binary cursor for select regclass('" + mSchemaTableName + "')::oid";
23692406
// set up the cursor
2370-
PQexec(connection, oidDeclare.toUtf8());
2407+
PQexecNR(connection, oidDeclare.toUtf8());
23712408
QString fetch = "fetch forward 1 from oidcursor";
23722409

23732410
QgsDebugMsg("Fetching a record and attempting to get check endian-ness");
23742411

23752412
PGresult *fResult = PQexec(connection, fetch.toUtf8());
2376-
PQexec(connection, QString("end work").toUtf8());
2413+
PQexecNR(connection, QString("end work").toUtf8());
23772414
swapEndian = true;
23782415
if(PQntuples(fResult) > 0){
23792416
// get the oid value from the binary cursor
@@ -2548,11 +2585,32 @@ QString QgsPostgresProvider::quotedIdentifier( QString ident )
25482585

25492586
QString QgsPostgresProvider::quotedValue( QString value )
25502587
{
2588+
if( value.isNull() )
2589+
return "NULL";
2590+
25512591
// FIXME: use PQescapeStringConn
25522592
value.replace("'", "''");
25532593
return value.prepend("'").append("'");
25542594
}
25552595

2596+
void QgsPostgresProvider::PQexecNR(PGconn *conn, const char *query)
2597+
{
2598+
PGresult *res = PQexec(conn, query);
2599+
if(res)
2600+
{
2601+
QgsDebugMsg( QString("Query: %1 returned %2 [%3]")
2602+
.arg(query)
2603+
.arg(PQresStatus(PQresultStatus(res)))
2604+
.arg(PQresultErrorMessage(res))
2605+
);
2606+
PQclear(res);
2607+
}
2608+
else
2609+
{
2610+
QgsDebugMsg( QString("Query: %1 returned no result buffer").arg(query) );
2611+
}
2612+
}
2613+
25562614
void QgsPostgresProvider::showMessageBox(const QString& title,
25572615
const QString& text)
25582616
{

‎src/providers/postgres/qgspostgresprovider.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -595,6 +595,9 @@ class QgsPostgresProvider:public QgsVectorDataProvider
595595

596596
void appendGeomString(QgsGeometry *geom, QString &geomParam) const;
597597
QByteArray paramValue(QString fieldvalue, const QString &defaultValue) const;
598+
599+
// run a query and free result buffer
600+
static void PQexecNR(PGconn *conn, const char *query);
598601
};
599602

600603
#endif

0 commit comments

Comments
 (0)
Please sign in to comment.