Skip to content

Commit

Permalink
Improve filtering by schema for postgres connections in browser
Browse files Browse the repository at this point in the history 
Results in much faster display and refresh of postgres tables

Also:
- ensure browser correctly respects postgres connection settings
- add schema comment tooltip and refresh context menu item
- make postgres connections less noisy in debug logs
  • Loading branch information
@nyalldawson
nyalldawson committed Dec 17, 2014
1 parent 4e4e161 commit 2c773a7
Show file tree
Hide file tree
Showing 4 changed files with 130 additions and 47 deletions.
92 changes: 68 additions & 24 deletions src/providers/postgres/qgspostgresconn.cpp
View file
Expand Up @@ -302,7 +302,7 @@ void QgsPostgresConn::addColumnInfo( QgsPostgresLayerProperty& layerProperty, co
QString sql = QString( "SELECT attname, CASE WHEN typname = ANY(ARRAY['geometry','geography','topogeometry']) THEN 1 ELSE null END AS isSpatial FROM pg_attribute JOIN pg_type ON atttypid=pg_type.oid WHERE attrelid=regclass('%1.%2')" )
.arg( quotedIdentifier( schemaName ) )
.arg( quotedIdentifier( viewName ) );
QgsDebugMsg( sql );
//QgsDebugMsg( sql );
QgsPostgresResult colRes = PQexec( sql );

layerProperty.pkCols.clear();
Expand All @@ -314,7 +314,7 @@ void QgsPostgresConn::addColumnInfo( QgsPostgresLayerProperty& layerProperty, co
{
if ( fetchPkCandidates )
{
QgsDebugMsg( colRes.PQgetvalue( i, 0 ) );
//QgsDebugMsg( colRes.PQgetvalue( i, 0 ) );
layerProperty.pkCols << colRes.PQgetvalue( i, 0 );
}

Expand All @@ -331,14 +331,14 @@ void QgsPostgresConn::addColumnInfo( QgsPostgresLayerProperty& layerProperty, co

}

bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchPublicOnly, bool allowGeometrylessTables )
bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchPublicOnly, bool allowGeometrylessTables, const QString schema )
{
int nColumns = 0;
int foundInTables = 0;
QgsPostgresResult result;
QgsPostgresLayerProperty layerProperty;

QgsDebugMsg( "Entering." );
//QgsDebugMsg( "Entering." );

mLayersSupported.clear();

Expand Down Expand Up @@ -401,9 +401,12 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
if ( searchPublicOnly )
sql += " AND n.nspname='public'";

if ( !schema.isEmpty() )
sql += QString( " AND %1='%2'" ).arg( schemaName ).arg( schema );

sql += QString( " ORDER BY n.nspname,c.relname,%1" ).arg( columnName );

QgsDebugMsg( "getting table info: " + sql );
//QgsDebugMsg( "getting table info: " + sql );
result = PQexec( sql, i == 0 );
if ( result.PQresultStatus() != PGRES_TUPLES_OK )
{
Expand All @@ -429,13 +432,13 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
srid = INT_MIN;
}

QgsDebugMsg( QString( "%1 : %2.%3.%4: %5 %6 %7 %8" )
/*QgsDebugMsg( QString( "%1 : %2.%3.%4: %5 %6 %7 %8" )
.arg( gtableName )
.arg( schemaName ).arg( tableName ).arg( column )
.arg( type )
.arg( srid )
.arg( relkind )
.arg( dim ) );
.arg( dim ) );*/

layerProperty.schemaName = schemaName;
layerProperty.tableName = tableName;
Expand All @@ -449,7 +452,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP

if ( isView && layerProperty.pkCols.empty() )
{
QgsDebugMsg( "no key columns found." );
//QgsDebugMsg( "no key columns found." );
continue;
}

Expand All @@ -460,11 +463,6 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
foundInTables |= 1 << i;
}

if ( nColumns == 0 )
{
QgsMessageLog::logMessage( tr( "Database connection was successful, but the accessible tables could not be determined." ), tr( "PostGIS" ) );
}

//search for geometry columns in tables that are not in the geometry_columns metatable
if ( !searchGeometryColumnsOnly )
{
Expand All @@ -489,6 +487,9 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
if ( searchPublicOnly )
sql += " AND n.nspname='public'";

if ( !schema.isEmpty() )
sql += QString( " AND n.nspname='%2'" ).arg( schema );

// skip columns of which we already derived information from the metadata tables
if ( nColumns > 0 )
{
Expand All @@ -508,7 +509,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
}
}

QgsDebugMsg( "sql: " + sql );
//QgsDebugMsg( "sql: " + sql );

result = PQexec( sql );

Expand Down Expand Up @@ -537,7 +538,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
QString coltype = result.PQgetvalue( i, 4 ); // column type
bool isView = relkind == "v" || relkind == "m";

QgsDebugMsg( QString( "%1.%2.%3: %4" ).arg( schemaName ).arg( tableName ).arg( column ).arg( relkind ) );
//QgsDebugMsg( QString( "%1.%2.%3: %4" ).arg( schemaName ).arg( tableName ).arg( column ).arg( relkind ) );

layerProperty.types.clear();
layerProperty.srids.clear();
Expand All @@ -564,7 +565,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
addColumnInfo( layerProperty, schemaName, tableName, isView );
if ( isView && layerProperty.pkCols.empty() )
{
QgsDebugMsg( "no key columns found." );
//QgsDebugMsg( "no key columns found." );
continue;
}

Expand Down Expand Up @@ -593,7 +594,10 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
if ( searchPublicOnly )
sql += " AND pg_namespace.nspname='public'";

QgsDebugMsg( "sql: " + sql );
if ( !schema.isEmpty() )
sql += QString( " AND pg_namespace.nspname='%2'" ).arg( schema );

//QgsDebugMsg( "sql: " + sql );

result = PQexec( sql );

Expand All @@ -612,14 +616,29 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
QString relkind = result.PQgetvalue( i, 2 ); // relation kind
bool isView = relkind == "v" || relkind == "m";

QgsDebugMsg( QString( "%1.%2: %3" ).arg( schema ).arg( table ).arg( relkind ) );
//QgsDebugMsg( QString( "%1.%2: %3" ).arg( schema ).arg( table ).arg( relkind ) );

layerProperty.types = QList<QGis::WkbType>() << QGis::WKBNoGeometry;
layerProperty.srids = QList<int>() << INT_MIN;
layerProperty.schemaName = schema;
layerProperty.tableName = table;
layerProperty.geometryColName = QString::null;
layerProperty.geometryColType = sctNone;

//check if we've already added this layer in some form
bool alreadyFound = false;
foreach ( QgsPostgresLayerProperty foundLayer, mLayersSupported )
{
if ( foundLayer.schemaName == schema && foundLayer.tableName == table )
{
//already found this table
alreadyFound = true;
break;
}
}
if ( alreadyFound )
continue;

addColumnInfo( layerProperty, schema, table, isView );
layerProperty.sql = "";

Expand All @@ -628,27 +647,52 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
}
}

