Skip to content

Commit 509223b

Browse files
author
jef
committedMar 31, 2008
postgres provider update
- remove some unnecessary includes - add private PQexec method with additional debug output - update all changed attributes of a feature at once - fix quoting and beautify creation of SQL statements - replace assertion on unexpected field types with useful debug output and consider values as NULL git-svn-id: http://svn.osgeo.org/qgis/trunk@8307 c8812cc2-4d05-0410-92ff-de0c093fc19c
·
ltr-3_40Before-merge-new_vector_api
1 parent 126fa86 commit 509223b

File tree

2 files changed

+275
-320
lines changed

2 files changed

+275
-320
lines changed
 

‎src/providers/postgres/qgspostgresprovider.cpp

Lines changed: 272 additions & 320 deletions
Original file line numberDiff line numberDiff line change
@@ -24,16 +24,11 @@
2424
#include <netinet/in.h>
2525
#endif
2626

27-
#include <fstream>
28-
#include <iostream>
2927
#include <cassert>
3028

31-
#include <QStringList>
3229
#include <QApplication>
3330
#include <QEvent>
3431
#include <QCustomEvent>
35-
#include <QTextOStream>
36-
3732

3833
#include <qgis.h>
3934
#include <qgsapplication.h>
@@ -494,7 +489,12 @@ bool QgsPostgresProvider::getFeature(PGresult *queryResult, int row, bool fetchG
494489
feature.addAttribute(*it, val);
495490
break;
496491
default:
497-
assert(0 && "unsupported field type");
492+
QgsDebugMsg( QString("feature %1, field %2, value '%3': unexpected variant type %4 considered as NULL")
493+
.arg( oid )
494+
.arg( fld.name() )
495+
.arg( val )
496+
.arg( fld.type() ) );
497+
feature.addAttribute(*it, QVariant(QString::null));
498498
}
499499
}
500500
else
@@ -762,22 +762,21 @@ void QgsPostgresProvider::loadFields()
762762
QgsDebugMsg("Loading fields for table " + mTableName);
763763

764764
// Get the relation oid for use in later queries
765-
QString sql = "SELECT regclass(" + quotedValue(mSchemaTableName) + ")::oid";
765+
QString sql = QString("SELECT regclass(%1)::oid").arg( quotedValue(mSchemaTableName) );
766766
PGresult *tresult= PQexec(connection, sql.toUtf8());
767767
QString tableoid = QString::fromUtf8(PQgetvalue(tresult, 0, 0));
768768
PQclear(tresult);
769769

770770
// Get the table description
771-
sql = "SELECT description FROM pg_description WHERE "
772-
"objoid = " + tableoid + " AND objsubid = 0";
771+
sql = QString("SELECT description FROM pg_description WHERE objoid=%1 AND objsubid=0").arg( tableoid );
773772
tresult = PQexec(connection, sql.toUtf8());
774773
if (PQntuples(tresult) > 0)
775774
mDataComment = QString::fromUtf8(PQgetvalue(tresult, 0, 0));
776775
PQclear(tresult);
777776

778777
// Populate the field vector for this layer. The field vector contains
779778
// field name, type, length, and precision (if numeric)
780-
sql = "select * from " + mSchemaTableName + " limit 0";
779+
sql = QString("select * from %1 limit 0").arg ( mSchemaTableName );
781780

782781
PGresult *result = PQexec(connection, sql.toUtf8());
783782
//--std::cout << "Field: Name, Type, Size, Modifier:" << std::endl;
@@ -794,24 +793,26 @@ void QgsPostgresProvider::loadFields()
794793
int fieldModifier = PQfmod(result, i);
795794
QString fieldComment("");
796795

797-
sql = "SELECT typname, typlen FROM pg_type WHERE "
798-
"oid="+typOid; // just oid; needs more work to support array type
799-
// "oid = (SELECT Distinct typelem FROM pg_type WHERE " //needs DISTINCT to guard against 2 or more rows on int2
800-
// "typelem = " + typOid + " AND typlen = -1)";
796+
sql = QString("SELECT typname,typlen FROM pg_type WHERE oid=%1").arg(typOid);
797+
// just oid; needs more work to support array type
798+
// "oid = (SELECT Distinct typelem FROM pg_type WHERE " //needs DISTINCT to guard against 2 or more rows on int2
799+
// "typelem = " + typOid + " AND typlen = -1)";
801800

802801
PGresult* oidResult = PQexec(connection, sql.toUtf8());
803802
QString fieldTypeName = QString::fromUtf8(PQgetvalue(oidResult, 0, 0));
804803
QString fieldSize = QString::fromUtf8(PQgetvalue(oidResult, 0, 1));
805804
PQclear(oidResult);
806805

807-
sql = "SELECT attnum FROM pg_attribute WHERE "
808-
"attrelid = " + tableoid + " AND attname = " + quotedValue(fieldName);
806+
sql = QString("SELECT attnum FROM pg_attribute WHERE attrelid=%1 AND attname=%2")
807+
.arg( tableoid ).arg( quotedValue(fieldName) );
808+
809809
PGresult *tresult = PQexec(connection, sql.toUtf8());
810810
QString attnum = QString::fromUtf8(PQgetvalue(tresult, 0, 0));
811811
PQclear(tresult);
812812

813-
sql = "SELECT description FROM pg_description WHERE "
814-
"objoid = " + tableoid + " AND objsubid = " + attnum;
813+
sql = QString("SELECT description FROM pg_description WHERE objoid=%1 AND objsubid=%2")
814+
.arg( tableoid ).arg( attnum );
815+
815816
tresult = PQexec(connection, sql.toUtf8());
816817
if (PQntuples(tresult) > 0)
817818
fieldComment = QString::fromUtf8(PQgetvalue(tresult, 0, 0));
@@ -864,8 +865,8 @@ QString QgsPostgresProvider::getPrimaryKey()
864865
// can be used as a key into the table. Primary keys are always
865866
// unique indices, so we catch them as well.
866867

867-
QString sql ="select indkey from pg_index where indisunique = 't' and "
868-
"indrelid = regclass(" + quotedValue(mSchemaTableName) + ")::oid";
868+
QString sql = QString("select indkey from pg_index where indisunique='t' and indrelid=regclass(%1)::oid")
869+
.arg( quotedValue(mSchemaTableName) );
869870

