@@ -298,53 +298,67 @@ QStringList QgsPostgresConn::pkCandidates( QString schemaName, QString viewName
298
298
bool QgsPostgresConn::getTableInfo ( bool searchGeometryColumnsOnly, bool searchPublicOnly, bool allowGeometrylessTables )
299
299
{
300
300
int nColumns = 0 ;
301
- int nGTables = 0 ;
301
+ int foundInTables = 0 ;
302
302
QgsPostgresResult result;
303
303
QgsPostgresLayerProperty layerProperty;
304
304
305
305
QgsDebugMsg ( " Entering." );
306
306
307
307
mLayersSupported .clear ();
308
308
309
- // TODO: query topology.layer too !
310
-
311
- for ( int i = 0 ; i < 2 ; i++ )
309
+ for ( int i = 0 ; i < 3 ; i++ )
312
310
{
313
- QString gtableName, columnName;
311
+ QString sql, tableName, schemaName, columnName, typeName, sridName, gtableName ;
314
312
315
313
if ( i == 0 )
316
314
{
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" ;
317
320
gtableName = " geometry_columns" ;
318
- columnName = " f_geometry_column" ;
319
321
}
320
322
else if ( i == 1 )
321
323
{
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" ;
322
329
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" ;
324
344
}
325
345
326
346
// The following query returns only tables that exist and the user has SELECT privilege on.
327
347
// 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 );
343
357
344
358
if ( searchPublicOnly )
345
- sql += " AND f_table_schema ='public'" ;
359
+ sql += " AND n.nspname ='public'" ;
346
360
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 );
348
362
349
363
QgsDebugMsg ( " getting table info: " + sql );
350
364
result = PQexec ( sql, i == 0 );
@@ -354,8 +368,6 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
354
368
continue ;
355
369
}
356
370
357
- nGTables++;
358
-
359
371
for ( int idx = 0 ; idx < result.PQntuples (); idx++ )
360
372
{
361
373
QString tableName = result.PQgetvalue ( idx, 0 );
@@ -384,76 +396,8 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
384
396
mLayersSupported << layerProperty;
385
397
nColumns++;
386
398
}
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 ) );
444
399
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;
457
401
}
458
402
459
403
if ( nColumns == 0 )
@@ -490,14 +434,13 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
490
434
{
491
435
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)" ;
492
436
493
- if ( nGTables > 1 )
437
+ if ( foundInTables & 1 )
494
438
{
495
439
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)" ;
496
440
}
497
441
498
- if ( hasTopology () )
442
+ if ( foundInTables & 2 )
499
443
{
500
- // TODO: do not lookup tables found in topology.layer (but why?)
501
444
sql += " AND (pg_namespace.nspname,pg_class.relname,pg_attribute.attname) NOT IN (SELECT schema_name,table_name,feature_column FROM topology.layer)" ;
502
445
}
503
446
}
0 commit comments