QStringList QgsPostgresConn::pkCandidates( QString schemaName, QString viewName )
{
QStringList cols;
QString sql = QString( "SELECT attname FROM pg_attribute JOIN pg_type ON atttypid=pg_type.oid WHERE pg_type.typname IN ('int2','int4','int8','oid','serial','serial8') AND attrelid=regclass('%1.%2')" )
.arg( quotedIdentifier( schemaName ) )
.arg( quotedIdentifier( viewName ) );
QgsDebugMsg( sql );
QgsPostgresResult colRes = PQexec( sql );
if ( colRes.PQresultStatus() == PGRES_TUPLES_OK )
{
for ( int i = 0; i < colRes.PQntuples(); i++ )
{
QgsDebugMsg( colRes.PQgetvalue( i, 0 ) );
cols << colRes.PQgetvalue( i, 0 );
}
}
else
{
QgsMessageLog::logMessage( tr( "SQL:%1\nresult:%2\nerror:%3\n" ).arg( sql ).arg( colRes.PQresultStatus() ).arg( colRes.PQresultErrorMessage() ), tr( "PostGIS" ) );
}
return cols;
}
bool QgsPostgresFeatureIterator::fetchFeature( QgsFeature& feature )
{
feature.setValid( false );
if ( mClosed )
return false;
if ( mFeatureQueue.empty() )
{
QString fetch = QString( "FETCH FORWARD %1 FROM %2" ).arg( mFeatureQueueSize ).arg( mCursorName );
QgsDebugMsgLevel( QString( "fetching %1 features." ).arg( mFeatureQueueSize ), 4 );
if ( mConn->PQsendQuery( fetch ) == 0 ) // fetch features asynchronously
{
QgsMessageLog::logMessage( QObject::tr( "Fetching from cursor %1 failed\nDatabase error: %2" ).arg( mCursorName, mConn->PQerrorMessage() ), QObject::tr( "PostGIS" ) );
}
QgsPostgresResult queryResult;
for ( ;; )
{
queryResult = mConn->PQgetResult();
if ( !queryResult.result() )
break;
if ( queryResult.PQresultStatus() != PGRES_TUPLES_OK )
{
QgsMessageLog::logMessage( QObject::tr( "Fetching from cursor %1 failed\nDatabase error: %2" ).arg( mCursorName, mConn->PQerrorMessage() ), QObject::tr( "PostGIS" ) );
break;
}
int rows = queryResult.PQntuples();
if ( rows == 0 )
continue;
for ( int row = 0; row < rows; row++ )
{
mFeatureQueue.enqueue( QgsFeature() );
getFeature( queryResult, row, mFeatureQueue.back() );
} // for each row in queue
}
}
if ( mFeatureQueue.empty() )
{
QgsDebugMsg( QString( "Finished after %1 features" ).arg( mFetched ) );
close();
mSource->mShared->ensureFeaturesCountedAtLeast( mFetched );
return false;
}
feature = mFeatureQueue.dequeue();
mFetched++;
feature.setValid( true );
feature.setFields( mSource->mFields ); // allow name-based attribute lookups
return true;
}
bool QgsPostgresConn::PQexecNR( QString query, bool retry )
{
QgsPostgresResult res = PQexec( query, false );
ExecStatusType errorStatus = res.PQresultStatus();
if ( errorStatus == PGRES_COMMAND_OK )
return true;
QgsMessageLog::logMessage( tr( "Query: %1 returned %2 [%3]" )
.arg( query )
.arg( errorStatus )
.arg( res.PQresultErrorMessage() ),
tr( "PostGIS" ) );
if ( mOpenCursors )
{
QgsMessageLog::logMessage( tr( "%1 cursor states lost.\nSQL: %2\nResult: %3 (%4)" )
.arg( mOpenCursors ).arg( query ).arg( errorStatus )
.arg( res.PQresultErrorMessage() ), tr( "PostGIS" ) );
mOpenCursors = 0;
}
if ( PQstatus() == CONNECTION_OK )
{
PQexecNR( "ROLLBACK" );
}
else if ( retry )
{
QgsMessageLog::logMessage( tr( "resetting bad connection." ), tr( "PostGIS" ) );
::PQreset( mConn );
if ( PQstatus() == CONNECTION_OK )
{
if ( PQexecNR( query, false ) )
{
QgsMessageLog::logMessage( tr( "retry after reset succeeded." ), tr( "PostGIS" ) );
return true;
}
else
{
QgsMessageLog::logMessage( tr( "retry after reset failed again." ), tr( "PostGIS" ) );
return false;
}
}
else
{
QgsMessageLog::logMessage( tr( "connection still bad after reset." ), tr( "PostGIS" ) );
}
}
else
{
QgsMessageLog::logMessage( tr( "bad connection, not retrying." ), tr( "PostGIS" ) );
}
return false;
}
bool QgsPostgresTransaction::executeSql( const QString &sql, QString &errorMsg )
{
if ( !mConn )
{
return false;
}
QgsDebugMsg( QString( "Transaction sql: %1" ).arg( sql ) );
mConn->lock();
QgsPostgresResult r = mConn->PQexec( sql, true );
mConn->unlock();
if ( r.PQresultStatus() != PGRES_COMMAND_OK )
{
errorMsg = QString( "Status %1 (%2)" ).arg( r.PQresultStatus() ).arg( r.PQresultErrorMessage() );
QgsDebugMsg( errorMsg );
return false;
}
QgsDebugMsg( QString( "Status %1 (OK)" ).arg( r.PQresultStatus() ) );
return true;
}
bool QgsPostgresFeatureIterator::nextFeature( QgsFeature& feature )
{
feature.setValid( false );
if ( mClosed )
return false;
#if 0
// featureAtId used to have some special checks - necessary?
if ( !mUseQueue )
{
QgsPostgresResult queryResult = P->mConnectionRO->PQexec( QString( "FETCH FORWARD 1 FROM %1" ).arg( mCursorName ) );
int rows = queryResult.PQntuples();
if ( rows == 0 )
{
QgsMessageLog::logMessage( tr( "feature %1 not found" ).arg( featureId ), tr( "PostGIS" ) );
P->mConnectionRO->closeCursor( cursorName );
return false;
}
else if ( rows != 1 )
{
QgsMessageLog::logMessage( tr( "found %1 features instead of just one." ).arg( rows ), tr( "PostGIS" ) );
}
bool gotit = getFeature( queryResult, 0, feature );
feature.setValid( gotit );
feature.setFields( &P->mAttributeFields ); // allow name-based attribute lookups
return gotit;
}
#endif
if ( mFeatureQueue.empty() )
{
QString fetch = QString( "FETCH FORWARD %1 FROM %2" ).arg( mFeatureQueueSize ).arg( mCursorName );
QgsDebugMsgLevel( QString( "fetching %1 features." ).arg( mFeatureQueueSize ), 4 );
if ( P->mConnectionRO->PQsendQuery( fetch ) == 0 ) // fetch features asynchronously
{
QgsMessageLog::logMessage( QObject::tr( "Fetching from cursor %1 failed\nDatabase error: %2" ).arg( mCursorName ).arg( P->mConnectionRO->PQerrorMessage() ), QObject::tr( "PostGIS" ) );
}
QgsPostgresResult queryResult;
for ( ;; )
{
queryResult = P->mConnectionRO->PQgetResult();
if ( !queryResult.result() )
break;
if ( queryResult.PQresultStatus() != PGRES_TUPLES_OK )
{
QgsMessageLog::logMessage( QObject::tr( "Fetching from cursor %1 failed\nDatabase error: %2" ).arg( mCursorName ).arg( P->mConnectionRO->PQerrorMessage() ), QObject::tr( "PostGIS" ) );
break;
}
int rows = queryResult.PQntuples();
if ( rows == 0 )
continue;
for ( int row = 0; row < rows; row++ )
{
mFeatureQueue.enqueue( QgsFeature() );
getFeature( queryResult, row, mFeatureQueue.back() );
} // for each row in queue
}
}
if ( mFeatureQueue.empty() )
{
QgsDebugMsg( QString( "Finished after %1 features" ).arg( mFetched ) );
close();
if ( P->mFeaturesCounted < mFetched )
{
QgsDebugMsg( QString( "feature count adjusted from %1 to %2" ).arg( P->mFeaturesCounted ).arg( mFetched ) );
P->mFeaturesCounted = mFetched;
}
return false;
}
// Now return the next feature from the queue
if ( mRequest.flags() & QgsFeatureRequest::NoGeometry )
{
feature.setGeometryAndOwnership( 0, 0 );
}
else
{
QgsGeometry* featureGeom = mFeatureQueue.front().geometryAndOwnership();
feature.setGeometry( featureGeom );
}
feature.setFeatureId( mFeatureQueue.front().id() );
feature.setAttributes( mFeatureQueue.front().attributes() );
mFeatureQueue.dequeue();
mFetched++;
feature.setValid( true );
feature.setFields( &P->mAttributeFields ); // allow name-based attribute lookups
return true;
}
bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchPublicOnly, bool allowGeometrylessTables )
{
int nColumns = 0;
int foundInTables = 0;
QgsPostgresResult result;
QgsPostgresLayerProperty layerProperty;
QgsDebugMsg( "Entering." );
mLayersSupported.clear();
for ( int i = 0; i < 3; i++ )
{
QString sql, tableName, schemaName, columnName, typeName, sridName, gtableName;
QgsPostgresGeometryColumnType columnType = sctGeometry;
if ( i == 0 )
{
tableName = "l.f_table_name";
schemaName = "l.f_table_schema";
columnName = "l.f_geometry_column";
typeName = "upper(l.type)";
sridName = "l.srid";
gtableName = "geometry_columns";
columnType = sctGeometry;
}
else if ( i == 1 )
{
tableName = "l.f_table_name";
schemaName = "l.f_table_schema";
columnName = "l.f_geography_column";
typeName = "upper(l.type)";
sridName = "l.srid";
gtableName = "geography_columns";
columnType = sctGeography;
}
else if ( i == 2 )
{
schemaName = "l.schema_name";
tableName = "l.table_name";
columnName = "l.feature_column";
typeName = "CASE "
"WHEN l.feature_type = 1 THEN 'MULTIPOINT' "
"WHEN l.feature_type = 2 THEN 'MULTILINESTRING' "
"WHEN l.feature_type = 3 THEN 'MULTIPOLYGON' "
"WHEN l.feature_type = 4 THEN 'GEOMETRYCOLLECTION' "
"END AS type";
sridName = "(SELECT srid FROM topology.topology t WHERE l.topology_id=t.id)";
gtableName = "topology.layer";
columnType = sctTopoGeometry;
}
// The following query returns only tables that exist and the user has SELECT privilege on.
// Can't use regclass here because table must exist, else error occurs.
sql = QString( "SELECT %1,%2,%3,%4,%5,c.relkind"
" FROM %6 l,pg_class c,pg_namespace n"
" WHERE c.relname=%1"
" AND %2=n.nspname"
" AND n.oid=c.relnamespace"
" AND has_schema_privilege(n.nspname,'usage')"
" AND has_table_privilege('\"'||n.nspname||'\".\"'||c.relname||'\"','select')" // user has select privilege
)
.arg( tableName ).arg( schemaName ).arg( columnName ).arg( typeName ).arg( sridName ).arg( gtableName );
if ( searchPublicOnly )
sql += " AND n.nspname='public'";
sql += QString( " ORDER BY n.nspname,c.relname,%1" ).arg( columnName );
QgsDebugMsg( "getting table info: " + sql );
result = PQexec( sql, i == 0 );
if ( result.PQresultStatus() != PGRES_TUPLES_OK )
{
PQexecNR( "COMMIT" );
continue;
}
for ( int idx = 0; idx < result.PQntuples(); idx++ )
{
QString tableName = result.PQgetvalue( idx, 0 );
QString schemaName = result.PQgetvalue( idx, 1 );
QString column = result.PQgetvalue( idx, 2 );
QString type = result.PQgetvalue( idx, 3 );
QString srid = result.PQgetvalue( idx, 4 );
QString relkind = result.PQgetvalue( idx, 5 );
QgsDebugMsg( QString( "%1 : %2.%3.%4: %5 %6 %7" )
.arg( gtableName )
.arg( schemaName ).arg( tableName ).arg( column )
.arg( type )
.arg( srid )
.arg( relkind ) );
layerProperty.pkCols.clear();
layerProperty.type = type;
layerProperty.schemaName = schemaName;
layerProperty.tableName = tableName;
layerProperty.geometryColName = column;
layerProperty.geometryColType = columnType;
if ( relkind == "v" )
{
layerProperty.pkCols = pkCandidates( schemaName, tableName );
if ( layerProperty.pkCols.isEmpty() )
{
QgsDebugMsg( "no key columns found." );
continue;
}
}
layerProperty.srid = srid;
layerProperty.sql = "";
mLayersSupported << layerProperty;
nColumns++;
}
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 )
{
// Now have a look for geometry columns that aren't in the geometry_columns table.
QString sql = "SELECT"
" c.relname"
",n.nspname"
",a.attname"
",c.relkind"
",CASE WHEN t.typname IN ('geometry','geography','topogeometry') THEN t.typname ELSE b.typname END AS coltype"
" FROM pg_attribute a"
" JOIN pg_class c ON c.oid=a.attrelid"
" JOIN pg_namespace n ON n.oid=c.relnamespace"
" JOIN pg_type t ON t.oid=a.atttypid"
" LEFT JOIN pg_type b ON b.oid=t.typbasetype"
" WHERE c.relkind IN ('v','r')"
" AND has_schema_privilege( n.nspname, 'usage' )"
" AND has_table_privilege( '\"' || n.nspname || '\".\"' || c.relname || '\"', 'select' )"
" AND (t.typname IN ('geometry','geography','topogeometry') OR b.typname IN ('geometry','geography','topogeometry'))";
// user has select privilege
if ( searchPublicOnly )
sql += " AND n.nspname='public'";
// skip columns of which we already derived information from the metadata tables
if ( nColumns > 0 )
{
if ( foundInTables & 1 )
{
sql += " AND (n.nspname,c.relname,a.attname) NOT IN (SELECT f_table_schema,f_table_name,f_geometry_column FROM geometry_columns)";
}
if ( foundInTables & 2 )
{
sql += " AND (n.nspname,c.relname,a.attname) NOT IN (SELECT f_table_schema,f_table_name,f_geography_column FROM geography_columns)";
}
if ( foundInTables & 4 )
{
sql += " AND (n.nspname,c.relname,a.attname) NOT IN (SELECT schema_name,table_name,feature_column FROM topology.layer)";
}
}
QgsDebugMsg( "sql: " + sql );
result = PQexec( sql );
if ( result.PQresultStatus() != PGRES_TUPLES_OK )
{
QgsMessageLog::logMessage( tr( "Database connection was successful, but the accessible tables could not be determined. The error message from the database was:\n%1\n" )
.arg( result.PQresultErrorMessage() ),
tr( "PostGIS" ) );
PQexecNR( "COMMIT" );
return false;
}
for ( int i = 0; i < result.PQntuples(); i++ )
{
// Have the column name, schema name and the table name. The concept of a
// catalog doesn't exist in postgresql so we ignore that, but we
// do need to get the geometry type.
// Make the assumption that the geometry type for the first
// row is the same as for all other rows.
QString tableName = result.PQgetvalue( i, 0 ); // relname
QString schemaName = result.PQgetvalue( i, 1 ); // nspname
QString column = result.PQgetvalue( i, 2 ); // attname
QString relkind = result.PQgetvalue( i, 3 ); // relation kind
QString coltype = result.PQgetvalue( i, 4 ); // column type
QgsDebugMsg( QString( "%1.%2.%3: %4" ).arg( schemaName ).arg( tableName ).arg( column ).arg( relkind ) );
layerProperty.type = QString::null;
layerProperty.schemaName = schemaName;
layerProperty.tableName = tableName;
layerProperty.geometryColName = column;
if ( coltype == "geometry" )
{
layerProperty.geometryColType = sctGeometry;
}
else if ( coltype == "geography" )
{
layerProperty.geometryColType = sctGeography;
}
else if ( coltype == "topogeometry" )
{
layerProperty.geometryColType = sctTopoGeometry;
}
else
{
Q_ASSERT( !"Unknown geometry type" );
}
if ( relkind == "v" )
{
layerProperty.pkCols = pkCandidates( schemaName, tableName );
if ( layerProperty.pkCols.isEmpty() )
{
QgsDebugMsg( "no key columns found." );
continue;
}
}
layerProperty.sql = "";
mLayersSupported << layerProperty;
nColumns++;
}
}
if ( allowGeometrylessTables )
{
QString sql = "SELECT "
"pg_class.relname"
",pg_namespace.nspname"
",pg_class.relkind"
" FROM "
" pg_class"
",pg_namespace"
" WHERE pg_namespace.oid=pg_class.relnamespace"
" AND has_schema_privilege(pg_namespace.nspname,'usage')"
" AND has_table_privilege('\"' || pg_namespace.nspname || '\".\"' || pg_class.relname || '\"','select')"
" AND pg_class.relkind IN ('v','r')";
// user has select privilege
if ( searchPublicOnly )
sql += " AND pg_namespace.nspname='public'";
QgsDebugMsg( "sql: " + sql );
result = PQexec( sql );
if ( result.PQresultStatus() != PGRES_TUPLES_OK )
{
QgsMessageLog::logMessage( tr( "Database connection was successful, but the accessible tables could not be determined.\nThe error message from the database was:\n%1" )
.arg( result.PQresultErrorMessage() ),
tr( "PostGIS" ) );
return false;
}
for ( int i = 0; i < result.PQntuples(); i++ )
{
QString table = result.PQgetvalue( i, 0 ); // relname
QString schema = result.PQgetvalue( i, 1 ); // nspname
QString relkind = result.PQgetvalue( i, 2 ); // relation kind
QgsDebugMsg( QString( "%1.%2: %3" ).arg( schema ).arg( table ).arg( relkind ) );
layerProperty.type = QString::null;
layerProperty.schemaName = schema;
layerProperty.tableName = table;
layerProperty.geometryColName = QString::null;
layerProperty.geometryColType = sctNone;
layerProperty.pkCols = relkind == "v" ? pkCandidates( schema, table ) : QStringList();
layerProperty.srid = "";
layerProperty.sql = "";
mLayersSupported << layerProperty;
nColumns++;
}
}
if ( nColumns == 0 )
{
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" ) );
}
return true;
}
void QgsPostgresConn::retrieveLayerTypes( QgsPostgresLayerProperty &layerProperty, bool useEstimatedMetadata )
{
QString table;
if ( !layerProperty.schemaName.isEmpty() )
{
table = QString( "%1.%2" )
.arg( quotedIdentifier( layerProperty.schemaName ) )
.arg( quotedIdentifier( layerProperty.tableName ) );
}
else
{
// Query
table = layerProperty.tableName;
}
// our estimatation ignores that a where clause might restrict the feature type or srid
if ( useEstimatedMetadata )
{
table = QString( "(SELECT %1 FROM %2 WHERE %1 IS NOT NULL%3 LIMIT %4) AS t" )
.arg( quotedIdentifier( layerProperty.geometryColName ) )
.arg( table )
.arg( layerProperty.sql.isEmpty() ? "" : QString( " AND (%1)" ).arg( layerProperty.sql ) )
.arg( sGeomTypeSelectLimit );
}
else if ( !layerProperty.sql.isEmpty() )
{
table += QString( " WHERE %1" ).arg( layerProperty.sql );
}
QString query = QString( "SELECT DISTINCT"
" CASE"
" WHEN %1 THEN 'POINT'"
" WHEN %2 THEN 'LINESTRING'"
" WHEN %3 THEN 'POLYGON'"
" END,"
" %4(%5%6)"
" FROM %7" )
.arg( postgisTypeFilter( layerProperty.geometryColName, QGis::WKBPoint, layerProperty.geometryColType == sctGeography ) )
.arg( postgisTypeFilter( layerProperty.geometryColName, QGis::WKBLineString, layerProperty.geometryColType == sctGeography ) )
.arg( postgisTypeFilter( layerProperty.geometryColName, QGis::WKBPolygon, layerProperty.geometryColType == sctGeography ) )
.arg( majorVersion() < 2 ? "srid" : "st_srid" )
.arg( quotedIdentifier( layerProperty.geometryColName ) )
.arg( layerProperty.geometryColType == sctGeography ? "::geometry" : "" )
.arg( table );
QgsDebugMsg( "Retrieving geometry types: " + query );
QgsPostgresResult gresult = PQexec( query );
QString type;
QString srid;
if ( gresult.PQresultStatus() == PGRES_TUPLES_OK )
{
QStringList types;
QStringList srids;
for ( int i = 0; i < gresult.PQntuples(); i++ )
{
QString type = gresult.PQgetvalue( i, 0 );
QString srid = gresult.PQgetvalue( i, 1 );
if ( type.isEmpty() )
continue;
types << type;
srids << srid;
}
type = types.join( "," );
srid = srids.join( "," );
}
QgsDebugMsg( QString( "type:%1 srid:%2" ).arg( type ).arg( srid ) );
layerProperty.type = type;
layerProperty.srid = srid;
}
bool QgsPostgresFeatureIterator::fetchFeature( QgsFeature& feature )
{
feature.setValid( false );
if ( mClosed )
return false;
if ( mFeatureQueue.empty() )
{
QString fetch = QString( "FETCH FORWARD %1 FROM %2" ).arg( mFeatureQueueSize ).arg( mCursorName );
QgsDebugMsgLevel( QString( "fetching %1 features." ).arg( mFeatureQueueSize ), 4 );
if ( P->mConnectionRO->PQsendQuery( fetch ) == 0 ) // fetch features asynchronously
{
QgsMessageLog::logMessage( QObject::tr( "Fetching from cursor %1 failed\nDatabase error: %2" ).arg( mCursorName ).arg( P->mConnectionRO->PQerrorMessage() ), QObject::tr( "PostGIS" ) );
}
QgsPostgresResult queryResult;
for ( ;; )
{
queryResult = P->mConnectionRO->PQgetResult();
if ( !queryResult.result() )
break;
if ( queryResult.PQresultStatus() != PGRES_TUPLES_OK )
{
QgsMessageLog::logMessage( QObject::tr( "Fetching from cursor %1 failed\nDatabase error: %2" ).arg( mCursorName ).arg( P->mConnectionRO->PQerrorMessage() ), QObject::tr( "PostGIS" ) );
break;
}
int rows = queryResult.PQntuples();
if ( rows == 0 )
continue;
for ( int row = 0; row < rows; row++ )
{
mFeatureQueue.enqueue( QgsFeature() );
getFeature( queryResult, row, mFeatureQueue.back() );
} // for each row in queue
}
}
if ( mFeatureQueue.empty() )
{
QgsDebugMsg( QString( "Finished after %1 features" ).arg( mFetched ) );
close();
/* only updates the feature count if it was already once.
* Otherwise, this would lead to false feature count if
* an existing project is open at a restrictive extent.
*/
if ( P->mFeaturesCounted > 0 && P->mFeaturesCounted < mFetched )
{
QgsDebugMsg( QString( "feature count adjusted from %1 to %2" ).arg( P->mFeaturesCounted ).arg( mFetched ) );
P->mFeaturesCounted = mFetched;
}
return false;
}
// Now return the next feature from the queue
if ( mRequest.flags() & QgsFeatureRequest::NoGeometry )
{
feature.setGeometryAndOwnership( 0, 0 );
}
else
{
QgsGeometry* featureGeom = mFeatureQueue.front().geometryAndOwnership();
feature.setGeometry( featureGeom );
}
feature.setFeatureId( mFeatureQueue.front().id() );
feature.setAttributes( mFeatureQueue.front().attributes() );
mFeatureQueue.dequeue();
mFetched++;
feature.setValid( true );
feature.setFields( &P->mAttributeFields ); // allow name-based attribute lookups
return true;
}