870871
QgsDebugMsg("Getting unique index using '" + sql + "'");
871872

@@ -885,7 +886,8 @@ QString QgsPostgresProvider::getPrimaryKey()
885886
// If the relation is a view try to find a suitable column to use as
886887
// the primary key.
887888

888-
sql = "SELECT relkind FROM pg_class WHERE oid = regclass(" + quotedValue(mSchemaTableName) + ")::oid";
889+
sql = QString("SELECT relkind FROM pg_class WHERE oid=regclass(%1)::oid")
890+
.arg( quotedValue(mSchemaTableName) );
889891
PGresult* tableType = executeDbCommand(connection, sql);
890892
QString type = QString::fromUtf8(PQgetvalue(tableType, 0, 0));
891893
PQclear(tableType);
@@ -898,8 +900,8 @@ QString QgsPostgresProvider::getPrimaryKey()
898900

899901
// If there is an oid on the table, use that instead,
900902
// otherwise give up
901-
sql = "SELECT attname FROM pg_attribute WHERE attname = 'oid' AND "
902-
"attrelid = regclass(" + quotedValue(mSchemaTableName) + ")";
903+
sql = QString("SELECT attname FROM pg_attribute WHERE attname='oid' AND attrelid=regclass(%1)")
904+
.arg( quotedValue(mSchemaTableName) );
903905

904906
PGresult* oidCheck = executeDbCommand(connection, sql);
905907

