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 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;
}
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;
}