if ( nColumns == 0 )
if ( nColumns == 0 && schema.isEmpty() )
{
QgsMessageLog::logMessage( tr( "Database connection was successful, but no accessible tables were found. Please verify that you have SELECT privilege on a table carrying PostGIS geometry." ), tr( "PostGIS" ) );
QgsMessageLog::logMessage( tr( "Database connection was successful, but the accessible tables could not be determined." ), tr( "PostGIS" ) );
}

return true;
}

bool QgsPostgresConn::supportedLayers( QVector<QgsPostgresLayerProperty> &layers, bool searchGeometryColumnsOnly, bool searchPublicOnly, bool allowGeometrylessTables )
bool QgsPostgresConn::supportedLayers( QVector<QgsPostgresLayerProperty> &layers, bool searchGeometryColumnsOnly, bool searchPublicOnly, bool allowGeometrylessTables, const QString schema )
{
// Get the list of supported tables
if ( !getTableInfo( searchGeometryColumnsOnly, searchPublicOnly, allowGeometrylessTables ) )
if ( !getTableInfo( searchGeometryColumnsOnly, searchPublicOnly, allowGeometrylessTables, schema ) )
{
QgsMessageLog::logMessage( tr( "Unable to get list of spatially enabled tables from the database" ), tr( "PostGIS" ) );
return false;
}

layers = mLayersSupported;

QgsDebugMsg( "Exiting." );
//QgsDebugMsg( "Exiting." );

return true;
}

