Skip to content

Commit d62cf51

Browse files
committedOct 18, 2012
cleanup topology lookup
1 parent d45877b commit d62cf51

File tree

1 file changed

+41
-98
lines changed

1 file changed

+41
-98
lines changed
 

‎src/providers/postgres/qgspostgresconn.cpp

Lines changed: 41 additions & 98 deletions
Original file line numberDiff line numberDiff line change
@@ -298,53 +298,67 @@ QStringList QgsPostgresConn::pkCandidates( QString schemaName, QString viewName
298298
bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchPublicOnly, bool allowGeometrylessTables )
299299
{
300300
int nColumns = 0;
301-
int nGTables = 0;
301+
int foundInTables = 0;
302302
QgsPostgresResult result;
303303
QgsPostgresLayerProperty layerProperty;
304304

305305
QgsDebugMsg( "Entering." );
306306

307307
mLayersSupported.clear();
308308

309-
// TODO: query topology.layer too !
310-
311-
for ( int i = 0; i < 2; i++ )
309+
for ( int i = 0; i < 3; i++ )
312310
{
313-
QString gtableName, columnName;
311+
QString sql, tableName, schemaName, columnName, typeName, sridName, gtableName;
314312

315313
if ( i == 0 )
316314
{
315+
tableName = "l.f_table_name";
316+
schemaName = "l.f_table_schema";
317+
columnName = "l.f_geometry_column";
318+
typeName = "upper(l.type)";
319+
sridName = "l.srid";
317320
gtableName = "geometry_columns";
318-
columnName = "f_geometry_column";
319321
}
320322
else if ( i == 1 )
321323
{
324+
tableName = "l.f_table_name";
325+
schemaName = "l.f_table_schema";
326+
columnName = "l.f_geography_column";
327+
typeName = "upper(l.type)";
328+
sridName = "l.srid";
322329
gtableName = "geography_columns";
323-
columnName = "f_geography_column";
330+
}
331+
else if ( i == 2 )
332+
{
333+
schemaName = "l.schema_name";
334+
tableName = "l.table_name";
335+
columnName = "l.feature_column";
336+
typeName = "CASE "
337+
"WHEN l.feature_type = 1 THEN 'MULTIPOINT' "
338+
"WHEN l.feature_type = 2 THEN 'MULTILINESTRING' "
339+
"WHEN l.feature_type = 3 THEN 'MULTIPOLYGON' "
340+
"WHEN l.feature_type = 4 THEN 'GEOMETRYCOLLECTION' "
341+
"END AS type";
342+
sridName = "(SELECT srid FROM topology.topology t WHERE l.topology_id=t.id)";
343+
gtableName = "topology.layer";
324344
}
325345

326346
// The following query returns only tables that exist and the user has SELECT privilege on.
327347
// Can't use regclass here because table must exist, else error occurs.
328-
QString sql = QString( "SELECT "
329-
"f_table_name,"
330-
"f_table_schema,"
331-
"%2,"
332-
"upper(type),"
333-
"srid,"
334-
"pg_class.relkind"
335-
" FROM "
336-
"%1,pg_class,pg_namespace"
337-
" WHERE relname=f_table_name"
338-
" AND f_table_schema=nspname"
339-
" AND pg_namespace.oid=pg_class.relnamespace"
340-
" AND has_schema_privilege(pg_namespace.nspname,'usage')"
341-
" AND has_table_privilege('\"'||pg_namespace.nspname||'\".\"'||pg_class.relname||'\"','select')" // user has select privilege
342-
).arg( gtableName ).arg( columnName );
348+
sql = QString( "SELECT %1,%2,%3,%4,c.relkind"
349+
" FROM %5 l,pg_class c,pg_namespace n"
350+
" WHERE c.relname=%1"
351+
" AND %2=n.nspname"
352+
" AND n.oid=c.relnamespace"
353+
" AND has_schema_privilege(n.nspname,'usage')"
354+
" AND has_table_privilege('\"'||n.nspname||'\".\"'||c.relname||'\"','select')" // user has select privilege
355+
)
356+
.arg( tableName ).arg( schemaName ).arg( columnName ).arg( typeName ).arg( gtableName );
343357

344358
if ( searchPublicOnly )
345-
sql += " AND f_table_schema='public'";
359+
sql += " AND n.nspname='public'";
346360

347-
sql += QString( " ORDER BY f_table_schema,f_table_name,%1" ).arg( columnName );
361+
sql += QString( " ORDER BY n.nspname,c.relname,%1" ).arg( columnName );
348362

349363
QgsDebugMsg( "getting table info: " + sql );
350364
result = PQexec( sql, i == 0 );
@@ -354,8 +368,6 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
354368
continue;
355369
}
356370

357-
nGTables++;
358-
359371
for ( int idx = 0; idx < result.PQntuples(); idx++ )
360372
{
361373
QString tableName = result.PQgetvalue( idx, 0 );
@@ -384,76 +396,8 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
384396
mLayersSupported << layerProperty;
385397
nColumns++;
386398
}
387-
}
388-
389-
if ( hasTopology() )
390-
{
391-
// Look in topology.layer
392-
// The following query returns only tables that exist
393-
// and the user has SELECT privilege on.
394-
QString sql = QString( "SELECT "
395-
"l.table_name,"
396-
"l.schema_name,"
397-
"l.feature_column,"
398-
"CASE WHEN l.feature_type = 1 THEN 'MULTIPOINT' "
399-
" WHEN l.feature_type = 2 THEN 'MULTILINESTRING' "
400-
" WHEN l.feature_type = 3 THEN 'MULTIPOLYGON' "
401-
" WHEN l.feature_type = 4 THEN 'GEOMETRYCOLLECTION' "
402-
"END as type,"
403-
"t.srid"
404-
" FROM "
405-
"topology.layer l, pg_class c, pg_namespace n, topology.topology t"
406-
" WHERE l.topology_id = t.id"
407-
" AND c.relname=l.table_name"
408-
" AND l.schema_name=n.nspname"
409-
" AND n.oid=c.relnamespace"
410-
" AND has_schema_privilege(n.nspname,'usage')"
411-
// user has select privilege
412-
" AND has_table_privilege('\"'||n.nspname||'\".\"'||c.relname||'\"','select')"
413-
);
414-
415-
if ( searchPublicOnly )
416-
sql += " AND n.nspname='public'";
417-
418-
sql += QString( " ORDER BY n.nspname,c.relname,l.feature_column" );
419-
420-
QgsDebugMsg( "getting topology.layer info: " + sql );
421-
result = PQexec( sql, true );
422-
if ( result.PQresultStatus() != PGRES_TUPLES_OK )
423-
{
424-
PQexecNR( "COMMIT" );
425-
return false;
426-
}
427-
428-
QString gtableName = "topology.layer";
429-
430-
for ( int idx = 0; idx < result.PQntuples(); idx++ )
431-
{
432-
QString tableName = result.PQgetvalue( idx, 0 );
433-
QString schemaName = result.PQgetvalue( idx, 1 );
434-
QString column = result.PQgetvalue( idx, 2 );
435-
QString type = result.PQgetvalue( idx, 3 );
436-
QString srid = result.PQgetvalue( idx, 4 );
437-
//QString relkind = result.PQgetvalue( idx, 5 );
438-
439-
QgsDebugMsg( QString( "%1 : %2.%3.%4: %5 %6 " )
440-
.arg( gtableName )
441-
.arg( schemaName ).arg( tableName ).arg( column )
442-
.arg( type )
443-
.arg( srid ) );
444399

445-
layerProperty.type = type;
446-
layerProperty.schemaName = schemaName;
447-
layerProperty.tableName = tableName;
448-
layerProperty.geometryColName = column;
449-
layerProperty.pkCols = QStringList(); // relkind == "v" ? pkCandidates( schemaName, tableName ) : QStringList();
450-
layerProperty.srid = srid;
451-
layerProperty.sql = "";
452-
layerProperty.isGeography = false; // TODO: use an enum for the type!
453-
454-
mLayersSupported << layerProperty;
455-
nColumns++;
456-
}
400+
foundInTables |= 1 << i;
457401
}
458402

459403
if ( nColumns == 0 )
@@ -490,14 +434,13 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
490434
{
491435
sql += " AND (pg_namespace.nspname,pg_class.relname,pg_attribute.attname) NOT IN (SELECT f_table_schema,f_table_name,f_geometry_column FROM geometry_columns)";
492436

493-
if ( nGTables > 1 )
437+
if ( foundInTables & 1 )
494438
{
495439
sql += " AND (pg_namespace.nspname,pg_class.relname,pg_attribute.attname) NOT IN (SELECT f_table_schema,f_table_name,f_geography_column FROM geography_columns)";
496440
}
497441

498-
if ( hasTopology() )
442+
if ( foundInTables & 2 )
499443
{
500-
// TODO: do not lookup tables found in topology.layer (but why?)
501444
sql += " AND (pg_namespace.nspname,pg_class.relname,pg_attribute.attname) NOT IN (SELECT schema_name,table_name,feature_column FROM topology.layer)";
502445
}
503446
}

0 commit comments

Comments
 (0)
Please sign in to comment.