@@ -947,9 +949,8 @@ QString QgsPostgresProvider::getPrimaryKey()
947949
if (columns.count() == 1)
948950
{
949951
// Get the column name and data type
950-
sql = "select attname, pg_type.typname from pg_attribute, pg_type where "
951-
"atttypid = pg_type.oid and attnum = " +
952-
col + " and attrelid = regclass(" + quotedValue(mSchemaTableName) + ")";
952+
sql = QString("select attname,pg_type.typname from pg_attribute,pg_type where atttypid=pg_type.oid and attnum=%1 and attrelid=regclass(%2)")
953+
.arg( col ).arg( quotedValue(mSchemaTableName) );
953954
PGresult* types = executeDbCommand(connection, sql);
954955

955956
if( PQntuples(types) > 0 )
@@ -974,10 +975,10 @@ QString QgsPostgresProvider::getPrimaryKey()
974975
}
975976
else
976977
{
977-
sql = "select attname from pg_attribute, pg_type where "
978-
"atttypid = pg_type.oid and attnum in (" +
979-
col.replace(" ", ",")
980-
+ ") and attrelid = regclass(" + quotedValue(mSchemaTableName) + ")::oid";
978+
sql = QString("select attname from pg_attribute, pg_type where atttypid=pg_type.oid and attnum in (%1) and attrelid=regclass(%2)::oid")
979+
.arg( col.replace(" ", ",") )
980+
.arg( quotedValue(mSchemaTableName) );
981+
981982
PGresult* types = executeDbCommand(connection, sql);
982983
QString colNames;
983984
int numCols = PQntuples(types);
@@ -1011,8 +1012,7 @@ QString QgsPostgresProvider::getPrimaryKey()
10111012
{
10121013
// If there is an oid on the table, use that instead,
10131014
// otherwise give up
1014-
sql = "select attname from pg_attribute where attname = 'oid' and "
1015-
"attrelid = regclass('" + mSchemaTableName + "')::oid";
1015+
sql = QString("select attname from pg_attribute where attname='oid' and attrelid=regclass(%1)::oid").arg( quotedValue(mSchemaTableName) );
10161016
PGresult* oidCheck = executeDbCommand(connection, sql);
10171017

10181018
if (PQntuples(oidCheck) != 0)
@@ -1079,7 +1079,7 @@ QString QgsPostgresProvider::chooseViewColumn(const tableCols& cols)
10791079

10801080
// Get the oid from pg_class for the given schema.relation for use
10811081
// in subsequent queries.
1082-
sql = "select regclass('" + quotedIdentifier(schemaName) + "." + quotedIdentifier(tableName) + "')::oid";
1082+
sql = QString("select regclass(%1)::oid").arg( quotedValue( quotedIdentifier(schemaName) + "." + quotedIdentifier(tableName) ) );
10831083
PGresult* result = PQexec(connection, sql.toUtf8());
10841084
QString rel_oid;
10851085
if (PQntuples(result) == 1)
@@ -1093,7 +1093,8 @@ QString QgsPostgresProvider::chooseViewColumn(const tableCols& cols)
10931093
QgsDebugMsg("Relation " + schemaName + "." + tableName +
10941094
" doesn't exist in the pg_class table."
10951095
"This shouldn't happen and is odd.");
1096-
assert(0);
1096+
PQclear(result);
1097+
continue;
10971098
}
10981099
PQclear(result);
10991100

@@ -1105,12 +1106,10 @@ QString QgsPostgresProvider::chooseViewColumn(const tableCols& cols)
11051106
// on it.
11061107
// 3) the constraint applies just to the column of interest (i.e.,
11071108
// it isn't a constraint over multiple columns.
1108-
sql = "select * from pg_constraint where conkey[1] = "
1109-
"(select attnum from pg_attribute where attname = '" + tableCol + "' "
1110-
"and attrelid = " + rel_oid + ")"
1111-
"and conrelid = " + rel_oid + " "
1112-
"and (contype = 'p' or contype = 'u') "
1113-
"and array_dims(conkey) = '[1:1]'";
1109+
sql = QString("select * from pg_constraint where "
1110+
"conkey[1]=(select attnum from pg_attribute where attname=%1 and attrelid=%2) "
1111+
"and conrelid=%2 and (contype='p' or contype='u') "
1112+
"and array_dims(conkey)='[1:1]'").arg( quotedValue(tableCol) ).arg( rel_oid );
11141113

11151114
result = PQexec(connection, sql.toUtf8());
11161115
if (PQntuples(result) == 1 && colType == "int4")
@@ -1168,9 +1167,9 @@ QString QgsPostgresProvider::chooseViewColumn(const tableCols& cols)
11681167
// Get the relation oid from our cache.
11691168
QString rel_oid = relOid[i->first];
11701169
// And see if the column has an index
1171-
sql = "select * from pg_index where indrelid = " + rel_oid +
1172-
" and indkey[0] = (select attnum from pg_attribute where "
1173-
"attrelid = " + rel_oid + " and attname = '" + i->second.column + "')";
1170+
sql = QString( "select * from pg_index where indrelid=%1 and indkey[0]=(select attnum from pg_attribute where attrelid=%1 and attname=%2)")
1171+
.arg( rel_oid )
1172+
.arg( quotedValue( i->second.column ) );
11741173
PGresult* result = PQexec(connection, sql.toUtf8());
11751174

11761175
if (PQntuples(result) > 0 && uniqueData(mSchemaName, mTableName, i->first))
@@ -1252,7 +1251,10 @@ bool QgsPostgresProvider::uniqueData(QString schemaName,
12521251

12531252
bool isUnique = false;
12541253

1255-
QString sql = "select count(distinct " + quotedIdentifier(colName) + ") = count(" + quotedIdentifier(colName) + ") from " + quotedIdentifier(schemaName) + "." + quotedIdentifier(tableName);
1254+
QString sql = QString("select count(distinct %1)=count(%1) from %2.%3")
1255+
.arg( quotedIdentifier(colName) )
1256+
.arg( quotedIdentifier(schemaName) )
1257+
.arg( quotedIdentifier(tableName) );
12561258

12571259
PGresult* unique = PQexec(connection, sql.toUtf8());
12581260

@@ -1266,7 +1268,8 @@ bool QgsPostgresProvider::uniqueData(QString schemaName,
12661268

12671269
int QgsPostgresProvider::SRCFromViewColumn(const QString& ns, const QString& relname, const QString& attname_table, const QString& attname_view, const QString& viewDefinition, SRC& result) const
12681270
{
1269-
QString newViewDefSql = "SELECT definition FROM pg_views WHERE schemaname = '" + ns + "' AND viewname = '" + relname + "'";
1271+
QString newViewDefSql = QString("SELECT definition FROM pg_views WHERE schemaname=%1 AND viewname=%2")
1272+
.arg( quotedValue(ns) ).arg( quotedValue(relname) );
12701273
PGresult* newViewDefResult = PQexec(connection, newViewDefSql.toUtf8());
12711274
int numEntries = PQntuples(newViewDefResult);
12721275

@@ -1278,19 +1281,71 @@ int QgsPostgresProvider::SRCFromViewColumn(const QString& ns, const QString& rel
12781281
QString newAttNameTable = attname_table;
12791282

12801283
//find out the attribute name of the underlying table/view
1281-
if (newViewDefinition.contains("AS"))
1284+
if (newViewDefinition.contains(" AS "))
12821285
{
1283-
QRegExp s("(\\w+) " + QString("AS ") + QRegExp::escape(attname_table));
1286+
QRegExp s("(\\w+)" + QString(" AS ") + QRegExp::escape(attname_table));
12841287
if (s.indexIn(newViewDefinition) != -1)
12851288
{
12861289
newAttNameTable = s.cap(1);
12871290
}
12881291
}
12891292

1290-
QString viewColumnSql = "SELECT table_schema, table_name, column_name FROM (SELECT DISTINCT current_database()::information_schema.sql_identifier AS view_catalog, nv.nspname::information_schema.sql_identifier AS view_schema, v.relname::information_schema.sql_identifier AS view_name, current_database()::information_schema.sql_identifier AS table_catalog, nt.nspname::information_schema.sql_identifier AS table_schema, t.relname::information_schema.sql_identifier AS table_name, a.attname::information_schema.sql_identifier AS column_name "
1291-
" FROM pg_namespace nv, pg_class v, pg_depend dv, pg_depend dt, pg_class t, pg_namespace nt, pg_attribute a "
1292-
" WHERE nv.oid = v.relnamespace AND v.relkind = 'v'::\"char\" AND v.oid = dv.refobjid AND dv.refclassid = 'pg_class'::regclass::oid AND dv.classid = 'pg_rewrite'::regclass::oid AND dv.deptype = 'i'::\"char\" AND dv.objid = dt.objid AND dv.refobjid <> dt.refobjid AND dt.classid = 'pg_rewrite'::regclass::oid AND dt.refclassid = 'pg_class'::regclass::oid AND dt.refobjid = t.oid AND t.relnamespace = nt.oid AND (t.relkind = ANY (ARRAY['r'::\"char\", 'v'::\"char\"])) AND t.oid = a.attrelid AND dt.refobjsubid = a.attnum "
1293-
"ORDER BY current_database()::information_schema.sql_identifier, nv.nspname::information_schema.sql_identifier, v.relname::information_schema.sql_identifier, current_database()::information_schema.sql_identifier, nt.nspname::information_schema.sql_identifier, t.relname::information_schema.sql_identifier, a.attname::information_schema.sql_identifier) x WHERE view_schema = '" + ns + "' AND view_name = '" + relname + "' AND column_name = '" + newAttNameTable +"'";
1293+
QString viewColumnSql =
1294+
QString("SELECT "
1295+
"table_schema,"
1296+
"table_name,"
1297+
"column_name"
1298+
" FROM "
1299+
"("
1300+
"SELECT DISTINCT "
1301+
"current_database()::information_schema.sql_identifier AS view_catalog,"
1302+
"nv.nspname::information_schema.sql_identifier AS view_schema,"
1303+
"v.relname::information_schema.sql_identifier AS view_name,"
1304+
"current_database()::information_schema.sql_identifier AS table_catalog,"
1305+
"nt.nspname::information_schema.sql_identifier AS table_schema,"
1306+
"t.relname::information_schema.sql_identifier AS table_name,"
1307+
"a.attname::information_schema.sql_identifier AS column_name"
1308+
" FROM "
1309+
"pg_namespace nv,"
1310+
"pg_class v,"
1311+
"pg_depend dv,"
1312+
"pg_depend dt,"
1313+
"pg_class t,"
1314+
"pg_namespace nt,"
1315+
"pg_attribute a"
1316+
" WHERE "
1317+
"nv.oid=v.relnamespace AND "
1318+
"v.relkind='v'::\"char\" AND "
1319+
"v.oid=dv.refobjid AND "
1320+
"dv.refclassid='pg_class'::regclass::oid AND "
1321+
"dv.classid='pg_rewrite'::regclass::oid AND "
1322+
"dv.deptype='i'::\"char\" AND "
1323+
"dv.objid = dt.objid AND "
1324+
"dv.refobjid<>dt.refobjid AND "
1325+
"dt.classid='pg_rewrite'::regclass::oid AND "
1326+
"dt.refclassid='pg_class'::regclass::oid AND "
1327+
"dt.refobjid=t.oid AND "
1328+
"t.relnamespace = nt.oid AND "
1329+
"(t.relkind=ANY (ARRAY['r'::\"char\", 'v'::\"char\"])) AND "
1330+
"t.oid=a.attrelid AND "
1331+
"dt.refobjsubid=a.attnum"
1332+
" ORDER BY "
1333+
"current_database()::information_schema.sql_identifier,"
1334+
"nv.nspname::information_schema.sql_identifier,"
1335+
"v.relname::information_schema.sql_identifier,"
1336+
"current_database()::information_schema.sql_identifier,"
1337+
"nt.nspname::information_schema.sql_identifier,"
1338+
"t.relname::information_schema.sql_identifier,"
1339+
"a.attname::information_schema.sql_identifier"
1340+
") x"
1341+
" WHERE "
1342+
"view_schema=%1 AND "
1343+
"view_name=%2 AND "
1344+
"column_name=%3")
1345+
.arg( quotedValue(ns) )
1346+
.arg( quotedValue(relname) )
1347+
.arg( quotedValue(newAttNameTable) );
1348+
12941349
PGresult* viewColumnResult = PQexec(connection, viewColumnSql.toUtf8());
12951350
if(PQntuples(viewColumnResult) > 0)
12961351
{
@@ -1311,10 +1366,24 @@ int QgsPostgresProvider::SRCFromViewColumn(const QString& ns, const QString& rel
13111366
PQclear(newViewDefResult);
13121367

13131368
//relation is table, we just have to add the type
1314-
QString typeSql = "SELECT pg_type.typname FROM pg_attribute, pg_class, pg_namespace, pg_type WHERE pg_class.relname = '" + relname + "' AND pg_namespace.nspname = '" + ns + "' AND pg_attribute.attname = '" + attname_table + "' AND pg_attribute.attrelid = pg_class.oid AND pg_class.relnamespace = pg_namespace.oid AND pg_attribute.atttypid = pg_type.oid";
1315-
QgsDebugMsg("***********************************************************************************");
1316-
QgsDebugMsg(typeSql);
1317-
QgsDebugMsg("***********************************************************************************");
1369+
QString typeSql = QString("SELECT "
1370+
"pg_type.typname"
1371+
" FROM "
1372+
"pg_attribute,"
1373+
"pg_class,"
1374+
"pg_namespace,"
1375+
"pg_type"
1376+
" WHERE "
1377+
"pg_class.relname=%1 AND "
1378+
"pg_namespace.nspname=%2 AND "
1379+
"pg_attribute.attname=%3 AND "
1380+
"pg_attribute.attrelid=pg_class.oid AND "
1381+
"pg_class.relnamespace=pg_namespace.oid AND "
1382+
"pg_attribute.atttypid=pg_type.oid")
1383+
.arg( quotedValue(relname ) )
1384+
.arg( quotedValue(ns) )
1385+
.arg( quotedValue(attname_table) );
1386+
13181387
PGresult* typeSqlResult = PQexec(connection, typeSql.toUtf8());
13191388
if(PQntuples(typeSqlResult) < 1)
13201389
{
@@ -1336,14 +1405,64 @@ int QgsPostgresProvider::SRCFromViewColumn(const QString& ns, const QString& rel
13361405

13371406
void QgsPostgresProvider::findColumns(tableCols& cols)
13381407
{
1339-
QString viewColumnSql = "SELECT table_schema, table_name, column_name FROM (SELECT DISTINCT current_database() AS view_catalog, nv.nspname AS view_schema, v.relname AS view_name, current_database() AS table_catalog, nt.nspname AS table_schema, t.relname AS table_name, a.attname AS column_name "
1340-
" FROM pg_namespace nv, pg_class v, pg_depend dv, pg_depend dt, pg_class t, pg_namespace nt, pg_attribute a "
1341-
" WHERE nv.oid = v.relnamespace AND v.relkind = 'v'::\"char\" AND v.oid = dv.refobjid AND dv.refclassid = 'pg_class'::regclass::oid AND dv.classid = 'pg_rewrite'::regclass::oid AND dv.deptype = 'i'::\"char\" AND dv.objid = dt.objid AND dv.refobjid <> dt.refobjid AND dt.classid = 'pg_rewrite'::regclass::oid AND dt.refclassid = 'pg_class'::regclass::oid AND dt.refobjid = t.oid AND t.relnamespace = nt.oid AND (t.relkind = ANY (ARRAY['r'::\"char\", 'v'::\"char\"])) AND t.oid = a.attrelid AND dt.refobjsubid = a.attnum "
1342-
"ORDER BY current_database(), nv.nspname, v.relname, current_database(), nt.nspname, t.relname, a.attname) x WHERE view_schema = '" + mSchemaName + "' AND view_name = '" + mTableName + "'";
1408+
QString viewColumnSql =
1409+
QString("SELECT "
1410+
"table_schema,"
1411+
"table_name,"
1412+
"column_name"
1413+
" FROM "
1414+
"("
1415+
"SELECT DISTINCT "
1416+
"current_database() AS view_catalog,"
1417+
"nv.nspname AS view_schema,"
1418+
"v.relname AS view_name,"
1419+
"current_database() AS table_catalog,"
1420+
"nt.nspname AS table_schema,"
1421+
"t.relname AS table_name,"
1422+
"a.attname AS column_name"
1423+
" FROM "
1424+
"pg_namespace nv,"
1425+
"pg_class v,"
1426+
"pg_depend dv,"
1427+
"pg_depend dt,"
1428+
"pg_class t,"
1429+
"pg_namespace nt,"
1430+
"pg_attribute a"
1431+
" WHERE "
1432+
"nv.oid=v.relnamespace AND "
1433+
"v.relkind='v'::\"char\" AND "
1434+
"v.oid=dv.refobjid AND "
1435+
"dv.refclassid='pg_class'::regclass::oid AND "
1436+
"dv.classid='pg_rewrite'::regclass::oid AND "
1437+
"dv.deptype='i'::\"char\" AND "
1438+
"dv.objid=dt.objid AND "
1439+
"dv.refobjid<>dt.refobjid AND "
1440+
"dt.classid='pg_rewrite'::regclass::oid AND "
1441+
"dt.refclassid='pg_class'::regclass::oid AND "
1442+
"dt.refobjid=t.oid AND "
1443+
"t.relnamespace=nt.oid AND "
1444+
"(t.relkind = ANY (ARRAY['r'::\"char\",'v'::\"char\"])) AND "
1445+
"t.oid=a.attrelid AND "
1446+
"dt.refobjsubid=a.attnum"
1447+
" ORDER BY "
1448+
"current_database(),"
1449+
"nv.nspname,"
1450+
"v.relname,"
1451+
"current_database(),"
1452+
"nt.nspname,"
1453+
"t.relname,"
1454+
"a.attname"
1455+
") x"
1456+
" WHERE "
1457+
"view_schema=%1 AND view_name=%2")
1458+
.arg( quotedValue(mSchemaName) )
1459+
.arg( quotedValue(mTableName) );
13431460
PGresult* viewColumnResult = PQexec(connection, viewColumnSql.toUtf8());
13441461

13451462
//find out view definition
1346-
QString viewDefSql = "SELECT definition FROM pg_views WHERE schemaname = '" + mSchemaName + "' AND viewname = '" + mTableName + "'";
1463+
QString viewDefSql = QString("SELECT definition FROM pg_views WHERE schemaname=%1 AND viewname=%2")
1464+
.arg( quotedValue( mSchemaName ) )
1465+
.arg( quotedValue( mTableName ) );
13471466
PGresult* viewDefResult = PQexec(connection, viewDefSql.toUtf8());
13481467
if(PQntuples(viewDefResult) < 1)
13491468
{
@@ -1366,7 +1485,7 @@ void QgsPostgresProvider::findColumns(tableCols& cols)
13661485
attname_view = attname_table;
13671486

13681487
//examine if the column name has been renamed in the view with AS
1369-
if (viewDefinition.contains("AS"))
1488+
if (viewDefinition.contains(" AS "))
13701489
{
13711490
// This regular expression needs more testing. Since the view
13721491
// definition comes from postgresql and has been 'standardised', we
@@ -1392,208 +1511,6 @@ void QgsPostgresProvider::findColumns(tableCols& cols)
13921511
}
13931512
PQclear(viewColumnResult);
13941513

1395-
#if 0
1396-
// This sql is derived from the one that defines the view
1397-
// 'information_schema.view_column_usage' in PostgreSQL, with a few
1398-
// mods to suit our purposes.
1399-
QString sql = ""
1400-
"SELECT DISTINCT "
1401-
" nv.nspname AS view_schema, "
1402-
" v.relname AS view_name, "
1403-
" a.attname AS view_column_name, "
1404-
" nt.nspname AS table_schema, "
1405-
" t.relname AS table_name, "
1406-
" a.attname AS column_name, "
1407-
" t.relkind AS table_type, "
1408-
" typ.typname AS column_type, "
1409-
" vs.definition AS view_definition "
1410-
"FROM "
1411-
" pg_namespace nv, "
1412-
" pg_class v, "
1413-
" pg_depend dv,"
1414-
" pg_depend dt, "
1415-
" pg_class t, "
1416-
" pg_namespace nt, "
1417-
" pg_attribute a,"
1418-
" pg_user u, "
1419-
" pg_type typ, "
1420-
" pg_views vs "
1421-
"WHERE "
1422-
" nv.oid = v.relnamespace AND "
1423-
" v.relkind = 'v'::\"char\" AND "
1424-
" v.oid = dv.refobjid AND "
1425-
" dv.refclassid = 'pg_class'::regclass::oid AND "
1426-
" dv.classid = 'pg_rewrite'::regclass::oid AND "
1427-
" dv.deptype = 'i'::\"char\" AND "
1428-
" dv.objid = dt.objid AND "
1429-
" dv.refobjid <> dt.refobjid AND "
1430-
" dt.classid = 'pg_rewrite'::regclass::oid AND "
1431-
" dt.refclassid = 'pg_class'::regclass::oid AND "
1432-
" dt.refobjid = t.oid AND "
1433-
" t.relnamespace = nt.oid AND "
1434-
" (t.relkind = 'r'::\"char\" OR t.relkind = 'v'::\"char\") AND "
1435-
" t.oid = a.attrelid AND "
1436-
" dt.refobjsubid = a.attnum AND "
1437-
" nv.nspname NOT IN ('pg_catalog', 'information_schema' ) AND "
1438-
" a.atttypid = typ.oid AND "
1439-
" nv.nspname = vs.schemaname AND "
1440-
" v.relname = vs.viewname";
1441-
1442-
// A structure to store the results of the above sql.
1443-
typedef std::map<QString, TT> columnRelationsType;
1444-
columnRelationsType columnRelations;
1445-
1446-
// A structure to cache the query results that return the view
1447-
// definition.
1448-
typedef QMap<QString, QString> viewDefCache;
1449-
viewDefCache viewDefs;
1450-
1451-
PGresult* result = PQexec(connection, (const char*)(sql.utf8()));
1452-
// Store the results of the query for convenient access
1453-
1454-
for (int i = 0; i < PQntuples(result); ++i)
1455-
{
1456-
TT temp;
1457-
temp.view_schema = PQgetvalue(result, i, 0);
1458-
temp.view_name = PQgetvalue(result, i, 1);
1459-
temp.view_column_name = PQgetvalue(result, i, 2);
1460-
temp.table_schema = PQgetvalue(result, i, 3);
1461-
temp.table_name = PQgetvalue(result, i, 4);
1462-
temp.column_name = PQgetvalue(result, i, 5);
1463-
temp.table_type = PQgetvalue(result, i, 6);
1464-
temp.column_type = PQgetvalue(result, i, 7);
1465-
QString viewDef = PQgetvalue(result, i, 8);
1466-
1467-
// BUT, the above SQL doesn't always give the correct value for the view
1468-
// column name (that's because that information isn't available directly
1469-
// from the database), mainly when the view column name has been renamed
1470-
// using 'AS'. To fix this we need to look in the view definition and
1471-
// adjust the view column name if necessary.
1472-
1473-
// Now pick the view definiton apart, looking for
1474-
// temp.column_name to the left of an 'AS'.
1475-
1476-
if (!viewDef.isEmpty())
1477-
{
1478-
// Compiling and executing the regexp for each row from the above query
1479-
// can take quite a while - a database can easily have hundreds of
1480-
// rows. Working on the premise that we are only doing this to catch the
1481-
// cases where the view column has been renamed using the AS construct,
1482-
// we'll check for that first before doing the potentially
1483-
// time-consuming regular expression.
1484-
1485-
if (viewDef.contains("AS"))
1486-
{
1487-
// This regular expression needs more testing. Since the view
1488-
// definition comes from postgresql and has been 'standardised', we
1489-
// don't need to deal with everything that the user could put in a view
1490-
// definition. Does the regexp have to deal with the schema??
1491-
1492-
QRegExp s(".* \"?" + QRegExp::escape(temp.table_name) +
1493-
"\"?\\.\"?" + QRegExp::escape(temp.column_name) +
1494-
"\"? AS \"?(\\w+)\"?,* .*");
1495-
1496-
QgsDebugMsg(viewDef + "\n" + s.pattern());
1497-
1498-
if (s.indexIn(viewDef) != -1)
1499-
{
1500-
temp.view_column_name = s.cap(1);
1501-
}
1502-
}
1503-
}
1504-
1505-
QgsDebugMsg(temp.view_schema + "."
1506-
+ temp.view_name + "."
1507-
+ temp.view_column_name + " <- "
1508-
+ temp.table_schema + "."
1509-
+ temp.table_name + "."
1510-
+ temp.column_name + " is a '"
1511-
+ temp.table_type + "' of type "
1512-
+ temp.column_type);
1513-
1514-
columnRelations[temp.view_schema + '.' +
1515-
temp.view_name + '.' +
1516-
temp.view_column_name] = temp;
1517-
}
1518-
PQclear(result);
1519-
1520-
// Loop over all columns in the view in question.
1521-
1522-
sql = "SELECT pg_namespace.nspname || '.' || "
1523-
"pg_class.relname || '.' || pg_attribute.attname "
1524-
"FROM pg_attribute, pg_class, pg_namespace "
1525-
"WHERE pg_class.relname = '" + mTableName + "' "
1526-
"AND pg_namespace.nspname = '" + mSchemaName + "' "
1527-
"AND pg_attribute.attrelid = pg_class.oid "
1528-
"AND pg_class.relnamespace = pg_namespace.oid";
1529-
1530-
result = PQexec(connection, (const char*)(sql.utf8()));
1531-
1532-
// Loop over the columns in mSchemaName.mTableName and find out the
1533-
// underlying schema, table, and column name.
1534-
for (int i = 0; i < PQntuples(result); ++i)
1535-
{
1536-
columnRelationsType::const_iterator
1537-
ii = columnRelations.find(PQgetvalue(result, i, 0));
1538-
columnRelationsType::const_iterator start_iter = ii;
1539-
1540-
if (ii == columnRelations.end())
1541-
continue;
1542-
1543-
int count = 0;
1544-
const int max_loops = 100;
1545-
1546-
while (ii->second.table_type != "r" && count < max_loops)
1547-
{
1548-
QgsDebugMsg("Searching for the column that " +
1549-
ii->second.table_schema + '.'+
1550-
ii->second.table_name + "." +
1551-
ii->second.column_name + " refers to.");
1552-
1553-
columnRelationsType::const_iterator
1554-
jj = columnRelations.find(QString(ii->second.table_schema + '.' +
1555-
ii->second.table_name + '.' +
1556-
ii->second.column_name));
1557-
1558-
if (jj == columnRelations.end())
1559-
{
1560-
QgsDebugMsg("WARNING: Failed to find the column that " +
1561-
ii->second.table_schema + "." +
1562-
ii->second.table_name + "." +
1563-
ii->second.column_name + " refers to.");
1564-
break;
1565-
}
1566-
1567-
ii = jj;
1568-
++count;
1569-
}
1570-
1571-
if (count >= max_loops)
1572-
{
1573-
QgsDebugMsg("Search for the underlying table.column for view column " +
1574-
ii->second.table_schema + "." +
1575-
ii->second.table_name + "." +
1576-
ii->second.column_name + " failed: exceeded maximum "
1577-
"interation limit (" + QString::number(max_loops) + ")");
1578-
1579-
cols[ii->second.view_column_name] = SRC("","","","");
1580-
}
1581-
else if (ii != columnRelations.end())
1582-
{
1583-
cols[start_iter->second.view_column_name] =
1584-
SRC(ii->second.table_schema,
1585-
ii->second.table_name,
1586-
ii->second.column_name,
1587-
ii->second.column_type);
1588-
1589-
QgsDebugMsg( QString(PQgetvalue(result, i, 0)) + " derives from " +
1590-
ii->second.table_schema + "." +
1591-
ii->second.table_name + "." +
1592-
ii->second.column_name);
1593-
}
1594-
}
1595-
PQclear(result);
1596-
#endif //0
15971514
}
15981515

15991516
// Returns the minimum value of an attribute
@@ -2078,6 +1995,9 @@ bool QgsPostgresProvider::changeAttributeValues(const QgsChangedAttributesMap &
20781995
if(fid<0)
20791996
continue;
20801997

1998+
QString sql = QString("UPDATE %1 SET ").arg( mSchemaTableName );
1999+
bool first = true;
2000+
20812001
const QgsAttributeMap& attrs = iter.value();
20822002

20832003
// cycle through the changed attributes of the feature
@@ -2086,25 +2006,30 @@ bool QgsPostgresProvider::changeAttributeValues(const QgsChangedAttributesMap &
20862006
try {
20872007
QString fieldName = field(siter.key()).name();
20882008

2089-
QString sql = QString("UPDATE %1 SET %2=%3 WHERE %4=%5")
2090-
.arg( mSchemaTableName )
2091-
.arg( quotedIdentifier(fieldName) )
2092-
.arg( quotedValue( siter->toString() ) )
2093-
.arg( quotedIdentifier(primaryKey) )
2094-
.arg( fid );
2095-
QgsDebugMsg(sql);
2096-
2097-
PGresult* result=PQexec(connection, sql.toUtf8());
2098-
if( result==0 || PQresultStatus(result)==PGRES_FATAL_ERROR )
2099-
throw PGException(result);
2100-
2101-
PQclear(result);
2009+
if(!first)
2010+
sql += ",";
2011+
else
2012+
first=false;
2013+
2014+
sql += QString("%1=%2")
2015+
.arg( quotedIdentifier(fieldName) )
2016+
.arg( quotedValue( siter->toString() ) );
21022017
}
21032018
catch(PGFieldNotFound)
21042019
{
21052020
// Field was missing - shouldn't happen
21062021
}
21072022
}
2023+
2024+
sql += QString(" WHERE %1=%2")
2025+
.arg( quotedIdentifier(primaryKey) )
2026+
.arg( fid );
2027+
2028+
PGresult* result=PQexec(connection, sql.toUtf8());
2029+
if( result==0 || PQresultStatus(result)==PGRES_FATAL_ERROR )
2030+
throw PGException(result);
2031+
2032+
PQclear(result);
21082033
}
21092034

21102035
PQexecNR(connection,QString("COMMIT").toUtf8());
@@ -2239,12 +2164,10 @@ long QgsPostgresProvider::getFeatureCount()
22392164
// a thread the task of getting the full count.
22402165

22412166
#ifdef POSTGRESQL_THREADS
2242-
QString sql = "select reltuples from pg_catalog.pg_class where relname = '" +
2243-
tableName + "'";
2244-
2167+
QString sql = QString("select reltuples from pg_catalog.pg_class where relname=%1").arg( quotedValue(tableName) );
22452168
QgsDebugMsg("Running SQL: " + sql);
22462169
#else
2247-
QString sql = "select count(*) from " + mSchemaTableName + "";
2170+
QString sql = QString("select count(*) from %1").arg( mSchemaTableName );
22482171

22492172
if(sqlWhereClause.length() > 0)
22502173
{
@@ -2270,26 +2193,27 @@ long QgsPostgresProvider::getFeatureCount()
22702193
void QgsPostgresProvider::calculateExtents()
22712194
{
22722195
#ifdef POSTGRESQL_THREADS
2273-
// get the approximate extent by retreiving the bounding box
2196+
// get the approximate extent by retrieving the bounding box
22742197
// of the first few items with a geometry
22752198

2276-
QString sql = "select box3d(" + quotedIdentifier(geometryColumn) + ") from "
2277-
+ mSchemaTableName + " where ";
2199+
QString sql = QString("select box3d(%1) from %2 where ")
2200+
.arg( quotedIdentifier(geometryColumn) )
2201+
.arg( mSchemaTableName );
22782202

22792203
if(sqlWhereClause.length() > 0)
22802204
{
2281-
sql += "(" + sqlWhereClause + ") and ";
2205+
sql += QString("(%1) and ").arg( sqlWhereClause );
22822206
}
22832207

2284-
sql += "not IsEmpty(" + quotedIdentifier(geometryColumn) + ") limit 5";
2285-
2208+
sql += QString("not IsEmpty(%1) limit 5").arg( quotedIdentifier(geometryColumn) );
22862209

22872210
#if WASTE_TIME
2288-
sql = "select xmax(extent(" + quotedIdentifier(geometryColumn) + ")) as xmax,"
2289-
"xmin(extent(" + quotedIdentifier(geometryColumn) + ")) as xmin,"
2290-
"ymax(extent(" + quotedIdentifier(geometryColumn) + ")) as ymax,"
2291-
"ymin(extent(" + quotedIdentifier(geometryColumn) + ")) as ymin"
2292-
" from " + mSchemaTableName;
2211+
sql = QString("select "
2212+
"xmax(extent(%1)) as xmax,"
2213+
"xmin(extent(%1)) as xmin,"
2214+
"ymax(extent(%1)) as ymax,"
2215+
"ymin(extent(%1)) as ymin"
2216+
" from %2").arg( quotedIdentifier(geometryColumn).arg( mSchemaTableName );
22932217
#endif
22942218

22952219
QgsDebugMsg("Getting approximate extent using: '" + sql + "'");
@@ -2326,19 +2250,22 @@ void QgsPostgresProvider::calculateExtents()
23262250

23272251
// get the extents
23282252

2329-
QString sql = "select extent(" + quotedIdentifier(geometryColumn) + ") from " +
2330-
mSchemaTableName;
2253+
QString sql = QString("select extent(%1) from %2")
2254+
.arg( quotedIdentifier(geometryColumn) )
2255+
.arg( mSchemaTableName );
2256+
23312257
if(sqlWhereClause.length() > 0)
23322258
{
23332259
sql += " where " + sqlWhereClause;
23342260
}
23352261

23362262
#if WASTE_TIME
2337-
sql = "select xmax(extent(" + quotedIdentifier(geometryColumn) + ")) as xmax,"
2338-
"xmin(extent(" + quotedIdentifier(geometryColumn) + ")) as xmin,"
2339-
"ymax(extent(" + quotedIdentifier(geometryColumn) + ")) as ymax,"
2340-
"ymin(extent(" + quotedIdentifier(geometryColumn) + ")) as ymin"
2341-
" from " + mSchemaTableName;
2263+
sql = QString("select "
2264+
"xmax(extent(%1)) as xmax,"
2265+
"xmin(extent(%1)) as xmin,"
2266+
"ymax(extent(%1)) as ymax,"
2267+
"ymin(extent(%1)) as ymin"
2268+
" from %2").arg( quotedIdentifier(geometryColumn) ).arg( mSchemaTableName );
23422269
#endif
23432270

23442271
QgsDebugMsg("Getting extents using schema.table: " + sql);
@@ -2461,7 +2388,7 @@ bool QgsPostgresProvider::deduceEndian()
24612388
// version 7.4, binary cursors return data in XDR whereas previous versions
24622389
// return data in the endian of the server
24632390

2464-
QString firstOid = "select regclass(" + quotedValue(mSchemaTableName) + ")::oid";
2391+
QString firstOid = QString("select regclass(%1)::oid").arg( quotedValue(mSchemaTableName) );
24652392
PGresult * oidResult = PQexec(connection, firstOid.toUtf8());
24662393
// get the int value from a "normal" select
24672394
QString oidValue = QString::fromUtf8(PQgetvalue(oidResult,0,0));
@@ -2471,7 +2398,7 @@ bool QgsPostgresProvider::deduceEndian()
24712398

24722399
// get the same value using a binary cursor
24732400

2474-
QString oidDeclare = "declare oidcursor binary cursor with hold for select regclass('" + mSchemaTableName + "')::oid";
2401+
QString oidDeclare = QString("declare oidcursor binary cursor with hold for select regclass(%1)::oid").arg( quotedValue(mSchemaTableName) );
24752402
// set up the cursor
24762403
PQexecNR(connection, oidDeclare.toUtf8());
24772404
QString fetch = "fetch forward 1 from oidcursor";
@@ -2503,21 +2430,22 @@ bool QgsPostgresProvider::getGeometryDetails()
25032430
valid = false;
25042431
QStringList log;
25052432

2506-
QString sql = "select f_geometry_column,type,srid from geometry_columns"
2507-
" where f_table_name='" + mTableName + "' and f_geometry_column = '" +
2508-
geometryColumn + "' and f_table_schema = '" + mSchemaName + "'";
2433+
QString sql = QString("select type,srid from geometry_columns"
2434+
" where f_table_name=%1 and f_geometry_column=%2 and f_table_schema=%3")
2435+
.arg( quotedValue(mTableName) )
2436+
.arg( quotedValue(geometryColumn) )
2437+
.arg( quotedValue(mSchemaName) );
25092438

25102439
QgsDebugMsg("Getting geometry column: " + sql);
25112440

25122441
PGresult *result = executeDbCommand(connection, sql);
25132442

25142443
QgsDebugMsg("geometry column query returned " + QString::number(PQntuples(result)));
2515-
QgsDebugMsg("column number of srid is " + QString::number(PQfnumber(result, "srid")));
25162444

25172445
if (PQntuples(result) > 0)
25182446
{
2519-
srid = QString::fromUtf8(PQgetvalue(result, 0, PQfnumber(result, QString("srid").toUtf8())));
2520-
fType = QString::fromUtf8(PQgetvalue(result, 0, PQfnumber(result, QString("type").toUtf8())));
2447+
fType = QString::fromUtf8(PQgetvalue(result, 0, 0));
2448+
srid = QString::fromUtf8(PQgetvalue(result, 0, 1));
25212449
PQclear(result);
25222450
}
25232451
else
@@ -2526,26 +2454,24 @@ bool QgsPostgresProvider::getGeometryDetails()
25262454
// get stuff from the relevant column instead. This may (will?)
25272455
// fail if there is no data in the relevant table.
25282456
PQclear(result); // for the query just before the if() statement
2529-
sql = "select "
2530-
"srid(" + quotedIdentifier(geometryColumn) + "), "
2531-
"geometrytype(" + quotedIdentifier(geometryColumn) + ") from " +
2532-
mSchemaTableName;
2457+
sql = QString("select srid(%1),geometrytype(%1) from %2" )
2458+
.arg( quotedIdentifier(geometryColumn) )
2459+
.arg( mSchemaTableName );
25332460

25342461
//it is possible that the where clause restricts the feature type
25352462
if(!sqlWhereClause.isEmpty())
2536-
{
2537-
sql += " WHERE ";
2538-
sql += sqlWhereClause;
2539-
}
2463+
{
2464+
sql += " WHERE " + sqlWhereClause;
2465+
}
25402466

25412467
sql += " limit 1";
25422468

25432469
result = executeDbCommand(connection, sql);
25442470

25452471
if (PQntuples(result) > 0)
25462472
{
2547-
srid = QString::fromUtf8(PQgetvalue(result, 0, PQfnumber(result, QString("srid").toUtf8())));
2548-
fType = QString::fromUtf8(PQgetvalue(result, 0, PQfnumber(result, QString("geometrytype").toUtf8())));
2473+
srid = QString::fromUtf8(PQgetvalue(result, 0, 0));
2474+
fType = QString::fromUtf8(PQgetvalue(result, 0, 1));
25492475
}
25502476
PQclear(result);
25512477
}
@@ -2662,18 +2588,44 @@ QString QgsPostgresProvider::quotedValue( QString value ) const
26622588
return value.prepend("'").append("'");
26632589
}
26642590

2591+
PGresult *QgsPostgresProvider::PQexec(PGconn *conn, const char *query)
2592+
{
2593+
PGresult *res = ::PQexec(conn, query);
2594+
2595+
#ifdef QGISDEBUG
2596+
if(res) {
2597+
int errorStatus = PQresultStatus(res);
2598+
if( errorStatus!=PGRES_COMMAND_OK && errorStatus!=PGRES_TUPLES_OK )
2599+
{
2600+
QString err = QString("Errornous query: %1 returned %2 [%3]")
2601+
.arg(query)
2602+
.arg(errorStatus)
2603+
.arg(PQresultErrorMessage(res));
2604+
QgsDebugMsgLevel( err, 3 );
2605+
}
2606+
}
2607+
#endif
2608+
2609+
return res;
2610+
}
2611+
26652612
bool QgsPostgresProvider::PQexecNR(PGconn *conn, const char *query)
26662613
{
2667-
PGresult *res = PQexec(conn, query);
2614+
PGresult *res = ::PQexec(conn, query);
26682615
if(res)
26692616
{
26702617
int errorStatus = PQresultStatus(res);
2671-
QgsDebugMsgLevel( QString("Query: %1 returned %2 [%3]")
2618+
#ifdef QGISDEBUG
2619+
if( errorStatus!=PGRES_COMMAND_OK )
2620+
{
2621+
QString err = QString("Query: %1 returned %2 [%3]")
26722622
.arg(query)
26732623
.arg(errorStatus)
2674-
.arg(PQresultErrorMessage(res)), 3 );
2624+
.arg(PQresultErrorMessage(res));
2625+
QgsDebugMsgLevel( err, 3 );
2626+
}
2627+
#endif
26752628
PQclear(res);
2676-
26772629
return errorStatus==PGRES_COMMAND_OK;
26782630
}
26792631
else

‎src/providers/postgres/qgspostgresprovider.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -602,6 +602,9 @@ class QgsPostgresProvider:public QgsVectorDataProvider
602602
void appendGeomString(QgsGeometry *geom, QString &geomParam) const;
603603
QByteArray paramValue(QString fieldvalue, const QString &defaultValue) const;
604604

605+
// run a query and check for errors
606+
static PGresult *PQexec(PGconn *conn, const char *query);
607+
605608
// run a query and free result buffer
606609
static bool PQexecNR(PGconn *conn, const char *query);
607610

0 commit comments

Comments
 (0)
Please sign in to comment.