bool QgsPostgresConn::getSchemas( QList<QgsPostgresSchemaProperty> &schemas )
{
schemas.clear();
QgsPostgresResult result;

QString sql = QString( "SELECT nspname, pg_get_userbyid(nspowner), pg_catalog.obj_description(oid) FROM pg_namespace WHERE nspname !~ '^pg_' AND nspname != 'information_schema' ORDER BY nspname" );

result = PQexec( sql, true );
if ( result.PQresultStatus() != PGRES_TUPLES_OK )
{
PQexecNR( "COMMIT" );
return false;
}

for ( int idx = 0; idx < result.PQntuples(); idx++ )
{
QgsPostgresSchemaProperty schema;
schema.name = result.PQgetvalue( idx, 0 );
schema.owner = result.PQgetvalue( idx, 1 );
schema.description = result.PQgetvalue( idx, 2 );
schemas << schema;
}
return true;
}

Expand Down Expand Up @@ -1193,7 +1237,7 @@ void QgsPostgresConn::retrieveLayerTypes( QgsPostgresLayerProperty &layerPropert

query += " FROM " + table;

QgsDebugMsg( "Retrieving geometry types: " + query );
//QgsDebugMsg( "Retrieving geometry types: " + query );

QgsPostgresResult gresult = PQexec( query );

Expand Down
40 changes: 36 additions & 4 deletions src/providers/postgres/qgspostgresconn.h
View file
Expand Up @@ -50,6 +50,14 @@ enum QgsPostgresPrimaryKeyType
pktFidMap
};

/** Schema properties structure */
struct QgsPostgresSchemaProperty
{
QString name;
QString description;
QString owner;
};

/** Layer Property structure */
// TODO: Fill to Postgres/PostGIS specifications
struct QgsPostgresLayerProperty
Expand Down Expand Up @@ -230,16 +238,40 @@ class QgsPostgresConn : public QObject
*/
static QString quotedValue( QVariant value );

//! Get the list of supported layers
/**Get the list of supported layers
* @param layers list to store layers in
* @param searchGeometryColumnsOnly only look for geometry columns which are
* contained in the geometry_columns metatable
* @param searchPublicOnly
* @param allowGeometrylessTables
* @param schema restrict layers to layers within specified schema
* @returns true if layers were fetched successfully
*/
bool supportedLayers( QVector<QgsPostgresLayerProperty> &layers,
bool searchGeometryColumnsOnly = true,
bool searchPublicOnly = true,
bool allowGeometrylessTables = false );
bool allowGeometrylessTables = false,
const QString schema = QString() );

/**Get the list of database schemas
* @param schemas list to store schemas in
* @returns true if schemas where fetched successfully
* @note added in QGIS 2.7
*/
bool getSchemas( QList<QgsPostgresSchemaProperty> &schemas );

void retrieveLayerTypes( QgsPostgresLayerProperty &layerProperty, bool useEstimatedMetadata );

/** Gets information about the spatial tables */
bool getTableInfo( bool searchGeometryColumnsOnly, bool searchPublicOnly, bool allowGeometrylessTables );
/**Gets information about the spatial tables
* @param searchGeometryColumnsOnly only look for geometry columns which are
* contained in the geometry_columns metatable
* @param searchPublicOnly
* @param allowGeometrylessTables
* @param schema restrict tables to those within specified schema
* @returns true if tables were successfully queried
*/
bool getTableInfo( bool searchGeometryColumnsOnly, bool searchPublicOnly, bool allowGeometrylessTables,
const QString schema = QString() );

qint64 getBinaryInt( QgsPostgresResult &queryResult, int row, int col );

Expand Down
44 changes: 25 additions & 19 deletions src/providers/postgres/qgspostgresdataitems.cpp
View file
Expand Up @@ -56,11 +56,8 @@ QVector<QgsDataItem*> QgsPGConnectionItem::createChildren()
return items;
}

QVector<QgsPostgresLayerProperty> layerProperties;
bool ok = conn->supportedLayers( layerProperties,
QgsPostgresConn::geometryColumnsOnly( mName ),
QgsPostgresConn::publicSchemaOnly( mName ),
QgsPostgresConn::allowGeometrylessTables( mName ) );
QList<QgsPostgresSchemaProperty> schemas;
bool ok = conn->getSchemas( schemas );

QgsPostgresConnPool::instance()->releaseConnection( conn );

Expand All @@ -70,15 +67,13 @@ QVector<QgsDataItem*> QgsPGConnectionItem::createChildren()
return items;
}

QSet<QString> schemaNames;
foreach ( QgsPostgresLayerProperty layerProperty, layerProperties )
foreach ( QgsPostgresSchemaProperty schema, schemas )
{
schemaNames.insert( layerProperty.schemaName );
}

foreach ( QString schemaName, schemaNames )
{
QgsPGSchemaItem * schemaItem = new QgsPGSchemaItem( this, mName, schemaName, mPath + "/" + schemaName );
QgsPGSchemaItem * schemaItem = new QgsPGSchemaItem( this, mName, schema.name, mPath + "/" + schema.name );
if ( !schema.description.isEmpty() )
{
schemaItem->setToolTip( schema.description );
}
items.append( schemaItem );
}

Expand Down Expand Up @@ -303,9 +298,9 @@ QVector<QgsDataItem*> QgsPGSchemaItem::createChildren()

QVector<QgsPostgresLayerProperty> layerProperties;
bool ok = conn->supportedLayers( layerProperties,
QgsPostgresConn::geometryColumnsOnly( mName ),
QgsPostgresConn::publicSchemaOnly( mName ),
QgsPostgresConn::allowGeometrylessTables( mName ) );
QgsPostgresConn::geometryColumnsOnly( mConnectionName ),
QgsPostgresConn::publicSchemaOnly( mConnectionName ),
QgsPostgresConn::allowGeometrylessTables( mConnectionName ), mName );

if ( !ok )
{
Expand All @@ -314,7 +309,7 @@ QVector<QgsDataItem*> QgsPGSchemaItem::createChildren()
return items;
}

bool dontResolveType = QgsPostgresConn::dontResolveType( mName );
bool dontResolveType = QgsPostgresConn::dontResolveType( mConnectionName );
foreach ( QgsPostgresLayerProperty layerProperty, layerProperties )
{
if ( layerProperty.schemaName != mName )
Expand All @@ -326,7 +321,7 @@ QVector<QgsDataItem*> QgsPGSchemaItem::createChildren()
{
if ( dontResolveType )
{
QgsDebugMsg( QString( "skipping column %1.%2 without type constraint" ).arg( layerProperty.schemaName ).arg( layerProperty.tableName ) );
//QgsDebugMsg( QString( "skipping column %1.%2 without type constraint" ).arg( layerProperty.schemaName ).arg( layerProperty.tableName ) );
continue;
}

Expand All @@ -345,9 +340,20 @@ QVector<QgsDataItem*> QgsPGSchemaItem::createChildren()
return items;
}

QList<QAction *> QgsPGSchemaItem::actions()
{
QList<QAction*> lst;

QAction* actionRefresh = new QAction( tr( "Refresh" ), this );
connect( actionRefresh, SIGNAL( triggered() ), this, SLOT( refresh() ) );
lst.append( actionRefresh );

return lst;
}

QgsPGLayerItem *QgsPGSchemaItem::createLayer( QgsPostgresLayerProperty layerProperty )
{
QgsDebugMsg( "schemaName = " + layerProperty.schemaName + " tableName = " + layerProperty.tableName + " geometryColName = " + layerProperty.geometryColName );
//QgsDebugMsg( "schemaName = " + layerProperty.schemaName + " tableName = " + layerProperty.tableName + " geometryColName = " + layerProperty.geometryColName );
QGis::WkbType wkbType = layerProperty.types[0];
QString tip = tr( "%1 as %2 in %3" ).arg( layerProperty.geometryColName ).arg( QgsPostgresConn::displayStringForWkbType( wkbType ) ).arg( layerProperty.srids[0] );

Expand Down
1 change: 1 addition & 0 deletions src/providers/postgres/qgspostgresdataitems.h
View file
Expand Up @@ -84,6 +84,7 @@ class QgsPGSchemaItem : public QgsDataCollectionItem
~QgsPGSchemaItem();

QVector<QgsDataItem*> createChildren();
virtual QList<QAction*> actions();

private:
QgsPGLayerItem * createLayer( QgsPostgresLayerProperty layerProperty );
Expand Down

0 comments on commit 2c773a7

Please sign in to comment.